[Home] [Help]
PACKAGE BODY: APPS.CS_TP_QUESTIONS_PVT_W
Source
1 package body cs_tp_questions_pvt_w as
2 /* $Header: cstprqsb.pls 120.2 2005/06/30 11:04 appldev 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_p1(t out nocopy cs_tp_questions_pvt.question_list, a0 JTF_NUMBER_TABLE
23 , a1 JTF_VARCHAR2_TABLE_2000
24 , a2 JTF_VARCHAR2_TABLE_100
25 , a3 JTF_VARCHAR2_TABLE_100
26 , a4 JTF_VARCHAR2_TABLE_100
27 , a5 JTF_NUMBER_TABLE
28 , a6 JTF_VARCHAR2_TABLE_100
29 , a7 JTF_VARCHAR2_TABLE_100
30 , a8 JTF_VARCHAR2_TABLE_100
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).mquestionid := rosetta_g_miss_num_map(a0(indx));
40 t(ddindx).mquestionname := a1(indx);
41 t(ddindx).manswertype := a2(indx);
42 t(ddindx).mmandatoryflag := a3(indx);
43 t(ddindx).mscoringflag := a4(indx);
44 t(ddindx).mlookupid := rosetta_g_miss_num_map(a5(indx));
45 t(ddindx).mlast_updated_date := a6(indx);
46 t(ddindx).mnotetype := a7(indx);
47 t(ddindx).mshowoncreationflag := a8(indx);
48 ddindx := ddindx+1;
49 if a0.last =indx
50 then exit;
51 end if;
52 indx := a0.next(indx);
53 end loop;
54 end if;
55 end if;
56 end rosetta_table_copy_in_p1;
57 procedure rosetta_table_copy_out_p1(t cs_tp_questions_pvt.question_list, a0 out nocopy JTF_NUMBER_TABLE
58 , a1 out nocopy JTF_VARCHAR2_TABLE_2000
59 , a2 out nocopy JTF_VARCHAR2_TABLE_100
60 , a3 out nocopy JTF_VARCHAR2_TABLE_100
61 , a4 out nocopy JTF_VARCHAR2_TABLE_100
62 , a5 out nocopy JTF_NUMBER_TABLE
63 , a6 out nocopy JTF_VARCHAR2_TABLE_100
64 , a7 out nocopy JTF_VARCHAR2_TABLE_100
65 , a8 out nocopy JTF_VARCHAR2_TABLE_100
66 ) as
67 ddindx binary_integer; indx binary_integer;
68 begin
69 if t is null or t.count = 0 then
70 a0 := JTF_NUMBER_TABLE();
71 a1 := JTF_VARCHAR2_TABLE_2000();
72 a2 := JTF_VARCHAR2_TABLE_100();
73 a3 := JTF_VARCHAR2_TABLE_100();
74 a4 := JTF_VARCHAR2_TABLE_100();
75 a5 := JTF_NUMBER_TABLE();
76 a6 := JTF_VARCHAR2_TABLE_100();
77 a7 := JTF_VARCHAR2_TABLE_100();
78 a8 := JTF_VARCHAR2_TABLE_100();
79 else
80 a0 := JTF_NUMBER_TABLE();
81 a1 := JTF_VARCHAR2_TABLE_2000();
82 a2 := JTF_VARCHAR2_TABLE_100();
83 a3 := JTF_VARCHAR2_TABLE_100();
84 a4 := JTF_VARCHAR2_TABLE_100();
85 a5 := JTF_NUMBER_TABLE();
86 a6 := JTF_VARCHAR2_TABLE_100();
87 a7 := JTF_VARCHAR2_TABLE_100();
88 a8 := JTF_VARCHAR2_TABLE_100();
89 if t.count > 0 then
90 a0.extend(t.count);
91 a1.extend(t.count);
92 a2.extend(t.count);
93 a3.extend(t.count);
94 a4.extend(t.count);
95 a5.extend(t.count);
96 a6.extend(t.count);
97 a7.extend(t.count);
98 a8.extend(t.count);
99 ddindx := t.first;
100 indx := 1;
101 while true loop
102 a0(indx) := rosetta_g_miss_num_map(t(ddindx).mquestionid);
103 a1(indx) := t(ddindx).mquestionname;
104 a2(indx) := t(ddindx).manswertype;
105 a3(indx) := t(ddindx).mmandatoryflag;
106 a4(indx) := t(ddindx).mscoringflag;
107 a5(indx) := rosetta_g_miss_num_map(t(ddindx).mlookupid);
108 a6(indx) := t(ddindx).mlast_updated_date;
109 a7(indx) := t(ddindx).mnotetype;
110 a8(indx) := t(ddindx).mshowoncreationflag;
111 indx := indx+1;
112 if t.last =ddindx
113 then exit;
114 end if;
115 ddindx := t.next(ddindx);
116 end loop;
117 end if;
118 end if;
119 end rosetta_table_copy_out_p1;
120
121 procedure add_question(p_api_version_number NUMBER
122 , p_init_msg_list VARCHAR2
123 , p_commit VARCHAR
124 , p_template_id NUMBER
125 , x_msg_count out nocopy NUMBER
126 , x_msg_data out nocopy VARCHAR2
127 , x_return_status out nocopy VARCHAR2
128 , x_question_id out nocopy NUMBER
129 , p3_a0 NUMBER := 0-1962.0724
130 , p3_a1 VARCHAR2 := fnd_api.g_miss_char
131 , p3_a2 VARCHAR2 := fnd_api.g_miss_char
132 , p3_a3 VARCHAR2 := fnd_api.g_miss_char
133 , p3_a4 VARCHAR2 := fnd_api.g_miss_char
134 , p3_a5 NUMBER := 0-1962.0724
135 , p3_a6 VARCHAR2 := fnd_api.g_miss_char
136 , p3_a7 VARCHAR2 := fnd_api.g_miss_char
137 , p3_a8 VARCHAR2 := fnd_api.g_miss_char
138 )
139
140 as
141 ddp_one_question cs_tp_questions_pvt.question;
142 ddindx binary_integer; indx binary_integer;
143 begin
144
145 -- copy data to the local IN or IN-OUT args, if any
146
147
148
149 ddp_one_question.mquestionid := rosetta_g_miss_num_map(p3_a0);
150 ddp_one_question.mquestionname := p3_a1;
151 ddp_one_question.manswertype := p3_a2;
152 ddp_one_question.mmandatoryflag := p3_a3;
153 ddp_one_question.mscoringflag := p3_a4;
154 ddp_one_question.mlookupid := rosetta_g_miss_num_map(p3_a5);
155 ddp_one_question.mlast_updated_date := p3_a6;
156 ddp_one_question.mnotetype := p3_a7;
157 ddp_one_question.mshowoncreationflag := p3_a8;
158
159
160
161
162
163
164 -- here's the delegated call to the old PL/SQL routine
165 cs_tp_questions_pvt.add_question(p_api_version_number,
166 p_init_msg_list,
167 p_commit,
168 ddp_one_question,
169 p_template_id,
170 x_msg_count,
171 x_msg_data,
172 x_return_status,
173 x_question_id);
174
175 -- copy data back from the local variables to OUT or IN-OUT args, if any
176
177
178
179
180
181
182
183
184 end;
185
186 procedure update_question(p_api_version_number NUMBER
187 , p_init_msg_list VARCHAR2
188 , p_commit VARCHAR
189 , x_msg_count out nocopy NUMBER
190 , x_msg_data out nocopy VARCHAR2
191 , x_return_status out nocopy VARCHAR2
192 , p3_a0 NUMBER := 0-1962.0724
193 , p3_a1 VARCHAR2 := fnd_api.g_miss_char
194 , p3_a2 VARCHAR2 := fnd_api.g_miss_char
195 , p3_a3 VARCHAR2 := fnd_api.g_miss_char
196 , p3_a4 VARCHAR2 := fnd_api.g_miss_char
197 , p3_a5 NUMBER := 0-1962.0724
198 , p3_a6 VARCHAR2 := fnd_api.g_miss_char
199 , p3_a7 VARCHAR2 := fnd_api.g_miss_char
200 , p3_a8 VARCHAR2 := fnd_api.g_miss_char
201 )
202
203 as
204 ddp_one_question cs_tp_questions_pvt.question;
205 ddindx binary_integer; indx binary_integer;
206 begin
207
208 -- copy data to the local IN or IN-OUT args, if any
209
210
211
212 ddp_one_question.mquestionid := rosetta_g_miss_num_map(p3_a0);
213 ddp_one_question.mquestionname := p3_a1;
214 ddp_one_question.manswertype := p3_a2;
215 ddp_one_question.mmandatoryflag := p3_a3;
216 ddp_one_question.mscoringflag := p3_a4;
217 ddp_one_question.mlookupid := rosetta_g_miss_num_map(p3_a5);
218 ddp_one_question.mlast_updated_date := p3_a6;
219 ddp_one_question.mnotetype := p3_a7;
220 ddp_one_question.mshowoncreationflag := p3_a8;
221
222
223
224
225 -- here's the delegated call to the old PL/SQL routine
226 cs_tp_questions_pvt.update_question(p_api_version_number,
227 p_init_msg_list,
228 p_commit,
229 ddp_one_question,
230 x_msg_count,
231 x_msg_data,
232 x_return_status);
233
234 -- copy data back from the local variables to OUT or IN-OUT args, if any
235
236
237
238
239
240
241 end;
242
243 procedure sort_questions(p_api_version_number NUMBER
244 , p_init_msg_list VARCHAR2
245 , p_commit VARCHAR
246 , p3_a0 JTF_NUMBER_TABLE
247 , p3_a1 JTF_VARCHAR2_TABLE_2000
248 , p3_a2 JTF_VARCHAR2_TABLE_100
249 , p3_a3 JTF_VARCHAR2_TABLE_100
250 , p3_a4 JTF_VARCHAR2_TABLE_100
251 , p3_a5 JTF_NUMBER_TABLE
252 , p3_a6 JTF_VARCHAR2_TABLE_100
253 , p3_a7 JTF_VARCHAR2_TABLE_100
254 , p3_a8 JTF_VARCHAR2_TABLE_100
255 , p_template_id NUMBER
256 , x_msg_count out nocopy NUMBER
257 , x_msg_data out nocopy VARCHAR2
258 , x_return_status out nocopy VARCHAR2
259 )
260
261 as
262 ddp_questions cs_tp_questions_pvt.question_list;
263 ddindx binary_integer; indx binary_integer;
264 begin
265
266 -- copy data to the local IN or IN-OUT args, if any
267
268
269
270 cs_tp_questions_pvt_w.rosetta_table_copy_in_p1(ddp_questions, p3_a0
271 , p3_a1
272 , p3_a2
273 , p3_a3
274 , p3_a4
275 , p3_a5
276 , p3_a6
277 , p3_a7
278 , p3_a8
279 );
280
281
282
283
284
285 -- here's the delegated call to the old PL/SQL routine
286 cs_tp_questions_pvt.sort_questions(p_api_version_number,
287 p_init_msg_list,
288 p_commit,
289 ddp_questions,
290 p_template_id,
291 x_msg_count,
292 x_msg_data,
293 x_return_status);
294
295 -- copy data back from the local variables to OUT or IN-OUT args, if any
296
297
298
299
300
301
302
303 end;
304
305 procedure show_questions(p_api_version_number NUMBER
306 , p_init_msg_list VARCHAR2
307 , p_commit VARCHAR
308 , p_template_id NUMBER
309 , p_start_question NUMBER
310 , p_end_question NUMBER
311 , p_display_order VARCHAR2
312 , x_msg_count out nocopy NUMBER
313 , x_msg_data out nocopy VARCHAR2
314 , x_return_status out nocopy VARCHAR2
315 , p10_a0 out nocopy JTF_NUMBER_TABLE
316 , p10_a1 out nocopy JTF_VARCHAR2_TABLE_2000
317 , p10_a2 out nocopy JTF_VARCHAR2_TABLE_100
318 , p10_a3 out nocopy JTF_VARCHAR2_TABLE_100
319 , p10_a4 out nocopy JTF_VARCHAR2_TABLE_100
320 , p10_a5 out nocopy JTF_NUMBER_TABLE
321 , p10_a6 out nocopy JTF_VARCHAR2_TABLE_100
322 , p10_a7 out nocopy JTF_VARCHAR2_TABLE_100
323 , p10_a8 out nocopy JTF_VARCHAR2_TABLE_100
324 , x_total_questions out nocopy NUMBER
325 , x_retrieved_question_number out nocopy NUMBER
326 )
327
328 as
329 ddx_question_list_to_show cs_tp_questions_pvt.question_list;
330 ddindx binary_integer; indx binary_integer;
331 begin
332
333 -- copy data to the local IN or IN-OUT args, if any
334
335
336
337
338
339
340
341
342
343
344
345
346
347 -- here's the delegated call to the old PL/SQL routine
348 cs_tp_questions_pvt.show_questions(p_api_version_number,
349 p_init_msg_list,
350 p_commit,
351 p_template_id,
352 p_start_question,
353 p_end_question,
354 p_display_order,
355 x_msg_count,
356 x_msg_data,
357 x_return_status,
358 ddx_question_list_to_show,
359 x_total_questions,
360 x_retrieved_question_number);
361
362 -- copy data back from the local variables to OUT or IN-OUT args, if any
363
364
365
366
367
368
369
370
371
372
373 cs_tp_questions_pvt_w.rosetta_table_copy_out_p1(ddx_question_list_to_show, p10_a0
374 , p10_a1
375 , p10_a2
376 , p10_a3
377 , p10_a4
378 , p10_a5
379 , p10_a6
380 , p10_a7
381 , p10_a8
382 );
383
384
385 end;
386
387 procedure show_question(p_api_version_number NUMBER
388 , p_init_msg_list VARCHAR2
389 , p_commit VARCHAR
390 , p_question_id NUMBER
391 , x_msg_count out nocopy NUMBER
392 , x_msg_data out nocopy VARCHAR2
393 , x_return_status out nocopy VARCHAR2
394 , p7_a0 out nocopy NUMBER
395 , p7_a1 out nocopy VARCHAR2
396 , p7_a2 out nocopy VARCHAR2
397 , p7_a3 out nocopy VARCHAR2
398 , p7_a4 out nocopy VARCHAR2
399 , p7_a5 out nocopy NUMBER
400 , p7_a6 out nocopy VARCHAR2
401 , p7_a7 out nocopy VARCHAR2
402 , p7_a8 out nocopy VARCHAR2
403 )
404
405 as
406 ddx_question_to_show cs_tp_questions_pvt.question;
407 ddindx binary_integer; indx binary_integer;
408 begin
409
410 -- copy data to the local IN or IN-OUT args, if any
411
412
413
414
415
416
417
418
419 -- here's the delegated call to the old PL/SQL routine
420 cs_tp_questions_pvt.show_question(p_api_version_number,
421 p_init_msg_list,
422 p_commit,
423 p_question_id,
424 x_msg_count,
425 x_msg_data,
426 x_return_status,
427 ddx_question_to_show);
428
429 -- copy data back from the local variables to OUT or IN-OUT args, if any
430
431
432
433
434
435
436
437 p7_a0 := rosetta_g_miss_num_map(ddx_question_to_show.mquestionid);
438 p7_a1 := ddx_question_to_show.mquestionname;
439 p7_a2 := ddx_question_to_show.manswertype;
440 p7_a3 := ddx_question_to_show.mmandatoryflag;
441 p7_a4 := ddx_question_to_show.mscoringflag;
442 p7_a5 := rosetta_g_miss_num_map(ddx_question_to_show.mlookupid);
443 p7_a6 := ddx_question_to_show.mlast_updated_date;
444 p7_a7 := ddx_question_to_show.mnotetype;
445 p7_a8 := ddx_question_to_show.mshowoncreationflag;
446 end;
447
448 end cs_tp_questions_pvt_w;