[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_JSP_LOV_RECS_PUB
Source
1 Package Body JTF_RS_JSP_LOV_RECS_PUB AS
2 /* $Header: jtfrsjlb.pls 120.2 2006/03/10 15:52:26 nsinghai ship $ */
3
4 ---------------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_RS_GET_RSC_NAMES_PUB
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Get resource details for JSP LOV Screens
11 -- NOTES
12 -- This package is publicly available for use
13 --
14 -- HISTORY
15 -- 04/30/2001 EIHSU Created
16 -- End of Comments
17 --
18 --
19 -- ***************************************************
20 -- GLOBAL VARIABLES
21 -- ***************************************************
22 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_RS_JSP_LOV_RECS_PUB';
23 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfrsjlb.pls';
24
25 G_NEW_LINE CONSTANT VARCHAR2(02) := FND_GLOBAL.Local_Chr(10);
26 G_APPL_ID CONSTANT NUMBER := FND_GLOBAL.Prog_Appl_Id;
27 G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.Conc_Login_Id;
28 G_PROGRAM_ID CONSTANT NUMBER := FND_GLOBAL.Conc_Program_Id;
29 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_Id;
30 G_REQUEST_ID CONSTANT NUMBER := FND_GLOBAL.Conc_Request_Id;
31 G_APP_SHORT_NAME CONSTANT VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
32
33 --TYPE GenericCurTyp IS REF CURSOR;
34 TYPE Lov_output_cur_type IS REF CURSOR ;--RETURN lov_output_rec_type;
35
36 Procedure Get_LOV_Records
37 ( p_range_low IN NUMBER,
38 p_range_high IN NUMBER,
39 p_record_group_name IN VARCHAR2, -- name of the data to fetch
40 p_in_filter_lov_rec IN lov_input_rec_type,
41 p_in_filter1 IN VARCHAR2,
42 p_in_filter2 IN VARCHAR2,
43 x_total_rows OUT NOCOPY NUMBER,
44 x_more_data_flag OUT NOCOPY VARCHAR2,
45 x_lov_ak_region OUT NOCOPY VARCHAR2,
46 x_result_tbl OUT NOCOPY lov_output_tbl_type,
47 x_ext_col_cnt OUT NOCOPY NUMBER
48 )
49 IS
50 -- Our generic cursor
51 lov_output_cur Lov_output_cur_type;
52 --lov_output_cur GenericCurTyp;
53
54 -- filtering record type
55 l_in_filter_lov_rec lov_input_rec_type;
56
57 -- Processed cursor inputs
58 l_display_value varchar2(2000);
59 l_code_value varchar2(100);
60 l_aux_value1 varchar2(2000);
61 l_aux_value2 varchar2(2000);
62 l_aux_value3 varchar2(2000);
63
64 /* Moved the initial assignment of below variables to inside begin */
65 l_in_filter1 VARCHAR2(100);
66 l_in_filter2 VARCHAR2(100);
67 l_filter_number1 NUMBER;
68 l_filter_number2 NUMBER;
69 -- Cursor iteration variables
70 l_index NUMBER := 0;
71 l_range_high NUMBER;
72 rec lov_output_rec_type;
73
74 /* Moved the initial assignment of below variables to inside begin */
75 l_new_low_value number;
76 l_new_high_value number;
77 l_total_count number := 0;
78 l_rec_set number := 1;
79 l_start number := 0;
80 l_more_data varchar2(1) ;
81 l_org_id number;
82 l_catset number;
83
84 BEGIN
85
86 l_in_filter1 := p_in_filter1;
87 l_in_filter2 := p_in_filter2;
88 l_new_low_value := p_range_low ;
89 l_new_high_value := p_range_high ;
90
91 l_in_filter_lov_rec := p_in_filter_lov_rec;
92 x_ext_col_cnt := 0; -- setting to default count of extra columns
93
94 IF (p_range_high < 0) THEN
95 l_rec_set := ABS(p_range_high);
96 END IF;
97
98 If p_record_group_name = 'RESOURCE_NAMES' then
99 -- Resource Names
100 x_lov_ak_region := 'JTF_RS_RES_LOV_REGION';
101 -- input resource_name
102 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
103 -- input resource_number
104 l_aux_value1 := NVL(l_in_filter_lov_rec.aux_value1,'%');
105 -- input resource_category
106 l_aux_value2 := NVL(l_in_filter_lov_rec.aux_value2,'%');
107 if l_in_filter1 is null then
108
109 -- Get the total count if "Last" hyperlink is clicked
110 IF (p_range_high < 0) THEN
111 SELECT count(*)
112 INTO l_total_count
113 from JTF_RS_RESOURCE_EXTNS_VL RES, FND_LOOKUPS FL
114 where category like l_aux_value2
115 and resource_number like l_aux_value1
116 and UPPER(resource_name) like l_display_value
117 and trunc(sysdate) <= nvl(res.end_date_active,trunc(sysdate))
118 and FL.lookup_type = 'JTF_RS_RESOURCE_CATEGORY'
119 and fl.lookup_code = res.category ;
120
121 l_start := MOD(l_total_count, l_rec_set);
122
123 IF (l_start = 0) THEN
124 l_new_low_value := l_total_count - l_rec_set + 1;
125 l_new_high_value := l_total_count ;
126 ELSE
127 l_new_low_value := l_total_count - l_start + 1;
128 l_new_high_value := l_total_count ;
129 END IF;
130
131 END IF;
132
133 OPEN lov_output_cur FOR
134
135 SELECT resource_name display_value,
136 to_char(resource_id) code_value,
137 resource_number aux_value1,
138 fl.lookup_code aux_value2,
139 fl.meaning aux_value3,
140 null ext_value1,
141 null ext_value2,
142 null ext_value3,
143 null ext_value4,
144 null ext_value5
145 from JTF_RS_RESOURCE_EXTNS_VL RES, FND_LOOKUPS FL
146 where category like l_aux_value2
147 and resource_number like l_aux_value1
148 and UPPER(resource_name) like l_display_value
149 and trunc(sysdate) <= nvl(res.end_date_active,trunc(sysdate))
150 and FL.lookup_type = 'JTF_RS_RESOURCE_CATEGORY'
151 and fl.lookup_code = res.category
152 order by resource_name;
153 else
154 l_filter_number1 := to_number(l_in_filter1);
155 -- dbms_output.put_line('l_filter_number1: ' ||l_filter_number1);
156
157 -- Get the total count if "Last" hyperlink is clicked
158 IF (p_range_high < 0) THEN
159 SELECT count(*)
160 INTO l_total_count
161 from JTF_RS_RESOURCE_EXTNS_VL RES, FND_LOOKUPS FL
162 where resource_id <> l_filter_number1
163 and category like l_aux_value2
164 and resource_number like l_aux_value1
165 and UPPER(resource_name) like l_display_value
166 and trunc(sysdate) <= nvl(res.end_date_active,trunc(sysdate))
167 and FL.lookup_type = 'JTF_RS_RESOURCE_CATEGORY'
168 and fl.lookup_code = res.category ;
169
170 l_start := MOD(l_total_count, l_rec_set);
171
172 IF (l_start = 0) THEN
173 l_new_low_value := l_total_count - l_rec_set + 1;
174 l_new_high_value := l_total_count ;
175 ELSE
176 l_new_low_value := l_total_count - l_start + 1;
177 l_new_high_value := l_total_count ;
178 END IF;
179
180 END IF;
181
182 OPEN lov_output_cur FOR
183 SELECT resource_name display_value,
184 to_char(resource_id) code_value,
185 resource_number aux_value1,
186 fl.lookup_code aux_value2,
187 fl.meaning aux_value3,
188 null ext_value1,
189 null ext_value2,
190 null ext_value3,
191 null ext_value4,
192 null ext_value5
193 from JTF_RS_RESOURCE_EXTNS_VL RES, FND_LOOKUPS FL
194 where resource_id <> l_filter_number1
195 and category like l_aux_value2
196 and resource_number like l_aux_value1
197 and UPPER(resource_name) like l_display_value
198 and trunc(sysdate) <= nvl(res.end_date_active,trunc(sysdate))
199 and FL.lookup_type = 'JTF_RS_RESOURCE_CATEGORY'
200 and fl.lookup_code = res.category
201 order by resource_name;
202 end if;
203
204 elsif p_record_group_name = 'MANAGER_NAMES' then
205 -- Resource Names
206 x_lov_ak_region := 'JTF_RS_MANAGER_LOV_REGION';
207 -- input manager_name
208 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
209 -- input org_id
210 l_aux_value1 := l_in_filter_lov_rec.aux_value1;
211 l_aux_value2 := l_in_filter_lov_rec.aux_value2;
212 if l_in_filter1 is null then
213
214 -- Get the total count if "Last" hyperlink is clicked
215 IF (p_range_high < 0) THEN
216 SELECT count(*)
217 INTO l_total_count
218 from JTF_RS_RESOURCE_EXTNS_VL RES
219 where UPPER(resource_name) like l_display_value
220 and res.source_business_grp_id = to_number(l_aux_value2)
221 and trunc(sysdate) <= nvl(res.end_date_active,trunc(sysdate))
222 and res.category = 'EMPLOYEE' ;
223
224 l_start := MOD(l_total_count, l_rec_set);
225
226 IF (l_start = 0) THEN
227 l_new_low_value := l_total_count - l_rec_set + 1;
228 l_new_high_value := l_total_count ;
229 ELSE
230 l_new_low_value := l_total_count - l_start + 1;
231 l_new_high_value := l_total_count ;
232 END IF;
233
234 END IF;
235
236 OPEN lov_output_cur FOR
237 SELECT resource_name display_value,
238 to_char(resource_id) code_value,
239 source_number aux_value1,
240 res.source_business_grp_id aux_value2,
241 null aux_value3,
242 null ext_value1,
243 null ext_value2,
244 null ext_value3,
245 null ext_value4,
246 null ext_value5
247 from JTF_RS_RESOURCE_EXTNS_VL RES
248 where UPPER(resource_name) like l_display_value
249 and res.source_business_grp_id = to_number(l_aux_value2)
250 and trunc(sysdate) <= nvl(res.end_date_active,trunc(sysdate))
251 and res.category = 'EMPLOYEE'
252 order by resource_name;
253 else
254 l_filter_number1 := to_number(l_in_filter1);
255 -- dbms_output.put_line('l_filter_number1: ' ||l_filter_number1);
256
257 -- Get the total count if "Last" hyperlink is clicked
258 IF (p_range_high < 0) THEN
259 SELECT count(*)
260 INTO l_total_count
261 from JTF_RS_RESOURCE_EXTNS_VL RES
262 where resource_id <> l_filter_number1
263 and res.source_business_grp_id = to_number(l_aux_value2)
264 and UPPER(resource_name) like l_display_value
265 and trunc(sysdate) <= nvl(res.end_date_active,trunc(sysdate))
266 and res.category = 'EMPLOYEE' ;
267
268 l_start := MOD(l_total_count, l_rec_set);
269
270 IF (l_start = 0) THEN
271 l_new_low_value := l_total_count - l_rec_set + 1;
272 l_new_high_value := l_total_count ;
273 ELSE
274 l_new_low_value := l_total_count - l_start + 1;
275 l_new_high_value := l_total_count ;
276 END IF;
277
278 END IF;
279
280 OPEN lov_output_cur FOR
281 SELECT resource_name display_value,
282 to_char(resource_id) code_value,
283 source_number aux_value1,
284 res.source_business_grp_id aux_value2,
285 null aux_value3,
286 null ext_value1,
287 null ext_value2,
288 null ext_value3,
289 null ext_value4,
290 null ext_value5
291 from JTF_RS_RESOURCE_EXTNS_VL RES
292 where resource_id <> l_filter_number1
293 and res.source_business_grp_id = to_number(l_aux_value2)
294 and UPPER(resource_name) like l_display_value
295 and trunc(sysdate) <= nvl(res.end_date_active,trunc(sysdate))
296 and res.category = 'EMPLOYEE'
297 order by resource_name;
298 end if;
299
300 elsif p_record_group_name = 'GROUP_NAMES' then
301 -- Group Names
302 x_lov_ak_region := 'JTF_RS_GROUP_LOV_REGION';
303 -- input group_name
304 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
305 -- input group_number
306 l_aux_value1 := l_in_filter_lov_rec.aux_value1 || '%';
307 l_filter_number1 := to_number(nvl(l_in_filter1, '-99'));
308
309 -- Get the total count if "Last" hyperlink is clicked
310 IF (p_range_high < 0) THEN
311 SELECT count(*)
312 INTO l_total_count
313 from jtf_rs_groups_vl
314 where UPPER(group_name) like l_display_value
315 and group_number like l_aux_value1
316 and trunc(sysdate) <= NVL(end_date_active, sysdate)
317 and group_id <> l_filter_number1 ;
318
319 l_start := MOD(l_total_count, l_rec_set);
320
321 IF (l_start = 0) THEN
322 l_new_low_value := l_total_count - l_rec_set + 1;
323 l_new_high_value := l_total_count ;
324 ELSE
325 l_new_low_value := l_total_count - l_start + 1;
326 l_new_high_value := l_total_count ;
327 END IF;
328
329 END IF;
330
331
332 OPEN lov_output_cur FOR
333 SELECT group_name, to_char(group_id), group_number, group_desc, null, null, null, null, null, null
334 from jtf_rs_groups_vl
335 where UPPER(group_name) like l_display_value
336 and group_number like l_aux_value1
337 and trunc(sysdate) <= NVL(end_date_active, sysdate)
338 and group_id <> l_filter_number1
339 order by group_name;
340
341 elsif p_record_group_name = 'ROLES' then
342 -- Role Names
343 x_lov_ak_region := 'JTF_RS_ROLES_LOV_REGION';
344 -- input role_name
345 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
346 -- input role_type_code
347 if l_in_filter_lov_rec.aux_value1 is not null then
348 l_aux_value1 := l_in_filter_lov_rec.aux_value1;
349 else
350 l_aux_value1 := '%';
351 end if;
352 -- input role_type_name
353 if l_in_filter_lov_rec.aux_value2 is not null then
354 l_aux_value2 := l_in_filter_lov_rec.aux_value2;
355 else
356 l_aux_value2 := '%';
357 end if;
358 x_ext_col_cnt := 4; -- 4 extra columns are used.
359
360 -- Get the total count if "Last" hyperlink is clicked
361 IF (p_range_high < 0) THEN
362 SELECT count(*)
363 INTO l_total_count
364 from jtf_rs_roles_vl jrr,
365 fnd_lookups flk
366 where UPPER(role_name) like l_display_value
367 and jrr.role_type_code like l_aux_value1
368 and flk.meaning like l_aux_value2
369 and flk.lookup_type = 'JTF_RS_ROLE_TYPE'
370 and flk.lookup_code = jrr.role_type_code
371 and nvl(jrr.active_flag, 'Y') <> 'N' ;
372
373 l_start := MOD(l_total_count, l_rec_set);
374
375 IF (l_start = 0) THEN
376 l_new_low_value := l_total_count - l_rec_set + 1;
377 l_new_high_value := l_total_count ;
378 ELSE
379 l_new_low_value := l_total_count - l_start + 1;
380 l_new_high_value := l_total_count ;
381 END IF;
382
383 END IF;
384
385
386 OPEN lov_output_cur FOR
387
388 select jrr.role_name, to_char(jrr.role_id), jrr.role_type_code, flk.meaning, null, '%'||manager_flag||'%', '%'||admin_flag||'%', '%'||member_flag||'%', '%'||lead_flag||'%', null
389 from jtf_rs_roles_vl jrr,
390 fnd_lookups flk
391 where UPPER(role_name) like l_display_value
392 and jrr.role_type_code like l_aux_value1
393 and flk.meaning like l_aux_value2
394 and flk.lookup_type = 'JTF_RS_ROLE_TYPE'
395 and flk.lookup_code = jrr.role_type_code
396 and nvl(jrr.active_flag, 'Y') <> 'N'
397 order by role_name;
398
399 elsif p_record_group_name = 'JOB_TITLES' then
400 -- Role Names
401 x_lov_ak_region := 'JTF_RS_JOB_TITLES_LOV_REGION';
402 -- input name
403 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
404 -- filter input business_group_id
405 l_aux_value1 := l_in_filter_lov_rec.aux_value1;
406
407 if l_aux_value1 is null then
408
409 -- Get the total count if "Last" hyperlink is clicked
410 IF (p_range_high < 0) THEN
411 SELECT count(*)
412 INTO l_total_count
413 from per_jobs
414 where UPPER(name) like l_display_value
415 and sysdate >= date_from and sysdate <= NVL(date_to, sysdate);
416
417 l_start := MOD(l_total_count, l_rec_set);
418
419 IF (l_start = 0) THEN
420 l_new_low_value := l_total_count - l_rec_set + 1;
421 l_new_high_value := l_total_count ;
422 ELSE
423 l_new_low_value := l_total_count - l_start + 1;
424 l_new_high_value := l_total_count ;
425 END IF;
426
427 END IF;
428
429 OPEN lov_output_cur FOR
430 select name, to_char(job_id), to_char(business_group_id), null, null, null, null, null, null, null
431 from per_jobs
432 where UPPER(name) like l_display_value
433 and sysdate >= date_from and sysdate <= NVL(date_to, sysdate)
434 order by name;
435 else
436
437 -- Get the total count if "Last" hyperlink is clicked
438 IF (p_range_high < 0) THEN
439 SELECT count(*)
440 INTO l_total_count
441 from per_jobs
442 where UPPER(name) like l_display_value
443 and business_group_id = l_aux_value1
444 and sysdate >= date_from and sysdate <= NVL(date_to, sysdate) ;
445
446 l_start := MOD(l_total_count, l_rec_set);
447
448 IF (l_start = 0) THEN
449 l_new_low_value := l_total_count - l_rec_set + 1;
450 l_new_high_value := l_total_count ;
451 ELSE
452 l_new_low_value := l_total_count - l_start + 1;
453 l_new_high_value := l_total_count ;
454 END IF;
455
456 END IF;
457
458 OPEN lov_output_cur FOR
459
460 select name, to_char(job_id), to_char(business_group_id), null, null, null, null, null, null, null
461 from per_jobs
462 where UPPER(name) like NVL(l_display_value, '%')
463 and business_group_id = l_aux_value1
464 and sysdate >= date_from and sysdate <= NVL(date_to, sysdate)
465 order by name;
466 end if;
467 elsif p_record_group_name = 'CATEGORIES' then
468 -- categories region
469 x_lov_ak_region := 'JTF_RS_CATEGORIES_LOV_REGION';
470 -- input name
471 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
472
473 l_catset := to_number(fnd_profile.value('CS_SR_DEFAULT_CATEGORY_SET'));
474 if l_catset is null then
475 -- Get the total count if "Last" hyperlink is clicked
476 IF (p_range_high < 0) THEN
477 SELECT count(*)
478 INTO l_total_count
479 from jtf_rs_item_categories_v
480 where UPPER(category_name) like l_display_value
481 and nvl(enabled_flag, 'Y') <> 'N'
482 and trunc(sysdate) < nvl(disable_date, sysdate);
483
484 l_start := MOD(l_total_count, l_rec_set);
485
486 IF (l_start = 0) THEN
487 l_new_low_value := l_total_count - l_rec_set + 1;
488 l_new_high_value := l_total_count ;
489 ELSE
490 l_new_low_value := l_total_count - l_start + 1;
491 l_new_high_value := l_total_count ;
492 END IF;
493
494 END IF;
495
496 OPEN lov_output_cur FOR
497 select distinct a.category_name, a.category_id, a.DESCRIPTION, null,null, null, null, null, null, null
498 from jtf_rs_item_categories_v a
499 where UPPER(a.category_name) like l_display_value
500 and nvl(a.enabled_flag, 'Y') <> 'N'
501 and trunc(sysdate) < nvl(disable_date, sysdate)
502 order by a.category_name;
503 else
504 -- Get the total count if "Last" hyperlink is clicked
505 IF (p_range_high < 0) THEN
506 SELECT count(*)
507 INTO l_total_count
508 from jtf_rs_item_categories_v a, mtl_category_set_valid_cats b
509 where UPPER(a.category_name) like NVL(l_display_value, '%')
510 and nvl(a.enabled_flag, 'Y') <> 'N'
511 and trunc(sysdate) < nvl(disable_date, sysdate)
512 and a.category_id = b.category_id
513 and b.category_set_id = l_catset;
514
515 l_start := MOD(l_total_count, l_rec_set);
516
517 IF (l_start = 0) THEN
518 l_new_low_value := l_total_count - l_rec_set + 1;
519 l_new_high_value := l_total_count ;
520 ELSE
521 l_new_low_value := l_total_count - l_start + 1;
522 l_new_high_value := l_total_count ;
523 END IF;
524
525 END IF;
526 OPEN lov_output_cur FOR
527 select distinct a.category_name, a.category_id, a.DESCRIPTION, null,null, null, null, null, null, null
528 from jtf_rs_item_categories_v a, mtl_category_set_valid_cats b
529 where UPPER(a.category_name) like l_display_value
530 and nvl(a.enabled_flag, 'Y') <> 'N'
531 and trunc(sysdate) < nvl(disable_date, sysdate)
532 and a.category_id = b.category_id
533 and b.category_set_id = l_catset
534 order by a.category_name;
535 end if;
536 elsif p_record_group_name = 'PRODUCTS' then
537 -- Products
538 x_lov_ak_region := 'JTF_RS_PRODUCTS_LOV_REGION';
539 -- Product field
540 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
541
542 l_org_id := jtf_resource_utl.get_inventory_org_id();
543
544 l_aux_value1 := l_in_filter_lov_rec.aux_value1;
545
546 if l_aux_value1 is null then
547
548 IF (p_range_high < 0) THEN
549 SELECT count(*)
550 INTO l_total_count
551 from jtf_rs_products_v a
552 where UPPER(a.PRODUCT_NAME) like l_display_value
553 and nvl(a.enabled_flag, 'Y') <> 'N'
554 and a.PRODUCT_ORG_ID = l_org_id;
555
556 l_start := MOD(l_total_count, l_rec_set);
557
558 IF (l_start = 0) THEN
559 l_new_low_value := l_total_count - l_rec_set + 1;
560 l_new_high_value := l_total_count ;
561 ELSE
562 l_new_low_value := l_total_count - l_start + 1;
563 l_new_high_value := l_total_count ;
564 END IF;
565
566 END IF;
567
568 OPEN lov_output_cur FOR
569 select a.PRODUCT_NAME, to_char(a.PRODUCT_ID), null, a.DESCRIPTION, null, null, null, null, null, null
570 from jtf_rs_products_v a
571 where UPPER(a.PRODUCT_NAME) like l_display_value
572 and nvl(a.enabled_flag, 'Y') <> 'N'
573 and a.PRODUCT_ORG_ID = l_org_id
574 order by a.PRODUCT_NAME;
575 else
576
577 -- Get the total count if "Last" hyperlink is clicked
578 IF (p_range_high < 0) THEN
579 SELECT count(*)
580 INTO l_total_count
581 from jtf_rs_products_v a
582 where UPPER(PRODUCT_NAME) like l_display_value
583 and PRODUCT_ORG_ID = l_org_id
584 and nvl(a.enabled_flag, 'Y') <> 'N'
585 and exists(select null from mtl_item_categories c
586 where a.product_id = c.inventory_item_id
587 and c.organization_id = l_org_id
588 and c.category_id = l_aux_value1);
589
590 l_start := MOD(l_total_count, l_rec_set);
591
592 IF (l_start = 0) THEN
593 l_new_low_value := l_total_count - l_rec_set + 1;
594 l_new_high_value := l_total_count ;
595 ELSE
596 l_new_low_value := l_total_count - l_start + 1;
597 l_new_high_value := l_total_count ;
598 END IF;
599
600 END IF;
601 OPEN lov_output_cur FOR
602 select a.PRODUCT_NAME, to_char(a.PRODUCT_ID), null, a.DESCRIPTION, null, null, null, null, null, null
603 from jtf_rs_products_v a
604 where UPPER(a.PRODUCT_NAME) like l_display_value
605 and a.PRODUCT_ORG_ID = l_org_id
606 and nvl(a.enabled_flag, 'Y') <> 'N'
607 and exists(select null from mtl_item_categories c
608 where a.product_id = c.inventory_item_id
609 and c.organization_id = l_org_id
610 and c.category_id = l_aux_value1)
611 order by a.PRODUCT_NAME;
612 end if;
613 elsif p_record_group_name = 'PLATFORMS' then
614 -- Products
615 x_lov_ak_region := 'JTF_RS_PLATFORMS_LOV_REGION';
616 -- Product field
617 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
618
619 l_org_id := jtf_resource_utl.get_inventory_org_id();
620
621 l_aux_value1 := l_in_filter_lov_rec.aux_value1;
622
623 if l_aux_value1 is null then
624 -- Get the total count if "Last" hyperlink is clicked
625 IF (p_range_high < 0) THEN
626 SELECT count(*)
627 INTO l_total_count
628 from jtf_rs_platforms_v a
629 where UPPER(a.PLATFORM_NAME) like l_display_value
630 and a.platform_org_id = l_org_id;
631
632 l_start := MOD(l_total_count, l_rec_set);
633
634 IF (l_start = 0) THEN
635 l_new_low_value := l_total_count - l_rec_set + 1;
636 l_new_high_value := l_total_count ;
637 ELSE
638 l_new_low_value := l_total_count - l_start + 1;
639 l_new_high_value := l_total_count ;
640 END IF;
641
642 END IF;
643
644 OPEN lov_output_cur FOR
645 select a.PLATFORM_NAME, to_char(a.PLATFORM_ID), to_char(a.category_id), a.DESCRIPTION, null, null, null, null, null, null
646 from jtf_rs_platforms_v a
647 where UPPER(a.PLATFORM_NAME) like l_display_value
648 and a.platform_org_id = l_org_id
649 order by a.PLATFORM_NAME;
650 else
651 -- Get the total count if "Last" hyperlink is clicked
652 IF (p_range_high < 0) THEN
653 SELECT count(*)
654 INTO l_total_count
655 from jtf_rs_platforms_v a
656 where UPPER(a.PLATFORM_NAME) like l_display_value
657 and a.category_id = l_aux_value1
658 and a.platform_org_id = l_org_id;
659
660 l_start := MOD(l_total_count, l_rec_set);
661
662 IF (l_start = 0) THEN
663 l_new_low_value := l_total_count - l_rec_set + 1;
664 l_new_high_value := l_total_count ;
665 ELSE
666 l_new_low_value := l_total_count - l_start + 1;
667 l_new_high_value := l_total_count ;
668 END IF;
669
670 END IF;
671
672 OPEN lov_output_cur FOR
673 select a.PLATFORM_NAME, to_char(a.PLATFORM_ID), to_char(a.category_id), a.DESCRIPTION, null, null, null, null, null, null
674 from jtf_rs_platforms_v a
675 where UPPER(a.PLATFORM_NAME) like l_display_value
676 and a.category_id = l_aux_value1
677 and a.platform_org_id = l_org_id
678 order by a.PLATFORM_NAME;
679 end if;
680 elsif p_record_group_name = 'PROBLEM_CODES' then
681 -- Problem codes region
682 x_lov_ak_region := 'JTF_RS_PROBLEM_CODE_LOV_REGION';
683 -- input name
684 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
685
686 -- Get the total count if "Last" hyperlink is clicked
687 IF (p_range_high < 0) THEN
688 SELECT count(*)
689 INTO l_total_count
690 from jtf_rs_problem_codes_v
691 where UPPER(PROBLEM_NAME) like l_display_value
692 AND enabled_flag = 'Y'
693 AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
694 nvl(end_date_active, sysdate);
695
696 l_start := MOD(l_total_count, l_rec_set);
697
698 IF (l_start = 0) THEN
699 l_new_low_value := l_total_count - l_rec_set + 1;
700 l_new_high_value := l_total_count ;
701 ELSE
702 l_new_low_value := l_total_count - l_start + 1;
703 l_new_high_value := l_total_count ;
704 END IF;
705
706 END IF;
707
708 OPEN lov_output_cur FOR
709 select a.PROBLEM_NAME, a.PROBLEM_CODE, a.DESCRIPTION, null,null, null, null, null, null, null
710 from jtf_rs_problem_codes_v a
711 where UPPER(a.PROBLEM_NAME) like l_display_value
712 AND a.enabled_flag = 'Y'
713 AND trunc(sysdate) between trunc(nvl(a.start_date_active, sysdate)) and
714 nvl(a.end_date_active, sysdate)
715 order by a.PROBLEM_NAME;
716 elsif p_record_group_name = 'COMPONENTS' then
717 -- Products
718 x_lov_ak_region := 'JTF_RS_COMPONENTS_LOV_REGION';
719 -- Product field
720 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
721 -- filter input product id
722 l_aux_value1 := l_in_filter_lov_rec.aux_value1;
723
724 l_org_id := jtf_resource_utl.get_inventory_org_id();
725
726 if l_aux_value1 is null then
727
728 OPEN lov_output_cur FOR
729 select null, null, null, null, null, null, null, null, null, null
730 from dual
731 where 'Y' = 'N';
732 else
733 IF (p_range_high < 0) THEN
734 SELECT count(*)
735 INTO l_total_count
736 from jtf_rs_components_v a
737 where UPPER(a.COMPONENT_NAME) like l_display_value
738 and a.product_org_id = l_org_id
739 and a.product_id = l_aux_value1;
740
741 l_start := MOD(l_total_count, l_rec_set);
742
743 IF (l_start = 0) THEN
744 l_new_low_value := l_total_count - l_rec_set + 1;
745 l_new_high_value := l_total_count ;
746 ELSE
747 l_new_low_value := l_total_count - l_start + 1;
748 l_new_high_value := l_total_count ;
749 END IF;
750
751 END IF;
752
753 OPEN lov_output_cur FOR
754 select a.COMPONENT_NAME, to_char(a.COMPONENT_ID), to_char(a.product_id), a.DESCRIPTION, null, null, null, null, null, null
755 from jtf_rs_components_v a
756 where UPPER(a.COMPONENT_NAME) like l_display_value
757 and a.product_id = l_aux_value1
758 and a.product_org_id = l_org_id
759 order by a.COMPONENT_NAME;
760 end if;
761 elsif p_record_group_name = 'PRODUCT_PROBLEM_CODES' then
762 -- Products
763 x_lov_ak_region := 'JTF_RS_PROBLEM_CODE_LOV_REGION';
764 -- Product field
765 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
766 -- filter input product id
767 l_aux_value1 := l_in_filter_lov_rec.aux_value1;
768
769 l_org_id := jtf_resource_utl.get_inventory_org_id();
770
771 if l_aux_value1 is null then
772 OPEN lov_output_cur FOR
773 ' select null, null, null, null, null, null, null, null, null, null '||
774 ' from dual '||
775 ' where 1 = 2 ';
776 else
777 IF (p_range_high < 0) THEN
778 EXECUTE IMMEDIATE
779 ' SELECT count(*) '||
780 ' from cs_sr_prob_code_mapping a, jtf_rs_problem_codes_v b '||
781 ' where UPPER(b.PROBLEM_NAME) like :1 '||
782 ' and nvl(b.enabled_flag, ''Y'') <> ''N'' '||
783 ' and a.ORGANIZATION_ID = :2 '||
784 ' and a.inventory_item_id = :3 '||
785 ' and a.problem_code = b.problem_code '
786 INTO l_total_count
787 USING l_display_value ,l_org_id, l_aux_value1;
788
789 l_start := MOD(l_total_count, l_rec_set);
790
791 IF (l_start = 0) THEN
792 l_new_low_value := l_total_count - l_rec_set + 1;
793 l_new_high_value := l_total_count ;
794 ELSE
795 l_new_low_value := l_total_count - l_start + 1;
796 l_new_high_value := l_total_count ;
797 END IF;
798
799 END IF;
800 OPEN lov_output_cur FOR
801 ' select b.PROBLEM_NAME, a.PROBLEM_CODE, to_char(a.inventory_item_id), b.DESCRIPTION, null, null, null, null, null, null ' ||
802 ' from cs_sr_prob_code_mapping a, jtf_rs_problem_codes_v b '||
803 ' where UPPER(b.PROBLEM_NAME) like :1 '||
804 ' and nvl(b.enabled_flag, ''Y'') <> ''N'' '||
805 ' and a.ORGANIZATION_ID = :2 '||
806 ' and a.inventory_item_id = :3 '||
807 ' and a.problem_code = b.problem_code '||
808 ' order by b.PROBLEM_NAME '
809 USING l_display_value, l_org_id, l_aux_value1;
810 end if;
811 elsif p_record_group_name = 'SUPPORT_SITES' then
812 -- Support site Names
813 x_lov_ak_region := 'JTF_RS_SUPPORT_SITE_LOV_REGION';
814 -- input Support site address 1
815 l_display_value := UPPER(l_in_filter_lov_rec.display_value) || '%';
816 l_filter_number1 := to_number(nvl(l_in_filter1, '-99'));
817
818 -- Get the total count if "Last" hyperlink is clicked
819 IF (p_range_high < 0) THEN
820 SELECT count(*)
821 INTO l_total_count
822 FROM hz_party_sites p, hz_locations loc, hz_party_site_uses psu
823 WHERE UPPER(loc.address1) like l_display_value
824 AND p.party_site_id = psu.party_site_id
825 AND psu.site_use_type = 'SUPPORT_SITE'
826 AND p.location_id = loc.location_id;
827
828 l_start := MOD(l_total_count, l_rec_set);
829
830 IF (l_start = 0) THEN
831 l_new_low_value := l_total_count - l_rec_set + 1;
832 l_new_high_value := l_total_count ;
833 ELSE
834 l_new_low_value := l_total_count - l_start + 1;
835 l_new_high_value := l_total_count ;
836 END IF;
837
838 END IF;
839
840
841 OPEN lov_output_cur FOR
842 SELECT loc.address1, to_char(p.party_site_id), loc.city, null, null, null, null, null, null, null
843 FROM hz_party_sites p, hz_locations loc, hz_party_site_uses psu
844 WHERE UPPER(loc.address1) like l_display_value
845 AND p.party_site_id = psu.party_site_id
846 AND psu.site_use_type = 'SUPPORT_SITE'
847 AND p.location_id = loc.location_id
848 ORDER BY loc.address1;
849
850 else
851 --dbms_output.put_line('Invalid record group name specified ');
852
853 -- Get the total count if "Last" hyperlink is clicked
854 IF (p_range_high < 0) THEN
855 SELECT count(*)
856 INTO l_total_count
857 FROM dual;
858
859 l_start := MOD(l_total_count, l_rec_set);
860
861 IF (l_start = 0) THEN
862 l_new_low_value := l_total_count - l_rec_set + 1;
863 l_new_high_value := l_total_count ;
864 ELSE
865 l_new_low_value := l_total_count - l_start + 1;
866 l_new_high_value := l_total_count ;
867 END IF;
868
869 END IF;
870
871 OPEN lov_output_cur FOR
872 select 'display_value', 'code_value', 'aux_value1', 'aux_value2', 'aux_value3', null, null, null, null, null
873 from dual;
874 end if;
875
876 ----------------------------------------------
877 -- Loop dynamic cursor to output table type
878 ----------------------------------------------
879 x_total_rows := 0;
880 l_range_high := l_new_high_value + 1;
881
882 loop
883 fetch lov_output_cur into rec;
884 IF (x_total_rows = l_range_high) then
885 x_more_data_flag := 'Y' ;
886 ELSE
887 IF ((x_total_rows <> l_range_high) OR lov_output_cur%notfound ) THEN
888 x_more_data_flag := 'N' ;
889 END IF;
890 END IF;
891 exit when x_total_rows = l_range_high;
892 exit when lov_output_cur%notfound;
893
894 x_total_rows := x_total_rows + 1;
895 if (x_total_rows between l_new_low_value and l_new_high_value) OR
896 (p_range_high = -1) then
897 l_index := l_index + 1;
898 x_result_tbl(l_index) := rec;
899 end if;
900 end loop;
901 close lov_output_cur;
902
903 END Get_LOV_Records;
904
905 END JTF_RS_JSP_LOV_RECS_PUB;