View: CE_STATEMENT_LINES_V

Product:	CE - Cash Management
Description:	- Retrofitted
Implementation/DBA Data:	APPS.CE_STATEMENT_LINES_V

View Text

SELECT SL.ROWID ROW_ID
, SL.STATEMENT_LINE_ID STATEMENT_LINE_ID
, SL.STATEMENT_HEADER_ID STATEMENT_HEADER_ID
, SL.LINE_NUMBER LINE_NUMBER
, SL.TRX_DATE TRX_DATE
, SL.TRX_TYPE TRX_TYPE
, L.MEANING TRX_TYPE_DSP
, SL.TRX_CODE_ID TRX_CODE_ID
, SL.TRX_CODE TRX_CODE_ID_DSP
, SL.EFFECTIVE_DATE EFFECTIVE_DATE
, SL.BANK_TRX_NUMBER BANK_TRX_NUMBER
, SL.TRX_TEXT TRX_TEXT
, SL.CUSTOMER_TEXT CUSTOMER_TEXT
, SL.INVOICE_TEXT INVOICE_TEXT
, SL.BANK_ACCOUNT_TEXT BANK_ACCOUNT_TEXT
, SL.AMOUNT AMOUNT
, SL.CHARGES_AMOUNT CHARGES_AMOUNT
, SL.ORIGINAL_AMOUNT ORIGINAL_AMOUNT
, SL.EXCHANGE_RATE EXCHANGE_RATE
, DECODE(CE_AUTO_BANK_MATCH.GET_INVERSE_RATE
, 'N'
, EXCHANGE_RATE
, 'Y'
, DECODE(NVL(EXCHANGE_RATE
, 0)
, 0
, NULL
, 1/EXCHANGE_RATE)) EXCHANGE_RATE_DSP
, SL.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, GCT.USER_CONVERSION_TYPE USER_EXCHANGE_RATE_TYPE
, SL.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE
, SL.CURRENCY_CODE CURRENCY_CODE
, NULL TRX_CURRENCY_TYPE
, SL.STATUS STATUS
, L2.MEANING STATUS_DSP
, SL.CREATED_BY CREATED_BY
, SL.CREATION_DATE CREATION_DATE
, SL.LAST_UPDATED_BY LAST_UPDATED_BY
, SL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SL.RECONCILE_TO_STATEMENT_FLAG RECONCILE_TO_STATEMENT_LINE
, SL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, SL.ATTRIBUTE1 ATTRIBUTE1
, SL.ATTRIBUTE2 ATTRIBUTE2
, SL.ATTRIBUTE3 ATTRIBUTE3
, SL.ATTRIBUTE4 ATTRIBUTE4
, SL.ATTRIBUTE5 ATTRIBUTE5
, SL.ATTRIBUTE6 ATTRIBUTE6
, SL.ATTRIBUTE7 ATTRIBUTE7
, SL.ATTRIBUTE8 ATTRIBUTE8
, SL.ATTRIBUTE9 ATTRIBUTE9
, SL.ATTRIBUTE10 ATTRIBUTE10
, SL.ATTRIBUTE11 ATTRIBUTE11
, SL.ATTRIBUTE12 ATTRIBUTE12
, SL.ATTRIBUTE13 ATTRIBUTE13
, SL.ATTRIBUTE14 ATTRIBUTE14
, SL.ATTRIBUTE15 ATTRIBUTE15
, DECODE(SL.TRX_TYPE
, 'MISC_DEBIT'
, SL.AMOUNT
, 'DEBIT'
, SL.AMOUNT
, 'REJECTED'
, SL.AMOUNT
, 'NSF'
, SL.AMOUNT
, 'SWEEP_OUT'
, SL.AMOUNT
, NULL) DEBIT_AMOUNT
, DECODE(SL.TRX_TYPE
, 'MISC_CREDIT'
, SL.AMOUNT
, 'CREDIT'
, SL.AMOUNT
, 'STOP'
, SL.AMOUNT
, 'SWEEP_IN'
, SL.AMOUNT
, NULL) CREDIT_AMOUNT
, DECODE(SL.TRX_TYPE
, 'MISC_DEBIT'
, 1
, 'DEBIT'
, 1
, 'REJECTED'
, 1
, 'NSF'
, 1
, 'SWEEP_OUT'
, 1
, NULL) DEBIT_COUNT
, DECODE(SL.TRX_TYPE
, 'MISC_CREDIT'
, 1
, 'CREDIT'
, 1
, 'STOP'
, 1
, 'SWEEP_IN'
, 1
, NULL) CREDIT_COUNT
, DECODE(SL.STATUS
, 'UNRECONCILED'
, SL.AMOUNT
, 'ERROR'
, SL.AMOUNT
, NULL) UNRECONCILED_AMOUNT
, DECODE(SL.STATUS
, 'UNRECONCILED'
, DECODE( SL.TRX_TYPE
, 'MISC_DEBIT'
, -1*SL.AMOUNT
, 'DEBIT'
, -1*SL.AMOUNT
, 'REJECTED'
, -1*SL.AMOUNT
, 'NSF'
, -1*SL.AMOUNT
, 'SWEEP_OUT'
, - 1 * SL.AMOUNT
, SL.AMOUNT )
, 'ERROR'
, DECODE(SL.TRX_TYPE
, 'MISC_DEBIT'
, -1*SL.AMOUNT
, 'DEBIT'
, -1*SL.AMOUNT
, 'REJECTED'
, -1*SL.AMOUNT
, 'NSF'
, -1*SL.AMOUNT
, 'SWEEP_OUT'
, -1 * SL.AMOUNT
, SL.AMOUNT )
, TO_NUMBER(NULL)) SIGNED_UNREC_AMOUNT
, DECODE(SL.STATUS
, 'UNRECONCILED'
, 1
, 'ERROR'
, 1
, NULL) UNRECONCILED_COUNT
, SL.TRX_CODE
, SL.CASHFLOW_ID
, SL.JE_STATUS_FLAG
, SL.GLOBAL_ATTRIBUTE_CATEGORY
, SL.GLOBAL_ATTRIBUTE1
, SL.GLOBAL_ATTRIBUTE2
, SL.GLOBAL_ATTRIBUTE3
, SL.GLOBAL_ATTRIBUTE4
, SL.GLOBAL_ATTRIBUTE5
, SL.GLOBAL_ATTRIBUTE6
, SL.GLOBAL_ATTRIBUTE7
, SL.GLOBAL_ATTRIBUTE8
, SL.GLOBAL_ATTRIBUTE9
, SL.GLOBAL_ATTRIBUTE10
, SL.GLOBAL_ATTRIBUTE11
, SL.GLOBAL_ATTRIBUTE12
, SL.GLOBAL_ATTRIBUTE13
, SL.GLOBAL_ATTRIBUTE14
, SL.GLOBAL_ATTRIBUTE15
, SL.GLOBAL_ATTRIBUTE16
, SL.GLOBAL_ATTRIBUTE17
, SL.GLOBAL_ATTRIBUTE18
, SL.GLOBAL_ATTRIBUTE19
, SL.GLOBAL_ATTRIBUTE20
FROM CE_LOOKUPS L2
, CE_LOOKUPS L
, GL_DAILY_CONVERSION_TYPES GCT
, CE_BANK_ACCTS_GT_V BA
, CE_STATEMENT_HEADERS SH
, CE_STATEMENT_LINES SL
WHERE L2.LOOKUP_TYPE = 'STATEMENT_LINE_STATUS'
AND GCT.CONVERSION_TYPE(+) = SL.EXCHANGE_RATE_TYPE
AND L2.LOOKUP_CODE = SL.STATUS
AND BA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID
AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND L.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND L.LOOKUP_CODE = SL.TRX_TYPE

Columns