1 Reply Latest reply on Nov 28, 2013 11:27 AM by user11928461

    Need sql query to find out same item is used in open po and closed po

    bmn

      Hi Experts,

       

      I have an requirement is that, i need to find out the item is used in open po and closed po.

       

        example: A is the item is used in closed PO and Open PO.

       

        i need to write down the scripts how many same items is used in open po and closed po.

       

      if any sql script is there please share.

       

      Please let us know if any concerns.

       

      Thanks

      bmn

        • 1. Re: Need sql query to find out same item is used in open po and closed po
          user11928461

          Hi

           

          Please try below query

           

          select

          distinct c.segment1

          from

          po_lines_all a,

          po_line_locations_all b,

          mtl_system_items_b c

          where

          a.po_line_id= b.po_line_id

          and a.item_id=c.inventory_item_id

          and b.closed_code='OPEN'

          and a.item_id in ( select 

          item_id

          from

          po_lines_all d,

          po_line_locations_all e,

          mtl_system_items_b f

          where

          d.po_line_id= e.po_line_id

          and d.item_id=f.inventory_item_id

          and e.closed_code in ('CLOSED','FINALLY CLOSED','CLOSED FOR INVOICE','CLOSED FOR RECEIVING'));