VIEW: APPS.PA_BILLING_INV_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT /*+ LEADING(pdi)*/ t.project_id ,t.task_id ,t.top_task_id ,ei.expenditure_type ,ei.system_linkage_function ,exp.expenditure_id ,exp.incurred_by_person_id ,ei.expenditure_item_id ,ei.expenditure_item_date ,ei.quantity ,ei.raw_cost ,ei.burden_cost ,sum(rdl.bill_amount) ,sum(rdl.amount) ,ei.project_currency_code FROM pa_expenditures_all exp, pa_tasks t, pa_expenditure_items_all ei, pa_cust_rev_dist_lines_all rdl, pa_draft_invoice_items pdii, pa_draft_invoices pdi WHERE ei.task_id = t.task_Id and t.project_id = pdi.project_id and ei.expenditure_id = exp.expenditure_id and t.top_task_id = nvl(PA_BILLING.GetTaskId, t.top_task_id) and t.project_id = PA_BILLING.GetProjId and pdi.project_id = PA_BILLING.GetProjId and pdii.project_id = rdl.project_id and pdii.draft_invoice_num = rdl.draft_invoice_num and pdii.line_num = rdl.draft_invoice_item_line_num and pdii.invoice_line_type <> 'NET ZERO ADJUSTMENT' and pdii.project_id = pdi.project_id and pdii.draft_invoice_num = pdi.draft_invoice_num and pdi.request_id = PA_BILLING.GetReqId and ei.expenditure_item_id = rdl.expenditure_item_id and ((PA_BILLING.GetCallPlace = 'ADJ' and pdi.draft_invoice_num_credited IS NOT NULL) OR (nvl(PA_BILLING.GetCallPlace,'REG') = 'REG' and pdi.draft_invoice_num_credited IS NULL)) GROUP BY t.project_id, t.task_id, t.top_task_id, ei.expenditure_type, ei.system_linkage_function, exp.expenditure_id, exp.incurred_by_person_id, ei.expenditure_item_date, ei.expenditure_item_id, ei.quantity, ei.raw_cost, ei.burden_cost, ei.project_currency_code

View Text - HTML Formatted

SELECT /*+ LEADING(PDI)*/ T.PROJECT_ID
, T.TASK_ID
, T.TOP_TASK_ID
, EI.EXPENDITURE_TYPE
, EI.SYSTEM_LINKAGE_FUNCTION
, EXP.EXPENDITURE_ID
, EXP.INCURRED_BY_PERSON_ID
, EI.EXPENDITURE_ITEM_ID
, EI.EXPENDITURE_ITEM_DATE
, EI.QUANTITY
, EI.RAW_COST
, EI.BURDEN_COST
, SUM(RDL.BILL_AMOUNT)
, SUM(RDL.AMOUNT)
, EI.PROJECT_CURRENCY_CODE
FROM PA_EXPENDITURES_ALL EXP
, PA_TASKS T
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_CUST_REV_DIST_LINES_ALL RDL
, PA_DRAFT_INVOICE_ITEMS PDII
, PA_DRAFT_INVOICES PDI
WHERE EI.TASK_ID = T.TASK_ID
AND T.PROJECT_ID = PDI.PROJECT_ID
AND EI.EXPENDITURE_ID = EXP.EXPENDITURE_ID
AND T.TOP_TASK_ID = NVL(PA_BILLING.GETTASKID
, T.TOP_TASK_ID)
AND T.PROJECT_ID = PA_BILLING.GETPROJID
AND PDI.PROJECT_ID = PA_BILLING.GETPROJID
AND PDII.PROJECT_ID = RDL.PROJECT_ID
AND PDII.DRAFT_INVOICE_NUM = RDL.DRAFT_INVOICE_NUM
AND PDII.LINE_NUM = RDL.DRAFT_INVOICE_ITEM_LINE_NUM
AND PDII.INVOICE_LINE_TYPE <> 'NET ZERO ADJUSTMENT'
AND PDII.PROJECT_ID = PDI.PROJECT_ID
AND PDII.DRAFT_INVOICE_NUM = PDI.DRAFT_INVOICE_NUM
AND PDI.REQUEST_ID = PA_BILLING.GETREQID
AND EI.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID
AND ((PA_BILLING.GETCALLPLACE = 'ADJ'
AND PDI.DRAFT_INVOICE_NUM_CREDITED IS NOT NULL) OR (NVL(PA_BILLING.GETCALLPLACE
, 'REG') = 'REG'
AND PDI.DRAFT_INVOICE_NUM_CREDITED IS NULL)) GROUP BY T.PROJECT_ID
, T.TASK_ID
, T.TOP_TASK_ID
, EI.EXPENDITURE_TYPE
, EI.SYSTEM_LINKAGE_FUNCTION
, EXP.EXPENDITURE_ID
, EXP.INCURRED_BY_PERSON_ID
, EI.EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_ITEM_ID
, EI.QUANTITY
, EI.RAW_COST
, EI.BURDEN_COST
, EI.PROJECT_CURRENCY_CODE