[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_JSP_LOV_RECS_PUB
Source
1 Package Body JTF_TERR_JSP_LOV_RECS_PUB AS
2 /* $Header: jtfpjlvb.pls 120.7 2008/07/28 09:23:29 gmarwah ship $ */
3
4 ---------------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_TERR_JSP_LOV_RECS_PUB
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- JTF/A Territories LOV Package
11 -- NOTES
12 -- This package is publicly available for use
13 --
14 -- HISTORY
15 -- 04/30/2001 EIHSU Created
16 -- 11/20/2001 EIHSU Additional data groups
17 -- 05/18/2004 ACHANDA Bug # 3610389 : Make call to WF_NOTIFICATION.SubstituteSpecialChars
18 -- before rendering the data in jsp
19 -- 06/03/2004 ACHANDA Bug # 3664794
20 -- 28/07/2008 GMARWAH Bug 7237992. modified jtf_qual_usg to
21 -- jtf_qual_usg_all.
22 -- End of Comments
23 --
24 --
25 -- ***************************************************
26 -- GLOBAL VARIABLES
27 -- ***************************************************
28 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERR_JSP_LOV_RECS_PUB';
29 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfpjlvb.pls';
30
31 G_NEW_LINE VARCHAR2(02);
32 G_APPL_ID NUMBER;
33 G_LOGIN_ID NUMBER;
34 G_PROGRAM_ID NUMBER;
35 G_USER_ID NUMBER;
36 G_REQUEST_ID NUMBER;
37 G_APP_SHORT_NAME VARCHAR2(15);
38 G_test_var NUMBER;
39 G_test_var2 NUMBER;
40 G_dyn_result_tbl JTF_TERR_JSP_LOV_RECS_PUB.lov_output_tbl_type;
41
42 --TYPE GenericCurTyp IS REF CURSOR;
43 TYPE Lov_output_cur_type IS REF CURSOR RETURN lov_inout_rec_type;
44
45 Procedure Get_LOV_Records
46 ( p_range_low IN NUMBER,
47 p_range_high IN NUMBER,
48 p_record_group_name IN VARCHAR2, -- name of the data to fetch
49 p_in_filter_lov_rec IN lov_inout_rec_type,
50 x_total_rows OUT NOCOPY NUMBER,
51 x_more_data_flag OUT NOCOPY VARCHAR2,
52 x_lov_ak_region OUT NOCOPY VARCHAR2,
53 x_result_tbl OUT NOCOPY lov_output_tbl_type,
54 x_disp_format_tbl OUT NOCOPY lov_disp_format_tbl_type
55 )
56 IS
57 -- Our generic cursor
58 lov_output_cur Lov_output_cur_type;
59
60 -- filtering record type
61 l_in_filter_lov_rec lov_inout_rec_type;
62
63 -- Processed cursor filter inputs
64 l_column1 varchar2(2000);
65 l_column2 varchar2(2000);
66 l_column3 varchar2(2000);
67 l_column4 varchar2(2000);
68 l_column5 varchar2(2000);
69 l_column6 varchar2(2000);
70 l_column7 varchar2(2000);
71 l_column8 varchar2(2000);
72 l_column9 varchar2(2000);
73 l_column10 varchar2(2000);
74 l_column11 varchar2(2000);
75 l_column12 varchar2(2000);
76 l_column13 varchar2(2000);
77 l_column14 varchar2(2000);
78 l_column15 varchar2(2000);
79 -------------------------------
80 l_filter1 varchar2(2000);
81 l_filter2 varchar2(2000);
82 l_filter3 varchar2(2000);
83 l_filter4 varchar2(2000);
84 l_filter5 varchar2(2000);
85
86 -- Cursor iteration variables
87 l_index NUMBER := 0;
88 l_range_high NUMBER;
89 rec lov_inout_rec_type;
90
91 l_new_low_value number;
92 l_new_high_value number;
93 l_total_count number := 0;
94 l_rec_set number := 1;
95 l_start number := 0;
96 l_more_data varchar2(1) ;
97 l_org_id number;
98 l_catset number;
99 l_row_count number;
100
101 -- Other variables
102 l_jsp_lov_sql varchar2(2000);
103
104 -- dynamic block variables
105 jtty_list1 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
106 jtty_list2 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
107 jtty_list3 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
108 jtty_list4 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
112
109 jtty_list5 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
110 l_out_cols number := 0; -- number of columns in dynamic qual value sql
111 l_qv_total number := 0;
113 l_dummy1 varchar2(300);
114 l_dummy2 varchar2(300);
115 l_dyn_body_top varchar2(2000);
116 l_dyn_body_loop_cmds varchar2(2000);
117 l_dyn_body_btm varchar2(2000);
118
119 -- cursor c_all_dyn_qual_vals is
120 -- select * from JTF_TERR_JSP_LOV_REC_TBL;
121
122
123 BEGIN
124 G_NEW_LINE := FND_GLOBAL.Local_Chr(10);
125 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
126 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
127 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
128 G_USER_ID := FND_GLOBAL.User_Id;
129 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
130 G_APP_SHORT_NAME := FND_GLOBAL.Application_Short_Name;
131 G_test_var := 5;
132 G_test_var2 := 4;
133
134 l_column1 := p_in_filter_lov_rec.column1;
135 l_column2 := p_in_filter_lov_rec.column2;
136 l_column3 := p_in_filter_lov_rec.column3;
137 l_column4 := p_in_filter_lov_rec.column4;
138 l_column5 := p_in_filter_lov_rec.column5;
139 l_column6 := p_in_filter_lov_rec.column6;
140 l_column7 := p_in_filter_lov_rec.column7;
141 l_column8 := p_in_filter_lov_rec.column8;
142 l_column9 := p_in_filter_lov_rec.column9;
143 l_column10 := p_in_filter_lov_rec.column10;
144 l_column11 := p_in_filter_lov_rec.column11;
145 l_column12 := p_in_filter_lov_rec.column12;
146 l_column13 := p_in_filter_lov_rec.column13;
147 l_column14 := p_in_filter_lov_rec.column14;
148 l_column15 := p_in_filter_lov_rec.column15;
149 l_filter1 := p_in_filter_lov_rec.filter1;
150 l_filter2 := p_in_filter_lov_rec.filter2;
151 l_filter3 := p_in_filter_lov_rec.filter3;
152 l_filter4 := p_in_filter_lov_rec.filter4;
153 l_filter5 := p_in_filter_lov_rec.filter5;
154 l_in_filter_lov_rec := p_in_filter_lov_rec;
155
156 l_new_low_value := p_range_low ;
157 l_new_high_value := p_range_high ;
158
159 l_dyn_body_top := '';
160 l_dyn_body_loop_cmds := '';
161 l_dyn_body_btm := '';
162
163 IF (p_range_high < 0) THEN
164 l_rec_set := ABS(p_range_high);
165 END IF;
166
167
168
169 If p_record_group_name = 'QUALIFIER_VALUES' then
170
171 --////////////////////////////////////
172 --// QUALIFIER VALUES CONDITION
173 --////////////////////////////////////
174
175 if l_in_filter_lov_rec.column1 = '-9001' then
176 l_jsp_lov_sql := 'select WF_NOTIFICATION.SubstituteSpecialChars(Meaning) col1_value, lookup_code col2_value from ar_lookups where lookup_type = ''HZ_PARTY_CERT_LEVEL'' ';
177 else
178 select distinct jsp_lov_sql into l_jsp_lov_sql
179 from jtf_qual_usgs_all
180 where qual_usg_id = l_in_filter_lov_rec.column1;
181
182 end if;
183
184 --dbms_output.put_line('l_in_filter_lov_rec.column1 ' || l_in_filter_lov_rec.column1);
185
186 --/////////////////////////////////////////////////////
187 --// Dynamic block for fetching all qualifier values
188 --/////////////////////////////////////////////////////
189
190 if instr(l_jsp_lov_sql, 'col1_value') > 0 then
191 l_out_cols := 1;
192 l_dyn_body_loop_cmds := l_dyn_body_loop_cmds || '
193 ld_jtty_list1.extend;
194 ld_jtty_list1(ld_out_index) := ld_rec_qual_vals.col1_value;
195 ';
196 end if;
197 if instr(l_jsp_lov_sql, 'col2_value') > 0 then
198 l_out_cols := 2;
199 l_dyn_body_loop_cmds := l_dyn_body_loop_cmds || '
200 ld_jtty_list2.extend;
201 ld_jtty_list2(ld_out_index) := ld_rec_qual_vals.col2_value;
202 ';
203 end if;
204 if instr(l_jsp_lov_sql, 'col3_value') > 0 then
205 l_out_cols := 3;
206 l_dyn_body_loop_cmds := l_dyn_body_loop_cmds || '
207 ld_jtty_list3.extend;
208 ld_jtty_list3(ld_out_index) := ld_rec_qual_vals.col3_value;
209 ';
210 end if;
211 if instr(l_jsp_lov_sql, 'col4_value') > 0 then
212 l_out_cols := 4;
213 l_dyn_body_loop_cmds := l_dyn_body_loop_cmds || '
214 ld_jtty_list4.extend;
215 ld_jtty_list4(ld_out_index) := ld_rec_qual_vals.col4_value;
216 ';
217 end if;
218 if instr(l_jsp_lov_sql, 'col5_value') > 0 then
219 l_out_cols := 5;
220 l_dyn_body_loop_cmds := l_dyn_body_loop_cmds || '
221 ld_jtty_list5.extend;
222 ld_jtty_list5(ld_out_index) := ld_rec_qual_vals.col5_value;
223 ';
224 end if;
225
226 l_dyn_body_top := '
227 DECLARE
231 '
228 CURSOR ldc_qual_vals IS
229 '
230 || l_jsp_lov_sql || ';' ||
232 ld_out_index number := 1;
233 ld_dummy1 varchar2(300);
234 ld_jtty_list1 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
235 ld_jtty_list2 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
236 ld_jtty_list3 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
237 ld_jtty_list4 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
238 ld_jtty_list5 JTF_TERR_CHAR_360LIST := JTF_TERR_CHAR_360LIST();
239 ld_low_value number := ' || l_new_low_value || ';
240 ld_high_value number := ' || l_new_high_value || ';
241 ld_out_total number;
242 BEGIN
243
244 --dbms_output.put_line(''ld_low_value '' || ld_low_value);
245 --dbms_output.put_line(''ld_high_value '' || ld_high_value);
246
247 for ld_rec_qual_vals in ldc_qual_vals loop
248 --dbms_output.put_line(''vals: '' || ld_rec_qual_vals.col1_value || ''//'' ||
249 -- ld_rec_qual_vals.col2_value);
250 '
251 ;
252
253 l_dyn_body_btm := '
254 ld_out_index := ld_out_index + 1;
255 end loop;
256
257 --dbms_output.put_line(''ld_jtty_list1.last '' || ld_jtty_list1.last);
258 ld_out_total := ld_jtty_list1.last ;
259 if ld_out_total is null then
260 ld_out_total := 0;
261 end if;
262
263 :1 := ld_jtty_list1;
264 :2 := ld_jtty_list2;
265 :3 := ld_jtty_list3;
266 :4 := ld_jtty_list4;
267 :5 := ld_jtty_list5;
268 :6 := ld_out_total;
269 END;
270 '
271 ;
272
273 if l_new_low_value is null then l_new_low_value := 1; end if;
274 if l_new_high_value is null then l_new_high_value := -1; end if;
275 -- create dynamic block
276 execute immediate
277 l_dyn_body_top || l_dyn_body_loop_cmds || l_dyn_body_btm
278 using in OUT jtty_list1,
279 in OUT jtty_list2,
280 in OUT jtty_list3,
281 in OUT jtty_list4,
282 in OUT jtty_list5,
283 in OUT l_qv_total;
284
285 --dbms_output.put_line('l_qv_total= ' || l_qv_total);
286 -- this is total row coming OUT NOCOPYof dynamic block
287
288 l_row_count := 0;
289 loop
290 exit when jtty_list1.last is null;
291 exit when jtty_list1.last = l_row_count;
292 l_row_count := l_row_count + 1;
293 if l_out_cols >= 1 then
294 x_result_tbl(l_row_count).column1 := WF_NOTIFICATION.SubstituteSpecialChars(jtty_list1(l_row_count));
295 end if;
296 if l_out_cols >= 2 then
297 x_result_tbl(l_row_count).column2 := jtty_list2(l_row_count);
298 end if;
299 if l_out_cols >= 3 then
300 x_result_tbl(l_row_count).column3 := jtty_list3(l_row_count);
301 end if;
302 if l_out_cols >= 4 then
303 x_result_tbl(l_row_count).column4 := jtty_list4(l_row_count);
304 end if;
305 if l_out_cols >= 5 then
306 x_result_tbl(l_row_count).column5 := jtty_list5(l_row_count);
307 end if;
308 end loop;
309
310 x_total_rows := l_row_count;
311
312 else -- data group name is not qualifier_values
313
314 If p_record_group_name = 'SOURCES' then
315 --------------------------------------------------------------------
316 -- Sources
317 --------------------------------------------------------------------
318 -- AK REGION
319 x_lov_ak_region := 'JTF_TERR_SOURCES_LOV_REGION';
320 -- instantiate filter values
321 l_column1 := UPPER(l_in_filter_lov_rec.column1) || '%';
322
323 -- Get the total count for display
324 SELECT count(*)
325 INTO l_total_count
326 from jtf_sources
327 where UPPER(meaning) like NVL(l_column1, '%');
328
329 OPEN lov_output_cur FOR
330 Select WF_NOTIFICATION.SubstituteSpecialChars(meaning), source_id, null, null, null,
331 null, null, null, null, null,
332 null, null, null, null, null,
333 null, null, null, null, null
334 from jtf_sources
335 where UPPER(meaning) like NVL(l_column1, '%')
336 order by meaning;
337
338 elsif p_record_group_name = 'ENABLED_QUALS' then
339 --------------------------------------------------------------------
340 -- DATA_GROUP
341 --------------------------------------------------------------------
342 -- AK REGION
343 x_lov_ak_region := 'XXXX';
344 -- instantiate filter values
345 l_column1 := UPPER(l_in_filter_lov_rec.column1) || '%';
346
347 -- Get the total count for display
348 SELECT count(*)
352 -- and source_id = l_column2
349 INTO l_total_count
350 from jtf_seeded_qual_usgs_v
351 where UPPER(seeded_qual_name) like NVL(l_column1, '%') || '%'
353 and enabled_flag = 'Y';
354
355 OPEN lov_output_cur FOR
356 Select distinct WF_NOTIFICATION.SubstituteSpecialChars(jsquv.seeded_qual_name) sqname, jsquv.qual_usg_id, jsquv.qual_type_id,
357 jsquv.source_id, jsquv.qual_col1_alias,
358 null, null, null, null, null,
359 null, null, null, null, null,
360 null, null, null, null, null
361 from jtf_seeded_qual_usgs_v jsquv
362 where UPPER(seeded_qual_name) like NVL(l_column1, '%') || '%'
363 and source_id = NVL(l_column2, source_id)
364 and enabled_flag = 'Y'
365 order by jsquv.source_id, sqname;
366
367 elsif p_record_group_name = 'ALL_TERR_RESOURCES' then
368 --------------------------------------------------------------------
369 -- DATA_GROUP
370 --------------------------------------------------------------------
371 -- AK REGION
372 x_lov_ak_region := 'XXXX';
373 -- instantiate filter values
374 l_column1 := UPPER(l_in_filter_lov_rec.column1) || '%';
375
376 -- Get the total count for display
377 SELECT distinct count(*)
378 INTO l_total_count
379 from jtf_terr_resources_v jtrv
380 where UPPER(resource_name) like NVL(l_column1, '%') || '%'
381 -- ARPATEL09/08/2003 bug#2966686 fix
382 or l_column1 = '%';
383
384 OPEN lov_output_cur FOR
385 Select distinct WF_NOTIFICATION.SubstituteSpecialChars(resource_name), resource_id,
386 resource_type, null, null,
387 null, null, null, null, null,
388 null, null, null, null, null,
389 null, null, null, null, null
390 from jtf_terr_resources_v jtrv
391 where (UPPER(resource_name) like NVL(l_column1, '%') || '%'
392 -- ARPATEL09/08/2003 bug#2966686 fix
393 or l_column1 = '%')
394 AND EXISTS ( SELECT NULL
395 FROM jtf_terr_denorm_rules_all jtdr
396 WHERE jtdr.source_id = NVL(l_column2, source_id)
397 AND jtdr.terr_id = jtrv.terr_id
398 AND JTDR.RELATED_TERR_ID = JTRV.TERR_ID)
399 order by resource_name;
400
401 elsif p_record_group_name = 'LOOKUP_QUALIFIERS' then
402 --------------------------------------------------------------------
403 -- DATA_GROUP
404 --------------------------------------------------------------------
405 -- AK REGION
406 x_lov_ak_region := 'XXXX';
407 -- instantiate filter values
408 l_column1 := l_in_filter_lov_rec.column1; -- usage filter
409 --dbms_output.put_line('LOOKUP_QUALIFIERS ');
410 -- Get the total count for display
411 l_total_count := 1; -- because this will always be used to get all rows
412
413 OPEN lov_output_cur FOR
414
415 /* select WF_NOTIFICATION.SubstituteSpecialChars(seeded_qual_name), qual_usg_id, Decode(jsp_lov_sql, null, 'N', 'Y'),
416 qual_col1_alias, display_type,
417 seeded_qual_id, null, null, null, null,
418 null, null, null, null, null,
419 null, null, null, null, null
420 from jtf_seeded_qual_usgs_v
421 where use_in_lookup_flag = 'Y'
422 and enabled_flag = 'Y'
423 and source_id = -1001
424 ARPATEL BUG#3736597 fix
425 and qual_col1_alias is not null
426 order by display_sequence;*/ --commented for bug 7237992.
427
428 -- replaced the above query with following query
429 SELECT distinct WF_NOTIFICATION.SubstituteSpecialChars(jsq.name), jqu.qual_usg_id, Decode(jqu.jsp_lov_sql, null, 'N', 'Y'),
430 jqu.qual_col1_alias, jqu.display_type,
431 jsq.seeded_qual_id, null, null, null, null,
432 null, null, null, null, null,
433 null, null, null, null, null
434 FROM jtf_qual_usgs_all jqu, jtf_seeded_qual_all_b jsq,jtf_qual_type_usgs_all jqtu,
435 jtf_qual_types_all jqt ,jtf_sources_all js
436 WHERE jqu.use_in_lookup_flag = 'Y'
437 and jqu.enabled_flag = 'Y'
438 and jqtu.qual_type_id = jqt.qual_type_id
439 and jqtu.qual_type_usg_id = jqu.qual_type_usg_id
440 and jsq.seeded_qual_id = jqu.seeded_qual_id
441 and js.source_id = jqtu.source_id
442 and js.source_id= -1001
443 and jqu.qual_col1_alias is not null;
444
445 elsif p_record_group_name = 'BLANK' then
446 --------------------------------------------------------------------
447 -- DATA_GROUP
448 --------------------------------------------------------------------
449 -- AK REGION
450 x_lov_ak_region := 'XXXX';
451 -- instantiate filter values
452 -- Get the total count for display
453 l_total_count := 1; -- because this will always be used to get all rows
454
455 OPEN lov_output_cur FOR
456 select '--------------', '-------------', ' ', ' ', ' ',
457 null, null, null, null, null,
458 null, null, null, null, null,
459 null, null, null, null, null
460 from dual;
461
462 elsif p_record_group_name = 'SYSTEM_DATE' then
463 --------------------------------------------------------------------
464 -- DATA_GROUP
465 --------------------------------------------------------------------
466 -- AK REGION
467 x_lov_ak_region := 'XXXX';
468 -- instantiate filter values
469 -- Get the total count for display
470 l_total_count := 1; -- because this will always be used to get all rows
471
472 OPEN lov_output_cur FOR
473 select trunc(sysdate), null, null, null, null,
474 null, null, null, null, null,
475 null, null, null, null, null,
476 null, null, null, null, null
477 from dual;
478
479
480
481 elsif p_record_group_name = 'SALES_GROUP' then
482 --------------------------------------------------------------------
483 -- DATA_GROUP
484 --------------------------------------------------------------------
485 -- AK REGION
486 x_lov_ak_region := 'XXXX';
487 -- instantiate filter values
488 l_column1 := UPPER(l_in_filter_lov_rec.column1) || '%';
489
490 -- Get the total count for display
491 SELECT count(*)
492 INTO l_total_count
493 from JTF_TERR_RESOURCES_V
494 where (end_date_active is null or end_date_active >= sysdate)
495 and group_id is not null;
496
497 OPEN lov_output_cur FOR
498 Select distinct jtr.group_name, jtr.group_id, null, null, null,
499 null, null, null, null, null,
500 null, null, null, null, null,
501 null, null, null, null, null
502 from JTF_TERR_RESOURCES_V jtr
503 where (jtr.end_date_active is null or jtr.end_date_active >= sysdate)
504 and jtr.group_id is not null
505 AND EXISTS ( SELECT NULL
506 FROM jtf_terr_denorm_rules_all jtdr
507 WHERE jtdr.source_id = -1001
508 AND jtdr.terr_id = jtr.terr_id
509 AND JTDR.RELATED_TERR_ID = JTR.TERR_ID )
510 order by group_name;
511
512
513 else
514 l_total_count := 0;
515
516 end if;
517
518 ----------------------------------------------
519 -- Loop dynamic cursor to output table type
520 ----------------------------------------------
521 l_row_count := 0;
522 If l_range_high >= 0 then
523 l_range_high := l_new_high_value + 1;
524 end if;
525
526 if l_total_count > 0 then
527 loop
528 fetch lov_output_cur into rec;
529 IF (l_row_count = l_range_high) then
530 x_more_data_flag := 'Y' ;
531 ELSE
532 IF ((l_row_count <> l_range_high) OR lov_output_cur%notfound ) THEN
533 x_more_data_flag := 'N' ;
534 END IF;
535 END IF;
536 -- RETURN ALL RECORDS IF THEY PUT -1 as range high
537 if l_range_high = -1 then
538 null;
539 else
540 exit when l_row_count = l_range_high;
541 end if;
542 exit when lov_output_cur%notfound;
543
544 l_row_count := l_row_count + 1;
545 if (l_row_count between l_new_low_value and l_new_high_value) OR
546 (p_range_high = -1) then
547 l_index := l_index + 1;
548 x_result_tbl(l_index) := rec;
549 end if;
550 end loop;
551 close lov_output_cur;
552
553 end if;
554 x_total_rows := l_total_count;
555
556 end if; -- qualifier_values or other data group?
557
558 END Get_LOV_Records;
559
560 END JTF_TERR_JSP_LOV_RECS_PUB;