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_222_TRANSACTIONS_V

Source

View Text - Preformatted


SELECT /*+ leading(aba, branchparty, bankparty, bau, sys, ou, cr, crh) use_nl(cr, crh) */ CRH.ROWID, 'N', aba.BANK_ACCOUNT_ID, ABA.BANK_ACCOUNT_NAME, ABA.BANK_ACCOUNT_NUM, BankParty.PARTY_NAME, BranchParty.PARTY_NAME, CRH.CASH_RECEIPT_HISTORY_ID, CR.TYPE, L1.MEANING, CR.RECEIPT_NUMBER, 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.GL_DATE, L3.MEANING, CRH.STATUS, CR.RECEIPT_DATE, DECODE(CRH.STATUS, 'CLEARED', CRH.TRX_DATE), APS.DUE_DATE, CRH.EXCHANGE_DATE, CRH.EXCHANGE_RATE_TYPE, GCT.USER_CONVERSION_TYPE, CRH.EXCHANGE_RATE, DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT) , TO_NUMBER(NULL), B.NAME, B.BATCH_ID, HZ.PARTY_NAME, HZ.PARTY_NAME, ARM.NAME, NULL, CR.PAY_FROM_CUSTOMER, TO_NUMBER(NULL), L2.MEANING, CR.REFERENCE_TYPE, CR.REFERENCE_ID , DECODE(CRE.STATUS_FLAG,'M',DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE, CRH.AMOUNT, ABA.CURRENCY_CODE, CRH.AMOUNT, CRH.ACCTD_AMOUNT) + NVL(DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT),0)), CRH.CREATION_DATE , CRH.CREATED_BY , CRH.LAST_UPDATE_DATE , CRH.LAST_UPDATED_BY , B.BANK_DEPOSIT_NUMBER , CRH.CASH_RECEIPT_ID , 222, 0 , ABA.CURRENCY_CODE , DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN') , NVL(gac.AR_ASSET_CCID , ABA.ASSET_CODE_COMBINATION_ID) , NULL , NULL , TO_NUMBER(NULL) , TO_NUMBER(NULL) , CR.TYPE , NULL , CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0) , ARC.RECEIPT_CLASS_ID , ARM.RECEIPT_METHOD_ID , ARC.NAME , CR.DEPOSIT_DATE , DECODE(CRH.STATUS, 'REVERSED', 'Y', DECODE(aps2.class, 'DM', 'Y', NULL)) , CR.ACTUAL_VALUE_DATE , TO_NUMBER(NULL) , BAU.BANK_ACCT_USE_ID , TO_NUMBER(NULL) , 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, HZ_PARTIES BankParty, HZ_PARTIES BranchParty, CE_BANK_ACCT_USES_ALL BAU, CE_SECURITY_PROFILES_GT OU, CE_GL_ACCOUNTS_CCID GAC, 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_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 CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND ABA.BANK_BRANCH_ID = BranchParty.PARTY_ID AND ABA.BANK_ID = BankParty.PARTY_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 ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_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.BATCH_ID(+) = CRH.BATCH_ID AND B.ORG_ID(+) = CRH.ORG_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND CR.STATUS <> 'REV' AND CR.RECEIPT_DATE >= SYS.CASHBOOK_BEGIN_DATE 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', 'RISK_ELIMINATED'), DECODE(CR.promise_source, 'AR_PREPAYMENT', 'APPROVED', 'REMITTED')) AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE AND APS2.ORG_ID(+) = CRH.ORG_ID AND aps2.reversed_cash_receipt_id (+) = crh.cash_receipt_id UNION ALL SELECT /*+ leading(aba, branchparty, bankparty, bau, sys, ou, cr, crh, cre) use_nl(cr, crh, cre) */ CRH.ROWID, 'N', aba.BANK_ACCOUNT_ID, ABA.BANK_ACCOUNT_NAME, ABA.BANK_ACCOUNT_NUM, BankParty.PARTY_NAME, BranchParty.PARTY_NAME, CRH.CASH_RECEIPT_HISTORY_ID, CR.TYPE, L1.MEANING, CR.RECEIPT_NUMBER, 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.GL_DATE, L3.MEANING, CRH.STATUS, CR.RECEIPT_DATE, DECODE(CRH.STATUS, 'CLEARED', CRH.TRX_DATE), APS.DUE_DATE, CRH.EXCHANGE_DATE, CRH.EXCHANGE_RATE_TYPE, GCT.USER_CONVERSION_TYPE, CRH.EXCHANGE_RATE, DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT), TO_NUMBER(NULL), B.NAME, B.BATCH_ID, HZ.PARTY_NAME, HZ.PARTY_NAME, ARM.NAME, NULL, CR.PAY_FROM_CUSTOMER, TO_NUMBER(NULL), L2.MEANING, CR.REFERENCE_TYPE, CR.REFERENCE_ID, DECODE(CRE.STATUS_FLAG,'M',DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE, CRH.AMOUNT, ABA.CURRENCY_CODE, CRH.AMOUNT, CRH.ACCTD_AMOUNT) + NVL(DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT),0)), CRH.CREATION_DATE, CRH.CREATED_BY, CRH.LAST_UPDATE_DATE, CRH.LAST_UPDATED_BY, B.BANK_DEPOSIT_NUMBER, CRH.CASH_RECEIPT_ID, 222, 0, ABA.CURRENCY_CODE, DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), NVL(gac.AR_ASSET_CCID , ABA.ASSET_CODE_COMBINATION_ID), NULL, NULL, TO_NUMBER(NULL), TO_NUMBER(NULL), CR.TYPE, NULL, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0), ARC.RECEIPT_CLASS_ID, ARM.RECEIPT_METHOD_ID, ARC.NAME, CR.DEPOSIT_DATE, DECODE(CRH.STATUS, 'REVERSED', 'Y', DECODE(aps2.class, 'DM', 'Y', NULL)), CR.ACTUAL_VALUE_DATE, TO_NUMBER(NULL), BAU.BANK_ACCT_USE_ID, TO_NUMBER(NULL), 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, HZ_PARTIES BankParty, HZ_PARTIES BranchParty, CE_BANK_ACCT_USES_ALL BAU, CE_SECURITY_PROFILES_GT OU, CE_GL_ACCOUNTS_CCID GAC, 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_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 CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND ABA.BANK_BRANCH_ID = BranchParty.PARTY_ID AND ABA.BANK_ID = BankParty.PARTY_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 ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_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.BATCH_ID(+) = CRH.BATCH_ID AND B.ORG_ID(+) = CRH.ORG_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND CR.STATUS <> 'REV' AND CR.RECEIPT_DATE >= SYS.CASHBOOK_BEGIN_DATE AND CRH.STATUS = DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING,1,'X', 'REVERSED') AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE AND APS2.ORG_ID(+) = CRH.ORG_ID AND aps2.reversed_cash_receipt_id (+) = crh.cash_receipt_id AND NOT EXISTS (SELECT '1' FROM AR_CASH_RECEIPT_HISTORY_ALL CRH2 WHERE CRH.CASH_RECEIPT_HISTORY_ID = CRH2.REVERSAL_CASH_RECEIPT_HIST_ID AND CRH2.STATUS = 'CONFIRMED' ) UNION ALL SELECT /*+ leading(aba, branchparty, bankparty, bau, sys, ou, cr, crh, crh2) use_nl(cr, crh, crh2) */ CRH.ROWID, 'N', aba.BANK_ACCOUNT_ID, ABA.BANK_ACCOUNT_NAME, ABA.BANK_ACCOUNT_NUM, BankParty.PARTY_NAME, BranchParty.PARTY_NAME, CRH.CASH_RECEIPT_HISTORY_ID, CR.TYPE, L1.MEANING, CR.RECEIPT_NUMBER, 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.GL_DATE, L3.MEANING, CRH.STATUS, CR.RECEIPT_DATE, DECODE(CRH.STATUS, 'CLEARED', CRH.TRX_DATE), APS.DUE_DATE, CRH.EXCHANGE_DATE, CRH.EXCHANGE_RATE_TYPE, GCT.USER_CONVERSION_TYPE, CRH.EXCHANGE_RATE, DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT,CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT), TO_NUMBER(NULL), B.NAME, B.BATCH_ID, HZ.PARTY_NAME, HZ.PARTY_NAME, ARM.NAME, NULL, CR.PAY_FROM_CUSTOMER, TO_NUMBER(NULL), L2.MEANING, CR.REFERENCE_TYPE, CR.REFERENCE_ID, DECODE(CRE.STATUS_FLAG,'M',DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE, CRH.AMOUNT, ABA.CURRENCY_CODE, CRH.AMOUNT, CRH.ACCTD_AMOUNT) + NVL(DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE,CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE,CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT),0)), CRH.CREATION_DATE, CRH.CREATED_BY, CRH.LAST_UPDATE_DATE, CRH.LAST_UPDATED_BY, B.BANK_DEPOSIT_NUMBER, CRH.CASH_RECEIPT_ID, 222, 0, ABA.CURRENCY_CODE, DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), NVL(gac.AR_ASSET_CCID , ABA.ASSET_CODE_COMBINATION_ID), NULL, NULL, TO_NUMBER(NULL), TO_NUMBER(NULL), CR.TYPE, NULL, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0), ARC.RECEIPT_CLASS_ID, ARM.RECEIPT_METHOD_ID, ARC.NAME, CR.DEPOSIT_DATE, DECODE(CRH2.STATUS, 'REVERSED', 'Y', NULL), CR.ACTUAL_VALUE_DATE, TO_NUMBER(NULL), BAU.BANK_ACCT_USE_ID, TO_NUMBER(NULL), 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, HZ_PARTIES BankParty, HZ_PARTIES BranchParty, CE_BANK_ACCT_USES_ALL BAU, CE_SECURITY_PROFILES_GT OU, CE_GL_ACCOUNTS_CCID GAC, 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 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_BRANCH_ID = BranchParty.PARTY_ID AND ABA.BANK_ID = BankParty.PARTY_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 ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_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.BATCH_ID(+) = CRH.BATCH_ID AND B.ORG_ID(+) = CRH.ORG_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND CR.STATUS <> 'REV' AND CR.RECEIPT_DATE >= SYS.CASHBOOK_BEGIN_DATE 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 = 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 UNION ALL SELECT /*+ leading(aba, branchparty, bankparty, bau, sys, ou, cr, crh, cre, aps2) use_nl(cr, crh, cre) use_nl(crh, aps2) */ CRH.ROWID, 'N', aba.BANK_ACCOUNT_ID, ABA.BANK_ACCOUNT_NAME, ABA.BANK_ACCOUNT_NUM, BankParty.PARTY_NAME, BranchParty.PARTY_NAME, CRH.CASH_RECEIPT_HISTORY_ID, CR.TYPE, L1.MEANING, CR.RECEIPT_NUMBER, 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)), TO_NUMBER(NULL), CRH.GL_DATE, L3.MEANING, 'REVERSED', CR.RECEIPT_DATE, TO_DATE(NULL), APS.DUE_DATE, CRH.EXCHANGE_DATE, CRH.EXCHANGE_RATE_TYPE, GCT.USER_CONVERSION_TYPE, CRH.EXCHANGE_RATE, DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT), TO_NUMBER(NULL), B.NAME, B.BATCH_ID, HZ.PARTY_NAME, HZ.PARTY_NAME, ARM.NAME, NULL, CR.PAY_FROM_CUSTOMER, TO_NUMBER(NULL), L2.MEANING, CR.REFERENCE_TYPE, CR.REFERENCE_ID, DECODE(CRE.STATUS_FLAG,'M',DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE, CRH.AMOUNT, ABA.CURRENCY_CODE, CRH.AMOUNT, CRH.ACCTD_AMOUNT) + NVL(DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, ABA.CURRENCY_CODE, CRH.FACTOR_DISCOUNT_AMOUNT, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT),0)), CRH.CREATION_DATE, CRH.CREATED_BY, CRH.LAST_UPDATE_DATE, CRH.LAST_UPDATED_BY, B.BANK_DEPOSIT_NUMBER, CRH.CASH_RECEIPT_ID, 222, 0, ABA.CURRENCY_CODE, DECODE(CR.CURRENCY_CODE,SOB.CURRENCY_CODE, 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), NVL(gac.AR_ASSET_CCID , ABA.ASSET_CODE_COMBINATION_ID), NULL, NULL, TO_NUMBER(NULL), TO_NUMBER(NULL), CR.TYPE, NULL, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0), ARC.RECEIPT_CLASS_ID, ARM.RECEIPT_METHOD_ID, ARC.NAME, CR.DEPOSIT_DATE, 'Y', CR.ACTUAL_VALUE_DATE, TO_NUMBER(NULL), BAU.BANK_ACCT_USE_ID, TO_NUMBER(NULL), 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, HZ_PARTIES BankParty, HZ_PARTIES BranchParty, CE_BANK_ACCT_USES_ALL BAU, CE_SECURITY_PROFILES_GT OU, CE_GL_ACCOUNTS_CCID GAC, 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_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 CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND ABA.BANK_BRANCH_ID = BranchParty.PARTY_ID AND ABA.BANK_ID = BankParty.PARTY_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 ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID AND L3.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS' AND L3.LOOKUP_CODE = 'REVERSED' 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 NVL(CRE.STATUS_FLAG,'U') = 'U' AND cre.reference_type (+) = 'DM REVERSAL' AND NVL(CRE.CURRENT_RECORD_FLAG,'Y') = 'Y' AND CRE.REFERENCE_ID (+) = CRH.CASH_RECEIPT_HISTORY_ID AND B.BATCH_ID(+) = CRH.BATCH_ID AND CR.STATUS <> 'REV' AND CRE.ORG_ID(+) = CRH.ORG_ID AND B.ORG_ID(+) = CRH.ORG_ID AND CR.RECEIPT_DATE >= SYS.CASHBOOK_BEGIN_DATE AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE AND aps2.ORG_ID = CRH.ORG_ID AND aps2.reversed_cash_receipt_id = crh.cash_receipt_id AND aps2.class = 'DM'

View Text - HTML Formatted

SELECT /*+ LEADING(ABA
, BRANCHPARTY
, BANKPARTY
, BAU
, SYS
, OU
, CR
, CRH) USE_NL(CR
, CRH) */ CRH.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, BANKPARTY.PARTY_NAME
, BRANCHPARTY.PARTY_NAME
, CRH.CASH_RECEIPT_HISTORY_ID
, CR.TYPE
, L1.MEANING
, CR.RECEIPT_NUMBER
, 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.GL_DATE
, L3.MEANING
, CRH.STATUS
, CR.RECEIPT_DATE
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.TRX_DATE)
, APS.DUE_DATE
, CRH.EXCHANGE_DATE
, CRH.EXCHANGE_RATE_TYPE
, GCT.USER_CONVERSION_TYPE
, CRH.EXCHANGE_RATE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, TO_NUMBER(NULL)
, B.NAME
, B.BATCH_ID
, HZ.PARTY_NAME
, HZ.PARTY_NAME
, ARM.NAME
, NULL
, CR.PAY_FROM_CUSTOMER
, TO_NUMBER(NULL)
, L2.MEANING
, CR.REFERENCE_TYPE
, CR.REFERENCE_ID
, DECODE(CRE.STATUS_FLAG
, 'M'
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.AMOUNT
, ABA.CURRENCY_CODE
, CRH.AMOUNT
, CRH.ACCTD_AMOUNT) + NVL(DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, 0))
, CRH.CREATION_DATE
, CRH.CREATED_BY
, CRH.LAST_UPDATE_DATE
, CRH.LAST_UPDATED_BY
, B.BANK_DEPOSIT_NUMBER
, CRH.CASH_RECEIPT_ID
, 222
, 0
, ABA.CURRENCY_CODE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(GAC.AR_ASSET_CCID
, ABA.ASSET_CODE_COMBINATION_ID)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CR.TYPE
, NULL
, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0)
, ARC.RECEIPT_CLASS_ID
, ARM.RECEIPT_METHOD_ID
, ARC.NAME
, CR.DEPOSIT_DATE
, DECODE(CRH.STATUS
, 'REVERSED'
, 'Y'
, DECODE(APS2.CLASS
, 'DM'
, 'Y'
, NULL))
, CR.ACTUAL_VALUE_DATE
, TO_NUMBER(NULL)
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, 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
, HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_GL_ACCOUNTS_CCID GAC
, 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_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 CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_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 ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_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.BATCH_ID(+) = CRH.BATCH_ID
AND B.ORG_ID(+) = CRH.ORG_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CR.STATUS <> 'REV'
AND CR.RECEIPT_DATE >= SYS.CASHBOOK_BEGIN_DATE
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'
, 'RISK_ELIMINATED')
, DECODE(CR.PROMISE_SOURCE
, 'AR_PREPAYMENT'
, 'APPROVED'
, 'REMITTED'))
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND APS2.ORG_ID(+) = CRH.ORG_ID
AND APS2.REVERSED_CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID UNION ALL SELECT /*+ LEADING(ABA
, BRANCHPARTY
, BANKPARTY
, BAU
, SYS
, OU
, CR
, CRH
, CRE) USE_NL(CR
, CRH
, CRE) */ CRH.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, BANKPARTY.PARTY_NAME
, BRANCHPARTY.PARTY_NAME
, CRH.CASH_RECEIPT_HISTORY_ID
, CR.TYPE
, L1.MEANING
, CR.RECEIPT_NUMBER
, 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.GL_DATE
, L3.MEANING
, CRH.STATUS
, CR.RECEIPT_DATE
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.TRX_DATE)
, APS.DUE_DATE
, CRH.EXCHANGE_DATE
, CRH.EXCHANGE_RATE_TYPE
, GCT.USER_CONVERSION_TYPE
, CRH.EXCHANGE_RATE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, TO_NUMBER(NULL)
, B.NAME
, B.BATCH_ID
, HZ.PARTY_NAME
, HZ.PARTY_NAME
, ARM.NAME
, NULL
, CR.PAY_FROM_CUSTOMER
, TO_NUMBER(NULL)
, L2.MEANING
, CR.REFERENCE_TYPE
, CR.REFERENCE_ID
, DECODE(CRE.STATUS_FLAG
, 'M'
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.AMOUNT
, ABA.CURRENCY_CODE
, CRH.AMOUNT
, CRH.ACCTD_AMOUNT) + NVL(DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, 0))
, CRH.CREATION_DATE
, CRH.CREATED_BY
, CRH.LAST_UPDATE_DATE
, CRH.LAST_UPDATED_BY
, B.BANK_DEPOSIT_NUMBER
, CRH.CASH_RECEIPT_ID
, 222
, 0
, ABA.CURRENCY_CODE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(GAC.AR_ASSET_CCID
, ABA.ASSET_CODE_COMBINATION_ID)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CR.TYPE
, NULL
, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0)
, ARC.RECEIPT_CLASS_ID
, ARM.RECEIPT_METHOD_ID
, ARC.NAME
, CR.DEPOSIT_DATE
, DECODE(CRH.STATUS
, 'REVERSED'
, 'Y'
, DECODE(APS2.CLASS
, 'DM'
, 'Y'
, NULL))
, CR.ACTUAL_VALUE_DATE
, TO_NUMBER(NULL)
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, 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
, HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_GL_ACCOUNTS_CCID GAC
, 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_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 CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_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 ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_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.BATCH_ID(+) = CRH.BATCH_ID
AND B.ORG_ID(+) = CRH.ORG_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CR.STATUS <> 'REV'
AND CR.RECEIPT_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND CRH.STATUS = DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'X'
, 'REVERSED')
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND APS2.ORG_ID(+) = CRH.ORG_ID
AND APS2.REVERSED_CASH_RECEIPT_ID (+) = CRH.CASH_RECEIPT_ID
AND NOT EXISTS (SELECT '1'
FROM AR_CASH_RECEIPT_HISTORY_ALL CRH2
WHERE CRH.CASH_RECEIPT_HISTORY_ID = CRH2.REVERSAL_CASH_RECEIPT_HIST_ID
AND CRH2.STATUS = 'CONFIRMED' ) UNION ALL SELECT /*+ LEADING(ABA
, BRANCHPARTY
, BANKPARTY
, BAU
, SYS
, OU
, CR
, CRH
, CRH2) USE_NL(CR
, CRH
, CRH2) */ CRH.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, BANKPARTY.PARTY_NAME
, BRANCHPARTY.PARTY_NAME
, CRH.CASH_RECEIPT_HISTORY_ID
, CR.TYPE
, L1.MEANING
, CR.RECEIPT_NUMBER
, 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.GL_DATE
, L3.MEANING
, CRH.STATUS
, CR.RECEIPT_DATE
, DECODE(CRH.STATUS
, 'CLEARED'
, CRH.TRX_DATE)
, APS.DUE_DATE
, CRH.EXCHANGE_DATE
, CRH.EXCHANGE_RATE_TYPE
, GCT.USER_CONVERSION_TYPE
, CRH.EXCHANGE_RATE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, TO_NUMBER(NULL)
, B.NAME
, B.BATCH_ID
, HZ.PARTY_NAME
, HZ.PARTY_NAME
, ARM.NAME
, NULL
, CR.PAY_FROM_CUSTOMER
, TO_NUMBER(NULL)
, L2.MEANING
, CR.REFERENCE_TYPE
, CR.REFERENCE_ID
, DECODE(CRE.STATUS_FLAG
, 'M'
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.AMOUNT
, ABA.CURRENCY_CODE
, CRH.AMOUNT
, CRH.ACCTD_AMOUNT) + NVL(DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, 0))
, CRH.CREATION_DATE
, CRH.CREATED_BY
, CRH.LAST_UPDATE_DATE
, CRH.LAST_UPDATED_BY
, B.BANK_DEPOSIT_NUMBER
, CRH.CASH_RECEIPT_ID
, 222
, 0
, ABA.CURRENCY_CODE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(GAC.AR_ASSET_CCID
, ABA.ASSET_CODE_COMBINATION_ID)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CR.TYPE
, NULL
, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0)
, ARC.RECEIPT_CLASS_ID
, ARM.RECEIPT_METHOD_ID
, ARC.NAME
, CR.DEPOSIT_DATE
, DECODE(CRH2.STATUS
, 'REVERSED'
, 'Y'
, NULL)
, CR.ACTUAL_VALUE_DATE
, TO_NUMBER(NULL)
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, 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
, HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_GL_ACCOUNTS_CCID GAC
, 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
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_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_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 ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_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.BATCH_ID(+) = CRH.BATCH_ID
AND B.ORG_ID(+) = CRH.ORG_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CR.STATUS <> 'REV'
AND CR.RECEIPT_DATE >= SYS.CASHBOOK_BEGIN_DATE
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 = 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 UNION ALL SELECT /*+ LEADING(ABA
, BRANCHPARTY
, BANKPARTY
, BAU
, SYS
, OU
, CR
, CRH
, CRE
, APS2) USE_NL(CR
, CRH
, CRE) USE_NL(CRH
, APS2) */ CRH.ROWID
, 'N'
, ABA.BANK_ACCOUNT_ID
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, BANKPARTY.PARTY_NAME
, BRANCHPARTY.PARTY_NAME
, CRH.CASH_RECEIPT_HISTORY_ID
, CR.TYPE
, L1.MEANING
, CR.RECEIPT_NUMBER
, 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))
, TO_NUMBER(NULL)
, CRH.GL_DATE
, L3.MEANING
, 'REVERSED'
, CR.RECEIPT_DATE
, TO_DATE(NULL)
, APS.DUE_DATE
, CRH.EXCHANGE_DATE
, CRH.EXCHANGE_RATE_TYPE
, GCT.USER_CONVERSION_TYPE
, CRH.EXCHANGE_RATE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, TO_NUMBER(NULL)
, B.NAME
, B.BATCH_ID
, HZ.PARTY_NAME
, HZ.PARTY_NAME
, ARM.NAME
, NULL
, CR.PAY_FROM_CUSTOMER
, TO_NUMBER(NULL)
, L2.MEANING
, CR.REFERENCE_TYPE
, CR.REFERENCE_ID
, DECODE(CRE.STATUS_FLAG
, 'M'
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.AMOUNT
, ABA.CURRENCY_CODE
, CRH.AMOUNT
, CRH.ACCTD_AMOUNT) + NVL(DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, ABA.CURRENCY_CODE
, CRH.FACTOR_DISCOUNT_AMOUNT
, CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)
, 0))
, CRH.CREATION_DATE
, CRH.CREATED_BY
, CRH.LAST_UPDATE_DATE
, CRH.LAST_UPDATED_BY
, B.BANK_DEPOSIT_NUMBER
, CRH.CASH_RECEIPT_ID
, 222
, 0
, ABA.CURRENCY_CODE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(GAC.AR_ASSET_CCID
, ABA.ASSET_CODE_COMBINATION_ID)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CR.TYPE
, NULL
, CRH.ACCTD_AMOUNT+NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT
, 0)
, ARC.RECEIPT_CLASS_ID
, ARM.RECEIPT_METHOD_ID
, ARC.NAME
, CR.DEPOSIT_DATE
, 'Y'
, CR.ACTUAL_VALUE_DATE
, TO_NUMBER(NULL)
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, 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
, HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_GL_ACCOUNTS_CCID GAC
, 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_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 CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_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 ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID
AND L3.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS'
AND L3.LOOKUP_CODE = 'REVERSED'
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 NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND CRE.REFERENCE_TYPE (+) = 'DM REVERSAL'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND CRE.REFERENCE_ID (+) = CRH.CASH_RECEIPT_HISTORY_ID
AND B.BATCH_ID(+) = CRH.BATCH_ID
AND CR.STATUS <> 'REV'
AND CRE.ORG_ID(+) = CRH.ORG_ID
AND B.ORG_ID(+) = CRH.ORG_ID
AND CR.RECEIPT_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE
AND APS2.ORG_ID = CRH.ORG_ID
AND APS2.REVERSED_CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND APS2.CLASS = 'DM'