[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select OBJECT_VERSION_NUMBER
from PJI_MT_MEASURES_B
where MEASURE_ID = p_measure_id
for update of measure_id nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure DELETE_ROW (
p_measure_id IN pji_mt_measures_b.measure_id%TYPE
) is
begin
delete from PJI_MT_MEASURES_TL
where measure_id = p_measure_id;
delete from PJI_MT_MEASURES_B
where measure_id = p_measure_id;
end DELETE_ROW;
procedure INSERT_ROW(
X_rowid IN OUT NOCOPY rowid,
X_measure_id IN pji_mt_measures_b.measure_id%type,
X_measure_set_code IN pji_mt_measures_b.measure_set_code%type,
X_measure_code IN pji_mt_measures_b.measure_code%type,
X_xtd_type IN pji_mt_measures_b.xtd_type%type,
X_pl_sql_api IN pji_mt_measures_b.pl_sql_api%type,
X_object_version_number IN pji_mt_measures_b.object_version_number%type,
X_name IN pji_mt_measures_tl.name%type,
X_description IN pji_mt_measures_tl.description%type,
X_last_update_date IN pji_mt_measures_b.last_update_date%Type,
X_last_updated_by IN pji_mt_measures_b.last_updated_by%Type,
X_creation_date IN pji_mt_measures_b.creation_date%Type,
X_created_by IN pji_mt_measures_b.created_by%Type,
X_last_update_Login IN pji_mt_measures_b.last_update_Login%Type,
X_return_status OUT NOCOPY VARCHAR2,
X_msg_data OUT NOCOPY VARCHAR2,
X_msg_count OUT NOCOPY NUMBER
) is
l_measure_id pji_mt_measures_b.MEASURE_ID%type;
cursor C is select ROWID from pji_mt_measures_b
where MEASURE_ID = l_measure_id;
select nvl(X_MEASURE_ID,PJI_MT_MEASURES_S.nextval)
into l_measure_id
from dual;
pa_debug.g_err_stage:= 'Inserting record in pji_mt_measures_b'||to_char(l_measure_id);
INSERT INTO Pji_Mt_Measures_B
(
measure_id,
measure_set_code,
measure_code,
xtd_type,
pl_sql_api,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES
(
l_measure_id,
X_measure_set_code,
X_measure_code,
X_xtd_type,
X_pl_sql_api,
X_object_version_number,
X_last_update_date,
X_last_updated_by,
X_creation_date,
X_created_by,
X_last_update_login
);
pa_debug.g_err_stage:= 'Inserting record in pji_mt_Measures_tl'||to_char(l_measure_id);
INSERT INTO pji_mt_measures_tl
(
measure_id,
name,
description,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
language,
source_lang
)
SELECT
l_measure_id,
X_name,
X_description,
X_last_update_date,
X_last_updated_by,
X_creation_date,
X_created_by,
X_last_update_login,
L.Language_Code,
Userenv('Lang')
FROM Fnd_Languages L
WHERE L.Installed_Flag In ('I', 'B')
AND NOT EXISTS
(SELECT NULL FROM Pji_Mt_Measures_Tl T
WHERE T.Measure_Id = L_Measure_Id
AND T.Language = L.Language_Code);
pa_debug.g_err_stage:= 'Rowid could not be fetched after Inserting for'||to_char(l_measure_id);
,p_procedure_name => 'Insert Row'
,p_error_text => x_msg_data);
END INSERT_ROW;
procedure UPDATE_ROW (
X_measure_id IN pji_mt_measures_b.measure_id%type,
X_measure_set_code IN pji_mt_measures_b.measure_set_code%type,
X_measure_code IN pji_mt_measures_b.measure_code%type,
X_xtd_type IN pji_mt_measures_b.xtd_type%type,
X_pl_sql_api IN pji_mt_measures_b.pl_sql_api%type,
X_object_version_number IN pji_mt_measures_b.object_version_number%type,
X_name IN pji_mt_measures_tl.name%type,
X_description IN pji_mt_measures_tl.description%type,
X_last_update_date IN pji_mt_measures_b.last_update_date%Type,
X_last_updated_by IN pji_mt_measures_b.last_updated_by%Type,
X_last_update_login IN pji_mt_measures_b.last_update_login%Type,
X_return_status OUT NOCOPY VARCHAR2,
X_msg_data OUT NOCOPY VARCHAR2,
X_msg_count OUT NOCOPY NUMBER
) IS
l_return_status VARCHAR2(1) := NULL;
UPDATE Pji_Mt_Measures_B
SET
measure_set_code = X_measure_set_code,
measure_code = X_measure_code,
xtd_type = X_xtd_type,
pl_sql_api = X_pl_sql_api,
object_version_number = X_object_version_number
where Measure_Id = X_Measure_Id;
UPDATE Pji_Mt_Measures_Tl
SET
Name = X_Name,
Description = X_Description,
Last_Update_Date = X_Last_Update_Date,
Last_Updated_By = X_Last_Updated_By,
Last_Update_Login = X_Last_Update_Login,
Source_Lang = Userenv('Lang')
WHERE Measure_Id = X_Measure_Id
AND Userenv('Lang') In (Language, Source_Lang);
,p_procedure_name => 'UPDATE_ROW'
,p_error_text => x_msg_data);
END UPDATE_ROW;
Pji_Mt_Measures_Pkg.Update_Row (
X_Measure_Id => X_Measure_Id,
X_measure_set_code => X_measure_set_code,
X_measure_code => X_measure_code,
X_xtd_type => X_xtd_type,
X_pl_sql_api => X_pl_sql_api,
X_object_version_number => X_object_version_number,
X_Name => X_Name,
X_Description => X_Description,
X_Last_Update_Date => Sysdate,
X_Last_Updated_By => User_Id,
X_Last_Update_Login => 0,
X_Return_Status => l_Return_Status,
X_Msg_Data => l_Msg_Data,
X_Msg_Count => l_Msg_Count
);
Pji_Mt_Measures_Pkg.Insert_Row (
X_Rowid => X_Rowid,
X_Measure_Id => X_Measure_Id,
X_measure_set_code => X_measure_set_code,
X_measure_code => X_measure_code,
X_xtd_type => X_xtd_type,
X_pl_sql_api => X_pl_sql_api,
X_object_version_number => X_object_version_number,
X_Name => X_Name,
X_Description => X_Description,
X_Creation_Date => Sysdate,
X_Created_By => User_Id,
X_Last_Update_Date => Sysdate,
X_Last_Updated_By => User_Id,
X_Last_Update_Login => 0,
X_Return_Status => l_Return_Status,
X_Msg_Data => l_Msg_Data,
X_Msg_Count => l_Msg_Count
);
,p_procedure_name => 'UPDATE_ROW'
,p_error_text => l_msg_data);
delete from PJI_MT_MEASURES_TL T
where not exists
(select NULL
from PJI_MT_MEASURES_B B
where B.MEASURE_ID = T.MEASURE_ID
);
update PJI_MT_MEASURES_TL T set (
NAME,
DESCRIPTION
) = (select
B.NAME,
B.DESCRIPTION
from PJI_MT_MEASURES_TL B
where B.MEASURE_ID = T.MEASURE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.MEASURE_ID,
T.LANGUAGE
) in (select
SUBT.MEASURE_ID,
SUBT.LANGUAGE
from PJI_MT_MEASURES_TL SUBB, PJI_MT_MEASURES_TL SUBT
where SUBB.MEASURE_ID = SUBT.MEASURE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.NAME <> SUBT.NAME
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into PJI_MT_MEASURES_TL (
MEASURE_ID,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)select
B.MEASURE_ID,
B.NAME,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.LAST_UPDATE_LOGIN,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY
from PJI_MT_MEASURES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PJI_MT_MEASURES_TL T
where T.MEASURE_ID = B.MEASURE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
update PJI_MT_MEASURES_TL set
NAME = X_NAME,
DESCRIPTION = X_DESCRIPTION,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = decode(X_OWNER, 'SEED', 1, 0),
LAST_UPDATE_LOGIN = 0,
SOURCE_LANG = USERENV('LANG')
where MEASURE_ID = X_MEASURE_ID
and USERENV('LANG') IN (LANGUAGE, SOURCE_LANG) ;