PA – Project Revenue exception - Oracle Consulting Services | USA | 99% Customer Retention

Introduction

This Post illustrates the steps required to project Revenue Exception in Fusion Application.

Script to PA – Project Revenue exception

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