VIEW: APPS.PA_BILLING_REV_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT 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_revenue_items dri, pa_draft_revenues dr WHERE ei.expenditure_id = exp.expenditure_id AND ei.task_id = t.task_id AND dr.project_id = nvl(PA_BILLING.GetProjId,t.project_id) AND t.project_id = rdl.project_id AND t.top_task_id = nvl(PA_BILLING.GetTaskId, t.top_task_id) AND dri.request_id = nvl(PA_BILLING.GetReqId,dri.request_id) AND ei.expenditure_item_id = rdl.expenditure_item_id AND dri.project_id = dr.project_id AND dri.draft_revenue_num = dr.draft_revenue_num AND dri.project_id = rdl.project_id AND dri.draft_revenue_num = rdl.draft_revenue_num AND dri.line_num = rdl.draft_revenue_item_line_num AND ((PA_BILLING.GetCallPlace = 'ADJ' and dr.draft_revenue_num_credited IS NOT NULL) OR (nvl(PA_BILLING.GetCallPlace,'REG') = 'REG' and dr.draft_revenue_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 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_REVENUE_ITEMS DRI
, PA_DRAFT_REVENUES DR
WHERE EI.EXPENDITURE_ID = EXP.EXPENDITURE_ID
AND EI.TASK_ID = T.TASK_ID
AND DR.PROJECT_ID = NVL(PA_BILLING.GETPROJID
, T.PROJECT_ID)
AND T.PROJECT_ID = RDL.PROJECT_ID
AND T.TOP_TASK_ID = NVL(PA_BILLING.GETTASKID
, T.TOP_TASK_ID)
AND DRI.REQUEST_ID = NVL(PA_BILLING.GETREQID
, DRI.REQUEST_ID)
AND EI.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID
AND DRI.PROJECT_ID = DR.PROJECT_ID
AND DRI.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM
AND DRI.PROJECT_ID = RDL.PROJECT_ID
AND DRI.DRAFT_REVENUE_NUM = RDL.DRAFT_REVENUE_NUM
AND DRI.LINE_NUM = RDL.DRAFT_REVENUE_ITEM_LINE_NUM
AND ((PA_BILLING.GETCALLPLACE = 'ADJ'
AND DR.DRAFT_REVENUE_NUM_CREDITED IS NOT NULL) OR (NVL(PA_BILLING.GETCALLPLACE
, 'REG') = 'REG'
AND DR.DRAFT_REVENUE_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