Query to find Purchase Order and Invoice Details

Query to find Purchase Order and Invoice Details
Oracle apps ebs Purchase Order and Invoice link
oracle purchase order query
oracle purchase order tables

SELECT a.org_id                          "ORG ID",
       e.vendor_name                     "VENDOR NAME",
       UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
       f.vendor_site_code                "VENDOR SITE",
       f.address_line1                   "ADDRESS",
       f.city                            "CITY",
       f.country                         "COUNTRY",
       TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
       d.segment1                        "PO NUMBER",
       d.type_lookup_code                "PO TYPE",
       c.quantity_ordered                "QTY ORDERED",
       c.quantity_cancelled              "QTY CANCALLED",
       g.item_description                "ITEM DESCRIPTION",
       g.unit_price                      "UNIT PRICE",
         (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))
       * NVL (g.unit_price, 0)
          "PO Line Amount",
       (SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
          FROM po.po_headers_all ph
         WHERE ph.po_header_id = d.po_header_id)
          "PO STATUS",
       a.invoice_type_lookup_code        "INVOICE TYPE",
       a.invoice_amount                  "INVOICE AMOUNT",
       TO_CHAR (TRUNC (a.invoice_date))  "INVOICE DATE",
       a.invoice_num                     "INVOICE NUMBER",
       (SELECT DECODE (x.match_status_flag, 'A', 'Approved')
          FROM ap.ap_invoice_distributions_all x
         WHERE x.invoice_distribution_id = b.invoice_distribution_id)
          "Invoice Approved?",
       i.check_number                    "CHEQUE NUMBER",
       TO_CHAR (TRUNC (i.check_date))    "PAYMENT DATE"
  FROM ap.ap_invoices_all              a,
       ap.ap_invoice_distributions_all b,
       po.po_distributions_all         c,
       po.po_headers_all               d,
       po.po_vendors                   e,
       po.po_vendor_sites_all          f,
       po.po_lines_all                 g,
       ap.ap_invoice_payments_all      h,
       ap.ap_checks_all                i
 WHERE     a.invoice_id = b.invoice_id
       AND b.po_distribution_id = c.po_distribution_id(+)
       AND c.po_header_id = d.po_header_id(+)
       AND e.vendor_id(+) = d.vendor_id
       AND f.vendor_site_id(+) = d.vendor_site_id
       AND d.po_header_id = g.po_header_id
       AND c.po_line_id = g.po_line_id
       AND a.invoice_id = h.invoice_id
       AND h.check_id = i.check_id
       AND f.vendor_site_id = i.vendor_site_id
       AND c.po_header_id IS NOT NULL
       AND a.payment_status_flag = 'Y'
       AND d.type_lookup_code != 'BLANKET'
Related posts: Upload your own post and refer it anywhere anytime:

Leave a Reply

%d bloggers like this: