SELECT pcdl.prvdr_gl_period_name gl_period,
trx_org.NAME expenditure_organization,
prb.project_id,
NVL (prb.segment1, 'N/A') project_number,
NVL (prl.NAME, 'N/A') project_name,
NULL project_type_class_code,
ppn.full_name employee_vendor,
papf.person_number employee_number,
ptv.task_name,
pec.expenditure_category_name expenditure_category,
pet.expenditure_type_name expenditure_type,
pcdl.prvdr_gl_date week_ending,
pei.expenditure_item_date expenditure_date,
pcdl.denom_currency_code entered_currency_code,
NVL (pcdl.denom_raw_cost, 0) entered_amount,
NVL (pcdl.acct_raw_cost, 0) accounted_amount,
NVL (pcdl.acct_exchange_rate, 1) accounted_exchange_rate,
NVL (pcdl.acct_rate_type, 'FIXED') accounted_exchange_type,
pcdl.prvdr_gl_date conversion_date,
gl.NAME set_of_books_name,
gl.currency_code book_currency_code,
pec1.expenditure_comment comments,
pei.orig_transaction_reference ap_invoice_number,
'PA' subledger_name,
FROM pjc_cost_dist_lines_all pcdl,
pjc_exp_items_all pei,
pjf_tasks_v ptv,
pjf_projects_all_b prb,
pjf_projects_all_tl prl,
pjc_exp_comments pec1,
pjf_exp_types_vl pet,
pjf_exp_categories_tl pec,
fun_names_business_units_v fnbu,
gl_ledgers gl,
gl_code_combinations gcc,
XLA_DISTRIBUTION_LINKS XDA,
xla_ae_lines xal,
hr_all_organization_units_tl trx_org,
per_all_people_f_v papf,
per_person_names_f ppn
WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
AND ptv.project_id = pei.project_id
AND ptv.task_id = pei.task_id
AND pei.project_id = prb.project_id
AND pei.project_id = prl.project_id
AND pei.expenditure_item_id = pec1.expenditure_item_id(+)
AND pei.expenditure_type_id = pet.expenditure_type_id
AND pec.expenditure_category_id = pet.expenditure_category_id
AND fnbu.bu_id = pei.org_id
AND fnbu.primary_ledger_id = gl.ledger_id
AND pcdl.expenditure_item_id = XDA.SOURCE_DISTRIBUTION_ID_NUM_1(+)
AND xda.ae_line_num(+) = 2
AND xda.ae_header_id = xal.ae_header_id(+)
AND xda.ae_line_num = xal.ae_line_num(+)
AND pcdl.line_num_reversed IS NULL
AND pcdl.reversed_flag IS NULL
AND pcdl.RAW_LINE_NUM_REVERSED IS NULL
AND ppn.NAME_TYPE = 'GLOBAL'
AND NVL (pei.override_to_organization_id, pei.incurred_by_organization_id) = trx_org.organization_id
AND pec.expenditure_category_name IN ('Expenses', 'Material')
AND pei.incurred_by_person_id = papf.person_id
AND pei.incurred_by_person_id = ppn.person_id
AND SYSDATE BETWEEN NVL (papf.effective_start_date, SYSDATE - 1) AND NVL (papf.effective_end_date, SYSDATE + 1)
AND SYSDATE BETWEEN NVL (ppn.effective_start_date, SYSDATE - 1) AND NVL (ppn.effective_end_date, SYSDATE + 1)
trx_org.NAME expenditure_organization,
prb.project_id,
NVL (prb.segment1, 'N/A') project_number,
NVL (prl.NAME, 'N/A') project_name,
NULL project_type_class_code,
ppn.full_name employee_vendor,
papf.person_number employee_number,
ptv.task_name,
pec.expenditure_category_name expenditure_category,
pet.expenditure_type_name expenditure_type,
pcdl.prvdr_gl_date week_ending,
pei.expenditure_item_date expenditure_date,
pcdl.denom_currency_code entered_currency_code,
NVL (pcdl.denom_raw_cost, 0) entered_amount,
NVL (pcdl.acct_raw_cost, 0) accounted_amount,
NVL (pcdl.acct_exchange_rate, 1) accounted_exchange_rate,
NVL (pcdl.acct_rate_type, 'FIXED') accounted_exchange_type,
pcdl.prvdr_gl_date conversion_date,
gl.NAME set_of_books_name,
gl.currency_code book_currency_code,
pec1.expenditure_comment comments,
pei.orig_transaction_reference ap_invoice_number,
'PA' subledger_name,
FROM pjc_cost_dist_lines_all pcdl,
pjc_exp_items_all pei,
pjf_tasks_v ptv,
pjf_projects_all_b prb,
pjf_projects_all_tl prl,
pjc_exp_comments pec1,
pjf_exp_types_vl pet,
pjf_exp_categories_tl pec,
fun_names_business_units_v fnbu,
gl_ledgers gl,
gl_code_combinations gcc,
XLA_DISTRIBUTION_LINKS XDA,
xla_ae_lines xal,
hr_all_organization_units_tl trx_org,
per_all_people_f_v papf,
per_person_names_f ppn
WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
AND ptv.project_id = pei.project_id
AND ptv.task_id = pei.task_id
AND pei.project_id = prb.project_id
AND pei.project_id = prl.project_id
AND pei.expenditure_item_id = pec1.expenditure_item_id(+)
AND pei.expenditure_type_id = pet.expenditure_type_id
AND pec.expenditure_category_id = pet.expenditure_category_id
AND fnbu.bu_id = pei.org_id
AND fnbu.primary_ledger_id = gl.ledger_id
AND pcdl.expenditure_item_id = XDA.SOURCE_DISTRIBUTION_ID_NUM_1(+)
AND xda.ae_line_num(+) = 2
AND xda.ae_header_id = xal.ae_header_id(+)
AND xda.ae_line_num = xal.ae_line_num(+)
AND pcdl.line_num_reversed IS NULL
AND pcdl.reversed_flag IS NULL
AND pcdl.RAW_LINE_NUM_REVERSED IS NULL
AND ppn.NAME_TYPE = 'GLOBAL'
AND NVL (pei.override_to_organization_id, pei.incurred_by_organization_id) = trx_org.organization_id
AND pec.expenditure_category_name IN ('Expenses', 'Material')
AND pei.incurred_by_person_id = papf.person_id
AND pei.incurred_by_person_id = ppn.person_id
AND SYSDATE BETWEEN NVL (papf.effective_start_date, SYSDATE - 1) AND NVL (papf.effective_end_date, SYSDATE + 1)
AND SYSDATE BETWEEN NVL (ppn.effective_start_date, SYSDATE - 1) AND NVL (ppn.effective_end_date, SYSDATE + 1)
Very nice thanks for your content, very informative...
ReplyDeleteSoft Online Training offers
Oracle Fusion SCM Online Training
Oracle Fusion HCM Online Training
Oracle Fusion Financials Online Training
Oracle Fusion Technical Online Training
Oracle Fusion PPM Online Training
Oracle Integration Cloud Online Training