APPS.QP_ITEMGROUP_UPG_UTIL_PVT SQL Statements

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

Line: 29

  SELECT *
  FROM   oe_item_groups;

Line: 34

  SELECT *
  FROM   oe_item_group_lines
  WHERE  group_id = p_group_id;

Line: 62

     SELECT id_flex_num
     INTO   l_new_structure_id
     FROM   fnd_id_flex_structures
     WHERE  id_flex_code = 'MCAT'
     AND    id_flex_structure_code = 'PRICELIST_ITEM_CATEGORIES';

Line: 93

						   dynamic_insert_flag => 'N',
						   shorthand_enabled_flag => 'N',
						   shorthand_prompt => '',
						   shorthand_length => 0
						   );

Line: 133

    SELECT id_flex_num
    INTO   l_new_structure_id
    FROM   fnd_id_flex_structures
    WHERE  id_flex_code = 'MCAT'
    AND    id_flex_structure_code = 'PRICELIST_ITEM_CATEGORIES';

Line: 159

  SELECT mtl_categories_s.nextval
  INTO   l_new_category_id
  FROM   dual;

Line: 164

    INSERT INTO mtl_categories_b
    (
     category_id,
     structure_id,
     last_update_date,
     last_updated_by,
     creation_date,
     created_by,
--   description,
     summary_flag,
     enabled_flag,
     segment1
    )
    SELECT
     l_new_category_id,
     l_new_structure_id,
     sysdate,
     l_user_id,
     sysdate,
     l_user_id,
--   l_description,
     'N',
     l_enabled_flag, -- whether segment combination is enabled
     'Item Category for Item Groups'
    FROM  dual
    WHERE NOT EXISTS (SELECT 'X'
				  FROM   mtl_categories_b b
				  WHERE  b.structure_id = l_new_structure_id
				  AND    b.segment1 = 'Item Category for Item Groups');

Line: 194

    INSERT INTO mtl_categories_tl
    (
     category_id,
     language,
     source_lang,
     description,
     last_update_date,
     last_updated_by,
     creation_date,
     created_by
    )
    SELECT
     l_new_category_id,
     l.LANGUAGE_CODE,
     userenv('LANG'),
     l_description,
     sysdate,
     l_user_id,
     sysdate,
     l_user_id
    FROM FND_LANGUAGES l
    WHERE l.INSTALLED_FLAG in ('I', 'B')
    AND NOT EXISTS (SELECT 'X'
			     FROM mtl_categories_tl t
			     WHERE t.category_id = l_new_category_id
			     AND t.language = l.LANGUAGE_CODE)
    AND EXISTS (SELECT 'X'
			 FROM   mtl_categories_b b
			 WHERE  b.category_id = l_new_category_id);

Line: 242

    SELECT mtl_category_sets_s.nextval
    INTO   l_new_category_set_id
    FROM   dual;

Line: 247

    INSERT INTO mtl_category_sets_b
    (
     category_set_id,
--     category_set_name,
     structure_id,
     validate_flag,
     control_level,
--     description,
     last_update_date,
     last_updated_by,
     creation_date,
     created_by,
	mult_item_cat_assign_flag
    )
    SELECT
	l_new_category_set_id,
--     upper(l_oe_item_groups_rec.name) || '_CATEGORY_SET',
     l_new_structure_id,
     'N',
     l_control_level, --Control level is item_level
--     l_oe_item_groups_rec.description,
     l_oe_item_groups_rec.last_update_date,
     l_oe_item_groups_rec.last_updated_by,
     l_oe_item_groups_rec.creation_date,
     l_oe_item_groups_rec.created_by,
	'Y'
    FROM  dual
    WHERE NOT EXISTS (SELECT 'X'
				  FROM   mtl_category_sets_b b, mtl_category_sets_tl t
				  WHERE  b.category_set_id = t.category_set_id
				  AND    b.structure_id = l_new_structure_id
				  AND    t.category_set_name =
				  substr(upper(l_oe_item_groups_rec.name),1,15) || '_CATEGORY_SET');

Line: 281

    INSERT INTO mtl_category_sets_tl
    (
	category_set_id,
	language,
	source_lang,
	category_set_name,
	description,
	last_update_date,
	last_updated_by,
	creation_date,
	created_by
    )
    SELECT
	l_new_category_set_id,
	l.LANGUAGE_CODE,
	userenv('LANG'),
     substr(upper(l_oe_item_groups_rec.name),1,15) || '_CATEGORY_SET',
     substr(l_oe_item_groups_rec.description,1, 240),
     l_oe_item_groups_rec.last_update_date,
     l_oe_item_groups_rec.last_updated_by,
     l_oe_item_groups_rec.creation_date,
     l_oe_item_groups_rec.created_by
    FROM  FND_LANGUAGES l
    WHERE l.INSTALLED_FLAG IN ('I', 'B')
    AND NOT EXISTS (SELECT 'X'
				FROM   mtl_category_sets_tl t
				WHERE  t.category_set_id = l_new_category_set_id
				AND    t.language = l.LANGUAGE_CODE)
    AND EXISTS (SELECT 'X'
			 FROM   mtl_category_sets_b b
                WHERE  b.category_set_id = l_new_category_set_id);

Line: 331

      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
      )
      SELECT
	  l_oe_item_group_lines_rec.inventory_item_id,
	  TO_NUMBER(l_organization_id),
	  l_new_category_set_id,
	  l_new_category_id,
	  l_oe_item_group_lines_rec.last_update_date,
	  l_oe_item_group_lines_rec.last_updated_by,
	  l_oe_item_group_lines_rec.creation_date,
	  l_oe_item_group_lines_rec.created_by
	 FROM  dual
	 WHERE EXISTS (SELECT 'X'
				FROM   mtl_categories_b b
				WHERE  b.category_id = l_new_category_id)
	 AND EXISTS (SELECT 'X'
			   FROM   mtl_category_sets_b s
			   WHERE  s.category_set_id = l_new_category_set_id);