VIEW: APPS.IBE_PAYMENT_DETAIL_V

Source

View Text - Preformatted

SELECT app.cash_receipt_id cash_receipt_id , decode(app.applied_payment_schedule_id,-1,l_acc.meaning, ps_inv.trx_number) trx_number , decode(app.applied_payment_schedule_id,-1,null,l_class.meaning) trx_class_name , app.amount_applied , to_number(decode(app.applied_payment_schedule_id,-1,null, nvl(app.earned_discount_taken,0) + nvl(app.unearned_discount_taken,0))) discount , app.apply_date apply_date , ps_inv.amount_due_original , to_number(decode(app.applied_payment_schedule_id,-1, app.amount_applied*-1 ,ps_inv.amount_due_remaining)) amount_due_remaining , ps_inv.customer_trx_id customer_trx_id , ps_inv.status status , AL_STATUS.meaning status_meaning , ps_inv.invoice_currency_code currency_code , cr.receipt_number ,ps_inv.org_id org_id FROM ar_payment_schedules_all ps_inv ,ar_lookups l_class ,ar_lookups l_acc ,hz_cust_accounts acct ,ar_receivable_applications_all app ,ar_cash_receipts_all cr ,ar_lookups AL_STATUS WHERE app.applied_payment_schedule_id = ps_inv.payment_schedule_id AND app.display = 'Y' AND ps_inv.customer_id = acct.cust_account_id AND ps_inv.class = l_class.lookup_code AND l_class.lookup_type = 'INV/CM' AND l_acc.lookup_type = 'PAYMENT_TYPE' AND l_acc.lookup_code= 'ACC' AND cr.cash_receipt_id = app.cash_receipt_id AND ps_inv.STATUS = AL_STATUS.LOOKUP_CODE AND AL_STATUS.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'

View Text - HTML Formatted

SELECT APP.CASH_RECEIPT_ID CASH_RECEIPT_ID
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, L_ACC.MEANING
, PS_INV.TRX_NUMBER) TRX_NUMBER
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, NULL
, L_CLASS.MEANING) TRX_CLASS_NAME
, APP.AMOUNT_APPLIED
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, NULL
, NVL(APP.EARNED_DISCOUNT_TAKEN
, 0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN
, 0))) DISCOUNT
, APP.APPLY_DATE APPLY_DATE
, PS_INV.AMOUNT_DUE_ORIGINAL
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, APP.AMOUNT_APPLIED*-1
, PS_INV.AMOUNT_DUE_REMAINING)) AMOUNT_DUE_REMAINING
, PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS_INV.STATUS STATUS
, AL_STATUS.MEANING STATUS_MEANING
, PS_INV.INVOICE_CURRENCY_CODE CURRENCY_CODE
, CR.RECEIPT_NUMBER
, PS_INV.ORG_ID ORG_ID
FROM AR_PAYMENT_SCHEDULES_ALL PS_INV
, AR_LOOKUPS L_CLASS
, AR_LOOKUPS L_ACC
, HZ_CUST_ACCOUNTS ACCT
, AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_CASH_RECEIPTS_ALL CR
, AR_LOOKUPS AL_STATUS
WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
AND APP.DISPLAY = 'Y'
AND PS_INV.CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND PS_INV.CLASS = L_CLASS.LOOKUP_CODE
AND L_CLASS.LOOKUP_TYPE = 'INV/CM'
AND L_ACC.LOOKUP_TYPE = 'PAYMENT_TYPE'
AND L_ACC.LOOKUP_CODE= 'ACC'
AND CR.CASH_RECEIPT_ID = APP.CASH_RECEIPT_ID
AND PS_INV.STATUS = AL_STATUS.LOOKUP_CODE
AND AL_STATUS.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'