Query to find Purchase Order and Invoice Details

1SELECT a.org_id                          "ORG ID",
2       e.vendor_name                     "VENDOR NAME",
3       UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
4       f.vendor_site_code                "VENDOR SITE",
5       f.address_line1                   "ADDRESS",
6       f.city                            "CITY",
7       f.country                         "COUNTRY",
8       TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
9       d.segment1                        "PO NUMBER",
10       d.type_lookup_code                "PO TYPE",
11       c.quantity_ordered                "QTY ORDERED",
12       c.quantity_cancelled              "QTY CANCALLED",
13       g.item_description                "ITEM DESCRIPTION",
14       g.unit_price                      "UNIT PRICE",
15         (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))
16       * NVL (g.unit_price, 0)
17          "PO Line Amount",
18       (SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
19          FROM po.po_headers_all ph
20         WHERE ph.po_header_id = d.po_header_id)
21          "PO STATUS",
22       a.invoice_type_lookup_code        "INVOICE TYPE",
23       a.invoice_amount                  "INVOICE AMOUNT",
24       TO_CHAR (TRUNC (a.invoice_date))  "INVOICE DATE",
25       a.invoice_num                     "INVOICE NUMBER",
26       (SELECT DECODE (x.match_status_flag, 'A', 'Approved')
27          FROM ap.ap_invoice_distributions_all x
28         WHERE x.invoice_distribution_id = b.invoice_distribution_id)
29          "Invoice Approved?",
30       a.amount_paid,
31       h.amount,
32       i.check_number                    "CHEQUE NUMBER",
33       TO_CHAR (TRUNC (i.check_date))    "PAYMENT DATE"
34  FROM ap.ap_invoices_all              a,
35       ap.ap_invoice_distributions_all b,
36       po.po_distributions_all         c,
37       po.po_headers_all               d,
38       po.po_vendors                   e,
39       po.po_vendor_sites_all          f,
40       po.po_lines_all                 g,
41       ap.ap_invoice_payments_all      h,
42       ap.ap_checks_all                i
43 WHERE     a.invoice_id = b.invoice_id
44       AND b.po_distribution_id = c.po_distribution_id(+)
45       AND c.po_header_id = d.po_header_id(+)
46       AND e.vendor_id(+) = d.vendor_id
47       AND f.vendor_site_id(+) = d.vendor_site_id
48       AND d.po_header_id = g.po_header_id
49       AND c.po_line_id = g.po_line_id
50       AND a.invoice_id = h.invoice_id
51       AND h.check_id = i.check_id
52       AND f.vendor_site_id = i.vendor_site_id
53       AND c.po_header_id IS NOT NULL
54       AND a.payment_status_flag = 'Y'
55       AND d.type_lookup_code != 'BLANKET'