Post date: Nov 10, 2010 3:27:48 PM
/*-----------------------------------------------------------------------------
Created By : Iman
Purpose : API for uploading recipe header
Note :
For uploading recipe, run API in the following order
- API Recipe Header
- API Recipe Validity Rule
- API Recipe Material
*/-----------------------------------------------------------------------------
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;