[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_SPACE_SCHEDULE_PVT_W
Source
1 package body ahl_ltp_space_schedule_pvt_w as
2 /* $Header: AHLWSPSB.pls 120.1 2006/05/04 07:59 anraj noship $ */
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 rosetta_g_mistake_date_high date := to_date('01/01/+4710', 'MM/DD/SYYYY');
6 rosetta_g_mistake_date_low date := to_date('01/01/-4710', 'MM/DD/SYYYY');
7
8 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
9 function rosetta_g_miss_date_in_map(d date) return date as
10 begin
11 if d > rosetta_g_mistake_date_high then return fnd_api.g_miss_date; end if;
12 if d < rosetta_g_mistake_date_low then return fnd_api.g_miss_date; end if;
13 return d;
14 end;
15
16 procedure rosetta_table_copy_in_p5(t out nocopy ahl_ltp_space_schedule_pvt.search_visits_tbl, a0 JTF_VARCHAR2_TABLE_300
17 , a1 JTF_NUMBER_TABLE
18 , a2 JTF_VARCHAR2_TABLE_100
19 , a3 JTF_NUMBER_TABLE
20 , a4 JTF_VARCHAR2_TABLE_100
21 , a5 JTF_VARCHAR2_TABLE_100
22 , a6 JTF_NUMBER_TABLE
23 , a7 JTF_VARCHAR2_TABLE_100
24 , a8 JTF_VARCHAR2_TABLE_100
25 , a9 JTF_VARCHAR2_TABLE_100
26 , a10 JTF_VARCHAR2_TABLE_100
27 , a11 JTF_NUMBER_TABLE
28 , a12 JTF_VARCHAR2_TABLE_100
29 , a13 JTF_NUMBER_TABLE
30 , a14 JTF_VARCHAR2_TABLE_100
31 , a15 JTF_VARCHAR2_TABLE_100
32 , a16 JTF_VARCHAR2_TABLE_100
33 , a17 JTF_DATE_TABLE
34 , a18 JTF_DATE_TABLE
35 , a19 JTF_DATE_TABLE
36 ) as
37 ddindx binary_integer; indx binary_integer;
38 begin
39 if a0 is not null and a0.count > 0 then
40 if a0.count > 0 then
41 indx := a0.first;
42 ddindx := 1;
43 while true loop
44 t(ddindx).org_name := a0(indx);
45 t(ddindx).org_id := a1(indx);
46 t(ddindx).department_name := a2(indx);
47 t(ddindx).department_id := a3(indx);
48 t(ddindx).department_code := a4(indx);
49 t(ddindx).space_name := a5(indx);
50 t(ddindx).space_id := a6(indx);
51 t(ddindx).space_category := a7(indx);
52 t(ddindx).space_category_mean := a8(indx);
53 t(ddindx).visit_type_code := a9(indx);
54 t(ddindx).visit_type_mean := a10(indx);
55 t(ddindx).item_id := a11(indx);
56 t(ddindx).item_description := a12(indx);
57 t(ddindx).plan_id := a13(indx);
58 t(ddindx).plan_name := a14(indx);
59 t(ddindx).display_period_code := a15(indx);
60 t(ddindx).display_period_mean := a16(indx);
61 t(ddindx).start_date := rosetta_g_miss_date_in_map(a17(indx));
62 t(ddindx).start_period := rosetta_g_miss_date_in_map(a18(indx));
63 t(ddindx).end_period := rosetta_g_miss_date_in_map(a19(indx));
64 ddindx := ddindx+1;
65 if a0.last =indx
66 then exit;
67 end if;
68 indx := a0.next(indx);
69 end loop;
70 end if;
71 end if;
72 end rosetta_table_copy_in_p5;
73 procedure rosetta_table_copy_out_p5(t ahl_ltp_space_schedule_pvt.search_visits_tbl, a0 out nocopy JTF_VARCHAR2_TABLE_300
74 , a1 out nocopy JTF_NUMBER_TABLE
75 , a2 out nocopy JTF_VARCHAR2_TABLE_100
76 , a3 out nocopy JTF_NUMBER_TABLE
77 , a4 out nocopy JTF_VARCHAR2_TABLE_100
78 , a5 out nocopy JTF_VARCHAR2_TABLE_100
79 , a6 out nocopy JTF_NUMBER_TABLE
80 , a7 out nocopy JTF_VARCHAR2_TABLE_100
81 , a8 out nocopy JTF_VARCHAR2_TABLE_100
82 , a9 out nocopy JTF_VARCHAR2_TABLE_100
83 , a10 out nocopy JTF_VARCHAR2_TABLE_100
84 , a11 out nocopy JTF_NUMBER_TABLE
85 , a12 out nocopy JTF_VARCHAR2_TABLE_100
86 , a13 out nocopy JTF_NUMBER_TABLE
87 , a14 out nocopy JTF_VARCHAR2_TABLE_100
88 , a15 out nocopy JTF_VARCHAR2_TABLE_100
89 , a16 out nocopy JTF_VARCHAR2_TABLE_100
90 , a17 out nocopy JTF_DATE_TABLE
91 , a18 out nocopy JTF_DATE_TABLE
92 , a19 out nocopy JTF_DATE_TABLE
93 ) as
94 ddindx binary_integer; indx binary_integer;
95 begin
96 if t is null or t.count = 0 then
97 a0 := JTF_VARCHAR2_TABLE_300();
98 a1 := JTF_NUMBER_TABLE();
99 a2 := JTF_VARCHAR2_TABLE_100();
100 a3 := JTF_NUMBER_TABLE();
101 a4 := JTF_VARCHAR2_TABLE_100();
102 a5 := JTF_VARCHAR2_TABLE_100();
103 a6 := JTF_NUMBER_TABLE();
104 a7 := JTF_VARCHAR2_TABLE_100();
105 a8 := JTF_VARCHAR2_TABLE_100();
106 a9 := JTF_VARCHAR2_TABLE_100();
107 a10 := JTF_VARCHAR2_TABLE_100();
108 a11 := JTF_NUMBER_TABLE();
109 a12 := JTF_VARCHAR2_TABLE_100();
110 a13 := JTF_NUMBER_TABLE();
111 a14 := JTF_VARCHAR2_TABLE_100();
112 a15 := JTF_VARCHAR2_TABLE_100();
113 a16 := JTF_VARCHAR2_TABLE_100();
114 a17 := JTF_DATE_TABLE();
115 a18 := JTF_DATE_TABLE();
116 a19 := JTF_DATE_TABLE();
117 else
118 a0 := JTF_VARCHAR2_TABLE_300();
119 a1 := JTF_NUMBER_TABLE();
120 a2 := JTF_VARCHAR2_TABLE_100();
121 a3 := JTF_NUMBER_TABLE();
122 a4 := JTF_VARCHAR2_TABLE_100();
123 a5 := JTF_VARCHAR2_TABLE_100();
124 a6 := JTF_NUMBER_TABLE();
125 a7 := JTF_VARCHAR2_TABLE_100();
126 a8 := JTF_VARCHAR2_TABLE_100();
127 a9 := JTF_VARCHAR2_TABLE_100();
128 a10 := JTF_VARCHAR2_TABLE_100();
129 a11 := JTF_NUMBER_TABLE();
130 a12 := JTF_VARCHAR2_TABLE_100();
131 a13 := JTF_NUMBER_TABLE();
132 a14 := JTF_VARCHAR2_TABLE_100();
133 a15 := JTF_VARCHAR2_TABLE_100();
134 a16 := JTF_VARCHAR2_TABLE_100();
135 a17 := JTF_DATE_TABLE();
136 a18 := JTF_DATE_TABLE();
137 a19 := JTF_DATE_TABLE();
138 if t.count > 0 then
139 a0.extend(t.count);
140 a1.extend(t.count);
141 a2.extend(t.count);
142 a3.extend(t.count);
143 a4.extend(t.count);
144 a5.extend(t.count);
145 a6.extend(t.count);
146 a7.extend(t.count);
147 a8.extend(t.count);
148 a9.extend(t.count);
149 a10.extend(t.count);
150 a11.extend(t.count);
151 a12.extend(t.count);
152 a13.extend(t.count);
153 a14.extend(t.count);
154 a15.extend(t.count);
155 a16.extend(t.count);
156 a17.extend(t.count);
157 a18.extend(t.count);
158 a19.extend(t.count);
159 ddindx := t.first;
160 indx := 1;
161 while true loop
162 a0(indx) := t(ddindx).org_name;
163 a1(indx) := t(ddindx).org_id;
164 a2(indx) := t(ddindx).department_name;
165 a3(indx) := t(ddindx).department_id;
166 a4(indx) := t(ddindx).department_code;
167 a5(indx) := t(ddindx).space_name;
168 a6(indx) := t(ddindx).space_id;
169 a7(indx) := t(ddindx).space_category;
170 a8(indx) := t(ddindx).space_category_mean;
171 a9(indx) := t(ddindx).visit_type_code;
172 a10(indx) := t(ddindx).visit_type_mean;
173 a11(indx) := t(ddindx).item_id;
174 a12(indx) := t(ddindx).item_description;
175 a13(indx) := t(ddindx).plan_id;
176 a14(indx) := t(ddindx).plan_name;
177 a15(indx) := t(ddindx).display_period_code;
178 a16(indx) := t(ddindx).display_period_mean;
179 a17(indx) := t(ddindx).start_date;
180 a18(indx) := t(ddindx).start_period;
181 a19(indx) := t(ddindx).end_period;
182 indx := indx+1;
183 if t.last =ddindx
184 then exit;
185 end if;
186 ddindx := t.next(ddindx);
187 end loop;
188 end if;
189 end if;
190 end rosetta_table_copy_out_p5;
191
192 procedure rosetta_table_copy_in_p6(t out nocopy ahl_ltp_space_schedule_pvt.scheduled_visits_tbl, a0 JTF_VARCHAR2_TABLE_300
193 , a1 JTF_VARCHAR2_TABLE_100
194 , a2 JTF_NUMBER_TABLE
195 , a3 JTF_VARCHAR2_TABLE_100
196 , a4 JTF_VARCHAR2_TABLE_100
197 , a5 JTF_NUMBER_TABLE
198 , a6 JTF_VARCHAR2_TABLE_100
199 , a7 JTF_VARCHAR2_TABLE_100
200 , a8 JTF_VARCHAR2_TABLE_100
201 , a9 JTF_VARCHAR2_TABLE_100
202 , a10 JTF_VARCHAR2_TABLE_100
203 , a11 JTF_VARCHAR2_TABLE_100
204 , a12 JTF_VARCHAR2_TABLE_100
205 , a13 JTF_VARCHAR2_TABLE_100
206 , a14 JTF_VARCHAR2_TABLE_100
207 , a15 JTF_VARCHAR2_TABLE_100
208 , a16 JTF_VARCHAR2_TABLE_100
209 , a17 JTF_VARCHAR2_TABLE_100
210 , a18 JTF_VARCHAR2_TABLE_100
211 , a19 JTF_VARCHAR2_TABLE_100
212 , a20 JTF_VARCHAR2_TABLE_100
213 , a21 JTF_VARCHAR2_TABLE_100
214 ) as
215 ddindx binary_integer; indx binary_integer;
216 begin
217 if a0 is not null and a0.count > 0 then
218 if a0.count > 0 then
219 indx := a0.first;
220 ddindx := 1;
221 while true loop
222 t(ddindx).org_name := a0(indx);
223 t(ddindx).department_name := a1(indx);
224 t(ddindx).department_id := a2(indx);
225 t(ddindx).department_code := a3(indx);
226 t(ddindx).space_name := a4(indx);
227 t(ddindx).space_id := a5(indx);
228 t(ddindx).space_category := a6(indx);
229 t(ddindx).space_category_mean := a7(indx);
230 t(ddindx).value_1 := a8(indx);
231 t(ddindx).value_2 := a9(indx);
232 t(ddindx).value_3 := a10(indx);
233 t(ddindx).value_4 := a11(indx);
234 t(ddindx).value_5 := a12(indx);
235 t(ddindx).value_6 := a13(indx);
236 t(ddindx).value_7 := a14(indx);
237 t(ddindx).value_8 := a15(indx);
238 t(ddindx).value_9 := a16(indx);
239 t(ddindx).value_10 := a17(indx);
240 t(ddindx).value_11 := a18(indx);
241 t(ddindx).value_12 := a19(indx);
242 t(ddindx).value_13 := a20(indx);
243 t(ddindx).value_14 := a21(indx);
244 ddindx := ddindx+1;
245 if a0.last =indx
246 then exit;
247 end if;
248 indx := a0.next(indx);
249 end loop;
250 end if;
251 end if;
252 end rosetta_table_copy_in_p6;
253 procedure rosetta_table_copy_out_p6(t ahl_ltp_space_schedule_pvt.scheduled_visits_tbl, a0 out nocopy JTF_VARCHAR2_TABLE_300
254 , a1 out nocopy JTF_VARCHAR2_TABLE_100
255 , a2 out nocopy JTF_NUMBER_TABLE
256 , a3 out nocopy JTF_VARCHAR2_TABLE_100
257 , a4 out nocopy JTF_VARCHAR2_TABLE_100
258 , a5 out nocopy JTF_NUMBER_TABLE
259 , a6 out nocopy JTF_VARCHAR2_TABLE_100
260 , a7 out nocopy JTF_VARCHAR2_TABLE_100
261 , a8 out nocopy JTF_VARCHAR2_TABLE_100
262 , a9 out nocopy JTF_VARCHAR2_TABLE_100
263 , a10 out nocopy JTF_VARCHAR2_TABLE_100
264 , a11 out nocopy JTF_VARCHAR2_TABLE_100
265 , a12 out nocopy JTF_VARCHAR2_TABLE_100
266 , a13 out nocopy JTF_VARCHAR2_TABLE_100
267 , a14 out nocopy JTF_VARCHAR2_TABLE_100
268 , a15 out nocopy JTF_VARCHAR2_TABLE_100
269 , a16 out nocopy JTF_VARCHAR2_TABLE_100
270 , a17 out nocopy JTF_VARCHAR2_TABLE_100
271 , a18 out nocopy JTF_VARCHAR2_TABLE_100
272 , a19 out nocopy JTF_VARCHAR2_TABLE_100
273 , a20 out nocopy JTF_VARCHAR2_TABLE_100
274 , a21 out nocopy JTF_VARCHAR2_TABLE_100
275 ) as
276 ddindx binary_integer; indx binary_integer;
277 begin
278 if t is null or t.count = 0 then
279 a0 := JTF_VARCHAR2_TABLE_300();
280 a1 := JTF_VARCHAR2_TABLE_100();
281 a2 := JTF_NUMBER_TABLE();
282 a3 := JTF_VARCHAR2_TABLE_100();
283 a4 := JTF_VARCHAR2_TABLE_100();
284 a5 := JTF_NUMBER_TABLE();
285 a6 := JTF_VARCHAR2_TABLE_100();
286 a7 := JTF_VARCHAR2_TABLE_100();
287 a8 := JTF_VARCHAR2_TABLE_100();
288 a9 := JTF_VARCHAR2_TABLE_100();
289 a10 := JTF_VARCHAR2_TABLE_100();
290 a11 := JTF_VARCHAR2_TABLE_100();
291 a12 := JTF_VARCHAR2_TABLE_100();
292 a13 := JTF_VARCHAR2_TABLE_100();
293 a14 := JTF_VARCHAR2_TABLE_100();
294 a15 := JTF_VARCHAR2_TABLE_100();
295 a16 := JTF_VARCHAR2_TABLE_100();
296 a17 := JTF_VARCHAR2_TABLE_100();
297 a18 := JTF_VARCHAR2_TABLE_100();
298 a19 := JTF_VARCHAR2_TABLE_100();
299 a20 := JTF_VARCHAR2_TABLE_100();
300 a21 := JTF_VARCHAR2_TABLE_100();
301 else
302 a0 := JTF_VARCHAR2_TABLE_300();
303 a1 := JTF_VARCHAR2_TABLE_100();
304 a2 := JTF_NUMBER_TABLE();
305 a3 := JTF_VARCHAR2_TABLE_100();
306 a4 := JTF_VARCHAR2_TABLE_100();
307 a5 := JTF_NUMBER_TABLE();
308 a6 := JTF_VARCHAR2_TABLE_100();
309 a7 := JTF_VARCHAR2_TABLE_100();
310 a8 := JTF_VARCHAR2_TABLE_100();
311 a9 := JTF_VARCHAR2_TABLE_100();
312 a10 := JTF_VARCHAR2_TABLE_100();
313 a11 := JTF_VARCHAR2_TABLE_100();
314 a12 := JTF_VARCHAR2_TABLE_100();
315 a13 := JTF_VARCHAR2_TABLE_100();
316 a14 := JTF_VARCHAR2_TABLE_100();
317 a15 := JTF_VARCHAR2_TABLE_100();
318 a16 := JTF_VARCHAR2_TABLE_100();
319 a17 := JTF_VARCHAR2_TABLE_100();
320 a18 := JTF_VARCHAR2_TABLE_100();
321 a19 := JTF_VARCHAR2_TABLE_100();
322 a20 := JTF_VARCHAR2_TABLE_100();
323 a21 := JTF_VARCHAR2_TABLE_100();
324 if t.count > 0 then
328 a3.extend(t.count);
325 a0.extend(t.count);
326 a1.extend(t.count);
327 a2.extend(t.count);
329 a4.extend(t.count);
330 a5.extend(t.count);
331 a6.extend(t.count);
332 a7.extend(t.count);
333 a8.extend(t.count);
334 a9.extend(t.count);
335 a10.extend(t.count);
336 a11.extend(t.count);
337 a12.extend(t.count);
338 a13.extend(t.count);
339 a14.extend(t.count);
340 a15.extend(t.count);
341 a16.extend(t.count);
342 a17.extend(t.count);
343 a18.extend(t.count);
344 a19.extend(t.count);
345 a20.extend(t.count);
346 a21.extend(t.count);
347 ddindx := t.first;
348 indx := 1;
349 while true loop
350 a0(indx) := t(ddindx).org_name;
351 a1(indx) := t(ddindx).department_name;
352 a2(indx) := t(ddindx).department_id;
353 a3(indx) := t(ddindx).department_code;
354 a4(indx) := t(ddindx).space_name;
355 a5(indx) := t(ddindx).space_id;
356 a6(indx) := t(ddindx).space_category;
357 a7(indx) := t(ddindx).space_category_mean;
358 a8(indx) := t(ddindx).value_1;
359 a9(indx) := t(ddindx).value_2;
360 a10(indx) := t(ddindx).value_3;
361 a11(indx) := t(ddindx).value_4;
362 a12(indx) := t(ddindx).value_5;
363 a13(indx) := t(ddindx).value_6;
364 a14(indx) := t(ddindx).value_7;
365 a15(indx) := t(ddindx).value_8;
366 a16(indx) := t(ddindx).value_9;
367 a17(indx) := t(ddindx).value_10;
368 a18(indx) := t(ddindx).value_11;
369 a19(indx) := t(ddindx).value_12;
370 a20(indx) := t(ddindx).value_13;
371 a21(indx) := t(ddindx).value_14;
372 indx := indx+1;
373 if t.last =ddindx
374 then exit;
375 end if;
376 ddindx := t.next(ddindx);
377 end loop;
378 end if;
379 end if;
380 end rosetta_table_copy_out_p6;
381
382 procedure rosetta_table_copy_in_p7(t out nocopy ahl_ltp_space_schedule_pvt.visits_end_date_tbl, a0 JTF_NUMBER_TABLE
383 , a1 JTF_DATE_TABLE
384 ) as
385 ddindx binary_integer; indx binary_integer;
386 begin
387 if a0 is not null and a0.count > 0 then
388 if a0.count > 0 then
389 indx := a0.first;
390 ddindx := 1;
391 while true loop
392 t(ddindx).visit_id := a0(indx);
393 t(ddindx).visit_end_date := rosetta_g_miss_date_in_map(a1(indx));
394 ddindx := ddindx+1;
395 if a0.last =indx
396 then exit;
397 end if;
398 indx := a0.next(indx);
399 end loop;
400 end if;
401 end if;
402 end rosetta_table_copy_in_p7;
403 procedure rosetta_table_copy_out_p7(t ahl_ltp_space_schedule_pvt.visits_end_date_tbl, a0 out nocopy JTF_NUMBER_TABLE
404 , a1 out nocopy JTF_DATE_TABLE
405 ) as
406 ddindx binary_integer; indx binary_integer;
407 begin
408 if t is null or t.count = 0 then
409 a0 := JTF_NUMBER_TABLE();
410 a1 := JTF_DATE_TABLE();
411 else
412 a0 := JTF_NUMBER_TABLE();
413 a1 := JTF_DATE_TABLE();
414 if t.count > 0 then
415 a0.extend(t.count);
416 a1.extend(t.count);
417 ddindx := t.first;
418 indx := 1;
419 while true loop
420 a0(indx) := t(ddindx).visit_id;
421 a1(indx) := t(ddindx).visit_end_date;
422 indx := indx+1;
423 if t.last =ddindx
424 then exit;
425 end if;
426 ddindx := t.next(ddindx);
427 end loop;
428 end if;
429 end if;
430 end rosetta_table_copy_out_p7;
431
432 procedure rosetta_table_copy_in_p8(t out nocopy ahl_ltp_space_schedule_pvt.visit_details_tbl, a0 JTF_NUMBER_TABLE
433 , a1 JTF_VARCHAR2_TABLE_100
434 , a2 JTF_VARCHAR2_TABLE_100
435 , a3 JTF_NUMBER_TABLE
436 , a4 JTF_VARCHAR2_TABLE_100
437 , a5 JTF_VARCHAR2_TABLE_100
438 , a6 JTF_VARCHAR2_TABLE_100
439 , a7 JTF_VARCHAR2_TABLE_300
440 , a8 JTF_VARCHAR2_TABLE_100
441 , a9 JTF_VARCHAR2_TABLE_100
442 , a10 JTF_DATE_TABLE
443 , a11 JTF_DATE_TABLE
444 , a12 JTF_DATE_TABLE
445 ) as
446 ddindx binary_integer; indx binary_integer;
447 begin
448 if a0 is not null and a0.count > 0 then
449 if a0.count > 0 then
450 indx := a0.first;
451 ddindx := 1;
452 while true loop
453 t(ddindx).visit_number := a0(indx);
454 t(ddindx).visit_type := a1(indx);
455 t(ddindx).visit_name := a2(indx);
456 t(ddindx).visit_id := a3(indx);
457 t(ddindx).visit_status := a4(indx);
458 t(ddindx).item_description := a5(indx);
459 t(ddindx).serial_number := a6(indx);
460 t(ddindx).unit_name := a7(indx);
461 t(ddindx).yes_no_type := a8(indx);
462 t(ddindx).plan_flag := a9(indx);
463 t(ddindx).start_date := rosetta_g_miss_date_in_map(a10(indx));
467 if a0.last =indx
464 t(ddindx).end_date := rosetta_g_miss_date_in_map(a11(indx));
465 t(ddindx).due_by := rosetta_g_miss_date_in_map(a12(indx));
466 ddindx := ddindx+1;
468 then exit;
469 end if;
470 indx := a0.next(indx);
471 end loop;
472 end if;
473 end if;
474 end rosetta_table_copy_in_p8;
475 procedure rosetta_table_copy_out_p8(t ahl_ltp_space_schedule_pvt.visit_details_tbl, a0 out nocopy JTF_NUMBER_TABLE
476 , a1 out nocopy JTF_VARCHAR2_TABLE_100
477 , a2 out nocopy JTF_VARCHAR2_TABLE_100
478 , a3 out nocopy JTF_NUMBER_TABLE
479 , a4 out nocopy JTF_VARCHAR2_TABLE_100
480 , a5 out nocopy JTF_VARCHAR2_TABLE_100
481 , a6 out nocopy JTF_VARCHAR2_TABLE_100
482 , a7 out nocopy JTF_VARCHAR2_TABLE_300
483 , a8 out nocopy JTF_VARCHAR2_TABLE_100
484 , a9 out nocopy JTF_VARCHAR2_TABLE_100
485 , a10 out nocopy JTF_DATE_TABLE
486 , a11 out nocopy JTF_DATE_TABLE
487 , a12 out nocopy JTF_DATE_TABLE
488 ) as
489 ddindx binary_integer; indx binary_integer;
490 begin
491 if t is null or t.count = 0 then
492 a0 := JTF_NUMBER_TABLE();
493 a1 := JTF_VARCHAR2_TABLE_100();
494 a2 := JTF_VARCHAR2_TABLE_100();
495 a3 := JTF_NUMBER_TABLE();
496 a4 := JTF_VARCHAR2_TABLE_100();
497 a5 := JTF_VARCHAR2_TABLE_100();
498 a6 := JTF_VARCHAR2_TABLE_100();
499 a7 := JTF_VARCHAR2_TABLE_300();
500 a8 := JTF_VARCHAR2_TABLE_100();
501 a9 := JTF_VARCHAR2_TABLE_100();
502 a10 := JTF_DATE_TABLE();
503 a11 := JTF_DATE_TABLE();
504 a12 := JTF_DATE_TABLE();
505 else
506 a0 := JTF_NUMBER_TABLE();
507 a1 := JTF_VARCHAR2_TABLE_100();
508 a2 := JTF_VARCHAR2_TABLE_100();
509 a3 := JTF_NUMBER_TABLE();
510 a4 := JTF_VARCHAR2_TABLE_100();
511 a5 := JTF_VARCHAR2_TABLE_100();
512 a6 := JTF_VARCHAR2_TABLE_100();
513 a7 := JTF_VARCHAR2_TABLE_300();
514 a8 := JTF_VARCHAR2_TABLE_100();
515 a9 := JTF_VARCHAR2_TABLE_100();
516 a10 := JTF_DATE_TABLE();
517 a11 := JTF_DATE_TABLE();
518 a12 := JTF_DATE_TABLE();
519 if t.count > 0 then
520 a0.extend(t.count);
521 a1.extend(t.count);
522 a2.extend(t.count);
523 a3.extend(t.count);
524 a4.extend(t.count);
525 a5.extend(t.count);
526 a6.extend(t.count);
527 a7.extend(t.count);
528 a8.extend(t.count);
529 a9.extend(t.count);
530 a10.extend(t.count);
531 a11.extend(t.count);
532 a12.extend(t.count);
533 ddindx := t.first;
534 indx := 1;
535 while true loop
536 a0(indx) := t(ddindx).visit_number;
537 a1(indx) := t(ddindx).visit_type;
538 a2(indx) := t(ddindx).visit_name;
539 a3(indx) := t(ddindx).visit_id;
540 a4(indx) := t(ddindx).visit_status;
541 a5(indx) := t(ddindx).item_description;
542 a6(indx) := t(ddindx).serial_number;
543 a7(indx) := t(ddindx).unit_name;
544 a8(indx) := t(ddindx).yes_no_type;
545 a9(indx) := t(ddindx).plan_flag;
546 a10(indx) := t(ddindx).start_date;
547 a11(indx) := t(ddindx).end_date;
548 a12(indx) := t(ddindx).due_by;
549 indx := indx+1;
550 if t.last =ddindx
551 then exit;
552 end if;
553 ddindx := t.next(ddindx);
554 end loop;
555 end if;
556 end if;
557 end rosetta_table_copy_out_p8;
558
559 procedure derive_visit_end_date(p0_a0 in out nocopy JTF_NUMBER_TABLE
560 , p0_a1 in out nocopy JTF_DATE_TABLE
561 , x_return_status out nocopy VARCHAR2
562 , x_msg_count out nocopy NUMBER
563 , x_msg_data out nocopy VARCHAR2
564 )
565
566 as
567 ddp_visits_end_date_tbl ahl_ltp_space_schedule_pvt.visits_end_date_tbl;
568 ddindx binary_integer; indx binary_integer;
569 begin
570
571 -- copy data to the local IN or IN-OUT args, if any
572 ahl_ltp_space_schedule_pvt_w.rosetta_table_copy_in_p7(ddp_visits_end_date_tbl, p0_a0
573 , p0_a1
574 );
575
576
577
578
579 -- here's the delegated call to the old PL/SQL routine
580 ahl_ltp_space_schedule_pvt.derive_visit_end_date(ddp_visits_end_date_tbl,
581 x_return_status,
582 x_msg_count,
583 x_msg_data);
584
585 -- copy data back from the local variables to OUT or IN-OUT args, if any
586 ahl_ltp_space_schedule_pvt_w.rosetta_table_copy_out_p7(ddp_visits_end_date_tbl, p0_a0
587 , p0_a1
588 );
589
590
591
592 end;
593
594 procedure search_scheduled_visits(p_api_version NUMBER
595 , p_init_msg_list VARCHAR2
596 , p_commit VARCHAR2
597 , p_validation_level NUMBER
598 , p_module_type VARCHAR2
599 , p5_a0 VARCHAR2
600 , p5_a1 NUMBER
601 , p5_a2 VARCHAR2
602 , p5_a3 NUMBER
603 , p5_a4 VARCHAR2
604 , p5_a5 VARCHAR2
605 , p5_a6 NUMBER
606 , p5_a7 VARCHAR2
607 , p5_a8 VARCHAR2
608 , p5_a9 VARCHAR2
612 , p5_a13 NUMBER
609 , p5_a10 VARCHAR2
610 , p5_a11 NUMBER
611 , p5_a12 VARCHAR2
613 , p5_a14 VARCHAR2
614 , p5_a15 VARCHAR2
615 , p5_a16 VARCHAR2
616 , p5_a17 DATE
617 , p5_a18 DATE
618 , p5_a19 DATE
619 , p6_a0 out nocopy JTF_VARCHAR2_TABLE_300
620 , p6_a1 out nocopy JTF_VARCHAR2_TABLE_100
621 , p6_a2 out nocopy JTF_NUMBER_TABLE
622 , p6_a3 out nocopy JTF_VARCHAR2_TABLE_100
623 , p6_a4 out nocopy JTF_VARCHAR2_TABLE_100
624 , p6_a5 out nocopy JTF_NUMBER_TABLE
625 , p6_a6 out nocopy JTF_VARCHAR2_TABLE_100
626 , p6_a7 out nocopy JTF_VARCHAR2_TABLE_100
627 , p6_a8 out nocopy JTF_VARCHAR2_TABLE_100
628 , p6_a9 out nocopy JTF_VARCHAR2_TABLE_100
629 , p6_a10 out nocopy JTF_VARCHAR2_TABLE_100
630 , p6_a11 out nocopy JTF_VARCHAR2_TABLE_100
631 , p6_a12 out nocopy JTF_VARCHAR2_TABLE_100
632 , p6_a13 out nocopy JTF_VARCHAR2_TABLE_100
633 , p6_a14 out nocopy JTF_VARCHAR2_TABLE_100
634 , p6_a15 out nocopy JTF_VARCHAR2_TABLE_100
635 , p6_a16 out nocopy JTF_VARCHAR2_TABLE_100
636 , p6_a17 out nocopy JTF_VARCHAR2_TABLE_100
637 , p6_a18 out nocopy JTF_VARCHAR2_TABLE_100
638 , p6_a19 out nocopy JTF_VARCHAR2_TABLE_100
639 , p6_a20 out nocopy JTF_VARCHAR2_TABLE_100
640 , p6_a21 out nocopy JTF_VARCHAR2_TABLE_100
641 , p7_a0 out nocopy VARCHAR2
642 , p7_a1 out nocopy DATE
643 , p7_a2 out nocopy DATE
644 , p7_a3 out nocopy VARCHAR2
645 , p7_a4 out nocopy DATE
646 , p7_a5 out nocopy DATE
647 , p7_a6 out nocopy VARCHAR2
648 , p7_a7 out nocopy DATE
649 , p7_a8 out nocopy DATE
650 , p7_a9 out nocopy VARCHAR2
651 , p7_a10 out nocopy DATE
652 , p7_a11 out nocopy DATE
653 , p7_a12 out nocopy VARCHAR2
654 , p7_a13 out nocopy DATE
655 , p7_a14 out nocopy DATE
656 , p7_a15 out nocopy VARCHAR2
657 , p7_a16 out nocopy DATE
658 , p7_a17 out nocopy DATE
659 , p7_a18 out nocopy VARCHAR2
660 , p7_a19 out nocopy DATE
661 , p7_a20 out nocopy DATE
662 , p7_a21 out nocopy VARCHAR2
663 , p7_a22 out nocopy DATE
664 , p7_a23 out nocopy DATE
665 , p7_a24 out nocopy VARCHAR2
666 , p7_a25 out nocopy DATE
667 , p7_a26 out nocopy DATE
668 , p7_a27 out nocopy VARCHAR2
669 , p7_a28 out nocopy DATE
670 , p7_a29 out nocopy DATE
671 , p7_a30 out nocopy VARCHAR2
672 , p7_a31 out nocopy DATE
673 , p7_a32 out nocopy DATE
674 , p7_a33 out nocopy VARCHAR2
675 , p7_a34 out nocopy DATE
676 , p7_a35 out nocopy DATE
677 , p7_a36 out nocopy VARCHAR2
678 , p7_a37 out nocopy DATE
679 , p7_a38 out nocopy DATE
680 , p7_a39 out nocopy VARCHAR2
681 , p7_a40 out nocopy DATE
682 , p7_a41 out nocopy DATE
683 , p7_a42 out nocopy DATE
684 , p7_a43 out nocopy DATE
685 , x_return_status out nocopy VARCHAR2
686 , x_msg_count out nocopy NUMBER
687 , x_msg_data out nocopy VARCHAR2
688 )
689
690 as
691 ddp_search_visits_rec ahl_ltp_space_schedule_pvt.search_visits_rec_type;
692 ddx_scheduled_visit_tbl ahl_ltp_space_schedule_pvt.scheduled_visits_tbl;
693 ddx_display_rec ahl_ltp_space_schedule_pvt.display_rec_type;
694 ddindx binary_integer; indx binary_integer;
695 begin
696
697 -- copy data to the local IN or IN-OUT args, if any
698
699
700
701
702
703 ddp_search_visits_rec.org_name := p5_a0;
704 ddp_search_visits_rec.org_id := p5_a1;
705 ddp_search_visits_rec.department_name := p5_a2;
706 ddp_search_visits_rec.department_id := p5_a3;
707 ddp_search_visits_rec.department_code := p5_a4;
708 ddp_search_visits_rec.space_name := p5_a5;
709 ddp_search_visits_rec.space_id := p5_a6;
710 ddp_search_visits_rec.space_category := p5_a7;
711 ddp_search_visits_rec.space_category_mean := p5_a8;
712 ddp_search_visits_rec.visit_type_code := p5_a9;
713 ddp_search_visits_rec.visit_type_mean := p5_a10;
714 ddp_search_visits_rec.item_id := p5_a11;
715 ddp_search_visits_rec.item_description := p5_a12;
716 ddp_search_visits_rec.plan_id := p5_a13;
717 ddp_search_visits_rec.plan_name := p5_a14;
718 ddp_search_visits_rec.display_period_code := p5_a15;
719 ddp_search_visits_rec.display_period_mean := p5_a16;
720 ddp_search_visits_rec.start_date := rosetta_g_miss_date_in_map(p5_a17);
721 ddp_search_visits_rec.start_period := rosetta_g_miss_date_in_map(p5_a18);
722 ddp_search_visits_rec.end_period := rosetta_g_miss_date_in_map(p5_a19);
723
724
725
726
727
728
729 -- here's the delegated call to the old PL/SQL routine
730 ahl_ltp_space_schedule_pvt.search_scheduled_visits(p_api_version,
731 p_init_msg_list,
732 p_commit,
733 p_validation_level,
734 p_module_type,
735 ddp_search_visits_rec,
736 ddx_scheduled_visit_tbl,
737 ddx_display_rec,
738 x_return_status,
739 x_msg_count,
740 x_msg_data);
741
742 -- copy data back from the local variables to OUT or IN-OUT args, if any
743
744
745
746
747
748
752 , p6_a3
749 ahl_ltp_space_schedule_pvt_w.rosetta_table_copy_out_p6(ddx_scheduled_visit_tbl, p6_a0
750 , p6_a1
751 , p6_a2
753 , p6_a4
754 , p6_a5
755 , p6_a6
756 , p6_a7
757 , p6_a8
758 , p6_a9
759 , p6_a10
760 , p6_a11
761 , p6_a12
762 , p6_a13
763 , p6_a14
764 , p6_a15
765 , p6_a16
766 , p6_a17
767 , p6_a18
768 , p6_a19
769 , p6_a20
770 , p6_a21
771 );
772
773 p7_a0 := ddx_display_rec.field_1;
774 p7_a1 := ddx_display_rec.start_period_1;
775 p7_a2 := ddx_display_rec.end_period_1;
776 p7_a3 := ddx_display_rec.field_2;
777 p7_a4 := ddx_display_rec.start_period_2;
778 p7_a5 := ddx_display_rec.end_period_2;
779 p7_a6 := ddx_display_rec.field_3;
780 p7_a7 := ddx_display_rec.start_period_3;
781 p7_a8 := ddx_display_rec.end_period_3;
782 p7_a9 := ddx_display_rec.field_4;
783 p7_a10 := ddx_display_rec.start_period_4;
784 p7_a11 := ddx_display_rec.end_period_4;
785 p7_a12 := ddx_display_rec.field_5;
786 p7_a13 := ddx_display_rec.start_period_5;
787 p7_a14 := ddx_display_rec.end_period_5;
788 p7_a15 := ddx_display_rec.field_6;
789 p7_a16 := ddx_display_rec.start_period_6;
790 p7_a17 := ddx_display_rec.end_period_6;
791 p7_a18 := ddx_display_rec.field_7;
792 p7_a19 := ddx_display_rec.start_period_7;
793 p7_a20 := ddx_display_rec.end_period_7;
794 p7_a21 := ddx_display_rec.field_8;
795 p7_a22 := ddx_display_rec.start_period_8;
796 p7_a23 := ddx_display_rec.end_period_8;
797 p7_a24 := ddx_display_rec.field_9;
798 p7_a25 := ddx_display_rec.start_period_9;
799 p7_a26 := ddx_display_rec.end_period_9;
800 p7_a27 := ddx_display_rec.field_10;
801 p7_a28 := ddx_display_rec.start_period_10;
802 p7_a29 := ddx_display_rec.end_period_10;
803 p7_a30 := ddx_display_rec.field_11;
804 p7_a31 := ddx_display_rec.start_period_11;
805 p7_a32 := ddx_display_rec.end_period_11;
806 p7_a33 := ddx_display_rec.field_12;
807 p7_a34 := ddx_display_rec.start_period_12;
808 p7_a35 := ddx_display_rec.end_period_12;
809 p7_a36 := ddx_display_rec.field_13;
810 p7_a37 := ddx_display_rec.start_period_13;
811 p7_a38 := ddx_display_rec.end_period_13;
812 p7_a39 := ddx_display_rec.field_14;
813 p7_a40 := ddx_display_rec.start_period_14;
814 p7_a41 := ddx_display_rec.end_period_14;
815 p7_a42 := ddx_display_rec.start_period;
816 p7_a43 := ddx_display_rec.end_period;
817
818
819
820 end;
821
822 procedure get_visit_details(p_api_version NUMBER
823 , p_init_msg_list VARCHAR2
824 , p_commit VARCHAR2
825 , p_validation_level NUMBER
826 , p_module_type VARCHAR2
827 , p5_a0 VARCHAR2
828 , p5_a1 NUMBER
829 , p5_a2 VARCHAR2
830 , p5_a3 NUMBER
831 , p5_a4 VARCHAR2
832 , p5_a5 VARCHAR2
833 , p5_a6 NUMBER
834 , p5_a7 VARCHAR2
835 , p5_a8 VARCHAR2
836 , p5_a9 VARCHAR2
837 , p5_a10 VARCHAR2
838 , p5_a11 NUMBER
839 , p5_a12 VARCHAR2
840 , p5_a13 NUMBER
841 , p5_a14 VARCHAR2
842 , p5_a15 VARCHAR2
843 , p5_a16 VARCHAR2
844 , p5_a17 DATE
845 , p5_a18 DATE
846 , p5_a19 DATE
847 , p6_a0 out nocopy JTF_NUMBER_TABLE
848 , p6_a1 out nocopy JTF_VARCHAR2_TABLE_100
849 , p6_a2 out nocopy JTF_VARCHAR2_TABLE_100
850 , p6_a3 out nocopy JTF_NUMBER_TABLE
851 , p6_a4 out nocopy JTF_VARCHAR2_TABLE_100
852 , p6_a5 out nocopy JTF_VARCHAR2_TABLE_100
853 , p6_a6 out nocopy JTF_VARCHAR2_TABLE_100
854 , p6_a7 out nocopy JTF_VARCHAR2_TABLE_300
855 , p6_a8 out nocopy JTF_VARCHAR2_TABLE_100
856 , p6_a9 out nocopy JTF_VARCHAR2_TABLE_100
857 , p6_a10 out nocopy JTF_DATE_TABLE
858 , p6_a11 out nocopy JTF_DATE_TABLE
859 , p6_a12 out nocopy JTF_DATE_TABLE
860 , x_return_status out nocopy VARCHAR2
861 , x_msg_count out nocopy NUMBER
862 , x_msg_data out nocopy VARCHAR2
863 )
864
865 as
866 ddp_search_visits_rec ahl_ltp_space_schedule_pvt.search_visits_rec_type;
867 ddx_visit_details_tbl ahl_ltp_space_schedule_pvt.visit_details_tbl;
868 ddindx binary_integer; indx binary_integer;
869 begin
870
871 -- copy data to the local IN or IN-OUT args, if any
872
873
874
875
876
877 ddp_search_visits_rec.org_name := p5_a0;
878 ddp_search_visits_rec.org_id := p5_a1;
879 ddp_search_visits_rec.department_name := p5_a2;
880 ddp_search_visits_rec.department_id := p5_a3;
881 ddp_search_visits_rec.department_code := p5_a4;
882 ddp_search_visits_rec.space_name := p5_a5;
883 ddp_search_visits_rec.space_id := p5_a6;
884 ddp_search_visits_rec.space_category := p5_a7;
885 ddp_search_visits_rec.space_category_mean := p5_a8;
886 ddp_search_visits_rec.visit_type_code := p5_a9;
887 ddp_search_visits_rec.visit_type_mean := p5_a10;
888 ddp_search_visits_rec.item_id := p5_a11;
889 ddp_search_visits_rec.item_description := p5_a12;
890 ddp_search_visits_rec.plan_id := p5_a13;
894 ddp_search_visits_rec.start_date := rosetta_g_miss_date_in_map(p5_a17);
891 ddp_search_visits_rec.plan_name := p5_a14;
892 ddp_search_visits_rec.display_period_code := p5_a15;
893 ddp_search_visits_rec.display_period_mean := p5_a16;
895 ddp_search_visits_rec.start_period := rosetta_g_miss_date_in_map(p5_a18);
896 ddp_search_visits_rec.end_period := rosetta_g_miss_date_in_map(p5_a19);
897
898
899
900
901
902 -- here's the delegated call to the old PL/SQL routine
903 ahl_ltp_space_schedule_pvt.get_visit_details(p_api_version,
904 p_init_msg_list,
905 p_commit,
906 p_validation_level,
907 p_module_type,
908 ddp_search_visits_rec,
909 ddx_visit_details_tbl,
910 x_return_status,
911 x_msg_count,
912 x_msg_data);
913
914 -- copy data back from the local variables to OUT or IN-OUT args, if any
915
916
917
918
919
920
921 ahl_ltp_space_schedule_pvt_w.rosetta_table_copy_out_p8(ddx_visit_details_tbl, p6_a0
922 , p6_a1
923 , p6_a2
924 , p6_a3
925 , p6_a4
926 , p6_a5
927 , p6_a6
928 , p6_a7
932 , p6_a11
929 , p6_a8
930 , p6_a9
931 , p6_a10
933 , p6_a12
934 );
935
936
937
938 end;
939
940 end ahl_ltp_space_schedule_pvt_w;