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