APPS.INV_ITEM_ATTRIBUTES_PKG SQL Statements

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

Line: 210

    SELECT COLUMN_TYPE
          ,LOOKUP_TABLE
          ,LOOKUP_COLUMN
          ,LOOKUP_TYPE
          ,LOOKUP_TYPE_VALUE
          ,REFERENCE_KEY_COLUMN
    INTO g_att_tab(g_current_att_index).column_type
        ,g_att_tab(g_current_att_index).lookup_table
        ,g_att_tab(g_current_att_index).lookup_column
        ,g_att_tab(g_current_att_index).lookup_type
        ,g_att_tab(g_current_att_index).lookup_type_value
        ,g_att_tab(g_current_att_index).reference_key_column
    FROM MTL_ITEM_ATTRIBUTES_SEED_INFO
    WHERE attribute_code = g_att_tab(g_current_att_index).item_column_name
    ORDER BY rowid;

Line: 226

    /*SELECT default_value_varchar2
          ,display_value_length
    INTO g_att_tab(g_current_att_index).foreign_key_name
        ,g_att_tab(g_current_att_index).column_type
    FROM ak_object_attributes
    WHERE database_object_name = 'MTL_SYSTEM_ITEMS_VL'
    AND attribute_code = g_att_tab(g_current_att_index).item_column_name
    ORDER BY rowid;

Line: 244

      SELECT attribute_label_long
      INTO g_att_tab(g_current_att_index).display_column
      FROM ak_object_attributes_vl
      WHERE database_object_name = 'MTL_SYSTEM_ITEMS_VL'
      AND attribute_code = g_att_tab(g_current_att_index).item_column_name;

Line: 256

      SELECT from_to_name
            ,to_from_name
      INTO g_att_tab(g_current_att_index).lookup_table
          ,g_att_tab(g_current_att_index).lookup_column
      FROM ak_foreign_keys_tl
      WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name
      AND language = 'US';

Line: 264

      SELECT from_to_description
            ,to_from_description
      INTO g_att_tab(g_current_att_index).lookup_type
          ,g_att_tab(g_current_att_index).lookup_type_value
      FROM ak_foreign_keys_tl
      WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name
      AND language = 'US';

Line: 272

      SELECT attribute_code
      INTO g_att_tab(g_current_att_index).foreign_key_column
      FROM ak_foreign_key_columns
      WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name;

Line: 277

      SELECT attribute_code
      INTO g_att_tab(g_current_att_index).reference_key_column
      FROM ak_unique_key_columns
      WHERE unique_key_name = g_att_tab(g_current_att_index).lookup_table;

Line: 286

      SELECT from_to_name
            ,to_from_name
      INTO g_att_tab(g_current_att_index).lookup_table
          ,g_att_tab(g_current_att_index).lookup_column
      FROM ak_foreign_keys_tl
      WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name
      AND language = 'US';

Line: 294

      SELECT from_to_description
            ,to_from_description
      INTO g_att_tab(g_current_att_index).lookup_type
          ,g_att_tab(g_current_att_index).lookup_type_value
      FROM ak_foreign_keys_tl
      WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name
      AND language = 'US';

Line: 302

      SELECT attribute_code
      INTO g_att_tab(g_current_att_index).foreign_key_column
      FROM ak_foreign_key_columns
      WHERE foreign_key_name = g_att_tab(g_current_att_index).foreign_key_name;

Line: 502

  * construct appropriate where clasue and update query accordingly. */
  IF p_attribute_category IS NOT NULL THEN
     --Modified for bug 4025750.
     IF p_copy_dff_to_null IS NULL OR p_copy_dff_to_null = 'NO' THEN
      l_dff_w := ' AND itm.attribute_category = :l_attribute_category ';

Line: 514

  x_item_cursor :=  'SELECT  DISTINCT
                             par.organization_code
                           , par.organization_id    '                              ||
                    ', ' ||  l_mstk_segs                                           ||
                          ', itm.inventory_item_id'                                ||
                     ' FROM  mtl_system_items_b itm'                               ||
                         ' , mtl_parameters par'                                   ||
                             l_mcat_f                                              ||
                     ' WHERE itm.organization_id = par.organization_id'            ||
                       ' AND itm.organization_id = :organization_id'     ||
                             l_mstk_w                                              ||
                             l_mcat_w1                                             ||
                             l_sts_w                                               ||
                             l_dff_w;

Line: 637

  DELETE FROM MTL_ITEM_ATTRIBUTES_TEMP;

Line: 1015

      INSERT INTO MTL_ITEM_ATTRIBUTES_TEMP(
         organization_code
        ,organization_id
        ,item_code
        ,item_id
         )
         VALUES
           ( l_organization_code
           , l_organization_id
           , l_item_number
           , l_inventory_item_id
           );

Line: 1052

      l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP tmp SET ( '
         || g_att_tab(g_current_att_index).temp_column_name
         || ' ) = '
         || ' (SELECT '
         || g_att_tab(g_current_att_index).item_column_name
         || ' FROM MTL_SYSTEM_ITEMS_VL itm '
         || ' WHERE tmp.item_id = itm.inventory_item_id '
         || ' AND tmp.organization_id = itm.organization_id '
         || ')';

Line: 1064

      l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP TMP SET ( '
         || CONCAT(g_att_tab(g_current_att_index).temp_column_name,'_DSP')
         || ' ) = '
         || ' (SELECT FND.'
         || g_att_tab(g_current_att_index).lookup_column
         || ' FROM '
         || g_att_tab(g_current_att_index).lookup_table
         || ' FND '
         || ' WHERE FND.'
         || g_att_tab(g_current_att_index).lookup_type
         || ' =  :1'
         || ' AND FND.'
         || g_att_tab(g_current_att_index).reference_key_column
         || ' = '
         || ' TMP.'
         || g_att_tab(g_current_att_index).temp_column_name
         || ')';

Line: 1087

      l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP TMP SET ( '
         || g_att_tab(g_current_att_index).temp_column_name
         || ' ) = '
         || ' (SELECT '
         || g_att_tab(g_current_att_index).item_column_name
         || ' FROM MTL_SYSTEM_ITEMS_VL ITM '
         || ' WHERE TMP.item_id = ITM.inventory_item_id '
         || ' AND TMP.organization_id = ITM.organization_id '
         || ')';

Line: 1099

      l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP TMP SET ( '
         || CONCAT(g_att_tab(g_current_att_index).temp_column_name,'_DSP')
         || ' ) = '
         || ' (SELECT FND.'
         || g_att_tab(g_current_att_index).lookup_column
         || ' FROM '
         || g_att_tab(g_current_att_index).lookup_table
         || ' FND '
         || ' WHERE FND.'
         || g_att_tab(g_current_att_index).lookup_type_value
         || ' = '
         || ' TMP.'
         || g_att_tab(g_current_att_index).temp_column_name
         || ')';

Line: 1118

      l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP tmp SET ( '
         || g_att_tab(g_current_att_index).temp_column_name
         || ' ) = '
         || ' (SELECT '
         || g_att_tab(g_current_att_index).item_column_name
         || ' FROM MTL_SYSTEM_ITEMS_VL itm '
         || ' WHERE tmp.item_id = itm.inventory_item_id '
         || ' AND tmp.organization_id = itm.organization_id '
         || ')';

Line: 1131

      l_dml_str := 'UPDATE MTL_ITEM_ATTRIBUTES_TEMP tmp SET ( '
         || CONCAT(g_att_tab(g_current_att_index).temp_column_name,'_dsp')
         || ' ) = '
         || ' (SELECT '
         || g_att_tab(g_current_att_index).item_column_name
         || ' FROM MTL_SYSTEM_ITEMS_VL itm '
         || ' WHERE tmp.item_id = itm.inventory_item_id '
         || ' AND tmp.organization_id = itm.organization_id '
         || ')';

Line: 1185

  delete MTL_ITEM_ATTRIBUTES_TEMP;

Line: 1216

   SELECT ORGANIZATION_ID
   FROM   ORG_ORGANIZATION_DEFINITIONS
   WHERE   ORGANIZATION_ID IN
           (SELECT  DISTINCT ORGANIZATION_ID_PARENT
            FROM     PER_ORG_STRUCTURE_ELEMENTS)
           OR
           ORGANIZATION_ID  IN
           (SELECT  ORGANIZATION_ID_CHILD
            FROM    PER_ORG_STRUCTURE_ELEMENTS)
   ORDER BY ORGANIZATION_NAME;

Line: 1271

PROCEDURE call_item_update
( p_att_tab            IN  INV_ITEM_ATTRIBUTES_PKG.att_tbl_type
 ,p_sel_tab            IN  INV_ITEM_ATTRIBUTES_PKG.sel_tbl_type
 ,p_inventory_item_id  OUT NOCOPY NUMBER
 ,p_organization_id    OUT NOCOPY NUMBER
 ,p_return_status      OUT NOCOPY VARCHAR2
 ,p_error_tab          OUT NOCOPY INV_Item_GRP.Error_tbl_type
)
IS

x_errbuff             VARCHAR2(240);

Line: 1294

                             , 'call_item_update'
                             );

Line: 1299

END call_item_update;

Line: 1324

  SELECT  COUNT(*)
    INTO  l_error_count
    FROM  mtl_system_items_interface
    WHERE NVL(request_id, 0) = NVL(FND_GLOBAL.conc_request_id, 0);

Line: 1370

  SELECT  COUNT(*)
    INTO  l_count
    FROM  fnd_concurrent_requests
    WHERE request_id = p_request_id
      AND phase_code = 'C';

Line: 1509

PROCEDURE Submit_Item_Update
( p_organization_id  IN            NUMBER
, p_set_process_id   IN            NUMBER
, x_workers          IN OUT NOCOPY g_request_tbl_type
, p_request_count    IN            NUMBER
)
IS
  l_worker_idx     BINARY_INTEGER;

Line: 1523

    , '> Submit_Item_Update'
    );

Line: 1579

    , '< Submit_Item_Update'
    );

Line: 1583

END Submit_Item_Update;

Line: 1605

  SELECT  mtl_system_items_intf_sets_s.NEXTVAL
    INTO  l_set_process_id
    FROM  dual;

Line: 1636

  SELECT  master_organization_id
    INTO  l_master_org_id
    FROM  mtl_parameters
    WHERE organization_id = p_org_hier_level_id;

Line: 1679

PROCEDURE batch_item_update
( x_errbuff            OUT NOCOPY VARCHAR2
, x_retcode            OUT NOCOPY NUMBER
, p_seq_id             IN  NUMBER
)

IS

--================
-- TYPE
--================

TYPE itm_rec_type IS
     RECORD (item_column_name     VARCHAR2(240)
            ,chosen_value         VARCHAR2(240));

Line: 1710

  SELECT item_column_name
        ,chosen_value
  FROM mtl_item_values_temp
  WHERE item_update_id = p_seq_id
  ORDER BY current_att_index;

Line: 1717

  SELECT inventory_item_id
        ,organization_id
  FROM mtl_update_records_temp
  WHERE item_update_id = p_seq_id;

Line: 1766

    , '> Batch Item Update'
    );

Line: 1774

  SELECT status
  INTO l_status
  FROM user_objects
  WHERE object_name = 'INV_ITEM_ATTRIBUTES_PKG'
  AND object_type = 'PACKAGE BODY';

Line: 1881

      INSERT INTO mtl_system_items_interface
      ( process_flag
      , set_process_id
      , transaction_type
      , inventory_item_id
      , organization_id
      , last_update_date
      , last_updated_by
      , creation_date
      , created_by
      , last_update_login
      , request_id
      , program_application_id
      , program_id
      , program_update_date
      , copy_item_id
      , copy_organization_id
       )
      VALUES
      ( 1
      , l_set_process_id
      , 'UPDATE'
      , l_sel_tab(l_current_sel_index).item_id
      , l_sel_tab(l_current_sel_index).organization_id
      , SYSDATE
      , FND_GLOBAL.user_id
      , SYSDATE
      , FND_GLOBAL.user_id
      , FND_GLOBAL.login_id
      , FND_GLOBAL.conc_request_id
      , FND_GLOBAL.prog_appl_id
      , FND_GLOBAL.conc_program_id
      , SYSDATE
      , l_sel_tab(l_current_sel_index).item_id
      , l_master_org_id
      );

Line: 1923

        SELECT count(*)
        INTO l_count
        FROM mtl_item_values_temp
        WHERE item_update_id = p_seq_id;

Line: 1945

               SELECT data_type
               INTO l_data_type
               FROM all_tab_columns
               WHERE table_name = 'MTL_SYSTEM_ITEMS_INTERFACE'
               AND owner = l_app_owner_schema
               AND column_name = upper(l_att_tab(l_current_att_index).item_column_name);

Line: 1954

               SELECT data_type
               INTO l_data_type
               FROM user_synonyms syn, dba_tab_columns col
               WHERE syn.synonym_name = 'MTL_SYSTEM_ITEMS_INTERFACE'
               AND col.owner = syn.table_owner
               AND col.table_name = syn.table_name
               AND col.owner = l_app_owner_schema
               AND col.column_name = upper(l_att_tab(l_current_att_index).item_column_name);

Line: 1964

                 UPDATE mtl_item_values_temp tmp
                 SET tmp.chosen_value = '-999999'
                 WHERE tmp.current_att_index = l_current_att_index
                 AND tmp.item_update_id = p_seq_id;

Line: 1969

                 UPDATE mtl_item_values_temp tmp
                 SET tmp.chosen_value = '!'
                 WHERE tmp.current_att_index = l_current_att_index
                 AND tmp.item_update_id = p_seq_id;

Line: 1977

              UPDATE mtl_item_values_temp tmp
              SET tmp.chosen_value = FND_PROFILE.VALUE('INV_STATUS_DEFAULT')
              WHERE tmp.current_att_index = l_current_att_index
              AND tmp.item_update_id = p_seq_id;

Line: 1986

          l_dml_str := 'UPDATE mtl_system_items_interface int SET (int.'
             || l_att_tab(l_current_att_index).item_column_name
             || ') = ('
             || ' SELECT tmp.chosen_value'
             || ' FROM mtl_item_values_temp tmp '
             || ' WHERE tmp.current_att_index = :1'
             || ' AND tmp.item_update_id = :2'
             || ') WHERE (int.set_process_id = :3'
             || ')';

Line: 2001

            , 'Update Complete'
            );

Line: 2016

        Submit_Item_Update(l_sel_tab(l_current_sel_index).organization_id
                          ,l_set_process_id
                          ,l_workers_tbl
                          ,l_request_count);

Line: 2044

  DELETE mtl_item_values_temp
  WHERE item_update_id = p_seq_id;

Line: 2047

  DELETE mtl_update_records_temp
  WHERE item_update_id = p_seq_id;

Line: 2058

    , '< Batch Item Update'
    );

Line: 2071

                             , 'batch_item_update'
                             );

Line: 2077

END batch_item_update;

Line: 2109

  SELECT mtl_update_session_s.NEXTVAL INTO l_seq_id FROM dual;

Line: 2119

    INSERT INTO mtl_item_values_temp(item_update_id
                                    ,item_column_name
                                    ,chosen_value
                                    ,current_att_index
                                    )
                              VALUES(l_seq_id
                                    ,g_att_tab(g_current_att_index).item_column_name
                                    ,g_att_tab(g_current_att_index).chosen_value
                                    ,g_current_att_index
                                    );

Line: 2141

  INSERT into mtl_update_records_temp(
    item_update_id
   ,inventory_item_id
   ,organization_id)
  SELECT
    l_seq_id
   ,mia.item_id
   ,mia.organization_id
  FROM
    mtl_item_attributes_temp mia
  WHERE mia.checkbox = 'Y';