VIEW: APPS.PA_PROJ_UNBILLED_BASE_VIEW

Source

View Text - Preformatted

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

View Text - HTML Formatted

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