Tuesday, 24 July 2012

Total Credit related to a customer – sql query

What is the Credit Note for and when to use it ?
A Credit Note or Credit Memo is a document used to adjust or rectify errors made in a sales invoice which has already been processed and sent to a customer. If you have already sent an invoice to a customer but now need to provide a credit for that invoice, you would send them a Credit Note or Credit Memo. You can think of a credit note as a “negative invoice.”
Some examples of when you would use a Credit Note
* Unit price overcharged or over-billed : For example you issued an invoice for an item for $1100 when the correct price of the item should have been $1010 instead. Therefore you need to issue Credit Note to give a credit of $90 to your customer for the amount over-billed. View Sample
* Goods short shipped : You invoiced a customer for 10 units of your product but only shipped 9 units to them by mistake. The customer then calls you to say that 9 units are okay and does not want the shortfall item at the moment. Therefore you need to issue a Credit Note to credit your customer for the shortfall quantity of 1 unit. View Sample
* Faulty goods returned or goods rejected by customer. You would issue a credit note for the goods returned to correct your Accounts Receivable and Inventory. View Sample
* Product Wrongly Shipped : You wrongly invoiced and shipped Product A when the customer actually ordered Product B which may or may not be at a different price. To rectify this, you would then ship Product B together with a Credit Note for Product A and another invoice for product B. This will restore the inventory and Accounts Receivable in your books while billing the customer for the correct item and amount. Meanwhile the customer returns the incorrect Product A. View Sample
* Discounts given after the invoice is issued: You sent an invoice for $1100. The customer then calls you asking for a discount and ask you to waive the $100 making the net invoice amount $1000. You agree to this in good faith. You would then issue a credit note for $100 to this customer to adjust for the discount given. View Sample
* To Write-off Customer Short Payments : You send an invoice for say $2010. The customer sends you a short payment of $2000 only. You do not wish to recover the shortfall amount but your books indicate that $10 is still owing on this invoice. You can then issue a credit note of $10 to write-off the shortfall amount.
Here is the simple SQL script that gives the total credit for a customer in oracle applications.
SELECT   NVL (SUM (apsa.amount_due_remaining), 0)
              FROM ar_payment_schedules_all apsa
             WHERE apsa.customer_site_use_id = ‘&&site_use_id’
               AND apsa.CLASS = ‘CM’
               AND apsa.STATUS = ‘OP’
          GROUP BY apsa.invoice_currency_code,

No comments:

Post a Comment