Below is Simple Query to get Query to get UNPAID Invoices (Oracle Payables). I have alos build condition to exclude ZERO Dollar Invoices.
FROM ap_payment_schedules_all ps,
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,
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.
You can also add the condition to check if the invoices are not cancelled,
i.cancelled_date is not null
Thanks for Update and suggestion, i have just included "Not Cancelled" condition.ReplyDelete
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?
AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) != 0ReplyDelete
above condition is to EXCLUDE the UNPAID Invoices Zero $ invoices.
Hope that help
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 ?
I go with Srikanth.. 'Cancelled_date is null' is right..Delete
yes cancelled date is null is rightReplyDelete
Suppliers having the balances in multiple organization, so you can add multiple org_id and invoice_type_lookup_code in the existing query.
Yeap you are very correct , by adding Operating Unit ( org_id) field we can easily identify the data for multi Operating Units.
The condition is "cancelled_date is not null" wrong. Kindly modify it for your use.ReplyDelete