Oralce fusion Query to Get Project Details for Subledger Tables

 PA - project revenue

exception
querySELECT 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')