ITEM_INSPECTION_REQ_UPD_QRY

/* Formatted on 16-09-2011 12:15:37 (QP5 v5.115.810.9015) */
  SELECT   msi.inventory_item_id,
           msi.segment1 item_code,
           msi.organization_id,
           ood.ORGANIZATION_NAME,
           msi.INSPECTION_REQUIRED_FLAG
    FROM   mtl_system_items_b msi, org_organization_definitions ood
   WHERE   SUBSTR (msi.Segment1, 1, 2) IN (SELECT   ITM_GRP
                                             FROM   xx_item_map
                                            WHERE   itm_cat = 'MRO')
GROUP BY   msi.inventory_item_id,
           msi.segment1,
           msi.organization_id,
           ood.ORGANIZATION_NAME,
           msi.INSPECTION_REQUIRED_FLAG
--Select * from xx_item_map where itm_cat='MRO'


/* Formatted on 16-09-2011 12:18:04 (QP5 v5.115.810.9015) */
UPDATE   mtl_system_items_b
   SET   INSPECTION_REQUIRED_FLAG = 'Y'
 WHERE   INVENTORY_ITEM_ID IN
               (SELECT   inventory_item_id
                  FROM   (  SELECT   msi.inventory_item_id,
                                     msi.segment1 item_code,
                                     msi.organization_id,
                                     ood.ORGANIZATION_NAME,
                                     msi.INSPECTION_REQUIRED_FLAG
                              FROM   mtl_system_items_b msi,
                                     org_organization_definitions ood
                             WHERE   SUBSTR (msi.Segment1, 1, 2) IN
                                           (SELECT   ITM_GRP
                                              FROM   xx_item_map
                                             WHERE   itm_cat = 'MRO')
                          GROUP BY   msi.inventory_item_id,
                                     msi.segment1,
                                     msi.organization_id,
                                     ood.ORGANIZATION_NAME,
                                     msi.INSPECTION_REQUIRED_FLAG))
                                    
SELECT   RECEIVING_ROUTING_ID, SHIP_TO_ORGANIZATION_ID
  FROM   po_line_locations_all
 WHERE   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  RECEIVING_ROUTING_ID not in (1, 2)
Select * from rcv_routing_headers
Select * from org_organization_definitions where organization_id=134
SELECT   organization_id , organization_code
                               FROM   org_organization_definitions
                              WHERE   organization_code LIKE '%C%' OR organization_code LIKE '%E%' OR  organization_code LIKE '%S%'
                              OR  organization_code LIKE '%I%'
                             
                             
/* Formatted on 16-09-2011 18:04:44 (QP5 v5.115.810.9015) */
create table PO_LINE_LOCATIONS_ALL_16SEP11 as  Select * from po_line_locations_all
UPDATE   po_line_locations_all
   SET   RECEIVING_ROUTING_ID = 2
 WHERE   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 RECEIVING_ROUTING_ID NOT IN (1, 2)
        
SELECT   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
 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
        
/* Formatted on 17-09-2011 16:53:33 (QP5 v5.115.810.9015) */
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
                 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)