[Home] [Help]
3:
4: /****************************************************************************
5: This is 8i compatible concurrent program
6: This is a concurrent program to populate the data in JTF_RS_DBI_MGR_GROUPS
7: and JTF_RS_DBI_DENORM_RES_GROUPS
8: table so that it can be accessed via view JTF_RS_DBI_RES_GRP_VL for Sales
9: Group Hierarchy in DBI product. This program is exclusively built for DBI
10: product and is NOT included in mainline code of ATG Resource Manager.
11:
90: BEGIN
91: SELECT owner, table_owner, tablespace_name
92: INTO l_index_owner, l_table_owner, l_index_tblspace
93: FROM ALL_INDEXES
94: WHERE TABLE_NAME = 'JTF_RS_DBI_DENORM_RES_GROUPS'
95: AND index_name = 'JTF_RS_DBI_DENORM_RES_GRPS_N1'
96: AND table_owner= l_jtfu;
97:
98: l_index_exists := 'Y' ;
142:
143: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_MGR_GROUPS',
144: percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism ,granularity=>'GLOBAL',cascade=>TRUE);
145:
146: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS' ;
147:
148: -- Can drop index only if it exists
149: IF (l_index_exists <> 'N') THEN
150: EXECUTE IMMEDIATE 'DROP INDEX '||l_index_owner||'.JTF_RS_DBI_DENORM_RES_GRPS_N1';
154:
155: l_stage := 'Stage = Z-TOP-MANAGER-GROUPS : ' ;
156:
157: -- INSERT star groups (Top manager groups)
158: INSERT /*+ APPEND PARALLEL(jtf_rs_dbi_denorm_res_groups) NOLOGGING */
159: INTO jtf_rs_dbi_denorm_res_groups
160: (VALUE, id , current_id ,
161: parent_id , denorm_level , start_date ,
162: end_date , user_id , resource_id,
155: l_stage := 'Stage = Z-TOP-MANAGER-GROUPS : ' ;
156:
157: -- INSERT star groups (Top manager groups)
158: INSERT /*+ APPEND PARALLEL(jtf_rs_dbi_denorm_res_groups) NOLOGGING */
159: INTO jtf_rs_dbi_denorm_res_groups
160: (VALUE, id , current_id ,
161: parent_id , denorm_level , start_date ,
162: end_date , user_id , resource_id,
163: debug_column, denorm_id , mem_flag,
165: SELECT /*+ use_hash(g x) parallel(g) parallel(x) */
166: ' * ' VALUE, x.group_id id , TO_NUMBER(-9999) current_id,
167: x.group_id parent_id, TO_NUMBER(0) denorm_level, g.start_date_active start_date,
168: g.end_date_active end_date, x.user_id user_id, x.resource_id resource_id,
169: 'Z-TOP-MANAGER-GROUPS' debug_column, jtf_rs_dbi_denorm_res_groups_s.NEXTVAL denorm_id, 'N' mem_flag,
170: 'A' mem_status, l_sysdate creation_date, l_user_id created_by
171: , 'Y' active_grp_rel_only
172: FROM (
173: SELECT /*+ use_hash(res mgr) parallel(res) parallel(mgr) */
187:
188: l_stage := 'Stage = 0-FIRST-TIME-GROUPS : ' ;
189:
190: --first time login groups
191: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
192: (VALUE,
193: id ,
194: current_id ,
195: parent_id ,
211: d1.group_id id,
212: TO_NUMBER(-1111) current_id, d1.actual_parent_id parent_id,
213: d1.denorm_level , d1.start_date_active start_date,
214: d1.end_date_active end_date, n1.user_id ,n1.resource_id ,
215: '0-FIRST-TIME-GROUPS' debug_column, jtf_rs_dbi_denorm_res_groups_s.nextval
216: ,'N' mem_flag , 'A' mem_status
217: ,l_sysdate, l_user_id
218: ,DECODE(d1.active_flag,'Y','Y','N')
219: FROM jtf_rs_groups_denorm d1 , jtf_rs_dbi_mgr_groups n1
226:
227: l_stage := 'Stage = A-PARENT : ' ;
228:
229: --parent
230: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
231: (VALUE,
232: id ,
233: current_id ,
234: parent_id ,
249: d1.group_id id, d2.group_id current_id,
250: d1.actual_parent_id parent_id, d1.denorm_level,
251: d1.start_date_active start_date, d1.end_date_active end_date,
252: n1.user_id, n1.resource_id, 'A-PARENT' debug_column,
253: jtf_rs_dbi_denorm_res_groups_s.nextval
254: ,'N' mem_flag , 'A' mem_status
255: ,l_sysdate, l_user_id
256: ,DECODE(d1.active_flag,'Y','Y','N')
257: FROM jtf_rs_groups_denorm d1 , jtf_rs_groups_denorm d2,
268:
269: l_stage := 'Stage = C-SELF : ' ;
270:
271: --self
272: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
273: (VALUE,
274: id ,
275: current_id ,
276: parent_id ,
291: , d1.group_id id, d1.group_id current_id,
292: d1.actual_parent_id parent_id, d1.denorm_level,
293: d1.start_date_active start_date, d1.end_date_active end_date,
294: n1.user_id, n1.resource_id, 'C-SELF' debug_column,
295: jtf_rs_dbi_denorm_res_groups_s.nextval
296: ,'N' mem_flag , 'A' mem_status
297: ,l_sysdate, l_user_id
298: ,DECODE(d1.active_flag,'Y','Y','N')
299: FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
305:
306: l_stage := 'Stage = D-CHILD : ' ;
307:
308: --child
309: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
310: (VALUE,
311: id ,
312: current_id ,
313: parent_id ,
328: ,d1.group_id id,
329: d1.actual_parent_id current_id, d1.actual_parent_id parent_id,
330: d1.denorm_level, d1.start_date_active start_date,
331: d1.end_date_active end_date, n1.user_id, n1.resource_id, 'D-CHILD' debug_column
332: , jtf_rs_dbi_denorm_res_groups_s.nextval
333: ,'N' mem_flag , 'A' mem_status
334: ,l_sysdate, l_user_id
335: ,DECODE(d1.active_flag,'Y','Y','N')
336: FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
351:
352: -- l_stage := 'Stage = B-PEER : ' ;
353:
354: -- peer groups
355: -- INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
356: /* (VALUE,
357: id ,
358: current_id ,
359: parent_id ,
373: /* DECODE (d1.active_flag ,'Y', ' -- ', ' -- [ ') VALUE,
374: d1.group_id id, drg1.current_id current_id,
375: d1.parent_group_id parent_id, d1.denorm_level denorm_level,
376: d1.start_date_active start_date, d1.end_date_active end_date,
377: drg1.user_id, drg1.resource_id, 'B-PEER' DEBUG_COLUMN , jtf_rs_dbi_denorm_res_groups_s.nextval
378: ,'N' mem_flag , 'A' mem_status
379: ,l_sysdate, l_user_id
380: ,DECODE(d1.active_flag,'Y','Y','N') active_grp_rel_only
381: FROM jtf_rs_groups_denorm d1,
378: ,'N' mem_flag , 'A' mem_status
379: ,l_sysdate, l_user_id
380: ,DECODE(d1.active_flag,'Y','Y','N') active_grp_rel_only
381: FROM jtf_rs_groups_denorm d1,
382: jtf_rs_dbi_denorm_res_groups drg1
383: WHERE drg1.current_id = drg1.id
384: AND drg1.denorm_level > 0
385: AND drg1.parent_id = d1.parent_group_id
386: AND drg1.current_id <> d1.group_id
406: -- on 01/16/2004, Now manager and member roles will be displayed
407: -- admin roles will be excluded.
408:
409: EXECUTE IMMEDIATE
410: 'INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
411: (VALUE,
412: id_for_grp_mem ,
413: current_id ,
414: parent_id ,
423: active_grp_rel_only )
424: SELECT Decode(x.mem_status,''I'',''----[ '',''----'')value,
425: x.resource_id||''.''||x.group_id id_for_grp_mem, x.group_id current_id,
426: x.group_id parent_id, to_number(100) denorm_level,
427: ''E-SELF-GROUP-MEMBERS'' debug_column , jtf_rs_dbi_denorm_res_groups_s.NEXTVAL denorm_id
428: ,x.resource_id grp_mem_resource_id, ''Y'' mem_flag, x.mem_status
429: ,:l_sysdate, :l_user_id, ''Y'' active_grp_rel_only
430: FROM (
431: SELECT /*+ use_hash(gm1 rrl1 rol1) PARALLEL(gm1) PARALLEL(rrl1) PARALLEL(rol1) */
473: COMMIT;
474:
475: EXECUTE IMMEDIATE 'CREATE INDEX '||l_index_owner
476: ||'.JTF_RS_DBI_DENORM_RES_GRPS_N1 ON '
477: ||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS (current_id, user_id, id) '
478: ||' TABLESPACE '||l_index_tblspace
479: ||' NOLOGGING PARALLEL (DEGREE '||bis_common_parameters.get_degree_of_parallelism||' ) ';
480:
481: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_DENORM_RES_GROUPS',
477: ||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS (current_id, user_id, id) '
478: ||' TABLESPACE '||l_index_tblspace
479: ||' NOLOGGING PARALLEL (DEGREE '||bis_common_parameters.get_degree_of_parallelism||' ) ';
480:
481: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_DENORM_RES_GROUPS',
482: percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism, granularity=>'GLOBAL',cascade=>TRUE);
483:
484: ---------Commented out this code on 18th Feb 04 by NSINGHAI ------------------
485: -- we do not need first time login
495: -- Here, no data inserted for Id column. In view, id_for_grp_mem column will be used as
496: -- id. This is just for group members rows.
497: -- Making it dynamic SQL because in 8i 'CASE' function doesn't work in PLSQL
498: -- EXECUTE IMMEDIATE
499: -- 'INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
500: /* (VALUE,
501: id_for_grp_mem ,
502: current_id ,
503: parent_id ,
513: created_by )
514: SELECT Decode(x.mem_status,''I'',''--[ '',''--'')value,
515: x.grp_mem_resource_id||''.''||x.group_id id_for_grp_mem, to_number(-1111) current_id,
516: x.group_id parent_id, to_number(1) denorm_level, x.user_id, x.resource_id,
517: ''F-FIRST-TIME-GROUP-MEMBERS'' debug_column , jtf_rs_dbi_denorm_res_groups_s.NEXTVAL denorm_id
518: ,x.grp_mem_resource_id, ''Y'' mem_flag, x.mem_status
519: ,:l_sysdate, :l_user_id
520: FROM (
521: SELECT /*+ use_hash(dbi mem rrl rol) PARALLEL(dbi) PARALLEL(mem) PARALLEL(rrl)
525: CASE WHEN :l_bis_date BETWEEN rrl.start_date_active AND nvl(rrl.end_date_active, :l_bis_date + 1)
526: THEN ''A'' ELSE ''I'' END AS mem_status
527: FROM jtf_rs_group_members mem,
528: jtf_rs_role_relations rrl,
529: jtf_rs_dbi_denorm_res_groups dbi
530: ,jtf_rs_roles_b rol
531: WHERE mem.group_member_id = rrl.role_resource_id
532: AND mem.delete_flag = ''N''
533: AND rrl.role_resource_type = ''RS_GROUP_MEMBER''
542: ' USING l_sysdate, l_user_id, l_bis_date, l_bis_date;
543:
544: COMMIT;
545:
546: fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_DENORM_RES_GROUPS',
547: percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism, granularity=>'GLOBAL',cascade=>TRUE);
548:
549: */
550:
549: */
550:
551: SELECT COUNT(*)
552: INTO l_insert_count
553: FROM jtf_rs_dbi_denorm_res_groups;
554:
555: l_stage := 'Stage = WRAPUP : ' ;
556:
557: BIS_COLLECTION_UTILITIES.wrapup(