[Home] [Help]
PACKAGE BODY: APPS.AS_SCORECARD_RULES_PUB_W
Source
1 package body as_scorecard_rules_pub_w as
2 /* $Header: asxwscob.pls 120.2 2006/03/09 15:41 solin 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 as_scorecard_rules_pub.cardrule_qual_tbl_type, a0 JTF_NUMBER_TABLE
23 , a1 JTF_DATE_TABLE
24 , a2 JTF_NUMBER_TABLE
25 , a3 JTF_DATE_TABLE
26 , a4 JTF_NUMBER_TABLE
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_100
36 , a14 JTF_VARCHAR2_TABLE_100
37 , a15 JTF_DATE_TABLE
38 , a16 JTF_DATE_TABLE
39 , a17 JTF_DATE_TABLE
40 , a18 JTF_DATE_TABLE
41 ) as
42 ddindx binary_integer; indx binary_integer;
43 begin
44 if a0 is not null and a0.count > 0 then
45 if a0.count > 0 then
46 indx := a0.first;
47 ddindx := 1;
48 while true loop
49 t(ddindx).qual_value_id := rosetta_g_miss_num_map(a0(indx));
50 t(ddindx).last_update_date := rosetta_g_miss_date_in_map(a1(indx));
51 t(ddindx).last_updated_by := rosetta_g_miss_num_map(a2(indx));
52 t(ddindx).creation_date := rosetta_g_miss_date_in_map(a3(indx));
53 t(ddindx).created_by := rosetta_g_miss_num_map(a4(indx));
54 t(ddindx).last_update_login := rosetta_g_miss_num_map(a5(indx));
55 t(ddindx).scorecard_id := rosetta_g_miss_num_map(a6(indx));
56 t(ddindx).score := rosetta_g_miss_num_map(a7(indx));
57 t(ddindx).card_rule_id := rosetta_g_miss_num_map(a8(indx));
58 t(ddindx).seed_qual_id := rosetta_g_miss_num_map(a9(indx));
59 t(ddindx).high_value_number := rosetta_g_miss_num_map(a10(indx));
60 t(ddindx).low_value_number := rosetta_g_miss_num_map(a11(indx));
61 t(ddindx).high_value_char := a12(indx);
62 t(ddindx).low_value_char := a13(indx);
63 t(ddindx).currency_code := a14(indx);
64 t(ddindx).low_value_date := rosetta_g_miss_date_in_map(a15(indx));
65 t(ddindx).high_value_date := rosetta_g_miss_date_in_map(a16(indx));
66 t(ddindx).start_date_active := rosetta_g_miss_date_in_map(a17(indx));
67 t(ddindx).end_date_active := rosetta_g_miss_date_in_map(a18(indx));
68 ddindx := ddindx+1;
69 if a0.last =indx
70 then exit;
71 end if;
72 indx := a0.next(indx);
73 end loop;
74 end if;
75 end if;
76 end rosetta_table_copy_in_p2;
77 procedure rosetta_table_copy_out_p2(t as_scorecard_rules_pub.cardrule_qual_tbl_type, a0 out nocopy JTF_NUMBER_TABLE
78 , a1 out nocopy JTF_DATE_TABLE
79 , a2 out nocopy JTF_NUMBER_TABLE
80 , a3 out nocopy JTF_DATE_TABLE
81 , a4 out nocopy JTF_NUMBER_TABLE
82 , a5 out nocopy JTF_NUMBER_TABLE
83 , a6 out nocopy JTF_NUMBER_TABLE
84 , a7 out nocopy JTF_NUMBER_TABLE
85 , a8 out nocopy JTF_NUMBER_TABLE
86 , a9 out nocopy JTF_NUMBER_TABLE
87 , a10 out nocopy JTF_NUMBER_TABLE
88 , a11 out nocopy JTF_NUMBER_TABLE
89 , a12 out nocopy JTF_VARCHAR2_TABLE_100
90 , a13 out nocopy JTF_VARCHAR2_TABLE_100
91 , a14 out nocopy JTF_VARCHAR2_TABLE_100
92 , a15 out nocopy JTF_DATE_TABLE
93 , a16 out nocopy JTF_DATE_TABLE
94 , a17 out nocopy JTF_DATE_TABLE
95 , a18 out nocopy JTF_DATE_TABLE
96 ) as
97 ddindx binary_integer; indx binary_integer;
98 begin
99 if t is null or t.count = 0 then
100 a0 := JTF_NUMBER_TABLE();
101 a1 := JTF_DATE_TABLE();
102 a2 := JTF_NUMBER_TABLE();
103 a3 := JTF_DATE_TABLE();
104 a4 := JTF_NUMBER_TABLE();
105 a5 := JTF_NUMBER_TABLE();
106 a6 := JTF_NUMBER_TABLE();
107 a7 := JTF_NUMBER_TABLE();
108 a8 := JTF_NUMBER_TABLE();
109 a9 := JTF_NUMBER_TABLE();
110 a10 := JTF_NUMBER_TABLE();
111 a11 := JTF_NUMBER_TABLE();
112 a12 := JTF_VARCHAR2_TABLE_100();
113 a13 := JTF_VARCHAR2_TABLE_100();
114 a14 := JTF_VARCHAR2_TABLE_100();
115 a15 := JTF_DATE_TABLE();
116 a16 := JTF_DATE_TABLE();
117 a17 := JTF_DATE_TABLE();
118 a18 := JTF_DATE_TABLE();
119 else
120 a0 := JTF_NUMBER_TABLE();
121 a1 := JTF_DATE_TABLE();
122 a2 := JTF_NUMBER_TABLE();
123 a3 := JTF_DATE_TABLE();
124 a4 := JTF_NUMBER_TABLE();
125 a5 := JTF_NUMBER_TABLE();
126 a6 := JTF_NUMBER_TABLE();
127 a7 := JTF_NUMBER_TABLE();
128 a8 := JTF_NUMBER_TABLE();
129 a9 := JTF_NUMBER_TABLE();
130 a10 := JTF_NUMBER_TABLE();
131 a11 := JTF_NUMBER_TABLE();
132 a12 := JTF_VARCHAR2_TABLE_100();
133 a13 := JTF_VARCHAR2_TABLE_100();
134 a14 := JTF_VARCHAR2_TABLE_100();
135 a15 := JTF_DATE_TABLE();
136 a16 := JTF_DATE_TABLE();
137 a17 := JTF_DATE_TABLE();
138 a18 := JTF_DATE_TABLE();
139 if t.count > 0 then
140 a0.extend(t.count);
141 a1.extend(t.count);
142 a2.extend(t.count);
143 a3.extend(t.count);
144 a4.extend(t.count);
145 a5.extend(t.count);
146 a6.extend(t.count);
147 a7.extend(t.count);
148 a8.extend(t.count);
149 a9.extend(t.count);
150 a10.extend(t.count);
151 a11.extend(t.count);
152 a12.extend(t.count);
153 a13.extend(t.count);
154 a14.extend(t.count);
155 a15.extend(t.count);
156 a16.extend(t.count);
157 a17.extend(t.count);
158 a18.extend(t.count);
159 ddindx := t.first;
160 indx := 1;
161 while true loop
162 a0(indx) := rosetta_g_miss_num_map(t(ddindx).qual_value_id);
163 a1(indx) := t(ddindx).last_update_date;
164 a2(indx) := rosetta_g_miss_num_map(t(ddindx).last_updated_by);
165 a3(indx) := t(ddindx).creation_date;
166 a4(indx) := rosetta_g_miss_num_map(t(ddindx).created_by);
167 a5(indx) := rosetta_g_miss_num_map(t(ddindx).last_update_login);
168 a6(indx) := rosetta_g_miss_num_map(t(ddindx).scorecard_id);
169 a7(indx) := rosetta_g_miss_num_map(t(ddindx).score);
170 a8(indx) := rosetta_g_miss_num_map(t(ddindx).card_rule_id);
171 a9(indx) := rosetta_g_miss_num_map(t(ddindx).seed_qual_id);
172 a10(indx) := rosetta_g_miss_num_map(t(ddindx).high_value_number);
173 a11(indx) := rosetta_g_miss_num_map(t(ddindx).low_value_number);
174 a12(indx) := t(ddindx).high_value_char;
175 a13(indx) := t(ddindx).low_value_char;
176 a14(indx) := t(ddindx).currency_code;
177 a15(indx) := t(ddindx).low_value_date;
178 a16(indx) := t(ddindx).high_value_date;
179 a17(indx) := t(ddindx).start_date_active;
180 a18(indx) := t(ddindx).end_date_active;
181 indx := indx+1;
182 if t.last =ddindx
183 then exit;
184 end if;
185 ddindx := t.next(ddindx);
186 end loop;
187 end if;
188 end if;
189 end rosetta_table_copy_out_p2;
190
191 procedure create_scorecard(p_api_version NUMBER
192 , p_init_msg_list VARCHAR2
193 , p_commit VARCHAR2
194 , p_validation_level NUMBER
195 , x_return_status out nocopy VARCHAR2
196 , x_msg_count out nocopy NUMBER
197 , x_msg_data out nocopy VARCHAR2
198 , x_scorecard_id out nocopy NUMBER
199 , p7_a0 NUMBER := 0-1962.0724
200 , p7_a1 DATE := fnd_api.g_miss_date
201 , p7_a2 NUMBER := 0-1962.0724
202 , p7_a3 DATE := fnd_api.g_miss_date
203 , p7_a4 NUMBER := 0-1962.0724
204 , p7_a5 NUMBER := 0-1962.0724
205 , p7_a6 VARCHAR2 := fnd_api.g_miss_char
206 , p7_a7 VARCHAR2 := fnd_api.g_miss_char
207 , p7_a8 DATE := fnd_api.g_miss_date
208 , p7_a9 DATE := fnd_api.g_miss_date
209 )
210
211 as
212 ddp_scorecard_rec as_scorecard_rules_pub.scorecard_rec_type;
213 ddindx binary_integer; indx binary_integer;
214 begin
215
216 -- copy data to the local IN or IN-OUT args, if any
217
218
219
220
221
222
223
224 ddp_scorecard_rec.scorecard_id := rosetta_g_miss_num_map(p7_a0);
225 ddp_scorecard_rec.last_update_date := rosetta_g_miss_date_in_map(p7_a1);
226 ddp_scorecard_rec.last_updated_by := rosetta_g_miss_num_map(p7_a2);
227 ddp_scorecard_rec.creation_date := rosetta_g_miss_date_in_map(p7_a3);
228 ddp_scorecard_rec.created_by := rosetta_g_miss_num_map(p7_a4);
229 ddp_scorecard_rec.last_update_login := rosetta_g_miss_num_map(p7_a5);
230 ddp_scorecard_rec.description := p7_a6;
231 ddp_scorecard_rec.enabled_flag := p7_a7;
232 ddp_scorecard_rec.start_date_active := rosetta_g_miss_date_in_map(p7_a8);
233 ddp_scorecard_rec.end_date_active := rosetta_g_miss_date_in_map(p7_a9);
234
235
236 -- here's the delegated call to the old PL/SQL routine
237 as_scorecard_rules_pub.create_scorecard(p_api_version,
238 p_init_msg_list,
239 p_commit,
240 p_validation_level,
241 x_return_status,
242 x_msg_count,
243 x_msg_data,
244 ddp_scorecard_rec,
245 x_scorecard_id);
246
247 -- copy data back from the local variables to OUT or IN-OUT args, if any
248
249
250
251
252
253
254
255
256 end;
257
258 procedure update_scorecard(p_api_version NUMBER
259 , p_init_msg_list VARCHAR2
260 , p_commit VARCHAR2
261 , p_validation_level NUMBER
262 , x_return_status out nocopy VARCHAR2
263 , x_msg_count out nocopy NUMBER
264 , x_msg_data out nocopy VARCHAR2
265 , p7_a0 NUMBER := 0-1962.0724
266 , p7_a1 DATE := fnd_api.g_miss_date
267 , p7_a2 NUMBER := 0-1962.0724
268 , p7_a3 DATE := fnd_api.g_miss_date
269 , p7_a4 NUMBER := 0-1962.0724
270 , p7_a5 NUMBER := 0-1962.0724
271 , p7_a6 VARCHAR2 := fnd_api.g_miss_char
272 , p7_a7 VARCHAR2 := fnd_api.g_miss_char
273 , p7_a8 DATE := fnd_api.g_miss_date
274 , p7_a9 DATE := fnd_api.g_miss_date
275 )
276
277 as
278 ddp_scorecard_rec as_scorecard_rules_pub.scorecard_rec_type;
279 ddindx binary_integer; indx binary_integer;
280 begin
281
282 -- copy data to the local IN or IN-OUT args, if any
283
284
285
286
287
288
289
290 ddp_scorecard_rec.scorecard_id := rosetta_g_miss_num_map(p7_a0);
291 ddp_scorecard_rec.last_update_date := rosetta_g_miss_date_in_map(p7_a1);
292 ddp_scorecard_rec.last_updated_by := rosetta_g_miss_num_map(p7_a2);
293 ddp_scorecard_rec.creation_date := rosetta_g_miss_date_in_map(p7_a3);
294 ddp_scorecard_rec.created_by := rosetta_g_miss_num_map(p7_a4);
295 ddp_scorecard_rec.last_update_login := rosetta_g_miss_num_map(p7_a5);
296 ddp_scorecard_rec.description := p7_a6;
297 ddp_scorecard_rec.enabled_flag := p7_a7;
298 ddp_scorecard_rec.start_date_active := rosetta_g_miss_date_in_map(p7_a8);
299 ddp_scorecard_rec.end_date_active := rosetta_g_miss_date_in_map(p7_a9);
300
301 -- here's the delegated call to the old PL/SQL routine
302 as_scorecard_rules_pub.update_scorecard(p_api_version,
303 p_init_msg_list,
304 p_commit,
305 p_validation_level,
306 x_return_status,
307 x_msg_count,
308 x_msg_data,
309 ddp_scorecard_rec);
310
311 -- copy data back from the local variables to OUT or IN-OUT args, if any
312
313
314
315
316
317
318
319 end;
320
321 procedure create_cardrule_qual(p_api_version NUMBER
322 , p_init_msg_list VARCHAR2
323 , p_commit VARCHAR2
324 , p_validation_level NUMBER
325 , x_return_status out nocopy VARCHAR2
326 , x_msg_count out nocopy NUMBER
327 , x_msg_data out nocopy VARCHAR2
328 , x_qual_value_id out nocopy NUMBER
329 , p7_a0 NUMBER := 0-1962.0724
330 , p7_a1 DATE := fnd_api.g_miss_date
331 , p7_a2 NUMBER := 0-1962.0724
332 , p7_a3 DATE := fnd_api.g_miss_date
333 , p7_a4 NUMBER := 0-1962.0724
334 , p7_a5 NUMBER := 0-1962.0724
335 , p7_a6 NUMBER := 0-1962.0724
336 , p7_a7 NUMBER := 0-1962.0724
337 , p7_a8 NUMBER := 0-1962.0724
338 , p7_a9 NUMBER := 0-1962.0724
339 , p7_a10 NUMBER := 0-1962.0724
340 , p7_a11 NUMBER := 0-1962.0724
341 , p7_a12 VARCHAR2 := fnd_api.g_miss_char
342 , p7_a13 VARCHAR2 := fnd_api.g_miss_char
343 , p7_a14 VARCHAR2 := fnd_api.g_miss_char
344 , p7_a15 DATE := fnd_api.g_miss_date
345 , p7_a16 DATE := fnd_api.g_miss_date
346 , p7_a17 DATE := fnd_api.g_miss_date
347 , p7_a18 DATE := fnd_api.g_miss_date
348 )
349
350 as
351 ddp_cardrule_qual_rec as_scorecard_rules_pub.cardrule_qual_rec_type;
352 ddindx binary_integer; indx binary_integer;
353 begin
354
355 -- copy data to the local IN or IN-OUT args, if any
356
357
358
359
360
361
362
363 ddp_cardrule_qual_rec.qual_value_id := rosetta_g_miss_num_map(p7_a0);
364 ddp_cardrule_qual_rec.last_update_date := rosetta_g_miss_date_in_map(p7_a1);
365 ddp_cardrule_qual_rec.last_updated_by := rosetta_g_miss_num_map(p7_a2);
366 ddp_cardrule_qual_rec.creation_date := rosetta_g_miss_date_in_map(p7_a3);
367 ddp_cardrule_qual_rec.created_by := rosetta_g_miss_num_map(p7_a4);
368 ddp_cardrule_qual_rec.last_update_login := rosetta_g_miss_num_map(p7_a5);
369 ddp_cardrule_qual_rec.scorecard_id := rosetta_g_miss_num_map(p7_a6);
370 ddp_cardrule_qual_rec.score := rosetta_g_miss_num_map(p7_a7);
371 ddp_cardrule_qual_rec.card_rule_id := rosetta_g_miss_num_map(p7_a8);
372 ddp_cardrule_qual_rec.seed_qual_id := rosetta_g_miss_num_map(p7_a9);
373 ddp_cardrule_qual_rec.high_value_number := rosetta_g_miss_num_map(p7_a10);
374 ddp_cardrule_qual_rec.low_value_number := rosetta_g_miss_num_map(p7_a11);
375 ddp_cardrule_qual_rec.high_value_char := p7_a12;
376 ddp_cardrule_qual_rec.low_value_char := p7_a13;
377 ddp_cardrule_qual_rec.currency_code := p7_a14;
378 ddp_cardrule_qual_rec.low_value_date := rosetta_g_miss_date_in_map(p7_a15);
379 ddp_cardrule_qual_rec.high_value_date := rosetta_g_miss_date_in_map(p7_a16);
380 ddp_cardrule_qual_rec.start_date_active := rosetta_g_miss_date_in_map(p7_a17);
381 ddp_cardrule_qual_rec.end_date_active := rosetta_g_miss_date_in_map(p7_a18);
382
383
384 -- here's the delegated call to the old PL/SQL routine
385 as_scorecard_rules_pub.create_cardrule_qual(p_api_version,
386 p_init_msg_list,
387 p_commit,
388 p_validation_level,
389 x_return_status,
390 x_msg_count,
391 x_msg_data,
392 ddp_cardrule_qual_rec,
393 x_qual_value_id);
394
395 -- copy data back from the local variables to OUT or IN-OUT args, if any
396
397
398
399
400
401
402
403
404 end;
405
406 procedure update_cardrule_qual(p_api_version NUMBER
407 , p_init_msg_list VARCHAR2
408 , p_commit VARCHAR2
409 , p_validation_level NUMBER
410 , x_return_status out nocopy VARCHAR2
411 , x_msg_count out nocopy NUMBER
412 , x_msg_data out nocopy VARCHAR2
413 , p7_a0 NUMBER := 0-1962.0724
414 , p7_a1 DATE := fnd_api.g_miss_date
415 , p7_a2 NUMBER := 0-1962.0724
416 , p7_a3 DATE := fnd_api.g_miss_date
417 , p7_a4 NUMBER := 0-1962.0724
418 , p7_a5 NUMBER := 0-1962.0724
419 , p7_a6 NUMBER := 0-1962.0724
420 , p7_a7 NUMBER := 0-1962.0724
421 , p7_a8 NUMBER := 0-1962.0724
422 , p7_a9 NUMBER := 0-1962.0724
423 , p7_a10 NUMBER := 0-1962.0724
424 , p7_a11 NUMBER := 0-1962.0724
425 , p7_a12 VARCHAR2 := fnd_api.g_miss_char
426 , p7_a13 VARCHAR2 := fnd_api.g_miss_char
427 , p7_a14 VARCHAR2 := fnd_api.g_miss_char
428 , p7_a15 DATE := fnd_api.g_miss_date
429 , p7_a16 DATE := fnd_api.g_miss_date
430 , p7_a17 DATE := fnd_api.g_miss_date
431 , p7_a18 DATE := fnd_api.g_miss_date
432 )
433
434 as
435 ddp_cardrule_qual_rec as_scorecard_rules_pub.cardrule_qual_rec_type;
436 ddindx binary_integer; indx binary_integer;
437 begin
438
439 -- copy data to the local IN or IN-OUT args, if any
440
441
442
443
444
445
446
447 ddp_cardrule_qual_rec.qual_value_id := rosetta_g_miss_num_map(p7_a0);
448 ddp_cardrule_qual_rec.last_update_date := rosetta_g_miss_date_in_map(p7_a1);
449 ddp_cardrule_qual_rec.last_updated_by := rosetta_g_miss_num_map(p7_a2);
450 ddp_cardrule_qual_rec.creation_date := rosetta_g_miss_date_in_map(p7_a3);
451 ddp_cardrule_qual_rec.created_by := rosetta_g_miss_num_map(p7_a4);
452 ddp_cardrule_qual_rec.last_update_login := rosetta_g_miss_num_map(p7_a5);
453 ddp_cardrule_qual_rec.scorecard_id := rosetta_g_miss_num_map(p7_a6);
454 ddp_cardrule_qual_rec.score := rosetta_g_miss_num_map(p7_a7);
455 ddp_cardrule_qual_rec.card_rule_id := rosetta_g_miss_num_map(p7_a8);
456 ddp_cardrule_qual_rec.seed_qual_id := rosetta_g_miss_num_map(p7_a9);
457 ddp_cardrule_qual_rec.high_value_number := rosetta_g_miss_num_map(p7_a10);
458 ddp_cardrule_qual_rec.low_value_number := rosetta_g_miss_num_map(p7_a11);
459 ddp_cardrule_qual_rec.high_value_char := p7_a12;
460 ddp_cardrule_qual_rec.low_value_char := p7_a13;
461 ddp_cardrule_qual_rec.currency_code := p7_a14;
462 ddp_cardrule_qual_rec.low_value_date := rosetta_g_miss_date_in_map(p7_a15);
463 ddp_cardrule_qual_rec.high_value_date := rosetta_g_miss_date_in_map(p7_a16);
464 ddp_cardrule_qual_rec.start_date_active := rosetta_g_miss_date_in_map(p7_a17);
465 ddp_cardrule_qual_rec.end_date_active := rosetta_g_miss_date_in_map(p7_a18);
466
467 -- here's the delegated call to the old PL/SQL routine
468 as_scorecard_rules_pub.update_cardrule_qual(p_api_version,
469 p_init_msg_list,
470 p_commit,
471 p_validation_level,
472 x_return_status,
473 x_msg_count,
474 x_msg_data,
475 ddp_cardrule_qual_rec);
476
477 -- copy data back from the local variables to OUT or IN-OUT args, if any
478
479
480
481
482
483
484
485 end;
486
487 end as_scorecard_rules_pub_w;