Project Accounting Labor Revenue query - FUSION

SELECT prd.org_id employee_cost_org_id, prd.gl_period_name gl_period,
       NVL (pei.override_to_organization_id,
            pei.incurred_by_organization_id
           ) expenditure_org_id,
       trx_org.NAME expenditure_org, fnbu.bu_name,
       prb.carrying_out_organization_id project_owning_org_id,
       (SELECT NAME
          FROM hr_organization_units
         WHERE organization_id =
                          prb.carrying_out_organization_id)
                                                           project_owning_org,
       prb.project_id, prb.segment1 project_number, prl.NAME project_name,
       (SELECT s.project_status_name
          FROM pjf_project_statuses_tl s
         WHERE prb.project_status_code = s.project_status_code)
                                                               project_status,
       ptv.task_id, ptv.task_name, NVL (ptv.billable_flag, 'N') billable_task,
       NVL (pei.billable_flag, 'N') billable_expenditure_item,
       pei.expenditure_item_id, pei.expenditure_item_date,
       pei.expenditure_ending_date,
       pet.expenditure_type_name expenditure_type,
       pec.expenditure_category_name expenditure_category,
       pei.incurred_by_person_id, ppn.full_name employee_name,
       papf.person_number employee_number,
       NVL (prb.project_currency_code,
            pei.project_currency_code
           ) project_currency_code,
       NVL (prd.revenue_curr_amt, 0) project_revenue_amount,
       NVL (pei.projfunc_currency_code,
            prd.ledger_currency_code
           ) projfunc_currency_code,
       NVL (prd.ledger_curr_revenue_amt, 0) projfunc_revenue_amount, 0 amount,
       gcc.segment1 company, gcc.segment2 ACCOUNT, gcc.segment3 department,
       gcc.segment4 region, gcc.segment5 FUNCTION, gcc.segment6 intercompany,
       NULL sub_cost_currency_code, 0 sub_cost_rate, 0 subcontractor_cost,
       prd.trns_currency_code bill_transaction_currency,
       prd.trns_curr_revenue_amt bill_transaction_currency_rev,
       DECODE
          ('USD',
           prd.revenue_currency_code, prd.revenue_curr_amt,
           prd.ledger_currency_code, prd.ledger_curr_revenue_amt,
           prd.contract_currency_code, prd.cont_curr_revenue_amt,
           prd.project_currency_code, prd.project_curr_revenue_amt,
             prd.revenue_curr_amt
           * NVL ((SELECT gdr.conversion_rate
                     FROM gl_daily_rates gdr
                    WHERE gdr.conversion_type = 'Corporate'
                      AND gdr.to_currency = 'USD'
                      AND gdr.from_currency = prd.revenue_currency_code
                      AND gdr.conversion_date = prd.gl_date),
                  1
                 )
          ) usd_daily_revenue_amount,
       pei.bill_hold_flag,
       DECODE ((SELECT '1'
                  FROM pjb_bill_trxs a
                 WHERE pei.expenditure_item_id = a.transaction_id
                   AND NVL (inv_curr_billed_amt, 0) <> 0),
               '1', 'Y',
               'N'
              ) billed_flag,
       prb.attribute4, pei.burden_cost_rate,
       (SELECT project_type
          FROM pjf_project_types_tl ppt
         WHERE ppt.project_type_id = prb.project_type_id) project_type,
       (SELECT class_code
          FROM pjf_project_classes prc, pjf_class_codes_tl pct
         WHERE prc.class_code_id = pct.class_code_id
           AND prc.project_id = prb.project_id) region_code,
       prd.org_id, prd.ledger_curr_revenue_amt functrevenu,
       (SELECT hou1.NAME
          FROM okc_k_headers_all_b okh,
               hr_all_organization_units hou1
         WHERE okh.owning_org_id = hou1.organization_id
           AND (okh.ID, okh.major_version) IN (
                         SELECT   okh1.ID,
                                  MAX (okh1.major_version) major_version
                             FROM okc_k_headers_all_b okh1
                            WHERE okh1.ID = prd.contract_id
                         GROUP BY ID)) contract_org,
       (SELECT sts_code
          FROM okc_k_headers_all_b okh
         WHERE (okh.ID, okh.major_version) IN (
                  SELECT   okh1.ID,
                           MAX (okh1.major_version)
                                               major_version
                      FROM okc_k_headers_all_b okh1
                     WHERE okh1.ID = prd.contract_id
                  GROUP BY ID)) contract_status,
       (SELECT contract_number
          FROM okc_k_headers_all_b okh
         WHERE (okh.ID, okh.major_version) IN (
                  SELECT   okh1.ID,
                           MAX (okh1.major_version)
                                               major_version
                      FROM okc_k_headers_all_b okh1
                     WHERE okh1.ID = prd.contract_id
                  GROUP BY ID)) contract_number,
       (SELECT oct.NAME
          FROM okc_k_headers_all_b okh,
               okc_contract_types_vl oct
         WHERE okh.contract_type_id = oct.contract_type_id
           AND (okh.ID, okh.major_version) IN (
                         SELECT   okh1.ID,
                                  MAX (okh1.major_version) major_version
                             FROM okc_k_headers_all_b okh1
                            WHERE okh1.ID = prd.contract_id
                         GROUP BY ID)) contract_type,
       prd.contract_currency_code, prd.cont_curr_revenue_amt,
       prd.revenue_currency_code, prd.ledger_currency_code,
       project_curr_revenue_amt, prd.revenue_curr_amt,
       prd.ledger_curr_revenue_amt, pei.revenue_hold_flag, pei.person_type,
       (SELECT NAME
          FROM per_jobs_f_vl pjb
         WHERE pjb.job_id = pei.person_job_id) job_name,
       pei.expenditure_type_id, prd.bill_rate
  FROM pjc_exp_items_all pei,
       pjf_tasks_v ptv,
       pjf_projects_all_b prb,
       pjf_projects_all_tl prl,
       pjc_exp_comments pec,
       pjf_exp_types_vl pet,
       pjf_exp_categories_tl pec,
       fun_names_business_units_v fnbu,
       gl_ledgers gl,
       gl_code_combinations gcc,
       xla_distribution_links xda,
       xla_ae_lines xal,
       hr_all_organization_units_tl trx_org,
       pjb_rev_distributions prd,
       per_person_names_f ppn,
       per_all_people_f papf
 WHERE 1 = 1
   AND pei.project_id = ptv.project_id(+)
   AND pei.task_id = ptv.task_id(+)
   AND pei.project_id = prb.project_id
   AND pei.project_id = prl.project_id
   AND pei.expenditure_item_id = pec.expenditure_item_id(+)
   AND pei.expenditure_type_id = pet.expenditure_type_id
   AND pec.expenditure_category_id = pet.expenditure_category_id
   AND fnbu.bu_id = prb.org_id
   AND fnbu.primary_ledger_id = gl.ledger_id
   AND prd.sla_event_id = xda.event_id
   AND prd.rev_distribution_id = xda.source_distribution_id_num_1
   AND xda.ae_line_num = 2
   AND xal.ae_header_id = xda.ae_header_id
   AND xal.ae_line_num = xda.ae_line_num
   AND gcc.code_combination_id = xal.code_combination_id
   AND trx_org.organization_id =
          NVL (pei.override_to_organization_id,
               pei.incurred_by_organization_id
              )
   AND prd.line_num_reversed IS NULL
   AND prd.reversed_flag IS NULL
   AND prd.transaction_id = pei.expenditure_item_id
   AND ppn.person_id = pei.incurred_by_person_id
   AND SYSDATE BETWEEN NVL (ppn.effective_start_date, SYSDATE - 1)
                   AND NVL (ppn.effective_end_date, SYSDATE + 1)
   AND ppn.name_type = 'GLOBAL'
   AND papf.person_id = pei.incurred_by_person_id
   AND SYSDATE BETWEEN NVL (papf.effective_start_date, SYSDATE - 1)
                   AND NVL (papf.effective_end_date, SYSDATE + 1)
   AND prd.gl_period_name IN (:p_period_name)
   AND pei.revenue_recognized_flag <> 'U'
   AND pec.expenditure_category_name IN ('Labor')