[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT A.RELATED_GROUP_ID FROM JTF_RS_GRP_RELATIONS A
WHERE A.GROUP_ID = P_GROUP_ID
AND NVL(A.DELETE_FLAG, 'N') <> 'Y'
AND A.START_DATE_ACTIVE <= P_START_DATE
AND NVL(P_END_DATE, P_START_DATE) <= NVL(A.END_DATE_ACTIVE,
NVL(P_END_DATE, P_START_DATE))
ORDER BY A.START_DATE_ACTIVE; -- just in case there are multiple
PROCEDURE INSERT_GROUPS_NO_CON(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 );
PROCEDURE UPDATE_GROUPS_NO_CON(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 );
PROCEDURE DELETE_GRP_RELATIONS_NO_CON(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_relate_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
p_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
p_related_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2);
PROCEDURE INSERT_GROUPS_PARENT_NO_CON(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 );
SELECT den.group_id
FROM jtf_rs_groups_denorm den
WHERE den.group_id = x_group_id
AND den.parent_group_id = x_parent_group_id;
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp
WHERE group_id = x_group_id;
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_DENORM_GRP_ID
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => p_group_id,
X_PARENT_GROUP_ID => p_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
X_START_DATE_ACTIVE => trunc(l_start_date),
X_END_DATE_ACTIVE => trunc(l_end_date),
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_DENORM_LEVEL => 0) ;
PROCEDURE UPDATE_RES_GROUPS(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%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_RES_GROUPS';
SELECT denorm_grp_id
FROM jtf_rs_groups_denorm
WHERE group_id = l_group_id
AND parent_group_id = l_group_id;
jtf_rs_groups_denorm_pkg.delete_row(X_DENORM_GRP_ID => l_DENORM_GRP_ID);
END UPDATE_RES_GROUPS;
PROCEDURE INSERT_GROUPS(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR c_parents(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
IS
SELECT rel.group_id,
rel.related_group_id,
rel.start_date_active,
rel.end_date_active,
rel.delete_flag,
level
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
CONNECT BY rel.group_id = prior rel.related_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
AND rel.related_group_id <> x_group_id
START WITH rel.group_id = x_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y';
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp
WHERE group_id = x_group_id;
SELECT den.group_id
FROM jtf_rs_groups_denorm den
WHERE den.group_id = x_group_id
AND den.parent_group_id = x_parent_group_id
--AND start_date_active = l_start_date_active
AND ((l_start_date_active between den.start_date_active and
nvl(den.end_date_active,l_start_date_active+1))
OR (l_end_date_active between den.start_date_active
and nvl(den.end_date_active,l_end_date_active+1))
OR ((l_start_date_active <= den.start_date_active)
AND (l_end_date_active >= den.end_date_active
OR l_end_date_active IS NULL)));
SELECT rel.group_id,
rel.related_group_id,
rel.start_date_active,
rel.end_date_active,
level
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
CONNECT BY rel.related_group_id = prior rel.group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
AND rel.group_id <> x_group_id
START WITH rel.related_group_id = x_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y';
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUPS';
procedure delete_table(p_level in number,
l_flag in varchar2)
is
k BINARY_INTEGER;
level_child_table.DELETE(j);
level_child_table.DELETE(j);
level_par_table.DELETE(j);
level_par_table.DELETE(j);
end delete_table;
delete_table(l_child_tab(i).level, 'C');
delete_table(l_child_tab(i).level, 'C');
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_denorm_grp_id
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => l_child_tab(i).p_group_id,
X_PARENT_GROUP_ID => p_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
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_DENORM_LEVEL => l_child_tab(i).level);
JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
P_API_VERSION => 1.0,
P_GROUP_DENORM_ID => l_denorm_grp_id,
P_GROUP_ID => l_child_tab(i).p_group_id ,
P_PARENT_GROUP_ID => p_group_id ,
P_START_DATE_ACTIVE => l_start_date_active ,
P_END_DATE_ACTIVE => l_end_date_active ,
P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
P_DENORM_LEVEL => l_child_tab(i).level,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
IF(r_parents.delete_flag <> 'Y')
THEN
l_start_date := r_parents.start_date_active;
delete_table(r_parents.level, 'P');
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_denorm_grp_id
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => p_group_id,
X_PARENT_GROUP_ID => r_parents.related_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
X_START_DATE_ACTIVE => trunc(l_start_date_1),
X_END_DATE_ACTIVE => trunc(l_end_date_1),
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_DENORM_LEVEL => r_parents.level);
JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
P_API_VERSION => 1.0,
P_GROUP_DENORM_ID => l_denorm_grp_id,
P_GROUP_ID => p_group_id ,
P_PARENT_GROUP_ID => r_parents.related_group_id ,
P_START_DATE_ACTIVE => l_start_date_1 ,
P_END_DATE_ACTIVE => l_end_date_1 ,
P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
P_DENORM_LEVEL => r_parents.level,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
delete_table(l_child_tab(i).level, 'C');
delete_table(l_child_tab(i).level, 'C');
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_denorm_grp_id
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => l_child_tab(i).p_group_id,
X_PARENT_GROUP_ID => r_parents.related_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
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_DENORM_LEVEL => l_child_tab(i).level + r_parents.level);
JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
P_API_VERSION => 1.0,
P_GROUP_DENORM_ID => l_denorm_grp_id,
P_GROUP_ID => l_child_tab(i).p_group_id ,
P_PARENT_GROUP_ID => r_parents.related_group_id ,
P_START_DATE_ACTIVE => l_start_date_active ,
P_END_DATE_ACTIVE => l_end_date_active ,
P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
P_DENORM_LEVEL => l_child_tab(i).level + r_parents.level,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
END LOOP; -- end of child tab insert
delete_table(1, 'C');
END IF; -- end of delete flag check
INSERT_GROUPS_NO_CON(
P_API_VERSION => cb_p_api_version,
P_INIT_MSG_LIST => cb_p_init_msg_list,
P_COMMIT => cb_p_commit,
p_group_id => cb_p_group_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
END INSERT_GROUPS;
PROCEDURE UPDATE_GROUPS(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.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(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
IS
SELECT rel.group_id,
rel.related_group_id,
rel.start_date_active,
rel.end_date_active
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
CONNECT BY rel.related_group_id = prior rel.group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
AND rel.group_id <> x_group_id
START WITH rel.group_id = x_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y';
SELECT denorm_grp_id,
group_id,
parent_group_id
FROM JTF_RS_GROUPS_DENORM
WHERE group_id = l_group_id
AND PARENT_GROUP_ID <> L_GROUP_ID;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUPS';
--delete the previous hierarchy for the group
for r_group_denorm IN c_group_denorm(p_group_id)
loop
JTF_RS_REP_MGR_DENORM_PVT.DELETE_REP_MGR (
P_API_VERSION => 1.0,
P_GROUP_ID => r_group_denorm.group_id,
P_PARENT_GROUP_ID => r_group_denorm.parent_group_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.DENORM_GRP_ID);
--delete the hiearchy of all the child records of the group
FOR r_child IN c_child(p_group_id)
LOOP
for r_group_denorm IN c_group_denorm(r_child.group_id)
loop
JTF_RS_REP_MGR_DENORM_PVT.DELETE_REP_MGR (
P_API_VERSION => 1.0,
P_GROUP_ID => r_group_denorm.group_id,
P_PARENT_GROUP_ID => r_group_denorm.parent_group_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.DENORM_GRP_ID);
JTF_RS_GROUP_DENORM_PVT.Insert_Groups(1.0,NULL, NULL,r_child.group_id, x_return_status, x_msg_count, x_msg_data);
JTF_RS_GROUP_DENORM_PVT.Insert_Groups(1.0,NULL, NULL,p_group_id, x_return_status, x_msg_count, x_msg_data);
UPDATE_GROUPS_NO_CON(
P_API_VERSION => cb_p_api_version,
P_INIT_MSG_LIST => cb_p_init_msg_list,
P_COMMIT => cb_p_commit,
p_group_id => cb_p_group_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
END UPDATE_GROUPS;
PROCEDURE DELETE_GRP_RELATIONS(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_relate_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
p_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
p_related_group_id IN JTF_RS_GROUPS_VL.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(l_group_id number)
is
select group_id,
related_group_id,
start_date_active,
end_date_active
from jtf_rs_grp_relations
where relation_type = 'PARENT_GROUP'
connect by related_group_id = prior group_id
and nvl(delete_flag, 'N') <> 'Y'
-- and group_id <> l_group_id
start with related_group_id = l_group_id
and nvl(delete_flag, 'N') <> 'Y';
SELECT rel.group_id,
rel.related_group_id,
rel.start_date_active,
rel.end_date_active
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
AND related_group_id = l_related_group_id
CONNECT BY rel.group_id = prior rel.related_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
--AND rel.related_group_id <> p_related_group_id
START WITH rel.group_id = l_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y';
SELECT rel.group_id,
rel.related_group_id,
rel.start_date_active,
rel.end_date_active
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
CONNECT BY rel.group_id = prior rel.related_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
--AND rel.related_group_id <> p_related_group_id
START WITH rel.group_id = p_related_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y';
select rel.role_relate_id,
mem.group_id
from jtf_rs_group_members mem,
jtf_rs_role_relations rel
where mem.group_id = l_no
and nvl(mem.delete_flag , 'N') <> 'Y'
and mem.group_member_id = rel.role_resource_id
and rel.role_resource_type = 'RS_GROUP_MEMBER'
and nvl(rel.delete_flag, 'N') <> 'Y';
select rel.role_relate_id,
mem.group_id
from jtf_rs_group_members mem,
jtf_rs_role_relations rel,
jtf_rs_roles_b rol
where mem.group_id = l_group_id
and nvl(mem.delete_flag , 'N') <> 'Y'
and mem.group_member_id = rel.role_resource_id
and rel.role_resource_type = 'RS_GROUP_MEMBER'
and nvl(rel.delete_flag, 'N') <> 'Y'
and rel.role_id = rol.role_id
and (
nvl(rol.manager_flag, 'N') = 'Y'
or
nvl(rol.admin_flag, 'N') = 'Y'
);
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GRP_RELATIONS';
delete jtf_rs_groups_denorm
where group_id = l_child_tab(i).p_group_id
and parent_group_id = l_parent_tab(j).p_related_group_id;
delete jtf_rs_rep_managers
where par_role_relate_id = l_par_rol_rel_tab(l).role_relate_id
and child_role_relate_id = l_child_rol_rel_tab(k).role_relate_id;
jtf_rs_group_denorm_pvt.insert_groups_parent(
p_api_version => 1.0,
p_commit => 'T',
p_group_id => l_child_tab(i).p_group_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/* this has been moved to jtf_rs_groups_denorm.insert_groups_parent
if(l_found)
then
--rebuild the parent rep managers for the parent role relate ids only
FOR k IN 1 .. l_child_rol_rel_tab.COUNT
LOOP
if(l_child_rol_rel_tab(k).group_id = l_child_tab(i).p_group_id)
then
jtf_rs_rep_mgr_denorm_pvt.insert_rep_mgr_parent(
p_api_version => 1.0,
p_commit => 'T',
p_role_relate_id => l_child_rol_rel_tab(k).role_relate_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
DELETE_GRP_RELATIONS_NO_CON(
P_API_VERSION => cb_p_api_version,
P_INIT_MSG_LIST => cb_p_init_msg_list,
P_COMMIT => cb_p_commit,
p_group_id => cb_p_group_id,
p_group_relate_id => cb_p_group_relate_id,
p_related_group_id => cb_p_related_group_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
end delete_grp_relations;
PROCEDURE INSERT_GROUPS_PARENT(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR c_parents(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
IS
SELECT rel.group_id,
rel.related_group_id,
rel.start_date_active,
rel.end_date_active,
rel.delete_flag,
level
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
CONNECT BY rel.group_id = prior rel.related_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
AND rel.related_group_id <> x_group_id
START WITH rel.group_id = x_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y';
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp
WHERE group_id = x_group_id;
SELECT den.group_id
FROM jtf_rs_groups_denorm den
WHERE den.group_id = x_group_id
AND den.parent_group_id = x_parent_group_id
--AND start_date_active = l_start_date_active
AND ((l_start_date_active between den.start_date_active and
nvl(den.end_date_active,l_start_date_active+1))
OR (l_end_date_active between den.start_date_active
and nvl(den.end_date_active,l_end_date_active+1))
OR ((l_start_date_active <= den.start_date_active)
AND (l_end_date_active >= den.end_date_active
OR l_end_date_active IS NULL)));
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUPS_PARENT';
procedure delete_table(p_level in number)
is
k BINARY_INTEGER;
level_value_table.DELETE(j);
level_value_table.DELETE(j);
end delete_table;
IF(r_parents.delete_flag <> 'Y')
THEN
l_start_date := r_parents.start_date_active;
delete_table(r_parents.level);
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_denorm_grp_id
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => p_group_id,
X_PARENT_GROUP_ID => r_parents.related_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
X_START_DATE_ACTIVE => trunc(l_start_date_1),
X_END_DATE_ACTIVE => trunc(l_end_date_1),
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_DENORM_LEVEL => r_parents.level );
JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
P_API_VERSION => 1.0,
P_GROUP_DENORM_ID => l_denorm_grp_id,
P_GROUP_ID => p_group_id ,
P_PARENT_GROUP_ID => r_parents.related_group_id ,
P_START_DATE_ACTIVE => l_start_date_1 ,
P_END_DATE_ACTIVE => l_end_date_1 ,
P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
P_DENORM_LEVEL => r_parents.level,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
END IF; -- end of delete flag check
INSERT_GROUPS_PARENT_NO_CON(
P_API_VERSION => cb_p_api_version,
P_INIT_MSG_LIST => cb_p_init_msg_list,
P_COMMIT => cb_p_commit,
p_group_id => cb_p_group_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
END INSERT_GROUPS_PARENT;
PROCEDURE DELETE_GROUPS(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
CURSOR c_group_denorm(l_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
IS
SELECT denorm_grp_id,
parent_group_id
FROM JTF_RS_GROUPS_DENORM
WHERE group_id = l_group_id
AND parent_group_id <> l_group_id; --added this
SELECT denorm_grp_id
FROM JTF_RS_GROUPS_DENORM
WHERE group_id = l_group_id
AND parent_group_id = l_parent_group_id;
SELECT rel.group_id,
rel.related_group_id,
rel.start_date_active,
rel.end_date_active
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
CONNECT BY rel.related_group_id = prior rel.group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
AND rel.group_id <> x_group_id
START WITH rel.related_group_id = x_group_id
and nvl(rel.delete_flag,'N') <> 'Y';
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUPS';
--delete the previous hierarchy for the group
for r_group_denorm IN c_group_denorm(p_group_id)
loop
--call to DELETt records in jtf_rs_rep_managers
JTF_RS_REP_MGR_DENORM_PVT.DELETE_GROUP_DENORM
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_DENORM_GRP_ID => r_group_denorm.denorm_grp_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.DELETE_GROUP_DENORM
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_DENORM_GRP_ID => r_child_denorm.denorm_grp_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
JTF_RS_GROUP_DENORM_PVT.Insert_Groups(1.0,NULL, NULL, p_group_id, x_return_status, x_msg_count, x_msg_data);
END DELETE_GROUPS;
SELECT rel.group_id,
rel.related_group_id,
trunc(greatest(rel.start_date_active,
nvl(p_greatest_start_date, rel.start_date_active))) greatest_start_date,
/* Logic : end_date_active, p_least_end_date
NULL , NULL = NULL
NULL , Value = Value
Value , NULL = Value
Value1 , Value2 = least(value1, value2) */
trunc(least(nvl(rel.end_date_active, p_least_end_date),
nvl(p_least_end_date, rel.end_date_active))) least_end_date
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
AND rel.group_id = p_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
AND least(nvl(end_date_active, to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR')),
nvl(p_least_end_date, to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR'))) >=
trunc(greatest(start_date_active,
nvl(p_greatest_start_date, start_date_active)));
g_parent_tab.delete;
SELECT rel.group_id,
rel.related_group_id,
trunc(greatest(rel.start_date_active,
nvl(p_greatest_start_date, rel.start_date_active))) greatest_start_date,
/* Logic : end_date_active, p_least_end_date
NULL , NULL = NULL
NULL , Value = Value
Value , NULL = Value
Value1 , Value2 = least(value1, value2) */
trunc(least(nvl(rel.end_date_active, p_least_end_date),
nvl(p_least_end_date, rel.end_date_active))) least_end_date
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
AND rel.related_group_id = p_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y'
AND least(nvl(end_date_active, to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR')),
nvl(p_least_end_date, to_date(to_char(FND_API.G_MISS_DATE,'dd-MM-RRRR'),'dd-MM-RRRR'))) >=
trunc(greatest(start_date_active,
nvl(p_greatest_start_date, start_date_active)));
g_child_tab.delete;
PROCEDURE INSERT_GROUPS_NO_CON(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR c_date(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
IS
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp
WHERE group_id = x_group_id;
SELECT den.group_id
FROM jtf_rs_groups_denorm den
WHERE den.group_id = x_group_id
AND den.parent_group_id = x_parent_group_id
--AND start_date_active = l_start_date_active
AND ((l_start_date_active between den.start_date_active and
nvl(den.end_date_active,l_start_date_active+1))
OR (l_end_date_active between den.start_date_active
and nvl(den.end_date_active,l_end_date_active+1))
OR ((l_start_date_active <= den.start_date_active)
AND (l_end_date_active >= den.end_date_active
OR l_end_date_active IS NULL)));
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUPS_NO_CON';
procedure delete_table(p_level in number,
l_flag in varchar2)
is
k BINARY_INTEGER;
level_child_table.DELETE(j);
level_child_table.DELETE(j);
level_par_table.DELETE(j);
level_par_table.DELETE(j);
end delete_table;
g_child_tab.delete;
delete_table(l_child_tab(i).level, 'C');
delete_table(l_child_tab(i).level, 'C');
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_denorm_grp_id
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => l_child_tab(i).p_group_id,
X_PARENT_GROUP_ID => p_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
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_DENORM_LEVEL => l_child_tab(i).level);
JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
P_API_VERSION => 1.0,
P_GROUP_DENORM_ID => l_denorm_grp_id,
P_GROUP_ID => l_child_tab(i).p_group_id ,
P_PARENT_GROUP_ID => p_group_id ,
P_START_DATE_ACTIVE => l_start_date_active ,
P_END_DATE_ACTIVE => l_end_date_active ,
P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
P_DENORM_LEVEL => l_child_tab(i).level,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
delete_table(l_parent_tab(j).level, 'P');
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_denorm_grp_id
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => p_group_id,
X_PARENT_GROUP_ID => l_parent_tab(j).p_related_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
X_START_DATE_ACTIVE => trunc(l_start_date_1),
X_END_DATE_ACTIVE => trunc(l_end_date_1),
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_DENORM_LEVEL => l_parent_tab(j).level);
JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
P_API_VERSION => 1.0,
P_GROUP_DENORM_ID => l_denorm_grp_id,
P_GROUP_ID => p_group_id ,
P_PARENT_GROUP_ID => l_parent_tab(j).p_related_group_id ,
P_START_DATE_ACTIVE => l_start_date_1 ,
P_END_DATE_ACTIVE => l_end_date_1 ,
P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
P_DENORM_LEVEL => l_parent_tab(j).level,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
delete_table(l_child_tab(i).level, 'C');
delete_table(l_child_tab(i).level, 'C');
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_denorm_grp_id
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => l_child_tab(i).p_group_id,
X_PARENT_GROUP_ID => l_parent_tab(j).p_related_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
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_DENORM_LEVEL => l_child_tab(i).level + l_parent_tab(j).level);
JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
P_API_VERSION => 1.0,
P_GROUP_DENORM_ID => l_denorm_grp_id,
P_GROUP_ID => l_child_tab(i).p_group_id ,
P_PARENT_GROUP_ID => l_parent_tab(j).p_related_group_id ,
P_START_DATE_ACTIVE => l_start_date_active ,
P_END_DATE_ACTIVE => l_end_date_active ,
P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
P_DENORM_LEVEL => l_child_tab(i).level + l_parent_tab(j).level,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
END LOOP; -- end of child tab insert
delete_table(1, 'C');
END INSERT_GROUPS_NO_CON;
PROCEDURE UPDATE_GROUPS_NO_CON(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR c_group_denorm(l_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
IS
SELECT denorm_grp_id,
group_id,
parent_group_id
FROM JTF_RS_GROUPS_DENORM
WHERE group_id = l_group_id
AND PARENT_GROUP_ID <> L_GROUP_ID;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUPS_NO_CON';
--delete the previous hierarchy for the group
for r_group_denorm IN c_group_denorm(p_group_id)
loop
JTF_RS_REP_MGR_DENORM_PVT.DELETE_REP_MGR (
P_API_VERSION => 1.0,
P_GROUP_ID => r_group_denorm.group_id,
P_PARENT_GROUP_ID => r_group_denorm.parent_group_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.DENORM_GRP_ID);
--delete the hiearchy of all the child records of the group
POPULATE_CHILD_TABLE(p_group_id);
JTF_RS_REP_MGR_DENORM_PVT.DELETE_REP_MGR (
P_API_VERSION => 1.0,
P_GROUP_ID => r_group_denorm.group_id,
P_PARENT_GROUP_ID => r_group_denorm.parent_group_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_groups_denorm_pkg.delete_row(r_group_denorm.DENORM_GRP_ID);
JTF_RS_GROUP_DENORM_PVT.Insert_Groups_No_Con(1.0,NULL, NULL,l_child_tab(i).p_group_id, x_return_status, x_msg_count, x_msg_data);
JTF_RS_GROUP_DENORM_PVT.insert_groups_no_con(1.0,NULL, NULL,p_group_id, x_return_status, x_msg_count, x_msg_data);
END UPDATE_GROUPS_NO_CON;
PROCEDURE DELETE_GRP_RELATIONS_NO_CON(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_relate_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
p_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
p_related_group_id IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
is
i BINARY_INTEGER := 0;
SELECT rel.group_id,
rel.related_group_id,
rel.start_date_active,
rel.end_date_active
FROM jtf_rs_grp_relations rel
WHERE relation_type = 'PARENT_GROUP'
AND related_group_id = l_related_group_id
CONNECT BY rel.group_id = prior rel.related_group_id
AND NVL(rel.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)))
--AND rel.related_group_id <> p_related_group_id
START WITH rel.group_id = l_group_id
AND NVL(rel.delete_flag, 'N') <> 'Y';
select rel.role_relate_id,
mem.group_id
from jtf_rs_group_members mem,
jtf_rs_role_relations rel
where mem.group_id = l_no
and nvl(mem.delete_flag , 'N') <> 'Y'
and mem.group_member_id = rel.role_resource_id
and rel.role_resource_type = 'RS_GROUP_MEMBER'
and nvl(rel.delete_flag, 'N') <> 'Y';
select rel.role_relate_id,
mem.group_id
from jtf_rs_group_members mem,
jtf_rs_role_relations rel,
jtf_rs_roles_b rol
where mem.group_id = l_group_id
and nvl(mem.delete_flag , 'N') <> 'Y'
and mem.group_member_id = rel.role_resource_id
and rel.role_resource_type = 'RS_GROUP_MEMBER'
and nvl(rel.delete_flag, 'N') <> 'Y'
and rel.role_id = rol.role_id
and (
nvl(rol.manager_flag, 'N') = 'Y'
or
nvl(rol.admin_flag, 'N') = 'Y'
);
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GRP_RELATIONS_NO_CON';
g_child_tab.delete;
l_parent_tab.delete;
delete jtf_rs_groups_denorm
where group_id = l_child_tab(i).p_group_id
and parent_group_id = l_parent_tab(j).p_related_group_id;
delete jtf_rs_rep_managers
where par_role_relate_id = l_par_rol_rel_tab(l).role_relate_id
and child_role_relate_id = l_child_rol_rel_tab(k).role_relate_id;
jtf_rs_group_denorm_pvt.insert_groups_parent_no_con(
p_api_version => 1.0,
p_init_msg_list => NULL,
p_commit => 'T',
p_group_id => l_child_tab(i).p_group_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
end delete_grp_relations_no_con;
PROCEDURE INSERT_GROUPS_PARENT_NO_CON(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
p_group_id IN JTF_RS_GROUPS_B.GROUP_ID%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR c_date(x_group_id JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
IS
SELECT grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp
WHERE group_id = x_group_id;
SELECT den.group_id
FROM jtf_rs_groups_denorm den
WHERE den.group_id = x_group_id
AND den.parent_group_id = x_parent_group_id
--AND start_date_active = l_start_date_active
AND ((l_start_date_active between den.start_date_active and
nvl(den.end_date_active,l_start_date_active+1))
OR (l_end_date_active between den.start_date_active
and nvl(den.end_date_active,l_end_date_active+1))
OR ((l_start_date_active <= den.start_date_active)
AND (l_end_date_active >= den.end_date_active
OR l_end_date_active IS NULL)));
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUPS_PARENT_NO_CON';
procedure delete_table(p_level in number)
is
k BINARY_INTEGER;
level_value_table.DELETE(j);
level_value_table.DELETE(j);
end delete_table;
delete_table(l_parent_tab(i).level);
SELECT jtf_rs_groups_denorm_s.nextval
INTO l_denorm_grp_id
FROM dual;
jtf_rs_groups_denorm_pkg.insert_row(
X_ROWID => x_row_id,
X_DENORM_GRP_ID => l_DENORM_GRP_ID,
X_GROUP_ID => p_group_id,
X_PARENT_GROUP_ID => l_parent_tab(i).p_related_group_id,
X_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
X_ACTUAL_PARENT_ID => l_actual_parent_id,
X_START_DATE_ACTIVE => trunc(l_start_date_1),
X_END_DATE_ACTIVE => trunc(l_end_date_1),
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_DENORM_LEVEL => l_parent_tab(i).level );
JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_DENORM(
P_API_VERSION => 1.0,
P_GROUP_DENORM_ID => l_denorm_grp_id,
P_GROUP_ID => p_group_id ,
P_PARENT_GROUP_ID => l_parent_tab(i).p_related_group_id ,
P_START_DATE_ACTIVE => l_start_date_1 ,
P_END_DATE_ACTIVE => l_end_date_1 ,
P_IMMEDIATE_PARENT_FLAG => l_immediate_parent_flag,
P_DENORM_LEVEL => l_parent_tab(i).level,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
END INSERT_GROUPS_PARENT_NO_CON;