1 PACKAGE BODY jtf_rs_dbi_conc_pub AS
2 /* $Header: jtfrsdbb.pls 120.2 2011/08/15 22:07:41 nsinghai noship $ */
3
4 /****************************************************************************
5 This is a concurrent program to populate the data in JTF_RS_DBI_MGR_GROUPS
6 and JTF_RS_DBI_DENORM_RES_GROUPS
7 table so that it can be accessed via view JTF_RS_DBI_RES_GRP_VL for Sales
8 Group Hierarchy in DBI product. This program is exclusively built for DBI
9 product and is NOT included in mainline code of ATG Resource Manager.
10
11 CREATED BY nsinghai 01/16/2003
12 MODIFIED BY nsinghai 02/28/2003 -- Added BIS_COLLECTION_UTILITIES
13 -- call for setup, wrapup, debug
14 -- so that parallel session enabling
15 -- can be done by setup program
16 -- itself. Done after instructions
17 -- from performance team.
18 nsinghai 10/03/2003 -- Commented out active_parent_id
19 -- update in groups denorm (Bug 3162692)
20 nsinghai 10/03/2003 -- Added function get_sg_id for
21 -- fetching group id for first time login
22 -- sales pages (usage = 'SALES')
23 -- ER Bug # 3155246
24 nsinghai 11/18/2003 -- modified for ER 3263259 -to just show
25 -- the persons with member roles. Manager
26 -- and admin roles will be excluded.
27 nsinghai 02/11/2004 -- Perf. Bug # 3423173. Removed peer groups
28 -- from the insert code. It was the costliest
29 -- query. Doing online query for peer in view
30 -- at runtime.
31 -- Removed first time login group members. This
32 -- functionality is not being used in product
33 -- pages.
34 nsinghai 06/07/2004 -- Bug 3651322, 8i compatibility issue for 11.5.10
35 copied code from jtfrsdab.pls (115.4)
36 (8i version code). This will go in ver 115.18
37 In version 115.19 will revert back the code to
38 existing 9i version (same as 115.17).
39 nsinghai 06/07/2004 -- Reverted back to 9i version. Same as 115.17
40 for DBI 7.0.
41 Moved the variable assignment from declaration
42 to body of the code.
43 nsinghai 07/13/2004 -- ER 3761218 - Field Service District DBI conc prog
44 Created new procedure populate_main and populate_fld_srv_district
45 Moved main processing to populate_main procedure. Similarly
46 created new function for taking usage as input parameter.
47 nsinghai 09/03/2004 ER 3855071 - Pass back NULL instead of '-1111' if some exception
48 occures in get_sg_id, get_fsg_id and get_first_login_group_id
49 functions.
50 nsinghai 08/15/2011 Bug 12864430 - removed hard coding of JTF schema
51 name as per release teams mandate
52 ***************************************************************************/
53
54 /****************************************************************************
55 This is a concurrent program to populate the data that can be accessed via view
56 JTF_RS_DBI_RES_GRP_VL for Sales Group Hierarchy (usage : SALES) in DBI
57 product.
58
59 This program is exclusively built for DBI product and is NOT included in
60 mainline code of ATG Resource Manager.
61
62 Created By nsinghai 16-Jan-2003
63 ***************************************************************************/
64
65 PROCEDURE populate_res_grp
66 (ERRBUF OUT NOCOPY VARCHAR2,
67 RETCODE OUT NOCOPY VARCHAR2)
68 IS
69 BEGIN
70 retcode := 0;
71
72 JTF_RS_DBI_CONC_PUB.POPULATE_MAIN (P_USAGE => 'SALES',
73 P_ERRBUF => ERRBUF,
74 P_RETCODE => RETCODE
75 );
76 EXCEPTION
77 WHEN OTHERS THEN
78 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
79 retcode := '2'; -- Error
80 errbuf := sqlerrm;
81 BIS_COLLECTION_UTILITIES.Debug('Error in Update Sales Group Hierarchy:'||errbuf);
82 END populate_res_grp;
83
84 /****************************************************************************
85 This is concurrent program to populate the data that can be accessed via view
86 JTF_RS_DBI_RES_GRP_VL for usage 'FLD_SRV_DISTRICT' (Field Service District
87 Hierarchy) in DBI product.
88
89 This program is exclusively built for DBI product and is NOT included in
90 mainline code of ATG Resource Manager.
91
92 Created By nsinghai 01-JUL-2004
93 ***************************************************************************/
94
95 PROCEDURE populate_fld_srv_district
96 (ERRBUF OUT NOCOPY VARCHAR2,
97 RETCODE OUT NOCOPY VARCHAR2)
98 IS
99 BEGIN
100 retcode := 0; -- success
101
102 JTF_RS_DBI_CONC_PUB.POPULATE_MAIN (P_USAGE => 'FLD_SRV_DISTRICT',
103 P_ERRBUF => ERRBUF,
104 P_RETCODE => RETCODE
105 );
106 EXCEPTION
107 WHEN OTHERS THEN
108 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
109 retcode := '2'; -- Error
110 errbuf := sqlerrm;
111 BIS_COLLECTION_UTILITIES.Debug('Error in Update Field Service District Hierarchy:'||errbuf);
112 END populate_fld_srv_district;
113
114 /****************************************************************************
115 This is main procedure to populate the data in JTF_RS_DBI_MGR_GROUPS
116 and JTF_RS_DBI_DENORM_RES_GROUPS table so that it can be accessed via view
117 JTF_RS_DBI_RES_GRP_VL for usage 'SALES' (Sales Group Hierarchy) and
118 'FLD_SRV_DISTRICT' (Field Service District Hierarchy) in DBI product.
119
120 This program is exclusively built for DBI product and is NOT included in
121 mainline code of ATG Resource Manager.
122
123 Created By nsinghai 01-JUL-2004
124 ***************************************************************************/
125
126 PROCEDURE populate_main
127 (P_USAGE IN VARCHAR2,
128 P_ERRBUF OUT NOCOPY VARCHAR2,
129 P_RETCODE OUT NOCOPY VARCHAR2)
130 IS
131
132 CURSOR c_get_bis_date IS
133 SELECT current_date_id
134 FROM bis_system_date;
135
136 CURSOR c_product_info IS
137 SELECT i.tablespace, i.index_tablespace, u.oracle_username
138 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
139 WHERE a.application_short_name = 'JTF'
140 AND a.application_id = i.application_id
141 AND u.oracle_id = i.oracle_id;
142
143 l_temp_bis_date DATE;
144 l_bis_date DATE;
145 l_index_owner VARCHAR2(240) ;
146 l_table_owner VARCHAR2(240) ;
147 l_index_tblspace VARCHAR2(240) ;
148 l_insert_count NUMBER ;
149 l_index_exists VARCHAR2(10) ;
150 l_sysdate DATE ;
151 l_user_id NUMBER ;
152
153 l_jtfu VARCHAR2(240);
154 l_jtfx VARCHAR2(240);
155 l_jtft VARCHAR2(240);
156
157 l_usage VARCHAR2(100);
158 l_partition VARCHAR2(100);
159
160 BEGIN
161 -- EXECUTE IMMEDIATE 'ALTER SESSION SET sql_trace=TRUE';
162
163 -- Bug 12864430 (removed schema harcoded values from initialization. These values will be fetched from database)
164 -- l_index_owner := 'JTF';
165 -- l_table_owner := 'JTF';
166 l_insert_count := 0;
167 l_index_exists := 'Y';
168 l_sysdate := sysdate;
169 l_user_id := fnd_global.user_id;
170
171 l_usage := p_usage;
172 l_partition := 'USAGE_'||p_usage||'_P1' ;
173
174 p_retcode := '0' ;
175
176 -- Call BIS_COLLECTION_UTILITIES to enable parallel session and other logging utilities
177 IF(BIS_COLLECTION_UTILITIES.Setup(
178 p_object_name => 'JTF_RS_DBI_RES_GRP_VL') = false)
179 THEN
180 p_errbuf := FND_MESSAGE.Get;
181 p_retcode := '-1';
182 RAISE_APPLICATION_ERROR(-20000,p_errbuf);
183 END IF;
184
185 -- fetch bis date
186 OPEN c_get_bis_date;
187 FETCH c_get_bis_date INTO l_temp_bis_date;
188 CLOSE c_get_bis_date;
189
190 l_bis_date := TRUNC(NVL(l_temp_bis_date, SYSDATE));
191
192 --fetch user name for JTF product
193 OPEN c_product_info;
194 FETCH c_product_info INTO l_jtft,l_jtfx,l_jtfu;
195 CLOSE c_product_info;
196
197 -- keep index information so that it is easy to create them back
198 -- Check owner name for the tables and indexes.
199 BEGIN
200 SELECT owner, table_owner, tablespace_name
201 INTO l_index_owner, l_table_owner, l_index_tblspace
202 FROM ALL_INDEXES
203 WHERE TABLE_NAME = 'JTF_RS_DBI_DENORM_RES_GROUPS'
204 AND index_name = 'JTF_RS_DBI_DENORM_RES_GRPS_N1'
205 AND table_owner= l_jtfu;
206
207 l_index_exists := 'Y' ;
208
209 EXCEPTION WHEN OTHERS THEN
210
211 l_index_exists := 'N' ;
212
213 -- Check some ther index which will definitly exist
214 SELECT owner, table_owner, tablespace_name
215 INTO l_index_owner, l_table_owner, l_index_tblspace
216 FROM ALL_INDEXES
217 WHERE TABLE_NAME = 'JTF_RS_GROUPS_DENORM'
218 AND index_name = 'JTF_RS_GROUPS_DENORM_U1'
219 AND table_owner= l_jtfu;
220
221 END;
222
223 IF (l_index_tblspace IS NULL) THEN
224 l_index_tblspace := l_jtfx;
225 END IF;
226
227 IF (l_index_owner IS NULL) THEN
228 l_index_owner := l_jtfu;
229 END IF;
230
231 IF (l_table_owner IS NULL) THEN
232 l_table_owner := l_jtfu;
233 END IF;
234
235 BIS_COLLECTION_UTILITIES.debug('Index Information: l_index_owner='||l_index_owner
236 ||': l_table_owner='||l_table_owner||': l_index_tblspace='||l_index_tblspace
237 ||': Degree of parallelism='||bis_common_parameters.get_degree_of_parallelism
238 ||': Partition= '||l_partition);
239
240 -- Truncate Table Partitions
241 EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS TRUNCATE PARTITION '||l_partition ;
242 EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS TRUNCATE PARTITION '||l_partition;
243
244 -- Make indexes unusable
245 EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS MODIFY PARTITION '||l_partition||' UNUSABLE LOCAL INDEXES ';
246 EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS MODIFY PARTITION '||l_partition||' UNUSABLE LOCAL INDEXES ';
247
248 -- change session parameter so that data can be inserted in partition containing unusable index
249 EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE ' ;
250
251 COMMIT;
252
253 -- MULTI-TABLE Insert
254 -- INSERT top manager groups in intermediate table
255 -- INSERT star groups (Top manager groups)
256 -- INSERT top manager groups (first time login manager groups) in main table
257 INSERT ALL
258 INTO /*+ APPEND PARALLEL(jtf_rs_dbi_mgr_groups) NOLOGGING */ jtf_rs_dbi_mgr_groups
259 (dbi_mgr_id, resource_id, user_id, group_id,
260 creation_date, created_by
261 ,last_update_date, last_updated_by, usage )
262 VALUES (jtf_rs_dbi_mgr_groups_s.nextval, resource_id, user_id, group_id
263 , l_sysdate, l_user_id
264 , l_sysdate, l_user_id, usage)
265 INTO /*+ APPEND PARALLEL(jtf_rs_dbi_denorm_res_groups) NOLOGGING */ jtf_rs_dbi_denorm_res_groups
266 (VALUE, id , current_id ,
267 parent_id , denorm_level ,
268 start_date , end_date ,
269 user_id ,
270 resource_id,
271 debug_column, denorm_id , mem_flag,
272 mem_status , creation_date, created_by
273 ,active_grp_rel_only
274 ,last_update_date, last_updated_by, usage )
275 VALUES (' * ', group_id, TO_NUMBER(-9999),
276 group_id, TO_NUMBER(0),
277 start_date_active, end_date_active,
278 user_id, resource_id,
279 'Z-TOP-MANAGER-GROUPS', jtf_rs_dbi_denorm_res_groups_s.NEXTVAL, 'N',
280 'A', l_sysdate, l_user_id ,'Y'
281 , l_sysdate, l_user_id, usage)
282 INTO /*+ APPEND PARALLEL(jtf_rs_dbi_denorm_res_groups) NOLOGGING */ jtf_rs_dbi_denorm_res_groups
283 (VALUE,
284 id ,
285 current_id ,
286 parent_id ,
287 denorm_level ,
288 start_date ,
289 end_date ,
290 user_id ,
291 resource_id,
292 debug_column,
293 denorm_id ,
294 mem_flag,
295 mem_status,
296 creation_date,
297 created_by,
298 active_grp_rel_only
299 ,last_update_date, last_updated_by, usage )
300 VALUES ( ' ', group_id , TO_NUMBER(-1111) , group_id ,
301 TO_NUMBER(0) , start_date_active , end_date_active ,
302 user_id , resource_id ,
303 '0-FIRST-TIME-PARENT-GROUPS' , jtf_rs_dbi_denorm_res_groups_s.nextval
304 ,'N' , 'A' ,l_sysdate, l_user_id, 'Y'
305 , l_sysdate, l_user_id, usage)
306 SELECT /*+ use_hash(g x usg) parallel(g) parallel(x) parallel(usg)*/
307 x.resource_id, x.user_id, x.group_id
308 , g.start_date_active, g.end_date_active
309 , usg.usage
310 FROM (
311 SELECT /*+ use_hash(res mgr) parallel(res) parallel(mgr) */
312 DISTINCT mgr.resource_id, res.user_id, mgr.group_id
313 FROM jtf_rs_rep_managers mgr, jtf_rs_resource_extns res
314 WHERE mgr.hierarchy_type IN ('MGR_TO_MGR','ADMIN_TO_ADMIN')
315 AND mgr.resource_id = mgr.parent_resource_id
316 AND l_bis_date BETWEEN mgr.start_date_active
317 AND NVL(mgr.end_date_active,to_date('12/31/4712','MM/DD/RRRR'))
318 AND mgr.resource_id = res.resource_id
319 AND res.user_id IS NOT NULL
320 ) x
321 , jtf_rs_groups_b g
322 , jtf_rs_group_usages usg
323 WHERE x.group_id = g.group_id
324 AND x.group_id = usg.group_id
325 AND usg.usage = l_usage
326 ;
327
328 l_insert_count := l_insert_count + SQL%ROWCOUNT ;
329
330 COMMIT;
331
332 EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS '||
333 ' MODIFY PARTITION '||l_partition||' REBUILD UNUSABLE LOCAL INDEXES ';
334
335 fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_MGR_GROUPS',
336 percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism ,granularity=>'ALL',cascade=>TRUE);
337
338 -- first time login child groups
339 INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
340 (VALUE,
341 id ,
342 current_id ,
343 parent_id ,
344 denorm_level ,
345 start_date ,
346 end_date ,
347 user_id ,
348 resource_id,
349 debug_column,
350 denorm_id ,
351 mem_flag,
352 mem_status,
353 creation_date,
354 created_by,
355 active_grp_rel_only,
356 last_update_date,
357 last_updated_by,
358 usage )
359 SELECT /*+ use_hash(d1 n1 usg) PARALLEL(d1) PARALLEL(n1) PARALLEL(usg)*/
360 DECODE(d1.active_flag,'Y','-- ','-- [ ') VALUE
361 , d1.group_id id,
362 TO_NUMBER(-1111) current_id, d1.actual_parent_id parent_id,
363 d1.denorm_level , d1.start_date_active start_date,
364 d1.end_date_active end_date, n1.user_id ,n1.resource_id ,
365 '0-FIRST-TIME-CHILD-GROUPS' debug_column, jtf_rs_dbi_denorm_res_groups_s.nextval
366 ,'N' mem_flag , 'A' mem_status
367 ,l_sysdate, l_user_id
368 ,DECODE(d1.active_flag,'Y','Y','N')
369 ,l_sysdate, l_user_id, usg.usage
370 FROM jtf_rs_groups_denorm d1 , jtf_rs_dbi_mgr_groups n1
371 , jtf_rs_group_usages usg
372 WHERE n1.group_id = d1.actual_parent_id
373 AND d1.denorm_level = 1
374 AND d1.latest_relationship_flag = 'Y'
375 AND d1.group_id = usg.group_id
376 AND usg.usage = l_usage
377 AND n1.usage = l_usage
378 ;
379
380 l_insert_count := l_insert_count + SQL%ROWCOUNT ;
381
382 COMMIT;
383
384 --parent
385 INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
386 (VALUE,
387 id ,
388 current_id ,
389 parent_id ,
390 denorm_level ,
391 start_date ,
392 end_date ,
393 user_id ,
394 resource_id,
395 debug_column,
396 denorm_id ,
397 mem_flag,
398 mem_status ,
399 creation_date,
400 created_by,
401 active_grp_rel_only,
402 last_update_date,
403 last_updated_by,
404 usage )
405 SELECT /*+ use_hash(d1 d2 n1 usg) PARALLEL(d1) PARALLEL(d2) PARALLEL(n1) PARALLEL(usg) */
406 DECODE (d1.active_flag,'Y',' ',' [ ') VALUE
407 , d1.group_id id, d2.group_id current_id,
408 d1.actual_parent_id parent_id, d1.denorm_level,
409 d1.start_date_active start_date, d1.end_date_active end_date,
410 n1.user_id, n1.resource_id, 'A-PARENT' debug_column,
411 jtf_rs_dbi_denorm_res_groups_s.nextval
412 ,'N' mem_flag , 'A' mem_status
413 ,l_sysdate, l_user_id
414 ,DECODE(d1.active_flag,'Y','Y','N')
415 ,l_sysdate, l_user_id, usg.usage
416 FROM jtf_rs_groups_denorm d1 , jtf_rs_groups_denorm d2,
417 jtf_rs_dbi_mgr_groups n1
418 ,jtf_rs_group_usages usg
419 WHERE n1.group_id = d1.parent_group_id
420 AND d1.group_id = d2.actual_parent_id
421 AND n1.group_id = d2.parent_group_id
422 AND d1.group_id <> d2.group_id
423 AND d1.latest_relationship_flag = 'Y'
424 AND d2.latest_relationship_flag = 'Y'
425 AND d1.group_id = usg.group_id
426 AND usg.usage = l_usage
427 AND n1.usage = l_usage
428 ;
429
430 l_insert_count := l_insert_count + SQL%ROWCOUNT ;
431
432 COMMIT;
433
434 --self
435 INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
436 (VALUE,
437 id ,
438 current_id ,
439 parent_id ,
440 denorm_level ,
441 start_date ,
442 end_date ,
443 user_id ,
444 resource_id,
445 debug_column,
446 denorm_id ,
447 mem_flag,
448 mem_status,
449 creation_date,
450 created_by,
451 active_grp_rel_only,
452 last_update_date,
453 last_updated_by,
454 usage )
455 SELECT /*+ use_hash(d1 n1 usg) PARALLEL(d1) PARALLEL(n1) PARALLEL(usg) */
456 DECODE(d1.active_flag,'Y','-- ','-- [ ') VALUE
457 , d1.group_id id, d1.group_id current_id,
458 d1.actual_parent_id parent_id, d1.denorm_level,
459 d1.start_date_active start_date, d1.end_date_active end_date,
460 n1.user_id, n1.resource_id, 'C-SELF' debug_column,
461 jtf_rs_dbi_denorm_res_groups_s.nextval
462 ,'N' mem_flag , 'A' mem_status
463 ,l_sysdate, l_user_id
464 ,DECODE(d1.active_flag,'Y','Y','N')
465 ,l_sysdate, l_user_id, usg.usage
466 FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
467 ,jtf_rs_group_usages usg
468 WHERE n1.group_id = d1.parent_group_id
469 AND d1.latest_relationship_flag = 'Y'
470 AND d1.group_id = usg.group_id
471 AND usg.usage = l_usage
472 AND n1.usage = l_usage
473 ;
474
475 l_insert_count := l_insert_count + SQL%ROWCOUNT ;
476
477 COMMIT;
478
479 --child
480 INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
481 (VALUE,
482 id ,
483 current_id ,
484 parent_id ,
485 denorm_level ,
486 start_date ,
487 end_date ,
488 user_id ,
489 resource_id,
490 debug_column,
491 denorm_id ,
492 mem_flag,
493 mem_status ,
494 creation_date,
495 created_by,
496 active_grp_rel_only,
497 last_update_date,
498 last_updated_by,
499 usage )
500 SELECT /*+ use_hash(d1 n1 usg) PARALLEL(d1) PARALLEL(n1) PARALLEL(usg) */
501 DECODE (d1.active_flag, 'Y','---- ','---- [ ') VALUE
502 ,d1.group_id id,
503 d1.actual_parent_id current_id, d1.actual_parent_id parent_id,
504 d1.denorm_level, d1.start_date_active start_date,
505 d1.end_date_active end_date, n1.user_id, n1.resource_id, 'D-CHILD' debug_column
506 , jtf_rs_dbi_denorm_res_groups_s.nextval
507 ,'N' mem_flag , 'A' mem_status
508 ,l_sysdate, l_user_id
509 ,DECODE(d1.active_flag,'Y','Y','N')
510 ,l_sysdate, l_user_id, usg.usage
511 FROM jtf_rs_groups_denorm d1, jtf_rs_dbi_mgr_groups n1
512 ,jtf_rs_group_usages usg
513 WHERE n1.group_id = d1.parent_group_id
514 AND d1.denorm_level > 0
515 AND d1.latest_relationship_flag = 'Y'
516 AND d1.group_id = usg.group_id
517 AND usg.usage = l_usage
518 AND n1.usage = l_usage
519 ;
520
521 l_insert_count := l_insert_count + SQL%ROWCOUNT ;
522
523 COMMIT;
524
525
526 -- group members -- not for specific user -- just preprocessed records
527 -- so that view performs faster and takes less sharable memory. no security applied
528 -- not for 1st time login. First time login group members not required since that
529 -- part of LOV is never executed.
530 -- For group member rows, no data is inserted in Id, user_id and resource_id columns
531 -- since they are not for specific user.
532 -- modified for ER 3263259 on 11/18/2003 - to just show the persons with member roles
533 -- on 01/16/2004, above ER is reverted. Now manager and member roles will be displayed
534 -- admin roles will be excluded.
535 INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
536 (VALUE,
537 id_for_grp_mem ,
538 current_id ,
539 parent_id ,
540 denorm_level ,
541 debug_column,
542 denorm_id,
543 grp_mem_resource_id,
544 mem_flag,
545 mem_status,
546 creation_date,
547 created_by,
548 active_grp_rel_only,
549 last_update_date,
550 last_updated_by,
551 usage )
552 SELECT Decode(x.mem_status,'I','----[ ','----')value,
553 x.resource_id||'.'||x.group_id id_for_grp_mem, x.group_id current_id,
554 x.group_id parent_id, to_number(100) denorm_level,
555 'E-SELF-GROUP-MEMBERS' debug_column , jtf_rs_dbi_denorm_res_groups_s.NEXTVAL denorm_id
556 ,x.resource_id grp_mem_resource_id, 'Y' mem_flag, x.mem_status
557 ,l_sysdate, l_user_id
558 ,'Y' active_grp_rel_only
559 ,l_sysdate, l_user_id, x.usage
560 FROM (
561 -- changed the select statement in order to fetch the member as well as manager role
562 -- changed the select statement to get only 1 distinct row. If active role is available
563 -- do not fetch the inactive role. If only inactive role is available, show that one.
564 -- changed on 01/16/2004 for dbi 7.0
565 SELECT /*+ use_hash(gm1 rrl1 rol1 usg1) PARALLEL(gm1) PARALLEL(rrl1) PARALLEL(rol1) PARALLEL(usg1)*/
566 DISTINCT gm1.resource_id, gm1.group_id
567 , 'A' mem_status, usg1.usage
568 FROM jtf_rs_group_members gm1, jtf_rs_role_relations rrl1, jtf_rs_roles_b rol1
569 ,jtf_rs_group_usages usg1
570 WHERE gm1.group_member_id = rrl1.role_resource_id
571 AND gm1.delete_flag = 'N'
572 AND rrl1.role_resource_type = 'RS_GROUP_MEMBER'
573 AND rrl1.delete_flag = 'N'
574 AND rrl1.role_id = rol1.role_id
575 AND 'Y' IN (rol1.member_flag, rol1.manager_flag)
576 AND rrl1.active_flag = 'Y'
577 AND gm1.group_id = usg1.group_id
578 AND usg1.usage = l_usage
579 UNION ALL
580 SELECT /*+ use_hash(gm2 rrl2 rol2 usg2) PARALLEL(gm2) PARALLEL(rrl2) PARALLEL(rol2) PARALLEL(usg2) */
581 DISTINCT gm2.resource_id, gm2.group_id
582 , 'I' mem_status, usg2.usage
583 FROM jtf_rs_group_members gm2, jtf_rs_role_relations rrl2, jtf_rs_roles_b rol2
584 ,jtf_rs_group_usages usg2
585 WHERE gm2.group_member_id = rrl2.role_resource_id
586 AND gm2.delete_flag = 'N'
587 AND rrl2.role_resource_type = 'RS_GROUP_MEMBER'
588 AND rrl2.delete_flag = 'N'
589 AND rrl2.role_id = rol2.role_id
590 AND 'Y' IN (rol2.member_flag, rol2.manager_flag)
591 AND rrl2.active_flag IS NULL
592 AND gm2.group_id = usg2.group_id
593 AND usg2.usage = l_usage
594 AND NOT EXISTS ( -- to check if active role doesn't exist
595 SELECT /*+ use_hash(gm3 rrl3 rol3 usg3) PARALLEL(gm3) PARALLEL(rrl3) PARALLEL(rol3) PARALLEL(usg3)*/
596 '1'
597 FROM jtf_rs_group_members gm3, jtf_rs_role_relations rrl3
598 , jtf_rs_roles_b rol3
599 , jtf_rs_group_usages usg3
600 WHERE gm3.group_member_id = rrl3.role_resource_id
601 AND gm3.delete_flag = 'N'
602 AND rrl3.role_resource_type = 'RS_GROUP_MEMBER'
603 AND rrl3.delete_flag = 'N'
604 AND rrl3.role_id = rol3.role_id
605 AND 'Y' IN (rol3.member_flag, rol3.manager_flag)
606 AND rrl3.active_flag = 'Y'
607 AND gm3.resource_id = gm2.resource_id
608 AND gm3.group_id = gm2.group_id
609 AND gm3.group_id = usg3.group_id
610 AND usg3.usage = l_usage
611 )
612 ) x
613 ;
614
615 l_insert_count := l_insert_count + SQL%ROWCOUNT ;
616
617 COMMIT;
618
619 -- Member Login for DBI 7.1. new insert stmt created by nsinghai on 8-Oct-2004
620 INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_dbi_denorm_res_groups a
621 (VALUE,id, id_for_grp_mem , current_id , parent_id , denorm_level ,
622 START_DATE, end_date, resource_id, user_id, grp_mem_resource_id,
623 debug_column, denorm_id, mem_flag, mem_status, creation_date, created_by,
624 active_grp_rel_only, last_update_date, last_updated_by, usage )
625 SELECT ' * ' VALUE, group_id id, resource_id||'.'||group_id id_for_grp_mem,
626 TO_NUMBER(-7777) current_id, group_id parent_id, TO_NUMBER(0) denorm_level,
627 START_DATE , end_date, resource_id, user_id, resource_id grp_mem_resource_id,
628 '1-GROUP-MEMBER-LOGIN' debug_column ,jtf_rs_dbi_denorm_res_groups_s.NEXTVAL,
629 'N' mem_flag , 'A' mem_status ,l_sysdate, l_user_id, 'Y' active_grp_rel_only
630 ,l_sysdate, l_user_id, usage
631 FROM (
632 SELECT /*+ use_hash(gm rrl rol res usg) parallel(gm) parallel(rrl) parallel(rol) parallel(res) parallel(usg)*/
633 gm.group_id group_id, res.resource_id resource_id,
634 res.user_id user_id,
635 MIN(rrl.start_date_active) start_date,
636 MAX(nvl(rrl.end_date_active,TO_DATE('12/31/4712','MM/DD/RRRR'))) end_date,
637 usg.usage
638 FROM jtf_rs_group_members gm
639 ,jtf_rs_role_relations rrl
640 ,jtf_rs_roles_b rol
641 ,jtf_rs_resource_extns_vl res
642 ,jtf_rs_group_usages usg
643 WHERE gm.delete_flag = 'N'
644 AND gm.group_member_id = rrl.role_resource_id
645 AND rrl.role_resource_type = 'RS_GROUP_MEMBER'
646 AND rrl.delete_flag = 'N'
647 AND rrl.active_flag = 'Y'
648 AND rrl.role_id = rol.role_id
649 AND rol.member_flag = 'Y'
650 AND NVL(rol.admin_flag,'N') = 'N'
651 AND NVL(rol.manager_flag,'N') = 'N'
652 AND NVL(rol.active_flag,'Y') = 'Y'
653 AND gm.resource_id = res.resource_id
654 AND res.user_id IS NOT NULL
655 AND gm.group_id = usg.group_id
656 AND usg.usage = l_usage
657 GROUP BY usg.usage, gm.group_id, res.resource_id, res.user_id
658 )
659 ;
660
661 l_insert_count := l_insert_count + SQL%ROWCOUNT ;
662
663 COMMIT;
664
665 EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.JTF_RS_DBI_DENORM_RES_GROUPS '||
666 ' MODIFY PARTITION '||l_partition||' REBUILD UNUSABLE LOCAL INDEXES ';
667
668 fnd_stats.gather_table_stats(ownname => l_table_owner, tabname => 'JTF_RS_DBI_DENORM_RES_GROUPS',
669 percent=>5, degree=>bis_common_parameters.get_degree_of_parallelism, granularity=>'ALL',cascade=>TRUE);
670
671 BIS_COLLECTION_UTILITIES.wrapup(
672 p_status => TRUE ,
673 p_count => l_insert_count,
674 p_period_to => l_bis_date);
675
676 -- EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=FALSE';
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
681 p_retcode := '2'; -- Error
682 p_errbuf := sqlerrm;
683 BIS_COLLECTION_UTILITIES.Debug('Error in Update Group Hierarchy for usage:'||
684 p_usage||' : '||p_errbuf);
685
686 BIS_COLLECTION_UTILITIES.wrapup(
687 p_status => FALSE ,
688 p_message => sqlerrm,
689 p_count => l_insert_count,
690 p_period_to => l_bis_date);
691
692 -- dbms_output.put_line('Error : '||sqlcode||':'||sqlerrm);
693
694 END populate_main;
695
696 /****************************************************************************
697 This function is for providing a common method of fetching the group id
698 for first time login pages. Instead of passing '-1111' to Sales Group
699 Dimension LOV, product teams will call this function which will return
700 them a valid group id. This group id will be used by product teams to
701 query the data rather then querying data for dummy group '-1111'.
702 Internally this function will query for '-1111' and then return the first
703 record. This is for usage : 'SALES'
704
705 Created By nsinghai 03-Oct-2003
706 ***************************************************************************/
707
708 FUNCTION get_sg_id RETURN VARCHAR2 IS
709 BEGIN
710
711 RETURN get_first_login_group_id('SALES','N');
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 RETURN NULL;
716 END get_sg_id;
717
718 /****************************************************************************
719 This function is for providing a common method of fetching the group id
720 for first time login pages. Instead of passing '-1111' to Group Hierarchy
721 Dimension LOV, product teams will call this function which will return
722 them a valid group id. This group id will be used by Field Service team to
723 query the data rather then querying data for dummy group '-1111'.
724 Internally this function will query for '-1111' and then return the first
725 record. This is for Field Service Districts (Usage: 'FLD_SRV_DISTRICT').
726
727 Created By nsinghai 01-JUL-2004
728 ***************************************************************************/
729
730 FUNCTION get_fsg_id RETURN VARCHAR2 IS
731 BEGIN
732
733 RETURN get_first_login_group_id('FLD_SRV_DISTRICT','N');
734
735 EXCEPTION
736 WHEN OTHERS THEN
737 RETURN NULL;
738 END get_fsg_id;
739
740 /****************************************************************************
741 This function is for providing a common method of fetching the group id
742 for first time login pages. Instead of passing '-1111' to Sales Group
743 Dimension LOV, product teams will call this function which will return
744 them a valid group id. This group id will be used by product teams to
745 query the data rather then querying data for dummy group '-1111'.
746 Internally this function will query for '-1111' and then return the first
747 record. This is for usage : 'SALES'
748
749 "get_sg_id" returns first time login id only from managers and admin groups
750 for sales
751 "get_sg_id_all_login" returns first time login id only from managers, admin
752 and member groups for sales
753
754 Created By nsinghai 08-Oct-2004
755 ***************************************************************************/
756
757 FUNCTION get_sg_id_all_login RETURN VARCHAR2 IS
758 BEGIN
759
760 RETURN get_first_login_group_id('SALES','Y');
761
762 EXCEPTION
763 WHEN OTHERS THEN
764 RETURN NULL;
765 END get_sg_id_all_login;
766
767 /****************************************************************************
768 This function is for providing a common method of fetching the group id
769 for first time login pages. Instead of passing '-1111' to Sales Group
770 Dimension LOV, product teams will call this function which will return
771 them a valid group id. This group id will be used by product teams to
772 query the data rather then querying data for dummy group '-1111'.
773 Internally this function will query for '-1111' and then return the first
774 record. This is for usage : 'FLD_SRV_DISTRICT'
775
776 "get_fsg_id" returns first time login id only from managers and admin groups
777 for field service
778 "get_fsg_id_all_login" returns first time login id only from managers, admin
779 and member groups for field service
780
781 Created By nsinghai 08-Oct-2004
782 ***************************************************************************/
783
784 FUNCTION get_fsg_id_all_login RETURN VARCHAR2 IS
785 BEGIN
786
787 RETURN get_first_login_group_id('FLD_SRV_DISTRICT','Y');
788
789 EXCEPTION
790 WHEN OTHERS THEN
791 RETURN NULL;
792 END get_fsg_id_all_login;
793
794 /***************************************************************************
795 This function which will be called for getting first time login
796 group from other functions like get_sg_id and get_fsg_id functions.
797 ****************************************************************************/
798
799 FUNCTION get_first_login_group_id(p_usage VARCHAR2, p_include_member_groups VARCHAR2)
800 RETURN VARCHAR2 IS
801 l_sg_id VARCHAR2(100);
802 l_usage VARCHAR2(100);
803 l_include_member_groups VARCHAR2(10);
804 BEGIN
805 l_usage := p_usage;
806 l_include_member_groups := p_include_member_groups;
807
808 IF (l_include_member_groups = 'N') THEN
809 SELECT id
810 INTO l_sg_id
811 FROM (
812 SELECT id, rank() over (order by value, id nulls last) rnk
813 FROM jtf_rs_dbi_res_grp_vl
814 WHERE usage = l_usage
815 AND current_id = -1111
816 AND denorm_level = 0
817 )
818 WHERE rnk = 1;
819 END IF;
820
821 IF (l_include_member_groups = 'Y') THEN
822 SELECT id
823 INTO l_sg_id
824 FROM (
825 SELECT id, rank() over (order by current_id desc, value, id nulls last) rnk
826 FROM jtf_rs_dbi_res_grp_vl
827 WHERE usage = l_usage
828 AND current_id IN (-1111, -7777)
829 AND denorm_level = 0
830 )
831 WHERE rnk = 1;
832 END IF;
833
834 RETURN l_sg_id;
835 EXCEPTION
836 WHEN OTHERS THEN
837 RETURN NULL;
838 END get_first_login_group_id;
839
840 END jtf_rs_dbi_conc_pub ; -- end package body
[Home] [Help]