[Home] [Help]
PACKAGE BODY: APPS.OKL_TMPT_SET_PVT
Source
1 PACKAGE BODY okl_tmpt_set_pvt AS
2 /* $Header: OKLCAESB.pls 115.5 2002/02/18 20:10:17 pkm ship $ */
3
4
5 PROCEDURE create_tmpt_set(
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_aesv_rec IN aesv_rec_type
12 ,p_avlv_tbl IN avlv_tbl_type
13 ,p_atlv_tbl IN atlv_tbl_type
14 ,x_aesv_rec OUT NOCOPY aesv_rec_type
15 ,x_avlv_tbl OUT NOCOPY avlv_tbl_type
16 ,x_atlv_tbl OUT NOCOPY atlv_tbl_type
17 )
18
19 AS
20
21 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
22 l_aesv_rec aesv_rec_type;
23 l_avlv_tbl avlv_tbl_type := p_avlv_tbl;
24 l_atlv_tbl atlv_tbl_type := p_atlv_tbl;
25 i NUMBER;
26 j NUMBER;
27
28 BEGIN
29
30 --Populate the Template Set Table
31
32 create_tmpt_set(p_api_version,
33 p_init_msg_list,
34 x_return_status,
35 x_msg_count,
36 x_msg_data,
37 p_aesv_rec,
38 x_aesv_rec);
39
40 -- Proceed Further only if no Error is Encountered.
41
42 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
43
44 -- Populate the foreign key for the line
45 IF (l_avlv_tbl.COUNT > 0) THEN
46 i := l_avlv_tbl.FIRST;
47 LOOP
48 l_avlv_tbl(i).aes_id := x_aesv_rec.id;
49 EXIT WHEN (i = l_avlv_tbl.LAST);
50 i := l_avlv_tbl.NEXT(i);
51 END LOOP;
52 END IF;
53
54 --Populate the Template Table
55
56 create_template(p_api_version,
57 p_init_msg_list,
58 x_return_status,
59 x_msg_count,
60 x_msg_data,
61 l_avlv_tbl,
62 x_avlv_tbl);
63
64 -- Proceed with the lines only if Template Creation is Successful.
65
66 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
67
68 -- Populate the foreign key for the detail
69 IF (l_avlv_tbl.COUNT > 0) THEN
70 i := l_avlv_tbl.FIRST;
71 LOOP
72 j := l_atlv_tbl.FIRST;
73 LOOP
74 IF (i = p_atlv_tbl(j).avl_tbl_index) THEN
75 l_atlv_tbl(j).avl_id := x_avlv_tbl(i).id;
76 END IF;
77 EXIT WHEN (j = l_atlv_tbl.LAST);
78 j := l_atlv_tbl.NEXT(j);
79 END LOOP;
80 EXIT WHEN (i = l_avlv_tbl.LAST);
81 i := l_avlv_tbl.NEXT(i);
82 END LOOP;
83 END IF;
84
85 --Populate the detail
86 create_tmpt_lines(p_api_version,
87 p_init_msg_list,
88 x_return_status,
89 x_msg_count,
90 x_msg_data,
91 l_atlv_tbl,
92 x_atlv_tbl);
93 END IF;
94
95 END IF;
96
97
98 EXCEPTION
99
100 WHEN OTHERS THEN
101 Okc_Api.set_message(p_app_name => g_app_name,
102 p_msg_name => g_unexpected_error,
103 p_token1 => g_sqlcode_token,
104 p_token1_value => SQLCODE,
105 p_token2 => g_sqlerrm_token,
106 p_token2_value => SQLERRM);
107 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
108
109 END;
110
111
112 PROCEDURE update_tmpt_set(
113 p_api_version IN NUMBER
114 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
115 ,x_return_status OUT NOCOPY VARCHAR2
116 ,x_msg_count OUT NOCOPY NUMBER
117 ,x_msg_data OUT NOCOPY VARCHAR2
118 ,p_aesv_rec IN aesv_rec_type
119 ,p_avlv_tbl IN avlv_tbl_type
120 ,p_atlv_tbl IN atlv_tbl_type
121 ,x_aesv_rec OUT NOCOPY aesv_rec_type
122 ,x_avlv_tbl OUT NOCOPY avlv_tbl_type
123 ,x_atlv_tbl OUT NOCOPY atlv_tbl_type
124 )
125 AS
126 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
127
128 BEGIN
129
130 --Update the Template Set Table
131
132 update_tmpt_set(p_api_version,
133 p_init_msg_list,
134 x_return_status,
135 x_msg_count,
136 x_msg_data,
137 p_aesv_rec,
138 x_aesv_rec);
139
140 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
141
142 --Update the Template Table
143 update_template(p_api_version,
144 p_init_msg_list,
145 x_return_status,
146 x_msg_count,
147 x_msg_data,
148 p_avlv_tbl,
149 x_avlv_tbl);
150
151 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
152
153 --Update the Template Line Table
154 update_tmpt_lines(p_api_version,
155 p_init_msg_list,
156 x_return_status,
157 x_msg_count,
158 x_msg_data,
159 p_atlv_tbl,
160 x_atlv_tbl);
161 END IF;
162
163 END IF;
164
165 END UPDATE_TMPT_SET;
166
167
168
169 PROCEDURE validate_tmpt_set(
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_aesv_rec IN aesv_rec_type
176 ,p_avlv_tbl IN avlv_tbl_type
177 ,p_atlv_tbl IN atlv_tbl_type
178 )
179
180 AS
181 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
182
183 BEGIN
184
185 -- Validate the Template Set Table
186
187 validate_tmpt_set(p_api_version,
188 p_init_msg_list,
189 x_return_status,
190 x_msg_count,
191 x_msg_data,
192 p_aesv_rec);
193
194 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
195 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
196 l_overall_status := x_return_status;
197 END IF;
198 END IF;
199
200 --Validate the Template Table
201 validate_template(p_api_version,
202 p_init_msg_list,
203 x_return_status,
204 x_msg_count,
205 x_msg_data,
206 p_avlv_tbl);
207
208 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
209 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
210 l_overall_status := x_return_status;
211 END IF;
212 END IF;
213
214 --Validate the detail
215 validate_tmpt_lines(p_api_version,
216 p_init_msg_list,
217 x_return_status,
218 x_msg_count,
219 x_msg_data,
220 p_atlv_tbl);
221
222 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
223 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
224 l_overall_status := x_return_status;
225 END IF;
226 END IF;
227
228 x_return_status := l_overall_status;
229
230
231 EXCEPTION
232
233 WHEN OTHERS THEN
234 Okc_Api.set_message(p_app_name => g_app_name,
235 p_msg_name => g_unexpected_error,
236 p_token1 => g_sqlcode_token,
237 p_token1_value => SQLCODE,
238 p_token2 => g_sqlerrm_token,
239 p_token2_value => SQLERRM);
240 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
241
242 END;
243
244
245 PROCEDURE create_tmpt_set(
246 p_api_version IN NUMBER,
247 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2,
251 p_aesv_rec IN aesv_rec_type,
252 x_aesv_rec OUT NOCOPY aesv_rec_type) IS
253
254 BEGIN
255
256 okl_aes_pvt.insert_row(p_api_version,
257 p_init_msg_list,
258 x_return_status,
259 x_msg_count,
260 x_msg_data,
261 p_aesv_rec,
262 x_aesv_rec);
263
264 END create_tmpt_set;
265
266 PROCEDURE create_tmpt_set(
267 p_api_version IN NUMBER,
268 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2,
272 p_aesv_tbl IN aesv_tbl_type,
273 x_aesv_tbl OUT NOCOPY aesv_tbl_type) IS
274
275 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
276 i NUMBER := 0;
277 BEGIN
278
279 Okc_Api.init_msg_list(p_init_msg_list);
280 -- Make sure PL/SQL table has records in it before passing
281 IF (p_aesv_tbl.COUNT > 0) THEN
282 i := p_aesv_tbl.FIRST;
283 LOOP
284 create_tmpt_set(
285 p_api_version => p_api_version,
286 p_init_msg_list => Okc_Api.G_FALSE,
287 x_return_status => x_return_status,
288 x_msg_count => x_msg_count,
289 x_msg_data => x_msg_data,
290 p_aesv_rec => p_aesv_tbl(i),
291 x_aesv_rec => x_aesv_tbl(i));
292
293 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
294 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
295 l_overall_status := x_return_status;
296 END IF;
297 END IF;
298 EXIT WHEN (i = p_aesv_tbl.LAST);
299 i := p_aesv_tbl.NEXT(i);
300 END LOOP;
301
302 END IF;
303
304 x_return_status := l_overall_status;
305
306 END create_tmpt_set;
307
308
309 PROCEDURE lock_tmpt_set(
310 p_api_version IN NUMBER,
311 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2,
315 p_aesv_rec IN aesv_rec_type) IS
316 BEGIN
317 okl_aes_pvt.lock_row(p_api_version,
318 p_init_msg_list,
319 x_return_status,
320 x_msg_count,
321 x_msg_data,
322 p_aesv_rec);
323 END lock_tmpt_set;
324
325 PROCEDURE lock_tmpt_set(
326 p_api_version IN NUMBER,
327 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_count OUT NOCOPY NUMBER,
330 x_msg_data OUT NOCOPY VARCHAR2,
331 p_aesv_tbl IN aesv_tbl_type) IS
332
333 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
334 i NUMBER := 0;
335
336 BEGIN
337
338 OKC_API.init_msg_list(p_init_msg_list);
339 IF (p_aesv_tbl.COUNT > 0) THEN
340 i := p_aesv_tbl.FIRST;
341 LOOP
342 lock_tmpt_set(p_api_version => p_api_version,
343 p_init_msg_list => p_init_msg_list,
344 x_return_status => x_return_status,
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data,
347 p_aesv_rec => p_aesv_tbl(i));
348
349 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
350 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
351 l_overall_status := x_return_status;
352 END IF;
353 END IF;
354
355 EXIT WHEN (i = p_aesv_tbl.LAST);
356 i := p_aesv_tbl.NEXT(i);
357 END LOOP;
358 END IF;
359
360 x_return_status := l_overall_status;
361
362 END lock_tmpt_set;
363
364 PROCEDURE update_tmpt_set(
365 p_api_version IN NUMBER,
366 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
367 x_return_status OUT NOCOPY VARCHAR2,
368 x_msg_count OUT NOCOPY NUMBER,
369 x_msg_data OUT NOCOPY VARCHAR2,
370 p_aesv_rec IN aesv_rec_type,
371 x_aesv_rec OUT NOCOPY aesv_rec_type) IS
372 BEGIN
373 okl_aes_pvt.update_row(p_api_version,
374 p_init_msg_list,
375 x_return_status,
376 x_msg_count,
377 x_msg_data,
378 p_aesv_rec,
379 x_aesv_rec);
380
381 END update_tmpt_set;
382
383 PROCEDURE update_tmpt_set(
384 p_api_version IN NUMBER,
385 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
386 x_return_status OUT NOCOPY VARCHAR2,
387 x_msg_count OUT NOCOPY NUMBER,
388 x_msg_data OUT NOCOPY VARCHAR2,
389 p_aesv_tbl IN aesv_tbl_type,
390 x_aesv_tbl OUT NOCOPY aesv_tbl_type) IS
391
392 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
393 i NUMBER := 0;
394
395 BEGIN
396
397 Okc_Api.init_msg_list(p_init_msg_list);
398 -- Make sure PL/SQL table has records in it before passing
399 IF (p_aesv_tbl.COUNT > 0) THEN
400 i := p_aesv_tbl.FIRST;
401 LOOP
402 update_tmpt_set(
403 p_api_version => p_api_version,
404 p_init_msg_list => Okc_Api.G_FALSE,
405 x_return_status => x_return_status,
406 x_msg_count => x_msg_count,
407 x_msg_data => x_msg_data,
408 p_aesv_rec => p_aesv_tbl(i),
409 x_aesv_rec => x_aesv_tbl(i));
410
411 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
412 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
413 l_overall_status := x_return_status;
414 END IF;
415 END IF;
416
417 EXIT WHEN (i = p_aesv_tbl.LAST);
418 i := p_aesv_tbl.NEXT(i);
419 END LOOP;
420 END IF;
421
422 x_return_status := l_overall_status;
423
424 END update_tmpt_set;
425
426
427 PROCEDURE delete_tmpt_set(
428 p_api_version IN NUMBER,
429 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_msg_count OUT NOCOPY NUMBER,
432 x_msg_data OUT NOCOPY VARCHAR2,
433 p_aesv_rec IN aesv_rec_type) IS
434
435 i NUMBER :=0;
436 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
437
438
439 CURSOR tmpl_csr(p_aes_id NUMBER)
440 IS
441 SELECT id
442 FROM okl_ae_templates
443 WHERE aes_id = p_aes_id;
444
445
446 l_ae_template_tbl OKL_AVL_PVT.avlv_tbl_type;
447
448 i NUMBER := 0;
449 j NUMBER := 0;
450
451 BEGIN
452
453
454 FOR tmpl_rec IN tmpl_csr(p_aesv_rec.ID)
455 LOOP
456 j := j + 1;
457 l_ae_template_tbl(j).ID := tmpl_rec.ID;
458 END LOOP;
459
460 -- Delete_template will take care of deleting the Lines as well.
461
462 delete_template(p_api_version
463 ,p_init_msg_list
464 ,x_return_status
465 ,x_msg_count
466 ,x_msg_data
467 ,l_ae_template_tbl);
468
469 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
470
471 delete_tmpt_set(p_api_version,
472 p_init_msg_list,
473 x_return_status,
474 x_msg_count,
475 x_msg_data,
476 p_aesv_rec);
477 END IF;
478
479
480 EXCEPTION
481
482 WHEN OTHERS THEN
483 OKC_API.set_message(p_app_name => g_app_name,
484 p_msg_name => g_unexpected_error,
485 p_token1 => g_sqlcode_token,
486 p_token1_value => SQLCODE,
487 p_token2 => g_sqlerrm_token,
488 p_token2_value => SQLERRM);
489 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
490 END delete_tmpt_set;
491
492
493 PROCEDURE delete_tmpt_set(
494 p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_msg_data OUT NOCOPY VARCHAR2,
499 p_aesv_tbl IN aesv_tbl_type) IS
500 i NUMBER :=0;
501 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
502 BEGIN
503 --Initialize the return status
504 x_return_status := OKC_API.G_RET_STS_SUCCESS;
505
506 IF (p_aesv_tbl.COUNT > 0) THEN
507 i := p_aesv_tbl.FIRST;
508 LOOP
509 delete_tmpt_set(
510 p_api_version
511 ,p_init_msg_list
512 ,x_return_status
513 ,x_msg_count
514 ,x_msg_data
515 ,p_aesv_tbl(i));
516
517 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
518 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
519 l_overall_status := x_return_status;
520 END IF;
521 END IF;
522
523 EXIT WHEN (i = p_aesv_tbl.LAST);
524 i := p_aesv_tbl.NEXT(i);
525 END LOOP;
526
527 END IF;
528
529 x_return_status := l_overall_status;
530
531 END delete_tmpt_set;
532
533
534 PROCEDURE validate_tmpt_set(
535 p_api_version IN NUMBER,
536 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
537 x_return_status OUT NOCOPY VARCHAR2,
538 x_msg_count OUT NOCOPY NUMBER,
539 x_msg_data OUT NOCOPY VARCHAR2,
540 p_aesv_rec IN aesv_rec_type) IS
541 BEGIN
542 okl_aes_pvt.validate_row(
543 p_api_version,
544 p_init_msg_list,
545 x_return_status,
546 x_msg_count,
547 x_msg_data,
548 p_aesv_rec);
549 END validate_tmpt_set;
550
551 PROCEDURE validate_tmpt_set(
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
554 x_return_status OUT NOCOPY VARCHAR2,
555 x_msg_count OUT NOCOPY NUMBER,
556 x_msg_data OUT NOCOPY VARCHAR2,
557 p_aesv_tbl IN aesv_tbl_type) IS
558
559 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
560 i NUMBER := 0;
561
562 BEGIN
563 Okc_Api.init_msg_list(p_init_msg_list);
564 -- Make sure PL/SQL table has records in it before passing
565
566 IF (p_aesv_tbl.COUNT > 0) THEN
567
568 i := p_aesv_tbl.FIRST;
569
570 LOOP
571 validate_tmpt_set(p_api_version,
572 p_init_msg_list,
573 x_return_status,
574 x_msg_count,
575 x_msg_data,
576 p_aesv_tbl(i));
577
578 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
579 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
580 l_overall_status := x_return_status;
581 END IF;
582 END IF;
583
584 EXIT WHEN (i = p_aesv_tbl.LAST);
585 i := p_aesv_tbl.NEXT(i);
586
587 END LOOP;
588
589 END IF;
590
591 x_return_status := l_overall_status;
592
593 END validate_tmpt_set;
594
595 PROCEDURE create_template(
596 p_api_version IN NUMBER,
597 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
598 x_return_status OUT NOCOPY VARCHAR2,
599 x_msg_count OUT NOCOPY NUMBER,
600 x_msg_data OUT NOCOPY VARCHAR2,
601 p_avlv_rec IN avlv_rec_type,
602 x_avlv_rec OUT NOCOPY avlv_rec_type) IS
603 BEGIN
604 okl_avl_pvt.insert_row(
605 p_api_version,
606 p_init_msg_list,
607 x_return_status,
608 x_msg_count,
609 x_msg_data,
610 p_avlv_rec,
611 x_avlv_rec);
612
613 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
614 NULL;
615 -- Custom code by developer Update_Minor_Version(p_avlv_rec.dnz_chr_id);
616 END IF;
617
618 END create_template;
619
620
621 PROCEDURE create_template(
622 p_api_version IN NUMBER,
623 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
624 x_return_status OUT NOCOPY VARCHAR2,
625 x_msg_count OUT NOCOPY NUMBER,
626 x_msg_data OUT NOCOPY VARCHAR2,
627 p_avlv_tbl IN avlv_tbl_type,
628 x_avlv_tbl OUT NOCOPY avlv_tbl_type) IS
629
630 i NUMBER := 0;
631 l_overall_Status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
632
633 BEGIN
634
635 Okc_Api.init_msg_list(p_init_msg_list);
636 -- Make sure PL/SQL table has records in it before passing
637 IF (p_avlv_tbl.COUNT > 0) THEN
638 i := p_avlv_tbl.FIRST;
639 LOOP
640 create_template(
641 p_api_version => p_api_version,
642 p_init_msg_list => Okc_Api.G_FALSE,
643 x_return_status => x_return_status,
644 x_msg_count => x_msg_count,
645 x_msg_data => x_msg_data,
646 p_avlv_rec => p_avlv_tbl(i),
647 x_avlv_rec => x_avlv_tbl(i));
648
649 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
650 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
651 l_overall_status := x_return_status;
652 END IF;
653 END IF;
654 EXIT WHEN (i = p_avlv_tbl.LAST);
655 i := p_avlv_tbl.NEXT(i);
656 END LOOP;
657 END IF;
658 x_return_status := l_overall_status;
659
660 END create_template;
661
662
663 PROCEDURE lock_template(
664 p_api_version IN NUMBER,
665 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2,
669 p_avlv_rec IN avlv_rec_type) IS
670 BEGIN
671 okl_avl_pvt.lock_row(
672 p_api_version,
673 p_init_msg_list,
674 x_return_status,
675 x_msg_count,
676 x_msg_data,
677 p_avlv_rec);
678 END lock_template;
679
680 PROCEDURE lock_template(
681 p_api_version IN NUMBER,
682 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_msg_count OUT NOCOPY NUMBER,
685 x_msg_data OUT NOCOPY VARCHAR2,
686 p_avlv_tbl IN avlv_tbl_type) IS
687
688 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
689 i NUMBER := 0;
690
691 BEGIN
692
693 Okc_Api.init_msg_list(p_init_msg_list);
694 -- Make sure PL/SQL table has records in it before passing
695 IF (p_avlv_tbl.COUNT > 0) THEN
696 i := p_avlv_tbl.FIRST;
697 LOOP
698 lock_template(
699 p_api_version => p_api_version,
700 p_init_msg_list => Okc_Api.G_FALSE,
701 x_return_status => x_return_status,
702 x_msg_count => x_msg_count,
703 x_msg_data => x_msg_data,
704 p_avlv_rec => p_avlv_tbl(i));
705
706 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
707 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
708 l_overall_status := x_return_status;
709 END IF;
710 END IF;
711 EXIT WHEN (i = p_avlv_tbl.LAST);
712 i := p_avlv_tbl.NEXT(i);
713 END LOOP;
714 END IF;
715
716 x_return_status := l_overall_status;
717
718 END lock_template;
719
720 PROCEDURE update_template(
721 p_api_version IN NUMBER,
722 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
723 x_return_status OUT NOCOPY VARCHAR2,
724 x_msg_count OUT NOCOPY NUMBER,
725 x_msg_data OUT NOCOPY VARCHAR2,
726 p_avlv_rec IN avlv_rec_type,
727 x_avlv_rec OUT NOCOPY avlv_rec_type) IS
728 BEGIN
729
730 OKL_AVL_PVT.update_row(
731 p_api_version,
732 p_init_msg_list,
733 x_return_status,
734 x_msg_count,
735 x_msg_data,
736 p_avlv_rec,
737 x_avlv_rec);
738
739 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
740 NULL;
741 -- Custom code by developer Update_Minor_Version(p_avlv_rec.dnz_chr_id);
742 END IF;
743 END update_template;
744
745 PROCEDURE update_template(
746 p_api_version IN NUMBER,
747 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_msg_data OUT NOCOPY VARCHAR2,
751 p_avlv_tbl IN avlv_tbl_type,
752 x_avlv_tbl OUT NOCOPY avlv_tbl_type) IS
753 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
754 i NUMBER := 0;
755 BEGIN
756
757 Okc_Api.init_msg_list(p_init_msg_list);
758 -- Make sure PL/SQL table has records in it before passing
759 IF (p_avlv_tbl.COUNT > 0) THEN
760 i := p_avlv_tbl.FIRST;
761 LOOP
762 update_template(
763 p_api_version => p_api_version,
764 p_init_msg_list => Okc_Api.G_FALSE,
765 x_return_status => x_return_status,
766 x_msg_count => x_msg_count,
767 x_msg_data => x_msg_data,
768 p_avlv_rec => p_avlv_tbl(i),
769 x_avlv_rec => x_avlv_tbl(i));
770
771 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
772 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
773 l_overall_status := x_return_status;
774 END IF;
775 END IF;
776
777 EXIT WHEN (i = p_avlv_tbl.LAST);
778 i := p_avlv_tbl.NEXT(i);
779 END LOOP;
780 END IF;
781
782 x_return_status := l_overall_status;
783
784 END update_template;
785
786
787 PROCEDURE delete_template(
788 p_api_version IN NUMBER,
789 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
790 x_return_status OUT NOCOPY VARCHAR2,
791 x_msg_count OUT NOCOPY NUMBER,
792 x_msg_data OUT NOCOPY VARCHAR2,
793 p_avlv_rec IN avlv_rec_type) IS
794
795 i NUMBER :=0;
796 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
797 l_atlv_tbl OKL_ATL_PVT.ATLV_TBL_TYPE;
798
799 CURSOR atl_csr(v_avl_id NUMBER) IS
800 SELECT ID
801 FROM OKL_AE_TMPT_LNES
802 WHERE avl_id = p_avlv_rec.ID;
803
804 atl_rec atl_csr%ROWTYPE;
805
806 BEGIN
807
808 FOR atl_rec IN atl_csr(p_avlv_rec.ID)
809 LOOP
810
811 i := i + 1;
812 l_atlv_tbl(i).ID := atl_rec.ID;
813
814 END LOOP;
815
816 delete_tmpt_lines(p_api_version,
817 p_init_msg_list,
818 x_return_Status,
819 x_msg_count,
820 x_msg_data,
821 l_atlv_tbl);
822
823 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
824
825 okl_avl_pvt.delete_row(
826 p_api_version,
827 p_init_msg_list,
828 x_return_status,
829 x_msg_count,
830 x_msg_data,
831 p_avlv_rec);
832 END IF;
833
834 END delete_template;
835
836
837
838 PROCEDURE delete_template(
839 p_api_version IN NUMBER,
840 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
841 x_return_status OUT NOCOPY VARCHAR2,
842 x_msg_count OUT NOCOPY NUMBER,
843 x_msg_data OUT NOCOPY VARCHAR2,
844 p_avlv_tbl IN avlv_tbl_type) IS
845 i NUMBER :=0;
846 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
847
848 BEGIN
849 --Initialize the return status
850 x_return_status := OKC_API.G_RET_STS_SUCCESS;
851
852 IF (p_avlv_tbl.COUNT > 0) THEN
853 i := p_avlv_tbl.FIRST;
854 LOOP
855 delete_template(
856 p_api_version,
857 p_init_msg_list,
858 x_return_status,
859 x_msg_count,
860 x_msg_data,
861 p_avlv_tbl(i));
862
863 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
864 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
865 l_overall_status := x_return_status;
866 END IF;
867 END IF;
868
869 EXIT WHEN (i = p_avlv_tbl.LAST);
870 i := p_avlv_tbl.NEXT(i);
871 END LOOP;
872 END IF;
873
874 x_return_status := l_overall_status;
875
876 END delete_template;
877
878 PROCEDURE validate_template(
879 p_api_version IN NUMBER,
880 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
881 x_return_status OUT NOCOPY VARCHAR2,
882 x_msg_count OUT NOCOPY NUMBER,
883 x_msg_data OUT NOCOPY VARCHAR2,
884 p_avlv_rec IN avlv_rec_type) IS
885 BEGIN
886 okl_avl_pvt.validate_row(
887 p_api_version,
888 p_init_msg_list,
889 x_return_status,
890 x_msg_count,
891 x_msg_data,
892 p_avlv_rec);
893 END validate_template;
894
895 PROCEDURE validate_template(
896 p_api_version IN NUMBER,
897 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
898 x_return_status OUT NOCOPY VARCHAR2,
899 x_msg_count OUT NOCOPY NUMBER,
900 x_msg_data OUT NOCOPY VARCHAR2,
901 p_avlv_tbl IN avlv_tbl_type) IS
902
903 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
904 i NUMBER := 0;
905
906 BEGIN
907
908 Okc_Api.init_msg_list(p_init_msg_list);
909 -- Make sure PL/SQL table has records in it before passing
910 IF (p_avlv_tbl.COUNT > 0) THEN
911 i := p_avlv_tbl.FIRST;
912 LOOP
913 validate_template(
914 p_api_version => p_api_version,
915 p_init_msg_list => Okc_Api.G_FALSE,
916 x_return_status => x_return_status,
917 x_msg_count => x_msg_count,
918 x_msg_data => x_msg_data,
919 p_avlv_rec => p_avlv_tbl(i));
920
921 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
922 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
923 l_overall_status := x_return_status;
924 END IF;
925 END IF;
926
927 EXIT WHEN (i = p_avlv_tbl.LAST);
928 i := p_avlv_tbl.NEXT(i);
929 END LOOP;
930 END IF;
931 x_return_status := l_overall_status;
932
933 END validate_template;
934
935 PROCEDURE create_tmpt_lines(
936 p_api_version IN NUMBER,
937 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
938 x_return_status OUT NOCOPY VARCHAR2,
939 x_msg_count OUT NOCOPY NUMBER,
940 x_msg_data OUT NOCOPY VARCHAR2,
941 p_atlv_rec IN atlv_rec_type,
942 x_atlv_rec OUT NOCOPY atlv_rec_type) IS
943 BEGIN
944 okl_atl_pvt.insert_row(
945 p_api_version,
946 p_init_msg_list,
947 x_return_status,
948 x_msg_count,
949 x_msg_data,
950 p_atlv_rec,
951 x_atlv_rec);
952
953 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
954 NULL;
955 -- Custom code by developer Update_Minor_Version(p_atlv_rec.dnz_chr_id);
956 END IF;
957
958 END create_tmpt_lines;
959
960 PROCEDURE create_tmpt_lines(
961 p_api_version IN NUMBER,
962 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
963 x_return_status OUT NOCOPY VARCHAR2,
964 x_msg_count OUT NOCOPY NUMBER,
965 x_msg_data OUT NOCOPY VARCHAR2,
966 p_atlv_tbl IN atlv_tbl_type,
967 x_atlv_tbl OUT NOCOPY atlv_tbl_type) IS
968
969 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
970 i NUMBER := 0;
971 BEGIN
972
973 Okc_Api.init_msg_list(p_init_msg_list);
974 -- Make sure PL/SQL table has records in it before passing
975 IF (p_atlv_tbl.COUNT > 0) THEN
976 i := p_atlv_tbl.FIRST;
977 LOOP
978 create_tmpt_lines (
979 p_api_version => p_api_version,
980 p_init_msg_list => Okc_Api.G_FALSE,
981 x_return_status => x_return_status,
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data,
984 p_atlv_rec => p_atlv_tbl(i),
985 x_atlv_rec => x_atlv_tbl(i));
986
987 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
988 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
989 l_overall_status := x_return_status;
990 END IF;
991 END IF;
992 EXIT WHEN (i = p_atlv_tbl.LAST);
993 i := p_atlv_tbl.NEXT(i);
994 END LOOP;
995 END IF;
996 x_return_status := l_overall_status;
997
998 END create_tmpt_lines;
999
1000 PROCEDURE lock_tmpt_lines(
1001 p_api_version IN NUMBER,
1002 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1003 x_return_status OUT NOCOPY VARCHAR2,
1004 x_msg_count OUT NOCOPY NUMBER,
1005 x_msg_data OUT NOCOPY VARCHAR2,
1006 p_atlv_rec IN atlv_rec_type) IS
1007 BEGIN
1008 okl_atl_pvt.lock_row(
1009 p_api_version,
1010 p_init_msg_list,
1011 x_return_status,
1012 x_msg_count,
1013 x_msg_data,
1014 p_atlv_rec);
1015 END lock_tmpt_lines;
1016
1017 PROCEDURE lock_tmpt_lines(
1018 p_api_version IN NUMBER,
1019 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1020 x_return_status OUT NOCOPY VARCHAR2,
1021 x_msg_count OUT NOCOPY NUMBER,
1022 x_msg_data OUT NOCOPY VARCHAR2,
1023 p_atlv_tbl IN atlv_tbl_type) IS
1024 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1025 i NUMBER := 0;
1026 BEGIN
1027
1028 Okc_Api.init_msg_list(p_init_msg_list);
1029 -- Make sure PL/SQL table has records in it before passing
1030 IF (p_atlv_tbl.COUNT > 0) THEN
1031 i := p_atlv_tbl.FIRST;
1032 LOOP
1033 lock_tmpt_lines (
1034 p_api_version => p_api_version,
1035 p_init_msg_list => Okc_Api.G_FALSE,
1036 x_return_status => x_return_status,
1037 x_msg_count => x_msg_count,
1038 x_msg_data => x_msg_data,
1039 p_atlv_rec => p_atlv_tbl(i));
1040
1041 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1042 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1043 l_overall_status := x_return_status;
1044 END IF;
1045 END IF;
1046 EXIT WHEN (i = p_atlv_tbl.LAST);
1047 i := p_atlv_tbl.NEXT(i);
1048 END LOOP;
1049 END IF;
1050 x_return_status := l_overall_status;
1051
1052 END lock_tmpt_lines;
1053
1054 PROCEDURE update_tmpt_lines(
1055 p_api_version IN NUMBER,
1056 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1057 x_return_status OUT NOCOPY VARCHAR2,
1058 x_msg_count OUT NOCOPY NUMBER,
1059 x_msg_data OUT NOCOPY VARCHAR2,
1060 p_atlv_rec IN atlv_rec_type,
1061 x_atlv_rec OUT NOCOPY atlv_rec_type) IS
1062 BEGIN
1063 okl_atl_pvt.update_row(
1064 p_api_version,
1065 p_init_msg_list,
1066 x_return_status,
1067 x_msg_count,
1068 x_msg_data,
1069 p_atlv_rec,
1070 x_atlv_rec);
1071
1072 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1073 NULL;
1074 -- Custom code by developer Update_Minor_Version(p_atlv_rec.dnz_chr_id);
1075 END IF;
1076 END update_tmpt_lines;
1077
1078 PROCEDURE update_tmpt_lines(
1079 p_api_version IN NUMBER,
1080 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1081 x_return_status OUT NOCOPY VARCHAR2,
1082 x_msg_count OUT NOCOPY NUMBER,
1083 x_msg_data OUT NOCOPY VARCHAR2,
1084 p_atlv_tbl IN atlv_tbl_type,
1085 x_atlv_tbl OUT NOCOPY atlv_tbl_type) IS
1086 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1087 i NUMBER := 0;
1088 BEGIN
1089
1090 Okc_Api.init_msg_list(p_init_msg_list);
1091 -- Make sure PL/SQL table has records in it before passing
1092 IF (p_atlv_tbl.COUNT > 0) THEN
1093 i := p_atlv_tbl.FIRST;
1094 LOOP
1095 update_tmpt_lines(
1096 p_api_version => p_api_version,
1097 p_init_msg_list => Okc_Api.G_FALSE,
1098 x_return_status => x_return_status,
1099 x_msg_count => x_msg_count,
1100 x_msg_data => x_msg_data,
1101 p_atlv_rec => p_atlv_tbl(i),
1102 x_atlv_rec => x_atlv_tbl(i));
1103
1104 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1105 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1106 l_overall_status := x_return_status;
1107 END IF;
1108 END IF;
1109
1110 EXIT WHEN (i = p_atlv_tbl.LAST);
1111 i := p_atlv_tbl.NEXT(i);
1112 END LOOP;
1113
1114 END IF;
1115
1116 x_return_status := l_overall_status;
1117
1118 END update_tmpt_lines;
1119
1120
1121 --Put custom code for cascade delete by developer
1122 PROCEDURE delete_tmpt_lines(
1123 p_api_version IN NUMBER,
1124 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1125 x_return_status OUT NOCOPY VARCHAR2,
1126 x_msg_count OUT NOCOPY NUMBER,
1127 x_msg_data OUT NOCOPY VARCHAR2,
1128 p_atlv_rec IN atlv_rec_type) IS
1129 i NUMBER :=0;
1130 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1131 BEGIN
1132 --Delete the Master
1133 okl_atl_pvt.delete_row(
1134 p_api_version,
1135 p_init_msg_list,
1136 x_return_status,
1137 x_msg_count,
1138 x_msg_data,
1139 p_atlv_rec);
1140
1141 END delete_tmpt_lines;
1142
1143 PROCEDURE delete_tmpt_lines(
1144 p_api_version IN NUMBER,
1145 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1146 x_return_status OUT NOCOPY VARCHAR2,
1147 x_msg_count OUT NOCOPY NUMBER,
1148 x_msg_data OUT NOCOPY VARCHAR2,
1149 p_atlv_tbl IN atlv_tbl_type) IS
1150 i NUMBER :=0;
1151 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1152 BEGIN
1153 --Initialize the return status
1154
1155 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1156
1157 IF (p_atlv_tbl.COUNT > 0) THEN
1158 i := p_atlv_tbl.FIRST;
1159 LOOP
1160 delete_tmpt_lines(
1161 p_api_version,
1162 p_init_msg_list,
1163 x_return_status,
1164 x_msg_count,
1165 x_msg_data,
1166 p_atlv_tbl(i));
1167
1168 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1169 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1170 l_overall_status := x_return_status;
1171 END IF;
1172 END IF;
1173
1174 EXIT WHEN (i = p_atlv_tbl.LAST);
1175 i := p_atlv_tbl.NEXT(i);
1176 END LOOP;
1177
1178 END IF;
1179 x_return_status := l_overall_Status;
1180
1181 END delete_tmpt_lines;
1182
1183 PROCEDURE validate_tmpt_lines(
1184 p_api_version IN NUMBER,
1185 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1186 x_return_status OUT NOCOPY VARCHAR2,
1187 x_msg_count OUT NOCOPY NUMBER,
1188 x_msg_data OUT NOCOPY VARCHAR2,
1189 p_atlv_rec IN atlv_rec_type) IS
1190 BEGIN
1191 okl_atl_pvt.validate_row(
1192 p_api_version,
1193 p_init_msg_list,
1194 x_return_status,
1195 x_msg_count,
1196 x_msg_data,
1197 p_atlv_rec);
1198 END validate_tmpt_lines;
1199
1200 PROCEDURE validate_tmpt_lines(
1201 p_api_version IN NUMBER,
1202 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1203 x_return_status OUT NOCOPY VARCHAR2,
1204 x_msg_count OUT NOCOPY NUMBER,
1205 x_msg_data OUT NOCOPY VARCHAR2,
1206 p_atlv_tbl IN atlv_tbl_type) IS
1207
1208 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1209 i NUMBER := 0;
1210
1211 BEGIN
1212
1213 Okc_Api.init_msg_list(p_init_msg_list);
1214 -- Make sure PL/SQL table has records in it before passing
1215 IF (p_atlv_tbl.COUNT > 0) THEN
1216 i := p_atlv_tbl.FIRST;
1217 LOOP
1218 validate_tmpt_lines(
1219 p_api_version => p_api_version,
1220 p_init_msg_list => Okc_Api.G_FALSE,
1221 x_return_status => x_return_status,
1222 x_msg_count => x_msg_count,
1223 x_msg_data => x_msg_data,
1224 p_atlv_rec => p_atlv_tbl(i));
1225
1226 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1227 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1228 l_overall_status := x_return_status;
1229 END IF;
1230 END IF;
1231
1232 EXIT WHEN (i = p_atlv_tbl.LAST);
1233 i := p_atlv_tbl.NEXT(i);
1234 END LOOP;
1235 END IF;
1236
1237 x_return_status := l_overall_status;
1238
1239 END validate_tmpt_lines;
1240
1241
1242 END okl_tmpt_set_pvt;