[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_DBI_8I_CONC_PUB
Source
1 PACKAGE BODY jtf_rs_dbi_8i_conc_pub AS
2 /* $Header: jtfrsdab.pls 115.5 2004/06/09 17:31:17 nsinghai noship $ */
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
12 CREATED BY nsinghai 10/27/2003
13 MODIFIED BY nsinghai 02/18/2004 Added DBI 7.0 (Drop 2) functionality
14 "Expired Sales Group Hierarchy Support"
15 Also added manager role to be picked up
16 for group members. ER 3378250
17 ***************************************************************************/
18
19 PROCEDURE populate_res_grp
20 (ERRBUF OUT NOCOPY VARCHAR2,
21 RETCODE OUT NOCOPY VARCHAR2)
22 IS
23
24 CURSOR c_get_bis_date IS
25 SELECT current_date_id
26 FROM bis_system_date;
27
28 CURSOR c_product_info IS
29 SELECT i.tablespace, i.index_tablespace, u.oracle_username
30 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
31 WHERE a.application_short_name = 'JTF'
32 AND a.application_id = i.application_id
33 AND u.oracle_id = i.oracle_id;
34
35 l_temp_bis_date DATE;
36 l_bis_date DATE;
37 l_index_owner VARCHAR2(30) ;
38 l_table_owner VARCHAR2(30) ;
39 l_index_tblspace VARCHAR2(45) ;
40 l_insert_count NUMBER ;
41 l_index_exists VARCHAR2(10) ;
42 l_sysdate DATE ;
43 l_user_id NUMBER ;
44 l_stage VARCHAR2(100) ;
45
46 l_jtfu varchar2(60);
47 l_jtfx varchar2(60);
48 l_jtft varchar2(60);
49
50
51 BEGIN
52 --EXECUTE IMMEDIATE 'ALTER SESSION SET sql_trace=TRUE';
53
54 -- Initialize variables
55 l_index_owner := 'JTF';
56 l_table_owner := 'JTF';
57 l_insert_count := 0;
58 l_index_exists := 'Y';
59 l_sysdate := sysdate;
60 l_user_id := fnd_global.user_id;
61 l_stage := 'Stage = START : ' ;
62
63 retcode := '0' ;
64
65 -- Call BIS_COLLECTION_UTILITIES to enable parallel session and other logging utilities
66 IF(BIS_COLLECTION_UTILITIES.Setup(
67 p_object_name => 'JTF_RS_DBI_RES_GRP_VL') = false)
68 THEN
69 errbuf := FND_MESSAGE.Get;
70 retcode := '-1';
71 RAISE_APPLICATION_ERROR(-20000,errbuf);
72 END IF;
73
74 -- fetch bis date
75 OPEN c_get_bis_date;
76 FETCH c_get_bis_date INTO l_temp_bis_date;
77 CLOSE c_get_bis_date;
78
79 --fetch user name for JTF product
80 OPEN c_product_info;
81 FETCH c_product_info INTO l_jtft,l_jtfx,l_jtfu;
82 CLOSE c_product_info;
83
84 l_bis_date := TRUNC(NVL(l_temp_bis_date, SYSDATE));
85
86 l_stage := 'Stage = INDEX INFO : ' ;
87
88 -- keep index information so that it is easy to create them back
89 -- Check owner name for the tables and indexes.
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' ;
99
100 EXCEPTION WHEN OTHERS THEN
101
102 l_index_exists := 'N' ;
103
104 -- Check some ther index which will definitly exist
105 SELECT owner, table_owner, tablespace_name
106 INTO l_index_owner, l_table_owner, l_index_tblspace
107 FROM ALL_INDEXES
108 WHERE TABLE_NAME = 'JTF_RS_GROUPS_DENORM'
109 AND index_name = 'JTF_RS_GROUPS_DENORM_U1'
110 AND table_owner= l_jtfu;
111 END;
112
113 BIS_COLLECTION_UTILITIES.debug('Index Information: l_index_owner='||l_index_owner
114 ||': l_table_owner='||l_table_owner||': l_index_tblspace='||l_index_tblspace
115 ||': Degree of parallelism='||bis_common_parameters.get_degree_of_parallelism);
116
117 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_table_owner||'.JTF_RS_DBI_MGR_GROUPS';
118
119 l_stage := 'Stage = jtf_rs_dbi_mgr_groups : ' ;
120
121 -- INSERT top manager groups in intermediate table
122 INSERT /*+ APPEND PARALLEL(jtf_rs_dbi_mgr_groups) NOLOGGING */
123 INTO jtf_rs_dbi_mgr_groups
124 (dbi_mgr_id, resource_id, user_id, group_id,
125 creation_date, created_by )
126 SELECT /*+ use_hash(x) parallel(x) */
127 jtf_rs_dbi_mgr_groups_s.nextval, x.resource_id, x.user_id, x.group_id
128 , l_sysdate, l_user_id
129 FROM (
130 SELECT /*+ use_hash(res mgr) parallel(res) parallel(mgr) */
131 DISTINCT mgr.resource_id, res.user_id, mgr.group_id
132 FROM jtf_rs_rep_managers mgr, jtf_rs_resource_extns res
133 WHERE mgr.hierarchy_type IN ('MGR_TO_MGR','ADMIN_TO_ADMIN')
134 AND mgr.resource_id = mgr.parent_resource_id
135 AND l_bis_date BETWEEN mgr.start_date_active
136 AND NVL(mgr.end_date_active,to_date('12/31/4712','MM/DD/RRRR'))
137 AND mgr.resource_id = res.resource_id
138 AND res.user_id IS NOT NULL ) x
139 ;
140
141 COMMIT;
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';
151 END IF;
152
153 COMMIT;
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,
163 debug_column, denorm_id , mem_flag,
164 mem_status , creation_date, created_by, active_grp_rel_only )
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) */
174 DISTINCT mgr.resource_id, res.user_id, mgr.group_id
175 FROM jtf_rs_rep_managers mgr, jtf_rs_resource_extns res
176 WHERE mgr.hierarchy_type IN ('MGR_TO_MGR','ADMIN_TO_ADMIN')
177 AND mgr.resource_id = mgr.parent_resource_id
178 AND l_bis_date BETWEEN mgr.start_date_active
179 AND NVL(mgr.end_date_active,to_date('12/31/4712','MM/DD/RRRR'))
180 AND mgr.resource_id = res.resource_id
181 AND res.user_id IS NOT NULL ) x
182 , jtf_rs_groups_b g
183 WHERE x.group_id = g.group_id
184 ;
185
186 COMMIT;
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 ,
196 denorm_level ,
197 start_date ,
198 end_date ,
199 user_id ,
200 resource_id,
201 debug_column,
202 denorm_id ,
203 mem_flag,
204 mem_status,
205 creation_date,
206 created_by,
207 active_grp_rel_only )
208 SELECT /*+ use_hash(d1 n1) PARALLEL(d1) PARALLEL(n1) */
209 DECODE(d1.denorm_level,1,DECODE(d1.active_flag,'Y','-- ','-- [ '),' ') VALUE,
210 --DECODE(d1.denorm_level,1,'-- ',' ') VALUE,
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
220 WHERE n1.group_id = d1.actual_parent_id
221 AND d1.denorm_level < 2
222 AND d1.latest_relationship_flag = 'Y'
223 ;
224
225 COMMIT;
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 ,
235 denorm_level ,
236 start_date ,
237 end_date ,
238 user_id ,
239 resource_id,
240 debug_column,
241 denorm_id ,
242 mem_flag,
243 mem_status ,
244 creation_date,
245 created_by,
246 active_grp_rel_only )
247 SELECT /*+ use_hash(d1 d2 n1) PARALLEL(d1) PARALLEL(d2) PARALLEL(n1) */
248 DECODE (d1.active_flag,'Y',' ',' [ ') VALUE,
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,
258 jtf_rs_dbi_mgr_groups n1
259 WHERE n1.group_id = d1.parent_group_id
260 AND d1.group_id = d2.actual_parent_id
261 AND n1.group_id = d2.parent_group_id
262 AND d1.group_id <> d2.group_id
263 AND d1.latest_relationship_flag = 'Y'
264 AND d2.latest_relationship_flag = 'Y'
265 ;
266
267 COMMIT;
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 ,
277 denorm_level ,
278 start_date ,
279 end_date ,
280 user_id ,
281 resource_id,
282 debug_column,
283 denorm_id ,
284 mem_flag,
285 mem_status,
286 creation_date,
287 created_by,
288 active_grp_rel_only )
289 SELECT /*+ use_hash(d1 n1) PARALLEL(d1) PARALLEL(n1) */
290 DECODE(d1.active_flag,'Y','-- ','-- [ ') VALUE
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
300 WHERE n1.group_id = d1.parent_group_id
301 AND d1.latest_relationship_flag = 'Y'
302 ;
303
304 COMMIT;
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 ,
314 denorm_level ,
315 start_date ,
316 end_date ,
317 user_id ,
318 resource_id,
319 debug_column,
320 denorm_id ,
321 mem_flag,
322 mem_status ,
323 creation_date,
324 created_by,
325 active_grp_rel_only )
326 SELECT /*+ use_hash(d1 n1) PARALLEL(d1) PARALLEL(n1) */
327 DECODE (d1.active_flag, 'Y','---- ','---- [ ') VALUE
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
337 WHERE n1.group_id = d1.parent_group_id
338 AND d1.denorm_level > 0
339 AND d1.latest_relationship_flag = 'Y'
340 ;
341
342 COMMIT;
343
344 --------------------Commented out by NSINGHAI on 02/20/04 --------------------
345 -- This is new query if ever it has to be used for peer groups.
346 -- right now shifted the query to view definition since it takes > 6 minutes
347 -- and inserts 3.5 million rows in the table
348 -- memory footprint in the view is comparable.It increased from 125Kb to 167Kb
349 -- if anytime this insert query is to be used, the below given logic should be used.
350 ------------------------------------------------------------------------------
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 ,
360 denorm_level ,
361 start_date ,
362 end_date ,
363 user_id ,
364 resource_id,
365 debug_column,
366 denorm_id,
367 mem_flag,
368 mem_status ,
369 creation_date,
370 created_by,
371 active_grp_rel_only )
372 SELECT /*+ use_hash(d1 drg1) PARALLEL(d1) PARALLEL(drg1) */
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,
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
387 AND drg1.parent_id = d1.actual_parent_id
388 AND d1.denorm_level = 1
389 AND d1.latest_relationship_flag = 'Y'
390 ;
391
392 COMMIT;
393
394 -- End of Peer Groups insert
395 */
396
397 l_stage := 'Stage = E-SELF-GROUP-MEMBERS : ' ;
398
399 -- group members -- not for specific user -- just preprocessed records
400 -- so that view performs faster and takes less sharable memory. no security applied
401 -- not for 1st time login. First time login group members done after index creation.
402 -- (since that query uses indexes)
403 -- For group member rows, no data is inserted in Id, user_id and resource_id columns
404 -- since they are not for specific user.
405 -- Making it dynamic SQL because in 8i 'CASE' function doesn't work in PLSQL
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 ,
415 denorm_level ,
416 debug_column,
417 denorm_id,
418 grp_mem_resource_id,
419 mem_flag,
420 mem_status,
421 creation_date,
422 created_by,
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) */
432 DISTINCT gm1.resource_id, gm1.group_id
433 , ''A'' mem_status
434 FROM jtf_rs_group_members gm1, jtf_rs_role_relations rrl1, jtf_rs_roles_b rol1
435 WHERE gm1.group_member_id = rrl1.role_resource_id
436 AND gm1.delete_flag = ''N''
437 AND rrl1.role_resource_type = ''RS_GROUP_MEMBER''
438 AND rrl1.delete_flag = ''N''
439 AND rrl1.role_id = rol1.role_id
440 AND ''Y'' IN (rol1.member_flag, rol1.manager_flag)
441 AND rrl1.active_flag = ''Y''
442 UNION ALL
443 SELECT /*+ use_hash(gm2 rrl2 rol2) PARALLEL(gm2) PARALLEL(rrl2) PARALLEL(rol2) */
444 DISTINCT gm2.resource_id, gm2.group_id
445 , ''I'' mem_status
446 FROM jtf_rs_group_members gm2, jtf_rs_role_relations rrl2, jtf_rs_roles_b rol2
447 WHERE gm2.group_member_id = rrl2.role_resource_id
448 AND gm2.delete_flag = ''N''
449 AND rrl2.role_resource_type = ''RS_GROUP_MEMBER''
450 AND rrl2.delete_flag = ''N''
451 AND rrl2.role_id = rol2.role_id
452 AND ''Y'' IN (rol2.member_flag, rol2.manager_flag)
453 AND rrl2.active_flag IS NULL
454 AND NOT EXISTS (
455 SELECT /*+ use_hash(gm3 rrl3 rol3) PARALLEL(gm3) PARALLEL(rrl3) PARALLEL(rol3) */
456 ''1''
457 FROM jtf_rs_group_members gm3, jtf_rs_role_relations rrl3
458 , jtf_rs_roles_b rol3
459 WHERE gm3.group_member_id = rrl3.role_resource_id
460 AND gm3.delete_flag = ''N''
461 AND rrl3.role_resource_type = ''RS_GROUP_MEMBER''
462 AND rrl3.delete_flag = ''N''
463 AND rrl3.role_id = rol3.role_id
464 AND ''Y'' IN (rol3.member_flag, rol3.manager_flag)
465 AND rrl3.active_flag = ''Y''
466 AND gm3.resource_id = gm2.resource_id
467 AND gm3.group_id = gm2.group_id
468 )
469 ) x
470 ' USING l_sysdate, l_user_id
471 ;
472
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',
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
486 -- child groups any more because, all teams will query the function
487 -- jtf_rs_dbi_conc_pub.get_sg_id to default first time login groups for
488 -- their pages. Teams will never see first time login functionality in
489 -- Sales Group LOV dropdown.
490 -----------------------------------------------------------------------------
491 -- l_stage := 'Stage = F-FIRST-TIME-GROUP-MEMBERS : ' ;
492
493 --Insert FIRST TIME LOGIN group members. To be done after index is created because
494 -- the select statement given below uses index in the query
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 ,
504 denorm_level ,
505 user_id ,
506 resource_id,
507 debug_column,
508 denorm_id,
509 grp_mem_resource_id,
510 mem_flag,
511 mem_status ,
512 creation_date,
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)
522 PARALLEL(rol)*/
523 /* DISTINCT mem.resource_id grp_mem_resource_id, mem.group_id, to_number(-1111) current_id,
524 dbi.user_id, dbi.resource_id,
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''
534 AND rrl.delete_flag = ''N''
535 AND dbi.id = mem.group_id
536 AND dbi.id = dbi.parent_id
537 AND dbi.current_id = -1111
538 AND dbi.denorm_level = 0
539 AND rrl.role_id = rol.role_id
540 AND rol.member_flag = ''Y''
541 ) x
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
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(
558 p_status => TRUE ,
559 p_count => l_insert_count,
560 p_period_to => l_bis_date);
561
562 --EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=FALSE';
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 fnd_file.put_line(fnd_file.log, l_stage||sqlcode||':'||sqlerrm);
567 retcode := '2'; -- Error
568 errbuf := l_stage||sqlerrm;
569 BIS_COLLECTION_UTILITIES.Debug('Error in Update Sales Group Hierarchy: '||l_stage||errbuf);
570
571 BIS_COLLECTION_UTILITIES.wrapup(
572 p_status => FALSE ,
573 p_message => l_stage||sqlerrm,
574 p_count => l_insert_count,
575 p_period_to => l_bis_date);
576
577 -- dbms_output.put_line('Error : '||sqlcode||':'||sqlerrm);
578
579 END populate_res_grp;
580
581 /****************************************************************************
582 This function is for providing a common method of fetching the group id
583 for first time login pages. Instead of passing '-1111' to Sales Group
584 Dimension LOV, product teams will call this function which will return
585 them a valid group id. This group id will be used by product teams to
586 query the data rather then querying data for dummy group '-1111'.
587 Internally this function will query for '-1111' and then return the first
588 record.
589
590 This is 8i compatible version
591
592 ER # 3155246
593 Created By nsinghai 03-Oct-2003
594 ***************************************************************************/
595
596 FUNCTION get_sg_id RETURN VARCHAR2 IS
597 l_sg_id VARCHAR2(100);
598
599 BEGIN
600 -- dynamic sql for 8i
601 EXECUTE IMMEDIATE
602 'SELECT id
603 FROM (
604 SELECT id, rank() over (order by value, id nulls last) rnk
605 FROM jtf_rs_dbi_res_grp_vl
606 WHERE usage = ''SALES''
607 AND current_id = -1111
608 AND denorm_level = 0
609 )
610 WHERE rnk = 1' INTO l_sg_id;
611
612 l_sg_id := NVL(l_sg_id, '-1111');
613
614 RETURN l_sg_id;
615
616 EXCEPTION
617 WHEN OTHERS THEN
618 RETURN '-1111';
619 END get_sg_id;
620
621 END jtf_rs_dbi_8i_conc_pub ; -- end package body