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