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')
I am not find pjf tables in R12.2.6 version. Is it correct query for R12
ReplyDeleteGood Blog,thanks for shariong this informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
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.
ReplyDeleteOracle 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
This comment has been removed by the author.
ReplyDeleteI 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.
ReplyDeleteOracle Fusion SCM Online Training
Oracle Integration Cloud Service Online Training