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. 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. Thanks for Update and suggestion, i have just included "Not Cancelled" condition.

    ReplyDelete
  3. 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. 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. 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. I go with Srikanth.. 'Cancelled_date is null' is right..

      Delete
  6. yes cancelled date is null is right

    ReplyDelete
  7. 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. 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. The condition is "cancelled_date is not null" wrong. Kindly modify it for your use.

    ReplyDelete