VIEW: APPS.CE_260_CF_REVERSAL_V

Source

View Text - Preformatted

SELECT CC.ROWID, /* 1 */ 'N', /* 2 */ ABA.BANK_ACCOUNT_ID, /* 3 */ CC.CASHFLOW_ID, /* 4 */ CC.CASHFLOW_DIRECTION, /* 5 */ L1.MEANING, /* 6 */ CC.BANK_TRXN_NUMBER, /* 7 trx_number */ CC.CASHFLOW_CURRENCY_CODE, /* 8 */ CC.CASHFLOW_AMOUNT, /* 9 */ 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)))), /*10*/ DECODE(CC.CASHFLOW_CURRENCY_CODE, /* 11 */ SOB.CURRENCY_CODE, CC.CLEARED_AMOUNT, ABA.CURRENCY_CODE, CC.CLEARED_AMOUNT, CC.CLEARED_AMOUNT), nvl(CCH.ACCOUNTING_DATE,cc.cleared_date), /* 12 */ L2.MEANING, /* 13 */ CC.CASHFLOW_STATUS_CODE, /* 14 */ CC.CASHFLOW_DATE, /* 15 */ CC.CLEARED_DATE, /* 16 */ CC.CLEARED_DATE, /* 17 */ to_char(pay.payment_instruction_id), /* 18 */ BR2.BANK_NAME, /* 19 */ CC.COUNTERPARTY_BANK_ACCOUNT_ID, /* 20 */ TO_NUMBER(NULL), /* 21 */ CC.CREATION_DATE, /* 22 */ CC.CREATED_BY, /* 23 */ CC.LAST_UPDATE_DATE, /* 24 */ CC.LAST_UPDATED_BY, /* 25 */ CPT.PAYMENT_METHOD_CODE, /* 26 */ ABA.BANK_ACCOUNT_NUM, /* 27*/ ABA.BANK_ACCOUNT_NAME, /* 28 */ ABA.CURRENCY_CODE, /* 29 */ BR.BANK_NAME, /* 30 */ L2.MEANING, /* 31 */ NULL , /* 32 */ NULL, /* 33 */ NULL , /* 34 */ NULL , /* 35 */ cpt.payment_method_code, /* 36 */ to_char(pay.payment_instruction_id) , /* 37 */ null , /* 38 */ 260, /* 39 */ to_char(CC.CASHFLOW_ID), /* 40 remittance_number*/ ABA2.BANK_ACCOUNT_NUM, /* 41 */ DECODE( CRE.STATEMENT_LINE_ID, NULL, 'N', 'Y' ), /* 42 */ CRE.STATEMENT_LINE_ID, /* 43 */ SH.STATEMENT_NUMBER, /* 44 */ SL.AMOUNT, /* 45 */ DECODE(CC.CASHFLOW_CURRENCY_CODE, /* 46 */ SOB.CURRENCY_CODE, 'FUNCTIONAL',ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), CC.BASE_AMOUNT, /* 47 */ CC.ACTUAL_VALUE_DATE, /* 48 */ null, /* 49 */ null, /* 50 */ CC.CASHFLOW_LEGAL_ENTITY_ID, cc.cashflow_id FROM CE_STATEMENT_HEADERS SH, CE_STATEMENT_LINES SL, CE_STATEMENT_RECONCILS_ALL CRE, GL_SETS_OF_BOOKS SOB, CE_LOOKUPS L2, CE_LOOKUPS L1, ce_cashflows CC, ce_cashflow_acct_h CCH, ce_payment_transactions CPT, CE_SYSTEM_PARAMETERS SYS, CE_BANK_BRANCHES_V BR, CE_BANK_ACCTS_GT_V ABA, CE_BANK_BRANCHES_V BR2, CE_BANK_ACCTS_GT_V ABA2, CE_SECURITY_PROFILES_GT LE, iby_fd_docs_payable_v docs, iby_fd_payments_v pay, fnd_currencies fc WHERE 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.STATUS_FLAG(+) = 'M' AND CRE.REFERENCE_TYPE(+) = 'CASHFLOW' AND CRE.REFERENCE_ID(+) = CC.CASHFLOW_ID and cc.trxn_reference_number = cpt.trxn_reference_number (+) AND BR.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID AND CC.CASHFLOW_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID AND cc.CASHFLOW_LEGAL_ENTITY_ID = ABA.ACCOUNT_OWNER_ORG_ID AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID AND CC.CASHFLOW_CURRENCY_CODE = FC.CURRENCY_CODE AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY' AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND CC.COUNTERPARTY_BANK_ACCOUNT_ID = ABA2.BANK_ACCOUNT_ID (+) AND BR2.BANK_PARTY_ID (+) = CC.COUNTERPARTY_PARTY_ID AND L2.LOOKUP_TYPE(+) = 'CASHFLOW_STATUS_CODE' AND L2.LOOKUP_CODE(+) = CC.CASHFLOW_STATUS_CODE AND L1.LOOKUP_TYPE(+) = 'CE_CASHFLOW_DIRECTION_TYPE' AND L1.LOOKUP_CODE(+) = CC.CASHFLOW_DIRECTION AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND CC.CASHFLOW_STATUS_CODE = 'CLEARED' AND CC.CLEARED_BY_FLAG = 'M' AND CC.CASHFLOW_ID = CCH.CASHFLOW_ID (+) AND CCH.CURRENT_RECORD_FLAG(+) = 'Y' AND CCH.EVENT_TYPE (+) = 'CE_BAT_CLEARED' AND docs.calling_app_doc_ref_number(+) = cpt.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 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ CC.CASHFLOW_ID
, /* 4 */ CC.CASHFLOW_DIRECTION
, /* 5 */ L1.MEANING
, /* 6 */ CC.BANK_TRXN_NUMBER
, /* 7 TRX_NUMBER */ CC.CASHFLOW_CURRENCY_CODE
, /* 8 */ CC.CASHFLOW_AMOUNT
, /* 9 */ 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))))
, /*10*/ DECODE(CC.CASHFLOW_CURRENCY_CODE
, /* 11 */ SOB.CURRENCY_CODE
, CC.CLEARED_AMOUNT
, ABA.CURRENCY_CODE
, CC.CLEARED_AMOUNT
, CC.CLEARED_AMOUNT)
, NVL(CCH.ACCOUNTING_DATE
, CC.CLEARED_DATE)
, /* 12 */ L2.MEANING
, /* 13 */ CC.CASHFLOW_STATUS_CODE
, /* 14 */ CC.CASHFLOW_DATE
, /* 15 */ CC.CLEARED_DATE
, /* 16 */ CC.CLEARED_DATE
, /* 17 */ TO_CHAR(PAY.PAYMENT_INSTRUCTION_ID)
, /* 18 */ BR2.BANK_NAME
, /* 19 */ CC.COUNTERPARTY_BANK_ACCOUNT_ID
, /* 20 */ TO_NUMBER(NULL)
, /* 21 */ CC.CREATION_DATE
, /* 22 */ CC.CREATED_BY
, /* 23 */ CC.LAST_UPDATE_DATE
, /* 24 */ CC.LAST_UPDATED_BY
, /* 25 */ CPT.PAYMENT_METHOD_CODE
, /* 26 */ ABA.BANK_ACCOUNT_NUM
, /* 27*/ ABA.BANK_ACCOUNT_NAME
, /* 28 */ ABA.CURRENCY_CODE
, /* 29 */ BR.BANK_NAME
, /* 30 */ L2.MEANING
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33 */ NULL
, /* 34 */ NULL
, /* 35 */ CPT.PAYMENT_METHOD_CODE
, /* 36 */ TO_CHAR(PAY.PAYMENT_INSTRUCTION_ID)
, /* 37 */ NULL
, /* 38 */ 260
, /* 39 */ TO_CHAR(CC.CASHFLOW_ID)
, /* 40 REMITTANCE_NUMBER*/ ABA2.BANK_ACCOUNT_NUM
, /* 41 */ DECODE( CRE.STATEMENT_LINE_ID
, NULL
, 'N'
, 'Y' )
, /* 42 */ CRE.STATEMENT_LINE_ID
, /* 43 */ SH.STATEMENT_NUMBER
, /* 44 */ SL.AMOUNT
, /* 45 */ DECODE(CC.CASHFLOW_CURRENCY_CODE
, /* 46 */ SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, CC.BASE_AMOUNT
, /* 47 */ CC.ACTUAL_VALUE_DATE
, /* 48 */ NULL
, /* 49 */ NULL
, /* 50 */ CC.CASHFLOW_LEGAL_ENTITY_ID
, CC.CASHFLOW_ID
FROM CE_STATEMENT_HEADERS SH
, CE_STATEMENT_LINES SL
, CE_STATEMENT_RECONCILS_ALL CRE
, GL_SETS_OF_BOOKS SOB
, CE_LOOKUPS L2
, CE_LOOKUPS L1
, CE_CASHFLOWS CC
, CE_CASHFLOW_ACCT_H CCH
, CE_PAYMENT_TRANSACTIONS CPT
, CE_SYSTEM_PARAMETERS SYS
, CE_BANK_BRANCHES_V BR
, CE_BANK_ACCTS_GT_V ABA
, CE_BANK_BRANCHES_V BR2
, CE_BANK_ACCTS_GT_V ABA2
, CE_SECURITY_PROFILES_GT LE
, IBY_FD_DOCS_PAYABLE_V DOCS
, IBY_FD_PAYMENTS_V PAY
, FND_CURRENCIES FC
WHERE 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.STATUS_FLAG(+) = 'M'
AND CRE.REFERENCE_TYPE(+) = 'CASHFLOW'
AND CRE.REFERENCE_ID(+) = CC.CASHFLOW_ID
AND CC.TRXN_REFERENCE_NUMBER = CPT.TRXN_REFERENCE_NUMBER (+)
AND BR.BRANCH_PARTY_ID = ABA.BANK_BRANCH_ID
AND CC.CASHFLOW_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID
AND CC.CASHFLOW_LEGAL_ENTITY_ID = ABA.ACCOUNT_OWNER_ORG_ID
AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID
AND CC.CASHFLOW_CURRENCY_CODE = FC.CURRENCY_CODE
AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY'
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND CC.COUNTERPARTY_BANK_ACCOUNT_ID = ABA2.BANK_ACCOUNT_ID (+)
AND BR2.BANK_PARTY_ID (+) = CC.COUNTERPARTY_PARTY_ID
AND L2.LOOKUP_TYPE(+) = 'CASHFLOW_STATUS_CODE'
AND L2.LOOKUP_CODE(+) = CC.CASHFLOW_STATUS_CODE
AND L1.LOOKUP_TYPE(+) = 'CE_CASHFLOW_DIRECTION_TYPE'
AND L1.LOOKUP_CODE(+) = CC.CASHFLOW_DIRECTION
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND CC.CASHFLOW_STATUS_CODE = 'CLEARED'
AND CC.CLEARED_BY_FLAG = 'M'
AND CC.CASHFLOW_ID = CCH.CASHFLOW_ID (+)
AND CCH.CURRENT_RECORD_FLAG(+) = 'Y'
AND CCH.EVENT_TYPE (+) = 'CE_BAT_CLEARED'
AND DOCS.CALLING_APP_DOC_REF_NUMBER(+) = CPT.TRXN_REFERENCE_NUMBER
AND PAY.PAYMENT_ID (+) = DOCS.PAYMENT_ID
AND DOCS.CALLING_APP_ID (+) = 260