VIEW: APPS.CE_MISC_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT CR.ROWID, ABA.BANK_ACCOUNT_ID, CR.CASH_RECEIPT_ID, CR.TYPE, CR.RECEIVABLES_TRX_ID, AR.NAME, CR.RECEIPT_METHOD_ID, RM.NAME, CR.RECEIPT_NUMBER, CRH.TRX_DATE, CR.CURRENCY_CODE, DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE, 'FUNCTIONAL',ABA.CURRENCY_CODE, 'BANK', 'FOREIGN' ), CRH.EXCHANGE_DATE, GCT.USER_CONVERSION_TYPE, CRH.EXCHANGE_RATE_TYPE, CRH.EXCHANGE_RATE, DECODE(CE_AUTO_BANK_MATCH.GET_INVERSE_RATE, 'N', CRH.EXCHANGE_RATE, 'Y', DECODE(NVL(CRH.EXCHANGE_RATE,0),0,NULL, 1/CRH.EXCHANGE_RATE)), CRH.AMOUNT, CRH.GL_DATE, DECODE(CR.CURRENCY_CODE, SOB.CURRENCY_CODE, CRH.AMOUNT, ABA.CURRENCY_CODE, CRH.AMOUNT, NVL(CRH.ACCTD_AMOUNT,CRH.AMOUNT)), CR.VAT_TAX_ID, VT.TAX_RATE_CODE, CR.DOC_SEQUENCE_ID, CR.DOC_SEQUENCE_VALUE, CR.DEPOSIT_DATE, L.MEANING, CR.REFERENCE_TYPE, CR.REFERENCE_ID, CR.MISC_PAYMENT_SOURCE, CR.COMMENTS, CR.CREATION_DATE, CR.CREATED_BY, CR.LAST_UPDATE_DATE, CR.LAST_UPDATED_BY, ABA.BANK_BRANCH_ID, CR.RECEIPT_DATE, CR.ANTICIPATED_CLEARING_DATE, DECODE(CRH.GL_POSTED_DATE,NULL,'N','Y'), CRH.GL_POSTED_DATE, ABA.BANK_ACCOUNT_NAME, ABA.BANK_ACCOUNT_NUM, ABA.CURRENCY_CODE, 222, CR.REMIT_BANK_ACCT_USE_ID, CR.ORG_ID FROM GL_DAILY_CONVERSION_TYPES GCT, CE_BANK_ACCOUNTS ABA, CE_BANK_ACCT_USES_OU_V BAU, AR_LOOKUPS L, GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS, zx_sco_rates /*AR_VAT_TAX */ VT, AR_RECEIPT_METHODS RM, AR_RECEIVABLES_TRX_ALL AR, AR_CASH_RECEIPT_HISTORY_ALL CRH, AR_CASH_RECEIPTS_ALL CR WHERE GCT.CONVERSION_TYPE(+) = CRH.EXCHANGE_RATE_TYPE AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID AND BAU.AR_USE_ENABLE_FLAG = 'Y' AND L.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE' AND L.LOOKUP_CODE(+) = CR.REFERENCE_TYPE AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND /*VT.VAT_TAX_ID(+) = CR.VAT_TAX_ID AND*/ VT.TAX_RATE_ID(+) = CR.VAT_TAX_ID AND RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID AND AR.RECEIVABLES_TRX_ID(+) = CR.RECEIVABLES_TRX_ID AND CR.TYPE = 'MISC' AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND CRH.CURRENT_RECORD_FLAG = 'Y' AND CRH.STATUS IN ('REMITTED','CLEARED','REVERSED','RISK_ELIMINATED') AND BAU.ORG_ID = CRH.ORG_ID AND /*BAU.ORG_ID = SYS.ORG_ID AND SYS.ORG_ID = CRH.ORG_ID AND*/ CRH.ORG_ID = CR.ORG_ID

View Text - HTML Formatted

SELECT CR.ROWID
, ABA.BANK_ACCOUNT_ID
, CR.CASH_RECEIPT_ID
, CR.TYPE
, CR.RECEIVABLES_TRX_ID
, AR.NAME
, CR.RECEIPT_METHOD_ID
, RM.NAME
, CR.RECEIPT_NUMBER
, CRH.TRX_DATE
, CR.CURRENCY_CODE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN' )
, CRH.EXCHANGE_DATE
, GCT.USER_CONVERSION_TYPE
, CRH.EXCHANGE_RATE_TYPE
, CRH.EXCHANGE_RATE
, DECODE(CE_AUTO_BANK_MATCH.GET_INVERSE_RATE
, 'N'
, CRH.EXCHANGE_RATE
, 'Y'
, DECODE(NVL(CRH.EXCHANGE_RATE
, 0)
, 0
, NULL
, 1/CRH.EXCHANGE_RATE))
, CRH.AMOUNT
, CRH.GL_DATE
, DECODE(CR.CURRENCY_CODE
, SOB.CURRENCY_CODE
, CRH.AMOUNT
, ABA.CURRENCY_CODE
, CRH.AMOUNT
, NVL(CRH.ACCTD_AMOUNT
, CRH.AMOUNT))
, CR.VAT_TAX_ID
, VT.TAX_RATE_CODE
, CR.DOC_SEQUENCE_ID
, CR.DOC_SEQUENCE_VALUE
, CR.DEPOSIT_DATE
, L.MEANING
, CR.REFERENCE_TYPE
, CR.REFERENCE_ID
, CR.MISC_PAYMENT_SOURCE
, CR.COMMENTS
, CR.CREATION_DATE
, CR.CREATED_BY
, CR.LAST_UPDATE_DATE
, CR.LAST_UPDATED_BY
, ABA.BANK_BRANCH_ID
, CR.RECEIPT_DATE
, CR.ANTICIPATED_CLEARING_DATE
, DECODE(CRH.GL_POSTED_DATE
, NULL
, 'N'
, 'Y')
, CRH.GL_POSTED_DATE
, ABA.BANK_ACCOUNT_NAME
, ABA.BANK_ACCOUNT_NUM
, ABA.CURRENCY_CODE
, 222
, CR.REMIT_BANK_ACCT_USE_ID
, CR.ORG_ID
FROM GL_DAILY_CONVERSION_TYPES GCT
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_OU_V BAU
, AR_LOOKUPS L
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, ZX_SCO_RATES /*AR_VAT_TAX */ VT
, AR_RECEIPT_METHODS RM
, AR_RECEIVABLES_TRX_ALL AR
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPTS_ALL CR
WHERE GCT.CONVERSION_TYPE(+) = CRH.EXCHANGE_RATE_TYPE
AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND BAU.AR_USE_ENABLE_FLAG = 'Y'
AND L.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE'
AND L.LOOKUP_CODE(+) = CR.REFERENCE_TYPE
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND /*VT.VAT_TAX_ID(+) = CR.VAT_TAX_ID AND*/ VT.TAX_RATE_ID(+) = CR.VAT_TAX_ID
AND RM.RECEIPT_METHOD_ID = CR.RECEIPT_METHOD_ID
AND AR.RECEIVABLES_TRX_ID(+) = CR.RECEIVABLES_TRX_ID
AND CR.TYPE = 'MISC'
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS IN ('REMITTED'
, 'CLEARED'
, 'REVERSED'
, 'RISK_ELIMINATED')
AND BAU.ORG_ID = CRH.ORG_ID
AND /*BAU.ORG_ID = SYS.ORG_ID
AND SYS.ORG_ID = CRH.ORG_ID AND*/ CRH.ORG_ID = CR.ORG_ID