The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inventory_item_id FROM mtl_system_items_b WHERE
inventory_item_id= p_item_id AND organization_id= p_org_id;
SELECT recipe_enabled_flag INTO l_temp FROM mtl_system_items_b WHERE inventory_item_id=p_formula_obj.formula_details(j).inventory_item_id
AND organization_id=p_formula_obj.formula_details(j).owner_organization_id;
/* update_org_ids */
/* */
/* DESCRIPTION: */
/* This procedure converts organization code to organization ids. */
/* ======================================================================== */
PROCEDURE update_org_ids(
p_formula_obj IN gmd_ebi_formula_obj
,x_formula_obj OUT NOCOPY gmd_ebi_formula_obj
,x_msg_count OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_org_id NUMBER;
SELECT organization_id INTO l_org_id FROM mtl_parameters
WHERE organization_code = l_formula_obj.formula_header.OWNER_ORGANIZATION_CODE;
PROCEDURE update_item_ids(
p_formula_obj IN gmd_ebi_formula_obj
,x_formula_obj OUT NOCOPY gmd_ebi_formula_obj
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_item_id NUMBER;
SELECT inventory_item_id INTO l_item_id FROM mtl_system_items_kfv
WHERE concatenated_segments = l_formula_obj.formula_details(j).item_no
AND organization_id = l_formula_obj.formula_details(j).owner_organization_id;
/* update_org_ids */
/* */
/* DESCRIPTION: */
/* This procedure populates the output object with the entity ids and */
/* corresponding business component ids. */
/* ======================================================================== */
PROCEDURE populate_output(
p_input IN gmd_ebi_formula_obj
,p_line_delete_tbl IN gmd_ebi_formula_line_del_tbl
,status_flag IN VARCHAR2
,x_output OUT NOCOPY gmd_ebi_formula_output_obj
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_out_obj gmd_ebi_formula_output_obj;
SELECT Max(formula_id) INTO l_form_id FROM fm_form_mst_b WHERE formula_no = p_input.formula_header.formula_no;
SELECT formulaline_id INTO l_form_line_id FROM fm_matl_dtl WHERE formula_id = l_form_id
AND line_type=p_input.formula_details(j).line_type AND line_no = p_input.formula_details(j).line_no;
l_out_obj := gmd_ebi_formula_output_obj(p_input.formula_header.business_component_id,l_form_id,l_out_line_sync_tbl,p_line_delete_tbl);
SELECT organization_id INTO l_org_id
FROM MTL_PARAMETERS
WHERE organization_code=p_org_code;
/* The procedure creates/updates routings and routing step dependencies */
/* ======================================================================== */
PROCEDURE sync_routings(
p_routing_obj IN gmd_ebi_mfg_routing_obj
,p_commit IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_routings_insert gmd_routings%ROWTYPE;
l_routings_step_tbl_insert GMD_ROUTINGS_PUB.gmd_routings_step_tab;
l_routings_step_dep_tbl_insert GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab;
l_routings_step_insert_ctr NUMBER :=1;
l_routings_step_dep_insert_ctr NUMBER :=1;
l_rout_tbl GMD_ROUTINGS_PUB.update_tbl_type;
l_rout_rec GMD_ROUTINGS_PUB.update_table_rec_type;
l_routings_insert.ROUTING_STATUS := p_routing_obj.ROUTING_STATUS;
l_routings_insert.ENFORCE_STEP_DEPENDENCY := p_routing_obj.ENFORCE_STEP_DEPENDENCY;
l_routings_insert.ROUTING_DESC := p_routing_obj.ROUTING_DESC;
l_routings_insert.FIXED_PROCESS_LOSS := p_routing_obj.FIXED_PROCESS_LOSS ;
l_routings_insert.FIXED_PROCESS_LOSS_UOM := p_routing_obj.FIXED_PROCESS_LOSS_UOM;
l_routings_insert.OWNER_ORGANIZATION_ID := get_org_id(p_routing_obj.OWNER_ORGANIZATION_CODE);
l_routings_insert.ROUTING_ID := p_routing_obj.ROUTING_ID;
l_routings_insert.ROUTING_NO := p_routing_obj.ROUTING_NO;
l_routings_insert.ROUTING_VERS := p_routing_obj.ROUTING_VERS;
l_routings_insert.ROUTING_CLASS := p_routing_obj.ROUTING_CLASS ;
l_routings_insert.ROUTING_QTY := p_routing_obj.ROUTING_QTY;
l_routings_insert.ITEM_UM := p_routing_obj.ITEM_UM ;
l_routings_insert.ROUTING_UOM := p_routing_obj.ROUTING_UOM ;
l_routings_insert.CONTIGUOUS_IND := p_routing_obj.CONTIGUOUS_IND;
l_routings_insert.DELETE_MARK := p_routing_obj.DELETE_MARK;
l_routings_insert.TEXT_CODE := p_routing_obj.TEXT_CODE;
l_routings_insert.INACTIVE_IND := p_routing_obj.INACTIVE_IND ;
l_routings_insert.IN_USE := p_routing_obj.IN_USE;
l_routings_insert.CREATION_DATE := Nvl(p_routing_obj.CREATION_DATE,SYSDATE);
l_routings_insert.CREATED_BY := Nvl(p_routing_obj.CREATED_BY,FND_GLOBAL.USER_ID);
l_routings_insert.LAST_UPDATE_LOGIN := Nvl(p_routing_obj.LAST_UPDATE_LOGIN ,FND_GLOBAL.USER_ID);
l_routings_insert.LAST_UPDATE_DATE := Nvl(p_routing_obj.LAST_UPDATE_DATE,SYSDATE);
l_routings_insert.LAST_UPDATED_BY := Nvl(p_routing_obj.LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
l_routings_insert.ATTRIBUTE_CATEGORY :=p_routing_obj.flex_obj.ATTRIBUTE_CATEGORY;
l_routings_insert.ATTRIBUTE_CATEGORY:=p_routing_obj.flex_obj.ATTRIBUTE_CATEGORY;
l_routings_insert.ATTRIBUTE1:=p_routing_obj.flex_obj.ATTRIBUTE1;
l_routings_insert.ATTRIBUTE2:=p_routing_obj.flex_obj.ATTRIBUTE2;
l_routings_insert.ATTRIBUTE3:=p_routing_obj.flex_obj.ATTRIBUTE3;
l_routings_insert.ATTRIBUTE4:=p_routing_obj.flex_obj.ATTRIBUTE4;
l_routings_insert.ATTRIBUTE5:=p_routing_obj.flex_obj.ATTRIBUTE5;
l_routings_insert.ATTRIBUTE6:=p_routing_obj.flex_obj.ATTRIBUTE6;
l_routings_insert.ATTRIBUTE7:=p_routing_obj.flex_obj.ATTRIBUTE7;
l_routings_insert.ATTRIBUTE8:=p_routing_obj.flex_obj.ATTRIBUTE8;
l_routings_insert.ATTRIBUTE9:=p_routing_obj.flex_obj.ATTRIBUTE9;
l_routings_insert.ATTRIBUTE10:=p_routing_obj.flex_obj.ATTRIBUTE10;
l_routings_insert.ATTRIBUTE11:=p_routing_obj.flex_obj.ATTRIBUTE11;
l_routings_insert.ATTRIBUTE12:=p_routing_obj.flex_obj.ATTRIBUTE12;
l_routings_insert.ATTRIBUTE13:=p_routing_obj.flex_obj.ATTRIBUTE13;
l_routings_insert.ATTRIBUTE14:=p_routing_obj.flex_obj.ATTRIBUTE14;
l_routings_insert.ATTRIBUTE15:=p_routing_obj.flex_obj.ATTRIBUTE15;
l_routings_insert.ATTRIBUTE16:=p_routing_obj.flex_obj.ATTRIBUTE16;
l_routings_insert.ATTRIBUTE17:=p_routing_obj.flex_obj.ATTRIBUTE17;
l_routings_insert.ATTRIBUTE18:=p_routing_obj.flex_obj.ATTRIBUTE18;
l_routings_insert.ATTRIBUTE19:=p_routing_obj.flex_obj.ATTRIBUTE19;
l_routings_insert.ATTRIBUTE20:=p_routing_obj.flex_obj.ATTRIBUTE20;
l_routings_insert.ATTRIBUTE21:=p_routing_obj.flex_obj.ATTRIBUTE21;
l_routings_insert.ATTRIBUTE22:=p_routing_obj.flex_obj.ATTRIBUTE22;
l_routings_insert.ATTRIBUTE23:=p_routing_obj.flex_obj.ATTRIBUTE23;
l_routings_insert.ATTRIBUTE24:=p_routing_obj.flex_obj.ATTRIBUTE24;
l_routings_insert.ATTRIBUTE25:=p_routing_obj.flex_obj.ATTRIBUTE25;
l_routings_insert.ATTRIBUTE26:=p_routing_obj.flex_obj.ATTRIBUTE26;
l_routings_insert.ATTRIBUTE27:=p_routing_obj.flex_obj.ATTRIBUTE27;
l_routings_insert.ATTRIBUTE28:=p_routing_obj.flex_obj.ATTRIBUTE28;
l_routings_insert.ATTRIBUTE29:=p_routing_obj.flex_obj.ATTRIBUTE29;
l_routings_insert.ATTRIBUTE30:=p_routing_obj.flex_obj.ATTRIBUTE30;
l_routings_insert.EFFECTIVE_START_DATE := p_routing_obj.EFFECTIVE_START_DATE;
l_routings_insert.EFFECTIVE_END_DATE := p_routing_obj.EFFECTIVE_END_DATE;
l_routings_insert.OWNER_ID := Nvl(p_routing_obj.OWNER_ID,FND_GLOBAL.USER_ID);
l_routings_insert.PROJECT_ID := p_routing_obj.PROJECT_ID ;
l_routings_insert.PROCESS_LOSS := p_routing_obj.PROCESS_LOSS ;
l_routings_step_rec.LAST_UPDATED_BY:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
l_routings_step_rec.LAST_UPDATE_DATE:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATE_DATE,SYSDATE);
l_routings_step_rec.LAST_UPDATE_LOGIN:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
l_routings_step_tbl_insert(l_routings_step_insert_ctr) := l_routings_step_rec;
l_routings_step_insert_ctr := l_routings_step_insert_ctr + 1;
l_routings_step_dep_rec.LAST_UPDATED_BY := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
l_routings_step_dep_rec.LAST_UPDATE_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_DATE,SYSDATE);
l_routings_step_dep_rec.LAST_UPDATE_LOGIN := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
l_routings_step_dep_tbl_insert(l_routings_step_dep_insert_ctr) := l_routings_step_dep_rec;
l_routings_step_dep_insert_ctr := l_routings_step_dep_insert_ctr + 1;
GMD_ROUTINGS_PUB.insert_routing(
p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routings => l_routings_insert
, p_routings_step_tbl => l_routings_step_tbl_insert
, p_routings_step_dep_tbl => l_routings_step_dep_tbl_insert
, x_message_count => l_msg_count
, x_message_list => l_msg
, x_return_status => l_ret_status
);
l_rout_rec.p_col_to_update := 'routing_status';
l_rout_rec.p_col_to_update := 'enforce_step_dependency';
l_rout_rec.p_col_to_update := 'routing_desc';
l_rout_rec.p_col_to_update := 'fixed_process_loss';
l_rout_rec.p_col_to_update := 'fixed_process_loss_uom';
l_rout_rec.p_col_to_update := 'owner_organization_id';
l_rout_rec.p_col_to_update := 'routing_id';
l_rout_rec.p_col_to_update := 'routing_no';
l_rout_rec.p_col_to_update := 'routing_vers';
l_rout_rec.p_col_to_update := 'routing_class';
l_rout_rec.p_col_to_update := 'routing_qty';
l_rout_rec.p_col_to_update := 'item_um';
l_rout_rec.p_col_to_update := 'routing_uom';
l_rout_rec.p_col_to_update := 'contiguous_ind';
l_rout_rec.p_col_to_update := 'delete_mark';
l_rout_rec.p_value := p_routing_obj.delete_mark;
l_rout_rec.p_col_to_update := 'text_code';
l_rout_rec.p_col_to_update := 'inactive_ind';
l_rout_rec.p_col_to_update := 'in_use';
l_rout_rec.p_col_to_update := 'creation_date';
l_rout_rec.p_col_to_update := 'created_by';
l_rout_rec.p_col_to_update := 'last_update_login';
l_rout_rec.p_value := p_routing_obj.last_update_login;
l_rout_rec.p_col_to_update := 'last_update_date';
l_rout_rec.p_value := p_routing_obj.last_update_date;
l_rout_rec.p_col_to_update := 'last_updated_by';
l_rout_rec.p_value := p_routing_obj.last_updated_by;
l_rout_rec.p_col_to_update := 'effective_start_date';
l_rout_rec.p_col_to_update := 'effective_end_date';
l_rout_rec.p_col_to_update := 'owner_id';
l_rout_rec.p_col_to_update := 'project_id';
l_rout_rec.p_col_to_update := 'process_loss';
l_rout_rec.p_col_to_update := 'attribute_category';
l_rout_rec.p_col_to_update := 'attribute1';
l_rout_rec.p_col_to_update := 'attribute2';
l_rout_rec.p_col_to_update := 'attribute3';
l_rout_rec.p_col_to_update := 'attribute4';
l_rout_rec.p_col_to_update := 'attribute5';
l_rout_rec.p_col_to_update := 'attribute6';
l_rout_rec.p_col_to_update := 'attribute7';
l_rout_rec.p_col_to_update := 'attribute8';
l_rout_rec.p_col_to_update := 'attribute9';
l_rout_rec.p_col_to_update := 'attribute10';
l_rout_rec.p_col_to_update := 'attribute11';
l_rout_rec.p_col_to_update := 'attribute12';
l_rout_rec.p_col_to_update := 'attribute13';
l_rout_rec.p_col_to_update := 'attribute14';
l_rout_rec.p_col_to_update := 'attribute15';
l_rout_rec.p_col_to_update := 'attribute16';
l_rout_rec.p_col_to_update := 'attribute17';
l_rout_rec.p_col_to_update := 'attribute18';
l_rout_rec.p_col_to_update := 'attribute19';
l_rout_rec.p_col_to_update := 'attribute20';
l_rout_rec.p_col_to_update := 'attribute21';
l_rout_rec.p_col_to_update := 'attribute22';
l_rout_rec.p_col_to_update := 'attribute23';
l_rout_rec.p_col_to_update := 'attribute24';
l_rout_rec.p_col_to_update := 'attribute25';
l_rout_rec.p_col_to_update := 'attribute26';
l_rout_rec.p_col_to_update := 'attribute27';
l_rout_rec.p_col_to_update := 'attribute28';
l_rout_rec.p_col_to_update := 'attribute29';
l_rout_rec.p_col_to_update := 'attribute30';
GMD_ROUTINGS_PUB.update_routing
( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => FALSE
, p_routing_id => p_routing_obj.ROUTING_ID
, p_routing_no => p_routing_obj.ROUTING_NO
, p_routing_vers => p_routing_obj.ROUTING_VERS
, p_update_table => l_rout_tbl
, x_message_count => l_msg_count
, x_message_list => l_msg
, x_return_status => l_ret_status
);
l_routings_step_rec.LAST_UPDATED_BY:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
l_routings_step_rec.LAST_UPDATE_DATE:=NVL(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATE_DATE,SYSDATE);
l_routings_step_rec.LAST_UPDATE_LOGIN:=Nvl(p_routing_obj.gmd_ebi_routing_steps(j).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
IF l_routings_step_dep_tbl_insert.Count > 1 THEN
l_routings_step_dep_tbl_insert.DELETE;
l_routings_step_dep_insert_ctr := 1;
l_routings_step_dep_rec.LAST_UPDATED_BY := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
l_routings_step_dep_rec.LAST_UPDATE_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_DATE,SYSDATE);
l_routings_step_dep_rec.LAST_UPDATE_LOGIN := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
l_routings_step_dep_tbl_insert(l_routings_step_dep_insert_ctr) := l_routings_step_dep_rec;
l_routings_step_dep_insert_ctr := l_routings_step_dep_insert_ctr + 1;
GMD_ROUTING_STEPS_PUB.insert_routing_steps
(
p_api_version => 1
, p_init_msg_list => FALSE
, p_commit => FALSE
, p_routing_id => p_routing_obj.ROUTING_ID
, p_routing_no => p_routing_obj.ROUTING_NO
, p_routing_vers => p_routing_obj.ROUTING_VERS
, p_routing_step_rec => l_routings_step_rec
, p_routings_step_dep_tbl => l_routings_step_dep_tbl_insert
, x_message_count => l_msg_count
, x_message_list => l_msg
, x_return_status => l_ret_status
);
l_rout_tbl.DELETE;
l_rout_rec.p_col_to_update := 'x_coordinate';
l_rout_rec.p_col_to_update := 'y_coordinate';
l_rout_rec.p_col_to_update := 'minimum_transfer_qty';
l_rout_rec.p_col_to_update := 'routing_id';
l_rout_rec.p_col_to_update := 'routingstep_no';
l_rout_rec.p_col_to_update := 'routingstep_id';
l_rout_rec.p_col_to_update := 'oprn_id';
l_rout_rec.p_col_to_update := 'step_qty';
l_rout_rec.p_col_to_update := 'steprelease_type';
l_rout_rec.p_col_to_update := 'text_code';
l_rout_rec.p_col_to_update := 'last_updated_by';
l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).last_updated_by;
l_rout_rec.p_col_to_update := 'created_by';
l_rout_rec.p_col_to_update := 'last_update_date';
l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).last_update_date;
l_rout_rec.p_col_to_update := 'creation_date';
l_rout_rec.p_col_to_update := 'last_update_login';
l_rout_rec.p_value := p_routing_obj.gmd_ebi_routing_steps(j).last_update_login;
l_rout_rec.p_col_to_update := 'attribute_category';
l_rout_rec.p_col_to_update := 'attribute1';
l_rout_rec.p_col_to_update := 'attribute2';
l_rout_rec.p_col_to_update := 'attribute3';
l_rout_rec.p_col_to_update := 'attribute4';
l_rout_rec.p_col_to_update := 'attribute5';
l_rout_rec.p_col_to_update := 'attribute6';
l_rout_rec.p_col_to_update := 'attribute7';
l_rout_rec.p_col_to_update := 'attribute8';
l_rout_rec.p_col_to_update := 'attribute9';
l_rout_rec.p_col_to_update := 'attribute10';
l_rout_rec.p_col_to_update := 'attribute11';
l_rout_rec.p_col_to_update := 'attribute12';
l_rout_rec.p_col_to_update := 'attribute13';
l_rout_rec.p_col_to_update := 'attribute14';
l_rout_rec.p_col_to_update := 'attribute15';
l_rout_rec.p_col_to_update := 'attribute16';
l_rout_rec.p_col_to_update := 'attribute17';
l_rout_rec.p_col_to_update := 'attribute18';
l_rout_rec.p_col_to_update := 'attribute19';
l_rout_rec.p_col_to_update := 'attribute20';
l_rout_rec.p_col_to_update := 'attribute21';
l_rout_rec.p_col_to_update := 'attribute22';
l_rout_rec.p_col_to_update := 'attribute23';
l_rout_rec.p_col_to_update := 'attribute24';
l_rout_rec.p_col_to_update := 'attribute25';
l_rout_rec.p_col_to_update := 'attribute26';
l_rout_rec.p_col_to_update := 'attribute27';
l_rout_rec.p_col_to_update := 'attribute28';
l_rout_rec.p_col_to_update := 'attribute29';
l_rout_rec.p_col_to_update := 'attribute30';
GMD_ROUTING_STEPS_PUB.update_routing_steps
( p_api_version => 1
, p_init_msg_list => FALSE
, p_commit => FALSE
, p_routingstep_id => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_id
, p_routingstep_no => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_no
, p_routing_id => p_routing_obj.gmd_ebi_routing_steps(j).routing_id
, p_routing_no => p_routing_obj.ROUTING_NO
, p_routing_vers => p_routing_obj.ROUTING_vers
, p_update_table => l_rout_tbl
, x_message_count => l_msg_count
, x_message_list => l_msg
, x_return_status => l_ret_status
);
IF l_routings_step_dep_tbl_insert.Count > 1 THEN
l_routings_step_dep_tbl_insert.DELETE;
l_routings_step_dep_insert_ctr := 1;
l_routings_step_dep_rec.LAST_UPDATED_BY :=Nvl( p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATED_BY,FND_GLOBAL.USER_ID);
l_routings_step_dep_rec.LAST_UPDATE_DATE := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_DATE,SYSDATE);
l_routings_step_dep_rec.LAST_UPDATE_LOGIN := Nvl(p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_LOGIN,FND_GLOBAL.USER_ID);
l_routings_step_dep_tbl_insert(l_routings_step_dep_insert_ctr) := l_routings_step_dep_rec;
l_routings_step_dep_insert_ctr := l_routings_step_dep_insert_ctr + 1;
l_rout_tbl.DELETE;
l_rout_rec.p_col_to_update := 'ROUTINGSTEP_NO_UOM';
l_rout_rec.p_col_to_update := 'CHARGEABLE_IND';
l_rout_rec.p_col_to_update := 'ROUTINGSTEP_NO';
l_rout_rec.p_col_to_update := 'DEP_ROUTINGSTEP_NO';
l_rout_rec.p_col_to_update := 'ROUTING_ID';
l_rout_rec.p_col_to_update := 'DEP_TYPE';
l_rout_rec.p_col_to_update := 'REWORK_CODE';
l_rout_rec.p_col_to_update := 'STANDARD_DELAY';
l_rout_rec.p_col_to_update := 'MINIMUM_DELAY';
l_rout_rec.p_col_to_update := 'MAX_DELAY';
l_rout_rec.p_col_to_update := 'TRANSFER_QTY';
l_rout_rec.p_col_to_update := 'ITEM_UM';
l_rout_rec.p_col_to_update := 'TEXT_CODE';
l_rout_rec.p_col_to_update := 'LAST_UPDATED_BY';
l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATED_BY;
l_rout_rec.p_col_to_update := 'CREATED_BY';
l_rout_rec.p_col_to_update := 'LAST_UPDATE_DATE';
l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_DATE;
l_rout_rec.p_col_to_update := 'CREATION_DATE';
l_rout_rec.p_col_to_update := 'LAST_UPDATE_LOGIN';
l_rout_rec.p_value := p_routing_obj.gmd_ebi_rtg_step_dependency(i).LAST_UPDATE_LOGIN;
l_rout_rec.p_col_to_update := 'TRANSFER_PCT';
GMD_ROUTING_STEPS_PUB.update_step_dependencies
( p_api_version =>1
, p_init_msg_list =>FALSE
, p_commit => FALSE
, p_dep_routingstep_no => p_routing_obj.gmd_ebi_rtg_step_dependency(i).DEP_ROUTINGSTEP_NO
, p_routingstep_id => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_id
, p_routingstep_no => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_no
, p_routing_id => p_routing_obj.gmd_ebi_routing_steps(j).routing_id
, p_routing_no => p_routing_obj.ROUTING_NO
, p_routing_vers => p_routing_obj.ROUTING_vers
, p_update_table =>l_rout_tbl
, x_message_count =>l_msg_count
, x_message_list => l_msg
, x_return_status => l_ret_status
);
IF l_routings_step_dep_tbl_insert.Count > 1 THEN
GMD_ROUTING_STEPS_PUB.insert_step_dependencies
( p_api_version => 1
, p_init_msg_list => FALSE
, p_commit => FALSE
, p_routing_id => p_routing_obj.gmd_ebi_routing_steps(j).routing_id
, p_routing_no => p_routing_obj.ROUTING_NO
, p_routing_vers => p_routing_obj.ROUTING_vers
, p_routingstep_id => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_id
, p_routingstep_no => p_routing_obj.gmd_ebi_routing_steps(j).routingstep_no
, p_routings_step_dep_tbl => l_routings_step_dep_tbl_insert
, x_message_count => l_msg_count
, x_message_list => l_msg
, x_return_status => l_ret_status
);
/* The procedure creates/updates the attachment that denotes routing */
/* information. */
/* ======================================================================== */
PROCEDURE add_attachment(
p_recipe_obj IN gmd_ebi_recipe_obj
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_text LONG;
SELECT category_id INTO l_category_id FROM FND_DOCUMENT_CATEGORIES_tl WHERE user_name='Formula Header Info';
SELECT DISTINCT concatenated_segments,description INTO l_item_no,l_item_desc FROM mtl_system_items_kfv WHERE inventory_item_id = p_recipe_obj.formula_obj.formula_details(k).INVENTORY_ITEM_ID AND organization_id = l_org_id;
SELECT DISTINCT concatenated_segments,description INTO l_item_no,l_item_desc FROM mtl_system_items_kfv WHERE inventory_item_id = p_recipe_obj.formula_obj.formula_details(k).INVENTORY_ITEM_ID AND organization_id = l_org_id;
SELECT DISTINCT concatenated_segments,description INTO l_item_no,l_item_desc FROM mtl_system_items_kfv WHERE inventory_item_id = p_recipe_obj.formula_obj.formula_details(k).INVENTORY_ITEM_ID AND organization_id = l_org_id;
SELECT Max(formula_id) INTO l_formula_id FROM fm_form_mst_b WHERE formula_no=p_recipe_obj.formula_obj.formula_header.FORMULA_no;
FND_Documents_PKG.Insert_Row(X_Rowid => l_row_id1,
X_document_id => l_document_id,
X_creation_date => sysdate,
X_created_by => FND_GLOBAL.USER_ID,
X_last_update_date => sysdate,
X_last_updated_by => FND_GLOBAL.USER_ID,
X_datatype_id => l_datatype_id,
X_category_id => l_category_id,
X_security_type => l_security_tp,
X_publish_flag => l_publish_flag,
X_usage_type => l_usage_type,
X_language => userenv('LANG'),
X_description => l_description,
X_file_name => null,
X_media_id => l_media_id);
INSERT INTO fnd_documents_long_text(Media_id,long_text) VALUES ( l_media_id,l_text);
SELECT fnd_attached_documents_s.NEXTVAL
INTO l_att_doc_id
FROM dual;
FND_Attached_Documents_PKG.Insert_Row(X_Rowid => l_row_id2,
X_attached_document_id => l_att_doc_id,
X_document_id => l_document_id,
X_creation_date => sysdate,
X_created_by => FND_GLOBAL.USER_ID,
X_last_update_date => sysdate,
X_last_updated_by => FND_GLOBAL.USER_ID,
X_seq_num => '10',
X_entity_name => l_entity_name,
X_column1 => NULL,
X_pk1_value => l_formula_id,
X_pk2_value => NULL,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_automatically_added_flag => l_aut_add_flag,
X_datatype_id => l_datatype_id,
X_category_id => l_category_id,
X_security_type => l_security_tp,
X_publish_flag => l_publish_flag,
X_usage_type => l_usage_type,
X_language => UserEnv('LANG'),
X_description => l_description,
X_file_name => null,
X_media_id => l_media_id,
X_doc_attribute_Category => NULL,
X_doc_attribute1 => NULL,
X_doc_attribute2 => NULL,
X_doc_attribute3 => NULL,
X_doc_attribute4 => NULL,
X_doc_attribute5 => NULL,
X_doc_attribute6 => NULL,
X_doc_attribute7 => NULL,
X_doc_attribute8 => NULL,
X_doc_attribute9 => NULL,
X_doc_attribute10 => NULL,
X_doc_attribute11 => NULL,
X_doc_attribute12 => NULL,
X_doc_attribute13 => NULL,
X_doc_attribute14 => NULL,
X_doc_attribute15 => NULL);
SELECT * INTO l_attached_doc
FROM FND_ATTACHED_DOCUMENTS
WHERE ENTITY_NAME ='FM_FORM_MST_B' AND PK1_VALUE = l_formula_id;
SELECT * INTO l_doc
FROM fnd_documents
WHERE document_id = l_attached_doc.document_id;
SELECT DISTINCT row_id INTO l_row_id1 FROM FND_ATTACHED_DOCS_FORM_VL
WHERE attached_document_id = l_attached_doc.attached_document_id;
FND_Attached_Documents_PKG.Update_Row(X_Rowid => l_row_id1
,X_attached_document_id => l_attached_doc.attached_document_id
,X_document_id =>l_attached_doc.document_id
,X_last_update_date => sysdate
,X_last_updated_by => FND_GLOBAL.USER_ID
,X_last_update_login => FND_GLOBAL.USER_ID
,X_seq_num => l_attached_doc.seq_num
,X_entity_name => l_attached_doc.entity_name
,X_column1 => l_attached_doc.column1
,X_pk1_value => l_attached_doc.pk1_value
,X_pk2_value => NULL
,X_pk3_value => NULL
,X_pk4_value => NULL
,X_pk5_value => NULL
,X_automatically_added_flag => l_attached_doc.automatically_added_flag
,X_request_id => l_attached_doc.request_id
,X_program_application_id => l_attached_doc.program_application_id
,X_program_id => l_attached_doc.program_id
,X_program_update_date => sysdate
,X_doc_attribute_Category => NULL
,X_doc_attribute1 => NULL
,X_doc_attribute2 => NULL
,X_doc_attribute3 => NULL
,X_doc_attribute4 => NULL
,X_doc_attribute5 => NULL
,X_doc_attribute6 => NULL
,X_doc_attribute7 => NULL
,X_doc_attribute8 => NULL
,X_doc_attribute9 => NULL
,X_doc_attribute10 => NULL
,X_doc_attribute11 => NULL
,X_doc_attribute12 => NULL
,X_doc_attribute13 => NULL
,X_doc_attribute14 => NULL
,X_doc_attribute15 => NULL
,X_datatype_id => l_doc.datatype_id
,X_category_id => l_doc.category_id
,X_security_type => l_doc.security_type
,X_publish_flag => l_doc.publish_flag
,X_usage_type => l_doc.usage_type
,X_language => userenv('LANG')
,X_image_type =>null
,X_storage_type => null
,X_start_date_active => sysdate
,X_end_date_active =>null
,X_description => l_description
,X_file_name => null
,X_media_id => l_doc.media_id
,X_url => NULL
,X_title => 'ROUTING DETAILS'
,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
);
UPDATE fnd_documents_long_text
SET long_text = l_text
WHERE media_id = l_doc.media_id;
/* Delete_FormulaDetail */
/* */
/* DESCRIPTION: */
/* The procedure deletes the formula details that have been removed */
/* from the formula. */
/* ======================================================================== */
PROCEDURE Delete_FormulaDetail(
p_formula_detail_tbl IN GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type
,x_line_delete_tbl OUT NOCOPY gmd_ebi_formula_line_del_tbl
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_line_delete_tbl gmd_ebi_formula_line_del_tbl := gmd_ebi_formula_line_del_tbl();
l_line_delete_obj gmd_ebi_formula_line_del_obj;
l_delete_ctr NUMBER :=1;
l_formula_delete_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
l_formula_delete_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
SELECT formula_id,formulaline_id FROM fm_matl_dtl WHERE formula_id = p_formula_detail_tbl(1).formula_id;
l_formula_delete_detail_rec.formula_id := formula_line.formula_id;
l_formula_delete_detail_rec.formulaline_id := formula_line.formulaline_id;
l_formula_delete_detail_rec.record_type := 'D';
l_formula_delete_detail_tbl(l_delete_ctr) := l_formula_delete_detail_rec;
l_line_delete_obj := gmd_ebi_formula_line_del_obj(formula_line.formulaline_id);
l_line_delete_tbl.extend();
l_line_delete_tbl(l_delete_ctr):= l_line_delete_obj;
l_delete_ctr := l_delete_ctr + 1;
IF l_formula_delete_detail_tbl.Count > 0 THEN
GMD_FORMULA_DETAIL_PUB.Delete_FormulaDetail(p_api_version => 1.1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_called_from_forms => 'NO'
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_formula_detail_tbl => l_formula_delete_detail_tbl);
x_line_delete_tbl := l_line_delete_tbl;
x_msg_data := SQLERRM||' AT DELETE FORMULA LINES';
/* is_updateable */
/* */
/* DESCRIPTION: */
/* This function returns 'Y' or 'N' based on whether formula is */
/* version controlled or not. */
/* ======================================================================== */
FUNCTION is_updateable(p_orgn_id IN NUMBER)
RETURN VARCHAR2
IS
l_vers_cntrl VARCHAR2(1);
,x_line_delete_tbl OUT NOCOPY gmd_ebi_formula_line_del_tbl
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_formula_insert_header_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
l_formula_insert_header_tbl GMD_FORMULA_PUB.formula_insert_hdr_tbl_type;
l_formula_update_header_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
l_formula_update_header_tbl GMD_FORMULA_PUB.formula_update_hdr_tbl_type;
l_formula_insert_detail_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
l_formula_insert_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_insert_dtl_tbl_type;
l_formula_update_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
l_formula_update_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
l_insert_detailctr NUMBER := 1;
l_insert_headerctr NUMBER := 1;
l_update_detailctr NUMBER := 1;
l_update_header_flag NUMBER;
l_line_delete_tbl gmd_ebi_formula_line_del_tbl;
SELECT user_id INTO l_formula_owner_id FROM fnd_user WHERE user_name = l_formula_owner_name;
SELECT Max(formula_vers)+1 INTO l_form_version FROM fm_form_mst_b WHERE formula_no = p_formula_obj.formula_header.formula_no;
l_formula_insert_header_rec.formula_id := p_formula_obj.formula_header.formula_id;
l_formula_insert_header_rec.formula_no := p_formula_obj.formula_header.formula_no;
l_formula_insert_header_rec.formula_vers := l_form_version;
l_formula_insert_header_rec.formula_type := l_form_type;
l_formula_insert_header_rec.master_formula_id := p_formula_obj.formula_header.master_formula_id;
l_formula_insert_header_rec.scale_type_hdr := p_formula_obj.formula_header.scale_type_hdr;
l_formula_insert_header_rec.formula_desc1 := p_formula_obj.formula_header.formula_desc1;
l_formula_insert_header_rec.formula_desc2 := p_formula_obj.formula_header.formula_desc2;
l_formula_insert_header_rec.formula_class := p_formula_obj.formula_header.formula_class;
l_formula_insert_header_rec.fmcontrol_class := p_formula_obj.formula_header.fmcontrol_class;
l_formula_insert_header_rec.inactive_ind := Nvl(p_formula_obj.formula_header.inactive_ind,0);
l_formula_insert_header_rec.owner_organization_id := p_formula_obj.formula_header.owner_organization_id;
l_formula_insert_header_rec.total_input_qty := p_formula_obj.formula_header.total_input_qty ;
l_formula_insert_header_rec.total_output_qty := p_formula_obj.formula_header.total_output_qty ;
l_formula_insert_header_rec.yield_uom := p_formula_obj.formula_header.yield_uom;
l_formula_insert_header_rec.formula_status := p_formula_obj.formula_header.formula_status ;
l_formula_insert_header_rec.owner_id := l_formula_owner_id;
l_formula_insert_header_rec.attribute1 := p_formula_obj.formula_header.attribute1;
l_formula_insert_header_rec.attribute2 := p_formula_obj.formula_header.attribute2;
l_formula_insert_header_rec.attribute3 := p_formula_obj.formula_header.attribute3;
l_formula_insert_header_rec.attribute4 := p_formula_obj.formula_header.attribute4;
l_formula_insert_header_rec.attribute5 := p_formula_obj.formula_header.attribute5;
l_formula_insert_header_rec.attribute6 := p_formula_obj.formula_header.attribute6;
l_formula_insert_header_rec.attribute7 := p_formula_obj.formula_header.attribute7;
l_formula_insert_header_rec.attribute8 := p_formula_obj.formula_header.attribute8;
l_formula_insert_header_rec.attribute9 := p_formula_obj.formula_header.attribute9;
l_formula_insert_header_rec.attribute10 := p_formula_obj.formula_header.attribute10;
l_formula_insert_header_rec.attribute11 := p_formula_obj.formula_header.attribute11;
l_formula_insert_header_rec.attribute12 := p_formula_obj.formula_header.attribute12;
l_formula_insert_header_rec.attribute13 := p_formula_obj.formula_header.attribute13;
l_formula_insert_header_rec.attribute14 := p_formula_obj.formula_header.attribute14;
l_formula_insert_header_rec.attribute15 := p_formula_obj.formula_header.attribute15;
l_formula_insert_header_rec.attribute16 := p_formula_obj.formula_header.attribute16;
l_formula_insert_header_rec.attribute17 := p_formula_obj.formula_header.attribute17;
l_formula_insert_header_rec.attribute18 := p_formula_obj.formula_header.attribute18;
l_formula_insert_header_rec.attribute19 := p_formula_obj.formula_header.attribute19;
l_formula_insert_header_rec.attribute20 := p_formula_obj.formula_header.attribute20;
l_formula_insert_header_rec.attribute21 := p_formula_obj.formula_header.attribute21;
l_formula_insert_header_rec.attribute22 := p_formula_obj.formula_header.attribute22;
l_formula_insert_header_rec.attribute23 := p_formula_obj.formula_header.attribute23;
l_formula_insert_header_rec.attribute24 := p_formula_obj.formula_header.attribute24;
l_formula_insert_header_rec.attribute25 := p_formula_obj.formula_header.attribute25;
l_formula_insert_header_rec.attribute26 := p_formula_obj.formula_header.attribute26;
l_formula_insert_header_rec.attribute27 := p_formula_obj.formula_header.attribute27;
l_formula_insert_header_rec.attribute28 := p_formula_obj.formula_header.attribute28;
l_formula_insert_header_rec.attribute29 := p_formula_obj.formula_header.attribute29;
l_formula_insert_header_rec.attribute30 := p_formula_obj.formula_header.attribute30;
l_formula_insert_header_rec.attribute_category := p_formula_obj.formula_header.attribute_category;
l_formula_insert_header_rec.text_code_hdr := p_formula_obj.formula_header.text_code_hdr;
l_formula_insert_header_rec.delete_mark := NVL(p_formula_obj.formula_header.delete_mark,0);
l_formula_insert_header_rec.created_by := FND_GLOBAL.USER_ID;
l_formula_insert_header_rec.creation_date := NVL(p_formula_obj.formula_header.creation_date, SYSDATE);
l_formula_insert_header_rec.last_update_date := NVL(p_formula_obj.formula_header.last_update_date, SYSDATE);
l_formula_insert_header_rec.last_update_login := NVL(p_formula_obj.formula_header.last_update_login, FND_GLOBAL.USER_ID);
l_formula_insert_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_formula_insert_header_rec.formulaline_id := p_formula_obj.formula_details(j).formulaline_id;
l_formula_insert_header_rec.line_type := p_formula_obj.formula_details(j).line_type;
l_formula_insert_header_rec.line_no := p_formula_obj.formula_details(j).line_no;
l_formula_insert_header_rec.inventory_item_id := p_formula_obj.formula_details(j).inventory_item_id;
l_formula_insert_header_rec.revision := p_formula_obj.formula_details(j).revision;
l_formula_insert_header_rec.qty := p_formula_obj.formula_details(j).qty;
l_formula_insert_header_rec.detail_uom := p_formula_obj.formula_details(j).detail_uom;
l_formula_insert_header_rec.release_type := p_formula_obj.formula_details(j).release_type;
l_formula_insert_header_rec.scrap_factor := p_formula_obj.formula_details(j).scrap_factor;
l_formula_insert_header_rec.scale_type_dtl := p_formula_obj.formula_details(j).scale_type_dtl;
l_formula_insert_header_rec.cost_alloc := p_formula_obj.formula_details(j).cost_alloc;
l_formula_insert_header_rec.phantom_type := p_formula_obj.formula_details(j).phantom_type;
l_formula_insert_header_rec.buffer_ind := p_formula_obj.formula_details(j).buffer_ind;
l_formula_insert_header_rec.tpformula_id := p_formula_obj.formula_details(j).tpformula_id;
l_formula_insert_header_rec.iaformula_id := p_formula_obj.formula_details(j).iaformula_id;
l_formula_insert_header_rec.scale_multiple := p_formula_obj.formula_details(j).scale_multiple;
l_formula_insert_header_rec.contribute_yield_ind := p_formula_obj.formula_details(j).contribute_yield_ind;
l_formula_insert_header_rec.scale_uom := p_formula_obj.formula_details(j).scale_uom;
l_formula_insert_header_rec.contribute_step_qty_ind := p_formula_obj.formula_details(j).contribute_step_qty_ind;
l_formula_insert_header_rec.scale_rounding_variance := p_formula_obj.formula_details(j).scale_rounding_variance;
l_formula_insert_header_rec.rounding_direction := p_formula_obj.formula_details(j).rounding_direction;
l_formula_insert_header_rec.by_product_type := p_formula_obj.formula_details(j).by_product_type;
l_formula_insert_header_rec.ingredient_end_date := p_formula_obj.formula_details(j).ingredient_end_date;
l_formula_insert_header_rec.text_code_dtl := p_formula_obj.formula_details(j).text_code_dtl;
l_formula_insert_header_rec.dtl_attribute_category := p_formula_obj.formula_details(j).dtl_attribute_category;
l_formula_insert_header_rec.dtl_attribute1 := p_formula_obj.formula_details(j).dtl_attribute1;
l_formula_insert_header_rec.dtl_attribute2 := p_formula_obj.formula_details(j).dtl_attribute2;
l_formula_insert_header_rec.dtl_attribute3 := p_formula_obj.formula_details(j).dtl_attribute3;
l_formula_insert_header_rec.dtl_attribute4 := p_formula_obj.formula_details(j).dtl_attribute4;
l_formula_insert_header_rec.dtl_attribute5 := p_formula_obj.formula_details(j).dtl_attribute5;
l_formula_insert_header_rec.dtl_attribute6 := p_formula_obj.formula_details(j).dtl_attribute6;
l_formula_insert_header_rec.dtl_attribute7 := p_formula_obj.formula_details(j).dtl_attribute7;
l_formula_insert_header_rec.dtl_attribute8 := p_formula_obj.formula_details(j).dtl_attribute8;
l_formula_insert_header_rec.dtl_attribute9 := p_formula_obj.formula_details(j).dtl_attribute9;
l_formula_insert_header_rec.dtl_attribute10 := p_formula_obj.formula_details(j).dtl_attribute10;
l_formula_insert_header_rec.dtl_attribute11 := p_formula_obj.formula_details(j).dtl_attribute11;
l_formula_insert_header_rec.dtl_attribute12 := p_formula_obj.formula_details(j).dtl_attribute12;
l_formula_insert_header_rec.dtl_attribute13 := p_formula_obj.formula_details(j).dtl_attribute13;
l_formula_insert_header_rec.dtl_attribute14 := p_formula_obj.formula_details(j).dtl_attribute14;
l_formula_insert_header_rec.dtl_attribute15 := p_formula_obj.formula_details(j).dtl_attribute15;
l_formula_insert_header_rec.dtl_attribute16 := p_formula_obj.formula_details(j).dtl_attribute16;
l_formula_insert_header_rec.dtl_attribute17 := p_formula_obj.formula_details(j).dtl_attribute17;
l_formula_insert_header_rec.dtl_attribute18 := p_formula_obj.formula_details(j).dtl_attribute18;
l_formula_insert_header_rec.dtl_attribute19 := p_formula_obj.formula_details(j).dtl_attribute19;
l_formula_insert_header_rec.dtl_attribute20 := p_formula_obj.formula_details(j).dtl_attribute20;
l_formula_insert_header_rec.dtl_attribute21 := p_formula_obj.formula_details(j).dtl_attribute21;
l_formula_insert_header_rec.dtl_attribute22 := p_formula_obj.formula_details(j).dtl_attribute22;
l_formula_insert_header_rec.dtl_attribute23 := p_formula_obj.formula_details(j).dtl_attribute23;
l_formula_insert_header_rec.dtl_attribute24 := p_formula_obj.formula_details(j).dtl_attribute24;
l_formula_insert_header_rec.dtl_attribute25 := p_formula_obj.formula_details(j).dtl_attribute25;
l_formula_insert_header_rec.dtl_attribute26 := p_formula_obj.formula_details(j).dtl_attribute26;
l_formula_insert_header_rec.dtl_attribute27 := p_formula_obj.formula_details(j).dtl_attribute27;
l_formula_insert_header_rec.dtl_attribute28 := p_formula_obj.formula_details(j).dtl_attribute28;
l_formula_insert_header_rec.dtl_attribute29 := p_formula_obj.formula_details(j).dtl_attribute29;
l_formula_insert_header_rec.dtl_attribute30 := p_formula_obj.formula_details(j).dtl_attribute30;
l_formula_insert_header_tbl(l_insert_headerctr) := l_formula_insert_header_rec;
l_insert_headerctr := l_insert_headerctr + 1;
IF l_formula_update_header_tbl.Count = 0 THEN
IF(is_updateable( p_formula_obj.formula_header.owner_organization_id)=FND_API.G_FALSE)THEN
FND_MESSAGE.set_name('GMD','GMD_EBI_VERSION_ERROR');
l_formula_update_header_rec.formula_id := p_formula_obj.formula_header.formula_id;
l_formula_update_header_rec.formula_no := p_formula_obj.formula_header.formula_no;
l_formula_update_header_rec.formula_type := l_form_type;
l_formula_update_header_rec.scale_type_hdr := p_formula_obj.formula_header.scale_type_hdr;
l_formula_update_header_rec.formula_desc1 := p_formula_obj.formula_header.formula_desc1;
l_formula_update_header_rec.formula_desc2 := p_formula_obj.formula_header.formula_desc2;
l_formula_update_header_rec.formula_class := p_formula_obj.formula_header.formula_class;
l_formula_update_header_rec.fmcontrol_class := p_formula_obj.formula_header.fmcontrol_class;
l_formula_update_header_rec.inactive_ind := Nvl(p_formula_obj.formula_header.inactive_ind,0);
l_formula_update_header_rec.owner_organization_id := p_formula_obj.formula_header.owner_organization_id;
l_formula_update_header_rec.total_input_qty := p_formula_obj.formula_header.total_input_qty ;
l_formula_update_header_rec.total_output_qty := p_formula_obj.formula_header.total_output_qty ;
l_formula_update_header_rec.yield_uom := p_formula_obj.formula_header.yield_uom;
l_formula_update_header_rec.formula_status := p_formula_obj.formula_header.formula_status;
l_formula_update_header_rec.formula_status := NULL;
l_formula_update_header_rec.owner_id := l_formula_owner_id;
l_formula_update_header_rec.attribute1 := p_formula_obj.formula_header.attribute1;
l_formula_update_header_rec.attribute2 := p_formula_obj.formula_header.attribute2;
l_formula_update_header_rec.attribute3 := p_formula_obj.formula_header.attribute3;
l_formula_update_header_rec.attribute4 := p_formula_obj.formula_header.attribute4;
l_formula_update_header_rec.attribute5 := p_formula_obj.formula_header.attribute5;
l_formula_update_header_rec.attribute6 := p_formula_obj.formula_header.attribute6;
l_formula_update_header_rec.attribute7 := p_formula_obj.formula_header.attribute7;
l_formula_update_header_rec.attribute8 := p_formula_obj.formula_header.attribute8;
l_formula_update_header_rec.attribute9 := p_formula_obj.formula_header.attribute9;
l_formula_update_header_rec.attribute10 := p_formula_obj.formula_header.attribute10;
l_formula_update_header_rec.attribute11 := p_formula_obj.formula_header.attribute11;
l_formula_update_header_rec.attribute12 := p_formula_obj.formula_header.attribute12;
l_formula_update_header_rec.attribute13 := p_formula_obj.formula_header.attribute13;
l_formula_update_header_rec.attribute14 := p_formula_obj.formula_header.attribute14;
l_formula_update_header_rec.attribute15 := p_formula_obj.formula_header.attribute15;
l_formula_update_header_rec.attribute16 := p_formula_obj.formula_header.attribute16;
l_formula_update_header_rec.attribute17 := p_formula_obj.formula_header.attribute17;
l_formula_update_header_rec.attribute18 := p_formula_obj.formula_header.attribute18;
l_formula_update_header_rec.attribute19 := p_formula_obj.formula_header.attribute19;
l_formula_update_header_rec.attribute20 := p_formula_obj.formula_header.attribute20;
l_formula_update_header_rec.attribute21 := p_formula_obj.formula_header.attribute21;
l_formula_update_header_rec.attribute22 := p_formula_obj.formula_header.attribute22;
l_formula_update_header_rec.attribute23 := p_formula_obj.formula_header.attribute23;
l_formula_update_header_rec.attribute24 := p_formula_obj.formula_header.attribute24;
l_formula_update_header_rec.attribute25 := p_formula_obj.formula_header.attribute25;
l_formula_update_header_rec.attribute26 := p_formula_obj.formula_header.attribute26;
l_formula_update_header_rec.attribute27 := p_formula_obj.formula_header.attribute27;
l_formula_update_header_rec.attribute28 := p_formula_obj.formula_header.attribute28;
l_formula_update_header_rec.attribute29 := p_formula_obj.formula_header.attribute29;
l_formula_update_header_rec.attribute30 := p_formula_obj.formula_header.attribute30;
l_formula_update_header_rec.attribute_category:= p_formula_obj.formula_header.attribute_category;
l_formula_update_header_rec.text_code_hdr := p_formula_obj.formula_header.text_code_hdr;
l_formula_update_header_rec.delete_mark := NVL(p_formula_obj.formula_header.delete_mark,0);
l_formula_update_header_rec.created_by := FND_GLOBAL.USER_ID;
l_formula_update_header_rec.creation_date := NVL(p_formula_obj.formula_header.creation_date, SYSDATE);
l_formula_update_header_rec.last_update_date := NVL(p_formula_obj.formula_header.last_update_date, SYSDATE);
l_formula_update_header_rec.last_update_login := NVL(p_formula_obj.formula_header.last_update_login, FND_GLOBAL.USER_ID);
l_formula_update_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_formula_update_header_tbl(1) := l_formula_update_header_rec;
l_formula_insert_detail_rec.formula_id := p_formula_obj.formula_details(j).formula_id;
l_formula_insert_detail_rec.formulaline_id := p_formula_obj.formula_details(j).formulaline_id;
l_formula_insert_detail_rec.line_type := p_formula_obj.formula_details(j).line_type;
l_formula_insert_detail_rec.line_no := p_formula_obj.formula_details(j).line_no;
l_formula_insert_detail_rec.inventory_item_id := p_formula_obj.formula_details(j).inventory_item_id;
l_formula_insert_detail_rec.revision := p_formula_obj.formula_details(j).revision;
l_formula_insert_detail_rec.qty := p_formula_obj.formula_details(j).qty;
l_formula_insert_detail_rec.detail_uom := p_formula_obj.formula_details(j).detail_uom;
l_formula_insert_detail_rec.release_type := p_formula_obj.formula_details(j).release_type;
l_formula_insert_detail_rec.scrap_factor := p_formula_obj.formula_details(j).scrap_factor;
l_formula_insert_detail_rec.scale_type_dtl := p_formula_obj.formula_details(j).scale_type_dtl;
l_formula_insert_detail_rec.cost_alloc := p_formula_obj.formula_details(j).cost_alloc;
l_formula_insert_detail_rec.phantom_type := p_formula_obj.formula_details(j).phantom_type;
l_formula_insert_detail_rec.buffer_ind := p_formula_obj.formula_details(j).buffer_ind;
l_formula_insert_detail_rec.tpformula_id := p_formula_obj.formula_details(j).tpformula_id;
l_formula_insert_detail_rec.iaformula_id := p_formula_obj.formula_details(j).iaformula_id;
l_formula_insert_detail_rec.scale_multiple := p_formula_obj.formula_details(j).scale_multiple;
l_formula_insert_detail_rec.contribute_yield_ind := p_formula_obj.formula_details(j).contribute_yield_ind;
l_formula_insert_detail_rec.scale_uom := p_formula_obj.formula_details(j).scale_uom;
l_formula_insert_detail_rec.contribute_step_qty_ind := p_formula_obj.formula_details(j).contribute_step_qty_ind;
l_formula_insert_detail_rec.scale_rounding_variance := p_formula_obj.formula_details(j).scale_rounding_variance;
l_formula_insert_detail_rec.rounding_direction := p_formula_obj.formula_details(j).rounding_direction;
l_formula_insert_detail_rec.by_product_type := p_formula_obj.formula_details(j).by_product_type;
l_formula_insert_detail_rec.ingredient_end_date := p_formula_obj.formula_details(j).ingredient_end_date;
l_formula_insert_detail_rec.text_code_dtl := p_formula_obj.formula_details(j).text_code_dtl;
l_formula_insert_detail_rec.dtl_attribute_category := p_formula_obj.formula_details(j).dtl_attribute_category;
l_formula_insert_detail_rec.dtl_attribute1 := p_formula_obj.formula_details(j).dtl_attribute1;
l_formula_insert_detail_rec.dtl_attribute2 := p_formula_obj.formula_details(j).dtl_attribute2;
l_formula_insert_detail_rec.dtl_attribute3 := p_formula_obj.formula_details(j).dtl_attribute3;
l_formula_insert_detail_rec.dtl_attribute4 := p_formula_obj.formula_details(j).dtl_attribute4;
l_formula_insert_detail_rec.dtl_attribute5 := p_formula_obj.formula_details(j).dtl_attribute5;
l_formula_insert_detail_rec.dtl_attribute6 := p_formula_obj.formula_details(j).dtl_attribute6;
l_formula_insert_detail_rec.dtl_attribute7 := p_formula_obj.formula_details(j).dtl_attribute7;
l_formula_insert_detail_rec.dtl_attribute8 := p_formula_obj.formula_details(j).dtl_attribute8;
l_formula_insert_detail_rec.dtl_attribute9 := p_formula_obj.formula_details(j).dtl_attribute9;
l_formula_insert_detail_rec.dtl_attribute10 := p_formula_obj.formula_details(j).dtl_attribute10;
l_formula_insert_detail_rec.dtl_attribute11 := p_formula_obj.formula_details(j).dtl_attribute11;
l_formula_insert_detail_rec.dtl_attribute12 := p_formula_obj.formula_details(j).dtl_attribute12;
l_formula_insert_detail_rec.dtl_attribute13 := p_formula_obj.formula_details(j).dtl_attribute13;
l_formula_insert_detail_rec.dtl_attribute14 := p_formula_obj.formula_details(j).dtl_attribute14;
l_formula_insert_detail_rec.dtl_attribute15 := p_formula_obj.formula_details(j).dtl_attribute15;
l_formula_insert_detail_rec.dtl_attribute16 := p_formula_obj.formula_details(j).dtl_attribute16;
l_formula_insert_detail_rec.dtl_attribute17 := p_formula_obj.formula_details(j).dtl_attribute17;
l_formula_insert_detail_rec.dtl_attribute18 := p_formula_obj.formula_details(j).dtl_attribute18;
l_formula_insert_detail_rec.dtl_attribute19 := p_formula_obj.formula_details(j).dtl_attribute19;
l_formula_insert_detail_rec.dtl_attribute20 := p_formula_obj.formula_details(j).dtl_attribute20;
l_formula_insert_detail_rec.dtl_attribute21 := p_formula_obj.formula_details(j).dtl_attribute21;
l_formula_insert_detail_rec.dtl_attribute22 := p_formula_obj.formula_details(j).dtl_attribute22;
l_formula_insert_detail_rec.dtl_attribute23 := p_formula_obj.formula_details(j).dtl_attribute23;
l_formula_insert_detail_rec.dtl_attribute24 := p_formula_obj.formula_details(j).dtl_attribute24;
l_formula_insert_detail_rec.dtl_attribute25 := p_formula_obj.formula_details(j).dtl_attribute25;
l_formula_insert_detail_rec.dtl_attribute26 := p_formula_obj.formula_details(j).dtl_attribute26;
l_formula_insert_detail_rec.dtl_attribute27 := p_formula_obj.formula_details(j).dtl_attribute27;
l_formula_insert_detail_rec.dtl_attribute28 := p_formula_obj.formula_details(j).dtl_attribute28;
l_formula_insert_detail_rec.dtl_attribute29 := p_formula_obj.formula_details(j).dtl_attribute29;
l_formula_insert_detail_rec.dtl_attribute30 := p_formula_obj.formula_details(j).dtl_attribute30;
l_formula_insert_detail_tbl(l_insert_detailctr) := l_formula_insert_detail_rec;
l_insert_detailctr := l_insert_detailctr + 1;
l_formula_update_detail_rec.formula_id := p_formula_obj.formula_details(j).formula_id;
l_formula_update_detail_rec.formulaline_id := p_formula_obj.formula_details(j).formulaline_id;
l_formula_update_detail_rec.owner_organization_id := p_formula_obj.formula_details(j).owner_organization_id;
l_formula_update_detail_rec.line_type := p_formula_obj.formula_details(j).line_type;
l_formula_update_detail_rec.line_no := NULL;
l_formula_update_detail_rec.inventory_item_id := p_formula_obj.formula_details(j).inventory_item_id;
l_formula_update_detail_rec.revision := p_formula_obj.formula_details(j).revision;
l_formula_update_detail_rec.qty := p_formula_obj.formula_details(j).qty;
l_formula_update_detail_rec.detail_uom := p_formula_obj.formula_details(j).detail_uom;
l_formula_update_detail_rec.release_type := p_formula_obj.formula_details(j).release_type;
l_formula_update_detail_rec.scrap_factor := p_formula_obj.formula_details(j).scrap_factor;
l_formula_update_detail_rec.scale_type_dtl := p_formula_obj.formula_details(j).scale_type_dtl;
l_formula_update_detail_rec.cost_alloc := p_formula_obj.formula_details(j).cost_alloc;
l_formula_update_detail_rec.phantom_type := p_formula_obj.formula_details(j).phantom_type;
l_formula_update_detail_rec.buffer_ind := p_formula_obj.formula_details(j).buffer_ind;
l_formula_update_detail_rec.tpformula_id := p_formula_obj.formula_details(j).tpformula_id;
l_formula_update_detail_rec.iaformula_id := p_formula_obj.formula_details(j).iaformula_id;
l_formula_update_detail_rec.scale_multiple := p_formula_obj.formula_details(j).scale_multiple;
l_formula_update_detail_rec.contribute_yield_ind := p_formula_obj.formula_details(j).contribute_yield_ind;
l_formula_update_detail_rec.scale_uom := p_formula_obj.formula_details(j).scale_uom;
l_formula_update_detail_rec.contribute_step_qty_ind := p_formula_obj.formula_details(j).contribute_step_qty_ind;
l_formula_update_detail_rec.scale_rounding_variance := p_formula_obj.formula_details(j).scale_rounding_variance;
l_formula_update_detail_rec.rounding_direction := p_formula_obj.formula_details(j).rounding_direction;
l_formula_update_detail_rec.by_product_type := p_formula_obj.formula_details(j).by_product_type;
l_formula_update_detail_rec.ingredient_end_date := p_formula_obj.formula_details(j).ingredient_end_date;
l_formula_update_detail_rec.text_code_dtl := p_formula_obj.formula_details(j).text_code_dtl;
l_formula_update_detail_rec.attribute_category := p_formula_obj.formula_details(j).dtl_attribute_category;
l_formula_update_detail_rec.attribute1 := p_formula_obj.formula_details(j).dtl_attribute1;
l_formula_update_detail_rec.attribute2 := p_formula_obj.formula_details(j).dtl_attribute2;
l_formula_update_detail_rec.attribute3 := p_formula_obj.formula_details(j).dtl_attribute3;
l_formula_update_detail_rec.attribute4 := p_formula_obj.formula_details(j).dtl_attribute4;
l_formula_update_detail_rec.attribute5 := p_formula_obj.formula_details(j).dtl_attribute5;
l_formula_update_detail_rec.attribute6 := p_formula_obj.formula_details(j).dtl_attribute6;
l_formula_update_detail_rec.attribute7 := p_formula_obj.formula_details(j).dtl_attribute7;
l_formula_update_detail_rec.attribute8 := p_formula_obj.formula_details(j).dtl_attribute8;
l_formula_update_detail_rec.attribute9 := p_formula_obj.formula_details(j).dtl_attribute9;
l_formula_update_detail_rec.attribute10 := p_formula_obj.formula_details(j).dtl_attribute10;
l_formula_update_detail_rec.attribute11 := p_formula_obj.formula_details(j).dtl_attribute11;
l_formula_update_detail_rec.attribute12 := p_formula_obj.formula_details(j).dtl_attribute12;
l_formula_update_detail_rec.attribute13 := p_formula_obj.formula_details(j).dtl_attribute13;
l_formula_update_detail_rec.attribute14 := p_formula_obj.formula_details(j).dtl_attribute14;
l_formula_update_detail_rec.attribute15 := p_formula_obj.formula_details(j).dtl_attribute15;
l_formula_update_detail_rec.attribute16 := p_formula_obj.formula_details(j).dtl_attribute16;
l_formula_update_detail_rec.attribute17 := p_formula_obj.formula_details(j).dtl_attribute17;
l_formula_update_detail_rec.attribute18 := p_formula_obj.formula_details(j).dtl_attribute18;
l_formula_update_detail_rec.attribute19 := p_formula_obj.formula_details(j).dtl_attribute19;
l_formula_update_detail_rec.attribute20 := p_formula_obj.formula_details(j).dtl_attribute20;
l_formula_update_detail_rec.attribute21 := p_formula_obj.formula_details(j).dtl_attribute21;
l_formula_update_detail_rec.attribute22 := p_formula_obj.formula_details(j).dtl_attribute22;
l_formula_update_detail_rec.attribute23 := p_formula_obj.formula_details(j).dtl_attribute23;
l_formula_update_detail_rec.attribute24 := p_formula_obj.formula_details(j).dtl_attribute24;
l_formula_update_detail_rec.attribute25 := p_formula_obj.formula_details(j).dtl_attribute25;
l_formula_update_detail_rec.attribute26 := p_formula_obj.formula_details(j).dtl_attribute26;
l_formula_update_detail_rec.attribute27 := p_formula_obj.formula_details(j).dtl_attribute27;
l_formula_update_detail_rec.attribute28 := p_formula_obj.formula_details(j).dtl_attribute28;
l_formula_update_detail_rec.attribute29 := p_formula_obj.formula_details(j).dtl_attribute29;
l_formula_update_detail_rec.attribute30 := p_formula_obj.formula_details(j).dtl_attribute30;
l_formula_update_detail_rec.created_by := FND_GLOBAL.USER_ID;
l_formula_update_detail_rec.creation_date := NVL(p_formula_obj.formula_details(j).creation_date, SYSDATE);
l_formula_update_detail_rec.last_update_date := NVL(p_formula_obj.formula_details(j).last_update_date, SYSDATE);
l_formula_update_detail_rec.last_update_login := NVL(p_formula_obj.formula_details(j).last_update_login, FND_GLOBAL.USER_ID);
l_formula_update_detail_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_formula_update_detail_tbl(l_update_detailctr) := l_formula_update_detail_rec;
l_update_detailctr := l_update_detailctr + 1;
IF ( l_formula_insert_header_tbl.COUNT > 0) THEN
GMD_FORMULA_PUB.Insert_Formula
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_called_from_forms => 'NO'
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_formula_header_tbl => l_formula_insert_header_tbl
,p_allow_zero_ing_qty => l_zero_ing_qty
);
IF ( l_formula_update_header_tbl.COUNT > 0) THEN
GMD_FORMULA_PUB.Update_FormulaHeader
( p_api_version => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_called_from_forms => 'NO'
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_formula_header_tbl => l_formula_update_header_tbl
);
IF ( l_formula_update_detail_tbl.COUNT > 0) THEN
Delete_FormulaDetail(
p_formula_detail_tbl => l_formula_update_detail_tbl
,x_line_delete_tbl => l_line_delete_tbl
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
x_line_delete_tbl := l_line_delete_tbl;
GMD_FORMULA_DETAIL_PUB.Update_FormulaDetail
( p_api_version => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_called_from_forms => 'NO'
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_formula_detail_tbl => l_formula_update_detail_tbl
);
IF ( l_formula_insert_detail_tbl.COUNT > 0) THEN
GMD_FORMULA_DETAIL_PUB.Insert_FormulaDetail
( p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_called_from_forms => 'NO'
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_formula_detail_tbl =>l_formula_insert_detail_tbl
);
SELECT Count(*) INTO l_msg_count FROM fm_matl_dtl WHERE formula_id=470;
l_formula_obj_updated gmd_ebi_formula_obj;
l_line_delete_tbl gmd_ebi_formula_line_del_tbl;
update_org_ids(
p_formula_obj => l_formula_obj_inp
,x_formula_obj => l_formula_obj
,x_msg_count => l_msg_count
,x_return_status => l_ret_status
,x_msg_data => l_ret_msg
);
update_item_ids(
p_formula_obj => l_formula_obj
,x_formula_obj => l_formula_obj_updated
,x_return_status => l_ret_status
,x_msg_data => l_ret_msg
);
p_formula_obj => l_formula_obj_updated
,x_return_status => l_ret_status
,x_msg_data => l_ret_msg
);
IF l_formula_obj_updated.formula_details.Count < 2 THEN
FND_MESSAGE.SET_NAME('GMD', 'FM_SAVE_FORMULA_ERR');
FND_MESSAGE.SET_TOKEN('FORMULA_NO', l_formula_obj_updated.formula_header.formula_no);
FND_MESSAGE.SET_TOKEN('FORMULA_VERS', l_formula_obj_updated.formula_header.formula_vers);
p_formula_obj => l_formula_obj_updated
,x_line_delete_tbl => l_line_delete_tbl
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_ret_msg
);
p_input => l_formula_obj_updated
,p_line_delete_tbl => l_line_delete_tbl
,status_flag => l_status
,x_output => l_output
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_ret_msg
);