APPS.INV_ITEM_DATA_SCRIPTS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 21

     UPDATE MTL_SYSTEM_ITEMS_B
      SET   INTERNAL_ORDER_ENABLED_FLAG ='N'
      WHERE INTERNAL_ORDER_ENABLED_FLAG ='Y'
      AND   INTERNAL_ORDER_FLAG ='N';

Line: 27

    UPDATE MTL_SYSTEM_ITEMS_B
     SET    INTERNAL_ORDER_ENABLED_FLAG ='N',
            INTERNAL_ORDER_FLAG ='N'
     WHERE  INTERNAL_ORDER_FLAG ='Y'
     AND    NVL(CONTRACT_ITEM_TYPE_CODE, 'SUBSCRIPTION') IN
            ('WARRANTY','SERVICE','USAGE');

Line: 35

    UPDATE MTL_SYSTEM_ITEMS_B
     SET    SHIPPABLE_ITEM_FLAG ='Y'
     WHERE  INTERNAL_ORDER_FLAG ='Y'
     AND    SHIPPABLE_ITEM_FLAG ='N'
     AND    NVL(CONTRACT_ITEM_TYPE_CODE, 'SUBSCRIPTION') NOT IN
                ('WARRANTY','SERVICE','USAGE');

Line: 54

/* Procedure to call generic script that updates the mtl_item_categories table
  to correct the following conditions
  Note: This is valid only for Master Controlled Category Sets and is valid
        for both single and multi assignment category sets.
        Case 1: There are category assignments at the master and child
        organizations, but the values are not the same.
        Case 2: There are category assignments at the master and child organizations
        that have the same values, but there are also an additional category
        assigment record for the child organization.
        Case 3: There are category assignments in the master organization for which
        there is no corresponding child organization category assignment. */

PROCEDURE proc_cat_datafix(
              errbuf  OUT NOCOPY VARCHAR2,
              retcode OUT NOCOPY NUMBER)
IS

/*Retrieves all Master Orgs*/
  CURSOR master_org_cur IS
    SELECT master_organization_id
    FROM   mtl_parameters
    WHERE  organization_id = master_organization_id;

Line: 78

    SELECT inventory_item_id,
           organization_id,
           category_set_id,
           category_id
    FROM   mtl_item_categories a
    WHERE  EXISTS (SELECT 'X'
                   FROM   mtl_category_sets_b
                   WHERE  control_level = 1
                          AND category_set_id = a.category_set_id)
           AND a.organization_id = master_org_id;

Line: 91

    SELECT msib.organization_id
    FROM   mtl_system_items_b msib,
           mtl_parameters mp
    WHERE  msib.organization_id = mp.organization_id
           AND msib.inventory_item_id = item_id
           AND mp.master_organization_id = master_org_id
           AND mp.organization_id <> mp.master_organization_id;

Line: 105

    /*Case 1 and 2: This will delete additional category records from the child organization
      that are not present in the Master Organization*/
    DELETE FROM mtl_item_categories a
    WHERE       a.organization_id IN (SELECT m.organization_id
                                      FROM   mtl_parameters m
                                      WHERE  m.master_organization_id = rec_cur.master_organization_id
                                             AND m.organization_id <> m.master_organization_id)
                AND EXISTS (SELECT 'X'
                            FROM   mtl_category_sets_b
                            WHERE  control_level = 1
                                   AND category_set_id = a.category_set_id)
                AND NOT EXISTS (SELECT 'x'
                                FROM   mtl_item_categories b
                                WHERE  b.category_set_id = a.category_set_id
                                       AND b.inventory_item_id = a.inventory_item_id
                                       AND b.category_id = a.category_id
                                       AND b.organization_id = rec_cur.master_organization_id);

Line: 123

    /*Case 3: This will insert categories that are not present in the child org*/
    FOR i IN items_list(rec_cur.master_organization_id) LOOP
      /*Fetch the list of child orgs to which the item is assigned*/
      FOR j IN child_orgs(i.inventory_item_id,i.organization_id) LOOP
        /*Check whether the give item under a child org is assigned to
		  the same category set and category as that of the master org*/
        BEGIN
          SELECT 'FOUND'
          INTO   l_cat_set_exists
          FROM   mtl_item_categories
          WHERE  inventory_item_id = i.inventory_item_id
                 AND organization_id = j.organization_id
                 AND category_set_id = i.category_set_id
                 AND category_id = i.category_id;

Line: 142

        /*If not assigned at the child org level then insert details into the mtl_item_categories table*/
        IF l_cat_set_exists = 'NOTFOUND' THEN
          INSERT INTO mtl_item_categories
           (inventory_item_id,
            organization_id,
            category_set_id,
            category_id,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            last_update_login,
            request_id,
            program_application_id,
            program_id,
            program_update_date,
            wh_update_date)
			SELECT inventory_item_id,
			       j.organization_id,
			       category_set_id,
			       category_id,
			       last_update_date,
			       last_updated_by,
			       creation_date,
			       created_by,
			       last_update_login,
			       request_id,
			       program_application_id,
			       program_id,
			       program_update_date,
			       wh_update_date
			FROM   mtl_item_categories
			WHERE  inventory_item_id = i.inventory_item_id
			       AND organization_id = i.organization_id
			       AND category_set_id = i.category_set_id
			       AND category_id = i.category_id;