Saturday, June 25, 2016

Query for Application of Receipts/Credit Memos to Invoices/Debit Memos

I have a custom pl/sql program that applied credit memos and receipts to invoices and debit memos. At the end of program i wanted to see what has been applied to what. Receipt UI was not very good for viewing All applications. So i wrote query to check how receipts and credit memos are applied to invoices and debit memos.

I noticed that when a receipt is applied to invoice or debit memo, APPLY_DATE in AR_RECEIVABLES_APPLICATIONS table has no time component but when a credit memo is applied to invoice or debit memo, it has time component.

This query tells me what has been applied on 13th Jun 2016 between 8 am to 10 am. If APPLY_DATE for receipt application had time component, there would not be any need for condition of creation_date column. I can not use only creation date because there is no index on this column.

1 comment:

  1. Hi, I was wondering if you can help me out in writing a similar query.
    We need to find invoices (INV) that were generated in FY less than the FY of the
    receipt. And, the invoice has been refunded (DM).
    The Receipt should have applied invoice (FY < FY of the receipt) and applied debit memo.
    The request is for receipts and invoices that were applied prior year invoices and refunded (debit memo applied).so I need to look for a receipt that got applied to a DM in FY16 then narrow from that list to see if it got applied to invoice before