VIEW: APPS.CE_999_REVERSAL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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