[Home] [Help]
PACKAGE BODY: APPS.OKL_OPTIONS_PVT
Source
1 PACKAGE BODY OKL_OPTIONS_PVT AS
2 /* $Header: OKLCOPTB.pls 115.4 2002/02/18 20:13:00 pkm ship $ */
3
4
5 PROCEDURE create_options(
6 p_api_version IN NUMBER
7 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
8 ,x_return_status OUT NOCOPY VARCHAR2
9 ,x_msg_count OUT NOCOPY NUMBER
10 ,x_msg_data OUT NOCOPY VARCHAR2
11 ,p_optv_rec IN optv_rec_type
12 ,p_ovev_tbl IN ovev_tbl_type
13 ,x_optv_rec OUT NOCOPY optv_rec_type
14 ,x_ovev_tbl OUT NOCOPY ovev_tbl_type
15 ) IS
16
17 i NUMBER;
18 l_optv_rec optv_rec_type;
19 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
20 l_ovev_tbl ovev_tbl_type := p_ovev_tbl;
21
22 BEGIN
23 -- Populate TRX CONTRACTS
24 create_options(
25 p_api_version => p_api_version
26 ,p_init_msg_list => p_init_msg_list
27 ,x_return_status => x_return_status
28 ,x_msg_count => x_msg_count
29 ,x_msg_data => x_msg_data
30 ,p_optv_rec => p_optv_rec
31 ,x_optv_rec => x_optv_rec);
32 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
33 RAISE G_EXCEPTION_HALT_VALIDATION;
34 ELSE
35 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
36 l_return_status := x_return_status;
37 END IF;
38 END IF;
39 -- populate the foreign key for the detail
40 IF (l_ovev_tbl.COUNT > 0) THEN
41 i:= l_ovev_tbl.FIRST;
42 LOOP
43 l_ovev_tbl(i).opt_id := x_optv_rec.id;
44 EXIT WHEN(i = l_ovev_tbl.LAST);
45 i := l_ovev_tbl.NEXT(i);
46 END LOOP;
47 END IF;
48
49
50 -- populate the detail
51 create_option_values(
52 p_api_version => p_api_version
53 ,p_init_msg_list => p_init_msg_list
54 ,x_return_status => x_return_status
55 ,x_msg_count => x_msg_count
56 ,x_msg_data => x_msg_data
57 ,p_ovev_tbl => l_ovev_tbl
58 ,x_ovev_tbl => x_ovev_tbl);
59 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
60 RAISE G_EXCEPTION_HALT_VALIDATION;
61 ELSE
62 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
63 l_return_status := x_return_status;
64 END IF;
65 END IF;
66
67 EXCEPTION
68 WHEN G_EXCEPTION_HALT_VALIDATION THEN
69 NULL;
70
71 WHEN OTHERS THEN
72 OKC_API.SET_MESSAGE(p_app_name => g_app_name
73 ,p_msg_name => g_unexpected_error
74 ,p_token1 => g_sqlcode_token
75 ,p_token1_value => sqlcode
76 ,p_token2 => g_sqlerrm_token
77 ,p_token2_value => sqlerrm);
78 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
79 END create_options;
80
81 PROCEDURE create_options(
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_optv_rec IN optv_rec_type
88 ,x_optv_rec OUT NOCOPY optv_rec_type) IS
89 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
90 BEGIN
91 okl_opt_pvt.insert_row(
92 p_api_version => p_api_version
93 ,p_init_msg_list => p_init_msg_list
94 ,x_return_status => x_return_status
95 ,x_msg_count => x_msg_count
96 ,x_msg_data => x_msg_data
97 ,p_optv_rec => p_optv_rec
98 ,x_optv_rec => x_optv_rec
99 );
100
101 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
102 RAISE G_EXCEPTION_HALT_VALIDATION;
103 ELSE
104 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
105 l_return_status := x_return_status;
106 END IF;
107 END IF;
108 -- Custom code if any
109
110 EXCEPTION
111 WHEN G_EXCEPTION_HALT_VALIDATION THEN
112 NULL;
113
114 WHEN OTHERS THEN
115 OKC_API.SET_MESSAGE(p_app_name => g_app_name
116 ,p_msg_name => g_unexpected_error
117 ,p_token1 => g_sqlcode_token
118 ,p_token1_value => sqlcode
119 ,p_token2 => g_sqlerrm_token
120 ,p_token2_value => sqlerrm);
121 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
122 END create_options;
123 --null;
124 PROCEDURE create_options(
125 p_api_version IN NUMBER
126 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
127 ,x_return_status OUT NOCOPY VARCHAR2
128 ,x_msg_count OUT NOCOPY NUMBER
129 ,x_msg_data OUT NOCOPY VARCHAR2
130 ,p_optv_tbl IN optv_tbl_type
131 ,x_optv_tbl OUT NOCOPY optv_tbl_type) IS
132 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
133
134 BEGIN
135 okl_opt_pvt.insert_row(
136 p_api_version => p_api_version
137 ,p_init_msg_list => p_init_msg_list
138 ,x_return_status => x_return_status
139 ,x_msg_count => x_msg_count
140 ,x_msg_data => x_msg_data
141 ,p_optv_tbl => p_optv_tbl
142 ,x_optv_tbl => x_optv_tbl
143 );
144 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
145 RAISE G_EXCEPTION_HALT_VALIDATION;
146 ELSE
147 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
148 l_return_status := x_return_status;
149 END IF;
150 END IF;
151 -- Custom code if any
152
153 EXCEPTION
154 WHEN G_EXCEPTION_HALT_VALIDATION THEN
155 NULL;
156
157 WHEN OTHERS THEN
158 OKC_API.SET_MESSAGE(p_app_name => g_app_name
159 ,p_msg_name => g_unexpected_error
160 ,p_token1 => g_sqlcode_token
161 ,p_token1_value => sqlcode
162 ,p_token2 => g_sqlerrm_token
163 ,p_token2_value => sqlerrm);
164 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
165
166 END create_options;
167
168 -- Object type procedure for update
169 PROCEDURE update_options(
170 p_api_version IN NUMBER,
171 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2,
175 p_optv_rec IN optv_rec_type,
176 p_ovev_tbl IN ovev_tbl_type,
177 x_optv_rec OUT NOCOPY optv_rec_type,
178 x_ovev_tbl OUT NOCOPY ovev_tbl_type) IS
179 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
180 BEGIN
181 -- Update the master
182 update_options(
183 p_api_version => p_api_version
184 ,p_init_msg_list => p_init_msg_list
185 ,x_return_status => x_return_status
186 ,x_msg_count => x_msg_count
187 ,x_msg_data => x_msg_data
188 ,p_optv_rec => p_optv_rec
189 ,x_optv_rec => x_optv_rec
190 );
191 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
192 RAISE G_EXCEPTION_HALT_VALIDATION;
193 ELSE
194 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
195 l_return_status := x_return_status;
196 END IF;
197 END IF;
198
199 -- Update the detail
200 update_option_values(
201 p_api_version => p_api_version
202 ,p_init_msg_list => p_init_msg_list
203 ,x_return_status => x_return_status
204 ,x_msg_count => x_msg_count
205 ,x_msg_data => x_msg_data
206 ,p_ovev_tbl => p_ovev_tbl
207 ,x_ovev_tbl => x_ovev_tbl
208 );
209
210 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
211 RAISE G_EXCEPTION_HALT_VALIDATION;
212 ELSE
213 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
214 l_return_status := x_return_status;
215 END IF;
216 END IF;
217
218 EXCEPTION
219 WHEN G_EXCEPTION_HALT_VALIDATION THEN
220 NULL;
221
222 WHEN OTHERS THEN
223 OKC_API.SET_MESSAGE(p_app_name => g_app_name
224 ,p_msg_name => g_unexpected_error
225 ,p_token1 => g_sqlcode_token
226 ,p_token1_value => sqlcode
227 ,p_token2 => g_sqlerrm_token
228 ,p_token2_value => sqlerrm);
229 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
230
231 END update_options;
232
233 PROCEDURE validate_options(
234 p_api_version IN NUMBER
235 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
236 ,x_return_status OUT NOCOPY VARCHAR2
237 ,x_msg_count OUT NOCOPY NUMBER
238 ,x_msg_data OUT NOCOPY VARCHAR2
239 ,p_optv_rec IN optv_rec_type
240 ,p_ovev_tbl IN ovev_tbl_type) IS
241
242 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
243 BEGIN
244 -- Validate the master
245 validate_options(
246 p_api_version => p_api_version
247 ,p_init_msg_list => p_init_msg_list
248 ,x_return_status => x_return_status
249 ,x_msg_count => x_msg_count
250 ,x_msg_data => x_msg_data
251 ,p_optv_rec => p_optv_rec
252 );
253
254 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
255 RAISE G_EXCEPTION_HALT_VALIDATION;
256 ELSE
257 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
258 l_return_status := x_return_status;
259 END IF;
260 END IF;
261
262 -- Validate the detail
263 validate_option_values(
264 p_api_version => p_api_version
265 ,p_init_msg_list => p_init_msg_list
266 ,x_return_status => x_return_status
267 ,x_msg_count => x_msg_count
268 ,x_msg_data => x_msg_data
269 ,p_ovev_tbl => p_ovev_tbl
270 );
271
272 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
273 RAISE G_EXCEPTION_HALT_VALIDATION;
274 ELSE
275 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
276 l_return_status := x_return_status;
277 END IF;
278 END IF;
279
280 EXCEPTION
281 WHEN G_EXCEPTION_HALT_VALIDATION THEN
282 NULL;
283 WHEN OTHERS THEN
284 OKC_API.SET_MESSAGE(p_app_name => g_app_name
285 ,p_msg_name => g_unexpected_error
286 ,p_token1 => g_sqlcode_token
287 ,p_token1_value => sqlcode
288 ,p_token2 => g_sqlerrm_token
289 ,p_token2_value => sqlerrm);
290 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
291
292 END validate_options;
293
294 PROCEDURE lock_options(
295 p_api_version IN NUMBER
296 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
297 ,x_return_status OUT NOCOPY VARCHAR2
298 ,x_msg_count OUT NOCOPY NUMBER
299 ,x_msg_data OUT NOCOPY VARCHAR2
300 ,p_optv_rec IN optv_rec_type) IS
301 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
302
303 BEGIN
304 okl_opt_pvt.lock_row(
305 p_api_version => p_api_version
306 ,p_init_msg_list => p_init_msg_list
307 ,x_return_status => x_return_status
308 ,x_msg_count => x_msg_count
309 ,x_msg_data => x_msg_data
310 ,p_optv_rec => p_optv_rec
311 );
312 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
313 RAISE G_EXCEPTION_HALT_VALIDATION;
314 ELSE
315 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
316 l_return_status := x_return_status;
317 END IF;
318 END IF;
319
320 EXCEPTION
321 WHEN G_EXCEPTION_HALT_VALIDATION THEN
322 NULL;
323 WHEN OTHERS THEN
324 OKC_API.SET_MESSAGE(p_app_name => g_app_name
325 ,p_msg_name => g_unexpected_error
326 ,p_token1 => g_sqlcode_token
327 ,p_token1_value => sqlcode
328 ,p_token2 => g_sqlerrm_token
329 ,p_token2_value => sqlerrm);
330 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
331 END lock_options;
332
333 PROCEDURE lock_options(
334 p_api_version IN NUMBER
335 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
336 ,x_return_status OUT NOCOPY VARCHAR2
337 ,x_msg_count OUT NOCOPY NUMBER
338 ,x_msg_data OUT NOCOPY VARCHAR2
339 ,p_optv_tbl IN optv_tbl_type) IS
340 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
341
342 BEGIN
343 okl_opt_pvt.lock_row(
344 p_api_version => p_api_version
345 ,p_init_msg_list => p_init_msg_list
346 ,x_return_status => x_return_status
347 ,x_msg_count => x_msg_count
348 ,x_msg_data => x_msg_data
349 ,p_optv_tbl => p_optv_tbl
350 );
351 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
352 RAISE G_EXCEPTION_HALT_VALIDATION;
353 ELSE
354 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
355 l_return_status := x_return_status;
356 END IF;
357 END IF;
358
359 EXCEPTION
360 WHEN G_EXCEPTION_HALT_VALIDATION THEN
361 NULL;
362 WHEN OTHERS THEN
363 OKC_API.SET_MESSAGE(p_app_name => g_app_name
364 ,p_msg_name => g_unexpected_error
365 ,p_token1 => g_sqlcode_token
366 ,p_token1_value => sqlcode
367 ,p_token2 => g_sqlerrm_token
368 ,p_token2_value => sqlerrm);
369 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
370 END lock_options;
371
372 PROCEDURE update_options(
373 p_api_version IN NUMBER
374 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
375 ,x_return_status OUT NOCOPY VARCHAR2
376 ,x_msg_count OUT NOCOPY NUMBER
377 ,x_msg_data OUT NOCOPY VARCHAR2
378 ,p_optv_rec IN optv_rec_type
379 ,x_optv_rec OUT NOCOPY optv_rec_type) IS
380 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
381
382 BEGIN
383 okl_opt_pvt.update_row(
384 p_api_version => p_api_version
385 ,p_init_msg_list => p_init_msg_list
386 ,x_return_status => x_return_status
387 ,x_msg_count => x_msg_count
388 ,x_msg_data => x_msg_data
389 ,p_optv_rec => p_optv_rec
390 ,x_optv_rec => x_optv_rec
391 );
392 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
393 RAISE G_EXCEPTION_HALT_VALIDATION;
394 ELSE
395 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
396 l_return_status := x_return_status;
397 END IF;
398 END IF;
399 EXCEPTION
400 WHEN G_EXCEPTION_HALT_VALIDATION THEN
401 NULL;
402 WHEN OTHERS THEN
403 OKC_API.SET_MESSAGE(p_app_name => g_app_name
404 ,p_msg_name => g_unexpected_error
405 ,p_token1 => g_sqlcode_token
406 ,p_token1_value => sqlcode
407 ,p_token2 => g_sqlerrm_token
408 ,p_token2_value => sqlerrm);
409 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
410 END update_options;
411
412 PROCEDURE update_options(
413 p_api_version IN NUMBER
414 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
415 ,x_return_status OUT NOCOPY VARCHAR2
416 ,x_msg_count OUT NOCOPY NUMBER
417 ,x_msg_data OUT NOCOPY VARCHAR2
418 ,p_optv_tbl IN optv_tbl_type
419 ,x_optv_tbl OUT NOCOPY optv_tbl_type) IS
420 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
421 BEGIN
422 okl_opt_pvt.update_row(
423 p_api_version => p_api_version
424 ,p_init_msg_list => p_init_msg_list
425 ,x_return_status => x_return_status
426 ,x_msg_count => x_msg_count
427 ,x_msg_data => x_msg_data
428 ,p_optv_tbl => p_optv_tbl
429 ,x_optv_tbl => x_optv_tbl
430 );
431 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
432 RAISE G_EXCEPTION_HALT_VALIDATION;
433 ELSE
434 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
435 l_return_status := x_return_status;
436 END IF;
437 END IF;
438 EXCEPTION
439 WHEN G_EXCEPTION_HALT_VALIDATION THEN
440 NULL;
441 WHEN OTHERS THEN
442 OKC_API.SET_MESSAGE(p_app_name => g_app_name
443 ,p_msg_name => g_unexpected_error
444 ,p_token1 => g_sqlcode_token
445 ,p_token1_value => sqlcode
446 ,p_token2 => g_sqlerrm_token
447 ,p_token2_value => sqlerrm);
448 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
449 END update_options;
450
451 --Put custom code for cascade delete by developer
452 PROCEDURE delete_options(
453 p_api_version IN NUMBER
454 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
455 ,x_return_status OUT NOCOPY VARCHAR2
456 ,x_msg_count OUT NOCOPY NUMBER
457 ,x_msg_data OUT NOCOPY VARCHAR2
458 ,p_optv_rec IN optv_rec_type) IS
459 i NUMBER :=0;
460 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
461 l_ovev_tbl ovev_tbl_type;
462
463 CURSOR ove_csr IS
464 SELECT ove.id
465 FROM OKL_OPT_VALUES_V ove
466 WHERE ove.opt_id = p_optv_rec.id;
467 BEGIN
468 FOR ove_rec IN ove_csr
469 LOOP
470 i := i + 1;
471 l_ovev_tbl(i).id := ove_rec.id;
472 END LOOP;
473 IF l_ovev_tbl.COUNT > 0 THEN
474 delete_option_values(
475 p_api_version => p_api_version
476 ,p_init_msg_list => p_init_msg_list
477 ,x_return_status => x_return_status
478 ,x_msg_count => x_msg_count
479 ,x_msg_data => x_msg_data
480 ,p_ovev_tbl => l_ovev_tbl
481 );
482
483 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
484 RAISE G_EXCEPTION_HALT_VALIDATION;
485 ELSE
486 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
487 l_return_status := x_return_status;
488 END IF;
489 END IF;
490 END IF;
491 --Delete the Master
492 okl_opt_pvt.delete_row(
493 p_api_version => p_api_version
494 ,p_init_msg_list => p_init_msg_list
495 ,x_return_status => x_return_status
496 ,x_msg_count => x_msg_count
497 ,x_msg_data => x_msg_data
498 ,p_optv_rec => p_optv_rec
499 );
500
501 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
502 RAISE G_EXCEPTION_HALT_VALIDATION;
503 ELSE
504 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
505 l_return_status := x_return_status;
506 END IF;
507 END IF;
508 EXCEPTION
509 WHEN G_EXCEPTION_HALT_VALIDATION THEN
510 NULL;
511 WHEN OTHERS THEN
512 OKC_API.SET_MESSAGE(p_app_name => g_app_name
513 ,p_msg_name => g_unexpected_error
514 ,p_token1 => g_sqlcode_token
515 ,p_token1_value => sqlcode
516 ,p_token2 => g_sqlerrm_token
517 ,p_token2_value => sqlerrm);
518
519 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
520 END delete_options;
521
522 PROCEDURE delete_options(
523 p_api_version IN NUMBER
524 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
525 ,x_return_status OUT NOCOPY VARCHAR2
526 ,x_msg_count OUT NOCOPY NUMBER
527 ,x_msg_data OUT NOCOPY VARCHAR2
528 ,p_optv_tbl IN optv_tbl_type) IS
529 i NUMBER :=0;
530 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
531 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
532
533 BEGIN
534 --Initialize the return status
535 x_return_status := OKC_API.G_RET_STS_SUCCESS;
536 IF (p_optv_tbl.COUNT > 0) THEN
537 i := p_optv_tbl.FIRST;
538 LOOP
539 delete_options(
540 p_api_version => p_api_version
541 ,p_init_msg_list => p_init_msg_list
542 ,x_return_status => x_return_status
543 ,x_msg_count => x_msg_count
544 ,x_msg_data => x_msg_data
545 ,p_optv_rec => p_optv_tbl(i)
546 );
547 -- TCHGS: Store the highest degree of error
548 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
549 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
550 l_overall_status := x_return_status;
551 END IF;
552 END IF;
553 EXIT WHEN (i = p_optv_tbl.LAST);
554 i := p_optv_tbl.NEXT(i);
555 END LOOP;
556 --TCHGS: return overall status
557 x_return_status := l_overall_status;
558 END IF;
559 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
560 RAISE G_EXCEPTION_HALT_VALIDATION;
561 ELSE
562 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
563 l_return_status := x_return_status;
564 END IF;
565 END IF;
566 EXCEPTION
567 WHEN G_EXCEPTION_HALT_VALIDATION THEN
568 NULL;
569 WHEN OTHERS THEN
570 OKC_API.SET_MESSAGE(p_app_name => g_app_name
571 ,p_msg_name => g_unexpected_error
572 ,p_token1 => g_sqlcode_token
573 ,p_token1_value => sqlcode
574 ,p_token2 => g_sqlerrm_token
575 ,p_token2_value => sqlerrm);
576 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
577 END delete_options;
578
579 PROCEDURE validate_options(
580 p_api_version IN NUMBER
581 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
582 ,x_return_status OUT NOCOPY VARCHAR2
583 ,x_msg_count OUT NOCOPY NUMBER
584 ,x_msg_data OUT NOCOPY VARCHAR2
585 ,p_optv_rec IN optv_rec_type) IS
586 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
587 BEGIN
588 okl_opt_pvt.validate_row(
589 p_api_version => p_api_version
590 ,p_init_msg_list => p_init_msg_list
591 ,x_return_status => x_return_status
592 ,x_msg_count => x_msg_count
593 ,x_msg_data => x_msg_data
594 ,p_optv_rec => p_optv_rec
595 );
596 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
597 RAISE G_EXCEPTION_HALT_VALIDATION;
598 ELSE
599 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
600 l_return_status := x_return_status;
601 END IF;
602 END IF;
603 EXCEPTION
604 WHEN G_EXCEPTION_HALT_VALIDATION THEN
605 NULL;
606 WHEN OTHERS THEN
607 OKC_API.SET_MESSAGE(p_app_name => g_app_name
608 ,p_msg_name => g_unexpected_error
609 ,p_token1 => g_sqlcode_token
610 ,p_token1_value => sqlcode
611 ,p_token2 => g_sqlerrm_token
612 ,p_token2_value => sqlerrm);
613 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
614 END validate_options;
615
616 PROCEDURE validate_options(
617 p_api_version IN NUMBER,
618 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
619 x_return_status OUT NOCOPY VARCHAR2,
620 x_msg_count OUT NOCOPY NUMBER,
621 x_msg_data OUT NOCOPY VARCHAR2,
622 p_optv_tbl IN optv_tbl_type) IS
623 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
624
625 BEGIN
626 okl_opt_pvt.validate_row(
627 p_api_version => p_api_version
628 ,p_init_msg_list => p_init_msg_list
629 ,x_return_status => x_return_status
630 ,x_msg_count => x_msg_count
631 ,x_msg_data => x_msg_data
632 ,p_optv_tbl => p_optv_tbl
633 );
634 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
635 RAISE G_EXCEPTION_HALT_VALIDATION;
636 ELSE
637 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
638 l_return_status := x_return_status;
639 END IF;
640 END IF;
641 EXCEPTION
642 WHEN G_EXCEPTION_HALT_VALIDATION THEN
643 NULL;
644 WHEN OTHERS THEN
645 OKC_API.SET_MESSAGE(p_app_name => g_app_name
646 ,p_msg_name => g_unexpected_error
647 ,p_token1 => g_sqlcode_token
648 ,p_token1_value => sqlcode
649 ,p_token2 => g_sqlerrm_token
650 ,p_token2_value => sqlerrm);
651 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
652 END validate_options;
653
654 PROCEDURE create_option_values(
655 p_api_version IN NUMBER
656 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
657 ,x_return_status OUT NOCOPY VARCHAR2
658 ,x_msg_count OUT NOCOPY NUMBER
659 ,x_msg_data OUT NOCOPY VARCHAR2
660 ,p_ovev_rec IN ovev_rec_type
661 ,x_ovev_rec OUT NOCOPY ovev_rec_type) IS
662 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
663
664 BEGIN
665 okl_ove_pvt.insert_row(
666 p_api_version => p_api_version
667 ,p_init_msg_list => p_init_msg_list
668 ,x_return_status => x_return_status
669 ,x_msg_count => x_msg_count
670 ,x_msg_data => x_msg_data
671 ,p_ovev_rec => p_ovev_rec
672 ,x_ovev_rec => x_ovev_rec
673 );
674 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
675 RAISE G_EXCEPTION_HALT_VALIDATION;
676 ELSE
677 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
678 l_return_status := x_return_status;
679 END IF;
680 END IF;
681 EXCEPTION
682 WHEN G_EXCEPTION_HALT_VALIDATION THEN
683 NULL;
684 WHEN OTHERS THEN
685 OKC_API.SET_MESSAGE(p_app_name => g_app_name
686 ,p_msg_name => g_unexpected_error
687 ,p_token1 => g_sqlcode_token
688 ,p_token1_value => sqlcode
689 ,p_token2 => g_sqlerrm_token
690 ,p_token2_value => sqlerrm);
691 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
692 END create_option_values;
693
694 PROCEDURE create_option_values(
695 p_api_version IN NUMBER
696 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
697 ,x_return_status OUT NOCOPY VARCHAR2
698 ,x_msg_count OUT NOCOPY NUMBER
699 ,x_msg_data OUT NOCOPY VARCHAR2
700 ,p_ovev_tbl IN ovev_tbl_type
701 ,x_ovev_tbl OUT NOCOPY ovev_tbl_type) IS
702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
703
704 BEGIN
705 okl_ove_pvt.insert_row(
706 p_api_version => p_api_version
707 ,p_init_msg_list => p_init_msg_list
708 ,x_return_status => x_return_status
709 ,x_msg_count => x_msg_count
710 ,x_msg_data => x_msg_data
711 ,p_ovev_tbl => p_ovev_tbl
712 ,x_ovev_tbl => x_ovev_tbl
713 );
714 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
715 RAISE G_EXCEPTION_HALT_VALIDATION;
716 ELSE
717 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
718 l_return_status := x_return_status;
719 END IF;
720 END IF;
721 EXCEPTION
722 WHEN G_EXCEPTION_HALT_VALIDATION THEN
723 NULL;
724 WHEN OTHERS THEN
725 OKC_API.SET_MESSAGE(p_app_name => g_app_name
726 ,p_msg_name => g_unexpected_error
727 ,p_token1 => g_sqlcode_token
728 ,p_token1_value => sqlcode
729 ,p_token2 => g_sqlerrm_token
730 ,p_token2_value => sqlerrm);
731 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
732 END create_option_values;
733
734 PROCEDURE lock_option_values(
735 p_api_version IN NUMBER
736 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
737 ,x_return_status OUT NOCOPY VARCHAR2
738 ,x_msg_count OUT NOCOPY NUMBER
739 ,x_msg_data OUT NOCOPY VARCHAR2
740 ,p_ovev_rec IN ovev_rec_type) IS
741 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
742 BEGIN
743 okl_ove_pvt.lock_row(
744 p_api_version => p_api_version
745 ,p_init_msg_list => p_init_msg_list
746 ,x_return_status => x_return_status
747 ,x_msg_count => x_msg_count
748 ,x_msg_data => x_msg_data
749 ,p_ovev_rec => p_ovev_rec
750 );
751 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
752 RAISE G_EXCEPTION_HALT_VALIDATION;
753 ELSE
754 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
755 l_return_status := x_return_status;
756 END IF;
757 END IF;
758 EXCEPTION
759 WHEN G_EXCEPTION_HALT_VALIDATION THEN
760 NULL;
761 WHEN OTHERS THEN
762 OKC_API.SET_MESSAGE(p_app_name => g_app_name
763 ,p_msg_name => g_unexpected_error
764 ,p_token1 => g_sqlcode_token
765 ,p_token1_value => sqlcode
766 ,p_token2 => g_sqlerrm_token
767 ,p_token2_value => sqlerrm);
768 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
769
770 END lock_option_values;
771
772 PROCEDURE lock_option_values(
773 p_api_version IN NUMBER
774 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
775 ,x_return_status OUT NOCOPY VARCHAR2
776 ,x_msg_count OUT NOCOPY NUMBER
777 ,x_msg_data OUT NOCOPY VARCHAR2
778 ,p_ovev_tbl IN ovev_tbl_type) IS
779 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
780
781 BEGIN
782 okl_ove_pvt.lock_row(
783 p_api_version => p_api_version
784 ,p_init_msg_list => p_init_msg_list
785 ,x_return_status => x_return_status
786 ,x_msg_count => x_msg_count
787 ,x_msg_data => x_msg_data
788 ,p_ovev_tbl => p_ovev_tbl
789 );
790 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
791 RAISE G_EXCEPTION_HALT_VALIDATION;
792 ELSE
793 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
794 l_return_status := x_return_status;
795 END IF;
796 END IF;
797 EXCEPTION
798 WHEN G_EXCEPTION_HALT_VALIDATION THEN
799 NULL;
800 WHEN OTHERS THEN
801 OKC_API.SET_MESSAGE(p_app_name => g_app_name
802 ,p_msg_name => g_unexpected_error
803 ,p_token1 => g_sqlcode_token
804 ,p_token1_value => sqlcode
805 ,p_token2 => g_sqlerrm_token
806 ,p_token2_value => sqlerrm);
807 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
808 END lock_option_values;
809
810 PROCEDURE update_option_values(
811 p_api_version IN NUMBER
812 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
813 ,x_return_status OUT NOCOPY VARCHAR2
814 ,x_msg_count OUT NOCOPY NUMBER
815 ,x_msg_data OUT NOCOPY VARCHAR2
816 ,p_ovev_rec IN ovev_rec_type
817 ,x_ovev_rec OUT NOCOPY ovev_rec_type) IS
818 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
819
820 BEGIN
821 okl_ove_pvt.update_row(
822 p_api_version => p_api_version
823 ,p_init_msg_list => p_init_msg_list
824 ,x_return_status => x_return_status
825 ,x_msg_count => x_msg_count
826 ,x_msg_data => x_msg_data
827 ,p_ovev_rec => p_ovev_rec
828 ,x_ovev_rec => x_ovev_rec
829 );
830 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
831 RAISE G_EXCEPTION_HALT_VALIDATION;
832 ELSE
833 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
834 l_return_status := x_return_status;
835 END IF;
836 END IF;
837 EXCEPTION
838 WHEN G_EXCEPTION_HALT_VALIDATION THEN
839 NULL;
840 WHEN OTHERS THEN
841 OKC_API.SET_MESSAGE(p_app_name => g_app_name
842 ,p_msg_name => g_unexpected_error
843 ,p_token1 => g_sqlcode_token
844 ,p_token1_value => sqlcode
845 ,p_token2 => g_sqlerrm_token
846 ,p_token2_value => sqlerrm);
847 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
848 END update_option_values;
849
850 PROCEDURE update_option_values(
851 p_api_version IN NUMBER
852 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
853 ,x_return_status OUT NOCOPY VARCHAR2
854 ,x_msg_count OUT NOCOPY NUMBER
855 ,x_msg_data OUT NOCOPY VARCHAR2
856 ,p_ovev_tbl IN ovev_tbl_type
857 ,x_ovev_tbl OUT NOCOPY ovev_tbl_type) IS
858 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
859
860 BEGIN
861 okl_ove_pvt.update_row(
862 p_api_version => p_api_version
863 ,p_init_msg_list => p_init_msg_list
864 ,x_return_status => x_return_status
865 ,x_msg_count => x_msg_count
866 ,x_msg_data => x_msg_data
867 ,p_ovev_tbl => p_ovev_tbl
868 ,x_ovev_tbl => x_ovev_tbl
869 );
870 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
871 RAISE G_EXCEPTION_HALT_VALIDATION;
872 ELSE
873 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
874 l_return_status := x_return_status;
875 END IF;
876 END IF;
877 EXCEPTION
878 WHEN G_EXCEPTION_HALT_VALIDATION THEN
879 NULL;
880 WHEN OTHERS THEN
881 OKC_API.SET_MESSAGE(p_app_name => g_app_name
882 ,p_msg_name => g_unexpected_error
883 ,p_token1 => g_sqlcode_token
884 ,p_token1_value => sqlcode
885 ,p_token2 => g_sqlerrm_token
886 ,p_token2_value => sqlerrm);
887 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
888 END update_option_values;
889
890 --Put custom code for cascade delete by developer
891 PROCEDURE delete_option_values(
892 p_api_version IN NUMBER
893 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
894 ,x_return_status OUT NOCOPY VARCHAR2
895 ,x_msg_count OUT NOCOPY NUMBER
896 ,x_msg_data OUT NOCOPY VARCHAR2
897 ,p_ovev_rec IN ovev_rec_type) IS
898 i NUMBER :=0;
899 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
900 BEGIN
901 okl_ove_pvt.delete_row(
902 p_api_version => p_api_version
903 ,p_init_msg_list => p_init_msg_list
904 ,x_return_status => x_return_status
905 ,x_msg_count => x_msg_count
906 ,x_msg_data => x_msg_data
907 ,p_ovev_rec => p_ovev_rec);
908 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
909 RAISE G_EXCEPTION_HALT_VALIDATION;
910 ELSE
911 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
912 l_return_status := x_return_status;
913 END IF;
914 END IF;
915 EXCEPTION
916 WHEN G_EXCEPTION_HALT_VALIDATION THEN
917 NULL;
918 WHEN OTHERS THEN
919 OKC_API.SET_MESSAGE(p_app_name => g_app_name
920 ,p_msg_name => g_unexpected_error
921 ,p_token1 => g_sqlcode_token
922 ,p_token1_value => sqlcode
923 ,p_token2 => g_sqlerrm_token
924 ,p_token2_value => sqlerrm);
925 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
926 END delete_option_values;
927
928 PROCEDURE delete_option_values(
929 p_api_version IN NUMBER
930 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
931 ,x_return_status OUT NOCOPY VARCHAR2
932 ,x_msg_count OUT NOCOPY NUMBER
933 ,x_msg_data OUT NOCOPY VARCHAR2
934 ,p_ovev_tbl IN ovev_tbl_type) IS
935 i NUMBER :=0;
936 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
937 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
938
939 BEGIN
940 --Initialize the return status
941 x_return_status := OKC_API.G_RET_STS_SUCCESS;
942 IF (p_ovev_tbl.COUNT > 0) THEN
943 i := p_ovev_tbl.FIRST;
944 LOOP
945 delete_option_values(
946 p_api_version => p_api_version
947 ,p_init_msg_list => p_init_msg_list
948 ,x_return_status => x_return_status
949 ,x_msg_count => x_msg_count
950 ,x_msg_data => x_msg_data
951 ,p_ovev_rec => p_ovev_tbl(i));
952 -- TCHGS: Store the highest degree of error
953 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
954 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
955 l_overall_status := x_return_status;
956 END IF;
957 END IF;
958 EXIT WHEN (i = p_ovev_tbl.LAST);
959 i := p_ovev_tbl.NEXT(i);
960 END LOOP;
961 --TCHGS: return overall status
962 x_return_status := l_overall_status;
963 END IF;
964 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
965 RAISE G_EXCEPTION_HALT_VALIDATION;
966 ELSE
967 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
968 l_return_status := x_return_status;
969 END IF;
970 END IF;
971 EXCEPTION
972 WHEN G_EXCEPTION_HALT_VALIDATION THEN
973 NULL;
974 WHEN OTHERS THEN
975 OKC_API.SET_MESSAGE(p_app_name => g_app_name
976 ,p_msg_name => g_unexpected_error
977 ,p_token1 => g_sqlcode_token
978 ,p_token1_value => sqlcode
979 ,p_token2 => g_sqlerrm_token
980 ,p_token2_value => sqlerrm);
981 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
982 END delete_option_values;
983
984 PROCEDURE validate_option_values(
985 p_api_version IN NUMBER
986 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
987 ,x_return_status OUT NOCOPY VARCHAR2
988 ,x_msg_count OUT NOCOPY NUMBER
989 ,x_msg_data OUT NOCOPY VARCHAR2
990 ,p_ovev_rec IN ovev_rec_type) IS
991 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
992 BEGIN
993 okl_ove_pvt.validate_row(
994 p_api_version => p_api_version
995 ,p_init_msg_list => p_init_msg_list
996 ,x_return_status => x_return_status
997 ,x_msg_count => x_msg_count
998 ,x_msg_data => x_msg_data
999 ,p_ovev_rec => p_ovev_rec
1000 );
1001 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1002 RAISE G_EXCEPTION_HALT_VALIDATION;
1003 ELSE
1004 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1005 l_return_status := x_return_status;
1006 END IF;
1007 END IF;
1008 EXCEPTION
1009 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1010 NULL;
1011 WHEN OTHERS THEN
1012 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1013 ,p_msg_name => g_unexpected_error
1014 ,p_token1 => g_sqlcode_token
1015 ,p_token1_value => sqlcode
1016 ,p_token2 => g_sqlerrm_token
1017 ,p_token2_value => sqlerrm);
1018 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1019 END validate_option_values;
1020
1021 PROCEDURE validate_option_values(
1022 p_api_version IN NUMBER
1023 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1024 ,x_return_status OUT NOCOPY VARCHAR2
1025 ,x_msg_count OUT NOCOPY NUMBER
1026 ,x_msg_data OUT NOCOPY VARCHAR2
1027 ,p_ovev_tbl IN ovev_tbl_type) IS
1028 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1029 BEGIN
1030 okl_ove_pvt.validate_row(
1031 p_api_version => p_api_version
1032 ,p_init_msg_list => p_init_msg_list
1033 ,x_return_status => x_return_status
1034 ,x_msg_count => x_msg_count
1035 ,x_msg_data => x_msg_data
1036 ,p_ovev_tbl => p_ovev_tbl
1037 );
1038 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1039 RAISE G_EXCEPTION_HALT_VALIDATION;
1040 ELSE
1041 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1042 l_return_status := x_return_status;
1043 END IF;
1044 END IF;
1045 EXCEPTION
1046 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1047 NULL;
1048 WHEN OTHERS THEN
1049 OKC_API.SET_MESSAGE(p_app_name => g_app_name
1050 ,p_msg_name => g_unexpected_error
1051 ,p_token1 => g_sqlcode_token
1052 ,p_token1_value => sqlcode
1053 ,p_token2 => g_sqlerrm_token
1054 ,p_token2_value => sqlerrm);
1055 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1056 END validate_option_values;
1057
1058 END OKL_OPTIONS_PVT;