AR rebate taken

This Excel-Out report displays the Receivables rebate / discount the customers took for early payment including all the customer and payment details.
The only parameter is p_parameter01 representing the number of months for which to report the discounts. You can enhance it further and limit it by customer if needed.


SELECT hca.account_number "Customer Account #"
, hp.party_name  "Customer Name"
, rcta.trx_number  "Invoice #"
, rcta.trx_date  "Invoice Date"
,apsa.amount_due_original  "Invoice Amount"
,araa.amount_applied  "Payment Amount"
, acra.receipt_number "Receipt #"
, acra.RECEIPT_DATE  "Payment Date"
, ceil(acra.RECEIPT_DATE-rcta.trx_date) "Payment date - Invoice date"
, acra.amount  "Receipt Amount"
,TO_NUMBER (
             DECODE (
                SIGN (APPLIED_PAYMENT_SCHEDULE_ID),
                -1, NULL,
                  NVL (EARNED_DISCOUNT_TAKEN, 0)
                + NVL (UNEARNED_DISCOUNT_TAKEN, 0))) discount,EARNED_DISCOUNT_TAKEN, UNEARNED_DISCOUNT_TAKEN
FROM hz_parties hp
, hz_cust_accounts hca
, ra_customer_trx_all rcta
, ar_cash_receipts_all acra
, ar_payment_schedules_all apsa
, ar_receivable_applications_all araa
, ar_lookups al
where 1=1
and hp.party_id = hca.party_id
and rcta.sold_to_customer_id = hca.cust_account_id
and rcta.org_id = 81
and rcta.customer_trx_id = apsa.customer_trx_id
and araa.applied_customer_trx_id = apsa.customer_trx_id
and acra.cash_receipt_id = araa.cash_receipt_id
and acra.status = al.lookup_code
and al.lookup_type = 'PAYMENT_TYPE'
and trunc(months_between(sysdate,araa.creation_date)) <=to_number(p_parameter01)
and TO_NUMBER (
             DECODE (
                SIGN (APPLIED_PAYMENT_SCHEDULE_ID),
                -1, NULL,
                  NVL (EARNED_DISCOUNT_TAKEN, 0)
                + NVL (UNEARNED_DISCOUNT_TAKEN, 0)))!=0
                order by rcta.trx_number
;