Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

compare two invoices with same distribution line count

user13785045Feb 27 2013 — edited Mar 14 2013
I am trying to pull data out of Oracle Payables - invoices for which the invoice amount ,the vendor and distribution line count is same.
I could achieve pulling invoices with same Vendor having same amount.But finding hard to compare the counts.
Can anyone share ideas on how to achieve this ... Tried self join but did not work.
The query which I used is as follows :

select invoice_num,invoice_id,invoice_amount,vendor_id,
(select vendor_name from apps.po_vendors where vendor_id=aia.vendor_id) vendor_name,
(select count(*) from apps.ap_invoice_distributions_all aid where aid.invoice_id=aia.invoice_id) line_count
from apps.ap_invoices_all aia
where invoice_amount in (select aiab.invoice_amount
from apps.ap_invoices_all aiab
where aiab.creation_date >='01-AUG-2012'
and vendor_id=aia.vendor_id
group by aiab.invoice_amount
Having (count(aiab.invoice_amount) >1))
and aia.creation_date >='01-AUG-2012'

Thanks in Advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 11 2013
Added on Feb 27 2013
6 comments
420 views