Friday 5 April 2013

PO (2way,3way,4way) Matching Query IN ORACLE

-- VENDOR, PO AND INVOICE DETAILS (2 WAY MATCH)
===============================================

/* Formatted on 4/5/2013 4:36:10 PM (QP5 v5.114.809.3010) */
SELECT   DISTINCT
         a.org_id "ORG ID",
         e.segment1 "VENDOR NUM",
         e.vendor_name "SUPPLIER NAME",
         UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
         f.vendor_site_code "VENDOR SITE CODE",
         f.address_line1 "ADDRESS",
         f.city "CITY",
         f.country "COUNTRY",
         TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
         d.segment1 "PO NUM",
         d.type_lookup_code "PO TYPE",
         c.quantity_ordered "QTY ORDERED",
         c.quantity_cancelled "QTY CANCELLED",
         g1.quantity_received,
         g1.quantity_rejected,
         g1.quantity_billed,
         g.item_id "ITEM ID",
         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 APPROVED",
         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",
         a.amount_paid
  FROM   ap.ap_invoices_all a,
         ap.ap_invoice_distributions_all b,
         po.po_distributions_all c,
         po.po_headers_all d,
         ap.ap_suppliers e,
         ap.ap_supplier_sites_all f,
         po.po_lines_all g,
         po.po_line_locations_all g1
 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 g1.po_header_id = c.po_header_id
         AND g1.po_line_id = g.po_line_id
         AND c.po_line_id = g.po_line_id
         AND c.line_location_id = g1.line_location_id
         AND g1.inspection_required_flag = 'N'
         AND g1.receipt_required_flag = 'N'

-- VENDOR, PO AND INVOICE DETAILS (3 WAY MATCH)
===============================================

/* Formatted on 4/5/2013 4:36:29 PM (QP5 v5.114.809.3010) */
SELECT   DISTINCT
         a.org_id "ORG ID",
         e.segment1 "VENDOR NUM",
         e.vendor_name "SUPPLIER NAME",
         UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
         f.vendor_site_code "VENDOR SITE CODE",
         f.address_line1 "ADDRESS",
         f.city "CITY",
         f.country "COUNTRY",
         TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
         d.segment1 "PO NUM",
         d.type_lookup_code "PO TYPE",
         c.quantity_ordered "QTY ORDERED",
         c.quantity_cancelled "QTY CANCELLED",
         g1.quantity_received,
         g1.quantity_rejected,
         g1.quantity_billed,
         g.item_id "ITEM ID",
         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 APPROVED?",
         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",
         a.amount_paid
  FROM   ap.ap_invoices_all a,
         ap.ap_invoice_distributions_all b,
         po.po_distributions_all c,
         po.po_headers_all d,
         ap.ap_suppliers e,
         ap.ap_supplier_sites_all f,
         po.po_lines_all g,
         po.po_line_locations_all g1
 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 g1.po_header_id = c.po_header_id
         AND g1.po_line_id = g.po_line_id
         AND c.po_line_id = g.po_line_id
         AND c.line_location_id = g1.line_location_id
         AND g1.inspection_required_flag = 'N'
         AND g1.receipt_required_flag = 'Y'

-- VENDOR, PO AND INVOICE DETAILS (4 WAY MATCH)
================================================

/* Formatted on 4/5/2013 4:36:48 PM (QP5 v5.114.809.3010) */
SELECT   DISTINCT
         a.org_id "ORG ID",
         e.segment1 "VENDOR NUM",
         e.vendor_name "SUPPLIER NAME",
         UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
         f.vendor_site_code "VENDOR SITE CODE",
         f.address_line1 "ADDRESS",
         f.city "CITY",
         f.country "COUNTRY",
         TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
         d.segment1 "PO NUM",
         d.type_lookup_code "PO TYPE",
         c.quantity_ordered "QTY ORDERED",
         c.quantity_cancelled "QTY CANCELLED",
         g1.quantity_received,
         g1.quantity_rejected,
         g1.quantity_billed,
         g.item_id "ITEM ID",
         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 APPROVED?",
         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",
         a.amount_paid
  FROM   ap.ap_invoices_all a,
         ap.ap_invoice_distributions_all b,
         po.po_distributions_all c,
         po.po_headers_all d,
         ap.ap_suppliers e,
         ap.ap_supplier_sites_all f,
         po.po_lines_all g,
         po.po_line_locations_all g1
 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 g1.po_header_id = c.po_header_id
         AND g1.po_line_id = g.po_line_id
         AND c.po_line_id = g.po_line_id
         AND c.line_location_id = g1.line_location_id
         AND g1.inspection_required_flag = 'Y'
         AND g1.receipt_required_flag = 'Y'

  -- VENDOR, PO, INVOICE AND PAYMENT DETAILS
==============================================

/* Formatted on 4/5/2013 4:37:08 PM (QP5 v5.114.809.3010) */
  SELECT   DISTINCT
           a.org_id "ORG ID",
           e.segment1 "VENDOR NUMBER",
           e.vendor_name "VENDOR NAME",
           UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
           f.vendor_site_code "VENDOR SITE CODE",
           TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
           d.segment1 "PO NUM",
           d.type_lookup_code "PO TYPE",
           c.quantity_ordered "QTY ORDERED",
           c.quantity_cancelled "QTY CANCELLED",
           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 APPROVED?",
           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",
           a.amount_paid,
           h.check_id,
           i.check_number,
           h.invoice_payment_id,
           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,
           ap.ap_suppliers e,
           ap.ap_supplier_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 c.po_header_id IS NOT NULL
           AND a.payment_status_flag = 'Y'
           AND d.type_lookup_code != 'BLANKET'
ORDER BY   E.VENDOR_NAME

No comments:

Post a Comment