[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_JSP_LOV_RECS_PUB_W
Source
1 package body jtf_terr_jsp_lov_recs_pub_w as
2 /* $Header: jtfwjlvb.pls 120.0 2005/06/02 18:23:17 appldev ship $ */
3 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
4 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
5
6 function rosetta_g_miss_num_map(n number) return number as
7 a number := fnd_api.g_miss_num;
8 b number := 0-1962.0724;
9 begin
10 if n=a then return b; end if;
11 if n=b then return a; end if;
12 return n;
13 end;
14
15 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
16 function rosetta_g_miss_date_in_map(d date) return date as
17 begin
18 if d = rosetta_g_mistake_date then return fnd_api.g_miss_date; end if;
19 return d;
20 end;
21
22 procedure rosetta_table_copy_in_p2(t OUT NOCOPY jtf_terr_jsp_lov_recs_pub.lov_output_tbl_type, a0 JTF_VARCHAR2_TABLE_2000
23 , a1 JTF_VARCHAR2_TABLE_2000
24 , a2 JTF_VARCHAR2_TABLE_2000
25 , a3 JTF_VARCHAR2_TABLE_2000
26 , a4 JTF_VARCHAR2_TABLE_2000
27 , a5 JTF_VARCHAR2_TABLE_2000
28 , a6 JTF_VARCHAR2_TABLE_2000
29 , a7 JTF_VARCHAR2_TABLE_2000
30 , a8 JTF_VARCHAR2_TABLE_2000
31 , a9 JTF_VARCHAR2_TABLE_2000
32 , a10 JTF_VARCHAR2_TABLE_2000
33 , a11 JTF_VARCHAR2_TABLE_2000
34 , a12 JTF_VARCHAR2_TABLE_2000
35 , a13 JTF_VARCHAR2_TABLE_2000
36 , a14 JTF_VARCHAR2_TABLE_2000
37 , a15 JTF_VARCHAR2_TABLE_2000
38 , a16 JTF_VARCHAR2_TABLE_2000
39 , a17 JTF_VARCHAR2_TABLE_2000
40 , a18 JTF_VARCHAR2_TABLE_2000
41 , a19 JTF_VARCHAR2_TABLE_2000
42 ) as
43 ddindx binary_integer; indx binary_integer;
44 begin
45 if a0 is not null and a0.count > 0 then
46 if a0.count > 0 then
47 indx := a0.first;
48 ddindx := 1;
49 while true loop
50 t(ddindx).column1 := a0(indx);
51 t(ddindx).column2 := a1(indx);
52 t(ddindx).column3 := a2(indx);
53 t(ddindx).column4 := a3(indx);
54 t(ddindx).column5 := a4(indx);
55 t(ddindx).column6 := a5(indx);
56 t(ddindx).column7 := a6(indx);
57 t(ddindx).column8 := a7(indx);
58 t(ddindx).column9 := a8(indx);
59 t(ddindx).column10 := a9(indx);
60 t(ddindx).column11 := a10(indx);
61 t(ddindx).column12 := a11(indx);
62 t(ddindx).column13 := a12(indx);
63 t(ddindx).column14 := a13(indx);
64 t(ddindx).column15 := a14(indx);
65 t(ddindx).filter1 := a15(indx);
66 t(ddindx).filter2 := a16(indx);
67 t(ddindx).filter3 := a17(indx);
68 t(ddindx).filter4 := a18(indx);
69 t(ddindx).filter5 := a19(indx);
70 ddindx := ddindx+1;
71 if a0.last =indx
72 then exit;
73 end if;
74 indx := a0.next(indx);
75 end loop;
76 end if;
77 end if;
78 end rosetta_table_copy_in_p2;
79 procedure rosetta_table_copy_out_p2(t jtf_terr_jsp_lov_recs_pub.lov_output_tbl_type, a0 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
80 , a1 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
81 , a2 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
82 , a3 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
83 , a4 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
84 , a5 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
85 , a6 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
86 , a7 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
87 , a8 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
88 , a9 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
89 , a10 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
90 , a11 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
91 , a12 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
92 , a13 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
93 , a14 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
94 , a15 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
95 , a16 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
96 , a17 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
97 , a18 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
98 , a19 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
99 ) as
100 ddindx binary_integer; indx binary_integer;
101 begin
102 if t is null or t.count = 0 then
103 a0 := JTF_VARCHAR2_TABLE_2000();
104 a1 := JTF_VARCHAR2_TABLE_2000();
105 a2 := JTF_VARCHAR2_TABLE_2000();
106 a3 := JTF_VARCHAR2_TABLE_2000();
107 a4 := JTF_VARCHAR2_TABLE_2000();
108 a5 := JTF_VARCHAR2_TABLE_2000();
109 a6 := JTF_VARCHAR2_TABLE_2000();
110 a7 := JTF_VARCHAR2_TABLE_2000();
111 a8 := JTF_VARCHAR2_TABLE_2000();
112 a9 := JTF_VARCHAR2_TABLE_2000();
113 a10 := JTF_VARCHAR2_TABLE_2000();
114 a11 := JTF_VARCHAR2_TABLE_2000();
115 a12 := JTF_VARCHAR2_TABLE_2000();
116 a13 := JTF_VARCHAR2_TABLE_2000();
117 a14 := JTF_VARCHAR2_TABLE_2000();
118 a15 := JTF_VARCHAR2_TABLE_2000();
119 a16 := JTF_VARCHAR2_TABLE_2000();
120 a17 := JTF_VARCHAR2_TABLE_2000();
121 a18 := JTF_VARCHAR2_TABLE_2000();
122 a19 := JTF_VARCHAR2_TABLE_2000();
123 else
124 a0 := JTF_VARCHAR2_TABLE_2000();
125 a1 := JTF_VARCHAR2_TABLE_2000();
126 a2 := JTF_VARCHAR2_TABLE_2000();
127 a3 := JTF_VARCHAR2_TABLE_2000();
128 a4 := JTF_VARCHAR2_TABLE_2000();
129 a5 := JTF_VARCHAR2_TABLE_2000();
130 a6 := JTF_VARCHAR2_TABLE_2000();
131 a7 := JTF_VARCHAR2_TABLE_2000();
132 a8 := JTF_VARCHAR2_TABLE_2000();
133 a9 := JTF_VARCHAR2_TABLE_2000();
134 a10 := JTF_VARCHAR2_TABLE_2000();
135 a11 := JTF_VARCHAR2_TABLE_2000();
136 a12 := JTF_VARCHAR2_TABLE_2000();
137 a13 := JTF_VARCHAR2_TABLE_2000();
138 a14 := JTF_VARCHAR2_TABLE_2000();
139 a15 := JTF_VARCHAR2_TABLE_2000();
140 a16 := JTF_VARCHAR2_TABLE_2000();
141 a17 := JTF_VARCHAR2_TABLE_2000();
142 a18 := JTF_VARCHAR2_TABLE_2000();
143 a19 := JTF_VARCHAR2_TABLE_2000();
144 if t.count > 0 then
145 a0.extend(t.count);
146 a1.extend(t.count);
147 a2.extend(t.count);
148 a3.extend(t.count);
149 a4.extend(t.count);
150 a5.extend(t.count);
151 a6.extend(t.count);
152 a7.extend(t.count);
153 a8.extend(t.count);
154 a9.extend(t.count);
155 a10.extend(t.count);
156 a11.extend(t.count);
157 a12.extend(t.count);
158 a13.extend(t.count);
159 a14.extend(t.count);
160 a15.extend(t.count);
161 a16.extend(t.count);
162 a17.extend(t.count);
163 a18.extend(t.count);
164 a19.extend(t.count);
165 ddindx := t.first;
166 indx := 1;
167 while true loop
168 a0(indx) := t(ddindx).column1;
169 a1(indx) := t(ddindx).column2;
170 a2(indx) := t(ddindx).column3;
171 a3(indx) := t(ddindx).column4;
172 a4(indx) := t(ddindx).column5;
173 a5(indx) := t(ddindx).column6;
174 a6(indx) := t(ddindx).column7;
175 a7(indx) := t(ddindx).column8;
176 a8(indx) := t(ddindx).column9;
177 a9(indx) := t(ddindx).column10;
178 a10(indx) := t(ddindx).column11;
179 a11(indx) := t(ddindx).column12;
180 a12(indx) := t(ddindx).column13;
181 a13(indx) := t(ddindx).column14;
182 a14(indx) := t(ddindx).column15;
183 a15(indx) := t(ddindx).filter1;
184 a16(indx) := t(ddindx).filter2;
185 a17(indx) := t(ddindx).filter3;
186 a18(indx) := t(ddindx).filter4;
187 a19(indx) := t(ddindx).filter5;
188 indx := indx+1;
189 if t.last =ddindx
190 then exit;
191 end if;
192 ddindx := t.next(ddindx);
193 end loop;
194 end if;
195 end if;
196 end rosetta_table_copy_out_p2;
197
198 procedure rosetta_table_copy_in_p3(t OUT NOCOPY jtf_terr_jsp_lov_recs_pub.lov_disp_format_tbl_type, a0 JTF_NUMBER_TABLE
199 , a1 JTF_VARCHAR2_TABLE_100
200 , a2 JTF_VARCHAR2_TABLE_100
201 ) as
202 ddindx binary_integer; indx binary_integer;
203 begin
204 if a0 is not null and a0.count > 0 then
205 if a0.count > 0 then
206 indx := a0.first;
207 ddindx := 1;
208 while true loop
209 t(ddindx).column_number := a0(indx);
210 t(ddindx).column_display_enable := a1(indx);
211 t(ddindx).column_search_enable := a2(indx);
212 ddindx := ddindx+1;
213 if a0.last =indx
214 then exit;
215 end if;
216 indx := a0.next(indx);
217 end loop;
218 end if;
219 end if;
220 end rosetta_table_copy_in_p3;
221 procedure rosetta_table_copy_out_p3(t jtf_terr_jsp_lov_recs_pub.lov_disp_format_tbl_type, a0 OUT NOCOPY JTF_NUMBER_TABLE
222 , a1 OUT NOCOPY JTF_VARCHAR2_TABLE_100
223 , a2 OUT NOCOPY JTF_VARCHAR2_TABLE_100
224 ) as
225 ddindx binary_integer; indx binary_integer;
226 begin
227 if t is null or t.count = 0 then
228 a0 := JTF_NUMBER_TABLE();
229 a1 := JTF_VARCHAR2_TABLE_100();
230 a2 := JTF_VARCHAR2_TABLE_100();
231 else
232 a0 := JTF_NUMBER_TABLE();
233 a1 := JTF_VARCHAR2_TABLE_100();
234 a2 := JTF_VARCHAR2_TABLE_100();
235 if t.count > 0 then
236 a0.extend(t.count);
237 a1.extend(t.count);
238 a2.extend(t.count);
239 ddindx := t.first;
240 indx := 1;
241 while true loop
242 a0(indx) := t(ddindx).column_number;
243 a1(indx) := t(ddindx).column_display_enable;
244 a2(indx) := t(ddindx).column_search_enable;
245 indx := indx+1;
246 if t.last =ddindx
247 then exit;
248 end if;
249 ddindx := t.next(ddindx);
250 end loop;
251 end if;
252 end if;
253 end rosetta_table_copy_out_p3;
254
255 procedure get_lov_records(p_range_low NUMBER
256 , p_range_high NUMBER
257 , p_record_group_name VARCHAR2
258 , p3_a0 VARCHAR2
259 , p3_a1 VARCHAR2
260 , p3_a2 VARCHAR2
261 , p3_a3 VARCHAR2
262 , p3_a4 VARCHAR2
263 , p3_a5 VARCHAR2
264 , p3_a6 VARCHAR2
265 , p3_a7 VARCHAR2
266 , p3_a8 VARCHAR2
267 , p3_a9 VARCHAR2
268 , p3_a10 VARCHAR2
269 , p3_a11 VARCHAR2
270 , p3_a12 VARCHAR2
271 , p3_a13 VARCHAR2
272 , p3_a14 VARCHAR2
273 , p3_a15 VARCHAR2
274 , p3_a16 VARCHAR2
275 , p3_a17 VARCHAR2
276 , p3_a18 VARCHAR2
277 , p3_a19 VARCHAR2
278 , x_total_rows OUT NOCOPY NUMBER
279 , x_more_data_flag OUT NOCOPY VARCHAR2
280 , x_lov_ak_region OUT NOCOPY VARCHAR2
281 , p7_a0 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
282 , p7_a1 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
283 , p7_a2 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
284 , p7_a3 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
285 , p7_a4 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
286 , p7_a5 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
287 , p7_a6 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
288 , p7_a7 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
289 , p7_a8 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
290 , p7_a9 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
291 , p7_a10 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
292 , p7_a11 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
293 , p7_a12 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
294 , p7_a13 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
295 , p7_a14 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
296 , p7_a15 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
297 , p7_a16 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
298 , p7_a17 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
299 , p7_a18 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
300 , p7_a19 OUT NOCOPY JTF_VARCHAR2_TABLE_2000
301 , p8_a0 OUT NOCOPY JTF_NUMBER_TABLE
302 , p8_a1 OUT NOCOPY JTF_VARCHAR2_TABLE_100
303 , p8_a2 OUT NOCOPY JTF_VARCHAR2_TABLE_100
304 )
305 as
306 ddp_in_filter_lov_rec jtf_terr_jsp_lov_recs_pub.lov_inout_rec_type;
307 ddx_result_tbl jtf_terr_jsp_lov_recs_pub.lov_output_tbl_type;
308 ddx_disp_format_tbl jtf_terr_jsp_lov_recs_pub.lov_disp_format_tbl_type;
309 ddindx binary_integer; indx binary_integer;
310 begin
311
312 -- copy data to the local IN or IN-OUT NOCOPY args, if any
313
314
315
316 ddp_in_filter_lov_rec.column1 := p3_a0;
317 ddp_in_filter_lov_rec.column2 := p3_a1;
318 ddp_in_filter_lov_rec.column3 := p3_a2;
319 ddp_in_filter_lov_rec.column4 := p3_a3;
320 ddp_in_filter_lov_rec.column5 := p3_a4;
321 ddp_in_filter_lov_rec.column6 := p3_a5;
322 ddp_in_filter_lov_rec.column7 := p3_a6;
323 ddp_in_filter_lov_rec.column8 := p3_a7;
324 ddp_in_filter_lov_rec.column9 := p3_a8;
325 ddp_in_filter_lov_rec.column10 := p3_a9;
326 ddp_in_filter_lov_rec.column11 := p3_a10;
327 ddp_in_filter_lov_rec.column12 := p3_a11;
328 ddp_in_filter_lov_rec.column13 := p3_a12;
329 ddp_in_filter_lov_rec.column14 := p3_a13;
330 ddp_in_filter_lov_rec.column15 := p3_a14;
331 ddp_in_filter_lov_rec.filter1 := p3_a15;
332 ddp_in_filter_lov_rec.filter2 := p3_a16;
333 ddp_in_filter_lov_rec.filter3 := p3_a17;
334 ddp_in_filter_lov_rec.filter4 := p3_a18;
335 ddp_in_filter_lov_rec.filter5 := p3_a19;
336
337
338
339
340
341
342 -- here's the delegated call to the old PL/SQL routine
343 jtf_terr_jsp_lov_recs_pub.get_lov_records(p_range_low,
344 p_range_high,
345 p_record_group_name,
346 ddp_in_filter_lov_rec,
347 x_total_rows,
348 x_more_data_flag,
349 x_lov_ak_region,
350 ddx_result_tbl,
351 ddx_disp_format_tbl);
352
353 -- copy data back from the local OUT NOCOPY or IN-OUT NOCOPY args, if any
354
355
356
357
358
359
360
361 jtf_terr_jsp_lov_recs_pub_w.rosetta_table_copy_out_p2(ddx_result_tbl, p7_a0
362 , p7_a1
363 , p7_a2
364 , p7_a3
365 , p7_a4
366 , p7_a5
367 , p7_a6
368 , p7_a7
369 , p7_a8
370 , p7_a9
371 , p7_a10
372 , p7_a11
373 , p7_a12
374 , p7_a13
375 , p7_a14
376 , p7_a15
377 , p7_a16
378 , p7_a17
379 , p7_a18
380 , p7_a19
381 );
382
383 jtf_terr_jsp_lov_recs_pub_w.rosetta_table_copy_out_p3(ddx_disp_format_tbl, p8_a0
384 , p8_a1
385 , p8_a2
386 );
387 end;
388
389 end jtf_terr_jsp_lov_recs_pub_w;