The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_jtf_perz_data(
px_PERZ_DATA_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
p_PROFILE_ID NUMBER,
p_APPLICATION_ID NUMBER,
p_PERZ_DATA_NAME VARCHAR2,
p_PERZ_DATA_TYPE VARCHAR2,
p_PERZ_DATA_DESC VARCHAR2
)
IS
CURSOR C2 IS SELECT JTF_PERZ_DATA_S.nextval FROM sys.dual;
INSERT INTO JTF_PERZ_DATA(
PERZ_DATA_ID,
PROFILE_ID,
APPLICATION_ID,
PERZ_DATA_NAME,
PERZ_DATA_TYPE,
PERZ_DATA_DESC,
OBJECT_VERSION_NUMBER,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
px_PERZ_DATA_ID,
decode( p_PROFILE_ID, FND_API.G_MISS_NUM, NULL, p_PROFILE_ID),
decode( p_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_APPLICATION_ID),
decode( p_PERZ_DATA_NAME, FND_API.G_MISS_CHAR, NULL, p_PERZ_DATA_NAME),
decode( p_PERZ_DATA_TYPE, FND_API.G_MISS_CHAR, NULL, p_PERZ_DATA_TYPE),
decode( p_PERZ_DATA_DESC, FND_API.G_MISS_CHAR, NULL, p_PERZ_DATA_DESC),
1, G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID);
End insert_jtf_perz_data;
PROCEDURE insert_jtf_perz_data_attrib(
px_PERZ_DATA_ATTRIB_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
p_PERZ_DATA_ID NUMBER,
p_ATTRIBUTE_NAME VARCHAR2,
p_ATTRIBUTE_TYPE VARCHAR2,
p_ATTRIBUTE_VALUE VARCHAR2,
p_ATTRIBUTE_CONTEXT VARCHAR2)
IS
CURSOR C2 IS SELECT JTF_PERZ_DATA_ATTRIBUTES_S.nextval FROM sys.dual;
INSERT INTO JTF_PERZ_DATA_ATTRIB(
PERZ_DATA_ATTRIB_ID,
PERZ_DATA_ID,
ATTRIBUTE_NAME,
ATTRIBUTE_TYPE,
ATTRIBUTE_VALUE,
ATTRIBUTE_CONTEXT,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
px_PERZ_DATA_ATTRIB_ID,
decode( p_PERZ_DATA_ID, FND_API.G_MISS_NUM, NULL, p_PERZ_DATA_ID),
decode( p_ATTRIBUTE_NAME, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_NAME),
decode( p_ATTRIBUTE_TYPE, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_TYPE),
decode( p_ATTRIBUTE_VALUE, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_VALUE),
decode( p_ATTRIBUTE_CONTEXT, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CONTEXT),
G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID );
End insert_jtf_perz_data_attrib;
PROCEDURE update_jtf_perz_data(
p_PERZ_DATA_ID NUMBER,
p_PROFILE_ID NUMBER,
p_APPLICATION_ID NUMBER,
p_PERZ_DATA_NAME VARCHAR2,
p_PERZ_DATA_TYPE VARCHAR2,
p_PERZ_DATA_DESC VARCHAR2,
p_OBJECT_VERSION_NUMBER IN NUMBER)
IS
BEGIN
Update JTF_PERZ_DATA
SET
PROFILE_ID = decode( p_PROFILE_ID, FND_API.G_MISS_NUM, PROFILE_ID, p_PROFILE_ID),
APPLICATION_ID = decode( p_APPLICATION_ID, FND_API.G_MISS_NUM, APPLICATION_ID, p_APPLICATION_ID),
PERZ_DATA_NAME = decode( p_PERZ_DATA_NAME, FND_API.G_MISS_CHAR, PERZ_DATA_NAME, p_PERZ_DATA_NAME),
PERZ_DATA_TYPE = decode( p_PERZ_DATA_TYPE, FND_API.G_MISS_CHAR, PERZ_DATA_TYPE, p_PERZ_DATA_TYPE),
PERZ_DATA_DESC = decode( p_PERZ_DATA_DESC, FND_API.G_MISS_CHAR, PERZ_DATA_DESC, p_PERZ_DATA_DESC),
OBJECT_VERSION_NUMBER = decode (p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,
OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
where PERZ_DATA_ID = p_PERZ_DATA_ID
and OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
END update_jtf_perz_data;
SELECT perz_data_id, object_version_number
INTO x_perz_data_id, x_object_version_number
FROM jtf_perz_data
WHERE
perz_data_name = p_perz_data_name AND
application_id = p_application_id AND
profile_id = p_profile_id AND
perz_data_type = p_perz_data_type;
SELECT perz_data_id, object_version_number
INTO x_perz_data_id, x_object_version_number
FROM jtf_perz_data
WHERE perz_data_name = p_perz_data_name AND
application_id = p_application_id AND
profile_id = p_profile_id;
SELECT perz_data_id, object_version_number
INTO l_temp_id, x_object_version_number
from jtf_perz_data
where perz_data_id = x_perz_data_id;
Update_Perz_Data
( l_api_version_number,
p_init_msg_list,
l_commit,
p_application_id,
l_profile_id ,
l_perz_data_id,
p_perz_data_name ,
p_perz_data_type ,
p_perz_data_desc,
p_data_attrib_tbl,
x_perz_data_id ,
x_return_status ,
x_msg_count,
x_msg_data
);
insert_jtf_perz_data(
l_perz_data_id,
p_PROFILE_ID,
p_APPLICATION_ID,
p_PERZ_DATA_NAME,
p_PERZ_DATA_TYPE,
p_PERZ_DATA_DESC
);
insert_jtf_perz_data_attrib(
l_PERZ_DATA_ATTRIB_ID,
l_perz_data_id,
p_data_attrib_tbl(l_curr_row).ATTRIBUTE_NAME,
p_data_attrib_tbl(l_curr_row).ATTRIBUTE_TYPE,
p_data_attrib_tbl(l_curr_row).ATTRIBUTE_VALUE,
p_data_attrib_tbl(l_curr_row).ATTRIBUTE_CONTEXT);
SELECT perz_data_attrib_id, perz_data_id, attribute_name,
attribute_type, attribute_value, attribute_context
FROM jtf_perz_data_attrib
WHERE perz_data_id = p_perz_data_id;
-- Instead of individual temp variables, this will be referred in the select
-- into statements etc.
-- SMATTEGU Enhancement #1181062 End
/*
l_perz_data_id_temp number;
SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
perz_data_type, perz_data_desc
FROM jtf_perz_data
WHERE Profile_ID = p_profile_id AND
Application_ID = p_application_id;
SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
perz_data_type, perz_data_desc
FROM jtf_perz_data
WHERE perz_data_id = p_perz_data_id;
SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
perz_data_type, perz_data_desc
FROM jtf_perz_data
WHERE perz_data_name = p_perz_data_name AND
Profile_ID = p_profile_id AND
Application_ID = p_application_id;
SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
perz_data_type, perz_data_desc
FROM jtf_perz_data
WHERE perz_data_type = p_perz_data_type AND
Profile_ID = p_profile_id AND
Application_ID = p_application_id;
SELECT perz_data_id, Profile_ID, Application_id, perz_data_name,
perz_data_type, perz_data_desc
FROM jtf_perz_data
WHERE perz_data_name = p_perz_data_name AND
perz_data_type = p_perz_data_type AND
Profile_ID = p_profile_id AND
Application_ID = p_application_id;
PROCEDURE Update_Perz_Data
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR := FND_API.G_FALSE,
p_application_id IN NUMBER,
p_profile_id IN NUMBER,
p_perz_data_id IN NUMBER,
p_perz_data_name IN VARCHAR2,
p_perz_data_type IN VARCHAR2 := NULL,
p_perz_data_desc IN VARCHAR2,
p_data_attrib_tbl IN JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE
:= JTF_PERZ_DATA_PUB.G_MISS_DATA_ATTRIB_TBL,
x_perz_data_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)IS
--******** Update_Perz_Data local variable for standards **********
l_api_name CONSTANT VARCHAR2(30) := 'Update PerzData';
SAVEPOINT UPDATE_PERZ_DATA_PVT;
-- 2. Delete all the attributes for that Perz Data ID
DELETE FROM JTF_PERZ_DATA_ATTRIB WHERE
PERZ_DATA_ID = l_perz_data_id;
-- 3. Update Perz Data header information
update_jtf_perz_data(
l_perz_data_id,
p_PROFILE_ID,
p_APPLICATION_ID,
p_PERZ_DATA_NAME,
p_PERZ_DATA_TYPE,
p_PERZ_DATA_DESC,
l_object_version_number);
-- 4. Insert all new rocords from input.
IF (p_data_attrib_tbl.COUNT > 0) THEN
FOR l_curr_row in 1..p_data_attrib_tbl.COUNT LOOP
l_PERZ_DATA_ATTRIB_ID :=p_data_attrib_tbl(l_curr_row).PERZ_DATA_ATTRIB_ID ;
insert_jtf_perz_data_attrib(
l_PERZ_DATA_ATTRIB_ID,
l_perz_data_id,
p_data_attrib_tbl(l_curr_row).ATTRIBUTE_NAME,
p_data_attrib_tbl(l_curr_row).ATTRIBUTE_TYPE,
p_data_attrib_tbl(l_curr_row).ATTRIBUTE_VALUE,
p_data_attrib_tbl(l_curr_row).ATTRIBUTE_CONTEXT);
ROLLBACK TO UPDATE_PERZ_DATA_PVT;
ROLLBACK TO UPDATE_PERZ_DATA_PVT;
ROLLBACK TO UPDATE_PERZ_DATA_PVT;
END Update_Perz_Data;
PROCEDURE Delete_Perz_Data
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR := FND_API.G_FALSE,
p_application_id IN NUMBER,
p_profile_id IN NUMBER,
p_perz_data_id IN NUMBER,
x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)IS
--******** Update_Perz_Data local variable for standards **********
l_api_name CONSTANT VARCHAR2(30) := 'Create PerzData';
SAVEPOINT DELETE_PERZ_DATA_PVT;
DELETE FROM JTF_PERZ_DATA_ATTRIB WHERE
PERZ_DATA_ID = p_perz_data_id;
DELETE FROM JTF_PERZ_DATA WHERE
PERZ_DATA_ID = p_perz_data_id;
ROLLBACK TO DELETE_PERZ_DATA_PVT;
ROLLBACK TO DELETE_PERZ_DATA_PVT;
ROLLBACK TO DELETE_PERZ_DATA_PVT;
END Delete_Perz_Data;