View: PA_CRDL_REVERSAL_RATE_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_CRDL_REVERSAL_RATE_V

View Text

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 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

Columns