Query to find invoices unmatched to purchase order
[convertkit form=1415728]
select *
from ap_invoices_all ai
where not exists
(select 1
from ap_invoice_lines_all ail
where ai.invoice_id = ail.invoice_id
and ail.po_header_id is not null);
Note: this Query will not list an invoice if invoice has multiple lines where some of them are matched or unmatched.
OR
select * from ap_invoices_all ai, ap_invoice_lines_all ail where ai.invoice_id = ail.invoice_id and ail.po_header_id is null
Note: this Query will list all invoice if invoice has multiple lines where some of them are matched or unmatched.
Hi Tim
sorry for late reply 🙂
you can achieve this easily by :
select *
from ap_invoices_all ai, ap_invoice_lines_all ail
where ai.invoice_id = ail.invoice_id
and ail.po_header_id is null
and trunc(ai.invoice_date) = trunc(last_day (add_months (sysdate, -1)))
just you need to change sysdate with specific date you want.
hope this helpful.
Hi, thanks for your sharing.
If we would like to know, last day of previous monthly, how many unmatched receipt, how to query? Kindly help, thanks
Hi, thanks for your sharing.
If we would like to know, last day of previous monthly, how many unmatched receipt, how to query? Kindly help, thanks