PA - Project Revenue exception query

SELECT pcdl.prvdr_gl_period_name ei_latest_gl_period_name,
       gcc.segment1 r_company,
       peia.revenue_recognized_flag ei_revenue_distributed_flag,
       ppat.NAME ei_project_name, ppab.segment1 ei_project_number,
       pet.expenditure_type_name ei_expenditure_type,
       pec.expenditure_category_name ei_expenditure_category,
       ppn.full_name ei_employee_name,
       TRUNC (peia.expenditure_item_date) ei_expenditure_item_date,
       peia.receipt_currency_code ei_receipt_currency_code,
       peia.project_currency_code ei_project_currency_code,
       hr.NAME ei_expenditure_org_name, op.NAME ei_prvdr_org_name,
       hr1.NAME project_owning_org,
       CASE
          WHEN peia.bill_trans_curr_rev_amt = 0
             THEN 'N'
          ELSE 'Y'
       END billed_flag, ptv.task_number,
       (SELECT MESSAGE_TEXT
          FROM fnd_messages
         WHERE message_name = pe.ERROR_CODE) rejection_code,
       pcdl.project_raw_cost ei_project_raw_revenue,
       prd.project_curr_revenue_amt r_project_revenue_amount,
       pcdl.quantity ei_quantity, prd.bill_rate ei_bill_rate,
       peia.expenditure_item_id transaction_number, peia.billable_flag,
       (SELECT flv.meaning
          FROM fnd_lookup_values flv
         WHERE flv.lookup_type = 'PJB_EVT_INVOICED_FLAG'
           AND lookup_code = peia.invoiced_flag) invoice_status,
       (SELECT flv1.meaning
          FROM fnd_lookup_values flv1
         WHERE flv1.lookup_type = 'PJB_EVT_REVENUE_RECOGNZD'
           AND flv1.lookup_code = peia.revenue_recognized_flag)
                                                               revenue_status,
       (SELECT pj.NAME
          FROM per_jobs pj
         WHERE pj.job_id = peia.person_job_id) employee_job,
       peia.unit_of_measure
  FROM pjc_exp_items_all peia,
       pjf_exp_types_vl pet,
       pjf_tasks_v ptv,
       pjf_projects_all_b ppab,
       pjf_projects_all_tl ppat,
       hr_all_organization_units hr,
       hr_all_organization_units hr1,
       hr_operating_units op,
       pjc_cost_dist_lines_all pcdl,
       pjf_exp_categories_tl pec,
       gl_code_combinations gcc,
       per_person_names_f ppn,
       pjb_rev_distributions prd,
       pjb_errors pe,
       xla_distribution_links xda,
       xla_ae_lines xal
 WHERE pet.expenditure_type_id = peia.expenditure_type_id
   AND peia.revenue_exception_flag = 'E'
   AND pcdl.acct_source_code <> 'AP_INV'
   AND peia.task_id = ptv.task_id
   AND peia.project_id = ppab.project_id
   AND ppab.project_id = ppat.project_id
   AND NVL (peia.override_to_organization_id,
            peia.incurred_by_organization_id) = hr.organization_id
   AND hr1.organization_id = ppab.carrying_out_organization_id
   AND peia.org_id = op.organization_id
   AND peia.expenditure_item_id = pcdl.expenditure_item_id
   AND pcdl.line_num_reversed IS NULL
   AND pcdl.reversed_flag IS NULL
   AND prd.line_num_reversed IS NULL
   AND prd.reversed_flag IS NULL
   AND pcdl.acct_event_id = xda.event_id(+)
   AND pcdl.expenditure_item_id = xda.source_distribution_id_num_1(+)
   AND pcdl.line_num = xda.source_distribution_id_num_2(+)
   AND (   xda.rounding_class_code NOT LIKE '%CLEARING'
        OR xda.rounding_class_code IS NULL
       )
   AND xda.ae_header_id = xal.ae_header_id(+)
   AND xda.ae_line_num = xal.ae_line_num(+)
   AND gcc.code_combination_id =
          NVL (NVL (pcdl.raw_cost_dr_ccid, pcdl.raw_cost_cr_ccid),
               xal.code_combination_id
              )
   AND pec.expenditure_category_id = pet.expenditure_category_id
   AND ppn.person_id = peia.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 peia.expenditure_item_id = prd.transaction_id(+)
   AND pe.expenditure_item_id = peia.expenditure_item_id
   AND ppab.project_status_code = 'ACTIVE'
   AND EXISTS (SELECT 1
                 FROM okc_k_headers_all_b okh
                WHERE okh.ID = pe.contract_id AND okh.sts_code = 'ACTIVE')
   AND NVL (net_zero_adjustment_flag, 'N') = 'N'
   AND peia.revenue_recognized_flag IN ('P', 'U')
   AND peia.billable_flag = 'Y'
   AND pe.request_id IN (
          SELECT MAX (pe1.request_id)
            FROM pjb_errors pe1
           WHERE pe1.expenditure_item_id = pe.expenditure_item_id
             AND pe1.erroring_process = 'REVENUE_GEN')
UNION ALL
SELECT pcdl.prvdr_gl_period_name ei_latest_gl_period_name,
       gcc.segment1 r_company,
       peia.revenue_recognized_flag ei_revenue_distributed_flag,
       ppat.NAME ei_project_name, ppab.segment1 ei_project_number,
       pet.expenditure_type_name ei_expenditure_type,
       pec.expenditure_category_name ei_expenditure_category,
       ppn.full_name ei_employee_name,
       TRUNC (peia.expenditure_item_date) ei_expenditure_item_date,
       peia.receipt_currency_code ei_receipt_currency_code,
       peia.project_currency_code ei_project_currency_code,
       hr.NAME ei_expenditure_org_name, op.NAME ei_prvdr_org_name,
       hr1.NAME project_owning_org,
       CASE
          WHEN peia.bill_trans_curr_rev_amt = 0
             THEN 'N'
          ELSE 'Y'
       END billed_flag, ptv.task_number,
       (SELECT MESSAGE_TEXT
          FROM fnd_messages
         WHERE message_name = pe.ERROR_CODE) rejection_code,
       pcdl.project_raw_cost ei_project_raw_revenue,
     
       --peia.project_curr_rev_amt
       prd.project_curr_revenue_amt r_project_revenue_amount,
       pcdl.quantity ei_quantity, prd.bill_rate ei_bill_rate,
       peia.expenditure_item_id transaction_number, peia.billable_flag,
       (SELECT flv.meaning
          FROM fnd_lookup_values flv
         WHERE flv.lookup_type = 'PJB_EVT_INVOICED_FLAG'
           AND lookup_code = peia.invoiced_flag) invoice_status,
       (SELECT flv1.meaning
          FROM fnd_lookup_values flv1
         WHERE flv1.lookup_type = 'PJB_EVT_REVENUE_RECOGNZD'
           AND flv1.lookup_code = peia.revenue_recognized_flag)
                                                               revenue_status,
       (SELECT pj.NAME
          FROM per_jobs pj
         WHERE pj.job_id = peia.person_job_id) employee_job,
       peia.unit_of_measure
  FROM pjc_exp_items_all peia,
       pjf_exp_types_vl pet,
       pjf_tasks_v ptv,
       pjf_projects_all_b ppab,
       pjf_projects_all_tl ppat,
       hr_all_organization_units hr,
       hr_all_organization_units hr1,
       hr_operating_units op,
       pjc_cost_dist_lines_all pcdl,
       pjf_exp_categories_tl pec,
       gl_code_combinations gcc,
       per_person_names_f ppn,
       pjb_rev_distributions prd,
       pjb_errors pe,
       xla_distribution_links xda,
       xla_ae_lines xal,
       ap_invoice_distributions_all apd
 WHERE pet.expenditure_type_id = peia.expenditure_type_id
   AND peia.revenue_exception_flag = 'E'
   AND apd.invoice_distribution_id = peia.original_dist_id
   AND pcdl.acct_source_code = 'AP_INV'
   AND peia.task_id = ptv.task_id
   AND peia.project_id = ppab.project_id
   AND ppab.project_id = ppat.project_id
   AND NVL (peia.override_to_organization_id,
            peia.incurred_by_organization_id) = hr.organization_id
   AND hr1.organization_id = ppab.carrying_out_organization_id
   AND peia.org_id = op.organization_id
   AND peia.expenditure_item_id = pcdl.expenditure_item_id
   AND pcdl.line_num_reversed IS NULL
   AND pcdl.reversed_flag IS NULL
   AND prd.line_num_reversed IS NULL
   AND prd.reversed_flag IS NULL
   AND pcdl.acct_event_id = xda.event_id(+)
   AND pcdl.expenditure_item_id = xda.source_distribution_id_num_1(+)
   AND pcdl.line_num = xda.source_distribution_id_num_2(+)
   AND (   xda.rounding_class_code NOT LIKE '%CLEARING'
        OR xda.rounding_class_code IS NULL
       )
   AND xda.ae_header_id = xal.ae_header_id(+)
   AND xda.ae_line_num = xal.ae_line_num(+)
   AND gcc.code_combination_id =
                   NVL (apd.dist_code_combination_id, xal.code_combination_id)
   AND pec.expenditure_category_id = pet.expenditure_category_id
   AND ppn.person_id = peia.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 peia.expenditure_item_id = prd.transaction_id(+)
   AND pe.expenditure_item_id = peia.expenditure_item_id
   AND ppab.project_status_code = 'ACTIVE'
   AND EXISTS (SELECT 1
                 FROM okc_k_headers_all_b okh
                WHERE okh.ID = pe.contract_id AND okh.sts_code = 'ACTIVE')
   AND NVL (net_zero_adjustment_flag, 'N') = 'N'
   AND peia.revenue_recognized_flag IN ('P', 'U')
   AND peia.billable_flag = 'Y'
   AND pe.request_id IN (
          SELECT MAX (pe1.request_id)
            FROM pjb_errors pe1
           WHERE pe1.expenditure_item_id = pe.expenditure_item_id
             AND pe1.erroring_process = 'REVENUE_GEN')