Introduction
This Post illustrates the steps required to Project Accounting Labor Revenue in fusion Application
Script to Project Accounting Labor Revenue in fusion Application
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’)
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
