View: PA_PROJ_INVOICE_BASE_VIEW

Product:	PA - Projects
Description:	PA_PROJ_INVOICE_BASE_VIEW is the base view for reporting revenue and invoice totals for projects
Implementation/DBA Data:	APPS.PA_PROJ_INVOICE_BASE_VIEW

View Text

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

Columns