ITEM_INSPECTION_REQ_UPD_QRY_18SEP11

SELECT   pol.RECEIVING_ROUTING_ID , poh.segment1 , ood.ORGANIZATION_CODE
  FROM   po_line_locations_all pol, po_headers_all poh, org_organization_definitions ood
 WHERE  
 pol.po_header_id=poh.po_header_id and
 ood.ORGANIZATION_ID=pol.SHIP_TO_ORGANIZATION_ID and
 pol.po_line_id IN
               (SELECT   po_line_id
                  FROM   po_lines_all
                 WHERE   item_id IN
                               (SELECT   inventory_item_id
                                  FROM   mtl_system_items_b msi,
                                         xx_item_map map
                                 WHERE   MAP.ITM_NEW_GRP =
                                            SUBSTR (msi.segment1, 1, 2)
                                         AND map.ITM_CAT = 'MRO'))
                                         and RECEIVING_ROUTING_ID =3 --3
                                         and length(poh.segment1)=8

UPDATE   po_line_locations_all
   SET   RECEIVING_ROUTING_ID = 2
 WHERE   po_line_id IN
               (SELECT   pol.po_line_id
                  FROM   po_line_locations_all pol,
                         po_headers_all poh,
                         org_organization_definitions ood
                 WHERE   pol.po_header_id = poh.po_header_id
                         AND ood.ORGANIZATION_ID =
                               pol.SHIP_TO_ORGANIZATION_ID
                         AND pol.po_line_id IN
                                  (SELECT   po_line_id
                                     FROM   po_lines_all
                                    WHERE   item_id IN
                                                  (SELECT   inventory_item_id
                                                     FROM   mtl_system_items_b msi,
                                                            xx_item_map map
                                                    WHERE   MAP.ITM_NEW_GRP =
                                                               SUBSTR (
                                                                  msi.segment1,
                                                                  1,
                                                                  2
                                                               )
                                                            AND map.ITM_CAT =
                                                                  'MRO'))
                         AND RECEIVING_ROUTING_ID = 3
                         AND LENGTH (poh.segment1) = 8)