VIEW: APPS.PA_PROJ_INVOICE_BASE_VIEW

Source

View Text - Preformatted

SELECT pf.project_id , sum(nvl(pf.total_baselined_amount,0)) , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0, 0 , 0 , 0 , 0 , 0 FROM pa_summary_project_fundings pf GROUP BY pf.project_id UNION SELECT dri.project_id ,0 , sum(nvl(dri.amount,0)) , 0 , 0 , 0 , 0 , 0 , 0 , 0, 0 , 0 , 0 , 0 , 0 FROM pa_draft_revenue_items dri, pa_draft_revenues dr WHERE dr.generation_error_flag = 'N' AND dr.project_id = dri.project_id AND dr.draft_revenue_num = dri.draft_revenue_num GROUP BY dri.project_id UNION SELECT e.project_id , 0 , 0 , sum(decode(et.event_type_classification, 'WRITE OFF', nvl(e.revenue_amount,0),0)) , sum(decode(et.event_type_classification, 'WRITE OFF', 0, nvl(e.revenue_amount,0))) , 0 , 0 , 0 , 0 , 0, 0 , 0 , 0 , 0 , 0 FROM pa_events e,pa_event_types et WHERE e.event_type = et.event_type AND e.revenue_amount <> 0 AND EXISTS (Select 'event revenue' from pa_cust_event_rev_dist_lines erdl where nvl(erdl.task_id, 0) = nvl(e.task_id, 0) and erdl.project_id = e.project_id and erdl.event_num = e.event_num) GROUP BY e.project_id UNION SELECT inv.project_id , 0 , 0 , 0 , 0 , sum(item.amount) , - sum(decode(item.invoice_line_type,'RETENTION', item.amount,0)) , sum(item.amount - decode(item.invoice_line_type,'RETENTION', item.amount,0)), 0 , 0 , sum(item.amount -decode(nvl(inv.write_off_flag,'N'),'Y',0,item.amount)) , sum(item.projfunc_bill_amount), sum(DECODE(item.invoice_line_type,'RETENTION',0,DECODE(nvl(inv.write_off_flag,'N'),'Y',0,item.projfunc_bill_amount))), sum(item.projfunc_bill_amount-decode(nvl(inv.write_off_flag,'N'),'Y',0,item.projfunc_bill_amount)), sum(DECODE(item.invoice_line_type,'RETENTION',DECODE(nvl(inv.write_off_flag,'N'),'Y',0,item.projfunc_bill_amount),0)) FROM pa_draft_invoice_items item, pa_draft_invoices inv WHERE inv.project_id = item.project_id AND inv.draft_invoice_num = item.draft_invoice_num AND inv.released_date is not null GROUP BY inv.project_id UNION SELECT rdl.project_id , 0 , 0 , 0 , 0 , 0 , 0 , 0 , sum(nvl(rdl.bill_amount,0)), count(distinct ei.expenditure_item_id), 0 , 0 , 0 , 0 , 0 FROM PA_EXPENDITURE_ITEMS_ALL EI,PA_CUST_REV_DIST_LINES RDL WHERE ei.bill_hold_flag in ('Y','O') AND ei.expenditure_item_id = rdl.expenditure_item_id AND rdl.draft_invoice_num is null GROUP BY rdl.project_id

View Text - HTML Formatted

SELECT PF.PROJECT_ID
, SUM(NVL(PF.TOTAL_BASELINED_AMOUNT
, 0))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_SUMMARY_PROJECT_FUNDINGS PF GROUP BY PF.PROJECT_ID UNION SELECT DRI.PROJECT_ID
, 0
, SUM(NVL(DRI.AMOUNT
, 0))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_DRAFT_REVENUE_ITEMS DRI
, PA_DRAFT_REVENUES DR
WHERE DR.GENERATION_ERROR_FLAG = 'N'
AND DR.PROJECT_ID = DRI.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM = DRI.DRAFT_REVENUE_NUM GROUP BY DRI.PROJECT_ID UNION SELECT E.PROJECT_ID
, 0
, 0
, SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, NVL(E.REVENUE_AMOUNT
, 0)
, 0))
, SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION
, 'WRITE OFF'
, 0
, NVL(E.REVENUE_AMOUNT
, 0)))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM PA_EVENTS E
, PA_EVENT_TYPES ET
WHERE E.EVENT_TYPE = ET.EVENT_TYPE
AND E.REVENUE_AMOUNT <> 0
AND EXISTS (SELECT 'EVENT REVENUE'
FROM PA_CUST_EVENT_REV_DIST_LINES ERDL
WHERE NVL(ERDL.TASK_ID
, 0) = NVL(E.TASK_ID
, 0)
AND ERDL.PROJECT_ID = E.PROJECT_ID
AND ERDL.EVENT_NUM = E.EVENT_NUM) GROUP BY E.PROJECT_ID UNION SELECT INV.PROJECT_ID
, 0
, 0
, 0
, 0
, SUM(ITEM.AMOUNT)
, - SUM(DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.AMOUNT
, 0))
, SUM(ITEM.AMOUNT - DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.AMOUNT
, 0))
, 0
, 0
, SUM(ITEM.AMOUNT -DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, ITEM.AMOUNT))
, SUM(ITEM.PROJFUNC_BILL_AMOUNT)
, SUM(DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, 0
, DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, ITEM.PROJFUNC_BILL_AMOUNT)))
, SUM(ITEM.PROJFUNC_BILL_AMOUNT-DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, ITEM.PROJFUNC_BILL_AMOUNT))
, SUM(DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, DECODE(NVL(INV.WRITE_OFF_FLAG
, 'N')
, 'Y'
, 0
, ITEM.PROJFUNC_BILL_AMOUNT)
, 0))
FROM PA_DRAFT_INVOICE_ITEMS ITEM
, PA_DRAFT_INVOICES INV
WHERE INV.PROJECT_ID = ITEM.PROJECT_ID
AND INV.DRAFT_INVOICE_NUM = ITEM.DRAFT_INVOICE_NUM
AND INV.RELEASED_DATE IS NOT NULL GROUP BY INV.PROJECT_ID UNION SELECT RDL.PROJECT_ID
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, SUM(NVL(RDL.BILL_AMOUNT
, 0))
, COUNT(DISTINCT EI.EXPENDITURE_ITEM_ID)
, 0
, 0
, 0
, 0
, 0
FROM PA_EXPENDITURE_ITEMS_ALL EI
, PA_CUST_REV_DIST_LINES RDL
WHERE EI.BILL_HOLD_FLAG IN ('Y'
, 'O')
AND EI.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID
AND RDL.DRAFT_INVOICE_NUM IS NULL GROUP BY RDL.PROJECT_ID