[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*FOR INSERT IN JTF_RS_ROLE_RELATIONS */
PROCEDURE INSERT_REP_MANAGER(
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,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR rep_mgr_seq_cur
IS
SELECT jtf_rs_rep_managers_s.nextval
FROM dual;
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
rol.member_flag ,
rol.admin_flag ,
rol.lead_flag ,
rol.manager_flag,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE rlt.role_relate_id = l_role_relate_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = mem.group_member_id
AND rlt.role_id = rol.role_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND mem.resource_id = rsc.resource_id;
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
rol.member_flag ,
rol.admin_flag ,
rol.lead_flag ,
rol.manager_flag,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_relate_id <> l_role_relate_id
/* AND ((rlt.start_date_active between l_start_date_active and
nvl(l_end_date_active,rlt.start_date_active+1))
OR (rlt.end_date_active between l_start_date_active
and nvl(l_end_date_active,rlt.end_date_active+1))
OR ((rlt.start_date_active <= l_start_date_active)
AND (rlt.end_date_active >= l_end_date_active
OR l_end_date_active IS NULL))) */
AND rlt.role_id = rol.role_id
--added to eliminate managers
AND nvl(rol.manager_flag , 'N') <> 'Y'
AND (
nvl(rol.admin_flag, 'N') = 'Y'
OR
nvl(rol.member_flag, 'N') = 'Y'
)
AND mem.resource_id = rsc.resource_id;
SELECT person_id
FROM jtf_rs_rep_managers
WHERE group_id = l_group_id
AND ( person_id = l_person_id
OR (l_person_id IS NULL AND person_id IS NULL))
AND manager_person_id = l_manager_person_id
AND resource_id = l_resource_id
AND start_date_active = l_start_date_active
AND (end_date_active = l_end_date_active
OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
SELECT person_id
FROM jtf_rs_rep_managers
WHERE par_role_relate_id = l_par_role_relate_id
AND child_role_relate_id = l_child_role_relate_id
AND group_id = l_group_id
AND ((l_start_date_active between start_date_active and
nvl(end_date_active,l_start_date_active+1))
OR (l_end_date_active between start_date_active
and nvl(end_date_active,l_end_date_active+1))
OR ((l_start_date_active <= start_date_active)
AND (l_end_date_active >= end_date_active
OR l_end_date_active IS NULL)));
SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rol.admin_flag ,
rol.manager_flag,
rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_B rol
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_relate_id <> l_role_relate_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
--AND rlt.role_relate_id <> l_role_relate_id
/* AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null))) */
AND rlt.role_id = rol.role_id
AND nvl(rol.manager_flag , 'N') = 'Y';
SELECT parent_group_id,
immediate_parent_flag,
start_date_active,
end_date_active,
denorm_level
FROM jtf_rs_groups_denorm
WHERE group_id = l_group_id
AND parent_group_id <> l_group_id
/* AND ((l_start_date_active between start_date_active
and nvl(end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, start_date_active +1)
between start_date_active and
nvl(end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and end_date_active is null)))*/
;
SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol
WHERE mem.group_id = l_group_id
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 nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_id = rol.role_id
AND rol.admin_flag = 'Y'
AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null)));
SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = rol.role_id
AND rol.manager_flag = 'Y' ;
SELECT group_id,
immediate_parent_flag,
start_date_active,
end_date_active,
denorm_level
FROM jtf_rs_groups_denorm
WHERE parent_group_id = l_group_id
AND group_id <> l_group_id;
SELECT mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rol.manager_flag,
rol.admin_flag,
rol.member_flag,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_b rol,
jtf_rs_resource_extns rsc
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = rol.role_id
AND ( nvl(rol.manager_flag,'N') = 'Y'
OR
nvl(rol.admin_flag, 'N') = 'Y'
OR
nvl(rol.member_flag, 'N') = 'Y')
--AND rlt.start_date_active <= l_start_date_active
/* AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null))) */
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND mem.resource_id = rsc.resource_id;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REP_MANAGER';
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(mem_dtls_rec.start_date_active),
X_END_DATE_ACTIVE => trunc(mem_dtls_rec.end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_role_relate_id,
X_CHILD_ROLE_RELATE_ID => l_role_relate_id,
X_DENORM_LEVEL => 0);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => same_grp_mgr_admin_rec.person_id,
X_PARENT_RESOURCE_ID => same_grp_mgr_admin_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => same_grp_mgr_admin_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID => l_role_relate_id,
X_DENORM_LEVEL => 0);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID =>other_rec.resource_id,
X_PERSON_ID =>other_rec.person_id,
X_CATEGORY => other_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mem_dtls_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>other_rec.role_relate_id,
X_DENORM_LEVEL => 0);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => mgr_rec.person_id,
X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id,
X_DENORM_LEVEL => par_grp_rec.denorm_level);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => mgr_rec.person_id,
X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
X_GROUP_ID => par_grp_rec.parent_group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id,
X_DENORM_LEVEL => par_grp_rec.denorm_level);
END IF; --end of mgr flag check for inserting opp rec
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID =>child_mem_rec.resource_id,
X_PERSON_ID => child_mem_rec.person_id,
X_CATEGORY => child_mem_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => child_grp_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_role_relate_id,
X_CHILD_ROLE_RELATE_ID =>child_mem_rec.role_relate_id,
X_DENORM_LEVEL => child_grp_rec.denorm_level);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID =>child_mem_rec.resource_id,
X_PERSON_ID => child_mem_rec.person_id,
X_CATEGORY => child_mem_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_role_relate_id,
X_CHILD_ROLE_RELATE_ID =>child_mem_rec.role_relate_id,
X_DENORM_LEVEL => child_grp_rec.denorm_level);
END IF; --end of child group members insert if mem mgr flag = Y
END INSERT_REP_MANAGER;
/*FOR INSERT IN JTF_RS_ROLE_RELATIONS */
PROCEDURE INSERT_REP_MANAGER_MIGR(
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,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR rep_mgr_seq_cur
IS
SELECT jtf_rs_rep_managers_s.nextval
FROM dual;
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
rol.member_flag ,
rol.admin_flag ,
rol.lead_flag ,
rol.manager_flag,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE rlt.role_relate_id = l_role_relate_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = mem.group_member_id
AND rlt.role_id = rol.role_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND mem.resource_id = rsc.resource_id;
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
rol.member_flag ,
rol.admin_flag ,
rol.lead_flag ,
rol.manager_flag,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_relate_id <> l_role_relate_id
AND ((rlt.start_date_active between l_start_date_active and
nvl(l_end_date_active,rlt.start_date_active+1))
OR (rlt.end_date_active between l_start_date_active
and nvl(l_end_date_active,rlt.end_date_active+1))
OR ((rlt.start_date_active <= l_start_date_active)
AND (rlt.end_date_active >= l_end_date_active
OR l_end_date_active IS NULL)))
AND rlt.role_id = rol.role_id
AND mem.resource_id = rsc.resource_id;
SELECT person_id
FROM jtf_rs_rep_managers
WHERE group_id = l_group_id
AND ( person_id = l_person_id
OR (l_person_id IS NULL AND person_id IS NULL))
AND manager_person_id = l_manager_person_id
AND resource_id = l_resource_id
AND start_date_active = l_start_date_active
AND (end_date_active = l_end_date_active
OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
SELECT person_id
FROM jtf_rs_rep_managers
WHERE par_role_relate_id = l_par_role_relate_id
AND child_role_relate_id = l_child_role_relate_id
AND group_id = l_group_id;
SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rol.admin_flag ,
rol.manager_flag,
rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_B rol
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_relate_id <> l_role_relate_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
--AND rlt.role_relate_id <> l_role_relate_id
AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null)))
AND rlt.role_id = rol.role_id
AND (rol.manager_flag = 'Y');
SELECT parent_group_id,
immediate_parent_flag
FROM jtf_rs_groups_denorm
WHERE group_id = l_group_id
AND parent_group_id <> l_group_id
AND ((l_start_date_active between start_date_active
and nvl(end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, start_date_active +1)
between start_date_active and
nvl(end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and end_date_active is null)));
SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol
WHERE mem.group_id = l_group_id
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 nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_id = rol.role_id
AND rol.admin_flag = 'Y'
AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null)));
SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = rol.role_id
AND rol.manager_flag = 'Y'
AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null)));
SELECT group_id,
immediate_parent_flag
FROM jtf_rs_groups_denorm
WHERE parent_group_id = l_group_id
AND group_id <> l_group_id
AND ((l_start_date_active between start_date_active
and nvl(end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, start_date_active +1)
between start_date_active and
nvl(end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and end_date_active is null)));
SELECT mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rol.manager_flag,
rol.admin_flag,
rol.member_flag,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_b rol,
jtf_rs_resource_extns rsc
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = rol.role_id
--AND rlt.start_date_active <= l_start_date_active
AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null)))
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND mem.resource_id = rsc.resource_id;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REP_MANAGER';
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(mem_dtls_rec.start_date_active),
X_END_DATE_ACTIVE => trunc(mem_dtls_rec.end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_role_relate_id,
X_CHILD_ROLE_RELATE_ID => l_role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => same_grp_mgr_admin_rec.person_id,
X_PARENT_RESOURCE_ID => same_grp_mgr_admin_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => same_grp_mgr_admin_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID => l_role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => same_grp_mgr_admin_rec.person_id,
X_PARENT_RESOURCE_ID => same_grp_mgr_admin_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => same_grp_mgr_admin_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID => l_role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID =>other_rec.resource_id,
X_PERSON_ID =>other_rec.person_id,
X_CATEGORY => other_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mem_dtls_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>other_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID =>other_rec.resource_id,
X_PERSON_ID =>other_rec.person_id,
X_CATEGORY => other_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mem_dtls_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>other_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => mgr_rec.person_id,
X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => mgr_rec.person_id,
X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
X_GROUP_ID => par_grp_rec.parent_group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => admin_rec.person_id,
X_PARENT_RESOURCE_ID => admin_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => admin_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => admin_rec.person_id,
X_PARENT_RESOURCE_ID => admin_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => admin_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID => mem_dtls_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID =>child_mem_rec.resource_id,
X_PERSON_ID => child_mem_rec.person_id,
X_CATEGORY => child_mem_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => child_grp_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_role_relate_id,
X_CHILD_ROLE_RELATE_ID =>child_mem_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID =>child_mem_rec.resource_id,
X_PERSON_ID => child_mem_rec.person_id,
X_CATEGORY => child_mem_rec.category,
X_MANAGER_PERSON_ID => mem_dtls_rec.person_id,
X_PARENT_RESOURCE_ID => mem_dtls_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_role_relate_id,
X_CHILD_ROLE_RELATE_ID =>child_mem_rec.role_relate_id);
END IF; --end of child group members insert
END INSERT_REP_MANAGER_MIGR;
PROCEDURE UPDATE_REP_MANAGER(
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,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
/* CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
IS
SELECT distinct(den.denorm_mgr_id) denorm_mgr_id
FROM jtf_rs_rep_managers den
WHERE par_role_relate_id = l_role_relate_id
OR child_role_relate_id = l_role_relate_id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REP_MANAGER';
/* --get all the denorm id's for the role relate id and delete the rows from rep manager table
OPEN denorm_cur(l_role_relate_id);
jtf_rs_rep_managers_pkg.delete_row(denorm_rec.denorm_mgr_id);
DELETE FROM jtf_rs_rep_managers
WHERE par_role_relate_id = p_role_relate_id
OR child_role_relate_id = p_role_relate_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 => l_pass_commit,
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 UPDATE_REP_MANAGER;
PROCEDURE DELETE_MEMBERS(
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,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR denorm_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
IS
SELECT distinct(den.denorm_mgr_id) denorm_mgr_id
FROM jtf_rs_rep_managers den
WHERE par_role_relate_id = l_role_relate_id
OR child_role_relate_id = l_role_relate_id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REP_MANAGER';
jtf_rs_rep_managers_pkg.delete_row(denorm_rec.denorm_mgr_id);
delete jtf_rs_rep_managers where par_role_relate_id = p_role_relate_id;
delete jtf_rs_rep_managers where child_role_relate_id = p_role_relate_id;
END DELETE_MEMBERS;
PROCEDURE DELETE_GROUP_DENORM(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_DENORM_GRP_ID IN JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
l_denorm_grp_id JTF_RS_GROUPS_DENORM.DENORM_GRP_ID%TYPE := p_denorm_grp_id;
SELECT parent_group_id ,
group_id
FROM jtf_rs_groups_denorm
WHERE denorm_grp_id = l_denorm_group_id;
SELECT role_relate_id
FROM JTF_RS_ROLE_RELATIONS rlt,
jtf_rs_group_members mem
WHERE mem.group_id = l_group_id
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 nvl(mem.delete_flag,'N') <> 'Y';
SELECT role_relate_id
FROM JTF_RS_ROLE_RELATIONS rlt,
jtf_rs_group_members mem
WHERE mem.group_id = l_group_id
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 nvl(mem.delete_flag,'N') <> 'Y';
SELECT denorm_mgr_id
FROM jtf_rs_rep_managers
WHERE par_role_relate_id = l_par_role_relate_id
AND child_role_relate_id = l_child_role_relate_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP_DENORM';
jtf_rs_groups_denorm_pkg.delete_row(l_denorm_grp_id);
delete jtf_rs_rep_managers
where par_role_relate_id = par_role_relate_rec.role_relate_id
and child_role_relate_id = child_role_relate_rec.role_relate_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 => l_pass_commit,
P_ROLE_RELATE_ID => child_role_relate_rec.role_relate_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
JTF_RS_REP_MGR_DENORM_PVT.INSERT_REP_MANAGER(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => l_pass_commit,
P_ROLE_RELATE_ID => par_role_relate_rec.role_relate_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
END DELETE_GROUP_DENORM;
PROCEDURE INSERT_REP_MGR_PARENT(
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,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR rep_mgr_seq_cur
IS
SELECT jtf_rs_rep_managers_s.nextval
FROM dual;
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
rol.member_flag ,
rol.admin_flag ,
rol.lead_flag ,
rol.manager_flag,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE rlt.role_relate_id = l_role_relate_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = mem.group_member_id
AND rlt.role_id = rol.role_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND mem.resource_id = rsc.resource_id;
SELECT person_id
FROM jtf_rs_rep_managers
WHERE group_id = l_group_id
AND ( person_id = l_person_id
OR (l_person_id IS NULL AND person_id IS NULL))
AND manager_person_id = l_manager_person_id
AND resource_id = l_resource_id
AND start_date_active = l_start_date_active
AND (end_date_active = l_end_date_active
OR ( end_date_active IS NULL AND l_end_date_active IS NULL));
SELECT person_id
FROM jtf_rs_rep_managers
WHERE par_role_relate_id = l_par_role_relate_id
AND child_role_relate_id = l_child_role_relate_id
AND group_id = l_group_id;
SELECT parent_group_id,
immediate_parent_flag
FROM jtf_rs_groups_denorm
WHERE group_id = l_group_id
AND parent_group_id <> l_group_id
AND ((l_start_date_active between start_date_active
and nvl(end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, start_date_active +1)
between start_date_active and
nvl(end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and end_date_active is null)));
SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol
WHERE mem.group_id = l_group_id
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 nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_id = rol.role_id
AND rol.admin_flag = 'Y'
AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null)));
SELECT /*+ ordered use_nl(MEM,RLT,ROL) */
mem.resource_id,
mem.person_id,
rlt.start_date_active,
rlt.end_date_active,
rlt.role_relate_id
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = rol.role_id
AND rol.manager_flag = 'Y'
AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null)));
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REP_MGR_PARENT';
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => mgr_rec.person_id,
X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
X_GROUP_ID => mem_dtls_rec.group_id,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => mem_dtls_rec.resource_id,
X_PERSON_ID => mem_dtls_rec.person_id,
X_CATEGORY => mem_dtls_rec.category,
X_MANAGER_PERSON_ID => mgr_rec.person_id,
X_PARENT_RESOURCE_ID => mgr_rec.resource_id,
X_GROUP_ID => par_grp_rec.parent_group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => mgr_rec.role_relate_id,
X_CHILD_ROLE_RELATE_ID =>mem_dtls_rec.role_relate_id);
END INSERT_REP_MGR_PARENT;
PROCEDURE INSERT_GRP_DENORM(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_DENORM_ID IN NUMBER,
P_GROUP_ID IN NUMBER,
P_PARENT_GROUP_ID IN NUMBER,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
P_IMMEDIATE_PARENT_FLAG IN VARCHAR2,
P_DENORM_LEVEL IN NUMBER,
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) := 'INSERT_GRP_DENORM';
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
DECODE('Y',nvl(rol.manager_flag,'N'),'MGR',nvl(rol.admin_flag,'N'),'ADMIN',
nvl(rol.member_flag,'N'),'REP','OTHER') ROLE_TYPE,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE mem.group_id = p_group_id
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = mem.group_member_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND rlt.role_id = rol.role_id
AND (nvl(rol.manager_flag, 'N') = 'Y'
OR
nvl(rol.admin_flag, 'N' ) = 'Y'
OR
nvl(rol.member_flag, 'N') = 'Y')
AND mem.resource_id = rsc.resource_id;
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
DECODE('Y',nvl(rol.manager_flag,'N'),'MGR',nvl(rol.admin_flag,'N'),'ADMIN',
nvl(rol.member_flag,'N'),'REP','OTHER') ROLE_TYPE,
rsc.category,
rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = mem.group_member_id
AND rlt.role_id = rol.role_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND mem.resource_id = rsc.resource_id
AND mem.group_id = p_group_id
AND nvl(rol.manager_flag,'N') = 'Y';
SELECT jtf_rs_rep_managers_s.nextval
INTO l_denorm_manager_id
FROM dual;
INSERT INTO JTF_RS_REP_MANAGERS
( DENORM_MGR_ID,
RESOURCE_ID,
PERSON_ID,
CATEGORY,
MANAGER_PERSON_ID,
GROUP_ID,
HIERARCHY_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REPORTS_TO_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
OBJECT_VERSION_NUMBER,
PAR_ROLE_RELATE_ID,
CHILD_ROLE_RELATE_ID,
PARENT_RESOURCE_ID,
DENORM_LEVEL)
VALUES
(L_DENORM_MANAGER_ID,
l_process_table(k).RESOURCE_ID,
l_process_table(k).PERSON_ID,
l_process_table(k).CATEGORY,
l_process_table(k).MANAGER_PERSON_ID,
l_process_table(k).GROUP_ID,
l_process_table(k).HIERARCHY_TYPE,
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id,
l_process_table(k).REPORTS_TO_FLAG,
trunc(l_process_table(k).START_DATE_ACTIVE),
trunc(l_process_table(k).END_DATE_ACTIVE),
1,
l_process_table(k).PAR_ROLE_RELATE_ID,
l_process_table(k).CHILD_ROLE_RELATE_ID,
l_process_table(k).PARENT_RESOURCE_ID,
p_denorm_level);
l_process_table.DELETE;
l_diff_grp_parent_mbr_role.DELETE;
l_diff_grp_child_mbr_role.DELETE;
END INSERT_GRP_DENORM;
PROCEDURE DELETE_REP_MGR (
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_ID IN JTF_RS_GROUPS_DENORM.GROUP_ID%TYPE,
P_PARENT_GROUP_ID IN JTF_RS_GROUPS_DENORM.PARENT_GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
CURSOR c_child_role_relate_cur(l_group_id IN NUMBER) IS
SELECT rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem
WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = mem.group_member_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND mem.group_id = l_group_id;
SELECT rlt.role_relate_id
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem
WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = mem.group_member_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND mem.group_id = l_parent_group_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_REP_MGR';
DELETE JTF_RS_REP_MANAGERS
WHERE child_role_relate_id = r_child_role_relate_rec.role_relate_id
AND par_role_relate_id = r_parent_role_relate_rec.role_relate_id ;
END DELETE_REP_MGR;
/*FOR INSERT IN JTF_RS_GRP_RELATIONS */
--not being used now as this id done from group denorm which calls INSERT_GRP_DENORM
PROCEDURE INSERT_GRP_RELATIONS(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE := p_group_relate_id;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GRP_RELATIONS';
SELECT related_group_id,
group_id,
start_date_active,
end_date_active
FROM jtf_rs_grp_relations
WHERE group_relate_id = l_group_relate_id
and delete_flag <> 'Y';
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_relate_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
rol.admin_flag ,
rol.manager_flag
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_B rol
WHERE mem.group_id IN ( select distinct(parent_group_id)
from jtf_rs_groups_denorm
where group_id = l_group_id)
/* this has been added to include all parents in the hierarchy */
AND mem.group_member_id = rlt.role_resource_id
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = rol.role_id
AND nvl(rlt.delete_flag, 'N') <> 'Y'
AND (rol.admin_flag = 'Y'
OR manager_flag = 'Y')
AND ((l_start_date_active between rlt.start_date_active
and nvl(rlt.end_date_active , l_start_date_active +1))
OR ((nvl(l_end_date_active, rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, l_end_date_active + 1))
or (l_end_date_active is null and rlt.end_date_active is null)));
SELECT group_id,
start_date_active,
end_date_active,
immediate_parent_flag
FROM jtf_rs_groups_denorm
WHERE parent_group_id = l_group_id
AND group_id NOT IN (l_group_id);
SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_relate_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
rol.member_flag ,
rol.admin_flag ,
rol.lead_flag ,
rol.manager_flag,
rsc.category
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = rol.role_id
AND mem.resource_id = rsc.resource_id
AND ((rlt.start_date_active between l_start_date_active and
nvl(l_end_date_active ,rlt.start_date_active+1))
OR (rlt.end_date_active between l_start_date_active
and nvl(l_end_date_active,rlt.end_date_active+1))
OR ((rlt.start_date_active <= l_start_date_active)
AND (rlt.end_date_active >= l_end_date_active
OR l_end_date_active IS NULL)));
/* SELECT mem.resource_id,
mem.person_id,
mem.group_id,
rlt.role_relate_id,
rlt.role_id,
rlt.start_date_active,
rlt.end_date_active,
rol.member_flag ,
rol.admin_flag ,
rol.lead_flag ,
rol.manager_flag,
rsc.category
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rlt,
jtf_rs_roles_B rol,
jtf_rs_resource_extns rsc
WHERE mem.group_id = l_group_id
AND mem.group_member_id = rlt.role_resource_id
AND nvl(rlt.delete_flag,'N') <> 'Y'
AND nvl(mem.delete_flag,'N') <> 'Y'
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_id = rol.role_id
AND mem.resource_id = rsc.resource_id
AND rlt.start_date_active >= to_date(to_char(l_start_date_active,'dd-MM-yyyy'),'dd-MM-yyyy')
AND ((to_date(to_char(l_start_date_active,'dd-MM-yyyy'),'dd-MM-yyyy') between rlt.start_date_active
and nvl(rlt.end_date_active , to_date(to_char(l_start_date_active,'dd-MM-yyyy'),'dd-MM-yyyy')+1))
OR ((nvl(to_date(to_char(l_start_date_active,'dd-MM-yyyy'),'dd-MM-yyyy'), rlt.start_date_active +1)
between rlt.start_date_active and
nvl(rlt.end_date_active, to_date(to_char(l_end_date_active,'dd-MM-yyyy'),'dd-MM-yyyy')+ 1))
or (to_date(to_char(l_end_date_active,'dd-MM-yyyy'),'dd-MM-yyyy') is null and rlt.end_date_active is null)));
SELECT jtf_rs_rep_managers_s.nextval
FROM dual;
SELECT person_id
FROM jtf_rs_rep_managers
WHERE par_role_relate_id = l_par_role_relate_id
AND child_role_relate_id = l_child_role_relate_id
AND group_id = l_group_id;
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => member_rec.resource_id,
X_PERSON_ID => member_rec.person_id,
X_CATEGORY => member_rec.category,
X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
X_GROUP_ID => member_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).P_role_relate_id,
X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => member_rec.resource_id,
X_PERSON_ID => member_rec.person_id,
X_CATEGORY => member_rec.category,
X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
X_GROUP_ID => l_mgr_rec(i).p_group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).P_role_relate_id,
X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
END IF; --end of reverse record insert
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => member_rec.resource_id,
X_PERSON_ID => member_rec.person_id,
X_CATEGORY => member_rec.category,
X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
X_GROUP_ID => member_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).p_role_relate_id,
X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => member_rec.resource_id,
X_PERSON_ID => member_rec.person_id,
X_CATEGORY => member_rec.category,
X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
X_GROUP_ID => member_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).P_role_relate_id,
X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => member_rec.resource_id,
X_PERSON_ID => member_rec.person_id,
X_CATEGORY => member_rec.category,
X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
X_GROUP_ID => l_mgr_rec(i).p_group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => trunc(l_start_date_active),
X_END_DATE_ACTIVE => trunc(l_end_date_active),
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).P_role_relate_id,
X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
jtf_rs_rep_managers_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_MGR_ID => l_denorm_mgr_id,
X_RESOURCE_ID => member_rec.resource_id,
X_PERSON_ID => member_rec.person_id,
X_CATEGORY => member_rec.category,
X_MANAGER_PERSON_ID =>l_mgr_rec(i).p_person_id,
X_PARENT_RESOURCE_ID => l_mgr_rec(i).p_resource_id,
X_GROUP_ID => member_rec.group_id,
X_REPORTS_TO_FLAG => l_reports_to_flag,
X_HIERARCHY_TYPE => l_hierarchy_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
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,
X_PAR_ROLE_RELATE_ID => l_mgr_rec(i).p_role_relate_id,
X_CHILD_ROLE_RELATE_ID => member_rec.role_relate_id);
END INSERT_GRP_RELATIONS;