APPS.INVICGDS SQL Statements

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

Line: 4

PROCEDURE inv_update_item_desc(
inv_item_id                 IN    NUMBER   DEFAULT  NULL,
org_id                      IN    NUMBER   DEFAULT  NULL,
first_elem_break            IN    NUMBER   DEFAULT  30,
use_name_as_first_elem      IN    VARCHAR2 DEFAULT  'N',
delimiter                   IN    VARCHAR2 DEFAULT  NULL,
show_all_delim              IN    VARCHAR2 DEFAULT  'Y'
)
IS

  delim_val          VARCHAR2(1);

Line: 24

    SELECT inventory_item_id, organization_id--, description
      FROM mtl_system_items_B MSI
     WHERE (MSI.inventory_item_id = iii OR iii IS NULL)
       AND (MSI.organization_id = org OR org IS NULL)
       AND MSI.ITEM_CATALOG_GROUP_ID is NOT NULL;

Line: 34

    SELECT inventory_item_id, organization_id, description
      FROM mtl_system_items_VL MSI
     WHERE MSI.inventory_item_id = NVL(iii, MSI.inventory_item_id)
       AND MSI.item_catalog_group_id is NOT NULL
       AND MSI.organization_id in (SELECT organization_id
                                     FROM mtl_parameters mp
                                    WHERE mp.master_organization_id = m_org);

Line: 43

    SELECT inventory_item_id, organization_id
      FROM mtl_system_items_B MSI
     WHERE MSI.inventory_item_id = iii
       AND EXISTS (SELECT NULL
                     FROM mtl_parameters mp
                    WHERE mp.master_organization_id = m_org
		      AND MSI.organization_id = mp.organization_id);

Line: 58

  select INSTALLED_FLAG
  into   l_INSTALLED_FLAG
  from  FND_LANGUAGES
  where  LANGUAGE_CODE = userenv('LANG');

Line: 67

  SELECT FT.concatenated_segment_delimiter INTO delim_val
  FROM fnd_id_flex_structures FT
  WHERE FT.ID_FLEX_CODE = 'MICG'
    AND FT.APPLICATION_ID = 401;

Line: 86

        SELECT mp.master_organization_id INTO master_org_id_val
        FROM  mtl_parameters mp
        WHERE mp.organization_id = gir.organization_id;

Line: 90

        SELECT mia.control_level INTO desc_control_level
        FROM  mtl_item_attributes mia
        WHERE attribute_name = 'MTL_SYSTEM_ITEMS.DESCRIPTION';

Line: 96

          IF desc_control_level = 1 THEN  /*item level: update all children*/

             FOR  gcir in get_child_item_rows( gir.inventory_item_id,
                                               gir.organization_id )
             LOOP

		UPDATE  mtl_system_items_B  MSI
		SET
                   MSI.description = decode(l_INSTALLED_FLAG,'B', new_description, MSI.description)
		WHERE MSI.inventory_item_id = gcir.inventory_item_id
		  AND MSI.organization_id = gcir.organization_id;

Line: 111

  update MTL_SYSTEM_ITEMS_TL
  set
     DESCRIPTION = new_description
--  ,  LAST_UPDATE_DATE  =  l_sysdate
--  ,  LAST_UPDATED_BY   =  user_id
--  ,  LAST_UPDATE_LOGIN =  login_id
  ,  SOURCE_LANG = userenv('LANG')
  where  INVENTORY_ITEM_ID = gcir.inventory_item_id
    and  ORGANIZATION_ID = gcir.organization_id
    and  userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;

Line: 126

		UPDATE  mtl_system_items_B  MSI
		   SET
                      MSI.description = decode(l_INSTALLED_FLAG,'B', new_description, MSI.description)
		 WHERE MSI.inventory_item_id = gir.inventory_item_id
		   AND MSI.organization_id = gir.organization_id;

Line: 135

  update MTL_SYSTEM_ITEMS_TL
  set
     DESCRIPTION = new_description
--  ,  LAST_UPDATE_DATE  =  l_sysdate
--  ,  LAST_UPDATED_BY   =  user_id
--  ,  LAST_UPDATE_LOGIN =  login_id
  ,  SOURCE_LANG = userenv('LANG')
  where  INVENTORY_ITEM_ID = gir.inventory_item_id
    and  ORGANIZATION_ID = gir.organization_id
    and  userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;

Line: 158

		UPDATE  mtl_system_items_B  MSI
		   SET
                      MSI.description = decode(l_INSTALLED_FLAG,'B', new_description, MSI.description)
		 WHERE MSI.inventory_item_id = gir.inventory_item_id
		   AND MSI.organization_id = gir.organization_id;

Line: 167

  update MTL_SYSTEM_ITEMS_TL
  set
     DESCRIPTION = new_description
--  ,  LAST_UPDATE_DATE  =  l_sysdate
--  ,  LAST_UPDATED_BY   =  user_id
--  ,  LAST_UPDATE_LOGIN =  login_id
  ,  SOURCE_LANG = userenv('LANG')
  where  INVENTORY_ITEM_ID = gir.inventory_item_id
    and  ORGANIZATION_ID = gir.organization_id
    and  userenv('LANG') in (LANGUAGE, SOURCE_LANG) ;

Line: 203

                The design called for a bare-bones mass update script
                runnable from the SQL> prompt
              */

END inv_update_item_desc;

Line: 290

	 SELECT item_catalog_group_id INTO icg_id_val
	   FROM mtl_system_items_B MSI
	  WHERE MSI.inventory_item_id = inv_item_id
	    AND rownum =1;

Line: 319

    SELECT MICGK.concatenated_segments into icg_desc_or_name
      FROM mtl_item_catalog_groups_kfv MICGK
     WHERE MICGK.item_catalog_group_id = icg_id_val;

Line: 324

    SELECT MICG.description into icg_desc_or_name
      FROM mtl_item_catalog_groups MICG
     WHERE MICG.item_catalog_group_id = icg_id_val;

Line: 333

  SELECT FT.concatenated_segment_delimiter INTO delim_val
  FROM fnd_id_flex_structures FT
  WHERE FT.ID_FLEX_CODE = 'MICG'
    AND FT.APPLICATION_ID = 401;

Line: 388

   The first select stmt is the one MOST likely to get caught in
   no_data_found.
   If data found there the other selects are pretty much guaranteed
   to work okay
 */

 WHEN OTHERS THEN
  dummyerr:= error_text|| ' INVICGDS(3-3): '||SQLCODE||':'
                         ||substrb(SQLERRM,1,30);

Line: 417

    SELECT element_name, element_value, element_sequence
      FROM mtl_descr_element_values MDEV
     WHERE MDEV.inventory_item_id = iii
       AND MDEV.default_element_flag = 'Y'
       ORDER BY element_sequence;

Line: 424

    SELECT element_name, element_value, element_sequence
      FROM mtl_descr_element_values MDEV
     WHERE MDEV.inventory_item_id = iii
       AND MDEV.default_element_flag = 'Y'
       AND MDEV.element_value is not NULL
       ORDER BY element_sequence;

Line: 434

      SELECT FT.concatenated_segment_delimiter INTO delim_val
        FROM fnd_id_flex_structures FT
       WHERE FT.ID_FLEX_CODE = 'MICG'
         AND FT.APPLICATION_ID = 401;