[Home] [Help]
PACKAGE BODY: APPS.OKC_STD_ARTICLE_PVT
Source
1 package body okc_std_article_pvt as
2 /* $Header: OKCCSAEB.pls 120.0 2005/05/25 22:30:18 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --Procedures pertaining to Setting up of a standard Article
7
8 PROCEDURE add_language IS
9 BEGIN
10 OKC_SAE_PVT.add_language;
11 OKC_SAV_PVT.add_language;
12 END add_language;
13
14 --Procedures pertaining to Std Article Objects
15
16 PROCEDURE Create_std_article(
17 p_api_version IN NUMBER,
18 p_init_msg_list IN VARCHAR2 ,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_saev_rec IN saev_rec_type,
23 p_savv_tbl IN savv_tbl_type,
24 p_saiv_tbl IN saiv_tbl_type,
25 p_samv_tbl IN samv_tbl_type,
26 x_saev_rec OUT NOCOPY saev_rec_type,
27 x_savv_tbl OUT NOCOPY savv_tbl_type,
28 x_saiv_tbl OUT NOCOPY saiv_tbl_type,
29 x_samv_tbl OUT NOCOPY samv_tbl_type) IS
30 l_savv_tbl savv_tbl_type := p_savv_tbl;
31 l_saiv_tbl saiv_tbl_type := p_saiv_tbl;
32 l_samv_tbl samv_tbl_type := p_samv_tbl;
33 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
34 i NUMBER := 0;
35 BEGIN
36 create_std_article(
37 p_api_version,
38 p_init_msg_list,
39 x_return_status,
40 x_msg_count,
41 x_msg_data,
42 p_saev_rec,
43 x_saev_rec);
44 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
45 raise G_EXCEPTION_HALT_VALIDATION;
46 ELSE
47 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
48 l_return_status := x_return_status;
49 END IF;
50 END IF;
51
52 IF (l_savv_tbl.COUNT > 0) THEN
53 i := l_savv_tbl.FIRST;
54 LOOP
55 l_savv_tbl(i).sae_id := x_saev_rec.id;
56 EXIT WHEN (i = l_savv_tbl.LAST);
57 i := l_savv_tbl.NEXT(i);
58 END LOOP;
59 END IF;
60
61 -- in case of saiv , it is assumed that sae_id and sae_id_for will be populated by the newly generated
62 --id in saev_rec. That is the l_saiv_tbl has already got sae_id_for
63 IF (l_saiv_tbl.COUNT > 0) THEN
64 i := l_saiv_tbl.FIRST;
65 LOOP
66 l_saiv_tbl(i).sae_id := x_saev_rec.id;
67 EXIT WHEN (i = l_saiv_tbl.LAST);
68 i := l_saiv_tbl.NEXT(i);
69 END LOOP;
70 END IF;
71
72 IF (l_samv_tbl.COUNT > 0) THEN
73 i := l_samv_tbl.FIRST;
74 LOOP
75 l_samv_tbl(i).sae_id := x_saev_rec.id;
76 EXIT WHEN (i = l_samv_tbl.LAST);
77 i := l_samv_tbl.NEXT(i);
78 END LOOP;
79 END IF;
80
81 --Now call complex apis for each child individually
82
83 create_std_art_version(
84 p_api_version,
85 p_init_msg_list,
86 x_return_status,
87 x_msg_count,
88 x_msg_data,
89 l_savv_tbl,
90 x_savv_tbl);
91 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
92 raise G_EXCEPTION_HALT_VALIDATION;
93 ELSE
94 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
95 l_return_status := x_return_status;
96 END IF;
97 END IF;
98
99 create_std_art_incmpt(
100 p_api_version,
101 p_init_msg_list,
102 x_return_status,
103 x_msg_count,
104 x_msg_data,
105 l_saiv_tbl,
106 x_saiv_tbl);
107
108 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
109 raise G_EXCEPTION_HALT_VALIDATION;
110 ELSE
111 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
112 l_return_status := x_return_status;
113 END IF;
114 END IF;
115
116 create_std_art_set_mem(
117 p_api_version,
118 p_init_msg_list,
119 x_return_status,
120 x_msg_count,
121 x_msg_data,
122 l_samv_tbl,
123 x_samv_tbl);
124 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
125 raise G_EXCEPTION_HALT_VALIDATION;
126 ELSE
127 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
128 l_return_status := x_return_status;
129 END IF;
130 END IF;
131
132 x_return_status := l_return_status;
133
134 EXCEPTION
135 WHEN G_EXCEPTION_HALT_VALIDATION THEN
136 NULL;
137 WHEN OTHERS THEN
138 OKC_API.set_message(p_app_name => g_app_name,
139 p_msg_name => g_unexpected_error,
140 p_token1 => g_sqlcode_token,
141 p_token1_value => sqlcode,
142 p_token2 => g_sqlerrm_token,
143 p_token2_value => sqlerrm);
144 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
145 END Create_std_article;
146
147
148
149 PROCEDURE Update_std_article(
150 p_api_version IN NUMBER,
151 p_init_msg_list IN VARCHAR2 ,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2,
155 p_saev_rec IN saev_rec_type,
156 p_savv_tbl IN savv_tbl_type,
157 p_saiv_tbl IN saiv_tbl_type,
158 p_samv_tbl IN samv_tbl_type,
159 x_saev_rec OUT NOCOPY saev_rec_type,
160 x_savv_tbl OUT NOCOPY savv_tbl_type,
161 x_saiv_tbl OUT NOCOPY saiv_tbl_type,
162 x_samv_tbl OUT NOCOPY samv_tbl_type) IS
163 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
164 BEGIN
165 Update_std_article(
166 p_api_version,
167 p_init_msg_list,
168 x_return_status,
169 x_msg_count,
170 x_msg_data,
171 p_saev_rec,
172 x_saev_rec);
173 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
174 raise G_EXCEPTION_HALT_VALIDATION;
175 ELSE
176 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
177 l_return_status := x_return_status;
178 END IF;
179 END IF;
180
181 --Now call complex apis for each child individually
182
183 Update_std_art_version(
184 p_api_version,
185 p_init_msg_list,
186 x_return_status,
187 x_msg_count,
188 x_msg_data,
189 p_savv_tbl,
190 x_savv_tbl);
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_std_art_incmpt(
200 p_api_version,
201 p_init_msg_list,
202 x_return_status,
203 x_msg_count,
204 x_msg_data,
205 p_saiv_tbl,
206 x_saiv_tbl);
207 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
208 raise G_EXCEPTION_HALT_VALIDATION;
209 ELSE
210 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
211 l_return_status := x_return_status;
212 END IF;
213 END IF;
214
215 Update_std_art_set_mem(
216 p_api_version,
217 p_init_msg_list,
218 x_return_status,
219 x_msg_count,
220 x_msg_data,
221 p_samv_tbl,
222 x_samv_tbl);
223 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
224 raise G_EXCEPTION_HALT_VALIDATION;
225 ELSE
226 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
227 l_return_status := x_return_status;
228 END IF;
229 END IF;
230
231 x_return_status := l_return_status;
232
233 EXCEPTION
234 WHEN G_EXCEPTION_HALT_VALIDATION THEN
235 NULL;
236 WHEN OTHERS THEN
237 OKC_API.set_message(p_app_name => g_app_name,
238 p_msg_name => g_unexpected_error,
239 p_token1 => g_sqlcode_token,
240 p_token1_value => sqlcode,
241 p_token2 => g_sqlerrm_token,
242 p_token2_value => sqlerrm);
243 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
244 END Update_std_article;
245
246
247
248 PROCEDURE Validate_std_article(
249 p_api_version IN NUMBER,
250 p_init_msg_list IN VARCHAR2 ,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 p_saev_rec IN saev_rec_type,
255 p_savv_tbl IN savv_tbl_type,
256 p_saiv_tbl IN saiv_tbl_type,
257 p_samv_tbl IN samv_tbl_type) IS
258 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
259 BEGIN
260 Validate_std_article(
261 p_api_version,
262 p_init_msg_list,
263 x_return_status,
264 x_msg_count,
265 x_msg_data,
266 p_saev_rec);
267 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
268 raise G_EXCEPTION_HALT_VALIDATION;
269 ELSE
270 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
271 l_return_status := x_return_status;
272 END IF;
273 END IF;
274
275 --Now call complex apis for each child individually
276
277 Validate_std_art_version(
278 p_api_version,
279 p_init_msg_list,
280 x_return_status,
281 x_msg_count,
282 x_msg_data,
283 p_savv_tbl);
284 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
285 raise G_EXCEPTION_HALT_VALIDATION;
286 ELSE
287 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
288 l_return_status := x_return_status;
289 END IF;
290 END IF;
291
292 Validate_std_art_incmpt(
293 p_api_version,
294 p_init_msg_list,
295 x_return_status,
296 x_msg_count,
297 x_msg_data,
298 p_saiv_tbl);
299 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
300 raise G_EXCEPTION_HALT_VALIDATION;
301 ELSE
302 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
303 l_return_status := x_return_status;
304 END IF;
305 END IF;
306
307 Validate_std_art_set_mem(
308 p_api_version,
309 p_init_msg_list,
310 x_return_status,
311 x_msg_count,
312 x_msg_data,
313 p_samv_tbl);
314 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
315 raise G_EXCEPTION_HALT_VALIDATION;
316 ELSE
317 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
318 l_return_status := x_return_status;
319 END IF;
320 END IF;
321
322 x_return_status := l_return_status;
323
324 EXCEPTION
325 WHEN G_EXCEPTION_HALT_VALIDATION THEN
326 NULL;
327 WHEN OTHERS THEN
328 OKC_API.set_message(p_app_name => g_app_name,
329 p_msg_name => g_unexpected_error,
330 p_token1 => g_sqlcode_token,
331 p_token1_value => sqlcode,
332 p_token2 => g_sqlerrm_token,
333 p_token2_value => sqlerrm);
334 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
335
336 END Validate_std_article;
337
338 PROCEDURE Create_std_article(
339 p_api_version IN NUMBER,
340 p_init_msg_list IN VARCHAR2 ,
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_count OUT NOCOPY NUMBER,
343 x_msg_data OUT NOCOPY VARCHAR2,
344 p_saev_tbl IN saev_tbl_type,
345 x_saev_tbl OUT NOCOPY saev_tbl_type) IS
346 BEGIN
347 OKC_SAE_PVT.insert_row(p_api_version,
348 p_init_msg_list,
349 x_return_status,
350 x_msg_count,
351 x_msg_data,
352 p_saev_tbl,
353 x_saev_tbl);
354 END Create_std_article;
355
356
357 PROCEDURE Create_std_article(
358 p_api_version IN NUMBER,
359 p_init_msg_list IN VARCHAR2 ,
360 x_return_status OUT NOCOPY VARCHAR2,
361 x_msg_count OUT NOCOPY NUMBER,
362 x_msg_data OUT NOCOPY VARCHAR2,
363 p_saev_rec IN saev_rec_type,
364 x_saev_rec OUT NOCOPY saev_rec_type) IS
365 BEGIN
366 OKC_SAE_PVT.insert_row(p_api_version,
367 p_init_msg_list,
368 x_return_status,
369 x_msg_count,
370 x_msg_data,
371 p_saev_rec,
372 x_saev_rec);
373 END Create_std_article;
374
375 PROCEDURE lock_std_article(
376 p_api_version IN NUMBER,
377 p_init_msg_list IN VARCHAR2 ,
378 x_return_status OUT NOCOPY VARCHAR2,
379 x_msg_count OUT NOCOPY NUMBER,
380 x_msg_data OUT NOCOPY VARCHAR2,
381 p_saev_tbl IN saev_tbl_type) IS
382 BEGIN
383 OKC_SAE_PVT.lock_row(p_api_version,
384 p_init_msg_list,
385 x_return_status,
386 x_msg_count,
387 x_msg_data,
388 p_saev_tbl);
389 END lock_std_article;
390
391
392 PROCEDURE lock_std_article(
393 p_api_version IN NUMBER,
394 p_init_msg_list IN VARCHAR2 ,
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398 p_saev_rec IN saev_rec_type) IS
399 BEGIN
400 OKC_SAE_PVT.lock_row(p_api_version,
401 p_init_msg_list,
402 x_return_status,
403 x_msg_count,
404 x_msg_data,
405 p_saev_rec);
406 END lock_std_article;
407
408 PROCEDURE update_std_article(
409 p_api_version IN NUMBER,
410 p_init_msg_list IN VARCHAR2 ,
411 x_return_status OUT NOCOPY VARCHAR2,
412 x_msg_count OUT NOCOPY NUMBER,
413 x_msg_data OUT NOCOPY VARCHAR2,
414 p_saev_tbl IN saev_tbl_type,
415 x_saev_tbl OUT NOCOPY saev_tbl_type) IS
416 BEGIN
417 OKC_SAE_PVT.update_row(p_api_version,
418 p_init_msg_list,
419 x_return_status,
420 x_msg_count,
421 x_msg_data,
422 p_saev_tbl,
423 x_saev_tbl);
424 END update_std_article;
425
426
427 PROCEDURE update_std_article(
428 p_api_version IN NUMBER,
429 p_init_msg_list IN VARCHAR2 ,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_msg_count OUT NOCOPY NUMBER,
432 x_msg_data OUT NOCOPY VARCHAR2,
433 p_saev_rec IN saev_rec_type,
434 x_saev_rec OUT NOCOPY saev_rec_type) IS
435 BEGIN
436 OKC_SAE_PVT.update_row(p_api_version,
437 p_init_msg_list,
438 x_return_status,
439 x_msg_count,
440 x_msg_data,
441 p_saev_rec,
442 x_saev_rec);
443 END update_std_article;
444
445
446 PROCEDURE delete_std_article(
447 p_api_version IN NUMBER,
448 p_init_msg_list IN VARCHAR2 ,
449 x_return_status OUT NOCOPY VARCHAR2,
450 x_msg_count OUT NOCOPY NUMBER,
451 x_msg_data OUT NOCOPY VARCHAR2,
452 p_saev_rec IN saev_rec_type) IS
453
454 Cursor l_ver(id number) is
455 select '1' from OKC_STD_ART_VERSIONS_B where sae_id=id;
456 Cursor l_set(id number) is
457 select '1' from OKC_STD_ART_SET_MEMS where sae_id=id;
458 Cursor l_incmpt(id number) is
459 select '1' from OKC_STD_ART_INCMPTS where sae_id=id;
460 l_count varchar2(1):='0';
461
462 BEGIN
463 x_return_status := OKC_API.G_RET_STS_SUCCESS;
464
465 IF p_saev_rec.id IS NOT NULL THEN
466 OPEN l_ver(p_saev_rec.id);
467 FETCH l_ver into l_count;
468 Close l_ver;
469 IF (l_COUNT='1') THEN
470 raise G_EXCEPTION_HALT_VALIDATION;
471 END IF;
472
473 OPEN l_set(p_saev_rec.id);
474 FETCH l_set into l_count;
475 Close l_set;
476 IF (l_COUNT='1') THEN
477 raise G_EXCEPTION_HALT_VALIDATION;
478 END IF;
479
480 OPEN l_incmpt(p_saev_rec.id);
481 FETCH l_incmpt into l_count;
482 Close l_incmpt;
483 IF (l_COUNT='1') THEN
484 raise G_EXCEPTION_HALT_VALIDATION;
485 END IF;
486 END IF;
487
488
489 OKC_SAE_PVT.delete_row(p_api_version,
490 p_init_msg_list,
491 x_return_status,
492 x_msg_count,
493 x_msg_data,
494 p_saev_rec);
495
496 EXCEPTION
497 WHEN G_EXCEPTION_HALT_VALIDATION THEN
498 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
499 p_msg_name => G_CHILD_DELETE);
500 x_return_status := OKC_API.G_RET_STS_ERROR;
501 WHEN OTHERS THEN
502 OKC_API.set_message(p_app_name => g_app_name,
503 p_msg_name => g_unexpected_error,
504 p_token1 => g_sqlcode_token,
505 p_token1_value => sqlcode,
506 p_token2 => g_sqlerrm_token,
507 p_token2_value => sqlerrm);
508 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
509 END delete_std_article;
510
511
512 PROCEDURE delete_std_article(
513 p_api_version IN NUMBER,
514 p_init_msg_list IN VARCHAR2 ,
515 x_return_status OUT NOCOPY VARCHAR2,
516 x_msg_count OUT NOCOPY NUMBER,
517 x_msg_data OUT NOCOPY VARCHAR2,
518 p_saev_tbl IN saev_tbl_type) IS
519 i number :=0;
520 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
521 BEGIN
522 x_return_status := OKC_API.G_RET_STS_SUCCESS;
523 IF (p_saev_tbl.COUNT > 0) THEN
524 i := p_saev_tbl.FIRST;
525 LOOP
526 delete_std_article(
527 p_api_version,
528 p_init_msg_list,
529 l_return_status,
530 x_msg_count,
531 x_msg_data,
532 p_saev_tbl(i));
533 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
534 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
535 x_return_status := l_return_status;
536 raise G_EXCEPTION_HALT_VALIDATION;
537 ELSE
538 x_return_status := l_return_status;
539 END IF;
540 END IF;
541 EXIT WHEN (i = p_saev_tbl.LAST);
542 i := p_saev_tbl.NEXT(i);
543 END LOOP;
544 END IF;
545 EXCEPTION
546 WHEN G_EXCEPTION_HALT_VALIDATION THEN
547 NULL;
548 WHEN OTHERS THEN
549 OKC_API.set_message(p_app_name => g_app_name,
550 p_msg_name => g_unexpected_error,
551 p_token1 => g_sqlcode_token,
552 p_token1_value => sqlcode,
553 p_token2 => g_sqlerrm_token,
554 p_token2_value => sqlerrm);
555 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
556
557 END delete_std_article;
558
559 PROCEDURE validate_std_article(
560 p_api_version IN NUMBER,
561 p_init_msg_list IN VARCHAR2 ,
562 x_return_status OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2,
565 p_saev_tbl IN saev_tbl_type) IS
566 BEGIN
567 OKC_SAE_PVT.validate_row(p_api_version,
568 p_init_msg_list,
569 x_return_status,
570 x_msg_count,
571 x_msg_data,
572 p_saev_tbl);
573 END validate_std_article;
574
575
576 PROCEDURE validate_std_article(
577 p_api_version IN NUMBER,
578 p_init_msg_list IN VARCHAR2 ,
579 x_return_status OUT NOCOPY VARCHAR2,
580 x_msg_count OUT NOCOPY NUMBER,
581 x_msg_data OUT NOCOPY VARCHAR2,
582 p_saev_rec IN saev_rec_type) IS
583 BEGIN
584 OKC_SAE_PVT.validate_row(p_api_version,
585 p_init_msg_list,
586 x_return_status,
587 x_msg_count,
588 x_msg_data,
589 p_saev_rec);
590 END validate_std_article;
591
592
593 PROCEDURE validate_name(
594 p_api_version IN NUMBER,
595 p_init_msg_list IN VARCHAR2 ,
596 x_return_status OUT NOCOPY VARCHAR2,
597 x_msg_count OUT NOCOPY NUMBER,
598 x_msg_data OUT NOCOPY VARCHAR2,
599 p_saev_rec IN saev_rec_type) IS
600
601 l_api_version CONSTANT NUMBER := 1;
602 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Name';
603 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
604 l_saev_rec saev_rec_type := p_saev_rec;
605 BEGIN
606 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
607 G_PKG_NAME,
608 p_init_msg_list,
609 l_api_version,
610 p_api_version,
611 '_PVT',
612 x_return_status);
613 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
614 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
615 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
616 RAISE OKC_API.G_EXCEPTION_ERROR;
617 END IF;
618 /*
619 OKC_UTIL.ADD_VIEW('OKC_STD_ARTICLES_V',x_return_status);
620 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
621 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
622 l_return_status := x_return_status;
623 RAISE G_EXCEPTION_HALT_VALIDATION;
624 ELSE
625 l_return_status := x_return_status; -- record that there was an error
626 END IF;
627 END IF;
628 */
629 --- Validate name
630 OKC_SAE_PVT.validate_name(p_saev_rec,l_return_status);
631 --- If any errors happen abort API
632 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
633 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
634 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
635 RAISE OKC_API.G_EXCEPTION_ERROR;
636 END IF;
637 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
638 EXCEPTION
639
640 WHEN G_EXCEPTION_HALT_VALIDATION THEN
641 null;
642
643 WHEN OKC_API.G_EXCEPTION_ERROR THEN
644 x_return_status := OKC_API.HANDLE_EXCEPTIONS
645 (
646 l_api_name,
647 G_PKG_NAME,
648 'OKC_API.G_RET_STS_ERROR',
649 x_msg_count,
650 x_msg_data,
651 '_PVT'
652 );
653 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
654 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
655 (
656 l_api_name,
657 G_PKG_NAME,
658 'OKC_API.G_RET_STS_UNEXP_ERROR',
659 x_msg_count,
660 x_msg_data,
661 '_PVT'
662 );
663 WHEN OTHERS THEN
664 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
665 (
666 l_api_name,
667 G_PKG_NAME,
668 'OTHERS',
669 x_msg_count,
670 x_msg_data,
671 '_PVT'
672 );
673 END validate_name;
674
675 PROCEDURE validate_no_k_attached(
676 p_saev_rec IN saev_rec_type,
677 x_return_status OUT NOCOPY VARCHAR2) IS
678
679 BEGIN
680
681 --- Validate that no contract is attached to the release getting updated
682 OKC_SAE_PVT.validate_no_k_attached(p_saev_rec,x_return_status);
683
684 END validate_no_k_attached;
685
686 PROCEDURE Create_std_art_version(
687 p_api_version IN NUMBER,
688 p_init_msg_list IN VARCHAR2 ,
689 x_return_status OUT NOCOPY VARCHAR2,
690 x_msg_count OUT NOCOPY NUMBER,
691 x_msg_data OUT NOCOPY VARCHAR2,
692 p_savv_tbl IN savv_tbl_type,
693 x_savv_tbl OUT NOCOPY savv_tbl_type) IS
694 BEGIN
695 OKC_SAV_PVT.insert_row(p_api_version,
696 p_init_msg_list,
697 x_return_status,
698 x_msg_count,
699 x_msg_data,
700 p_savv_tbl,
701 x_savv_tbl);
702 END Create_std_art_version;
703
704
705 PROCEDURE Create_std_art_version(
706 p_api_version IN NUMBER,
707 p_init_msg_list IN VARCHAR2 ,
708 x_return_status OUT NOCOPY VARCHAR2,
709 x_msg_count OUT NOCOPY NUMBER,
710 x_msg_data OUT NOCOPY VARCHAR2,
711 p_savv_rec IN savv_rec_type,
712 x_savv_rec OUT NOCOPY savv_rec_type) IS
713 BEGIN
714 OKC_SAV_PVT.insert_row(p_api_version,
715 p_init_msg_list,
716 x_return_status,
717 x_msg_count,
718 x_msg_data,
719 p_savv_rec,
720 x_savv_rec);
721 END Create_std_art_version;
722
723 PROCEDURE lock_std_art_version(
724 p_api_version IN NUMBER,
725 p_init_msg_list IN VARCHAR2 ,
726 x_return_status OUT NOCOPY VARCHAR2,
727 x_msg_count OUT NOCOPY NUMBER,
728 x_msg_data OUT NOCOPY VARCHAR2,
729 p_savv_tbl IN savv_tbl_type) IS
730 BEGIN
731 OKC_SAV_PVT.lock_row(p_api_version,
732 p_init_msg_list,
733 x_return_status,
734 x_msg_count,
735 x_msg_data,
736 p_savv_tbl);
737 END lock_std_art_version;
738
739
740 PROCEDURE lock_std_art_version(
741 p_api_version IN NUMBER,
742 p_init_msg_list IN VARCHAR2 ,
743 x_return_status OUT NOCOPY VARCHAR2,
744 x_msg_count OUT NOCOPY NUMBER,
745 x_msg_data OUT NOCOPY VARCHAR2,
746 p_savv_rec IN savv_rec_type) IS
747 BEGIN
748 OKC_SAV_PVT.lock_row(p_api_version,
749 p_init_msg_list,
750 x_return_status,
751 x_msg_count,
752 x_msg_data,
753 p_savv_rec);
754 END lock_std_art_version;
755
756 PROCEDURE update_std_art_version(
757 p_api_version IN NUMBER,
758 p_init_msg_list IN VARCHAR2 ,
759 x_return_status OUT NOCOPY VARCHAR2,
760 x_msg_count OUT NOCOPY NUMBER,
761 x_msg_data OUT NOCOPY VARCHAR2,
762 p_savv_tbl IN savv_tbl_type,
763 x_savv_tbl OUT NOCOPY savv_tbl_type) IS
764 BEGIN
765 OKC_SAV_PVT.update_row(p_api_version,
766 p_init_msg_list,
767 x_return_status,
768 x_msg_count,
769 x_msg_data,
770 p_savv_tbl,
771 x_savv_tbl);
772 END update_std_art_version;
773
774
775 PROCEDURE update_std_art_version(
776 p_api_version IN NUMBER,
777 p_init_msg_list IN VARCHAR2 ,
778 x_return_status OUT NOCOPY VARCHAR2,
779 x_msg_count OUT NOCOPY NUMBER,
780 x_msg_data OUT NOCOPY VARCHAR2,
781 p_savv_rec IN savv_rec_type,
782 x_savv_rec OUT NOCOPY savv_rec_type) IS
783 BEGIN
784 OKC_SAV_PVT.update_row(p_api_version,
785 p_init_msg_list,
786 x_return_status,
787 x_msg_count,
788 x_msg_data,
789 p_savv_rec,
790 x_savv_rec);
791 END update_std_art_version;
792
793 PROCEDURE delete_std_art_version(
794 p_api_version IN NUMBER,
795 p_init_msg_list IN VARCHAR2 ,
796 x_return_status OUT NOCOPY VARCHAR2,
797 x_msg_count OUT NOCOPY NUMBER,
798 x_msg_data OUT NOCOPY VARCHAR2,
799 p_savv_tbl IN savv_tbl_type) IS
800 BEGIN
801 OKC_SAV_PVT.delete_row(p_api_version,
802 p_init_msg_list,
803 x_return_status,
804 x_msg_count,
805 x_msg_data,
806 p_savv_tbl);
807 END delete_std_art_version;
808
809 PROCEDURE delete_std_art_version(
810 p_api_version IN NUMBER,
811 p_init_msg_list IN VARCHAR2 ,
812 x_return_status OUT NOCOPY VARCHAR2,
813 x_msg_count OUT NOCOPY NUMBER,
814 x_msg_data OUT NOCOPY VARCHAR2,
815 p_savv_rec IN savv_rec_type) IS
816 BEGIN
817 OKC_SAV_PVT.delete_row(p_api_version,
818 p_init_msg_list,
819 x_return_status,
820 x_msg_count,
821 x_msg_data,
822 p_savv_rec);
823 END delete_std_art_version;
824
825 PROCEDURE validate_std_art_version(
826 p_api_version IN NUMBER,
827 p_init_msg_list IN VARCHAR2 ,
828 x_return_status OUT NOCOPY VARCHAR2,
829 x_msg_count OUT NOCOPY NUMBER,
830 x_msg_data OUT NOCOPY VARCHAR2,
831 p_savv_tbl IN savv_tbl_type) IS
832 BEGIN
833 OKC_SAV_PVT.validate_row(p_api_version,
834 p_init_msg_list,
835 x_return_status,
836 x_msg_count,
837 x_msg_data,
838 p_savv_tbl);
839 END validate_std_art_version;
840
841
842 PROCEDURE validate_std_art_version(
843 p_api_version IN NUMBER,
844 p_init_msg_list IN VARCHAR2 ,
845 x_return_status OUT NOCOPY VARCHAR2,
846 x_msg_count OUT NOCOPY NUMBER,
847 x_msg_data OUT NOCOPY VARCHAR2,
848 p_savv_rec IN savv_rec_type) IS
849 BEGIN
850 OKC_SAV_PVT.validate_row(p_api_version,
851 p_init_msg_list,
852 x_return_status,
853 x_msg_count,
854 x_msg_data,
855 p_savv_rec);
856 END validate_std_art_version;
857
858 PROCEDURE validate_sav_release(
859 p_api_version IN NUMBER,
860 p_init_msg_list IN VARCHAR2 ,
861 x_return_status OUT NOCOPY VARCHAR2,
862 x_msg_count OUT NOCOPY NUMBER,
863 x_msg_data OUT NOCOPY VARCHAR2,
864 p_savv_rec IN savv_rec_type) IS
865
866 l_api_version CONSTANT NUMBER := 1;
867 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Sav_Release';
868 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
869 l_savv_rec savv_rec_type := p_savv_rec;
870 BEGIN
871 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
872 G_PKG_NAME,
873 p_init_msg_list,
874 l_api_version,
875 p_api_version,
876 '_PVT',
877 x_return_status);
878 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
879 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
880 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
881 RAISE OKC_API.G_EXCEPTION_ERROR;
882 END IF;
883
884 /*
885 OKC_UTIL.ADD_VIEW('OKC_STD_ART_VERSIONS_V',x_return_status);
886 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
887 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
888 l_return_status := x_return_status;
889 RAISE G_EXCEPTION_HALT_VALIDATION;
890 ELSE
891 l_return_status := x_return_status; -- record that there was an error
892 END IF;
893 END IF;
894 */
895
896 --- Validate sav_release
897 OKC_SAV_PVT.validate_sav_release(p_savv_rec,l_return_status);
898 --- If any errors happen abort API
899 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
900 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
901 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
902 RAISE OKC_API.G_EXCEPTION_ERROR;
903 END IF;
904
905 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
906 EXCEPTION
907 WHEN G_EXCEPTION_HALT_VALIDATION THEN
908 null;
909
910 WHEN OKC_API.G_EXCEPTION_ERROR THEN
911 x_return_status := OKC_API.HANDLE_EXCEPTIONS
912 (
913 l_api_name,
914 G_PKG_NAME,
915 'OKC_API.G_RET_STS_ERROR',
916 x_msg_count,
917 x_msg_data,
918 '_PVT'
919 );
920 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
921 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
922 (
923 l_api_name,
924 G_PKG_NAME,
925 'OKC_API.G_RET_STS_UNEXP_ERROR',
926 x_msg_count,
927 x_msg_data,
928 '_PVT'
929 );
930 WHEN OTHERS THEN
931 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
932 (
933 l_api_name,
934 G_PKG_NAME,
935 'OTHERS',
936 x_msg_count,
937 x_msg_data,
938 '_PVT'
939 );
940 END validate_sav_release;
941
942 PROCEDURE validate_date_active(
943 p_api_version IN NUMBER,
944 p_init_msg_list IN VARCHAR2 ,
945 x_return_status OUT NOCOPY VARCHAR2,
946 x_msg_count OUT NOCOPY NUMBER,
947 x_msg_data OUT NOCOPY VARCHAR2,
948 p_savv_rec IN savv_rec_type) IS
949
950 l_api_version CONSTANT NUMBER := 1;
951 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Date_Active';
952 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
953 l_savv_rec savv_rec_type := p_savv_rec;
954 BEGIN
955 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
956 G_PKG_NAME,
957 p_init_msg_list,
958 l_api_version,
959 p_api_version,
960 '_PVT',
961 x_return_status);
962 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
963 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
964 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
965 RAISE OKC_API.G_EXCEPTION_ERROR;
966 END IF;
967
968 --- Validate date_active
969 OKC_SAV_PVT.validate_date_active(p_savv_rec,l_return_status);
970 --- If any errors happen abort API
971 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
972 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
973 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
974 RAISE OKC_API.G_EXCEPTION_ERROR;
975 END IF;
976 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
977 EXCEPTION
978
979 WHEN OKC_API.G_EXCEPTION_ERROR THEN
980 x_return_status := OKC_API.HANDLE_EXCEPTIONS
981 (
982 l_api_name,
983 G_PKG_NAME,
984 'OKC_API.G_RET_STS_ERROR',
985 x_msg_count,
986 x_msg_data,
987 '_PVT'
988 );
989 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
990 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
991 (
992 l_api_name,
993 G_PKG_NAME,
994 'OKC_API.G_RET_STS_UNEXP_ERROR',
995 x_msg_count,
996 x_msg_data,
997 '_PVT'
998 );
999 WHEN OTHERS THEN
1000 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1001 (
1002 l_api_name,
1003 G_PKG_NAME,
1004 'OTHERS',
1005 x_msg_count,
1006 x_msg_data,
1007 '_PVT'
1008 );
1009 END validate_date_active;
1010
1011 PROCEDURE validate_no_k_attached(
1012 p_savv_rec IN savv_rec_type,
1013 x_return_status OUT NOCOPY VARCHAR2) IS
1014
1015 BEGIN
1016
1017 --- Validate that no contract is attached to the release getting updated
1018 OKC_SAV_PVT.validate_no_k_attached(p_savv_rec,x_return_status);
1019
1020 END validate_no_k_attached;
1021
1022 PROCEDURE validate_latest(
1023 p_savv_rec IN savv_rec_type,
1024 x_return_status OUT NOCOPY VARCHAR2) IS
1025
1026 BEGIN
1027
1028 --- Validate that the release getting updated is the latest release
1029 OKC_SAV_PVT.validate_latest(p_savv_rec,x_return_status);
1030
1031 END validate_latest;
1032
1033 PROCEDURE validate_updatable(
1034 p_api_version IN NUMBER,
1035 p_init_msg_list IN VARCHAR2 ,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2,
1039 p_savv_rec IN savv_rec_type) IS
1040
1041 l_api_version CONSTANT NUMBER := 1;
1042 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Updatable';
1043 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1044 l_savv_rec savv_rec_type := p_savv_rec;
1045 BEGIN
1046 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1047 G_PKG_NAME,
1048 p_init_msg_list,
1049 l_api_version,
1050 p_api_version,
1051 '_PVT',
1052 x_return_status);
1053 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1054 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1055 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1056 RAISE OKC_API.G_EXCEPTION_ERROR;
1057 END IF;
1058
1059 --- Validate that the date_active and text are updatable
1060 OKC_SAV_PVT.validate_updatable(p_savv_rec,l_return_status);
1061 --- If any errors happen abort API
1062 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1063 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1064 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1065 RAISE OKC_API.G_EXCEPTION_ERROR;
1066 END IF;
1067 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1068 EXCEPTION
1069 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1070 null;
1071
1072
1073 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1074 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1075 (
1076 l_api_name,
1077 G_PKG_NAME,
1078 'OKC_API.G_RET_STS_ERROR',
1079 x_msg_count,
1080 x_msg_data,
1081 '_PVT'
1082 );
1083 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1084 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1085 (
1086 l_api_name,
1087 G_PKG_NAME,
1088 'OKC_API.G_RET_STS_UNEXP_ERROR',
1089 x_msg_count,
1090 x_msg_data,
1091 '_PVT'
1092 );
1093 WHEN OTHERS THEN
1094 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1095 (
1096 l_api_name,
1097 G_PKG_NAME,
1098 'OTHERS',
1099 x_msg_count,
1100 x_msg_data,
1101 '_PVT'
1102 );
1103 END validate_updatable;
1104
1105 /*
1106 -- No check length is called as the form column length made equal to
1107 -- table column length -- JOHN
1108 --
1109 PROCEDURE validate_short_description(
1110 p_api_version IN NUMBER,
1111 p_init_msg_list IN VARCHAR2 ,
1112 x_return_status OUT NOCOPY VARCHAR2,
1113 x_msg_count OUT NOCOPY NUMBER,
1114 x_msg_data OUT NOCOPY VARCHAR2,
1115 p_savv_rec IN savv_rec_type) IS
1116
1117 l_api_version CONSTANT NUMBER := 1;
1118 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Short_Description';
1119 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1120 l_savv_rec savv_rec_type := p_savv_rec;
1121 BEGIN
1122 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1123 G_PKG_NAME,
1124 p_init_msg_list,
1125 l_api_version,
1126 p_api_version,
1127 '_PVT',
1128 x_return_status);
1129 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1130 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1131 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1132 RAISE OKC_API.G_EXCEPTION_ERROR;
1133 END IF;
1134
1135 OKC_UTIL.ADD_VIEW('OKC_STD_ART_VERSIONS_V',x_return_status);
1136 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1137 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1138 l_return_status := x_return_status;
1139 RAISE G_EXCEPTION_HALT_VALIDATION;
1140 ELSE
1141 l_return_status := x_return_status; -- record that there was an error
1142 END IF;
1143 END IF;
1144
1145 --- Validate short_description
1146 OKC_SAV_PVT.validate_short_description(p_savv_rec,l_return_status);
1147 --- If any errors happen abort API
1148 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1149 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1150 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1151 RAISE OKC_API.G_EXCEPTION_ERROR;
1152 END IF;
1153
1154 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1155 EXCEPTION
1156 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1157 null;
1158
1159 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1160 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1161 (
1162 l_api_name,
1163 G_PKG_NAME,
1164 'OKC_API.G_RET_STS_ERROR',
1165 x_msg_count,
1166 x_msg_data,
1167 '_PVT'
1168 );
1169 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1170 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1171 (
1172 l_api_name,
1173 G_PKG_NAME,
1174 'OKC_API.G_RET_STS_UNEXP_ERROR',
1175 x_msg_count,
1176 x_msg_data,
1177 '_PVT'
1178 );
1179 WHEN OTHERS THEN
1180 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1181 (
1182 l_api_name,
1183 G_PKG_NAME,
1184 'OTHERS',
1185 x_msg_count,
1186 x_msg_data,
1187 '_PVT'
1188 );
1189 END validate_short_description;
1190 */
1191
1192 PROCEDURE Create_std_art_incmpt(
1193 p_api_version IN NUMBER,
1194 p_init_msg_list IN VARCHAR2 ,
1195 x_return_status OUT NOCOPY VARCHAR2,
1196 x_msg_count OUT NOCOPY NUMBER,
1197 x_msg_data OUT NOCOPY VARCHAR2,
1198 p_saiv_tbl IN saiv_tbl_type,
1199 x_saiv_tbl OUT NOCOPY saiv_tbl_type) IS
1200 BEGIN
1201 OKC_SAI_PVT.insert_row(p_api_version,
1202 p_init_msg_list,
1203 x_return_status,
1204 x_msg_count,
1205 x_msg_data,
1206 p_saiv_tbl,
1207 x_saiv_tbl);
1208 END Create_std_art_incmpt;
1209
1210
1211 PROCEDURE Create_std_art_incmpt(
1212 p_api_version IN NUMBER,
1213 p_init_msg_list IN VARCHAR2 ,
1214 x_return_status OUT NOCOPY VARCHAR2,
1215 x_msg_count OUT NOCOPY NUMBER,
1216 x_msg_data OUT NOCOPY VARCHAR2,
1217 p_saiv_rec IN saiv_rec_type,
1218 x_saiv_rec OUT NOCOPY saiv_rec_type) IS
1219
1220 l_saiv_rec saiv_rec_type;
1221
1222 BEGIN
1223 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
1224
1225 l_saiv_rec:=p_saiv_rec;
1226 l_saiv_rec.sae_id:=p_saiv_rec.sae_id_for;
1227 l_saiv_rec.sae_id_for:=p_saiv_rec.sae_id;
1228
1229 OKC_SAI_PVT.validate_unique(l_saiv_rec,x_return_status);
1230 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1231 OKC_SAI_PVT.insert_row(p_api_version,
1232 p_init_msg_list,
1233 x_return_status,
1234 x_msg_count,
1235 x_msg_data,
1236 p_saiv_rec,
1237 x_saiv_rec);
1238
1239 END IF;
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 OKC_API.set_message(p_app_name => g_app_name,
1243 p_msg_name => g_unexpected_error,
1244 p_token1 => g_sqlcode_token,
1245 p_token1_value => sqlcode,
1246 p_token2 => g_sqlerrm_token,
1247 p_token2_value => sqlerrm);
1248 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1249
1250 END Create_std_art_incmpt;
1251
1252 PROCEDURE lock_std_art_incmpt(
1253 p_api_version IN NUMBER,
1254 p_init_msg_list IN VARCHAR2 ,
1255 x_return_status OUT NOCOPY VARCHAR2,
1256 x_msg_count OUT NOCOPY NUMBER,
1257 x_msg_data OUT NOCOPY VARCHAR2,
1258 p_saiv_tbl IN saiv_tbl_type) IS
1259 BEGIN
1260 OKC_SAI_PVT.lock_row(p_api_version,
1261 p_init_msg_list,
1262 x_return_status,
1263 x_msg_count,
1264 x_msg_data,
1265 p_saiv_tbl);
1266 END lock_std_art_incmpt;
1267
1268
1269 PROCEDURE lock_std_art_incmpt(
1270 p_api_version IN NUMBER,
1271 p_init_msg_list IN VARCHAR2 ,
1272 x_return_status OUT NOCOPY VARCHAR2,
1273 x_msg_count OUT NOCOPY NUMBER,
1274 x_msg_data OUT NOCOPY VARCHAR2,
1275 p_saiv_rec IN saiv_rec_type) IS
1276
1277 l_saiv_rec saiv_rec_type;
1278 CURSOR l_sai_csr IS
1279 SELECT '1'
1280 FROM okc_std_art_incmpts sai
1281 WHERE sai.sae_id = p_saiv_rec.sae_id_for and sai.sae_id_for = p_saiv_rec.sae_id;
1282 l_dummy_var VARCHAR2(1):='0';
1283 BEGIN
1284 --check if the opposite combination record exists
1285 OPEN l_sai_csr;
1286 FETCH l_sai_csr into l_dummy_var;
1287 CLOSE l_sai_csr;
1288 IF (l_dummy_var<>'1') Then --if opposite combination record doesnot exist then
1289 --lock the record if the combination exists like this
1290 OKC_SAI_PVT.lock_row(p_api_version,
1291 p_init_msg_list,
1292 x_return_status,
1293 x_msg_count,
1294 x_msg_data,
1295 p_saiv_rec);
1296 ELSE
1297 l_saiv_rec:=p_saiv_rec;
1298 l_saiv_rec.sae_id:=p_saiv_rec.sae_id_for;
1299 l_saiv_rec.sae_id_for:=p_saiv_rec.sae_id;
1300 -- lock the opposite combination as the record might exist the opposite combination way
1301 OKC_SAI_PVT.lock_row(p_api_version,
1302 p_init_msg_list,
1303 x_return_status,
1304 x_msg_count,
1305 x_msg_data,
1306 l_saiv_rec);
1307 END IF;
1308
1309 EXCEPTION
1310 WHEN OTHERS THEN
1311 OKC_API.set_message(p_app_name => g_app_name,
1312 p_msg_name => g_unexpected_error,
1313 p_token1 => g_sqlcode_token,
1314 p_token1_value => sqlcode,
1315 p_token2 => g_sqlerrm_token,
1316 p_token2_value => sqlerrm);
1317 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1318 END lock_std_art_incmpt;
1319
1320 PROCEDURE update_std_art_incmpt(
1321 p_api_version IN NUMBER,
1322 p_init_msg_list IN VARCHAR2 ,
1323 x_return_status OUT NOCOPY VARCHAR2,
1324 x_msg_count OUT NOCOPY NUMBER,
1325 x_msg_data OUT NOCOPY VARCHAR2,
1326 p_saiv_tbl IN saiv_tbl_type,
1327 x_saiv_tbl OUT NOCOPY saiv_tbl_type) IS
1328 BEGIN
1329 OKC_SAI_PVT.update_row(p_api_version,
1330 p_init_msg_list,
1331 x_return_status,
1332 x_msg_count,
1333 x_msg_data,
1334 p_saiv_tbl,
1335 x_saiv_tbl);
1336 END update_std_art_incmpt;
1337
1338 PROCEDURE update_std_art_incmpt(
1339 p_api_version IN NUMBER,
1340 p_init_msg_list IN VARCHAR2 ,
1341 x_return_status OUT NOCOPY VARCHAR2,
1342 x_msg_count OUT NOCOPY NUMBER,
1343 x_msg_data OUT NOCOPY VARCHAR2,
1344 p_saiv_rec IN saiv_rec_type,
1345 x_saiv_rec OUT NOCOPY saiv_rec_type) IS
1346
1347 l_api_version CONSTANT NUMBER := 1;
1348 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Std_Art_Incmpt';
1349 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1350 l_saiv_rec saiv_rec_type;
1351 CURSOR l_sai_csr IS
1352 SELECT '1'
1353 FROM okc_std_art_incmpts sai
1354 WHERE sai.sae_id = p_saiv_rec.sae_id_for and sai.sae_id_for = p_saiv_rec.sae_id;
1355
1356 l_dummy_var VARCHAR2(1):='0';
1357 BEGIN
1358
1359 --check if the opposite combination record exists
1360 OPEN l_sai_csr;
1361 FETCH l_sai_csr into l_dummy_var;
1362 CLOSE l_sai_csr;
1363 IF (l_dummy_var<>'1') Then --if opposite combination record doesnot exist then
1364 --update the record if the combination exists like this
1365 OKC_SAI_PVT.update_row(p_api_version,
1366 p_init_msg_list,
1367 x_return_status,
1368 x_msg_count,
1369 x_msg_data,
1370 p_saiv_rec,
1371 x_saiv_rec);
1372 ELSE
1373 l_saiv_rec:=p_saiv_rec;
1374 l_saiv_rec.sae_id:=p_saiv_rec.sae_id_for;
1375 l_saiv_rec.sae_id_for:=p_saiv_rec.sae_id;
1376 -- update the opposite combination as the record might exist the opposite combination way
1377 OKC_SAI_PVT.update_row(p_api_version,
1378 p_init_msg_list,
1379 x_return_status,
1380 x_msg_count,
1381 x_msg_data,
1382 l_saiv_rec,
1383 x_saiv_rec);
1384 END IF;
1385
1386 EXCEPTION
1387 WHEN OTHERS THEN
1388 OKC_API.set_message(p_app_name => g_app_name,
1389 p_msg_name => g_unexpected_error,
1390 p_token1 => g_sqlcode_token,
1391 p_token1_value => sqlcode,
1392 p_token2 => g_sqlerrm_token,
1393 p_token2_value => sqlerrm);
1394 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1395
1396 END update_std_art_incmpt;
1397
1398 PROCEDURE delete_std_art_incmpt(
1399 p_api_version IN NUMBER,
1400 p_init_msg_list IN VARCHAR2 ,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 p_saiv_tbl IN saiv_tbl_type) IS
1405 BEGIN
1406 OKC_SAI_PVT.delete_row(p_api_version,
1407 p_init_msg_list,
1408 x_return_status,
1409 x_msg_count,
1410 x_msg_data,
1411 p_saiv_tbl);
1412 END delete_std_art_incmpt;
1413
1414 PROCEDURE delete_std_art_incmpt(
1415 p_api_version IN NUMBER,
1416 p_init_msg_list IN VARCHAR2 ,
1417 x_return_status OUT NOCOPY VARCHAR2,
1418 x_msg_count OUT NOCOPY NUMBER,
1419 x_msg_data OUT NOCOPY VARCHAR2,
1420 p_saiv_rec IN saiv_rec_type) IS
1421
1422 l_saiv_rec saiv_rec_type;
1423 CURSOR l_sai_csr IS
1424 SELECT '1'
1425 FROM okc_std_art_incmpts sai
1426 WHERE sai.sae_id = p_saiv_rec.sae_id_for and sai.sae_id_for = p_saiv_rec.sae_id;
1427 l_dummy_var VARCHAR2(1):='0';
1428 BEGIN
1429 --check if the opposite combination record exists
1430 OPEN l_sai_csr;
1431 FETCH l_sai_csr into l_dummy_var;
1432 CLOSE l_sai_csr;
1433 IF (l_dummy_var<>'1') Then --if opposite combination record doesnot exist then
1434 --delete the record if the combination exists like this
1435 OKC_SAI_PVT.delete_row(p_api_version,
1436 p_init_msg_list,
1437 x_return_status,
1438 x_msg_count,
1439 x_msg_data,
1440 p_saiv_rec);
1441 ELSE
1442 l_saiv_rec:=p_saiv_rec;
1443 l_saiv_rec.sae_id:=p_saiv_rec.sae_id_for;
1444 l_saiv_rec.sae_id_for:=p_saiv_rec.sae_id;
1445 -- try to delete the opposite combination as the record might exist the opposite combination way
1446 OKC_SAI_PVT.delete_row(p_api_version,
1447 p_init_msg_list,
1448 x_return_status,
1449 x_msg_count,
1450 x_msg_data,
1451 l_saiv_rec);
1452 END IF;
1453
1454 EXCEPTION
1455 WHEN OTHERS THEN
1456 OKC_API.set_message(p_app_name => g_app_name,
1457 p_msg_name => g_unexpected_error,
1458 p_token1 => g_sqlcode_token,
1459 p_token1_value => sqlcode,
1460 p_token2 => g_sqlerrm_token,
1461 p_token2_value => sqlerrm);
1462 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1463
1464 END delete_std_art_incmpt;
1465
1466
1467 PROCEDURE validate_std_art_incmpt(
1468 p_api_version IN NUMBER,
1469 p_init_msg_list IN VARCHAR2 ,
1470 x_return_status OUT NOCOPY VARCHAR2,
1471 x_msg_count OUT NOCOPY NUMBER,
1472 x_msg_data OUT NOCOPY VARCHAR2,
1473 p_saiv_tbl IN saiv_tbl_type) IS
1474 BEGIN
1475 OKC_SAI_PVT.validate_row(p_api_version,
1476 p_init_msg_list,
1477 x_return_status,
1478 x_msg_count,
1479 x_msg_data,
1480 p_saiv_tbl);
1481 END validate_std_art_incmpt;
1482
1483
1484 PROCEDURE validate_std_art_incmpt(
1485 p_api_version IN NUMBER,
1486 p_init_msg_list IN VARCHAR2 ,
1487 x_return_status OUT NOCOPY VARCHAR2,
1488 x_msg_count OUT NOCOPY NUMBER,
1489 x_msg_data OUT NOCOPY VARCHAR2,
1490 p_saiv_rec IN saiv_rec_type) IS
1491
1492 l_api_version CONSTANT NUMBER := 1;
1493 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Std_Art_Incmpt';
1494 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1495 l_saiv_rec saiv_rec_type;
1496 CURSOR l_sai_csr IS
1497 SELECT '1'
1498 FROM okc_std_art_incmpts sai
1499 WHERE sai.sae_id = p_saiv_rec.sae_id_for and sai.sae_id_for = p_saiv_rec.sae_id;
1500
1501 l_dummy_var VARCHAR2(1):='0';
1502 BEGIN
1503 --check if the opposite combination record exists
1504 OPEN l_sai_csr;
1505 FETCH l_sai_csr into l_dummy_var;
1506 CLOSE l_sai_csr;
1507 IF (l_dummy_var<>'1') Then --if opposite combination record doesnot exist then
1508 --validate the record if the combination exists like this
1509 OKC_SAI_PVT.validate_row(p_api_version,
1510 p_init_msg_list,
1511 x_return_status,
1512 x_msg_count,
1513 x_msg_data,
1514 p_saiv_rec);
1515 ELSE
1516 l_saiv_rec:=p_saiv_rec;
1517 l_saiv_rec.sae_id:=p_saiv_rec.sae_id_for;
1518 l_saiv_rec.sae_id_for:=p_saiv_rec.sae_id;
1519 -- validate the opposite combination as the record might exist the opposite combination way
1520 OKC_SAI_PVT.validate_row(p_api_version,
1521 p_init_msg_list,
1522 x_return_status,
1523 x_msg_count,
1524 x_msg_data,
1525 l_saiv_rec);
1526 END IF;
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 OKC_API.set_message(p_app_name => g_app_name,
1531 p_msg_name => g_unexpected_error,
1532 p_token1 => g_sqlcode_token,
1533 p_token1_value => sqlcode,
1534 p_token2 => g_sqlerrm_token,
1535 p_token2_value => sqlerrm);
1536 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1537
1538 END validate_std_art_incmpt;
1539
1540 PROCEDURE validate_unique(
1541 p_api_version IN NUMBER,
1542 p_init_msg_list IN VARCHAR2 ,
1543 x_return_status OUT NOCOPY VARCHAR2,
1544 x_msg_count OUT NOCOPY NUMBER,
1545 x_msg_data OUT NOCOPY VARCHAR2,
1546 p_saiv_rec IN saiv_rec_type) IS
1547
1548 l_api_version CONSTANT NUMBER := 1;
1549 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Unique';
1550 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1551 l_saiv_rec saiv_rec_type := p_saiv_rec;
1552
1553
1554 BEGIN
1555 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1556 G_PKG_NAME,
1557 p_init_msg_list,
1558 l_api_version,
1559 p_api_version,
1560 '_PVT',
1561 x_return_status);
1562 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1563 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1564 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1565 RAISE OKC_API.G_EXCEPTION_ERROR;
1566 END IF;
1567
1568 --check if the record exists
1569 OKC_SAI_PVT.validate_unique(p_saiv_rec,l_return_status);
1570
1571 --- If any errors happen abort API
1572 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1573 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1574 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1575 RAISE OKC_API.G_EXCEPTION_ERROR;
1576 END IF;
1577
1578 l_saiv_rec:=p_saiv_rec;
1579 l_saiv_rec.sae_id:=p_saiv_rec.sae_id_for;
1580 l_saiv_rec.sae_id_for:=p_saiv_rec.sae_id;
1581 -- validate opposite combination as the record might exist the opposite combination way
1582
1583 OKC_SAI_PVT.validate_unique(l_saiv_rec,l_return_status);
1584 --- If any errors happen abort API
1585 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1586 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1587 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1588 RAISE OKC_API.G_EXCEPTION_ERROR;
1589 END IF;
1590
1591 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1592 EXCEPTION
1593
1594 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1595 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1596 (
1597 l_api_name,
1598 G_PKG_NAME,
1599 'OKC_API.G_RET_STS_ERROR',
1600 x_msg_count,
1601 x_msg_data,
1602 '_PVT'
1603 );
1604 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1605 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1606 (
1607 l_api_name,
1608 G_PKG_NAME,
1609 'OKC_API.G_RET_STS_UNEXP_ERROR',
1610 x_msg_count,
1611 x_msg_data,
1612 '_PVT'
1613 );
1614 WHEN OTHERS THEN
1615 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1616 (
1617 l_api_name,
1618 G_PKG_NAME,
1619 'OTHERS',
1620 x_msg_count,
1621 x_msg_data,
1622 '_PVT'
1623 );
1624 END validate_unique;
1625
1626 PROCEDURE create_std_art_classing(
1627 p_api_version IN NUMBER,
1628 p_init_msg_list IN VARCHAR2 ,
1629 x_return_status OUT NOCOPY VARCHAR2,
1630 x_msg_count OUT NOCOPY NUMBER,
1631 x_msg_data OUT NOCOPY VARCHAR2,
1632 p_sacv_tbl IN sacv_tbl_type,
1633 x_sacv_tbl OUT NOCOPY sacv_tbl_type) IS
1634 BEGIN
1635 OKC_SAC_PVT.insert_row(p_api_version,
1636 p_init_msg_list,
1637 x_return_status,
1638 x_msg_count,
1639 x_msg_data,
1640 p_sacv_tbl,
1641 x_sacv_tbl);
1642 END Create_std_art_classing;
1643
1644 PROCEDURE create_std_art_classing(
1645 p_api_version IN NUMBER,
1646 p_init_msg_list IN VARCHAR2 ,
1647 x_return_status OUT NOCOPY VARCHAR2,
1648 x_msg_count OUT NOCOPY NUMBER,
1649 x_msg_data OUT NOCOPY VARCHAR2,
1650 p_sacv_rec IN sacv_rec_type,
1651 x_sacv_rec OUT NOCOPY sacv_rec_type) IS
1652 BEGIN
1653 OKC_SAC_PVT.insert_row(p_api_version,
1654 p_init_msg_list,
1655 x_return_status,
1656 x_msg_count,
1657 x_msg_data,
1658 p_sacv_rec,
1659 x_sacv_rec);
1660 END create_std_art_classing;
1661
1662 PROCEDURE lock_std_art_classing(
1663 p_api_version IN NUMBER,
1664 p_init_msg_list IN VARCHAR2 ,
1665 x_return_status OUT NOCOPY VARCHAR2,
1666 x_msg_count OUT NOCOPY NUMBER,
1667 x_msg_data OUT NOCOPY VARCHAR2,
1668 p_sacv_tbl IN sacv_tbl_type)IS
1669 BEGIN
1670 OKC_SAC_PVT.lock_row(p_api_version,
1671 p_init_msg_list,
1672 x_return_status,
1673 x_msg_count,
1674 x_msg_data,
1675 p_sacv_tbl);
1676 END lock_std_art_classing;
1677
1678
1679 PROCEDURE lock_std_art_classing(
1680 p_api_version IN NUMBER,
1681 p_init_msg_list IN VARCHAR2 ,
1682 x_return_status OUT NOCOPY VARCHAR2,
1683 x_msg_count OUT NOCOPY NUMBER,
1684 x_msg_data OUT NOCOPY VARCHAR2,
1685 p_sacv_rec IN sacv_rec_type) IS
1686 BEGIN
1687 OKC_SAC_PVT.lock_row(p_api_version,
1688 p_init_msg_list,
1689 x_return_status,
1690 x_msg_count,
1691 x_msg_data,
1692 p_sacv_rec);
1693 END lock_std_art_classing;
1694
1695
1696 PROCEDURE update_std_art_classing(
1697 p_api_version IN NUMBER,
1698 p_init_msg_list IN VARCHAR2 ,
1699 x_return_status OUT NOCOPY VARCHAR2,
1700 x_msg_count OUT NOCOPY NUMBER,
1701 x_msg_data OUT NOCOPY VARCHAR2,
1702 p_sacv_tbl IN sacv_tbl_type,
1703 x_sacv_tbl OUT NOCOPY sacv_tbl_type) IS
1704 BEGIN
1705 OKC_SAC_PVT.update_row(p_api_version,
1706 p_init_msg_list,
1707 x_return_status,
1708 x_msg_count,
1709 x_msg_data,
1710 p_sacv_tbl,
1711 x_sacv_tbl);
1712 END update_std_art_classing;
1713
1714
1715 PROCEDURE update_std_art_classing(
1716 p_api_version IN NUMBER,
1717 p_init_msg_list IN VARCHAR2 ,
1718 x_return_status OUT NOCOPY VARCHAR2,
1719 x_msg_count OUT NOCOPY NUMBER,
1720 x_msg_data OUT NOCOPY VARCHAR2,
1721 p_sacv_rec IN sacv_rec_type,
1722 x_sacv_rec OUT NOCOPY sacv_rec_type) IS
1723 BEGIN
1724 OKC_SAC_PVT.update_row(p_api_version,
1725 p_init_msg_list,
1726 x_return_status,
1727 x_msg_count,
1728 x_msg_data,
1729 p_sacv_rec,
1730 x_sacv_rec);
1731 END update_std_art_classing;
1732
1733 PROCEDURE delete_std_art_classing(
1734 p_api_version IN NUMBER,
1735 p_init_msg_list IN VARCHAR2 ,
1736 x_return_status OUT NOCOPY VARCHAR2,
1737 x_msg_count OUT NOCOPY NUMBER,
1738 x_msg_data OUT NOCOPY VARCHAR2,
1739 p_sacv_tbl IN sacv_tbl_type) IS
1740 BEGIN
1741 OKC_SAC_PVT.delete_row(p_api_version,
1742 p_init_msg_list,
1743 x_return_status,
1744 x_msg_count,
1745 x_msg_data,
1746 p_sacv_tbl);
1747 END delete_std_art_classing;
1748
1749
1750 PROCEDURE delete_std_art_classing(
1751 p_api_version IN NUMBER,
1752 p_init_msg_list IN VARCHAR2 ,
1753 x_return_status OUT NOCOPY VARCHAR2,
1754 x_msg_count OUT NOCOPY NUMBER,
1755 x_msg_data OUT NOCOPY VARCHAR2,
1756 p_sacv_rec IN sacv_rec_type) IS
1757 BEGIN
1758 OKC_SAC_PVT.delete_row(p_api_version,
1759 p_init_msg_list,
1760 x_return_status,
1761 x_msg_count,
1762 x_msg_data,
1763 p_sacv_rec);
1764 END delete_std_art_classing;
1765
1766 PROCEDURE validate_std_art_classing(
1767 p_api_version IN NUMBER,
1768 p_init_msg_list IN VARCHAR2 ,
1769 x_return_status OUT NOCOPY VARCHAR2,
1770 x_msg_count OUT NOCOPY NUMBER,
1771 x_msg_data OUT NOCOPY VARCHAR2,
1772 p_sacv_tbl IN sacv_tbl_type) IS
1773 BEGIN
1774 OKC_SAC_PVT.validate_row(p_api_version,
1775 p_init_msg_list,
1776 x_return_status,
1777 x_msg_count,
1778 x_msg_data,
1779 p_sacv_tbl);
1780 END validate_std_art_classing;
1781
1782
1783 PROCEDURE validate_std_art_classing(
1784 p_api_version IN NUMBER,
1785 p_init_msg_list IN VARCHAR2 ,
1786 x_return_status OUT NOCOPY VARCHAR2,
1787 x_msg_count OUT NOCOPY NUMBER,
1788 x_msg_data OUT NOCOPY VARCHAR2,
1789 p_sacv_rec IN sacv_rec_type) IS
1790 BEGIN
1791 OKC_SAC_PVT.validate_row(p_api_version,
1792 p_init_msg_list,
1793 x_return_status,
1794 x_msg_count,
1795 x_msg_data,
1796 p_sacv_rec);
1797 END validate_std_art_classing;
1798
1799 PROCEDURE validate_price_type(
1800 p_api_version IN NUMBER,
1801 p_init_msg_list IN VARCHAR2 ,
1802 x_return_status OUT NOCOPY VARCHAR2,
1803 x_msg_count OUT NOCOPY NUMBER,
1804 x_msg_data OUT NOCOPY VARCHAR2,
1805 p_sacv_rec IN sacv_rec_type) IS
1806
1807 l_api_version CONSTANT NUMBER := 1;
1808 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Price_Type';
1809 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1810 l_sacv_rec sacv_rec_type := p_sacv_rec;
1811 BEGIN
1812 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1813 G_PKG_NAME,
1814 p_init_msg_list,
1815 l_api_version,
1816 p_api_version,
1817 '_PVT',
1818 x_return_status);
1819 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1820 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1821 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1822 RAISE OKC_API.G_EXCEPTION_ERROR;
1823 END IF;
1824
1825 /*
1826 OKC_UTIL.ADD_VIEW('OKC_STD_ART_CLASSINGS_V',x_return_status);
1827 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1828 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1829 l_return_status := x_return_status;
1830 RAISE G_EXCEPTION_HALT_VALIDATION;
1831 ELSE
1832 l_return_status := x_return_status; -- record that there was an error
1833 END IF;
1834 END IF;
1835 */
1836
1837 --- Validate price_type
1838 OKC_SAC_PVT.validate_price_type(p_sacv_rec,l_return_status);
1839 --- If any errors happen abort API
1840 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1841 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1842 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1843 RAISE OKC_API.G_EXCEPTION_ERROR;
1844 END IF;
1845 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1846 EXCEPTION
1847 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1848 null;
1849
1850 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1851 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1852 (
1853 l_api_name,
1854 G_PKG_NAME,
1855 'OKC_API.G_RET_STS_ERROR',
1856 x_msg_count,
1857 x_msg_data,
1858 '_PVT'
1859 );
1860 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1861 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1862 (
1863 l_api_name,
1864 G_PKG_NAME,
1865 'OKC_API.G_RET_STS_UNEXP_ERROR',
1866 x_msg_count,
1867 x_msg_data,
1868 '_PVT'
1869 );
1870 WHEN OTHERS THEN
1871 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1872 (
1873 l_api_name,
1874 G_PKG_NAME,
1875 'OTHERS',
1876 x_msg_count,
1877 x_msg_data,
1878 '_PVT'
1879 );
1880 END validate_price_type;
1881
1882 PROCEDURE validate_scs_code(
1883 p_api_version IN NUMBER,
1884 p_init_msg_list IN VARCHAR2 ,
1885 x_return_status OUT NOCOPY VARCHAR2,
1886 x_msg_count OUT NOCOPY NUMBER,
1887 x_msg_data OUT NOCOPY VARCHAR2,
1888 p_sacv_rec IN sacv_rec_type) IS
1889
1890 l_api_version CONSTANT NUMBER := 1;
1891 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_SCS_Code';
1892 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1893 l_sacv_rec sacv_rec_type := p_sacv_rec;
1894 BEGIN
1895 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1896 G_PKG_NAME,
1897 p_init_msg_list,
1898 l_api_version,
1899 p_api_version,
1900 '_PVT',
1901 x_return_status);
1902 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1903 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1904 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1905 RAISE OKC_API.G_EXCEPTION_ERROR;
1906 END IF;
1907
1908 /*
1909 OKC_UTIL.ADD_VIEW('OKC_STD_ART_CLASSINGS_V',x_return_status);
1910 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1911 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1912 l_return_status := x_return_status;
1913 RAISE G_EXCEPTION_HALT_VALIDATION;
1914 ELSE
1915 l_return_status := x_return_status; -- record that there was an error
1916 END IF;
1917 END IF;
1918 */
1919
1920 --- Validate scs_code
1921 OKC_SAC_PVT.validate_scs_code(p_sacv_rec,l_return_status);
1922 --- If any errors happen abort API
1923 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1924 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1925 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1926 RAISE OKC_API.G_EXCEPTION_ERROR;
1927 END IF;
1928 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1929 EXCEPTION
1930 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1931 null;
1932
1933 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1934 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1935 (
1936 l_api_name,
1937 G_PKG_NAME,
1938 'OKC_API.G_RET_STS_ERROR',
1939 x_msg_count,
1940 x_msg_data,
1941 '_PVT'
1942 );
1943 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1944 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1945 (
1946 l_api_name,
1947 G_PKG_NAME,
1948 'OKC_API.G_RET_STS_UNEXP_ERROR',
1949 x_msg_count,
1950 x_msg_data,
1951 '_PVT'
1952 );
1953 WHEN OTHERS THEN
1954 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1955 (
1956 l_api_name,
1957 G_PKG_NAME,
1958 'OTHERS',
1959 x_msg_count,
1960 x_msg_data,
1961 '_PVT'
1962 );
1963 END validate_scs_code;
1964
1965
1966 PROCEDURE create_std_art_set_mem(
1967 p_api_version IN NUMBER,
1968 p_init_msg_list IN VARCHAR2 ,
1969 x_return_status OUT NOCOPY VARCHAR2,
1970 x_msg_count OUT NOCOPY NUMBER,
1971 x_msg_data OUT NOCOPY VARCHAR2,
1972 p_samv_tbl IN samv_tbl_type,
1973 x_samv_tbl OUT NOCOPY samv_tbl_type) IS
1974 BEGIN
1975 OKC_SAM_PVT.Insert_row(p_api_version,
1976 p_init_msg_list,
1977 x_return_status,
1978 x_msg_count,
1979 x_msg_data,
1980 p_samv_tbl,
1981 x_samv_tbl);
1982 END create_std_art_set_mem;
1983
1984
1985 PROCEDURE create_std_art_set_mem(
1986 p_api_version IN NUMBER,
1987 p_init_msg_list IN VARCHAR2 ,
1988 x_return_status OUT NOCOPY VARCHAR2,
1989 x_msg_count OUT NOCOPY NUMBER,
1990 x_msg_data OUT NOCOPY VARCHAR2,
1991 p_samv_rec IN samv_rec_type,
1992 x_samv_rec OUT NOCOPY samv_rec_type) IS
1993 BEGIN
1994 OKC_SAM_PVT.Insert_row(p_api_version,
1995 p_init_msg_list,
1996 x_return_status,
1997 x_msg_count,
1998 x_msg_data,
1999 p_samv_rec,
2000 x_samv_rec);
2001 END create_std_art_set_mem;
2002
2003
2004 PROCEDURE lock_std_art_set_mem(
2005 p_api_version IN NUMBER,
2006 p_init_msg_list IN VARCHAR2 ,
2007 x_return_status OUT NOCOPY VARCHAR2,
2008 x_msg_count OUT NOCOPY NUMBER,
2009 x_msg_data OUT NOCOPY VARCHAR2,
2010 p_samv_tbl IN samv_tbl_type) IS
2011 BEGIN
2012 OKC_SAM_PVT.lock_row(p_api_version,
2013 p_init_msg_list,
2014 x_return_status,
2015 x_msg_count,
2016 x_msg_data,
2017 p_samv_tbl);
2018 END lock_std_art_set_mem;
2019
2020
2021 PROCEDURE lock_std_art_set_mem(
2022 p_api_version IN NUMBER,
2023 p_init_msg_list IN VARCHAR2 ,
2024 x_return_status OUT NOCOPY VARCHAR2,
2025 x_msg_count OUT NOCOPY NUMBER,
2026 x_msg_data OUT NOCOPY VARCHAR2,
2027 p_samv_rec IN samv_rec_type) IS
2028 BEGIN
2029 OKC_SAM_PVT.lock_row(p_api_version,
2030 p_init_msg_list,
2031 x_return_status,
2032 x_msg_count,
2033 x_msg_data,
2034 p_samv_rec);
2035 END lock_std_art_set_mem;
2036
2037 PROCEDURE update_std_art_set_mem(
2038 p_api_version IN NUMBER,
2039 p_init_msg_list IN VARCHAR2 ,
2040 x_return_status OUT NOCOPY VARCHAR2,
2041 x_msg_count OUT NOCOPY NUMBER,
2042 x_msg_data OUT NOCOPY VARCHAR2,
2043 p_samv_tbl IN samv_tbl_type,
2044 x_samv_tbl OUT NOCOPY samv_tbl_type) IS
2045 BEGIN
2046 OKC_SAM_PVT.update_row(p_api_version,
2047 p_init_msg_list,
2048 x_return_status,
2049 x_msg_count,
2050 x_msg_data,
2051 p_samv_tbl,
2052 x_samv_tbl);
2053 END update_std_art_set_mem;
2054
2055 PROCEDURE update_std_art_set_mem(
2056 p_api_version IN NUMBER,
2057 p_init_msg_list IN VARCHAR2 ,
2058 x_return_status OUT NOCOPY VARCHAR2,
2059 x_msg_count OUT NOCOPY NUMBER,
2060 x_msg_data OUT NOCOPY VARCHAR2,
2061 p_samv_rec IN samv_rec_type,
2062 x_samv_rec OUT NOCOPY samv_rec_type) IS
2063 BEGIN
2064 OKC_SAM_PVT.update_row(p_api_version,
2065 p_init_msg_list,
2066 x_return_status,
2067 x_msg_count,
2068 x_msg_data,
2069 p_samv_rec,
2070 x_samv_rec);
2071 END update_std_art_set_mem;
2072
2073 PROCEDURE delete_std_art_set_mem(
2074 p_api_version IN NUMBER,
2075 p_init_msg_list IN VARCHAR2 ,
2076 x_return_status OUT NOCOPY VARCHAR2,
2077 x_msg_count OUT NOCOPY NUMBER,
2078 x_msg_data OUT NOCOPY VARCHAR2,
2079 p_samv_tbl IN samv_tbl_type) IS
2080 BEGIN
2081 OKC_SAM_PVT.delete_row(p_api_version,
2082 p_init_msg_list,
2083 x_return_status,
2084 x_msg_count,
2085 x_msg_data,
2086 p_samv_tbl);
2087 END delete_std_art_set_mem;
2088
2089 PROCEDURE delete_std_art_set_mem(
2090 p_api_version IN NUMBER,
2091 p_init_msg_list IN VARCHAR2 ,
2092 x_return_status OUT NOCOPY VARCHAR2,
2093 x_msg_count OUT NOCOPY NUMBER,
2094 x_msg_data OUT NOCOPY VARCHAR2,
2095 p_samv_rec IN samv_rec_type) IS
2096 BEGIN
2097 OKC_SAM_PVT.delete_row(p_api_version,
2098 p_init_msg_list,
2099 x_return_status,
2100 x_msg_count,
2101 x_msg_data,
2102 p_samv_rec);
2103 END delete_std_art_set_mem;
2104
2105 PROCEDURE validate_std_art_set_mem(
2106 p_api_version IN NUMBER,
2107 p_init_msg_list IN VARCHAR2 ,
2108 x_return_status OUT NOCOPY VARCHAR2,
2109 x_msg_count OUT NOCOPY NUMBER,
2110 x_msg_data OUT NOCOPY VARCHAR2,
2111 p_samv_tbl IN samv_tbl_type) IS
2112 BEGIN
2113 OKC_SAM_PVT.validate_row(p_api_version,
2114 p_init_msg_list,
2115 x_return_status,
2116 x_msg_count,
2117 x_msg_data,
2118 p_samv_tbl);
2119 END validate_std_art_set_mem;
2120
2121 PROCEDURE validate_std_art_set_mem(
2122 p_api_version IN NUMBER,
2123 p_init_msg_list IN VARCHAR2 ,
2124 x_return_status OUT NOCOPY VARCHAR2,
2125 x_msg_count OUT NOCOPY NUMBER,
2126 x_msg_data OUT NOCOPY VARCHAR2,
2127 p_samv_rec IN samv_rec_type) IS
2128 BEGIN
2129 OKC_SAM_PVT.validate_row(p_api_version,
2130 p_init_msg_list,
2131 x_return_status,
2132 x_msg_count,
2133 x_msg_data,
2134 p_samv_rec);
2135 END validate_std_art_set_mem;
2136
2137
2138 PROCEDURE validate_unique(
2139 p_api_version IN NUMBER,
2140 p_init_msg_list IN VARCHAR2 ,
2141 x_return_status OUT NOCOPY VARCHAR2,
2142 x_msg_count OUT NOCOPY NUMBER,
2143 x_msg_data OUT NOCOPY VARCHAR2,
2144 p_samv_rec IN samv_rec_type) IS
2145
2146 l_api_version CONSTANT NUMBER := 1;
2147 l_api_name CONSTANT VARCHAR2(30) := 'V_Validate_Unique';
2148 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2149 l_samv_rec samv_rec_type := p_samv_rec;
2150 BEGIN
2151 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2152 G_PKG_NAME,
2153 p_init_msg_list,
2154 l_api_version,
2155 p_api_version,
2156 '_PVT',
2157 x_return_status);
2158 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2159 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2160 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2161 RAISE OKC_API.G_EXCEPTION_ERROR;
2162 END IF;
2163
2164 --- Validate unique
2165 OKC_SAM_PVT.validate_unique(p_samv_rec,l_return_status);
2166 --- If any errors happen abort API
2167 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2168 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2169 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2170 RAISE OKC_API.G_EXCEPTION_ERROR;
2171 END IF;
2172 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2173 EXCEPTION
2174
2175 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2176 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2177 (
2178 l_api_name,
2179 G_PKG_NAME,
2180 'OKC_API.G_RET_STS_ERROR',
2181 x_msg_count,
2182 x_msg_data,
2183 '_PVT'
2184 );
2185 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2186 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2187 (
2188 l_api_name,
2189 G_PKG_NAME,
2190 'OKC_API.G_RET_STS_UNEXP_ERROR',
2191 x_msg_count,
2192 x_msg_data,
2193 '_PVT'
2194 );
2195 WHEN OTHERS THEN
2196 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2197 (
2198 l_api_name,
2199 G_PKG_NAME,
2200 'OTHERS',
2201 x_msg_count,
2202 x_msg_data,
2203 '_PVT'
2204 );
2205 END validate_unique;
2206
2207 FUNCTION used_in_contracts
2208 (
2209 p_sav_sae_id IN okc_k_articles_b.sav_sae_id%TYPE,
2210 p_sav_sav_release IN okc_k_articles_b.sav_sav_release%TYPE
2211 )
2212 RETURN VARCHAR2 IS
2213 /*
2214 This function is used in view okc_kol_std_art_lib_v
2215 This function returns 'Y' if
2216 The current article release is used in contract
2217 If this is 'Y' we disable delete for the release
2218 */
2219
2220 l_count NUMBER;
2221
2222 CURSOR csr_cnt_k IS
2223 SELECT COUNT(*)
2224 FROM okc_k_articles_v
2225 WHERE sav_sae_id = p_sav_sae_id
2226 AND sav_sav_release = p_sav_sav_release ;
2227
2228 BEGIN
2229
2230 OPEN csr_cnt_k;
2231 FETCH csr_cnt_k INTO l_count;
2232 CLOSE csr_cnt_k;
2233
2234 -- if not used in contracts delete is allowed
2235 IF NVL(l_count,0) = 0 THEN
2236 RETURN 'N' ;
2237 ELSE
2238 RETURN 'Y';
2239 END IF;
2240 END used_in_contracts;
2241
2242 FUNCTION empclob
2243 RETURN CLOB IS
2244
2245 c1 CLOB;
2246
2247 BEGIN
2248
2249 DBMS_LOB.CREATETEMPORARY(c1,true);
2250 DBMS_LOB.OPEN(c1,dbms_lob.lob_readwrite);
2251 DBMS_LOB.WRITE(c1,1,1,' ');
2252 RETURN c1;
2253
2254 END empclob;
2255
2256
2257 FUNCTION latest_release
2258 (
2259 p_sav_sae_id IN okc_k_articles_b.sav_sae_id%TYPE
2260 )
2261 RETURN VARCHAR2 IS
2262
2263
2264 l_latest_release okc_std_art_versions_b.sav_release%TYPE := '';
2265
2266 /*
2267 We check the latest release based on the following
2268 1. date_active
2269 2. creation_date
2270
2271 Also the date_active MUST BE LESS THEN OR EQUAL TO sysdate
2272 i.e future dated articles cannot be latest release
2273 */
2274
2275 CURSOR csr_latest_release IS
2276 SELECT sav_release
2277 FROM okc_std_art_versions_b
2278 WHERE sae_id = p_sav_sae_id
2279 AND date_active <= sysdate
2280 ORDER BY date_active DESC, creation_date DESC ;
2281
2282 BEGIN
2283
2284 OPEN csr_latest_release;
2285 FETCH csr_latest_release INTO l_latest_release;
2286 CLOSE csr_latest_release;
2287
2288 RETURN l_latest_release;
2289
2290 END latest_release;
2291
2292 FUNCTION latest_release
2293 (
2294 p_sav_sae_id IN okc_k_articles_b.sav_sae_id%TYPE,
2295 p_sav_sav_release IN okc_k_articles_b.sav_sav_release%TYPE
2296 )
2297 RETURN VARCHAR2 IS
2298 /*
2299 This function is used in view okc_kol_std_art_latest_rel_v
2300 This function returns 'Y' if
2301 The current article release is latest release
2302 */
2303
2304 l_latest_release okc_std_art_versions_b.sav_release%TYPE := '';
2305
2306 BEGIN
2307 l_latest_release := latest_release(p_sav_sae_id);
2308
2309 IF l_latest_release = p_sav_sav_release THEN
2310 RETURN 'Y' ;
2311 ELSE
2312 RETURN 'N';
2313 END IF;
2314 END latest_release;
2315
2316 FUNCTION latest_or_future_release
2317 (
2318 p_sav_sae_id IN okc_k_articles_b.sav_sae_id%TYPE,
2319 p_sav_sav_release IN okc_k_articles_b.sav_sav_release%TYPE,
2320 p_date_active IN okc_std_art_versions_b.date_active%TYPE
2321 )
2322 RETURN VARCHAR2 IS
2323 /*
2324 This function is used in view okc_kol_std_art_lib_v
2325 This function returns 'Y' if the current release is latest or future dated.
2326 This is used in view okc_kol_std_art_lib_v to decide if an article can be updated
2327
2328 Article can be update if :
2329 1. It is NOT used in any contracts
2330 2. It is the latest release OR future dated release.
2331 */
2332
2333 l_latest_release okc_std_art_versions_b.sav_release%TYPE := '';
2334
2335 BEGIN
2336
2337 -- check if future release
2338 IF p_date_active > sysdate THEN
2339 RETURN 'Y' ;
2340 END IF;
2341
2342 -- check if latest release
2343 l_latest_release := latest_release(p_sav_sae_id);
2344
2345 IF l_latest_release = p_sav_sav_release THEN
2346 RETURN 'Y' ;
2347 ELSE
2348 RETURN 'N';
2349 END IF;
2350 END latest_or_future_release;
2351
2352
2353 -- Bug 3188215 KOL: BACKWARD COMPATIBILITY CHANGES
2354 -- Introduced the new api fundtion.
2355 FUNCTION art_used_in_contracts
2356 (
2357 p_sav_sae_id IN okc_k_articles_b.sav_sae_id%TYPE,
2358 p_article_version_number IN okc_article_versions.article_version_number%TYPE
2359 )
2360 RETURN VARCHAR2 IS
2361 /*
2362 This function is used in view okc_kol_std_art_lib_v
2363 This function returns 'Y' if
2364 The current article version is used in contract
2365 If this is 'Y' we disable delete for the release
2366 */
2367
2368 l_count NUMBER;
2369
2370 CURSOR csr_cnt_k IS
2371 SELECT COUNT(*)
2372 FROM okc_article_versions a, okc_k_articles_b b
2373 WHERE b.sav_sae_id = p_sav_sae_id
2374 AND b.sav_sae_id = a.article_id
2375 AND ( a.article_version_number = p_article_version_number OR
2376 a.sav_release = to_char(p_article_version_number) );
2377
2378 BEGIN
2379
2380 OPEN csr_cnt_k;
2381 FETCH csr_cnt_k INTO l_count;
2382 CLOSE csr_cnt_k;
2383
2384 -- if not used in contracts delete is allowed
2385 IF NVL(l_count,0) = 0 THEN
2386 RETURN 'N' ;
2387 ELSE
2388 RETURN 'Y';
2389 END IF;
2390 END art_used_in_contracts;
2391
2392 -- Bug 3188215 KOL: BACKWARD COMPATIBILITY CHANGES
2393 -- Introduced the new api fundtion.
2394 FUNCTION latest_art_release
2395 (
2396 p_sav_sae_id IN okc_k_articles_b.sav_sae_id%TYPE
2397 )
2398 RETURN VARCHAR2 IS
2399
2400
2401 l_latest_release okc_article_versions.sav_release%TYPE := ' ';
2402
2403 /*
2404 We check the latest release based on the following
2405 1. date_active/start_date
2406 2. creation_date
2407
2408 Also the date_active MUST BE LESS THEN OR EQUAL TO sysdate
2409 i.e future dated articles cannot be latest release
2410 */
2411
2412 CURSOR csr_latest_release IS
2413 SELECT NVL(sav_release, to_char(article_version_number))
2414 FROM okc_article_versions
2415 WHERE article_id = p_sav_sae_id
2416 AND start_date <= sysdate
2417 ORDER BY start_date DESC, creation_date DESC ;
2418
2419 BEGIN
2420
2421 OPEN csr_latest_release;
2422 FETCH csr_latest_release INTO l_latest_release;
2423 CLOSE csr_latest_release;
2424
2425 RETURN l_latest_release;
2426
2427 END latest_art_release;
2428
2429 -- Bug 3188215 KOL: BACKWARD COMPATIBILITY CHANGES
2430 -- Introduced the new api fundtion.
2431 FUNCTION latest_art_release
2432 (
2433 p_sav_sae_id IN okc_k_articles_b.sav_sae_id%TYPE,
2434 p_article_version_number IN okc_article_versions.article_version_number%TYPE
2435 )
2436 RETURN VARCHAR2 IS
2437 /*
2438 This function is used in view okc_kol_std_art_latest_rel_v
2439 This function returns 'Y' if
2440 The current article version is latest release
2441 */
2442
2443 l_latest_release okc_article_versions.sav_release%TYPE := ' ';
2444
2445 BEGIN
2446 l_latest_release := latest_art_release(p_sav_sae_id);
2447
2448 IF l_latest_release = to_char(p_article_version_number) THEN
2449 RETURN 'Y' ;
2450 ELSE
2451 RETURN 'N';
2452 END IF;
2453 END latest_art_release;
2454
2455
2456 -- Bug 3188215 KOL: BACKWARD COMPATIBILITY CHANGES
2457 -- Introduced the new api fundtion.
2458 FUNCTION latest_or_future_art_release
2459 (
2460 p_sav_sae_id IN okc_k_articles_b.sav_sae_id%TYPE,
2461 p_article_version_number IN okc_article_versions.article_version_number%TYPE,
2462 p_date_active IN okc_article_versions.start_date%TYPE
2463 )
2464 RETURN VARCHAR2 IS
2465 /*
2466 This function is used in view okc_kol_std_art_lib_v
2467 This function returns 'Y' if the current version is latest or future dated.
2468 This is used in view okc_kol_std_art_lib_v to decide if an article can be updated
2469
2470 Article can be update if :
2471 1. It is NOT used in any contracts
2472 2. It is the latest release OR future dated release.
2473 */
2474
2475 l_latest_release okc_article_versions.sav_release%TYPE := ' ';
2476
2477 BEGIN
2478
2479 -- check if future release
2480 IF p_date_active > sysdate THEN
2481 RETURN 'Y' ;
2482 END IF;
2483
2484 -- check if latest release
2485 l_latest_release := latest_art_release(p_sav_sae_id);
2486
2487 IF l_latest_release = to_char(p_article_version_number) THEN
2488 RETURN 'Y' ;
2489 ELSE
2490 RETURN 'N';
2491 END IF;
2492 END latest_or_future_art_release;
2493
2494
2495 END okc_std_article_pvt;