[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_SUBSCRIPTION_INFO_W
Source
1 package body jtf_um_subscription_info_w as
2 /* $Header: JTFWSBIB.pls 120.2 2005/09/02 18:36:12 applrt 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 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
7 function rosetta_g_miss_date_in_map(d date) return date as
8 begin
9 if d = rosetta_g_mistake_date then return fnd_api.g_miss_date; end if;
10 return d;
11 end;
12
13 function rosetta_g_miss_num_map(n number) return number as
14 a number := fnd_api.g_miss_num;
15 b number := 0-1962.0724;
16 begin
17 if n=a then return b; end if;
18 if n=b then return a; end if;
19 return n;
20 end;
21
22 procedure rosetta_table_copy_in_p2(t out nocopy jtf_um_subscription_info.subscription_info_table, a0 JTF_VARCHAR2_TABLE_1000
23 , a1 JTF_VARCHAR2_TABLE_100
24 , a2 JTF_VARCHAR2_TABLE_2000
25 , a3 JTF_NUMBER_TABLE
26 , a4 JTF_VARCHAR2_TABLE_100
27 , a5 JTF_NUMBER_TABLE
28 , a6 JTF_NUMBER_TABLE
29 , a7 JTF_NUMBER_TABLE
30 , a8 JTF_NUMBER_TABLE
31 , a9 JTF_NUMBER_TABLE
32 , a10 JTF_NUMBER_TABLE
33 , a11 JTF_NUMBER_TABLE
34 , a12 JTF_VARCHAR2_TABLE_100
35 , a13 JTF_VARCHAR2_TABLE_300
36 , a14 JTF_VARCHAR2_TABLE_300
37 ) as
38 ddindx binary_integer; indx binary_integer;
39 begin
40 if a0 is not null and a0.count > 0 then
41 if a0.count > 0 then
42 indx := a0.first;
43 ddindx := 1;
44 while true loop
45 t(ddindx).name := a0(indx);
46 t(ddindx).key := a1(indx);
47 t(ddindx).description := a2(indx);
48 t(ddindx).display_order := rosetta_g_miss_num_map(a3(indx));
49 t(ddindx).activation_mode := a4(indx);
50 t(ddindx).delegation_role := rosetta_g_miss_num_map(a5(indx));
51 t(ddindx).checkbox_status := rosetta_g_miss_num_map(a6(indx));
52 t(ddindx).approval_required := rosetta_g_miss_num_map(a7(indx));
53 t(ddindx).subscription_id := rosetta_g_miss_num_map(a8(indx));
54 t(ddindx).subscription_reg_id := rosetta_g_miss_num_map(a9(indx));
55 t(ddindx).approval_id := rosetta_g_miss_num_map(a10(indx));
56 t(ddindx).is_user_enrolled := rosetta_g_miss_num_map(a11(indx));
57 t(ddindx).subscription_status := a12(indx);
58 t(ddindx).template_handler := a13(indx);
59 t(ddindx).page_name := a14(indx);
60 ddindx := ddindx+1;
61 if a0.last =indx
62 then exit;
63 end if;
64 indx := a0.next(indx);
65 end loop;
66 end if;
67 end if;
68 end rosetta_table_copy_in_p2;
69 procedure rosetta_table_copy_out_p2(t jtf_um_subscription_info.subscription_info_table, a0 out nocopy JTF_VARCHAR2_TABLE_1000
70 , a1 out nocopy JTF_VARCHAR2_TABLE_100
71 , a2 out nocopy JTF_VARCHAR2_TABLE_2000
72 , a3 out nocopy JTF_NUMBER_TABLE
73 , a4 out nocopy JTF_VARCHAR2_TABLE_100
74 , a5 out nocopy JTF_NUMBER_TABLE
75 , a6 out nocopy JTF_NUMBER_TABLE
76 , a7 out nocopy JTF_NUMBER_TABLE
77 , a8 out nocopy JTF_NUMBER_TABLE
78 , a9 out nocopy JTF_NUMBER_TABLE
79 , a10 out nocopy JTF_NUMBER_TABLE
80 , a11 out nocopy JTF_NUMBER_TABLE
81 , a12 out nocopy JTF_VARCHAR2_TABLE_100
82 , a13 out nocopy JTF_VARCHAR2_TABLE_300
83 , a14 out nocopy JTF_VARCHAR2_TABLE_300
84 ) as
85 ddindx binary_integer; indx binary_integer;
86 begin
87 if t is null or t.count = 0 then
88 a0 := JTF_VARCHAR2_TABLE_1000();
89 a1 := JTF_VARCHAR2_TABLE_100();
90 a2 := JTF_VARCHAR2_TABLE_2000();
91 a3 := JTF_NUMBER_TABLE();
92 a4 := JTF_VARCHAR2_TABLE_100();
93 a5 := JTF_NUMBER_TABLE();
94 a6 := JTF_NUMBER_TABLE();
95 a7 := JTF_NUMBER_TABLE();
96 a8 := JTF_NUMBER_TABLE();
97 a9 := JTF_NUMBER_TABLE();
98 a10 := JTF_NUMBER_TABLE();
99 a11 := JTF_NUMBER_TABLE();
100 a12 := JTF_VARCHAR2_TABLE_100();
101 a13 := JTF_VARCHAR2_TABLE_300();
102 a14 := JTF_VARCHAR2_TABLE_300();
103 else
104 a0 := JTF_VARCHAR2_TABLE_1000();
105 a1 := JTF_VARCHAR2_TABLE_100();
106 a2 := JTF_VARCHAR2_TABLE_2000();
107 a3 := JTF_NUMBER_TABLE();
108 a4 := JTF_VARCHAR2_TABLE_100();
109 a5 := JTF_NUMBER_TABLE();
110 a6 := JTF_NUMBER_TABLE();
111 a7 := JTF_NUMBER_TABLE();
112 a8 := JTF_NUMBER_TABLE();
113 a9 := JTF_NUMBER_TABLE();
114 a10 := JTF_NUMBER_TABLE();
115 a11 := JTF_NUMBER_TABLE();
116 a12 := JTF_VARCHAR2_TABLE_100();
117 a13 := JTF_VARCHAR2_TABLE_300();
118 a14 := JTF_VARCHAR2_TABLE_300();
119 if t.count > 0 then
120 a0.extend(t.count);
121 a1.extend(t.count);
122 a2.extend(t.count);
123 a3.extend(t.count);
124 a4.extend(t.count);
125 a5.extend(t.count);
126 a6.extend(t.count);
127 a7.extend(t.count);
128 a8.extend(t.count);
129 a9.extend(t.count);
130 a10.extend(t.count);
131 a11.extend(t.count);
132 a12.extend(t.count);
133 a13.extend(t.count);
134 a14.extend(t.count);
135 ddindx := t.first;
136 indx := 1;
137 while true loop
138 a0(indx) := t(ddindx).name;
139 a1(indx) := t(ddindx).key;
140 a2(indx) := t(ddindx).description;
141 a3(indx) := rosetta_g_miss_num_map(t(ddindx).display_order);
142 a4(indx) := t(ddindx).activation_mode;
143 a5(indx) := rosetta_g_miss_num_map(t(ddindx).delegation_role);
144 a6(indx) := rosetta_g_miss_num_map(t(ddindx).checkbox_status);
145 a7(indx) := rosetta_g_miss_num_map(t(ddindx).approval_required);
146 a8(indx) := rosetta_g_miss_num_map(t(ddindx).subscription_id);
147 a9(indx) := rosetta_g_miss_num_map(t(ddindx).subscription_reg_id);
148 a10(indx) := rosetta_g_miss_num_map(t(ddindx).approval_id);
149 a11(indx) := rosetta_g_miss_num_map(t(ddindx).is_user_enrolled);
150 a12(indx) := t(ddindx).subscription_status;
151 a13(indx) := t(ddindx).template_handler;
152 a14(indx) := t(ddindx).page_name;
153 indx := indx+1;
154 if t.last =ddindx
155 then exit;
156 end if;
157 ddindx := t.next(ddindx);
158 end loop;
159 end if;
160 end if;
161 end rosetta_table_copy_out_p2;
162
163 procedure rosetta_table_copy_in_p3(t out nocopy jtf_um_subscription_info.subscription_list, a0 JTF_NUMBER_TABLE
164 ) as
165 ddindx binary_integer; indx binary_integer;
166 begin
167 if a0 is not null and a0.count > 0 then
168 if a0.count > 0 then
169 indx := a0.first;
170 ddindx := 1;
171 while true loop
172 t(ddindx).subscription_id := rosetta_g_miss_num_map(a0(indx));
173 ddindx := ddindx+1;
174 if a0.last =indx
175 then exit;
176 end if;
177 indx := a0.next(indx);
178 end loop;
179 end if;
180 end if;
181 end rosetta_table_copy_in_p3;
182 procedure rosetta_table_copy_out_p3(t jtf_um_subscription_info.subscription_list, a0 out nocopy JTF_NUMBER_TABLE
183 ) as
184 ddindx binary_integer; indx binary_integer;
185 begin
186 if t is null or t.count = 0 then
187 a0 := JTF_NUMBER_TABLE();
188 else
189 a0 := JTF_NUMBER_TABLE();
190 if t.count > 0 then
191 a0.extend(t.count);
192 ddindx := t.first;
193 indx := 1;
194 while true loop
195 a0(indx) := rosetta_g_miss_num_map(t(ddindx).subscription_id);
196 indx := indx+1;
197 if t.last =ddindx
198 then exit;
199 end if;
200 ddindx := t.next(ddindx);
201 end loop;
202 end if;
203 end if;
204 end rosetta_table_copy_out_p3;
205
206 procedure get_usertype_sub_info(p_usertype_id NUMBER
207 , p_user_id NUMBER
208 , p_is_admin NUMBER
209 , p3_a0 out nocopy JTF_VARCHAR2_TABLE_1000
210 , p3_a1 out nocopy JTF_VARCHAR2_TABLE_100
211 , p3_a2 out nocopy JTF_VARCHAR2_TABLE_2000
212 , p3_a3 out nocopy JTF_NUMBER_TABLE
213 , p3_a4 out nocopy JTF_VARCHAR2_TABLE_100
214 , p3_a5 out nocopy JTF_NUMBER_TABLE
215 , p3_a6 out nocopy JTF_NUMBER_TABLE
216 , p3_a7 out nocopy JTF_NUMBER_TABLE
217 , p3_a8 out nocopy JTF_NUMBER_TABLE
218 , p3_a9 out nocopy JTF_NUMBER_TABLE
219 , p3_a10 out nocopy JTF_NUMBER_TABLE
220 , p3_a11 out nocopy JTF_NUMBER_TABLE
221 , p3_a12 out nocopy JTF_VARCHAR2_TABLE_100
222 , p3_a13 out nocopy JTF_VARCHAR2_TABLE_300
223 , p3_a14 out nocopy JTF_VARCHAR2_TABLE_300
224 )
225
226 as
227 ddx_result jtf_um_subscription_info.subscription_info_table;
228 ddindx binary_integer; indx binary_integer;
229 begin
230
231 -- copy data to the local IN or IN-OUT args, if any
232
233
234
235
236 -- here's the delegated call to the old PL/SQL routine
237 jtf_um_subscription_info.get_usertype_sub_info(p_usertype_id,
238 p_user_id,
239 p_is_admin,
240 ddx_result);
241
242 -- copy data back from the local variables to OUT or IN-OUT args, if any
243
244
245
246 jtf_um_subscription_info_w.rosetta_table_copy_out_p2(ddx_result, p3_a0
247 , p3_a1
248 , p3_a2
249 , p3_a3
250 , p3_a4
251 , p3_a5
252 , p3_a6
253 , p3_a7
254 , p3_a8
255 , p3_a9
256 , p3_a10
257 , p3_a11
258 , p3_a12
259 , p3_a13
260 , p3_a14
261 );
262 end;
263
264 procedure get_user_sub_info(p_user_id NUMBER
265 , p_is_admin NUMBER
266 , p_logged_in_user_id NUMBER
267 , p_administrator NUMBER
268 , p_sub_status VARCHAR2
269 , p5_a0 out nocopy JTF_VARCHAR2_TABLE_1000
270 , p5_a1 out nocopy JTF_VARCHAR2_TABLE_100
271 , p5_a2 out nocopy JTF_VARCHAR2_TABLE_2000
272 , p5_a3 out nocopy JTF_NUMBER_TABLE
273 , p5_a4 out nocopy JTF_VARCHAR2_TABLE_100
274 , p5_a5 out nocopy JTF_NUMBER_TABLE
275 , p5_a6 out nocopy JTF_NUMBER_TABLE
276 , p5_a7 out nocopy JTF_NUMBER_TABLE
277 , p5_a8 out nocopy JTF_NUMBER_TABLE
278 , p5_a9 out nocopy JTF_NUMBER_TABLE
279 , p5_a10 out nocopy JTF_NUMBER_TABLE
280 , p5_a11 out nocopy JTF_NUMBER_TABLE
281 , p5_a12 out nocopy JTF_VARCHAR2_TABLE_100
282 , p5_a13 out nocopy JTF_VARCHAR2_TABLE_300
283 , p5_a14 out nocopy JTF_VARCHAR2_TABLE_300
284 )
285
286 as
287 ddx_result jtf_um_subscription_info.subscription_info_table;
288 ddindx binary_integer; indx binary_integer;
289 begin
290
291 -- copy data to the local IN or IN-OUT args, if any
292
293
294
295
296
297
298 -- here's the delegated call to the old PL/SQL routine
299 jtf_um_subscription_info.get_user_sub_info(p_user_id,
300 p_is_admin,
301 p_logged_in_user_id,
302 p_administrator,
303 p_sub_status,
304 ddx_result);
305
306 -- copy data back from the local variables to OUT or IN-OUT args, if any
307
308
309
310
311
312 jtf_um_subscription_info_w.rosetta_table_copy_out_p2(ddx_result, p5_a0
313 , p5_a1
314 , p5_a2
315 , p5_a3
316 , p5_a4
317 , p5_a5
318 , p5_a6
319 , p5_a7
320 , p5_a8
321 , p5_a9
322 , p5_a10
323 , p5_a11
324 , p5_a12
325 , p5_a13
326 , p5_a14
327 );
328 end;
329
330 procedure get_conf_sub_info(p_user_id NUMBER
331 , p_usertype_id NUMBER
332 , p_is_admin NUMBER
333 , p_admin_id NUMBER
334 , p_administrator NUMBER
335 , p5_a0 JTF_NUMBER_TABLE
336 , p6_a0 out nocopy JTF_VARCHAR2_TABLE_1000
337 , p6_a1 out nocopy JTF_VARCHAR2_TABLE_100
338 , p6_a2 out nocopy JTF_VARCHAR2_TABLE_2000
339 , p6_a3 out nocopy JTF_NUMBER_TABLE
340 , p6_a4 out nocopy JTF_VARCHAR2_TABLE_100
341 , p6_a5 out nocopy JTF_NUMBER_TABLE
342 , p6_a6 out nocopy JTF_NUMBER_TABLE
343 , p6_a7 out nocopy JTF_NUMBER_TABLE
344 , p6_a8 out nocopy JTF_NUMBER_TABLE
345 , p6_a9 out nocopy JTF_NUMBER_TABLE
346 , p6_a10 out nocopy JTF_NUMBER_TABLE
347 , p6_a11 out nocopy JTF_NUMBER_TABLE
348 , p6_a12 out nocopy JTF_VARCHAR2_TABLE_100
349 , p6_a13 out nocopy JTF_VARCHAR2_TABLE_300
350 , p6_a14 out nocopy JTF_VARCHAR2_TABLE_300
351 )
352
353 as
354 ddp_sub_list jtf_um_subscription_info.subscription_list;
355 ddx_result jtf_um_subscription_info.subscription_info_table;
356 ddindx binary_integer; indx binary_integer;
357 begin
358
359 -- copy data to the local IN or IN-OUT args, if any
360
361
362
363
364
365 jtf_um_subscription_info_w.rosetta_table_copy_in_p3(ddp_sub_list, p5_a0
366 );
367
368
369 -- here's the delegated call to the old PL/SQL routine
370 jtf_um_subscription_info.get_conf_sub_info(p_user_id,
371 p_usertype_id,
372 p_is_admin,
373 p_admin_id,
374 p_administrator,
375 ddp_sub_list,
376 ddx_result);
377
378 -- copy data back from the local variables to OUT or IN-OUT args, if any
379
380
381
382
383
384
385 jtf_um_subscription_info_w.rosetta_table_copy_out_p2(ddx_result, p6_a0
386 , p6_a1
387 , p6_a2
388 , p6_a3
389 , p6_a4
390 , p6_a5
391 , p6_a6
392 , p6_a7
393 , p6_a8
394 , p6_a9
395 , p6_a10
396 , p6_a11
397 , p6_a12
398 , p6_a13
399 , p6_a14
400 );
401 end;
402
403 procedure get_usertype_sub(p_usertype_id NUMBER
404 , p_user_id NUMBER
405 , p_is_admin NUMBER
406 , p_admin_id NUMBER
407 , p4_a0 JTF_NUMBER_TABLE
408 , p5_a0 out nocopy JTF_VARCHAR2_TABLE_1000
409 , p5_a1 out nocopy JTF_VARCHAR2_TABLE_100
410 , p5_a2 out nocopy JTF_VARCHAR2_TABLE_2000
411 , p5_a3 out nocopy JTF_NUMBER_TABLE
412 , p5_a4 out nocopy JTF_VARCHAR2_TABLE_100
413 , p5_a5 out nocopy JTF_NUMBER_TABLE
414 , p5_a6 out nocopy JTF_NUMBER_TABLE
415 , p5_a7 out nocopy JTF_NUMBER_TABLE
416 , p5_a8 out nocopy JTF_NUMBER_TABLE
417 , p5_a9 out nocopy JTF_NUMBER_TABLE
418 , p5_a10 out nocopy JTF_NUMBER_TABLE
419 , p5_a11 out nocopy JTF_NUMBER_TABLE
420 , p5_a12 out nocopy JTF_VARCHAR2_TABLE_100
421 , p5_a13 out nocopy JTF_VARCHAR2_TABLE_300
422 , p5_a14 out nocopy JTF_VARCHAR2_TABLE_300
423 )
424
425 as
426 ddp_sub_list jtf_um_subscription_info.subscription_list;
427 ddx_result jtf_um_subscription_info.subscription_info_table;
428 ddindx binary_integer; indx binary_integer;
429 begin
430
431 -- copy data to the local IN or IN-OUT args, if any
432
433
434
435
436 jtf_um_subscription_info_w.rosetta_table_copy_in_p3(ddp_sub_list, p4_a0
437 );
438
439
440 -- here's the delegated call to the old PL/SQL routine
441 jtf_um_subscription_info.get_usertype_sub(p_usertype_id,
442 p_user_id,
443 p_is_admin,
444 p_admin_id,
445 ddp_sub_list,
446 ddx_result);
447
448 -- copy data back from the local variables to OUT or IN-OUT args, if any
449
450
451
452
453
454 jtf_um_subscription_info_w.rosetta_table_copy_out_p2(ddx_result, p5_a0
455 , p5_a1
456 , p5_a2
457 , p5_a3
458 , p5_a4
459 , p5_a5
460 , p5_a6
461 , p5_a7
462 , p5_a8
463 , p5_a9
464 , p5_a10
465 , p5_a11
466 , p5_a12
467 , p5_a13
468 , p5_a14
469 );
470 end;
471
472 end jtf_um_subscription_info_w;