[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT object_version_number
FROM pji_mt_rowset_b
WHERE rowset_code = p_rowset_code
FOR UPDATE OF rowset_code NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE DELETE_ROW (
p_rowset_code IN pji_mt_rowset_b.rowset_code%TYPE
) IS
BEGIN
DELETE FROM pji_mt_rowset_TL
WHERE rowset_code = p_rowset_code;
DELETE FROM pji_mt_rowset_B
WHERE rowset_code = p_rowset_code;
END DELETE_ROW;
PROCEDURE Insert_Row (
X_Rowid IN OUT NOCOPY ROWID,
X_rowset_Code IN pji_mt_rowset_b.Rowset_Code%TYPE,
X_Object_Version_Number IN pji_mt_rowset_b.Object_Version_Number%TYPE,
X_Name IN pji_mt_rowset_Tl.Name%TYPE,
X_Description IN pji_mt_rowset_Tl.Description%TYPE,
X_Last_Update_Date IN pji_mt_rowset_b.Last_Update_Date%TYPE,
X_Last_Updated_by IN pji_mt_rowset_b.Last_Updated_by%TYPE,
X_Creation_Date IN pji_mt_rowset_b.Creation_Date%TYPE,
X_Created_By IN pji_mt_rowset_b.Created_By%TYPE,
X_Last_Update_Login IN pji_mt_rowset_b.Last_Update_Login%TYPE,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER
) IS
CURSOR C IS SELECT ROWID FROM pji_mt_rowset_b
WHERE rowset_code = x_rowset_code;
pa_debug.g_err_stage:= 'Inserting record in pji_mt_rowset_B '||X_Rowset_Code;
INSERT INTO pji_mt_rowset_B
(
Rowset_Code
, Object_Version_Number
, Creation_Date
, Last_Update_Date
, Last_Updated_By
, Created_By
, Last_Update_Login )
VALUES
(
X_Rowset_Code
, X_Object_Version_Number
, X_Creation_Date
, X_Last_Update_Date
, X_Last_Updated_By
, X_Created_By
, X_Last_Update_Login
);
pa_debug.g_err_stage:= 'Inserting record in pji_mt_rowset_tl '||X_Rowset_Code;
INSERT INTO pji_mt_rowset_tl
(
Rowset_Code,
Name,
Description,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
LANGUAGE,
Source_Lang
)
SELECT
X_Rowset_Code,
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_rowset_tl T
WHERE T.rowset_code = X_Rowset_Code
AND T.LANGUAGE = L.Language_Code);
pa_debug.g_err_stage:= 'Rowid could not be fetched after Inserting for '||X_Rowset_Code;
,p_procedure_name => 'Insert Row'
,p_error_text => x_msg_data);
END Insert_Row;
PROCEDURE Update_Row (
X_Rowset_Code IN pji_mt_rowset_b.Rowset_Code%TYPE,
X_Object_Version_Number IN pji_mt_rowset_b.Object_Version_Number%TYPE,
X_Name IN pji_mt_rowset_Tl.Name%TYPE,
X_Description IN pji_mt_rowset_Tl.Description%TYPE,
X_Last_Update_Date IN pji_mt_rowset_b.Last_Update_Date%TYPE,
X_Last_Updated_by IN pji_mt_rowset_b.Last_Updated_by%TYPE,
X_Last_Update_Login IN pji_mt_rowset_b.Last_Update_Login%TYPE,
X_Lock_Flag IN VARCHAR2 DEFAULT 'true',
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_rowset_B
SET
Rowset_Code = X_Rowset_Code
, Object_Version_Number = l_object_version_number
, Last_Update_Date = X_Last_Update_Date
, Last_Updated_By = X_Last_Updated_By
, Last_Update_Login = X_Last_Update_Login
WHERE rowset_code = X_rowset_code;
UPDATE pji_mt_rowset_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 Rowset_Code = X_Rowset_Code
AND USERENV('Lang') IN (LANGUAGE, Source_Lang);
,p_procedure_name => 'UPDATE_ROW'
,p_error_text => x_msg_data);
END Update_Row;
PJI_MT_ROWSET_Pkg.Update_Row (
X_Rowset_Code => X_Rowset_Code ,
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_Lock_Flag => 'false' ,
X_Return_Status => l_Return_Status ,
X_Msg_Data => l_Msg_Data ,
X_Msg_Count => l_Msg_Count );
PJI_MT_ROWSET_Pkg.Insert_Row (
X_Rowid => X_Rowid ,
X_Rowset_Code => X_Rowset_Code ,
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_rowset_tl T
WHERE NOT EXISTS
(SELECT NULL
FROM pji_mt_rowset_B B
WHERE B.rowset_code = T.rowset_code
);
UPDATE pji_mt_rowset_tl T SET (
NAME,
DESCRIPTION
) = (SELECT
B.NAME,
B.DESCRIPTION
FROM pji_mt_rowset_tl B
WHERE B.rowset_code = T.rowset_code
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.rowset_code,
T.LANGUAGE
) IN (SELECT
SUBT.rowset_code,
SUBT.LANGUAGE
FROM pji_mt_rowset_tl SUBB, pji_mt_rowset_tl SUBT
WHERE SUBB.rowset_code = SUBT.rowset_code
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_rowset_tl(
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
rowset_code,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
)SELECT
B.LAST_UPDATE_LOGIN,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.rowset_code,
B.NAME,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM pji_mt_rowset_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_rowset_tl T
WHERE T.rowset_code = B.rowset_code
AND T.LANGUAGE = L.LANGUAGE_CODE);
UPDATE pji_mt_rowset_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 rowset_code = x_rowset_code
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG) ;