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
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
Oracle Fusion HCM Online Training
ReplyDeleteOracle 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
Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle PPM Cloud . Actually I was looking for the same information on internet for Oracle Project Portfolio Management (PPM) Cloud Tutorial and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle PPM Cloud . By attending Oracle PPM Cloud Training .
ReplyDeleteThanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Procurement . Actually I was looking for the same information on internet for Oracle Fusion Supply Chain Management Cloud and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle Fusion Manufacturing . By attending Oracle Fusion Financials .
ReplyDeleteVery glad to see this blog post. very informative. thanks for sharing with us.
ReplyDeleteYour can find here the most information about
Soft Online Training offers best oracle fusion courses
Oracle Fusion SCM Online Training
Oracle Fusion HCM Online Training
Oracle Fusion Financials Online Training
Oracle Fusion Technical Online Training
Oracle Fusion PPM Online Training
Oracle Integration Cloud Online Training