DECLARE
CURSOR c_operation IS
SELECT DISTINCT oprn_no
, oprn_desc
, process_qty_uom
, oprn_vers
, delete_mark
, effective_start_date
, operation_status
, owner_organization_id
, activity
, offset_interval
, activity_factor
FROM mii_gmd_operations
WHERE flag IS NULL;
CURSOR c_resource (
p_oprn_no varchar2,
p_activity varchar2
) IS
SELECT resources,
process_qty,
resource_process_uom,
resource_usage,
resource_usage_uom,
cost_cmpntcls_id,
cost_analysis_code,
prim_rsrc_ind,
resource_count,
scale_type,
offset_interval
FROM mii_gmd_operations
WHERE oprn_no = p_oprn_no
AND activity = p_activity
ORDER BY PRIM_RSRC_IND;
l_operations gmd_operations%ROWTYPE;
l_oprn_actv_tbl
gmd_operations_pub.gmd_oprn_activities_tbl_type;
l_oprn_rsrc_tbl
gmd_operation_resources_pub.gmd_oprn_resources_tbl_type;
l_count NUMBER
:= 0;
l_count1 NUMBER
:= 0;
l_loop_cnt NUMBER
:= 0;
l_record_count NUMBER
:= 0;
l_data VARCHAR2 (2000);
l_data1 VARCHAR2 (2000);
l_return_status VARCHAR2 (1);
l_return_status1 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_oprn_line_id number;
v_count number;
v_resources
varchar2(100);
v_process_qty
number;
v_resource_process_uom varchar2(5);
v_resource_usage number;
v_resource_usage_uom varchar2(5);
v_cost_cmpntcls_id number;
v_cost_analysis_code varchar2(5);
v_prim_rsrc_ind
number;
v_resource_count number;
v_scale_type
number;
v_offset_interval number;
BEGIN
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 22882,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
FOR l_oprn IN c_operation
LOOP
DBMS_OUTPUT.put_line (‘Value Test ‘ ||
l_operations.oprn_no);
l_operations.oprn_no := l_oprn.oprn_no;
l_operations.oprn_desc := l_oprn.oprn_desc;
l_operations.process_qty_uom := l_oprn.process_qty_uom;
l_operations.oprn_vers := l_oprn.oprn_vers;
l_operations.delete_mark := l_oprn.delete_mark;
l_operations.effective_start_date :=
l_oprn.effective_start_date;
l_operations.operation_status := l_oprn.operation_status;
l_operations.owner_organization_id :=
l_oprn.owner_organization_id;
l_oprn_actv_tbl (1).activity := l_oprn.activity;
l_oprn_actv_tbl (1).offset_interval :=
l_oprn.offset_interval;
l_oprn_actv_tbl (1).activity_factor :=
l_oprn.activity_factor;
l_oprn_actv_tbl (1).delete_mark := l_oprn.delete_mark;
–l_oprn_actv_tbl (1).activity := l_master.activity;
SELECT COUNT(*)
INTO v_count
FROM mii_gmd_operations
WHERE oprn_no = l_oprn.oprn_no
AND activity = l_oprn.activity
ORDER BY PRIM_RSRC_IND;
OPEN c_resource (l_oprn.oprn_no, l_oprn.activity);
FOR i IN 1 .. v_count
LOOP
FETCH c_resource INTO v_resources,
v_process_qty,
v_resource_process_uom,
v_resource_usage,
v_resource_usage_uom,
v_cost_cmpntcls_id,
v_cost_analysis_code,
v_prim_rsrc_ind,
v_resource_count,
v_scale_type,
v_offset_interval;
l_oprn_rsrc_tbl (i).activity :=
l_oprn.activity;
l_oprn_rsrc_tbl (i).resources := v_resources;
l_oprn_rsrc_tbl (i).process_qty :=
v_process_qty;
l_oprn_rsrc_tbl (i).resource_process_uom :=
v_resource_process_uom;
l_oprn_rsrc_tbl (i).resource_usage :=
v_resource_usage;
l_oprn_rsrc_tbl (i).resource_usage_uom :=
v_resource_usage_uom;
l_oprn_rsrc_tbl (i).cost_cmpntcls_id :=
v_cost_cmpntcls_id;
l_oprn_rsrc_tbl (i).cost_analysis_code :=
v_cost_analysis_code;
l_oprn_rsrc_tbl (i).prim_rsrc_ind :=
v_prim_rsrc_ind;
l_oprn_rsrc_tbl (i).resource_count :=
v_resource_count;
l_oprn_rsrc_tbl (i).scale_type :=
v_scale_type;
l_oprn_rsrc_tbl (i).offset_interval :=
v_offset_interval;
UPDATE mii_gmd_operations
set flag = ‘Y’
WHERE oprn_no = l_oprn.oprn_no
AND resources = v_resources;
END LOOP;
CLOSE c_resource;
gmd_operations_pub.insert_operation (p_api_version
=> 1.0,
p_init_msg_list
=> l_init_msg_list,
p_commit
=> l_commit,
p_operations
=> l_operations,
p_oprn_actv_tbl
=> l_oprn_actv_tbl,
p_oprn_rsrc_tbl
=> l_oprn_rsrc_tbl,
x_message_count
=> l_count,
x_return_status
=> l_return_status,
x_message_list
=> l_data
);
UPDATE gmd_operations_b
set operation_status = 700
WHERE operation_status = 100
AND oprn_no = l_oprn.oprn_no;
IF l_status = ‘E’ OR l_status = ‘U’
THEN
UPDATE mii_gmd_operations
set flag = ‘E’
, note = l_data
WHERE oprn_no = l_oprn.oprn_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;
DBMS_OUTPUT.put_line (‘Sucess’);
END;
/
R12 – OPM ROUTING CREATION USING API
DECLARE
CURSOR c_routing_header IS
SELECT DISTINCT ROUTING_NO,
ROUTING_VERS,
ROUTING_DESC,
EFFECTIVE_START_DATE,
ROUTING_QTY,
ROUTING_UOM,
OWNER_ID,
OWNER_ORGANIZATION_ID,
ROUTING_STATUS,
DELETE_MARK
FROM mii_gmd_routing
WHERE flag IS NULL;
CURSOR c_routing_step (
p_routing_no varchar2
)
IS
SELECT ROUTINGSTEP_NO
, OPRN_NO
, STEP_QTY
, STEPRELEASE_TYPE
, ROUTINGSTEP_NO1
, DEP_ROUTINGSTEP_NO
, DEP_TYPE
, STANDARD_DELAY
, TRANSFER_PCT
, ROUTINGSTEP_NO_UOM
FROM mii_gmd_routing
WHERE flag IS NULL
AND routing_no = p_routing_no
ORDER BY routingstep_no;
l_routings
gmd_routings%ROWTYPE;
l_routings_step_tbl
gmd_routings_pub.gmd_routings_step_tab;
l_routings_step_dep_tbl
gmd_routings_pub.gmd_routings_step_dep_tab;
l_count
NUMBER
:= 0;
l_loop_cnt
NUMBER
:= 0;
l_record_count NUMBER
:= 0;
l_data
VARCHAR2 (4000);
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_routingstep_no number;
v_oprn_id
number;
v_oprn_no
varchar2(100);
v_step_qty
number;
v_steprelease_type number;
v_routingstep_no1 number;
v_dep_routingstep_no number;
v_dep_type
number;
v_standard_delay number;
v_transfer_pct number;
v_routingstep_no_uom
varchar2(100);
v_count
number;
BEGIN
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 22882,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
FOR l_routing_header IN c_routing_header
LOOP
l_routings.routing_no := l_routing_header.routing_no;
l_routings.routing_vers := l_routing_header.routing_vers;
l_routings.routing_desc := l_routing_header.routing_desc;
l_routings.routing_qty := l_routing_header.routing_qty;
–l_routings.item_um := l_routing_header.item_um;
l_routings.routing_uom := l_routing_header.routing_uom;
l_routings.effective_start_date :=
l_routing_header.effective_start_date;
l_routings.owner_id := l_routing_header.owner_id;
l_routings.routing_status :=
l_routing_header.routing_status;
l_routings.delete_mark := l_routing_header.delete_mark;
l_routings.owner_organization_id :=
l_routing_header.owner_organization_id;
SELECT COUNT(*)
INTO v_count
FROM mii_gmd_routing
WHERE flag IS NULL
AND routing_no = l_routing_header.routing_no;
OPEN c_routing_step (l_routing_header.routing_no);
FOR i IN 1 .. v_count
LOOP
FETCH c_routing_step INTO v_routingstep_no,
v_oprn_no,
v_step_qty,
v_steprelease_type,
v_routingstep_no1,
v_dep_routingstep_no,
v_dep_type,
v_standard_delay,
v_transfer_pct,
v_routingstep_no_uom;
SELECT oprn_id
INTO v_oprn_id
FROM GMD_OPERATIONS_VL
WHERE oprn_no = v_oprn_no;
–dbms_output.put_line (‘v_oprn_id: ‘ ||
v_oprn_id);
l_routings_step_tbl (i).routingstep_no :=
v_routingstep_no;
l_routings_step_tbl (i).oprn_id := v_oprn_id;
l_routings_step_tbl (i).step_qty :=
v_step_qty;
l_routings_step_tbl (i).steprelease_type :=
v_steprelease_type;
–dbms_output.put_line (‘l_routings_step_tbl
(‘||i||’).routingstep_no: ‘ || l_routings_step_tbl (i).routingstep_no);
–IF v_routingstep_no1 IS NOT NULL THEN
l_routings_step_dep_tbl
(i).routingstep_no := v_routingstep_no1;
l_routings_step_dep_tbl
(i).dep_routingstep_no := v_dep_routingstep_no;
l_routings_step_dep_tbl (i).dep_type
:= v_dep_type;
l_routings_step_dep_tbl
(i).standard_delay := v_standard_delay;
l_routings_step_dep_tbl
(i).transfer_pct := v_transfer_pct;
l_routings_step_dep_tbl
(i).routingstep_no_uom := v_routingstep_no_uom;
–END IF;
UPDATE mii_gmd_routing
set flag = ‘Y’
WHERE routing_no =
l_routing_header.routing_no
AND oprn_no = v_oprn_no;
END LOOP;
CLOSE c_routing_step;
–DBMS_OUTPUT.put_line (‘Value Test ‘ || l_routings.routing_no);
gmd_routings_pub.insert_routing
(p_api_version
=> 1.0,
p_init_msg_list
=> l_init_msg_list,
p_commit
=> l_commit,
p_routings
=> l_routings,
p_routings_step_tbl =>
l_routings_step_tbl,
p_routings_step_dep_tbl =>
l_routings_step_dep_tbl,
x_message_count
=> l_count,
x_return_status
=> l_return_status,
x_message_list
=> l_data
);
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;
END;
R12 – OPM FORMULA CREATION USING API
API Material:
DECLARE
CURSOR c_master IS
SELECT *
FROM nfi_gmd_recipe
WHERE flag = ‘V’
AND routing_no IS NOT NULL;
CURSOR c_formula (
p_formula_id number
)
IS
SELECT *
FROM FM_MATL_DTL
WHERE formula_id = p_formula_id
ORDER BY line_type, line_no;
l_recipe_flex_tbl
gmd_recipe_detail.recipe_flex;
l_recipe_mtl_tbl gmd_recipe_detail.recipe_mtl_tbl;
l_recipe
nfi_gmd_recipe%ROWTYPE;
l_formula
FM_MATL_DTL%ROWTYPE;
l_count NUMBER
:= 0;
l_count_mtl
NUMBER
:= 0;
l_loop_cnt NUMBER
:=
0;
l_record_count NUMBER
:= 0;
l_data
VARCHAR2 (2000);
l_data_mtl
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_routing_id number;
v_formula_id number;
v_routingstep_id number;
v_formulaline_id number;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 50618,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
i := i;
— IF NOT c_master%ISOPEN THEN
— CLOSE c_master;
— END IF;
OPEN c_master;
LOOP
FETCH c_master INTO l_recipe;
exit WHEN c_master%NOTFOUND;
IF l_recipe.routing_no IS NOT NULL THEN
SELECT routing_id
INTO v_routing_id
FROM fm_rout_hdr
WHERE routing_no = l_recipe.routing_no
AND routing_vers =
l_recipe.routing_vers;
END IF;
SELECT formula_id
INTO v_formula_id
FROM FM_FORM_MST
WHERE formula_no = l_recipe.formula_no
AND formula_vers = l_recipe.formula_vers;
IF l_recipe.routing_no IS NOT NULL THEN
OPEN c_formula(v_formula_id);
LOOP
FETCH c_formula INTO l_formula;
exit WHEN c_formula%NOTFOUND;
IF l_formula.line_type = -1 AND
l_formula.line_no = 1 THEN
SELECT routingstep_id
INTO
v_routingstep_id
FROM fm_rout_dtl
WHERE routing_id = v_routing_id
AND
routingstep_no = 10;
ELSIF l_formula.line_type = -1
AND l_formula.line_no > 1 THEN
SELECT routingstep_id
INTO
v_routingstep_id
FROM fm_rout_dtl
WHERE routing_id =
v_routing_id
AND
routingstep_no = 20;
ELSIF l_formula.line_type = 1
THEN
SELECT routingstep_id
INTO
v_routingstep_id
FROM fm_rout_dtl
WHERE routing_id =
v_routing_id
AND
routingstep_no = 30;
END IF;
l_recipe_mtl_tbl (i).recipe_no :=
l_recipe.recipe_no;
l_recipe_mtl_tbl (i).recipe_version
:= l_recipe.recipe_version;
l_recipe_mtl_tbl (i).formulaline_id
:= l_formula.formulaline_id;
l_recipe_mtl_tbl (i).routingstep_id
:= v_routingstep_id;
DBMS_OUTPUT.put_line(i);
DBMS_OUTPUT.put_line(l_recipe_mtl_tbl
(i).formulaline_id);
DBMS_OUTPUT.put_line(l_recipe_mtl_tbl
(i).recipe_no);
i := i + 1;
–DBMS_OUTPUT.put_line (‘Value Test ‘
|| i || ‘: ‘|| l_recipe_mtl_tbl (i).recipe_no);
END LOOP;
CLOSE c_formula;
END IF;
UPDATE nfi_gmd_recipe
set flag = ‘M’
WHERE recipe_no = l_recipe.recipe_no;
END LOOP;
CLOSE c_master;
gmd_recipe_detail.create_recipe_mtl
(p_api_version
=> 1.0,
–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_count_mtl,
x_msg_data
=> l_data_mtl,
p_recipe_mtl_tbl
=> l_recipe_mtl_tbl,
p_recipe_mtl_flex
=> l_recipe_flex_tbl
);
IF l_count_mtl >= 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index
=> l_loop_cnt,
p_data =>
l_data_mtl,
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_mtl);
IF l_status = ‘E’ OR
l_status = ‘U’
THEN
l_data_mtl := CONCAT (‘ERROR ‘,
l_data_mtl);
END IF;
DBMS_OUTPUT.put_line
(l_data_mtl);
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_mtl
THEN
EXIT;
END IF;
END LOOP;
END IF;
COMMIT;
END;
API Validity Rule:
DECLARE
CURSOR c_master IS
SELECT *
FROM nfi_gmd_recipe
WHERE flag = ‘Y’;
l_recipe_flex_tbl gmd_recipe_detail.recipe_flex;
l_recipe_vr_tbl
gmd_recipe_detail.recipe_vr_tbl;
l_recipe
nfi_gmd_recipe%ROWTYPE;
l_formula
FM_MATL_DTL%ROWTYPE;
l_count NUMBER
:= 0;
l_count_mtl
NUMBER
:= 0;
l_loop_cnt NUMBER
:=
0;
l_record_count NUMBER
:= 0;
l_data
VARCHAR2 (2000);
l_data_mtl
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_routing_id number;
v_formula_id number;
v_routingstep_id number;
v_formulaline_id number;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 50618,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
i := 0;
DBMS_OUTPUT.put_line (‘Open cursor’);
OPEN c_master;
LOOP
FETCH c_master INTO l_recipe;
exit WHEN c_master%NOTFOUND;
l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_no :=
l_recipe.recipe_no;
l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_version :=
l_recipe.recipe_version;
l_recipe_vr_tbl (c_master%ROWCOUNT).organization_id :=
l_recipe.organization_id;
l_recipe_vr_tbl (c_master%ROWCOUNT).start_date :=
l_recipe.start_date;
DBMS_OUTPUT.put_line (‘Value Test ‘ || c_master%ROWCOUNT
|| ‘: ‘|| l_recipe_vr_tbl (c_master%ROWCOUNT).recipe_no);
UPDATE nfi_gmd_recipe
set flag = ‘V’
WHERE recipe_no = l_recipe.recipe_no;
END LOOP;
CLOSE c_master;
DBMS_OUTPUT.put_line (‘Close cursor’);
gmd_recipe_detail.create_recipe_vr
(p_api_version
=> 1.0,
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_count,
x_msg_data
=> l_data,
p_recipe_vr_tbl
=> l_recipe_vr_tbl,
p_recipe_vr_flex
=> l_recipe_flex_tbl
);
UPDATE GMD_RECIPE_VALIDITY_RULES
set VALIDITY_RULE_STATUS = 700
WHERE VALIDITY_RULE_STATUS != 700;
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;
END;
API Recipe Header:
DECLARE
CURSOR c_master IS
SELECT *
FROM nfi_gmd_recipe
WHERE flag IS NULL;
l_recipe_header_tbl gmd_recipe_header.recipe_tbl;
l_recipe_flex_tbl gmd_recipe_header.recipe_flex;
l_recipe nfi_gmd_recipe%ROWTYPE;
l_formula FM_MATL_DTL%ROWTYPE;
l_count NUMBER := 0;
l_count_mtl NUMBER := 0;
l_loop_cnt NUMBER := 0;
l_record_count NUMBER := 0;
l_data VARCHAR2 (2000);
l_data_mtl 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_routing_id number;
v_formula_id number;
v_routingstep_id number;
v_formulaline_id number;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id => 1090,
resp_id => 50618,
resp_appl_id => 552
);
l_init_msg_list := TRUE;
l_commit := TRUE;
DBMS_OUTPUT.put_line (‘Open cursor’);
OPEN c_master;
LOOP
FETCH c_master INTO l_recipe;
exit WHEN c_master%NOTFOUND;
IF l_recipe.routing_no IS NOT NULL THEN
SELECT routing_id
INTO v_routing_id
FROM fm_rout_hdr
WHERE routing_no = l_recipe.routing_no
AND routing_vers = l_recipe.routing_vers;
l_recipe_header_tbl (c_master%ROWCOUNT).routing_id :=
v_routing_id;
END IF;
SELECT formula_id
INTO v_formula_id
FROM FM_FORM_MST
WHERE formula_no = l_recipe.formula_no
AND formula_vers = l_recipe.formula_vers;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_no :=
l_recipe.recipe_no;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_description :=
l_recipe.recipe_description;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_version :=
l_recipe.recipe_version;
l_recipe_header_tbl (c_master%ROWCOUNT).user_id := 1090;
l_recipe_header_tbl (c_master%ROWCOUNT).owner_organization_id :=
l_recipe.owner_organization_id;
l_recipe_header_tbl (c_master%ROWCOUNT).formula_id := v_formula_id;
l_recipe_header_tbl (c_master%ROWCOUNT).recipe_status :=
‘700’;
DBMS_OUTPUT.put_line (‘Value Test ‘ || c_master%ROWCOUNT ||
‘: ‘|| l_recipe_header_tbl (c_master%ROWCOUNT).recipe_no);
UPDATE nfi_gmd_recipe
set flag = ‘Y’
WHERE recipe_no = l_recipe.recipe_no;
END LOOP;
CLOSE c_master;
DBMS_OUTPUT.put_line (‘Close cursor’);
gmd_recipe_header.create_recipe_header
(p_api_version => 1.0,
–p_init_msg_list => FND_API.G_TRUE,
–p_commit => FND_API.G_TRUE,
p_recipe_header_tbl => l_recipe_header_tbl,
p_recipe_header_flex => l_recipe_flex_tbl,
x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_data
);
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;
END;
API Formula:
DECLARE
CURSOR c_header IS
SELECT DISTINCT formula_no
FROM mii_gmd_formula
WHERE flag IS NULL;
CURSOR c_master (
p_formula varchar2
) IS
SELECT *
FROM 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
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;
BEGIN
–FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
fnd_global.apps_initialize (user_id
=> 1090,
resp_id
=> 22882,
resp_appl_id => 552
);
/*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 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 := 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 := ‘N’;
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 mii_gmd_formula
set flag = l_return_status
–, note = l_data
WHERE formula_no = l_formula.formula_no;
— ELSE
— UPDATE 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;
END;
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.
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 ;
