, , , , ,

R12 – Item Category Create/Update/Delete using API’s

There are few APIs in INV_ITEM_CATEGORY_PUB package related to item category. This article will follow a category flexfield structure. Please refer the below post for more detail.
1. INV_ITEM_CATEGORY_PUB.Create_Category:
DECLARE
l_category_rec    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
l_return_status   VARCHAR2(80);
l_error_code      NUMBER;
l_msg_count       NUMBER;
l_msg_data        VARCHAR2(80);
l_out_category_id NUMBER;
BEGIN
  l_category_rec.segment1 := 'RED';

  SELECT f.ID_FLEX_NUM
    INTO l_category_rec.structure_id
    FROM FND_ID_FLEX_STRUCTURES f
   WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS';

  l_category_rec.description := 'Red';

  INV_ITEM_CATEGORY_PUB.Create_Category
          (
          p_api_version   => 1.0,
          p_init_msg_list => FND_API.G_FALSE,
          p_commit        => FND_API.G_TRUE,
          x_return_status => l_return_status,
          x_errorcode     => l_error_code,
          x_msg_count     => l_msg_count,
          x_msg_data      => l_msg_data,
          p_category_rec  => l_category_rec,
          x_category_id   => l_out_category_id
          );
  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;


2. INV_ITEM_CATEGORY_PUB. Delete_Category:




DECLARE
l_return_status VARCHAR2(80);
l_error_code    NUMBER;
l_msg_count     NUMBER;
l_msg_data      VARCHAR2(80);
l_category_id   NUMBER;
BEGIN
  SELECT mcb.CATEGORY_ID
    INTO l_category_id
    FROM mtl_categories_b mcb
   WHERE mcb.SEGMENT1='RED'
     AND mcb.STRUCTURE_ID =
        (SELECT mcs_b.STRUCTURE_ID
           FROM mtl_category_sets_b mcs_b
          WHERE mcs_b.CATEGORY_SET_ID =
               (SELECT mcs_tl.CATEGORY_SET_ID
                  FROM mtl_category_sets_tl mcs_tl
                 WHERE CATEGORY_SET_NAME ='INV_COLORS_SET'
                 )
        );

    INV_ITEM_CATEGORY_PUB.Delete_Category
          (
          p_api_version     => 1.0,
          p_init_msg_list   => FND_API.G_FALSE,
          p_commit          => FND_API.G_TRUE,
          x_return_status   => l_return_status,
          x_errorcode       => l_error_code,
          x_msg_count       => l_msg_count,
          x_msg_data        => l_msg_data,
          p_category_id     => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Deletion of Item Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Deletion of Item Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;




3. INV_ITEM_CATEGORY_PUB.Update_Category_Description
Updates the category description.


DECLARE
         l_return_status VARCHAR2(80);
         l_error_code    NUMBER;
         l_msg_count     NUMBER;
         l_msg_data      VARCHAR2(80);
         l_category_id   NUMBER;
         l_description   VARCHAR2(80);
BEGIN
      select mcb.CATEGORY_ID into l_category_id
        from mtl_categories_b mcb
       where mcb.SEGMENT1='BLACK'
         and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
             from mtl_category_sets_b mcs_b
             where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                 from mtl_category_sets_tl mcs_tl
                 where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      l_description := 'new black color';

     INV_ITEM_CATEGORY_PUB.Update_Category_Description (
       p_api_version     => 1.0,
       p_init_msg_list   => FND_API.G_FALSE,
       p_commit          => FND_API.G_TRUE,
       x_return_status   => l_return_status,
       x_errorcode       => l_error_code,
       x_msg_count       => l_msg_count,
       x_msg_data        => l_msg_data,
       p_category_id     => l_category_id,
       p_description     => l_description);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;





Use following API for assigning a category to a category set. A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.





4. INV_ITEM_CATEGORY_PUB.Create_Valid_Category





Create a record in mtl_category_set_valid_cats.




DECLARE
        l_return_status   VARCHAR2(80);
        l_error_code      NUMBER;
        l_msg_count       NUMBER;
        l_msg_data        VARCHAR2(80);
        l_category_set_id NUMBER;
        l_category_id     NUMBER;
BEGIN
       select mcs_tl.CATEGORY_SET_ID into l_category_set_id
         from mtl_category_sets_tl mcs_tl
        where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

       select mcb.CATEGORY_ID into l_category_id
         from mtl_categories_b mcb
        where mcb.SEGMENT1='RED'
          and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
              from mtl_category_sets_b mcs_b
              where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                    from mtl_category_sets_tl mcs_tl
                    where CATEGORY_SET_NAME ='INV_COLORS_SET'));

       INV_ITEM_CATEGORY_PUB.Create_Valid_Category (
             p_api_version        => 1.0,
             p_init_msg_list      => FND_API.G_FALSE,
             p_commit             => FND_API.G_TRUE,
             x_return_status      => l_return_status,
             x_errorcode          => l_error_code,
             x_msg_count          => l_msg_count,
             x_msg_data           => l_msg_data,
             p_category_set_id    => l_category_set_id,
             p_category_id        => l_category_id,
             p_parent_category_id => NULL );

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;





5. INV_ITEM_CATEGORY_PUB.Delete_Valid_Category





Delete the record from mtl_category_set_valid_cats.




DECLARE
           l_return_status    VARCHAR2(80);
           l_error_code       NUMBER;
           l_msg_count        NUMBER;
           l_msg_data         VARCHAR2(80);
           l_category_set_id  NUMBER;
           l_category_id      NUMBER;
BEGIN
         select mcs_tl.CATEGORY_SET_ID into l_category_set_id
           from mtl_category_sets_tl mcs_tl
          where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET';

         select mcb.CATEGORY_ID into l_category_id
           from mtl_categories_b mcb
          where mcb.SEGMENT1='RED'
            and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID
                from mtl_category_sets_b mcs_b
                where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID
                  from mtl_category_sets_tl mcs_tl
                  where CATEGORY_SET_NAME ='INV_COLORS_SET'));

      INV_ITEM_CATEGORY_PUB.Delete_Valid_Category (
            p_api_version      => 1.0,
            p_init_msg_list    => FND_API.G_FALSE,
            p_commit           => FND_API.G_TRUE,
            x_return_status    => l_return_status,
            x_errorcode        => l_error_code,
            x_msg_count        => l_msg_count,
            x_msg_data         => l_msg_data,
            p_category_set_id  => l_category_set_id,
            p_category_id      => l_category_id);

  IF l_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id);
  ELSE
    DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code);
    ROLLBACK;
  END IF;
END ;






/by 
, , , , , , 
R12 – Assign Inventory Item to an Child Organization using API
EGO_ITEM_PUB package provides functionality for maintaining items, item revisions, etc. We can use ASSIGN_ITEM_TO_ORG procedure to assign one item to an organization.

The procedure definition is:


PROCEDURE Assign_Item_To_Org(
    p_api_version             IN      NUMBER
   ,p_init_msg_list           IN      VARCHAR2        DEFAULT  G_FALSE
   ,p_commit                  IN      VARCHAR2        DEFAULT  G_FALSE
   ,p_Inventory_Item_Id       IN      NUMBER          DEFAULT  G_MISS_NUM
   ,p_Item_Number             IN      VARCHAR2        DEFAULT  G_MISS_CHAR
   ,p_Organization_Id         IN      NUMBER          DEFAULT  G_MISS_NUM
   ,p_Organization_Code       IN      VARCHAR2        DEFAULT  G_MISS_CHAR
   ,p_Primary_Uom_Code        IN      VARCHAR2        DEFAULT  G_MISS_CHAR
   ,x_return_status           OUT NOCOPY  VARCHAR2
   ,x_msg_count               OUT NOCOPY  NUMBER);




The parameters are:


    

  • P_API_VERSION – A decimal number indicating major and minor revisions to the API. Pass 1.0 unless otherwise indicated in the API parameter list.
    • 

  • P_INIT_MSG_LIST – A one-character flag indicating whether to initialize the FND_MSG_PUB package’s message stack at the beginning of API processing (and thus remove any messages that may exist on the stack from prior processing in the same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
    • 

  • P_COMMIT – A one-character flag indicating whether to commit work at the end of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
    • 

  • P_INVENTORY_ITEM_ID – Inventory Item Id of the Item
    • 

  • P_ITEM_NUMBER – Segment1 of the Item
    • 

  • P_ORGANIZATION_ID – Organization Id of the Organization to whom Item must be assigned
    • 

  • P_ORGANIZATION_CODE – 3 character Organization Code of the Organization to whom Item must be assigned
    • 

  • P_PRIMARY_UOM_CODE – Primary Unit of Measure of the item.
    • 

  • X_RETURN_STATUS – A one-character code indicating whether any errors occurred during processing (in which case error messages will be present on the FND_MSG_PUB package’s message stack). Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.
    • 

  • X_MSG_COUNT – An integer indicating the number of messages on the FND_MSG_PUB package’s message stack at the end of API processing.
    • 



Tested in R12.1.3


DECLARE
        g_user_id             fnd_user.user_id%TYPE :=NULL;
        l_appl_id             fnd_application.application_id%TYPE;
        l_resp_id             fnd_responsibility_tl.responsibility_id%TYPE;
        l_api_version    NUMBER := 1.0;
        l_init_msg_list       VARCHAR2(2) := fnd_api.g_false;
        l_commit        VARCHAR2(2) := FND_API.G_FALSE;
        x_message_list        error_handler.error_tbl_type;
        x_return_status    VARCHAR2(2);
        x_msg_count        NUMBER := 0;
BEGIN
        SELECT fa.application_id
          INTO l_appl_id
          FROM fnd_application fa
         WHERE fa.application_short_name = 'INV';

        SELECT fr.responsibility_id
          INTO l_resp_id
          FROM fnd_application fa, fnd_responsibility_tl fr
         WHERE fa.application_short_name = 'INV'
           AND fa.application_id = fr.application_id
           AND UPPER (fr.responsibility_name) = 'INVENTORY';

        fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

        EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
                   P_API_VERSION          => l_api_version
                ,  P_INIT_MSG_LIST        => l_init_msg_list
                ,  P_COMMIT               => l_commit
                ,  P_INVENTORY_ITEM_ID    => 1004
                ,  p_item_number          => TEST1010
                ,  p_organization_id      => 11047
                ,  P_ORGANIZATION_CODE    => 'DXN'
                ,  P_PRIMARY_UOM_CODE     => 'EA'
                ,  X_RETURN_STATUS        => x_return_status
                ,  X_MSG_COUNT            => x_msg_count
            );
        DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);
        IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
          DBMS_OUTPUT.PUT_LINE('Error Messages :');
          Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
            FOR j IN 1..x_message_list.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);
            END LOOP;
        END IF;
EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;




/by 
, , , , , 
R12 – OPM Operations Creation using API
DECLARE
   CURSOR c_operation IS
      SELECT DISTINCT oprn_no
             , oprn_desc
             , process_qty_uom
             , oprn_vers
             , delete_mark
             , effective_start_date
             , operation_status
             , owner_organization_id
             , activity
             , offset_interval
             , activity_factor
        FROM mii_gmd_operations
       WHERE flag IS NULL;
  
   CURSOR c_resource (
      p_oprn_no     varchar2,
      p_activity    varchar2
   ) IS
      SELECT resources,
             process_qty,
             resource_process_uom,
             resource_usage,
             resource_usage_uom,
             cost_cmpntcls_id,
             cost_analysis_code,
             prim_rsrc_ind,
             resource_count,
             scale_type,
             offset_interval
        FROM mii_gmd_operations
       WHERE oprn_no = p_oprn_no
         AND activity = p_activity
       ORDER BY PRIM_RSRC_IND;
       
   l_operations      gmd_operations%ROWTYPE;
   l_oprn_actv_tbl   gmd_operations_pub.gmd_oprn_activities_tbl_type;
   l_oprn_rsrc_tbl   gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
   l_count           NUMBER                                              := 0;
   l_count1           NUMBER                                              := 0;
   l_loop_cnt        NUMBER                                              := 0;
   l_record_count    NUMBER                                              := 0;
   l_data            VARCHAR2 (2000);
   l_data1            VARCHAR2 (2000);
   l_return_status   VARCHAR2 (1);
   l_return_status1   VARCHAR2 (1);
   l_status          VARCHAR2 (1);
   i                 NUMBER                                              := 1;
   l_dummy_cnt       NUMBER;
   l_api_version     NUMBER                                              := 1;
   l_init_msg_list   BOOLEAN;
   l_commit          BOOLEAN;
   return_sts        BOOLEAN;
   v_oprn_line_id    number;
   v_count           number;
   v_resources                  varchar2(100);
   v_process_qty                number;
   v_resource_process_uom       varchar2(5);
   v_resource_usage             number;
   v_resource_usage_uom         varchar2(5);
   v_cost_cmpntcls_id           number;
   v_cost_analysis_code         varchar2(5);
   v_prim_rsrc_ind              number;
   v_resource_count             number;
   v_scale_type                 number;
   v_offset_interval            number;
BEGIN


   fnd_global.apps_initialize (user_id           => 1090,
                               resp_id           => 22882,
                               resp_appl_id      => 552
                              );
  
   l_init_msg_list := TRUE;
   l_commit := TRUE;
  
   FOR l_oprn IN c_operation
   LOOP
      DBMS_OUTPUT.put_line (‘Value Test ‘ || l_operations.oprn_no);
     
      l_operations.oprn_no := l_oprn.oprn_no;
      l_operations.oprn_desc := l_oprn.oprn_desc;
      l_operations.process_qty_uom := l_oprn.process_qty_uom;
      l_operations.oprn_vers := l_oprn.oprn_vers;
      l_operations.delete_mark := l_oprn.delete_mark;
      l_operations.effective_start_date := l_oprn.effective_start_date;
      l_operations.operation_status := l_oprn.operation_status;
      l_operations.owner_organization_id := l_oprn.owner_organization_id;
     
      l_oprn_actv_tbl (1).activity := l_oprn.activity;
      l_oprn_actv_tbl (1).offset_interval := l_oprn.offset_interval;
      l_oprn_actv_tbl (1).activity_factor := l_oprn.activity_factor;
      l_oprn_actv_tbl (1).delete_mark := l_oprn.delete_mark;
      –l_oprn_actv_tbl (1).activity := l_master.activity;
     
      SELECT COUNT(*)
        INTO v_count
        FROM mii_gmd_operations
       WHERE oprn_no = l_oprn.oprn_no
         AND activity = l_oprn.activity
       ORDER BY PRIM_RSRC_IND;
     
      OPEN c_resource (l_oprn.oprn_no, l_oprn.activity);
     
      FOR i IN 1 .. v_count
      LOOP
         FETCH c_resource INTO v_resources,
             v_process_qty,
             v_resource_process_uom,
             v_resource_usage,
             v_resource_usage_uom,
             v_cost_cmpntcls_id,
             v_cost_analysis_code,
             v_prim_rsrc_ind,
             v_resource_count,
             v_scale_type,
             v_offset_interval;
        
         l_oprn_rsrc_tbl (i).activity := l_oprn.activity;
         l_oprn_rsrc_tbl (i).resources := v_resources;
         l_oprn_rsrc_tbl (i).process_qty := v_process_qty;
         l_oprn_rsrc_tbl (i).resource_process_uom := v_resource_process_uom;
         l_oprn_rsrc_tbl (i).resource_usage := v_resource_usage;
         l_oprn_rsrc_tbl (i).resource_usage_uom := v_resource_usage_uom;
         l_oprn_rsrc_tbl (i).cost_cmpntcls_id := v_cost_cmpntcls_id;
         l_oprn_rsrc_tbl (i).cost_analysis_code := v_cost_analysis_code;
         l_oprn_rsrc_tbl (i).prim_rsrc_ind := v_prim_rsrc_ind;
         l_oprn_rsrc_tbl (i).resource_count := v_resource_count;
         l_oprn_rsrc_tbl (i).scale_type := v_scale_type;
         l_oprn_rsrc_tbl (i).offset_interval := v_offset_interval;
        
         UPDATE mii_gmd_operations
            set flag = ‘Y’
          WHERE oprn_no = l_oprn.oprn_no
            AND resources = v_resources;
        
      END LOOP;
     
      CLOSE c_resource;
     
      gmd_operations_pub.insert_operation (p_api_version        => 1.0,
                                        p_init_msg_list      => l_init_msg_list,
                                        p_commit             => l_commit,
                                        p_operations         => l_operations,
                                        p_oprn_actv_tbl      => l_oprn_actv_tbl,
                                        p_oprn_rsrc_tbl      => l_oprn_rsrc_tbl,
                                        x_message_count      => l_count,
                                        x_return_status      => l_return_status,
                                        x_message_list       => l_data
                                       );  
                                      
      UPDATE gmd_operations_b
      set operation_status = 700
      WHERE operation_status = 100
      AND   oprn_no = l_oprn.oprn_no;   
     
      IF l_status = ‘E’ OR l_status = ‘U’
         THEN
            UPDATE mii_gmd_operations
            set flag = ‘E’
                , note = l_data
          WHERE oprn_no = l_oprn.oprn_no;
         END IF;
        
   END LOOP;
   
   IF l_count >= 0
   THEN
      l_loop_cnt := 1;


      LOOP
         fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                          p_data               => l_data,
                          p_encoded            => fnd_api.g_false,
                          p_msg_index_out      => l_dummy_cnt
                         );
         DBMS_OUTPUT.put_line (‘Record = ‘ || l_loop_cnt);
         DBMS_OUTPUT.put_line (l_data);


         IF l_status = ‘E’ OR l_status = ‘U’
         THEN
            l_data := CONCAT (‘ERROR ‘, l_data);
         END IF;


         DBMS_OUTPUT.put_line (l_data);


         IF (l_status = ‘U’)
         THEN
            l_return_status := l_status;
         ELSIF (l_status = ‘E’ AND l_return_status <> ‘U’)
         THEN
            l_return_status := l_status;
         ELSE
            l_return_status := l_status;
         END IF;


         l_loop_cnt := l_loop_cnt + 1;


         IF l_loop_cnt > l_count
         THEN
            EXIT;
         END IF;
      END LOOP;
   END IF;
   COMMIT;
   DBMS_OUTPUT.put_line (‘Sucess’);
END;
/


/by 
, , , , , 
R12 – OPM Routing Creation using API
DECLARE
   CURSOR c_routing_header IS
      SELECT DISTINCT ROUTING_NO,
             ROUTING_VERS,
             ROUTING_DESC,
             EFFECTIVE_START_DATE,
             ROUTING_QTY,
             ROUTING_UOM,
             OWNER_ID,
             OWNER_ORGANIZATION_ID,
             ROUTING_STATUS,
             DELETE_MARK
        FROM mii_gmd_routing
       WHERE flag IS NULL;
  
   CURSOR c_routing_step (
      p_routing_no  varchar2
   )
   IS
      SELECT ROUTINGSTEP_NO
             , OPRN_NO
             , STEP_QTY
             , STEPRELEASE_TYPE
             , ROUTINGSTEP_NO1
             , DEP_ROUTINGSTEP_NO
             , DEP_TYPE
             , STANDARD_DELAY
             , TRANSFER_PCT
             , ROUTINGSTEP_NO_UOM
        FROM mii_gmd_routing
       WHERE flag IS NULL
         AND routing_no = p_routing_no
       ORDER BY routingstep_no;
  
   l_routings                gmd_routings%ROWTYPE;
   l_routings_step_tbl       gmd_routings_pub.gmd_routings_step_tab;
   l_routings_step_dep_tbl   gmd_routings_pub.gmd_routings_step_dep_tab;
   l_count                   NUMBER                                     := 0;
   l_loop_cnt                NUMBER                                     := 0;
   l_record_count            NUMBER                                     := 0;
   l_data                    VARCHAR2 (4000);
   l_return_status           VARCHAR2 (1);
   l_status                  VARCHAR2 (1);
   i                         NUMBER                                     := 1;
   l_dummy_cnt               NUMBER;
   l_api_version             NUMBER                                     := 1;
   l_init_msg_list           BOOLEAN;
   l_commit                  BOOLEAN;
   return_sts                BOOLEAN;
  
   v_routingstep_no           number;
   v_oprn_id                  number;
   v_oprn_no                  varchar2(100);
   v_step_qty                 number;
   v_steprelease_type         number;
   v_routingstep_no1           number;
   v_dep_routingstep_no       number;
   v_dep_type                 number;
   v_standard_delay           number;
   v_transfer_pct             number;
   v_routingstep_no_uom           varchar2(100);
   v_count                      number;
BEGIN


   fnd_global.apps_initialize (user_id           => 1090,
                               resp_id           => 22882,
                               resp_appl_id      => 552
                              );


   l_init_msg_list := TRUE;
   l_commit := TRUE;


   FOR l_routing_header IN c_routing_header
   LOOP
      l_routings.routing_no := l_routing_header.routing_no;
      l_routings.routing_vers := l_routing_header.routing_vers;
      l_routings.routing_desc := l_routing_header.routing_desc;
      l_routings.routing_qty := l_routing_header.routing_qty;
      –l_routings.item_um := l_routing_header.item_um;
      l_routings.routing_uom := l_routing_header.routing_uom;
      l_routings.effective_start_date := l_routing_header.effective_start_date;
      l_routings.owner_id := l_routing_header.owner_id;
      l_routings.routing_status := l_routing_header.routing_status;
      l_routings.delete_mark := l_routing_header.delete_mark;
      l_routings.owner_organization_id := l_routing_header.owner_organization_id;
 
      SELECT COUNT(*)
        INTO v_count
        FROM mii_gmd_routing
       WHERE flag IS NULL
         AND routing_no = l_routing_header.routing_no;
        
      OPEN c_routing_step (l_routing_header.routing_no);
      FOR i IN 1 .. v_count
      LOOP
         FETCH c_routing_step INTO v_routingstep_no,
                                   v_oprn_no,
                                   v_step_qty,
                                   v_steprelease_type,
                                   v_routingstep_no1,
                                   v_dep_routingstep_no,
                                   v_dep_type,
                                   v_standard_delay,
                                   v_transfer_pct,
                                   v_routingstep_no_uom;
        
         SELECT oprn_id
           INTO v_oprn_id
           FROM GMD_OPERATIONS_VL    
          WHERE oprn_no = v_oprn_no;
        
         –dbms_output.put_line (‘v_oprn_id: ‘ || v_oprn_id);
                                 
         l_routings_step_tbl (i).routingstep_no := v_routingstep_no;
         l_routings_step_tbl (i).oprn_id := v_oprn_id;
         l_routings_step_tbl (i).step_qty := v_step_qty;
         l_routings_step_tbl (i).steprelease_type := v_steprelease_type;
        
         –dbms_output.put_line (‘l_routings_step_tbl (‘||i||’).routingstep_no: ‘ || l_routings_step_tbl (i).routingstep_no);
        
         –IF v_routingstep_no1 IS NOT NULL THEN
            l_routings_step_dep_tbl (i).routingstep_no := v_routingstep_no1;
            l_routings_step_dep_tbl (i).dep_routingstep_no := v_dep_routingstep_no;
            l_routings_step_dep_tbl (i).dep_type := v_dep_type;
            l_routings_step_dep_tbl (i).standard_delay := v_standard_delay;
            l_routings_step_dep_tbl (i).transfer_pct := v_transfer_pct;
            l_routings_step_dep_tbl (i).routingstep_no_uom := v_routingstep_no_uom;
         –END IF;
        
         UPDATE mii_gmd_routing
         set flag = ‘Y’
         WHERE routing_no = l_routing_header.routing_no
         AND oprn_no = v_oprn_no;
      END LOOP;
     
      CLOSE c_routing_step;
     
      –DBMS_OUTPUT.put_line (‘Value Test ‘ || l_routings.routing_no);


      gmd_routings_pub.insert_routing
                          (p_api_version                => 1.0,
                           p_init_msg_list              => l_init_msg_list,
                           p_commit                     => l_commit,
                           p_routings                   => l_routings,
                           p_routings_step_tbl          => l_routings_step_tbl,
                           p_routings_step_dep_tbl      => l_routings_step_dep_tbl,
                           x_message_count              => l_count,
                           x_return_status              => l_return_status,
                           x_message_list               => l_data
                          );
     
     
   END LOOP;
  
   IF l_count >= 0
   THEN
      l_loop_cnt := 1;


      LOOP
         fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                          p_data               => l_data,
                          p_encoded            => fnd_api.g_false,
                          p_msg_index_out      => l_dummy_cnt
                         );
         DBMS_OUTPUT.put_line (‘Record = ‘ || l_loop_cnt);
         DBMS_OUTPUT.put_line (l_data);


         IF l_status = ‘E’ OR l_status = ‘U’
         THEN
            l_data := CONCAT (‘ERROR ‘, l_data);
         END IF;


         DBMS_OUTPUT.put_line (l_data);


         IF (l_status = ‘U’)
         THEN
            l_return_status := l_status;
         ELSIF (l_status = ‘E’ AND l_return_status <> ‘U’)
         THEN
            l_return_status := l_status;
         ELSE
            l_return_status := l_status;
         END IF;


         l_loop_cnt := l_loop_cnt + 1;


         IF l_loop_cnt > l_count
         THEN
            EXIT;
         END IF;
      END LOOP;
   END IF;
   COMMIT;
END;


/by 
, , , , , , 
R12 – OPM Formula Creation using API
API Material:

DECLARE
   CURSOR c_master IS
      SELECT *
        FROM nfi_gmd_recipe
       WHERE flag = ‘V’
         AND routing_no IS NOT NULL;
  
   CURSOR c_formula (
      p_formula_id  number
   )
   IS
      SELECT *
        FROM FM_MATL_DTL
       WHERE formula_id = p_formula_id
       ORDER BY line_type, line_no;


   l_recipe_flex_tbl     gmd_recipe_detail.recipe_flex;
   l_recipe_mtl_tbl      gmd_recipe_detail.recipe_mtl_tbl;
   l_recipe              nfi_gmd_recipe%ROWTYPE;
   l_formula             FM_MATL_DTL%ROWTYPE;
   l_count               NUMBER                        := 0;
   l_count_mtl               NUMBER                        := 0;
   l_loop_cnt            NUMBER                        := 0;
   l_record_count        NUMBER                        := 0;
   l_data                VARCHAR2 (2000);
   l_data_mtl                VARCHAR2 (2000);
   l_return_status       VARCHAR2 (1);
   l_status              VARCHAR2 (1);
   i                     NUMBER                        := 1;
   l_dummy_cnt           NUMBER;
   l_api_version         NUMBER                        := 1;
   l_init_msg_list       BOOLEAN;
   l_commit              BOOLEAN;
   return_sts            BOOLEAN;
   v_routing_id          number;
   v_formula_id          number;
   v_routingstep_id      number;
   v_formulaline_id      number;
  
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
   fnd_global.apps_initialize (user_id           => 1090,
                               resp_id           => 50618,
                               resp_appl_id      => 552
                              );
  
   l_init_msg_list := TRUE;
   l_commit := TRUE;
  
   i := i;
  
—   IF NOT c_master%ISOPEN THEN
—      CLOSE c_master;
—   END IF;
  
   OPEN c_master;
  
   LOOP
      FETCH c_master INTO l_recipe;
      exit WHEN c_master%NOTFOUND;
     
      IF l_recipe.routing_no IS NOT NULL THEN
         SELECT routing_id
           INTO v_routing_id
           FROM fm_rout_hdr
          WHERE routing_no = l_recipe.routing_no
            AND routing_vers = l_recipe.routing_vers;
      END IF;
        
      SELECT formula_id
        INTO v_formula_id
        FROM FM_FORM_MST
       WHERE formula_no = l_recipe.formula_no
         AND formula_vers = l_recipe.formula_vers;
        
     
     
      IF l_recipe.routing_no IS NOT NULL THEN
        
         OPEN c_formula(v_formula_id);
         LOOP
            FETCH c_formula INTO l_formula;
            exit WHEN c_formula%NOTFOUND;
           
            IF l_formula.line_type = -1 AND l_formula.line_no = 1 THEN
               SELECT routingstep_id
                 INTO v_routingstep_id
                 FROM fm_rout_dtl
                WHERE routing_id = v_routing_id
                  AND routingstep_no = 10;
                 
            ELSIF  l_formula.line_type = -1 AND l_formula.line_no > 1 THEN
               SELECT routingstep_id
                 INTO v_routingstep_id
                 FROM fm_rout_dtl
                WHERE routing_id = v_routing_id
                  AND routingstep_no = 20;
           
            ELSIF  l_formula.line_type = 1 THEN
               SELECT routingstep_id
                 INTO v_routingstep_id
                 FROM fm_rout_dtl
                WHERE routing_id = v_routing_id
                  AND routingstep_no = 30;
                       
            END IF;
           
            l_recipe_mtl_tbl (i).recipe_no := l_recipe.recipe_no;
            l_recipe_mtl_tbl (i).recipe_version := l_recipe.recipe_version;
            l_recipe_mtl_tbl (i).formulaline_id := l_formula.formulaline_id;
            l_recipe_mtl_tbl (i).routingstep_id := v_routingstep_id;
           
            DBMS_OUTPUT.put_line(i);
            DBMS_OUTPUT.put_line(l_recipe_mtl_tbl (i).formulaline_id);
            DBMS_OUTPUT.put_line(l_recipe_mtl_tbl (i).recipe_no);
            i := i + 1;
           
            –DBMS_OUTPUT.put_line (‘Value Test ‘ || i || ‘: ‘|| l_recipe_mtl_tbl (i).recipe_no);
         END LOOP;
        
         CLOSE c_formula;
      END IF;
     
     
      UPDATE nfi_gmd_recipe
         set flag = ‘M’
       WHERE recipe_no = l_recipe.recipe_no;  
            
   END LOOP;
    
      CLOSE c_master;
      gmd_recipe_detail.create_recipe_mtl
                                  (p_api_version             => 1.0,
                                   –p_init_msg_list           => FND_API.G_TRUE,
                                   –p_commit                  => FND_API.G_TRUE,    
                                   p_called_from_forms       => ‘NO’,                      
                                   x_return_status           => l_return_status,
                                   x_msg_count               => l_count_mtl,
                                   x_msg_data                => l_data_mtl,
                                   p_recipe_mtl_tbl           => l_recipe_mtl_tbl,
                                   p_recipe_mtl_flex          => l_recipe_flex_tbl
                                  );                         
         
  
   IF l_count_mtl >= 0
   THEN
      l_loop_cnt := 1;


      LOOP
         fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                          p_data               => l_data_mtl,
                          p_encoded            => fnd_api.g_false,
                          p_msg_index_out      => l_dummy_cnt
                         );
         DBMS_OUTPUT.put_line (‘Record = ‘ || l_loop_cnt);
         DBMS_OUTPUT.put_line (l_data_mtl);


         IF l_status = ‘E’ OR l_status = ‘U’
         THEN
            l_data_mtl := CONCAT (‘ERROR ‘, l_data_mtl);
         END IF;


         DBMS_OUTPUT.put_line (l_data_mtl);


         IF (l_status = ‘U’)
         THEN
            l_return_status := l_status;
         ELSIF (l_status = ‘E’ AND l_return_status <> ‘U’)
         THEN
            l_return_status := l_status;
         ELSE
            l_return_status := l_status;
         END IF;


         l_loop_cnt := l_loop_cnt + 1;


         IF l_loop_cnt > l_count_mtl
         THEN
            EXIT;
         END IF;
      END LOOP;
   END IF;
   COMMIT;


END;


API Validity Rule:

DECLARE
   CURSOR c_master IS
      SELECT *
        FROM nfi_gmd_recipe
       WHERE flag = ‘Y’;
 
      
   l_recipe_flex_tbl     gmd_recipe_detail.recipe_flex;
   l_recipe_vr_tbl       gmd_recipe_detail.recipe_vr_tbl;
   l_recipe              nfi_gmd_recipe%ROWTYPE;
   l_formula             FM_MATL_DTL%ROWTYPE;
   l_count               NUMBER                        := 0;
   l_count_mtl               NUMBER                        := 0;
   l_loop_cnt            NUMBER                        := 0;
   l_record_count        NUMBER                        := 0;
   l_data                VARCHAR2 (2000);
   l_data_mtl                VARCHAR2 (2000);
   l_return_status       VARCHAR2 (1);
   l_status              VARCHAR2 (1);
   i                     NUMBER                        := 1;
   l_dummy_cnt           NUMBER;
   l_api_version         NUMBER                        := 1;
   l_init_msg_list       BOOLEAN;
   l_commit              BOOLEAN;
   return_sts            BOOLEAN;
   v_routing_id          number;
   v_formula_id          number;
   v_routingstep_id      number;
   v_formulaline_id      number;
  
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
   fnd_global.apps_initialize (user_id           => 1090,
                               resp_id           => 50618,
                               resp_appl_id      => 552
                              );
  
   l_init_msg_list := TRUE;
   l_commit := TRUE;
  
   i := 0;
  
   DBMS_OUTPUT.put_line (‘Open cursor’);
  
   OPEN c_master;
  
   LOOP
      FETCH c_master INTO l_recipe;
      exit WHEN c_master%NOTFOUND;
     
           
      l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_no := l_recipe.recipe_no;
      l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_version := l_recipe.recipe_version;
      l_recipe_vr_tbl (c_master%ROWCOUNT).organization_id := l_recipe.organization_id;
      l_recipe_vr_tbl (c_master%ROWCOUNT).start_date := l_recipe.start_date;
     
     
  
      DBMS_OUTPUT.put_line (‘Value Test ‘ || c_master%ROWCOUNT || ‘: ‘|| l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_no);
     
      UPDATE nfi_gmd_recipe
         set flag = ‘V’
       WHERE recipe_no = l_recipe.recipe_no;  
            
   END LOOP;
  
    CLOSE c_master;
    DBMS_OUTPUT.put_line (‘Close cursor’);
   
      gmd_recipe_detail.create_recipe_vr
                                  (p_api_version             => 1.0,
                                   p_init_msg_list           => fnd_api.g_true,
                                   p_commit                  => fnd_api.g_true,    
                                   p_called_from_forms       => ‘NO’,                      
                                   x_return_status           => l_return_status,
                                   x_msg_count               => l_count,
                                   x_msg_data                => l_data,
                                   p_recipe_vr_tbl           => l_recipe_vr_tbl,
                                   p_recipe_vr_flex          => l_recipe_flex_tbl
                                  );                         
       
   UPDATE GMD_RECIPE_VALIDITY_RULES
      set VALIDITY_RULE_STATUS = 700
    WHERE VALIDITY_RULE_STATUS != 700;
  
   IF l_count >= 0
   THEN
      l_loop_cnt := 1;


      LOOP
         fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                          p_data               => l_data,
                          p_encoded            => fnd_api.g_false,
                          p_msg_index_out      => l_dummy_cnt
                         );
         DBMS_OUTPUT.put_line (‘Record = ‘ || l_loop_cnt);
         DBMS_OUTPUT.put_line (l_data);


         IF l_status = ‘E’ OR l_status = ‘U’
         THEN
            l_data := CONCAT (‘ERROR ‘, l_data);
         END IF;


         DBMS_OUTPUT.put_line (l_data);


         IF (l_status = ‘U’)
         THEN
            l_return_status := l_status;
         ELSIF (l_status = ‘E’ AND l_return_status <> ‘U’)
         THEN
            l_return_status := l_status;
         ELSE
            l_return_status := l_status;
         END IF;


         l_loop_cnt := l_loop_cnt + 1;


         IF l_loop_cnt > l_count
         THEN
            EXIT;
         END IF;
      END LOOP;
   END IF;
   COMMIT;


END;


API Recipe Header:

DECLARE
CURSOR c_master IS
SELECT *
FROM nfi_gmd_recipe
WHERE flag IS NULL;


l_recipe_header_tbl gmd_recipe_header.recipe_tbl;
l_recipe_flex_tbl gmd_recipe_header.recipe_flex;
l_recipe nfi_gmd_recipe%ROWTYPE;
l_formula FM_MATL_DTL%ROWTYPE;
l_count NUMBER := 0;
l_count_mtl NUMBER := 0;
l_loop_cnt NUMBER := 0;
l_record_count NUMBER := 0;
l_data VARCHAR2 (2000);
l_data_mtl VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_status VARCHAR2 (1);
i NUMBER := 1;
l_dummy_cnt NUMBER;
l_api_version NUMBER := 1;
l_init_msg_list BOOLEAN;
l_commit BOOLEAN;
return_sts BOOLEAN;
v_routing_id number;
v_formula_id number;
v_routingstep_id number;
v_formulaline_id number;


BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id => 1090,
resp_id => 50618,
resp_appl_id => 552
);


l_init_msg_list := TRUE;
l_commit := TRUE;


DBMS_OUTPUT.put_line (‘Open cursor’);


OPEN c_master;


LOOP
FETCH c_master INTO l_recipe;
exit WHEN c_master%NOTFOUND;


IF l_recipe.routing_no IS NOT NULL THEN
SELECT routing_id
INTO v_routing_id
FROM fm_rout_hdr
WHERE routing_no = l_recipe.routing_no
AND routing_vers = l_recipe.routing_vers;


l_recipe_header_tbl (c_master%ROWCOUNT).routing_id := v_routing_id;


END IF;


SELECT formula_id
INTO v_formula_id
FROM FM_FORM_MST
WHERE formula_no = l_recipe.formula_no
AND formula_vers = l_recipe.formula_vers;


l_recipe_header_tbl (c_master%ROWCOUNT).recipe_no := l_recipe.recipe_no;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_description := l_recipe.recipe_description;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_version := l_recipe.recipe_version;
l_recipe_header_tbl (c_master%ROWCOUNT).user_id := 1090;
l_recipe_header_tbl (c_master%ROWCOUNT).owner_organization_id := l_recipe.owner_organization_id;
l_recipe_header_tbl (c_master%ROWCOUNT).formula_id := v_formula_id;


l_recipe_header_tbl (c_master%ROWCOUNT).recipe_status := ‘700’;


DBMS_OUTPUT.put_line (‘Value Test ‘ || c_master%ROWCOUNT || ‘: ‘|| l_recipe_header_tbl (c_master%ROWCOUNT).recipe_no);


UPDATE nfi_gmd_recipe
set flag = ‘Y’
WHERE recipe_no = l_recipe.recipe_no;


END LOOP;


CLOSE c_master;


DBMS_OUTPUT.put_line (‘Close cursor’);


gmd_recipe_header.create_recipe_header
(p_api_version => 1.0,
–p_init_msg_list => FND_API.G_TRUE,
–p_commit => FND_API.G_TRUE,
p_recipe_header_tbl => l_recipe_header_tbl,
p_recipe_header_flex => l_recipe_flex_tbl,
x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_data
);


IF l_count >= 0
THEN
l_loop_cnt := 1;


LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
DBMS_OUTPUT.put_line (‘Record = ‘ || l_loop_cnt);
DBMS_OUTPUT.put_line (l_data);


IF l_status = ‘E’ OR l_status = ‘U’
THEN
l_data := CONCAT (‘ERROR ‘, l_data);
END IF;


DBMS_OUTPUT.put_line (l_data);


IF (l_status = ‘U’)
THEN
l_return_status := l_status;
ELSIF (l_status = ‘E’ AND l_return_status <> ‘U’)
THEN
l_return_status := l_status;
ELSE
l_return_status := l_status;
END IF;


l_loop_cnt := l_loop_cnt + 1;


IF l_loop_cnt > l_count
THEN
EXIT;
END IF;
END LOOP;
END IF;
COMMIT;


END;


API Formula:

DECLARE
   CURSOR c_header IS
      SELECT DISTINCT formula_no
      FROM mii_gmd_formula
       WHERE flag IS NULL;
      
   CURSOR c_master  (
      p_formula     varchar2
   ) IS
      SELECT *
        FROM mii_gmd_formula
       WHERE flag IS NULL
         AND formula_no = p_formula;
      
   l_formula_header_tbl   gmd_formula_pub.formula_insert_hdr_tbl_type;
   l_formula              mii_gmd_formula%ROWTYPE;
   l_count                NUMBER                                      := 0;
   l_loop_cnt             NUMBER                                      := 0;
   l_record_count         NUMBER                                      := 0;
   l_data                 VARCHAR2 (2000);
   l_return_status        VARCHAR2 (1);
   l_status               VARCHAR2 (1);
   i                      NUMBER                                      := 1;
   l_dummy_cnt            NUMBER;
   l_api_version          NUMBER                                      := 1;
   l_init_msg_list        BOOLEAN;
   l_commit               BOOLEAN;
   return_sts             BOOLEAN;
   v_item_id              number;
   v_organization_id      number;
   v_user_id                            number;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
   fnd_global.apps_initialize (user_id           => 1090,
                               resp_id           => 22882,
                               resp_appl_id      => 552
                              );
/*FND_GLOBAL.APPS_INITIALIZE(user_id, resp_id, resp_appl_id);*/
   l_init_msg_list := TRUE;
   l_commit := TRUE;


FOR l_header IN c_header  
LOOP
   OPEN c_master(l_header.formula_no);
   LOOP
      FETCH c_master INTO l_formula;
      exit WHEN c_master%NOTFOUND;
     
      dbms_output.put_line (c_master%ROWCOUNT);
      BEGIN
         SELECT DISTINCT inventory_item_id
           INTO v_item_id
           FROM mtl_system_items_b
          WHERE segment1 = UPPER(l_formula.inventory_item_code);
      
         SELECT organization_id
          INTO v_organization_id
           FROM mtl_parameters
          WHERE organization_code = l_formula.owner_organization_code;
      
         SELECT user_id
           INTO v_user_id
           FROM fnd_user
          WHERE user_name = l_formula.owner_name;
      EXCEPTION
         WHEN others THEN
            UPDATE mii_gmd_formula
             set flag = ‘E’
             –, note = ‘Ada exception’
            WHERE formula_no = l_formula.formula_no;
      END;
      l_formula_header_tbl (c_master%ROWCOUNT).record_type := ‘I’;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_no := UPPER(l_formula.formula_no);
      l_formula_header_tbl (c_master%ROWCOUNT).formula_vers := l_formula.formula_vers;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_type := l_formula.formula_type;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_desc1 := l_formula.formula_desc1;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_class := l_formula.formula_class;
      l_formula_header_tbl (c_master%ROWCOUNT).inactive_ind := l_formula.inactive_ind;
      l_formula_header_tbl (c_master%ROWCOUNT).owner_organization_id := v_organization_id;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_status := l_formula.formula_status;
      l_formula_header_tbl (c_master%ROWCOUNT).owner_id := v_user_id;
      l_formula_header_tbl (c_master%ROWCOUNT).line_type := l_formula.line_type;
      l_formula_header_tbl (c_master%ROWCOUNT).line_no := l_formula.line_no;
      l_formula_header_tbl (c_master%ROWCOUNT).inventory_item_id := v_item_id;
      l_formula_header_tbl (c_master%ROWCOUNT).qty := l_formula.qty;
      l_formula_header_tbl (c_master%ROWCOUNT).detail_uom := l_formula.detail_uom;
      l_formula_header_tbl (c_master%ROWCOUNT).release_type := l_formula.release_type;
      l_formula_header_tbl (c_master%ROWCOUNT).scale_type_hdr := l_formula.scale_type_hdr;
      l_formula_header_tbl (c_master%ROWCOUNT).scale_type_dtl := l_formula.scale_type_dtl;
      l_formula_header_tbl (c_master%ROWCOUNT).cost_alloc := l_formula.cost_alloc;
      l_formula_header_tbl (c_master%ROWCOUNT).CONTRIBUTE_YIELD_IND := l_formula.CONTRIBUTE_YIELD_IND;
      l_formula_header_tbl (c_master%ROWCOUNT).PHANTOM_TYPE := l_formula.PHANTOM_TYPE;
      l_formula_header_tbl (c_master%ROWCOUNT).delete_mark := l_formula.delete_mark;
      l_formula_header_tbl (c_master%ROWCOUNT).CONTRIBUTE_STEP_QTY_IND := ‘N’;
     
     
      DBMS_OUTPUT.put_line (‘Value Test ‘ || l_formula_header_tbl (1).formula_no);
     
—      UPDATE mii_gmd_formula
—         set flag = ‘Y’
—       WHERE formula_no = l_formula.formula_no
—         AND line_no = l_formula.line_no
—         AND inventory_item_code = l_formula.inventory_item_code;
        
   END LOOP;
  
   CLOSE c_master;
  
   gmd_formula_pub.insert_formula
                                (p_api_version             => 1.0,
                                 p_formula_header_tbl      => l_formula_header_tbl,
                                 x_return_status           => l_return_status,
                                 x_msg_count               => l_count,
                                 x_msg_data                => l_data
                                );
                                DBMS_OUTPUT.put_line (‘l_return_status:’||l_return_status);
                                DBMS_OUTPUT.put_line (‘l_data:’||l_data);
                               
   –IF l_return_status = ‘E’ OR l_return_status = ‘U’
   —      THEN
            UPDATE mii_gmd_formula
             set flag = l_return_status
             –, note = l_data
       WHERE formula_no = l_formula.formula_no;
—   ELSE 
—     UPDATE mii_gmd_formula
—             set flag = ‘Y’
—             , note = l_data
—       WHERE formula_no = l_formula.formula_no;
—   END IF;                            
                                   
END LOOP;
/*
   IF l_count >= 0
   THEN
      l_loop_cnt := 1;


      LOOP
         fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                          p_data               => l_data,
                          p_encoded            => fnd_api.g_false,
                          p_msg_index_out      => l_dummy_cnt
                         );
         DBMS_OUTPUT.put_line (‘Record = ‘ || l_loop_cnt);
         DBMS_OUTPUT.put_line (l_data);


         IF l_status = ‘E’ OR l_status = ‘U’
         THEN
            l_data := CONCAT (‘ERROR ‘, l_data);
         END IF;


         DBMS_OUTPUT.put_line (l_data);


         IF (l_status = ‘U’)
         THEN
            l_return_status := l_status;
         ELSIF (l_status = ‘E’ AND l_return_status <> ‘U’)
         THEN
            l_return_status := l_status;
         ELSE
            l_return_status := l_status;
         END IF;


         l_loop_cnt := l_loop_cnt + 1;


         IF l_loop_cnt > l_count
         THEN
            EXIT;
         END IF;
      END LOOP;
   END IF;*/
   COMMIT;


END;


/by