View: PA_PROJ_UNBILLED_BASE_VIEW

Product:	PA - Projects
Description:	Base view of project unbilled amounts based on detail transactions
Implementation/DBA Data:	APPS.PA_PROJ_UNBILLED_BASE_VIEW

View Text

SELECT T.PROJECT_ID
, SUM(NVL(EI.BILL_AMOUNT
, 0))
, COUNT(EI.EXPENDITURE_ITEM_ID)
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'Y'
, EI.BILL_AMOUNT
, 0))
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'Y'
, 1
, 0))
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'O'
, EI.BILL_AMOUNT
, 0))
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'O'
, 1
, 0))
, 0
, COUNT(DISTINCT BILL_TRANS_CURRENCY_CODE)
, MAX(BILL_TRANS_CURRENCY_CODE)
, SUM(NVL(EI.BILL_TRANS_BILL_AMOUNT
, 0))
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'Y'
, EI.BILL_TRANS_BILL_AMOUNT
, 0))
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'O'
, EI.BILL_TRANS_BILL_AMOUNT
, 0))
FROM PA_TASKS T
, PA_EXPENDITURE_ITEMS_ALL EI
WHERE T.TASK_ID = EI.TASK_ID
AND EI.BILLABLE_FLAG = 'Y'
AND EI.EVENT_NUM IS NULL
AND NOT EXISTS (SELECT 'INVOICED'
FROM PA_CUST_REV_DIST_LINES_ALL RDL
, PA_DRAFT_INVOICE_ITEMS INV
WHERE RDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND RDL.PROJECT_ID = INV.PROJECT_ID
AND RDL.DRAFT_INVOICE_NUM = INV.DRAFT_INVOICE_NUM
AND RDL.DRAFT_INVOICE_ITEM_LINE_NUM = INV.LINE_NUM
AND INV.INVOICE_LINE_TYPE <> 'NET ZERO ADJUSTMENT'
AND ROWNUM = 1) GROUP BY T.PROJECT_ID UNION SELECT EV.PROJECT_ID
, 0
, 0
, 0
, 0
, 0
, 0
, SUM(NVL(EV.BILL_AMOUNT
, 0))
, 0
, NULL
, 0
, 0
, 0
FROM PA_EVENTS EV
WHERE EV.BILL_AMOUNT <> 0
AND NOT EXISTS ( SELECT 'INVOICED'
FROM PA_DRAFT_INVOICE_ITEMS ITEM
WHERE ITEM.PROJECT_ID = EV.PROJECT_ID
AND NVL(ITEM.EVENT_TASK_ID
, 0) = NVL(EV.TASK_ID
, 0)
AND ITEM.EVENT_NUM = EV.EVENT_NUM
AND ROWNUM = 1) GROUP BY EV.PROJECT_ID

Columns