, , , , , ,

OPM Recipe Header Uploading through Script using API

Recipe Header Uploading can be done using the following steps:

1. First Create a staging table say “MJIL_RCP_HDR_UPL_TBL” as shown below.

CREATE TABLE MJIL_RCP_HDR_UPL_TBL
(
   RECIPE_ID                  NUMBER (15),
   RECIPE_DESCRIPTION         VARCHAR2 (70 BYTE),
   RECIPE_NO                  VARCHAR2 (32 BYTE),
   RECIPE_VERSION             NUMBER (5),
   USER_ID                    NUMBER (15),
   USER_NAME                  VARCHAR2 (70 BYTE),
   OWNER_ORGN_CODE            VARCHAR2 (4 BYTE),
   CREATION_ORGN_CODE         VARCHAR2 (4 BYTE),
   OWNER_ORGANIZATION_ID      NUMBER,
   CREATION_ORGANIZATION_ID   NUMBER,
   FORMULA_ID                 NUMBER (15),
   FORMULA_NO                 VARCHAR2 (32 BYTE),
   FORMULA_VERS               NUMBER,
   ROUTING_ID                 NUMBER,
   ROUTING_NO                 VARCHAR2 (32 BYTE),
   ROUTING_VERS               NUMBER (5),
   PROJECT_ID                 NUMBER (15),
   RECIPE_STATUS              VARCHAR2 (30 BYTE),
   PLANNED_PROCESS_LOSS       NUMBER,
   TEXT_CODE                  NUMBER (10),
   DELETE_MARK                NUMBER (5),
   CONTIGUOUS_IND             NUMBER,
   ENHANCED_PI_IND            VARCHAR2 (1 BYTE),
   RECIPE_TYPE                NUMBER,
   CREATION_DATE              DATE,
   CREATED_BY                 NUMBER (15),
   LAST_UPDATED_BY            NUMBER (15),
   LAST_UPDATE_DATE           DATE,
   LAST_UPDATE_LOGIN          NUMBER (15),
   OWNER_ID                   NUMBER (15),
   OWNER_LAB_TYPE             VARCHAR2 (4 BYTE),
   CALCULATE_STEP_QUANTITY    NUMBER (5),
   FIXED_PROCESS_LOSS         NUMBER,
   FIXED_PROCESS_LOSS_UOM     VARCHAR2 (3 BYTE),
   ATTRIBUTE_CATEGORY         VARCHAR2 (30 BYTE),
   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)
);

2. Next create a procedure using the script as shown below.

CREATE OR REPLACE PROCEDURE CONA_RECIPE_UPLOAD_PD (ERRBUF    OUT VARCHAR2,
                                                   RETCODE   OUT NUMBER)
IS
   /******************************************************************************
   NAME: APPS.CONA_RECIPE_UPLOAD_PD
   PURPOSE: Recipe Header 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: APPS.CONA_RECIPE_UPLOAD_PD
   ******************************************************************************/

   mjil_rcp_hdr_tbl          gmd_recipe_header.recipe_tbl;
   mjil_rcp_hdr_flex_tbl     gmd_recipe_header.recipe_flex;
   X_status                  VARCHAR2 (1);
   X_msg_cnt                 NUMBER;
   X_msg_dat                 VARCHAR2 (1000);
   X_row                     NUMBER := 1;
   l_user_id                 NUMBER := 1114;
   l_responsibility_id       NUMBER := 22883;
   l_out_index               NUMBER;
   l_responsibility_app_id   NUMBER;

   CURSOR c1
   IS
      SELECT * FROM mjil_rcp_hdr_upl_tbl;
BEGIN
   FND_GLOBAL.
    APPS_INITIALIZE (l_user_id, l_responsibility_id, l_responsibility_app_id);

   FOR i IN c1
   LOOP
      mjil_rcp_hdr_tbl (X_row).recipe_no := i.recipe_no;
      mjil_rcp_hdr_tbl (X_row).recipe_version := i.recipe_version;
      mjil_rcp_hdr_tbl (X_row).recipe_description := i.recipe_description;
      mjil_rcp_hdr_tbl (X_row).RECIPE_STATUS := i.RECIPE_STATUS;
      mjil_rcp_hdr_tbl (X_row).RECIPE_TYPE := i.RECIPE_TYPE;
      mjil_rcp_hdr_tbl (X_row).formula_no := i.formula_no;
      mjil_rcp_hdr_tbl (X_row).formula_vers := i.formula_vers;
      mjil_rcp_hdr_tbl (X_row).routing_no := i.routing_no;
      mjil_rcp_hdr_tbl (X_row).routing_vers := i.routing_vers;

      mjil_rcp_hdr_tbl (X_row).delete_mark := i.delete_mark;
      mjil_rcp_hdr_tbl (X_row).creation_date := SYSDATE;
      mjil_rcp_hdr_tbl (X_row).created_by := i.created_by;
      mjil_rcp_hdr_tbl (X_row).last_updated_by := i.last_updated_by;
      mjil_rcp_hdr_tbl (X_row).last_update_date := SYSDATE;
      mjil_rcp_hdr_tbl (X_row).last_update_login := 1114;

      mjil_rcp_hdr_tbl (X_row).user_name := i.user_name;
      mjil_rcp_hdr_tbl (X_row).owner_orgn_code := i.owner_orgn_code;
      mjil_rcp_hdr_tbl (X_row).OWNER_ORGANIZATION_ID :=
         i.owner_organization_id;
      mjil_rcp_hdr_tbl (X_row).creation_orgn_code := i.creation_orgn_code;
      mjil_rcp_hdr_tbl (X_row).owner_id := i.owner_id;

      mjil_rcp_hdr_flex_tbl (X_row).attribute1 := ‘FLEX1’;

      X_row := X_row + 1;
   END LOOP;

   gmd_recipe_header.
    create_recipe_header (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        => X_status,
                          x_msg_count            => X_msg_cnt,
                          x_msg_data             => X_msg_dat,
                          p_recipe_header_tbl    => mjil_rcp_hdr_tbl,
                          p_recipe_header_flex   => mjil_rcp_hdr_flex_tbl);

   DBMS_OUTPUT.put_line (‘Return status – ‘ || X_status);
   DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);

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

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

3. Finally register the procedure and run it.

/by
, , , ,

OPM – GMD Insert Formula Ingredient in R12

Module GMD R12.GMD.A.6 (UNP Product Development; Process Manufacturing Product Development)
The procedure for adding an ingredient in the formula

PROCEDURE insert_formuladetail(
    p_formula_id    NUMBER ,
    p_ingredient_id NUMBER ,
    p_new_qty       NUMBER ,
    p_uom           VARCHAR2 DEFAULT NULL,
    p_return_status OUT VARCHAR2 ,
    p_message OUT VARCHAR2 )
IS
  l_api_version       CONSTANT NUMBER := 1.0;
  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_INSERT_DTL_TBL_TYPE;
  — l_formula_head fm_form_mst_b%rowtype;
  L_LINE_NO NUMBER ;
  — 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 := ‘ Could not managed to find a formula ‘;
    raise no_proc_finish;
  END ;
  BEGIN
    SELECT MAX (fm.line_no)
    INTO l_line_no
    FROM fm_matl_dtl fm
    WHERE fm.FORMULA_ID = p_formula_id
    AND fm.line_type    = -1;
  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).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).line_type         :=           -1;
  l_formula_detail_tbl(i).line_no           := l_line_no + 1;
  l_formula_detail_tbl(i).inventory_item_id := p_ingredient_id;
  l_formula_detail_tbl(i).QTY               := p_new_qty;
  L_FORMULA_DETAIL_TBL(I).RELEASE_TYPE      := 1; — manually
  IF (p_uom                                 IS NOT NULL) THEN
    l_formula_detail_tbl(i).DETAIL_UOM      := p_uom;
  END IF ;
  gmd_formula_detail_pub.insert_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 ;

/by