Post date: Nov 09, 2010 3:20:2 PM
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;