DBA Data[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