The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_item_code IN VARCHAR2,
p_item_no IN VARCHAR2,
p_language IN VARCHAR2,
p_label_code IN VARCHAR2,
p_source_lang IN VARCHAR2,
p_name_description IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_attribute21 IN VARCHAR2,
p_attribute22 IN VARCHAR2,
p_attribute23 IN VARCHAR2,
p_attribute24 IN VARCHAR2,
p_attribute25 IN VARCHAR2,
p_attribute26 IN VARCHAR2,
p_attribute27 IN VARCHAR2,
p_attribute28 IN VARCHAR2,
p_attribute29 IN VARCHAR2,
p_attribute30 IN VARCHAR2,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER,
x_rowid OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_oracle_error OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SAVEPOINT Insert_Row;
INSERT INTO gr_generic_ml_name_tl
(item_code,
item_no,
language,
label_code,
source_lang,
name_description,
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)
VALUES
(p_item_code,
p_item_no,
p_language,
p_label_code,
p_source_lang,
p_name_description,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_attribute21,
p_attribute22,
p_attribute23,
p_attribute24,
p_attribute25,
p_attribute26,
p_attribute27,
p_attribute28,
p_attribute29,
p_attribute30,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_update_login);
/* Now get the row id of the inserted record */
Check_Primary_Key
(p_item_code,
p_item_no,
p_language,
'F',
l_rowid,
l_key_exists);
ROLLBACK TO SAVEPOINT Insert_Row;
ROLLBACK TO SAVEPOINT Insert_Row;
ROLLBACK TO SAVEPOINT Insert_Row;
'GR_NO_RECORD_INSERTED');
ROLLBACK TO SAVEPOINT Insert_Row;
END Insert_Row;
PROCEDURE Update_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_item_code IN VARCHAR2,
p_item_no IN VARCHAR2,
p_language IN VARCHAR2,
p_label_code IN VARCHAR2,
p_source_lang IN VARCHAR2,
p_name_description IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_attribute21 IN VARCHAR2,
p_attribute22 IN VARCHAR2,
p_attribute23 IN VARCHAR2,
p_attribute24 IN VARCHAR2,
p_attribute25 IN VARCHAR2,
p_attribute26 IN VARCHAR2,
p_attribute27 IN VARCHAR2,
p_attribute28 IN VARCHAR2,
p_attribute29 IN VARCHAR2,
p_attribute30 IN VARCHAR2,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_oracle_error OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SAVEPOINT Update_Row;
UPDATE gr_generic_ml_name_tl
SET item_code = p_item_code,
item_no = p_item_no,
language = p_language,
label_code = p_label_code,
source_lang = p_source_lang,
name_description = p_name_description,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20,
attribute21 = p_attribute11,
attribute22 = p_attribute22,
attribute23 = p_attribute23,
attribute24 = p_attribute24,
attribute25 = p_attribute25,
attribute26 = p_attribute26,
attribute27 = p_attribute27,
attribute28 = p_attribute28,
attribute29 = p_attribute29,
attribute30 = p_attribute30,
created_by = p_created_by,
creation_date = p_creation_date,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date,
last_update_login = p_last_update_login
WHERE rowid = p_rowid;
ROLLBACK TO SAVEPOINT Update_Row;
ROLLBACK TO SAVEPOINT Update_Row;
'GR_NO_RECORD_INSERTED');
ROLLBACK TO SAVEPOINT Update_Row;
END Update_Row;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT gin.name_description,
gin.label_code,
gin.attribute_category,
gin.attribute1,
gin.attribute2,
gin.attribute3,
gin.attribute4,
gin.attribute5,
gin.attribute6,
gin.attribute7,
gin.attribute8,
gin.attribute9,
gin.attribute10,
gin.attribute11,
gin.attribute12,
gin.attribute13,
gin.attribute14,
gin.attribute15,
gin.attribute16,
gin.attribute17,
gin.attribute18,
gin.attribute19,
gin.attribute20,
gin.attribute21,
gin.attribute22,
gin.attribute23,
gin.attribute24,
gin.attribute25,
gin.attribute26,
gin.attribute27,
gin.attribute28,
gin.attribute29,
gin.attribute30,
gin.created_by,
gin.creation_date,
gin.last_updated_by,
gin.last_update_date,
gin.last_update_login
FROM gr_generic_ml_name_tl gin
WHERE gin.item_code = p_item_code
AND gin.item_no = p_item_no
AND gin.language = l_language;
SELECT lng.language_code
FROM fnd_languages lng
WHERE lng.installed_flag IN ('I', 'B');
delete from GR_GENERIC_ML_NAME_TL T
where not exists
(select NULL
from GR_GENERIC_ITEMS_B B
where B.ITEM_CODE = T.ITEM_CODE
and B.ITEM_NO = T.ITEM_NO
);
update gr_generic_ml_name_tl t set (
name_description ) =
( select
B.NAME_DESCRIPTION
from GR_GENERIC_ML_NAME_TL B
where B.ITEM_CODE = T.ITEM_CODE
and B.ITEM_NO = T.ITEM_NO
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ITEM_CODE,
T.ITEM_NO,
T.LANGUAGE
) in (select
SUBT.ITEM_CODE,
SUBT.ITEM_NO,
SUBT.LANGUAGE
from GR_GENERIC_ML_NAME_TL SUBB, GR_GENERIC_ML_NAME_TL SUBT
where SUBB.ITEM_CODE = SUBT.ITEM_CODE
and SUBB.ITEM_NO = SUBB.ITEM_NO
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.NAME_DESCRIPTION <> SUBT.NAME_DESCRIPTION
or (SUBB.NAME_DESCRIPTION is null and SUBT.NAME_DESCRIPTION is not null)
or (SUBB.NAME_DESCRIPTION is not null and SUBT.NAME_DESCRIPTION is null)
));
l_last_updated_by := GenericDesc.last_updated_by;
l_last_update_date := GenericDesc.last_update_date;
l_last_update_login := GenericDesc.last_update_login;
** insert it and go on to the next.
*/
OPEN c_get_installed_languages;
INSERT INTO gr_generic_ml_name_tl
(item_code,
item_no,
language,
label_code,
source_lang,
name_description,
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)
VALUES
(p_item_code,
p_item_no,
l_language,
l_label_code,
p_language,
l_base_desc,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_attribute16,
l_attribute17,
l_attribute18,
l_attribute19,
l_attribute20,
l_attribute21,
l_attribute22,
l_attribute23,
l_attribute24,
l_attribute25,
l_attribute26,
l_attribute27,
l_attribute28,
l_attribute29,
l_attribute30,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login);
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_oracle_error OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SELECT last_update_date
FROM gr_generic_ml_name_tl
WHERE rowid = p_rowid
FOR UPDATE NOWAIT;
IF LockGenericRcd.last_update_date <> p_last_update_date THEN
RAISE RECORD_CHANGED_ERROR;
PROCEDURE Delete_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_item_code IN VARCHAR2,
p_item_no IN VARCHAR2,
p_language IN VARCHAR2,
p_label_code IN VARCHAR2,
p_source_lang IN VARCHAR2,
p_name_description IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute16 IN VARCHAR2,
p_attribute17 IN VARCHAR2,
p_attribute18 IN VARCHAR2,
p_attribute19 IN VARCHAR2,
p_attribute20 IN VARCHAR2,
p_attribute21 IN VARCHAR2,
p_attribute22 IN VARCHAR2,
p_attribute23 IN VARCHAR2,
p_attribute24 IN VARCHAR2,
p_attribute25 IN VARCHAR2,
p_attribute26 IN VARCHAR2,
p_attribute27 IN VARCHAR2,
p_attribute28 IN VARCHAR2,
p_attribute29 IN VARCHAR2,
p_attribute30 IN VARCHAR2,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_oracle_error OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
SAVEPOINT Delete_Row;
DELETE FROM gr_generic_ml_name_tl
WHERE rowid = p_rowid;
ROLLBACK TO SAVEPOINT Delete_Row;
ROLLBACK TO SAVEPOINT Delete_Row;
ROLLBACK TO SAVEPOINT Delete_Row;
END Delete_Row;
PROCEDURE Delete_Rows
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_delete_option IN VARCHAR2,
p_item_code IN VARCHAR2,
p_item_no IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_oracle_error OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/* Alpha Variables */
L_RETURN_STATUS VARCHAR2(1) := 'S';
NULL_DELETE_OPTION_ERROR EXCEPTION;
SAVEPOINT Delete_Rows;
** p_delete_option has one of three values.
** 'G' - Delete all rows for the generic item in p_item_code.
** 'I' - Delete all rows for the specified item in p_item_no.
** 'B' - Delete all rows for the generic and specified item.
*/
IF p_delete_option = 'G' THEN
IF p_item_code IS NULL THEN
l_msg_token := 'Item Code';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_generic_ml_name_tl
WHERE item_code = p_item_code;
ELSIF p_delete_option = 'I' THEN
IF p_item_no IS NULL THEN
l_msg_token := 'Item No.';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_generic_ml_name_tl
WHERE item_no = p_item_no;
ELSIF p_delete_option = 'B' THEN
IF p_item_code IS NULL OR
p_item_no IS NULL THEN
l_msg_token := 'Item Code / Item No.';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_generic_ml_name_tl
WHERE item_code = p_item_code
AND item_no = p_item_no;
WHEN Null_Delete_Option_Error THEN
x_return_status := 'E';
ROLLBACK TO SAVEPOINT Delete_Rows;
END Delete_Rows;
SELECT ic.item_id
FROM ic_item_mst ic
WHERE ic.item_no = p_item_no;
SELECT lng.language_code
FROM fnd_languages lng
WHERE lng.language_code = l_language_code;
ROLLBACK TO SAVEPOINT Delete_Row;
SELECT lng.installed_flag
FROM fnd_languages lng
WHERE lng.language_code = p_language
AND lng.installed_flag IN ('B', 'I');
SELECT gin.rowid
FROM gr_generic_ml_name_tl gin
WHERE gin.item_code = p_item_code
AND gin.item_no = p_item_no
AND gin.language = p_language;
delete from GR_GENERIC_ML_NAME_TL T
where not exists
(select NULL
from GR_GENERIC_ITEMS_B B
where B.ITEM_CODE = T.ITEM_CODE
and B.ITEM_NO = T.ITEM_NO
);
update GR_GENERIC_ML_NAME_TL T set (
NAME_DESCRIPTION
) = (select
B.NAME_DESCRIPTION
from GR_GENERIC_ML_NAME_TL B
where B.ITEM_CODE = T.ITEM_CODE
and B.ITEM_NO = T.ITEM_NO
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ITEM_CODE,
T.ITEM_NO,
T.LANGUAGE
) in (select
SUBT.ITEM_CODE,
SUBT.ITEM_NO,
SUBT.LANGUAGE
from GR_GENERIC_ML_NAME_TL SUBB, GR_GENERIC_ML_NAME_TL SUBT
where SUBB.ITEM_CODE = SUBT.ITEM_CODE
and SUBB.ITEM_NO = SUBT.ITEM_NO
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.NAME_DESCRIPTION <> SUBT.NAME_DESCRIPTION
));
insert into GR_GENERIC_ML_NAME_TL (
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,
ITEM_CODE,
ITEM_NO,
LABEL_CODE,
NAME_DESCRIPTION,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.ATTRIBUTE10,
B.ATTRIBUTE11,
B.ATTRIBUTE12,
B.ATTRIBUTE13,
B.ATTRIBUTE14,
B.ATTRIBUTE15,
B.ATTRIBUTE16,
B.ATTRIBUTE17,
B.ATTRIBUTE18,
B.ATTRIBUTE19,
B.ATTRIBUTE20,
B.ATTRIBUTE21,
B.ATTRIBUTE22,
B.ATTRIBUTE23,
B.ATTRIBUTE24,
B.ATTRIBUTE25,
B.ATTRIBUTE26,
B.ATTRIBUTE27,
B.ATTRIBUTE28,
B.ATTRIBUTE29,
B.ATTRIBUTE30,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.ITEM_CODE,
B.ITEM_NO,
B.LABEL_CODE,
B.NAME_DESCRIPTION,
B.ATTRIBUTE_CATEGORY,
B.ATTRIBUTE1,
B.ATTRIBUTE2,
B.ATTRIBUTE3,
B.ATTRIBUTE4,
B.ATTRIBUTE5,
B.ATTRIBUTE6,
B.ATTRIBUTE7,
B.ATTRIBUTE8,
B.ATTRIBUTE9,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from GR_GENERIC_ML_NAME_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from GR_GENERIC_ML_NAME_TL T
where T.ITEM_CODE = B.ITEM_CODE
and T.ITEM_NO = B.ITEM_NO
and T.LANGUAGE = L.LANGUAGE_CODE);