[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM FM_TEXT_TBL_TL
WHERE text_code = p_text_code;
l_insert varchar2(1);
l_insert := 'F';
DELETE FROM FM_TEXT_TBL_TL
WHERE text_code = p_text_code;
l_insert := 'T';
IF l_insert = 'T' THEN
FOR i IN 1..edit_text_tbl.count LOOP
IF edit_text_tbl(i).text_code = p_text_code THEN
INSERT INTO FM_TEXT_TBL_TL(
TEXT_CODE,
LANG_CODE,
PARAGRAPH_CODE,
SUB_PARACODE,
LINE_NO,
TEXT,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
LAST_UPDATE_LOGIN )
VALUES (
edit_text_tbl(i).text_code,
edit_text_tbl(i).lang_code,
edit_text_tbl(i).paragraph_code,
edit_text_tbl(i).sub_paracode,
edit_text_tbl(i).line_no,
edit_text_tbl(i).text,
edit_text_tbl(i).language,
edit_text_tbl(i).source_lang,
edit_text_tbl(i).last_updated_by,
edit_text_tbl(i).created_by,
edit_text_tbl(i).last_update_date,
edit_text_tbl(i).creation_date,
edit_text_tbl(i).last_update_login);
l_insert := 'F';
DELETE FROM FM_TEXT_TBL_TL
WHERE TEXT_CODE = edit_text_tbl(i).text_code;
INSERT INTO FM_TEXT_TBL_TL(
TEXT_CODE,
LANG_CODE,
PARAGRAPH_CODE,
SUB_PARACODE,
LINE_NO,
TEXT,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
LAST_UPDATE_LOGIN )
VALUES (
edit_text_tbl(i).text_code,
edit_text_tbl(i).lang_code,
edit_text_tbl(i).paragraph_code,
edit_text_tbl(i).sub_paracode,
edit_text_tbl(i).line_no,
edit_text_tbl(i).text,
edit_text_tbl(i).language,
edit_text_tbl(i).source_lang,
edit_text_tbl(i).last_updated_by,
edit_text_tbl(i).created_by,
edit_text_tbl(i).last_update_date,
edit_text_tbl(i).creation_date,
edit_text_tbl(i).last_update_login);
SELECT gem5_routing_id_s.NEXTVAL
FROM FND_DUAL;
SELECT *
FROM fm_rout_hdr
WHERE routing_id = p_routing_id;
SELECT MAX(routing_vers) + 1
FROM fm_rout_hdr
WHERE routing_no = X_hdr_rec.routing_no;
SELECT *
FROM fm_rout_dtl
WHERE routing_id = p_routing_id;
/* Insert header record */
IF X_hdr_rec.TEXT_CODE IS NOT NULL THEN
l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_hdr_rec.TEXT_CODE,
'FM_TEXT_TBL_TL',
'FM_TEXT_TBL_TL');
GMD_ROUTINGS_PKG.INSERT_ROW(
X_ROWID => l_ROWID,
X_ROUTING_ID => X_ROUTING_ID,
X_OWNER_ORGANIZATION_ID => X_HDR_rec.OWNER_ORGANIZATION_ID,
X_ROUTING_NO => X_HDR_rec.ROUTING_NO,
X_ROUTING_VERS => X_ROUTING_VERS,
X_ROUTING_CLASS => X_HDR_rec.ROUTING_CLASS,
X_ENFORCE_STEP_DEPENDENCY => X_hdr_rec.enforce_step_dependency,
X_CONTIGUOUS_IND => X_hdr_rec.contiguous_ind,
X_ROUTING_QTY => X_HDR_rec.ROUTING_QTY,
X_ROUTING_UOM => X_HDR_rec.ROUTING_UOM,
X_DELETE_MARK => 0,
X_TEXT_CODE => l_text_code,
X_INACTIVE_IND => 0,
X_IN_USE => 0,
X_ATTRIBUTE1 => X_HDR_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => X_HDR_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => X_HDR_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => X_HDR_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => X_HDR_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => X_HDR_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => X_HDR_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => X_HDR_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => X_HDR_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => X_HDR_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => X_HDR_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => X_HDR_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => X_HDR_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => X_HDR_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => X_HDR_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => X_HDR_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => X_HDR_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => X_HDR_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => X_HDR_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => X_HDR_rec.ATTRIBUTE20,
X_ATTRIBUTE21 => X_HDR_rec.ATTRIBUTE21,
X_ATTRIBUTE22 => X_HDR_rec.ATTRIBUTE22,
X_ATTRIBUTE23 => X_HDR_rec.ATTRIBUTE23,
X_ATTRIBUTE24 => X_HDR_rec.ATTRIBUTE24,
X_ATTRIBUTE25 => X_HDR_rec.ATTRIBUTE25,
X_ATTRIBUTE26 => X_HDR_rec.ATTRIBUTE26,
X_ATTRIBUTE27 => X_HDR_rec.ATTRIBUTE27,
X_ATTRIBUTE28 => X_HDR_rec.ATTRIBUTE28,
X_ATTRIBUTE29 => X_HDR_rec.ATTRIBUTE29,
X_ATTRIBUTE30 => X_HDR_rec.ATTRIBUTE30,
X_ATTRIBUTE_CATEGORY => X_HDR_rec.ATTRIBUTE_CATEGORY,
X_EFFECTIVE_START_DATE => X_HDR_rec.EFFECTIVE_START_DATE,
X_EFFECTIVE_END_DATE => X_HDR_rec.EFFECTIVE_END_DATE,
X_OWNER_ID => X_HDR_rec.OWNER_ID,
X_PROJECT_ID => X_HDR_rec.PROJECT_ID,
X_PROCESS_LOSS => X_HDR_rec.PROCESS_LOSS,
X_ROUTING_STATUS => 100,
X_ROUTING_DESC => X_HDR_rec.ROUTING_DESC,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => P_created_by,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => P_created_by,
X_LAST_UPDATE_LOGIN => P_login_id);
/* Insert detail records */
FOR i IN 1..X_dtl_tbl.count LOOP
IF X_dtl_tbl(i).text_code IS NOT NULL THEN
l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_dtl_tbl(i).text_code,
'FM_TEXT_TBL_TL',
'FM_TEXT_TBL_TL');
INSERT INTO fm_rout_dtl
(routing_id, routingstep_no, routingstep_id, oprn_id,
step_qty, steprelease_type, text_code, creation_date,
created_by, last_update_login, last_update_date,
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)
VALUES (x_routing_id, X_dtl_tbl(i).routingstep_no,
gem5_routingstep_id_s.NEXTVAL, X_dtl_tbl(i).oprn_id,
X_dtl_tbl(i).step_qty, X_dtl_tbl(i).steprelease_type,
l_text_code, SYSDATE, P_created_by, P_login_id, SYSDATE,
-- Bug #2673008 (JKB) Changed above.
P_created_by, X_dtl_tbl(i).attribute1, X_dtl_tbl(i).attribute2,
X_dtl_tbl(i).attribute3, X_dtl_tbl(i).attribute4,
X_dtl_tbl(i).attribute5, X_dtl_tbl(i).attribute6,
X_dtl_tbl(i).attribute7, X_dtl_tbl(i).attribute8,
X_dtl_tbl(i).attribute9, X_dtl_tbl(i).attribute10,
X_dtl_tbl(i).attribute11, X_dtl_tbl(i).attribute12,
X_dtl_tbl(i).attribute13, X_dtl_tbl(i).attribute14,
X_dtl_tbl(i).attribute15, X_dtl_tbl(i).attribute16,
X_dtl_tbl(i).attribute17, X_dtl_tbl(i).attribute18,
X_dtl_tbl(i).attribute19, X_dtl_tbl(i).attribute20,
X_dtl_tbl(i).attribute21, X_dtl_tbl(i).attribute22,
X_dtl_tbl(i).attribute23, X_dtl_tbl(i).attribute24,
X_dtl_tbl(i).attribute25, X_dtl_tbl(i).attribute26,
X_dtl_tbl(i).attribute27, X_dtl_tbl(i).attribute28,
X_dtl_tbl(i).attribute29, X_dtl_tbl(i).attribute30,
X_dtl_tbl(i).attribute_category);
SELECT GEM5_OPRN_ID_S.NEXTVAL
FROM FND_DUAL;
SELECT GEM5_OPRNLINE_ID_S.NEXTVAL
FROM FND_DUAL;
SELECT *
FROM gmd_operations_vl
WHERE oprn_id = p_oprn_id;
SELECT MAX(oprn_vers) + 1
FROM gmd_operations_vl
WHERE oprn_no = X_hdr_rec.oprn_no;
SELECT *
FROM gmd_operation_activities
WHERE oprn_id = p_oprn_id
ORDER BY oprn_line_id;
SELECT *
FROM gmd_operation_resources
WHERE oprn_line_id = V_oprn_line_id;
SELECT *
FROM GMD_OPRN_PROCESS_PARAMETERS_V1
where oprn_line_id = V_oprn_line_id
order by oprn_line_id;
/* Insert header record */
IF X_hdr_rec.TEXT_CODE IS NOT NULL THEN
l_text_code := GMA_EDITTEXT_PKG.Copy_Text(X_hdr_rec.TEXT_CODE,
'FM_TEXT_TBL_TL',
'FM_TEXT_TBL_TL');
GMD_OPERATIONS_PKG.INSERT_ROW(
X_ROWID => l_rowid ,
X_OPRN_ID => x_oprn_id,
X_ATTRIBUTE30 => X_HDR_rec.ATTRIBUTE30,
X_ATTRIBUTE_CATEGORY => X_HDR_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE25 => X_HDR_rec.ATTRIBUTE25,
X_ATTRIBUTE26 => X_HDR_rec.ATTRIBUTE26,
X_ATTRIBUTE27 => X_HDR_rec.ATTRIBUTE27,
X_ATTRIBUTE28 => X_HDR_rec.ATTRIBUTE28,
X_ATTRIBUTE29 => X_HDR_rec.ATTRIBUTE29,
X_ATTRIBUTE22 => X_HDR_rec.ATTRIBUTE22,
X_ATTRIBUTE23 => X_HDR_rec.ATTRIBUTE23,
X_ATTRIBUTE24 => X_HDR_rec.ATTRIBUTE24,
X_OPRN_NO => X_HDR_rec.OPRN_NO,
X_OPRN_VERS => X_OPRN_VERS,
X_PROCESS_QTY_UOM => X_HDR_rec.PROCESS_QTY_UOM,
X_OPRN_CLASS => X_HDR_rec.OPRN_CLASS,
X_INACTIVE_IND => 0,
X_EFFECTIVE_START_DATE => X_HDR_rec.EFFECTIVE_START_DATE,
X_EFFECTIVE_END_DATE => X_HDR_rec.EFFECTIVE_END_DATE,
X_DELETE_MARK => 0,
X_TEXT_CODE => l_text_code,
-- Bug #2673008 (JKB) Changed above.
X_ATTRIBUTE1 => X_HDR_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => X_HDR_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => X_HDR_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => X_HDR_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => X_HDR_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => X_HDR_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => X_HDR_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => X_HDR_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => X_HDR_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => X_HDR_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => X_HDR_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => X_HDR_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => X_HDR_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => X_HDR_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => X_HDR_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => X_HDR_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => X_HDR_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => X_HDR_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => X_HDR_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => X_HDR_rec.ATTRIBUTE20,
X_ATTRIBUTE21 => X_HDR_rec.ATTRIBUTE21,
X_OPERATION_STATUS => 100,
X_OWNER_ORGANIZATION_ID => X_HDR_rec.OWNER_ORGANIZATION_ID,
X_OPRN_DESC => X_HDR_rec.OPRN_DESC,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => P_CREATED_BY,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => P_CREATED_BY,
X_LAST_UPDATE_LOGIN => P_login_id);
/* Insert Activities */
X_rsrc_cnt := 0;
INSERT INTO gmd_operation_activities (oprn_id, oprn_line_id, activity,
activity_factor, delete_mark, text_code, last_updated_by,
created_by, last_update_date, creation_date,
last_update_login, 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,
offset_interval, break_ind, max_break)
VALUES (x_oprn_id, X_oprn_line_id, X_actv_tbl(i).activity,
X_actv_tbl(i).activity_factor, 0, l_text_code, P_created_by,
-- Bug #2673008 (JKB) Changed above.
P_created_by, SYSDATE, SYSDATE, P_login_id,
X_actv_tbl(i).attribute1, X_actv_tbl(i).attribute2,
X_actv_tbl(i).attribute3, X_actv_tbl(i).attribute4,
X_actv_tbl(i).attribute5, X_actv_tbl(i).attribute6,
X_actv_tbl(i).attribute7, X_actv_tbl(i).attribute8,
X_actv_tbl(i).attribute9, X_actv_tbl(i).attribute10,
X_actv_tbl(i).attribute11, X_actv_tbl(i).attribute12,
X_actv_tbl(i).attribute13, X_actv_tbl(i).attribute14,
X_actv_tbl(i).attribute15, X_actv_tbl(i).attribute16,
X_actv_tbl(i).attribute17, X_actv_tbl(i).attribute18,
X_actv_tbl(i).attribute19, X_actv_tbl(i).attribute20,
X_actv_tbl(i).attribute21, X_actv_tbl(i).attribute22,
X_actv_tbl(i).attribute23, X_actv_tbl(i).attribute24,
X_actv_tbl(i).attribute25, X_actv_tbl(i).attribute26,
X_actv_tbl(i).attribute27, X_actv_tbl(i).attribute28,
X_actv_tbl(i).attribute29, X_actv_tbl(i).attribute30,
X_actv_tbl(i).attribute_category,
X_actv_tbl(i).offset_interval, X_actv_tbl(i).break_ind,
X_actv_tbl(i).max_break);
INSERT INTO gmd_operation_resources (oprn_line_id, resources,
resource_usage, resource_count, process_qty,
prim_rsrc_ind, scale_type, cost_analysis_code,
cost_cmpntcls_id, usage_um, offset_interval,
delete_mark, text_code, created_by, last_updated_by,
last_update_date, creation_date, last_update_login,
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_uom, min_capacity, max_capacity, capacity_uom,
process_parameter_1, process_parameter_2,
process_parameter_3, process_parameter_4,
process_parameter_5,RESOURCE_USAGE_UOM,
RESOURCE_PROCESS_UOM ,RESOURCE_CAPACITY_UOM)--bug 4186561
VALUES (X_oprn_line_id, X_rsrc_tbl(X_rsrc_cnt).resources,
X_rsrc_tbl(X_rsrc_cnt).resource_usage,
X_rsrc_tbl(X_rsrc_cnt).resource_count,
X_rsrc_tbl(X_rsrc_cnt).process_qty,
X_rsrc_tbl(X_rsrc_cnt).prim_rsrc_ind,
X_rsrc_tbl(X_rsrc_cnt).scale_type,
X_rsrc_tbl(X_rsrc_cnt).cost_analysis_code,
X_rsrc_tbl(X_rsrc_cnt).cost_cmpntcls_id,
X_rsrc_tbl(X_rsrc_cnt).usage_um,
X_rsrc_tbl(X_rsrc_cnt).offset_interval,
X_rsrc_tbl(X_rsrc_cnt).delete_mark, l_text_code,
-- Bug #2673008 (JKB) Changed above.
P_created_by, P_created_by, SYSDATE, SYSDATE,
P_login_id, X_rsrc_tbl(X_rsrc_cnt).attribute1,
X_rsrc_tbl(X_rsrc_cnt).attribute2,
X_rsrc_tbl(X_rsrc_cnt).attribute3,
X_rsrc_tbl(X_rsrc_cnt).attribute4,
X_rsrc_tbl(X_rsrc_cnt).attribute5,
X_rsrc_tbl(X_rsrc_cnt).attribute6,
X_rsrc_tbl(X_rsrc_cnt).attribute7,
X_rsrc_tbl(X_rsrc_cnt).attribute8,
X_rsrc_tbl(X_rsrc_cnt).attribute9,
X_rsrc_tbl(X_rsrc_cnt).attribute10,
X_rsrc_tbl(X_rsrc_cnt).attribute11,
X_rsrc_tbl(X_rsrc_cnt).attribute12,
X_rsrc_tbl(X_rsrc_cnt).attribute13,
X_rsrc_tbl(X_rsrc_cnt).attribute14,
X_rsrc_tbl(X_rsrc_cnt).attribute15,
X_rsrc_tbl(X_rsrc_cnt).attribute16,
X_rsrc_tbl(X_rsrc_cnt).attribute17,
X_rsrc_tbl(X_rsrc_cnt).attribute18,
X_rsrc_tbl(X_rsrc_cnt).attribute19,
X_rsrc_tbl(X_rsrc_cnt).attribute20,
X_rsrc_tbl(X_rsrc_cnt).attribute21,
X_rsrc_tbl(X_rsrc_cnt).attribute22,
X_rsrc_tbl(X_rsrc_cnt).attribute23,
X_rsrc_tbl(X_rsrc_cnt).attribute24,
X_rsrc_tbl(X_rsrc_cnt).attribute25,
X_rsrc_tbl(X_rsrc_cnt).attribute26,
X_rsrc_tbl(X_rsrc_cnt).attribute27,
X_rsrc_tbl(X_rsrc_cnt).attribute28,
X_rsrc_tbl(X_rsrc_cnt).attribute29,
X_rsrc_tbl(X_rsrc_cnt).attribute30,
X_rsrc_tbl(X_rsrc_cnt).attribute_category,
X_rsrc_tbl(X_rsrc_cnt).process_uom,
X_rsrc_tbl(X_rsrc_cnt).min_capacity,
X_rsrc_tbl(X_rsrc_cnt).max_capacity,
X_rsrc_tbl(X_rsrc_cnt).capacity_uom,
X_rsrc_tbl(X_rsrc_cnt).process_parameter_1,
X_rsrc_tbl(X_rsrc_cnt).process_parameter_2,
X_rsrc_tbl(X_rsrc_cnt).process_parameter_3,
X_rsrc_tbl(X_rsrc_cnt).process_parameter_4,
X_rsrc_tbl(X_rsrc_cnt).process_parameter_5,
X_rsrc_tbl(X_rsrc_cnt).RESOURCE_USAGE_UOM,
X_rsrc_tbl(X_rsrc_cnt).RESOURCE_PROCESS_UOM,
X_rsrc_tbl(X_rsrc_cnt).RESOURCE_CAPACITY_UOM);--bug 4186561
INSERT INTO GMD_OPRN_PROCESS_PARAMETERS(OPRN_LINE_ID ,
RESOURCES ,
PARAMETER_ID ,
TARGET_VALUE ,
MINIMUM_VALUE ,
MAXIMUM_VALUE ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY)
VALUES (X_oprn_line_id,
X_parm_tbl(j).resources,
x_parm_tbl(j).parameter_id,
x_parm_tbl(j).target_value,
x_parm_tbl(j).minimum_value,
x_parm_tbl(j).maximum_value,
SYSDATE,
P_created_by,
P_created_by,
SYSDATE,
P_login_id);
SELECT *
FROM gmd_recipes
WHERE recipe_id = p_recipe_id;
SELECT *
FROM gmd_recipe_process_loss
WHERE recipe_id = p_recipe_id;
SELECT *
FROM gmd_recipe_customers
WHERE recipe_id = p_recipe_id;
SELECT *
FROM gmd_recipe_routing_steps
WHERE recipe_id = p_recipe_id;
SELECT *
FROM gmd_recipe_validity_rules
WHERE recipe_id = p_recipe_id;
SELECT *
FROM gmd_recipe_orgn_activities
WHERE recipe_id = p_recipe_id;
SELECT *
FROM gmd_recipe_orgn_resources
WHERE recipe_id = p_recipe_id;
SELECT *
FROM gmd_recipe_step_materials
WHERE recipe_id = p_recipe_id;
SELECT MAX(recipe_version) + 1
FROM gmd_recipes
WHERE recipe_no = X_hdr_rec.recipe_no;
SELECT gmd_recipe_id_s.NEXTVAL
FROM FND_DUAL;
SELECT formula_id, routing_id
FROM gmd_recipes_b
WHERE recipe_id = v_recipe_id;
gmd_recipes_mls.insert_row(X_ROWID => l_rowid,
X_RECIPE_ID => x_recipe_id,
X_OWNER_ID => X_hdr_rec.owner_id,
X_OWNER_LAB_TYPE => X_hdr_rec.owner_lab_type,
X_DELETE_MARK => 0,
X_TEXT_CODE => l_text_code,
-- Bug #2673008 (JKB) Changed above.
X_RECIPE_NO => X_hdr_rec.recipe_no,
X_RECIPE_VERSION => X_recipe_vers,
X_OWNER_ORGANIZATION_ID => X_hdr_rec.OWNER_ORGANIZATION_ID,
X_CREATION_ORGANIZATION_ID => X_hdr_rec.creation_ORGANIZATION_ID,
X_FORMULA_ID => X_hdr_rec.formula_id,
X_ROUTING_ID => X_hdr_rec.routing_id,
X_PROJECT_ID => X_hdr_rec.project_id,
X_RECIPE_STATUS => 100,
X_CALCULATE_STEP_QUANTITY => X_hdr_rec.calculate_step_quantity,
X_CONTIGUOUS_IND => X_hdr_rec.contiguous_ind,
X_PLANNED_PROCESS_LOSS => X_hdr_rec.planned_process_loss,
X_RECIPE_DESCRIPTION => X_hdr_rec.recipe_description,
X_ENHANCED_PI_IND => X_hdr_rec.enhanced_pi_ind,
X_RECIPE_TYPE => X_hdr_rec.recipe_type,
X_ATTRIBUTE_CATEGORY => X_hdr_rec.attribute_category,
X_ATTRIBUTE1 => X_hdr_rec.attribute1,
X_ATTRIBUTE2 => X_hdr_rec.attribute2,
X_ATTRIBUTE3 => X_hdr_rec.attribute3,
X_ATTRIBUTE4 => X_hdr_rec.attribute4,
X_ATTRIBUTE5 => X_hdr_rec.attribute5,
X_ATTRIBUTE6 => X_hdr_rec.attribute6,
X_ATTRIBUTE7 => X_hdr_rec.attribute7,
X_ATTRIBUTE8 => X_hdr_rec.attribute8,
X_ATTRIBUTE9 => X_hdr_rec.attribute9,
X_ATTRIBUTE10 => X_hdr_rec.attribute10,
X_ATTRIBUTE11 => X_hdr_rec.attribute11,
X_ATTRIBUTE12 => X_hdr_rec.attribute12,
X_ATTRIBUTE13 => X_hdr_rec.attribute13,
X_ATTRIBUTE14 => X_hdr_rec.attribute14,
X_ATTRIBUTE15 => X_hdr_rec.attribute15,
X_ATTRIBUTE16 => X_hdr_rec.attribute16,
X_ATTRIBUTE17 => X_hdr_rec.attribute17,
X_ATTRIBUTE18 => X_hdr_rec.attribute18,
X_ATTRIBUTE19 => X_hdr_rec.attribute19,
X_ATTRIBUTE20 => X_hdr_rec.attribute20,
X_ATTRIBUTE21 => X_hdr_rec.attribute21,
X_ATTRIBUTE22 => X_hdr_rec.attribute22,
X_ATTRIBUTE23 => X_hdr_rec.attribute23,
X_ATTRIBUTE24 => X_hdr_rec.attribute24,
X_ATTRIBUTE25 => X_hdr_rec.attribute25,
X_ATTRIBUTE26 => X_hdr_rec.attribute26,
X_ATTRIBUTE27 => X_hdr_rec.attribute27,
X_ATTRIBUTE28 => X_hdr_rec.attribute28,
X_ATTRIBUTE29 => X_hdr_rec.attribute29,
X_ATTRIBUTE30 => X_hdr_rec.attribute30,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => P_created_by,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => P_created_by,
X_LAST_UPDATE_LOGIN => P_login_id,
X_FIXED_PROCESS_LOSS => X_hdr_rec.fixed_process_loss, /* B6811759 */
X_FIXED_PROCESS_LOSS_UOM => X_hdr_rec.fixed_process_loss_uom
);
INSERT INTO gmd_recipe_process_loss(recipe_id, organization_id, process_loss,
creation_date, created_by,
last_updated_by, last_update_date, last_update_login,
recipe_process_loss_id, text_code,fixed_process_loss,fixed_process_loss_uom)
VALUES (x_recipe_id, X_proc_loss_tbl(i).organization_id,
X_proc_loss_tbl(i).process_loss,
SYSDATE, P_created_by, P_created_by, SYSDATE, P_login_id,
gmd_recipe_process_loss_id_s.NEXTVAL, l_text_code,
X_proc_loss_tbl(i).fixed_process_loss,
X_proc_loss_tbl(i).fixed_process_loss_uom);
INSERT INTO gmd_recipe_customers(recipe_id, customer_id,org_id,site_id, created_by,
creation_date, last_updated_by,
last_update_login, text_code, last_update_date)
VALUES (x_recipe_id, X_cust_tbl(i).customer_id,X_cust_tbl(i).org_id,
X_cust_tbl(i).site_id,P_created_by,
SYSDATE, P_created_by, P_login_id, l_text_code, SYSDATE);
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, X_step_tbl(i).routingstep_id,
X_step_tbl(i).step_qty, P_created_by, SYSDATE, SYSDATE,
P_login_id, l_text_code, P_created_by,
-- Bug #2673008 (JKB) Changed above.
X_step_tbl(i).attribute1, X_step_tbl(i).attribute2,
X_step_tbl(i).attribute3, X_step_tbl(i).attribute4,
X_step_tbl(i).attribute5, X_step_tbl(i).attribute6,
X_step_tbl(i).attribute7, X_step_tbl(i).attribute8,
X_step_tbl(i).attribute9, X_step_tbl(i).attribute10,
X_step_tbl(i).attribute11, X_step_tbl(i).attribute12,
X_step_tbl(i).attribute13, X_step_tbl(i).attribute14,
X_step_tbl(i).attribute15, X_step_tbl(i).attribute16,
X_step_tbl(i).attribute17, X_step_tbl(i).attribute18,
X_step_tbl(i).attribute19, X_step_tbl(i).attribute20,
X_step_tbl(i).attribute21, X_step_tbl(i).attribute22,
X_step_tbl(i).attribute23, X_step_tbl(i).attribute24,
X_step_tbl(i).attribute25, X_step_tbl(i).attribute26,
X_step_tbl(i).attribute27, X_step_tbl(i).attribute28,
X_step_tbl(i).attribute29, X_step_tbl(i).attribute30,
X_step_tbl(i).attribute_category,
X_step_tbl(i).mass_std_uom, X_step_tbl(i).volume_std_uom,
X_step_tbl(i).volume_qty, X_step_tbl(i).mass_qty);
INSERT INTO gmd_recipe_validity_rules(recipe_validity_rule_id, recipe_id,
organization_id, inventory_item_id, revision, 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 (gmd_recipe_validity_id_s.NEXTVAL, x_recipe_id,
X_vr_tbl(i).organization_id, X_vr_tbl(i).inventory_item_id,
X_vr_tbl(i).revision,X_vr_tbl(i).recipe_use, X_vr_tbl(i).preference,
X_vr_tbl(i).start_date, X_vr_tbl(i).end_date,
X_vr_tbl(i).min_qty, X_vr_tbl(i).max_qty,
X_vr_tbl(i).std_qty, X_vr_tbl(i).detail_uom,
X_vr_tbl(i).inv_min_qty, X_vr_tbl(i).inv_max_qty,
l_text_code, X_vr_tbl(i).attribute_category,
-- Bug #2673008 (JKB) Changed above.
X_vr_tbl(i).attribute1, X_vr_tbl(i).attribute2,
X_vr_tbl(i).attribute3, X_vr_tbl(i).attribute4,
X_vr_tbl(i).attribute5, X_vr_tbl(i).attribute6,
X_vr_tbl(i).attribute7, X_vr_tbl(i).attribute8,
X_vr_tbl(i).attribute9, X_vr_tbl(i).attribute10,
X_vr_tbl(i).attribute11, X_vr_tbl(i).attribute12,
X_vr_tbl(i).attribute13, X_vr_tbl(i).attribute14,
X_vr_tbl(i).attribute15, X_vr_tbl(i).attribute16,
X_vr_tbl(i).attribute17, X_vr_tbl(i).attribute18,
X_vr_tbl(i).attribute19, X_vr_tbl(i).attribute20,
X_vr_tbl(i).attribute21, X_vr_tbl(i).attribute22,
X_vr_tbl(i).attribute23, X_vr_tbl(i).attribute24,
X_vr_tbl(i).attribute25, X_vr_tbl(i).attribute26,
X_vr_tbl(i).attribute27, X_vr_tbl(i).attribute28,
X_vr_tbl(i).attribute29, X_vr_tbl(i).attribute30,
P_created_by, SYSDATE, P_created_by, SYSDATE,
P_login_id, 0, X_vr_tbl(i).lab_type, 100);
INSERT INTO gmd_recipe_orgn_activities(recipe_id, routingstep_id,
activity_factor, attribute_category, attribute1,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login, organization_id, 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, text_code,
oprn_line_id)
VALUES (x_recipe_id, X_actv_tbl(i).routingstep_id,
X_actv_tbl(i).activity_factor,
X_actv_tbl(i).attribute_category, X_actv_tbl(i).attribute1,
P_created_by, SYSDATE, P_created_by, SYSDATE, P_login_id,
X_actv_tbl(i).organization_id, X_actv_tbl(i).attribute2,
X_actv_tbl(i).attribute3, X_actv_tbl(i).attribute4,
X_actv_tbl(i).attribute5, X_actv_tbl(i).attribute6,
X_actv_tbl(i).attribute7, X_actv_tbl(i).attribute8,
X_actv_tbl(i).attribute9, X_actv_tbl(i).attribute10,
X_actv_tbl(i).attribute11, X_actv_tbl(i).attribute12,
X_actv_tbl(i).attribute13, X_actv_tbl(i).attribute14,
X_actv_tbl(i).attribute15, X_actv_tbl(i).attribute16,
X_actv_tbl(i).attribute17, X_actv_tbl(i).attribute18,
X_actv_tbl(i).attribute19, X_actv_tbl(i).attribute20,
X_actv_tbl(i).attribute21, X_actv_tbl(i).attribute22,
X_actv_tbl(i).attribute23, X_actv_tbl(i).attribute24,
X_actv_tbl(i).attribute25, X_actv_tbl(i).attribute26,
X_actv_tbl(i).attribute27, X_actv_tbl(i).attribute28,
X_actv_tbl(i).attribute29, X_actv_tbl(i).attribute30,
l_text_code, X_actv_tbl(i).oprn_line_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_um,
usage_uom, resource_usage, process_qty)
VALUES (x_recipe_id, X_rsrc_tbl(i).organization_id,
X_rsrc_tbl(i).routingstep_id, X_rsrc_tbl(i).oprn_line_id,
X_rsrc_tbl(i).resources, SYSDATE, P_created_by,
P_created_by, SYSDATE, X_rsrc_tbl(i).min_capacity,
X_rsrc_tbl(i).max_capacity, P_login_id, l_text_code,
-- Bug #2673008 (JKB) Changed above.
X_rsrc_tbl(i).attribute1, X_rsrc_tbl(i).attribute2,
X_rsrc_tbl(i).attribute3, X_rsrc_tbl(i).attribute4,
X_rsrc_tbl(i).attribute5, X_rsrc_tbl(i).attribute6,
X_rsrc_tbl(i).attribute7, X_rsrc_tbl(i).attribute8,
X_rsrc_tbl(i).attribute9, X_rsrc_tbl(i).attribute10,
X_rsrc_tbl(i).attribute11, X_rsrc_tbl(i).attribute12,
X_rsrc_tbl(i).attribute13, X_rsrc_tbl(i).attribute14,
X_rsrc_tbl(i).attribute15, X_rsrc_tbl(i).attribute16,
X_rsrc_tbl(i).attribute17, X_rsrc_tbl(i).attribute18,
X_rsrc_tbl(i).attribute19, X_rsrc_tbl(i).attribute20,
X_rsrc_tbl(i).attribute21, X_rsrc_tbl(i).attribute22,
X_rsrc_tbl(i).attribute23, X_rsrc_tbl(i).attribute24,
X_rsrc_tbl(i).attribute25, X_rsrc_tbl(i).attribute26,
X_rsrc_tbl(i).attribute27, X_rsrc_tbl(i).attribute28,
X_rsrc_tbl(i).attribute29, X_rsrc_tbl(i).attribute30,
X_rsrc_tbl(i).attribute_category,
X_rsrc_tbl(i).process_parameter_5,
X_rsrc_tbl(i).process_parameter_4,
X_rsrc_tbl(i).process_parameter_3,
X_rsrc_tbl(i).process_parameter_2,
X_rsrc_tbl(i).process_parameter_1,
X_rsrc_tbl(i).process_um, X_rsrc_tbl(i).usage_uom,
X_rsrc_tbl(i).resource_usage, X_rsrc_tbl(i).process_qty);
INSERT INTO gmd_recipe_step_materials(recipe_id, formulaline_id,
routingstep_id, text_code, creation_date, created_by,
-- Bug #2673008 (JKB) Changed above.
last_updated_by, last_update_date, last_update_login)
VALUES (x_recipe_id, X_stepmtl_tbl(i).formulaline_id,
X_stepmtl_tbl(i).routingstep_id, l_text_code, SYSDATE,
P_created_by, P_created_by, SYSDATE, P_login_id);
SELECT gem5_formula_id_s.NEXTVAL
FROM FND_DUAL;
SELECT *
FROM fm_form_mst
WHERE formula_id = p_formula_id;
SELECT MAX(formula_vers) + 1
FROM fm_form_mst
WHERE formula_no = X_hdr_rec.formula_no;
SELECT *
FROM fm_matl_dtl
WHERE formula_id = p_formula_id;
SELECT 1
FROM fnd_attached_documents fad
WHERE fad.entity_name = cp_entity_name
AND fad.pk1_value = cp_pk1_value;
FM_FORM_MST_MLS.INSERT_ROW(
X_ROWID => l_rowid,
X_FORMULA_ID => X_formula_id,
X_MASTER_FORMULA_ID => Null,
X_OWNER_ORGANIZATION_ID => x_hdr_rec.OWNER_ORGANIZATION_ID,
X_TOTAL_INPUT_QTY => 0,
X_TOTAL_OUTPUT_QTY => 0,
X_YIELD_UOM => NULL,
X_FORMULA_STATUS => 100,
X_OWNER_ID => X_hdr_rec.created_by,
X_PROJECT_ID => NULL,
X_TEXT_CODE => l_text_code,
X_DELETE_MARK => X_hdr_rec.DELETE_MARK,
X_FORMULA_NO => X_hdr_rec.formula_no,
X_FORMULA_VERS => X_formula_vers,
X_FORMULA_TYPE => X_hdr_rec.FORMULA_TYPE,
X_IN_USE => 0,
X_INACTIVE_IND => 0,
X_SCALE_TYPE => X_hdr_rec.SCALE_TYPE,
X_FORMULA_CLASS => X_hdr_rec.FORMULA_CLASS,
X_FMCONTROL_CLASS => X_hdr_rec.FMCONTROL_CLASS,
X_ATTRIBUTE_CATEGORY => X_hdr_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => X_hdr_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => X_hdr_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => X_hdr_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => X_hdr_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => X_hdr_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => X_hdr_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => X_hdr_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => X_hdr_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => X_hdr_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => X_hdr_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => X_hdr_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => X_hdr_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => X_hdr_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => X_hdr_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => X_hdr_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => X_hdr_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => X_hdr_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => X_hdr_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => X_hdr_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => X_hdr_rec.ATTRIBUTE20,
X_ATTRIBUTE21 => X_hdr_rec.ATTRIBUTE21,
X_ATTRIBUTE22 => X_hdr_rec.ATTRIBUTE22,
X_ATTRIBUTE23 => X_hdr_rec.ATTRIBUTE23,
X_ATTRIBUTE24 => X_hdr_rec.ATTRIBUTE24,
X_ATTRIBUTE25 => X_hdr_rec.ATTRIBUTE25,
X_ATTRIBUTE26 => X_hdr_rec.ATTRIBUTE26,
X_ATTRIBUTE27 => X_hdr_rec.ATTRIBUTE27,
X_ATTRIBUTE28 => X_hdr_rec.ATTRIBUTE28,
X_ATTRIBUTE29 => X_hdr_rec.ATTRIBUTE29,
X_ATTRIBUTE30 => X_hdr_rec.ATTRIBUTE30,
X_FORMULA_DESC1 => X_hdr_rec.FORMULA_DESC1,
X_FORMULA_DESC2 => X_hdr_rec.FORMULA_DESC2,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => P_created_by,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => P_created_by,
X_LAST_UPDATE_LOGIN => P_login_id,
X_AUTO_PRODUCT_CALC => X_hdr_rec.auto_product_calc);
X_last_update_login => FND_GLOBAL.LOGIN_ID,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL );
INSERT INTO fm_matl_dtl(formulaline_id, formula_id, line_type, line_no,
inventory_item_id, qty, detail_uom,revision, release_type, scrap_factor,
scale_type, cost_alloc, phantom_type, rework_type, text_code,
organization_id,last_updated_by, created_by, last_update_date, creation_date,
last_update_login, 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, tpformula_id,
scale_multiple, contribute_yield_ind, scale_uom,
contribute_step_qty_ind, scale_rounding_variance)
VALUES (gem5_formulaline_id_s.NEXTVAL, X_formula_id,
X_dtl_tbl(i).line_type, X_dtl_tbl(i).line_no,
X_dtl_tbl(i).inventory_item_id, X_dtl_tbl(i).qty,
X_dtl_tbl(i).detail_uom, X_dtl_tbl(i).revision, X_dtl_tbl(i).release_type,
X_dtl_tbl(i).scrap_factor, X_dtl_tbl(i).scale_type,
X_dtl_tbl(i).cost_alloc, X_dtl_tbl(i).phantom_type,
X_dtl_tbl(i).rework_type, l_text_code,X_dtl_tbl(i).organization_id,
-- Bug #2673008 (JKB) Changed above.
P_created_by, P_created_by, SYSDATE, SYSDATE, P_login_id,
X_dtl_tbl(i).attribute1, X_dtl_tbl(i).attribute2,
X_dtl_tbl(i).attribute3, X_dtl_tbl(i).attribute4,
X_dtl_tbl(i).attribute5, X_dtl_tbl(i).attribute6,
X_dtl_tbl(i).attribute7, X_dtl_tbl(i).attribute8,
X_dtl_tbl(i).attribute9, X_dtl_tbl(i).attribute10,
X_dtl_tbl(i).attribute11, X_dtl_tbl(i).attribute12,
X_dtl_tbl(i).attribute13, X_dtl_tbl(i).attribute14,
X_dtl_tbl(i).attribute15, X_dtl_tbl(i).attribute16,
X_dtl_tbl(i).attribute17, X_dtl_tbl(i).attribute18,
X_dtl_tbl(i).attribute19, X_dtl_tbl(i).attribute20,
X_dtl_tbl(i).attribute21, X_dtl_tbl(i).attribute22,
X_dtl_tbl(i).attribute23, X_dtl_tbl(i).attribute24,
X_dtl_tbl(i).attribute25, X_dtl_tbl(i).attribute26,
X_dtl_tbl(i).attribute27, X_dtl_tbl(i).attribute28,
X_dtl_tbl(i).attribute29, X_dtl_tbl(i).attribute30,
X_dtl_tbl(i).attribute_category, X_dtl_tbl(i).tpformula_id,
X_dtl_tbl(i).scale_multiple,
X_dtl_tbl(i).contribute_yield_ind, X_dtl_tbl(i).scale_uom,
X_dtl_tbl(i).contribute_step_qty_ind,
X_dtl_tbl(i).scale_rounding_variance)
RETURNING formulaline_id INTO l_formulaline_id;
/* Added a new procedure to calculate the total quantity and update the
formula master with the total output quantity. */
GMD_COMMON_VAL.calculate_total_qty(
formula_id => X_formula_id,
x_product_qty => l_product_qty ,
x_ingredient_qty => l_ing_qty ,
x_uom => l_uom ,
x_return_status => x_return_status ,
x_msg_count => X_msg_cnt ,
x_msg_data => x_msg_dat );
/* Update formula header table with TOQ and TIQ */
UPDATE fm_form_mst_b
SET total_output_qty = l_product_qty,
total_input_qty = l_ing_qty,
yield_uom = l_uom
WHERE formula_id = X_formula_id;
X_last_update_login => FND_GLOBAL.LOGIN_ID,
X_program_application_id => NULL,
X_program_id => NULL,
X_request_id => NULL );
SELECT gmd_item_substitution_hdr_s.nextval
FROM DUAL;
SELECT gmd_item_substitution_dtl_s.nextval
FROM DUAL;
SELECT gmd_formula_substitution_s.nextval
FROM DUAL;
SELECT *
FROM GMD_ITEM_SUBSTITUTION_HDR_VL
WHERE substitution_id = p_substitution_id;
SELECT MAX(SUBSTITUTION_VERSION) + 1 FROM GMD_ITEM_SUBSTITUTION_HDR_B
WHERE SUBSTITUTION_NAME = X_hdr_rec.SUBSTITUTION_NAME;
SELECT MAX(preference) + 1 FROM GMD_ITEM_SUBSTITUTION_HDR_B
WHERE ORIGINAL_INVENTORY_ITEM_ID = X_hdr_rec.ORIGINAL_INVENTORY_ITEM_ID
AND OWNER_ORGANIZATION_ID = X_hdr_rec.OWNER_ORGANIZATION_ID;
SELECT *
FROM GMD_ITEM_SUBSTITUTION_DTL
WHERE substitution_id = p_substitution_id;
SELECT *
FROM GMD_FORMULA_SUBSTITUTION
WHERE substitution_id = p_substitution_id;
GMD_ITEM_SUBSTITUTION_HDR_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_SUBSTITUTION_ID => x_substitution_id,
X_SUBSTITUTION_NAME => X_hdr_rec.SUBSTITUTION_NAME,
X_SUBSTITUTION_VERSION => X_hdr_rec.SUBSTITUTION_VERSION,
X_SUBSTITUTION_STATUS => 100,
X_ORIGINAL_INVENTORY_ITEM_ID => X_hdr_rec.ORIGINAL_INVENTORY_ITEM_ID,
X_ORIGINAL_UOM => X_hdr_rec.ORIGINAL_UOM,
X_ORIGINAL_QTY => X_hdr_rec.ORIGINAL_QTY,
X_PREFERENCE => X_hdr_rec.PREFERENCE,
X_START_DATE => X_hdr_rec.START_DATE,
X_END_DATE => X_hdr_rec.END_DATE,
X_OWNER_ORGANIZATION_ID => X_hdr_rec.OWNER_ORGANIZATION_ID,
X_REPLACEMENT_UOM_TYPE => X_hdr_rec.REPLACEMENT_UOM_TYPE,
X_ATTRIBUTE_CATEGORY => X_hdr_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => X_hdr_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => X_hdr_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => X_hdr_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => X_hdr_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => X_hdr_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => X_hdr_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => X_hdr_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => X_hdr_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => X_hdr_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => X_hdr_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => X_hdr_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => X_hdr_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => X_hdr_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => X_hdr_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => X_hdr_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => X_hdr_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => X_hdr_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => X_hdr_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => X_hdr_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => X_hdr_rec.ATTRIBUTE20,
X_ATTRIBUTE21 => X_hdr_rec.ATTRIBUTE21,
X_ATTRIBUTE22 => X_hdr_rec.ATTRIBUTE22,
X_ATTRIBUTE23 => X_hdr_rec.ATTRIBUTE23,
X_ATTRIBUTE24 => X_hdr_rec.ATTRIBUTE24,
X_ATTRIBUTE25 => X_hdr_rec.ATTRIBUTE25,
X_ATTRIBUTE26 => X_hdr_rec.ATTRIBUTE26,
X_ATTRIBUTE27 => X_hdr_rec.ATTRIBUTE27,
X_ATTRIBUTE28 => X_hdr_rec.ATTRIBUTE28,
X_ATTRIBUTE29 => X_hdr_rec.ATTRIBUTE29,
X_ATTRIBUTE30 => X_hdr_rec.ATTRIBUTE30,
X_SUBSTITUTION_DESCRIPTION => X_hdr_rec.SUBSTITUTION_DESCRIPTION,
X_CREATION_DATE => sysdate,
X_CREATED_BY => P_created_by,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => P_created_by,
X_LAST_UPDATE_LOGIN => P_login_id);
GMD_FORMULA_SUBSTITUTION_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_FORMULA_SUBSTITUTION_ID => l_formula_substitution_id,
X_SUBSTITUTION_ID => x_substitution_id,
X_FORMULA_ID => X_formula_sub_tbl(i).FORMULA_ID,
X_ASSOCIATED_FLAG => 'N',
X_CREATION_DATE => sysdate,
X_CREATED_BY => P_created_by,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => P_created_by,
X_LAST_UPDATE_LOGIN => P_login_id);
GMD_ITEM_SUBSTITUTION_DTL_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_SUBSTITUTION_LINE_ID => l_substitution_line_id,
X_SUBSTITUTION_ID => x_substitution_id,
X_INVENTORY_ITEM_ID => X_get_det_tbl(i).INVENTORY_ITEM_ID,
X_UNIT_QTY => X_get_det_tbl(i).UNIT_QTY,
X_DETAIL_UOM => X_get_det_tbl(i).DETAIL_UOM,
X_CREATION_DATE => sysdate,
X_CREATED_BY => P_created_by,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => P_created_by,
X_LAST_UPDATE_LOGIN => P_login_id);