SELECT pcdl.org_id employee_cost_org_id, pcdl.prvdr_gl_period_name gl_period,
pcdl.prvdr_pa_period_name,
NVL (pei.override_to_organization_id,
pei.incurred_by_organization_id
) expenditure_org_id,
trx_org.NAME expenditure_org, fnbu.bu_name AS exp_bu_name,
prb.carrying_out_organization_id project_owning_org_id,
(SELECT NAME
FROM hr_organization_units
WHERE organization_id =
prb.carrying_out_organization_id)
project_owning_org,
prb.project_id, prb.segment1 project_number, prl.NAME project_name,
(SELECT s.project_status_name
FROM pjf_project_statuses_tl s
WHERE prb.project_status_code = s.project_status_code)
project_status,
ptv.task_id, ptv.task_name, NVL (ptv.billable_flag, 'N') billable_task,
NVL (pei.billable_flag, 'N') billable_expenditure_item,
pei.expenditure_item_id, pei.expenditure_item_date,
pei.expenditure_ending_date,
pet.expenditure_type_name expenditure_type,
pec.expenditure_category_name expenditure_category,
pei.incurred_by_person_id, ppn.full_name employee_name,
papf.person_number employee_number, rev_det.usd_revenue_amount,
NVL (pei.project_currency_code,
prb.project_currency_code
) project_currency_code,
rev_det.revenue_curr_amt project_revenue_amount,
NVL (pei.projfunc_currency_code,
prb.projfunc_currency_code
) projfunc_currency_code,
rev_det.ledger_curr_revenue_amt projfunc_revenue_amount,
gcc.segment1 company, gcc.segment2 ACCOUNT, gcc.segment3 department,
gcc.segment4 region, gcc.segment5 FUNCTION, gcc.segment6 intercompany,
pei.bill_trans_currency_code bill_transaction_currency,
rev_det.trns_curr_revenue_amt bill_transaction_currency_rev,
rev_det.usd_daily_revenue_amount, pei.bill_hold_flag,
DECODE ((SELECT '1'
FROM pjb_bill_trxs a
WHERE pei.expenditure_item_id = a.transaction_id
AND NVL (inv_curr_billed_amt, 0) <> 0
AND ROWNUM = 1),
'1', 'Y',
'N'
) billed_flag,
prb.attribute4,
(SELECT project_type
FROM pjf_project_types_tl ppt
WHERE ppt.project_type_id = prb.project_type_id) project_type,
(SELECT class_code
FROM pjf_project_classes prc, pjf_class_codes_tl pct
WHERE prc.class_code_id = pct.class_code_id
AND prc.project_id = prb.project_id) region_code,
pcdl.org_id, rev_det.ledger_curr_revenue_amt functrevenu,
rev_det.contract_org, rev_det.contract_status, rev_det.contract_number,
rev_det.contract_type, rev_det.contract_currency_code,
rev_det.cont_curr_revenue_amt, rev_det.revenue_currency_code,
rev_det.ledger_currency_code ledger_currency_code,
rev_det.project_curr_revenue_amt, rev_det.revenue_curr_amt,
rev_det.ledger_curr_revenue_amt, pei.revenue_hold_flag,
pei.person_type,
(SELECT NAME
FROM per_jobs_f_vl pjb
WHERE pjb.job_id = pei.person_job_id
AND SYSDATE BETWEEN NVL (pjb.effective_start_date, SYSDATE - 1)
AND NVL (pjb.effective_end_date, SYSDATE + 1)
AND active_status = 'A') job_name,
pei.expenditure_type_id, rev_det.bill_rate,
pei.project_raw_cost project_currency_amount, pei.quantity,
pei.org_id recvr_org_id, pei.burden_cost_rate, rev_det.rev_bu_name,
(SELECT hourly_cost_rate
FROM pjf_comp_details_all pcd
WHERE pei.person_job_id = pcd.person_job_id(+)
AND pcd.person_id = pei.incurred_by_person_id
AND pei.incurred_by_organization_id = pcd.organization_id(+)
AND SYSDATE BETWEEN NVL (pcd.start_date_active, SYSDATE - 1)
AND NVL (pcd.end_date_active, SYSDATE + 1))
subcontractor_cost_rate,
(SELECT fnbu.bu_name
FROM fun_names_business_units_v fnbu
WHERE fnbu.bu_id = prb.org_id) project_bu_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,
hr_all_organization_units_tl trx_org,
gl_ledgers gl,
gl_code_combinations gcc,
per_person_names_f ppn,
per_all_people_f papf,
(SELECT xda2.event_id, xda2.source_distribution_id_num_1,
xda2.rounding_class_code, xda2.source_distribution_id_num_2,
xda2.ae_line_num, xda2.ae_header_id
FROM xla_distribution_links xda2
WHERE xda2.ref_ae_line_num IS NULL
AND xda2.rounding_class_code = 'RAW_COST_CLEARING'
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links xda1
WHERE xda1.ref_ae_line_num = xda2.ae_line_num
AND xda1.ref_ae_header_id = xda2.ae_header_id)) xda,
xla_ae_lines xal,
pjf_txn_sources_vl ts,
(WITH gl_translation_rates_cte AS
(SELECT DISTINCT UPPER (r.period_name) period_name,
r.to_currency_code to_currency_code,
r.avg_rate avg_rate, r.eop_rate eop_rate,
s.currency_code functional_currency
FROM gl_translation_rates r, gl_sets_of_books s
WHERE s.set_of_books_id = r.set_of_books_id
AND r.to_currency_code = 'USD')
SELECT SUM
(DECODE
(prd1.ledger_currency_code,
'USD', NVL (prd1.ledger_curr_revenue_amt, 0),
( NVL
(gtrc.avg_rate,
NVL
((SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type =
'Corporate'
AND gdr.to_currency = 'USD'
AND gdr.from_currency =
prd1.revenue_currency_code
AND gdr.conversion_date =
prd1.gl_date),
1
)
)
* NVL (prd1.ledger_curr_revenue_amt, 0)
)
)
) usd_revenue_amount,
SUM
(DECODE ('USD',
prd1.revenue_currency_code, prd1.revenue_curr_amt,
prd1.ledger_currency_code, prd1.ledger_curr_revenue_amt,
prd1.contract_currency_code, prd1.cont_curr_revenue_amt,
prd1.project_currency_code, prd1.project_curr_revenue_amt,
prd1.revenue_curr_amt
* NVL ((SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type = 'Corporate'
AND gdr.to_currency = 'USD'
AND gdr.from_currency =
prd1.revenue_currency_code
AND gdr.conversion_date = prd1.gl_date),
1
)
)
) usd_daily_revenue_amount,
SUM (prd1.revenue_curr_amt) revenue_curr_amt,
SUM (prd1.ledger_curr_revenue_amt) ledger_curr_revenue_amt,
SUM (prd1.trns_curr_revenue_amt) trns_curr_revenue_amt,
SUM (prd1.cont_curr_revenue_amt) cont_curr_revenue_amt,
SUM (prd1.project_curr_revenue_amt) project_curr_revenue_amt,
prd1.bill_rate, prd1.revenue_currency_code,
cont_org.NAME contract_org, okh.sts_code contract_status,
okh.contract_number contract_number, oct.NAME contract_type,
prd1.contract_currency_code, prd1.trns_currency_code,
prd1.ledger_currency_code,
UPPER (prd1.gl_period_name) gl_period_name,
prd1.transaction_id,
(SELECT fnbu.bu_name
FROM fun_names_business_units_v fnbu
WHERE fnbu.bu_id = prd1.org_id) rev_bu_name
FROM pjb_rev_distributions prd1,
gl_translation_rates_cte gtrc,
okc_k_headers_all_b okh,
hr_all_organization_units cont_org,
okc_contract_types_vl oct
WHERE 1 = 1
AND okh.ID = prd1.contract_id
AND okh.version_type = 'C'
AND okh.owning_org_id = cont_org.organization_id
AND okh.contract_type_id = oct.contract_type_id
AND prd1.ledger_currency_code = gtrc.functional_currency(+)
AND UPPER (prd1.gl_period_name) = gtrc.period_name(+)
GROUP BY prd1.trns_currency_code,
cont_org.NAME,
okh.sts_code,
okh.contract_number,
oct.NAME,
prd1.contract_currency_code,
prd1.revenue_currency_code,
prd1.bill_rate,
UPPER (prd1.gl_period_name),
prd1.transaction_id,
prd1.ledger_currency_code,
prd1.org_id) rev_det
WHERE 1 = 1
AND pei.expenditure_item_id = rev_det.transaction_id(+)
AND UPPER (pcdl.prvdr_gl_period_name) = rev_det.gl_period_name(+)
AND pei.transaction_source_id = ts.transaction_source_id
AND pcdl.expenditure_item_id = pei.expenditure_item_id
AND pei.project_id = ptv.project_id(+)
AND pei.task_id = ptv.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.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.ae_header_id = xal.ae_header_id(+)
AND xda.ae_line_num = xal.ae_line_num(+)
AND gcc.code_combination_id =
NVL (NVL (xal.code_combination_id,
NVL (pcdl.raw_cost_dr_ccid, pcdl.raw_cost_cr_ccid)
),
(SELECT pas.default_code_comb_id
FROM per_all_assignments_m pas
WHERE SYSDATE BETWEEN NVL (pas.effective_start_date,
SYSDATE - 1
)
AND NVL (pas.effective_end_date,
SYSDATE + 1
)
AND pas.person_id = pei.incurred_by_person_id
AND pas.assignment_id = pei.hcm_assignment_id)
)
AND trx_org.organization_id =
NVL (pei.override_to_organization_id,
pei.incurred_by_organization_id
)
AND pcdl.line_num_reversed IS NULL
AND pcdl.reversed_flag IS NULL
AND ppn.person_id = pei.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 papf.person_id(+) = pei.incurred_by_person_id
AND SYSDATE BETWEEN NVL (papf.effective_start_date, SYSDATE - 1)
AND NVL (papf.effective_end_date, SYSDATE + 1)
AND UPPER (pcdl.prvdr_gl_period_name) IN (:p_period_name)
AND pec.expenditure_category_name IN ('Labor')
pcdl.prvdr_pa_period_name,
NVL (pei.override_to_organization_id,
pei.incurred_by_organization_id
) expenditure_org_id,
trx_org.NAME expenditure_org, fnbu.bu_name AS exp_bu_name,
prb.carrying_out_organization_id project_owning_org_id,
(SELECT NAME
FROM hr_organization_units
WHERE organization_id =
prb.carrying_out_organization_id)
project_owning_org,
prb.project_id, prb.segment1 project_number, prl.NAME project_name,
(SELECT s.project_status_name
FROM pjf_project_statuses_tl s
WHERE prb.project_status_code = s.project_status_code)
project_status,
ptv.task_id, ptv.task_name, NVL (ptv.billable_flag, 'N') billable_task,
NVL (pei.billable_flag, 'N') billable_expenditure_item,
pei.expenditure_item_id, pei.expenditure_item_date,
pei.expenditure_ending_date,
pet.expenditure_type_name expenditure_type,
pec.expenditure_category_name expenditure_category,
pei.incurred_by_person_id, ppn.full_name employee_name,
papf.person_number employee_number, rev_det.usd_revenue_amount,
NVL (pei.project_currency_code,
prb.project_currency_code
) project_currency_code,
rev_det.revenue_curr_amt project_revenue_amount,
NVL (pei.projfunc_currency_code,
prb.projfunc_currency_code
) projfunc_currency_code,
rev_det.ledger_curr_revenue_amt projfunc_revenue_amount,
gcc.segment1 company, gcc.segment2 ACCOUNT, gcc.segment3 department,
gcc.segment4 region, gcc.segment5 FUNCTION, gcc.segment6 intercompany,
pei.bill_trans_currency_code bill_transaction_currency,
rev_det.trns_curr_revenue_amt bill_transaction_currency_rev,
rev_det.usd_daily_revenue_amount, pei.bill_hold_flag,
DECODE ((SELECT '1'
FROM pjb_bill_trxs a
WHERE pei.expenditure_item_id = a.transaction_id
AND NVL (inv_curr_billed_amt, 0) <> 0
AND ROWNUM = 1),
'1', 'Y',
'N'
) billed_flag,
prb.attribute4,
(SELECT project_type
FROM pjf_project_types_tl ppt
WHERE ppt.project_type_id = prb.project_type_id) project_type,
(SELECT class_code
FROM pjf_project_classes prc, pjf_class_codes_tl pct
WHERE prc.class_code_id = pct.class_code_id
AND prc.project_id = prb.project_id) region_code,
pcdl.org_id, rev_det.ledger_curr_revenue_amt functrevenu,
rev_det.contract_org, rev_det.contract_status, rev_det.contract_number,
rev_det.contract_type, rev_det.contract_currency_code,
rev_det.cont_curr_revenue_amt, rev_det.revenue_currency_code,
rev_det.ledger_currency_code ledger_currency_code,
rev_det.project_curr_revenue_amt, rev_det.revenue_curr_amt,
rev_det.ledger_curr_revenue_amt, pei.revenue_hold_flag,
pei.person_type,
(SELECT NAME
FROM per_jobs_f_vl pjb
WHERE pjb.job_id = pei.person_job_id
AND SYSDATE BETWEEN NVL (pjb.effective_start_date, SYSDATE - 1)
AND NVL (pjb.effective_end_date, SYSDATE + 1)
AND active_status = 'A') job_name,
pei.expenditure_type_id, rev_det.bill_rate,
pei.project_raw_cost project_currency_amount, pei.quantity,
pei.org_id recvr_org_id, pei.burden_cost_rate, rev_det.rev_bu_name,
(SELECT hourly_cost_rate
FROM pjf_comp_details_all pcd
WHERE pei.person_job_id = pcd.person_job_id(+)
AND pcd.person_id = pei.incurred_by_person_id
AND pei.incurred_by_organization_id = pcd.organization_id(+)
AND SYSDATE BETWEEN NVL (pcd.start_date_active, SYSDATE - 1)
AND NVL (pcd.end_date_active, SYSDATE + 1))
subcontractor_cost_rate,
(SELECT fnbu.bu_name
FROM fun_names_business_units_v fnbu
WHERE fnbu.bu_id = prb.org_id) project_bu_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,
hr_all_organization_units_tl trx_org,
gl_ledgers gl,
gl_code_combinations gcc,
per_person_names_f ppn,
per_all_people_f papf,
(SELECT xda2.event_id, xda2.source_distribution_id_num_1,
xda2.rounding_class_code, xda2.source_distribution_id_num_2,
xda2.ae_line_num, xda2.ae_header_id
FROM xla_distribution_links xda2
WHERE xda2.ref_ae_line_num IS NULL
AND xda2.rounding_class_code = 'RAW_COST_CLEARING'
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links xda1
WHERE xda1.ref_ae_line_num = xda2.ae_line_num
AND xda1.ref_ae_header_id = xda2.ae_header_id)) xda,
xla_ae_lines xal,
pjf_txn_sources_vl ts,
(WITH gl_translation_rates_cte AS
(SELECT DISTINCT UPPER (r.period_name) period_name,
r.to_currency_code to_currency_code,
r.avg_rate avg_rate, r.eop_rate eop_rate,
s.currency_code functional_currency
FROM gl_translation_rates r, gl_sets_of_books s
WHERE s.set_of_books_id = r.set_of_books_id
AND r.to_currency_code = 'USD')
SELECT SUM
(DECODE
(prd1.ledger_currency_code,
'USD', NVL (prd1.ledger_curr_revenue_amt, 0),
( NVL
(gtrc.avg_rate,
NVL
((SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type =
'Corporate'
AND gdr.to_currency = 'USD'
AND gdr.from_currency =
prd1.revenue_currency_code
AND gdr.conversion_date =
prd1.gl_date),
1
)
)
* NVL (prd1.ledger_curr_revenue_amt, 0)
)
)
) usd_revenue_amount,
SUM
(DECODE ('USD',
prd1.revenue_currency_code, prd1.revenue_curr_amt,
prd1.ledger_currency_code, prd1.ledger_curr_revenue_amt,
prd1.contract_currency_code, prd1.cont_curr_revenue_amt,
prd1.project_currency_code, prd1.project_curr_revenue_amt,
prd1.revenue_curr_amt
* NVL ((SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type = 'Corporate'
AND gdr.to_currency = 'USD'
AND gdr.from_currency =
prd1.revenue_currency_code
AND gdr.conversion_date = prd1.gl_date),
1
)
)
) usd_daily_revenue_amount,
SUM (prd1.revenue_curr_amt) revenue_curr_amt,
SUM (prd1.ledger_curr_revenue_amt) ledger_curr_revenue_amt,
SUM (prd1.trns_curr_revenue_amt) trns_curr_revenue_amt,
SUM (prd1.cont_curr_revenue_amt) cont_curr_revenue_amt,
SUM (prd1.project_curr_revenue_amt) project_curr_revenue_amt,
prd1.bill_rate, prd1.revenue_currency_code,
cont_org.NAME contract_org, okh.sts_code contract_status,
okh.contract_number contract_number, oct.NAME contract_type,
prd1.contract_currency_code, prd1.trns_currency_code,
prd1.ledger_currency_code,
UPPER (prd1.gl_period_name) gl_period_name,
prd1.transaction_id,
(SELECT fnbu.bu_name
FROM fun_names_business_units_v fnbu
WHERE fnbu.bu_id = prd1.org_id) rev_bu_name
FROM pjb_rev_distributions prd1,
gl_translation_rates_cte gtrc,
okc_k_headers_all_b okh,
hr_all_organization_units cont_org,
okc_contract_types_vl oct
WHERE 1 = 1
AND okh.ID = prd1.contract_id
AND okh.version_type = 'C'
AND okh.owning_org_id = cont_org.organization_id
AND okh.contract_type_id = oct.contract_type_id
AND prd1.ledger_currency_code = gtrc.functional_currency(+)
AND UPPER (prd1.gl_period_name) = gtrc.period_name(+)
GROUP BY prd1.trns_currency_code,
cont_org.NAME,
okh.sts_code,
okh.contract_number,
oct.NAME,
prd1.contract_currency_code,
prd1.revenue_currency_code,
prd1.bill_rate,
UPPER (prd1.gl_period_name),
prd1.transaction_id,
prd1.ledger_currency_code,
prd1.org_id) rev_det
WHERE 1 = 1
AND pei.expenditure_item_id = rev_det.transaction_id(+)
AND UPPER (pcdl.prvdr_gl_period_name) = rev_det.gl_period_name(+)
AND pei.transaction_source_id = ts.transaction_source_id
AND pcdl.expenditure_item_id = pei.expenditure_item_id
AND pei.project_id = ptv.project_id(+)
AND pei.task_id = ptv.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.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.ae_header_id = xal.ae_header_id(+)
AND xda.ae_line_num = xal.ae_line_num(+)
AND gcc.code_combination_id =
NVL (NVL (xal.code_combination_id,
NVL (pcdl.raw_cost_dr_ccid, pcdl.raw_cost_cr_ccid)
),
(SELECT pas.default_code_comb_id
FROM per_all_assignments_m pas
WHERE SYSDATE BETWEEN NVL (pas.effective_start_date,
SYSDATE - 1
)
AND NVL (pas.effective_end_date,
SYSDATE + 1
)
AND pas.person_id = pei.incurred_by_person_id
AND pas.assignment_id = pei.hcm_assignment_id)
)
AND trx_org.organization_id =
NVL (pei.override_to_organization_id,
pei.incurred_by_organization_id
)
AND pcdl.line_num_reversed IS NULL
AND pcdl.reversed_flag IS NULL
AND ppn.person_id = pei.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 papf.person_id(+) = pei.incurred_by_person_id
AND SYSDATE BETWEEN NVL (papf.effective_start_date, SYSDATE - 1)
AND NVL (papf.effective_end_date, SYSDATE + 1)
AND UPPER (pcdl.prvdr_gl_period_name) IN (:p_period_name)
AND pec.expenditure_category_name IN ('Labor')
No comments:
Post a Comment