VIEW: APPS.CE_185_RECONCILED_V

Source

View Text - Preformatted

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 (nvl(DDA.deal_type,'X') <> 'RTMM' OR (DDA.deal_type = 'RTMM' AND NOT (DDA.amount_type='PRINFLW' AND DDA.date_type='SETTLE')))

View Text - HTML Formatted

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 (NVL(DDA.DEAL_TYPE
, 'X') <> 'RTMM' OR (DDA.DEAL_TYPE = 'RTMM'
AND NOT (DDA.AMOUNT_TYPE='PRINFLW'
AND DDA.DATE_TYPE='SETTLE')))