Introduction
This Post illustrates the steps required to get Project Unbilled Balances in Fusion Application.
.Script to Project Unbilled Balances in Fusion Application.
WITH
FUNCTION get_rev_todate_amt (
p_contract_id IN NUMBER,
p_contract_line_id IN NUMBER,
P_AS_OF_DATE IN DATE
)
RETURN NUMBER
IS
ln_amount NUMBER;
BEGIN
SELECT SUM(PRD.cont_curr_revenue_amt) cont_curr_revenue_amt
INTO ln_amount
FROM pjb_rev_distributions PRD
WHERE PRD.gl_date <= P_AS_OF_DATE
AND PRD.contract_id = p_contract_id
AND PRD.contract_line_id = p_contract_line_id;
RETURN ln_amount;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
SELECT PPA.segment1 project_number
,OCTV.name contracty_type
,PPA.name project_name
,PPA.description
,HOU.name organization_name
,PPA.project_currency_code
,OKH.contract_number
,OKL.line_number
,TO_CHAR (PPA.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) completion_date
,ROUND(NVL((INV.cont_curr_billed_amt), 0), 2) project_to_date_Invoiced_sum
,ROUND(NVL(get_rev_todate_amt(OKH.id, OKL.id, :P_AS_OF_DATE), 0), 2) project_to_date_revenue_sum
,ROUND(NVL(get_rev_todate_amt(OKH.id, OKL.id, :P_AS_OF_DATE), 0), 2) – ROUND(NVL((INV.cont_curr_billed_amt), 0), 2) unbilled_balance
,PBC.billing_cycle_name
,TO_CHAR (INV.last_date_invoiced, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) last_date_invoiced
,OKH.id contract_id
,OKL.id contract_line_id
FROM pjf_projects_all_vl PPA
,pjb_cntrct_proj_links PCPL
,okc_k_lines_b OKL
,okc_k_headers_all_b OKH
,okc_contract_types_vl OCTV
,pjf_project_types_vl PPT
,hr_all_organization_units HOU
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
,PILD.contract_id, PILD.contract_line_id
FROM pjb_inv_line_dists PILD
,pjb_invoice_headers PIH
WHERE 1=1
AND PILD.invoice_id = PIH.invoice_id
AND PIH.transfer_status_code = ‘A’
AND PIH.gl_date <= :P_AS_OF_DATE
GROUP BY PILD.contract_id, PILD.contract_line_id
) INV
,pjb_bill_plans_vl PBP
,pjf_billing_cycles_vl PBC
WHERE 1=1
and PPA.project_id = PCPL.project_id
AND PCPL.version_type = ‘C’ — Current
AND PCPL.contract_line_id = OKL.id
AND PCPL.major_version = OKL.major_version
AND OKL.chr_id = OKH.id
AND OKL.major_version = OKH.major_version
AND OKL.version_type = ‘C’ — Current
AND OKH.version_type = ‘C’ — Current
AND OKH.contract_type_id = OCTV.contract_type_id
AND PPA.project_type_id = PPT.project_type_id
AND HOU.organization_id = PPA.carrying_out_organization_id
AND OKH.sts_code <> ‘DRAFT’
AND PPT.project_type NOT IN (‘INTERCOMPANY’, ‘Intercompany’)
AND INV.contract_id (+) = OKH.id
and INV.contract_line_id (+) = OKL.id
AND OKL.bill_plan_id = PBP.bill_plan_id (+)
AND OKL.major_version = PBP.major_version (+)
AND PBP.billing_cycle_id = PBC.billing_cycle_id (+)
Summary
This Post described the script for Project Unbilled Balances in Fusion Application.
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
