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