PROCEDURE create_recipe (p_create_recipe IN VARCHAR2)
IS
--local variables
l_recipe_tbl apps.gmd_recipe_header.recipe_tbl;
l_recipe_flex apps.gmd_recipe_header.recipe_flex;
l_recipe_vr_tbl apps.gmd_recipe_detail.recipe_vr_tbl;
l_recipe_flex_tbl apps.gmd_recipe_detail.recipe_flex;
l_recipe_mtl_tbl apps.gmd_recipe_detail.recipe_mtl_tbl;
l_return_status VARCHAR2 (10);
l_msg_ind VARCHAR2 (240);
l_msg_data VARCHAR2 (2000);
l_msg_count NUMBER;
l_count NUMBER;
ln_formula_no apps.fm_form_mst.formula_no%TYPE;
ln_formula_vers apps.fm_form_mst.formula_vers%TYPE;
ln_routing_no apps.fm_rout_hdr.routing_no%TYPE;
ln_routing_vers apps.fm_rout_hdr.routing_vers%TYPE;
ln_progress NUMBER := 0;
ln_suc_rec_cnt NUMBER := 0;
ln_rej_rec_cnt NUMBER := 0;
ln_count NUMBER;
ln_organization_id NUMBER;
ln_formula_id NUMBER;
ln_routing_id NUMBER;
ln_recipe_type NUMBER;
ln_routingstep_id NUMBER;
ln_inventory_item_id NUMBER;
ln_required_qty NUMBER;
ln_recipe_id NUMBER;
ln_recipe_version NUMBER;
ln_formulaline_id NUMBER;
ln_routstep_id NUMBER;
lc_recipe_no VARCHAR2 (15);
lc_uom_code VARCHAR2 (20);
lc_segment1 VARCHAR2 (40);
lc_organization_code VARCHAR2 (10);
--===============================
--Cursor to get new records count
--===============================
CURSOR lcu_count
IS
SELECT COUNT (*)
FROM xxblr_opm_recipe_stg
WHERE 1=1
AND record_status = gc_validation_flag;
--===============================
--Cursor to get Recipe Data
--===============================
CURSOR lcu_recipe_data
IS
SELECT transaction_id
, sr_no
, new_recipe_no recipe_no
, recipe_version
, recipe_description
, new_formula_no formula_no
, formula_version
, new_routing_no routing_no
, routing_version
, owner_org_code
, recipe_validity_date
, recipe_type
, formula_id
, routing_id
, organization_id
, process_flag
, error_code
, record_status
, error_message
, last_updated_by
, created_by
, last_update_date
, creation_date
, last_update_login
FROM xxblr_opm_recipe_stg
WHERE 1=1
GROUP BY transaction_id
, sr_no
, new_recipe_no
, recipe_version
, recipe_description
, new_formula_no
, formula_version
, new_routing_no
, routing_version
, owner_org_code
, recipe_validity_date
, recipe_type
, formula_id
, routing_id
, organization_id
, process_flag
, error_code
, record_status
, error_message
, last_updated_by
, created_by
, last_update_date
, creation_date
, last_update_login;
--===============================
--Cursor to get Formula Line Id
--===============================
CURSOR lcu_recipe_id(p_recipe_no varchar2,p_recipe_version number)
IS
SELECT recipe_id
FROM apps.gmd_recipes_b
WHERE 1=1
AND recipe_no = p_recipe_no
AND recipe_version = p_recipe_version;
--===============================
--Cursor to get Issue to Step
--===============================
-------------
CURSOR lcu_issue_to_step(p_formula_id number,p_routing_id number)
IS
SELECT fmd.formulaline_id
, frd.routingstep_id
FROM xxblr_opm_formula_stg xrof
, apps.fm_form_mst ffm
, apps.fm_matl_dtl fmd
, apps.mtl_system_items_b msi
, apps.fm_rout_dtl frd
WHERE 1=1
AND xrof.new_formula_no = ffm.formula_no
AND xrof.formula_vers = ffm.formula_vers
AND ffm.formula_id = p_formula_id
AND fmd.formula_id = ffm.formula_id
AND ( fmd.inventory_item_id = msi.inventory_item_id
and msi.organization_id = g_master_org
and msi.segment1 = xrof.new_item_no
)
AND fmd.qty = xrof.required_qty
AND frd.routing_id = p_routing_id
AND frd.routingstep_no = xrof.issue_to_step
GROUP BY fmd.formulaline_id, frd.routingstep_id
ORDER BY fmd.formulaline_id,frd.routingstep_id;
---------------
--===============================
--Cursor to get RoutingStep Id
--===============================
CURSOR lcu_routstep_id (p_recipe_no VARCHAR2, p_recipe_vers NUMBER)
IS
SELECT frd.routingstep_id
FROM apps.fm_rout_dtl frd,
apps.gmd_recipes grb
WHERE 1=1
AND frd.routing_id = grb.routing_id
AND grb.recipe_no = p_recipe_no
AND grb.recipe_version = p_recipe_vers;
TYPE tbl_recipe_data IS TABLE OF lcu_recipe_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_per tbl_recipe_data;
BEGIN
apps.fnd_file.put_line(apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Executing CREATE_RECIPE ---------');
apps.fnd_file.put_line(apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line(apps.fnd_file.output, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.output,' --------- Executing CREATE_RECIPE ---------');
apps.fnd_file.put_line(apps.fnd_file.output, RPAD (' ', 80, ' '));
OPEN lcu_count;
FETCH lcu_count
INTO ln_count;
CLOSE lcu_count;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Number of Records Loaded :' || ln_count);
-------------------------------------------------------------------------
-- Bulk Insert Data into Recipe.
--------------------------------------------------------------------------
OPEN lcu_recipe_data;
LOOP
lt_per.DELETE;
gn_bulk_err := 0;
FETCH lcu_recipe_data BULK COLLECT INTO lt_per LIMIT 50000;
IF lt_per.COUNT > 0
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'COUNT-->' || lt_per.COUNT);
FOR i IN lt_per.first .. lt_per.last
LOOP
apps.fnd_file.put_line(apps.fnd_file.LOG,'Recipe No:'||lt_per (i).recipe_no);
apps.fnd_file.put_line(apps.fnd_file.LOG,'Rouitng Id:'||lt_per (i).routing_id);
l_recipe_tbl (1).recipe_id := NULL;
l_recipe_tbl (1).recipe_description := lt_per (i).recipe_description;
l_recipe_tbl (1).recipe_no := lt_per (i).recipe_no;
l_recipe_tbl (1).recipe_version := lt_per (i).recipe_version;
l_recipe_tbl (1).user_id := l_user_id;
l_recipe_tbl (1).user_name := l_user_name;
l_recipe_tbl (1).owner_orgn_code := lt_per (i).owner_org_code;
l_recipe_tbl (1).creation_orgn_code := lt_per (i).owner_org_code;
l_recipe_tbl (1).owner_organization_id := lt_per (i).organization_id;
l_recipe_tbl (1).creation_organization_id := lt_per (i).organization_id;
l_recipe_tbl (1).formula_id := lt_per (i).formula_id;
l_recipe_tbl (1).formula_no := lt_per (i).formula_no;
l_recipe_tbl (1).formula_vers := lt_per (i).formula_version;
l_recipe_tbl (1).routing_id := lt_per (i).routing_id;
l_recipe_tbl (1).routing_no := lt_per (i).routing_no;
l_recipe_tbl (1).routing_vers := lt_per (i).routing_version;
l_recipe_tbl (1).project_id := NULL;
l_recipe_tbl (1).recipe_status := gn_status;
l_recipe_tbl (1).planned_process_loss := 0;
l_recipe_tbl (1).text_code := NULL;
l_recipe_tbl (1).delete_mark := 0;
l_recipe_tbl (1).contiguous_ind := 0;
l_recipe_tbl (1).enhanced_pi_ind := 1;
l_recipe_tbl (1).recipe_type := lt_per(i).recipe_type;
l_recipe_tbl (1).creation_date := gd_sysdate;
l_recipe_tbl (1).created_by := l_user_id;
l_recipe_tbl (1).last_updated_by := l_user_id;
l_recipe_tbl (1).last_update_date := gd_sysdate;
l_recipe_tbl (1).last_update_login := gn_login_id;
l_recipe_tbl (1).owner_id := l_user_id;
l_recipe_tbl (1).owner_lab_type := NULL;
l_recipe_tbl (1).calculate_step_quantity := 1;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_HEADER');
BEGIN
apps.gmd_recipe_header.create_recipe_header
(p_api_version => '1.0',
p_init_msg_list => 'T',
p_commit => 'F',
p_called_from_forms => 'NO',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_recipe_header_tbl => l_recipe_tbl,
p_recipe_header_flex => l_recipe_flex
);
apps.fnd_file.put_line (apps.fnd_file.LOG,'RETURN_STATUS:-' || l_return_status || ' : ' || l_msg_data);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
END;
IF l_return_status <> 'S' THEN
ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_ind
);
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.6' || l_msg_data);
END LOOP;
ELSE
ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 14');
------------------------------------------------------------------------------------------------------------------------
--- INITIATING THE VALIDATE RULE CREATION API WHICH WILL UPDATE THE GMD_RECIPE_VALIDITY_RULES TABLE
-------------------------------------------------------------------------------------------------------------------------
BEGIN
BEGIN
SELECT fmd.inventory_item_id
, ffm.total_output_qty
, ffm.yield_uom
INTO ln_inventory_item_id
, ln_required_qty
, lc_uom_code
FROM apps.fm_form_mst ffm
, apps.fm_matl_dtl fmd
WHERE 1=1
AND ffm.formula_id = fmd.formula_id
AND ffm.formula_id = l_recipe_tbl (1).formula_id
AND fmd.line_type = 1
AND fmd.line_no = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'INVENTORY ITEM ID IS INVALID'|| ln_inventory_item_id);
apps.fnd_file.put_line(apps.fnd_file.LOG,'REQUIRED QTY IS INVALID' || ln_required_qty);
apps.fnd_file.put_line(apps.fnd_file.LOG,'UOM CODE IS INVALID' || lc_uom_code);
WHEN OTHERS
THEN
ln_inventory_item_id := NULL;
lc_uom_code := NULL;
ln_required_qty := NULL;
END;
ln_recipe_id := NULL;
OPEN lcu_recipe_id (lt_per (i).recipe_no,lt_per (i).recipe_version);
FETCH lcu_recipe_id
INTO ln_recipe_id;
CLOSE lcu_recipe_id;
l_count := 1;
l_recipe_vr_tbl (l_count).recipe_validity_rule_id := NULL;
l_recipe_vr_tbl (l_count).recipe_id := ln_recipe_id;
l_recipe_vr_tbl (l_count).recipe_no := lt_per (i).recipe_no;
l_recipe_vr_tbl (l_count).recipe_version := lt_per (i).recipe_version;
l_recipe_vr_tbl (l_count).user_id := l_user_id;
l_recipe_vr_tbl (l_count).user_name := l_user_name;
l_recipe_vr_tbl (l_count).orgn_code := lt_per (i).owner_org_code;
l_recipe_vr_tbl (l_count).inventory_item_id := ln_inventory_item_id;
l_recipe_vr_tbl (l_count).revision := NULL;
l_recipe_vr_tbl (l_count).item_no := NULL; --lc_segment1;
l_recipe_vr_tbl (l_count).recipe_use := 0;
l_recipe_vr_tbl (l_count).preference := 1;
l_recipe_vr_tbl (l_count).start_date := NULL;
l_recipe_vr_tbl (l_count).end_date := NULL;
l_recipe_vr_tbl (l_count).min_qty := 0;
l_recipe_vr_tbl (l_count).max_qty := 999999999;
l_recipe_vr_tbl (l_count).std_qty := ln_required_qty;
l_recipe_vr_tbl (l_count).detail_uom := lc_uom_code;
l_recipe_vr_tbl (l_count).inv_min_qty := NULL;
l_recipe_vr_tbl (l_count).inv_max_qty := NULL;
l_recipe_vr_tbl (l_count).text_code := NULL;
l_recipe_vr_tbl (l_count).created_by := l_user_id;
l_recipe_vr_tbl (l_count).creation_date := gd_sysdate;
l_recipe_vr_tbl (l_count).last_updated_by := l_user_id;
l_recipe_vr_tbl (l_count).last_update_date := gd_sysdate;
l_recipe_vr_tbl (l_count).last_update_login := l_user_id;
l_recipe_vr_tbl (l_count).delete_mark := 0;
l_recipe_vr_tbl (l_count).planned_process_loss := NULL;
l_recipe_vr_tbl (l_count).validity_rule_status := gn_status;
l_recipe_vr_tbl (l_count).organization_id := lt_per(i).organization_id;
l_recipe_vr_tbl (l_count).fixed_process_loss := NULL;
l_recipe_vr_tbl (l_count).fixed_process_loss_uom := NULL;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_VR');
BEGIN
apps.gmd_recipe_detail.create_recipe_vr
( p_api_version => '1.0'
, p_init_msg_list => 'F'
, p_commit => 'F'
, p_called_from_forms => 'NO'
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_recipe_vr_tbl => l_recipe_vr_tbl
, p_recipe_vr_flex => l_recipe_flex_tbl
);
apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE VRS API RETURN_STATUS:-'|| l_return_status);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE VRS API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
END;
IF l_return_status <> 'S'
THEN
ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get ( p_msg_index => i
, p_encoded => 'F'
, p_data => l_msg_data
, p_msg_index_out => l_msg_ind
);
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.6' || l_msg_data);
END LOOP;
ELSE
ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in Recipe Validity Rule Proc:'|| SQLCODE|| SQLERRM);
END;
--=================STEP/MATERIAL ASSOCIATION =========================----------
BEGIN
ln_recipe_id := NULL;
OPEN lcu_recipe_id (lt_per (i).recipe_no,lt_per (i).recipe_version);
FETCH lcu_recipe_id INTO ln_recipe_id;
CLOSE lcu_recipe_id;
ln_count := 0;
l_recipe_mtl_tbl.delete;
FOR c_cur IN lcu_issue_to_step(lt_per (i).formula_id, lt_per (i).routing_id)
LOOP
ln_count := ln_count + 1;
l_recipe_mtl_tbl (ln_count).recipe_id := ln_recipe_id;
l_recipe_mtl_tbl (ln_count).recipe_no := lt_per (i).recipe_no;
l_recipe_mtl_tbl (ln_count).recipe_version := lt_per (i).recipe_version;
l_recipe_mtl_tbl (ln_count).user_id := l_user_id;
l_recipe_mtl_tbl (ln_count).user_name := l_user_name;
l_recipe_mtl_tbl (ln_count).formulaline_id := c_cur.formulaline_id;
l_recipe_mtl_tbl (ln_count).text_code := NULL;
l_recipe_mtl_tbl (ln_count).creation_date := gd_sysdate;
l_recipe_mtl_tbl (ln_count).created_by := l_user_id;
l_recipe_mtl_tbl (ln_count).last_updated_by := l_user_id;
l_recipe_mtl_tbl (ln_count).last_update_date := gd_sysdate;
l_recipe_mtl_tbl (ln_count).last_update_login := l_user_id;
l_recipe_mtl_tbl (ln_count).routingstep_id := c_cur.routingstep_id;
/*
UPDATE xxblr_opm_formula_stg
SET formulaline_id = c_cur.formulaline_id
, routingstep_id = c_cur.routingstep_id
WHERE 1=1
AND new_formula_no = lt_per (i).formula_no
AND formula_vers = lt_per (i).formula_version;
*/
EXIT WHEN lcu_issue_to_step%NOTFOUND;
END LOOP;
-- COMMIT;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_MTL');
BEGIN
apps.gmd_recipe_detail.create_recipe_mtl
( p_api_version => '1.0'
, p_init_msg_list => 'F'
, p_commit => 'F'
, p_called_from_forms => 'NO'
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_recipe_mtl_tbl => l_recipe_mtl_tbl
, p_recipe_mtl_flex => l_recipe_flex_tbl
);
apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE STEP MATERIAL API RETURN_STATUS:-'|| l_return_status);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE STEP MATERIAL API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
END;
IF l_return_status <> 'S' THEN
ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get ( p_msg_index => i
, p_encoded => 'F'
, p_data => l_msg_data
, p_msg_index_out => l_msg_ind
);
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.6' || l_msg_data);
END LOOP;
ELSE
ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in Step Material Association Proc:'|| SQLCODE|| SQLERRM);
END;
BEGIN
UPDATE xxblr_opm_recipe_stg
SET record_status = l_return_status
, error_code = l_msg_count
, error_message = l_msg_data
WHERE new_recipe_no = lt_per (i).recipe_no
AND recipe_version = lt_per (i).recipe_version;
UPDATE xxblr_opm_formula_stg
SET record_status_recipe = l_return_status
, error_code_recipe = l_msg_count
, error_message_recipe = l_msg_data
WHERE new_formula_no = lt_per (i).formula_no
AND formula_vers = lt_per (i).formula_version;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
END;
COMMIT;
END LOOP;
END IF;
apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------+');
apps.fnd_file.put_line (apps.fnd_file.LOG,' | Total records processed : '|| ln_suc_rec_cnt);
apps.fnd_file.put_line (apps.fnd_file.LOG,' | Total records rejected : '|| ln_rej_rec_cnt);
apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------+');
apps.fnd_file.put_line (apps.fnd_file.LOG, ' ');
EXIT WHEN lcu_recipe_data%NOTFOUND;
END LOOP;
CLOSE lcu_recipe_data;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-RECEPI :> ' || SQLERRM || ', ' || SQLCODE);
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Procedure CREATE_RECIPE Exit ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
END create_recipe;
IS
--local variables
l_recipe_tbl apps.gmd_recipe_header.recipe_tbl;
l_recipe_flex apps.gmd_recipe_header.recipe_flex;
l_recipe_vr_tbl apps.gmd_recipe_detail.recipe_vr_tbl;
l_recipe_flex_tbl apps.gmd_recipe_detail.recipe_flex;
l_recipe_mtl_tbl apps.gmd_recipe_detail.recipe_mtl_tbl;
l_return_status VARCHAR2 (10);
l_msg_ind VARCHAR2 (240);
l_msg_data VARCHAR2 (2000);
l_msg_count NUMBER;
l_count NUMBER;
ln_formula_no apps.fm_form_mst.formula_no%TYPE;
ln_formula_vers apps.fm_form_mst.formula_vers%TYPE;
ln_routing_no apps.fm_rout_hdr.routing_no%TYPE;
ln_routing_vers apps.fm_rout_hdr.routing_vers%TYPE;
ln_progress NUMBER := 0;
ln_suc_rec_cnt NUMBER := 0;
ln_rej_rec_cnt NUMBER := 0;
ln_count NUMBER;
ln_organization_id NUMBER;
ln_formula_id NUMBER;
ln_routing_id NUMBER;
ln_recipe_type NUMBER;
ln_routingstep_id NUMBER;
ln_inventory_item_id NUMBER;
ln_required_qty NUMBER;
ln_recipe_id NUMBER;
ln_recipe_version NUMBER;
ln_formulaline_id NUMBER;
ln_routstep_id NUMBER;
lc_recipe_no VARCHAR2 (15);
lc_uom_code VARCHAR2 (20);
lc_segment1 VARCHAR2 (40);
lc_organization_code VARCHAR2 (10);
--===============================
--Cursor to get new records count
--===============================
CURSOR lcu_count
IS
SELECT COUNT (*)
FROM xxblr_opm_recipe_stg
WHERE 1=1
AND record_status = gc_validation_flag;
--===============================
--Cursor to get Recipe Data
--===============================
CURSOR lcu_recipe_data
IS
SELECT transaction_id
, sr_no
, new_recipe_no recipe_no
, recipe_version
, recipe_description
, new_formula_no formula_no
, formula_version
, new_routing_no routing_no
, routing_version
, owner_org_code
, recipe_validity_date
, recipe_type
, formula_id
, routing_id
, organization_id
, process_flag
, error_code
, record_status
, error_message
, last_updated_by
, created_by
, last_update_date
, creation_date
, last_update_login
FROM xxblr_opm_recipe_stg
WHERE 1=1
GROUP BY transaction_id
, sr_no
, new_recipe_no
, recipe_version
, recipe_description
, new_formula_no
, formula_version
, new_routing_no
, routing_version
, owner_org_code
, recipe_validity_date
, recipe_type
, formula_id
, routing_id
, organization_id
, process_flag
, error_code
, record_status
, error_message
, last_updated_by
, created_by
, last_update_date
, creation_date
, last_update_login;
--===============================
--Cursor to get Formula Line Id
--===============================
CURSOR lcu_recipe_id(p_recipe_no varchar2,p_recipe_version number)
IS
SELECT recipe_id
FROM apps.gmd_recipes_b
WHERE 1=1
AND recipe_no = p_recipe_no
AND recipe_version = p_recipe_version;
--===============================
--Cursor to get Issue to Step
--===============================
-------------
CURSOR lcu_issue_to_step(p_formula_id number,p_routing_id number)
IS
SELECT fmd.formulaline_id
, frd.routingstep_id
FROM xxblr_opm_formula_stg xrof
, apps.fm_form_mst ffm
, apps.fm_matl_dtl fmd
, apps.mtl_system_items_b msi
, apps.fm_rout_dtl frd
WHERE 1=1
AND xrof.new_formula_no = ffm.formula_no
AND xrof.formula_vers = ffm.formula_vers
AND ffm.formula_id = p_formula_id
AND fmd.formula_id = ffm.formula_id
AND ( fmd.inventory_item_id = msi.inventory_item_id
and msi.organization_id = g_master_org
and msi.segment1 = xrof.new_item_no
)
AND fmd.qty = xrof.required_qty
AND frd.routing_id = p_routing_id
AND frd.routingstep_no = xrof.issue_to_step
GROUP BY fmd.formulaline_id, frd.routingstep_id
ORDER BY fmd.formulaline_id,frd.routingstep_id;
---------------
--===============================
--Cursor to get RoutingStep Id
--===============================
CURSOR lcu_routstep_id (p_recipe_no VARCHAR2, p_recipe_vers NUMBER)
IS
SELECT frd.routingstep_id
FROM apps.fm_rout_dtl frd,
apps.gmd_recipes grb
WHERE 1=1
AND frd.routing_id = grb.routing_id
AND grb.recipe_no = p_recipe_no
AND grb.recipe_version = p_recipe_vers;
TYPE tbl_recipe_data IS TABLE OF lcu_recipe_data%ROWTYPE INDEX BY BINARY_INTEGER;
lt_per tbl_recipe_data;
BEGIN
apps.fnd_file.put_line(apps.fnd_file.LOG, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Executing CREATE_RECIPE ---------');
apps.fnd_file.put_line(apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line(apps.fnd_file.output, RPAD ('*', 80, '*'));
apps.fnd_file.put_line(apps.fnd_file.output,' --------- Executing CREATE_RECIPE ---------');
apps.fnd_file.put_line(apps.fnd_file.output, RPAD (' ', 80, ' '));
OPEN lcu_count;
FETCH lcu_count
INTO ln_count;
CLOSE lcu_count;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Number of Records Loaded :' || ln_count);
-------------------------------------------------------------------------
-- Bulk Insert Data into Recipe.
--------------------------------------------------------------------------
OPEN lcu_recipe_data;
LOOP
lt_per.DELETE;
gn_bulk_err := 0;
FETCH lcu_recipe_data BULK COLLECT INTO lt_per LIMIT 50000;
IF lt_per.COUNT > 0
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'COUNT-->' || lt_per.COUNT);
FOR i IN lt_per.first .. lt_per.last
LOOP
apps.fnd_file.put_line(apps.fnd_file.LOG,'Recipe No:'||lt_per (i).recipe_no);
apps.fnd_file.put_line(apps.fnd_file.LOG,'Rouitng Id:'||lt_per (i).routing_id);
l_recipe_tbl (1).recipe_id := NULL;
l_recipe_tbl (1).recipe_description := lt_per (i).recipe_description;
l_recipe_tbl (1).recipe_no := lt_per (i).recipe_no;
l_recipe_tbl (1).recipe_version := lt_per (i).recipe_version;
l_recipe_tbl (1).user_id := l_user_id;
l_recipe_tbl (1).user_name := l_user_name;
l_recipe_tbl (1).owner_orgn_code := lt_per (i).owner_org_code;
l_recipe_tbl (1).creation_orgn_code := lt_per (i).owner_org_code;
l_recipe_tbl (1).owner_organization_id := lt_per (i).organization_id;
l_recipe_tbl (1).creation_organization_id := lt_per (i).organization_id;
l_recipe_tbl (1).formula_id := lt_per (i).formula_id;
l_recipe_tbl (1).formula_no := lt_per (i).formula_no;
l_recipe_tbl (1).formula_vers := lt_per (i).formula_version;
l_recipe_tbl (1).routing_id := lt_per (i).routing_id;
l_recipe_tbl (1).routing_no := lt_per (i).routing_no;
l_recipe_tbl (1).routing_vers := lt_per (i).routing_version;
l_recipe_tbl (1).project_id := NULL;
l_recipe_tbl (1).recipe_status := gn_status;
l_recipe_tbl (1).planned_process_loss := 0;
l_recipe_tbl (1).text_code := NULL;
l_recipe_tbl (1).delete_mark := 0;
l_recipe_tbl (1).contiguous_ind := 0;
l_recipe_tbl (1).enhanced_pi_ind := 1;
l_recipe_tbl (1).recipe_type := lt_per(i).recipe_type;
l_recipe_tbl (1).creation_date := gd_sysdate;
l_recipe_tbl (1).created_by := l_user_id;
l_recipe_tbl (1).last_updated_by := l_user_id;
l_recipe_tbl (1).last_update_date := gd_sysdate;
l_recipe_tbl (1).last_update_login := gn_login_id;
l_recipe_tbl (1).owner_id := l_user_id;
l_recipe_tbl (1).owner_lab_type := NULL;
l_recipe_tbl (1).calculate_step_quantity := 1;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_HEADER');
BEGIN
apps.gmd_recipe_header.create_recipe_header
(p_api_version => '1.0',
p_init_msg_list => 'T',
p_commit => 'F',
p_called_from_forms => 'NO',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_recipe_header_tbl => l_recipe_tbl,
p_recipe_header_flex => l_recipe_flex
);
apps.fnd_file.put_line (apps.fnd_file.LOG,'RETURN_STATUS:-' || l_return_status || ' : ' || l_msg_data);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
END;
IF l_return_status <> 'S' THEN
ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_ind
);
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.6' || l_msg_data);
END LOOP;
ELSE
ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
END IF;
apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 14');
------------------------------------------------------------------------------------------------------------------------
--- INITIATING THE VALIDATE RULE CREATION API WHICH WILL UPDATE THE GMD_RECIPE_VALIDITY_RULES TABLE
-------------------------------------------------------------------------------------------------------------------------
BEGIN
BEGIN
SELECT fmd.inventory_item_id
, ffm.total_output_qty
, ffm.yield_uom
INTO ln_inventory_item_id
, ln_required_qty
, lc_uom_code
FROM apps.fm_form_mst ffm
, apps.fm_matl_dtl fmd
WHERE 1=1
AND ffm.formula_id = fmd.formula_id
AND ffm.formula_id = l_recipe_tbl (1).formula_id
AND fmd.line_type = 1
AND fmd.line_no = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'INVENTORY ITEM ID IS INVALID'|| ln_inventory_item_id);
apps.fnd_file.put_line(apps.fnd_file.LOG,'REQUIRED QTY IS INVALID' || ln_required_qty);
apps.fnd_file.put_line(apps.fnd_file.LOG,'UOM CODE IS INVALID' || lc_uom_code);
WHEN OTHERS
THEN
ln_inventory_item_id := NULL;
lc_uom_code := NULL;
ln_required_qty := NULL;
END;
ln_recipe_id := NULL;
OPEN lcu_recipe_id (lt_per (i).recipe_no,lt_per (i).recipe_version);
FETCH lcu_recipe_id
INTO ln_recipe_id;
CLOSE lcu_recipe_id;
l_count := 1;
l_recipe_vr_tbl (l_count).recipe_validity_rule_id := NULL;
l_recipe_vr_tbl (l_count).recipe_id := ln_recipe_id;
l_recipe_vr_tbl (l_count).recipe_no := lt_per (i).recipe_no;
l_recipe_vr_tbl (l_count).recipe_version := lt_per (i).recipe_version;
l_recipe_vr_tbl (l_count).user_id := l_user_id;
l_recipe_vr_tbl (l_count).user_name := l_user_name;
l_recipe_vr_tbl (l_count).orgn_code := lt_per (i).owner_org_code;
l_recipe_vr_tbl (l_count).inventory_item_id := ln_inventory_item_id;
l_recipe_vr_tbl (l_count).revision := NULL;
l_recipe_vr_tbl (l_count).item_no := NULL; --lc_segment1;
l_recipe_vr_tbl (l_count).recipe_use := 0;
l_recipe_vr_tbl (l_count).preference := 1;
l_recipe_vr_tbl (l_count).start_date := NULL;
l_recipe_vr_tbl (l_count).end_date := NULL;
l_recipe_vr_tbl (l_count).min_qty := 0;
l_recipe_vr_tbl (l_count).max_qty := 999999999;
l_recipe_vr_tbl (l_count).std_qty := ln_required_qty;
l_recipe_vr_tbl (l_count).detail_uom := lc_uom_code;
l_recipe_vr_tbl (l_count).inv_min_qty := NULL;
l_recipe_vr_tbl (l_count).inv_max_qty := NULL;
l_recipe_vr_tbl (l_count).text_code := NULL;
l_recipe_vr_tbl (l_count).created_by := l_user_id;
l_recipe_vr_tbl (l_count).creation_date := gd_sysdate;
l_recipe_vr_tbl (l_count).last_updated_by := l_user_id;
l_recipe_vr_tbl (l_count).last_update_date := gd_sysdate;
l_recipe_vr_tbl (l_count).last_update_login := l_user_id;
l_recipe_vr_tbl (l_count).delete_mark := 0;
l_recipe_vr_tbl (l_count).planned_process_loss := NULL;
l_recipe_vr_tbl (l_count).validity_rule_status := gn_status;
l_recipe_vr_tbl (l_count).organization_id := lt_per(i).organization_id;
l_recipe_vr_tbl (l_count).fixed_process_loss := NULL;
l_recipe_vr_tbl (l_count).fixed_process_loss_uom := NULL;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_VR');
BEGIN
apps.gmd_recipe_detail.create_recipe_vr
( p_api_version => '1.0'
, p_init_msg_list => 'F'
, p_commit => 'F'
, p_called_from_forms => 'NO'
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_recipe_vr_tbl => l_recipe_vr_tbl
, p_recipe_vr_flex => l_recipe_flex_tbl
);
apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE VRS API RETURN_STATUS:-'|| l_return_status);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE VRS API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
END;
IF l_return_status <> 'S'
THEN
ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get ( p_msg_index => i
, p_encoded => 'F'
, p_data => l_msg_data
, p_msg_index_out => l_msg_ind
);
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.6' || l_msg_data);
END LOOP;
ELSE
ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in Recipe Validity Rule Proc:'|| SQLCODE|| SQLERRM);
END;
--=================STEP/MATERIAL ASSOCIATION =========================----------
BEGIN
ln_recipe_id := NULL;
OPEN lcu_recipe_id (lt_per (i).recipe_no,lt_per (i).recipe_version);
FETCH lcu_recipe_id INTO ln_recipe_id;
CLOSE lcu_recipe_id;
ln_count := 0;
l_recipe_mtl_tbl.delete;
FOR c_cur IN lcu_issue_to_step(lt_per (i).formula_id, lt_per (i).routing_id)
LOOP
ln_count := ln_count + 1;
l_recipe_mtl_tbl (ln_count).recipe_id := ln_recipe_id;
l_recipe_mtl_tbl (ln_count).recipe_no := lt_per (i).recipe_no;
l_recipe_mtl_tbl (ln_count).recipe_version := lt_per (i).recipe_version;
l_recipe_mtl_tbl (ln_count).user_id := l_user_id;
l_recipe_mtl_tbl (ln_count).user_name := l_user_name;
l_recipe_mtl_tbl (ln_count).formulaline_id := c_cur.formulaline_id;
l_recipe_mtl_tbl (ln_count).text_code := NULL;
l_recipe_mtl_tbl (ln_count).creation_date := gd_sysdate;
l_recipe_mtl_tbl (ln_count).created_by := l_user_id;
l_recipe_mtl_tbl (ln_count).last_updated_by := l_user_id;
l_recipe_mtl_tbl (ln_count).last_update_date := gd_sysdate;
l_recipe_mtl_tbl (ln_count).last_update_login := l_user_id;
l_recipe_mtl_tbl (ln_count).routingstep_id := c_cur.routingstep_id;
/*
UPDATE xxblr_opm_formula_stg
SET formulaline_id = c_cur.formulaline_id
, routingstep_id = c_cur.routingstep_id
WHERE 1=1
AND new_formula_no = lt_per (i).formula_no
AND formula_vers = lt_per (i).formula_version;
*/
EXIT WHEN lcu_issue_to_step%NOTFOUND;
END LOOP;
-- COMMIT;
apps.fnd_file.put_line (apps.fnd_file.LOG,'Calling CREATE_RECIPE_MTL');
BEGIN
apps.gmd_recipe_detail.create_recipe_mtl
( p_api_version => '1.0'
, p_init_msg_list => 'F'
, p_commit => 'F'
, p_called_from_forms => 'NO'
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_recipe_mtl_tbl => l_recipe_mtl_tbl
, p_recipe_mtl_flex => l_recipe_flex_tbl
);
apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE STEP MATERIAL API RETURN_STATUS:-'|| l_return_status);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'RECIPE STEP MATERIAL API RAISED ERROR: '|| SQLCODE|| '-'|| SQLERRM);
END;
IF l_return_status <> 'S' THEN
ln_rej_rec_cnt := ln_rej_rec_cnt + 1;
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get ( p_msg_index => i
, p_encoded => 'F'
, p_data => l_msg_data
, p_msg_index_out => l_msg_ind
);
apps.fnd_file.put_line (apps.fnd_file.LOG,'TRACK 13.6' || l_msg_data);
END LOOP;
ELSE
ln_suc_rec_cnt := ln_suc_rec_cnt + 1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error in Step Material Association Proc:'|| SQLCODE|| SQLERRM);
END;
BEGIN
UPDATE xxblr_opm_recipe_stg
SET record_status = l_return_status
, error_code = l_msg_count
, error_message = l_msg_data
WHERE new_recipe_no = lt_per (i).recipe_no
AND recipe_version = lt_per (i).recipe_version;
UPDATE xxblr_opm_formula_stg
SET record_status_recipe = l_return_status
, error_code_recipe = l_msg_count
, error_message_recipe = l_msg_data
WHERE new_formula_no = lt_per (i).formula_no
AND formula_vers = lt_per (i).formula_version;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.LOG,'Error occured in staging table updation:'||sqlcode||sqlerrm);
END;
COMMIT;
END LOOP;
END IF;
apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------+');
apps.fnd_file.put_line (apps.fnd_file.LOG,' | Total records processed : '|| ln_suc_rec_cnt);
apps.fnd_file.put_line (apps.fnd_file.LOG,' | Total records rejected : '|| ln_rej_rec_cnt);
apps.fnd_file.put_line(apps.fnd_file.LOG,' +----------------------------------------------------------+');
apps.fnd_file.put_line (apps.fnd_file.LOG, ' ');
EXIT WHEN lcu_recipe_data%NOTFOUND;
END LOOP;
CLOSE lcu_recipe_data;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-CRT-RECEPI :> ' || SQLERRM || ', ' || SQLCODE);
apps.fnd_file.put_line(apps.fnd_file.LOG,' --------- Procedure CREATE_RECIPE Exit ---------');
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
END create_recipe;
No comments:
Post a Comment
Note: only a member of this blog may post a comment.