[Home] [Help]
| Product: | IBE - iStore |
|---|---|
| Description: | This view is used to get the Payment Information in Post-sales Interaciton with Customer in iStore. This view is based on ar_cash_receipts,ar_payment_schedules. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT CR.ROWID ROW_ID
, CR.CASH_RECEIPT_ID CASH_RECEIPT_ID
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, CUST.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, CUST.CUSTOMER_NAME CUSTOMER_NAME
, CUST.ACCOUNT_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.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, CUST.PARTY_ID PARTY_ID
, CR.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, CR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_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
FROM AR_LOOKUPS AL_PAY_TYPE
, AP_BANK_BRANCHES CUST_BANK_BRANCH
, AP_BANK_ACCOUNTS CUST_BANK
, ASO_I_CUST_ACCOUNTS_V CUST
, AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS RA
, AR_CASH_RECEIPTS CR
WHERE CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_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 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.RECEIPT_METHOD_ID
, CUST.PARTY_ID
, CUST.CUSTOMER_NAME
, CUST.ACCOUNT_NUMBER
, CR.CUSTOMER_SITE_USE_ID
, CR.CUSTOMER_BANK_ACCOUNT_ID
, CUST_BANK.BANK_ACCOUNT_NUM
, CUST_BANK_BRANCH.BANK_BRANCH_NAME
, PS.PAYMENT_SCHEDULE_ID
, CR.AMOUNT
, CR.TYPE
, AL_PAY_TYPE.MEANING
, CUST_BANK_BRANCH.BANK_NAME
, PS.AMOUNT_APPLIED
, PS.DUE_DATE
, PS.PAYMENT_SCHEDULE_ID
, CUST.CUST_ACCOUNT_ID