API Archives - Page 3 of 20 - Oracle ERP Apps Guide

API, Apps Technical, ERP, OPM, Oracle Apps, Oracle Manufacturing, PL SQL

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.

August 17, 2013/by Oracle ERP Apps Guide

API, Apps Technical, OPM, Oracle Apps, Oracle Manufacturing

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 ;

August 17, 2013/by Oracle ERP Apps Guide

API, Apps Technical, OPM, Oracle Apps, Oracle Manufacturing, Scripts

OPM Formula Uploading through Script using API

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.

August 16, 2013/by Oracle ERP Apps Guide

API, Apps Technical, OPM, Oracle Apps, Oracle Manufacturing, Scripts, Toad

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 ;

August 16, 2013/by Oracle ERP Apps Guide

API, Apps Technical, ERP, INV, Oracle Apps, Scripts, Toad

API to create Item Category

The item categories can be created using item import when Items are created. They can also be created using APIs discussed below:

Following are the steps to Check and/or Create Item Categories
1) Create Category Segment combinations
2) If the enforce list is checked for category then insert category combinations in the enforce list
3) Create/Update/Delete Category Assignments on Item

In Detail

Create Category Segment combinations
Following APIs can be used to create/update/delete category combinations.

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
  X_CATEGORY_ID  OUT    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER
)

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.

INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_CATEGORY_ID  IN    NUMBER,
  P_PARENT_CATEGORY_ID  IN    NUMBER,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2
)

Following APIs can be used to create/update/delete Item category assignments.

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_DESCRIPTION  IN    VARCHAR2
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)

August 1, 2012/by Oracle ERP Apps Guide

OPM Recipe Header Uploading through Script using API

OPM – GMD Insert Formula Ingredient in R12

OPM Formula Uploading through Script using API

OPM – GMD – Delete Row from the formula in R12

API to create Item Category

Recent Comments

Latest Posts

Ads Blocker Detected!!!

Share this:

Share this:

Share this:

Share this:

Share this:

Recent Comments

Latest Posts

Ads Blocker Detected!!!