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

NOTES:

Automatically available Auto Replace Keywords:
Object Name: CONA_FML_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_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.