Monday 4 March 2019

Fusion Query to find Revenue Exceptions details for Expenditures


SELECT   x.ei_latest_gl_period_name, x.r_company,
         x.ei_revenue_distributed_flag, x.ei_project_name,
         x.ei_project_number, x.ei_expenditure_type,
         x.ei_expenditure_category, x.ei_employee_name,
         x.ei_expenditure_item_date, x.ei_receipt_currency_code,
         x.ei_project_currency_code, x.ei_expenditure_org_name,
         x.ei_prvdr_org_name, x.project_owning_org,
         x.billed_flag, x.task_number, x.rejection_code,
         transaction_number, billable_flag, invoice_status, revenue_status,
         employee_job, unit_of_measure,
         SUM (x.ei_project_raw_revenue) ei_project_raw_revenue,
         (SELECT SUM (prd.project_curr_revenue_amt)
            FROM pjb_rev_distributions prd
           WHERE prd.transaction_id =
                          x.transaction_number
             AND prd.line_num_reversed IS NULL
             AND prd.reversed_flag IS NULL) r_project_revenue_amount,
         SUM (x.ei_quantity) ei_quantity,
         (SELECT   prd.bill_rate
              FROM pjb_rev_distributions prd
             WHERE prd.transaction_id =
                                      x.transaction_number
               AND prd.line_num_reversed IS NULL
               AND prd.reversed_flag IS NULL
          GROUP BY prd.bill_rate) ei_bill_rate,
         contract_type_name, contract_number, contract_rev_hold_flag,
         revenue_exception_flag,contract_line_num
    FROM (
    --1. Expendituere 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,
                 (SELECT ppn.full_name
                    FROM per_person_names_f ppn
                   WHERE 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') 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,
                 pcdl.quantity ei_quantity,
                 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,
                 pjbp.on_hold_flag contract_rev_hold_flag,
                 okct.NAME contract_type_name, okh.contract_number,
                 peia.revenue_exception_flag,
                 okl.line_number contract_line_num
            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,
                 okc_k_headers_all_b okh,
                 okc_k_lines_b okl,
                 pjb_bill_plans_vl pjbp,
                 okc_contract_types_tl okct,
                 xla_distribution_links xda,
                 xla_ae_lines xal
           WHERE pet.expenditure_type_id = peia.expenditure_type_id
             AND peia.revenue_exception_flag IN ('E', 'W')
             AND pcdl.acct_source_code <> 'AP_INV'
             AND pe.contract_id = okh.ID
             AND okh.version_type = 'C'
             AND pe.contract_line_id = okl.ID
             AND okl.version_type = 'C'
             AND okl.revenue_plan_id = pjbp.bill_plan_id
             AND pjbp.version_type = 'C'
             AND okh.contract_type_id = okct.contract_type_id
             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 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 NVL (NVL (pcdl.raw_cost_dr_ccid, pcdl.raw_cost_cr_ccid),
                      xal.code_combination_id
                     ) = gcc.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 pe.expenditure_item_id = peia.expenditure_item_id
             AND ppab.project_status_code = 'ACTIVE'
             AND okh.sts_code = 'ACTIVE'
             AND (   peia.revenue_recognized_flag IN ('P', 'U')
                  OR (    revenue_exception_flag = 'W'
                      AND peia.revenue_recognized_flag = 'F'
                     )
                 )
             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
          --1. Expendituere coming from AP 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,
                 NVL
                    ((SELECT ppn.full_name
                        FROM per_person_names_f ppn
                       WHERE 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'),
                     (SELECT party_name
                        FROM hz_parties hp1, ap_invoices_all api
                       WHERE hp1.party_id = api.party_id
                         AND api.invoice_id = apd.invoice_id)
                    ) 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,
                 pcdl.quantity ei_quantity,
                 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,
                 pjbp.on_hold_flag contract_rev_hold_flag,
                 okct.NAME contract_type_name, okh.contract_number,
                 peia.revenue_exception_flag
                 ,okl.line_number contract_line_num
            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,
                 pjb_errors pe,
                 okc_k_headers_all_b okh,
                 okc_k_lines_b okl,
                 pjb_bill_plans_vl pjbp,
                 okc_contract_types_tl okct,
                 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 IN ('E', 'W')
             AND apd.invoice_distribution_id = peia.original_dist_id
             AND pe.contract_id = okh.ID
             AND okh.version_type = 'C'
             AND pe.contract_line_id = okl.ID
             AND okl.version_type = 'C'
             AND okl.revenue_plan_id = pjbp.bill_plan_id
             AND pjbp.version_type = 'C'
             AND okh.contract_type_id = okct.contract_type_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 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 NVL (apd.dist_code_combination_id, xal.code_combination_id) =
                                                                           gcc.code_combination_id(+)
             AND pec.expenditure_category_id = pet.expenditure_category_id
             AND pe.expenditure_item_id = peia.expenditure_item_id
             AND ppab.project_status_code = 'ACTIVE'
             AND okh.sts_code = 'ACTIVE'
             AND (   peia.revenue_recognized_flag IN ('P', 'U')
                  OR (    revenue_exception_flag = 'W'
                      AND peia.revenue_recognized_flag = 'F'
                     )
                 )
             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')) x
GROUP BY x.ei_latest_gl_period_name,
         x.r_company,
         x.ei_revenue_distributed_flag,
         x.ei_project_name,
         x.ei_project_number,
         x.ei_expenditure_type,
         x.ei_expenditure_category,
         x.ei_employee_name,
         x.ei_expenditure_item_date,
         x.ei_receipt_currency_code,
         x.ei_project_currency_code,
         x.ei_expenditure_org_name,
         x.ei_prvdr_org_name,
         x.project_owning_org,
         x.billed_flag,
         x.task_number,
         x.rejection_code,
         transaction_number,
         billable_flag,
         invoice_status,
         revenue_status,
         employee_job,
         unit_of_measure,
         contract_rev_hold_flag,
         contract_type_name,
         contract_number,
         revenue_exception_flag
         ,contract_line_num

No comments:

Post a Comment