The following lines contain the word 'select', 'insert', 'update' or 'delete':
select role_relate_id,start_date_active,end_date_active,object_version_number
from jtf_rs_role_relations
where role_id = l_role_id
and role_resource_id = l_resource_id
and role_resource_type = 'RS_INDIVIDUAL'
and nvl(delete_flag, 'N') <> 'Y'
order by start_date_active desc;
select start_date_active,
end_date_active,
resource_name
from jtf_rs_resource_extns_vl
where resource_id = l_resource_id;
select group_name
from jtf_rs_groups_vl
where group_id = l_group_id;
select group_member_id
from jtf_rs_group_members
where group_id = l_group_id
and resource_id = l_resource_id
and nvl(delete_flag, 'N') <> 'Y';
select trunc(lkp.start_date_active),
trunc(lkp.end_date_active)
from fnd_lookups lkp, jtf_rs_roles_b rol
where rol.role_id = l_role_id
and lkp.lookup_type = 'JTF_RS_ROLE_TYPE'
and lkp.lookup_code = rol.role_type_code;
jtf_rs_role_relate_pvt.update_resource_role_relate
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_ROLE_RELATE_ID => l_role_relate_id,
P_START_DATE_ACTIVE => l_new_role_start_date,
P_END_DATE_ACTIVE => l_new_role_end_date,
P_OBJECT_VERSION_NUM => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pvt.update_resource_role_relate
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_ROLE_RELATE_ID => l_role_relate_id,
P_START_DATE_ACTIVE => l_new_role_start_date,
P_END_DATE_ACTIVE => l_new_role_end_date,
P_OBJECT_VERSION_NUM => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pvt.update_resource_role_relate
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_ROLE_RELATE_ID => l_role_relate_id,
P_START_DATE_ACTIVE => l_new_role_start_date,
P_END_DATE_ACTIVE => l_new_role_end_date,
P_OBJECT_VERSION_NUM => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
PROCEDURE update_group_membership
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
P_RESOURCE_ID IN NUMBER,
P_ROLE_ID IN NUMBER,
P_ROLE_RELATE_ID IN NUMBER,
P_START_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
P_END_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
P_OBJECT_VERSION_NUM IN NUMBER,
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_GROUP_MEMBERSHIP';
select role_relate_id,start_date_active,end_date_active,object_version_number
from jtf_rs_role_relations
where role_id = l_role_id
and role_resource_id = l_resource_id
and role_resource_type = 'RS_INDIVIDUAL'
and nvl(delete_flag, 'N') <> 'Y'
order by start_date_active desc;
select start_date_active,
end_date_active
from jtf_rs_resource_extns
where resource_id = l_resource_id;
select trunc(lkp.start_date_active),
trunc(lkp.end_date_active)
from fnd_lookups lkp, jtf_rs_roles_b rol
where rol.role_id = l_role_id
and lkp.lookup_type = 'JTF_RS_ROLE_TYPE'
and lkp.lookup_code = rol.role_type_code;
jtf_rs_role_relate_pub.update_resource_role_relate
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_ROLE_RELATE_ID => l_role_relate_id,
P_START_DATE_ACTIVE => l_new_role_start_date,
P_END_DATE_ACTIVE => l_new_role_end_date,
P_OBJECT_VERSION_NUM => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pub.update_resource_role_relate
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_ROLE_RELATE_ID => l_role_relate_id,
P_START_DATE_ACTIVE => l_new_role_start_date,
P_END_DATE_ACTIVE => l_new_role_end_date,
P_OBJECT_VERSION_NUM => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pub.update_resource_role_relate
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_ROLE_RELATE_ID => l_role_relate_id,
P_START_DATE_ACTIVE => l_new_role_start_date,
P_END_DATE_ACTIVE => l_new_role_end_date,
P_OBJECT_VERSION_NUM => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pub.update_resource_role_relate
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_role_relate_id => p_role_relate_id
,p_start_date_active => p_start_date
,p_end_date_active => p_end_date
,p_object_version_num => l_object_version_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END update_group_membership;
PROCEDURE delete_group_membership
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
P_GROUP_ID IN NUMBER,
P_RESOURCE_ID IN NUMBER,
P_GROUP_MEMBER_ID IN NUMBER,
P_ROLE_RELATE_ID IN NUMBER,
P_OBJECT_VERSION_NUM IN NUMBER,
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_GROUP_MEMBERSHIP';
/* Cursor Variables to check group member has to be deleted or not */
cursor group_member_exists(l_group_member_id number)
is
select role_relate_id
from jtf_rs_role_relations
where role_resource_id = l_group_member_id
and role_resource_type = 'RS_GROUP_MEMBER'
and nvl(delete_flag, 'N') <> 'Y';
select object_version_number
from jtf_rs_group_members
where group_member_id = l_group_member_id;
jtf_rs_role_relate_pub.delete_resource_role_relate
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_ROLE_RELATE_ID => p_role_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_group_members_pub.delete_resource_group_members
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_GROUP_ID => p_group_id,
P_GROUP_NUMBER => null,
P_RESOURCE_ID => p_resource_id,
P_RESOURCE_NUMBER => null,
P_OBJECT_VERSION_NUM => l_object_version_num,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
END delete_group_membership;