Friday, October 13, 2017

PURGE_QUEUE_TABLE

This procedure purges messages from queue tables. You can perform various purge operations on both single-consumer and multi consumer queue tables for persistent and buffered messages

Syntax

DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table        IN   VARCHAR2,
   purge_condition    IN   VARCHAR2,
   purge_options      IN   aq$_purge_options_t);


Parameters Table:PURGE_QUEUE_TABLE Procedure Parameters



Usage Notes
You an purge selected messages from the queue table by specifying a purge_condition. Table 22-35 describes these parameters. Messages can be enqueued to and dequeued from the queue table while the queue table is being purged.
A trace file is generated in the udump destination when you run this procedure. It details what the procedure is doing.
This procedure commits batches of messages in autonomous transactions. Several such autonomous transactions may get executed as a part of one purge_queue_table call depending on the number of messages in the queue table.

Sample Code : 

DECLARE
 purge_opt_t dbms_aqadm.aq$_purge_options_t;
BEGIN
  dbms_aqadm.purge_queue_table('<OWNER.QUEUE_TABLE_NAME', NULL, purge_opt_t);
END;
/

Sunday, October 8, 2017

Sample script to update the Item attributes (Inventory)

DECLARE
   CURSOR c1
   IS
      SELECT inventory_item_id
           , segment1
        FROM mtl_system_items_b
       WHERE organization_id = 103
         AND inventory_item_status_code = 'PreRelease';

   TYPE sku_tbl_typ IS TABLE OF c1%ROWTYPE
      INDEX BY SIMPLE_INTEGER;

   sku_tbl                                      sku_tbl_typ;

   l_api_version                                NUMBER := 1.0;
   l_init_msg_list                              VARCHAR2(2) := fnd_api.g_true;
   l_commit                                     VARCHAR2(2) := fnd_api.g_false;
   l_item_tbl                                   ego_item_pub.item_tbl_type;
   l_role_grant_tbl                             ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
   x_item_tbl                                   ego_item_pub.item_tbl_type;
   x_message_list                               error_handler.error_tbl_type;
   x_return_status                              VARCHAR2(2);
   x_msg_count                                  NUMBER := 0;
   l_user_id                                    NUMBER := -1;
   l_resp_id                                    NUMBER := -1;
   l_application_id                             NUMBER := -1;
   l_rowcnt                                     NUMBER := 1;
   l_user_name                                  VARCHAR2(30) := '<USER_NAME>';
   l_resp_name                                  VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';
   i                                            NUMBER;
BEGIN
   -- 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
    WHERE responsibility_key = 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);

   OPEN c1;

   FETCH c1 BULK COLLECT INTO sku_tbl;

   CLOSE c1;

   IF sku_tbl.COUNT > 0
   THEN
      i                                                                            := sku_tbl.FIRST;

      LOOP
         l_item_tbl( i).transaction_type                                              := 'UPDATE';
         l_item_tbl( i).inventory_item_id                                             := sku_tbl(i).inventory_item_id;
         l_item_tbl( i).inventory_item_status_code                                    := 'Active';
         l_item_tbl( i).organization_id                                               := 103;
         EXIT WHEN i = sku_tbl.LAST;
         i                                                                            := sku_tbl.NEXT( i);
      END LOOP;
   END IF;

   -- call API to load Items
   DBMS_OUTPUT.put_line( '=====================================');
   DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.Process_Items API');
   ego_item_pub.process_items(p_api_version                             => l_api_version
                            , p_init_msg_list                           => l_init_msg_list
                            , p_commit                                  => l_commit
                            , p_item_tbl                                => l_item_tbl
                            , p_role_grant_tbl                          => l_role_grant_tbl
                            , x_item_tbl                                => x_item_tbl
                            , x_return_status                           => x_return_status
                            , x_msg_count                               => x_msg_count);

   DBMS_OUTPUT.put_line( '=====================================');
   DBMS_OUTPUT.put_line(   'Return Status: '
                        || x_return_status);

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
            FOR i IN 1 .. x_item_tbl.COUNT
            LOOP
               DBMS_OUTPUT.put_line(   'Inventory Item Id :'
                                    || TO_CHAR( x_item_tbl( i).inventory_item_id));
               DBMS_OUTPUT.put_line(   'Organization Id   :'
                                    || TO_CHAR( x_item_tbl( i).organization_id));
            END LOOP;

      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line( 'Error Messages :');
      error_handler.get_message_list( x_message_list => x_message_list);

      FOR i IN 1 .. x_message_list.COUNT
      LOOP
         DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
      END LOOP;
   END IF;

   DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line( 'Exception Occured :');
      DBMS_OUTPUT.put_line(   SQLCODE
                           || ':'
                           || SQLERRM);
      DBMS_OUTPUT.put_line( '=====================================');
END;

Wednesday, October 4, 2017

Script to create New Item copying existing SKU, it's attributes and all categories and also Assign it to all Inventory Organization.

Please test the script first in NON PROD instance.

DECLARE
   k                                            NUMBER;
   l_api_version                                NUMBER := 1.0;
   l_init_msg_list       VARCHAR2(2) := fnd_api.g_true;
   l_commit              VARCHAR2(2) := fnd_api.g_false;
   l_item_tbl            ego_item_pub.item_tbl_type;
   l_role_grant_tbl                             ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
   x_item_tbl        ego_item_pub.item_tbl_type;
   x_message_list    error_handler.error_tbl_type;
   x_return_status                              VARCHAR2(2);
   x_msg_count                                  NUMBER := 0;

   l_user_id                                    NUMBER := -1;
   l_resp_id                                    NUMBER := -1;
   l_application_id                             NUMBER := -1;
   l_rowcnt                                     NUMBER := 1;
   l_user_name         VARCHAR2(30) := '<USER_NAME>';
   l_resp_name         VARCHAR2(30) := '<EGO_DEVELOPMENT_MANAGER>';

   CURSOR c_query_org
   IS
      SELECT organization_id FROM mtl_parameters;

   TYPE organization_id_tbl_typ IS TABLE OF NUMBER
      INDEX BY SIMPLE_INTEGER;

   organization_id_tbl       organization_id_tbl_typ;

   PROCEDURE item_assignment(p_organization_id                            IN NUMBER
                           , p_inventory_item_id                          IN NUMBER)
   IS
      p_api_version                                NUMBER;
      p_init_msg_list                              VARCHAR2(32767);
      p_commit                                     VARCHAR2(32767);
      p_item_number                                VARCHAR2(32767);
      p_organization_code                          VARCHAR2(32767);
      p_primary_uom_code                           VARCHAR2(32767);
      x_return_status                              VARCHAR2(32767);
      x_msg_count                                  NUMBER;
      l_rowcnt                                     NUMBER := 1;
      x_message_list                               error_handler.error_tbl_type;
   BEGIN
      p_api_version             := 1.0;
      p_init_msg_list           := fnd_api.g_true;
      p_commit                  := fnd_api.g_false;
      DBMS_OUTPUT.put_line( '=====================================');
      DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.assign_item_to_org API');
      apps.ego_item_pub.assign_item_to_org(p_api_version
                                         , p_init_msg_list
                                         , p_commit
                                         , p_inventory_item_id
                                         , p_item_number
                                         , p_organization_id
                                         , p_organization_code
                                         , p_primary_uom_code
                                         , x_return_status
                                         , x_msg_count);
      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(   'Inventory Item Id :'
                              || TO_CHAR( p_inventory_item_id));
         DBMS_OUTPUT.put_line(   'Organization Id   :'
                              || TO_CHAR( p_organization_id));
      ELSE
         DBMS_OUTPUT.put_line( 'Error Messages :');
         error_handler.get_message_list( x_message_list => x_message_list);

         FOR i IN 1 .. x_message_list.COUNT
         LOOP
            DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
         END LOOP;
      END IF;

      COMMIT;
      DBMS_OUTPUT.put_line( '=====================================');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line( 'Exception Occured :');
         DBMS_OUTPUT.put_line(   SQLCODE
                              || ':'
                              || SQLERRM);
         DBMS_OUTPUT.put_line( '=====================================');
         COMMIT;
   END;

   PROCEDURE process_item_cat_assignment(
             p_source_item_id           IN NUMBER
           , p_desti_item_id            IN NUMBER)
   IS
      CURSOR c1
      IS
         SELECT /*+ CARDINALITY (MSI 1)*/
               msi.organization_id
              , msi.inventory_item_id
              , mic.category_id
              , mic.category_set_id
           FROM mtl_item_categories_v mic
              , mtl_system_items_b msi
          WHERE 1 = 1
            AND msi.organization_id = mic.organization_id
            AND msi.inventory_item_id = mic.inventory_item_id
            AND msi.inventory_item_id = p_source_item_id
            AND msi.organization_id = &&&Mas_org_id;

      l_api_version                                NUMBER := 1.0;
      l_init_msg_list          VARCHAR2(2) := fnd_api.g_true;
      l_commit                 VARCHAR2(2) := fnd_api.g_false;
      l_item_tbl               ego_item_pub.item_tbl_type;
      l_role_grant_tbl                    ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
      x_item_tbl           ego_item_pub.item_tbl_type;
      x_message_list       error_handler.error_tbl_type;
      x_return_status                              VARCHAR2(2);
      x_msg_data                                   VARCHAR2(2000);
      x_msg_count                                  NUMBER := 0;
      x_errorcode                                  NUMBER;
   BEGIN
      FOR z IN c1
      LOOP
         -- call API to load Items
         DBMS_OUTPUT.put_line( '=====================================');
         DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.process_item_cat_assignment API');
         ego_item_pub.process_item_cat_assignment(p_api_version                             => 1.0
         , p_init_msg_list               => fnd_api.g_true
         , p_commit                      => fnd_api.g_true
         , p_category_id                 => z.category_id
         , p_category_set_id             => z.category_set_id
         , p_old_category_id             => NULL
         , p_inventory_item_id           => p_desti_item_id
         , p_organization_id             => z.organization_id
         , p_transaction_type            => 'CREATE'
         , x_return_status               => x_return_status
         , x_errorcode                   => x_errorcode
         , x_msg_count                   => x_msg_count
         , x_msg_data                    => x_msg_data);

         DBMS_OUTPUT.put_line( '=====================================');
         DBMS_OUTPUT.put_line(   'Return Status: '
                              || x_return_status);

         IF (x_return_status = fnd_api.g_ret_sts_success)
         THEN
            FOR i IN 1 .. x_item_tbl.COUNT
            LOOP
               DBMS_OUTPUT.put_line(   'Inventory Item Id :'
                                    || TO_CHAR( x_item_tbl( i).inventory_item_id));
               DBMS_OUTPUT.put_line(   'Organization Id   :'
                                    || TO_CHAR( x_item_tbl( i).organization_id));
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line( 'Error Messages :');
            error_handler.get_message_list( x_message_list => x_message_list);

            FOR i IN 1 .. x_message_list.COUNT
            LOOP
               DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
            END LOOP;
         END IF;
      END LOOP;

      COMMIT;
      DBMS_OUTPUT.put_line( '=====================================');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line( 'Exception Occured :');
         DBMS_OUTPUT.put_line(   SQLCODE
                              || ':'
                              || SQLERRM);
         DBMS_OUTPUT.put_line( '=====================================');
   END;
BEGIN
   -- 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
    WHERE responsibility_key = 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);

   l_item_tbl( 1).transaction_type                          := 'CREATE';
   l_item_tbl( 1).copy_inventory_item_id                    := &Item_id_to_be_Copied;
   l_item_tbl( 1).segment1                                  := &New_item_name;
   l_item_tbl( 1).description                               := &New_item_Description;
   l_item_tbl( 1).organization_id                           := 103;

   -- call API to load Items
   DBMS_OUTPUT.put_line( '=====================================');
   DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.Process_Items API');
   ego_item_pub.process_items(p_api_version                             => l_api_version
                            , p_init_msg_list                           => l_init_msg_list
                            , p_commit                                  => l_commit
                            , p_item_tbl                                => l_item_tbl
                            , p_role_grant_tbl                          => l_role_grant_tbl
                            , x_item_tbl                                => x_item_tbl
                            , x_return_status                           => x_return_status
                            , x_msg_count                               => x_msg_count);

   DBMS_OUTPUT.put_line( '=====================================');
   DBMS_OUTPUT.put_line(   'Return Status: '
                        || x_return_status);

   IF (x_return_status = fnd_api.g_ret_sts_success)
   THEN
      FOR i IN 1 .. x_item_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line(   'Inventory Item Id :'
                              || TO_CHAR( x_item_tbl( i).inventory_item_id));
         DBMS_OUTPUT.put_line(   'Organization Id   :'
                              || TO_CHAR( x_item_tbl( i).organization_id));

         process_item_cat_assignment(l_item_tbl( 1).copy_inventory_item_id
                                   , x_item_tbl( i).inventory_item_id);

         OPEN c_query_org;

         FETCH c_query_org BULK COLLECT INTO organization_id_tbl;

         CLOSE c_query_org;

         k                                                        := organization_id_tbl.FIRST;

         LOOP
            item_assignment(p_organization_id                         => organization_id_tbl( k)
                          , p_inventory_item_id                       => x_item_tbl( i).inventory_item_id);
            EXIT WHEN k = organization_id_tbl.LAST;
            k                                                        := organization_id_tbl.NEXT( k);
         END LOOP;
      END LOOP;
   ELSE
      DBMS_OUTPUT.put_line( 'Error Messages :');
      error_handler.get_message_list( x_message_list => x_message_list);

      FOR i IN 1 .. x_message_list.COUNT
      LOOP
         DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
      END LOOP;
   END IF;

   COMMIT;
   DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line( 'Exception Occured :');
      DBMS_OUTPUT.put_line(   SQLCODE
                           || ':'
                           || SQLERRM);
      DBMS_OUTPUT.put_line( '=====================================');
END;