2 Replies Latest reply on Feb 1, 2011 4:18 AM by makdutakdu

    latest vendor name based on item

    makdutakdu
      hi

      when i do the following query , i am taking an eg of the item_no 1020101001
      select msib.segment1,max(pha.approved_date) from
      po_headers_all pha,
      po_lines_all pla,
      po_vendors pv,
      mtl_system_items_b msib
      where
      pha.po_header_id = pla.po_header_id
      and pla.item_id = msib.inventory_item_id
      and pv.vendor_id=pha.vendor_id
      and msib.segment1='1020101001'
      --and pha.approved_date=max(pha.approved_date)
      group by 
      --item_id.
      msib.segment1
      this is the output i get

      segment1 MAX(PHA.APPROVED_DATE)
      1020101001 1/9/2011 3:16:48 PM
      when i do the following query
      
      select msib.segment1,max(pha.approved_date) ,pv.vendor_name from
      po_headers_all pha,
      po_lines_all pla,
      po_vendors pv,
      mtl_system_items_b msib
      where
      pha.po_header_id = pla.po_header_id
      and pla.item_id = msib.inventory_item_id
      and pv.vendor_id=pha.vendor_id
      and msib.segment1='1020101001'
      --and pha.approved_date=max(pha.approved_date)
      group by 
      --item_id.
      msib.segment1
      ,pv.vendor_name
      
      
      
      
      segment1           MAX(PHA.APPROVED_DATE)  VENDOR_NAME
       
      1020101001    7/21/2008 9:09:20 AM   TRIZAC  ABU DHABI
      1020101001   1/9/2011 3:16:48 PM        SAUDI CEMENT COMPANY
      1020101001   2/14/2010 4:03:46 PM     UNION CEMENT NORCEM CO.
      1020101001  5/19/2010 3:08:32 PM      AS CIMENTO SANAYI VE TICARET A.S.
      
      
      
      i require the most recent vendor_name for a particular item ( i require the output as below)
      
      segment1 MAX(PHA.APPROVED_DATE) VENDOR_NAME
      
      
      1020101001 1/9/2011 3:16:48 PM SAUDI CEMENT COMPANY
      kindly guide me
      thanking in advance
        • 1. Re: latest vendor name based on item
          813340
          I am not sure what you are looking to fetch. For the scenario you have given you need to use the sub-query.

          SELECT A,SEGMENT1, A.APPROVED_DATE, PV.VENDOR_NAME
          FROM (SELECT msib.segment1, MAX(PH.approved_date) approved_date
          FROM PO_LINES_ALL PL,
          PO_HEADERS_ALL PH
          mtl_system_items_b msib
          WHERE PL.po_header_id = PH.po_header_id
          AND MSIB.item_id = PL.ITEM_ID
          AND MSIB.segment1 = :1
          GROUP BY MSIB.segment1) A,
          PO_HEADERS_ALL PH,
          PO_VENDORS PV
          WHERE A.approved_date = PH.approved_date
          AND PV.VENDOR_ID =PH.vendor_id

          - The query should have right filters (item_id, vendor_id or document_num), otherwise it will behave very badly.
          - They can return multiple recods if there are more than one PO approved at same time. In that case the outer query also needs an aggregation.

          Thanks, Siva
          • 2. Re: latest vendor name based on item
            makdutakdu
            hi

            i already got it , put max(pha.approved_date) in the and part of the query

            thanks