[Home] [Help]
PACKAGE BODY: APPS.JTF_PERZ_QUERY_PUB_W
Source
1 package body jtf_perz_query_pub_w as
2 /* $Header: jtfzwpqb.pls 120.2 2005/11/02 23:48:52 skothe 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_p1(t OUT NOCOPY /* file.sql.39 change */ jtf_perz_query_pub.query_parameter_tbl_type, a0 JTF_NUMBER_TABLE
23 , a1 JTF_NUMBER_TABLE
24 , a2 JTF_VARCHAR2_TABLE_100
25 , a3 JTF_VARCHAR2_TABLE_100
26 , a4 JTF_VARCHAR2_TABLE_300
27 , a5 JTF_VARCHAR2_TABLE_100
28 , a6 JTF_NUMBER_TABLE
29 ) as
30 ddindx binary_integer; indx binary_integer;
31 begin
32 if a0 is not null and a0.count > 0 then
33 if a0.count > 0 then
34 indx := a0.first;
35 ddindx := 1;
36 while true loop
37 t(ddindx).query_param_id := a0(indx);
38 t(ddindx).query_id := a1(indx);
39 t(ddindx).parameter_name := a2(indx);
40 t(ddindx).parameter_type := a3(indx);
41 t(ddindx).parameter_value := a4(indx);
42 t(ddindx).parameter_condition := a5(indx);
43 t(ddindx).parameter_sequence := a6(indx);
44 ddindx := ddindx+1;
45 if a0.last =indx
46 then exit;
47 end if;
48 indx := a0.next(indx);
49 end loop;
50 end if;
51 end if;
52 end rosetta_table_copy_in_p1;
53 procedure rosetta_table_copy_out_p1(t jtf_perz_query_pub.query_parameter_tbl_type, a0 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
54 , a1 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
55 , a2 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
56 , a3 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
57 , a4 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_300
58 , a5 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
59 , a6 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
60 ) as
61 ddindx binary_integer; indx binary_integer;
62 begin
63 if t is null or t.count = 0 then
64 a0 := JTF_NUMBER_TABLE();
65 a1 := JTF_NUMBER_TABLE();
66 a2 := JTF_VARCHAR2_TABLE_100();
67 a3 := JTF_VARCHAR2_TABLE_100();
68 a4 := JTF_VARCHAR2_TABLE_300();
69 a5 := JTF_VARCHAR2_TABLE_100();
70 a6 := JTF_NUMBER_TABLE();
71 else
72 a0 := JTF_NUMBER_TABLE();
73 a1 := JTF_NUMBER_TABLE();
74 a2 := JTF_VARCHAR2_TABLE_100();
75 a3 := JTF_VARCHAR2_TABLE_100();
76 a4 := JTF_VARCHAR2_TABLE_300();
77 a5 := JTF_VARCHAR2_TABLE_100();
78 a6 := JTF_NUMBER_TABLE();
79 if t.count > 0 then
80 a0.extend(t.count);
81 a1.extend(t.count);
82 a2.extend(t.count);
83 a3.extend(t.count);
84 a4.extend(t.count);
85 a5.extend(t.count);
86 a6.extend(t.count);
87 ddindx := t.first;
88 indx := 1;
89 while true loop
90 a0(indx) := t(ddindx).query_param_id;
91 a1(indx) := t(ddindx).query_id;
92 a2(indx) := t(ddindx).parameter_name;
93 a3(indx) := t(ddindx).parameter_type;
94 a4(indx) := t(ddindx).parameter_value;
95 a5(indx) := t(ddindx).parameter_condition;
96 a6(indx) := t(ddindx).parameter_sequence;
97 indx := indx+1;
98 if t.last =ddindx
99 then exit;
100 end if;
101 ddindx := t.next(ddindx);
102 end loop;
103 end if;
104 end if;
105 end rosetta_table_copy_out_p1;
106
107 procedure rosetta_table_copy_in_p4(t OUT NOCOPY /* file.sql.39 change */ jtf_perz_query_pub.query_out_tbl_type, a0 JTF_NUMBER_TABLE
108 , a1 JTF_NUMBER_TABLE
109 , a2 JTF_NUMBER_TABLE
110 , a3 JTF_VARCHAR2_TABLE_100
111 , a4 JTF_VARCHAR2_TABLE_100
112 , a5 JTF_VARCHAR2_TABLE_300
113 , a6 JTF_VARCHAR2_TABLE_2000
114 ) as
115 ddindx binary_integer; indx binary_integer;
116 begin
117 if a0 is not null and a0.count > 0 then
118 if a0.count > 0 then
119 indx := a0.first;
120 ddindx := 1;
121 while true loop
122 t(ddindx).query_id := a0(indx);
123 t(ddindx).profile_id := a1(indx);
124 t(ddindx).application_id := a2(indx);
125 t(ddindx).query_name := a3(indx);
126 t(ddindx).query_type := a4(indx);
127 t(ddindx).query_description := a5(indx);
128 t(ddindx).query_data_source := a6(indx);
129 ddindx := ddindx+1;
130 if a0.last =indx
131 then exit;
132 end if;
133 indx := a0.next(indx);
134 end loop;
135 end if;
136 end if;
137 end rosetta_table_copy_in_p4;
138 procedure rosetta_table_copy_out_p4(t jtf_perz_query_pub.query_out_tbl_type, a0 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
139 , a1 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
140 , a2 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
141 , a3 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
142 , a4 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
143 , a5 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_300
144 , a6 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_2000
145 ) as
146 ddindx binary_integer; indx binary_integer;
147 begin
148 if t is null or t.count = 0 then
149 a0 := JTF_NUMBER_TABLE();
150 a1 := JTF_NUMBER_TABLE();
151 a2 := JTF_NUMBER_TABLE();
152 a3 := JTF_VARCHAR2_TABLE_100();
153 a4 := JTF_VARCHAR2_TABLE_100();
154 a5 := JTF_VARCHAR2_TABLE_300();
155 a6 := JTF_VARCHAR2_TABLE_2000();
156 else
157 a0 := JTF_NUMBER_TABLE();
158 a1 := JTF_NUMBER_TABLE();
159 a2 := JTF_NUMBER_TABLE();
160 a3 := JTF_VARCHAR2_TABLE_100();
161 a4 := JTF_VARCHAR2_TABLE_100();
162 a5 := JTF_VARCHAR2_TABLE_300();
163 a6 := JTF_VARCHAR2_TABLE_2000();
164 if t.count > 0 then
165 a0.extend(t.count);
166 a1.extend(t.count);
167 a2.extend(t.count);
168 a3.extend(t.count);
169 a4.extend(t.count);
170 a5.extend(t.count);
171 a6.extend(t.count);
172 ddindx := t.first;
173 indx := 1;
174 while true loop
175 a0(indx) := t(ddindx).query_id;
176 a1(indx) := t(ddindx).profile_id;
177 a2(indx) := t(ddindx).application_id;
178 a3(indx) := t(ddindx).query_name;
179 a4(indx) := t(ddindx).query_type;
180 a5(indx) := t(ddindx).query_description;
181 a6(indx) := t(ddindx).query_data_source;
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_p4;
191
192 procedure rosetta_table_copy_in_p6(t OUT NOCOPY /* file.sql.39 change */ jtf_perz_query_pub.query_order_by_tbl_type, a0 JTF_NUMBER_TABLE
193 , a1 JTF_NUMBER_TABLE
194 , a2 JTF_VARCHAR2_TABLE_100
195 , a3 JTF_VARCHAR2_TABLE_100
196 , a4 JTF_NUMBER_TABLE
197 ) as
198 ddindx binary_integer; indx binary_integer;
199 begin
200 if a0 is not null and a0.count > 0 then
201 if a0.count > 0 then
202 indx := a0.first;
203 ddindx := 1;
204 while true loop
205 t(ddindx).query_order_by_id := a0(indx);
206 t(ddindx).query_id := a1(indx);
207 t(ddindx).parameter_name := a2(indx);
208 t(ddindx).acnd_dcnd_flag := a3(indx);
209 t(ddindx).parameter_sequence := a4(indx);
210 ddindx := ddindx+1;
211 if a0.last =indx
212 then exit;
213 end if;
214 indx := a0.next(indx);
215 end loop;
216 end if;
217 end if;
218 end rosetta_table_copy_in_p6;
219 procedure rosetta_table_copy_out_p6(t jtf_perz_query_pub.query_order_by_tbl_type, a0 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
220 , a1 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
221 , a2 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
222 , a3 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
223 , a4 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
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_NUMBER_TABLE();
230 a2 := JTF_VARCHAR2_TABLE_100();
231 a3 := JTF_VARCHAR2_TABLE_100();
232 a4 := JTF_NUMBER_TABLE();
233 else
234 a0 := JTF_NUMBER_TABLE();
235 a1 := JTF_NUMBER_TABLE();
236 a2 := JTF_VARCHAR2_TABLE_100();
237 a3 := JTF_VARCHAR2_TABLE_100();
238 a4 := JTF_NUMBER_TABLE();
239 if t.count > 0 then
240 a0.extend(t.count);
241 a1.extend(t.count);
242 a2.extend(t.count);
243 a3.extend(t.count);
244 a4.extend(t.count);
245 ddindx := t.first;
246 indx := 1;
247 while true loop
248 a0(indx) := t(ddindx).query_order_by_id;
249 a1(indx) := t(ddindx).query_id;
250 a2(indx) := t(ddindx).parameter_name;
251 a3(indx) := t(ddindx).acnd_dcnd_flag;
252 a4(indx) := t(ddindx).parameter_sequence;
253 indx := indx+1;
254 if t.last =ddindx
255 then exit;
256 end if;
257 ddindx := t.next(ddindx);
258 end loop;
259 end if;
260 end if;
261 end rosetta_table_copy_out_p6;
262
263 procedure save_perz_query(p_api_version_number NUMBER
264 , p_init_msg_list VARCHAR2
265 , p_commit VARCHAR2
266 , p_application_id NUMBER
267 , p_profile_id NUMBER
268 , p_profile_name VARCHAR2
269 , p_profile_type VARCHAR2
270 , p7_a0 JTF_NUMBER_TABLE
271 , p7_a1 JTF_NUMBER_TABLE
272 , p7_a2 JTF_VARCHAR2_TABLE_100
273 , p7_a3 JTF_VARCHAR2_TABLE_100
274 , p7_a4 JTF_VARCHAR2_TABLE_100
275 , p_query_id NUMBER
276 , p_query_name VARCHAR2
277 , p_query_type VARCHAR2
278 , p_query_desc VARCHAR2
279 , p_query_data_source VARCHAR2
280 , p13_a0 JTF_NUMBER_TABLE
281 , p13_a1 JTF_NUMBER_TABLE
282 , p13_a2 JTF_VARCHAR2_TABLE_100
283 , p13_a3 JTF_VARCHAR2_TABLE_100
284 , p13_a4 JTF_VARCHAR2_TABLE_300
285 , p13_a5 JTF_VARCHAR2_TABLE_100
286 , p13_a6 JTF_NUMBER_TABLE
287 , p14_a0 JTF_NUMBER_TABLE
288 , p14_a1 JTF_NUMBER_TABLE
289 , p14_a2 JTF_VARCHAR2_TABLE_100
290 , p14_a3 JTF_VARCHAR2_TABLE_100
291 , p14_a4 JTF_NUMBER_TABLE
292 , p15_a0 NUMBER
293 , p15_a1 NUMBER
294 , p15_a2 VARCHAR2
295 , p15_a3 VARCHAR2
296 , p15_a4 VARCHAR2
297 , p15_a5 VARCHAR2
298 , p15_a6 VARCHAR2
299 , p15_a7 VARCHAR2
300 , x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER
301 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
302 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
303 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
304 )
305
306 as
307 ddp_profile_attrib jtf_perz_profile_pub.profile_attrib_tbl_type;
308 ddp_query_param_tbl jtf_perz_query_pub.query_parameter_tbl_type;
309 ddp_query_order_by_tbl jtf_perz_query_pub.query_order_by_tbl_type;
310 ddp_query_raw_sql_rec jtf_perz_query_pub.query_raw_sql_rec_type;
311 ddindx binary_integer; indx binary_integer;
312 begin
313
314 -- copy data to the local IN or IN-OUT args, if any
315
316
317
318
319
320
321
322 jtf_perz_profile_pub_w.rosetta_table_copy_in_p1(ddp_profile_attrib, p7_a0
323 , p7_a1
324 , p7_a2
325 , p7_a3
326 , p7_a4
327 );
328
329
330
331
332
333
334 jtf_perz_query_pub_w.rosetta_table_copy_in_p1(ddp_query_param_tbl, p13_a0
335 , p13_a1
336 , p13_a2
337 , p13_a3
338 , p13_a4
339 , p13_a5
340 , p13_a6
341 );
342
343 jtf_perz_query_pub_w.rosetta_table_copy_in_p6(ddp_query_order_by_tbl, p14_a0
344 , p14_a1
345 , p14_a2
346 , p14_a3
347 , p14_a4
348 );
349
350 ddp_query_raw_sql_rec.query_raw_sql_id := p15_a0;
351 ddp_query_raw_sql_rec.query_id := p15_a1;
352 ddp_query_raw_sql_rec.select_string := p15_a2;
353 ddp_query_raw_sql_rec.from_string := p15_a3;
354 ddp_query_raw_sql_rec.where_string := p15_a4;
355 ddp_query_raw_sql_rec.order_by_string := p15_a5;
356 ddp_query_raw_sql_rec.group_by_string := p15_a6;
357 ddp_query_raw_sql_rec.having_string := p15_a7;
358
359
360
361
362
363 -- here's the delegated call to the old PL/SQL routine
364 jtf_perz_query_pub.save_perz_query(p_api_version_number,
365 p_init_msg_list,
366 p_commit,
367 p_application_id,
368 p_profile_id,
369 p_profile_name,
370 p_profile_type,
371 ddp_profile_attrib,
372 p_query_id,
373 p_query_name,
374 p_query_type,
375 p_query_desc,
376 p_query_data_source,
377 ddp_query_param_tbl,
378 ddp_query_order_by_tbl,
379 ddp_query_raw_sql_rec,
380 x_query_id,
381 x_return_status,
382 x_msg_count,
383 x_msg_data);
384
385 -- copy data back from the local variables to OUT or IN-OUT args, if any
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405 end;
406
407 procedure create_perz_query(p_api_version_number NUMBER
408 , p_init_msg_list VARCHAR2
409 , p_commit VARCHAR2
410 , p_application_id NUMBER
411 , p_profile_id NUMBER
412 , p_profile_name VARCHAR2
413 , p_query_id NUMBER
414 , p_query_name VARCHAR2
415 , p_query_type VARCHAR2
416 , p_query_desc VARCHAR2
417 , p_query_data_source VARCHAR2
418 , p11_a0 JTF_NUMBER_TABLE
419 , p11_a1 JTF_NUMBER_TABLE
420 , p11_a2 JTF_VARCHAR2_TABLE_100
421 , p11_a3 JTF_VARCHAR2_TABLE_100
422 , p11_a4 JTF_VARCHAR2_TABLE_300
423 , p11_a5 JTF_VARCHAR2_TABLE_100
424 , p11_a6 JTF_NUMBER_TABLE
425 , p12_a0 JTF_NUMBER_TABLE
426 , p12_a1 JTF_NUMBER_TABLE
427 , p12_a2 JTF_VARCHAR2_TABLE_100
428 , p12_a3 JTF_VARCHAR2_TABLE_100
429 , p12_a4 JTF_NUMBER_TABLE
430 , p13_a0 NUMBER
431 , p13_a1 NUMBER
432 , p13_a2 VARCHAR2
433 , p13_a3 VARCHAR2
434 , p13_a4 VARCHAR2
435 , p13_a5 VARCHAR2
436 , p13_a6 VARCHAR2
437 , p13_a7 VARCHAR2
438 , x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER
439 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
440 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
441 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
442 )
443
444 as
445 ddp_query_param_tbl jtf_perz_query_pub.query_parameter_tbl_type;
446 ddp_query_order_by_tbl jtf_perz_query_pub.query_order_by_tbl_type;
447 ddp_query_raw_sql_rec jtf_perz_query_pub.query_raw_sql_rec_type;
448 ddindx binary_integer; indx binary_integer;
449 begin
450
451 -- copy data to the local IN or IN-OUT args, if any
452
453
454
455
456
457
458
459
460
461
462
463 jtf_perz_query_pub_w.rosetta_table_copy_in_p1(ddp_query_param_tbl, p11_a0
464 , p11_a1
465 , p11_a2
466 , p11_a3
467 , p11_a4
468 , p11_a5
469 , p11_a6
470 );
471
472 jtf_perz_query_pub_w.rosetta_table_copy_in_p6(ddp_query_order_by_tbl, p12_a0
473 , p12_a1
474 , p12_a2
475 , p12_a3
476 , p12_a4
477 );
478
479 ddp_query_raw_sql_rec.query_raw_sql_id := p13_a0;
480 ddp_query_raw_sql_rec.query_id := p13_a1;
481 ddp_query_raw_sql_rec.select_string := p13_a2;
482 ddp_query_raw_sql_rec.from_string := p13_a3;
483 ddp_query_raw_sql_rec.where_string := p13_a4;
484 ddp_query_raw_sql_rec.order_by_string := p13_a5;
485 ddp_query_raw_sql_rec.group_by_string := p13_a6;
486 ddp_query_raw_sql_rec.having_string := p13_a7;
487
488
489
490
491
492 -- here's the delegated call to the old PL/SQL routine
493 jtf_perz_query_pub.create_perz_query(p_api_version_number,
494 p_init_msg_list,
495 p_commit,
496 p_application_id,
497 p_profile_id,
498 p_profile_name,
499 p_query_id,
500 p_query_name,
501 p_query_type,
502 p_query_desc,
503 p_query_data_source,
504 ddp_query_param_tbl,
505 ddp_query_order_by_tbl,
506 ddp_query_raw_sql_rec,
507 x_query_id,
508 x_return_status,
509 x_msg_count,
510 x_msg_data);
511
512 -- copy data back from the local variables to OUT or IN-OUT args, if any
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530 end;
531
532 procedure get_perz_query(p_api_version_number NUMBER
533 , p_init_msg_list VARCHAR2
534 , p_application_id NUMBER
535 , p_profile_id NUMBER
536 , p_profile_name VARCHAR2
537 , p_query_id NUMBER
538 , p_query_name VARCHAR2
539 , p_query_type VARCHAR2
540 , x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER
541 , x_query_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2
542 , x_query_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2
543 , x_query_desc OUT NOCOPY /* file.sql.39 change */ VARCHAR2
544 , x_query_data_source OUT NOCOPY /* file.sql.39 change */ VARCHAR2
545 , p13_a0 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
546 , p13_a1 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
547 , p13_a2 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
548 , p13_a3 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
549 , p13_a4 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_300
550 , p13_a5 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
551 , p13_a6 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
552 , p14_a0 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
553 , p14_a1 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
554 , p14_a2 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
555 , p14_a3 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
556 , p14_a4 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
557 , p15_a0 OUT NOCOPY /* file.sql.39 change */ NUMBER
558 , p15_a1 OUT NOCOPY /* file.sql.39 change */ NUMBER
559 , p15_a2 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
560 , p15_a3 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
561 , p15_a4 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
562 , p15_a5 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
563 , p15_a6 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
564 , p15_a7 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
565 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
566 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
567 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
568 )
569
570 as
571 ddx_query_param_tbl jtf_perz_query_pub.query_parameter_tbl_type;
572 ddx_query_order_by_tbl jtf_perz_query_pub.query_order_by_tbl_type;
573 ddx_query_raw_sql_rec jtf_perz_query_pub.query_raw_sql_rec_type;
574 ddindx binary_integer; indx binary_integer;
575 begin
576
577 -- copy data to the local IN or IN-OUT args, if any
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597 -- here's the delegated call to the old PL/SQL routine
598 jtf_perz_query_pub.get_perz_query(p_api_version_number,
599 p_init_msg_list,
600 p_application_id,
601 p_profile_id,
602 p_profile_name,
603 p_query_id,
604 p_query_name,
605 p_query_type,
606 x_query_id,
607 x_query_name,
608 x_query_type,
609 x_query_desc,
610 x_query_data_source,
611 ddx_query_param_tbl,
612 ddx_query_order_by_tbl,
613 ddx_query_raw_sql_rec,
614 x_return_status,
615 x_msg_count,
616 x_msg_data);
617
618 -- copy data back from the local variables to OUT or IN-OUT args, if any
619
620
621
622
623
624
625
626
627
628
629
630
631
632 jtf_perz_query_pub_w.rosetta_table_copy_out_p1(ddx_query_param_tbl, p13_a0
633 , p13_a1
634 , p13_a2
635 , p13_a3
636 , p13_a4
637 , p13_a5
638 , p13_a6
639 );
640
641 jtf_perz_query_pub_w.rosetta_table_copy_out_p6(ddx_query_order_by_tbl, p14_a0
642 , p14_a1
643 , p14_a2
644 , p14_a3
645 , p14_a4
646 );
647
648 p15_a0 := ddx_query_raw_sql_rec.query_raw_sql_id;
649 p15_a1 := ddx_query_raw_sql_rec.query_id;
650 p15_a2 := ddx_query_raw_sql_rec.select_string;
651 p15_a3 := ddx_query_raw_sql_rec.from_string;
652 p15_a4 := ddx_query_raw_sql_rec.where_string;
653 p15_a5 := ddx_query_raw_sql_rec.order_by_string;
654 p15_a6 := ddx_query_raw_sql_rec.group_by_string;
655 p15_a7 := ddx_query_raw_sql_rec.having_string;
656
657
658
659 end;
660
661 procedure get_perz_query_summary(p_api_version_number NUMBER
662 , p_init_msg_list VARCHAR2
663 , p_application_id NUMBER
664 , p_profile_id NUMBER
665 , p_profile_name VARCHAR2
666 , p_query_id NUMBER
667 , p_query_name VARCHAR2
668 , p_query_type VARCHAR2
669 , p8_a0 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
670 , p8_a1 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
671 , p8_a2 OUT NOCOPY /* file.sql.39 change */ JTF_NUMBER_TABLE
672 , p8_a3 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
673 , p8_a4 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_100
674 , p8_a5 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_300
675 , p8_a6 OUT NOCOPY /* file.sql.39 change */ JTF_VARCHAR2_TABLE_2000
676 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
677 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
678 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
679 )
680
681 as
682 ddx_query_out_tbl jtf_perz_query_pub.query_out_tbl_type;
683 ddindx binary_integer; indx binary_integer;
684 begin
685
686 -- copy data to the local IN or IN-OUT args, if any
687
688
689
690
691
692
693
694
695
696
697
698
699 -- here's the delegated call to the old PL/SQL routine
700 jtf_perz_query_pub.get_perz_query_summary(p_api_version_number,
701 p_init_msg_list,
702 p_application_id,
703 p_profile_id,
704 p_profile_name,
705 p_query_id,
706 p_query_name,
707 p_query_type,
708 ddx_query_out_tbl,
709 x_return_status,
710 x_msg_count,
711 x_msg_data);
712
713 -- copy data back from the local variables to OUT or IN-OUT args, if any
714
715
716
717
718
719
720
721
722 jtf_perz_query_pub_w.rosetta_table_copy_out_p4(ddx_query_out_tbl, p8_a0
723 , p8_a1
724 , p8_a2
725 , p8_a3
726 , p8_a4
727 , p8_a5
728 , p8_a6
729 );
730
731
732
733 end;
734
735 procedure update_perz_query(p_api_version_number NUMBER
736 , p_init_msg_list VARCHAR2
737 , p_commit VARCHAR2
738 , p_application_id NUMBER
739 , p_profile_id NUMBER
740 , p_query_id NUMBER
741 , p_query_name VARCHAR2
742 , p_query_type VARCHAR2
743 , p_query_desc VARCHAR2
744 , p_query_data_source VARCHAR2
745 , p10_a0 JTF_NUMBER_TABLE
746 , p10_a1 JTF_NUMBER_TABLE
747 , p10_a2 JTF_VARCHAR2_TABLE_100
748 , p10_a3 JTF_VARCHAR2_TABLE_100
749 , p10_a4 JTF_VARCHAR2_TABLE_300
750 , p10_a5 JTF_VARCHAR2_TABLE_100
751 , p10_a6 JTF_NUMBER_TABLE
752 , p11_a0 JTF_NUMBER_TABLE
753 , p11_a1 JTF_NUMBER_TABLE
754 , p11_a2 JTF_VARCHAR2_TABLE_100
755 , p11_a3 JTF_VARCHAR2_TABLE_100
756 , p11_a4 JTF_NUMBER_TABLE
757 , p12_a0 NUMBER
758 , p12_a1 NUMBER
759 , p12_a2 VARCHAR2
760 , p12_a3 VARCHAR2
761 , p12_a4 VARCHAR2
762 , p12_a5 VARCHAR2
763 , p12_a6 VARCHAR2
764 , p12_a7 VARCHAR2
765 , x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER
766 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
767 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
768 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
769 )
770
771 as
772 ddp_query_param_tbl jtf_perz_query_pub.query_parameter_tbl_type;
773 ddp_query_order_by_tbl jtf_perz_query_pub.query_order_by_tbl_type;
774 ddp_query_raw_sql_rec jtf_perz_query_pub.query_raw_sql_rec_type;
775 ddindx binary_integer; indx binary_integer;
776 begin
777
778 -- copy data to the local IN or IN-OUT args, if any
779
780
781
782
783
784
785
786
787
788
789 jtf_perz_query_pub_w.rosetta_table_copy_in_p1(ddp_query_param_tbl, p10_a0
790 , p10_a1
791 , p10_a2
792 , p10_a3
793 , p10_a4
794 , p10_a5
795 , p10_a6
796 );
797
798 jtf_perz_query_pub_w.rosetta_table_copy_in_p6(ddp_query_order_by_tbl, p11_a0
799 , p11_a1
800 , p11_a2
801 , p11_a3
802 , p11_a4
803 );
804
805 ddp_query_raw_sql_rec.query_raw_sql_id := p12_a0;
806 ddp_query_raw_sql_rec.query_id := p12_a1;
807 ddp_query_raw_sql_rec.select_string := p12_a2;
808 ddp_query_raw_sql_rec.from_string := p12_a3;
809 ddp_query_raw_sql_rec.where_string := p12_a4;
810 ddp_query_raw_sql_rec.order_by_string := p12_a5;
811 ddp_query_raw_sql_rec.group_by_string := p12_a6;
812 ddp_query_raw_sql_rec.having_string := p12_a7;
813
814
815
816
817
818 -- here's the delegated call to the old PL/SQL routine
819 jtf_perz_query_pub.update_perz_query(p_api_version_number,
820 p_init_msg_list,
821 p_commit,
822 p_application_id,
823 p_profile_id,
824 p_query_id,
825 p_query_name,
826 p_query_type,
827 p_query_desc,
828 p_query_data_source,
829 ddp_query_param_tbl,
830 ddp_query_order_by_tbl,
831 ddp_query_raw_sql_rec,
832 x_query_id,
833 x_return_status,
834 x_msg_count,
835 x_msg_data);
836
837 -- copy data back from the local variables to OUT or IN-OUT args, if any
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854 end;
855
856 end jtf_perz_query_pub_w;