Query for finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module

WITH PARAMETER AS (SELECT :PERIOD_NAME AS PERIOD_NAME FROM DUAL)
SELECT distinct prj.project_id,prj.segment1,
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period   =PARAMETER.PERIOD_NAME
    -- and organization_id=TXN.organization_id
  ) "MTD Cost",
  (SELECT ROUND(SUM(unbilled_receivable_dr),2)--round(SUM(tot_revenue),2)
  FROM apps.pa_draft_revenues_all
  WHERE project_id              =prj.project_id
  AND TO_CHAR(gl_date,'MON-YY') =PARAMETER.PERIOD_NAME
  ) "MTD Revenue",
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period  IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
    -- and organization_id=TXN.organization_id
  ) "YTD Cost",
  (SELECT ROUND(SUM(tot_revenue),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period  IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
     ) "YTD Revenue",
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM pa.pa_txn_accum
  WHERE project_id=prj.project_id
  ) "ITD Cost",
  (SELECT ROUND(SUM(tot_revenue),2)
  FROM pa.pa_txn_accum
  WHERE project_id=prj.project_id
  )"ITD Revenue",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id                   =prj.project_id
  AND TO_CHAR(creation_date,'MON-YY')=PARAMETER.PERIOD_NAME
  )"MTD Fee",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id                     =prj.project_id
  AND TO_CHAR(creation_date,'MON-YY') IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
  )"YTD Fee",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id=prj.project_id
  )"ITD Fee",
  (SELECT SUM(allocated_amount)
  FROM pa_project_fundings
  WHERE project_id=prj.project_id
  )"Funded Revenue",
  (SELECT SUM(a.amount) "Revenue To Date"
  FROM pa.PA_DRAFT_REVENUE_ITEMS# a,
    pa.PA_DRAFT_REVENUES_ALL# b,
    pa.pa_projects_all ppa
  WHERE a.project_id      = ppa.project_id
  AND a.DRAFT_REVENUE_NUM = b.DRAFT_REVENUE_NUM
  AND a.project_id        = b.project_id
  AND b.GL_DATE          <=
    (SELECT END_DATE
    FROM gl.gl_period_statuses
    WHERE end_date =
      (SELECT MAX(end_date)
      FROM gl.gl_period_statuses
      WHERE application_id       = 101
      AND set_of_books_id        = 2022
      AND closing_status         = 'C'
      AND adjustment_period_flag = 'N'
      )
    AND application_id         = 101
    AND set_of_books_id        = 2022
    AND adjustment_period_flag = 'N'
    )
  AND ppa.project_id=prj.project_id
  )"GL Close Revenue"
FROM
    PA.PA_PROJECTS_ALL# PRJ,
  PA.PA_PROJECT_TYPES_ALL# PRJT,
  PA.PA_TXN_ACCUM# TXN,PARAMETER
WHERE prj.project_id                              = txn.project_id
AND prj.project_status_code                     ='APPROVED'
AND prj.PROJECT_TYPE                            = PRJT.PROJECT_TYPE(+)
AND prjt.project_type_class_code                ='CONTRACT'
AND TO_CHAR(week_ending_date , 'MON-RR') = PARAMETER.PERIOD_NAME