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 /*+INDEX(CRDL PA_CUST_REV_DIST_LINES_N2) */ EI.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 EI.BILL_TRANS_CURRENCY_CODE)
, MAX(EI.BILL_TRANS_CURRENCY_CODE)
, SUM(NVL(CRDL.BILL_TRANS_BILL_AMOUNT
, 0))
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'Y'
, CRDL.BILL_TRANS_BILL_AMOUNT
, 0))
, SUM(DECODE(EI.BILL_HOLD_FLAG
, 'O'
, CRDL.BILL_TRANS_BILL_AMOUNT
, 0))
, 0
, NULL
FROM PA_EXPENDITURE_ITEMS_ALL EI
, PA_CUST_REV_DIST_LINES_ALL CRDL
WHERE EI.BILLABLE_FLAG = 'Y'
AND CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND CRDL.LINE_NUM = (SELECT MAX(LINE_NUM)
FROM PA_CUST_REV_DIST_LINES CRDL2
WHERE CRDL.EXPENDITURE_ITEM_ID = CRDL2.EXPENDITURE_ITEM_ID)
AND CRDL.PROJECT_ID = EI.PROJECT_ID
AND CRDL.DRAFT_INVOICE_NUM IS NULL
AND EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
AND EI.EVENT_NUM IS NULL GROUP BY EI.PROJECT_ID UNION SELECT EV.PROJECT_ID
, 0
, 0
, 0
, 0
, 0
, 0
, SUM(NVL(EV.BILL_TRANS_BILL_AMOUNT
, 0))
, 0
, NULL
, 0
, 0
, 0
, COUNT(DISTINCT EV.BILL_TRANS_CURRENCY_CODE)
, MAX(EV.BILL_TRANS_CURRENCY_CODE)
FROM PA_EVENTS EV
WHERE EV.BILL_TRANS_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