VIEW: APPS.CE_260_RECONCILED_V

Source

View Text - Preformatted

SELECT CLT.ROWID, /* 1 */ 'N', /* 2 */ CLL.STATEMENT_LINE_ID, /* 3 */ CH.BANK_ACCOUNT_ID, /* 4 */ CLT.STATEMENT_LINE_ID, /* 5 */ CLT.TRX_TYPE, /* 6 */ LK.MEANING, /* 7 */ CLT.BANK_TRX_NUMBER, /* 8 */ NVL(CLT.CURRENCY_CODE,NVL(CH.CURRENCY_CODE,ABA.CURRENCY_CODE)), /* 9 */ DECODE(CLT.CURRENCY_CODE,SOB.CURRENCY_CODE, 'FUNCTIONAL', /* 10 */ ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), DECODE(CLT.CURRENCY_CODE, SOB.CURRENCY_CODE, CLT.AMOUNT, /* 11 */ ABA.CURRENCY_CODE, CLT.AMOUNT, NVL(CLT.ORIGINAL_AMOUNT, CLT.AMOUNT)), CLT.AMOUNT, /* 12 */ CRE.AMOUNT, /* 13 */ CH.GL_DATE, /* 14 */ L2.MEANING, /* 15 */ CLT.STATUS, /* 16 */ CLT.TRX_DATE, /* 17 */ CLT.TRX_DATE, /* 18 */ CLT.EFFECTIVE_DATE, /* 19 */ CLT.EXCHANGE_RATE_DATE, /* 20 */ GDC.USER_CONVERSION_TYPE, /* 21 */ CLT.EXCHANGE_RATE, /* 22 */ CLT.CHARGES_AMOUNT, /* 23 */ TO_NUMBER(NULL), /* 24 */ NULL, /* 25 */ TO_NUMBER(NULL), /* 26 */ NULL, /* 27 */ NULL, /* 28 */ NULL, /* 29 */ TO_NUMBER(NULL), /* 30 */ TO_NUMBER(NULL), /* 31 */ NULL, /* 32 */ NULL, /* 33*/ CLT.STATEMENT_HEADER_ID, /* 34 */ CRE.CREATION_DATE, /* 35 */ CRE.CREATED_BY, /* 36 */ CRE.LAST_UPDATE_DATE, /* 37 */ CRE.LAST_UPDATED_BY, /* 38 */ CLL.STATEMENT_HEADER_ID, /* 39 */ TO_NUMBER(NULL), /* 40 */ ABA.BANK_BRANCH_ID, /* 41 */ TO_DATE(NULL), /* 42 */ NULL, /* 43 */ TO_DATE(NULL), /* 44 */ L3.MEANING, /* 45 */ CLL.LINE_NUMBER, /* 46 */ CLL.TRX_DATE, /* 47 */ CLL.AMOUNT, /* 48 */ CLL.STATUS, /* 49 */ TC.DESCRIPTION, /* 50 */ CH.STATEMENT_NUMBER, /* 51 */ CH.STATEMENT_DATE, /* 52 */ ABA.BANK_ACCOUNT_NAME, /* 53 */ ABA.BANK_ACCOUNT_NUM, /* 54 */ ABA.CURRENCY_CODE, /* 55 */ CH.DOC_SEQUENCE_VALUE, /* 56 */ CLL.TRX_TYPE, /* 57 */ NULL, /* 58 */ CH.CONTROL_END_BALANCE, /* 59 */ 260, /* 60 */ 1, /* 61 */ CRE.AMOUNT, /* 62 */ 'STATEMENT', /* 63 */ CH.STATEMENT_COMPLETE_FLAG, /* 64 */ CRE.REQUEST_ID, /* 65 */ TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL) FROM CE_BANK_ACCTS_GT_V ABA, CE_STATEMENT_LINES CLT, CE_STATEMENT_LINES CLL, CE_STATEMENT_HEADERS CH, CE_STATEMENT_RECONCILS_ALL CRE, CE_TRANSACTION_CODES TC, CE_LOOKUPS LK, CE_LOOKUPS L2, CE_LOOKUPS L3, GL_DAILY_CONVERSION_TYPES GDC, GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS WHERE LK.LOOKUP_CODE = DECODE(CLT.TRX_TYPE,'MISC_DEBIT','DEBIT', 'MISC_CREDIT','CREDIT',CLT.TRX_TYPE) AND LK.LOOKUP_TYPE = 'AVAILABLE_STMT_TYPE' AND L2.LOOKUP_CODE = CLT.STATUS AND L2.LOOKUP_TYPE = 'STATEMENT_LINE_STATUS' AND L3.LOOKUP_CODE = CLL.TRX_TYPE AND L3.LOOKUP_TYPE = 'BANK_TRX_TYPE' AND TC.TRANSACTION_CODE_ID (+) = CLL.TRX_CODE_ID AND GDC.CONVERSION_TYPE(+) = CLT.EXCHANGE_RATE_TYPE AND ABA.BANK_ACCOUNT_ID = CH.BANK_ACCOUNT_ID AND CH.STATEMENT_HEADER_ID = CLL.STATEMENT_HEADER_ID AND CRE.STATEMENT_LINE_ID = CLL.STATEMENT_LINE_ID AND CRE.REFERENCE_ID = CLT. STATEMENT_LINE_ID AND CRE.REFERENCE_TYPE = 'STATEMENT' AND CRE.STATUS_FLAG = 'M' AND CRE.CURRENT_RECORD_FLAG = 'Y' 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 CLT.ROWID
, /* 1 */ 'N'
, /* 2 */ CLL.STATEMENT_LINE_ID
, /* 3 */ CH.BANK_ACCOUNT_ID
, /* 4 */ CLT.STATEMENT_LINE_ID
, /* 5 */ CLT.TRX_TYPE
, /* 6 */ LK.MEANING
, /* 7 */ CLT.BANK_TRX_NUMBER
, /* 8 */ NVL(CLT.CURRENCY_CODE
, NVL(CH.CURRENCY_CODE
, ABA.CURRENCY_CODE))
, /* 9 */ DECODE(CLT.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, /* 10 */ ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, DECODE(CLT.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CLT.AMOUNT
, /* 11 */ ABA.CURRENCY_CODE
, CLT.AMOUNT
, NVL(CLT.ORIGINAL_AMOUNT
, CLT.AMOUNT))
, CLT.AMOUNT
, /* 12 */ CRE.AMOUNT
, /* 13 */ CH.GL_DATE
, /* 14 */ L2.MEANING
, /* 15 */ CLT.STATUS
, /* 16 */ CLT.TRX_DATE
, /* 17 */ CLT.TRX_DATE
, /* 18 */ CLT.EFFECTIVE_DATE
, /* 19 */ CLT.EXCHANGE_RATE_DATE
, /* 20 */ GDC.USER_CONVERSION_TYPE
, /* 21 */ CLT.EXCHANGE_RATE
, /* 22 */ CLT.CHARGES_AMOUNT
, /* 23 */ TO_NUMBER(NULL)
, /* 24 */ NULL
, /* 25 */ TO_NUMBER(NULL)
, /* 26 */ NULL
, /* 27 */ NULL
, /* 28 */ NULL
, /* 29 */ TO_NUMBER(NULL)
, /* 30 */ TO_NUMBER(NULL)
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33*/ CLT.STATEMENT_HEADER_ID
, /* 34 */ CRE.CREATION_DATE
, /* 35 */ CRE.CREATED_BY
, /* 36 */ CRE.LAST_UPDATE_DATE
, /* 37 */ CRE.LAST_UPDATED_BY
, /* 38 */ CLL.STATEMENT_HEADER_ID
, /* 39 */ TO_NUMBER(NULL)
, /* 40 */ ABA.BANK_BRANCH_ID
, /* 41 */ TO_DATE(NULL)
, /* 42 */ NULL
, /* 43 */ TO_DATE(NULL)
, /* 44 */ L3.MEANING
, /* 45 */ CLL.LINE_NUMBER
, /* 46 */ CLL.TRX_DATE
, /* 47 */ CLL.AMOUNT
, /* 48 */ CLL.STATUS
, /* 49 */ TC.DESCRIPTION
, /* 50 */ CH.STATEMENT_NUMBER
, /* 51 */ CH.STATEMENT_DATE
, /* 52 */ ABA.BANK_ACCOUNT_NAME
, /* 53 */ ABA.BANK_ACCOUNT_NUM
, /* 54 */ ABA.CURRENCY_CODE
, /* 55 */ CH.DOC_SEQUENCE_VALUE
, /* 56 */ CLL.TRX_TYPE
, /* 57 */ NULL
, /* 58 */ CH.CONTROL_END_BALANCE
, /* 59 */ 260
, /* 60 */ 1
, /* 61 */ CRE.AMOUNT
, /* 62 */ 'STATEMENT'
, /* 63 */ CH.STATEMENT_COMPLETE_FLAG
, /* 64 */ CRE.REQUEST_ID
, /* 65 */ TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM CE_BANK_ACCTS_GT_V ABA
, CE_STATEMENT_LINES CLT
, CE_STATEMENT_LINES CLL
, CE_STATEMENT_HEADERS CH
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_TRANSACTION_CODES TC
, CE_LOOKUPS LK
, CE_LOOKUPS L2
, CE_LOOKUPS L3
, GL_DAILY_CONVERSION_TYPES GDC
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
WHERE LK.LOOKUP_CODE = DECODE(CLT.TRX_TYPE
, 'MISC_DEBIT'
, 'DEBIT'
, 'MISC_CREDIT'
, 'CREDIT'
, CLT.TRX_TYPE)
AND LK.LOOKUP_TYPE = 'AVAILABLE_STMT_TYPE'
AND L2.LOOKUP_CODE = CLT.STATUS
AND L2.LOOKUP_TYPE = 'STATEMENT_LINE_STATUS'
AND L3.LOOKUP_CODE = CLL.TRX_TYPE
AND L3.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND TC.TRANSACTION_CODE_ID (+) = CLL.TRX_CODE_ID
AND GDC.CONVERSION_TYPE(+) = CLT.EXCHANGE_RATE_TYPE
AND ABA.BANK_ACCOUNT_ID = CH.BANK_ACCOUNT_ID
AND CH.STATEMENT_HEADER_ID = CLL.STATEMENT_HEADER_ID
AND CRE.STATEMENT_LINE_ID = CLL.STATEMENT_LINE_ID
AND CRE.REFERENCE_ID = CLT. STATEMENT_LINE_ID
AND CRE.REFERENCE_TYPE = 'STATEMENT'
AND CRE.STATUS_FLAG = 'M'
AND CRE.CURRENT_RECORD_FLAG = 'Y'
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID