VIEW: APPS.PA_CRDL_REVERSAL_RATE_V

Source

View Text - Preformatted

SELECT crdl.expenditure_item_id, crdl.line_num, dii.inv_rate_type, dii.inv_rate_date, dii.inv_exchange_rate, 'X' from pa_cust_rev_dist_lines_all crdl, pa_cust_rev_dist_lines_all crdl1, pa_draft_invoice_items dii WHERE crdl.invoice_eligible_flag = 'Y' and crdl.project_id = pa_billing.GetProjId and crdl.line_num_reversed is not null and crdl.expenditure_item_id = crdl1.expenditure_item_id and crdl.line_num_reversed = crdl1.line_num and dii.project_id = crdl1.project_id and dii.draft_invoice_num = crdl1.draft_invoice_num and dii.line_num = crdl1.draft_invoice_item_line_num union select crdl.expenditure_item_id, crdl.line_num, dii.inv_rate_type, dii.inv_rate_date, dii.inv_exchange_rate, 'X' from pa_cust_rev_dist_lines_all crdl, pa_expenditure_items_all ei, pa_cust_rev_dist_lines_all crdl1, pa_draft_invoice_items dii where crdl.invoice_eligible_flag = 'Y' and crdl.expenditure_item_id = ei.expenditure_item_id and ei.adjusted_expenditure_item_id is not null and crdl1.expenditure_item_id = ei.adjusted_expenditure_item_id and crdl1.line_num = (select max(crdl2.line_num) from pa_cust_rev_dist_lines_all crdl2 where crdl2.expenditure_item_id = ei.adjusted_expenditure_item_id and crdl2.additional_revenue_flag = 'N') and dii.project_id = crdl1.project_id and crdl.project_id = pa_billing.GetProjId and dii.draft_invoice_num = crdl1.draft_invoice_num and dii.line_num = crdl1.draft_invoice_item_line_num union select crdl.expenditure_item_id, crdl.line_num, NULL, NULL, NULL, NULL from pa_cust_rev_dist_lines_all crdl, pa_expenditure_items_all ei where crdl.invoice_eligible_flag = 'Y' and crdl.expenditure_item_id = ei.expenditure_item_id and ei.adjusted_expenditure_item_id is null and crdl.line_num_reversed is null and crdl.project_id = pa_billing.GetProjId

View Text - HTML Formatted

SELECT CRDL.EXPENDITURE_ITEM_ID
, CRDL.LINE_NUM
, DII.INV_RATE_TYPE
, DII.INV_RATE_DATE
, DII.INV_EXCHANGE_RATE
, 'X'
FROM PA_CUST_REV_DIST_LINES_ALL CRDL
, PA_CUST_REV_DIST_LINES_ALL CRDL1
, PA_DRAFT_INVOICE_ITEMS DII
WHERE CRDL.INVOICE_ELIGIBLE_FLAG = 'Y'
AND CRDL.PROJECT_ID = PA_BILLING.GETPROJID
AND CRDL.LINE_NUM_REVERSED IS NOT NULL
AND CRDL.EXPENDITURE_ITEM_ID = CRDL1.EXPENDITURE_ITEM_ID
AND CRDL.LINE_NUM_REVERSED = CRDL1.LINE_NUM
AND DII.PROJECT_ID = CRDL1.PROJECT_ID
AND DII.DRAFT_INVOICE_NUM = CRDL1.DRAFT_INVOICE_NUM
AND DII.LINE_NUM = CRDL1.DRAFT_INVOICE_ITEM_LINE_NUM UNION SELECT CRDL.EXPENDITURE_ITEM_ID
, CRDL.LINE_NUM
, DII.INV_RATE_TYPE
, DII.INV_RATE_DATE
, DII.INV_EXCHANGE_RATE
, 'X'
FROM PA_CUST_REV_DIST_LINES_ALL CRDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_CUST_REV_DIST_LINES_ALL CRDL1
, PA_DRAFT_INVOICE_ITEMS DII
WHERE CRDL.INVOICE_ELIGIBLE_FLAG = 'Y'
AND CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NOT NULL
AND CRDL1.EXPENDITURE_ITEM_ID = EI.ADJUSTED_EXPENDITURE_ITEM_ID
AND CRDL1.LINE_NUM = (SELECT MAX(CRDL2.LINE_NUM)
FROM PA_CUST_REV_DIST_LINES_ALL CRDL2
WHERE CRDL2.EXPENDITURE_ITEM_ID = EI.ADJUSTED_EXPENDITURE_ITEM_ID
AND CRDL2.ADDITIONAL_REVENUE_FLAG = 'N')
AND DII.PROJECT_ID = CRDL1.PROJECT_ID
AND CRDL.PROJECT_ID = PA_BILLING.GETPROJID
AND DII.DRAFT_INVOICE_NUM = CRDL1.DRAFT_INVOICE_NUM
AND DII.LINE_NUM = CRDL1.DRAFT_INVOICE_ITEM_LINE_NUM UNION SELECT CRDL.EXPENDITURE_ITEM_ID
, CRDL.LINE_NUM
, NULL
, NULL
, NULL
, NULL
FROM PA_CUST_REV_DIST_LINES_ALL CRDL
, PA_EXPENDITURE_ITEMS_ALL EI
WHERE CRDL.INVOICE_ELIGIBLE_FLAG = 'Y'
AND CRDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
AND CRDL.LINE_NUM_REVERSED IS NULL
AND CRDL.PROJECT_ID = PA_BILLING.GETPROJID