VIEW: APPS.AR_INTERIM_CASH_RECEIPTS_V

Source

View Text - Preformatted

SELECT ICR.ROWID ROW_ID , ICR.CASH_RECEIPT_ID CASH_RECEIPT_ID , ICR.AMOUNT + NVL(ICR.FACTOR_DISCOUNT_AMOUNT,0) AMOUNT , ICR.FACTOR_DISCOUNT_AMOUNT FACTOR_DISCOUNT_AMOUNT , ICR.AMOUNT NET_AMOUNT , ICR.CURRENCY_CODE CURRENCY_CODE , ICR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID , ICR.REMIT_BANK_ACCT_USE_ID REMITTANCE_BANK_ACCOUNT_ID , ICR.BATCH_ID BATCH_ID , BT.NAME BATCH_NAME , ICR.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , ICR.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , ICR.EXCHANGE_DATE EXCHANGE_DATE , ICR.EXCHANGE_RATE EXCHANGE_RATE , ICR.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE , GD.USER_CONVERSION_TYPE EXCHANGE_RATE_TYPE_DSP , ICR.GL_DATE GL_DATE , ICR.GL_POSTED_DATE GL_POSTED_DATE , ICR.ANTICIPATED_CLEARING_DATE ANTICIPATED_CLEARING_DATE , ICR.PAY_FROM_CUSTOMER PAY_FROM_CUSTOMER , ICR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID , ICR.CUSTOMER_BANK_BRANCH_ID CUSTOMER_BANK_BRANCH_ID , EBA.BANK_NAME CUSTOMER_BANK_NAME , EBA.BANK_ACCOUNT_NUMBER CUSTOMER_BANK_ACCOUNT_NUM , ICR.RECEIPT_DATE RECEIPT_DATE , ICR.RECEIPT_NUMBER RECEIPT_NUMBER , ICR.SITE_USE_ID SITE_USE_ID , NVL(ICR.SPECIAL_TYPE, 'SINGLE') SPECIAL_TYPE , ICR.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE , ICR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID , ICR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE , ICR.CREATED_BY CREATED_BY , ICR.CREATION_DATE CREATION_DATE , ICR.LAST_UPDATED_BY LAST_UPDATED_BY , ICR.LAST_UPDATE_DATE LAST_UPDATE_DATE , ICR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , ICR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID , ICR.PROGRAM_ID PROGRAM_ID , ICR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE , ICR.STATUS STATUS , SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , CU.ACCOUNT_NUMBER CUSTOMER_NUMBER , SU.LOCATION BILLING_LOCATION , CT.TRX_NUMBER TRX_NUMBER , CI.CONS_BILLING_NUMBER BILLING_NUMBER , RM.NAME RECEIPT_METHOD_NAME , BB.BANK_NAME BANK_NAME , BA.BANK_BRANCH_ID REMITTANCE_BANK_BRANCH_ID , BA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER , ICR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , ICR.ATTRIBUTE1 ATTRIBUTE1 , ICR.ATTRIBUTE2 ATTRIBUTE2 , ICR.ATTRIBUTE3 ATTRIBUTE3 , ICR.ATTRIBUTE4 ATTRIBUTE4 , ICR.ATTRIBUTE5 ATTRIBUTE5 , ICR.ATTRIBUTE6 ATTRIBUTE6 , ICR.ATTRIBUTE7 ATTRIBUTE7 , ICR.ATTRIBUTE8 ATTRIBUTE8 , ICR.ATTRIBUTE9 ATTRIBUTE9 , ICR.ATTRIBUTE10 ATTRIBUTE10 , ICR.ATTRIBUTE11 ATTRIBUTE11 , ICR.ATTRIBUTE12 ATTRIBUTE12 , ICR.ATTRIBUTE13 ATTRIBUTE13 , ICR.ATTRIBUTE14 ATTRIBUTE14 , ICR.ATTRIBUTE15 ATTRIBUTE15 , L_RCS.MEANING RECEIPT_CREATION_STATUS_DSP , RC.CREATION_STATUS RECEIPT_CREATION_STATUS , DECODE( NVL(icr.special_type,'SINGLE'), 'SINGLE', nvl(icr.amount_applied,ICR.AMOUNT + NVL(ICR.FACTOR_DISCOUNT_AMOUNT,0)), NULL) , icr.trans_to_receipt_rate , ps.invoice_currency_code , icr.application_notes ,ICR.APPLICATION_REF_TYPE ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPLICATION_REF_TYPE', ICR.APPLICATION_REF_TYPE) APPLICATION_REF_TYPE_MEANING ,ICR.CUSTOMER_REASON ,ICR.CUSTOMER_REFERENCE , DECODE(nvl(icr.special_type,'SINGLE'),'SINGLE' , ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO' ,ARP_DEDUCTION.OVERAPPLICATION_INDICATOR (PS.AMOUNT_DUE_ORIGINAL ,PS.AMOUNT_DUE_REMAINING ,nvl(icr.amount_applied,ICR.AMOUNT + NVL(ICR.FACTOR_DISCOUNT_AMOUNT,0)) ) ) ,NULL ) OVERAPPLICATION_INDICATOR FROM AR_INTERIM_CASH_RECEIPTS ICR, IBY_EXT_BANK_ACCOUNTS_V EBA, AR_PAYMENT_SCHEDULES PS, AR_CONS_INV CI, AR_RECEIPT_METHODS RM, HZ_CUST_ACCOUNTS CU, HZ_PARTIES PARTY, RA_CUSTOMER_TRX CT, HZ_CUST_SITE_USES SU, GL_DAILY_CONVERSION_TYPES GD, AR_RECEIPT_CLASSES RC, AR_LOOKUPS L_RCS, CE_BANK_ACCOUNTS BA, CE_BANK_ACCT_USES USES, CE_BANK_BRANCHES_V BB, AR_BATCHES BT WHERE RM.RECEIPT_METHOD_ID(+) = ICR.RECEIPT_METHOD_ID AND USES.BANK_ACCT_USE_ID(+) = ICR.REMIT_BANK_ACCT_USE_ID AND BA.BANK_ACCOUNT_ID(+)=USES.BANK_ACCOUNT_ID AND BB.BRANCH_PARTY_ID(+) = BA.BANK_BRANCH_ID AND CU.CUST_ACCOUNT_ID(+) = ICR.PAY_FROM_CUSTOMER AND EBA.EXT_BANK_ACCOUNT_ID(+) = ICR.CUSTOMER_BANK_ACCOUNT_ID AND CU.PARTY_ID = PARTY.PARTY_ID(+) AND SU.SITE_USE_ID(+) = ICR.SITE_USE_ID AND RC.RECEIPT_CLASS_ID = RM.RECEIPT_CLASS_ID AND L_RCS.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS' AND L_RCS.LOOKUP_CODE = RC.CREATION_STATUS AND GD.CONVERSION_TYPE(+) = ICR.EXCHANGE_RATE_TYPE AND CT.CUSTOMER_TRX_ID(+) = ICR.CUSTOMER_TRX_ID AND BT.BATCH_ID(+) = ICR.BATCH_ID AND PS.PAYMENT_SCHEDULE_ID(+) = ICR.PAYMENT_SCHEDULE_ID AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID

View Text - HTML Formatted

SELECT ICR.ROWID ROW_ID
, ICR.CASH_RECEIPT_ID CASH_RECEIPT_ID
, ICR.AMOUNT + NVL(ICR.FACTOR_DISCOUNT_AMOUNT
, 0) AMOUNT
, ICR.FACTOR_DISCOUNT_AMOUNT FACTOR_DISCOUNT_AMOUNT
, ICR.AMOUNT NET_AMOUNT
, ICR.CURRENCY_CODE CURRENCY_CODE
, ICR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, ICR.REMIT_BANK_ACCT_USE_ID REMITTANCE_BANK_ACCOUNT_ID
, ICR.BATCH_ID BATCH_ID
, BT.NAME BATCH_NAME
, ICR.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, ICR.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, ICR.EXCHANGE_DATE EXCHANGE_DATE
, ICR.EXCHANGE_RATE EXCHANGE_RATE
, ICR.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, GD.USER_CONVERSION_TYPE EXCHANGE_RATE_TYPE_DSP
, ICR.GL_DATE GL_DATE
, ICR.GL_POSTED_DATE GL_POSTED_DATE
, ICR.ANTICIPATED_CLEARING_DATE ANTICIPATED_CLEARING_DATE
, ICR.PAY_FROM_CUSTOMER PAY_FROM_CUSTOMER
, ICR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID
, ICR.CUSTOMER_BANK_BRANCH_ID CUSTOMER_BANK_BRANCH_ID
, EBA.BANK_NAME CUSTOMER_BANK_NAME
, EBA.BANK_ACCOUNT_NUMBER CUSTOMER_BANK_ACCOUNT_NUM
, ICR.RECEIPT_DATE RECEIPT_DATE
, ICR.RECEIPT_NUMBER RECEIPT_NUMBER
, ICR.SITE_USE_ID SITE_USE_ID
, NVL(ICR.SPECIAL_TYPE
, 'SINGLE') SPECIAL_TYPE
, ICR.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, ICR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, ICR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, ICR.CREATED_BY CREATED_BY
, ICR.CREATION_DATE CREATION_DATE
, ICR.LAST_UPDATED_BY LAST_UPDATED_BY
, ICR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ICR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, ICR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, ICR.PROGRAM_ID PROGRAM_ID
, ICR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, ICR.STATUS STATUS
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CU.ACCOUNT_NUMBER CUSTOMER_NUMBER
, SU.LOCATION BILLING_LOCATION
, CT.TRX_NUMBER TRX_NUMBER
, CI.CONS_BILLING_NUMBER BILLING_NUMBER
, RM.NAME RECEIPT_METHOD_NAME
, BB.BANK_NAME BANK_NAME
, BA.BANK_BRANCH_ID REMITTANCE_BANK_BRANCH_ID
, BA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER
, ICR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, ICR.ATTRIBUTE1 ATTRIBUTE1
, ICR.ATTRIBUTE2 ATTRIBUTE2
, ICR.ATTRIBUTE3 ATTRIBUTE3
, ICR.ATTRIBUTE4 ATTRIBUTE4
, ICR.ATTRIBUTE5 ATTRIBUTE5
, ICR.ATTRIBUTE6 ATTRIBUTE6
, ICR.ATTRIBUTE7 ATTRIBUTE7
, ICR.ATTRIBUTE8 ATTRIBUTE8
, ICR.ATTRIBUTE9 ATTRIBUTE9
, ICR.ATTRIBUTE10 ATTRIBUTE10
, ICR.ATTRIBUTE11 ATTRIBUTE11
, ICR.ATTRIBUTE12 ATTRIBUTE12
, ICR.ATTRIBUTE13 ATTRIBUTE13
, ICR.ATTRIBUTE14 ATTRIBUTE14
, ICR.ATTRIBUTE15 ATTRIBUTE15
, L_RCS.MEANING RECEIPT_CREATION_STATUS_DSP
, RC.CREATION_STATUS RECEIPT_CREATION_STATUS
, DECODE( NVL(ICR.SPECIAL_TYPE
, 'SINGLE')
, 'SINGLE'
, NVL(ICR.AMOUNT_APPLIED
, ICR.AMOUNT + NVL(ICR.FACTOR_DISCOUNT_AMOUNT
, 0))
, NULL)
, ICR.TRANS_TO_RECEIPT_RATE
, PS.INVOICE_CURRENCY_CODE
, ICR.APPLICATION_NOTES
, ICR.APPLICATION_REF_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPLICATION_REF_TYPE'
, ICR.APPLICATION_REF_TYPE) APPLICATION_REF_TYPE_MEANING
, ICR.CUSTOMER_REASON
, ICR.CUSTOMER_REFERENCE
, DECODE(NVL(ICR.SPECIAL_TYPE
, 'SINGLE')
, 'SINGLE'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, ARP_DEDUCTION.OVERAPPLICATION_INDICATOR (PS.AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING
, NVL(ICR.AMOUNT_APPLIED
, ICR.AMOUNT + NVL(ICR.FACTOR_DISCOUNT_AMOUNT
, 0)) ) )
, NULL ) OVERAPPLICATION_INDICATOR
FROM AR_INTERIM_CASH_RECEIPTS ICR
, IBY_EXT_BANK_ACCOUNTS_V EBA
, AR_PAYMENT_SCHEDULES PS
, AR_CONS_INV CI
, AR_RECEIPT_METHODS RM
, HZ_CUST_ACCOUNTS CU
, HZ_PARTIES PARTY
, RA_CUSTOMER_TRX CT
, HZ_CUST_SITE_USES SU
, GL_DAILY_CONVERSION_TYPES GD
, AR_RECEIPT_CLASSES RC
, AR_LOOKUPS L_RCS
, CE_BANK_ACCOUNTS BA
, CE_BANK_ACCT_USES USES
, CE_BANK_BRANCHES_V BB
, AR_BATCHES BT
WHERE RM.RECEIPT_METHOD_ID(+) = ICR.RECEIPT_METHOD_ID
AND USES.BANK_ACCT_USE_ID(+) = ICR.REMIT_BANK_ACCT_USE_ID
AND BA.BANK_ACCOUNT_ID(+)=USES.BANK_ACCOUNT_ID
AND BB.BRANCH_PARTY_ID(+) = BA.BANK_BRANCH_ID
AND CU.CUST_ACCOUNT_ID(+) = ICR.PAY_FROM_CUSTOMER
AND EBA.EXT_BANK_ACCOUNT_ID(+) = ICR.CUSTOMER_BANK_ACCOUNT_ID
AND CU.PARTY_ID = PARTY.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = ICR.SITE_USE_ID
AND RC.RECEIPT_CLASS_ID = RM.RECEIPT_CLASS_ID
AND L_RCS.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS'
AND L_RCS.LOOKUP_CODE = RC.CREATION_STATUS
AND GD.CONVERSION_TYPE(+) = ICR.EXCHANGE_RATE_TYPE
AND CT.CUSTOMER_TRX_ID(+) = ICR.CUSTOMER_TRX_ID
AND BT.BATCH_ID(+) = ICR.BATCH_ID
AND PS.PAYMENT_SCHEDULE_ID(+) = ICR.PAYMENT_SCHEDULE_ID
AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID