This discussion is archived
1 Reply Latest reply: Nov 28, 2013 3:27 AM by user11928461 RSS

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

bmn Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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'));

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points