Update On hand Status using API

CREATE OR REPLACE PROCEDURE XX_UPDATE_MTL_STS

AS

   -- Common Declarations

   l_api_version      NUMBER := 1.0;

   l_init_msg_list    VARCHAR2 (2) := FND_API.G_TRUE;

   l_commit           VARCHAR2 (2) := FND_API.G_FALSE;

   x_return_status    VARCHAR2 (2);

   x_msg_count        NUMBER := 0;

   x_msg_data         VARCHAR2 (255);

   -- WHO columns

   l_user_id          NUMBER := -1;

   l_resp_id          NUMBER := -1;

   l_application_id   NUMBER := -1;

   l_row_cnt          NUMBER := 1;

   l_user_name        VARCHAR2 (30) := 'MFG';

   l_resp_name        VARCHAR2 (50)

                         := 'Manufacturing and Distribution Manager';

   -- API specific declarations

   l_object_type      VARCHAR2 (20);

   l_status_rec       INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;

BEGIN

   -- Initialize variables

   l_object_type := 'H'; -- 'O' = Lot , 'S' = Serial, 'Z' = Subinventory, 'L' = Locator, 'H' = Onhand

   l_status_rec.organization_id := 209;

   l_status_rec.inventory_item_id := 516963;

   l_status_rec.lot_number := 'EXPLOT200';

   l_status_rec.zone_code := 'RIP';

   l_status_rec.locator_id := NULL;

   l_status_rec.status_id := 1; -- select status_id, status_code from mtl_material_statuses_vl;

   l_status_rec.update_reason_id := 305; --'Reviewed';  -- select reason_id, reason_name from mtl_transaction_reasons where reason_type_display = 'Update Status';

   l_status_rec.update_method := 2;

   /*

       l_status_rec.serial_number         := fnd_api.g_miss_char;

       l_status_rec.to_serial_number      := fnd_api.g_miss_char;

       l_status_rec.lpn_id                := fnd_api.g_miss_num;

       l_status_rec.initial_status_flag   := fnd_api.g_miss_char;

       l_status_rec.from_mobile_apps_flag := fnd_api.g_miss_char;

       l_status_rec.grade_code            := fnd_api.g_miss_char;

       l_status_rec.primary_onhand        := fnd_api.g_miss_num;

       l_status_rec.secondary_onhand      := fnd_api.g_miss_num;

       l_status_rec.group_id              := fnd_api.g_miss_num;

       l_status_rec.pending_status        := fnd_api.g_miss_num;

   */

   -- Get the user_id

   SELECT user_id

     INTO l_user_id

     FROM fnd_user

    WHERE user_name = l_user_name;

   -- Get the application_id and responsibility_id

   SELECT application_id, responsibility_id

     INTO l_application_id, l_resp_id

     FROM fnd_responsibility_vl

    WHERE responsibility_name = l_resp_name;

   FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_application_id);

   DBMS_OUTPUT.put_line (

         'Initialized applications context: '

      || l_user_id

      || ' '

      || l_resp_id

      || ' '

      || l_application_id);

   -- call API to update material status

   DBMS_OUTPUT.PUT_LINE (

      '=======================================================');

   DBMS_OUTPUT.PUT_LINE ('Calling INV_MATERIAL_STATUS_PUB.Update_Status');

   INV_MATERIAL_STATUS_PUB.update_status (

      p_api_version_number   => l_api_version,

      p_init_msg_lst         => l_init_msg_list,

      p_commit               => l_commit,

      x_return_status        => x_return_status,

      x_msg_count            => x_msg_count,

      x_msg_data             => x_msg_data,

      p_object_type          => l_object_type,

      p_status_rec           => l_status_rec);

   DBMS_OUTPUT.PUT_LINE (

      '=======================================================');

   DBMS_OUTPUT.PUT_LINE ('Return Status: ' || x_return_status);

   IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)

   THEN

      DBMS_OUTPUT.PUT_LINE ('Error Message :' || x_msg_data);

   END IF;

   DBMS_OUTPUT.PUT_LINE (

      '=======================================================');

END XX_UPDATE_MTL_STS;