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 

Inventory Interface Script to perform an Account Alias Issue

Dear Reader

Many of us have come across requirements where we need to perform an account alias issue/receipt or miscellaneous issue/receipt

The below sample script provides an idea of the necessary columns you need to populate for perform an Acct Alias Issue. The same can be used as a baseline for doing the receipt as well

Insert into mtl_transactions_interface

(

   transaction_uom,

   transaction_date,

   source_code,

   source_line_id,

   source_header_id,

   process_flag ,

   transaction_mode ,

   lock_flag ,

   locator_id ,

   last_update_date ,

   last_updated_by ,

   creation_date ,

   created_by ,

   inventory_item_id ,

   subinventory_code,

   organization_id,

   transaction_source_name,

   transaction_source_id,

   transaction_quantity ,

   primary_quantity ,

   transaction_type_id

)

VALUES

(

   'EA',          --transaction uom

   SYSDATE,       --transaction date

   'Alias Issue', --source code

   99,            --source line id

   99,            --source header id

   1,             --process flag

   3 ,            --transaction mode

   2 ,            --lock flag

   NULL ,         --locator id

   SYSDATE ,      --last update date

   0,             --last updated by

   SYSDATE ,      --creation date

   0,             --created by

   81434 ,        --inventory item id

   'STAGING',     --From subinventory code

   341,           --organization id

   'TEST',        --transaction source

   2,             --transaction source id

   -2,            --transaction quantity

   2,             --Primary quantity

   31             --transaction type id

);

Hope this helps you all

Cheers
A