[Home] [Help]
PACKAGE BODY: APPS.CN_PAY_GROUP_DTLS_PVT_W
Source
1 package body cn_pay_group_dtls_pvt_w as
2 /* $Header: cnwpgdtb.pls 115.5 2002/11/27 12:20:46 pramadas 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 procedure rosetta_table_copy_in_p3(t out nocopy cn_pay_group_dtls_pvt.pay_group_dtls_tbl_type, a0 JTF_NUMBER_TABLE
14 , a1 JTF_VARCHAR2_TABLE_100
15 , a2 JTF_VARCHAR2_TABLE_100
16 , a3 JTF_VARCHAR2_TABLE_100
17 , a4 JTF_VARCHAR2_TABLE_100
18 , a5 JTF_NUMBER_TABLE
19 , a6 JTF_NUMBER_TABLE
20 , a7 JTF_DATE_TABLE
21 , a8 JTF_DATE_TABLE
22 ) as
23 ddindx binary_integer; indx binary_integer;
24 begin
25 if a0 is not null and a0.count > 0 then
26 if a0.count > 0 then
27 indx := a0.first;
28 ddindx := 1;
29 while true loop
30 t(ddindx).pay_group_id := a0(indx);
31 t(ddindx).name := a1(indx);
32 t(ddindx).period_set_name := a2(indx);
33 t(ddindx).period_type := a3(indx);
34 t(ddindx).period_name := a4(indx);
35 t(ddindx).period_year := a5(indx);
36 t(ddindx).quarter_num := a6(indx);
37 t(ddindx).start_date := rosetta_g_miss_date_in_map(a7(indx));
38 t(ddindx).end_date := rosetta_g_miss_date_in_map(a8(indx));
39 ddindx := ddindx+1;
40 if a0.last =indx
41 then exit;
42 end if;
43 indx := a0.next(indx);
44 end loop;
45 end if;
46 end if;
47 end rosetta_table_copy_in_p3;
48 procedure rosetta_table_copy_out_p3(t cn_pay_group_dtls_pvt.pay_group_dtls_tbl_type, a0 out nocopy JTF_NUMBER_TABLE
49 , a1 out nocopy JTF_VARCHAR2_TABLE_100
50 , a2 out nocopy JTF_VARCHAR2_TABLE_100
51 , a3 out nocopy JTF_VARCHAR2_TABLE_100
52 , a4 out nocopy JTF_VARCHAR2_TABLE_100
53 , a5 out nocopy JTF_NUMBER_TABLE
54 , a6 out nocopy JTF_NUMBER_TABLE
55 , a7 out nocopy JTF_DATE_TABLE
56 , a8 out nocopy JTF_DATE_TABLE
57 ) as
58 ddindx binary_integer; indx binary_integer;
59 begin
60 if t is null or t.count = 0 then
61 a0 := JTF_NUMBER_TABLE();
62 a1 := JTF_VARCHAR2_TABLE_100();
63 a2 := JTF_VARCHAR2_TABLE_100();
64 a3 := JTF_VARCHAR2_TABLE_100();
65 a4 := JTF_VARCHAR2_TABLE_100();
66 a5 := JTF_NUMBER_TABLE();
67 a6 := JTF_NUMBER_TABLE();
68 a7 := JTF_DATE_TABLE();
69 a8 := JTF_DATE_TABLE();
70 else
71 a0 := JTF_NUMBER_TABLE();
72 a1 := JTF_VARCHAR2_TABLE_100();
73 a2 := JTF_VARCHAR2_TABLE_100();
74 a3 := JTF_VARCHAR2_TABLE_100();
75 a4 := JTF_VARCHAR2_TABLE_100();
76 a5 := JTF_NUMBER_TABLE();
77 a6 := JTF_NUMBER_TABLE();
78 a7 := JTF_DATE_TABLE();
79 a8 := JTF_DATE_TABLE();
80 if t.count > 0 then
81 a0.extend(t.count);
82 a1.extend(t.count);
83 a2.extend(t.count);
84 a3.extend(t.count);
85 a4.extend(t.count);
86 a5.extend(t.count);
87 a6.extend(t.count);
88 a7.extend(t.count);
89 a8.extend(t.count);
90 ddindx := t.first;
91 indx := 1;
92 while true loop
93 a0(indx) := t(ddindx).pay_group_id;
94 a1(indx) := t(ddindx).name;
95 a2(indx) := t(ddindx).period_set_name;
96 a3(indx) := t(ddindx).period_type;
97 a4(indx) := t(ddindx).period_name;
98 a5(indx) := t(ddindx).period_year;
99 a6(indx) := t(ddindx).quarter_num;
100 a7(indx) := t(ddindx).start_date;
101 a8(indx) := t(ddindx).end_date;
102 indx := indx+1;
103 if t.last =ddindx
104 then exit;
105 end if;
106 ddindx := t.next(ddindx);
107 end loop;
108 end if;
109 end if;
110 end rosetta_table_copy_out_p3;
111
112 procedure rosetta_table_copy_in_p4(t out nocopy cn_pay_group_dtls_pvt.pay_group_sales_tbl_type, a0 JTF_NUMBER_TABLE
113 , a1 JTF_VARCHAR2_TABLE_100
114 , a2 JTF_VARCHAR2_TABLE_100
115 , a3 JTF_VARCHAR2_TABLE_100
116 , a4 JTF_VARCHAR2_TABLE_400
117 , a5 JTF_VARCHAR2_TABLE_100
118 , a6 JTF_DATE_TABLE
119 , a7 JTF_DATE_TABLE
120 ) as
121 ddindx binary_integer; indx binary_integer;
122 begin
123 if a0 is not null and a0.count > 0 then
124 if a0.count > 0 then
125 indx := a0.first;
126 ddindx := 1;
127 while true loop
128 t(ddindx).pay_group_id := a0(indx);
129 t(ddindx).name := a1(indx);
130 t(ddindx).period_set_name := a2(indx);
131 t(ddindx).period_type := a3(indx);
132 t(ddindx).salesrep_name := a4(indx);
133 t(ddindx).employee_number := a5(indx);
134 t(ddindx).start_date := rosetta_g_miss_date_in_map(a6(indx));
135 t(ddindx).end_date := rosetta_g_miss_date_in_map(a7(indx));
136 ddindx := ddindx+1;
137 if a0.last =indx
138 then exit;
139 end if;
140 indx := a0.next(indx);
141 end loop;
142 end if;
143 end if;
144 end rosetta_table_copy_in_p4;
145 procedure rosetta_table_copy_out_p4(t cn_pay_group_dtls_pvt.pay_group_sales_tbl_type, a0 out nocopy JTF_NUMBER_TABLE
146 , a1 out nocopy JTF_VARCHAR2_TABLE_100
147 , a2 out nocopy JTF_VARCHAR2_TABLE_100
148 , a3 out nocopy JTF_VARCHAR2_TABLE_100
149 , a4 out nocopy JTF_VARCHAR2_TABLE_400
150 , a5 out nocopy JTF_VARCHAR2_TABLE_100
151 , a6 out nocopy JTF_DATE_TABLE
152 , a7 out nocopy JTF_DATE_TABLE
153 ) as
154 ddindx binary_integer; indx binary_integer;
155 begin
156 if t is null or t.count = 0 then
157 a0 := JTF_NUMBER_TABLE();
158 a1 := JTF_VARCHAR2_TABLE_100();
159 a2 := JTF_VARCHAR2_TABLE_100();
160 a3 := JTF_VARCHAR2_TABLE_100();
161 a4 := JTF_VARCHAR2_TABLE_400();
162 a5 := JTF_VARCHAR2_TABLE_100();
163 a6 := JTF_DATE_TABLE();
164 a7 := JTF_DATE_TABLE();
165 else
166 a0 := JTF_NUMBER_TABLE();
167 a1 := JTF_VARCHAR2_TABLE_100();
168 a2 := JTF_VARCHAR2_TABLE_100();
169 a3 := JTF_VARCHAR2_TABLE_100();
170 a4 := JTF_VARCHAR2_TABLE_400();
171 a5 := JTF_VARCHAR2_TABLE_100();
172 a6 := JTF_DATE_TABLE();
173 a7 := JTF_DATE_TABLE();
174 if t.count > 0 then
175 a0.extend(t.count);
176 a1.extend(t.count);
177 a2.extend(t.count);
178 a3.extend(t.count);
179 a4.extend(t.count);
180 a5.extend(t.count);
181 a6.extend(t.count);
182 a7.extend(t.count);
183 ddindx := t.first;
184 indx := 1;
185 while true loop
186 a0(indx) := t(ddindx).pay_group_id;
187 a1(indx) := t(ddindx).name;
188 a2(indx) := t(ddindx).period_set_name;
189 a3(indx) := t(ddindx).period_type;
190 a4(indx) := t(ddindx).salesrep_name;
191 a5(indx) := t(ddindx).employee_number;
192 a6(indx) := t(ddindx).start_date;
193 a7(indx) := t(ddindx).end_date;
194 indx := indx+1;
195 if t.last =ddindx
196 then exit;
197 end if;
198 ddindx := t.next(ddindx);
199 end loop;
200 end if;
201 end if;
202 end rosetta_table_copy_out_p4;
203
204 procedure rosetta_table_copy_in_p5(t out nocopy cn_pay_group_dtls_pvt.pay_group_roles_tbl_type, a0 JTF_NUMBER_TABLE
205 , a1 JTF_VARCHAR2_TABLE_100
206 , a2 JTF_VARCHAR2_TABLE_100
207 , a3 JTF_VARCHAR2_TABLE_100
208 , a4 JTF_VARCHAR2_TABLE_100
209 , a5 JTF_NUMBER_TABLE
210 , a6 JTF_DATE_TABLE
211 , a7 JTF_DATE_TABLE
212 ) as
213 ddindx binary_integer; indx binary_integer;
214 begin
215 if a0 is not null and a0.count > 0 then
216 if a0.count > 0 then
217 indx := a0.first;
218 ddindx := 1;
219 while true loop
220 t(ddindx).pay_group_id := a0(indx);
221 t(ddindx).name := a1(indx);
222 t(ddindx).period_set_name := a2(indx);
223 t(ddindx).period_type := a3(indx);
224 t(ddindx).role_name := a4(indx);
225 t(ddindx).role_id := a5(indx);
226 t(ddindx).start_date := rosetta_g_miss_date_in_map(a6(indx));
227 t(ddindx).end_date := rosetta_g_miss_date_in_map(a7(indx));
228 ddindx := ddindx+1;
229 if a0.last =indx
230 then exit;
231 end if;
232 indx := a0.next(indx);
233 end loop;
234 end if;
235 end if;
236 end rosetta_table_copy_in_p5;
237 procedure rosetta_table_copy_out_p5(t cn_pay_group_dtls_pvt.pay_group_roles_tbl_type, a0 out nocopy JTF_NUMBER_TABLE
238 , a1 out nocopy JTF_VARCHAR2_TABLE_100
239 , a2 out nocopy JTF_VARCHAR2_TABLE_100
240 , a3 out nocopy JTF_VARCHAR2_TABLE_100
241 , a4 out nocopy JTF_VARCHAR2_TABLE_100
242 , a5 out nocopy JTF_NUMBER_TABLE
243 , a6 out nocopy JTF_DATE_TABLE
244 , a7 out nocopy JTF_DATE_TABLE
245 ) as
246 ddindx binary_integer; indx binary_integer;
247 begin
248 if t is null or t.count = 0 then
249 a0 := JTF_NUMBER_TABLE();
250 a1 := JTF_VARCHAR2_TABLE_100();
251 a2 := JTF_VARCHAR2_TABLE_100();
252 a3 := JTF_VARCHAR2_TABLE_100();
253 a4 := JTF_VARCHAR2_TABLE_100();
254 a5 := JTF_NUMBER_TABLE();
255 a6 := JTF_DATE_TABLE();
256 a7 := JTF_DATE_TABLE();
257 else
258 a0 := JTF_NUMBER_TABLE();
259 a1 := JTF_VARCHAR2_TABLE_100();
260 a2 := JTF_VARCHAR2_TABLE_100();
261 a3 := JTF_VARCHAR2_TABLE_100();
262 a4 := JTF_VARCHAR2_TABLE_100();
263 a5 := JTF_NUMBER_TABLE();
264 a6 := JTF_DATE_TABLE();
265 a7 := JTF_DATE_TABLE();
266 if t.count > 0 then
267 a0.extend(t.count);
268 a1.extend(t.count);
269 a2.extend(t.count);
270 a3.extend(t.count);
271 a4.extend(t.count);
272 a5.extend(t.count);
273 a6.extend(t.count);
274 a7.extend(t.count);
275 ddindx := t.first;
276 indx := 1;
277 while true loop
278 a0(indx) := t(ddindx).pay_group_id;
279 a1(indx) := t(ddindx).name;
280 a2(indx) := t(ddindx).period_set_name;
281 a3(indx) := t(ddindx).period_type;
282 a4(indx) := t(ddindx).role_name;
283 a5(indx) := t(ddindx).role_id;
284 a6(indx) := t(ddindx).start_date;
285 a7(indx) := t(ddindx).end_date;
286 indx := indx+1;
287 if t.last =ddindx
288 then exit;
289 end if;
290 ddindx := t.next(ddindx);
291 end loop;
292 end if;
293 end if;
294 end rosetta_table_copy_out_p5;
295
296 procedure get_pay_group_dtls(p_api_version NUMBER
297 , p_init_msg_list VARCHAR2
298 , p_commit VARCHAR2
299 , p_validation_level NUMBER
300 , p_start_record NUMBER
301 , p_fetch_size NUMBER
302 , p_pay_group_id NUMBER
303 , p7_a0 out nocopy JTF_NUMBER_TABLE
304 , p7_a1 out nocopy JTF_VARCHAR2_TABLE_100
305 , p7_a2 out nocopy JTF_VARCHAR2_TABLE_100
306 , p7_a3 out nocopy JTF_VARCHAR2_TABLE_100
307 , p7_a4 out nocopy JTF_VARCHAR2_TABLE_100
308 , p7_a5 out nocopy JTF_NUMBER_TABLE
309 , p7_a6 out nocopy JTF_NUMBER_TABLE
310 , p7_a7 out nocopy JTF_DATE_TABLE
311 , p7_a8 out nocopy JTF_DATE_TABLE
312 , x_total_record out nocopy NUMBER
313 , x_return_status out nocopy VARCHAR2
314 , x_msg_count out nocopy NUMBER
315 , x_msg_data out nocopy VARCHAR2
316 )
317
318 as
319 ddx_pay_group_dtls cn_pay_group_dtls_pvt.pay_group_dtls_tbl_type;
320 ddindx binary_integer; indx binary_integer;
321 begin
322
323 -- copy data to the local IN or IN-OUT args, if any
324
325
326
327
328
329
330
331
332
333
334
335
336 -- here's the delegated call to the old PL/SQL routine
337 cn_pay_group_dtls_pvt.get_pay_group_dtls(p_api_version,
338 p_init_msg_list,
339 p_commit,
340 p_validation_level,
341 p_start_record,
342 p_fetch_size,
343 p_pay_group_id,
344 ddx_pay_group_dtls,
345 x_total_record,
346 x_return_status,
347 x_msg_count,
348 x_msg_data);
349
350 -- copy data back from the local variables to OUT or IN-OUT args, if any
351
352
353
354
355
356
357
358 cn_pay_group_dtls_pvt_w.rosetta_table_copy_out_p3(ddx_pay_group_dtls, p7_a0
359 , p7_a1
360 , p7_a2
361 , p7_a3
362 , p7_a4
363 , p7_a5
364 , p7_a6
365 , p7_a7
366 , p7_a8
367 );
368
369
370
371
372 end;
373
374 procedure get_pay_group_sales(p_api_version NUMBER
375 , p_init_msg_list VARCHAR2
376 , p_commit VARCHAR2
377 , p_validation_level NUMBER
378 , p_start_record NUMBER
379 , p_fetch_size NUMBER
380 , p_pay_group_id NUMBER
381 , p7_a0 out nocopy JTF_NUMBER_TABLE
382 , p7_a1 out nocopy JTF_VARCHAR2_TABLE_100
383 , p7_a2 out nocopy JTF_VARCHAR2_TABLE_100
384 , p7_a3 out nocopy JTF_VARCHAR2_TABLE_100
385 , p7_a4 out nocopy JTF_VARCHAR2_TABLE_400
386 , p7_a5 out nocopy JTF_VARCHAR2_TABLE_100
387 , p7_a6 out nocopy JTF_DATE_TABLE
388 , p7_a7 out nocopy JTF_DATE_TABLE
389 , x_total_record out nocopy NUMBER
390 , x_return_status out nocopy VARCHAR2
391 , x_msg_count out nocopy NUMBER
392 , x_msg_data out nocopy VARCHAR2
393 )
394
395 as
396 ddx_pay_group_sales cn_pay_group_dtls_pvt.pay_group_sales_tbl_type;
397 ddindx binary_integer; indx binary_integer;
398 begin
399
400 -- copy data to the local IN or IN-OUT args, if any
401
402
403
404
405
406
407
408
409
410
411
412
413 -- here's the delegated call to the old PL/SQL routine
414 cn_pay_group_dtls_pvt.get_pay_group_sales(p_api_version,
415 p_init_msg_list,
416 p_commit,
417 p_validation_level,
418 p_start_record,
419 p_fetch_size,
420 p_pay_group_id,
421 ddx_pay_group_sales,
422 x_total_record,
423 x_return_status,
424 x_msg_count,
425 x_msg_data);
426
427 -- copy data back from the local variables to OUT or IN-OUT args, if any
428
429
430
431
432
433
434
435 cn_pay_group_dtls_pvt_w.rosetta_table_copy_out_p4(ddx_pay_group_sales, p7_a0
436 , p7_a1
437 , p7_a2
438 , p7_a3
439 , p7_a4
440 , p7_a5
441 , p7_a6
442 , p7_a7
443 );
444
445
446
447
448 end;
449
450 procedure get_pay_group_roles(p_api_version NUMBER
451 , p_init_msg_list VARCHAR2
452 , p_commit VARCHAR2
453 , p_validation_level NUMBER
454 , p_start_record NUMBER
455 , p_fetch_size NUMBER
456 , p_pay_group_id NUMBER
457 , p7_a0 out nocopy JTF_NUMBER_TABLE
458 , p7_a1 out nocopy JTF_VARCHAR2_TABLE_100
459 , p7_a2 out nocopy JTF_VARCHAR2_TABLE_100
460 , p7_a3 out nocopy JTF_VARCHAR2_TABLE_100
461 , p7_a4 out nocopy JTF_VARCHAR2_TABLE_100
462 , p7_a5 out nocopy JTF_NUMBER_TABLE
463 , p7_a6 out nocopy JTF_DATE_TABLE
464 , p7_a7 out nocopy JTF_DATE_TABLE
465 , x_total_record out nocopy NUMBER
466 , x_return_status out nocopy VARCHAR2
467 , x_msg_count out nocopy NUMBER
468 , x_msg_data out nocopy VARCHAR2
469 )
470
471 as
472 ddx_pay_group_roles cn_pay_group_dtls_pvt.pay_group_roles_tbl_type;
473 ddindx binary_integer; indx binary_integer;
474 begin
475
476 -- copy data to the local IN or IN-OUT args, if any
477
478
479
480
481
482
483
484
485
486
487
488
489 -- here's the delegated call to the old PL/SQL routine
490 cn_pay_group_dtls_pvt.get_pay_group_roles(p_api_version,
491 p_init_msg_list,
492 p_commit,
493 p_validation_level,
494 p_start_record,
495 p_fetch_size,
496 p_pay_group_id,
497 ddx_pay_group_roles,
498 x_total_record,
499 x_return_status,
500 x_msg_count,
501 x_msg_data);
502
503 -- copy data back from the local variables to OUT or IN-OUT args, if any
504
505
506
507
508
509
510
511 cn_pay_group_dtls_pvt_w.rosetta_table_copy_out_p5(ddx_pay_group_roles, p7_a0
512 , p7_a1
513 , p7_a2
514 , p7_a3
515 , p7_a4
516 , p7_a5
517 , p7_a6
518 , p7_a7
519 );
520
521
522
523
524 end;
525
526 end cn_pay_group_dtls_pvt_w;