[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| insert_row, delete_row are added in create_recipe_header procedure
| to add and delete the row with formula_id = -1.
| 27-APR-2004 S.Sriram Bug# 3408799
| Added SET_DEFAULT_STATUS procedure for Default Status Build
| 13-OCT-2004 Sriram.S Recipe Security Bug# 3948203
| Added a proc. to which checks if user has recipe orgn. access.
| 15-OCT-2004 Thomas Daniel Bug# 3953359
| Added code to set the default status appropriately in the copy
| recipe procedure.
| 22-Apr-2008 RLNAGARA Modified the proc Create_Recipe_Header for Fixed Process Loss ME
=============================================================================
*/
/* Api start of comments
+============================================================================
| PROCEDURE NAME
| Create_Text_Row
|
| DESCRIPTION
| Create a row in FM_TEXT_TBL
|
| INPUT PARAMETERS
| p_text_code NUMBER
| p_lang_code VARCHAR2
| p_text VARCHAR2
| p_line_no NUMBER
| p_paragraph_code VARCHAR2
| p_sub_paracode NUMBER
| p_table_lnk VARCHAR2
| p_user_id NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 03-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Create_Text_Row ( p_text_code IN NUMBER,
p_lang_code IN VARCHAR2,
p_text IN VARCHAR2,
p_line_no IN NUMBER,
p_paragraph_code IN VARCHAR2,
p_sub_paracode IN NUMBER,
p_table_lnk IN VARCHAR2,
p_user_id IN NUMBER,
x_row_id OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_dummy NUMBER := 0;
SELECT COUNT(*) INTO l_dummy
FROM fm_text_hdr WHERE text_code = p_text_code;
INSERT INTO fm_text_hdr ( text_code
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
VALUES (p_text_code,
sysdate,
p_user_id,
p_user_id,
p_user_id,
sysdate);
INSERT INTO fm_text_tbl_vl ( text_code
,lang_code
,paragraph_code
,sub_paracode
,line_no
,text
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
VALUES (p_text_code,
p_lang_code,
p_paragraph_code,
p_sub_paracode,
-1,
p_table_lnk,
sysdate,
p_user_id,
p_user_id,
p_user_id,
sysdate);
GMA_FM_TEXT_TBL_PKG.INSERT_ROW(
X_ROWID => x_row_id,
X_TEXT_CODE => p_text_code,
X_PARAGRAPH_CODE => p_paragraph_code,
X_SUB_PARACODE => p_sub_paracode,
X_LINE_NO => p_line_no,
X_LANG_CODE => p_lang_code,
X_TEXT => p_text,
X_CREATION_DATE => sysdate,
X_CREATED_BY => p_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => p_user_id);
| Update_Text_Row
|
| DESCRIPTION
| Update a row in FM_TEXT_TBL
|
| INPUT PARAMETERS
| p_text_code NUMBER
| p_lang_code VARCHAR2
| p_text VARCHAR2
| p_line_no NUMBER
| p_paragraph_code VARCHAR2
| p_sub_paracode NUMBER
| p_table_lnk VARCHAR2
| p_user_id NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 13-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Text_Row ( p_text_code IN NUMBER,
p_lang_code IN VARCHAR2,
p_text IN VARCHAR2,
p_line_no IN NUMBER,
p_paragraph_code IN VARCHAR2,
p_sub_paracode IN NUMBER,
p_user_id IN NUMBER,
p_row_id IN VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_code := 'S';
GMA_FM_TEXT_TBL_PKG.UPDATE_ROW(
X_ROW_ID => p_row_id,
X_TEXT_CODE => p_text_code,
X_LANG_CODE => p_lang_code,
X_PARAGRAPH_CODE => p_paragraph_code,
X_SUB_PARACODE => p_sub_paracode,
X_LINE_NO => p_line_no,
X_TEXT => p_text,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => p_user_id);
END Update_Text_Row;
| Delete_Text_Row
|
| DESCRIPTION
| Delete a row in FM_TEXT_TBL
|
| INPUT PARAMETERS
| p_text_code NUMBER
| p_lang_code VARCHAR2
| p_paragraph_code VARCHAR2
| p_sub_paracode NUMBER
| p_line_no NUMBER
| p_row_id VARCHAR2
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 13-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Text_Row ( p_text_code IN NUMBER,
p_lang_code IN VARCHAR2,
p_paragraph_code IN VARCHAR2,
p_sub_paracode IN NUMBER,
p_line_no IN NUMBER,
p_row_id IN VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_code := 'S';
GMA_FM_TEXT_TBL_PKG.DELETE_ROW(
X_TEXT_CODE => p_text_code,
X_LANG_CODE => p_lang_code,
X_PARAGRAPH_CODE => p_paragraph_code,
X_SUB_PARACODE => p_sub_paracode,
X_LINE_NO => p_line_no,
X_ROW_ID => p_row_id);
END Delete_Text_Row;
| Update_Recipe_Routing_step_Row
|
| DESCRIPTION
| Update a row in GMD_RECIPE_ROUTING_STEPS
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
| p_routingstep_id NUMBER
| p_text_code NUMBER
| p_last_update_date DATE
| p_last_update_date_origin DATE
| p_user_id NUMBER
| p_step_qty NUMBER
| p_mass_qty NUMBER
| p_vol_qty NUMBER
| p_mass_uom VARCHAR2
| p_vol_uom VARCHAR2
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 03-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Recipe_Routing_Step_Row ( p_recipe_id IN NUMBER,
p_routingstep_id IN NUMBER,
p_text_code IN NUMBER,
p_last_update_date IN DATE,
p_last_update_date_origin IN DATE,
p_user_id IN NUMBER,
p_step_qty IN NUMBER,
p_mass_qty IN NUMBER,
p_vol_qty IN NUMBER,
p_mass_uom IN VARCHAR2,
p_vol_uom IN VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_text_code NUMBER;
UPDATE
GMD_RECIPE_ROUTING_STEPS
SET
STEP_QTY = p_step_qty,
TEXT_CODE = l_text_code,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATE_LOGIN = p_user_id,
MASS_QTY = p_mass_qty,
MASS_REF_UOM = p_mass_uom,
VOLUME_QTY = p_vol_qty,
VOLUME_REF_UOM = p_vol_uom
WHERE
RECIPE_ID = p_recipe_id AND
ROUTINGSTEP_ID = p_routingstep_id AND
LAST_UPDATE_DATE = p_last_update_date_origin;
END Update_Recipe_Routing_Step_Row;
| p_last_update_date DATE
| p_user_id NUMBER
| p_step_qty NUMBER
| p_mass_qty NUMBER
| p_vol_qty NUMBER
| p_mass_uom VARCHAR2
| p_vol_uom VARCHAR2
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 10-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Create_Recipe_Routing_Step_Row ( p_recipe_id IN NUMBER,
p_routingstep_id IN NUMBER,
p_text_code IN NUMBER,
p_last_update_date IN DATE,
p_user_id IN NUMBER,
p_step_qty IN NUMBER,
p_mass_qty IN NUMBER,
p_vol_qty IN NUMBER,
p_mass_uom IN VARCHAR2,
p_vol_uom IN VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_code := 'S';
INSERT INTO gmd_recipe_routing_steps
(RECIPE_ID
,ROUTINGSTEP_ID
,STEP_QTY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,TEXT_CODE
,MASS_QTY
,MASS_REF_UOM
,VOLUME_QTY
,VOLUME_REF_UOM)
VALUES
(p_recipe_id,
p_routingstep_id,
p_step_qty,
p_user_id,
p_last_update_date,
p_user_id,
p_last_update_date,
p_user_id,
p_text_code,
p_mass_qty,
p_mass_uom,
p_vol_qty,
p_vol_uom);
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 04-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Create_Step_Material_Link ( p_recipe_id IN NUMBER,
p_formulaline_id IN NUMBER,
p_routingstep_id IN NUMBER,
p_text_code IN NUMBER,
p_user_id IN NUMBER,
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_code := 'S';
INSERT INTO gmd_recipe_step_materials (
recipe_id
,formulaline_id
,routingstep_id
,text_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES ( p_recipe_id
,p_formulaline_id
,p_routingstep_id
,p_text_code
,p_last_update_date
,p_user_id
,p_last_update_date
,p_user_id
,p_user_id);
| Delete_Step_Material_Link
|
| DESCRIPTION
| Delete a row in GMD_RECIPE_STEP_MATERIALS
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
| p_formulaline_id NUMBER
| p_routingstep_id NUMBER
| p_last_update_date_origin DATE
| p_user_id NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 04-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Step_Material_Link ( p_recipe_id IN NUMBER,
p_formulaline_id IN NUMBER,
p_routingstep_id IN NUMBER,
p_last_update_date_origin IN DATE,
p_user_id IN NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
/*
CURSOR Get_Step (c_recipe_id NUMBER, c_formulaline_id NUMBER, routingstep_id NUMBER) IS
SELECT
last_update_date
FROM
gmd_recipe_step_materials
WHERE
recipe_id = c_recipe_id AND
formulaline_id = c_formulaline_id AND
routingstep_id = routingstep_id;
DELETE
gmd_recipe_step_materials
WHERE
recipe_id = p_recipe_id AND
formulaline_id = p_formulaline_id AND
routingstep_id = p_routingstep_id AND
last_update_date = p_last_update_date_origin;
END Delete_Step_Material_Link;
| Update_Step_Material_Link
|
| DESCRIPTION
| Update a row in GMD_RECIPE_STEP_MATERIALS
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
| p_formulaline_id NUMBER
| p_routingstep_id NUMBER
| p_text_code NUMBER
| p_last_update_date DATE
| p_last_update_date_origin DATE
| p_user_id NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 04-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Step_Material_Link ( p_recipe_id IN NUMBER,
p_formulaline_id IN NUMBER,
p_routingstep_id IN NUMBER,
p_text_code IN NUMBER,
p_last_update_date IN DATE,
p_last_update_date_origin IN DATE,
p_user_id IN NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_code := 'S';
UPDATE
gmd_recipe_step_materials
SET
text_code = p_text_code,
last_update_date = p_last_update_date,
last_updated_by = p_user_id,
last_update_login = p_user_id
WHERE
recipe_id = p_recipe_id AND
formulaline_id = p_formulaline_id AND
routingstep_id = p_routingstep_id AND
last_update_date = p_last_update_date_origin;
END Update_Step_Material_Link;
SELECT routing_id, planned_process_loss, owner_organization_id
FROM gmd_recipes_b
WHERE recipe_id = p_recipe_id;
SELECT
ROUTINGSTEP_ID,
ROUTINGSTEP_NO
FROM
FM_ROUT_DTL
WHERE
routing_id = p_routing_id;
PROCEDURE Update_Step_Qty (p_routingstep_id NUMBER,
p_ind NUMBER) IS
BEGIN
UPDATE
GMD_RECIPE_ROUTING_STEPS
SET
STEP_QTY = l_step_tbl(p_ind).step_qty,
MASS_QTY = l_step_tbl(p_ind).step_mass_qty,
VOLUME_QTY = l_step_tbl(p_ind).step_vol_qty
WHERE
RECIPE_ID = p_recipe_id AND
ROUTINGSTEP_ID = p_routingstep_id;
INSERT INTO gmd_recipe_routing_steps
(RECIPE_ID
,ROUTINGSTEP_ID
,STEP_QTY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,TEXT_CODE
,MASS_QTY
,MASS_REF_UOM
,VOLUME_QTY
,VOLUME_REF_UOM)
VALUES
(p_recipe_id,
l_routingstep_id,
l_step_tbl(p_ind).step_qty,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
0,
l_step_tbl(p_ind).step_mass_qty,
l_step_tbl(p_ind).step_mass_uom,
l_step_tbl(p_ind).step_vol_qty,
l_step_tbl(p_ind).step_vol_uom);
END Update_Step_Qty;
Update_Step_Qty (l_routingstep_id, i);
l_charge_tbl.DELETE;
| Determine whether this recipe is in update or query mode
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
|
| OUTPUT PARAMETERS
| x_recipe_mode VARCHAR2
| x_return_code VARCHAR2
| x_error_msg VARCHAR2
|
| HISTORY
| 15-OCT-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Get_Recipe_Mode ( p_recipe_id IN NUMBER,
x_recipe_mode OUT NOCOPY VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return_code VARCHAR2(1);
IF GMD_COMMON_VAL.Update_Allowed(entity => 'RECIPE',
entity_id => p_recipe_id) AND
GMD_API_GRP.check_Orgn_Access(entity => 'RECIPE',
entity_id => p_recipe_id) THEN
x_recipe_mode := 'U';
SELECT
routing_id
FROM
gmd_recipes
WHERE
recipe_id = p_recipe_id;
| Update_Step_Quantities
|
| DESCRIPTION
| Update step quantities table
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
| p_ruting_id NUMBER
| p_user_id NUMBER
| p_text_code NUMBER
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2
| x_error_msg VARCHAR2
|
| HISTORY
| 10-JUL-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
/*
PROCEDURE Update_Step_Quantities ( p_recipe_id IN NUMBER,
p_routing_id IN NUMBER,
p_user_id IN NUMBER,
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Get_Step_Id (c_routing_id NUMBER, c_routingstep_no NUMBER) IS
SELECT
routingstep_id
FROM
fm_rout_dtl
WHERE
routing_id = c_routing_id AND
routingstep_no = c_routingstep_no;
UPDATE
gmd_recipe_routing_steps
SET
STEP_QTY = l_step_qty,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATE_LOGIN = p_user_id,
MASS_QTY = l_mass_qty,
MASS_REF_UOM = l_mass_uom,
VOLUME_QTY = l_vol_qty,
VOLUME_REF_UOM = l_vol_uom
WHERE
RECIPE_ID = p_recipe_id AND
ROUTINGSTEP_ID = l_routingstep_id AND
LAST_UPDATE_DATE = p_last_update_date;
INSERT INTO gmd_recipe_routing_steps
(RECIPE_ID
,ROUTINGSTEP_ID
,STEP_QTY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,TEXT_CODE
,MASS_QTY
,MASS_REF_UOM
,VOLUME_QTY
,VOLUME_REF_UOM)
VALUES
(p_recipe_id,
l_routingstep_id,
l_step_qty,
p_user_id,
p_last_update_date,
p_user_id,
p_last_update_date,
p_user_id,
0,
l_mass_qty,
l_mass_uom,
l_vol_qty,
l_vol_uom);
END Update_Step_Quantities;
| Delete_Recipe_Step_Quantities
|
| DESCRIPTION
| Delete all rows in GMD_RECIPE_ROUTING_STEPS
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 31-OCT-2001 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Recipe_Step_Quantities ( p_recipe_id IN NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
BEGIN
x_return_code := 'S';
DELETE FROM
GMD_RECIPE_ROUTING_STEPS
WHERE
recipe_id = p_recipe_id;
END Delete_Recipe_Step_Quantities;
SELECT
routing_id
FROM
gmd_recipes
WHERE
recipe_id = p_recipe_id;
| Update_Recipe_Header
|
| DESCRIPTION
| Update a row in GMD_RECIPES
|
| INPUT PARAMETERS
| p_recipe_id IN NUMBER
| p_recipe_description IN VARCHAR2
| p_recipe_no IN VARCHAR2
| p_recipe_version IN NUMBER
| p_recipe_status IN VARCHAR2
| p_delete_mark IN NUMBER
| p_formula_id IN NUMBER
| p_routing_id IN NUMBER
| p_planned_process_loss IN NUMBER
| p_text_code IN NUMBER
| p_owner_id IN NUMBER
| p_calculate_step_qty IN NUMBER
| p_user_id IN NUMBER
| p_last_update_date IN DATE
| p_last_update_date_origin IN DATE
| p_update_number_version IN VARCHAR2
| p_enhanced_pi_ind IN VARCHAR2
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 05-MAR-2002 Eddie Oumerretane Created.
| 19-SEP-2002 Eddie Oumerretane Modified interface and implemented call
| to the Update_Recipe_Header API.
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Recipe_Header ( p_recipe_id IN NUMBER,
p_recipe_description IN VARCHAR2,
p_recipe_no IN VARCHAR2,
p_recipe_version IN NUMBER,
p_owner_organization_id IN NUMBER,
p_creation_organization_id IN NUMBER,
p_recipe_status IN VARCHAR2,
p_delete_mark IN NUMBER,
p_formula_id IN NUMBER,
p_routing_id IN NUMBER,
p_planned_process_loss IN NUMBER,
p_text_code IN NUMBER,
p_owner_id IN NUMBER,
p_calculate_step_qty IN NUMBER,
p_user_id IN NUMBER,
p_last_update_date IN DATE,
p_last_update_date_origin IN DATE,
p_update_number_version IN VARCHAR2,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2,
p_enhanced_pi_ind IN VARCHAR2,
p_contiguous_ind IN NUMBER,
p_recipe_type IN NUMBER) IS
CURSOR Get_Recipe IS
SELECT *
FROM GMD_RECIPES
WHERE
recipe_id = p_recipe_id AND
last_update_date = p_last_update_date_origin;
l_recipe_update_flex GMD_RECIPE_HEADER.update_flex;
SELECT a.routingstep_id
FROM gmd_recipe_step_materials a, fm_rout_dtl b
WHERE a.recipe_id = p_recipe_id
AND a.routingstep_id = b.routingstep_id
AND b.routing_id = l_recipe_rec.routing_id;
SELECT a.routingstep_id
FROM gmd_recipe_routing_steps a, fm_rout_dtl b
WHERE a.recipe_id = p_recipe_id
AND a.routingstep_id = b.routingstep_id
AND b.routing_id = l_recipe_rec.routing_id;
UPDATE_RECIPE_EXCEPTION EXCEPTION;
l_recipe_tbl.DELETE_MARK := p_delete_mark;
l_recipe_tbl.LAST_UPDATED_BY := p_user_id;
l_recipe_tbl.LAST_UPDATE_DATE := p_last_update_date;
l_recipe_tbl.LAST_UPDATE_LOGIN := p_user_id;
l_recipe_update_flex.ATTRIBUTE_CATEGORY := l_recipe_rec.attribute_category;
l_recipe_update_flex.ATTRIBUTE1 := l_recipe_rec.attribute1;
l_recipe_update_flex.ATTRIBUTE2 := l_recipe_rec.attribute2;
l_recipe_update_flex.ATTRIBUTE3 := l_recipe_rec.attribute3;
l_recipe_update_flex.ATTRIBUTE4 := l_recipe_rec.attribute4;
l_recipe_update_flex.ATTRIBUTE5 := l_recipe_rec.attribute5;
l_recipe_update_flex.ATTRIBUTE6 := l_recipe_rec.attribute6;
l_recipe_update_flex.ATTRIBUTE7 := l_recipe_rec.attribute7;
l_recipe_update_flex.ATTRIBUTE8 := l_recipe_rec.attribute8;
l_recipe_update_flex.ATTRIBUTE9 := l_recipe_rec.attribute9;
l_recipe_update_flex.ATTRIBUTE10 := l_recipe_rec.attribute10;
l_recipe_update_flex.ATTRIBUTE11 := l_recipe_rec.attribute11;
l_recipe_update_flex.ATTRIBUTE12 := l_recipe_rec.attribute12;
l_recipe_update_flex.ATTRIBUTE13 := l_recipe_rec.attribute13;
l_recipe_update_flex.ATTRIBUTE14 := l_recipe_rec.attribute14;
l_recipe_update_flex.ATTRIBUTE15 := l_recipe_rec.attribute15;
l_recipe_update_flex.ATTRIBUTE16 := l_recipe_rec.attribute16;
l_recipe_update_flex.ATTRIBUTE17 := l_recipe_rec.attribute17;
l_recipe_update_flex.ATTRIBUTE18 := l_recipe_rec.attribute18;
l_recipe_update_flex.ATTRIBUTE19 := l_recipe_rec.attribute19;
l_recipe_update_flex.ATTRIBUTE20 := l_recipe_rec.attribute20;
l_recipe_update_flex.ATTRIBUTE21 := l_recipe_rec.attribute21;
l_recipe_update_flex.ATTRIBUTE22 := l_recipe_rec.attribute22;
l_recipe_update_flex.ATTRIBUTE23 := l_recipe_rec.attribute23;
l_recipe_update_flex.ATTRIBUTE24 := l_recipe_rec.attribute24;
l_recipe_update_flex.ATTRIBUTE25 := l_recipe_rec.attribute25;
l_recipe_update_flex.ATTRIBUTE26 := l_recipe_rec.attribute26;
l_recipe_update_flex.ATTRIBUTE27 := l_recipe_rec.attribute27;
l_recipe_update_flex.ATTRIBUTE28 := l_recipe_rec.attribute28;
l_recipe_update_flex.ATTRIBUTE29 := l_recipe_rec.attribute29;
l_recipe_update_flex.ATTRIBUTE30 := l_recipe_rec.attribute30;
GMD_RECIPE_HEADER_PVT.Update_Recipe_Header
( p_recipe_header_rec => l_recipe_tbl
,p_flex_header_rec => l_recipe_update_flex
,x_return_status => l_return_status);
RAISE UPDATE_RECIPE_EXCEPTION;
IF p_update_number_version = 'Y' THEN
IF l_recipe_rec.recipe_no <> p_recipe_no OR
l_recipe_rec.recipe_version <> p_recipe_version THEN
UPDATE
GMD_RECIPES_B
SET
recipe_no = p_recipe_no,
recipe_version = p_recipe_version
WHERE
recipe_id = p_recipe_id;
DELETE gmd_recipe_step_materials
WHERE recipe_id = p_recipe_id AND
routingstep_id = old_step_mat.routingstep_id;
DELETE gmd_recipe_routing_steps
WHERE recipe_id = p_recipe_id AND
routingstep_id = old_step.routingstep_id;
WHEN UPDATE_RECIPE_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Update_Recipe_Header;
SELECT gmd_recipe_id_s.NEXTVAL
FROM FND_DUAL;
INSERT_RECIPE_EXCEPTION EXCEPTION;
DELETE FROM gmd_formula_security
WHERE orgn_code = l_owner_orgn_code
AND formula_id = -1;
gmd_formula_security_pkg.insert_row (
X_FORMULA_SECURITY_ID => l_formula_security_id,
X_FORMULA_ID => -1,
X_ACCESS_TYPE_IND => 'U',
X_ORGN_CODE => l_owner_orgn_code,
X_USER_ID => l_user_id,
X_RESPONSIBILITY_ID => NULL,
X_OTHER_ORGN => NULL,
X_CREATION_DATE => l_timestamp,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => l_timestamp,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id);
delete from FM_FORM_MST_B where FORMULA_ID = -1;
insert into
FM_FORM_MST_B(FORMULA_ID,
OWNER_ORGANIZATION_ID,
DELETE_MARK,
FORMULA_STATUS,
OWNER_ID,
FORMULA_NO,
FORMULA_VERS,
FORMULA_TYPE,
INACTIVE_IND,
SCALE_TYPE,
FORMULA_DESC1,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values (-1,
p_orgn_id ,
1,
100,
l_user_id,
-1,
-1,
0,
1,
1,
-1,
l_timestamp,
l_user_id,
l_timestamp,
l_user_id,
l_login_id);
GMD_RECIPES_MLS.Insert_Row(
X_ROWID => l_rowid,
X_RECIPE_ID => x_recipe_id,
X_RECIPE_NO => l_recipe_no,
X_RECIPE_VERSION => 1,
X_OWNER_ORGANIZATION_ID => p_orgn_id,
X_CREATION_ORGANIZATION_ID => p_orgn_id,
X_FORMULA_ID => -1,
X_ROUTING_ID => NULL,
X_PROJECT_ID => NULL,
X_RECIPE_STATUS => '100',
X_CALCULATE_STEP_QUANTITY => 0,
X_PLANNED_PROCESS_LOSS => NULL,
X_RECIPE_DESCRIPTION => 'New',
X_OWNER_ID => l_user_id,
X_OWNER_LAB_TYPE => NULL,
--Additional 3 column added - Begin
X_CONTIGUOUS_IND => 0,
-- By default this value is 'N'
-- for gmd-gmo convergence
X_ENHANCED_PI_IND => 'N',
X_RECIPE_TYPE => 0,
--Additional 3 column added- End
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_ATTRIBUTE21 => NULL,
X_ATTRIBUTE22 => NULL,
X_ATTRIBUTE23 => NULL,
X_ATTRIBUTE24 => NULL,
X_ATTRIBUTE25 => NULL,
X_ATTRIBUTE26 => NULL,
X_ATTRIBUTE27 => NULL,
X_ATTRIBUTE28 => NULL,
X_ATTRIBUTE29 => NULL,
X_ATTRIBUTE30 => NULL,
X_DELETE_MARK => 0,
X_TEXT_CODE => NULL,
X_CREATION_DATE => l_timestamp,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => l_timestamp,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id,
X_FIXED_PROCESS_LOSS => NULL, /*RLNAGARA 6811759*/
X_FIXED_PROCESS_LOSS_UOM => NULL);
WHEN INSERT_RECIPE_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 15-OCT-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Add_Recipe_Customer (p_recipe_id IN NUMBER,
p_customer_id IN NUMBER,
p_text_code IN NUMBER,
p_org_id IN NUMBER, --new addition
p_site_use_id IN NUMBER, --new addition
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_cust_tbl GMD_RECIPE_DETAIL.recipe_detail_tbl;
l_cust_tbl(1).creation_date := p_last_update_date;
l_cust_tbl(1).last_updated_by := l_user_id;
l_cust_tbl(1).last_update_login := l_login_id;
l_cust_tbl(1).last_update_date := p_last_update_date;
| Delete_Recipe_Customer
|
| DESCRIPTION
| Delete customer from the recipe
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
| p_customer_id NUMBER
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 15-OCT-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Recipe_Customer (p_recipe_id IN NUMBER,
p_customer_id IN NUMBER,
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_status VARCHAR2(30);
DELETE
gmd_recipe_customers
WHERE
recipe_id = p_recipe_id AND
customer_id = p_customer_id AND
last_update_date = p_last_update_date;
End Delete_Recipe_Customer;
| p_last_update_date DATE
| p_loss_id NUMBER
|
| OUTPUT PARAMETERS
| x_loss_id NUMBER
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 30-OCT-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Create_Process_Loss (p_recipe_id IN NUMBER,
p_orgn_id IN NUMBER,
p_process_loss IN NUMBER,
p_text_code IN NUMBER,
p_contiguous_ind IN NUMBER,
p_last_update_date IN DATE,
p_loss_id IN NUMBER,
x_loss_id OUT NOCOPY NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Cur_loss_id IS
SELECT gmd_recipe_process_loss_id_s.NEXTVAL
FROM SYS.DUAL;
l_loss_tbl.creation_date := p_last_update_date;
l_loss_tbl.last_updated_by := l_user_id;
l_loss_tbl.last_update_login := l_login_id;
l_loss_tbl.last_update_date := p_last_update_date;
| p_last_update_date DATE
| p_loss_id NUMBER
|
| OUTPUT PARAMETERS
| x_loss_id NUMBER
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 10-DEC-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Add_Org_Process_Loss (p_recipe_id IN NUMBER,
p_orgn_id IN NUMBER,
p_process_loss IN NUMBER,
p_text_code IN NUMBER,
p_contiguous_ind IN NUMBER,
p_last_update_date IN DATE,
x_loss_id OUT NOCOPY NUMBER,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
BEGIN
Create_Process_Loss (p_recipe_id => p_recipe_id,
p_orgn_id => p_orgn_id,
p_process_loss => p_process_loss,
p_text_code => p_text_code,
p_contiguous_ind => p_contiguous_ind,
p_last_update_date => p_last_update_date,
p_loss_id => NULL,
x_loss_id => x_loss_id,
x_return_code => x_return_code,
x_error_msg => x_error_msg);
| Delete_Org_Process_Loss
|
| DESCRIPTION
| Delete organization specific process loss from the recipe
|
| INPUT PARAMETERS
| p_loss_id NUMBER
| p_last_update_date DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 30-OCT-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Org_Process_Loss (p_loss_id IN NUMBER,
p_last_update_date IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_status VARCHAR2(30);
DELETE
gmd_recipe_process_loss
WHERE
recipe_process_loss_id = p_loss_id AND
last_update_date = p_last_update_date;
END Delete_Org_Process_Loss;
| Update_Org_Process_Loss
|
| DESCRIPTION
| Update an organization specific process loss
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
| p_recipe_loss_id NUMBER
| p_orgn_id VARCHAR2
| p_process_loss NUMBER
| p_text_code NUMBER
| p_last_update_date DATE
| p_last_update_date_orig DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 09-OCT-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Update_Org_Process_Loss (p_recipe_id IN NUMBER,
p_recipe_loss_id IN NUMBER,
p_orgn_id IN NUMBER,
p_process_loss IN NUMBER,
p_text_code IN NUMBER,
p_contiguous_ind IN NUMBER,
p_last_update_date IN DATE,
p_last_update_date_orig IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Get_Loss IS
SELECT 1
FROM
gmd_recipe_process_loss
WHERE
recipe_process_loss_id = p_recipe_loss_id AND
last_update_date = p_last_update_date_orig;
UPDATE_LOSS_EXCEPTION EXCEPTION;
l_loss_tbl.last_updated_by := l_user_id;
l_loss_tbl.last_update_login := l_login_id;
l_loss_tbl.last_update_date := p_last_update_date;
GMD_RECIPE_DETAIL_PVT.Update_Recipe_Process_Loss(
p_recipe_detail_rec => l_loss_tbl,
x_return_status => l_status);
RAISE UPDATE_LOSS_EXCEPTION;
WHEN UPDATE_LOSS_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Update_Org_Process_Loss;
| Delete_Recipe
|
| DESCRIPTION
| Mark for purge the given recipe
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
| p_last_update_date_orig DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 13-NOV-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Delete_Recipe (p_recipe_id IN NUMBER,
p_last_update_date_orig IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Get_Recipe IS
SELECT *
FROM
gmd_recipes
WHERE
recipe_id = p_recipe_id AND
last_update_date = p_last_update_date_orig;
l_recipe_flex GMD_RECIPE_HEADER.update_flex;
DELETE_RECIPE_EXCEPTION EXCEPTION;
l_recipe_hdr.DELETE_MARK := 1;
l_recipe_hdr.LAST_UPDATED_BY := l_user_id;
l_recipe_hdr.LAST_UPDATE_DATE := SYSDATE;
l_recipe_hdr.LAST_UPDATE_LOGIN := l_login_id;
GMD_RECIPE_HEADER_PVT.Delete_Recipe_Header
( p_recipe_header_rec => l_recipe_hdr
,p_flex_header_rec => l_recipe_flex
,x_return_status => l_status);
RAISE DELETE_RECIPE_EXCEPTION;
WHEN DELETE_RECIPE_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Delete_Recipe;
| Undelete the the given recipe
|
| INPUT PARAMETERS
| p_recipe_id NUMBER
| p_last_update_date_orig DATE
|
| OUTPUT PARAMETERS
| x_return_code VARCHAR2(1)
| x_error_msg VARCHAR2(100)
|
| HISTORY
| 13-NOV-2002 Eddie Oumerretane Created.
|
+=============================================================================
Api end of comments
*/
PROCEDURE Undelete_Recipe (p_recipe_id IN NUMBER,
p_last_update_date_orig IN DATE,
x_return_code OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR Get_Recipe IS
SELECT *
FROM
gmd_recipes
WHERE
recipe_id = p_recipe_id AND
last_update_date = p_last_update_date_orig;
l_recipe_flex GMD_RECIPE_HEADER.update_flex;
DELETE_RECIPE_EXCEPTION EXCEPTION;
l_recipe_hdr.DELETE_MARK := 0;
l_recipe_hdr.LAST_UPDATED_BY := l_user_id;
l_recipe_hdr.LAST_UPDATE_DATE := SYSDATE;
l_recipe_hdr.LAST_UPDATE_LOGIN := l_login_id;
GMD_RECIPE_HEADER_PVT.Delete_Recipe_Header
( p_recipe_header_rec => l_recipe_hdr
,p_flex_header_rec => l_recipe_flex
,x_return_status => l_status);
RAISE DELETE_RECIPE_EXCEPTION;
WHEN DELETE_RECIPE_EXCEPTION THEN
FND_MSG_PUB.GET( p_msg_index => 1,
p_data => l_message,
p_encoded => 'F',
p_msg_index_out => l_dummy);
END Undelete_Recipe;
SELECT *
FROM gmd_recipes
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM gmd_recipe_process_parameters
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM gmd_recipe_validity_rules
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM gmd_recipe_orgn_activities
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM gmd_recipe_orgn_resources
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM gmd_recipe_routing_steps
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM gmd_recipe_customers
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM gmd_recipe_process_loss
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM gmd_recipe_step_materials
WHERE recipe_id = p_copy_from_recipe_id;
SELECT *
FROM fm_form_mst
WHERE formula_id = p_copy_from_formula_id;
SELECT *
FROM fm_matl_dtl
WHERE formula_id = p_copy_from_formula_id;
SELECT *
FROM gmd_routings
WHERE routing_id = p_copy_from_routing_id;
SELECT *
FROM fm_rout_dtl
WHERE routing_id = p_copy_from_routing_id;
SELECT *
FROM fm_rout_dep
WHERE routing_id = p_copy_from_routing_id;
SELECT *
FROM fm_text_tbl
WHERE text_code = p_text_code AND
line_no <> -1;
SELECT gem5_text_code_s.NEXTVAL
FROM sys.dual;
SELECT gmd_recipe_id_s.NEXTVAL
FROM FND_DUAL;
SELECT gmd_recipe_process_loss_id_s.NEXTVAL
FROM FND_DUAL;
SELECT gmd_recipe_validity_id_s.NEXTVAL
FROM FND_DUAL;
l_routing_update_allowed BOOLEAN;
l_rtg_upd_tbl GMD_ROUTINGS_PUB.update_tbl_type;
l_recipe_update_flex GMD_RECIPE_HEADER.flex;
DELETE fm_text_tbl WHERE text_code = l_text_code;
l_frm_hdr_rec.last_update_login := l_login_id;
l_frm_hdr_rec.delete_mark := 0;
GMD_FORMULA_HEADER_PVT.Insert_FormulaHeader
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_code
,x_msg_count => l_message_count
,x_msg_data => l_message_list
,p_formula_header_rec => l_frm_hdr_rec
);
GMD_FORMULA_HEADER_PVT.Update_FormulaHeader
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_code
,x_msg_count => l_message_count
,x_msg_data => l_message_list
,p_formula_header_rec => l_frm_hdr_rec
);
DELETE fm_matl_dtl WHERE formula_id = x_formula_id;
DELETE fm_text_tbl WHERE text_code = l_text_code;
GMD_FORMULA_DETAIL_PVT.Insert_FormulaDetail
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_code
,x_msg_count => l_message_count
,x_msg_data => l_message_list
,p_formula_detail_rec => l_frm_dtl_tbl(i)
);
l_routing_update_allowed := TRUE;
l_routing_update_allowed := GMD_COMMON_VAL.UPDATE_ALLOWED(
Entity => 'ROUTING',
Entity_id => l_rcp_hdr_rec.routing_id);
IF l_copy_routing OR l_routing_update_allowed THEN
IF l_copy_routing THEN
x_routing_id := GMDSURG.get_surrogate('routing_id');
DELETE fm_text_tbl WHERE text_code = l_text_code;
l_rtg_hdr_rec.last_update_login := l_login_id;
GMD_ROUTINGS_PVT.Insert_Routing ( p_routings => l_rtg_hdr_rec
,x_message_count => l_message_count
,x_message_list => l_message_list
,x_return_status => l_return_code);
GMD_ROUTING_DESIGNER_PKG.Update_Routing_Header
( p_routing_id => x_routing_id,
p_routing_no => p_routing_no,
p_routing_vers => p_routing_vers,
p_routing_desc => p_routing_desc,
p_routing_class => l_rtg_hdr_rec.routing_class,
p_effective_start_date => l_rtg_hdr_rec.effective_start_date,
p_effective_end_date => l_rtg_hdr_rec.effective_end_date,
p_routing_qty => l_rtg_hdr_rec.routing_qty,
p_routing_uom => l_rtg_hdr_rec.routing_uom,
p_process_loss => l_rtg_hdr_rec.process_loss,
p_owner_id => l_rtg_hdr_rec.owner_id,
p_owner_orgn_id => l_rtg_hdr_rec.owner_organization_id,
p_enforce_step_dep => l_rtg_hdr_rec.enforce_step_dependency,
p_last_update_date => l_rtg_hdr_rec.last_update_date,
p_user_id => l_user_id,
p_last_update_date_orig => l_rtg_hdr_rec.last_update_date,
p_update_release_type => 0,
p_contiguous_ind => l_rtg_hdr_rec.contiguous_ind,
x_return_code => l_return_code,
x_error_msg => x_error_msg);
DELETE fm_rout_dep WHERE routing_id = x_routing_id;
DELETE fm_rout_dtl WHERE routing_id = x_routing_id;
DELETE fm_text_tbl WHERE text_code = l_text_code;
GMD_ROUTING_STEPS_PVT.Insert_Routing_Steps
( p_routing_id => x_routing_id
,p_routing_step_rec => l_rtg_dtl_tbl(i)
,x_return_status => l_return_code);
INSERT INTO fm_rout_dep
(ROUTINGSTEP_NO,
DEP_ROUTINGSTEP_NO,
ROUTING_ID,
DEP_TYPE,
REWORK_CODE,
STANDARD_DELAY,
MINIMUM_DELAY,
MAX_DELAY,
TRANSFER_QTY,
ROUTINGSTEP_NO_UOM,
TEXT_CODE,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
LAST_UPDATE_LOGIN,
TRANSFER_PCT)
VALUES (l_step_dep_tbl(i).ROUTINGSTEP_NO,
l_step_dep_tbl(i).DEP_ROUTINGSTEP_NO,
x_routing_id,
l_step_dep_tbl(i).DEP_TYPE,
l_step_dep_tbl(i).REWORK_CODE,
l_step_dep_tbl(i).STANDARD_DELAY,
l_step_dep_tbl(i).MINIMUM_DELAY,
l_step_dep_tbl(i).MAX_DELAY,
l_step_dep_tbl(i).TRANSFER_QTY,
l_step_dep_tbl(i).ROUTINGSTEP_NO_UOM,
l_step_dep_tbl(i).TEXT_CODE,
l_user_id,
l_user_id,
SYSDATE,
SYSDATE,
l_login_id,
l_step_dep_tbl(i).TRANSFER_PCT);
DELETE fm_text_tbl WHERE text_code = l_text_code;
DELETE gmd_recipes_b WHERE recipe_id = p_copy_from_recipe_id;
l_recipe_tbl.DELETE_MARK := 0;
l_recipe_tbl.LAST_UPDATED_BY := l_user_id;
l_recipe_tbl.LAST_UPDATE_DATE := SYSDATE;
l_recipe_tbl.LAST_UPDATE_LOGIN := l_login_id;
l_recipe_update_flex.ATTRIBUTE_CATEGORY := l_rcp_hdr_rec.attribute_category;
l_recipe_update_flex.ATTRIBUTE1 := l_rcp_hdr_rec.attribute1;
l_recipe_update_flex.ATTRIBUTE2 := l_rcp_hdr_rec.attribute2;
l_recipe_update_flex.ATTRIBUTE3 := l_rcp_hdr_rec.attribute3;
l_recipe_update_flex.ATTRIBUTE4 := l_rcp_hdr_rec.attribute4;
l_recipe_update_flex.ATTRIBUTE5 := l_rcp_hdr_rec.attribute5;
l_recipe_update_flex.ATTRIBUTE6 := l_rcp_hdr_rec.attribute6;
l_recipe_update_flex.ATTRIBUTE7 := l_rcp_hdr_rec.attribute7;
l_recipe_update_flex.ATTRIBUTE8 := l_rcp_hdr_rec.attribute8;
l_recipe_update_flex.ATTRIBUTE9 := l_rcp_hdr_rec.attribute9;
l_recipe_update_flex.ATTRIBUTE10 := l_rcp_hdr_rec.attribute10;
l_recipe_update_flex.ATTRIBUTE11 := l_rcp_hdr_rec.attribute11;
l_recipe_update_flex.ATTRIBUTE12 := l_rcp_hdr_rec.attribute12;
l_recipe_update_flex.ATTRIBUTE13 := l_rcp_hdr_rec.attribute13;
l_recipe_update_flex.ATTRIBUTE14 := l_rcp_hdr_rec.attribute14;
l_recipe_update_flex.ATTRIBUTE15 := l_rcp_hdr_rec.attribute15;
l_recipe_update_flex.ATTRIBUTE16 := l_rcp_hdr_rec.attribute16;
l_recipe_update_flex.ATTRIBUTE17 := l_rcp_hdr_rec.attribute17;
l_recipe_update_flex.ATTRIBUTE18 := l_rcp_hdr_rec.attribute18;
l_recipe_update_flex.ATTRIBUTE19 := l_rcp_hdr_rec.attribute19;
l_recipe_update_flex.ATTRIBUTE20 := l_rcp_hdr_rec.attribute20;
l_recipe_update_flex.ATTRIBUTE21 := l_rcp_hdr_rec.attribute21;
l_recipe_update_flex.ATTRIBUTE22 := l_rcp_hdr_rec.attribute22;
l_recipe_update_flex.ATTRIBUTE23 := l_rcp_hdr_rec.attribute23;
l_recipe_update_flex.ATTRIBUTE24 := l_rcp_hdr_rec.attribute24;
l_recipe_update_flex.ATTRIBUTE25 := l_rcp_hdr_rec.attribute25;
l_recipe_update_flex.ATTRIBUTE26 := l_rcp_hdr_rec.attribute26;
l_recipe_update_flex.ATTRIBUTE27 := l_rcp_hdr_rec.attribute27;
l_recipe_update_flex.ATTRIBUTE28 := l_rcp_hdr_rec.attribute28;
l_recipe_update_flex.ATTRIBUTE29 := l_rcp_hdr_rec.attribute29;
l_recipe_update_flex.ATTRIBUTE30 := l_rcp_hdr_rec.attribute30;
,p_recipe_hdr_flex_rec => l_recipe_update_flex
,x_return_status => l_return_code);
DELETE gmd_recipe_routing_steps WHERE recipe_id = p_copy_from_recipe_id;
DELETE fm_text_tbl WHERE text_code = l_text_code;
INSERT INTO gmd_recipe_routing_steps
(recipe_id,
routingstep_id,
step_qty,
created_by,
creation_date,
last_update_date,
last_update_login,
text_code,
last_updated_by,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
mass_std_uom,
volume_std_uom,
volume_qty, mass_qty)
VALUES
(x_recipe_id,
l_rcp_stp_tbl(i).routingstep_id,
l_rcp_stp_tbl(i).step_qty,
l_user_id,
SYSDATE,
SYSDATE,
l_login_id,
l_rcp_stp_tbl(i).text_code,
l_user_id,
l_rcp_stp_tbl(i).attribute1,
l_rcp_stp_tbl(i).attribute2,
l_rcp_stp_tbl(i).attribute3,
l_rcp_stp_tbl(i).attribute4,
l_rcp_stp_tbl(i).attribute5,
l_rcp_stp_tbl(i).attribute6,
l_rcp_stp_tbl(i).attribute7,
l_rcp_stp_tbl(i).attribute8,
l_rcp_stp_tbl(i).attribute9,
l_rcp_stp_tbl(i).attribute10,
l_rcp_stp_tbl(i).attribute11,
l_rcp_stp_tbl(i).attribute12,
l_rcp_stp_tbl(i).attribute13,
l_rcp_stp_tbl(i).attribute14,
l_rcp_stp_tbl(i).attribute15,
l_rcp_stp_tbl(i).attribute16,
l_rcp_stp_tbl(i).attribute17,
l_rcp_stp_tbl(i).attribute18,
l_rcp_stp_tbl(i).attribute19,
l_rcp_stp_tbl(i).attribute20,
l_rcp_stp_tbl(i).attribute21,
l_rcp_stp_tbl(i).attribute22,
l_rcp_stp_tbl(i).attribute23,
l_rcp_stp_tbl(i).attribute24,
l_rcp_stp_tbl(i).attribute25,
l_rcp_stp_tbl(i).attribute26,
l_rcp_stp_tbl(i).attribute27,
l_rcp_stp_tbl(i).attribute28,
l_rcp_stp_tbl(i).attribute29,
l_rcp_stp_tbl(i).attribute30,
l_rcp_stp_tbl(i).attribute_category,
l_rcp_stp_tbl(i).mass_std_uom,
l_rcp_stp_tbl(i).volume_std_uom,
l_rcp_stp_tbl(i).volume_qty,
l_rcp_stp_tbl(i).mass_qty);
DELETE gmd_recipe_step_materials WHERE recipe_id = p_copy_from_recipe_id;
DELETE fm_text_tbl WHERE text_code = l_text_code;
p_last_update_date => SYSDATE,
x_return_code => x_return_code,
x_error_msg => x_error_msg);
DELETE gmd_recipe_customers WHERE recipe_id = p_copy_from_recipe_id;
DELETE fm_text_tbl WHERE text_code = l_text_code;
p_last_update_date => SYSDATE,
x_return_code => x_return_code,
x_error_msg => x_error_msg);
DELETE gmd_recipe_process_loss WHERE recipe_id = p_copy_from_recipe_id;
DELETE fm_text_tbl WHERE text_code = l_text_code;
p_last_update_date => SYSDATE,
p_loss_id => l_loss_id,
x_loss_id => l_dummy,
x_return_code => x_return_code,
x_error_msg => x_error_msg);
INSERT INTO gmd_recipe_validity_rules
( recipe_validity_rule_id,
recipe_id,
organization_id,
inventory_item_id,
recipe_use,
preference,
start_date,
end_date,
min_qty,
max_qty,
std_qty,
detail_uom,
inv_min_qty,
inv_max_qty,
text_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
delete_mark,
lab_type,
validity_rule_status)
VALUES
( l_vr_id,
x_recipe_id,
l_rcp_vr_tbl(i).organization_id,
l_rcp_vr_tbl(i).inventory_item_id,
l_rcp_vr_tbl(i).recipe_use,
l_rcp_vr_tbl(i).preference,
l_rcp_vr_tbl(i).start_date,
l_rcp_vr_tbl(i).end_date,
l_rcp_vr_tbl(i).min_qty,
l_rcp_vr_tbl(i).max_qty,
l_rcp_vr_tbl(i).std_qty,
l_rcp_vr_tbl(i).detail_uom,
l_rcp_vr_tbl(i).inv_min_qty,
l_rcp_vr_tbl(i).inv_max_qty,
l_text_code,
l_rcp_vr_tbl(i).attribute_category,
l_rcp_vr_tbl(i).attribute1,
l_rcp_vr_tbl(i).attribute2,
l_rcp_vr_tbl(i).attribute3,
l_rcp_vr_tbl(i).attribute4,
l_rcp_vr_tbl(i).attribute5,
l_rcp_vr_tbl(i).attribute6,
l_rcp_vr_tbl(i).attribute7,
l_rcp_vr_tbl(i).attribute8,
l_rcp_vr_tbl(i).attribute9,
l_rcp_vr_tbl(i).attribute10,
l_rcp_vr_tbl(i).attribute11,
l_rcp_vr_tbl(i).attribute12,
l_rcp_vr_tbl(i).attribute13,
l_rcp_vr_tbl(i).attribute14,
l_rcp_vr_tbl(i).attribute15,
l_rcp_vr_tbl(i).attribute16,
l_rcp_vr_tbl(i).attribute17,
l_rcp_vr_tbl(i).attribute18,
l_rcp_vr_tbl(i).attribute19,
l_rcp_vr_tbl(i).attribute20,
l_rcp_vr_tbl(i).attribute21,
l_rcp_vr_tbl(i).attribute22,
l_rcp_vr_tbl(i).attribute23,
l_rcp_vr_tbl(i).attribute24,
l_rcp_vr_tbl(i).attribute25,
l_rcp_vr_tbl(i).attribute26,
l_rcp_vr_tbl(i).attribute27,
l_rcp_vr_tbl(i).attribute28,
l_rcp_vr_tbl(i).attribute29,
l_rcp_vr_tbl(i).attribute30,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_login_id,
l_rcp_vr_tbl(i).delete_mark,
l_rcp_vr_tbl(i).lab_type,
100);
DELETE gmd_recipe_orgn_resources WHERE recipe_id = p_copy_from_recipe_id;
INSERT INTO gmd_recipe_orgn_resources
( recipe_id,
organization_id,
routingstep_id,
oprn_line_id,
resources,
creation_date,
created_by,
last_updated_by,
last_update_date,
min_capacity,
max_capacity,
last_update_login,
text_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
process_parameter_5,
process_parameter_4,
process_parameter_3,
process_parameter_2,
process_parameter_1,
process_uom,
usage_um,
resource_usage,
process_qty)
VALUES
( x_recipe_id,
l_rcp_rsrc_tbl(i).organization_id,
l_rcp_rsrc_tbl(i).routingstep_id,
l_rcp_rsrc_tbl(i).oprn_line_id,
l_rcp_rsrc_tbl(i).resources,
SYSDATE,
l_user_id,
l_user_id,
SYSDATE,
l_rcp_rsrc_tbl(i).min_capacity,
l_rcp_rsrc_tbl(i).max_capacity,
l_login_id,
l_text_code,
l_rcp_rsrc_tbl(i).attribute1,
l_rcp_rsrc_tbl(i).attribute2,
l_rcp_rsrc_tbl(i).attribute3,
l_rcp_rsrc_tbl(i).attribute4,
l_rcp_rsrc_tbl(i).attribute5,
l_rcp_rsrc_tbl(i).attribute6,
l_rcp_rsrc_tbl(i).attribute7,
l_rcp_rsrc_tbl(i).attribute8,
l_rcp_rsrc_tbl(i).attribute9,
l_rcp_rsrc_tbl(i).attribute10,
l_rcp_rsrc_tbl(i).attribute11,
l_rcp_rsrc_tbl(i).attribute12,
l_rcp_rsrc_tbl(i).attribute13,
l_rcp_rsrc_tbl(i).attribute14,
l_rcp_rsrc_tbl(i).attribute15,
l_rcp_rsrc_tbl(i).attribute16,
l_rcp_rsrc_tbl(i).attribute17,
l_rcp_rsrc_tbl(i).attribute18,
l_rcp_rsrc_tbl(i).attribute19,
l_rcp_rsrc_tbl(i).attribute20,
l_rcp_rsrc_tbl(i).attribute21,
l_rcp_rsrc_tbl(i).attribute22,
l_rcp_rsrc_tbl(i).attribute23,
l_rcp_rsrc_tbl(i).attribute24,
l_rcp_rsrc_tbl(i).attribute25,
l_rcp_rsrc_tbl(i).attribute26,
l_rcp_rsrc_tbl(i).attribute27,
l_rcp_rsrc_tbl(i).attribute28,
l_rcp_rsrc_tbl(i).attribute29,
l_rcp_rsrc_tbl(i).attribute30,
l_rcp_rsrc_tbl(i).attribute_category,
l_rcp_rsrc_tbl(i).process_parameter_5,
l_rcp_rsrc_tbl(i).process_parameter_4,
l_rcp_rsrc_tbl(i).process_parameter_3,
l_rcp_rsrc_tbl(i).process_parameter_2,
l_rcp_rsrc_tbl(i).process_parameter_1,
l_rcp_rsrc_tbl(i).process_uom,
l_rcp_rsrc_tbl(i).usage_um,
l_rcp_rsrc_tbl(i).resource_usage,
l_rcp_rsrc_tbl(i).process_qty);
DELETE gmd_recipe_orgn_activities
WHERE recipe_id = p_copy_from_recipe_id;
INSERT INTO gmd_recipe_orgn_activities
( recipe_id,
routingstep_id,
oprn_line_id,
activity_factor,
orgn_code,
organization_id,
last_update_login,
text_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category)
VALUES
( x_recipe_id,
l_rcp_actv_tbl(i).routingstep_id,
l_rcp_actv_tbl(i).oprn_line_id,
l_rcp_actv_tbl(i).activity_factor,
l_rcp_rsrc_tbl(i).orgn_code,
l_rcp_rsrc_tbl(i).organization_id,
l_login_id,
l_text_code,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE,
l_rcp_actv_tbl(i).attribute1,
l_rcp_actv_tbl(i).attribute2,
l_rcp_actv_tbl(i).attribute3,
l_rcp_actv_tbl(i).attribute4,
l_rcp_actv_tbl(i).attribute5,
l_rcp_actv_tbl(i).attribute6,
l_rcp_actv_tbl(i).attribute7,
l_rcp_actv_tbl(i).attribute8,
l_rcp_actv_tbl(i).attribute9,
l_rcp_actv_tbl(i).attribute10,
l_rcp_actv_tbl(i).attribute11,
l_rcp_actv_tbl(i).attribute12,
l_rcp_actv_tbl(i).attribute13,
l_rcp_actv_tbl(i).attribute14,
l_rcp_actv_tbl(i).attribute15,
l_rcp_actv_tbl(i).attribute16,
l_rcp_actv_tbl(i).attribute17,
l_rcp_actv_tbl(i).attribute18,
l_rcp_actv_tbl(i).attribute19,
l_rcp_actv_tbl(i).attribute20,
l_rcp_actv_tbl(i).attribute21,
l_rcp_actv_tbl(i).attribute22,
l_rcp_actv_tbl(i).attribute23,
l_rcp_actv_tbl(i).attribute24,
l_rcp_actv_tbl(i).attribute25,
l_rcp_actv_tbl(i).attribute26,
l_rcp_actv_tbl(i).attribute27,
l_rcp_actv_tbl(i).attribute28,
l_rcp_actv_tbl(i).attribute29,
l_rcp_actv_tbl(i).attribute30,
l_rcp_actv_tbl(i).attribute_category);
DELETE gmd_recipe_process_parameters
WHERE recipe_id = p_copy_from_recipe_id;
INSERT INTO gmd_recipe_process_parameters
( recipe_id,
organization_id,
routingstep_id,
oprn_line_id,
resources,
parameter_id,
target_value,
minimum_value,
maximum_value,
last_update_login,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES
( x_recipe_id,
l_rcp_pp_tbl(i).organization_id,
l_rcp_pp_tbl(i).routingstep_id,
l_rcp_pp_tbl(i).oprn_line_id,
l_rcp_pp_tbl(i).resources,
l_rcp_pp_tbl(i).parameter_id,
l_rcp_pp_tbl(i).target_value,
l_rcp_pp_tbl(i).minimum_value,
l_rcp_pp_tbl(i).maximum_value,
l_login_id,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE);
SELECT owner_organization_id
FROM gmd_recipes_b
WHERE recipe_id = p_recipe_id;
SELECT formula_id, owner_organization_id
FROM gmd_recipes_b
WHERE recipe_id = l_recipe_id;
SELECT owner_organization_id
FROM fm_form_mst_b
WHERE formula_id = vFormula_id;
SELECT owner_organization_id
FROM gmd_recipes_b
WHERE recipe_id = vRecipe_id;
SELECT owner_organization_id
FROM gmd_routings_b
WHERE routing_id = vRouting_id;