Thursday 18 June 2015

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;

No comments:

Post a Comment