eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CE_185_TRANSACTIONS_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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