View: CE_185_TRANSACTIONS_V

Product:	CE - Cash Management
Description:	Available Treasury transactions for reconciliation via Open Interface
Implementation/DBA Data:	APPS.CE_185_TRANSACTIONS_V

View Text

SELECT T.ROWID
, /* 1 */ 'N'
, /* 2 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ ABA.BANK_ACCOUNT_NAME
, /* 4 */ ABA.BANK_ACCOUNT_NUM
, /* 5 */ ABB.BANK_NAME
, /* 6 */ ABB.BANK_BRANCH_NAME
, /* 7 */ T.SETTLEMENT_SUMMARY_ID
, /* 8 */ DECODE(SIGN(T.SETTLEMENT_AMOUNT)
, 1
, 'CASH'
, 'PAYMENT')
, /* 9 */ DECODE(SIGN(T.SETTLEMENT_AMOUNT)
, 1
, 'CASH'
, 'PAYMENT')
, /* 10 */ TO_CHAR(T.SETTLEMENT_NUMBER)
, /* 11 */ T.CURRENCY
, /* 12 */ ABS(T.SETTLEMENT_AMOUNT)
, /* 13 */ ABS(T.SETTLEMENT_AMOUNT)
, /* 14 */ TO_NUMBER(NULL)
, /* 15 */ T.SETTLEMENT_DATE
, /* 16 */ DECODE(T.STATUS
, 'A'
, NVL(ABA.RECON_OI_FLOAT_STATUS
, '#')
, 'R'
, NVL(ABA.RECON_OI_CLEARED_STATUS
, '#'))
, /* 17 */ DECODE(T.STATUS
, 'A'
, NVL(ABA.RECON_OI_FLOAT_STATUS
, '#')
, 'R'
, NVL(ABA.RECON_OI_CLEARED_STATUS
, '#'))
, /* 18 */ T.SETTLEMENT_DATE
, /* 19 */ TO_DATE(NULL)
, /* 20 */ TO_DATE(NULL)
, /* 21 */ TO_DATE(NULL)
, /* 22 */ NULL
, /* 23 */ NULL
, /* 24 */ TO_NUMBER(NULL)
, /* 25 */ 0
, /* 26 */ 0
, /* 27 */ TO_CHAR(DDA.DEAL_NUMBER) || '/' ||TO_CHAR(DDA.TRANSACTION_NUMBER)
, /* 28 */ TO_NUMBER(NULL)
, /* 29 */ NULL
, /* 30 */ NULL
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33 */ TO_NUMBER(NULL)
, /* 34 */ TO_NUMBER(NULL)
, /* 35 */ NULL
, /* 36 */ NULL
, /* 37 */ TO_NUMBER(NULL)
, /* 38 */ DECODE(CRE.STATUS_FLAG
, 'M'
, /* 39 */ DECODE(DECODE(SIGN(T.SETTLEMENT_AMOUNT)
, 1
, 'CASH'
, 'PAYMENT')
, 'CASH'
, DECODE(T.CURRENCY
, SOB.CURRENCY_CODE
, T.SETTLEMENT_AMOUNT
, ABA.CURRENCY_CODE
, T.SETTLEMENT_AMOUNT
, 0)
, 'PAYMENT'
, DECODE(T.CURRENCY
, SOB.CURRENCY_CODE
, T.SETTLEMENT_AMOUNT
, ABA.CURRENCY_CODE
, T.SETTLEMENT_AMOUNT
, 0)))
, T.CREATION_DATE
, /* 40 */ T.CREATED_BY
, /* 41 */ T.LAST_UPDATE_DATE
, /* 42 */ T.LAST_UPDATED_BY
, /* 43 */ NULL
, /* 44 */ TO_NUMBER(NULL)
, /* 45 */ 185
, /* 46 */ 0
, /* 47 */ ABA.CURRENCY_CODE
, /* 48 */ DECODE(T.CURRENCY
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, /* 49 */ NVL(XTR_ASSET_CCID
, NVL(GAC.ASSET_CODE_COMBINATION_ID
, ABA.ASSET_CODE_COMBINATION_ID))
, /* 50 */ NULL
, /* 51 */ NULL
, /* 52 */ TO_NUMBER(NULL)
, /* 53 */ TO_NUMBER(NULL)
, /* 54 */ 'XTR_LINE'
, /* 55 */ NULL
, /* 56 */ TO_NUMBER(NULL)
, /* 57 */ NULL
, /* 58 */ TO_NUMBER(NULL)
, /* 59 */ TO_DATE(NULL)
, /* 60 */ TO_NUMBER(NULL)
, /* 61 */ TO_NUMBER(NULL)
, /* 62 */ NULL
, /* 63 */ TO_DATE(NULL)
, /* 64 */ BAU.BANK_ACCT_USE_ID
, SYS.LEGAL_ENTITY_ID
, TO_NUMBER(NULL)
FROM GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_SECURITY_PROFILES_GT LE
, CE_BANK_ACCOUNTS ABA
, /*CE_BANK_ACCT_USES_LE_V BAU
, */ CE_BANK_ACCT_USES_ALL BAU
, CE_GL_ACCOUNTS_CCID GAC
, CE_BANK_BRANCHES_V ABB
, XTR_SETTLEMENT_SUMMARY T
, CE_STATEMENT_RECONCILIATIONS CRE
, XTR_DEAL_DATE_AMOUNTS DDA
, XTR_BANK_ACCOUNTS XBA
WHERE SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = LE.ORGANIZATION_ID
AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY'
AND ABA.ACCOUNT_OWNER_ORG_ID = BAU.LEGAL_ENTITY_ID (+)
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID (+)
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID (+) /*AND BAU.BANK_ACCT_USE_ID = T.COMPANY_ACCT_USE_ID */
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND ABA.BANK_ACCOUNT_ID = XBA.CE_BANK_ACCOUNT_ID
AND XBA.ACCOUNT_NUMBER = T.COMPANY_ACCT_NO
AND CRE.REFERENCE_ID(+) = T.SETTLEMENT_SUMMARY_ID
AND T.SETTLEMENT_NUMBER = DDA.SETTLEMENT_NUMBER (+)
AND ((DDA.DEAL_TYPE IS NOT NULL
AND DDA.DEAL_TYPE <> 'RTMM') OR (DDA.DEAL_TYPE IS NULL))
AND CRE.REFERENCE_TYPE(+) = 'XTR_LINE'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND NVL(T.STATUS
, 'X')= 'A'
AND T.SETTLEMENT_DATE >= SYS.CASHBOOK_BEGIN_DATE

Columns