VIEW: APPS.CE_222_TXN_FOR_BATCH_V

Source

View Text - Preformatted

SELECT /*+ leading(CRH2) */ CRH.CASH_RECEIPT_HISTORY_ID, CR.TYPE, CR.CURRENCY_CODE, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0), DECODE( CR.CURRENCY_CODE, 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, DECODE(CRH.STATUS,'CLEARED',CRH.AMOUNT, NULL), ABA.CURRENCY_CODE, DECODE(CRH.STATUS,'CLEARED',CRH.AMOUNT,NULL), DECODE(CRH.STATUS,'CLEARED',CRH.ACCTD_AMOUNT,NULL)), CRH.STATUS, CR.RECEIPT_DATE, CRH.CASH_RECEIPT_ID, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0), crh2.batch_id, DECODE( CR.CURRENCY_CODE, SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT), CRH.GL_DATE, CR.EXCHANGE_RATE, CR.EXCHANGE_DATE, CR.EXCHANGE_RATE_TYPE, BAU.BANK_ACCT_USE_ID, CRH.ORG_ID FROM AR_RECEIPT_CLASSES ARC, AR_RECEIPT_METHODS ARM, /* HZ_CUST_ACCOUNTS CU, HZ_PARTIES HZ,*/ GL_DAILY_CONVERSION_TYPES GCT, AR_PAYMENT_SCHEDULES_ALL APS, CE_BANK_ACCOUNTS ABA, CE_SECURITY_PROFILES_GT OU, CE_BANK_ACCT_USES_OU_V BAU, AR_LOOKUPS L3, CE_LOOKUPS L1, AR_LOOKUPS L2, GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS, CE_STATEMENT_RECONCILS_ALL CRE, AR_BATCHES_ALL B, AR_CASH_RECEIPTS_ALL CR, AR_CASH_RECEIPT_HISTORY_ALL CRH, ar_cash_receipt_history_ALL crh2, ar_payment_schedules_ALL aps2 WHERE ARC.RECEIPT_CLASS_ID = ARM.RECEIPT_CLASS_ID /*AND CU.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER AND HZ.PARTY_ID(+) = CU.PARTY_ID*/ AND GCT.CONVERSION_TYPE(+) = CRH.EXCHANGE_RATE_TYPE AND APS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID AND APS.ORG_ID(+) = CR.ORG_ID AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND ABA.BANK_ACCOUNT_ID = BAU.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.ORG_ID = SYS.ORG_ID*/ AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND L3.LOOKUP_TYPE(+) = 'RECEIPT_CREATION_STATUS' AND L3.LOOKUP_CODE(+) = CRH.STATUS AND L1.LOOKUP_TYPE(+) = 'TRX_TYPE' AND L1.LOOKUP_CODE(+) = CR.TYPE AND L2.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE' AND L2.LOOKUP_CODE(+) = CR.REFERENCE_TYPE AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND CRE.REFERENCE_TYPE(+) = 'RECEIPT' AND NVL(CRE.STATUS_FLAG,'U') = 'U' AND NVL(CRE.CURRENT_RECORD_FLAG,'Y') = 'Y' AND CRE.REFERENCE_ID(+) = CRH.CASH_RECEIPT_HISTORY_ID AND CRE.ORG_ID(+) = CRH.ORG_ID AND B.ORG_ID(+) = CRH.ORG_ID AND B.BATCH_ID(+) = CRH.BATCH_ID AND CR.ORG_ID = CRH.ORG_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND NVL(CR.STATUS,'X') = DECODE(NVL(CR.STATUS,'X'),'REV','U',NVL(CR.STATUS,'X')) AND CRH.STATUS IN ('REMITTED', DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING,1,'REMITTED', DECODE(SYS.SHOW_CLEARED_FLAG,'N','REMITTED','CLEARED')), DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING,1,'REMITTED', 'REVERSED'), DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING,1,'REMITTED', 'RISK_ELIMINATED')) AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE and crh.cash_receipt_id = crh2.cash_receipt_id AND crh.ORG_ID = crh2.ORG_ID and crh.cash_receipt_history_id = decode(crh.batch_id, null, crh2.REVERSAL_CASH_RECEIPT_HIST_ID, crh2.CASH_RECEIPT_HISTORY_ID) AND APS2.ORG_ID(+) = CRH.ORG_ID and aps2.reversed_cash_receipt_id (+) = crh.cash_receipt_id UNION ALL select /*+ leading(CRH3) */ CRH.CASH_RECEIPT_HISTORY_ID, CR.TYPE, CR.CURRENCY_CODE, CRH.AMOUNT+ NVL (CRH.FACTOR_DISCOUNT_AMOUNT,0), DECODE(CR.CURRENCY_CODE, 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, DECODE(CRH.STATUS,'CLEARED',CRH.AMOUNT,NULL), ABA.CURRENCY_CODE, DECODE(CRH.STATUS,'CLEARED',CRH.AMOUNT,NULL), DECODE(CRH.STATUS,'CLEARED', CRH.ACCTD_AMOUNT,NULL)), CRH2.STATUS, CR.RECEIPT_DATE, CRH.CASH_RECEIPT_ID, CRH.ACCTD_AMOUNT, crh3.batch_id, DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT), CRH.GL_DATE, CR.EXCHANGE_RATE, CR.EXCHANGE_DATE, CR.EXCHANGE_RATE_TYPE, BAU.BANK_ACCT_USE_ID, CRH.ORG_ID FROM AR_RECEIPT_CLASSES ARC, AR_RECEIPT_METHODS ARM, /* HZ_CUST_ACCOUNTS CU, HZ_PARTIES HZ,*/ GL_DAILY_CONVERSION_TYPES GCT, AR_PAYMENT_SCHEDULES_ALL APS, CE_BANK_ACCOUNTS ABA, CE_BANK_ACCT_USES_ALL BAU, CE_SECURITY_PROFILES_GT OU, AR_LOOKUPS L3, CE_LOOKUPS L1, AR_LOOKUPS L2, GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS, CE_STATEMENT_RECONCILS_ALL CRE, AR_BATCHES_ALL B, AR_CASH_RECEIPTS_ALL CR, AR_CASH_RECEIPT_HISTORY_ALL CRH, AR_CASH_RECEIPT_HISTORY_ALL CRH2, ar_cash_receipt_history_ALL crh3 WHERE ARC.RECEIPT_CLASS_ID = ARM.RECEIPT_CLASS_ID /*AND CU.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER AND HZ.PARTY_ID(+) = CU.PARTY_ID */ AND GCT.CONVERSION_TYPE(+) = CRH.EXCHANGE_RATE_TYPE AND APS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND ABA.BANK_ACCOUNT_ID = BAU.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.ORG_ID = SYS.ORG_ID*/ AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND L3.LOOKUP_TYPE(+) = 'RECEIPT_CREATION_STATUS' AND L3.LOOKUP_CODE(+) = CRH.STATUS AND L1.LOOKUP_TYPE(+) = 'TRX_TYPE' AND L1.LOOKUP_CODE(+) = CR.TYPE AND L2.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE' AND L2.LOOKUP_CODE(+) = CR.REFERENCE_TYPE AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND CRE.REFERENCE_TYPE(+) = 'RECEIPT' AND NVL(CRE.STATUS_FLAG,'U') = 'U' AND NVL(CRE.CURRENT_RECORD_FLAG,'Y') = 'Y' AND CRE.REFERENCE_ID(+) = CRH.CASH_RECEIPT_HISTORY_ID AND CRE.ORG_ID(+) = CRH.ORG_ID AND B.ORG_ID(+) = CRH.ORG_ID AND B.BATCH_ID(+) = CRH.BATCH_ID AND CR.ORG_ID = CRH.ORG_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND NVL(CR.STATUS, 'X') = DECODE(NVL(CR.STATUS, 'X'), 'REV', 'U', NVL(CR.STATUS, 'X')) AND CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING != 1 AND CRH.STATUS IN ( 'REMITTED', 'RISK_ELIMINATED', DECODE(SYS.SHOW_CLEARED_FLAG,'N','REMITTED','CLEARED')) AND CRH.REVERSAL_CASH_RECEIPT_HIST_ID+0 = CRH2.CASH_RECEIPT_HISTORY_ID AND CRH2.STATUS = 'REVERSED' AND CRH2.CURRENT_RECORD_FLAG = 'Y' AND CRH2.PRV_STAT_CASH_RECEIPT_HIST_ID = CRH.CASH_RECEIPT_HISTORY_ID AND crh2.ORG_ID = crh.ORG_ID AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE and crh.cash_receipt_id = crh3.cash_receipt_id AND crh.ORG_ID = crh3.ORG_ID and crh.CASH_RECEIPT_HISTORY_ID = decode(crh.batch_id, null, crh3.REVERSAL_CASH_RECEIPT_HIST_ID, crh3.CASH_RECEIPT_HISTORY_ID)

View Text - HTML Formatted

SELECT /*+ LEADING(CRH2) */ CRH.CASH_RECEIPT_HISTORY_ID
, CR.TYPE
, CR.CURRENCY_CODE
, CRH.AMOUNT+NVL(CRH.FACTOR_DISCOUNT_AMOUNT
, 0)
, DECODE( CR.CURRENCY_CODE
, 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
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.AMOUNT
, NULL)
, ABA.CURRENCY_CODE
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.AMOUNT
, NULL)
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.ACCTD_AMOUNT
, NULL))
, CRH.STATUS
, CR.RECEIPT_DATE
, CRH.CASH_RECEIPT_ID
, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0)
, CRH2.BATCH_ID
, DECODE( CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, CRH.GL_DATE
, CR.EXCHANGE_RATE
, CR.EXCHANGE_DATE
, CR.EXCHANGE_RATE_TYPE
, BAU.BANK_ACCT_USE_ID
, CRH.ORG_ID
FROM AR_RECEIPT_CLASSES ARC
, AR_RECEIPT_METHODS ARM
, /* HZ_CUST_ACCOUNTS CU
, HZ_PARTIES HZ
, */ GL_DAILY_CONVERSION_TYPES GCT
, AR_PAYMENT_SCHEDULES_ALL APS
, CE_BANK_ACCOUNTS ABA
, CE_SECURITY_PROFILES_GT OU
, CE_BANK_ACCT_USES_OU_V BAU
, AR_LOOKUPS L3
, CE_LOOKUPS L1
, AR_LOOKUPS L2
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_STATEMENT_RECONCILS_ALL CRE
, AR_BATCHES_ALL B
, AR_CASH_RECEIPTS_ALL CR
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPT_HISTORY_ALL CRH2
, AR_PAYMENT_SCHEDULES_ALL APS2
WHERE ARC.RECEIPT_CLASS_ID = ARM.RECEIPT_CLASS_ID /*AND CU.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER
AND HZ.PARTY_ID(+) = CU.PARTY_ID*/
AND GCT.CONVERSION_TYPE(+) = CRH.EXCHANGE_RATE_TYPE
AND APS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND APS.ORG_ID(+) = CR.ORG_ID
AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND ABA.BANK_ACCOUNT_ID = BAU.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.ORG_ID = SYS.ORG_ID*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND L3.LOOKUP_TYPE(+) = 'RECEIPT_CREATION_STATUS'
AND L3.LOOKUP_CODE(+) = CRH.STATUS
AND L1.LOOKUP_TYPE(+) = 'TRX_TYPE'
AND L1.LOOKUP_CODE(+) = CR.TYPE
AND L2.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE'
AND L2.LOOKUP_CODE(+) = CR.REFERENCE_TYPE
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND CRE.REFERENCE_TYPE(+) = 'RECEIPT'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND CRE.REFERENCE_ID(+) = CRH.CASH_RECEIPT_HISTORY_ID
AND CRE.ORG_ID(+) = CRH.ORG_ID
AND B.ORG_ID(+) = CRH.ORG_ID
AND B.BATCH_ID(+) = CRH.BATCH_ID
AND CR.ORG_ID = CRH.ORG_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND NVL(CR.STATUS
, 'X') = DECODE(NVL(CR.STATUS
, 'X')
, 'REV'
, 'U'
, NVL(CR.STATUS
, 'X'))
AND CRH.STATUS IN ('REMITTED'
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'REMITTED'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'REMITTED'
, 'CLEARED'))
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'REMITTED'
, 'REVERSED')
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'REMITTED'
, 'RISK_ELIMINATED'))
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND CRH.CASH_RECEIPT_ID = CRH2.CASH_RECEIPT_ID
AND CRH.ORG_ID = CRH2.ORG_ID
AND CRH.CASH_RECEIPT_HISTORY_ID = DECODE(CRH.BATCH_ID
, NULL
, CRH2.REVERSAL_CASH_RECEIPT_HIST_ID
, CRH2.CASH_RECEIPT_HISTORY_ID)
AND APS2.ORG_ID(+) = CRH.ORG_ID
AND APS2.REVERSED_CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID UNION ALL SELECT /*+ LEADING(CRH3) */ CRH.CASH_RECEIPT_HISTORY_ID
, CR.TYPE
, CR.CURRENCY_CODE
, CRH.AMOUNT+ NVL (CRH.FACTOR_DISCOUNT_AMOUNT
, 0)
, DECODE(CR.CURRENCY_CODE
, 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
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.AMOUNT
, NULL)
, ABA.CURRENCY_CODE
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.AMOUNT
, NULL)
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.ACCTD_AMOUNT
, NULL))
, CRH2.STATUS
, CR.RECEIPT_DATE
, CRH.CASH_RECEIPT_ID
, CRH.ACCTD_AMOUNT
, CRH3.BATCH_ID
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, CRH.GL_DATE
, CR.EXCHANGE_RATE
, CR.EXCHANGE_DATE
, CR.EXCHANGE_RATE_TYPE
, BAU.BANK_ACCT_USE_ID
, CRH.ORG_ID
FROM AR_RECEIPT_CLASSES ARC
, AR_RECEIPT_METHODS ARM
, /* HZ_CUST_ACCOUNTS CU
, HZ_PARTIES HZ
, */ GL_DAILY_CONVERSION_TYPES GCT
, AR_PAYMENT_SCHEDULES_ALL APS
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, AR_LOOKUPS L3
, CE_LOOKUPS L1
, AR_LOOKUPS L2
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_STATEMENT_RECONCILS_ALL CRE
, AR_BATCHES_ALL B
, AR_CASH_RECEIPTS_ALL CR
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPT_HISTORY_ALL CRH2
, AR_CASH_RECEIPT_HISTORY_ALL CRH3
WHERE ARC.RECEIPT_CLASS_ID = ARM.RECEIPT_CLASS_ID /*AND CU.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER
AND HZ.PARTY_ID(+) = CU.PARTY_ID */
AND GCT.CONVERSION_TYPE(+) = CRH.EXCHANGE_RATE_TYPE
AND APS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND ABA.BANK_ACCOUNT_ID = BAU.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.ORG_ID = SYS.ORG_ID*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND L3.LOOKUP_TYPE(+) = 'RECEIPT_CREATION_STATUS'
AND L3.LOOKUP_CODE(+) = CRH.STATUS
AND L1.LOOKUP_TYPE(+) = 'TRX_TYPE'
AND L1.LOOKUP_CODE(+) = CR.TYPE
AND L2.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE'
AND L2.LOOKUP_CODE(+) = CR.REFERENCE_TYPE
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND CRE.REFERENCE_TYPE(+) = 'RECEIPT'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND CRE.REFERENCE_ID(+) = CRH.CASH_RECEIPT_HISTORY_ID
AND CRE.ORG_ID(+) = CRH.ORG_ID
AND B.ORG_ID(+) = CRH.ORG_ID
AND B.BATCH_ID(+) = CRH.BATCH_ID
AND CR.ORG_ID = CRH.ORG_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND NVL(CR.STATUS
, 'X') = DECODE(NVL(CR.STATUS
, 'X')
, 'REV'
, 'U'
, NVL(CR.STATUS
, 'X'))
AND CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING != 1
AND CRH.STATUS IN ( 'REMITTED'
, 'RISK_ELIMINATED'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'REMITTED'
, 'CLEARED'))
AND CRH.REVERSAL_CASH_RECEIPT_HIST_ID+0 = CRH2.CASH_RECEIPT_HISTORY_ID
AND CRH2.STATUS = 'REVERSED'
AND CRH2.CURRENT_RECORD_FLAG = 'Y'
AND CRH2.PRV_STAT_CASH_RECEIPT_HIST_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND CRH2.ORG_ID = CRH.ORG_ID
AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND CRH.CASH_RECEIPT_ID = CRH3.CASH_RECEIPT_ID
AND CRH.ORG_ID = CRH3.ORG_ID
AND CRH.CASH_RECEIPT_HISTORY_ID = DECODE(CRH.BATCH_ID
, NULL
, CRH3.REVERSAL_CASH_RECEIPT_HIST_ID
, CRH3.CASH_RECEIPT_HISTORY_ID)