View: CE_222_TXN_FOR_BATCH_V

Product:	CE - Cash Management
Description:	Available Remittance Batches for Reconciliation or Clearing.
Implementation/DBA Data:	APPS.CE_222_TXN_FOR_BATCH_V

View Text

SELECT 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 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)

Columns