[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
create, update and delete resource roles from other modules.
Its main procedures are as following:
Create Resource Role Relate
Update Resource Role Relate
Delete Resource Role Relate
Calls to these procedures will invoke procedures from jtf_rs_role_relate_pvt
to do business validations and to do actual inserts, updates and deletes into tables.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_ROLE_RELATE_PVT';
SELECT rsc.start_date_active,
rsc.end_date_active
FROM jtf_rs_resource_extns rsc
WHERE rsc.resource_id = ll_resource_id;
select rlt.role_relate_id,
rlt.start_date_active,
rlt.end_date_active
from jtf_rs_role_relations rlt,
jtf_rs_group_members mem
where mem.resource_id = p_resource_id
and nvl(mem.delete_flag, 'N') <> 'Y'
and rlt.role_resource_id = mem.group_member_id
and rlt.role_id = p_role_id --added vide bug#2474811
and rlt.role_resource_type = 'RS_GROUP_MEMBER'
and nvl(rlt.delete_flag, 'N') <> 'Y'
and rlt.start_date_active between p_old_start_date and
to_date(to_char(nvl(p_old_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR');
select rlt.role_relate_id,
rlt.start_date_active,
rlt.end_date_active
from jtf_rs_role_relations rlt,
jtf_rs_team_members mem
where mem.team_resource_id = p_resource_id
and mem.resource_type = 'INDIVIDUAL'
and nvl(mem.delete_flag, 'N') <> 'Y'
and rlt.role_resource_id = mem.team_member_id
and rlt.role_id = p_role_id --added vide bug#2474811
and rlt.role_resource_type = 'RS_TEAM_MEMBER'
and nvl(rlt.delete_flag, 'N') <> 'Y'
and rlt.start_date_active between p_old_start_date and
to_date(to_char(nvl(p_old_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR') ;
SELECT role_type_code
FROM jtf_rs_roles_b
WHERE role_id = l_role_id;
select 'X'
from fnd_lookups
where lookup_type = 'JTF_RS_ROLE_TYPE'
and lookup_code = l_role_type
and ENABLED_FLAG = 'Y'
and START_DATE_ACTIVE <= p_start_date
and (END_DATE_ACTIVE is NULL or
(p_end_date is not null and
END_DATE_ACTIVE >= p_end_date));
SELECT resource_type,
team_resource_id
FROM jtf_rs_team_members
WHERE team_member_id = l_team_member_id;
SELECT resource_id
FROM jtf_rs_group_members
WHERE group_member_id = l_grp_member_id;
SELECT 'X'
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_INDIVIDUAL'
AND role_resource_id = ll_role_resource_id
AND role_id = ll_role_id
AND nvl(delete_flag, '0') <> 'Y'
AND to_date(to_char(start_date_active , 'dd-MM-yyyy'),'dd-MM-yyyy') <=
to_date(to_char(ll_start_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
AND ( to_date(to_char(end_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
>= to_date(to_char(ll_end_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
OR ( end_date_active IS NULL AND ll_end_date_active IS NULL)
OR (end_date_active IS NULL AND ll_end_date_active IS NOT NULL))
AND nvl(delete_flag, '0') <> 'Y';
SELECT 'X'
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_GROUP'
AND role_resource_id = ll_role_resource_id
AND role_id = ll_role_id
AND start_date_active <= ll_start_date_active
AND nvl(delete_flag, '0') <> 'Y'
AND ( end_date_active >= ll_end_date_active
OR ( end_date_active IS NULL AND ll_end_date_active IS NULL)
OR (end_date_active IS NULL AND ll_end_date_active IS NOT NULL))
AND nvl(delete_flag, '0') <> 'Y';
SELECT start_date_active,
end_date_active
FROM jtf_rs_role_relations
WHERE role_resource_type = ll_role_resource_type
AND role_resource_id = ll_role_resource_id
AND role_id = ll_role_id
AND nvl(delete_flag, 'N') <> 'Y';
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp,
jtf_rs_group_members mem
WHERE mem.group_member_id = ll_member_id
AND mem.group_id = grp.group_id;
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp
WHERE grp.group_id = l_group_id;
SELECT tm.start_date_active,
tm.end_date_active
FROM jtf_rs_teams_b tm
WHERE tm.team_id = l_team_id;
SELECT tm.start_date_active,
tm.end_date_active
FROM jtf_rs_teams_b tm,
jtf_rs_team_members mem
WHERE mem.team_member_id = ll_member_id
AND mem.team_id = tm.team_id;
SELECT rsc.start_date_active,
rsc.end_date_active
FROM jtf_rs_resource_extns rsc
WHERE rsc.resource_id = ll_resource_id;
SELECT 'X'
FROM jtf_rs_groups_b G1,
jtf_rs_groups_b G2,
jtf_rs_group_members GM1,
jtf_rs_group_members GM2,
jtf_rs_group_usages GU1,
jtf_rs_group_usages GU2,
jtf_rs_role_relations RR1
/* commented the below line to improve the performance. We are not using this table in the select statement. */
-- jtf_rs_role_relations RR2
WHERE GM2.group_member_id = l_member_id
AND G1.group_id = GM1.group_id
AND G2.group_id = GM2.group_id
AND nvl(GM1.delete_flag, 'N') <> 'Y'
AND nvl(GM2.delete_flag, 'N') <> 'Y'
AND GM1.resource_id = GM2.resource_id
AND GM1.group_member_id = RR1.role_resource_id
AND RR1.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(RR1.delete_flag, 'N') <> 'Y'
AND not (((nvl(l_end_date_active,RR1.start_date_active + 1) < RR1.start_date_active OR
l_start_date_active > RR1.end_date_active) AND
RR1.end_date_active IS NOT NULL)
OR ( nvl(l_end_date_active,RR1.start_date_active + 1) < RR1.start_date_active AND
RR1.end_date_active IS NULL ))
AND G2.exclusive_flag = 'Y'
AND G1.exclusive_flag = 'Y'
AND GU1.group_id = G1.group_id
AND GU2.group_id = G2.group_id
AND GU1.usage = GU2.usage
AND G1.group_id <> G2.group_id;
select mem.group_id
from jtf_rs_group_members mem,
jtf_rs_role_relations rel
where rel.role_relate_id = l_role_relate_id
and rel.role_resource_id = mem.group_member_id;
select count(*) child_cnt
from jtf_rs_grp_relations rel
connect by related_group_id = prior group_id
and nvl(delete_flag, 'N') <> 'Y'
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with related_group_id = l_group_id
and nvl(delete_flag, 'N') <> 'Y';
select description
from fnd_concurrent_programs_vl
where concurrent_program_name = 'JTFRSRMG'
and application_id = 690;
select jtf_rs_role_relations_s.nextval
into l_role_relate_id
from dual;
jtf_rs_role_relate_aud_pvt.insert_role_relate(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_ROLE_RELATE_ID => l_role_relate_id,
P_ROLE_RESOURCE_TYPE => l_role_resource_type,
P_ROLE_RESOURCE_ID => l_role_resource_id,
P_ROLE_ID => l_role_id,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUMBER => 1,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data );
jtf_rs_role_relations_pkg.insert_row(X_ROWID => l_rowid,
X_ROLE_RELATE_ID => l_role_relate_id,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_ROLE_RESOURCE_TYPE => l_role_resource_type,
X_ROLE_RESOURCE_ID => l_role_resource_id,
X_ROLE_ID => l_role_id,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_DELETE_FLAG => 'N',
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE1 => p_attribute1,
X_CREATION_DATE => l_date,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id ) ;
insert into jtf_rs_chgd_role_relations
(role_relate_id,
role_resource_type,
role_resource_id,
role_id,
start_date_active,
end_date_active,
delete_flag,
operation_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values(
l_role_relate_id,
l_role_resource_type,
l_role_resource_id,
l_role_id,
l_start_date_active,
l_end_date_active,
'N',
'I',
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id);
JTF_RS_REP_MGR_DENORM_PVT.INSERT_REP_MANAGER
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_ROLE_RELATE_ID => l_role_relate_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
/* Procedure to update the resource roles
based on input values passed by calling routines. */
PROCEDURE update_resource_role_relate
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
P_ATTRIBUTE1 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_ROLE_RELATE';
SELECT role_resource_type,
role_resource_id,
role_id,
start_date_active,
end_date_active,
object_version_number,
delete_flag,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category
FROM jtf_rs_role_relations
WHERE role_relate_id = ll_role_relate_id
AND nvl(delete_flag, '0') <> 'Y';
l_delete_flag JTF_RS_ROLE_RELATIONS.DELETE_FLAG%TYPE ;
SELECT resource_type,
team_resource_id
FROM jtf_rs_team_members
WHERE team_member_id = l_team_member_id
AND nvl(delete_flag, '0') <> 'Y';
SELECT resource_id
FROM jtf_rs_group_members
WHERE group_member_id = l_grp_member_id
AND nvl(delete_flag, '0') <> 'Y';
SELECT 'X'
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_INDIVIDUAL'
AND role_resource_id = ll_role_resource_id
AND role_id = ll_role_id
AND to_date(to_char(start_date_active , 'dd-MM-yyyy'),'dd-MM-yyyy') <=
to_date(to_char(ll_start_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
AND ( to_date(to_char(end_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
>= to_date(to_char(ll_end_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
OR ( end_date_active IS NULL AND ll_end_date_active IS NULL)
OR (end_date_active IS NULL AND ll_end_date_active IS NOT NULL))
AND nvl(delete_flag, '0') <> 'Y';
SELECT 'X'
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_GROUP'
AND role_resource_id = ll_role_resource_id
AND role_id = ll_role_id
AND start_date_active <= ll_start_date_active
AND ( end_date_active >= ll_end_date_active
OR ( end_date_active IS NULL AND ll_end_date_active IS NULL)
OR (end_date_active IS NULL AND ll_end_date_active IS NOT NULL))
AND nvl(delete_flag, '0') <> 'Y';
SELECT start_date_active,
end_date_active
FROM jtf_rs_role_relations
WHERE role_relate_id <> ll_role_relate_id
AND role_resource_type = ll_role_resource_type
AND role_resource_id = ll_role_resource_id
AND role_id = ll_role_id
AND nvl(delete_flag, 'N') <> 'Y';
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp,
jtf_rs_group_members mem
WHERE mem.group_member_id = ll_member_id
AND mem.group_id = grp.group_id
AND nvl(mem.delete_flag, '0') <> 'Y';
SELECT tm.start_date_active,
tm.end_date_active
FROM jtf_rs_teams_b tm,
jtf_rs_team_members mem
WHERE mem.team_member_id = ll_member_id
AND mem.team_id = tm.team_id
AND nvl(mem.delete_flag, '0') <> 'Y';
SELECT rsc.start_date_active,
rsc.end_date_active
FROM jtf_rs_resource_extns rsc
WHERE rsc.resource_id = ll_resource_id;
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp
WHERE grp.group_id = l_group_id;
SELECT tm.start_date_active,
tm.end_date_active
FROM jtf_rs_teams_b tm
WHERE tm.team_id = l_team_id;
SELECT 'X'
FROM jtf_rs_groups_b G1,
jtf_rs_groups_b G2,
jtf_rs_group_members GM1,
jtf_rs_group_members GM2,
jtf_rs_group_usages GU1,
jtf_rs_group_usages GU2,
jtf_rs_role_relations RR1
/* commented the below line to improve the performance. We are not using this table in the select statement. */
-- jtf_rs_role_relations RR2
WHERE GM2.group_member_id = l_member_id
AND G1.group_id = GM1.group_id
AND G2.group_id = GM2.group_id
AND nvl(GM1.delete_flag, 'N') <> 'Y'
AND nvl(GM2.delete_flag, 'N') <> 'Y'
AND GM1.resource_id = GM2.resource_id
AND GM1.group_member_id = RR1.role_resource_id
AND RR1.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(RR1.delete_flag, 'N') <> 'Y'
AND not (((nvl(l_end_date_active,RR1.start_date_active + 1) < RR1.start_date_active OR
l_start_date_active > RR1.end_date_active) AND
RR1.end_date_active IS NOT NULL)
OR ( nvl(l_end_date_active,RR1.start_date_active + 1) < RR1.start_date_active AND
RR1.end_date_active IS NULL ))
AND G2.exclusive_flag = 'Y'
AND G1.exclusive_flag = 'Y'
AND GU1.group_id = G1.group_id
AND GU2.group_id = G2.group_id
AND GU1.usage = GU2.usage
AND G1.group_id <> G2.group_id;
SELECT 'X'
FROM jtf_rs_team_members mem,
jtf_rs_role_relations rlt
WHERE mem.team_resource_id = l_resource_id
AND mem.resource_type = 'INDIVIDUAL'
AND nvl(mem.delete_flag, 'N') <> 'Y'
AND mem.team_member_id = rlt.role_resource_id
AND rlt.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(rlt.delete_flag, 'N') <> 'Y'
AND ((l_start_date_active between rlt.start_date_active + 1
and nvl(rlt.end_date_active - 1, l_start_date_active +1))
OR (l_end_date_active between rlt.start_date_active + 1
and nvl(rlt.end_date_active - 1, l_end_date_active - 1)))
AND rlt.role_id = l_role_id;
SELECT rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt
WHERE mem.resource_id = l_resource_id
AND nvl(mem.delete_flag, 'N') <> 'Y'
AND mem.group_member_id = rlt.role_resource_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(rlt.delete_flag, 'N') <> 'Y'
AND ((l_start_date_active between rlt.start_date_active+1
and nvl(rlt.end_date_active - 1, l_start_date_active +1))
OR (l_end_date_active between rlt.start_date_active+1
and nvl(rlt.end_date_active - 1, l_end_date_active - 1)))
AND rlt.role_id = l_role_id;
select mem.group_id
from jtf_rs_group_members mem,
jtf_rs_role_relations rel
where rel.role_relate_id = l_role_relate_id
and rel.role_resource_id = mem.group_member_id;
select count(*) child_cnt
from jtf_rs_grp_relations rel
connect by related_group_id = prior group_id
and nvl(delete_flag, 'N') <> 'Y'
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with related_group_id = l_group_id
and nvl(delete_flag, 'N') <> 'Y';
select description
from fnd_concurrent_programs_vl
where concurrent_program_name = 'JTFRSRMG'
and application_id = 690;
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
then
JTF_RS_ROLE_RELATE_CUHK.UPDATE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
P_START_DATE_ACTIVE => P_start_date_active,
P_END_DATE_ACTIVE => P_end_date_active,
P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
then
JTF_RS_ROLE_RELATE_VUHK.UPDATE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
P_START_DATE_ACTIVE => P_start_date_active,
P_END_DATE_ACTIVE => P_end_date_active,
P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
then
JTF_RS_ROLE_RELATE_IUHK.UPDATE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
P_START_DATE_ACTIVE => P_start_date_active,
P_END_DATE_ACTIVE => P_end_date_active,
P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
l_delete_flag := role_relate_rec.delete_flag;
jtf_rs_role_relate_aud_pvt.update_role_relate(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_ROLE_RELATE_ID => l_role_relate_id,
P_ROLE_RESOURCE_TYPE => l_role_resource_type,
P_ROLE_RESOURCE_ID => l_role_resource_id,
P_ROLE_ID => l_role_id,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUMBER => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data );
/* Calling publish API to raise update resource role relation event. */
/* added by baianand on 04/09/2003 */
begin
jtf_rs_wf_events_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_role_resource_type => l_role_resource_type
,p_role_resource_id => l_role_resource_id
,p_role_id => l_role_id
,p_start_date_active => l_start_date_active
,p_end_date_active => l_end_date_active
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
jtf_rs_role_relations_pkg.update_row(X_ROLE_RELATE_ID => l_role_relate_id,
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_RESOURCE_TYPE => l_role_resource_type,
X_ROLE_RESOURCE_ID => l_role_resource_id,
X_ROLE_ID => l_role_id,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_DELETE_FLAG => l_delete_flag,
X_OBJECT_VERSION_NUMBER => l_object_version_number ,
X_ATTRIBUTE2 => l_attribute2,
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_ATTRIBUTE1 => l_attribute1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id ) ;
insert into jtf_rs_chgd_role_relations
(role_relate_id,
role_resource_type,
role_resource_id,
role_id,
start_date_active,
end_date_active,
delete_flag,
operation_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values(
l_role_relate_id,
l_role_resource_type,
l_role_resource_id,
l_role_id,
l_start_date_active,
l_end_date_active,
'N',
'U',
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id);
JTF_RS_REP_MGR_DENORM_PVT.UPDATE_REP_MANAGER
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_ROLE_RELATE_ID => l_role_relate_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'C' ))
then
JTF_RS_ROLE_RELATE_CUHK.UPDATE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
P_START_DATE_ACTIVE => P_start_date_active,
P_END_DATE_ACTIVE => P_end_date_active,
P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
then
JTF_RS_ROLE_RELATE_VUHK.UPDATE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
P_START_DATE_ACTIVE => P_start_date_active,
P_END_DATE_ACTIVE => P_end_date_active,
P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
then
JTF_RS_ROLE_RELATE_IUHK.UPDATE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
P_START_DATE_ACTIVE => P_start_date_active,
P_END_DATE_ACTIVE => P_end_date_active,
P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
'UPDATE_RESOURCE_ROLE_RELATE',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_ROLE_RELATE_PVT',
'UPDATE_RESOURCE_ROLE_RELATE',
'M',
'M')
THEN
IF (jtf_rs_role_relate_cuhk.ok_to_generate_msg(
p_role_relate_id => l_role_relate_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;
END update_resource_role_relate;
/* Procedure to delete the resource roles. */
PROCEDURE delete_resource_role_relate
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)IS
CURSOR chk_type_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
IS
SELECT role_resource_type,
role_resource_id,
role_id,
start_date_active,
end_date_active,
object_version_number,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category
FROM jtf_rs_role_relations
WHERE role_relate_id = l_role_relate_id;
SELECT 'x'
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem
WHERE mem.resource_id = l_resource_id
AND rlt.role_resource_id = mem.group_member_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = l_role_id
--AND nvl(end_date_active, TRUNC(sysdate) + 1) > TRUNC(sysdate)
AND (start_date_active between l_start_date_active and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
OR to_date(to_char(nvl(end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') between l_start_date_active and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
AND nvl(rlt.delete_flag, '0') <> 'Y';
SELECT 'x'
FROM jtf_rs_role_relations rlt,
jtf_rs_team_members mem
WHERE mem.team_resource_id = l_resource_id
AND mem.resource_type <> 'GROUP'
AND rlt.role_resource_id = mem.team_member_id
AND rlt.role_resource_type = 'RS_TEAM_MEMBER'
AND rlt.role_id = l_role_id
--AND nvl(rlt.end_date_active, TRUNC(sysdate) + 1) > TRUNC(sysdate)
AND (start_date_active between l_start_date_active and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
OR to_date(to_char(nvl(end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') between l_start_date_active and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
AND nvl(rlt.delete_flag, '0') <> 'Y';
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_ROLE_RELATE';
select mem.group_id
from jtf_rs_group_members mem,
jtf_rs_role_relations rel
where rel.role_relate_id = l_role_relate_id
and rel.role_resource_id = mem.group_member_id;
select count(*) child_cnt
from jtf_rs_grp_relations rel
connect by related_group_id = prior group_id
and nvl(delete_flag, 'N') <> 'Y'
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with related_group_id = l_group_id
and nvl(delete_flag, 'N') <> 'Y';
select description
from fnd_concurrent_programs_vl
where concurrent_program_name = 'JTFRSRMG'
and application_id = 690;
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
then
JTF_RS_ROLE_RELATE_CUHK.DELETE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
then
JTF_RS_ROLE_RELATE_VUHK.DELETE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
then
JTF_RS_ROLE_RELATE_IUHK.DELETE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
jtf_rs_role_relate_aud_pvt.delete_role_relate(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_ROLE_RELATE_ID => l_role_relate_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data );
jtf_rs_role_relations_pkg.update_row(
X_ROLE_RELATE_ID => l_role_relate_id,
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_RESOURCE_TYPE => chk_type_rec.role_resource_type,
X_ROLE_RESOURCE_ID => chk_type_rec.role_resource_id,
X_ROLE_ID => chk_type_rec.role_id,
X_START_DATE_ACTIVE => chk_type_rec.start_date_active,
X_END_DATE_ACTIVE => chk_type_rec.end_date_active,
X_DELETE_FLAG => 'Y',
X_OBJECT_VERSION_NUMBER => chk_type_rec.object_version_number ,
X_ATTRIBUTE2 => l_attribute2,
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_ATTRIBUTE1 => l_attribute1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id );
insert into jtf_rs_chgd_role_relations
(role_relate_id,
role_resource_type,
role_resource_id,
role_id,
start_date_active,
end_date_active,
delete_flag,
operation_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values(
l_role_relate_id,
chk_type_rec.role_resource_type,
chk_type_rec.role_resource_id,
chk_type_rec.role_id,
chk_type_rec.start_date_active,
chk_type_rec.end_date_active,
'Y',
'D',
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id);
JTF_RS_REP_MGR_DENORM_PVT.DELETE_MEMBERS
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_ROLE_RELATE_ID => l_role_relate_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'C' ))
then
JTF_RS_ROLE_RELATE_CUHK.DELETE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
then
JTF_RS_ROLE_RELATE_VUHK.DELETE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
then
JTF_RS_ROLE_RELATE_IUHK.DELETE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
'DELETE_RESOURCE_ROLE_RELATE',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_ROLE_RELATE_PVT',
'DELETE_RESOURCE_ROLE_RELATE',
'M',
'M')
THEN
IF (jtf_rs_role_relate_cuhk.ok_to_generate_msg(
p_role_relate_id => p_role_relate_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 relation event. */
/* added by baianand on 11/09/2002 */
begin
jtf_rs_wf_events_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 => l_role_relate_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
END delete_resource_role_relate;