View: CE_185_RECONCILED_V

Product:	CE - Cash Management
Description:	Reconciled Treasury transactions for unreconciling via Open Interface
Implementation/DBA Data:	APPS.CE_185_RECONCILED_V

View Text

SELECT T.ROWID
, /* 1 */ 'N'
, /* 2 */ SL.STATEMENT_LINE_ID
, /* 3 */ ABA.BANK_ACCOUNT_ID
, /* 4 */ T.SETTLEMENT_SUMMARY_ID
, /* 5 */ DECODE(SIGN(T.SETTLEMENT_AMOUNT)
, 1
, 'CASH'
, 'PAYMENT')
, /* 6 */ DECODE(SIGN(T.SETTLEMENT_AMOUNT)
, 1
, 'CASH'
, 'PAYMENT')
, /* 7 */ TO_CHAR(T.SETTLEMENT_NUMBER)
, /* 8 */ T.CURRENCY
, /* 9 */ DECODE(T.CURRENCY
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, /* 10 */ ABS(T.SETTLEMENT_AMOUNT)
, /* 11 */ DECODE(T.CURRENCY
, SOB.CURRENCY_CODE
, ABS(T.SETTLEMENT_AMOUNT)
, /* 12 */ ABA.CURRENCY_CODE
, ABS(T.SETTLEMENT_AMOUNT)
, ABS(T.SETTLEMENT_AMOUNT))
, DECODE(T.CURRENCY
, SOB.CURRENCY_CODE
, ABS(T.SETTLEMENT_AMOUNT)
, /* 13 */ ABA.CURRENCY_CODE
, ABS(T.SETTLEMENT_AMOUNT)
, ABS(T.SETTLEMENT_AMOUNT))
, T.SETTLEMENT_DATE
, /* 14 */ DECODE(T.STATUS
, 'A'
, NVL(ABA.RECON_OI_FLOAT_STATUS
, '#')
, 'R'
, NVL(ABA.RECON_OI_CLEARED_STATUS
, '#'))
, /* 15 */ DECODE(T.STATUS
, 'A'
, NVL(ABA.RECON_OI_FLOAT_STATUS
, '#')
, 'R'
, NVL(ABA.RECON_OI_CLEARED_STATUS
, '#'))
, /* 16 */ T.SETTLEMENT_DATE
, /* 17 */ T.SETTLEMENT_DATE
, /* 18 */ TO_DATE(NULL)
, /* 19 */ TO_DATE(NULL)
, /* 20 */ NULL
, /* 21 */ TO_NUMBER(NULL)
, /* 22 */ TO_NUMBER(NULL)
, /* 23 */ TO_NUMBER(NULL)
, /* 24 */ TO_CHAR(DDA.DEAL_NUMBER) || '/' ||TO_CHAR(DDA.TRANSACTION_NUMBER)
, /* 25 */ TO_NUMBER(NULL)
, /* 26 */ NULL
, /* 27 */ NULL
, /* 28 */ NULL
, /* 29 */ TO_NUMBER(NULL)
, /* 30 */ TO_NUMBER(NULL)
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33 */ TO_NUMBER(NULL)
, /* 34 */ T.CREATION_DATE
, /* 35 */ T.CREATED_BY
, /* 36 */ T.LAST_UPDATE_DATE
, /* 37 */ T.LAST_UPDATED_BY
, /* 38 */ SL.STATEMENT_HEADER_ID
, /* 39 */ TO_NUMBER(NULL)
, /* 40 */ ABA.BANK_BRANCH_ID
, /* 41 */ TO_DATE(NULL)
, /* 42 */ NULL
, /* 43 */ TO_DATE(NULL)
, /* 44 */ L1.MEANING
, /* 45 */ SL.LINE_NUMBER
, /* 46 */ SL.TRX_DATE
, /* 47 */ SL.AMOUNT
, /* 48 */ SL.STATUS
, /* 49 */ TC.DESCRIPTION
, /* 50 */ SH.STATEMENT_NUMBER
, /* 51 */ SH.STATEMENT_DATE
, /* 52 */ ABA.BANK_ACCOUNT_NAME
, /* 53 */ ABA.BANK_ACCOUNT_NUM
, /* 54 */ ABA.CURRENCY_CODE
, /* 55 */ SH.DOC_SEQUENCE_VALUE
, /* 56 */ SL.TRX_TYPE
, /* 57 */ NULL
, /* 58 */ SH.CONTROL_END_BALANCE
, /* 59 */ 185
, /* 60 */ 1
, /* 61 */ DECODE(DECODE(SIGN(T.SETTLEMENT_AMOUNT)
, 1
, 'CASH'
, 'PAYMENT')
, /* 62 */ 'CASH'
, DECODE(T.CURRENCY
, SOB.CURRENCY_CODE
, ABS(T.SETTLEMENT_AMOUNT)
, ABA.CURRENCY_CODE
, ABS(T.SETTLEMENT_AMOUNT)
, 0)
, 'PAYMENT'
, DECODE(T.CURRENCY
, SOB.CURRENCY_CODE
, ABS(T.SETTLEMENT_AMOUNT)
, ABA.CURRENCY_CODE
, ABS(T.SETTLEMENT_AMOUNT)
, 0))
, 'XTR_LINE'
, /* 63 */ BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, CRE.LEGAL_ENTITY_ID
FROM CE_LOOKUPS L1
, CE_STATEMENT_HEADERS SH
, CE_TRANSACTION_CODES TC
, GL_LEDGERS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_SECURITY_PROFILES_GT LE
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_ALL BAU
, XTR_SETTLEMENT_SUMMARY T
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_STATEMENT_LINES SL
, XTR_DEAL_DATE_AMOUNTS DDA
WHERE TC.TRANSACTION_CODE_ID(+) = SL.TRX_CODE_ID
AND SOB.LEDGER_ID = SYS.SET_OF_BOOKS_ID
AND SOB.OBJECT_TYPE_CODE = 'L'
AND NVL(SOB.COMPLETE_FLAG
, 'Y') = 'Y'
AND L1.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND L1.LOOKUP_CODE = SL.TRX_TYPE
AND T.SETTLEMENT_SUMMARY_ID = CRE.REFERENCE_ID
AND T.SETTLEMENT_NUMBER = DDA.SETTLEMENT_NUMBER (+)
AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_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.XTR_USE_ENABLE_FLAG = 'Y'
AND BAU.LEGAL_ENTITY_ID = CRE.LEGAL_ENTITY_ID
AND BAU.LEGAL_ENTITY_ID = SYS.LEGAL_ENTITY_ID*/
AND LE.ORGANIZATION_ID = SYS.LEGAL_ENTITY_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = CRE.LEGAL_ENTITY_ID
AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID
AND CRE.CURRENT_RECORD_FLAG = 'Y'
AND CRE.REFERENCE_TYPE = 'XTR_LINE'
AND CRE.STATUS_FLAG = 'M'
AND (DDA.DEAL_TYPE <> 'RTMM' OR (DDA.DEAL_TYPE = 'RTMM'
AND NOT (DDA.AMOUNT_TYPE='PRINFLW'
AND DDA.DATE_TYPE='SETTLE')))

Columns