[Home] [Help]
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_language IN VARCHAR2,
p_orgn_code IN VARCHAR2,
p_synonym_sequence_number IN NUMBER,
p_source_lang IN VARCHAR2,
p_item_other_name 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_other_names_tl
(item_code,
language,
orgn_code,
synonym_sequence_number,
source_lang,
item_other_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(p_item_code,
p_language,
p_orgn_code,
p_synonym_sequence_number,
p_source_lang,
p_item_other_name,
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_orgn_code,
p_language,
p_synonym_sequence_number,
'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_language IN VARCHAR2,
p_orgn_code IN VARCHAR2,
p_synonym_sequence_number IN NUMBER,
p_source_lang IN VARCHAR2,
p_item_other_name 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_other_names_tl
SET item_code = p_item_code,
language = p_language,
orgn_code = p_orgn_code,
synonym_sequence_number = p_synonym_sequence_number,
source_lang = p_source_lang,
item_other_name = p_item_other_name,
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 ion.item_other_name,
ion.orgn_code,
ion.synonym_sequence_number,
ion.created_by,
ion.creation_date,
ion.last_updated_by,
ion.last_update_date,
ion.last_update_login,
ion.language
FROM gr_other_names_tl ion
WHERE ion.item_code = p_item_code
AND ion.orgn_code = p_orgn_code
AND (ion.language = l_language OR
synonym_sequence_number NOT IN (SELECT synonym_sequence_number
FROM gr_other_names_tl
WHERE item_code = p_item_code
AND orgn_code = p_orgn_code
AND language = l_language));
SELECT 1
FROM gr_other_names_tl ion
WHERE ion.item_code = p_item_code
AND ion.orgn_code = p_orgn_code
AND ion.language = l_language
AND ion.synonym_sequence_number = l_synonym_sequence;
SELECT lng.language_code
FROM fnd_languages lng
WHERE lng.installed_flag IN ('I', 'B');
delete from GR_OTHER_NAMES_TL T
where not exists
(select NULL
from GR_ITEM_GENERAL B,
SY_ORGN_MST S
where B.ITEM_CODE = T.ITEM_CODE
and S.ORGN_CODE = T.ORGN_CODE
);
update gr_other_names_tl t set (
item_other_name ) =
( select
B.ITEM_OTHER_NAME
from GR_OTHER_NAMES_TL B
where B.ITEM_CODE = T.ITEM_CODE
and B.ORGN_CODE = T.ORGN_CODE
and B.LANGUAGE = T.SOURCE_LANG
and B.SYNONYM_SEQUENCE_NUMBER = T.SYNONYM_SEQUENCE_NUMBER)
where (
T.ITEM_CODE,
T.ORGN_CODE,
T.LANGUAGE
) in (select
SUBT.ITEM_CODE,
SUBT.ORGN_CODE,
SUBT.LANGUAGE
from GR_OTHER_NAMES_TL SUBB, GR_OTHER_NAMES_TL SUBT
where SUBB.ITEM_CODE = SUBT.ITEM_CODE
and SUBB.ORGN_CODE = SUBT.ORGN_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.ITEM_OTHER_NAME <> SUBT.ITEM_OTHER_NAME
or (SUBB.ITEM_OTHER_NAME is null and SUBT.ITEM_OTHER_NAME is not null)
or (SUBB.ITEM_OTHER_NAME is not null and SUBT.ITEM_OTHER_NAME is null)
));
l_last_updated_by := OtherNameDesc.last_updated_by;
l_last_update_date := OtherNameDesc.last_update_date;
l_last_update_login := OtherNameDesc.last_update_login;
** insert it and go on to the next.
*/
OPEN c_get_installed_languages;
INSERT INTO gr_other_names_tl
(item_code,
language,
orgn_code,
synonym_sequence_number,
source_lang,
item_other_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(p_item_code,
l_language,
l_orgn_code,
l_synonym_sequence,
p_language,
l_base_desc,
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 *
FROM gr_other_names_tl
WHERE rowid = p_rowid
FOR UPDATE NOWAIT;
PROCEDURE Delete_Row
(p_commit IN VARCHAR2,
p_called_by_form IN VARCHAR2,
p_rowid IN VARCHAR2,
p_item_code IN VARCHAR2,
p_language IN VARCHAR2,
p_orgn_code IN VARCHAR2,
p_synonym_sequence_number IN NUMBER,
p_source_lang IN VARCHAR2,
p_item_other_name 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_other_names_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_orgn_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';
NULL_DELETE_OPTION_ERROR EXCEPTION;
SAVEPOINT Delete_Rows;
** p_delete_option has one of three values.
** 'I' - Delete all rows for the item in p_item_code.
** 'O' - Delete all rows for the label in p_orgn_code.
** 'B' - Delete all rows for the item and label codes.
*/
IF p_delete_option = 'I' THEN
IF p_item_code IS NULL THEN
l_msg_token := 'Item Code';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_other_names_tl
WHERE item_code = p_item_code;
ELSIF p_delete_option = 'O' THEN
IF p_orgn_code IS NULL THEN
l_msg_token := 'Organisation Code';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_other_names_tl
WHERE orgn_code = p_orgn_code;
ELSIF p_delete_option = 'B' THEN
IF p_item_code IS NULL OR
p_orgn_code IS NULL THEN
l_msg_token := 'Item Code / Organisation Code';
RAISE Null_Delete_Option_Error;
DELETE FROM gr_other_names_tl
WHERE item_code = p_item_code
AND orgn_code = p_orgn_code;
WHEN Null_Delete_Option_Error THEN
x_return_status := 'E';
ROLLBACK TO SAVEPOINT Delete_Rows;
END Delete_Rows;
SELECT lng.language_code
FROM fnd_languages lng
WHERE lng.language_code = l_language_code;
SELECT om.orgn_code
FROM sy_orgn_mst om
WHERE orgn_code = p_orgn_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 ion.rowid
FROM gr_other_names_tl ion
WHERE ion.item_code = p_item_code
AND ion.orgn_code = p_orgn_code
AND ion.language = p_language
AND ion.synonym_sequence_number = p_synonym_sequence_number;
delete from GR_OTHER_NAMES_TL T
where not exists
(select NULL
from GR_ITEM_GENERAL B,
SY_ORGN_MST S
where B.ITEM_CODE = T.ITEM_CODE
and S.ORGN_CODE = T.ORGN_CODE
);
update gr_other_names_tl t set (
item_other_name ) =
( select
B.ITEM_OTHER_NAME
from GR_OTHER_NAMES_TL B
where B.ITEM_CODE = T.ITEM_CODE
and B.ORGN_CODE = T.ORGN_CODE
and B.LANGUAGE = T.SOURCE_LANG
and B.SYNONYM_SEQUENCE_NUMBER = T.SYNONYM_SEQUENCE_NUMBER)
where (
T.ITEM_CODE,
T.ORGN_CODE,
T.LANGUAGE
) in (select
SUBT.ITEM_CODE,
SUBT.ORGN_CODE,
SUBT.LANGUAGE
from GR_OTHER_NAMES_TL SUBB, GR_OTHER_NAMES_TL SUBT
where SUBB.ITEM_CODE = SUBT.ITEM_CODE
and SUBB.ORGN_CODE = SUBT.ORGN_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.ITEM_OTHER_NAME <> SUBT.ITEM_OTHER_NAME
or (SUBB.ITEM_OTHER_NAME is null and SUBT.ITEM_OTHER_NAME is not null)
or (SUBB.ITEM_OTHER_NAME is not null and SUBT.ITEM_OTHER_NAME is null)
));
insert into GR_OTHER_NAMES_TL (
ITEM_CODE,
ORGN_CODE,
SYNONYM_SEQUENCE_NUMBER,
ITEM_OTHER_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.ITEM_CODE,
B.ORGN_CODE,
B.SYNONYM_SEQUENCE_NUMBER,
B.ITEM_OTHER_NAME,
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_OTHER_NAMES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from GR_OTHER_NAMES_TL T
where T.ITEM_CODE = B.ITEM_CODE
and T.ORGN_CODE = B.ORGN_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);