Post date: Nov 10, 2010 3:21:18 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 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;