View: CE_222_REVERSAL_V

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

View Text

SELECT CRH.ROWID
, /* 1 */ 'N'
, /* 2 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ CRH.CASH_RECEIPT_HISTORY_ID
, /* 4 */ CR.TYPE
, /* 5 */ L1.MEANING
, /* 6 */ CR.RECEIPT_NUMBER
, /* 7 */ CR.CURRENCY_CODE
, /* 8 */ CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0)
, /* 9 */ DECODE(CR.CURRENCY_CODE
, /* 10 */ SOB.CURRENCY_CODE
, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0)
, ABA.CURRENCY_CODE
, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0)
, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0))
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 11 */ DECODE(CRH.STATUS
, 'CLEARED'
, CRH.AMOUNT
, NULL)
, ABA.CURRENCY_CODE
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.AMOUNT
, NULL)
, DECODE(CRH.STATUS
, 'CLEARED'
, NVL(CRH.ACCTD_AMOUNT
, CRH.AMOUNT)
, NULL))
, CRH.GL_DATE
, /* 12 */ L4.MEANING
, /* 13 */ CRH.STATUS
, /* 14 */ CR.RECEIPT_DATE
, /* 15 */ DECODE(CRH.STATUS
, 'CLEARED'
, CRH.TRX_DATE)
, /* 16 */ APS.DUE_DATE
, /* 17 */ B.NAME
, /* 18 */ HZ.PARTY_NAME
, /* 19 */ CR.PAY_FROM_CUSTOMER
, /* 20 */ TO_NUMBER(NULL)
, /* 21 */ CRH.CREATION_DATE
, /* 22 */ CRH.CREATED_BY
, /* 23 */ CRH.LAST_UPDATE_DATE
, /* 24 */ CRH.LAST_UPDATED_BY
, /* 25 */ RM.NAME
, /* 26 */ ABA.BANK_ACCOUNT_NUM
, /* 27*/ ABA.BANK_ACCOUNT_NAME
, /* 28 */ ABA.CURRENCY_CODE
, /* 29 */ BANKPARTY.PARTY_NAME
, /* 30 */ L2.MEANING
, /* 31 */ CR.REVERSAL_CATEGORY
, /* 32 */ L3.MEANING
, /* 33 */ CR.REVERSAL_REASON_CODE
, /* 34 */ REVERSAL_COMMENTS
, /* 35 */ TO_CHAR(CR.RECEIPT_METHOD_ID)
, /* 36 */ TO_CHAR(CRH.BATCH_ID)
, /* 37 */ CR.CASH_RECEIPT_ID
, /* 38 */ 222
, /* 39 */ B.BANK_DEPOSIT_NUMBER
, /* 40 */ HZ.PARTY_NUMBER
, /* 41 */ DECODE( CRE.STATEMENT_LINE_ID
, NULL
, 'N'
, 'Y' )
, /* 42 */ CRE.STATEMENT_LINE_ID
, /* 43 */ SH.STATEMENT_NUMBER
, /* 44 */ SL.AMOUNT
, /* 45 */ DECODE(CR.CURRENCY_CODE
, /* 46 */ SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0)
, /* 47 */ CR.ACTUAL_VALUE_DATE
, /* 48 */ BAU.BANK_ACCT_USE_ID
, /* 49 */ CRH.ORG_ID /* 50 */ FROM CE_STATEMENT_HEADERS SH
, CE_STATEMENT_LINES SL
, CE_STATEMENT_RECONCILS_ALL CRE
, AR_RECEIPT_METHODS RM
, HZ_CUST_ACCOUNTS CU
, HZ_PARTIES HZ
, AR_PAYMENT_SCHEDULES_ALL APS
, /*GL_SETS_OF_BOOKS SOB
, */ GL_LEDGERS SOB
, AR_LOOKUPS L4
, AR_LOOKUPS L3
, AR_LOOKUPS L2
, CE_LOOKUPS L1
, AR_BATCHES_ALL B
, AR_CASH_RECEIPTS_ALL CR
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, CE_SYSTEM_PARAMETERS SYS
, CE_BANK_ACCOUNTS ABA
, HZ_PARTIES BANKPARTY
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
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(+) = 'RECEIPT' AND CRE.REFERENCE_ID(+) = CRH.CASH_RECEIPT_HISTORY_ID AND CRE.ORG_ID(+) = CRH.ORG_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND CR.ORG_ID = CRH.ORG_ID AND RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID AND CU.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER
AND HZ.PARTY_ID(+) = CU.PARTY_ID AND APS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID AND APS.ORG_ID(+) = CR.ORG_ID AND BANKPARTY.PARTY_ID = ABA.BANK_ID AND BAU.BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID
AND BAU.AR_USE_ENABLE_FLAG = 'Y'
AND BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT' AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID /*AND BAU.ORG_ID = SYS.ORG_ID*/ AND BAU.ORG_ID = CR.ORG_ID AND L4.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS' AND L4.LOOKUP_CODE = CRH.STATUS AND L3.LOOKUP_TYPE(+) = 'CKAJST_REASON' AND L3.LOOKUP_CODE(+) = CR.REVERSAL_REASON_CODE AND L2.LOOKUP_TYPE(+) = 'REVERSAL_CATEGORY_TYPE' AND L2.LOOKUP_CODE(+) = CR.REVERSAL_CATEGORY AND L1.LOOKUP_TYPE(+) = 'TRX_TYPE' AND L1.LOOKUP_CODE(+) = CR.TYPE /*AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID*/ AND SOB.LEDGER_ID = SYS.SET_OF_BOOKS_ID
AND SOB.OBJECT_TYPE_CODE = 'L'
AND NVL(SOB.COMPLETE_FLAG
, 'Y') = 'Y' AND B.BATCH_ID(+) = CRH.BATCH_ID AND B.ORG_ID(+) = CRH.ORG_ID AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.STATUS IN ( DECODE( CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'CLEARED'
, 'REMITTED' )
, 'CLEARED'
, 'RISK_ELIMINATED')
AND ARP_CASHBOOK.RECEIPT_DEBIT_MEMO_REVERSED(CRH.CASH_RECEIPT_ID) != 'Y'

Columns