[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT current_date_id
FROM bis_system_date;
SELECT i.tablespace, i.index_tablespace, u.oracle_username
FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
WHERE a.application_short_name = 'JTF'
AND a.application_id = i.application_id
AND u.oracle_id = i.oracle_id;
l_insert_count NUMBER ;
l_insert_count := 0;
SELECT owner, table_owner, tablespace_name
INTO l_index_owner, l_table_owner, l_index_tblspace
FROM ALL_INDEXES
WHERE TABLE_NAME = 'JTF_RS_DBI_DENORM_RES_GROUPS'
AND index_name = 'JTF_RS_DBI_DENORM_RES_GRPS_N1'
AND table_owner= l_jtfu;
SELECT owner, table_owner, tablespace_name
INTO l_index_owner, l_table_owner, l_index_tblspace
FROM ALL_INDEXES
WHERE TABLE_NAME = 'JTF_RS_GROUPS_DENORM'
AND index_name = 'JTF_RS_GROUPS_DENORM_U1'
AND table_owner= l_jtfu;
INSERT /*+ APPEND PARALLEL(jtf_rs_dbi_mgr_groups) NOLOGGING */
INTO jtf_rs_dbi_mgr_groups
(dbi_mgr_id, resource_id, user_id, group_id,
creation_date, created_by )
SELECT /*+ use_hash(x) parallel(x) */
jtf_rs_dbi_mgr_groups_s.nextval, x.resource_id, x.user_id, x.group_id
, l_sysdate, l_user_id
FROM (
SELECT /*+ use_hash(res mgr) parallel(res) parallel(mgr) */
DISTINCT mgr.resource_id, res.user_id, mgr.group_id
FROM jtf_rs_rep_managers mgr, jtf_rs_resource_extns res
WHERE mgr.hierarchy_type IN ('MGR_TO_MGR','ADMIN_TO_ADMIN')
AND mgr.resource_id = mgr.parent_resource_id
AND l_bis_date BETWEEN mgr.start_date_active
AND NVL(mgr.end_date_active,to_date('12/31/4712','MM/DD/RRRR'))
AND mgr.resource_id = res.resource_id
AND res.user_id IS NOT NULL ) x
;
INSERT /*+ APPEND PARALLEL(jtf_rs_dbi_denorm_res_groups) NOLOGGING */
INTO jtf_rs_dbi_denorm_res_groups
(VALUE, id , current_id ,
parent_id , denorm_level , start_date ,
end_date , user_id , resource_id,
debug_column, denorm_id , mem_flag,
mem_status , creation_date, created_by, active_grp_rel_only )
SELECT /*+ use_hash(g x) parallel(g) parallel(x) */
' * ' VALUE, x.group_id id , TO_NUMBER(-9999) current_id,
x.group_id parent_id, TO_NUMBER(0) denorm_level, g.start_date_active start_date,
g.end_date_active end_date, x.user_id user_id, x.resource_id resource_id,
'Z-TOP-MANAGER-GROUPS' debug_column, jtf_rs_dbi_denorm_res_groups_s.NEXTVAL denorm_id, 'N' mem_flag,
'A' mem_status, l_sysdate creation_date, l_user_id created_by
, 'Y' active_grp_rel_only
FROM (
SELECT /*+ use_hash(res mgr) parallel(res) parallel(mgr) */
DISTINCT mgr.resource_id, res.user_id, mgr.group_id
FROM jtf_rs_rep_managers mgr, jtf_rs_resource_extns res
WHERE mgr.hierarchy_type IN ('MGR_TO_MGR','ADMIN_TO_ADMIN')
AND mgr.resource_id = mgr.parent_resource_id
AND l_bis_date BETWEEN mgr.start_date_active
AND NVL(mgr.end_date_active,to_date('12/31/4712','MM/DD/RRRR'))
AND mgr.resource_id = res.resource_id
AND res.user_id IS NOT NULL ) x
, jtf_rs_groups_b g
WHERE x.group_id = g.group_id
;
INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
(VALUE,
id ,
current_id ,
parent_id ,
denorm_level ,
start_date ,
end_date ,
user_id ,
resource_id,
debug_column,
denorm_id ,
mem_flag,
mem_status,
creation_date,
created_by,
active_grp_rel_only )
SELECT /*+ use_hash(d1 n1) PARALLEL(d1) PARALLEL(n1) */
DECODE(d1.denorm_level,1,DECODE(d1.active_flag,'Y','-- ','-- [ '),' ') VALUE,
--DECODE(d1.denorm_level,1,'-- ',' ') VALUE,
d1.group_id id,
TO_NUMBER(-1111) current_id, d1.actual_parent_id parent_id,
d1.denorm_level , d1.start_date_active start_date,
d1.end_date_active end_date, n1.user_id ,n1.resource_id ,
'0-FIRST-TIME-GROUPS' debug_column, jtf_rs_dbi_denorm_res_groups_s.nextval
,'N' mem_flag , 'A' mem_status
,l_sysdate, l_user_id
,DECODE(d1.active_flag,'Y','Y','N')
FROM jtf_rs_groups_denorm d1 , jtf_rs_dbi_mgr_groups n1
WHERE n1.group_id = d1.actual_parent_id
AND d1.denorm_level < 2
AND d1.latest_relationship_flag = 'Y'
;
INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
(VALUE,
id ,
current_id ,
parent_id ,
denorm_level ,
start_date ,
end_date ,
user_id ,
resource_id,
debug_column,
denorm_id ,
mem_flag,
mem_status ,
creation_date,
created_by,
active_grp_rel_only )
SELECT /*+ use_hash(d1 d2 n1) PARALLEL(d1) PARALLEL(d2) PARALLEL(n1) */
DECODE (d1.active_flag,'Y',' ',' [ ') VALUE,
d1.group_id id, d2.group_id current_id,
d1.actual_parent_id parent_id, d1.denorm_level,
d1.start_date_active start_date, d1.end_date_active end_date,
n1.user_id, n1.resource_id, 'A-PARENT' debug_column,
jtf_rs_dbi_denorm_res_groups_s.nextval
,'N' mem_flag , 'A' mem_status
,l_sysdate, l_user_id
,DECODE(d1.active_flag,'Y','Y','N')
FROM jtf_rs_groups_denorm d1 , jtf_rs_groups_denorm d2,
jtf_rs_dbi_mgr_groups n1
WHERE n1.group_id = d1.parent_group_id
AND d1.group_id = d2.actual_parent_id
AND n1.group_id = d2.parent_group_id
AND d1.group_id <> d2.group_id
AND d1.latest_relationship_flag = 'Y'
AND d2.latest_relationship_flag = 'Y'
;
INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
(VALUE,
id ,
current_id ,
parent_id ,
denorm_level ,
start_date ,
end_date ,
user_id ,
resource_id,
debug_column,
denorm_id ,
mem_flag,
mem_status,
creation_date,
created_by,
active_grp_rel_only )
SELECT /*+ use_hash(d1 n1) PARALLEL(d1) PARALLEL(n1) */
DECODE(d1.active_flag,'Y','-- ','-- [ ') VALUE
, d1.group_id id, d1.group_id current_id,
d1.actual_parent_id parent_id, d1.denorm_level,
d1.start_date_active start_date, d1.end_date_active end_date,
n1.user_id, n1.resource_id, 'C-SELF' debug_column,
jtf_rs_dbi_denorm_res_groups_s.nextval
,'N' mem_flag , 'A' mem_status
,l_sysdate, l_user_id
,DECODE(d1.active_flag,'Y','Y','N')
FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
WHERE n1.group_id = d1.parent_group_id
AND d1.latest_relationship_flag = 'Y'
;
INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
(VALUE,
id ,
current_id ,
parent_id ,
denorm_level ,
start_date ,
end_date ,
user_id ,
resource_id,
debug_column,
denorm_id ,
mem_flag,
mem_status ,
creation_date,
created_by,
active_grp_rel_only )
SELECT /*+ use_hash(d1 n1) PARALLEL(d1) PARALLEL(n1) */
DECODE (d1.active_flag, 'Y','---- ','---- [ ') VALUE
,d1.group_id id,
d1.actual_parent_id current_id, d1.actual_parent_id parent_id,
d1.denorm_level, d1.start_date_active start_date,
d1.end_date_active end_date, n1.user_id, n1.resource_id, 'D-CHILD' debug_column
, jtf_rs_dbi_denorm_res_groups_s.nextval
,'N' mem_flag , 'A' mem_status
,l_sysdate, l_user_id
,DECODE(d1.active_flag,'Y','Y','N')
FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
WHERE n1.group_id = d1.parent_group_id
AND d1.denorm_level > 0
AND d1.latest_relationship_flag = 'Y'
;
SELECT /*+ use_hash(d1 drg1) PARALLEL(d1) PARALLEL(drg1) */
/* DECODE (d1.active_flag ,'Y', ' -- ', ' -- [ ') VALUE,
d1.group_id id, drg1.current_id current_id,
d1.parent_group_id parent_id, d1.denorm_level denorm_level,
d1.start_date_active start_date, d1.end_date_active end_date,
drg1.user_id, drg1.resource_id, 'B-PEER' DEBUG_COLUMN , jtf_rs_dbi_denorm_res_groups_s.nextval
,'N' mem_flag , 'A' mem_status
,l_sysdate, l_user_id
,DECODE(d1.active_flag,'Y','Y','N') active_grp_rel_only
FROM jtf_rs_groups_denorm d1,
jtf_rs_dbi_denorm_res_groups drg1
WHERE drg1.current_id = drg1.id
AND drg1.denorm_level > 0
AND drg1.parent_id = d1.parent_group_id
AND drg1.current_id <> d1.group_id
AND drg1.parent_id = d1.actual_parent_id
AND d1.denorm_level = 1
AND d1.latest_relationship_flag = 'Y'
;
'INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
(VALUE,
id_for_grp_mem ,
current_id ,
parent_id ,
denorm_level ,
debug_column,
denorm_id,
grp_mem_resource_id,
mem_flag,
mem_status,
creation_date,
created_by,
active_grp_rel_only )
SELECT Decode(x.mem_status,''I'',''----[ '',''----'')value,
x.resource_id||''.''||x.group_id id_for_grp_mem, x.group_id current_id,
x.group_id parent_id, to_number(100) denorm_level,
''E-SELF-GROUP-MEMBERS'' debug_column , jtf_rs_dbi_denorm_res_groups_s.NEXTVAL denorm_id
,x.resource_id grp_mem_resource_id, ''Y'' mem_flag, x.mem_status
,:l_sysdate, :l_user_id, ''Y'' active_grp_rel_only
FROM (
SELECT /*+ use_hash(gm1 rrl1 rol1) PARALLEL(gm1) PARALLEL(rrl1) PARALLEL(rol1) */
DISTINCT gm1.resource_id, gm1.group_id
, ''A'' mem_status
FROM jtf_rs_group_members gm1, jtf_rs_role_relations rrl1, jtf_rs_roles_b rol1
WHERE gm1.group_member_id = rrl1.role_resource_id
AND gm1.delete_flag = ''N''
AND rrl1.role_resource_type = ''RS_GROUP_MEMBER''
AND rrl1.delete_flag = ''N''
AND rrl1.role_id = rol1.role_id
AND ''Y'' IN (rol1.member_flag, rol1.manager_flag)
AND rrl1.active_flag = ''Y''
UNION ALL
SELECT /*+ use_hash(gm2 rrl2 rol2) PARALLEL(gm2) PARALLEL(rrl2) PARALLEL(rol2) */
DISTINCT gm2.resource_id, gm2.group_id
, ''I'' mem_status
FROM jtf_rs_group_members gm2, jtf_rs_role_relations rrl2, jtf_rs_roles_b rol2
WHERE gm2.group_member_id = rrl2.role_resource_id
AND gm2.delete_flag = ''N''
AND rrl2.role_resource_type = ''RS_GROUP_MEMBER''
AND rrl2.delete_flag = ''N''
AND rrl2.role_id = rol2.role_id
AND ''Y'' IN (rol2.member_flag, rol2.manager_flag)
AND rrl2.active_flag IS NULL
AND NOT EXISTS (
SELECT /*+ use_hash(gm3 rrl3 rol3) PARALLEL(gm3) PARALLEL(rrl3) PARALLEL(rol3) */
''1''
FROM jtf_rs_group_members gm3, jtf_rs_role_relations rrl3
, jtf_rs_roles_b rol3
WHERE gm3.group_member_id = rrl3.role_resource_id
AND gm3.delete_flag = ''N''
AND rrl3.role_resource_type = ''RS_GROUP_MEMBER''
AND rrl3.delete_flag = ''N''
AND rrl3.role_id = rol3.role_id
AND ''Y'' IN (rol3.member_flag, rol3.manager_flag)
AND rrl3.active_flag = ''Y''
AND gm3.resource_id = gm2.resource_id
AND gm3.group_id = gm2.group_id
)
) x
' USING l_sysdate, l_user_id
;
SELECT Decode(x.mem_status,''I'',''--[ '',''--'')value,
x.grp_mem_resource_id||''.''||x.group_id id_for_grp_mem, to_number(-1111) current_id,
x.group_id parent_id, to_number(1) denorm_level, x.user_id, x.resource_id,
''F-FIRST-TIME-GROUP-MEMBERS'' debug_column , jtf_rs_dbi_denorm_res_groups_s.NEXTVAL denorm_id
,x.grp_mem_resource_id, ''Y'' mem_flag, x.mem_status
,:l_sysdate, :l_user_id
FROM (
SELECT /*+ use_hash(dbi mem rrl rol) PARALLEL(dbi) PARALLEL(mem) PARALLEL(rrl)
PARALLEL(rol)*/
/* DISTINCT mem.resource_id grp_mem_resource_id, mem.group_id, to_number(-1111) current_id,
dbi.user_id, dbi.resource_id,
CASE WHEN :l_bis_date BETWEEN rrl.start_date_active AND nvl(rrl.end_date_active, :l_bis_date + 1)
THEN ''A'' ELSE ''I'' END AS mem_status
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rrl,
jtf_rs_dbi_denorm_res_groups dbi
,jtf_rs_roles_b rol
WHERE mem.group_member_id = rrl.role_resource_id
AND mem.delete_flag = ''N''
AND rrl.role_resource_type = ''RS_GROUP_MEMBER''
AND rrl.delete_flag = ''N''
AND dbi.id = mem.group_id
AND dbi.id = dbi.parent_id
AND dbi.current_id = -1111
AND dbi.denorm_level = 0
AND rrl.role_id = rol.role_id
AND rol.member_flag = ''Y''
) x
' USING l_sysdate, l_user_id, l_bis_date, l_bis_date;
SELECT COUNT(*)
INTO l_insert_count
FROM jtf_rs_dbi_denorm_res_groups;
p_count => l_insert_count,
p_period_to => l_bis_date);
BIS_COLLECTION_UTILITIES.Debug('Error in Update Sales Group Hierarchy: '||l_stage||errbuf);
p_count => l_insert_count,
p_period_to => l_bis_date);
'SELECT id
FROM (
SELECT id, rank() over (order by value, id nulls last) rnk
FROM jtf_rs_dbi_res_grp_vl
WHERE usage = ''SALES''
AND current_id = -1111
AND denorm_level = 0
)
WHERE rnk = 1' INTO l_sg_id;