CREATE OR REPLACE PACKAGE xx_create_item
IS
   g_miss_num    CONSTANT NUMBER       := 9.99e125;
   g_miss_char   CONSTANT VARCHAR2 (1) := CHR (0);
   g_miss_date   CONSTANT DATE         := TO_DATE ('1', 'j');
   g_false       CONSTANT VARCHAR2 (1) := fnd_api.g_false;                                                                
   g_true        CONSTANT VARCHAR2 (1) := fnd_api.g_true;

   PROCEDURE create_item (
      p_item_number         IN       VARCHAR2
    , p_description         IN       VARCHAR2
    , p_organization_id     IN       NUMBER
    , p_item_type           IN       VARCHAR2
    , x_inventory_item_id   OUT      NUMBER
    , x_organization_id     OUT      NUMBER
    , x_return_status       OUT      VARCHAR2
    , x_msg_count           OUT      NUMBER
    , x_msg_data            OUT      VARCHAR2
   );

   PROCEDURE create_item1 (
      p_item_number         IN       VARCHAR2
    , p_description         IN       VARCHAR2
    , p_organization_id     IN       NUMBER
    , p_item_type           IN       VARCHAR2
    , x_inventory_item_id   OUT      NUMBER
    , x_organization_id     OUT      NUMBER
    , x_return_status       OUT      VARCHAR2
    , x_msg_count           OUT      NUMBER
    , x_msg_data            OUT      VARCHAR2
   );
END xx_create_item;
/

CREATE OR REPLACE PACKAGE BODY xx_create_item
IS
   PROCEDURE create_item (
      p_item_number         IN       VARCHAR2
    , p_description         IN       VARCHAR2
    , p_organization_id     IN       NUMBER
    , p_item_type           IN       VARCHAR2
    , x_inventory_item_id   OUT      NUMBER
    , x_organization_id     OUT      NUMBER
    , x_return_status       OUT      VARCHAR2
    , x_msg_count           OUT      NUMBER
    , x_msg_data            OUT      VARCHAR2
   )
   IS
      l_template_id       NUMBER;
      x_item_id           NUMBER;
      x_org_id            NUMBER;
      l_item_number       VARCHAR2 (100);
      l_description       VARCHAR2 (4000);
      l_organization_id   NUMBER;
      v_msg_index_out     NUMBER;
      v_message           VARCHAR2 (100);
   BEGIN
      SELECT template_id
        INTO l_template_id
        FROM mtl_item_templates_b
       WHERE template_name = p_item_type;                                                                     

      l_item_number := p_item_number;
      l_description := p_description;
      l_organization_id := l_organization_id;
      ego_item_pub.process_item (p_api_version                 => 1.0
                               , p_transaction_type            => 'CREATE'
                               , p_language_code               => 'US'
                               , p_template_id               => 207
                               , p_organization_id             => 204
                               , p_master_organization_id      => 204
                               , p_description                 => 'TEST113'
                               , p_long_description            => 'TEST113'
                               , p_item_number                 => 'TEST113'
                               , p_segment1                    => 'TEST113'
                               , x_inventory_item_id           => x_inventory_item_id
                               , x_organization_id             => x_organization_id
                               , x_return_status               => x_return_status
                               , x_msg_count                   => x_msg_count
                               , x_msg_data                    => x_msg_data
                                );

      IF x_msg_count > 0
      THEN
         FOR v_index IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
            v_message := SUBSTR (x_msg_data, 1, 200);
            DBMS_OUTPUT.put_line (x_msg_data);
            DBMS_OUTPUT.put_line ('============================================================');
         END LOOP;

         DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
         DBMS_OUTPUT.put_line ('============================================================');
      END IF;
   END;

   PROCEDURE create_item1 (
      p_item_number         IN       VARCHAR2
    , p_description         IN       VARCHAR2
    , p_organization_id     IN       NUMBER
    , p_item_type           IN       VARCHAR2
    , x_inventory_item_id   OUT      NUMBER
    , x_organization_id     OUT      NUMBER
    , x_return_status       OUT      VARCHAR2
    , x_msg_count           OUT      NUMBER
    , x_msg_data            OUT      VARCHAR2
   )
   IS
      l_template_id       NUMBER;
      l_item_number       VARCHAR2 (100);
      l_description       VARCHAR2 (4000);
      l_organization_id   NUMBER;
      v_msg_index_out     NUMBER;
      v_message           VARCHAR2 (100);
   BEGIN
      l_item_number := p_item_number;
      l_description := p_description;
      l_organization_id := p_organization_id;
      ego_item_pub.process_item (p_api_version            => 1.0
                               , p_transaction_type       => 'CREATE'
                               , p_language_code          => 'US'
                               , p_template_name          => p_item_type
                               , p_item_number            => l_item_number
                               , p_segment1               => l_item_number
                               , p_organization_id        => l_organization_id
                               , p_description            => l_item_number
                               , p_long_description       => l_item_number
                               , x_inventory_item_id      => x_inventory_item_id
                               , x_organization_id        => x_organization_id
                               , x_return_status          => x_return_status
                               , x_msg_count              => x_msg_count
                                );
   END;
END xx_create_item;
/

SHOW errors
/
DECLARE
   v_msg_index_out   NUMBER;
   x_item_id         NUMBER;
   x_org_id          NUMBER;
   x_return_status   VARCHAR2 (1);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (4000);
   v_message         VARCHAR2 (4000);
BEGIN
   fnd_global.apps_initialize (1318, 50583, 401);
   inv_globals.set_org_id (204);
   xx_create_item.create_item1 (p_item_number            => 'TEST111'
                             , p_description            => 'TEST111'
                             , p_organization_id        => 204
                             , p_item_type              => 'Finished Good'
                             , x_inventory_item_id      => x_item_id
                             , x_organization_id        => x_org_id
                             , x_return_status          => x_return_status
                             , x_msg_count              => x_msg_count
                             , x_msg_data               => x_msg_data
                              );
         DBMS_OUTPUT.put_line ('Return Status is :'||x_return_status);                             
         DBMS_OUTPUT.put_line ('Message Count is :'||x_msg_count);
         DBMS_OUTPUT.put_line ('Create Item ID  is :'||x_item_id);
         DBMS_OUTPUT.put_line ('Created in Organization is :'||x_org_id);

   IF x_msg_count > 0
   THEN
      FOR v_index IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
         v_message := SUBSTR (x_msg_data, 1, 200);
         DBMS_OUTPUT.put_line (x_msg_data);
         DBMS_OUTPUT.put_line ('============================================================');
      END LOOP;

      DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
      DBMS_OUTPUT.put_line ('============================================================');
   END IF;
END;
/

DECLARE
   v_msg_index_out   NUMBER;
   x_item_id         NUMBER;
   x_org_id          NUMBER;
   x_return_status   VARCHAR2 (1);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (4000);
   v_message         VARCHAR2 (4000);
BEGIN
   --fnd_global.apps_initialize (1318, 50583, 401);
   inv_globals.set_org_id (204);
   xx_create_item.create_item1 (p_item_number            => 'TEST111'
                             , p_description            => 'TEST111'
                             , p_organization_id        => 204
                             , p_item_type              => 'Finished Good'
                             , x_inventory_item_id      => x_item_id
                             , x_organization_id        => x_org_id
                             , x_return_status          => x_return_status
                             , x_msg_count              => x_msg_count
                             , x_msg_data               => x_msg_data
                              );
         DBMS_OUTPUT.put_line ('Return Status is :'||x_return_status);                             
         DBMS_OUTPUT.put_line ('Message Count is :'||x_msg_count);
         DBMS_OUTPUT.put_line ('Create Item ID  is :'||x_item_id);
         DBMS_OUTPUT.put_line ('Created in Organization is :'||x_org_id);

   IF x_msg_count > 0
   THEN
      FOR v_index IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
         v_message := SUBSTR (x_msg_data, 1, 200);
         DBMS_OUTPUT.put_line (x_msg_data);
         DBMS_OUTPUT.put_line ('============================================================');
      END LOOP;

      DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
      DBMS_OUTPUT.put_line ('============================================================');
   END IF;
END;