APPS OPM(Oracle Process Manufacturing) 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 5/16/2011 1. Puspendu Das Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: APPS.CONA_RECIPE_UPLOAD_PD
Sysdate: 5/16/2011
Date and Time: 5/16/2011, 1:22:42 PM, and 5/16/2011 1:22:42 PM

******************************************************************************/

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.