View: CE_260_CF_RECONCILED_V

Product:	CE - Cash Management
Description:	Reconciled cashflow receipts and payments for unreconciling
Implementation/DBA Data:	APPS.CE_260_CF_RECONCILED_V

View Text

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

Columns