Query to find the AP invoices that are applied to multiple Purchase Orders

•By Amol Jadhav
Query to find the AP invoices that are applied to multiple Purchase Orders
Oracle apps ebs sql to find the AP invoices that are applied to multiple Purchase Orders
SQL Query
sql
1SELECT COUNT (DISTINCT d.segment1), a.invoice_num
2    FROM ap.ap_invoices_all            A,
3         ap.ap_invoice_distributions_all b,
4         po.po_distributions_all       C,
5         po.po_headers_all             d,
6         po.po_lines_all               G
7   WHERE     a.invoice_id = b.invoice_id
8         AND b.po_distribution_id = c.po_distribution_id(+)
9         AND c.po_header_id = d.po_header_id(+)
10         AND d.po_header_id = g.po_header_id
11         AND c.po_line_id = g.po_line_id
12         AND c.po_header_id IS NOT NULL
13         AND a.payment_status_flag = 'Y'
14         AND d.type_lookup_code != 'BLANKET'
15         AND a.org_id = 1234
16         AND a.org_id = d.org_id
17GROUP BY a.invoice_num
18  HAVING COUNT (DISTINCT d.segment1) > 1