eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JTF_RS_GROUP_REPORT_PUB

Source


1 PACKAGE BODY JTF_RS_GROUP_REPORT_PUB AS
2 /* $Header: jtfrsbgb.pls 120.0 2005/05/11 08:19:17 appldev ship $ */
3 
4 /*****************************************************************************
5 
6    This is a sql to get the history of movement of members in and out of groups
7 
8    15-JUN-2004    nsinghai   added dummy parameters for selective enabling
9                              disbaling of fields.
10 
11  *****************************************************************************/
12 
13  TYPE REL_RECORD_TYPE IS RECORD
14   ( p_group_id           JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
15     p_related_group_id   JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
16     p_start_date_active  DATE,
17     p_end_date_active    DATE,
18     p_relation_start_date  DATE,
19     p_relation_end_date    DATE,
20     level                NUMBER);
21 
22 
23   TYPE rel_table IS TABLE OF REL_RECORD_TYPE INDEX BY BINARY_INTEGER;
24   g_child_tab rel_table;
25 
26 
27 PROCEDURE child_groups(p_group_id in number,
28                        p_usr_name in varchar2,
29                        p_start_date in date,
30                        p_end_date   in date,
31                        p_usage      in varchar2);
32 
33 /**********************************************************************************************
34  * <Start>
35  * Introduced for incorporating reporting of DFF fields
36  * Ref: ER# 2549463
37  * Data structures and private procedures to accomplish reporting of DFFs
38  */
39 
40 -- Cursor definition to verify if the DFF definition associated with the groups table is frozen.
41 CURSOR c_DFF_frozen
42 IS
43   SELECT 1
44   FROM FND_DESCRIPTIVE_FLEXS_VL
45   WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'JTF_RS_GROUPS'
46         AND FREEZE_FLEX_DEFINITION_FLAG = 'Y';
47 
48 r_DFF_frozen c_DFF_frozen%ROWTYPE;
49 
50 g_DFF_to_be_displayed boolean := false;
51 g_DFF_prompt_max_length number := 0;
52 
53 -- Type definition to store relevent details for each segment in the DFF definition
54 TYPE segment_details_record IS RECORD
55 ( context_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE,
56   is_global BOOLEAN,
57   segment_name fnd_descr_flex_col_usage_vl.end_user_column_name%TYPE,
58   is_displayed BOOLEAN,
59   row_prompt fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE,
60   application_column_name fnd_descr_flex_col_usage_vl.application_column_name%TYPE);
61 
62 -- Define table to store records containing relevant details of segments
63 -- Stores all the segments for all the contexts in the DFF definition
64 TYPE segment_details_table IS TABLE OF segment_details_record INDEX BY BINARY_INTEGER;
65 
66 all_segment_details_tab segment_details_table;
67 
68 
69 /*
70  * Private Procedure to cache the DFF segment details in a local table
71  * Uses FND_DFLEX APIs to populate segment and context details into a local table all_segment_details_tab
72  * A record is created for each segment in each context in the DFF definition.
73  */
74 PROCEDURE populate_segment_details_table ( p_appl_short_name  IN  fnd_application.application_short_name%TYPE,
75                          p_flexfield_name   IN  fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE )
76 IS
77   k INTEGER := 0;
78   flexfield fnd_dflex.dflex_r;
79   flexinfo  fnd_dflex.dflex_dr;
80   contexts  fnd_dflex.contexts_dr;
81   segments  fnd_dflex.segments_dr;
82 
83 BEGIN
84   fnd_dflex.get_flexfield(p_appl_short_name, p_flexfield_name, flexfield, flexinfo);
85   fnd_dflex.get_contexts(flexfield, contexts);
86   FOR i IN 1 .. contexts.ncontexts LOOP /* loop through the contexts defined */
87     IF(contexts.is_enabled(i)) THEN /* consider only the contexts that are enabled.*/
88       fnd_dflex.get_segments(fnd_dflex.make_context(flexfield, contexts.context_code(i)), segments,TRUE);
89       FOR j IN 1 .. segments.nsegments LOOP /* loop through the segments in the each context */
90         /* push a record into the local table all_segment_details_tab for each segment */
91         k := all_segment_details_tab.count + 1;
92         all_segment_details_tab(k).context_code := contexts.context_code(i);
93         all_segment_details_tab(k).is_global    := contexts.is_global(i);
94         all_segment_details_tab(k).segment_name := segments.segment_name(j);
95         all_segment_details_tab(k).is_displayed := segments.is_displayed(j);
96         all_segment_details_tab(k).row_prompt := segments.row_prompt(j);
97         all_segment_details_tab(k).application_column_name := segments.application_column_name(j);
98         /* Determine the maximum length of the prompts for the DFF segments */
99         IF ( LENGTH(segments.row_prompt(j)) > g_DFF_prompt_max_length)
100         THEN
101           g_DFF_prompt_max_length := LENGTH(segments.row_prompt(j)) ;
102         END IF;
103       END LOOP;
104     END IF;
105   END LOOP;
106 END populate_segment_details_table;
107 
108 /*
109  * Private Procedure to print the DFF field values to the output file, for a particular row.
110  * this proc may be invoked after only after invoking  the procedure: populate_segment_details_table
111  * Parameters: ATTRIBUTE_CATEGORY and  ATTRIBUTEn field values of the row and the required indent for printing
112  * Loops over all the segment records for the DFF definition from the local table all_segment_details_tab
113    already populated by the proc populate_segment_details_table
114  * Prints out values for segments that are either in the global context
115    or in the context pertinent to this particular row identified from the value of ATTRIBUTE_CATEGORY of the row
116    and with is_displayed flag on.
117  * For each segment, identifies the ATTRIBUTEn field storing value for that segment and prints it.
118  */
119 PROCEDURE get_DFF_output
120 ( p_attribute_category   jtf_rs_groups_b.ATTRIBUTE_CATEGORY%TYPE,
121   p_attribute1           jtf_rs_groups_b.ATTRIBUTE1%TYPE,
122   p_attribute2           jtf_rs_groups_b.ATTRIBUTE2%TYPE,
123   p_attribute3           jtf_rs_groups_b.ATTRIBUTE3%TYPE,
124   p_attribute4           jtf_rs_groups_b.ATTRIBUTE4%TYPE,
125   p_attribute5           jtf_rs_groups_b.ATTRIBUTE5%TYPE,
126   p_attribute6           jtf_rs_groups_b.ATTRIBUTE6%TYPE,
127   p_attribute7           jtf_rs_groups_b.ATTRIBUTE7%TYPE,
128   p_attribute8           jtf_rs_groups_b.ATTRIBUTE8%TYPE,
129   p_attribute9           jtf_rs_groups_b.ATTRIBUTE9%TYPE,
130   p_attribute10          jtf_rs_groups_b.ATTRIBUTE10%TYPE,
131   p_attribute11          jtf_rs_groups_b.ATTRIBUTE11%TYPE,
132   p_attribute12          jtf_rs_groups_b.ATTRIBUTE12%TYPE,
133   p_attribute13          jtf_rs_groups_b.ATTRIBUTE13%TYPE,
134   p_attribute14          jtf_rs_groups_b.ATTRIBUTE14%TYPE,
135   p_attribute15          jtf_rs_groups_b.ATTRIBUTE15%TYPE,
136   p_indent               NUMBER
137 )
138 IS
139 l_DFF_value jtf_rs_groups_b.ATTRIBUTE1%TYPE;
140 BEGIN
141   FOR i IN 1 .. all_segment_details_tab.count /* Loop over all the segments in the DFF defn. */
142   LOOP
143     IF (all_segment_details_tab(i).is_displayed AND (all_segment_details_tab(i).is_global OR all_segment_details_tab(i).context_code = p_attribute_category))
144     /* segment needs to be printed if it belongs to global context or context pertinent to this record */
145     THEN
146       /* Identify the ATTRIBUTEn field storing value for this segment */
147       IF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE1' THEN
148         l_DFF_value := p_attribute1;
149       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE2' THEN
150         l_DFF_value := p_attribute2;
151       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE3' THEN
152         l_DFF_value := p_attribute3;
153       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE4' THEN
154         l_DFF_value := p_attribute4;
155       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE5' THEN
156         l_DFF_value := p_attribute5;
157       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE6' THEN
158         l_DFF_value := p_attribute6;
159       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE7' THEN
160         l_DFF_value := p_attribute7;
161       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE8' THEN
162         l_DFF_value := p_attribute8;
163       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE9' THEN
164         l_DFF_value := p_attribute9;
165       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE10' THEN
166         l_DFF_value := p_attribute10;
167       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE11' THEN
168         l_DFF_value := p_attribute11;
169       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE12' THEN
170         l_DFF_value := p_attribute12;
171       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE13' THEN
172         l_DFF_value := p_attribute13;
173       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE14' THEN
174         l_DFF_value := p_attribute14;
175       ELSIF all_segment_details_tab(i).application_column_name = 'ATTRIBUTE15' THEN
176         l_DFF_value := p_attribute15;
177       END IF;
178       /* indent the line */
179       FOR i IN 1 .. p_indent
180       LOOP
181         fnd_file.put(fnd_file.log,' ');
182       END LOOP;
183       /* RPAD so that the values besides the prompts are printed aligned */
184       fnd_file.put(fnd_file.log, RPAD (all_segment_details_tab(i).row_prompt || ': ', g_DFF_prompt_max_length + 5, ' ')  || l_DFF_value);
185       fnd_file.put_line(fnd_file.log,'');
186     END IF;
187   END LOOP;
188 
189 END get_DFF_output;
190 
191 
192 /*
193  * Introduced for incorporating reporting of DFF fields
194  * Ref: ER# 2549463
195  * Data structures and private procedures to accomplish reporting of DFFs
196  * <End>
197  * Hereafter code is inserted at appropriate places to invoke the above procedures.
198  ***********************************************************************************************/
199 
200 
201 
202 
203 PROCEDURE query_group(ERRBUF  OUT NOCOPY VARCHAR2,
204                       RETCODE OUT NOCOPY VARCHAR2,
205                       P_REP_TYPE   IN  VARCHAR2 ,
206                       P_DUMMY_1    IN VARCHAR2 ,
207                       P_DUMMY_2    IN VARCHAR2 ,
208                       P_DUMMY_3    IN VARCHAR2 ,
209                       P_GROUP_ID   IN NUMBER ,
210                       P_RES_ID     IN NUMBER ,
211                       P_USAGE      IN VARCHAR2 ,
212                       P_USR_NAME   IN VARCHAR2 ,
213                       P_DATE_OPT   IN  VARCHAR2 ,
214                       P_START_DATE IN varchar2 ,
215                       P_END_DATE   IN varchar2 ,
216                       P_NO_OF_DAYS IN number )
217 IS
218 begin
219   query_group (ERRBUF, RETCODE, P_REP_TYPE, P_DUMMY_1, P_DUMMY_2, P_DUMMY_3,  P_GROUP_ID, P_RES_ID, P_USAGE, P_USR_NAME, P_DATE_OPT, P_START_DATE, P_END_DATE, P_NO_OF_DAYS, 'N') ;
220 end;
221 
222 
223 /*
224  * Procedure overloaded to give user choice to display DFF fields in the report.
225  * While submitting the concurrent request, The user may choose to display DFF fields in the report.
226  * Ref: ER# 2549463
227  */
228 
229 PROCEDURE query_group(ERRBUF  OUT NOCOPY VARCHAR2,
230                       RETCODE OUT NOCOPY VARCHAR2,
231                       P_REP_TYPE   IN  VARCHAR2 ,
232                       P_DUMMY_1    IN VARCHAR2 ,
233                       P_DUMMY_2    IN VARCHAR2 ,
234                       P_DUMMY_3    IN VARCHAR2 ,
235                       P_GROUP_ID   IN NUMBER ,
236                       P_RES_ID     IN NUMBER ,
237                       P_USAGE      IN VARCHAR2 ,
238                       P_USR_NAME   IN VARCHAR2 ,
239                       P_DATE_OPT   IN  VARCHAR2 ,
240                       P_START_DATE IN varchar2 ,
241                       P_END_DATE   IN varchar2 ,
242                       P_NO_OF_DAYS IN number,
243                       P_DISP_DFF_FIELDS IN VARCHAR2)
244 is
245   /* Moved the initial assignment of below variables to inside begin */
246 l_group_id   number;
247 l_res_id   number;
248 l_usr_name   VARCHAR2(2000);
249 l_start_date  date;
250 l_end_date  date;
251 l_no_of_days  number;
252 
253 cursor c_grp_usage
254     is
255  select /*+ INDEX(usg jtf_rs_group_usages_n1) */ group_id
256   from  jtf_rs_group_usages usg
257  where  usg.usage = p_usage
258    and  not exists (select group_id
259                      from  jtf_rs_grp_relations rel
260                     where  rel.group_id = usg.group_id
261                      and   nvl(delete_flag, 'N') <> 'Y') ;
262 
263 cursor c_grp_usage2
264     is
265  select /*+ INDEX(usg jtf_rs_group_usages_n1) */ group_id
266   from  jtf_rs_group_usages usg
267  where  usg.usage = p_usage
268    and  usg.group_id = p_group_id;
269 
270 r_grp_usage c_grp_usage%rowtype;
271 
272   --Bug# 2909006. To print Usage at the top of report.
273   cursor c_grp_usagename
274     is
275     select
276         meaning
277     from
278         fnd_lookups
279     where  lookup_type = 'JTF_RS_USAGE'
280     and lookup_code = p_usage;
281 
282      r_grp_usagename c_grp_usagename%rowtype;
283 
284 cursor c_grp_sec(l_group number)
285    is
286   select /*+ INDEX(gm jtf_rs_group_members_aud_nu3) */ g1.group_name,
287        --<Start>: Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
288        g1.attribute_category, g1.attribute1, g1.attribute2, g1.attribute3, g1.attribute4, g1.attribute5, g1.attribute6, g1.attribute7, g1.attribute8,
289        g1.attribute9, g1.attribute10, g1.attribute11, g1.attribute12, g1.attribute13, g1.attribute14, g1.attribute15,
290        --<End>: Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
291        ext.resource_number,
292        ext.resource_name,
293        ext.category,
294        lkp.name category_name,
295        to_char(gm.creation_date, 'DD-MON-YYYY:HH24:MI:SS') creation_date,
296        fnd.user_name   created_by,
297        decode(gm.old_group_id, g1.group_id, 'OUT', 'IN') moved,
298        decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_number ) from_group_number,
299        decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_name ) from_group_name,
300        decode(gm.old_group_id, g1.group_id, g2.group_number, null)    to_group_number,
301        decode(gm.old_group_id, g1.group_id, g2.group_name, null)    to_group_name,
302        ext.resource_id,
303        g1.group_id
304   from
305        jtf_rs_groups_vl g2,
306        jtf_rs_groups_vl g3,
307        jtf_rs_resource_extns_vl ext,
308        jtf_objects_vl lkp ,
309        fnd_user fnd,
310        jtf_rs_group_members_aud gm,
311        jtf_rs_group_members  mem,
312        jtf_rs_groups_vl g1
313  where g1.group_id = l_group
314  and   mem.group_id  = g1.group_id
315  and   gm.group_member_id = mem.group_member_id
316  and   ( gm.new_group_id   = g1.group_id
317            or gm.old_group_id = g1.group_id )
318  and  gm.creation_date between nvl(l_start_date,sysdate)
319                            and  nvl(l_end_date,sysdate)
320  and   gm.new_group_id  =   g2.group_id(+)
321  and   gm.old_group_id  =   g3.group_id(+)
322  and  fnd.user_name like nvl(l_usr_name, '%')
323  and  fnd.user_id = gm.created_by
324  and  mem.resource_id = ext.resource_id
325  and  ext.category = lkp.object_code
326  order by g1.group_name , resource_name, gm.creation_date , fnd.user_name;
327 
328 
329 r_grp c_grp_sec%rowtype;
330 l_prev_group JTF_RS_GROUPS_TL.GROUP_NAME%TYPE;
331 
332 
333 cursor c_res
334    is
335   select /*+ INDEX(gm jtf_rs_group_members_aud_nu3) */ g1.group_name,
336        --<Start>: Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
337        g1.attribute_category, g1.attribute1, g1.attribute2, g1.attribute3, g1.attribute4, g1.attribute5, g1.attribute6, g1.attribute7, g1.attribute8,
338        g1.attribute9, g1.attribute10, g1.attribute11, g1.attribute12, g1.attribute13, g1.attribute14, g1.attribute15,
339        --<End>: Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
340        ext.resource_number,
341        ext.resource_name,
342        ext.category,
343        lkp.name category_name,
344        to_char(gm.creation_date, 'DD-MON-YYYY:HH24:MI:SS') creation_date,
345        fnd.user_name   created_by,
346        decode(gm.old_group_id, g1.group_id, 'OUT', 'IN') moved,
347        decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_number ) from_group_number,
348        decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_name ) from_group_name,
349        decode(gm.old_group_id, g1.group_id, g2.group_number, null)    to_group_number,
350        decode(gm.old_group_id, g1.group_id, g2.group_name, null)    to_group_name
351  from
352        jtf_rs_groups_vl g2,
353        jtf_rs_groups_vl g3,
354        fnd_user fnd,
355        jtf_objects_vl lkp,
356        jtf_rs_groups_vl g1,
357        jtf_rs_group_members_aud gm,
358        jtf_rs_group_members  mem,
359        jtf_rs_resource_extns_vl ext
360   where  ext.resource_id = l_res_id
361     and  mem.resource_id = ext.resource_id
362     and  gm.group_member_id = mem.group_member_id
363     and  gm.creation_date between nvl(l_start_date,sysdate)
364                            and   nvl(l_end_date,sysdate)
365     and  gm.created_by = fnd.user_id
366     and  fnd.user_name like nvl(l_usr_name, '%')
367     and  g1.group_id = mem.group_id
368     and  g2.group_id(+)=  gm.new_group_id
369     and  g3.group_id(+) = gm.old_group_id
370     and  ext.category = lkp.object_code
371   order by resource_name, g1.group_name , gm.creation_date , fnd.user_name;
372 
373  r_res  c_res%rowtype;
374 
375   /* Moved the initial assignment of below variable to inside begin */
376  l_rep_type VARCHAR2(30);
377 
378  cursor to_grp_cur(l_group_id number,
379                    l_resource_id number)
380      is
381   select gm.group_member_id,
382         g1.group_id,
383         g1.group_name,
384         g1.group_number
385  from   jtf_rs_group_members_aud gm,
386          jtf_rs_groups_vl g1
387  where  gm.old_group_id = l_group_id
388  and    gm.new_resource_id = l_resource_id
389  and     g1.group_id = gm.new_group_id;
390 
391  to_grp_rec to_grp_cur%rowtype;
392  l_to_grp_number jtf_rs_groups_vl.group_number%type;
393  l_to_grp_name jtf_rs_groups_vl.group_name%type;
394 
395 
396 
397 
398 begin
399 
400   l_group_id    := p_group_id;
401   l_res_id      := p_res_id;
402   l_usr_name    := p_usr_name;
403   l_no_of_days  := p_no_of_days;
404   l_rep_type    := p_rep_type;
405 
406   IF(p_date_opt = 'RANGE')
407   THEN
408       l_start_date := trunc(to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS'));
409       l_end_date := trunc(to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS'));
410   ELSE
411       l_start_date := sysdate - nvl(p_no_of_days ,0);
412       l_end_date   := sysdate;
413   END IF;
414 
415   fnd_file.new_line(fnd_file.log, 1);
416 
417   --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
418   /*
419    * If the user chooses to display the DFFs AND the DFF definition is frozen
420    * Cache the segment details in a local table.
421    */
422   IF (P_DISP_DFF_FIELDS = 'Y')
423   THEN
424       OPEN c_DFF_frozen;
425       FETCH c_DFF_frozen INTO r_DFF_frozen;
426       IF(c_DFF_frozen%found)
427       THEN
428         g_DFF_to_be_displayed := TRUE;
429         all_segment_details_tab.delete;
430         populate_segment_details_table('JTF', 'JTF_RS_GROUPS');
431       END IF;
432   CLOSE c_DFF_frozen;
433   END IF;
434   --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
435 
436   IF(l_rep_type = 'GROUP')
437   THEN
438         open c_grp_sec(p_group_id);
439         fetch c_grp_sec into r_grp;
440         if(c_grp_sec%found)
441         then
442            fnd_file.put_line(fnd_file.log,'Group name: '||r_grp.group_name);
443            --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
444            IF (g_DFF_to_be_displayed)
445            THEN
446              get_DFF_output
447              (  r_grp.ATTRIBUTE_CATEGORY,
448                 r_grp.ATTRIBUTE1,
449                 r_grp.ATTRIBUTE2,
450                 r_grp.ATTRIBUTE3,
451                 r_grp.ATTRIBUTE4,
452                 r_grp.ATTRIBUTE5,
453                 r_grp.ATTRIBUTE6,
454                 r_grp.ATTRIBUTE7,
455                 r_grp.ATTRIBUTE8,
456                 r_grp.ATTRIBUTE9,
457                 r_grp.ATTRIBUTE10,
458                 r_grp.ATTRIBUTE11,
459                 r_grp.ATTRIBUTE12,
460                 r_grp.ATTRIBUTE13,
461                 r_grp.ATTRIBUTE14,
462                 r_grp.ATTRIBUTE15,
463                 0
464              );
465            END IF;
466            --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
467         else
468            fnd_file.put_line(fnd_file.log,'No values found based on your criterion');
469         end if;
470         l_prev_group := r_grp.group_name;
471         while (c_grp_sec%FOUND)
472         loop
473             if (l_prev_group <> r_grp.group_name)
474             then
475               fnd_file.new_line(fnd_file.log, 3);
476               fnd_file.put_line(fnd_file.log,'Group name: '||r_grp.group_name);
477               --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
478               IF (g_DFF_to_be_displayed)
479               THEN
480                 get_DFF_output
481                 (  r_grp.ATTRIBUTE_CATEGORY,
482                    r_grp.ATTRIBUTE1,
483                    r_grp.ATTRIBUTE2,
484                    r_grp.ATTRIBUTE3,
485                    r_grp.ATTRIBUTE4,
486                    r_grp.ATTRIBUTE5,
487                    r_grp.ATTRIBUTE6,
488                    r_grp.ATTRIBUTE7,
489                    r_grp.ATTRIBUTE8,
490                    r_grp.ATTRIBUTE9,
491                    r_grp.ATTRIBUTE10,
492                    r_grp.ATTRIBUTE11,
493                    r_grp.ATTRIBUTE12,
494                    r_grp.ATTRIBUTE13,
495                    r_grp.ATTRIBUTE14,
496                    r_grp.ATTRIBUTE15,
497                    0
498                 );
499               END IF;
500               --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
501             end if;
502 
503             if(r_grp.moved = 'OUT')
504             then
505                open  to_grp_cur( r_grp.group_id,
506                      r_grp.resource_id)  ;
507                fetch to_grp_cur into to_grp_rec;
508                l_to_grp_number := to_grp_rec.group_number;
509                l_to_grp_name := to_grp_rec.group_name;
510                close to_grp_cur;
511             else
512                l_to_grp_number := null;
513                l_to_grp_name := null;
514             end if;
515 
516             fnd_file.put_line(fnd_file.log,rpad('  Resource Name: ',27, ' ')||r_grp.resource_name);
517             fnd_file.put_line(fnd_file.log,rpad('  Resource Number: ',27, ' ')||r_grp.resource_number);
518             fnd_file.put_line(fnd_file.log,rpad('  Category: ',27, ' ')||r_grp.category_name);
519             fnd_file.put_line(fnd_file.log,rpad('  Date: ',27, ' ')||r_grp.creation_date);
520             fnd_file.put_line(fnd_file.log,rpad('  Updated By: ',27, ' ')||r_grp.created_by);
521             fnd_file.put_line(fnd_file.log,rpad('  Action: ',27, ' ')||r_grp.moved);
522             fnd_file.put_line(fnd_file.log,rpad('  Moved From Group #:',27, ' ')||r_grp.from_group_number);
523             fnd_file.put_line(fnd_file.log,rpad('  Moved From Group Name:',27, ' ')||r_grp.from_group_name);
524             fnd_file.put_line(fnd_file.log,rpad('  Moved To Group #:',27, ' ')||l_to_grp_number);
525             fnd_file.put_line(fnd_file.log,rpad('  Moved to Group Name: ',27, ' ')||l_to_grp_name);
526             --fnd_file.put_line(fnd_file.log,rpad('  Moved To Group #:',27, ' ')||r_grp.to_group_number);
527             --fnd_file.put_line(fnd_file.log,rpad('  Moved to Group Name: ',27, ' ')||r_grp.to_group_name);
528             l_prev_group := r_grp.group_name;
529             fnd_file.new_line(fnd_file.log, 1);
530             fetch c_grp_sec into r_grp;
531 
532         end loop; -- end of if
533         close c_grp_sec;
534 
535 
536        -- for the child groups
537        child_groups(p_group_id,
538                     p_usr_name,
539                     l_start_date,
540                     l_end_date,
541                     p_usage);
542     ELSIF(l_rep_type = 'USAGE')
543     THEN
544     --Bug# 2909006. Print Usage at the top of report.
545     open  c_grp_usagename;
546      fetch c_grp_usagename into r_grp_usagename;
547      if (c_grp_usagename%found) then
548         fnd_file.put_line(fnd_file.log,'Usage:      ' || r_grp_usagename.meaning);
549      end if;
550      close c_grp_usagename;
551 
552      if(p_group_id is null)
553      then
554 
555        open c_grp_usage;
556        fetch c_grp_usage into r_grp_usage;
557        while(c_grp_usage%found)
558        loop
559           open c_grp_sec(r_grp_usage.group_id);
560           fetch c_grp_sec into r_grp;
561           if(c_grp_sec%found)
562           then
563              fnd_file.put_line(fnd_file.log,'Group name: '||r_grp.group_name);
564              --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
565              IF (g_DFF_to_be_displayed)
566              THEN
567                get_DFF_output
568                (  r_grp.ATTRIBUTE_CATEGORY,
569                   r_grp.ATTRIBUTE1,
570                   r_grp.ATTRIBUTE2,
571                   r_grp.ATTRIBUTE3,
572                   r_grp.ATTRIBUTE4,
573                   r_grp.ATTRIBUTE5,
574                   r_grp.ATTRIBUTE6,
575                   r_grp.ATTRIBUTE7,
576                   r_grp.ATTRIBUTE8,
577                   r_grp.ATTRIBUTE9,
578                   r_grp.ATTRIBUTE10,
579                   r_grp.ATTRIBUTE11,
580                   r_grp.ATTRIBUTE12,
581                   r_grp.ATTRIBUTE13,
582                   r_grp.ATTRIBUTE14,
583                   r_grp.ATTRIBUTE15,
584                   0
585                );
586              END IF;
587              --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
588           end if;
589          l_prev_group := r_grp.group_name;
590 
591 
592           while (c_grp_sec%FOUND)
593           loop
594             if (l_prev_group <> r_grp.group_name)
595             then
596               fnd_file.new_line(fnd_file.log, 3);
597               fnd_file.put_line(fnd_file.log,'Group name: '||r_grp.group_name);
598               --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
599               IF (g_DFF_to_be_displayed)
600               THEN
601                 get_DFF_output
602                 (  r_grp.ATTRIBUTE_CATEGORY,
603                    r_grp.ATTRIBUTE1,
604                    r_grp.ATTRIBUTE2,
605                    r_grp.ATTRIBUTE3,
606                    r_grp.ATTRIBUTE4,
607                    r_grp.ATTRIBUTE5,
608                    r_grp.ATTRIBUTE6,
609                    r_grp.ATTRIBUTE7,
610                    r_grp.ATTRIBUTE8,
611                    r_grp.ATTRIBUTE9,
612                    r_grp.ATTRIBUTE10,
613                    r_grp.ATTRIBUTE11,
614                    r_grp.ATTRIBUTE12,
615                    r_grp.ATTRIBUTE13,
616                    r_grp.ATTRIBUTE14,
617                    r_grp.ATTRIBUTE15,
618                    0
619                 );
620               END IF;
621               --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
622             end if;
623 
624             if(r_grp.moved = 'OUT')
625             then
626                open  to_grp_cur( r_grp.group_id,
627                      r_grp.resource_id)  ;
628                fetch to_grp_cur into to_grp_rec;
629                l_to_grp_number := to_grp_rec.group_number;
630                l_to_grp_name := to_grp_rec.group_name;
631                close to_grp_cur;
632             else
633                l_to_grp_number := null;
634                l_to_grp_name := null;
635             end if;
636 
637             fnd_file.put_line(fnd_file.log,rpad('  Resource Name: ',27, ' ')||r_grp.resource_name);
638             fnd_file.put_line(fnd_file.log,rpad('  Resource Number: ',27, ' ')||r_grp.resource_number);
639             fnd_file.put_line(fnd_file.log,rpad('  Category: ',27, ' ')||r_grp.category_name);
640             fnd_file.put_line(fnd_file.log,rpad('  Date: ',27, ' ')||r_grp.creation_date);
641             fnd_file.put_line(fnd_file.log,rpad('  Updated By: ',27, ' ')||r_grp.created_by);
642             fnd_file.put_line(fnd_file.log,rpad('  Action: ',27, ' ')||r_grp.moved);
643             fnd_file.put_line(fnd_file.log,rpad('  Moved From Group #:',27, ' ')||r_grp.from_group_number);
644             fnd_file.put_line(fnd_file.log,rpad('  Moved From Group Name:',27, ' ')||r_grp.from_group_name);
645             --fnd_file.put_line(fnd_file.log,rpad('  Moved To Group #:',27, ' ')||r_grp.to_group_number);
646             --fnd_file.put_line(fnd_file.log,rpad('  Moved to Group Name: ',27, ' ')||r_grp.to_group_name);
647             fnd_file.put_line(fnd_file.log,rpad('  Moved To Group #:',27, ' ')||l_to_grp_number);
648             fnd_file.put_line(fnd_file.log,rpad('  Moved to Group Name: ',27, ' ')||l_to_grp_name);
649             l_prev_group := r_grp.group_name;
650             fnd_file.new_line(fnd_file.log, 1);
651             fetch c_grp_sec into r_grp;
652 
653           end loop; -- end of if
654           close c_grp_sec;
655 
656           --call drill down
657            child_groups(r_grp_usage.group_id,
658                     p_usr_name,
659                     l_start_date,
660                     l_end_date,
661                     p_usage);
662 
663 
664           fetch c_grp_usage into r_grp_usage;
665          end loop;
666          close c_grp_usage;
667      else
668        open c_grp_usage2;
669        fetch c_grp_usage2 into r_grp_usage;
670        while(c_grp_usage2%found)
671        loop
672           open c_grp_sec(r_grp_usage.group_id);
673           fetch c_grp_sec into r_grp;
674           if(c_grp_sec%found)
675           then
676              fnd_file.put_line(fnd_file.log,'Group name: '||r_grp.group_name);
677              --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
678              IF (g_DFF_to_be_displayed)
679              THEN
680                get_DFF_output
681                (  r_grp.ATTRIBUTE_CATEGORY,
682                   r_grp.ATTRIBUTE1,
683                   r_grp.ATTRIBUTE2,
684                   r_grp.ATTRIBUTE3,
685                   r_grp.ATTRIBUTE4,
686                   r_grp.ATTRIBUTE5,
687                   r_grp.ATTRIBUTE6,
688                   r_grp.ATTRIBUTE7,
689                   r_grp.ATTRIBUTE8,
690                   r_grp.ATTRIBUTE9,
691                   r_grp.ATTRIBUTE10,
692                   r_grp.ATTRIBUTE11,
693                   r_grp.ATTRIBUTE12,
694                   r_grp.ATTRIBUTE13,
695                   r_grp.ATTRIBUTE14,
696                   r_grp.ATTRIBUTE15,
697                   0
698                );
699              END IF;
700              --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
701          end if;
702          l_prev_group := r_grp.group_name;
703 
704 
705           while (c_grp_sec%FOUND)
706           loop
707             if (l_prev_group <> r_grp.group_name)
708             then
709               fnd_file.new_line(fnd_file.log, 3);
710               fnd_file.put_line(fnd_file.log,'Group name: '||r_grp.group_name);
711               --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
712               IF (g_DFF_to_be_displayed)
713               THEN
714                 get_DFF_output
715                 (  r_grp.ATTRIBUTE_CATEGORY,
716                    r_grp.ATTRIBUTE1,
717                    r_grp.ATTRIBUTE2,
718                    r_grp.ATTRIBUTE3,
719                    r_grp.ATTRIBUTE4,
720                    r_grp.ATTRIBUTE5,
721                    r_grp.ATTRIBUTE6,
722                    r_grp.ATTRIBUTE7,
723                    r_grp.ATTRIBUTE8,
724                    r_grp.ATTRIBUTE9,
725                    r_grp.ATTRIBUTE10,
726                    r_grp.ATTRIBUTE11,
727                    r_grp.ATTRIBUTE12,
728                    r_grp.ATTRIBUTE13,
729                    r_grp.ATTRIBUTE14,
730                    r_grp.ATTRIBUTE15,
731                    0
732                 );
733               END IF;
734               --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
735             end if;
736 
737             if(r_grp.moved = 'OUT')
738             then
739                open  to_grp_cur( r_grp.group_id,
740                      r_grp.resource_id)  ;
741                fetch to_grp_cur into to_grp_rec;
742                l_to_grp_number := to_grp_rec.group_number;
743                l_to_grp_name := to_grp_rec.group_name;
744                close to_grp_cur;
745             else
746                l_to_grp_number := null;
747                l_to_grp_name := null;
748             end if;
749 
750             fnd_file.put_line(fnd_file.log,rpad('  Resource Name: ',27, ' ')||r_grp.resource_name);
751             fnd_file.put_line(fnd_file.log,rpad('  Resource Number: ',27, ' ')||r_grp.resource_number);
752             fnd_file.put_line(fnd_file.log,rpad('  Category: ',27, ' ')||r_grp.category_name);
753             fnd_file.put_line(fnd_file.log,rpad('  Date: ',27, ' ')||r_grp.creation_date);
754             fnd_file.put_line(fnd_file.log,rpad('  Updated By: ',27, ' ')||r_grp.created_by);
755             fnd_file.put_line(fnd_file.log,rpad('  Action: ',27, ' ')||r_grp.moved);
756             fnd_file.put_line(fnd_file.log,rpad('  Moved From Group #:',27, ' ')||r_grp.from_group_number);
757             fnd_file.put_line(fnd_file.log,rpad('  Moved From Group Name:',27, ' ')||r_grp.from_group_name);
758             --fnd_file.put_line(fnd_file.log,rpad('  Moved To Group #:',27, ' ')||r_grp.to_group_number);
759             --fnd_file.put_line(fnd_file.log,rpad('  Moved to Group Name: ',27, ' ')||r_grp.to_group_name);
760             fnd_file.put_line(fnd_file.log,rpad('  Moved To Group #:',27, ' ')||l_to_grp_number);
761             fnd_file.put_line(fnd_file.log,rpad('  Moved to Group Name: ',27, ' ')||l_to_grp_name);
762             l_prev_group := r_grp.group_name;
763             fnd_file.new_line(fnd_file.log, 1);
764             fetch c_grp_sec into r_grp;
765 
766           end loop; -- end of if
767           close c_grp_sec;
768 
769           --call drill down
770            child_groups(r_grp_usage.group_id,
771                     p_usr_name,
772                     l_start_date,
773                     l_end_date,
774                     p_usage);
775 
776 
777           fetch c_grp_usage2 into r_grp_usage;
778          end loop;
779          close c_grp_usage2;
780      end if; --end of group_id check
781   ELSIF(l_rep_type = 'RESOURCE')
782   THEN
783         open c_res;
784         fetch c_res into r_res;
785         if(c_res%found)
786         then
787           fnd_file.put_line(fnd_file.log,'Resource: '||r_res.resource_name);
788           fnd_file.put_line(fnd_file.log,'Resource Number: '||r_res.resource_number);
789           fnd_file.put_line(fnd_file.log,'Category: '||r_res.category_name);
790           fnd_file.put_line(fnd_file.log,rpad(' Group name: ',27,' ')||r_res.group_name);
791           --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
792           IF (g_DFF_to_be_displayed)
793           THEN
794             get_DFF_output
795             (  r_res.ATTRIBUTE_CATEGORY,
796                r_res.ATTRIBUTE1,
797                r_res.ATTRIBUTE2,
798                r_res.ATTRIBUTE3,
799                r_res.ATTRIBUTE4,
800                r_res.ATTRIBUTE5,
801                r_res.ATTRIBUTE6,
802                r_res.ATTRIBUTE7,
803                r_res.ATTRIBUTE8,
804                r_res.ATTRIBUTE9,
805                r_res.ATTRIBUTE10,
806                r_res.ATTRIBUTE11,
807                r_res.ATTRIBUTE12,
808                r_res.ATTRIBUTE13,
809                r_res.ATTRIBUTE14,
810                r_res.ATTRIBUTE15,
811                1
812             );
813           END IF;
814           --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
815         else
816            fnd_file.put_line(fnd_file.log,'No values found based on your criterion');
817        end if;
818         l_prev_group := r_res.group_name;
819         while (c_res%FOUND)
820         loop
821             if (l_prev_group <> r_res.group_name)
822             then
823               fnd_file.new_line(fnd_file.log, 3);
824               fnd_file.put_line(fnd_file.log,rpad('  Group name: ',27,' ')||r_res.group_name);
825               --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
826               IF (g_DFF_to_be_displayed)
827               THEN
828                 get_DFF_output
829                 (  r_res.ATTRIBUTE_CATEGORY,
830                    r_res.ATTRIBUTE1,
831                    r_res.ATTRIBUTE2,
832                    r_res.ATTRIBUTE3,
833                    r_res.ATTRIBUTE4,
834                    r_res.ATTRIBUTE5,
835                    r_res.ATTRIBUTE6,
836                    r_res.ATTRIBUTE7,
837                    r_res.ATTRIBUTE8,
838                    r_res.ATTRIBUTE9,
839                    r_res.ATTRIBUTE10,
840                    r_res.ATTRIBUTE11,
841                    r_res.ATTRIBUTE12,
842                    r_res.ATTRIBUTE13,
843                    r_res.ATTRIBUTE14,
844                    r_res.ATTRIBUTE15,
845                    2
846                 );
847               END IF;
848               --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
849             end if;
850             fnd_file.put_line(fnd_file.log,rpad('  Date: ',29, ' ')||r_res.creation_date);
851             fnd_file.put_line(fnd_file.log,rpad('  Updated By: ',29, ' ')||r_res.created_by);
852             fnd_file.put_line(fnd_file.log,rpad('  Action: ',29, ' ')||r_res.moved);
853             fnd_file.put_line(fnd_file.log,rpad('  Moved From Group #:',29, ' ')||r_res.from_group_number);
854             fnd_file.put_line(fnd_file.log,rpad('  Moved From Group Name:',29, ' ')||r_res.from_group_name);
855             fnd_file.put_line(fnd_file.log,rpad('  Moved To Group #:',29, ' ')||r_res.to_group_number);
856             fnd_file.put_line(fnd_file.log,rpad('  Moved to Group Name: ',29, ' ')||r_res.to_group_name);
857             l_prev_group := r_res.group_name;
858             fnd_file.new_line(fnd_file.log, 1);
859             fetch c_res into r_res;
860 
861         end loop; -- end of if
862         close c_res;
863 
864   END IF;
865   --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
866   IF (g_DFF_to_be_displayed <> FALSE)
867   THEN
868     all_segment_details_tab.delete;
869     g_DFF_to_be_displayed := FALSE;
870     g_DFF_prompt_max_length := 0;
871   END IF;
872   --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
873 exception
874    when others then
875        fnd_file.put_line(fnd_file.log,sqlerrm);
876 end query_group;
877 
878  /* This procedure traverse recursively thru the child
879      hierarchy of a group and populates g_child_tab table with
880      records which are within the date range.  This procedure
881      emulates the connect by prior cursor for finding parent groups. */
882   PROCEDURE POPULATE_CHILD_TABLE(P_GROUP_ID IN NUMBER,
883                                  P_GREATEST_START_DATE IN DATE,
884                                  P_LEAST_END_DATE IN DATE,
885                                  P_LEVEL IN NUMBER)
886   IS
887     CURSOR c_children (l_g_miss_date DATE)
888     IS
889       SELECT rel.group_id,
890            rel.related_group_id,
891            trunc(greatest(rel.start_date_active,
892                       nvl(p_greatest_start_date, rel.start_date_active))) greatest_start_date,
893              /* Logic : end_date_active, p_least_end_date
894                           NULL         , NULL   = NULL
895                           NULL         , Value  = Value
896                           Value        , NULL   = Value
897                           Value1       , Value2 = least(value1, value2) */
898            trunc(least(nvl(rel.end_date_active, p_least_end_date),
899                    nvl(p_least_end_date, rel.end_date_active))) least_end_date,
900              start_date_active relation_start_date,
901              end_date_active relation_end_date
902        FROM jtf_rs_grp_relations rel
903        WHERE relation_type = 'PARENT_GROUP'
904        AND rel.related_group_id = p_group_id
905        AND NVL(rel.delete_flag, 'N') <> 'Y'
906          /*
907             mugsrin:
908             Modified the following lines to comply with GSCC standard
909             Original:
910             AND least(nvl(end_date_active, to_date(l_g_miss_date)),
911                    nvl(p_least_end_date, to_date(l_g_miss_date))) >=
912             Modified as given below:
913          */
914             AND least(nvl(end_date_active, to_date(to_char(l_g_miss_date,'DD-MM-RRRR'),'DD-MM-RRRR')),
915                 nvl(p_least_end_date, to_date(to_char(l_g_miss_date,'DD-MM-RRRR'),'DD-MM-RRRR'))) >=
916             trunc(greatest(start_date_active,nvl(p_greatest_start_date, start_date_active)));
917 
918      i INTEGER := 0;
919   BEGIN
920      FOR r_child IN c_children(FND_API.G_MISS_DATE) LOOP
921        i := g_child_tab.COUNT+1;
922        g_child_tab(i).p_group_id            := r_child.group_id;
923        g_child_tab(i).p_related_group_id    := r_child.related_group_id;
924        g_child_tab(i).p_start_date_active   := r_child.greatest_start_date;
925        g_child_tab(i).p_end_date_active     := r_child.least_end_date;
926        g_child_tab(i).p_relation_start_date := r_child.relation_start_date;
927        g_child_tab(i).p_relation_end_date   := r_child.relation_end_date;
928        g_child_tab(i).level                 := p_level;
929        populate_child_table(g_child_tab(i).p_group_id,
930                             g_child_tab(i).p_start_date_active,
931                             g_child_tab(i).p_end_date_active,
932                             p_level+1);
933      END LOOP;
934   END;
935 
936   /* This procedure traverse recursively thru the child
937      hierarchy of a group and populates g_child_tab table with
938      records which are within the date range.  This procedure
939      emulates the connect by prior cursor for finding parent groups. */
940   PROCEDURE POPULATE_CHILD_TABLE(P_GROUP_ID IN NUMBER)
941   IS
942   BEGIN
943      g_child_tab.delete;
944      populate_child_table(p_group_id, null, null, 1);
945   END;
946 
947 /*
948   Added P_DISP_ROLE parameter vide Bug# 1745032
949  */
950 
951 PROCEDURE query_group_hierarchy(ERRBUF  OUT NOCOPY VARCHAR2,
952                       RETCODE OUT NOCOPY VARCHAR2,
953                       P_GROUP_NAME IN VARCHAR2 ,
954                       P_DISP_ROLE IN VARCHAR2  )
955 is
956 begin
957   query_group_hierarchy (ERRBUF, RETCODE, P_GROUP_NAME, P_DISP_ROLE, 'N');
958 end;
959 
960 /*
961  * Procedure overloaded to give user choice to display DFF fields in the report.
962  * While submitting the concurrent request, The user may choose to display DFF fields in the report.
963  * Ref: ER# 2549463
964  */
965 PROCEDURE query_group_hierarchy(ERRBUF  OUT NOCOPY VARCHAR2,
966                       RETCODE OUT NOCOPY VARCHAR2,
967                       P_GROUP_NAME IN VARCHAR2 ,
968                       P_DISP_ROLE IN VARCHAR2,
969                       P_DISP_DFF_FIELDS IN VARCHAR2 )
970 is
971 
972 cursor group_id_cur
973    is
974  select group_id,
975         --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
976         attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
977         attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
978         --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
979   from  jtf_rs_groups_vl
980  where group_name = p_group_name;
981 group_id_rec group_id_cur%rowtype;
982 
983 cursor child_dtls_cur(l_group_id number)
984   is
985 select group_name,
986        start_date_active,
987       end_date_active,
988         --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
989         attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
990         attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
991         --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
992 from  jtf_rs_groups_vl
993 where group_id = l_group_id;
994 
995 --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
996 child_dtls_rec child_dtls_cur%rowtype;
997 --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
998 
999 /* Begin of Change - Bug # 1745032 */
1000 /*
1001 cursor members_cur(l_group_id number)
1002    is
1003 select mem.resource_id,
1004        res.resource_name,
1005        obj.name     category_name
1006  from  jtf_rs_group_members mem,
1007        jtf_rs_resource_extns_vl res,
1008        jtf_objects_vl  obj
1009 where  mem.group_id = l_group_id
1010   and  nvl(mem.delete_flag, 'N') <> 'Y'
1011   and  mem.resource_id = res.resource_id
1012   and  res.category    = obj.object_code;
1013 */
1014 
1015 cursor members_cur(l_group_id number)
1016    is
1017 select mem.resource_id,
1018        res.resource_name,
1019        rol.role_name,
1020        obj.name     category_name,
1021        rol.res_rl_start_date,
1022        rol.res_rl_end_date
1023  from  jtf_rs_group_members mem,
1024        jtf_rs_resource_extns_vl res,
1025        jtf_rs_defresroles_vl rol,
1026        jtf_objects_vl  obj
1027 where  mem.group_id = l_group_id
1028   and  nvl(mem.delete_flag, 'N') <> 'Y'
1029   and  mem.resource_id = res.resource_id
1030   and  mem.group_member_id = rol.role_resource_id(+)
1031   and  rol.role_resource_type(+) = 'RS_GROUP_MEMBER'
1032   and  nvl(rol.delete_flag, 'N') <> 'Y'
1033   -- and  trunc(sysdate) between nvl(rol.res_rl_start_date(+),sysdate) and nvl(rol.res_rl_end_date(+),sysdate)
1034   and  res.category    = obj.object_code;
1035 
1036 /* End of Change - Bug# 1745032 */
1037 
1038 members_rec members_cur%rowtype;
1039 l_group_id number;
1040 l_role_name jtf_rs_roles_vl.role_name%TYPE;
1041 l_sysdate DATE;
1042 
1043 l_child_tab rel_table;
1044 
1045 begin
1046 
1047 l_sysdate := TRUNC(SYSDATE);
1048 
1049 --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1050 /*
1051  * If the user chooses to display the DFFs AND the DFF definition is frozen
1052  * Cache the segment details in a local table.
1053  */
1054 IF (P_DISP_DFF_FIELDS = 'Y')
1055 THEN
1056     OPEN c_DFF_frozen;
1057     FETCH c_DFF_frozen INTO r_DFF_frozen;
1058     IF(c_DFF_frozen%found)
1059     THEN
1060       g_DFF_to_be_displayed := TRUE;
1061       all_segment_details_tab.delete;
1062       populate_segment_details_table('JTF', 'JTF_RS_GROUPS');
1063     END IF;
1064 CLOSE c_DFF_frozen;
1065 END IF;
1066 --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1067 open group_id_cur;
1068 fetch group_id_cur into group_id_rec;
1069 while(group_id_cur%found)
1070 loop
1071   fnd_file.put_line(fnd_file.log,'Group name: '||p_group_name);
1072   --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1073   IF (g_DFF_to_be_displayed)
1074   THEN
1075     get_DFF_output
1076     (  group_id_rec.ATTRIBUTE_CATEGORY,
1077        group_id_rec.ATTRIBUTE1,
1078        group_id_rec.ATTRIBUTE2,
1079        group_id_rec.ATTRIBUTE3,
1080        group_id_rec.ATTRIBUTE4,
1081        group_id_rec.ATTRIBUTE5,
1082        group_id_rec.ATTRIBUTE6,
1083        group_id_rec.ATTRIBUTE7,
1084        group_id_rec.ATTRIBUTE8,
1085        group_id_rec.ATTRIBUTE9,
1086        group_id_rec.ATTRIBUTE10,
1087        group_id_rec.ATTRIBUTE11,
1088        group_id_rec.ATTRIBUTE12,
1089        group_id_rec.ATTRIBUTE13,
1090        group_id_rec.ATTRIBUTE14,
1091        group_id_rec.ATTRIBUTE15,
1092        0
1093     );
1094   END IF;
1095   --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1096   l_group_id := group_id_rec.group_id;
1097   -- get members
1098   open members_cur(l_group_id);
1099   fetch  members_cur into members_rec;
1100   while(members_cur%found)
1101   loop
1102         --print members
1103       fnd_file.put_line(fnd_file.log,rpad('   Member :',15,' ') ||members_rec.resource_name);
1104       /* Begin of Add - Bug# 1745032 */
1105       if (p_disp_role ='Y') then
1106           l_role_name := members_rec.role_name;
1107         if members_rec.res_rl_start_date IS NOT NULL THEN
1108           if (l_sysdate NOT BETWEEN members_rec.res_rl_start_date
1109                               AND NVL (members_rec.res_rl_end_date, l_sysdate)) THEN
1110             l_role_name := NULL;
1111           end if;
1112         end if;
1113        fnd_file.put_line(fnd_file.log,rpad('     Role :',15,' ') ||l_role_name);
1114       end if;
1115       /* End  of Add - Bug# 1745032 */
1116       fnd_file.put_line(fnd_file.log,rpad(' Category :',15,' ') ||members_rec.category_name);
1117 
1118       fetch  members_cur into members_rec;
1119   end loop;
1120   close members_cur;
1121 
1122   populate_child_table(l_group_id);
1123   l_child_tab := g_child_tab;
1124 
1125   FOR I IN 1 .. l_child_tab.COUNT
1126   LOOP
1127       open child_dtls_cur(l_child_tab(i).p_group_id);
1128       --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1129       fetch child_dtls_cur into child_dtls_rec;
1130       --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1131 
1132      --get group name etc
1133       fnd_file.put_line(fnd_file.log,rpad('      Child Group name: ',27, ' ')||child_dtls_rec.group_name);
1134       fnd_file.put_line(fnd_file.log,rpad('      Level : ',27, ' ')||l_child_tab(i).level);
1135       fnd_file.put_line(fnd_file.log,rpad('      Effective : ',27, ' ')||l_child_tab(i).p_relation_start_date ||' -- '||l_child_tab(i).p_relation_end_date);
1136       --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1137       IF (g_DFF_to_be_displayed)
1138       THEN
1139         get_DFF_output
1140         (  child_dtls_rec.ATTRIBUTE_CATEGORY,
1141            child_dtls_rec.ATTRIBUTE1,
1142            child_dtls_rec.ATTRIBUTE2,
1143            child_dtls_rec.ATTRIBUTE3,
1144            child_dtls_rec.ATTRIBUTE4,
1145            child_dtls_rec.ATTRIBUTE5,
1146            child_dtls_rec.ATTRIBUTE6,
1147            child_dtls_rec.ATTRIBUTE7,
1148            child_dtls_rec.ATTRIBUTE8,
1149            child_dtls_rec.ATTRIBUTE9,
1150            child_dtls_rec.ATTRIBUTE10,
1151            child_dtls_rec.ATTRIBUTE11,
1152            child_dtls_rec.ATTRIBUTE12,
1153            child_dtls_rec.ATTRIBUTE13,
1154            child_dtls_rec.ATTRIBUTE14,
1155            child_dtls_rec.ATTRIBUTE15,
1156            6
1157         );
1158       END IF;
1159       --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1160       close child_dtls_cur;
1161 
1162      -- get members
1163       open members_cur(l_child_tab(i).p_group_id);
1164       fetch  members_cur into members_rec;
1165       while(members_cur%found)
1166       loop
1167         --print members
1168          fnd_file.put_line(fnd_file.log,rpad('       Member :',20,' ') ||members_rec.resource_name);
1169       /* Begin of Add - Bug# 1745032 */
1170          if (p_disp_role ='Y') then
1171            fnd_file.put_line(fnd_file.log,rpad('       Role   :',20,' ') ||members_rec.role_name);
1172          end if;
1173       /* End  of Add - Bug# 1745032 */
1174          fnd_file.put_line(fnd_file.log,rpad('     Category :',20,' ') ||members_rec.category_name);
1175 
1176          fetch  members_cur into members_rec;
1177       end loop;
1178       close members_cur;
1179   end loop; -- end of FOR loop
1180 fetch group_id_cur into group_id_rec;
1181 end loop;
1182 close group_id_cur;
1183 --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1184 IF (g_DFF_to_be_displayed <> FALSE)
1185 THEN
1186   all_segment_details_tab.delete;
1187   g_DFF_to_be_displayed := FALSE;
1188   g_DFF_prompt_max_length := 0;
1189 END IF;
1190 --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1191 end query_group_hierarchy;
1192 
1193 procedure child_groups(p_group_id in number,
1194                        p_usr_name in varchar2,
1195                        p_start_date in date,
1196                        p_end_date   in date,
1197                        p_usage      in varchar2)
1198 is
1199 
1200    CURSOR c_date(x_group_id   JTF_RS_GROUPS_VL.GROUP_ID%TYPE)
1201       IS
1202           SELECT grp.start_date_active,
1203                  grp.end_date_active
1204             FROM jtf_rs_groups_b grp
1205            WHERE grp.group_id = x_group_id;
1206 
1207   cursor  c_check_usage(l_group_id  number,
1208                         l_usage     varchar2)
1209       is
1210    select usg.usage
1211     from  jtf_rs_group_usages usg
1212    where  usg.usage = l_usage
1213      and  usg.group_id = l_group_id;
1214 
1215 
1216   r_check_usage c_check_usage%rowtype;
1217 
1218   cursor c_grp_child(l_group_id number,
1219                l_usr_name varchar2,
1220                l_start_date date,
1221                l_end_date   date)
1222    is
1223   select /*+ INDEX(gm jtf_rs_group_members_aud_nu3) */ g1.group_name,
1224        --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1225        g1.attribute_category, g1.attribute1, g1.attribute2, g1.attribute3, g1.attribute4, g1.attribute5, g1.attribute6, g1.attribute7, g1.attribute8,
1226        g1.attribute9, g1.attribute10, g1.attribute11, g1.attribute12, g1.attribute13, g1.attribute14, g1.attribute15,
1227        --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1228        ext.resource_number,
1229        ext.resource_name,
1230        ext.category,
1231        lkp.name category_name,
1232        to_char(gm.creation_date, 'DD-MON-YYYY:HH24:MI:SS') creation_date,
1233        fnd.user_name   created_by,
1234        decode(gm.old_group_id, g1.group_id, 'OUT', 'IN') moved,
1235        decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_number ) from_group_number,
1236        decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_name ) from_group_name,
1237        decode(gm.old_group_id, g1.group_id, g2.group_number, null)    to_group_number,
1238        decode(gm.old_group_id, g1.group_id, g2.group_name, null)    to_group_name,
1239        ext.resource_id,
1240        g1.group_id
1241   from
1242        jtf_rs_groups_vl g2,
1243        jtf_rs_groups_vl g3,
1244        jtf_rs_resource_extns_vl ext,
1245        jtf_objects_vl lkp ,
1246        fnd_user fnd,
1247        jtf_rs_group_members_aud gm,
1248        jtf_rs_group_members  mem,
1249        jtf_rs_groups_vl g1
1250  where g1.group_id = l_group_id
1251  and   mem.group_id  = g1.group_id
1252  and   gm.group_member_id = mem.group_member_id
1253  and   ( gm.new_group_id   = g1.group_id
1254            or gm.old_group_id = g1.group_id )
1255  and  gm.creation_date between nvl(l_start_date,sysdate)
1256                            and  nvl(l_end_date,sysdate)
1257  and   gm.new_group_id  =   g2.group_id(+)
1258  and   gm.old_group_id  =   g3.group_id(+)
1259  and  fnd.user_name like nvl(l_usr_name, '%')
1260  and  fnd.user_id = gm.created_by
1261    and  mem.resource_id = ext.resource_id
1262  and  ext.category = lkp.object_code
1263  order by g1.group_name , resource_name, gm.creation_date , fnd.user_name;
1264 
1265  r_grp_child c_grp_child%rowtype;
1266 
1267   l_child_tab rel_table;
1268 
1269   i BINARY_INTEGER := 0;
1270   j BINARY_INTEGER := 0;
1271 
1272   cursor to_grp_cur(l_group_id number,
1273                    l_resource_id number)
1274      is
1275   select gm.group_member_id,
1276         g1.group_id,
1277         g1.group_name,
1278         g1.group_number
1279  from   jtf_rs_group_members_aud gm,
1280          jtf_rs_groups_vl g1
1281  where  gm.old_group_id = l_group_id
1282  and    gm.new_resource_id = l_resource_id
1283  and     g1.group_id = gm.new_group_id;
1284 
1285  to_grp_rec to_grp_cur%rowtype;
1286  l_to_grp_number jtf_rs_groups_vl.group_number%type;
1287  l_to_grp_name jtf_rs_groups_vl.group_name%type;
1288 
1289 --Declare the variables
1290 --
1291     l_api_version CONSTANT NUMBER        :=1.0;
1292 
1293   /* Moved the initial assignment of below variable to inside begin */
1294     l_immediate_parent_flag VARCHAR2(1);
1295     l_date  Date;
1296     l_user_id  Number;
1297     l_login_id  Number;
1298     l_start_date Date;
1299     l_end_date Date;
1300   /* Moved the initial assignment of below variable to inside begin */
1301     l_return_status varchar2(1);
1302     l_msg_count   number;
1303     l_msg_data    varchar2(2000);
1304 
1305     l_usage_found number := 1;
1306 
1307 
1308     l_indent number := 0;
1309 
1310 
1311  BEGIN
1312 
1313     l_immediate_parent_flag  := 'N';
1314     l_return_status          := fnd_api.g_ret_sts_success;
1315 
1316    -- if no group id is passed in then raise error
1317    IF p_group_id IS NOT NULL
1318    THEN
1319 
1320   --fetch the start date and the end date for the group
1321    OPEN c_date(p_group_id);
1322    FETCH c_date INTO l_start_date, l_end_date;
1323    CLOSE c_date;
1324 
1325 
1326 
1327   --get all the child groups for this group
1328 
1329    POPULATE_CHILD_TABLE(p_group_id, l_start_date, l_end_date, 1);
1330    l_child_tab := g_child_tab;
1331 
1332    FOR I IN 1 .. l_child_tab.COUNT
1333    LOOP
1334 
1335 
1336             --assign start date and end date for which this relation is valid
1337 
1338             l_usage_found := 1;
1339             if(p_usage is not null)
1340             then
1341                 open c_check_usage(l_child_tab(i).p_group_id,
1342                                    p_usage);
1343                 fetch c_check_usage into r_check_usage;
1344                 if(c_check_usage%found)
1345                 then
1346                      l_usage_found := 1;
1347                 else
1348                      l_usage_found := 0;
1349                 end if;
1350                 close c_check_usage;
1351             end if;
1352 
1353 
1354            if(
1355                (l_usage_found = 1)
1356                )
1357            THEN
1358              --get audit report
1359 
1360                   open c_grp_child(l_child_tab(i).p_group_id,
1361                              p_usr_name,
1362                              p_start_date,
1363                              p_end_date);
1364 
1365                   fetch c_grp_child into r_grp_child;
1366                   if(c_grp_child%found)
1367                   then
1368                       l_indent := l_child_tab(i).level * 3 + 15;
1369                       fnd_file.put_line(fnd_file.log,lpad('Group name: ',l_indent,' ')||r_grp_child.group_name);
1370                       --<Start> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1371                       IF (g_DFF_to_be_displayed)
1372                       THEN
1373                         get_DFF_output
1374                         (  r_grp_child.ATTRIBUTE_CATEGORY,
1375                            r_grp_child.ATTRIBUTE1,
1376                            r_grp_child.ATTRIBUTE2,
1377                            r_grp_child.ATTRIBUTE3,
1378                            r_grp_child.ATTRIBUTE4,
1379                            r_grp_child.ATTRIBUTE5,
1380                            r_grp_child.ATTRIBUTE6,
1381                            r_grp_child.ATTRIBUTE7,
1382                            r_grp_child.ATTRIBUTE8,
1383                            r_grp_child.ATTRIBUTE9,
1384                            r_grp_child.ATTRIBUTE10,
1385                            r_grp_child.ATTRIBUTE11,
1386                            r_grp_child.ATTRIBUTE12,
1387                            r_grp_child.ATTRIBUTE13,
1388                            r_grp_child.ATTRIBUTE14,
1389                            r_grp_child.ATTRIBUTE15,
1390                            l_indent - 12
1391                         );
1392                       END IF;
1393                       --<End> Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
1394                       --fnd_file.put_line(fnd_file.log,'Level: '||l_child_tab(i).level);
1395 
1396                   end if;
1397                   while (c_grp_child%FOUND)
1398                   loop
1399                       if(r_grp_child.moved = 'OUT')
1400                        then
1401                           open  to_grp_cur( r_grp_child.group_id,
1402                                 r_grp_child.resource_id)  ;
1403                           fetch to_grp_cur into to_grp_rec;
1404                           l_to_grp_number := to_grp_rec.group_number;
1405                           l_to_grp_name := to_grp_rec.group_name;
1406                           close to_grp_cur;
1407                        else
1408                          l_to_grp_number := null;
1409                          l_to_grp_name := null;
1410                       end if;
1411 
1412                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Resource Name: ',27, ' '),15+l_indent, ' ')||r_grp_child.resource_name);
1413                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Resource Number: ',27, ' '),15+l_indent, ' ')||r_grp_child.resource_number);
1414                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Category: ',27, ' '),15+l_indent, ' ')||r_grp_child.category_name);
1415                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Date: ',27, ' '),15+l_indent, ' ')||r_grp_child.creation_date);
1416                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Updated By: ',27, ' '),15+l_indent, ' ')||r_grp_child.created_by);
1417                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Action: ',27, ' '),15+l_indent, ' ')||r_grp_child.moved);
1418                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Moved From Group #:',27, ' '),15+l_indent, ' ')||r_grp_child.from_group_number);
1419                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Moved From Group Name:',27, ' '),15+l_indent, ' ')||r_grp_child.from_group_name);
1420                     --  fnd_file.put_line(fnd_file.log,lpad(rpad('  Moved To Group #:',27, ' '),15+l_indent, ' ')||r_grp_child.to_group_number);
1421                      -- fnd_file.put_line(fnd_file.log,lpad(rpad('  Moved to Group Name: ',27, ' '),15+l_indent, ' ')||r_grp_child.to_group_name);
1422                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Moved To Group #:',27, ' '),15+l_indent,' ')||l_to_grp_number);
1423                       fnd_file.put_line(fnd_file.log,lpad(rpad('  Moved to Group Name: ',27, ' '),15+l_indent,' ')||l_to_grp_name);
1424                       fnd_file.new_line(fnd_file.log, 1);
1425                       fetch c_grp_child into r_grp_child;
1426 
1427                   end loop; -- end of if
1428                   close c_grp_child;
1429 
1430            END IF; -- end of start date < end date check
1431 
1432    END LOOP;
1433 
1434  END IF; -- end of group_id not cull check
1435 end child_groups;
1436 end jtf_rs_group_report_pub;