VIEW: APPS.CE_260_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT CL.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 */ CL.STATEMENT_LINE_ID, /* 8 */ CL.TRX_TYPE, /* 9 */ LK.MEANING, /* 10 */ CL.BANK_TRX_NUMBER, /* 11 */ NVL(CL.CURRENCY_CODE,NVL(CH.CURRENCY_CODE,ABA.CURRENCY_CODE)), /* 12 */ DECODE(NVL(CL.CURRENCY_CODE,NVL(CH.CURRENCY_CODE,ABA.CURRENCY_CODE)), /* 13 */ SOB.CURRENCY_CODE,CL. AMOUNT, ABA.CURRENCY_CODE, CL.AMOUNT, NVL(CL.ORIGINAL_AMOUNT,CL.AMOUNT)), CL.AMOUNT, /* 14 */ TO_NUMBER(NULL), /* 15 */ CH.GL_DATE, /* 16 */ L2.MEANING, /* 17 */ CL.STATUS, /* 18 */ CL.TRX_DATE, /* 19 */ TO_DATE(NULL), /* 20 */ CL.EFFECTIVE_DATE, /* 21 */ CL.EXCHANGE_RATE_DATE, /* 22 */ CL.EXCHANGE_RATE_TYPE, /* 23 */ GLCC.USER_CONVERSION_TYPE, /* 24 */ CL.EXCHANGE_RATE, /* 25 */ CL.CHARGES_AMOUNT, /* 26 */ TO_NUMBER(NULL), /* 27 */ NULL, /* 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 */ CL.STATEMENT_HEADER_ID, /* 38 */ TO_NUMBER(NULL), /* 39 */ CL.CREATION_DATE, /* 40 */ CL.CREATED_BY, /* 41 */ CL.LAST_UPDATE_DATE, /* 42 */ CL.LAST_UPDATED_BY, /* 43 */ NULL, /* 44 */ TO_NUMBER(NULL), /* 45 */ 260, /* 46 */ 0, /* 47 */ ABA.CURRENCY_CODE, /* 48 */ DECODE(NVL(CL.CURRENCY_CODE,NVL(CH.CURRENCY_CODE,ABA.CURRENCY_CODE)), /* 49 */ SOB.CURRENCY_CODE, 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), ABA.ASSET_CODE_COMBINATION_ID, /* 50 */ NULL, /* 51 */ NULL, /* 52 */ TO_NUMBER(NULL), /* 53 */ TO_NUMBER(NULL), /* 54 */ 'STATEMENT', /* 55 */ NULL, /* 56 */ CL.AMOUNT, /* 57 */ TO_NUMBER(NULL), /* 58 */ TO_NUMBER(NULL), /* 59 */ NULL, /* 60 */ TO_DATE(NULL), /* 61 */ CL.INVOICE_TEXT, /* 62 */ CL.BANK_ACCOUNT_TEXT, /* 63 */ CL.CUSTOMER_TEXT, /* 64 */ NULL, TO_NUMBER(NULL), TO_DATE(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL) FROM CE_BANK_ACCTS_GT_V ABA, CE_BANK_BRANCHES_V ABB, /* CE_BANK_ACCT_USES BAU, CE_GL_ACCOUNTS_CCID GAC,*/ CE_STATEMENT_LINES CL, CE_STATEMENT_HEADERS CH, CE_LOOKUPS LK, CE_LOOKUPS L2, GL_DAILY_CONVERSION_TYPES GLCC, GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS WHERE LK.LOOKUP_TYPE = 'AVAILABLE_STMT_TYPE' AND LK.LOOKUP_CODE = DECODE(CL.TRX_TYPE,'MISC_DEBIT','DEBIT', 'MISC_CREDIT','CREDIT',CL.TRX_TYPE) AND L2.LOOKUP_TYPE = 'STATEMENT_LINE_STATUS' AND L2.LOOKUP_CODE = CL.STATUS AND GLCC.CONVERSION_TYPE(+) = CL.EXCHANGE_RATE_TYPE AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID /*AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID (+) AND ABA.ACCOUNT_OWNER_ORG_ID = BAU.ORG_ID (+) AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID (+)*/ AND NVL(CH.STATEMENT_COMPLETE_FLAG, 'N') = 'N' AND ABA.BANK_ACCOUNT_ID = CH.BANK_ACCOUNT_ID AND CL.STATUS = 'UNRECONCILED' AND CH.STATEMENT_HEADER_ID = CL.STATEMENT_HEADER_ID AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID

View Text - HTML Formatted

SELECT CL.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 */ CL.STATEMENT_LINE_ID
, /* 8 */ CL.TRX_TYPE
, /* 9 */ LK.MEANING
, /* 10 */ CL.BANK_TRX_NUMBER
, /* 11 */ NVL(CL.CURRENCY_CODE
, NVL(CH.CURRENCY_CODE
, ABA.CURRENCY_CODE))
, /* 12 */ DECODE(NVL(CL.CURRENCY_CODE
, NVL(CH.CURRENCY_CODE
, ABA.CURRENCY_CODE))
, /* 13 */ SOB.CURRENCY_CODE
, CL. AMOUNT
, ABA.CURRENCY_CODE
, CL.AMOUNT
, NVL(CL.ORIGINAL_AMOUNT
, CL.AMOUNT))
, CL.AMOUNT
, /* 14 */ TO_NUMBER(NULL)
, /* 15 */ CH.GL_DATE
, /* 16 */ L2.MEANING
, /* 17 */ CL.STATUS
, /* 18 */ CL.TRX_DATE
, /* 19 */ TO_DATE(NULL)
, /* 20 */ CL.EFFECTIVE_DATE
, /* 21 */ CL.EXCHANGE_RATE_DATE
, /* 22 */ CL.EXCHANGE_RATE_TYPE
, /* 23 */ GLCC.USER_CONVERSION_TYPE
, /* 24 */ CL.EXCHANGE_RATE
, /* 25 */ CL.CHARGES_AMOUNT
, /* 26 */ TO_NUMBER(NULL)
, /* 27 */ NULL
, /* 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 */ CL.STATEMENT_HEADER_ID
, /* 38 */ TO_NUMBER(NULL)
, /* 39 */ CL.CREATION_DATE
, /* 40 */ CL.CREATED_BY
, /* 41 */ CL.LAST_UPDATE_DATE
, /* 42 */ CL.LAST_UPDATED_BY
, /* 43 */ NULL
, /* 44 */ TO_NUMBER(NULL)
, /* 45 */ 260
, /* 46 */ 0
, /* 47 */ ABA.CURRENCY_CODE
, /* 48 */ DECODE(NVL(CL.CURRENCY_CODE
, NVL(CH.CURRENCY_CODE
, ABA.CURRENCY_CODE))
, /* 49 */ SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, ABA.ASSET_CODE_COMBINATION_ID
, /* 50 */ NULL
, /* 51 */ NULL
, /* 52 */ TO_NUMBER(NULL)
, /* 53 */ TO_NUMBER(NULL)
, /* 54 */ 'STATEMENT'
, /* 55 */ NULL
, /* 56 */ CL.AMOUNT
, /* 57 */ TO_NUMBER(NULL)
, /* 58 */ TO_NUMBER(NULL)
, /* 59 */ NULL
, /* 60 */ TO_DATE(NULL)
, /* 61 */ CL.INVOICE_TEXT
, /* 62 */ CL.BANK_ACCOUNT_TEXT
, /* 63 */ CL.CUSTOMER_TEXT
, /* 64 */ NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM CE_BANK_ACCTS_GT_V ABA
, CE_BANK_BRANCHES_V ABB
, /* CE_BANK_ACCT_USES BAU
, CE_GL_ACCOUNTS_CCID GAC
, */ CE_STATEMENT_LINES CL
, CE_STATEMENT_HEADERS CH
, CE_LOOKUPS LK
, CE_LOOKUPS L2
, GL_DAILY_CONVERSION_TYPES GLCC
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
WHERE LK.LOOKUP_TYPE = 'AVAILABLE_STMT_TYPE'
AND LK.LOOKUP_CODE = DECODE(CL.TRX_TYPE
, 'MISC_DEBIT'
, 'DEBIT'
, 'MISC_CREDIT'
, 'CREDIT'
, CL.TRX_TYPE)
AND L2.LOOKUP_TYPE = 'STATEMENT_LINE_STATUS'
AND L2.LOOKUP_CODE = CL.STATUS
AND GLCC.CONVERSION_TYPE(+) = CL.EXCHANGE_RATE_TYPE
AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID /*AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID (+)
AND ABA.ACCOUNT_OWNER_ORG_ID = BAU.ORG_ID (+)
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID (+)*/
AND NVL(CH.STATEMENT_COMPLETE_FLAG
, 'N') = 'N'
AND ABA.BANK_ACCOUNT_ID = CH.BANK_ACCOUNT_ID
AND CL.STATUS = 'UNRECONCILED'
AND CH.STATEMENT_HEADER_ID = CL.STATEMENT_HEADER_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID