Changing Inventory Item Status via Item Interface

Dear Reader

Quite often we come across a requirement where we need to change the status of items in inventory item setup.

This may be because the items are being made as Inactive or to update some other flags. Essentially the item status affects the various flags such as Stockable, Reservable, Order Enabled etc on the item master setup

Below is a script which can be used to update status for a bunch of items whose item numbers I have loaded into a temporary table

DECLARE

   CURSOR lcur_item

   IS

      SELECT   ROWID row_id,

               tmp.*

      FROM     xx_item_status_update_tmp tmp  --this table contains all item numbers which need to be made inactive in INV

      WHERE    tmp.process_flag = 'N';

   lv_item_id           NUMBER;

   lv_process_set_id    NUMBER := &set_process_id;

   lv_organization_id   NUMBER;

   lv_item_descr        VARCHAR2(500);

BEGIN

   SELECT   organization_id

   INTO     lv_organization_id

   FROM     mtl_parameters

   WHERE    organization_code = 'IM';

   FOR lrec_item IN lcur_item

   LOOP

      SELECT   inventory_item_id,

               description

      INTO     lv_item_id,

               lv_item_descr

      FROM     mtl_system_items

      WHERE    segment1 = lrec_item.item_number

      AND      organization_id = lv_organization_id;

      INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE

      (

        PROCESS_FLAG

      , SEGMENT1

      , DESCRIPTION

      , SET_PROCESS_ID

      , ORGANIZATION_ID

      , TRANSACTION_TYPE

      , INVENTORY_ITEM_STATUS_CODE

      , INVENTORY_ITEM_ID

      )

      VALUES

      (

        1

      , lrec_item.item_number

      , lv_item_descr

      , lv_process_set_id

      , lv_organization_id

      , 'UPDATE'

      ,'Inactive'

      , lv_item_id

      );

      UPDATE   xx_item_status_update_tmp

      SET      process_flag = 'Y'

      WHERE    ROWID = lrec_item.row_id;

   END LOOP;

END;

Hope this helps you

Cheers

A