[Home] [Help]
PACKAGE BODY: APPS.JTF_REGION_PUB
Source
1 PACKAGE BODY jtf_region_pub AS
2 /* $Header: jtfregnb.pls 120.3 2005/11/01 21:25:22 psanyal ship $ */
3 JTT_IGNORE_COLUMN_NAME varchar2(23) := 'JTT_IGNORE_COLUMN_NAME_';
4 TYPE region_item is record (
5 attribute_label_long ak_region_items_vl.attribute_label_long%type -- varchar2(50)
6 , attribute_label_short ak_region_items_vl.attribute_label_short%type -- varchar2(30)
7 , data_type ak_region_items_vl.data_type%type -- varchar2(30)
8 , attribute_name ak_region_items_vl.attribute_name%type -- varchar2(30)
9 , attribute_code ak_region_items_vl.attribute_code%type -- varchar2(30)
10 , attribute_description ak_region_items_vl.attribute_description%type -- varchar2(2000)
11 , display_value_length ak_region_items_vl.display_value_length%type -- number
12 , lov_region_code ak_region_items_vl.lov_region_code%type -- varchar2(30)
13 , node_display_flag ak_region_items_vl.node_display_flag%type -- varchar2(1)
14 , node_query_flag ak_region_items_vl.node_query_flag%type -- varchar2(1)
15 );
16
17 TYPE region_items_table IS TABLE OF region_item INDEX BY BINARY_INTEGER;
18
19 function ever_varies_based_on_resp_id(
20 p_region_code varchar2,
21 p_application_id number) return number is
22 temp number;
23 -- each of the attribute_code in the ak_region_items table
24 cursor each_attribute_code is select attribute_code
25 from ak_region_items
26 where region_code = p_region_code and
27 region_application_id = p_application_id;
28
29 cursor excluded_resps (p_attribute_code varchar2) is select
30 responsibility_id from ak_excluded_items
31 where resp_application_id = p_application_id and
32 attribute_application_id = p_application_id and
33 attribute_code = p_attribute_code;
34 begin
35 -- for each attribute_code that we'll look at...
36 for each_att_rec in each_attribute_code loop
37 -- if this attribute_code is in excluded items, then return true
38 temp := null;
39 open excluded_resps(each_att_rec.attribute_code);
40 fetch excluded_resps into temp;
41 close excluded_resps;
42 if temp is not null then return 1; end if;
43 end loop;
44
45 -- no excluded items! return false;
46 return 0;
47 end;
48
49 -- returns all the responsibility_ids of the application
50 function get_respids_of(p_appid number) return number_table is
51 t_retval number_table;
52 t_idx number;
53 cursor c1(pp_appid number) is
54 select responsibility_id from fnd_responsibility
55 where application_id = pp_appid;
56 begin
57 t_idx := 1;
58 for c1_rec in c1(p_appid) loop
59 t_retval(t_idx) := c1_rec.responsibility_id;
60 t_idx := t_idx + 1;
61 end loop;
62 return t_retval;
63 end;
64
65 procedure populate_output(
66 p_region_code varchar2,
67 p_respid number,
68 p_object_name varchar2,
69 p_region_name varchar2,
70 p_region_description varchar2,
71 p_region_items_table jtf_region_pub.ak_region_items_table,
72 p_ret_region_codes in out nocopy short_varchar2_table,
73 p_ret_resp_ids in out nocopy number_table,
74 p_ret_object_name in out nocopy short_varchar2_table,
75 p_ret_region_name in out nocopy short_varchar2_table,
76 p_ret_region_description in out nocopy long_varchar2_table,
77 p_ret_region_items_table IN OUT nocopy jtf_region_pub.ak_region_items_table) is
78 t_row number := 1+p_ret_region_codes.count;
79 t_idx number;
80 begin
81 if p_region_items_table.count = 0 then return; end if;
82
86 p_ret_resp_ids(t_row) := p_respid;
83 t_idx := p_region_items_table.first;
84 while true loop
85 p_ret_region_codes(t_row) := p_region_code;
87 if t_idx = p_region_items_table.first then
88 p_ret_object_name(t_row) := p_object_name;
89 p_ret_region_name(t_row) := p_region_name;
90 p_ret_region_description(t_row) := p_region_description;
91 else
92 p_ret_object_name(t_row) := null;
93 p_ret_region_name(t_row) := null;
94 p_ret_region_description(t_row) := null;
95 end if;
96 p_ret_region_items_table(t_row) := p_region_items_table(t_idx);
97
98 t_row := t_row + 1;
99
100 -- next or break;
101 if t_idx = p_region_items_table.last then exit; end if;
102 t_idx := p_region_items_table.next(t_idx);
103 end loop;
104 end populate_output;
105
106 procedure get_regions(p_get_region_codes short_varchar2_table,
107 p_get_application_id number,
108 p_get_responsibility_ids number_table,
109 p_skip_column_name boolean,
110 p_lang OUT NOCOPY /* file.sql.39 change */ varchar2,
111 p_ret_region_codes OUT NOCOPY /* file.sql.39 change */ short_varchar2_table,
112 p_ret_resp_ids OUT NOCOPY /* file.sql.39 change */ number_table,
113 p_ret_object_name OUT NOCOPY /* file.sql.39 change */ short_varchar2_table,
114 p_ret_region_name OUT NOCOPY /* file.sql.39 change */ short_varchar2_table,
115 p_ret_region_description OUT NOCOPY /* file.sql.39 change */ long_varchar2_table,
116 p_ret_region_items_table OUT NOCOPY /* file.sql.39 change */ jtf_region_pub.ak_region_items_table) is
117 t_region_codes short_varchar2_table;
118 t_respids number_table;
119 t_respids_first number;
120 t_respids_index number;
121 t_asn fnd_application.application_short_name%type;
122 t_region_code_index number := 1;
123 t_respid_index number := 1;
124 t_object_name ak_regions_vl.object_name%type;
125 t_region_name ak_regions_vl.name%type;
126 t_region_description ak_regions_vl.description%type;
127 t_region_items_table jtf_region_pub.ak_region_items_table;
128 t_missing_or_invalid boolean;
129 t_prefix varchar2(25);
130 begin
131
132 if p_skip_column_name then
133 t_prefix := JTT_IGNORE_COLUMN_NAME;
134 else
135 t_prefix := '';
136 end if;
137
138 select userenv('lang') into p_lang from dual;
139
140 if p_get_region_codes.count > 0 then
141 t_region_codes := p_get_region_codes;
142 else
143 t_region_code_index := 1;
144 for rc in (select unique region_code from ak_regions_vl
145 where region_application_id = p_get_application_id) loop
146 t_region_codes(t_region_code_index) := rc.region_code;
147 t_region_code_index := t_region_code_index+1;
148 end loop;
149 end if;
150
151 if p_get_responsibility_ids.count > 0 then
152 t_respids := p_get_responsibility_ids;
153 else
154 t_respids := get_respids_of(p_get_application_id);
155 end if;
156 t_respids_first := t_respids.first;
157
158 -- for each item in t_region_codes
159 t_region_code_index := 1;
160
161 -- if the list of region_codes or respids is null, then do
162 -- nothing... the OUT NOCOPY /* file.sql.39 change */ parameters will simply be empty tables
163 if t_region_codes.count = 0 or t_respids.count = 0 then return; end if;
164
165 while true loop
166 -- if the contents of the region_code for this app_id
167 -- is NOT a function of the RESPID, then add the 'short version'
168 if 0 = ever_varies_based_on_resp_id(
169 t_region_codes(t_region_code_index), p_get_application_id) then
170
171 -- great! I can use the short version!
172 t_missing_or_invalid := false;
173 begin
174 get_region(
175 t_prefix || t_region_codes(t_region_code_index),
176 p_get_application_id,
177 t_respids(t_respids_first), t_object_name, t_region_name,
178 t_region_description, t_region_items_table);
179 exception when others then
180 t_missing_or_invalid := true;
181 end;
182 if not t_missing_or_invalid then
183 populate_output(
184 t_region_codes(t_region_code_index),
185 null, -- respid
186 t_object_name,
187 t_region_name,
188 t_region_description,
189 t_region_items_table,
190 p_ret_region_codes,
191 p_ret_resp_ids,
192 p_ret_object_name,
193 p_ret_region_name,
194 p_ret_region_description,
195 p_ret_region_items_table);
196 end if;
197 -- else add the 'long version', i.e. for each item in t_respids
198 else
199 t_respids_index := 1;
200 while true loop
201 -- get the region for this region_name, resp_id, and app_id
202 t_missing_or_invalid := false;
203 begin
204 get_region(
205 t_prefix || t_region_codes(t_region_code_index),
206 p_get_application_id,
207 t_respids(t_respids_index),
208 t_object_name, t_region_name,
209 t_region_description, t_region_items_table);
210 exception when others then
211 t_missing_or_invalid := true;
212 end;
213 if not t_missing_or_invalid then
214 -- copy to the output
215 populate_output(
216 t_region_codes(t_region_code_index),
217 t_respids(t_respids_index),
218 t_object_name,
219 t_region_name,
220 t_region_description,
221 t_region_items_table,
222 p_ret_region_codes,
223 p_ret_resp_ids,
224 p_ret_object_name,
225 p_ret_region_name,
229
226 p_ret_region_description,
227 p_ret_region_items_table);
228 end if;
230 -- next respid, or done
231 if t_respids_index = t_respids.last then exit; end if;
232 t_respids_index := t_respids.next(t_respids_index);
233 end loop;
234 end if;
235
236 -- next region code, or done
237 if t_region_code_index = t_region_codes.last then exit; end if;
238 t_region_code_index := t_region_codes.next(t_region_code_index);
239 end loop;
240 end get_regions;
241
242 PROCEDURE get_region(
243 p_region_code in varchar2
244 , p_application_id in number
245 , p_responsibility_id in number
246 , p_object_name OUT NOCOPY /* file.sql.39 change */ varchar2
247 , p_region_name OUT NOCOPY /* file.sql.39 change */ varchar2
248 , p_region_description OUT NOCOPY /* file.sql.39 change */ varchar2
249 , p_region_items_table OUT NOCOPY /* file.sql.39 change */ ak_region_items_table
250 ) IS
251 cnt number := 0;
252 l_region_items_table region_items_table;
253 p_column_name varchar2(30) := null;
254 temp_database_object_name ak_regions_vl.database_object_name%type;
255 t_punt_column_names boolean;
256 t_region_code ak_regions_vl.region_code%type;
257 BEGIN
258 -- decode the p_region_code value. If p_region_code is a string longer than
259 -- and beginning with 'JTT_IGNORE_COLUMN_NAME_', then remove the
260 -- 'JTT_IGNORE_COLUMN_NAME_' and assume we should punt on getting the
261 -- column_names.
262
263 if length(p_region_code) > 16 and
264 JTT_IGNORE_COLUMN_NAME = substr(p_region_code, 1,23) then
265 t_region_code := substr(p_region_code, 24);
266 t_punt_column_names := true;
267 else
268 t_region_code := p_region_code;
269 t_punt_column_names := false;
270 end if;
271
272 select object_name,name,description,database_object_name
273 into p_object_name, p_region_name,p_region_description,
274 temp_database_object_name
275 from ak_regions_vl
276 where region_code = t_region_code and
277 region_application_id = p_application_id;
278
279 for c1 in (select attribute_label_long,attribute_label_short,data_type,
280 attribute_name , object_attribute_flag, attribute_code,
281 attribute_description, display_value_length,lov_region_code,
282 node_display_flag,node_query_flag
283
284 from ak_region_items_vl a
285 where region_code=t_region_code and
286 region_application_id = p_application_id and
287 attribute_code not in
288 (select attribute_code from ak_excluded_items where
289 responsibility_id=p_responsibility_id and
290 resp_application_id=p_application_id and
291 attribute_application_id= p_application_id and
292 attribute_code = a.attribute_code)
293 order by display_sequence)
294 LOOP
295 cnt := cnt + 1;
296 p_column_name := null;
297
298 if((not t_punt_column_names) and c1.object_attribute_flag = 'Y' ) then
299 begin
300 select column_name into p_column_name from ak_object_attributes
301 where attribute_code = c1.attribute_code;
302 exception when too_many_rows then
303 select column_name into p_column_name from ak_object_attributes
304 where attribute_code = c1.attribute_code and
305 database_object_name = temp_database_object_name;
306 end;
307 end if;
308
309 p_region_items_table(cnt).attribute_label_long :=
310 c1.attribute_label_long;
311 p_region_items_table(cnt).attribute_label_short :=
312 c1.attribute_label_short;
313 p_region_items_table(cnt).column_name := p_column_name;
314 p_region_items_table(cnt).data_type := c1.data_type;
315 p_region_items_table(cnt).attribute_name := c1.attribute_name;
316 p_region_items_table(cnt).attribute_code := c1.attribute_code;
317 p_region_items_table(cnt).attribute_description :=
318 c1.attribute_description;
319 p_region_items_table(cnt).display_value_length :=
320 c1.display_value_length;
321 p_region_items_table(cnt).lov_region_code := c1.lov_region_code;
322 p_region_items_table(cnt).node_display_flag := c1.node_display_flag;
323 p_region_items_table(cnt).node_query_flag := c1.node_query_flag;
324 END LOOP;
325 END get_region;
326
327 FUNCTION get_region_item_name (
328 p_attribute_code in varchar2
329 , p_region_code in varchar2
330 ) RETURN VARCHAR2
331 IS
332 l_attribute_label_long varchar2(50);
333 BEGIN
334
335 select attribute_label_long into l_attribute_label_long
336 from ak_region_items_vl a
337 where region_code=p_region_code and attribute_code = p_attribute_code;
338
339 return l_attribute_label_long;
340
341 END get_region_item_name;
342
343
344 procedure transfer_row_to_column(
345 p_ak_result_table in ak_query_pkg.result_rec,
346 p_ak_result_rec OUT NOCOPY /* file.sql.39 change */ result_rec) IS
347 cnt number := 0;
348 BEGIN
349 p_ak_result_rec.value1 := p_ak_result_table.value1;
350 p_ak_result_rec.value2 := p_ak_result_table.value2;
351 p_ak_result_rec.value3 := p_ak_result_table.value3;
352 p_ak_result_rec.value4 := p_ak_result_table.value4;
353 p_ak_result_rec.value5 := p_ak_result_table.value5;
354 p_ak_result_rec.value6 := p_ak_result_table.value6;
355 p_ak_result_rec.value7 := p_ak_result_table.value7;
356 p_ak_result_rec.value8 := p_ak_result_table.value8;
357 p_ak_result_rec.value9 := p_ak_result_table.value9;
358 p_ak_result_rec.value10 := p_ak_result_table.value10;
359 p_ak_result_rec.value11 := p_ak_result_table.value11;
363 p_ak_result_rec.value15 := p_ak_result_table.value15;
360 p_ak_result_rec.value12 := p_ak_result_table.value12;
361 p_ak_result_rec.value13 := p_ak_result_table.value13;
362 p_ak_result_rec.value14 := p_ak_result_table.value14;
364 p_ak_result_rec.value16 := p_ak_result_table.value16;
365 p_ak_result_rec.value17 := p_ak_result_table.value17;
366 p_ak_result_rec.value18 := p_ak_result_table.value18;
367 p_ak_result_rec.value19 := p_ak_result_table.value19;
368 p_ak_result_rec.value20 := p_ak_result_table.value20;
369 p_ak_result_rec.value21 := p_ak_result_table.value21;
370 p_ak_result_rec.value22 := p_ak_result_table.value22;
371 p_ak_result_rec.value23 := p_ak_result_table.value23;
372 p_ak_result_rec.value24 := p_ak_result_table.value24;
373 p_ak_result_rec.value25 := p_ak_result_table.value25;
374 p_ak_result_rec.value26 := p_ak_result_table.value26;
375 p_ak_result_rec.value27 := p_ak_result_table.value27;
376 p_ak_result_rec.value28 := p_ak_result_table.value28;
377 p_ak_result_rec.value29 := p_ak_result_table.value29;
378 p_ak_result_rec.value30 := p_ak_result_table.value30;
379 p_ak_result_rec.value31 := p_ak_result_table.value31;
380 p_ak_result_rec.value32 := p_ak_result_table.value32;
381 p_ak_result_rec.value33 := p_ak_result_table.value33;
382 p_ak_result_rec.value34 := p_ak_result_table.value34;
383 p_ak_result_rec.value35 := p_ak_result_table.value35;
384 p_ak_result_rec.value36 := p_ak_result_table.value36;
385 p_ak_result_rec.value37 := p_ak_result_table.value37;
386 p_ak_result_rec.value38 := p_ak_result_table.value38;
387 p_ak_result_rec.value39 := p_ak_result_table.value39;
388 p_ak_result_rec.value40 := p_ak_result_table.value40;
389 p_ak_result_rec.value41 := p_ak_result_table.value41;
390 p_ak_result_rec.value42 := p_ak_result_table.value42;
391 p_ak_result_rec.value43 := p_ak_result_table.value43;
392 p_ak_result_rec.value44 := p_ak_result_table.value44;
393 p_ak_result_rec.value45 := p_ak_result_table.value45;
394 p_ak_result_rec.value46 := p_ak_result_table.value46;
395 p_ak_result_rec.value47 := p_ak_result_table.value47;
396 p_ak_result_rec.value48 := p_ak_result_table.value48;
397 p_ak_result_rec.value49 := p_ak_result_table.value49;
398 p_ak_result_rec.value50 := p_ak_result_table.value50;
399 p_ak_result_rec.value51 := p_ak_result_table.value51;
400 p_ak_result_rec.value52 := p_ak_result_table.value52;
401 p_ak_result_rec.value53 := p_ak_result_table.value53;
402 p_ak_result_rec.value54 := p_ak_result_table.value54;
403 p_ak_result_rec.value55 := p_ak_result_table.value55;
404 p_ak_result_rec.value56 := p_ak_result_table.value56;
405 p_ak_result_rec.value57 := p_ak_result_table.value57;
406 p_ak_result_rec.value58 := p_ak_result_table.value58;
407 p_ak_result_rec.value59 := p_ak_result_table.value59;
408 p_ak_result_rec.value60 := p_ak_result_table.value60;
409 p_ak_result_rec.value61 := p_ak_result_table.value61;
410 p_ak_result_rec.value62 := p_ak_result_table.value62;
411 p_ak_result_rec.value63 := p_ak_result_table.value63;
412 p_ak_result_rec.value64 := p_ak_result_table.value64;
413 p_ak_result_rec.value65 := p_ak_result_table.value65;
414 p_ak_result_rec.value66 := p_ak_result_table.value66;
415 p_ak_result_rec.value67 := p_ak_result_table.value67;
416 p_ak_result_rec.value68 := p_ak_result_table.value68;
417 p_ak_result_rec.value69 := p_ak_result_table.value69;
418 p_ak_result_rec.value70 := p_ak_result_table.value70;
419 p_ak_result_rec.value71 := p_ak_result_table.value71;
420 p_ak_result_rec.value72 := p_ak_result_table.value72;
421 p_ak_result_rec.value73 := p_ak_result_table.value73;
422 p_ak_result_rec.value74 := p_ak_result_table.value74;
423 p_ak_result_rec.value75 := p_ak_result_table.value75;
424 p_ak_result_rec.value76 := p_ak_result_table.value76;
425 p_ak_result_rec.value77 := p_ak_result_table.value77;
426 p_ak_result_rec.value78 := p_ak_result_table.value78;
427 p_ak_result_rec.value79 := p_ak_result_table.value79;
428 p_ak_result_rec.value80 := p_ak_result_table.value80;
429 p_ak_result_rec.value81 := p_ak_result_table.value81;
430 p_ak_result_rec.value82 := p_ak_result_table.value82;
431 p_ak_result_rec.value83 := p_ak_result_table.value83;
432 p_ak_result_rec.value84 := p_ak_result_table.value84;
433 p_ak_result_rec.value85 := p_ak_result_table.value85;
434 p_ak_result_rec.value86 := p_ak_result_table.value86;
435 p_ak_result_rec.value87 := p_ak_result_table.value87;
436 p_ak_result_rec.value88 := p_ak_result_table.value88;
437 p_ak_result_rec.value89 := p_ak_result_table.value89;
438 p_ak_result_rec.value90 := p_ak_result_table.value90;
439 p_ak_result_rec.value91 := p_ak_result_table.value91;
440 p_ak_result_rec.value92 := p_ak_result_table.value92;
441 p_ak_result_rec.value93 := p_ak_result_table.value93;
442 p_ak_result_rec.value94 := p_ak_result_table.value94;
443 p_ak_result_rec.value95 := p_ak_result_table.value95;
444 p_ak_result_rec.value96 := p_ak_result_table.value96;
445 p_ak_result_rec.value97 := p_ak_result_table.value97;
446 p_ak_result_rec.value98 := p_ak_result_table.value98;
447 p_ak_result_rec.value99 := p_ak_result_table.value99;
448 p_ak_result_rec.value100 := p_ak_result_table.value100;
449 END transfer_row_to_column;
450
451 PROCEDURE ak_query(
452 p_application_id in number
453 , p_region_code in varchar2
454 , p_where_clause in varchar2
455 , p_order_by_clause in varchar2
456 , p_responsibility_id in number
457 , p_user_id in number
458 , p_range_low in number default 0
459 , p_range_high in number default null
460 , p_max_rows IN OUT NOCOPY /* file.sql.39 change */ number
461 , p_where_binds in ak_bind_table
462 , p_ak_item_rec_table OUT NOCOPY /* file.sql.39 change */ ak_item_rec_table
463 , p_ak_result_table OUT NOCOPY /* file.sql.39 change */ ak_result_table
464 ) IS
465 cnt number := 0;
466 p_bind_tab ak_query_pkg.bind_tab;
467 l_result_rec result_rec := null;
468 l_ak_item_rec ak_item_rec := null;
469 range_high number := 0;
470 range_low number := 0;
471 p_column_name varchar2(30) := null;
472 BEGIN
473
474 if(p_where_binds is not null) then
475 for i in 1..p_where_binds.count LOOP
476 p_bind_tab(i).name := p_where_binds(i).name;
477 p_bind_tab(i).value := p_where_binds(i).value;
478 END LOOP;
479 end if;
480
481 ak_query_pkg.exec_query(
482 p_parent_region_appl_id=>p_application_id,
483 p_parent_region_code=>p_region_code,
484 p_where_clause=>p_where_clause,
485 p_order_by_clause=>p_order_by_clause,
486 p_user_id => p_user_id,
487 p_responsibility_id=>p_responsibility_id,
488 p_range_low=>p_range_low,
489 p_range_high=>p_range_high,
490 p_max_rows=>p_max_rows,
491 p_where_binds=>p_bind_tab);
492
493 if(ak_query_pkg.g_regions_table(0).total_result_count > 0) then
494 if(p_max_rows is null or p_max_rows > ak_query_pkg.g_regions_table(0).total_result_count ) then
495 p_max_rows := ak_query_pkg.g_regions_table(0).total_result_count;
496 end if;
497 else
498 p_max_rows := 0;
499 l_ak_item_rec.value_id := 0;
500 l_ak_item_rec.column_name := '';
501 p_ak_item_rec_table(1) := l_ak_item_rec;
502 p_ak_result_table(1) := null;
503 end if;
504
505 /* this is the first batch */
506
507
508 /* read all the item records */
509
510 /*dbms_output.put_line('reading the items table ' );*/
511
512 for i in ak_query_pkg.g_items_table.FIRST..ak_query_pkg.g_items_table.LAST
513 loop
514
515 /*dbms_output.put_line('reading the items table: ' || ak_query_pkg.g_items_table(i).attribute_code || 'll' || ak_query_pkg.g_items_table(i).object_attribute_flag);*/
516
517 if(ak_query_pkg.g_items_table(i).object_attribute_flag = 'Y') then
518 select column_name into p_column_name from ak_object_attributes
519 where attribute_code = ak_query_pkg.g_items_table(i).attribute_code;
520 l_ak_item_rec.value_id := ak_query_pkg.g_items_table(i).value_id;
521 l_ak_item_rec.column_name := p_column_name;
522
523 p_ak_item_rec_table(i + 1) := l_ak_item_rec;
524 cnt := cnt +1;
525 end if;
526 end loop;
527
528 /* read all the results for the current batch */
529
530
531 /*dbms_output.put_line('read the items table ' );*/
532
533 cnt := 1;
534
535 range_high := p_range_high;
536 range_low := p_range_low;
537
538
539 if( range_low > 0 and ( range_high > p_range_low or range_high = p_range_low) )
540 then
541
542 if(range_low > p_max_rows) then
543 range_low := p_max_rows;
544 /*p_ak_result_table := null;*/
545 return;
546 end if;
547
548 if(range_high > p_max_rows) then
549 range_high := p_max_rows;
550 end if;
551
552 for i in range_low-1..range_high-1 loop
553 transfer_row_to_column(ak_query_pkg.g_results_table(i), l_result_rec);
554 p_ak_result_table(cnt) := l_result_rec;
555 cnt := cnt + 1;
556 end loop;
557 end if;
558 exception
559 when no_data_found then
560 p_max_rows := 0;
561 l_ak_item_rec.value_id := 0;
562 l_ak_item_rec.column_name := '';
563 p_ak_item_rec_table(1) := l_ak_item_rec;
564 p_ak_result_table(1) := null;
565 /*dbms_output.put_line('No data found exception');*/
566 END ak_query;
567
568 end jtf_region_pub;