[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GRP_SUM_PUB
Source
1 Package Body JTF_RS_GRP_SUM_PUB AS
2 /* $Header: jtfrssgb.pls 120.1 2006/02/09 15:48:00 baianand noship $ */
3
4 ---------------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_RS_GRP_SUM_PUB
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Get group details for Group Summary Screen (jsp)
11 -- NOTES
12 -- This package is publicly available for use
13 --
14 -- HISTORY
15 -- 04/30/2001 NSINGHAI Created
16 -- 04/17/2002 SURAWAT Modified - Added the logic for "Last"
17 -- function. At the beginning of each new set, the
18 -- previous set is flushed out of the PL/SQL table.
19 -- 07/15/2002 ASACHAN Fixed problem of next button getting wrongly
20 -- disabled.
21 -- End of Comments
22 --
23 --
24 -- ***************************************************
25 -- GLOBAL VARIABLES
26 -- ***************************************************
27 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_RS_GRP_SUM_PUB';
28 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfrssgb.pls';
29
30 G_NEW_LINE VARCHAR2(02) := FND_GLOBAL.Local_Chr(10);
31 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
32 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
33 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
34 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
35 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
36 G_APP_SHORT_NAME VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
37
38 -- ***************************************************
39 -- start of comments
40 -- ***************************************************
41 -- api name : Get Group Summary
42 -- type : public.
43 -- function : Get the Groups summary information
44 -- pre-reqs : depends on jtf_rs_groups_vl
45 -- parameters :
46 -- end of comments
47
48 procedure Get_Group
49 ( p_range_low IN NUMBER,
50 p_range_high IN NUMBER,
51 p_called_from IN VARCHAR2,
52 p_user_id IN NUMBER,
53 p_group_name IN VARCHAR2,
54 p_group_number IN VARCHAR2,
55 p_group_desc IN VARCHAR2,
56 p_group_email IN VARCHAR2,
57 p_from_date IN VARCHAR2,
58 p_to_date IN VARCHAR2,
59 p_date_format IN VARCHAR2,
60 p_group_id IN NUMBER,
61 p_group_usage IN VARCHAR2,
62 x_total_rows OUT NOCOPY NUMBER,
63 x_result_tbl OUT NOCOPY grp_sum_tbl_type)
64
65 IS
66
67 cursor main_uid_cur(l_uid number) is
68 select mem.group_id group_id,
69 grp.group_name group_name,
70 upper(grp.group_name) u_group_name,
71 grp.group_desc group_desc,
72 grp.group_number group_number,
73 grp.start_date_active start_date_active,
74 grp.end_date_active end_date_active
75 from jtf_rs_group_members mem,
76 jtf_rs_groups_vl grp
77 where mem.resource_id in
78 (select rsc.resource_id
79 from jtf_rs_resource_extns rsc
80 where rsc.user_id = l_uid)
81 and nvl(mem.delete_flag,'N') = 'N'
82 and mem.group_id = grp.group_id
83 order by u_group_name, mem.group_id ;
84
85 r_main_cur main_uid_cur%rowtype;
86
87 cursor main_qf_cur(l_group varchar2) is
88 select group_id,
89 group_name,
90 upper(group_name) u_group_name,
91 group_desc,
92 group_number ,
93 start_date_active,
94 end_date_active
95 from jtf_rs_groups_vl
96 where upper(group_name) like l_group
97 union
98 select group_id,
99 group_name,
100 upper(group_name) u_group_name,
101 group_desc,
102 group_number,
103 start_date_active,
104 end_date_active
105 from jtf_rs_groups_vl
106 where group_number like l_group
107 /* union
108 select group_id,
109 group_name,
110 upper(group_name) u_group_name,
111 group_desc,
112 group_number,
113 start_date_active,
114 end_date_active
115 from jtf_rs_groups_vl
116 where group_id = l_grp_id
117 */ order by u_group_name, group_id ;
118
119 cursor get_parent_group(l_par_group_id number) is
120 select grl.related_group_id,
121 grp.group_name,
122 upper(grp.group_name) u_group_name
123 from jtf_rs_grp_relations grl,
124 jtf_rs_groups_vl grp
125 where grl.group_id = l_par_group_id
126 and trunc(sysdate) between grl.start_date_active and nvl(grl.end_date_active,sysdate)
127 and nvl(grl.delete_flag,'N') = 'N'
128 and grl.related_group_id = grp.group_id
129 order by u_group_name, grl.related_group_id ;
130
131 r_get_parent_group get_parent_group%rowtype;
132
133 cursor get_child_group(l_child_group_id number) is
134 select grl.group_id,
135 grp.group_name,
136 upper(grp.group_name) u_group_name
137 from jtf_rs_grp_relations grl,
138 jtf_rs_groups_vl grp
139 where grl.related_group_id = l_child_group_id
140 and trunc(sysdate) between grl.start_date_active and nvl(grl.end_date_active,sysdate)
141 and nvl(grl.delete_flag,'N') = 'N'
142 and grl.group_id = grp.group_id
143 order by u_group_name,grl.group_id ;
144
145 r_get_child_group get_child_group%rowtype;
146
147 l_date_format varchar2(15) := p_date_format ;
148 l_index NUMBER := 0;
149 l_user_id NUMBER := nvl(FND_PROFILE.VALUE('USER_ID'),-1);
150 l_group_name VARCHAR2(70) := UPPER(p_group_name)||'%';
151 l_group_number VARCHAR2(40) := p_group_number ||'%';
152 l_group_id NUMBER := p_group_id;
153 l_group_desc VARCHAR2(250) := UPPER(p_group_desc) || '%';
154 l_group_email VARCHAR2(250) := UPPER(p_group_email) || '%';
155 l_from_date VARCHAR2(15) := to_char(to_date(p_from_date,p_date_format),'DD-MM-RRRR');
156 l_to_date VARCHAR2(15) := to_char(to_date(p_to_date,p_date_format),'DD-MM-RRRR');
157 l_range_high NUMBER;
158 TYPE group_qry_cur IS REF CURSOR;
159 main_as_cur group_qry_cur;
160 l_qry VARCHAR2(2000);
161 l_bind_counter NUMBER := 1;
162 TYPE bind_rec_type IS record (bind_value varchar2(500));
163 TYPE bind_tbl_type IS table OF bind_rec_type
164 INDEX BY binary_integer;
165 bind_table bind_tbl_type;
166 i integer := 1;
167 l_group_usage VARCHAR2(240) := p_group_usage;
168
169 init_tbl_type grp_sum_tbl_type;
170
171 l_has_more_records BOOLEAN := FALSE;
172 BEGIN
173 x_total_rows := 1;
174 l_range_high := p_range_high + 1;
175
176 IF (p_called_from = 'DEFAULT' )THEN
177 IF p_user_id is null THEN
178 l_user_id := nvl(FND_PROFILE.VALUE('USER_ID'),-1);
179 ELSE l_user_id := p_user_id;
180 END IF;
181 END IF;
182 IF (p_called_from = 'AS') THEN
183 l_qry := ' ';
184 l_qry := ' select grp.group_id, grp.group_name, upper(grp.group_name) u_group_name, grp.group_desc, grp.group_number, grp.start_date_active, grp.end_date_active ';
185 -- l_qry := l_qry||' from jtf_rs_groups_vl where upper(group_name) like :b_group_name ' ;
186 -- i := l_bind_counter ;
187 -- bind_table(i).bind_value := l_group_name;
188
189 IF (p_group_usage IS NULL) THEN
190 l_qry := l_qry||' from jtf_rs_groups_vl grp ';
191 ELSE
192 l_qry := l_qry||' from jtf_rs_group_usages gu, jtf_rs_groups_vl grp ';
193 END IF;
194
195 l_qry := l_qry||' where upper(grp.group_name) like :b_group_name ' ;
196 i := l_bind_counter ;
197 bind_table(i).bind_value := l_group_name;
198
199 IF p_group_usage IS NOT NULL THEN
200 l_qry := l_qry||' and gu.usage = :b_usage ';
201 l_qry := l_qry||' and grp.group_id = gu.group_id ';
202 l_bind_counter := l_bind_counter + 1 ;
203 i := l_bind_counter ;
204 bind_table(i).bind_value := l_group_usage ;
205 END IF;
206
207 IF p_group_number IS NOT NULL THEN
208 --l_qry := l_qry||' and group_number like '||''''||l_group_number||'''';
209 l_qry := l_qry||' and grp.group_number like :b_group_number ';
210 l_bind_counter := l_bind_counter + 1 ;
211 i := l_bind_counter ;
212 bind_table(i).bind_value := l_group_number ;
213 END IF;
214
215 IF p_group_desc IS NOT NULL THEN
216 --l_qry := l_qry||' and upper(group_desc) like '||''''||l_group_desc||'''' ;
217 l_qry := l_qry||' and upper(grp.group_desc) like :b_group_desc ' ;
218 l_bind_counter := l_bind_counter + 1 ;
219 i := l_bind_counter ;
220 bind_table(i).bind_value := l_group_desc ;
221 END IF;
222
223 IF p_group_email IS NOT NULL THEN
224 --l_qry := l_qry||' and upper(email_address) like '||''''||l_group_email||'''' ;
225 l_qry := l_qry||' and upper(grp.email_address) like :b_group_email ' ;
226 l_bind_counter := l_bind_counter + 1 ;
227 i := l_bind_counter ;
228 bind_table(i).bind_value := l_group_email ;
229 END IF;
230
231 IF ((p_from_date IS NOT NULL) OR (p_to_date IS NOT NULL))THEN
232 IF p_from_date IS NULL THEN
233 l_from_date := '01-01-1800';
234 ELSE l_from_date := l_from_date ;
235 END IF;
236
237 IF p_to_date IS NULL THEN
238 l_to_date := '31-12-4712';
239 ELSE l_to_date := l_to_date ;
240 END IF;
241
242 -- l_qry := l_qry||' and (( start_date_active between to_date('||''''||l_from_date||''''||','||''''||'DD-MM-RRRR'||''''
243 -- ||') and to_date('||''''||l_to_date||''''||','||''''||'DD-MM-RRRR'||''''||'))';
244 -- l_qry := l_qry||' OR (start_date_active < to_date('||''''||l_from_date||''''||','||''''||'DD-MM-RRRR'||''''
245 -- ||') and (end_date_active IS NULL OR ';
246 -- l_qry := l_qry||' end_date_active >= to_date('||''''||l_from_date||''''||','||''''||'DD-MM-RRRR'||''''||')))) ';
247
248 l_qry := l_qry||' and (( grp.start_date_active between to_date(:b_from_date '||','||''''||'DD-MM-RRRR'||''''
249 ||') and to_date(:b_to_date '||','||''''||'DD-MM-RRRR'||''''||'))';
250 l_qry := l_qry||' OR (grp.start_date_active < to_date(:b_from_date '||','||''''||'DD-MM-RRRR'||''''
251 ||') and (grp.end_date_active IS NULL OR ';
252 l_qry := l_qry||' grp.end_date_active >= to_date(:b_from_date '||','||''''||'DD-MM-RRRR'||''''||')))) ';
253
254 l_bind_counter := l_bind_counter + 1 ;
255 i := l_bind_counter ;
256 bind_table(i).bind_value := l_from_date ;
257 bind_table(i+1).bind_value := l_to_date ;
258 bind_table(i+2).bind_value := l_from_date ;
259 bind_table(i+3).bind_value := l_from_date ;
260 END IF;
261
262 l_qry := l_qry||' order by u_group_name, grp.group_id ' ;
263
264 l_bind_counter := bind_table.COUNT;
265
266 -- dbms_output.put_line(substr(l_qry,1,200));
267 -- dbms_output.put_line(substr(l_qry,201,400));
268 -- dbms_output.put_line(substr(l_qry,401,600));
269
270 END IF;
271
272 -- Open Cursor based on passed parameter
273 IF (p_called_from = 'QF') THEN
274 OPEN main_qf_cur(l_group_name);
275 ELSIF (p_called_from = 'AS') THEN
276 IF (l_bind_counter = 1) THEN
277 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value;
278 ELSIF (l_bind_counter = 2) THEN
279 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value, bind_table(2).bind_value;
280 ELSIF (l_bind_counter = 3) THEN
281 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value, bind_table(2).bind_value, bind_table(3).bind_value;
282 ELSIF (l_bind_counter = 4) THEN
283 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value, bind_table(2).bind_value, bind_table(3).bind_value, bind_table(4).bind_value ;
284 ELSIF (l_bind_counter = 5) THEN
285 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value, bind_table(2).bind_value, bind_table(3).bind_value, bind_table(4).bind_value ,bind_table(5).bind_value ;
286 ELSIF (l_bind_counter = 6) THEN
287 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value, bind_table(2).bind_value, bind_table(3).bind_value, bind_table(4).bind_value ,bind_table(5).bind_value, bind_table(6).bind_value;
288 ELSIF (l_bind_counter = 7) THEN
289 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value, bind_table(2).bind_value, bind_table(3).bind_value, bind_table(4).bind_value ,bind_table(5).bind_value, bind_table(6).bind_value, bind_table(7).bind_value;
290 ELSIF (l_bind_counter = 8) THEN
291 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value, bind_table(2).bind_value, bind_table(3).bind_value, bind_table(4).bind_value ,bind_table(5).bind_value, bind_table(6).bind_value, bind_table(7).bind_value, bind_table(8).bind_value;
292 ELSIF (l_bind_counter = 9) THEN
293 OPEN main_as_cur FOR l_qry USING bind_table(1).bind_value, bind_table(2).bind_value, bind_table(3).bind_value, bind_table(4).bind_value ,bind_table(5).bind_value,
294 bind_table(6).bind_value, bind_table(7).bind_value, bind_table(8).bind_value, bind_table(9).bind_value;
295 END IF;
296 ELSE
297 OPEN main_uid_cur(l_user_id);
298 END IF;
299
300 LOOP
301
302 exit when l_has_more_records = TRUE;
303
304 -- Fetch Cursor based on passed parameter
305 IF (p_called_from = 'QF') THEN
306 FETCH main_qf_cur INTO r_main_cur;
307 IF main_qf_cur%notfound THEN
308 x_total_rows := x_total_rows - 1;
309 exit;
310 END IF;
311 ELSIF (p_called_from = 'AS') THEN
312 FETCH main_as_cur INTO r_main_cur;
313 IF main_as_cur%notfound THEN
314 x_total_rows := x_total_rows - 1;
315 exit;
316 END IF;
317 ELSE
318 FETCH main_uid_cur INTO r_main_cur;
319 IF main_uid_cur%notfound THEN
320 x_total_rows := x_total_rows - 1;
321 exit;
322 END IF;
323 END IF;
324
325 exit when x_total_rows = l_range_high;
326
327 OPEN get_parent_group(r_main_cur.group_id);
328 r_get_parent_group.group_name := NULL;
329 r_get_parent_group.related_group_id := NULL;
330 FETCH get_parent_group into r_get_parent_group;
331 CLOSE get_parent_group;
332
333 OPEN get_child_group(r_main_cur.group_id);
334 FETCH get_child_group into r_get_child_group;
335
336 IF (get_child_group%NOTFOUND) THEN
337 IF (x_total_rows between p_range_low and p_range_high) OR (p_range_high = -1) THEN
338 IF (p_range_high = -1) AND (mod(l_index, p_range_low) = 0) THEN
339 x_result_tbl := init_tbl_type;
340 l_index := 0;
341 END IF;
342
343 l_index := l_index + 1;
344 x_result_tbl(l_index).group_id := r_main_cur.group_id ;
345 x_result_tbl(l_index).group_name := r_main_cur.group_name ;
346 x_result_tbl(l_index).group_desc := r_main_cur.group_desc ;
347 x_result_tbl(l_index).group_number := r_main_cur.group_number ;
348 x_result_tbl(l_index).start_date_active := r_main_cur.start_date_active ;
349 x_result_tbl(l_index).end_date_active := r_main_cur.end_date_active ;
350 x_result_tbl(l_index).start_date_active := r_main_cur.start_date_active ;
351 x_result_tbl(l_index).parent_group := r_get_parent_group.group_name ;
352 x_result_tbl(l_index).parent_group_id := r_get_parent_group.related_group_id ;
353 END IF;
354
355 x_total_rows := x_total_rows + 1;
356
357 ELSE
358 LOOP
359 IF (x_total_rows between p_range_low and p_range_high) OR (p_range_high = -1) THEN
360 IF (p_range_high = -1) AND (mod(l_index, p_range_low) = 0) THEN
361 x_result_tbl := init_tbl_type;
362 l_index := 0;
363 END IF;
364
365 l_index := l_index + 1;
366 x_result_tbl(l_index).group_id := r_main_cur.group_id ;
367 x_result_tbl(l_index).group_name := r_main_cur.group_name ;
368 x_result_tbl(l_index).group_desc := r_main_cur.group_desc ;
369 x_result_tbl(l_index).group_number := r_main_cur.group_number ;
370 x_result_tbl(l_index).start_date_active := r_main_cur.start_date_active ;
371 x_result_tbl(l_index).end_date_active := r_main_cur.end_date_active ;
372 x_result_tbl(l_index).start_date_active := r_main_cur.start_date_active ;
373 x_result_tbl(l_index).parent_group := r_get_parent_group.group_name ;
374 x_result_tbl(l_index).parent_group_id := r_get_parent_group.related_group_id ;
375 x_result_tbl(l_index).child_group := r_get_child_group.group_name ;
376 x_result_tbl(l_index).child_group_id := r_get_child_group.group_id ;
377 END IF;
378
379 x_total_rows := x_total_rows + 1;
380 FETCH get_child_group into r_get_child_group;
381 exit when get_child_group%notfound;
382 IF (x_total_rows = l_range_high) THEN
383 l_has_more_records := TRUE;
384 exit;
385 END IF;
386 END LOOP;
387 END IF;
388 CLOSE get_child_group;
389 END LOOP; -- of Fetch Cursor based on passed parameter
390
391 IF (p_called_from = 'QF') THEN
392 CLOSE main_qf_cur ;
393 ELSIF (p_called_from = 'AS') THEN
394 CLOSE main_as_cur ;
395 ELSE
396 CLOSE main_uid_cur;
397 END IF;
398
399 END Get_Group;
400
401 END JTF_RS_GRP_SUM_PUB;