VIEW: APPS.CE_260_CF_RECONCILED_V

Source

View Text - Preformatted

SELECT CC.ROWID, /* 1 */ 'N', /* 2 */ CLL.STATEMENT_LINE_ID, /* 3 */ ABA.BANK_ACCOUNT_ID, /* 4 */ CC.CASHFLOW_ID, /* 5 */ CC.CASHFLOW_DIRECTION, /* 6 TRX_TYPE*/ LK.MEANING, /* 7 */ CC.BANK_TRXN_NUMBER, /* 8 TRX_NUMBER*/ CC.CASHFLOW_CURRENCY_CODE, /* 9 */ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, /* 10 */ 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), CC.CASHFLOW_AMOUNT, /* 11 */ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, CC.CASHFLOW_AMOUNT, ABA.CURRENCY_CODE, CC.CASHFLOW_AMOUNT, nvl(cc.BASE_AMOUNT,decode(fc.MINIMUM_ACCOUNTABLE_UNIT, null, round((CC.CASHFLOW_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1)), nvl(fc.PRECISION,2)), (round(((CC.CASHFLOW_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1))/fc.MINIMUM_ACCOUNTABLE_UNIT),0) * fc.MINIMUM_ACCOUNTABLE_UNIT)))), /* 14 BANK_ACCOUNT_AMOUNT*/ CRE.AMOUNT, /* 13 */ nvl(CCH.ACCOUNTING_DATE,cc.cleared_date), /* 14 */ L2.MEANING, /* 15 */ CC.CASHFLOW_STATUS_CODE, /* 16 */ CC.CASHFLOW_DATE, /* 17 */ CC.CLEARED_DATE, /* 18 */ TO_DATE(NULL), /* 19 maturity_date?*/ CC.CLEARED_EXCHANGE_DATE, /* 20 */ CC.CLEARED_EXCHANGE_RATE_TYPE, /* 21 */ CC.CLEARED_EXCHANGE_RATE, /* 22 */ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, CC.CLEARING_CHARGES_AMOUNT, ABA.CURRENCY_CODE, CC.CLEARING_CHARGES_AMOUNT, decode(fc.MINIMUM_ACCOUNTABLE_UNIT, null, round((CC.CLEARING_CHARGES_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1)), nvl(fc.PRECISION,2)), (round(((CC.CLEARING_CHARGES_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1))/fc.MINIMUM_ACCOUNTABLE_UNIT),0) * fc.MINIMUM_ACCOUNTABLE_UNIT))), /* 23 */ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, CC.CLEARING_ERROR_AMOUNT, ABA.CURRENCY_CODE, CC.CLEARING_ERROR_AMOUNT, decode(fc.MINIMUM_ACCOUNTABLE_UNIT, null, round((CC.CLEARING_ERROR_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1)), nvl(fc.PRECISION,2)), (round(((CC.CLEARING_ERROR_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1))/fc.MINIMUM_ACCOUNTABLE_UNIT),0) * fc.MINIMUM_ACCOUNTABLE_UNIT))), /* 24 */ to_char(pay.payment_instruction_id), /* 25 batch_name */ TO_NUMBER(pay.payment_instruction_id), /* 26 batch_id*/ NULL, /* 27 */ NULL, /* 28 */ NULL, /* 29 */ TO_NUMBER(NULL), /* 30 */ TO_NUMBER(NULL), /* 31 */ NULL, /* 32 */ NULL, /* 33*/ TO_NUMBER(cc.cashflow_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 */ to_char(CC.CASHFLOW_ID), /* 58 REMITTANCE_NUMBER*/ CH.CONTROL_END_BALANCE, /* 59 */ 260, /* 60 */ 1, /* 61 */ CRE.AMOUNT, /* 62 */ 'CASHFLOW', /* 63 */ CH.STATEMENT_COMPLETE_FLAG, /* 64 */ CRE.REQUEST_ID, /* 65 */ CC.ACTUAL_VALUE_DATE, TO_NUMBER(NULL), TO_NUMBER(NULL), cc.CASHFLOW_LEGAL_ENTITY_ID , CC.CASHFLOW_ID FROM CE_BANK_ACCTS_GT_V ABA, CE_STATEMENT_LINES CLL, CE_STATEMENT_HEADERS CH, CE_STATEMENT_RECONCILS_ALL CRE, CE_TRANSACTION_CODES TC, CE_SECURITY_PROFILES_GT LE, CE_CASHFLOWS CC, CE_CASHFLOW_ACCT_H CCH, FND_CURRENCIES fc, CE_LOOKUPS LK, CE_LOOKUPS L2, CE_LOOKUPS L3, gl_ledgers SOB, CE_SYSTEM_PARAMETERS SYS, ce_payment_transactions pt, iby_fd_docs_payable_v docs, iby_fd_payments_v pay WHERE LK.LOOKUP_CODE = CC.CASHFLOW_DIRECTION AND LK.LOOKUP_TYPE = 'CE_CASHFLOW_DIRECTION_TYPE' AND L2.LOOKUP_CODE = CC.CASHFLOW_STATUS_CODE AND L2.LOOKUP_TYPE = 'CASHFLOW_STATUS_CODE' AND L3.LOOKUP_CODE = CLL.TRX_TYPE AND L3.LOOKUP_TYPE = 'BANK_TRX_TYPE' AND TC.TRANSACTION_CODE_ID (+) = CLL.TRX_CODE_ID and nvl(tc.RECONCILE_FLAG(+),'CE') = 'CE' AND ABA.BANK_ACCOUNT_ID = CH.BANK_ACCOUNT_ID AND ABA.BANK_ACCOUNT_ID = CC.CASHFLOW_BANK_ACCOUNT_ID AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY' AND CCH.CASHFLOW_ID (+) = CC.CASHFLOW_ID AND CCH.CURRENT_RECORD_FLAG = 'Y' AND CCH.EVENT_TYPE in ('CE_STMT_RECORDED', 'CE_BAT_CLEARED') AND CC.CASHFLOW_STATUS_CODE = 'RECONCILED' AND CC.CASHFLOW_CURRENCY_CODE = FC.CURRENCY_CODE AND CH.STATEMENT_HEADER_ID = CLL.STATEMENT_HEADER_ID AND CRE.STATEMENT_LINE_ID = CLL.STATEMENT_LINE_ID AND CRE.REFERENCE_ID = CC.CASHFLOW_ID AND CRE.REFERENCE_TYPE = 'CASHFLOW' AND CRE.STATUS_FLAG = 'M' AND CRE.CURRENT_RECORD_FLAG = 'Y' AND cc.CASHFLOW_LEGAL_ENTITY_ID = ABA.ACCOUNT_OWNER_ORG_ID AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND SOB.LEDGER_ID (+) = SYS.SET_OF_BOOKS_ID and sob.object_type_code = 'L' AND nvl(sob.complete_flag, 'Y') = 'Y' AND pt.trxn_reference_number (+) = cc.trxn_reference_number AND docs.calling_app_doc_ref_number(+) = pt.trxn_reference_number and pay.payment_id (+) = docs.payment_id and docs.CALLING_APP_ID (+) = 260

View Text - HTML Formatted

SELECT CC.ROWID
, /* 1 */ 'N'
, /* 2 */ CLL.STATEMENT_LINE_ID
, /* 3 */ ABA.BANK_ACCOUNT_ID
, /* 4 */ CC.CASHFLOW_ID
, /* 5 */ CC.CASHFLOW_DIRECTION
, /* 6 TRX_TYPE*/ LK.MEANING
, /* 7 */ CC.BANK_TRXN_NUMBER
, /* 8 TRX_NUMBER*/ CC.CASHFLOW_CURRENCY_CODE
, /* 9 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 10 */ 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, CC.CASHFLOW_AMOUNT
, /* 11 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, CC.CASHFLOW_AMOUNT
, ABA.CURRENCY_CODE
, CC.CASHFLOW_AMOUNT
, NVL(CC.BASE_AMOUNT
, DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND((CC.CASHFLOW_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))
, NVL(FC.PRECISION
, 2))
, (ROUND(((CC.CASHFLOW_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))/FC.MINIMUM_ACCOUNTABLE_UNIT)
, 0) * FC.MINIMUM_ACCOUNTABLE_UNIT))))
, /* 14 BANK_ACCOUNT_AMOUNT*/ CRE.AMOUNT
, /* 13 */ NVL(CCH.ACCOUNTING_DATE
, CC.CLEARED_DATE)
, /* 14 */ L2.MEANING
, /* 15 */ CC.CASHFLOW_STATUS_CODE
, /* 16 */ CC.CASHFLOW_DATE
, /* 17 */ CC.CLEARED_DATE
, /* 18 */ TO_DATE(NULL)
, /* 19 MATURITY_DATE?*/ CC.CLEARED_EXCHANGE_DATE
, /* 20 */ CC.CLEARED_EXCHANGE_RATE_TYPE
, /* 21 */ CC.CLEARED_EXCHANGE_RATE
, /* 22 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, CC.CLEARING_CHARGES_AMOUNT
, ABA.CURRENCY_CODE
, CC.CLEARING_CHARGES_AMOUNT
, DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND((CC.CLEARING_CHARGES_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))
, NVL(FC.PRECISION
, 2))
, (ROUND(((CC.CLEARING_CHARGES_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))/FC.MINIMUM_ACCOUNTABLE_UNIT)
, 0) * FC.MINIMUM_ACCOUNTABLE_UNIT)))
, /* 23 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, CC.CLEARING_ERROR_AMOUNT
, ABA.CURRENCY_CODE
, CC.CLEARING_ERROR_AMOUNT
, DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND((CC.CLEARING_ERROR_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))
, NVL(FC.PRECISION
, 2))
, (ROUND(((CC.CLEARING_ERROR_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))/FC.MINIMUM_ACCOUNTABLE_UNIT)
, 0) * FC.MINIMUM_ACCOUNTABLE_UNIT)))
, /* 24 */ TO_CHAR(PAY.PAYMENT_INSTRUCTION_ID)
, /* 25 BATCH_NAME */ TO_NUMBER(PAY.PAYMENT_INSTRUCTION_ID)
, /* 26 BATCH_ID*/ NULL
, /* 27 */ NULL
, /* 28 */ NULL
, /* 29 */ TO_NUMBER(NULL)
, /* 30 */ TO_NUMBER(NULL)
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33*/ TO_NUMBER(CC.CASHFLOW_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 */ TO_CHAR(CC.CASHFLOW_ID)
, /* 58 REMITTANCE_NUMBER*/ CH.CONTROL_END_BALANCE
, /* 59 */ 260
, /* 60 */ 1
, /* 61 */ CRE.AMOUNT
, /* 62 */ 'CASHFLOW'
, /* 63 */ CH.STATEMENT_COMPLETE_FLAG
, /* 64 */ CRE.REQUEST_ID
, /* 65 */ CC.ACTUAL_VALUE_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CC.CASHFLOW_LEGAL_ENTITY_ID
, CC.CASHFLOW_ID
FROM CE_BANK_ACCTS_GT_V ABA
, CE_STATEMENT_LINES CLL
, CE_STATEMENT_HEADERS CH
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_TRANSACTION_CODES TC
, CE_SECURITY_PROFILES_GT LE
, CE_CASHFLOWS CC
, CE_CASHFLOW_ACCT_H CCH
, FND_CURRENCIES FC
, CE_LOOKUPS LK
, CE_LOOKUPS L2
, CE_LOOKUPS L3
, GL_LEDGERS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_PAYMENT_TRANSACTIONS PT
, IBY_FD_DOCS_PAYABLE_V DOCS
, IBY_FD_PAYMENTS_V PAY
WHERE LK.LOOKUP_CODE = CC.CASHFLOW_DIRECTION
AND LK.LOOKUP_TYPE = 'CE_CASHFLOW_DIRECTION_TYPE'
AND L2.LOOKUP_CODE = CC.CASHFLOW_STATUS_CODE
AND L2.LOOKUP_TYPE = 'CASHFLOW_STATUS_CODE'
AND L3.LOOKUP_CODE = CLL.TRX_TYPE
AND L3.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND TC.TRANSACTION_CODE_ID (+) = CLL.TRX_CODE_ID
AND NVL(TC.RECONCILE_FLAG(+)
, 'CE') = 'CE'
AND ABA.BANK_ACCOUNT_ID = CH.BANK_ACCOUNT_ID
AND ABA.BANK_ACCOUNT_ID = CC.CASHFLOW_BANK_ACCOUNT_ID
AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID
AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY'
AND CCH.CASHFLOW_ID (+) = CC.CASHFLOW_ID
AND CCH.CURRENT_RECORD_FLAG = 'Y'
AND CCH.EVENT_TYPE IN ('CE_STMT_RECORDED'
, 'CE_BAT_CLEARED')
AND CC.CASHFLOW_STATUS_CODE = 'RECONCILED'
AND CC.CASHFLOW_CURRENCY_CODE = FC.CURRENCY_CODE
AND CH.STATEMENT_HEADER_ID = CLL.STATEMENT_HEADER_ID
AND CRE.STATEMENT_LINE_ID = CLL.STATEMENT_LINE_ID
AND CRE.REFERENCE_ID = CC.CASHFLOW_ID
AND CRE.REFERENCE_TYPE = 'CASHFLOW'
AND CRE.STATUS_FLAG = 'M'
AND CRE.CURRENT_RECORD_FLAG = 'Y'
AND CC.CASHFLOW_LEGAL_ENTITY_ID = ABA.ACCOUNT_OWNER_ORG_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND SOB.LEDGER_ID (+) = SYS.SET_OF_BOOKS_ID
AND SOB.OBJECT_TYPE_CODE = 'L'
AND NVL(SOB.COMPLETE_FLAG
, 'Y') = 'Y'
AND PT.TRXN_REFERENCE_NUMBER (+) = CC.TRXN_REFERENCE_NUMBER
AND DOCS.CALLING_APP_DOC_REF_NUMBER(+) = PT.TRXN_REFERENCE_NUMBER
AND PAY.PAYMENT_ID (+) = DOCS.PAYMENT_ID
AND DOCS.CALLING_APP_ID (+) = 260