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;
Recent Comments