The following lines contain the word 'select', 'insert', 'update' or 'delete':
Insert_Row
DESCRIPTION:
This PL/SQL procedure is used to insert data into the table
GR_FIELD_NAME_MASKS_TL
PARAMETERS:
p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
p_language IN VARCHAR2,
p_field_name_mask IN VARCHAR2,
p_source_lang 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 VARCHAR2,
x_return_status OUT VARCHAR2,
x_oracle_error OUT NUMBER,
x_msg_data OUT VARCHAR2
HISTORY
===================================================================== */
PROCEDURE Insert_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
p_language IN VARCHAR2,
p_field_name_mask IN VARCHAR2,
p_source_lang 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_item_field_name_masks_tl
(item_code,
disclosure_code,
label_code,
language,
field_name_mask,
source_lang,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(p_item_code,
p_disclosure_code,
p_label_code,
p_language,
p_field_name_mask,
p_source_lang,
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_disclosure_code,
p_label_code,
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;
Update_Row
DESCRIPTION:
This PL/SQL procedure is used to update data in the table
GR_FIELD_NAME_MASKS_TL
PARAMETERS:
p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
pitem_code IN VARCHAR2,
pdisclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
p_language IN VARCHAR2,
p_field_name_mask IN VARCHAR2,
p_source_lang 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 VARCHAR2,
x_oracle_error OUT NUMBER,
x_msg_data OUT VARCHAR2
HISTORY
===================================================================== */
PROCEDURE Update_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
p_language IN VARCHAR2,
p_field_name_mask IN VARCHAR2,
p_source_lang 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_item_field_name_masks_tl
SET item_code = p_item_code,
disclosure_code = p_disclosure_code,
label_code = p_label_code,
language = p_language,
field_name_mask = p_field_name_mask,
source_lang = p_source_lang,
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 eit.field_name_mask,
eit.created_by,
eit.creation_date,
eit.last_updated_by,
eit.last_update_date,
eit.last_update_login
FROM gr_item_field_name_masks_tl eit
WHERE eit.item_code = p_item_code
AND eit.disclosure_code = p_disclosure_code
AND eit.label_code = p_label_code
AND eit.language = l_language;
SELECT lng.language_code
FROM fnd_languages lng
WHERE lng.installed_flag IN ('I', 'B');
delete from GR_ITEM_FIELD_NAME_MASKS_TL T
where not exists
(select NULL
from GR_ITEM_FIELD_NAME_MASKS_B B
where B.ITEM_CODE = T.ITEM_CODE
and B.DISCLOSURE_CODE = T.DISCLOSURE_CODE
and B.LABEL_CODE = T.LABEL_CODE
);
Update gr_item_field_name_masks_tl t set (
field_name_mask ) =
( select
b.field_name_mask
from gr_item_field_name_masks_tl b
where b.item_code = t.item_code
and b.disclosure_code = t.disclosure_code
and b.label_code = t.label_code
and b.language = t.source_lang)
where (
t.item_code,
t.disclosure_code,
t.label_code,
t.language
) in (select
subt.item_code,
subt.disclosure_code,
subt.label_code,
subt.language
from gr_item_field_name_masks_tl subb, gr_item_field_name_masks_tl subt
where subb.item_code = subt.item_code
and subb.disclosure_code = subt.disclosure_code
and subb.label_code = subt.label_code
and subb.language = subt.source_lang
and (subb.field_name_mask <> subt.field_name_mask
or (subb.field_name_mask is null and subt.field_name_mask is not null)
or (subb.field_name_mask is not null and subt.field_name_mask is null)));
l_last_updated_by := TypeDesc.last_updated_by;
l_last_update_date := TypeDesc.last_update_date;
l_last_update_login := TypeDesc.last_update_login;
** insert it and go on to the next.
*/
OPEN c_get_installed_languages;
INSERT INTO gr_item_field_name_masks_tl
(item_code,
disclosure_code,
label_code,
language,
field_name_mask,
source_lang,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(p_item_code,
p_disclosure_code,
p_label_code,
l_language,
l_base_desc,
p_language,
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 VARCHAR2,
x_oracle_error OUT NUMBER,
x_msg_data OUT VARCHAR2
HISTORY
===================================================================== */
PROCEDURE Lock_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
p_language IN VARCHAR2,
p_field_name_mask IN VARCHAR2,
p_source_lang 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';
SELECT last_update_date
FROM gr_item_field_name_masks_tl
WHERE rowid = p_rowid
FOR UPDATE NOWAIT;
IF LockEinRcd.last_update_date <> p_last_update_date THEN
RAISE RECORD_CHANGED_ERROR;
Delete_Row
DESCRIPTION:
This PL/SQL procedure is used to delete a row in the table
GR_FIELD_NAME_MASKS_TL
PARAMETERS:
p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
x_return_status OUT VARCHAR2,
x_oracle_error OUT NUMBER,
x_msg_data OUT VARCHAR2
HISTORY
===================================================================== */
PROCEDURE Delete_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
p_language 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';
SAVEPOINT Delete_Row;
DELETE FROM gr_item_field_name_masks_tl
WHERE rowid = p_rowid;
ROLLBACK TO SAVEPOINT Delete_Row;
ROLLBACK TO SAVEPOINT Delete_Row;
END Delete_Row;
Delete_Rows
DESCRIPTION:
This PL/SQL procedure is used to delete all of the rows in the table
GR_FIELD_NAME_MASKS_TL for the given item_code
PARAMETERS:
p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
x_return_status OUT VARCHAR2,
x_oracle_error OUT NUMBER,
x_msg_data OUT VARCHAR2
HISTORY
===================================================================== */
PROCEDURE Delete_Rows
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code 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';
SAVEPOINT Delete_Rows;
DELETE FROM gr_item_field_name_masks_tl
WHERE item_code = p_item_code;
ROLLBACK TO SAVEPOINT Delete_Rows;
END Delete_Rows;
SELECT lng.language_code
FROM fnd_languages lng
WHERE lng.language_code = l_language_code;
SELECT ein.rowid
FROM gr_item_field_name_masks_tl ein
WHERE ein.item_code = p_item_code
AND ein.disclosure_code = p_disclosure_code
AND ein.label_code = p_label_code
AND ein.language = p_language;
UPDATE GR_ITEM_FIELD_NAME_MASKS_TL SET
FIELD_NAME_MASK = X_FIELD_NAME_MASK,
SOURCE_LANG = USERENV('LANG'),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = 0,
LAST_UPDATE_LOGIN = 0
WHERE (ITEM_CODE = X_ITEM_CODE)
AND (DISCLOSURE_CODE = X_DISCLOSURE_CODE)
AND (LABEL_CODE = X_LABEL_CODE)
AND (USERENV('LANG') IN (LANGUAGE, SOURCE_LANG));
This PL/SQL procedure is used to update or insert a row into
GR_FIELD_NAME_MASKS_TL
PARAMETERS:
p_item_code IN VARCHAR2,
p_disclosure_code IN VARCHAR2,
p_label_code IN VARCHAR2,
p_language IN VARCHAR2,
p_field_name_mask IN VARCHAR2,
p_source_lang IN VARCHAR2,
HISTORY
===================================================================== */
PROCEDURE load_row (
X_ITEM_CODE IN VARCHAR2
,X_DISCLOSURE_CODE IN VARCHAR2
,X_LABEL_CODE IN VARCHAR2
,X_LANGUAGE IN VARCHAR2
,X_FIELD_NAME_MASK IN VARCHAR2
,X_SOURCE_LANG IN VARCHAR2
) IS
CURSOR Cur_rowid IS
SELECT rowid
FROM GR_ITEM_FIELD_NAME_MASKS_TL
WHERE (ITEM_CODE = X_ITEM_CODE)
AND (DISCLOSURE_CODE = X_DISCLOSURE_CODE)
AND (LABEL_CODE = X_LABEL_CODE)
AND (LANGUAGE = X_LANGUAGE);
GR_FIELD_NAME_MASKS_TL_PKG.UPDATE_ROW(
P_COMMIT => 'T'
,P_CALLED_BY_FORM => 'F'
,P_ROWID => l_row_id
,P_ITEM_CODE => X_ITEM_CODE
,P_DISCLOSURE_CODE => X_DISCLOSURE_CODE
,P_LABEL_CODE => X_LABEL_CODE
,P_LANGUAGE => X_LANGUAGE
,P_FIELD_NAME_MASK => X_FIELD_NAME_MASK
,P_SOURCE_LANG => X_SOURCE_LANG
,P_CREATED_BY => l_user_id
,P_CREATION_DATE => sysdate
,P_LAST_UPDATED_BY => l_user_id
,P_LAST_UPDATE_DATE => sysdate
,P_LAST_UPDATE_LOGIN => 0
,X_RETURN_STATUS => l_return_status
,X_ORACLE_ERROR => l_oracle_error
,X_MSG_DATA => l_msg_data);
GR_FIELD_NAME_MASKS_TL_PKG.INSERT_ROW(
P_COMMIT => 'T'
,P_CALLED_BY_FORM => 'F'
,P_ITEM_CODE => X_ITEM_CODE
,P_DISCLOSURE_CODE => X_DISCLOSURE_CODE
,P_LABEL_CODE => X_LABEL_CODE
,P_LANGUAGE => X_LANGUAGE
,P_FIELD_NAME_MASK => X_FIELD_NAME_MASK
,P_SOURCE_LANG => X_SOURCE_LANG
,P_CREATED_BY => l_user_id
,P_CREATION_DATE => sysdate
,P_LAST_UPDATED_BY => l_user_id
,P_LAST_UPDATE_DATE => sysdate
,P_LAST_UPDATE_LOGIN => 0
,X_ROWID => l_row_id
,X_RETURN_STATUS => l_return_status
,X_ORACLE_ERROR => l_oracle_error
,X_MSG_DATA => l_msg_data);
DELETE FROM GR_ITEM_FIELD_NAME_MASKS_TL T
WHERE not exists
(SELECT NULL
FROM GR_ITEM_FIELD_NAME_MASKS_B B
WHERE B.ITEM_CODE = T.ITEM_CODE
AND B.DISCLOSURE_CODE = T.DISCLOSURE_CODE
AND B.LABEL_CODE = T.LABEL_CODE
);
UPDATE GR_ITEM_FIELD_NAME_MASKS_TL T SET (
FIELD_NAME_MASK
) = (SELECT
B.FIELD_NAME_MASK
FROM GR_ITEM_FIELD_NAME_MASKS_TL B
WHERE B.ITEM_CODE = T.ITEM_CODE
AND B.LANGUAGE = T.SOURCE_LANG
AND B.DISCLOSURE_CODE = T.DISCLOSURE_CODE
AND B.LABEL_CODE = T.LABEL_CODE)
WHERE (
T.ITEM_CODE,
T.LANGUAGE,
T.DISCLOSURE_CODE,
T.LABEL_CODE
) in (SELECT
SUBT.ITEM_CODE,
SUBT.LANGUAGE,
SUBT.DISCLOSURE_CODE,
SUBT.LABEL_CODE
FROM GR_ITEM_FIELD_NAME_MASKS_TL SUBB, GR_ITEM_FIELD_NAME_MASKS_TL SUBT
WHERE SUBB.ITEM_CODE = SUBT.ITEM_CODE
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND SUBB.DISCLOSURE_CODE = SUBT.DISCLOSURE_CODE
AND SUBB.LABEL_CODE = SUBT.LABEL_CODE
AND (SUBB.FIELD_NAME_MASK <> SUBT.FIELD_NAME_MASK
));
INSERT into GR_ITEM_FIELD_NAME_MASKS_TL (
ITEM_CODE,
DISCLOSURE_CODE,
LABEL_CODE,
FIELD_NAME_MASK,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) SELECT
B.ITEM_CODE,
B.DISCLOSURE_CODE,
B.LABEL_CODE,
B.FIELD_NAME_MASK,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM GR_ITEM_FIELD_NAME_MASKS_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND B.LANGUAGE = userenv('LANG')
AND not exists
(SELECT NULL
FROM GR_ITEM_FIELD_NAME_MASKS_TL T
WHERE T.ITEM_CODE = B.ITEM_CODE
AND T.DISCLOSURE_CODE = B.DISCLOSURE_CODE
AND T.LABEL_CODE = B.LABEL_CODE
AND T.LANGUAGE = L.LANGUAGE_CODE);