eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CE_260_CF_TRANSACTIONS_V

Source

View Text - Preformatted


SELECT CC.ROWID, /* 1 */ 'N', /* 2 */ ABA.BANK_ACCOUNT_ID, /* 3 */ ABA.BANK_ACCOUNT_NAME, /* 4 */ ABA.BANK_ACCOUNT_NUM, /* 5 */ ABB.BANK_NAME, /* 6 */ ABB.BANK_BRANCH_NAME, /* 7 */ CC.CASHFLOW_ID, /* 8 */ CC.CASHFLOW_DIRECTION, /* 9 TRX_TYPE*/ LK.MEANING, /* 10 */ CC.BANK_TRXN_NUMBER, /* 11 TRX_NUMBER*/ CC.CASHFLOW_CURRENCY_CODE, /* 12 */ CC.CASHFLOW_AMOUNT, /* 13 */ 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*/ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, CC.CLEARED_AMOUNT, ABA.CURRENCY_CODE, CC.CLEARED_AMOUNT, decode(fc.MINIMUM_ACCOUNTABLE_UNIT, null, round((CC.CLEARED_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1)), nvl(fc.PRECISION,2)), (round(((CC.CLEARED_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1))/fc.MINIMUM_ACCOUNTABLE_UNIT),0) * fc.MINIMUM_ACCOUNTABLE_UNIT))), /* 15 AMOUNT_CLEARED */ nvl(CCH.ACCOUNTING_DATE, nvl(cc.cleared_date,cc.cashflow_date)), /* 16 GL_DATE */ L2.MEANING, /* 17 */ CC.CASHFLOW_STATUS_CODE, /* 18 */ CC.CASHFLOW_DATE, /* 19 */ CC.CLEARED_DATE, /* 20 */ TO_DATE(NULL), /* 21 maturity_date*/ CC.CASHFLOW_EXCHANGE_DATE, /* 22 */ CC.CASHFLOW_EXCHANGE_RATE_TYPE, /* 23 */ GLCC.USER_CONVERSION_TYPE, /* 24 */ CC.CASHFLOW_EXCHANGE_RATE, /* 25 */ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, DECODE(CC.CASHFLOW_STATUS_CODE, 'CLEARED', CC.CLEARING_CHARGES_AMOUNT), ABA.CURRENCY_CODE, DECODE(CC.CASHFLOW_STATUS_CODE, 'CLEARED', CC.CLEARING_CHARGES_AMOUNT), DECODE(CC.CASHFLOW_STATUS_CODE, 'CLEARED', 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)))), /* 26 */ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, DECODE(CC.CASHFLOW_STATUS_CODE, 'CLEARED', CC.CLEARING_ERROR_AMOUNT), ABA.CURRENCY_CODE, DECODE(CC.CASHFLOW_STATUS_CODE, 'CLEARED', CC.CLEARING_ERROR_AMOUNT), DECODE(CC.CASHFLOW_STATUS_CODE, 'CLEARED', 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)))), /* 27 */ (select to_char(pay.payment_instruction_id) from iby_fd_docs_payable_v docs, iby_fd_payments_v pay where docs.calling_app_doc_ref_number = cc.trxn_reference_number and pay.payment_id = docs.payment_id and docs.CALLING_APP_ID = 260 and docs.completed_pmts_group_id is not null and docs.document_status != 'REMOVED_PAYMENT_VOIDED' ), /* 28 batch_name */ (select TO_NUMBER(pay.payment_instruction_id) from iby_fd_docs_payable_v docs, iby_fd_payments_v pay where docs.calling_app_doc_ref_number = cc.trxn_reference_number and pay.payment_id = docs.payment_id and docs.CALLING_APP_ID = 260 and docs.completed_pmts_group_id is not null and docs.document_status != 'REMOVED_PAYMENT_VOIDED' ), /* 29 batch_id*/ NULL, /* 30 */ NULL, /* 31 */ NULL, /* 32 */ NULL, /* 33 */ CC.COUNTERPARTY_BANK_ACCOUNT_ID, /* 34 customer_id*/ TO_NUMBER(NULL), /* 35 */ NULL, /* 36 */ CC.SOURCE_TRXN_TYPE, /* 37 reference_type*/ TO_NUMBER(cc.cashflow_id), /* 38 reference_id*/ decode(cre.status_flag, 'M', DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, decode(cc.cashflow_direction,'PAYMENT', CC.CLEARED_AMOUNT-NVL(cc.CLEARING_CHARGES_AMOUNT,0)-NVL(cc.CLEARING_ERROR_AMOUNT,0) , CC.CLEARED_AMOUNT+NVL(cc.CLEARING_CHARGES_AMOUNT,0)+NVL(cc.CLEARING_ERROR_AMOUNT,0)) , ABA.CURRENCY_CODE, decode(cc.cashflow_direction,'PAYMENT', CC.CLEARED_AMOUNT-NVL(cc.CLEARING_CHARGES_AMOUNT,0)-NVL(cc.CLEARING_ERROR_AMOUNT,0) , CC.CLEARED_AMOUNT+NVL(cc.CLEARING_CHARGES_AMOUNT,0)+NVL(cc.CLEARING_ERROR_AMOUNT,0)), decode(cc.cashflow_direction,'PAYMENT', decode(fc.MINIMUM_ACCOUNTABLE_UNIT, null, round((CC.CLEARED_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1)), nvl(fc.PRECISION,2)), (round(((CC.CLEARED_AMOUNT * nvl(CC.CASHFLOW_EXCHANGE_RATE,1))/fc.MINIMUM_ACCOUNTABLE_UNIT),0) * fc.MINIMUM_ACCOUNTABLE_UNIT)))- 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))- 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)))), /* 39 ACTUAL_AMOUNT_CLEARED */ CC.CREATION_DATE, /* 40 */ CC.CREATED_BY, /* 41 */ CC.LAST_UPDATE_DATE, /* 42 */ CC.LAST_UPDATED_BY, /* 43 */ to_char(cc.cashflow_id), /* 44 remittance_number*/ TO_NUMBER(NULL), /* 45 */ 260, /* 46 */ 0, /* 47 */ ABA.CURRENCY_CODE, /* 48 */ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, /* 49 */ 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), ABA.ASSET_CODE_COMBINATION_ID, /* 50 */ NULL, /* 51 */ NULL, /* 52 */ TO_NUMBER(NULL), /* 53 */ TO_NUMBER(NULL), /* 54 */ 'CASHFLOW', /* 55 CLEARING_TRX_TYPE*/ NULL, /* 56 */ DECODE(CC.CASHFLOW_CURRENCY_CODE,SOB.CURRENCY_CODE, CC.CASHFLOW_AMOUNT, ABA.CURRENCY_CODE, CC.CASHFLOW_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))), /* 57 BASE_AMOUNT*/ TO_NUMBER(NULL), /* 58 */ TO_NUMBER(NULL), /* 59 */ NULL, /* 60 */ (select TO_DATE(to_char(pt.TRANSACTION_DATE,'YYYY/MM/DD'),'YYYY/MM/DD') from ce_payment_transactions pt where cc.trxn_reference_number = pt.trxn_reference_number) , /* 61 */ NULL, ABA2.BANK_ACCOUNT_NUM, /* 63 BANK_ACCOUNT_TEXT counterparty bank account */ NULL, /* 64 CUSTOMER_TEXT */ NULL, CC.STATEMENT_LINE_ID, /* 66 check_number match ZBA and JEC */ CC.ACTUAL_VALUE_DATE, NULL, cc.CASHFLOW_LEGAL_ENTITY_ID, TO_NUMBER(NULL), nvl(xle.name,cc.customer_text), trxn.transaction_sub_type_name, CC.CASHFLOW_ID FROM CE_BANK_ACCTS_GT_V ABA, CE_BANK_ACCTS_GT_V ABA2, CE_SECURITY_PROFILES_GT LE, CE_BANK_BRANCHES_V ABB, CE_STATEMENT_RECONCILS_ALL CRE, CE_CASHFLOWS CC, CE_CASHFLOW_ACCT_H CCH, FND_CURRENCIES fc, CE_LOOKUPS LK, CE_LOOKUPS L2, GL_DAILY_CONVERSION_TYPES GLCC, GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS, XLE_ENTITY_PROFILES xle, ce_trxns_subtype_codes trxn WHERE LK.LOOKUP_TYPE = 'CE_CASHFLOW_DIRECTION_TYPE' AND LK.LOOKUP_CODE = CC.CASHFLOW_DIRECTION AND L2.LOOKUP_TYPE = 'CASHFLOW_STATUS_CODE' AND L2.LOOKUP_CODE = CC.CASHFLOW_STATUS_CODE AND GLCC.CONVERSION_TYPE(+) = CC.CASHFLOW_EXCHANGE_RATE_TYPE AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID AND ABA.BANK_ACCOUNT_ID = CC.CASHFLOW_BANK_ACCOUNT_ID AND CC.CASHFLOW_STATUS_CODE IN ('CREATED', 'CLEARED') AND CC.CASHFLOW_CURRENCY_CODE = FC.CURRENCY_CODE AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY' AND CRE.REFERENCE_TYPE(+) = 'CASHFLOW' AND NVL(CRE.STATUS_FLAG,'U') = 'U' AND NVL(CRE.CURRENT_RECORD_FLAG,'Y') = 'Y' AND CRE.REFERENCE_ID(+) = CC.CASHFLOW_ID AND CC.CASHFLOW_LEGAL_ENTITY_ID = ABA.ACCOUNT_OWNER_ORG_ID AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND CC.CASHFLOW_DATE >= SYS.CASHBOOK_BEGIN_DATE AND CC.COUNTERPARTY_BANK_ACCOUNT_ID = ABA2.BANK_ACCOUNT_ID (+) AND CC.CASHFLOW_ID = CCH.CASHFLOW_ID AND CCH.CURRENT_RECORD_FLAG = 'Y' AND CCH.EVENT_TYPE in ('CE_STMT_RECORDED', 'CE_BAT_CLEARED', 'CE_BAT_UNCLEARED', 'CE_BAT_CREATED') and ((cc.source_trxn_type ='BAT' AND cc.trxn_reference_number = (select pt.trxn_reference_number from ce_payment_transactions pt where cc.trxn_reference_number = pt.trxn_reference_number and pt.trxn_status_code = 'SETTLED')) or (cc.source_trxn_type = 'STMT')) and cc.counterparty_party_id = xle.party_id(+) and cc.source_trxn_subtype_code_id = trxn.trxn_subtype_code_id(+)

View Text - HTML Formatted

SELECT CC.ROWID
, /* 1 */ 'N'
, /* 2 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ ABA.BANK_ACCOUNT_NAME
, /* 4 */ ABA.BANK_ACCOUNT_NUM
, /* 5 */ ABB.BANK_NAME
, /* 6 */ ABB.BANK_BRANCH_NAME
, /* 7 */ CC.CASHFLOW_ID
, /* 8 */ CC.CASHFLOW_DIRECTION
, /* 9 TRX_TYPE*/ LK.MEANING
, /* 10 */ CC.BANK_TRXN_NUMBER
, /* 11 TRX_NUMBER*/ CC.CASHFLOW_CURRENCY_CODE
, /* 12 */ CC.CASHFLOW_AMOUNT
, /* 13 */ 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*/ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, CC.CLEARED_AMOUNT
, ABA.CURRENCY_CODE
, CC.CLEARED_AMOUNT
, DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND((CC.CLEARED_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))
, NVL(FC.PRECISION
, 2))
, (ROUND(((CC.CLEARED_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))/FC.MINIMUM_ACCOUNTABLE_UNIT)
, 0) * FC.MINIMUM_ACCOUNTABLE_UNIT)))
, /* 15 AMOUNT_CLEARED */ NVL(CCH.ACCOUNTING_DATE
, NVL(CC.CLEARED_DATE
, CC.CASHFLOW_DATE))
, /* 16 GL_DATE */ L2.MEANING
, /* 17 */ CC.CASHFLOW_STATUS_CODE
, /* 18 */ CC.CASHFLOW_DATE
, /* 19 */ CC.CLEARED_DATE
, /* 20 */ TO_DATE(NULL)
, /* 21 MATURITY_DATE*/ CC.CASHFLOW_EXCHANGE_DATE
, /* 22 */ CC.CASHFLOW_EXCHANGE_RATE_TYPE
, /* 23 */ GLCC.USER_CONVERSION_TYPE
, /* 24 */ CC.CASHFLOW_EXCHANGE_RATE
, /* 25 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(CC.CASHFLOW_STATUS_CODE
, 'CLEARED'
, CC.CLEARING_CHARGES_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(CC.CASHFLOW_STATUS_CODE
, 'CLEARED'
, CC.CLEARING_CHARGES_AMOUNT)
, DECODE(CC.CASHFLOW_STATUS_CODE
, 'CLEARED'
, 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))))
, /* 26 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(CC.CASHFLOW_STATUS_CODE
, 'CLEARED'
, CC.CLEARING_ERROR_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(CC.CASHFLOW_STATUS_CODE
, 'CLEARED'
, CC.CLEARING_ERROR_AMOUNT)
, DECODE(CC.CASHFLOW_STATUS_CODE
, 'CLEARED'
, 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))))
, /* 27 */ (SELECT TO_CHAR(PAY.PAYMENT_INSTRUCTION_ID)
FROM IBY_FD_DOCS_PAYABLE_V DOCS
, IBY_FD_PAYMENTS_V PAY
WHERE DOCS.CALLING_APP_DOC_REF_NUMBER = CC.TRXN_REFERENCE_NUMBER
AND PAY.PAYMENT_ID = DOCS.PAYMENT_ID
AND DOCS.CALLING_APP_ID = 260
AND DOCS.COMPLETED_PMTS_GROUP_ID IS NOT NULL
AND DOCS.DOCUMENT_STATUS != 'REMOVED_PAYMENT_VOIDED' )
, /* 28 BATCH_NAME */ (SELECT TO_NUMBER(PAY.PAYMENT_INSTRUCTION_ID)
FROM IBY_FD_DOCS_PAYABLE_V DOCS
, IBY_FD_PAYMENTS_V PAY
WHERE DOCS.CALLING_APP_DOC_REF_NUMBER = CC.TRXN_REFERENCE_NUMBER
AND PAY.PAYMENT_ID = DOCS.PAYMENT_ID
AND DOCS.CALLING_APP_ID = 260
AND DOCS.COMPLETED_PMTS_GROUP_ID IS NOT NULL
AND DOCS.DOCUMENT_STATUS != 'REMOVED_PAYMENT_VOIDED' )
, /* 29 BATCH_ID*/ NULL
, /* 30 */ NULL
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33 */ CC.COUNTERPARTY_BANK_ACCOUNT_ID
, /* 34 CUSTOMER_ID*/ TO_NUMBER(NULL)
, /* 35 */ NULL
, /* 36 */ CC.SOURCE_TRXN_TYPE
, /* 37 REFERENCE_TYPE*/ TO_NUMBER(CC.CASHFLOW_ID)
, /* 38 REFERENCE_ID*/ DECODE(CRE.STATUS_FLAG
, 'M'
, DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, DECODE(CC.CASHFLOW_DIRECTION
, 'PAYMENT'
, CC.CLEARED_AMOUNT-NVL(CC.CLEARING_CHARGES_AMOUNT
, 0)-NVL(CC.CLEARING_ERROR_AMOUNT
, 0)
, CC.CLEARED_AMOUNT+NVL(CC.CLEARING_CHARGES_AMOUNT
, 0)+NVL(CC.CLEARING_ERROR_AMOUNT
, 0))
, ABA.CURRENCY_CODE
, DECODE(CC.CASHFLOW_DIRECTION
, 'PAYMENT'
, CC.CLEARED_AMOUNT-NVL(CC.CLEARING_CHARGES_AMOUNT
, 0)-NVL(CC.CLEARING_ERROR_AMOUNT
, 0)
, CC.CLEARED_AMOUNT+NVL(CC.CLEARING_CHARGES_AMOUNT
, 0)+NVL(CC.CLEARING_ERROR_AMOUNT
, 0))
, DECODE(CC.CASHFLOW_DIRECTION
, 'PAYMENT'
, DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND((CC.CLEARED_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))
, NVL(FC.PRECISION
, 2))
, (ROUND(((CC.CLEARED_AMOUNT * NVL(CC.CASHFLOW_EXCHANGE_RATE
, 1))/FC.MINIMUM_ACCOUNTABLE_UNIT)
, 0) * FC.MINIMUM_ACCOUNTABLE_UNIT)))- 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))- 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))))
, /* 39 ACTUAL_AMOUNT_CLEARED */ CC.CREATION_DATE
, /* 40 */ CC.CREATED_BY
, /* 41 */ CC.LAST_UPDATE_DATE
, /* 42 */ CC.LAST_UPDATED_BY
, /* 43 */ TO_CHAR(CC.CASHFLOW_ID)
, /* 44 REMITTANCE_NUMBER*/ TO_NUMBER(NULL)
, /* 45 */ 260
, /* 46 */ 0
, /* 47 */ ABA.CURRENCY_CODE
, /* 48 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 49 */ 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, ABA.ASSET_CODE_COMBINATION_ID
, /* 50 */ NULL
, /* 51 */ NULL
, /* 52 */ TO_NUMBER(NULL)
, /* 53 */ TO_NUMBER(NULL)
, /* 54 */ 'CASHFLOW'
, /* 55 CLEARING_TRX_TYPE*/ NULL
, /* 56 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, SOB.CURRENCY_CODE
, CC.CASHFLOW_AMOUNT
, ABA.CURRENCY_CODE
, CC.CASHFLOW_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)))
, /* 57 BASE_AMOUNT*/ TO_NUMBER(NULL)
, /* 58 */ TO_NUMBER(NULL)
, /* 59 */ NULL
, /* 60 */ (SELECT TO_DATE(TO_CHAR(PT.TRANSACTION_DATE
, 'YYYY/MM/DD')
, 'YYYY/MM/DD')
FROM CE_PAYMENT_TRANSACTIONS PT
WHERE CC.TRXN_REFERENCE_NUMBER = PT.TRXN_REFERENCE_NUMBER)
, /* 61 */ NULL
, ABA2.BANK_ACCOUNT_NUM
, /* 63 BANK_ACCOUNT_TEXT COUNTERPARTY BANK ACCOUNT */ NULL
, /* 64 CUSTOMER_TEXT */ NULL
, CC.STATEMENT_LINE_ID
, /* 66 CHECK_NUMBER MATCH ZBA
AND JEC */ CC.ACTUAL_VALUE_DATE
, NULL
, CC.CASHFLOW_LEGAL_ENTITY_ID
, TO_NUMBER(NULL)
, NVL(XLE.NAME
, CC.CUSTOMER_TEXT)
, TRXN.TRANSACTION_SUB_TYPE_NAME
, CC.CASHFLOW_ID
FROM CE_BANK_ACCTS_GT_V ABA
, CE_BANK_ACCTS_GT_V ABA2
, CE_SECURITY_PROFILES_GT LE
, CE_BANK_BRANCHES_V ABB
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_CASHFLOWS CC
, CE_CASHFLOW_ACCT_H CCH
, FND_CURRENCIES FC
, CE_LOOKUPS LK
, CE_LOOKUPS L2
, GL_DAILY_CONVERSION_TYPES GLCC
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, XLE_ENTITY_PROFILES XLE
, CE_TRXNS_SUBTYPE_CODES TRXN
WHERE LK.LOOKUP_TYPE = 'CE_CASHFLOW_DIRECTION_TYPE'
AND LK.LOOKUP_CODE = CC.CASHFLOW_DIRECTION
AND L2.LOOKUP_TYPE = 'CASHFLOW_STATUS_CODE'
AND L2.LOOKUP_CODE = CC.CASHFLOW_STATUS_CODE
AND GLCC.CONVERSION_TYPE(+) = CC.CASHFLOW_EXCHANGE_RATE_TYPE
AND ABB.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID
AND ABA.BANK_ACCOUNT_ID = CC.CASHFLOW_BANK_ACCOUNT_ID
AND CC.CASHFLOW_STATUS_CODE IN ('CREATED'
, 'CLEARED')
AND CC.CASHFLOW_CURRENCY_CODE = FC.CURRENCY_CODE
AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID
AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY'
AND CRE.REFERENCE_TYPE(+) = 'CASHFLOW'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND CRE.REFERENCE_ID(+) = CC.CASHFLOW_ID
AND CC.CASHFLOW_LEGAL_ENTITY_ID = ABA.ACCOUNT_OWNER_ORG_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND CC.CASHFLOW_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND CC.COUNTERPARTY_BANK_ACCOUNT_ID = ABA2.BANK_ACCOUNT_ID (+)
AND CC.CASHFLOW_ID = CCH.CASHFLOW_ID
AND CCH.CURRENT_RECORD_FLAG = 'Y'
AND CCH.EVENT_TYPE IN ('CE_STMT_RECORDED'
, 'CE_BAT_CLEARED'
, 'CE_BAT_UNCLEARED'
, 'CE_BAT_CREATED')
AND ((CC.SOURCE_TRXN_TYPE ='BAT'
AND CC.TRXN_REFERENCE_NUMBER = (SELECT PT.TRXN_REFERENCE_NUMBER
FROM CE_PAYMENT_TRANSACTIONS PT
WHERE CC.TRXN_REFERENCE_NUMBER = PT.TRXN_REFERENCE_NUMBER
AND PT.TRXN_STATUS_CODE = 'SETTLED')) OR (CC.SOURCE_TRXN_TYPE = 'STMT'))
AND CC.COUNTERPARTY_PARTY_ID = XLE.PARTY_ID(+)
AND CC.SOURCE_TRXN_SUBTYPE_CODE_ID = TRXN.TRXN_SUBTYPE_CODE_ID(+)