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’)
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’)
Recent Posts