Monday 24 September 2018

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

5 comments:

  1. I am not find pjf tables in R12.2.6 version. Is it correct query for R12

    ReplyDelete
  2. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad
    Oracle Fusion HCM Training In Hyderabad

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Oracle Fusion SCM Online Training
    Oracle Integration Cloud Service Online Training

    ReplyDelete