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;