Search 800 + Posts

Mar 24, 2011

Query to get UNPAID Invoices (Oracle Payables)

Below is Simple Query to get Query to get UNPAID Invoices (Oracle Payables). I have alos build condition to exclude ZERO Dollar Invoices.

select i.invoice_num,v.vendor_name,i.invoice_date,ps.due_date,
i.invoice_amount,i.amount_paid,ps.amount_remaining, SUM(i.invoice_amount),sum(ps.amount_remaining)
FROM    ap_payment_schedules_all ps,
        ap_invoices_all i,
        po_vendors v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.vendor_id = v.vendor_id
AND     i.vendor_site_id = vs.vendor_site_id
AND     i.payment_status_flag ='N'
AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
AND    i.cancelled_date is not null
group by v.vendor_name,i.invoice_num,i.invoice_date,ps.due_date,i.invoice_amount,i.amount_paid,
ps.amount_remaining
Order by v.vendor_name,i.invoice_num


----
Special Thanks to Kunal for suggesting "i.cancelled_date is not null" , with this new condition Query will not reterive Cancelled Invoices" from database.
-----

10 comments:

  1. UnknownMarch 24, 2011 at 11:32 PM

    Hi,

    You can also add the condition to check if the invoices are not cancelled,

    i.cancelled_date is not null

    Thanks,
    Kunal.

    ReplyDelete
  2. eoracleappsMarch 24, 2011 at 11:45 PM

    Thanks for Update and suggestion, i have just included "Not Cancelled" condition.

    ReplyDelete
  3. AnonymousNovember 8, 2011 at 8:15 AM

    Hi Kunal,

    I am not gettting the output if run this query, also if i comment
    --AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))!= 0

    this line i am getting the output.

    Can you please tell me as to what is missing?

    Thanks,
    shoban
    shobanbabutc@gmail.com

    ReplyDelete
  4. eoracleappsNovember 8, 2011 at 11:06 AM

    AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) != 0

    above condition is to EXCLUDE the UNPAID Invoices Zero $ invoices.

    Hope that help

    ReplyDelete
  5. aresSeptember 18, 2013 at 5:47 AM

    Hi,

    I think the condition should rather be :

    i.cancelled_date is null

    if the cancelled date is not null wouldn't it mean the invoice is cancelled ?

    Regards
    Srikanth Kanuri

    ReplyDelete
    Replies
    1. AnonymousFebruary 25, 2014 at 8:37 AM

      I go with Srikanth.. 'Cancelled_date is null' is right..

      Delete
  6. AnonymousDecember 10, 2014 at 3:12 AM

    yes cancelled date is null is right

    ReplyDelete
  7. Payable Aging query lesser then given date like dd-mon-yyyyAugust 19, 2015 at 12:35 AM

    Hi,

    Suppliers having the balances in multiple organization, so you can add multiple org_id and invoice_type_lookup_code in the existing query.

    Regards
    Thillainathan

    ReplyDelete
  8. Bizinsight Consulting IncAugust 31, 2015 at 1:20 PM

    Thanks Thillainathan

    Yeap you are very correct , by adding Operating Unit ( org_id) field we can easily identify the data for multi Operating Units.

    ReplyDelete
  9. ArunMarch 18, 2019 at 1:12 AM

    The condition is "cancelled_date is not null" wrong. Kindly modify it for your use.

    ReplyDelete

Subscribe to: Post Comments (Atom)