SELECT prd.org_id employee_cost_org_id, prd.gl_period_name gl_period,
NVL (pei.override_to_organization_id,
pei.incurred_by_organization_id
) expenditure_org_id,
trx_org.NAME expenditure_org, fnbu.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,
NVL (prb.project_currency_code,
pei.project_currency_code
) project_currency_code,
NVL (prd.revenue_curr_amt, 0) project_revenue_amount,
NVL (pei.projfunc_currency_code,
prd.ledger_currency_code
) projfunc_currency_code,
NVL (prd.ledger_curr_revenue_amt, 0) projfunc_revenue_amount, 0 amount,
gcc.segment1 company, gcc.segment2 ACCOUNT, gcc.segment3 department,
gcc.segment4 region, gcc.segment5 FUNCTION, gcc.segment6 intercompany,
NULL sub_cost_currency_code, 0 sub_cost_rate, 0 subcontractor_cost,
prd.trns_currency_code bill_transaction_currency,
prd.trns_curr_revenue_amt bill_transaction_currency_rev,
DECODE
('USD',
prd.revenue_currency_code, prd.revenue_curr_amt,
prd.ledger_currency_code, prd.ledger_curr_revenue_amt,
prd.contract_currency_code, prd.cont_curr_revenue_amt,
prd.project_currency_code, prd.project_curr_revenue_amt,
prd.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 = prd.revenue_currency_code
AND gdr.conversion_date = prd.gl_date),
1
)
) 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),
'1', 'Y',
'N'
) billed_flag,
prb.attribute4, pei.burden_cost_rate,
(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,
prd.org_id, prd.ledger_curr_revenue_amt functrevenu,
(SELECT hou1.NAME
FROM okc_k_headers_all_b okh,
hr_all_organization_units hou1
WHERE okh.owning_org_id = hou1.organization_id
AND (okh.ID, okh.major_version) IN (
SELECT okh1.ID,
MAX (okh1.major_version) major_version
FROM okc_k_headers_all_b okh1
WHERE okh1.ID = prd.contract_id
GROUP BY ID)) contract_org,
(SELECT sts_code
FROM okc_k_headers_all_b okh
WHERE (okh.ID, okh.major_version) IN (
SELECT okh1.ID,
MAX (okh1.major_version)
major_version
FROM okc_k_headers_all_b okh1
WHERE okh1.ID = prd.contract_id
GROUP BY ID)) contract_status,
(SELECT contract_number
FROM okc_k_headers_all_b okh
WHERE (okh.ID, okh.major_version) IN (
SELECT okh1.ID,
MAX (okh1.major_version)
major_version
FROM okc_k_headers_all_b okh1
WHERE okh1.ID = prd.contract_id
GROUP BY ID)) contract_number,
(SELECT oct.NAME
FROM okc_k_headers_all_b okh,
okc_contract_types_vl oct
WHERE okh.contract_type_id = oct.contract_type_id
AND (okh.ID, okh.major_version) IN (
SELECT okh1.ID,
MAX (okh1.major_version) major_version
FROM okc_k_headers_all_b okh1
WHERE okh1.ID = prd.contract_id
GROUP BY ID)) contract_type,
prd.contract_currency_code, prd.cont_curr_revenue_amt,
prd.revenue_currency_code, prd.ledger_currency_code,
project_curr_revenue_amt, prd.revenue_curr_amt,
prd.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) job_name,
pei.expenditure_type_id, prd.bill_rate
FROM pjc_exp_items_all pei,
pjf_tasks_v ptv,
pjf_projects_all_b prb,
pjf_projects_all_tl prl,
pjc_exp_comments pec,
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,
pjb_rev_distributions prd,
per_person_names_f ppn,
per_all_people_f papf
WHERE 1 = 1
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 = pec.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 = prb.org_id
AND fnbu.primary_ledger_id = gl.ledger_id
AND prd.sla_event_id = xda.event_id
AND prd.rev_distribution_id = xda.source_distribution_id_num_1
AND xda.ae_line_num = 2
AND xal.ae_header_id = xda.ae_header_id
AND xal.ae_line_num = xda.ae_line_num
AND gcc.code_combination_id = xal.code_combination_id
AND trx_org.organization_id =
NVL (pei.override_to_organization_id,
pei.incurred_by_organization_id
)
AND prd.line_num_reversed IS NULL
AND prd.reversed_flag IS NULL
AND prd.transaction_id = pei.expenditure_item_id
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 prd.gl_period_name IN (:p_period_name)
AND pei.revenue_recognized_flag <> 'U'
AND pec.expenditure_category_name IN ('Labor')
NVL (pei.override_to_organization_id,
pei.incurred_by_organization_id
) expenditure_org_id,
trx_org.NAME expenditure_org, fnbu.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,
NVL (prb.project_currency_code,
pei.project_currency_code
) project_currency_code,
NVL (prd.revenue_curr_amt, 0) project_revenue_amount,
NVL (pei.projfunc_currency_code,
prd.ledger_currency_code
) projfunc_currency_code,
NVL (prd.ledger_curr_revenue_amt, 0) projfunc_revenue_amount, 0 amount,
gcc.segment1 company, gcc.segment2 ACCOUNT, gcc.segment3 department,
gcc.segment4 region, gcc.segment5 FUNCTION, gcc.segment6 intercompany,
NULL sub_cost_currency_code, 0 sub_cost_rate, 0 subcontractor_cost,
prd.trns_currency_code bill_transaction_currency,
prd.trns_curr_revenue_amt bill_transaction_currency_rev,
DECODE
('USD',
prd.revenue_currency_code, prd.revenue_curr_amt,
prd.ledger_currency_code, prd.ledger_curr_revenue_amt,
prd.contract_currency_code, prd.cont_curr_revenue_amt,
prd.project_currency_code, prd.project_curr_revenue_amt,
prd.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 = prd.revenue_currency_code
AND gdr.conversion_date = prd.gl_date),
1
)
) 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),
'1', 'Y',
'N'
) billed_flag,
prb.attribute4, pei.burden_cost_rate,
(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,
prd.org_id, prd.ledger_curr_revenue_amt functrevenu,
(SELECT hou1.NAME
FROM okc_k_headers_all_b okh,
hr_all_organization_units hou1
WHERE okh.owning_org_id = hou1.organization_id
AND (okh.ID, okh.major_version) IN (
SELECT okh1.ID,
MAX (okh1.major_version) major_version
FROM okc_k_headers_all_b okh1
WHERE okh1.ID = prd.contract_id
GROUP BY ID)) contract_org,
(SELECT sts_code
FROM okc_k_headers_all_b okh
WHERE (okh.ID, okh.major_version) IN (
SELECT okh1.ID,
MAX (okh1.major_version)
major_version
FROM okc_k_headers_all_b okh1
WHERE okh1.ID = prd.contract_id
GROUP BY ID)) contract_status,
(SELECT contract_number
FROM okc_k_headers_all_b okh
WHERE (okh.ID, okh.major_version) IN (
SELECT okh1.ID,
MAX (okh1.major_version)
major_version
FROM okc_k_headers_all_b okh1
WHERE okh1.ID = prd.contract_id
GROUP BY ID)) contract_number,
(SELECT oct.NAME
FROM okc_k_headers_all_b okh,
okc_contract_types_vl oct
WHERE okh.contract_type_id = oct.contract_type_id
AND (okh.ID, okh.major_version) IN (
SELECT okh1.ID,
MAX (okh1.major_version) major_version
FROM okc_k_headers_all_b okh1
WHERE okh1.ID = prd.contract_id
GROUP BY ID)) contract_type,
prd.contract_currency_code, prd.cont_curr_revenue_amt,
prd.revenue_currency_code, prd.ledger_currency_code,
project_curr_revenue_amt, prd.revenue_curr_amt,
prd.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) job_name,
pei.expenditure_type_id, prd.bill_rate
FROM pjc_exp_items_all pei,
pjf_tasks_v ptv,
pjf_projects_all_b prb,
pjf_projects_all_tl prl,
pjc_exp_comments pec,
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,
pjb_rev_distributions prd,
per_person_names_f ppn,
per_all_people_f papf
WHERE 1 = 1
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 = pec.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 = prb.org_id
AND fnbu.primary_ledger_id = gl.ledger_id
AND prd.sla_event_id = xda.event_id
AND prd.rev_distribution_id = xda.source_distribution_id_num_1
AND xda.ae_line_num = 2
AND xal.ae_header_id = xda.ae_header_id
AND xal.ae_line_num = xda.ae_line_num
AND gcc.code_combination_id = xal.code_combination_id
AND trx_org.organization_id =
NVL (pei.override_to_organization_id,
pei.incurred_by_organization_id
)
AND prd.line_num_reversed IS NULL
AND prd.reversed_flag IS NULL
AND prd.transaction_id = pei.expenditure_item_id
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 prd.gl_period_name IN (:p_period_name)
AND pei.revenue_recognized_flag <> 'U'
AND pec.expenditure_category_name IN ('Labor')
Good 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
I 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