[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_LAB_FORMULA IS
error_msg VARCHAR2(240);
SELECT *
FROM lm_form_mst
WHERE exists (SELECT distinct d.formula_id
FROM lm_form_dtl d
WHERE NVL(d.tpformula_id, 0) = 0
AND d.formula_id = formula_id)
order by formula_no, formula_vers;
SELECT *
FROM lm_form_mst
WHERE formula_id IN (SELECT distinct d.formula_id
FROM lm_form_dtl d
WHERE NVL(d.tpformula_id, 0) > 0
AND d.formula_id = formula_id)
order by formula_no, formula_vers;
SELECT formula_vers , formula_status
FROM fm_form_mst
WHERE formula_no = V_formula_no
AND formula_vers = V_formula_vers ;
select count(*)
FROM gmd_lab_formula_assoc
WHERE old_formula_id = v_formula_id;
SELECT max(formula_vers)
FROM fm_form_mst
WHERE formula_no = V_formula_no;
SELECT formula_id
FROM fm_form_mst
WHERE (formula_no, formula_vers)
IN (SELECT formula_no, formula_vers
FROM lm_form_mst
WHERE formula_id = V_formula_id);
select gem5_formula_id_s.nextval from sys.dual;
select * from lm_form_dtl
where formula_id = Vformula_id ;
select gem5_formulaline_id_s.nextval from sys.dual;
increase the version of formula to be inserted. */
X_flag := 'FALSE' ;
select fnd_profile.value_specific('GEMMS_DEFAULT_ORGN',lab_rec.created_by)
INTO l_orgn_code
FROM sys.dual;
/* Step 3 : To insert data ino the fm_form_mst_b and fm_form_mst_vl
table */
/* changed x_formula_vers to lab_rec.formula_vers */
/* BEGIN BUG#3217071*/
IF lab_rec.inactive_ind = 1 THEN
X_STATUS := 1000;
INSERT INTO FM_FORM_MST_B (formula_id, orgn_code, total_input_qty, total_output_qty, formula_status,
formula_uom, owner_id, attribute_category, text_code, delete_mark,
formula_no, formula_vers, formula_type, 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, inactive_ind, scale_type, formula_class,
fmcontrol_class, creation_date, created_by, last_update_date, last_updated_by,
last_update_login)
VALUES (xFormula_Id, l_orgn_code, 0, 0, X_status,
NULL, lab_rec.created_by, lab_rec.attribute_category, lab_rec.text_code, lab_rec.delete_mark,
lab_rec.formula_no, X_formula_vers, lab_rec.formula_type, lab_rec.attribute1, lab_rec.attribute2, lab_rec.attribute3,
lab_rec.attribute4, lab_rec.attribute5, lab_rec.attribute6, lab_rec.attribute7, lab_rec.attribute8, lab_rec.attribute9,
lab_rec.attribute10, lab_rec.attribute11, lab_rec.attribute12, lab_rec.attribute13, lab_rec.attribute14, lab_rec.attribute15,
lab_rec.attribute16, lab_rec.attribute17, lab_rec.attribute18, lab_rec.attribute19, lab_rec.attribute20, lab_rec.attribute21,
lab_rec.attribute22, lab_rec.attribute23, lab_rec.attribute24, lab_rec.attribute25, lab_rec.attribute26, lab_rec.attribute27,
lab_rec.attribute28, lab_rec.attribute29, lab_rec.attribute30, lab_rec.inactive_ind, lab_rec.scale_type, lab_rec.formula_class,
lab_rec.fmcontrol_class, lab_rec.creation_date, lab_rec.created_by, lab_rec.last_update_date, lab_rec.last_updated_by,
lab_rec.last_update_login);
INSERT INTO FM_FORM_MST_TL (formula_id, formula_desc1, formula_desc2, language, source_lang,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
SELECT xFormula_Id, lab_rec.formula_desc1, lab_rec.formula_desc2, l.language_code, userenv('LANG'),
lab_rec.created_by, lab_rec.creation_date, lab_rec.last_updated_by, lab_rec.last_update_date, lab_rec.last_update_login
FROM FND_LANGUAGES l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS (SELECT NULL FROM FM_FORM_MST_TL T WHERE t.formula_id = xFormula_Id AND t.language = l.language_code);
INSERT INTO GMD_LAB_FORMULA_ASSOC(old_formula_id,old_formula_no,old_formula_vers,
new_formula_id,new_formula_no,new_formula_vers
,migrated)
VALUES(lab_rec.formula_id,lab_rec.formula_no,lab_rec.formula_vers,
xformula_id,lab_rec.formula_no,x_formula_vers,'YES');
insert INTO FM_MATL_DTL (
FORMULALINE_ID ,
FORMULA_ID ,
LINE_TYPE ,
LINE_NO ,
ITEM_ID ,
QTY ,
ITEM_UM ,
RELEASE_TYPE ,
SCRAP_FACTOR ,
SCALE_TYPE ,
COST_ALLOC ,
PHANTOM_TYPE ,
REWORK_TYPE ,
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 ,
TPFORMULA_ID )
values (
Xformulaline_id ,
XFORMULA_ID ,
lab_detail_rec.LINE_TYPE ,
lab_detail_rec.LINE_NO ,
lab_detail_rec.ITEM_ID ,
lab_detail_rec.QTY ,
lab_detail_rec.ITEM_UM ,
lab_detail_rec.RELEASE_TYPE ,
lab_detail_rec.SCRAP_FACTOR ,
lab_detail_rec.SCALE_TYPE ,
lab_detail_rec.COST_ALLOC ,
0 ,
0 ,
lab_detail_rec.TEXT_CODE ,
lab_detail_rec.LAST_UPDATED_BY ,
lab_detail_rec.CREATED_BY ,
lab_detail_rec.LAST_UPDATE_DATE ,
lab_detail_rec.CREATION_DATE ,
lab_detail_rec.LAST_UPDATE_LOGIN ,
lab_detail_rec.ATTRIBUTE1 ,
lab_detail_rec.ATTRIBUTE2 ,
lab_detail_rec.ATTRIBUTE3 ,
lab_detail_rec.ATTRIBUTE4 ,
lab_detail_rec.ATTRIBUTE5 ,
lab_detail_rec.ATTRIBUTE6 ,
lab_detail_rec.ATTRIBUTE7 ,
lab_detail_rec.ATTRIBUTE8 ,
lab_detail_rec.ATTRIBUTE9 ,
lab_detail_rec.ATTRIBUTE10 ,
lab_detail_rec.ATTRIBUTE11 ,
lab_detail_rec.ATTRIBUTE12 ,
lab_detail_rec.ATTRIBUTE13 ,
lab_detail_rec.ATTRIBUTE14 ,
lab_detail_rec.ATTRIBUTE15 ,
lab_detail_rec.ATTRIBUTE16 ,
lab_detail_rec.ATTRIBUTE17 ,
lab_detail_rec.ATTRIBUTE18 ,
lab_detail_rec.ATTRIBUTE19 ,
lab_detail_rec.ATTRIBUTE20 ,
lab_detail_rec.ATTRIBUTE21 ,
lab_detail_rec.ATTRIBUTE22 ,
lab_detail_rec.ATTRIBUTE23 ,
lab_detail_rec.ATTRIBUTE24 ,
lab_detail_rec.ATTRIBUTE25 ,
lab_detail_rec.ATTRIBUTE26 ,
lab_detail_rec.ATTRIBUTE27 ,
lab_detail_rec.ATTRIBUTE28 ,
lab_detail_rec.ATTRIBUTE29 ,
lab_detail_rec.ATTRIBUTE30 ,
lab_detail_rec.ATTRIBUTE_CATEGORY ,
lab_detail_rec.TPFORMULA_ID );
INSERT INTO GMD_MIGRATION ( migration_id,
source_table,
target_table,
source_id ,
target_id ,
message_text )
SELECT GMD_REQUEST_ID_S.nextval,
'LM_FORM_MST AND LM_FORM_DTL',
'FM_FORM_MST AND MF_MATL_DTL',
lab_rec.formula_id,
Xformula_id,
error_msg
FROM DUAL;
increase the version of formula to be inserted. */
X_flag := 'FALSE' ;
select fnd_profile.value_specific('GEMMS_DEFAULT_ORGN',lab_rec.created_by)
INTO l_orgn_code
FROM sys.dual;
/* Step 3 : To insert data ino the fm_form_mst_b and fm_form_mst_vl
table */
INSERT INTO GMD_LAB_FORMULA_ASSOC(old_formula_id,old_formula_no,old_formula_vers,
new_formula_id,new_formula_no,new_formula_vers
,migrated)
VALUES(lab_rec.formula_id,lab_rec.formula_no,lab_rec.formula_vers,
xformula_id,lab_rec.formula_no,x_formula_vers,'YES');
INSERT INTO FM_FORM_MST_B (formula_id, orgn_code, total_input_qty, total_output_qty, formula_status,
formula_uom, owner_id, attribute_category, text_code, delete_mark,
formula_no, formula_vers, formula_type, 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, inactive_ind, scale_type, formula_class,
fmcontrol_class, creation_date, created_by, last_update_date, last_updated_by,
last_update_login)
VALUES (xFormula_Id, l_orgn_code, 0, 0, X_status,
NULL, lab_rec.created_by, lab_rec.attribute_category, lab_rec.text_code, lab_rec.delete_mark,
lab_rec.formula_no, X_formula_vers, lab_rec.formula_type, lab_rec.attribute1, lab_rec.attribute2, lab_rec.attribute3,
lab_rec.attribute4, lab_rec.attribute5, lab_rec.attribute6, lab_rec.attribute7, lab_rec.attribute8, lab_rec.attribute9,
lab_rec.attribute10, lab_rec.attribute11, lab_rec.attribute12, lab_rec.attribute13, lab_rec.attribute14, lab_rec.attribute15,
lab_rec.attribute16, lab_rec.attribute17, lab_rec.attribute18, lab_rec.attribute19, lab_rec.attribute20, lab_rec.attribute21,
lab_rec.attribute22, lab_rec.attribute23, lab_rec.attribute24, lab_rec.attribute25, lab_rec.attribute26, lab_rec.attribute27,
lab_rec.attribute28, lab_rec.attribute29, lab_rec.attribute30, lab_rec.inactive_ind, lab_rec.scale_type, lab_rec.formula_class,
lab_rec.fmcontrol_class, lab_rec.creation_date, lab_rec.created_by, lab_rec.last_update_date, lab_rec.last_updated_by,
lab_rec.last_update_login);
INSERT INTO FM_FORM_MST_TL (formula_id, formula_desc1, formula_desc2, language, source_lang,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
SELECT xFormula_Id, lab_rec.formula_desc1, lab_rec.formula_desc2, l.language_code, userenv('LANG'),
lab_rec.created_by, lab_rec.creation_date, lab_rec.last_updated_by, lab_rec.last_update_date, lab_rec.last_update_login
FROM FND_LANGUAGES l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS (SELECT NULL FROM FM_FORM_MST_TL T WHERE t.formula_id = xFormula_Id AND t.language = l.language_code);
INSERT INTO GMD_LAB_FORMULA_ASSOC(old_formula_id,old_formula_no,old_formula_vers,
new_formula_id,new_formula_no,new_formula_vers
,migrated,old_tp_formula_id,new_tp_formula_id)
VALUES(lab_rec.formula_id,lab_rec.formula_no,lab_rec.formula_vers,
xformula_id,lab_rec.formula_no,x_formula_vers,'YES',
lab_detail_rec.tpformula_id,X_tp_formula_id);
insert INTO FM_MATL_DTL (
FORMULALINE_ID ,
FORMULA_ID ,
LINE_TYPE ,
LINE_NO ,
ITEM_ID ,
QTY ,
ITEM_UM ,
RELEASE_TYPE ,
SCRAP_FACTOR ,
SCALE_TYPE ,
COST_ALLOC ,
PHANTOM_TYPE ,
REWORK_TYPE ,
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 ,
TPFORMULA_ID )
values (
Xformulaline_id ,
XFORMULA_ID ,
lab_detail_rec.LINE_TYPE ,
lab_detail_rec.LINE_NO ,
lab_detail_rec.ITEM_ID ,
lab_detail_rec.QTY ,
lab_detail_rec.ITEM_UM ,
lab_detail_rec.RELEASE_TYPE ,
lab_detail_rec.SCRAP_FACTOR ,
lab_detail_rec.SCALE_TYPE ,
lab_detail_rec.COST_ALLOC ,
0 ,
0 ,
lab_detail_rec.TEXT_CODE ,
lab_detail_rec.LAST_UPDATED_BY ,
lab_detail_rec.CREATED_BY ,
lab_detail_rec.LAST_UPDATE_DATE ,
lab_detail_rec.CREATION_DATE ,
lab_detail_rec.LAST_UPDATE_LOGIN ,
lab_detail_rec.ATTRIBUTE1 ,
lab_detail_rec.ATTRIBUTE2 ,
lab_detail_rec.ATTRIBUTE3 ,
lab_detail_rec.ATTRIBUTE4 ,
lab_detail_rec.ATTRIBUTE5 ,
lab_detail_rec.ATTRIBUTE6 ,
lab_detail_rec.ATTRIBUTE7 ,
lab_detail_rec.ATTRIBUTE8 ,
lab_detail_rec.ATTRIBUTE9 ,
lab_detail_rec.ATTRIBUTE10 ,
lab_detail_rec.ATTRIBUTE11 ,
lab_detail_rec.ATTRIBUTE12 ,
lab_detail_rec.ATTRIBUTE13 ,
lab_detail_rec.ATTRIBUTE14 ,
lab_detail_rec.ATTRIBUTE15 ,
lab_detail_rec.ATTRIBUTE16 ,
lab_detail_rec.ATTRIBUTE17 ,
lab_detail_rec.ATTRIBUTE18 ,
lab_detail_rec.ATTRIBUTE19 ,
lab_detail_rec.ATTRIBUTE20 ,
lab_detail_rec.ATTRIBUTE21 ,
lab_detail_rec.ATTRIBUTE22 ,
lab_detail_rec.ATTRIBUTE23 ,
lab_detail_rec.ATTRIBUTE24 ,
lab_detail_rec.ATTRIBUTE25 ,
lab_detail_rec.ATTRIBUTE26 ,
lab_detail_rec.ATTRIBUTE27 ,
lab_detail_rec.ATTRIBUTE28 ,
lab_detail_rec.ATTRIBUTE29 ,
lab_detail_rec.ATTRIBUTE30 ,
lab_detail_rec.ATTRIBUTE_CATEGORY ,
X_tp_formula_id );
INSERT INTO GMD_MIGRATION ( migration_id,
source_table,
target_table,
source_id ,
target_id ,
message_text )
SELECT GMD_REQUEST_ID_S.nextval,
'LM_FORM_MST AND LM_FORM_DTL',
'FM_FORM_MST AND MF_MATL_DTL',
lab_rec.formula_id,
Xformula_id,
error_msg
FROM DUAL;
end insert_lab_formula;