[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
create, update and delete resource Roles.
Its main procedures are as following:
Create Resource Roles
Update Resource Roles
Delete Resource Roles
This package valoidates the input parameters to these procedures and then
to do business validations and to do actual inserts, updates and deletes into tables.
******************************************************************************************/
--Package variables.
G_PKG_NAME VARCHAR2(30) := 'JTF_RS_ROLES_PVT';
SELECT 'Y'
FROM jtf_rs_roles_vl
WHERE role_id = l_role_id;
SELECT role_code
FROM jtf_rs_roles_vl
WHERE role_code = l_role_code;
SELECT lookup_code from fnd_lookups
WHERE LOOKUP_TYPE = 'JTF_RS_ROLE_TYPE'
AND lookup_code = l_role_type_code
AND enabled_flag = 'Y'
AND trunc(sysdate) <= trunc(nvl(end_date_active,sysdate));
SELECT 'x'
FROM jtf_rs_roles_vl
WHERE upper(ROLE_NAME) = c_role_name
AND ROLE_TYPE_CODE = c_role_type_code;
SELECT jtf_rs_roles_s.nextval
INTO l_role_id
FROM dual;
jtf_rs_roles_pkg.insert_row (
X_ROWID => l_rowid,
X_ROLE_ID => l_role_id,
X_ATTRIBUTE3 => l_attribute3,
X_ATTRIBUTE4 => l_attribute4,
X_ATTRIBUTE5 => l_attribute5,
X_ATTRIBUTE6 => l_attribute6,
X_ATTRIBUTE7 => l_attribute7,
X_ATTRIBUTE8 => l_attribute8,
X_ATTRIBUTE9 => l_attribute9,
X_ATTRIBUTE10 => l_attribute10,
X_ATTRIBUTE11 => l_attribute11,
X_ATTRIBUTE12 => l_attribute12,
X_ATTRIBUTE13 => l_attribute13,
X_ATTRIBUTE14 => l_attribute14,
X_ATTRIBUTE15 => l_attribute15,
X_ATTRIBUTE_CATEGORY => l_attribute_category,
X_ROLE_CODE => l_role_code,
X_ROLE_TYPE_CODE => l_role_type_code,
X_SEEDED_FLAG => l_seeded_flag,
X_MEMBER_FLAG => l_member_flag,
X_ADMIN_FLAG => l_admin_flag,
X_LEAD_FLAG => l_lead_flag,
X_MANAGER_FLAG => l_manager_flag,
X_ACTIVE_FLAG => l_active_flag,
X_ATTRIBUTE1 => l_attribute1,
X_ATTRIBUTE2 => l_attribute2,
X_ROLE_NAME => l_role_name,
X_ROLE_DESC => l_role_desc,
X_CREATION_DATE => sysdate,
X_CREATED_BY => jtf_resource_utl.created_by,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
);
PROCEDURE update_rs_resource_roles (
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
P_ROLE_TYPE_CODE IN JTF_RS_ROLES_B.ROLE_TYPE_CODE%TYPE,
P_ROLE_NAME IN JTF_RS_ROLES_TL.ROLE_NAME%TYPE,
P_ROLE_DESC IN JTF_RS_ROLES_TL.ROLE_DESC%TYPE,
P_ACTIVE_FLAG IN JTF_RS_ROLES_B.ACTIVE_FLAG%TYPE,
P_SEEDED_FLAG IN JTF_RS_ROLES_B.SEEDED_FLAG%TYPE,
P_MEMBER_FLAG IN JTF_RS_ROLES_B.MEMBER_FLAG%TYPE,
P_ADMIN_FLAG IN JTF_RS_ROLES_B.ADMIN_FLAG%TYPE,
P_LEAD_FLAG IN JTF_RS_ROLES_B.LEAD_FLAG%TYPE,
P_MANAGER_FLAG IN JTF_RS_ROLES_B.MANAGER_FLAG%TYPE,
P_ATTRIBUTE1 IN JTF_RS_ROLES_B.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_ROLES_B.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_ROLES_B.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_ROLES_B.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_ROLES_B.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_ROLES_B.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_ROLES_B.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_ROLES_B.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_ROLES_B.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_ROLES_B.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_ROLES_B.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_ROLES_B.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_ROLES_B.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_ROLES_B.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_ROLES_B.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLES_B.ATTRIBUTE_CATEGORY%TYPE,
P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_ROLES_B.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RS_RESOURCE_ROLES';
SELECT role_code
FROM jtf_rs_roles_vl
WHERE role_code = l_role_code;
SELECT lookup_code from fnd_lookups
WHERE LOOKUP_TYPE = 'JTF_RS_ROLE_TYPE'
AND lookup_code = l_role_type_code
AND enabled_flag = 'Y'
AND trunc(sysdate) <= trunc(nvl(end_date_active,sysdate));
SELECT 'x'
FROM jtf_rs_roles_vl
WHERE upper(role_name) = c_role_name
AND role_type_code = c_role_type_code
AND role_id <> p_role_id;
CURSOR c_rs_role_update( l_role_id IN NUMBER ) IS
SELECT
Role_Code role_code,
DECODE(p_role_code, fnd_api.g_miss_char, role_code, p_role_code) l_role_code,
DECODE(p_role_type_code, fnd_api.g_miss_char, role_type_code, p_role_type_code) l_role_type_code,
DECODE(p_role_name, fnd_api.g_miss_char, role_name, p_role_name) l_role_name,
DECODE(p_seeded_flag, fnd_api.g_miss_char, seeded_flag, p_seeded_flag) l_seeded_flag,
DECODE(p_member_flag, fnd_api.g_miss_char, member_flag, p_member_flag) l_member_flag,
DECODE(p_admin_flag, fnd_api.g_miss_char, admin_flag, p_admin_flag) l_admin_flag,
DECODE(p_lead_flag, fnd_api.g_miss_char, lead_flag, p_lead_flag) l_lead_flag,
DECODE(p_manager_flag, fnd_api.g_miss_char, manager_flag, p_manager_flag) l_manager_flag,
DECODE(p_active_flag, fnd_api.g_miss_char, active_flag, p_active_flag) l_active_flag,
DECODE(p_role_desc, fnd_api.g_miss_char, role_desc, p_role_desc) l_role_desc,
DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
DECODE(p_attribute_category,fnd_api.g_miss_char, attribute1, p_attribute_category) l_attribute_category
FROM jtf_rs_roles_vl
WHERE role_id = l_role_id;
rs_role_rec c_rs_role_update%ROWTYPE;
SAVEPOINT update_rs_resource_roles_pvt;
'UPDATE_RS_RESOURCE_ROLES',
'B',
'C')
THEN
jtf_rs_roles_cuhk.update_rs_resource_roles_pre(
P_ROLE_ID => l_role_id,
P_ROLE_TYPE_CODE => l_role_type_code,
P_ROLE_CODE => l_role_code,
P_ROLE_NAME => l_role_name,
P_ROLE_DESC => l_role_desc,
P_ACTIVE_FLAG => l_active_flag,
P_SEEDED_FLAG => l_seeded_flag,
P_MEMBER_FLAG => l_member_flag,
P_ADMIN_FLAG => l_admin_flag,
P_LEAD_FLAG => l_lead_flag,
P_MANAGER_FLAG => l_manager_flag,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'UPDATE_RS_RESOURCE_ROLES',
'B',
'V')
THEN
jtf_rs_roles_vuhk.update_rs_resource_roles_pre(
P_ROLE_ID => l_role_id,
P_ROLE_TYPE_CODE => l_role_type_code,
P_ROLE_CODE => l_role_code,
P_ROLE_NAME => l_role_name,
P_ROLE_DESC => l_role_desc,
P_ACTIVE_FLAG => l_active_flag,
P_SEEDED_FLAG => l_seeded_flag,
P_MEMBER_FLAG => l_member_flag,
P_ADMIN_FLAG => l_admin_flag,
P_LEAD_FLAG => l_lead_flag,
P_MANAGER_FLAG => l_manager_flag,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'UPDATE_RS_RESOURCE_ROLES',
'B',
'I')
THEN
jtf_rs_roles_iuhk.update_rs_resource_roles_pre(
P_ROLE_ID => l_role_id,
P_ROLE_TYPE_CODE => l_role_type_code,
P_ROLE_CODE => l_role_code,
P_ROLE_NAME => l_role_name,
P_ROLE_DESC => l_role_desc,
P_ACTIVE_FLAG => l_active_flag,
P_SEEDED_FLAG => l_seeded_flag,
P_MEMBER_FLAG => l_member_flag,
P_ADMIN_FLAG => l_admin_flag,
P_LEAD_FLAG => l_lead_flag,
P_MANAGER_FLAG => l_manager_flag,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
OPEN c_rs_role_update(l_role_id);
FETCH c_rs_role_update INTO rs_role_rec;
IF c_rs_role_update%NOTFOUND THEN
CLOSE c_rs_role_update;
/* Calling publish API to raise update resource role event. */
/* added by baianand on 04/02/2003 */
begin
l_resource_role_rec.role_id := l_role_id;
jtf_rs_wf_events_pub.update_resource_role
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_resource_role_rec => l_resource_role_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
jtf_rs_roles_pkg.update_row (
X_ROLE_ID => l_role_id,
X_ATTRIBUTE3 => rs_role_rec.l_attribute3,
X_ATTRIBUTE4 => rs_role_rec.l_attribute4,
X_ATTRIBUTE5 => rs_role_rec.l_attribute5,
X_ATTRIBUTE6 => rs_role_rec.l_attribute6,
X_ATTRIBUTE7 => rs_role_rec.l_attribute7,
X_ATTRIBUTE8 => rs_role_rec.l_attribute8,
X_ATTRIBUTE9 => rs_role_rec.l_attribute9,
X_ATTRIBUTE10 => rs_role_rec.l_attribute10,
X_ATTRIBUTE11 => rs_role_rec.l_attribute11,
X_ATTRIBUTE12 => rs_role_rec.l_attribute12,
X_ATTRIBUTE13 => rs_role_rec.l_attribute13,
X_ATTRIBUTE14 => rs_role_rec.l_attribute14,
X_ATTRIBUTE15 => rs_role_rec.l_attribute15,
X_ATTRIBUTE_CATEGORY => rs_role_rec.l_attribute_category,
X_ROLE_CODE => rs_role_rec.l_role_code,
X_ROLE_TYPE_CODE => rs_role_rec.l_role_type_code,
X_SEEDED_FLAG => rs_role_rec.l_seeded_flag,
X_MEMBER_FLAG => rs_role_rec.l_member_flag,
X_ADMIN_FLAG => rs_role_rec.l_admin_flag,
X_LEAD_FLAG => rs_role_rec.l_lead_flag,
X_MANAGER_FLAG => rs_role_rec.l_manager_flag,
X_ACTIVE_FLAG => rs_role_rec.l_active_flag,
X_ATTRIBUTE1 => rs_role_rec.l_attribute1,
X_ATTRIBUTE2 => rs_role_rec.l_attribute2,
X_ROLE_NAME => rs_role_rec.l_role_name,
X_ROLE_DESC => rs_role_rec.l_role_desc,
X_OBJECT_VERSION_NUMBER=> l_object_version_number,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => jtf_resource_utl.updated_by,
X_LAST_UPDATE_LOGIN => jtf_resource_utl.login_id
);
CLOSE c_rs_role_update;
'UPDATE_RS_RESOURCE_ROLES',
'A',
'C')
THEN
jtf_rs_roles_cuhk.update_rs_resource_roles_post(
P_ROLE_ID => l_role_id,
P_ROLE_TYPE_CODE => l_role_type_code,
P_ROLE_CODE => l_role_code,
P_ROLE_NAME => l_role_name,
P_ROLE_DESC => l_role_desc,
P_ACTIVE_FLAG => l_active_flag,
P_SEEDED_FLAG => l_seeded_flag,
P_MEMBER_FLAG => l_member_flag,
P_ADMIN_FLAG => l_admin_flag,
P_LEAD_FLAG => l_lead_flag,
P_MANAGER_FLAG => l_manager_flag,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'UPDATE_RS_RESOURCE_ROLES',
'A',
'V')
THEN
jtf_rs_roles_vuhk.update_rs_resource_roles_post(
P_ROLE_ID => l_role_id,
P_ROLE_TYPE_CODE => l_role_type_code,
P_ROLE_CODE => l_role_code,
P_ROLE_NAME => l_role_name,
P_ROLE_DESC => l_role_desc,
P_ACTIVE_FLAG => l_active_flag,
P_SEEDED_FLAG => l_seeded_flag,
P_MEMBER_FLAG => l_member_flag,
P_ADMIN_FLAG => l_admin_flag,
P_LEAD_FLAG => l_lead_flag,
P_MANAGER_FLAG => l_manager_flag,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'UPDATE_RS_RESOURCE_ROLES',
'A',
'I')
THEN
jtf_rs_roles_iuhk.update_rs_resource_roles_post(
P_ROLE_ID => l_role_id,
P_ROLE_TYPE_CODE => l_role_type_code,
P_ROLE_CODE => l_role_code,
P_ROLE_NAME => l_role_name,
P_ROLE_DESC => l_role_desc,
P_ACTIVE_FLAG => l_active_flag,
P_SEEDED_FLAG => l_seeded_flag,
P_MEMBER_FLAG => l_member_flag,
P_ADMIN_FLAG => l_admin_flag,
P_LEAD_FLAG => l_lead_flag,
P_MANAGER_FLAG => l_manager_flag,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'UPDATE_RS_RESOURCE_ROLES',
'M',
'M')
THEN
IF (jtf_rs_roles_cuhk.ok_to_generate_msg(
p_role_id => l_role_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
ROLLBACK TO update_rs_resource_roles_pvt;
ROLLBACK TO update_rs_resource_roles_pvt;
END update_rs_resource_roles;
/* Procedure to delete the resource roles. */
PROCEDURE delete_rs_resource_roles
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_ROLE_ID IN JTF_RS_ROLES_B.ROLE_ID%TYPE,
P_ROLE_CODE IN JTF_RS_ROLES_B.ROLE_CODE%TYPE,
P_OBJECT_VERSION_NUMBER IN JTF_RS_ROLES_B.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_ROLES';
SELECT 'Y' from jtf_rs_role_relations
WHERE role_id = p_role_id
AND NVL(DELETE_FLAG,'N') <> 'Y';
SELECT role_name from jtf_rs_roles_vl
WHERE role_id = p_role_id;
SAVEPOINT delete_rs_resource_roles_pvt;
'DELETE_RS_RESOURCE_ROLES',
'B',
'C')
THEN
jtf_rs_roles_cuhk.delete_rs_resource_roles_pre(
P_ROLE_ID => l_role_id,
P_ROLE_CODE => l_role_code,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_RS_RESOURCE_ROLES',
'B',
'V')
THEN
jtf_rs_roles_vuhk.delete_rs_resource_roles_pre(
P_ROLE_ID => l_role_id,
P_ROLE_CODE => l_role_code,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_RS_RESOURCE_ROLES',
'B',
'I')
THEN
jtf_rs_roles_iuhk.delete_rs_resource_roles_pre(
P_ROLE_ID => l_role_id,
P_ROLE_CODE => l_role_code,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
jtf_rs_roles_pkg.delete_row (
X_ROLE_ID => l_role_id
);
'DELETE_RS_RESOURCE_ROLES',
'A',
'C')
THEN
jtf_rs_roles_cuhk.delete_rs_resource_roles_post(
P_ROLE_ID => l_role_id,
P_ROLE_CODE => l_role_code,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_RS_RESOURCE_ROLES',
'A',
'V')
THEN
jtf_rs_roles_vuhk.delete_rs_resource_roles_post(
P_ROLE_ID => l_role_id,
P_ROLE_CODE => l_role_code,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_RS_RESOURCE_ROLES',
'A',
'I')
THEN
jtf_rs_roles_iuhk.delete_rs_resource_roles_post(
P_ROLE_ID => l_role_id,
P_ROLE_CODE => l_role_code,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'DELETE_RS_RESOURCE_ROLES',
'M',
'M')
THEN
IF (jtf_rs_roles_cuhk.ok_to_generate_msg(
p_role_id => l_role_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
/* Calling publish API to raise delete resource role event. */
/* added by baianand on 04/02/2003 */
begin
jtf_rs_wf_events_pub.delete_resource_role
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_role_id => l_role_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
ROLLBACK TO delete_rs_resource_roles_pvt;
ROLLBACK TO delete_rs_resource_roles_pvt;
END delete_rs_resource_roles;