[Home] [Help]
PACKAGE BODY: APPS.PV_GQ_ELEMENTS_PVT_W
Source
1 package body pv_gq_elements_pvt_w as
2 /* $Header: pvxwgqeb.pls 120.1 2008/03/20 22:18:17 hekkiral 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 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_p2(t out nocopy pv_gq_elements_pvt.qsnr_element_tbl_type, a0 JTF_NUMBER_TABLE
17 , a1 JTF_NUMBER_TABLE
18 , a2 JTF_VARCHAR2_TABLE_100
19 , a3 JTF_NUMBER_TABLE
20 , a4 JTF_NUMBER_TABLE
21 , a5 JTF_VARCHAR2_TABLE_100
22 , a6 JTF_NUMBER_TABLE
23 , a7 JTF_NUMBER_TABLE
24 , a8 JTF_VARCHAR2_TABLE_100
25 , a9 JTF_NUMBER_TABLE
26 , a10 JTF_DATE_TABLE
27 , a11 JTF_NUMBER_TABLE
28 , a12 JTF_DATE_TABLE
29 , a13 JTF_NUMBER_TABLE
30 , a14 JTF_VARCHAR2_TABLE_1600
31 ) as
32 ddindx binary_integer; indx binary_integer;
33 begin
34 if a0 is not null and a0.count > 0 then
35 if a0.count > 0 then
36 indx := a0.first;
37 ddindx := 1;
38 while true loop
39 t(ddindx).qsnr_element_id := a0(indx);
40 t(ddindx).object_version_number := a1(indx);
41 t(ddindx).arc_used_by_entity_code := a2(indx);
42 t(ddindx).used_by_entity_id := a3(indx);
43 t(ddindx).qsnr_elmt_seq_num := a4(indx);
44 t(ddindx).qsnr_elmt_type := a5(indx);
45 t(ddindx).entity_attr_id := a6(indx);
46 t(ddindx).qsnr_elmt_page_num := a7(indx);
47 t(ddindx).is_required_flag := a8(indx);
48 t(ddindx).created_by := a9(indx);
49 t(ddindx).creation_date := rosetta_g_miss_date_in_map(a10(indx));
50 t(ddindx).last_updated_by := a11(indx);
51 t(ddindx).last_update_date := rosetta_g_miss_date_in_map(a12(indx));
52 t(ddindx).last_update_login := a13(indx);
53 t(ddindx).elmt_content := a14(indx);
54 ddindx := ddindx+1;
55 if a0.last =indx
56 then exit;
57 end if;
58 indx := a0.next(indx);
59 end loop;
60 end if;
61 end if;
62 end rosetta_table_copy_in_p2;
63 procedure rosetta_table_copy_out_p2(t pv_gq_elements_pvt.qsnr_element_tbl_type, a0 out nocopy JTF_NUMBER_TABLE
64 , a1 out nocopy JTF_NUMBER_TABLE
65 , a2 out nocopy JTF_VARCHAR2_TABLE_100
66 , a3 out nocopy JTF_NUMBER_TABLE
67 , a4 out nocopy JTF_NUMBER_TABLE
68 , a5 out nocopy JTF_VARCHAR2_TABLE_100
69 , a6 out nocopy JTF_NUMBER_TABLE
70 , a7 out nocopy JTF_NUMBER_TABLE
71 , a8 out nocopy JTF_VARCHAR2_TABLE_100
72 , a9 out nocopy JTF_NUMBER_TABLE
73 , a10 out nocopy JTF_DATE_TABLE
74 , a11 out nocopy JTF_NUMBER_TABLE
75 , a12 out nocopy JTF_DATE_TABLE
76 , a13 out nocopy JTF_NUMBER_TABLE
77 , a14 out nocopy JTF_VARCHAR2_TABLE_1600
78 ) as
79 ddindx binary_integer; indx binary_integer;
80 begin
81 if t is null or t.count = 0 then
82 a0 := JTF_NUMBER_TABLE();
83 a1 := JTF_NUMBER_TABLE();
84 a2 := JTF_VARCHAR2_TABLE_100();
85 a3 := JTF_NUMBER_TABLE();
86 a4 := JTF_NUMBER_TABLE();
87 a5 := JTF_VARCHAR2_TABLE_100();
88 a6 := JTF_NUMBER_TABLE();
89 a7 := JTF_NUMBER_TABLE();
90 a8 := JTF_VARCHAR2_TABLE_100();
91 a9 := JTF_NUMBER_TABLE();
92 a10 := JTF_DATE_TABLE();
93 a11 := JTF_NUMBER_TABLE();
94 a12 := JTF_DATE_TABLE();
95 a13 := JTF_NUMBER_TABLE();
96 a14 := JTF_VARCHAR2_TABLE_1600();
97 else
98 a0 := JTF_NUMBER_TABLE();
99 a1 := JTF_NUMBER_TABLE();
100 a2 := JTF_VARCHAR2_TABLE_100();
101 a3 := JTF_NUMBER_TABLE();
102 a4 := JTF_NUMBER_TABLE();
103 a5 := JTF_VARCHAR2_TABLE_100();
104 a6 := JTF_NUMBER_TABLE();
105 a7 := JTF_NUMBER_TABLE();
106 a8 := JTF_VARCHAR2_TABLE_100();
107 a9 := JTF_NUMBER_TABLE();
108 a10 := JTF_DATE_TABLE();
109 a11 := JTF_NUMBER_TABLE();
110 a12 := JTF_DATE_TABLE();
111 a13 := JTF_NUMBER_TABLE();
112 a14 := JTF_VARCHAR2_TABLE_1600();
113 if t.count > 0 then
114 a0.extend(t.count);
115 a1.extend(t.count);
116 a2.extend(t.count);
117 a3.extend(t.count);
118 a4.extend(t.count);
119 a5.extend(t.count);
120 a6.extend(t.count);
121 a7.extend(t.count);
122 a8.extend(t.count);
123 a9.extend(t.count);
124 a10.extend(t.count);
125 a11.extend(t.count);
126 a12.extend(t.count);
127 a13.extend(t.count);
128 a14.extend(t.count);
129 ddindx := t.first;
130 indx := 1;
131 while true loop
132 a0(indx) := t(ddindx).qsnr_element_id;
133 a1(indx) := t(ddindx).object_version_number;
134 a2(indx) := t(ddindx).arc_used_by_entity_code;
135 a3(indx) := t(ddindx).used_by_entity_id;
136 a4(indx) := t(ddindx).qsnr_elmt_seq_num;
137 a5(indx) := t(ddindx).qsnr_elmt_type;
138 a6(indx) := t(ddindx).entity_attr_id;
139 a7(indx) := t(ddindx).qsnr_elmt_page_num;
140 a8(indx) := t(ddindx).is_required_flag;
141 a9(indx) := t(ddindx).created_by;
142 a10(indx) := t(ddindx).creation_date;
143 a11(indx) := t(ddindx).last_updated_by;
144 a12(indx) := t(ddindx).last_update_date;
145 a13(indx) := t(ddindx).last_update_login;
146 a14(indx) := t(ddindx).elmt_content;
147 indx := indx+1;
148 if t.last =ddindx
149 then exit;
150 end if;
151 ddindx := t.next(ddindx);
152 end loop;
153 end if;
154 end if;
155 end rosetta_table_copy_out_p2;
156
157 procedure create_gq_elements(p_api_version_number NUMBER
158 , p_init_msg_list VARCHAR2
159 , p_commit VARCHAR2
160 , p_validation_level NUMBER
161 , x_return_status out nocopy VARCHAR2
162 , x_msg_count out nocopy NUMBER
163 , x_msg_data out nocopy VARCHAR2
164 , p7_a0 NUMBER
165 , p7_a1 NUMBER
166 , p7_a2 VARCHAR2
167 , p7_a3 NUMBER
168 , p7_a4 NUMBER
169 , p7_a5 VARCHAR2
170 , p7_a6 NUMBER
171 , p7_a7 NUMBER
172 , p7_a8 VARCHAR2
173 , p7_a9 NUMBER
174 , p7_a10 DATE
175 , p7_a11 NUMBER
176 , p7_a12 DATE
177 , p7_a13 NUMBER
178 , p7_a14 VARCHAR2
179 , x_qsnr_element_id out nocopy NUMBER
180 )
181
182 as
183 ddp_qsnr_element_rec pv_gq_elements_pvt.qsnr_element_rec_type;
184 ddindx binary_integer; indx binary_integer;
185 begin
186
187 -- copy data to the local IN or IN-OUT args, if any
188
189
190
191
192
193
194
195 ddp_qsnr_element_rec.qsnr_element_id := p7_a0;
196 ddp_qsnr_element_rec.object_version_number := p7_a1;
197 ddp_qsnr_element_rec.arc_used_by_entity_code := p7_a2;
198 ddp_qsnr_element_rec.used_by_entity_id := p7_a3;
199 ddp_qsnr_element_rec.qsnr_elmt_seq_num := p7_a4;
200 ddp_qsnr_element_rec.qsnr_elmt_type := p7_a5;
201 ddp_qsnr_element_rec.entity_attr_id := p7_a6;
202 ddp_qsnr_element_rec.qsnr_elmt_page_num := p7_a7;
203 ddp_qsnr_element_rec.is_required_flag := p7_a8;
204 ddp_qsnr_element_rec.created_by := p7_a9;
205 ddp_qsnr_element_rec.creation_date := rosetta_g_miss_date_in_map(p7_a10);
206 ddp_qsnr_element_rec.last_updated_by := p7_a11;
207 ddp_qsnr_element_rec.last_update_date := rosetta_g_miss_date_in_map(p7_a12);
208 ddp_qsnr_element_rec.last_update_login := p7_a13;
209 ddp_qsnr_element_rec.elmt_content := p7_a14;
210
211
212 -- here's the delegated call to the old PL/SQL routine
213 pv_gq_elements_pvt.create_gq_elements(p_api_version_number,
214 p_init_msg_list,
215 p_commit,
216 p_validation_level,
217 x_return_status,
218 x_msg_count,
219 x_msg_data,
220 ddp_qsnr_element_rec,
221 x_qsnr_element_id);
222
223 -- copy data back from the local variables to OUT or IN-OUT args, if any
224
225
226
227
228
229
230
231
232 end;
233
234 procedure update_gq_elements(p_api_version_number NUMBER
235 , p_init_msg_list VARCHAR2
236 , p_commit VARCHAR2
237 , p_validation_level NUMBER
238 , x_return_status out nocopy VARCHAR2
239 , x_msg_count out nocopy NUMBER
240 , x_msg_data out nocopy VARCHAR2
241 , p7_a0 NUMBER
242 , p7_a1 NUMBER
243 , p7_a2 VARCHAR2
244 , p7_a3 NUMBER
245 , p7_a4 NUMBER
246 , p7_a5 VARCHAR2
247 , p7_a6 NUMBER
248 , p7_a7 NUMBER
249 , p7_a8 VARCHAR2
250 , p7_a9 NUMBER
251 , p7_a10 DATE
252 , p7_a11 NUMBER
253 , p7_a12 DATE
254 , p7_a13 NUMBER
255 , p7_a14 VARCHAR2
256 , x_object_version_number out nocopy NUMBER
257 )
258
259 as
260 ddp_qsnr_element_rec pv_gq_elements_pvt.qsnr_element_rec_type;
261 ddindx binary_integer; indx binary_integer;
262 begin
263
264 -- copy data to the local IN or IN-OUT args, if any
265
266
267
268
269
270
271
272 ddp_qsnr_element_rec.qsnr_element_id := p7_a0;
273 ddp_qsnr_element_rec.object_version_number := p7_a1;
274 ddp_qsnr_element_rec.arc_used_by_entity_code := p7_a2;
275 ddp_qsnr_element_rec.used_by_entity_id := p7_a3;
276 ddp_qsnr_element_rec.qsnr_elmt_seq_num := p7_a4;
277 ddp_qsnr_element_rec.qsnr_elmt_type := p7_a5;
278 ddp_qsnr_element_rec.entity_attr_id := p7_a6;
279 ddp_qsnr_element_rec.qsnr_elmt_page_num := p7_a7;
280 ddp_qsnr_element_rec.is_required_flag := p7_a8;
281 ddp_qsnr_element_rec.created_by := p7_a9;
282 ddp_qsnr_element_rec.creation_date := rosetta_g_miss_date_in_map(p7_a10);
283 ddp_qsnr_element_rec.last_updated_by := p7_a11;
284 ddp_qsnr_element_rec.last_update_date := rosetta_g_miss_date_in_map(p7_a12);
285 ddp_qsnr_element_rec.last_update_login := p7_a13;
286 ddp_qsnr_element_rec.elmt_content := p7_a14;
287
288
289 -- here's the delegated call to the old PL/SQL routine
290 pv_gq_elements_pvt.update_gq_elements(p_api_version_number,
291 p_init_msg_list,
292 p_commit,
293 p_validation_level,
294 x_return_status,
295 x_msg_count,
296 x_msg_data,
297 ddp_qsnr_element_rec,
298 x_object_version_number);
299
300 -- copy data back from the local variables to OUT or IN-OUT args, if any
301
302
303
304
305
306
307
308
309 end;
310
311 procedure validate_gq_elements(p_api_version_number NUMBER
312 , p_init_msg_list VARCHAR2
313 , p_validation_level NUMBER
314 , p3_a0 NUMBER
315 , p3_a1 NUMBER
316 , p3_a2 VARCHAR2
317 , p3_a3 NUMBER
318 , p3_a4 NUMBER
319 , p3_a5 VARCHAR2
320 , p3_a6 NUMBER
321 , p3_a7 NUMBER
322 , p3_a8 VARCHAR2
323 , p3_a9 NUMBER
324 , p3_a10 DATE
325 , p3_a11 NUMBER
326 , p3_a12 DATE
327 , p3_a13 NUMBER
328 , p3_a14 VARCHAR2
329 , p_validation_mode VARCHAR2
330 , x_return_status out nocopy VARCHAR2
331 , x_msg_count out nocopy NUMBER
332 , x_msg_data out nocopy VARCHAR2
333 )
334
335 as
336 ddp_qsnr_element_rec pv_gq_elements_pvt.qsnr_element_rec_type;
337 ddindx binary_integer; indx binary_integer;
338 begin
339
340 -- copy data to the local IN or IN-OUT args, if any
341
342
343
344 ddp_qsnr_element_rec.qsnr_element_id := p3_a0;
345 ddp_qsnr_element_rec.object_version_number := p3_a1;
346 ddp_qsnr_element_rec.arc_used_by_entity_code := p3_a2;
347 ddp_qsnr_element_rec.used_by_entity_id := p3_a3;
348 ddp_qsnr_element_rec.qsnr_elmt_seq_num := p3_a4;
349 ddp_qsnr_element_rec.qsnr_elmt_type := p3_a5;
350 ddp_qsnr_element_rec.entity_attr_id := p3_a6;
351 ddp_qsnr_element_rec.qsnr_elmt_page_num := p3_a7;
352 ddp_qsnr_element_rec.is_required_flag := p3_a8;
353 ddp_qsnr_element_rec.created_by := p3_a9;
354 ddp_qsnr_element_rec.creation_date := rosetta_g_miss_date_in_map(p3_a10);
355 ddp_qsnr_element_rec.last_updated_by := p3_a11;
356 ddp_qsnr_element_rec.last_update_date := rosetta_g_miss_date_in_map(p3_a12);
357 ddp_qsnr_element_rec.last_update_login := p3_a13;
358 ddp_qsnr_element_rec.elmt_content := p3_a14;
359
360
361
362
363
364 -- here's the delegated call to the old PL/SQL routine
365 pv_gq_elements_pvt.validate_gq_elements(p_api_version_number,
366 p_init_msg_list,
367 p_validation_level,
368 ddp_qsnr_element_rec,
369 p_validation_mode,
370 x_return_status,
371 x_msg_count,
372 x_msg_data);
373
374 -- copy data back from the local variables to OUT or IN-OUT args, if any
375
376
377
378
379
380
381
382 end;
383
384 procedure check_qsnr_element_items(p0_a0 NUMBER
385 , p0_a1 NUMBER
386 , p0_a2 VARCHAR2
387 , p0_a3 NUMBER
388 , p0_a4 NUMBER
389 , p0_a5 VARCHAR2
390 , p0_a6 NUMBER
391 , p0_a7 NUMBER
392 , p0_a8 VARCHAR2
393 , p0_a9 NUMBER
394 , p0_a10 DATE
395 , p0_a11 NUMBER
396 , p0_a12 DATE
397 , p0_a13 NUMBER
398 , p0_a14 VARCHAR2
399 , p_validation_mode VARCHAR2
400 , x_return_status out nocopy VARCHAR2
401 )
402
403 as
404 ddp_qsnr_element_rec pv_gq_elements_pvt.qsnr_element_rec_type;
405 ddindx binary_integer; indx binary_integer;
406 begin
407
408 -- copy data to the local IN or IN-OUT args, if any
409 ddp_qsnr_element_rec.qsnr_element_id := p0_a0;
410 ddp_qsnr_element_rec.object_version_number := p0_a1;
411 ddp_qsnr_element_rec.arc_used_by_entity_code := p0_a2;
412 ddp_qsnr_element_rec.used_by_entity_id := p0_a3;
413 ddp_qsnr_element_rec.qsnr_elmt_seq_num := p0_a4;
414 ddp_qsnr_element_rec.qsnr_elmt_type := p0_a5;
415 ddp_qsnr_element_rec.entity_attr_id := p0_a6;
416 ddp_qsnr_element_rec.qsnr_elmt_page_num := p0_a7;
417 ddp_qsnr_element_rec.is_required_flag := p0_a8;
418 ddp_qsnr_element_rec.created_by := p0_a9;
419 ddp_qsnr_element_rec.creation_date := rosetta_g_miss_date_in_map(p0_a10);
420 ddp_qsnr_element_rec.last_updated_by := p0_a11;
421 ddp_qsnr_element_rec.last_update_date := rosetta_g_miss_date_in_map(p0_a12);
422 ddp_qsnr_element_rec.last_update_login := p0_a13;
423 ddp_qsnr_element_rec.elmt_content := p0_a14;
424
425
426
427 -- here's the delegated call to the old PL/SQL routine
428 pv_gq_elements_pvt.check_qsnr_element_items(ddp_qsnr_element_rec,
429 p_validation_mode,
430 x_return_status);
431
432 -- copy data back from the local variables to OUT or IN-OUT args, if any
433
434
435 end;
436
437 procedure validate_qsnr_element_rec(p_api_version_number NUMBER
438 , p_init_msg_list VARCHAR2
439 , x_return_status out nocopy VARCHAR2
440 , x_msg_count out nocopy NUMBER
441 , x_msg_data out nocopy VARCHAR2
442 , p5_a0 NUMBER
443 , p5_a1 NUMBER
444 , p5_a2 VARCHAR2
445 , p5_a3 NUMBER
446 , p5_a4 NUMBER
447 , p5_a5 VARCHAR2
448 , p5_a6 NUMBER
449 , p5_a7 NUMBER
450 , p5_a8 VARCHAR2
451 , p5_a9 NUMBER
452 , p5_a10 DATE
453 , p5_a11 NUMBER
454 , p5_a12 DATE
455 , p5_a13 NUMBER
456 , p5_a14 VARCHAR2
457 )
458
459 as
460 ddp_qsnr_element_rec pv_gq_elements_pvt.qsnr_element_rec_type;
461 ddindx binary_integer; indx binary_integer;
462 begin
463
464 -- copy data to the local IN or IN-OUT args, if any
465
466
467
468
469
470 ddp_qsnr_element_rec.qsnr_element_id := p5_a0;
471 ddp_qsnr_element_rec.object_version_number := p5_a1;
472 ddp_qsnr_element_rec.arc_used_by_entity_code := p5_a2;
473 ddp_qsnr_element_rec.used_by_entity_id := p5_a3;
474 ddp_qsnr_element_rec.qsnr_elmt_seq_num := p5_a4;
475 ddp_qsnr_element_rec.qsnr_elmt_type := p5_a5;
476 ddp_qsnr_element_rec.entity_attr_id := p5_a6;
477 ddp_qsnr_element_rec.qsnr_elmt_page_num := p5_a7;
478 ddp_qsnr_element_rec.is_required_flag := p5_a8;
479 ddp_qsnr_element_rec.created_by := p5_a9;
480 ddp_qsnr_element_rec.creation_date := rosetta_g_miss_date_in_map(p5_a10);
481 ddp_qsnr_element_rec.last_updated_by := p5_a11;
482 ddp_qsnr_element_rec.last_update_date := rosetta_g_miss_date_in_map(p5_a12);
483 ddp_qsnr_element_rec.last_update_login := p5_a13;
484 ddp_qsnr_element_rec.elmt_content := p5_a14;
485
486 -- here's the delegated call to the old PL/SQL routine
487 pv_gq_elements_pvt.validate_qsnr_element_rec(p_api_version_number,
488 p_init_msg_list,
489 x_return_status,
490 x_msg_count,
491 x_msg_data,
492 ddp_qsnr_element_rec);
493
494 -- copy data back from the local variables to OUT or IN-OUT args, if any
495
496
497
498
499
500 end;
501
502 procedure move_qsnr_element(p_api_version_number NUMBER
503 , p_init_msg_list VARCHAR2
504 , p_commit VARCHAR2
505 , p_validation_level NUMBER
506 , x_return_status out nocopy VARCHAR2
507 , x_msg_count out nocopy NUMBER
508 , x_msg_data out nocopy VARCHAR2
509 , p7_a0 NUMBER
510 , p7_a1 NUMBER
511 , p7_a2 VARCHAR2
512 , p7_a3 NUMBER
513 , p7_a4 NUMBER
514 , p7_a5 VARCHAR2
515 , p7_a6 NUMBER
516 , p7_a7 NUMBER
517 , p7_a8 VARCHAR2
518 , p7_a9 NUMBER
519 , p7_a10 DATE
520 , p7_a11 NUMBER
521 , p7_a12 DATE
522 , p7_a13 NUMBER
523 , p7_a14 VARCHAR2
524 , p_movement VARCHAR2
525 )
526
527 as
528 ddp_qsnr_element_rec pv_gq_elements_pvt.qsnr_element_rec_type;
529 ddindx binary_integer; indx binary_integer;
530 begin
531
532 -- copy data to the local IN or IN-OUT args, if any
533
534
535
536
537
538
539
540 ddp_qsnr_element_rec.qsnr_element_id := p7_a0;
541 ddp_qsnr_element_rec.object_version_number := p7_a1;
542 ddp_qsnr_element_rec.arc_used_by_entity_code := p7_a2;
543 ddp_qsnr_element_rec.used_by_entity_id := p7_a3;
544 ddp_qsnr_element_rec.qsnr_elmt_seq_num := p7_a4;
545 ddp_qsnr_element_rec.qsnr_elmt_type := p7_a5;
546 ddp_qsnr_element_rec.entity_attr_id := p7_a6;
547 ddp_qsnr_element_rec.qsnr_elmt_page_num := p7_a7;
548 ddp_qsnr_element_rec.is_required_flag := p7_a8;
549 ddp_qsnr_element_rec.created_by := p7_a9;
550 ddp_qsnr_element_rec.creation_date := rosetta_g_miss_date_in_map(p7_a10);
551 ddp_qsnr_element_rec.last_updated_by := p7_a11;
552 ddp_qsnr_element_rec.last_update_date := rosetta_g_miss_date_in_map(p7_a12);
553 ddp_qsnr_element_rec.last_update_login := p7_a13;
554 ddp_qsnr_element_rec.elmt_content := p7_a14;
555
556
557 -- here's the delegated call to the old PL/SQL routine
558 pv_gq_elements_pvt.move_qsnr_element(p_api_version_number,
559 p_init_msg_list,
560 p_commit,
561 p_validation_level,
562 x_return_status,
563 x_msg_count,
564 x_msg_data,
565 ddp_qsnr_element_rec,
566 p_movement);
567
568 -- copy data back from the local variables to OUT or IN-OUT args, if any
569
570
571
572
573
574
575
576
577 end;
578
579 end pv_gq_elements_pvt_w;