View: CE_260_CF_REVERSAL_V

Product:	CE - Cash Management
Description:	AR receipts for reversing or unclearing
Implementation/DBA Data:	APPS.CE_260_CF_REVERSAL_V

View Text

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
, /* 10 */ SOB.CURRENCY_CODE
, CC.CASHFLOW_AMOUNT
, ABA.CURRENCY_CODE
, CC.CASHFLOW_AMOUNT
, CC.CASHFLOW_AMOUNT)
, 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
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 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

Columns