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;

Pragma Autonomous Transaction

An autonomous transaction is an independent transaction that is initiated by another transaction,
and executes without interfering with the parent transaction. When an autonomous transaction is called,
the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.

Example 1:
CREATE OR REPLACE TRIGGER tab1_trig
   AFTER INSERT
   ON tab1
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO LOG
        VALUES (SYSDATE, 'Insert on TAB1');

   COMMIT;                             -- only allowed in autonomous triggers
END;

Example 2:
----------
CREATE TABLE xhl_test (
test_value VARCHAR2(25));

CREATE OR REPLACE PROCEDURE xhl_test1
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO xhl_test
               (test_value
               )
        VALUES ('Child block insert'
               );

   COMMIT;
END xhl_test1;

CREATE OR REPLACE PROCEDURE xhl_test2
IS
BEGIN
   INSERT INTO xhl_test
               (test_value
               )
        VALUES ('Parent block insert'
               );

   xhl_test1;
   ROLLBACK;
END xhl_test2;

exec xhl_test2;

select * from xhl_test

TRUNCATE TABLE xhl_test;

form personalization tables and form personalization Query

SELECT   fpt.application_name, ff.form_name source_form_name,
         fft.user_form_name,              -- fft.description form_description,
                            fff.function_name, ffft.user_function_name,
         ffft.description function_description,
         ffcr.SEQUENCE personalize_rule_sequence,
         ffcr.description personalize_rule_description,
         DECODE (ffcr.rule_type,
                 'F', 'Form',
                 'A', 'Function'
                ) personalize_rule_level,
         ffcr.enabled personalize_rule_enabled,
         ffcr.trigger_event personalize_rule_event, ffcr.trigger_object,
         ffcr.condition personalize_rule_condition,
         DECODE (ffcs.level_id,
                 10, 'Industry',
                 20, 'Site',
                 30, 'Responsibility',
                 40, 'User'
                ) context_level,
         DECODE (ffcs.level_id,
                 10, '',
                 20, '',
                 30, frt.responsibility_name,
                 40, fu.user_name
                ) context_level_value,
         ffca.SEQUENCE action_sequence,
         DECODE (ffca.action_type,
                 'P', 'Property',
                 'M', 'Message',
                 'B', 'Builtin',
                 'S', 'Menu',
                 ''
                ) action_type,            --  ffca.summary action_description,
         ffca.enabled action_enabled,
         DECODE (ffca.LANGUAGE,
                 '*', 'All',
                 'US', 'American English',
                 'AR', 'Arabic'
                ) action_language,
         DECODE (ffca.action_type,
                 'B', ffca.builtin_type,
                 NULL
                ) action_builtin_type,
         DECODE (ffca.action_type,
                 'B', ffca.builtin_arguments,
                 NULL
                ) action_builtin_arguments,
         ffcr.last_update_date
    FROM fnd_application fp,
         fnd_application_tl fpt,
         fnd_form ff,
         fnd_form_tl fft,
         fnd_form_functions fff,
         fnd_form_functions_tl ffft,
         fnd_form_custom_rules ffcr,
         fnd_form_custom_scopes ffcs,
         fnd_responsibility_tl frt,
         fnd_user fu,
         fnd_form_custom_actions ffca,
         fnd_form_custom_prop_list ffcpl
   WHERE                                           ----------------APPLICATION
         fp.application_id = fpt.application_id
     AND fpt.LANGUAGE = 'US'                     ------------------------ FORM
     AND fpt.application_id = ff.application_id
     AND ff.form_id = fft.form_id
     AND fft.LANGUAGE = 'US'                 ------------------------ FUNCTION
     AND ff.form_id = fff.form_id
     AND fff.function_id = ffft.function_id
     AND ffft.LANGUAGE = 'US'             ------------------------ Custom Rule
     AND ff.form_name = ffcr.form_name
     AND ffcr.function_name =
                        fff.function_name
                                         ------------------------ Custom Scope
     AND ffcr.ID = ffcs.rule_id
     AND ffcs.level_value = frt.responsibility_id(+)
     AND frt.LANGUAGE(+) = 'US'
     AND ffcs.level_value = fu.user_id(+)
                                       ------------------------ Custom Actions
     AND ffcr.ID = ffca.rule_id
     AND DECODE (ffca.action_type, 'P', ffca.property_name, 79) =
                                                             ffcpl.property_id
     AND DECODE (ffca.action_type, 'P', ffca.object_type, 'ITEM') =
                                                              ffcpl.field_type
     AND ff.form_name = 'GMEBDTED'
     AND ffcr.SEQUENCE IN (62, 64, 65, 66)
ORDER BY fft.application_id,
         ff.form_name,
         ffcr.function_name,
         ffcr.SEQUENCE,
         ffcs.level_id,
         ffcs.level_value,
         ffca.SEQUENCE

Assigning a value to a temporary variable (like Attribute)

Seq :1
Type :Property
Language :All
Enabled
Object Type :Item
Target Object :GME_BATCH_HEADER.ATTRIBUTE11
Property Name : Value
Value :
= select 'X' from mtl_lot_numbers where lot_number=:GME_PRODUCT_LOTS.LOT_NUMBER



Assigning null value to temporary variable (like Attribute11)

Seq :1
Type :Property
Language :All
Enabled
Object Type :Item
Target Object :GME_BATCH_HEADER.ATTRIBUTE11
Property Name : Value
Value :
= NULL

Convert the time zone Based on the Organization

CREATE OR REPLACE FUNCTION APPS.xx_org_tzone_conv (
   p_organization_id   NUMBER,
   pdate               DATE
)
   RETURN DATE
IS
   l_local_time   DATE;
   l_tz_time      VARCHAR2 (50);
   l_local_tz     VARCHAR2 (10);
BEGIN
   IF p_organization_id = 120
   THEN
      BEGIN
         SELECT SUBSTR (TO_CHAR (TZ_OFFSET ('US/Pacific')), 1, 6)
           INTO l_tz_time
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_tz_time := NULL;
      END;

      IF l_tz_time = '-07:00'
      THEN
         l_local_tz := 'PDT';
      ELSIF l_tz_time = '-08:00'
      THEN
         l_local_tz := 'PST';
      END IF;
   ELSIF p_organization_id = 200
   THEN
      BEGIN
         SELECT SUBSTR (TO_CHAR (TZ_OFFSET ('US/Eastern')), 1, 6)
           INTO l_tz_time
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_tz_time := NULL;
      END;

      IF l_tz_time = '-04:00'
      THEN
         l_local_tz := 'EDT';
      ELSIF l_tz_time = '-05:00'
      THEN
         l_local_tz := 'EST';
      END IF;

   END IF;

   dbms_output.put_line (l_tz_time || 'l_tz_time');
   dbms_output.put_line (l_local_tz || 'l_local_tz');

   BEGIN
      SELECT NEW_TIME (pdate, 'GMT', l_local_tz)
        INTO l_local_time
        FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_local_time := NULL;
   END;

   dbms_output.put_line (l_local_time || 'l_local_time');

   RETURN l_local_time;

EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
      dbms_output.put_line (SQLCODE || ':' ||SQLERRM);
END xx_org_tzone_conv;
/

Week of the Year and Week of the month ?

select to_char(sysdate,'WW') FROM DUAL --- To get the week of the year

select to_char(sysdate,'W') FROM DUAL   --- nto Get the week of the month