Tuesday, 3 November 2015

last Item wise Purchase order details Query

SELECT pha.segment1 po, pvv.vendor_name vendor_name, msi.segment1 item_code,
       pla.unit_price
  FROM po_headers_all pha,
       po_lines_all pla,
       po_vendors pvv,
       mtl_system_items msi
 WHERE pha.po_header_id = pla.po_header_id
   AND pha.vendor_id = pvv.vendor_id
   AND pla.item_id = msi.inventory_item_id
   AND msi.organization_id = :p_organization_id
   AND (TRUNC (pha.creation_date), pha.vendor_id, pla.item_id, pha.org_id) IN (
          SELECT *
            FROM (SELECT   MAX (TRUNC (ph.creation_date)) ct_date,
                           ph.vendor_id, pl.item_id, ph.org_id
                      FROM po_headers_all ph, po_vendors pv, po_lines_all pl
                     WHERE ph.vendor_id = pv.vendor_id
                       AND ph.po_header_id = pl.po_header_id
                       AND pl.item_id = :p_item_id
                       AND ph.org_id = :p_org_id
                  GROUP BY pl.item_id, ph.org_id, ph.vendor_id))

No comments:

Post a Comment