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;

attach responsibility to user in oracle apps from backend

DECLARE
   v_user_name             VARCHAR2 (30)  := 'RAMYA';
   v_responsibility_name   VARCHAR2 (100) := 'System Administrator';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key    VARCHAR2 (100) := NULL;
   v_security_group        VARCHAR2 (100) := NULL;
   v_description           VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name, fr.responsibility_key,
          fsg.security_group_key, frt.description
     INTO v_application_name, v_responsibility_key,
          v_security_group, v_description
     FROM apps.fnd_responsibility fr,
          fnd_application fa,
          fnd_security_groups fsg,
          fnd_responsibility_tl frt
    WHERE frt.responsibility_name = v_responsibility_name
      AND frt.LANGUAGE = USERENV ('LANG')
      AND frt.responsibility_id = fr.responsibility_id
      AND fr.application_id = fa.application_id
      AND fr.data_group_id = fsg.security_group_id;

   fnd_user_pkg.addresp (username            => v_user_name,
                         resp_app            => v_application_name,
                         resp_key            => v_responsibility_key,
                         security_group      => v_security_group,
                         description         => v_description,
                         start_date          => SYSDATE,
                         end_date            => NULL
                        );
   COMMIT;
   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is attached to the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
                         (   'Unable to attach responsibility to user due to'
                          || SQLCODE
                          || ' '
                          || SUBSTR (SQLERRM, 1, 100)
                         );
END;