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