Thursday, June 23, 2011

Single Query For P To P Cycle

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?",
       a.amount_paid, h.amount, 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';

1 comment:

Anonymous said...

huge information blog

Post a Comment

Best Blogger TipsGet Flower Effect