Introduction
This Post illustrates the steps required to find PROJECT UNBILLED BALANCES- FUSION Application
Script to PROJECT UNBILLED BALANCES- FUSION
This report will display all project unbilled details.
SELECT ppa.project_id, ppa.segment1 project_number,
NVL ((SELECT pt.project_type
FROM pjf_project_types_tl pt, pjf_projects_all_vl p
WHERE p.project_id(+) = ppa.project_id
AND p.project_type_id = pt.project_type_id),
‘N/A’
) project_type,
ppt.NAME project_name,
ppt.description description,
(SELECT ppm.resource_source_name
FROM pjf_proj_team_members_v ppm
WHERE 1 = 1
AND project_role_id = 1
AND project_id = ppa.project_id) manager_name,
haou.NAME organization_name, pih.invoice_currency_code currency_code,
SUM (NVL (prd.revenue_curr_amt, 0)) proj_todate_revenue_amt,
SUM (NVL (ccdl.acct_amount, 0)) proj_todate_inv_amt,
SUM (NVL (pild.trns_curr_billed_amt, 0)) unbilled_amt,
pih.ra_invoice_number Open_Ar_inv,
(SELECT TO_CHAR (MIN (e.completion_date),
‘DD-MON-YYYY’,
‘NLS_DATE_LANGUAGE = american’
) next_event_date
FROM pjb_billing_events e
WHERE e.project_id = ppa.project_id
AND e.bill_trns_amount <> 0
AND (e.bill_hold_flag = ‘N’ OR e.completion_date >= SYSDATE))
next_bill_date
–:p_as_of_date
,
(SELECT TO_CHAR (MAX (i.invoice_date),
‘DD-MON-YYYY’,
‘NLS_DATE_LANGUAGE = american’
) max_proj_inv_date
FROM pjb_rev_distributions i
WHERE i.linked_project_id = ppa.project_id
AND i.invoice_date <= SYSDATE –:p_as_of_date
–AND i.invoice_status_code = ‘ACCEPTED’
GROUP BY linked_project_id) last_date_invoiced,
TO_CHAR (ppa.completion_date,
‘DD-MON-YYYY’,
‘NLS_DATE_LANGUAGE = american’
) completion_date
FROM pjc_exp_items_all pei,
pjf_exp_types_tl pet,
pjf_projects_all_b ppa,
pjf_projects_all_tl ppt,
pjf_project_statuses_tl pps,
pjf_proj_elements_b ppeb,
pjf_proj_elements_tl ppet,
pjb_bill_trxs pbt,
pjb_invoice_headers pih,
pjb_invoice_lines pil,
pjb_inv_line_dists pild,
pjb_rev_distributions prd,
pjc_xla_ccdl_lines_adj_v ccdl,
hr_all_organization_units haou
WHERE 1 = 1
AND pei.expenditure_type_id = pet.expenditure_type_id
AND pei.project_id = ppa.project_id
AND pei.project_id = ppt.project_id
AND ppeb.project_id = ppt.project_id
AND ppa.project_status_code = pps.project_status_code
–AND pcpl.proj_element_id = ppeb.proj_element_id
–AND pcpl.proj_element_id = ppet.proj_element_id
AND pbt.linked_project_id = ppa.project_id
AND pbt.transaction_id = pei.expenditure_item_id
AND pild.bill_trx_id = pbt.bill_trx_id
AND pil.invoice_line_id = pild.invoice_line_id
AND pih.invoice_id = pild.invoice_id
AND pih.invoice_id = pil.invoice_id
AND prd.bill_trx_id = pbt.bill_trx_id
AND prd.revenue_rate_source_id = pbt.revenue_rate_source_id
AND pei.expenditure_item_id = ccdl.expenditure_item_id(+)
AND ppeb.carrying_out_organization_id = haou.organization_id
–AND ppa.segment1 = ‘11287’
GROUP BY ppa.project_id,
ppa.segment1,
ppt.NAME,
ppt.description,
haou.NAME,
pih.invoice_currency_code,
ppa.completion_date,
pih.ra_invoice_number
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
