[Home] [Help]
PACKAGE BODY: APPS.OKL_PDT_STYS_PUB
Source
1 PACKAGE BODY OKL_PDT_STYS_PUB AS
2 /* $Header: OKLPPSYB.pls 115.5 2004/04/13 10:58:50 rnaik noship $ */
3 PROCEDURE add_language IS
4 BEGIN
5 -- okl_psy_pvt.add_language;
6 NULL;
7 END add_language;
8
9
10 PROCEDURE insert_pdt_stys(
11 p_api_version IN NUMBER
12 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
13 ,x_return_status OUT NOCOPY VARCHAR2
14 ,x_msg_count OUT NOCOPY NUMBER
15 ,x_msg_data OUT NOCOPY VARCHAR2
16 ,p_psyv_rec IN psyv_rec_type
17 ,x_psyv_rec OUT NOCOPY psyv_rec_type
18 ) IS
19 l_psyv_rec psyv_rec_type;
20 l_data VARCHAR2(100);
21 l_api_name CONSTANT VARCHAR2(30) := 'insert_pdt_stys';
22 l_count NUMBER ;
23 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
24 BEGIN
25 x_return_status := FND_API.G_RET_STS_SUCCESS;
26 SAVEPOINT insert_pdt_stys;
27 l_psyv_rec := p_psyv_rec;
28
29
30
31 okl_psy_pvt.insert_row(
32 p_api_version => p_api_version
33 ,p_init_msg_list => p_init_msg_list
34 ,x_return_status => l_return_status
35 ,x_msg_count => x_msg_count
36 ,x_msg_data => x_msg_data
37 ,p_psyv_rec => l_psyv_rec
38 ,x_psyv_rec => x_psyv_rec
39 );
40
41 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
42 RAISE FND_API.G_EXC_ERROR;
43 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
45 END IF;
46
47 /* re-assign local record structure using output record from pvt api */
48 l_psyv_rec := x_psyv_rec;
49
50
51
52
53 EXCEPTION
54 WHEN FND_API.G_EXC_ERROR THEN
55 ROLLBACK TO insert_pdt_stys;
56 x_return_status := FND_API.G_RET_STS_ERROR;
57
58 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
59 ,p_count => x_msg_count
60 ,p_data => x_msg_data);
61
62 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
63 ROLLBACK TO insert_pdt_stys;
64 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
65 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
66 ,p_count => x_msg_count
67 ,p_data => x_msg_data);
68
69 WHEN OTHERS THEN
70 FND_MSG_PUB.ADD_EXC_MSG('OKL_pdt_stys_PUB','insert_pdt_stys');
71 -- store SQL error message on message stack for caller
72 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
73 ,p_count => x_msg_count
74 ,p_data => x_msg_data);
75 -- notify caller of an UNEXPECTED error
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77 END insert_pdt_stys;
78
79
80 PROCEDURE insert_pdt_stys(
81 p_api_version IN NUMBER
82 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
83 ,x_return_status OUT NOCOPY VARCHAR2
84 ,x_msg_count OUT NOCOPY NUMBER
85 ,x_msg_data OUT NOCOPY VARCHAR2
86 ,p_psyv_tbl IN psyv_tbl_type
87 ,x_psyv_tbl OUT NOCOPY psyv_tbl_type
88 ) IS
89 l_psyv_tbl psyv_tbl_type;
90 l_data VARCHAR2(100);
91 l_count NUMBER ;
92 l_api_name CONSTANT VARCHAR2(30) := 'insert_pdt_stys';
93 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
94 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
95 i NUMBER;
96
97 BEGIN
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99 SAVEPOINT insert_pdt_stys;
100 l_psyv_tbl := p_psyv_tbl;
101
102
103
104 IF (p_psyv_tbl.COUNT > 0) THEN
105 i := p_psyv_tbl.FIRST;
106
107 LOOP
108 insert_pdt_stys (
109 p_api_version => p_api_version
110 ,p_init_msg_list => p_init_msg_list
111 ,x_return_status => x_return_status
112 ,x_msg_count => x_msg_count
113 ,x_msg_data => x_msg_data
114 ,p_psyv_rec => p_psyv_tbl(i)
115 ,x_psyv_rec => x_psyv_tbl(i)
116 );
117
118 -- store the highest degree of error
119 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
120 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
121 l_overall_status := x_return_status;
122 END IF;
123 END IF;
124 EXIT WHEN (i = p_psyv_tbl.LAST);
125
126 i := p_psyv_tbl.NEXT(i);
127
128 END LOOP;
129
130 -- return overall status
131 x_return_status := l_overall_status;
132 END IF;
133
134 l_return_status := x_return_status;
135
136 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
137 RAISE FND_API.G_EXC_ERROR;
138 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140 END IF;
141
142 /* re-assign local table structure using output table from pvt api */
143 l_psyv_tbl := x_psyv_tbl;
144
145
146
147 EXCEPTION
148 WHEN FND_API.G_EXC_ERROR THEN
149 ROLLBACK TO insert_pdt_stys;
150 x_return_status := FND_API.G_RET_STS_ERROR;
151
152 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
153 ,p_count => x_msg_count
154 ,p_data => x_msg_data);
155
156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
157 ROLLBACK TO insert_pdt_stys;
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
160 ,p_count => x_msg_count
161 ,p_data => x_msg_data);
162
163 WHEN OTHERS THEN
164 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','insert_pdt_stys');
165 -- store SQL error message on message stack for caller
166 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
167 ,p_count => x_msg_count
168 ,p_data => x_msg_data);
169 -- notify caller of an UNEXPECTED error
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 END insert_pdt_stys;
172
173 PROCEDURE lock_pdt_stys(
174 p_api_version IN NUMBER
175 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
176 ,x_return_status OUT NOCOPY VARCHAR2
177 ,x_msg_count OUT NOCOPY NUMBER
178 ,x_msg_data OUT NOCOPY VARCHAR2
179 ,p_psyv_rec IN psyv_rec_type
180 ) IS
181
182 l_psyv_rec psyv_rec_type;
183 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
184
185 BEGIN
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187 SAVEPOINT lock_pdt_stys;
188 l_psyv_rec := p_psyv_rec;
189
190 okl_psy_pvt.lock_row(
191 p_api_version => p_api_version
192 ,p_init_msg_list => p_init_msg_list
193 ,x_return_status => l_return_status
194 ,x_msg_count => x_msg_count
195 ,x_msg_data => x_msg_data
196 ,p_psyv_rec => l_psyv_rec
197 );
198
199 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
200 RAISE FND_API.G_EXC_ERROR;
201 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
203 END IF;
204
205 EXCEPTION
206 WHEN FND_API.G_EXC_ERROR THEN
207 ROLLBACK TO lock_pdt_stys;
208 x_return_status := FND_API.G_RET_STS_ERROR;
209
210 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
211 ,p_count => x_msg_count
212 ,p_data => x_msg_data);
213
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 ROLLBACK TO lock_pdt_stys;
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
218 ,p_count => x_msg_count
219 ,p_data => x_msg_data);
220
221 WHEN OTHERS THEN
222 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','lock_pdt_stys');
223 -- store SQL error message on message stack for caller
224 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
225 ,p_count => x_msg_count
226 ,p_data => x_msg_data);
227 -- notify caller of an UNEXPECTED error
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 END lock_pdt_stys;
230
231 PROCEDURE lock_pdt_stys(
232 p_api_version IN NUMBER
233 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
234 ,x_return_status OUT NOCOPY VARCHAR2
235 ,x_msg_count OUT NOCOPY NUMBER
236 ,x_msg_data OUT NOCOPY VARCHAR2
237 ,p_psyv_tbl IN psyv_tbl_type
238 ) IS
239
240 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
241 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
242 i NUMBER;
243
244 BEGIN
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246 SAVEPOINT lock_pdt_stys;
247
248 IF (p_psyv_tbl.COUNT > 0) THEN
249 i := p_psyv_tbl.FIRST;
250
251 LOOP
252 lock_pdt_stys (
253 p_api_version => p_api_version
254 ,p_init_msg_list => p_init_msg_list
255 ,x_return_status => x_return_status
256 ,x_msg_count => x_msg_count
257 ,x_msg_data => x_msg_data
258 ,p_psyv_rec => p_psyv_tbl(i)
259 );
260
261 -- store the highest degree of error
262 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
263 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
264 l_overall_status := x_return_status;
265 END IF;
266 END IF;
267 EXIT WHEN (i = p_psyv_tbl.LAST);
268
269 i := p_psyv_tbl.NEXT(i);
270
271 END LOOP;
272
273 -- return overall status
274 x_return_status := l_overall_status;
275 END IF;
276
277 l_return_status := x_return_status;
278
279 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
280 RAISE FND_API.G_EXC_ERROR;
281 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283 END IF;
284
285 EXCEPTION
286 WHEN FND_API.G_EXC_ERROR THEN
287 ROLLBACK TO lock_pdt_stys;
288 x_return_status := FND_API.G_RET_STS_ERROR;
289
290 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
291 ,p_count => x_msg_count
292 ,p_data => x_msg_data);
293
294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
295 ROLLBACK TO lock_pdt_stys;
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
298 ,p_count => x_msg_count
299 ,p_data => x_msg_data);
300
301 WHEN OTHERS THEN
302 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','lock_pdt_stys');
303 -- store SQL error message on message stack for caller
304 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
305 ,p_count => x_msg_count
306 ,p_data => x_msg_data);
307 -- notify caller of an UNEXPECTED error
308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309 END lock_pdt_stys;
310
311 PROCEDURE update_pdt_stys(
312 p_api_version IN NUMBER
313 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
314 ,x_return_status OUT NOCOPY VARCHAR2
315 ,x_msg_count OUT NOCOPY NUMBER
316 ,x_msg_data OUT NOCOPY VARCHAR2
317 ,p_psyv_rec IN psyv_rec_type
318 ,x_psyv_rec OUT NOCOPY psyv_rec_type
319 ) IS
320 l_psyv_rec psyv_rec_type;
321 l_data VARCHAR2(100);
322 l_count NUMBER ;
323 l_api_name CONSTANT VARCHAR2(30) := 'update_pdt_stys';
324 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
325 BEGIN
326 x_return_status := FND_API.G_RET_STS_SUCCESS;
327 SAVEPOINT update_pdt_stys;
328 l_psyv_rec := p_psyv_rec;
329
330
331
332 okl_psy_pvt.update_row(
333 p_api_version => p_api_version
334 ,p_init_msg_list => p_init_msg_list
335 ,x_return_status => l_return_status
336 ,x_msg_count => x_msg_count
337 ,x_msg_data => x_msg_data
338 ,p_psyv_rec => l_psyv_rec
339 ,x_psyv_rec => x_psyv_rec
340 );
341
342 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
343 RAISE FND_API.G_EXC_ERROR;
344 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346 END IF;
347
348 /* re-assign local record structure using output record from pvt api */
349 l_psyv_rec := x_psyv_rec;
350
351
352
353 EXCEPTION
354 WHEN FND_API.G_EXC_ERROR THEN
355 ROLLBACK TO update_pdt_stys;
356 x_return_status := FND_API.G_RET_STS_ERROR;
357
358 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
359 ,p_count => x_msg_count
360 ,p_data => x_msg_data);
361
362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
363 ROLLBACK TO update_pdt_stys;
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
366 ,p_count => x_msg_count
367 ,p_data => x_msg_data);
368
369 WHEN OTHERS THEN
370 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','update_pdt_stys');
371 -- store SQL error message on message stack for caller
372 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
373 ,p_count => x_msg_count
374 ,p_data => x_msg_data);
375 -- notify caller of an UNEXPECTED error
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 END update_pdt_stys;
378
379
380 PROCEDURE update_pdt_stys(
381 p_api_version IN NUMBER
382 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
383 ,x_return_status OUT NOCOPY VARCHAR2
384 ,x_msg_count OUT NOCOPY NUMBER
385 ,x_msg_data OUT NOCOPY VARCHAR2
386 ,p_psyv_tbl IN psyv_tbl_type
387 ,x_psyv_tbl OUT NOCOPY psyv_tbl_type
388 ) IS
389 l_psyv_tbl psyv_tbl_type;
390 l_data VARCHAR2(100);
391 l_count NUMBER;
392 l_api_name CONSTANT VARCHAR2(30) := 'update_pdt_stys';
393 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
394 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
395 i NUMBER;
396
397 BEGIN
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399 SAVEPOINT update_pdt_stys;
400 l_psyv_tbl := p_psyv_tbl;
401
402
403
404 IF (p_psyv_tbl.COUNT > 0) THEN
405 i := p_psyv_tbl.FIRST;
406
407 LOOP
408 update_pdt_stys (
409 p_api_version => p_api_version
410 ,p_init_msg_list => p_init_msg_list
411 ,x_return_status => x_return_status
412 ,x_msg_count => x_msg_count
413 ,x_msg_data => x_msg_data
414 ,p_psyv_rec => p_psyv_tbl(i)
415 ,x_psyv_rec => x_psyv_tbl(i)
416 );
417
418 -- store the highest degree of error
419 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
420 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
421 l_overall_status := x_return_status;
422 END IF;
423 END IF;
424 EXIT WHEN (i = p_psyv_tbl.LAST);
425
426 i := p_psyv_tbl.NEXT(i);
427
428 END LOOP;
429
430 -- return overall status
431 x_return_status := l_overall_status;
432 END IF;
433
434 l_return_status := x_return_status;
435
436 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
437 RAISE FND_API.G_EXC_ERROR;
438 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
440 END IF;
441
442 /* re-assign local table structure using output table from pvt api */
443 l_psyv_tbl := x_psyv_tbl;
444
445
446
447 EXCEPTION
448 WHEN FND_API.G_EXC_ERROR THEN
449 ROLLBACK TO update_pdt_stys;
450 x_return_status := FND_API.G_RET_STS_ERROR;
451
452 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
453 ,p_count => x_msg_count
454 ,p_data => x_msg_data);
455
456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
457 ROLLBACK TO update_pdt_stys;
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
460 ,p_count => x_msg_count
461 ,p_data => x_msg_data);
462
463 WHEN OTHERS THEN
464 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','update_pdt_stys');
465 -- store SQL error message on message stack for caller
466 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
467 ,p_count => x_msg_count
468 ,p_data => x_msg_data);
469 -- notify caller of an UNEXPECTED error
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
471 END update_pdt_stys;
472
473 PROCEDURE delete_pdt_stys(
474 p_api_version IN NUMBER
475 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
476 ,x_return_status OUT NOCOPY VARCHAR2
477 ,x_msg_count OUT NOCOPY NUMBER
478 ,x_msg_data OUT NOCOPY VARCHAR2
479 ,p_psyv_rec IN psyv_rec_type
480 ) IS
481 l_psyv_rec psyv_rec_type;
482 l_data VARCHAR2(100);
483 l_count NUMBER ;
484 l_api_name CONSTANT VARCHAR2(30) := 'delete_pdt_stys';
485 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
486 BEGIN
487 x_return_status := FND_API.G_RET_STS_SUCCESS;
488 SAVEPOINT delete_pdt_stys;
489 l_psyv_rec := p_psyv_rec;
490
491
492
493 okl_psy_pvt.delete_row(
494 p_api_version => p_api_version
495 ,p_init_msg_list => p_init_msg_list
496 ,x_return_status => l_return_status
497 ,x_msg_count => x_msg_count
498 ,x_msg_data => x_msg_data
499 ,p_psyv_rec => l_psyv_rec
500 );
501
502 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
503 RAISE FND_API.G_EXC_ERROR;
504 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506 END IF;
507
508
509
510 EXCEPTION
511 WHEN FND_API.G_EXC_ERROR THEN
512 ROLLBACK TO delete_pdt_stys;
513 x_return_status := FND_API.G_RET_STS_ERROR;
514
515 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
516 ,p_count => x_msg_count
517 ,p_data => x_msg_data);
518
519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
520 ROLLBACK TO delete_pdt_stys;
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
523 ,p_count => x_msg_count
524 ,p_data => x_msg_data);
525
526 WHEN OTHERS THEN
527 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','delete_pdt_stys');
528 -- store SQL error message on message stack for caller
529 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
530 ,p_count => x_msg_count
531 ,p_data => x_msg_data);
532 -- notify caller of an UNEXPECTED error
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
534 END delete_pdt_stys;
535
536
537 PROCEDURE delete_pdt_stys(
538 p_api_version IN NUMBER
539 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
540 ,x_return_status OUT NOCOPY VARCHAR2
541 ,x_msg_count OUT NOCOPY NUMBER
542 ,x_msg_data OUT NOCOPY VARCHAR2
543 ,p_psyv_tbl IN psyv_tbl_type
544 ) IS
545 l_psyv_tbl psyv_tbl_type;
546 l_data VARCHAR2(100);
547 l_count NUMBER ;
548 l_api_name CONSTANT VARCHAR2(30) := 'delete_pdt_stys';
549 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
550 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
551 i NUMBER;
552
553 BEGIN
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555 SAVEPOINT delete_pdt_stys;
556 l_psyv_tbl := p_psyv_tbl;
557
558
559
560 IF (p_psyv_tbl.COUNT > 0) THEN
561 i := p_psyv_tbl.FIRST;
562
563 LOOP
564 delete_pdt_stys (
565 p_api_version => p_api_version
566 ,p_init_msg_list => p_init_msg_list
567 ,x_return_status => x_return_status
568 ,x_msg_count => x_msg_count
569 ,x_msg_data => x_msg_data
570 ,p_psyv_rec => p_psyv_tbl(i)
571 );
572
573 -- store the highest degree of error
574 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
575 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
576 l_overall_status := x_return_status;
577 END IF;
578 END IF;
579 EXIT WHEN (i = p_psyv_tbl.LAST);
580
581 i := p_psyv_tbl.NEXT(i);
582
583 END LOOP;
584
585 -- return overall status
586 x_return_status := l_overall_status;
587 END IF;
588
589 l_return_status := x_return_status;
590
591 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
592 RAISE FND_API.G_EXC_ERROR;
593 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
595 END IF;
596
597
598
599 EXCEPTION
600 WHEN FND_API.G_EXC_ERROR THEN
601 ROLLBACK TO delete_pdt_stys;
602 x_return_status := FND_API.G_RET_STS_ERROR;
603
604 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
605 ,p_count => x_msg_count
606 ,p_data => x_msg_data);
607
608 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
609 ROLLBACK TO delete_pdt_stys;
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
612 ,p_count => x_msg_count
613 ,p_data => x_msg_data);
614
615 WHEN OTHERS THEN
616 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','delete_pdt_stys');
617 -- store SQL error message on message stack for caller
618 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
619 ,p_count => x_msg_count
620 ,p_data => x_msg_data);
621 -- notify caller of an UNEXPECTED error
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623 END delete_pdt_stys;
624
625 PROCEDURE validate_pdt_stys(
626 p_api_version IN NUMBER
627 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
628 ,x_return_status OUT NOCOPY VARCHAR2
629 ,x_msg_count OUT NOCOPY NUMBER
630 ,x_msg_data OUT NOCOPY VARCHAR2
631 ,p_psyv_rec IN psyv_rec_type
632 ) IS
633 l_psyv_rec psyv_rec_type;
634 l_data VARCHAR2(100);
635 l_count NUMBER;
636 l_api_name CONSTANT VARCHAR2(30) := 'validate_pdt_stys';
637 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
638 BEGIN
639 x_return_status := FND_API.G_RET_STS_SUCCESS;
640 SAVEPOINT validate_pdt_stys;
641 l_psyv_rec := p_psyv_rec;
642
643
644
645 okl_psy_pvt.validate_row(
646 p_api_version => p_api_version
647 ,p_init_msg_list => p_init_msg_list
648 ,x_return_status => l_return_status
649 ,x_msg_count => x_msg_count
650 ,x_msg_data => x_msg_data
651 ,p_psyv_rec => l_psyv_rec
652 );
653
654 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
655 RAISE FND_API.G_EXC_ERROR;
656 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
658 END IF;
659
660
661
662 EXCEPTION
663 WHEN FND_API.G_EXC_ERROR THEN
664 ROLLBACK TO validate_pdt_stys;
665 x_return_status := FND_API.G_RET_STS_ERROR;
666
667 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
668 ,p_count => x_msg_count
669 ,p_data => x_msg_data);
670
671 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
672 ROLLBACK TO validate_pdt_stys;
673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
674 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
675 ,p_count => x_msg_count
676 ,p_data => x_msg_data);
677
678 WHEN OTHERS THEN
679 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','validate_pdt_stys');
680 -- store SQL error message on message stack for caller
681 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
682 ,p_count => x_msg_count
683 ,p_data => x_msg_data);
684 -- notify caller of an UNEXPECTED error
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
686 END validate_pdt_stys;
687
688
689 PROCEDURE validate_pdt_stys(
690 p_api_version IN NUMBER
691 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
692 ,x_return_status OUT NOCOPY VARCHAR2
693 ,x_msg_count OUT NOCOPY NUMBER
694 ,x_msg_data OUT NOCOPY VARCHAR2
695 ,p_psyv_tbl IN psyv_tbl_type
696 ) IS
697 l_psyv_tbl psyv_tbl_type;
698 l_data VARCHAR2(100);
699 l_count NUMBER;
700 l_api_name CONSTANT VARCHAR2(30) := 'validate_pdt_stys';
701 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
702 l_overall_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
703 i NUMBER;
704
705 BEGIN
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707 SAVEPOINT validate_pdt_stys;
708 l_psyv_tbl := p_psyv_tbl;
709
710
711
712 IF (p_psyv_tbl.COUNT > 0) THEN
713 i := p_psyv_tbl.FIRST;
714
715 LOOP
716 validate_pdt_stys (
717 p_api_version => p_api_version
718 ,p_init_msg_list => p_init_msg_list
719 ,x_return_status => x_return_status
720 ,x_msg_count => x_msg_count
721 ,x_msg_data => x_msg_data
722 ,p_psyv_rec => p_psyv_tbl(i)
723 );
724
725 -- store the highest degree of error
726 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
727 IF l_overall_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
728 l_overall_status := x_return_status;
729 END IF;
730 END IF;
731 EXIT WHEN (i = p_psyv_tbl.LAST);
732
733 i := p_psyv_tbl.NEXT(i);
734
735 END LOOP;
736
737 -- return overall status
738 x_return_status := l_overall_status;
739 END IF;
740
741 l_return_status := x_return_status;
742
743 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
744 RAISE FND_API.G_EXC_ERROR;
745 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
747 END IF;
748
749
750
751
752 EXCEPTION
753 WHEN FND_API.G_EXC_ERROR THEN
754 ROLLBACK TO validate_pdt_stys;
755 x_return_status := FND_API.G_RET_STS_ERROR;
756
757 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
758 ,p_count => x_msg_count
759 ,p_data => x_msg_data);
760
761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
762 ROLLBACK TO validate_pdt_stys;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
765 ,p_count => x_msg_count
766 ,p_data => x_msg_data);
767
768 WHEN OTHERS THEN
769 FND_MSG_PUB.ADD_EXC_MSG('OKL_PDT_STYS_PUB','validate_pdt_stys');
770 -- store SQL error message on message stack for caller
771 FND_MSG_PUB.Count_and_get(p_encoded => OKC_API.G_FALSE
772 ,p_count => x_msg_count
773 ,p_data => x_msg_data);
774 -- notify caller of an UNEXPECTED error
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776 END validate_pdt_stys;
777
778
779 END OKL_PDT_STYS_PUB;