View: ICX_AR_CASH_RECEIPTS_V

View Text

SELECT CR.ROWID ROW_ID
, CR.CASH_RECEIPT_ID CASH_RECEIPT_ID
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, CUST.CUSTOMER_NAME CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER CUSTOMER_NUMBER
, TO_CHAR(CR.AMOUNT
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( CR.CURRENCY_CODE
, 30)) AMOUNT
, CR.TYPE PAYMENT_TYPE
, AL_PAY_TYPE.MEANING TYPE
, CUST_BANK_BRANCH.BANK_NAME BANK_NAME
, TO_CHAR(-(PS.AMOUNT_APPLIED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( CR.CURRENCY_CODE
, 30)) APPLIED_AMOUNT
, CR.RECEIPT_DATE RECEIPT_DATE
, CUST_BANK.BANK_ACCOUNT_NUM CUSTOMER_BANK_ACCOUNT
, TO_CHAR(SUM(DECODE(RA.STATUS
, 'ACC'
, NVL(RA.AMOUNT_APPLIED
, 0)
, 0) )
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( CR.CURRENCY_CODE
, 30)) ON_ACCOUNT_AMOUNT
, PS.DUE_DATE MATURITY_DATE
, CR.CURRENCY_CODE CURRENCY_CODE
, TO_CHAR(SUM(DECODE(RA.STATUS
, 'UNAPP'
, NVL(RA.AMOUNT_APPLIED
, 0)
, 0))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( CR.CURRENCY_CODE
, 30)) UNAPPLIED_AMOUNT
, CR.STATUS RECEIPT_STATUS
, CR.COMMENTS COMMENTS
, CR.MISC_PAYMENT_SOURCE MISC_PAYMENT_SOURCE
, CR.EXCHANGE_RATE EXCHANGE_RATE
, CR.EXCHANGE_DATE EXCHANGE_RATE_DATE
, CR.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, CR.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER
, CR.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, CR.CUSTOMER_RECEIPT_REFERENCE CUSTOMER_RECEIPT_REFERENCE
, CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, CR.PAY_FROM_CUSTOMER CUSTOMER_ID
, CR.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, CR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID
, CR.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID
, CR.DEPOSIT_DATE DEPOSIT_DATE
, CR.REFERENCE_TYPE REFERENCE_TYPE
, CR.VAT_TAX_ID VAT_TAX_ID
, CR.REFERENCE_ID REFERENCE_ID
, CR.REMITTANCE_BANK_ACCOUNT_ID REMITTANCE_BANK_ACCOUNT_ID
, CR.RECEIVABLES_TRX_ID RECEIVABLES_TRX_ID
, CR.ATTRIBUTE1 ATTRIBUTE1
, CR.ATTRIBUTE2 ATTRIBUTE2
, CR.ATTRIBUTE3 ATTRIBUTE3
, CR.ATTRIBUTE4 ATTRIBUTE4
, CR.ATTRIBUTE5 ATTRIBUTE5
, CR.ATTRIBUTE6 ATTRIBUTE6
, CR.ATTRIBUTE7 ATTRIBUTE7
, CR.ATTRIBUTE8 ATTRIBUTE8
, CR.ATTRIBUTE9 ATTRIBUTE9
, CR.ATTRIBUTE10 ATTRIBUTE10
, CR.ATTRIBUTE11 ATTRIBUTE11
, CR.ATTRIBUTE12 ATTRIBUTE12
, CR.ATTRIBUTE13 ATTRIBUTE13
, CR.ATTRIBUTE14 ATTRIBUTE14
, CR.ATTRIBUTE15 ATTRIBUTE15
, CR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CR.REVERSAL_DATE REVERSAL_DATE
, CR.REVERSAL_CATEGORY REVERSAL_CATEGORY
, CR.REVERSAL_COMMENTS REVERSAL_COMMENTS
, CR.REVERSAL_REASON_CODE REVERSAL_REASON_CODE
, CR.OVERRIDE_REMIT_ACCOUNT_FLAG OVERRIDE_REMIT_BANK
, CR.CREATED_BY CREATED_BY
, CR.CREATION_DATE CREATION_DATE
, CR.LAST_UPDATED_BY LAST_UPDATED_BY
, CR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CR.REQUEST_ID REQUEST_ID
, CR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CR.PROGRAM_ID PROGRAM_ID
, CR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, CUST.JGZZ_FISCAL_CODE TAXPAYER_ID
, SITE_USES.LOCATION LOCATION
, SITE_USES.ADDRESS_ID ADDRESS_ID
, CUST_BANK_BRANCH.BANK_NAME CUSTOMER_BANK_NAME
, CUST_BANK_BRANCH.BANK_BRANCH_NAME CUSTOMER_BANK_BRANCH
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, SUM(DECODE(RA.STATUS
, 'UNID'
, NVL(RA.AMOUNT_APPLIED
, 0)
, 0)) UNIDENTIFIED_AMOUNT
, SUM(DECODE(RA.STATUS
, 'APP'
, NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)
, 0) ) DISCOUNTS_EARNED
, SUM(DECODE(RA.STATUS
, 'APP'
, NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0)) DISCOUNTS_UNEARNED
, SUM(DECODE(RA.STATUS
, 'APP'
, NVL(RA.EARNED_DISCOUNT_TAKEN
, 0)+ NVL(RA.UNEARNED_DISCOUNT_TAKEN
, 0)
, 0)) DISCOUNTS_TOTAL
FROM AR_LOOKUPS AL_PAY_TYPE
, AP_BANK_BRANCHES CUST_BANK_BRANCH
, AP_BANK_ACCOUNTS CUST_BANK
, RA_CUSTOMERS CUST
, AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS RA
, AR_CASH_RECEIPTS CR
, RA_SITE_USES SITE_USES
WHERE CR.PAY_FROM_CUSTOMER = CUST.CUSTOMER_ID (+)
AND CUST_BANK.BANK_ACCOUNT_ID (+) = CR.CUSTOMER_BANK_ACCOUNT_ID
AND CUST_BANK.BANK_BRANCH_ID = CUST_BANK_BRANCH.BANK_BRANCH_ID (+)
AND CR.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID (+)
AND PS.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
AND CR.CASH_RECEIPT_ID = RA.CASH_RECEIPT_ID(+)
AND AL_PAY_TYPE.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND CR.TYPE = AL_PAY_TYPE.LOOKUP_CODE GROUP BY CR.ROWID
, CR.CASH_RECEIPT_ID
, CR.CURRENCY_CODE
, CR.RECEIPT_NUMBER
, CR.RECEIPT_DATE
, CR.STATUS
, CR.COMMENTS
, CR.MISC_PAYMENT_SOURCE
, CR.EXCHANGE_RATE
, CR.EXCHANGE_DATE
, CR.EXCHANGE_RATE_TYPE
, CR.DOC_SEQUENCE_ID
, CR.DOC_SEQUENCE_VALUE
, CR.CUSTOMER_RECEIPT_REFERENCE
, CR.RECEIPT_METHOD_ID
, CR.PAY_FROM_CUSTOMER
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, CUST.JGZZ_FISCAL_CODE
, SITE_USES.LOCATION
, CR.CUSTOMER_SITE_USE_ID
, SITE_USES.ADDRESS_ID
, CR.CUSTOMER_BANK_ACCOUNT_ID
, CUST_BANK.BANK_ACCOUNT_NUM
, CUST_BANK_BRANCH.BANK_BRANCH_NAME
, CR.DISTRIBUTION_SET_ID
, CR.DEPOSIT_DATE
, CR.REFERENCE_TYPE
, CR.VAT_TAX_ID
, CR.REFERENCE_ID
, CR.REMITTANCE_BANK_ACCOUNT_ID
, PS.DUE_DATE
, PS.PAYMENT_SCHEDULE_ID
, CR.RECEIVABLES_TRX_ID
, CR.ATTRIBUTE1
, CR.ATTRIBUTE2
, CR.ATTRIBUTE3
, CR.ATTRIBUTE4
, CR.ATTRIBUTE5
, CR.ATTRIBUTE6
, CR.ATTRIBUTE7
, CR.ATTRIBUTE8
, CR.ATTRIBUTE9
, CR.ATTRIBUTE10
, CR.ATTRIBUTE11
, CR.ATTRIBUTE12
, CR.ATTRIBUTE13
, CR.ATTRIBUTE14
, CR.ATTRIBUTE15
, CR.ATTRIBUTE_CATEGORY
, CR.REVERSAL_DATE
, CR.REVERSAL_CATEGORY
, CR.REVERSAL_COMMENTS
, CR.REVERSAL_REASON_CODE
, CR.USSGL_TRANSACTION_CODE
, NULL
, NULL
, CR.OVERRIDE_REMIT_ACCOUNT_FLAG
, CR.CREATED_BY
, CR.CREATION_DATE
, CR.LAST_UPDATED_BY
, CR.LAST_UPDATE_DATE
, CR.LAST_UPDATE_LOGIN
, CR.REQUEST_ID
, CR.PROGRAM_APPLICATION_ID
, CR.PROGRAM_ID
, CR.PROGRAM_UPDATE_DATE
, PS.AMOUNT_APPLIED
, TO_CHAR(CR.AMOUNT
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( CR.CURRENCY_CODE
, 30))
, CR.AMOUNT
, CR.TYPE
, AL_PAY_TYPE.MEANING
, CUST_BANK_BRANCH.BANK_NAME

Columns