View: CE_999_REVERSAL_V

Product:	CE - Cash Management
Description:	Open interface transactions for unclearing
Implementation/DBA Data:	APPS.CE_999_REVERSAL_V

View Text

SELECT T.ROW_ID
, 'N'
, ABA.BANK_ACCOUNT_ID
, T.TRX_ID
, T.TRX_TYPE
, T.TRX_TYPE_DSP
, T.TRX_NUMBER
, T.CURRENCY_CODE
, T.AMOUNT
, DECODE(T.CURRENCY_CODE
, SOB.CURRENCY_CODE
, T.AMOUNT
, ABA.CURRENCY_CODE
, T.AMOUNT
, NVL(T.ACCTD_AMOUNT
, T.AMOUNT))
, DECODE(T.CURRENCY_CODE
, SOB.CURRENCY_CODE
, NVL(T.CLEARED_AMOUNT
, T.AMOUNT)
, ABA.CURRENCY_CODE
, NVL(T.CLEARED_AMOUNT
, T.AMOUNT)
, NVL(T.ACCTD_CLEARED_AMOUNT
, NVL(T.CLEARED_AMOUNT
, T.AMOUNT)))
, T.GL_DATE
, T.STATUS_DSP
, T.STATUS
, TRUNC(T.TRX_DATE)
, T.CLEARED_DATE
, TO_DATE(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, T.CREATION_DATE
, T.CREATED_BY
, T.LAST_UPDATE_DATE
, T.LAST_UPDATED_BY
, NULL
, ABA.BANK_ACCOUNT_NUM
, ABA.BANK_ACCOUNT_NAME
, ABA.CURRENCY_CODE
, ABB.BANK_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, 999
, NULL
, NULL
, 'N'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, DECODE(T.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, T.ACCTD_AMOUNT
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL) FROM CE_BANK_ACCTS_GT_V ABA
, CE_BANK_BRANCHES_V ABB
, CE_999_INTERFACE_V T
, CE_SYSTEM_PARAMETERS SYS
, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID AND ABA.BANK_ACCOUNT_ID = T.BANK_ACCOUNT_ID /*AND ABA.ACCOUNT_OWNER_ORG_ID = NVL(SYS.ORG_ID
, ABA.ACCOUNT_OWNER_ORG_ID)*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND NOT EXISTS ( SELECT NULL FROM CE_STATEMENT_RECONCILS_ALL CRE WHERE CRE.REFERENCE_TYPE = 'ROI_LINE' AND CRE.CURRENT_RECORD_FLAG = 'Y' AND CRE.STATUS_FLAG = 'M' AND CRE.REFERENCE_ID = T.TRX_ID) AND T.STATUS = ABA.RECON_OI_CLEARED_STATUS

Columns