OPM FORMULA UPLOAD PROCESS API-ORACLE APPS R12-US

 

 

CREATE TABLE XXCUST.XXJLN_FORMULA_HEADERS_RRRRRR

(

  FORMULA_NO               VARCHAR2(32 BYTE)    NOT NULL,

  FORMULA_VERS             NUMBER               DEFAULT 1                     NOT NULL,

  FORMULA_TYPE             NUMBER(5)            DEFAULT 1                     NOT NULL,

  FORMULA_DESC1            VARCHAR2(70 BYTE),

  FORMULA_CLASS            VARCHAR2(8 BYTE),

  INACTIVE_IND             NUMBER(5)            DEFAULT 0                     NOT NULL,

  FORMULA_UOM              VARCHAR2(4 BYTE),

  FORMULA_STATUS           VARCHAR2(30 BYTE)    DEFAULT 100                   NOT NULL,

  FLAG                     VARCHAR2(2 BYTE),

  RECORD_TYPE              VARCHAR2(12 BYTE),

  OWNER_ORGANIZATION_CODE  VARCHAR2(10 BYTE)    DEFAULT 'FMO'                 NOT NULL,

  OWNER_NAME               VARCHAR2(20 BYTE)    DEFAULT '34220'               NOT NULL,

  LINE_TYPE                NUMBER(10),

  LINE_NO                  INTEGER              DEFAULT 1                     NOT NULL,

  QTY                      NUMBER(20,10),

  RELEASE_TYPE             VARCHAR2(10 BYTE),

  COST_ALLOC               VARCHAR2(10 BYTE),

  SCALE_TYPE_HDR           NUMBER(10)           DEFAULT 1                     NOT NULL,

  DETAIL_UOM               VARCHAR2(10 BYTE),

  SCALE_TYPE_DTL           INTEGER              DEFAULT 1                     NOT NULL,

  PHANTOM_TYPE             INTEGER              DEFAULT 0                     NOT NULL,

  DELETE_MARK              VARCHAR2(10 BYTE)    DEFAULT 0                     NOT NULL,

  CONTRIBUTE_YIELD_IND     VARCHAR2(10 BYTE)    DEFAULT 'Y'                   NOT NULL,

  INVENTORY_ITEM_CODE      VARCHAR2(15 BYTE),

  TYPE_OF_ITEM             VARCHAR2(250 BYTE),

  FILE_NAME                VARCHAR2(250 BYTE),

  ORG_CODE                 VARCHAR2(200 BYTE),

  ORG_NAME                 VARCHAR2(300 BYTE),

  NOTE                     VARCHAR2(250 BYTE)

)

 

--------------------------------PROCESS-------------------------------------

 

DECLARE

begin

 

/*

----------First time for Item Assignmet-------------

 

 

delete from APPS.xxpran_subinv_locator_stg_rrr

where USER_NAME='333';

commit;

 

 

INSERT INTO  APPS.xxpran_subinv_locator_stg(ITEM_NUMBER,ORG_CODE,SUBINV_CODE,USER_NAME)

SELECT distinct INVENTORY_ITEM_CODE,'FMO','FMO','34220'

FROM XXCUST.XXJLN_FORMULA_HEADERS_REZA

where FLAG is null;

commit;

 

 

 

delete

FROM APPS.xxpran_subinv_locator_stg WHERE ITEM_NUMBER NOT IN (

SELECT BB.SEGMENT1 FROM MTL_SYSTEM_ITEMS_B BB WHERE BB.ORGANIZATION_ID=102 AND BB.SEGMENT1 IN (

SELECT ITEM_NUMBER FROM APPS.xxpran_subinv_locator_stg

));

commit;

 

 

apps.xxpran_inv_assign('xxxxx');

 

*/

 

----------After run one time then off this------------------

 

 

DECLARE

   CURSOR c_header IS

  

      SELECT DISTINCT formula_no

      FROM     xxcust.XXJLN_FORMULA_HEADERS_REZA a

       WHERE flag IS  NULL

       and  not exists (select 1 from  fm_form_mst b where a.FORMULA_NO = b.formula_no);

     

   CURSOR c_master  (

      p_formula     varchar2   --TEST_M020

   ) IS

      SELECT *

        FROM     xxcust.XXJLN_FORMULA_HEADERS_REZA --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             xxcust.XXJLN_FORMULA_HEADERS_REZA%ROWTYPE; -- 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;

   v_total_form                      number;

BEGIN

--FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);

   fnd_global.apps_initialize (user_id           => 1110,

                               resp_id           => 50774,

                               resp_appl_id      => 555

                              );

                             

                            --  FND_RESPONSIBILITY_VL ,  FND_user

                           

                             

/*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 xxcust.XXJLN_FORMULA_HEADERS_REZA --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 := 'Y'; -- 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 := 'Y';

    

    

      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 xxcust.XXJLN_FORMULA_HEADERS_REZA--mii_gmd_formula

             set flag = l_return_status

             --, note = l_data

       WHERE formula_no = l_formula.formula_no;

  ELSE

     UPDATE xxcust.XXJLN_FORMULA_HEADERS_REZA--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;

 

 

update XXCUST.XXJLN_FORMULA_HEADERS_RRRR

set FLAG =null

where FLAG = 'E';

commit;

 

---select count(1) into v_total_form  from XXCUST.XXJLN_FORMULA_HEADERS_REZA a where (a.flag is null or  a.flag !='Y');

 

END;

END;

/

/

/

/

/

/

/

/

/

/

OPM FORMULA UPLOADING THROUGH SCRIPT USING API-ORACLE APPS R12

 

Formula Upload can be done using the following steps:

1. Create a staging table say “MJIL_FORMULA_UPLOAD” whose structure is given below.

CREATE TABLE MJIL_FORMULA_UPLOAD
(
   RECORD_TYPE               VARCHAR2 (1 BYTE),
   FORMULA_NO                VARCHAR2 (32 BYTE),
   FORMULA_VERS              NUMBER,
   FORMULA_TYPE              NUMBER,
   FORMULA_DESC1             VARCHAR2 (70 BYTE),
   FORMULA_DESC2             VARCHAR2 (70 BYTE),
   FORMULA_CLASS             VARCHAR2 (32 BYTE),
   FMCONTROL_CLASS           VARCHAR2 (32 BYTE),
   INACTIVE_IND              NUMBER,
   OWNER_ORGANIZATION_ID     NUMBER,
   TOTAL_INPUT_QTY           NUMBER,
   TOTAL_OUTPUT_QTY          NUMBER,
   YIELD_UOM                 VARCHAR2 (3 BYTE),
   FORMULA_STATUS            VARCHAR2 (30 BYTE),
   OWNER_ID                  NUMBER (15),
   FORMULA_ID                NUMBER,
   FORMULALINE_ID            NUMBER,
   LINE_TYPE                 NUMBER,
   LINE_NO                   NUMBER,
   ITEM_NO                   VARCHAR2 (2000 BYTE),
   INVENTORY_ITEM_ID         NUMBER,
   REVISION                  VARCHAR2 (3 BYTE),
   QTY                       NUMBER,
   DETAIL_UOM                VARCHAR2 (3 BYTE),
   MASTER_FORMULA_ID         NUMBER,
   RELEASE_TYPE              NUMBER,
   SCRAP_FACTOR              NUMBER,
   SCALE_TYPE_HDR            NUMBER,
   SCALE_TYPE_DTL            NUMBER,
   COST_ALLOC                NUMBER,
   PHANTOM_TYPE              NUMBER,
   REWORK_TYPE               NUMBER,
   BUFFER_IND                NUMBER,
   BY_PRODUCT_TYPE           VARCHAR2 (1 BYTE),
   INGREDIENT_END_DATE       DATE,
   ATTRIBUTE1                VARCHAR2 (240 BYTE),
   ATTRIBUTE2                VARCHAR2 (240 BYTE),
   ATTRIBUTE3                VARCHAR2 (240 BYTE),
   ATTRIBUTE4                VARCHAR2 (240 BYTE),
   ATTRIBUTE5                VARCHAR2 (240 BYTE),
   ATTRIBUTE6                VARCHAR2 (240 BYTE),
   ATTRIBUTE7                VARCHAR2 (240 BYTE),
   ATTRIBUTE8                VARCHAR2 (240 BYTE),
   ATTRIBUTE9                VARCHAR2 (240 BYTE),
   ATTRIBUTE10               VARCHAR2 (240 BYTE),
   ATTRIBUTE11               VARCHAR2 (240 BYTE),
   ATTRIBUTE12               VARCHAR2 (240 BYTE),
   ATTRIBUTE13               VARCHAR2 (240 BYTE),
   ATTRIBUTE14               VARCHAR2 (240 BYTE),
   ATTRIBUTE15               VARCHAR2 (240 BYTE),
   ATTRIBUTE16               VARCHAR2 (240 BYTE),
   ATTRIBUTE17               VARCHAR2 (240 BYTE),
   ATTRIBUTE18               VARCHAR2 (240 BYTE),
   ATTRIBUTE19               VARCHAR2 (240 BYTE),
   ATTRIBUTE20               VARCHAR2 (240 BYTE),
   ATTRIBUTE21               VARCHAR2 (240 BYTE),
   ATTRIBUTE22               VARCHAR2 (240 BYTE),
   ATTRIBUTE23               VARCHAR2 (240 BYTE),
   ATTRIBUTE24               VARCHAR2 (240 BYTE),
   ATTRIBUTE25               VARCHAR2 (240 BYTE),
   ATTRIBUTE26               VARCHAR2 (240 BYTE),
   ATTRIBUTE27               VARCHAR2 (240 BYTE),
   ATTRIBUTE28               VARCHAR2 (240 BYTE),
   ATTRIBUTE29               VARCHAR2 (240 BYTE),
   ATTRIBUTE30               VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE1            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE2            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE3            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE4            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE5            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE6            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE7            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE8            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE9            VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE10           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE11           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE12           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE13           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE14           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE15           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE16           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE17           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE18           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE19           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE20           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE21           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE22           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE23           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE24           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE25           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE26           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE27           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE28           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE29           VARCHAR2 (240 BYTE),
   DTL_ATTRIBUTE30           VARCHAR2 (240 BYTE),
   ATTRIBUTE_CATEGORY        VARCHAR2 (30 BYTE),
   DTL_ATTRIBUTE_CATEGORY    VARCHAR2 (30 BYTE),
   TPFORMULA_ID              NUMBER,
   IAFORMULA_ID              NUMBER,
   SCALE_MULTIPLE            NUMBER,
   CONTRIBUTE_YIELD_IND      VARCHAR2 (1 BYTE),
   SCALE_UOM                 VARCHAR2 (4 BYTE),
   CONTRIBUTE_STEP_QTY_IND   VARCHAR2 (1 BYTE),
   SCALE_ROUNDING_VARIANCE   NUMBER,
   ROUNDING_DIRECTION        NUMBER,
   TEXT_CODE_HDR             NUMBER,
   TEXT_CODE_DTL             NUMBER,
   USER_ID                   NUMBER,
   CREATION_DATE             DATE,
   CREATED_BY                NUMBER (15),
   LAST_UPDATED_BY           NUMBER (15),
   LAST_UPDATE_DATE          DATE,
   LAST_UPDATE_LOGIN         NUMBER (15),
   USER_NAME                 VARCHAR2 (100 BYTE),
   DELETE_MARK               NUMBER DEFAULT 0,
   AUTO_PRODUCT_CALC         VARCHAR2 (1 BYTE),
   PROD_PERCENT              NUMBER
);

2. Next create a procedure similar to the one given below.

CREATE OR REPLACE PROCEDURE APPS.CONA_FML_UPLOAD_PD (ERRBUF    OUT VARCHAR2,
                                                     RETCODE   OUT NUMBER)
IS
   /******************************************************************************
   NAME: CONA_FML_UPLOAD_PD
   PURPOSE: Formula Uploading
   REVISIONS:
   Ver Date Author Description
   ——— ———- ————— ————————————
   1.0 8/16/2013 1. Oracle User Created this procedure.
   NOTES:
   Automatically available Auto Replace Keywords:
   Object Name: CONA_FML_UPLOAD_PD
   ******************************************************************************/
   mjil_fml_tabtype          apps.gmd_formula_pub.formula_insert_hdr_tbl_type;

   CURSOR c1
   IS
      SELECT *
        FROM MJIL_FORMULA_UPLOAD
       WHERE formula_no NOT IN (SELECT formula_no FROM fm_form_mst);

   cnt                       NUMBER;
   l_return_status           VARCHAR2 (1);
   l_msg_count               NUMBER;
   l_msg_data                VARCHAR2 (1000);
   l_out_index               NUMBER := 0;
   l_user_id                 NUMBER := 1114;
   l_responsibility_id       NUMBER := 22883;
   l_responsibility_app_id   NUMBER;
BEGIN
   FND_GLOBAL.
    APPS_INITIALIZE (l_user_id, l_responsibility_id, l_responsibility_app_id);
   cnt := 0;

   FOR i IN c1
   LOOP
      cnt := cnt + 1;
      mjil_fml_tabtype (cnt).record_type := i.record_type;
      mjil_fml_tabtype (cnt).FORMULA_NO := TRIM (i.FORMULA_NO);
      mjil_fml_tabtype (cnt).FORMULA_VERS := i.formula_vers;
      mjil_fml_tabtype (cnt).formula_type := i.formula_type;
      mjil_fml_tabtype (cnt).formula_desc1 := i.formula_desc1;
      mjil_fml_tabtype (cnt).formula_desc2 := i.formula_desc2;
      mjil_fml_tabtype (cnt).inactive_ind := i.inactive_ind;
      mjil_fml_tabtype (cnt).OWNER_ORGANIZATION_ID := i.owner_organization_id;
      mjil_fml_tabtype (cnt).total_input_qty := i.total_input_qty;
      mjil_fml_tabtype (cnt).total_output_qty := i.total_output_qty;
      mjil_fml_tabtype (cnt).formula_status := i.formula_status;
      mjil_fml_tabtype (cnt).line_no := i.line_no;
      mjil_fml_tabtype (cnt).line_type := i.line_type;
      mjil_fml_tabtype (cnt).item_no := i.item_no;
      mjil_fml_tabtype (cnt).qty := i.qty;
      mjil_fml_tabtype (cnt).detail_uom := i.detail_uom;
      mjil_fml_tabtype (cnt).release_type := i.release_Type;
      mjil_fml_tabtype (cnt).scrap_factor := i.scrap_factor;
      mjil_fml_tabtype (cnt).scale_type_hdr := i.scale_type_hdr;
      mjil_fml_tabtype (cnt).scale_type_dtl := i.scale_type_dtl;
      mjil_fml_tabtype (cnt).cost_alloc := i.cost_alloc;
      mjil_fml_tabtype (cnt).phantom_type := i.phantom_type;
      mjil_fml_tabtype (cnt).rework_type := i.rework_type;
      mjil_fml_tabtype (cnt).buffer_ind := i.buffer_ind;
      mjil_fml_tabtype (cnt).contribute_yield_ind := i.contribute_yield_ind;
      mjil_fml_tabtype (cnt).contribute_step_qty_ind :=
         i.contribute_step_qty_ind;
      mjil_fml_tabtype (cnt).delete_mark := i.delete_mark;
   END LOOP;

   GMD_FORMULA_PUB.Insert_Formula (p_api_version          => 1,
                                   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_msg_count,
                                   x_msg_data             => l_msg_data,
                                   p_formula_header_tbl   => mjil_fml_tabtype);
   DBMS_OUTPUT.put_line (‘Return status – ‘ || l_return_status);
   DBMS_OUTPUT.put_line (‘Message count – ‘ || l_msg_count);

   FOR i IN 1 .. l_msg_count
   LOOP
      FND_MSG_PUB.get (p_msg_index       => i,
                       p_encoded         => ‘F’,
                       p_data            => l_msg_data,
                       P_MSG_INDEX_OUT   => l_out_index);
      DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || l_msg_data);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (‘Return status – ‘ || l_return_status);
      DBMS_OUTPUT.put_line (‘Message count – ‘ || l_msg_count);

      FOR i IN 1 .. l_msg_count
      LOOP
         FND_MSG_PUB.get (p_msg_index       => i,
                          p_encoded         => ‘F’,
                          p_data            => l_msg_data,
                          P_MSG_INDEX_OUT   => l_out_index);
         DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || l_msg_data);
      END LOOP;
END;

3. Register and run the procedure in Oracle Apps.

 

OPM – GMD – DELETE ROW FROM THE FORMULA IN R12

Module GMD R12.GMD.A.6 (UNP Product Development; Process Manufacturing Product Development)

The procedure to delete the row from the formula

PROCEDURE delete_formuladetail(
    p_formula_id     NUMBER ,
    p_formulaline_id NUMBER ,
    p_return_status OUT VARCHAR2 ,
    p_message OUT VARCHAR2 )
IS
  l_api_version       CONSTANT NUMBER := 1.1;
  l_init_msg_list     VARCHAR2(1)     := FND_API.G_TRUE ;
  l_commit            VARCHAR2(1)     := FND_API.G_FALSE;
  l_called_from_forms VARCHAR2(10)    := ‘NO’;
  l_return_status     VARCHAR2(1) ;
  l_msg_count         NUMBER ;
  l_msg_data          VARCHAR2(400) ;
  L_FORMULA_DETAIL_TBL GMD_FORMULA_DETAIL_PUB.FORMULA_UPDATE_DTL_TBL_TYPE;
  — l_formula_head fm_form_mst_b%rowtype;
  L_FORMULA_LINE FM_MATL_DTL%ROWTYPE;
  — i pls_integer := 1;
  NO_PROC_FINISH EXCEPTION ;
BEGIN
  IF (P_FORMULA_ID IS NULL) THEN
    P_MESSAGE      := ‘ P_formula_id parameter can not be empty ‘;
    RAISE NO_PROC_FINISH;
  END IF ;
  — получаем данные заголовка
  BEGIN
    SELECT *
    INTO l_formula_head
    FROM fm_form_mst_b fm
    WHERE fm.formula_id = p_formula_id;
  EXCEPTION
  WHEN OTHERS THEN
    p_message := ‘ Unable to find a formula ‘;
    raise no_proc_finish;
  END ;
  — get the data string formula
  BEGIN
    SELECT *
    INTO l_formula_line
    FROM fm_matl_dtl fm
    WHERE fm.formulaline_id = p_formulaline_id;
  EXCEPTION
  WHEN OTHERS THEN
    p_message := ‘ Could not find a string formula ‘;
    raise no_proc_finish;
  END ;
  gme_common_pvt.set_who;
  l_formula_detail_tbl(i).RECORD_TYPE    := ‘D’;
  l_formula_detail_tbl(i).formula_id     := p_formula_id;
  l_formula_detail_tbl(i).formula_no     := l_formula_head.formula_no;
  l_formula_detail_tbl(i).formula_vers   := l_formula_head.formula_vers;
  l_formula_detail_tbl(i).formulaline_id := p_formulaline_id;
  GMD_FORMULA_DETAIL_PUB.DELETE_FORMULADETAIL( P_API_VERSION => L_API_VERSION, P_INIT_MSG_LIST => L_INIT_MSG_LIST, P_COMMIT => L_COMMIT, P_CALLED_FROM_FORMS => L_CALLED_FROM_FORMS, X_RETURN_STATUS => L_RETURN_STATUS, X_MSG_COUNT => L_MSG_COUNT, X_MSG_DATA => L_MSG_DATA, P_FORMULA_DETAIL_TBL => L_FORMULA_DETAIL_TBL );
  IF l_return_status != FND_API.g_ret_sts_success THEN
    IF l_msg_count = 1 THEN
      p_message   := FND_MSG_PUB.get(1,’F’);
      RAISE no_proc_finish;
    ELSE
      FOR l IN 1..l_msg_count
      LOOP
        FND_MSG_PUB.get (p_msg_index => l ,p_encoded => ‘F’ ,p_data => p_message ,p_msg_index_out => l_msg_count);
      END LOOP ;
      RAISE no_proc_finish;
    END IF ;
  END IF ;
  p_return_status := ‘S’;
EXCEPTION
WHEN no_proc_finish THEN
  p_return_status := ‘ E ‘;
WHEN OTHERS THEN
  p_message       := SQLERRM ;
  p_return_status := ‘ E ‘;
END ;