ITEM_INSPECTION_REQ_UPD_NEW_QRY

UPDATE   po_line_locations_all
   SET   RECEIVING_ROUTING_ID = 2
 WHERE   SHIP_TO_ORGANIZATION_ID IN
               (SELECT   POLL.SHIP_TO_ORGANIZATION_ID ORGANIZATION_ID
                  FROM   po_line_locations_all POLL,
                         PO_LINES_ALL PLA,
                         MTL_SYSTEM_ITEMS_B MSI,
                         PO_HEADERS_ALL POH,
                         ORG_ORGANIZATION_DEFINITIONS OOD,
                         RCV_ROUTING_HEADERS RRH,
                         XX_ITEM_MAP MAP
                 WHERE       POLL.PO_LINE_ID = PLA.PO_LINE_ID
                         AND POH.PO_HEADER_ID = PLA.PO_HEADER_ID
                         AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
                         AND POH.ORG_ID = PLA.ORG_ID
                         AND POLL.ORG_ID = PLA.ORG_ID
                         AND MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
                         AND POLL.SHIP_TO_ORGANIZATION_ID =
                               MSI.ORGANIZATION_ID
                         AND OOD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                         AND RRH.ROUTING_HEADER_ID =
                               POLL.RECEIVING_ROUTING_ID
                         AND POH.ORG_ID IN (90, 91)
                         AND POLL.SHIP_TO_ORGANIZATION_ID IN
                                  (SELECT   organization_id
                                     FROM   org_organization_definitions
                                    WHERE      organization_code LIKE '%C%'
                                            OR organization_code LIKE '%E%'
                                            OR organization_code LIKE '%S%'
                                            OR organization_code LIKE '%I%')
                         AND POLL.RECEIVING_ROUTING_ID NOT IN (1, 2)
                         AND LENGTH (POH.SEGMENT1) = 8
                         AND MAP.ITM_GRP=SUBSTR(MSI.SEGMENT1,1,2)
                         AND MAP.ITM_CAT='MRO'
                         )
                        
-------- INSPECTION REQUIRED UPDATION QUERY --------------
/* Formatted on 17-09-2011 17:01:33 (QP5 v5.115.810.9015) */
UPDATE   po_line_locations_all
   SET   RECEIVING_ROUTING_ID = 2
 WHERE   PO_LINE_ID IN
               (SELECT   POLL.PO_LINE_ID
                  FROM   po_line_locations_all POLL,
                         PO_LINES_ALL PLA,
                         MTL_SYSTEM_ITEMS_B MSI,
                         PO_HEADERS_ALL POH,
                         ORG_ORGANIZATION_DEFINITIONS OOD,
                         RCV_ROUTING_HEADERS RRH,
                         XX_ITEM_MAP MAP
                 WHERE       POLL.PO_LINE_ID = PLA.PO_LINE_ID
                         AND POH.PO_HEADER_ID = PLA.PO_HEADER_ID
                         AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
                         AND POH.ORG_ID = PLA.ORG_ID
                         AND POLL.ORG_ID = PLA.ORG_ID
                         AND MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
                         AND POLL.SHIP_TO_ORGANIZATION_ID =
                               MSI.ORGANIZATION_ID
                         AND OOD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                         AND RRH.ROUTING_HEADER_ID =
                               POLL.RECEIVING_ROUTING_ID
                         --AND POH.ORG_ID IN (90, 91)
                         AND POLL.SHIP_TO_ORGANIZATION_ID IN
                                  (SELECT   organization_id
                                     FROM   org_organization_definitions
                                    WHERE      organization_code LIKE '%C%'
                                            OR organization_code LIKE '%E%'
                                            OR organization_code LIKE '%S%'
                                            OR organization_code LIKE '%I%')
                         --AND POLL.RECEIVING_ROUTING_ID NOT IN (1, 2)
                         AND POLL.RECEIVING_ROUTING_ID=3
                         AND LENGTH (POH.SEGMENT1) = 8
                         AND MAP.ITM_GRP = SUBSTR (MSI.SEGMENT1, 1, 2)
                         AND MAP.ITM_CAT = 'MRO'
                         )
                        
                        
SELECT  DISTINCT POH.SEGMENT1 PO_NO,
         MSI.SEGMENT1 ITEM_CODE,
         POLL.RECEIVING_ROUTING_ID,
         RRH.ROUTING_NAME,
         POLL.SHIP_TO_ORGANIZATION_ID ORGANIZATION_ID,
         OOD.ORGANIZATION_NAME,
         OOD.ORGANIZATION_CODE,
         POH.AUTHORIZATION_STATUS
                  FROM   po_line_locations_all POLL,
                         PO_LINES_ALL PLA,
                         MTL_SYSTEM_ITEMS_B MSI,
                         PO_HEADERS_ALL POH,
                         ORG_ORGANIZATION_DEFINITIONS OOD,
                         RCV_ROUTING_HEADERS RRH,
                         XX_ITEM_MAP MAP
                 WHERE       POLL.PO_LINE_ID = PLA.PO_LINE_ID
                         AND POH.PO_HEADER_ID = PLA.PO_HEADER_ID
                         AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
                         AND POH.ORG_ID = PLA.ORG_ID
                         AND POLL.ORG_ID = PLA.ORG_ID
                         AND MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
                         AND POLL.SHIP_TO_ORGANIZATION_ID =
                               MSI.ORGANIZATION_ID
                         AND OOD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                         AND RRH.ROUTING_HEADER_ID =
                               POLL.RECEIVING_ROUTING_ID
                         --AND POH.ORG_ID IN (90, 91)
                         AND POLL.SHIP_TO_ORGANIZATION_ID IN
                                  (SELECT   organization_id
                                     FROM   org_organization_definitions
                                    WHERE      organization_code LIKE '%C%'
                                            OR organization_code LIKE '%E%'
                                            OR organization_code LIKE '%S%'
                                            OR organization_code LIKE '%I%')
                         AND POLL.RECEIVING_ROUTING_ID NOT IN (1, 2)
                         --AND POLL.RECEIVING_ROUTING_ID =3
                         AND LENGTH (POH.SEGMENT1) = 8
                         AND MAP.ITM_GRP = SUBSTR (MSI.SEGMENT1, 1, 2)
                         AND MAP.ITM_CAT = 'MRO'