[Home] [Help]
PACKAGE BODY: APPS.OKS_AVL_PVT
Source
1 PACKAGE BODY OKS_AVL_PVT AS
2 /* $Header: OKSSAVLB.pls 120.0 2005/05/25 18:27:17 appldev noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(okc_p_util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 null;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 null;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 null;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- FUNCTION get_rec for: OKS_SERV_AVAILS
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_sav_rec IN sav_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN sav_rec_type IS
42 CURSOR sav_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 OBJECT1_ID1,
46 OBJECT1_ID2,
47 JTOT_OBJECT1_CODE,
48 OBJECT_VERSION_NUMBER,
49 CREATED_BY,
50 CREATION_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_DATE,
53 GENERAL_YN,
54 EXCEPT_OBJECT_TYPE,
55 START_DATE_ACTIVE,
56 END_DATE_ACTIVE,
57 LAST_UPDATE_LOGIN,
58 ATTRIBUTE_CATEGORY,
59 ATTRIBUTE1,
60 ATTRIBUTE2,
61 ATTRIBUTE3,
62 ATTRIBUTE4,
63 ATTRIBUTE5,
64 ATTRIBUTE6,
65 ATTRIBUTE7,
66 ATTRIBUTE8,
67 ATTRIBUTE9,
68 ATTRIBUTE10,
69 ATTRIBUTE11,
70 ATTRIBUTE12,
71 ATTRIBUTE13,
72 ATTRIBUTE14,
73 ATTRIBUTE15
74 FROM Oks_Serv_Avails
75 WHERE oks_serv_avails.id = p_id;
76 l_sav_pk sav_pk_csr%ROWTYPE;
77 l_sav_rec sav_rec_type;
78 BEGIN
79 x_no_data_found := TRUE;
80 -- Get current database values
81 OPEN sav_pk_csr (p_sav_rec.id);
82 FETCH sav_pk_csr INTO
83 l_sav_rec.ID,
84 l_sav_rec.OBJECT1_ID1,
85 l_sav_rec.OBJECT1_ID2,
86 l_sav_rec.JTOT_OBJECT1_CODE,
87 l_sav_rec.OBJECT_VERSION_NUMBER,
88 l_sav_rec.CREATED_BY,
89 l_sav_rec.CREATION_DATE,
90 l_sav_rec.LAST_UPDATED_BY,
91 l_sav_rec.LAST_UPDATE_DATE,
92 l_sav_rec.GENERAL_YN,
93 l_sav_rec.EXCEPT_OBJECT_TYPE,
94 l_sav_rec.START_DATE_ACTIVE,
95 l_sav_rec.END_DATE_ACTIVE,
96 l_sav_rec.LAST_UPDATE_LOGIN,
97 l_sav_rec.ATTRIBUTE_CATEGORY,
98 l_sav_rec.ATTRIBUTE1,
99 l_sav_rec.ATTRIBUTE2,
100 l_sav_rec.ATTRIBUTE3,
101 l_sav_rec.ATTRIBUTE4,
102 l_sav_rec.ATTRIBUTE5,
103 l_sav_rec.ATTRIBUTE6,
104 l_sav_rec.ATTRIBUTE7,
105 l_sav_rec.ATTRIBUTE8,
106 l_sav_rec.ATTRIBUTE9,
107 l_sav_rec.ATTRIBUTE10,
108 l_sav_rec.ATTRIBUTE11,
109 l_sav_rec.ATTRIBUTE12,
110 l_sav_rec.ATTRIBUTE13,
111 l_sav_rec.ATTRIBUTE14,
112 l_sav_rec.ATTRIBUTE15;
113 x_no_data_found := sav_pk_csr%NOTFOUND;
114 CLOSE sav_pk_csr;
115 RETURN(l_sav_rec);
116 END get_rec;
117
118 FUNCTION get_rec (
119 p_sav_rec IN sav_rec_type
120 ) RETURN sav_rec_type IS
121 l_row_notfound BOOLEAN := TRUE;
122 BEGIN
123 RETURN(get_rec(p_sav_rec, l_row_notfound));
124 END get_rec;
125 ---------------------------------------------------------------------------
126 -- FUNCTION get_rec for: OKS_SERV_AVAILS_V
127 ---------------------------------------------------------------------------
128 FUNCTION get_rec (
129 p_savv_rec IN savv_rec_type,
130 x_no_data_found OUT NOCOPY BOOLEAN
131 ) RETURN savv_rec_type IS
132 CURSOR oks_savv_pk_csr (p_id IN NUMBER) IS
133 SELECT
134 ID,
135 OBJECT1_ID1,
136 OBJECT1_ID2,
137 JTOT_OBJECT1_CODE,
138 OBJECT_VERSION_NUMBER,
139 CREATED_BY,
140 CREATION_DATE,
141 LAST_UPDATED_BY,
142 LAST_UPDATE_DATE,
143 GENERAL_YN,
144 EXCEPT_OBJECT_TYPE,
145 START_DATE_ACTIVE,
146 END_DATE_ACTIVE,
147 LAST_UPDATE_LOGIN,
148 ATTRIBUTE_CATEGORY,
149 ATTRIBUTE1,
150 ATTRIBUTE2,
151 ATTRIBUTE3,
152 ATTRIBUTE4,
153 ATTRIBUTE5,
154 ATTRIBUTE6,
155 ATTRIBUTE7,
156 ATTRIBUTE8,
157 ATTRIBUTE9,
158 ATTRIBUTE10,
159 ATTRIBUTE11,
160 ATTRIBUTE12,
161 ATTRIBUTE13,
162 ATTRIBUTE14,
163 ATTRIBUTE15
164 FROM Oks_Serv_Avails_V
165 WHERE oks_serv_avails_v.id = p_id;
166 l_oks_savv_pk oks_savv_pk_csr%ROWTYPE;
167 l_savv_rec savv_rec_type;
168 BEGIN
169 x_no_data_found := TRUE;
170 -- Get current database values
171 OPEN oks_savv_pk_csr (p_savv_rec.id);
172 FETCH oks_savv_pk_csr INTO
173 l_savv_rec.ID,
174 l_savv_rec.OBJECT1_ID1,
175 l_savv_rec.OBJECT1_ID2,
176 l_savv_rec.JTOT_OBJECT1_CODE,
177 l_savv_rec.OBJECT_VERSION_NUMBER,
178 l_savv_rec.CREATED_BY,
179 l_savv_rec.CREATION_DATE,
180 l_savv_rec.LAST_UPDATED_BY,
181 l_savv_rec.LAST_UPDATE_DATE,
182 l_savv_rec.GENERAL_YN,
183 l_savv_rec.EXCEPT_OBJECT_TYPE,
184 l_savv_rec.START_DATE_ACTIVE,
185 l_savv_rec.END_DATE_ACTIVE,
186 l_savv_rec.LAST_UPDATE_LOGIN,
187 l_savv_rec.ATTRIBUTE_CATEGORY,
188 l_savv_rec.ATTRIBUTE1,
189 l_savv_rec.ATTRIBUTE2,
190 l_savv_rec.ATTRIBUTE3,
191 l_savv_rec.ATTRIBUTE4,
192 l_savv_rec.ATTRIBUTE5,
193 l_savv_rec.ATTRIBUTE6,
194 l_savv_rec.ATTRIBUTE7,
195 l_savv_rec.ATTRIBUTE8,
196 l_savv_rec.ATTRIBUTE9,
197 l_savv_rec.ATTRIBUTE10,
198 l_savv_rec.ATTRIBUTE11,
199 l_savv_rec.ATTRIBUTE12,
200 l_savv_rec.ATTRIBUTE13,
201 l_savv_rec.ATTRIBUTE14,
202 l_savv_rec.ATTRIBUTE15;
203 x_no_data_found := oks_savv_pk_csr%NOTFOUND;
204 CLOSE oks_savv_pk_csr;
205 RETURN(l_savv_rec);
206 END get_rec;
207
208 FUNCTION get_rec (
209 p_savv_rec IN savv_rec_type
210 ) RETURN savv_rec_type IS
211 l_row_notfound BOOLEAN := TRUE;
212 BEGIN
213 RETURN(get_rec(p_savv_rec, l_row_notfound));
214 END get_rec;
215
216 -------------------------------------------------------
217 -- FUNCTION null_out_defaults for: OKS_SERV_AVAILS_V --
218 -------------------------------------------------------
219 FUNCTION null_out_defaults (
220 p_savv_rec IN savv_rec_type
221 ) RETURN savv_rec_type IS
222 l_savv_rec savv_rec_type := p_savv_rec;
223 BEGIN
224 IF (l_savv_rec.object1_id1 = OKC_API.G_MISS_CHAR) THEN
225 l_savv_rec.object1_id1 := NULL;
226 END IF;
227 IF (l_savv_rec.object1_id2 = OKC_API.G_MISS_CHAR) THEN
228 l_savv_rec.object1_id2 := NULL;
229 END IF;
230 IF (l_savv_rec.JTOT_OBJECT1_CODE = OKC_API.G_MISS_CHAR) THEN
231 l_savv_rec.JTOT_OBJECT1_CODE := NULL;
232 END IF;
233 IF (l_savv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
234 l_savv_rec.object_version_number := NULL;
235 END IF;
236 IF (l_savv_rec.created_by = OKC_API.G_MISS_NUM) THEN
237 l_savv_rec.created_by := NULL;
238 END IF;
239 IF (l_savv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
240 l_savv_rec.creation_date := NULL;
241 END IF;
242 IF (l_savv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
243 l_savv_rec.last_updated_by := NULL;
244 END IF;
245 IF (l_savv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
246 l_savv_rec.last_update_date := NULL;
247 END IF;
248 IF (l_savv_rec.general_yn = OKC_API.G_MISS_CHAR) THEN
249 l_savv_rec.general_yn := NULL;
250 END IF;
251 IF (l_savv_rec.except_object_type = OKC_API.G_MISS_CHAR) THEN
252 l_savv_rec.except_object_type := NULL;
253 END IF;
254 IF (l_savv_rec.start_date_active = OKC_API.G_MISS_DATE) THEN
255 l_savv_rec.start_date_active := NULL;
256 END IF;
257 IF (l_savv_rec.end_date_active = OKC_API.G_MISS_DATE) THEN
258 l_savv_rec.end_date_active := NULL;
259 END IF;
260 IF (l_savv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
261 l_savv_rec.last_update_login := NULL;
262 END IF;
263 IF (l_savv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
264 l_savv_rec.attribute_category := NULL;
265 END IF;
266 IF (l_savv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
267 l_savv_rec.attribute1 := NULL;
268 END IF;
269 IF (l_savv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
270 l_savv_rec.attribute2 := NULL;
271 END IF;
272 IF (l_savv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
273 l_savv_rec.attribute3 := NULL;
274 END IF;
275 IF (l_savv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
276 l_savv_rec.attribute4 := NULL;
277 END IF;
278 IF (l_savv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
279 l_savv_rec.attribute5 := NULL;
280 END IF;
281 IF (l_savv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
282 l_savv_rec.attribute6 := NULL;
283 END IF;
284 IF (l_savv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
285 l_savv_rec.attribute7 := NULL;
286 END IF;
287 IF (l_savv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
288 l_savv_rec.attribute8 := NULL;
289 END IF;
290 IF (l_savv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
291 l_savv_rec.attribute9 := NULL;
292 END IF;
293 IF (l_savv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
294 l_savv_rec.attribute10 := NULL;
295 END IF;
296 IF (l_savv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
297 l_savv_rec.attribute11 := NULL;
298 END IF;
299 IF (l_savv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
300 l_savv_rec.attribute12 := NULL;
301 END IF;
302 IF (l_savv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
303 l_savv_rec.attribute13 := NULL;
304 END IF;
305 IF (l_savv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
306 l_savv_rec.attribute14 := NULL;
307 END IF;
308 IF (l_savv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
309 l_savv_rec.attribute15 := NULL;
310 END IF;
311 RETURN(l_savv_rec);
312 END null_out_defaults;
313
314 ---------------------------------------------------------------------------
315 -- PROCEDURE Validate_Attributes
316 ---------------------------------------------------------------------------
317 ---------------------------------------------------------------------------
318 -- PROCEDURE Validate_id
319 ---------------------------------------------------------------------------
320
321
322 PROCEDURE validate_id(x_return_status OUT NOCOPY varchar2,
323 p_id IN Number)
324 Is
325 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
326 Begin
327
328 x_return_status := OKC_API.G_RET_STS_SUCCESS;
329
330 If p_id = OKC_API.G_MISS_NUM OR p_id IS NULL Then
331
332 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
333 l_return_status := OKC_API.G_RET_STS_ERROR;
334 End If;
335
336 Exception
337 When G_EXCEPTION_HALT_VALIDATION THEN
338 x_return_status := l_return_status;
339 NULL;
340 When OTHERS THEN
341 -- store SQL error message on message stack for caller
342 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
343 p_msg_name => g_unexpected_error,
344 p_token1 => g_sqlcode_token,
345 p_token1_value => sqlcode,
346 p_token2 => g_sqlerrm_token,
347 p_token2_value => sqlerrm
348 );
349 -- notify caller of an UNEXPECTED error
350 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
351 End validate_id;
352
353 ---------------------------------------------------------------------------
354 -- PROCEDURE Validate_Objetc1_id1
355 ---------------------------------------------------------------------------
356
357 PROCEDURE validate_object1_id1(x_return_status OUT NOCOPY varchar2,
358 P_object1_id1 IN varchar2)
359
360 Is
361 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
362 Begin
363
364 x_return_status := OKC_API.G_RET_STS_SUCCESS;
365
366 If P_object1_id1 = OKC_API.G_MISS_CHAR OR P_object1_id1 IS NULL Then
367
368 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object1_id1' );
369 x_return_status := OKC_API.G_RET_STS_ERROR;
370 RAISE G_EXCEPTION_HALT_VALIDATION;
371
372 End If;
373
374 -- call column length utility
375 OKC_UTIL.CHECK_LENGTH
376 (
377 p_view_name => 'OKS_SERV_AVAILS_V'
378 ,p_col_name => 'object1_id1'
379 ,p_col_value => P_object1_id1
380 ,x_return_status => l_return_status
381 );
382
383 -- verify that length is within allowed limits
384 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
385 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
386 p_msg_name => g_unexpected_error,
387 p_token1 => g_sqlcode_token,
388 p_token1_value => sqlcode,
389 p_token2 => g_sqlerrm_token,
390 p_token2_value => 'Object1 id1 Length'
391 );
392 -- notify caller of an error
393 x_return_status := OKC_API.G_RET_STS_ERROR;
394 -- halt further validation of this column
395 RAISE G_EXCEPTION_HALT_VALIDATION;
396 End If;
397
398 Exception
399 When G_EXCEPTION_HALT_VALIDATION Then
400 NULL;
401 When OTHERS Then
402 -- store SQL error message on message stack for caller
403 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
404 p_msg_name => g_unexpected_error,
405 p_token1 => g_sqlcode_token,
406 p_token1_value => sqlcode,
407 p_token2 => g_sqlerrm_token,
408 p_token2_value => sqlerrm
409 );
410
411 -- notify caller of an UNEXPECTED error
412 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
413 END validate_object1_id1;
414
415 ---------------------------------------------------------------------------
416 -- PROCEDURE Validate_object1_id2
417 ---------------------------------------------------------------------------
418
419 PROCEDURE validate_object1_id2(x_return_status OUT NOCOPY varchar2,
420 P_object1_id2 IN varchar2)
421 Is
422 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
423 Begin
424
425 x_return_status := OKC_API.G_RET_STS_SUCCESS;
426
427 If P_object1_id2 = OKC_API.G_MISS_CHAR OR
428 P_object1_id2 IS NULL
429 Then
430 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object1_id2' );
431 x_return_status := OKC_API.G_RET_STS_ERROR;
432 RAISE G_EXCEPTION_HALT_VALIDATION;
433 End If;
434
435
436 -- call column length utility
437 OKC_UTIL.CHECK_LENGTH
438 (
439 p_view_name => 'OKS_SERV_AVAILS_V'
440 ,p_col_name => 'object1_id2'
441 ,p_col_value => P_object1_id2
442 ,x_return_status => l_return_status
443 );
444
445 -- verify that length is within allowed limits
446 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
447 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
448 p_msg_name => g_unexpected_error,
449 p_token1 => g_sqlcode_token,
450 p_token1_value => sqlcode,
451 p_token2 => g_sqlerrm_token,
452 p_token2_value => 'Object1 id2 Length'
453 );
454 -- notify caller of an error
455 x_return_status := OKC_API.G_RET_STS_ERROR;
456 -- halt further validation of this column
457 RAISE G_EXCEPTION_HALT_VALIDATION;
458 End If;
459
460 Exception
461 When G_EXCEPTION_HALT_VALIDATION Then
462 NULL;
463 When OTHERS Then
464 -- store SQL error message on message stack for caller
465 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
466 p_msg_name => g_unexpected_error,
467 p_token1 => g_sqlcode_token,
468 p_token1_value => sqlcode,
469 p_token2 => g_sqlerrm_token,
470 p_token2_value => sqlerrm
471 );
472 -- notify caller of an UNEXPECTED error
473 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
474 END validate_object1_id2;
475
476
477 ---------------------------------------------------------------------------
478 -- PROCEDURE Validate_Object_version_number
479 ---------------------------------------------------------------------------
480
481
482 PROCEDURE validate_objvernum(x_return_status OUT NOCOPY varchar2,
483 P_object_version_number IN Number)
484 Is
485 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
486 Begin
487
488 x_return_status := OKC_API.G_RET_STS_SUCCESS;
489
490 If p_object_version_number = OKC_API.G_MISS_NUM OR
491 p_object_version_number IS NULL
492 Then
493 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
494 x_return_status := OKC_API.G_RET_STS_ERROR;
495 RAISE G_EXCEPTION_HALT_VALIDATION;
496 End If;
497 OKC_UTIL.CHECK_LENGTH
498 (
499 p_view_name => 'OKS_SERV_AVAILS_V'
500 ,p_col_name => 'object_version_number'
501 ,p_col_value => P_object_version_number
502 ,x_return_status => l_return_status
503 );
504
505 -- verify that length is within allowed limits
506 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
507 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
508 p_msg_name => g_unexpected_error,
509 p_token1 => g_sqlcode_token,
510 p_token1_value => sqlcode,
511 p_token2 => g_sqlerrm_token,
512 p_token2_value => 'Object version number Length'
513 );
514 -- notify caller of an error
515 x_return_status := OKC_API.G_RET_STS_ERROR;
516 -- halt further validation of this column
517 RAISE G_EXCEPTION_HALT_VALIDATION;
518 End If;
519
520 Exception
521 When G_EXCEPTION_HALT_VALIDATION Then
522 NULL;
523 When OTHERS Then
524 -- store SQL error message on message stack for caller
525 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
526 p_msg_name => g_unexpected_error,
527 p_token1 => g_sqlcode_token,
528 p_token1_value => sqlcode,
529 p_token2 => g_sqlerrm_token,
530 p_token2_value => sqlerrm);
531 -- notify caller of an UNEXPECTED error
532 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
533 END validate_objvernum;
534
535 ---------------------------------------------------------------------------
536 -- PROCEDURE Validate_General_yn
537 ---------------------------------------------------------------------------
538
539 PROCEDURE validate_general_yn(x_return_status OUT NOCOPY varchar2,
540 P_general_yn IN Varchar2)
541 Is
542 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
543 Begin
544
545 x_return_status := OKC_API.G_RET_STS_SUCCESS;
546
547 If P_general_yn = OKC_API.G_MISS_CHAR OR
548 P_general_yn IS NULL
549 Then
550 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'General Y/N');
551 x_return_status := OKC_API.G_RET_STS_ERROR;
552 RAISE G_EXCEPTION_HALT_VALIDATION;
553 End If;
554 -- call column length utility
555 OKC_UTIL.CHECK_LENGTH
556 (
557 p_view_name => 'OKS_SERV_AVAILS_V'
558 ,p_col_name => 'general_yn'
559 ,p_col_value => P_general_yn
560 ,x_return_status => l_return_status
561 );
562
563 -- verify that length is within allowed limits
564 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
565 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
566 p_msg_name => g_unexpected_error,
567 p_token1 => g_sqlcode_token,
568 p_token1_value => sqlcode,
569 p_token2 => g_sqlerrm_token,
570 p_token2_value => 'general_yn length');
571
572
573 -- notify caller of an error
574 x_return_status := OKC_API.G_RET_STS_ERROR;
575 -- halt further validation of this column
576 RAISE G_EXCEPTION_HALT_VALIDATION;
577 End If;
578
579 Exception
580 When G_EXCEPTION_HALT_VALIDATION Then
581 NULL;
582 When OTHERS Then
583 -- store SQL error message on message stack for caller
584 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
585 p_msg_name => g_unexpected_error,
586 p_token1 => g_sqlcode_token,
587 p_token1_value => sqlcode,
588 p_token2 => g_sqlerrm_token,
589 p_token2_value => sqlerrm);
590
591 -- notify caller of an UNEXPECTED error
592 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
593 END validate_general_yn;
594
595 ---------------------------------------------------------------------------
596 -- PROCEDURE Validate_Except_object_type
597 ---------------------------------------------------------------------------
598
599
600 PROCEDURE validate_except_object_type( x_return_status OUT NOCOPY varchar2,
601 P_except_object_type IN Varchar2)
602 Is
603 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
604 Begin
605
606 x_return_status := OKC_API.G_RET_STS_SUCCESS;
607
608
609 If P_except_object_type = OKC_API.G_MISS_CHAR OR
610 P_except_object_type IS NULL
611 Then
612 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'except_object_type' );
613 x_return_status := OKC_API.G_RET_STS_ERROR;
614 RAISE G_EXCEPTION_HALT_VALIDATION;
615 End If;
616 -- call column length utility
617 OKC_UTIL.CHECK_LENGTH
618 (
619 p_view_name => 'OKS_SERV_AVAILS_V'
620 ,p_col_name => 'except_object_type'
621 ,p_col_value => P_except_object_type
622 ,x_return_status => l_return_status
623 );
624
625 -- verify that length is within allowed limits
626 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
627 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
628 p_msg_name => g_unexpected_error,
629 p_token1 => g_sqlcode_token,
630 p_token1_value => sqlcode,
631 p_token2 => g_sqlerrm_token,
632 p_token2_value => 'except_object_type length');
633 -- notify caller of an error
634 x_return_status := OKC_API.G_RET_STS_ERROR;
635 -- halt further validation of this column
636 RAISE G_EXCEPTION_HALT_VALIDATION;
637 End If;
638
639 Exception
640 When G_EXCEPTION_HALT_VALIDATION Then
641 NULL;
642 When OTHERS Then
643 -- store SQL error message on message stack for caller
644 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
645 p_msg_name => g_unexpected_error,
646 p_token1 => g_sqlcode_token,
647 p_token1_value => sqlcode,
648 p_token2 => g_sqlerrm_token,
649 p_token2_value => sqlerrm);
650
651 -- notify caller of an UNEXPECTED error
652 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
653 END validate_except_object_type;
654
655 PROCEDURE validate_jtot_object1_Code(x_return_status OUT NOCOPY varchar2,
656 P_jtot_object1_Code Varchar2)
657 Is
658 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
659 Begin
660
661 x_return_status := OKC_API.G_RET_STS_SUCCESS;
662
663
664 If P_jtot_object1_code = OKC_API.G_MISS_CHAR OR
665 P_jtot_object1_code IS NULL
666 Then
667 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'jtot_object1_code' );
668 x_return_status := OKC_API.G_RET_STS_ERROR;
669 RAISE G_EXCEPTION_HALT_VALIDATION;
670 End If;
671
672 Exception
673 When G_EXCEPTION_HALT_VALIDATION Then
674 NULL;
675 When OTHERS Then
676 -- store SQL error message on message stack for caller
677 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
678 p_msg_name => g_unexpected_error,
679 p_token1 => g_sqlcode_token,
680 p_token1_value => sqlcode,
681 p_token2 => g_sqlerrm_token,
682 p_token2_value => sqlerrm);
683
684 -- notify caller of an UNEXPECTED error
685 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
686 END validate_jtot_object1_code;
687
688
689
690
691 PROCEDURE validate_attribute_category(x_return_status OUT NOCOPY varchar2,
692 P_attribute_category IN varchar)
693 Is
694 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
695 Begin
696
697 x_return_status := OKC_API.G_RET_STS_SUCCESS;
698
699 -- call column length utility
700 OKC_UTIL.CHECK_LENGTH
701 (
702 p_view_name => 'OKS_SERV_AVAILS_V'
703 ,p_col_name => 'attribute_category'
704 ,p_col_value => p_attribute_category
705 ,x_return_status => l_return_status
706 );
707 -- verify that length is within allowed limits
708 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
709 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
710 p_msg_name => g_unexpected_error,
711 p_token1 => g_sqlcode_token,
712 p_token1_value => sqlcode,
713 p_token2 => g_sqlerrm_token,
714 p_token2_value => 'attribute category Length');
715 -- notify caller of an error
716 x_return_status := OKC_API.G_RET_STS_ERROR;
717 -- halt further validation of this column
718 RAISE G_EXCEPTION_HALT_VALIDATION;
719 End If;
720
721
722 Exception
723 When G_EXCEPTION_HALT_VALIDATION Then
724 NULL;
725 When OTHERS Then
726 -- store SQL error message on message stack for caller
727 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
728 p_msg_name => g_unexpected_error,
729 p_token1 => g_sqlcode_token,
730 p_token1_value => sqlcode,
731 p_token2 => g_sqlerrm_token,
732 p_token2_value => sqlerrm);
733
734 -- notify caller of an UNEXPECTED error
735 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
736 END validate_attribute_category;
737
738
739 PROCEDURE validate_attribute1(x_return_status OUT NOCOPY varchar2,
740 P_attribute1 IN varchar2)
741 Is
742 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
743 Begin
744 x_return_status := OKC_API.G_RET_STS_SUCCESS;
745 -- call column length utility
746 OKC_UTIL.CHECK_LENGTH
747 (
748 p_view_name => 'OKS_SERV_AVAILS_V'
749 ,p_col_name => 'attribute1'
750 ,p_col_value => p_attribute1
751 ,x_return_status => l_return_status
752 );
753 -- verify that length is within allowed limits
754 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
755 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
756 p_msg_name => g_unexpected_error,
757 p_token1 => g_sqlcode_token,
758 p_token1_value => sqlcode,
759 p_token2 => g_sqlerrm_token,
760 p_token2_value => 'attribute 1 Length');
761 -- notify caller of an error
762 x_return_status := OKC_API.G_RET_STS_ERROR;
763 -- halt further validation of this column
764 RAISE G_EXCEPTION_HALT_VALIDATION;
765 End If;
766
767 Exception
768 When G_EXCEPTION_HALT_VALIDATION Then
769 NULL;
770 When OTHERS Then
771 -- store SQL error message on message stack for caller
772 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
773 p_msg_name => g_unexpected_error,
774 p_token1 => g_sqlcode_token,
775 p_token1_value => sqlcode,
776 p_token2 => g_sqlerrm_token,
777 p_token2_value => sqlerrm);
778
779 -- notify caller of an UNEXPECTED error
780 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
781 END validate_attribute1;
782
783 PROCEDURE validate_attribute2(x_return_status OUT NOCOPY varchar2,
784 P_attribute2 IN varchar2)
785 Is
786 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
787 Begin
788
789 x_return_status := OKC_API.G_RET_STS_SUCCESS;
790
791 -- call column length utility
792 OKC_UTIL.CHECK_LENGTH
793 (
794 p_view_name => 'OKS_SERV_AVAILS_V'
795 ,p_col_name => 'attribute2'
796 ,p_col_value => p_attribute2
797 ,x_return_status => l_return_status
798 );
799
800 -- verify that length is within allowed limits
801 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
802 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
803 p_msg_name => g_unexpected_error,
804 p_token1 => g_sqlcode_token,
805 p_token1_value => sqlcode,
806 p_token2 => g_sqlerrm_token,
807 p_token2_value => 'attribute 2 Length');
808
809 -- notify caller of an error
810 x_return_status := OKC_API.G_RET_STS_ERROR;
811 -- halt further validation of this column
812 RAISE G_EXCEPTION_HALT_VALIDATION;
813 End If;
814
815 Exception
816 When G_EXCEPTION_HALT_VALIDATION Then
817 NULL;
818 When OTHERS Then
819 -- store SQL error message on message stack for caller
820 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
821 p_msg_name => g_unexpected_error,
822 p_token1 => g_sqlcode_token,
823 p_token1_value => sqlcode,
824 p_token2 => g_sqlerrm_token,
825 p_token2_value => sqlerrm);
826
827 -- notify caller of an UNEXPECTED error
828 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
829 END validate_attribute2;
830
831
832 PROCEDURE validate_attribute3(x_return_status OUT NOCOPY varchar2,
833 P_attribute3 IN varchar2)
834 Is
835 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
836 Begin
837 x_return_status := OKC_API.G_RET_STS_SUCCESS;
838 -- call column length utility
839 OKC_UTIL.CHECK_LENGTH
840 (
841 p_view_name => 'OKS_SERV_AVAILS_V'
842 ,p_col_name => 'attribute3'
843 ,p_col_value => p_attribute3
844 ,x_return_status => l_return_status
845 );
846
847 -- verify that length is within allowed limits
848 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
849 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
850 p_msg_name => g_unexpected_error,
851 p_token1 => g_sqlcode_token,
852 p_token1_value => sqlcode,
853 p_token2 => g_sqlerrm_token,
854 p_token2_value => 'attribute 3 Length');
855 -- notify caller of an error
856 x_return_status := OKC_API.G_RET_STS_ERROR;
857 -- halt further validation of this column
858 RAISE G_EXCEPTION_HALT_VALIDATION;
859 End If;
860
861 Exception
862 When G_EXCEPTION_HALT_VALIDATION Then
863 NULL;
864 When OTHERS Then
865 -- store SQL error message on message stack for caller
866 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
867 p_msg_name => g_unexpected_error,
868 p_token1 => g_sqlcode_token,
869 p_token1_value => sqlcode,
870 p_token2 => g_sqlerrm_token,
871 p_token2_value => sqlerrm);
872 -- notify caller of an UNEXPECTED error
873 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
874 END validate_attribute3;
875
876 PROCEDURE validate_attribute4 (x_return_status OUT NOCOPY varchar2,
877 P_attribute4 IN varchar2)
878 Is
879 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
880 Begin
881
882 x_return_status := OKC_API.G_RET_STS_SUCCESS;
883
884 -- call column length utility
885 OKC_UTIL.CHECK_LENGTH
886 (
887 p_view_name => 'OKS_SERV_AVAILS_V'
888 ,p_col_name => 'attribute4'
889 ,p_col_value => p_attribute4
890 ,x_return_status => l_return_status
891 );
892
893 -- verify that length is within allowed limits
894 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
895 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
896 p_msg_name => g_unexpected_error,
897 p_token1 => g_sqlcode_token,
898 p_token1_value => sqlcode,
899 p_token2 => g_sqlerrm_token,
900 p_token2_value => 'attribute 4 Length');
901 -- notify caller of an error
902 x_return_status := OKC_API.G_RET_STS_ERROR;
903 -- halt further validation of this column
904 RAISE G_EXCEPTION_HALT_VALIDATION;
905 End If;
906
907 Exception
908 When G_EXCEPTION_HALT_VALIDATION Then
909 NULL;
910 When OTHERS Then
911 -- store SQL error message on message stack for caller
912 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
913 p_msg_name => g_unexpected_error,
914 p_token1 => g_sqlcode_token,
915 p_token1_value => sqlcode,
916 p_token2 => g_sqlerrm_token,
917 p_token2_value => sqlerrm);
918
919 -- notify caller of an UNEXPECTED error
920 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
921 END validate_attribute4;
922
923
924 PROCEDURE validate_attribute5(x_return_status OUT NOCOPY varchar2,
925 P_attribute5 IN varchar2)
926 Is
927 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
928 Begin
929
930 x_return_status := OKC_API.G_RET_STS_SUCCESS;
931 -- call column length utility
932 OKC_UTIL.CHECK_LENGTH
933 (
934 p_view_name => 'OKS_SERV_AVAILS_V'
935 ,p_col_name => 'attribute5'
936 ,p_col_value => p_attribute5
937 ,x_return_status => l_return_status
938 );
939
940 -- verify that length is within allowed limits
941 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
942 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
943 p_msg_name => g_unexpected_error,
944 p_token1 => g_sqlcode_token,
945 p_token1_value => sqlcode,
946 p_token2 => g_sqlerrm_token,
947 p_token2_value => 'attribute 5 Length');
948
949 -- notify caller of an error
950 x_return_status := OKC_API.G_RET_STS_ERROR;
951 -- halt further validation of this column
952 RAISE G_EXCEPTION_HALT_VALIDATION;
953 End If;
954
955 Exception
956 When G_EXCEPTION_HALT_VALIDATION Then
957 NULL;
958 When OTHERS Then
959 -- store SQL error message on message stack for caller
960 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
961 p_msg_name => g_unexpected_error,
962 p_token1 => g_sqlcode_token,
963 p_token1_value => sqlcode,
964 p_token2 => g_sqlerrm_token,
965 p_token2_value => sqlerrm);
966
967 -- notify caller of an UNEXPECTED error
968 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
969 END validate_attribute5;
970
971 PROCEDURE validate_attribute6(x_return_status OUT NOCOPY varchar2,
972 P_attribute6 IN varchar2)
973 Is
974 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
975 Begin
976 x_return_status := OKC_API.G_RET_STS_SUCCESS;
977 -- call column length utility
978 OKC_UTIL.CHECK_LENGTH
979 (
980 p_view_name => 'OKS_SERV_AVAILS_V'
981 ,p_col_name => 'attribute6'
982 ,p_col_value => p_attribute6
983 ,x_return_status => l_return_status
984 );
985 -- verify that length is within allowed limits
986 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
987 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
988 p_msg_name => g_unexpected_error,
989 p_token1 => g_sqlcode_token,
990 p_token1_value => sqlcode,
991 p_token2 => g_sqlerrm_token,
992 p_token2_value => 'attribute 6 Length');
993 -- notify caller of an error
994 x_return_status := OKC_API.G_RET_STS_ERROR;
995 -- halt further validation of this column
996 RAISE G_EXCEPTION_HALT_VALIDATION;
997 End If;
998
999 Exception
1000 When G_EXCEPTION_HALT_VALIDATION Then
1001 NULL;
1002 When OTHERS Then
1003 -- store SQL error message on message stack for caller
1004 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1005 p_msg_name => g_unexpected_error,
1006 p_token1 => g_sqlcode_token,
1007 p_token1_value => sqlcode,
1008 p_token2 => g_sqlerrm_token,
1009 p_token2_value => sqlerrm);
1010
1011 -- notify caller of an UNEXPECTED error
1012 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1013 END validate_attribute6;
1014
1015 PROCEDURE validate_attribute7(x_return_status OUT NOCOPY varchar2,
1016 P_attribute7 IN varchar2)
1017 Is
1018 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1019 Begin
1020
1021 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1022
1023 -- call column length utility
1024 OKC_UTIL.CHECK_LENGTH
1025 (
1026 p_view_name => 'OKS_SERV_AVAILS_V'
1027 ,p_col_name => 'attribute7'
1028 ,p_col_value => p_attribute7
1029 ,x_return_status => l_return_status
1030 );
1031
1032 -- verify that length is within allowed limits
1033 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1034 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1035 p_msg_name => g_unexpected_error,
1036 p_token1 => g_sqlcode_token,
1037 p_token1_value => sqlcode,
1038 p_token2 => g_sqlerrm_token,
1039 p_token2_value => 'attribute 7 Length');
1040
1041 -- notify caller of an error
1042 x_return_status := OKC_API.G_RET_STS_ERROR;
1043 -- halt further validation of this column
1044 RAISE G_EXCEPTION_HALT_VALIDATION;
1045 End If;
1046
1047 Exception
1048 When G_EXCEPTION_HALT_VALIDATION Then
1049 NULL;
1050 When OTHERS Then
1051 -- store SQL error message on message stack for caller
1052 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1053 p_msg_name => g_unexpected_error,
1054 p_token1 => g_sqlcode_token,
1055 p_token1_value => sqlcode,
1056 p_token2 => g_sqlerrm_token,
1057 p_token2_value => sqlerrm);
1058
1059 -- notify caller of an UNEXPECTED error
1060 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1061 END validate_attribute7;
1062
1063
1064 PROCEDURE validate_attribute8(x_return_status OUT NOCOPY varchar2,
1065 P_attribute8 IN varchar2)
1066 Is
1067 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1068 Begin
1069 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1070 -- call column length utility
1071 OKC_UTIL.CHECK_LENGTH
1072 (
1073 p_view_name => 'OKS_SERV_AVAILS_V'
1074 ,p_col_name => 'attribute8'
1075 ,p_col_value => p_attribute8
1076 ,x_return_status => l_return_status
1077 );
1078
1079 -- verify that length is within allowed limits
1080 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1081 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1082 p_msg_name => g_unexpected_error,
1083 p_token1 => g_sqlcode_token,
1084 p_token1_value => sqlcode,
1085 p_token2 => g_sqlerrm_token,
1086 p_token2_value => 'attribute 8 Length');
1087 -- notify caller of an error
1088 x_return_status := OKC_API.G_RET_STS_ERROR;
1089 -- halt further validation of this column
1090 RAISE G_EXCEPTION_HALT_VALIDATION;
1091 End If;
1092
1093 Exception
1094 When G_EXCEPTION_HALT_VALIDATION Then
1095 NULL;
1096 When OTHERS Then
1097 -- store SQL error message on message stack for caller
1098 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1099 p_msg_name => g_unexpected_error,
1100 p_token1 => g_sqlcode_token,
1101 p_token1_value => sqlcode,
1102 p_token2 => g_sqlerrm_token,
1103 p_token2_value => sqlerrm);
1104 -- notify caller of an UNEXPECTED error
1105 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1106 END validate_attribute8;
1107
1108 PROCEDURE validate_attribute9 (x_return_status OUT NOCOPY varchar2,
1109 P_attribute9 IN varchar2)
1110 Is
1111 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1112 Begin
1113
1114 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1115
1116 -- call column length utility
1117 OKC_UTIL.CHECK_LENGTH
1118 (
1119 p_view_name => 'OKS_SERV_AVAILS_V'
1120 ,p_col_name => 'attribute9'
1121 ,p_col_value => p_attribute9
1122 ,x_return_status => l_return_status
1123 );
1124
1125 -- verify that length is within allowed limits
1126 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1127 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1128 p_msg_name => g_unexpected_error,
1129 p_token1 => g_sqlcode_token,
1130 p_token1_value => sqlcode,
1131 p_token2 => g_sqlerrm_token,
1132 p_token2_value => 'attribute 9 Length');
1133 -- notify caller of an error
1134 x_return_status := OKC_API.G_RET_STS_ERROR;
1135 -- halt further validation of this column
1136 RAISE G_EXCEPTION_HALT_VALIDATION;
1137 End If;
1138
1139 Exception
1140 When G_EXCEPTION_HALT_VALIDATION Then
1141 NULL;
1142 When OTHERS Then
1143 -- store SQL error message on message stack for caller
1144 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1145 p_msg_name => g_unexpected_error,
1146 p_token1 => g_sqlcode_token,
1147 p_token1_value => sqlcode,
1148 p_token2 => g_sqlerrm_token,
1149 p_token2_value => sqlerrm);
1150
1151 -- notify caller of an UNEXPECTED error
1152 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1153 END validate_attribute9;
1154
1155
1156 PROCEDURE validate_attribute10(x_return_status OUT NOCOPY varchar2,
1157 P_attribute10 IN varchar2)
1158 Is
1159 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1160 Begin
1161
1162 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1163 -- call column length utility
1164 OKC_UTIL.CHECK_LENGTH
1165 (
1166 p_view_name => 'OKS_SERV_AVAILS_V'
1167 ,p_col_name => 'attribute10'
1168 ,p_col_value => p_attribute10
1169 ,x_return_status => l_return_status
1170 );
1171
1172 -- verify that length is within allowed limits
1173 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1174 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1175 p_msg_name => g_unexpected_error,
1176 p_token1 => g_sqlcode_token,
1177 p_token1_value => sqlcode,
1178 p_token2 => g_sqlerrm_token,
1179 p_token2_value => 'attribute 10 Length');
1180
1181 -- notify caller of an error
1182 x_return_status := OKC_API.G_RET_STS_ERROR;
1183 -- halt further validation of this column
1184 RAISE G_EXCEPTION_HALT_VALIDATION;
1185 End If;
1186
1187 Exception
1188 When G_EXCEPTION_HALT_VALIDATION Then
1189 NULL;
1190 When OTHERS Then
1191 -- store SQL error message on message stack for caller
1192 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1193 p_msg_name => g_unexpected_error,
1194 p_token1 => g_sqlcode_token,
1195 p_token1_value => sqlcode,
1196 p_token2 => g_sqlerrm_token,
1197 p_token2_value => sqlerrm);
1198
1199 -- notify caller of an UNEXPECTED error
1200 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1201 END validate_attribute10;
1202
1203 PROCEDURE validate_attribute11(x_return_status OUT NOCOPY varchar2,
1204 P_attribute11 IN varchar2)
1205 Is
1206 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1207 Begin
1208 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1209 -- call column length utility
1210 OKC_UTIL.CHECK_LENGTH
1211 (
1212 p_view_name => 'OKS_SERV_AVAILS_V'
1213 ,p_col_name => 'attribute11'
1214 ,p_col_value => p_attribute11
1215 ,x_return_status => l_return_status
1216 );
1217 -- verify that length is within allowed limits
1218 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1219 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1220 p_msg_name => g_unexpected_error,
1221 p_token1 => g_sqlcode_token,
1222 p_token1_value => sqlcode,
1223 p_token2 => g_sqlerrm_token,
1224 p_token2_value => 'attribute 11 Length');
1225 -- notify caller of an error
1226 x_return_status := OKC_API.G_RET_STS_ERROR;
1227 -- halt further validation of this column
1228 RAISE G_EXCEPTION_HALT_VALIDATION;
1229 End If;
1230
1231 Exception
1232 When G_EXCEPTION_HALT_VALIDATION Then
1233 NULL;
1234 When OTHERS Then
1235 -- store SQL error message on message stack for caller
1236 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1237 p_msg_name => g_unexpected_error,
1238 p_token1 => g_sqlcode_token,
1239 p_token1_value => sqlcode,
1240 p_token2 => g_sqlerrm_token,
1241 p_token2_value => sqlerrm);
1242
1243 -- notify caller of an UNEXPECTED error
1244 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1245 END validate_attribute11;
1246
1247 PROCEDURE validate_attribute12(x_return_status OUT NOCOPY varchar2,
1248 P_attribute12 IN varchar2)
1249 Is
1250 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1251 Begin
1252 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1253 -- call column length utility
1254 OKC_UTIL.CHECK_LENGTH
1255 (
1256 p_view_name => 'OKS_SERV_AVAILS_V'
1257 ,p_col_name => 'attribute12'
1258 ,p_col_value => p_attribute12
1259 ,x_return_status => l_return_status
1260 );
1261
1262 -- verify that length is within allowed limits
1263 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1264 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1265 p_msg_name => g_unexpected_error,
1266 p_token1 => g_sqlcode_token,
1267 p_token1_value => sqlcode,
1268 p_token2 => g_sqlerrm_token,
1269 p_token2_value => 'attribute 12 Length');
1270 -- notify caller of an error
1271 x_return_status := OKC_API.G_RET_STS_ERROR;
1272 -- halt further validation of this column
1273 RAISE G_EXCEPTION_HALT_VALIDATION;
1274 End If;
1275
1276 Exception
1277 When G_EXCEPTION_HALT_VALIDATION Then
1278 NULL;
1279 When OTHERS Then
1280 -- store SQL error message on message stack for caller
1281 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1282 p_msg_name => g_unexpected_error,
1283 p_token1 => g_sqlcode_token,
1284 p_token1_value => sqlcode,
1285 p_token2 => g_sqlerrm_token,
1286 p_token2_value => sqlerrm);
1287 -- notify caller of an UNEXPECTED error
1288 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1289 END validate_attribute12;
1290
1291
1292
1293 PROCEDURE validate_attribute13(x_return_status OUT NOCOPY varchar2,
1294 P_attribute13 IN varchar2)
1295 Is
1296 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1297 Begin
1298 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1299 -- call column length utility
1300 OKC_UTIL.CHECK_LENGTH
1301 (
1302 p_view_name => 'OKS_SERV_AVAILS_V'
1303 ,p_col_name => 'attribute13'
1304 ,p_col_value => p_attribute13
1305 ,x_return_status => l_return_status
1306 );
1307
1308 -- verify that length is within allowed limits
1309 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1310 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1311 p_msg_name => g_unexpected_error,
1312 p_token1 => g_sqlcode_token,
1313 p_token1_value => sqlcode,
1314 p_token2 => g_sqlerrm_token,
1315 p_token2_value => 'attribute 3 Length');
1316 -- notify caller of an error
1317 x_return_status := OKC_API.G_RET_STS_ERROR;
1318 -- halt further validation of this column
1319 RAISE G_EXCEPTION_HALT_VALIDATION;
1320 End If;
1321
1322 Exception
1323 When G_EXCEPTION_HALT_VALIDATION Then
1324 NULL;
1325 When OTHERS Then
1326 -- store SQL error message on message stack for caller
1327 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1328 p_msg_name => g_unexpected_error,
1329 p_token1 => g_sqlcode_token,
1330 p_token1_value => sqlcode,
1331 p_token2 => g_sqlerrm_token,
1332 p_token2_value => sqlerrm);
1333 -- notify caller of an UNEXPECTED error
1334 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1335 END validate_attribute13;
1336
1337 PROCEDURE validate_attribute14 (x_return_status OUT NOCOPY varchar2,
1338 P_attribute14 IN varchar2)
1339 Is
1340 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1341 Begin
1342
1343 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1344
1345 -- call column length utility
1346 OKC_UTIL.CHECK_LENGTH
1347 (
1348 p_view_name => 'OKS_SERV_AVAILS_V'
1349 ,p_col_name => 'attribute14'
1350 ,p_col_value => p_attribute14
1351 ,x_return_status => l_return_status
1352 );
1353
1354 -- verify that length is within allowed limits
1355 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1356 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1357 p_msg_name => g_unexpected_error,
1358 p_token1 => g_sqlcode_token,
1359 p_token1_value => sqlcode,
1360 p_token2 => g_sqlerrm_token,
1361 p_token2_value => 'attribute 14 Length');
1362 -- notify caller of an error
1363 x_return_status := OKC_API.G_RET_STS_ERROR;
1364 -- halt further validation of this column
1365 RAISE G_EXCEPTION_HALT_VALIDATION;
1366 End If;
1367
1368 Exception
1369 When G_EXCEPTION_HALT_VALIDATION Then
1370 NULL;
1371 When OTHERS Then
1372 -- store SQL error message on message stack for caller
1373 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1374 p_msg_name => g_unexpected_error,
1375 p_token1 => g_sqlcode_token,
1376 p_token1_value => sqlcode,
1377 p_token2 => g_sqlerrm_token,
1378 p_token2_value => sqlerrm);
1379
1380 -- notify caller of an UNEXPECTED error
1381 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1382 END validate_attribute14;
1383
1384
1385 PROCEDURE validate_attribute15(x_return_status OUT NOCOPY varchar2,
1386 P_attribute15 IN varchar2)
1387 Is
1388 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1389 Begin
1390
1391 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1392 -- call column length utility
1393 OKC_UTIL.CHECK_LENGTH
1394 (
1395 p_view_name => 'OKS_SERV_AVAILS_V'
1396 ,p_col_name => 'attribute15'
1397 ,p_col_value => p_attribute15
1398 ,x_return_status => l_return_status
1399 );
1400
1401 -- verify that length is within allowed limits
1402 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1403 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1404 p_msg_name => g_unexpected_error,
1405 p_token1 => g_sqlcode_token,
1406 p_token1_value => sqlcode,
1407 p_token2 => g_sqlerrm_token,
1408 p_token2_value => 'attribute 15 Length');
1409
1410 -- notify caller of an error
1411 x_return_status := OKC_API.G_RET_STS_ERROR;
1412 -- halt further validation of this column
1413 RAISE G_EXCEPTION_HALT_VALIDATION;
1414 End If;
1415
1416 Exception
1417 When G_EXCEPTION_HALT_VALIDATION Then
1418 NULL;
1419 When OTHERS Then
1420 -- store SQL error message on message stack for caller
1421 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1422 p_msg_name => g_unexpected_error,
1423 p_token1 => g_sqlcode_token,
1424 p_token1_value => sqlcode,
1425 p_token2 => g_sqlerrm_token,
1426 p_token2_value => sqlerrm);
1427
1428 -- notify caller of an UNEXPECTED error
1429 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1430 END validate_attribute15;
1431
1432
1433 -----------------------------------------------
1434 -- Validate_Attributes for:OKS_SERV_AVAILS_V --
1435 -----------------------------------------------
1436 FUNCTION Validate_Attributes (
1437 p_savv_rec IN savv_rec_type
1438 )
1439 Return VARCHAR2 Is
1440 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1441 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1442
1443 Begin
1444 -- call OKC_UTIL.ADD_VIEW to prepare the PL/SQL table to hold columns of view
1445
1446 OKC_UTIL.ADD_VIEW('OKS_SERV_AVAILS_V',x_return_status);
1447
1448 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1449 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1450 -- need to leave
1451 l_return_status := x_return_status;
1452 RAISE G_EXCEPTION_HALT_VALIDATION;
1453 ELSE
1454 -- record that there is a error
1455 l_return_status := x_return_status;
1456 END IF;
1457 END IF;
1458
1459 --Column Level Validation
1460
1461 --ID
1462 validate_id(x_return_status, p_savv_rec.id);
1463
1464 -- store the highest degree of error
1465 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1466 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1467 -- need to leave
1468 l_return_status := x_return_status;
1469 RAISE G_EXCEPTION_HALT_VALIDATION;
1470 ELSE
1471 -- record that there was an error
1472 l_return_status := x_return_status;
1473 END IF;
1474 END IF;
1475 --OBJECT_VERSION_NUMBER
1476 validate_objvernum(x_return_status, p_savv_rec.object_version_number);
1477
1478 -- store the highest degree of error
1479 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1480 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1481 -- need to leave
1482 l_return_status := x_return_status;
1483 RAISE G_EXCEPTION_HALT_VALIDATION;
1484 ELSE
1485 -- record that there was an error
1486 l_return_status := x_return_status;
1487 END IF;
1488 END IF;
1489
1490 --GENERAL_YN
1491 validate_general_yn(x_return_status, p_savv_rec.general_yn);
1492 -- store the highest degree of error
1493 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1494 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1495 -- need to leave
1496 l_return_status := x_return_status;
1497 RAISE G_EXCEPTION_HALT_VALIDATION;
1498 ELSE
1499 -- record that there was an error
1500 l_return_status := x_return_status;
1501 END IF;
1502 END IF;
1503
1504 --EXCEPT_OBJECT_TYPE
1505 validate_except_object_type(x_return_status, p_savv_rec.except_object_type);
1506 -- store the highest degree of error
1507 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1508
1509 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1510
1511 -- need to leave
1512 l_return_status := x_return_status;
1513 RAISE G_EXCEPTION_HALT_VALIDATION;
1514 ELSE
1515 -- record that there was an error
1516 l_return_status := x_return_status;
1517 END IF;
1518 END IF;
1519
1520 /* --START_DATE_ACTIVE
1521 validate_start_date_active(x_return_status, p_savv_rec.start_date_active);
1522
1523 -- store the highest degree of error
1524 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1525 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1526
1527
1528 -- need to leave
1529 l_return_status := x_return_status;
1530 RAISE G_EXCEPTION_HALT_VALIDATION;
1531 ELSE
1532 -- record that there was an error
1533 l_return_status := x_return_status;
1534 END IF;
1535 END IF;
1536
1537 --END_DATE_ACTIVE
1538 validate_end_date_active(x_return_status, p_savv_rec.end_date_active);
1539
1540 -- store the highest degree of error
1541 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1542 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1543
1544 -- need to leave
1545 l_return_status := x_return_status;
1546 RAISE G_EXCEPTION_HALT_VALIDATION;
1547 ELSE
1548 -- record that there was an error
1549 l_return_status := x_return_status;
1550 END IF;
1551 END IF;
1552
1553 */
1554 --OBJECT1_ID1
1555 validate_object1_id1(x_return_status, p_savv_rec.object1_id1);
1556 -- store the highest degree of error
1557 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1558 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1559 -- need to leave
1560 l_return_status := x_return_status;
1561 RAISE G_EXCEPTION_HALT_VALIDATION;
1562 ELSE
1563 -- record that there was an error
1564 l_return_status := x_return_status;
1565 END IF;
1566 END IF;
1567
1568 --OBJECT1_ID2
1569 validate_object1_id2(x_return_status, p_savv_rec.object1_id2);
1570
1571 -- store the highest degree of error
1572 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1573 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1574 -- need to leave
1575 l_return_status := x_return_status;
1576 RAISE G_EXCEPTION_HALT_VALIDATION;
1577 ELSE
1578 -- record that there was an error
1579 l_return_status := x_return_status;
1580 END IF;
1581 END IF;
1582
1583
1584 --JTOT_OBJECT1_CODE
1585 validate_Jtot_object1_code(x_return_status, p_savv_rec.jtot_object1_code);
1586 -- store the highest degree of error
1587 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1588 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1589 -- need to leave
1590 l_return_status := x_return_status;
1591 RAISE G_EXCEPTION_HALT_VALIDATION;
1592 ELSE
1593 -- record that there was an error
1594 l_return_status := x_return_status;
1595 END IF;
1596 END IF;
1597
1598
1599 --ATTRIBUTE_CATEGORY
1600
1601 validate_attribute_category(x_return_status, p_savv_rec.attribute_category);
1602
1603 -- store the highest degree of error
1604 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1605 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1606 -- need to leave
1607 l_return_status := x_return_status;
1608 RAISE G_EXCEPTION_HALT_VALIDATION;
1609 ELSE
1610 -- record that there was an error
1611 l_return_status := x_return_status;
1612 END IF;
1613 END IF;
1614
1615
1616 --ATTRIBUTE1
1617
1618 validate_attribute1(x_return_status, p_savv_rec.attribute1);
1619
1620 -- store the highest degree of error
1621 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1622 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1623 -- need to leave
1624 l_return_status := x_return_status;
1625 RAISE G_EXCEPTION_HALT_VALIDATION;
1626 ELSE
1627 -- record that there was an error
1628 l_return_status := x_return_status;
1629 END IF;
1630 END IF;
1631
1632 --ATTRIBUTE2
1633
1634 validate_attribute2(x_return_status, p_savv_rec.attribute2);
1635
1636 -- store the highest degree of error
1637 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1638 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1639 -- need to leave
1640 l_return_status := x_return_status;
1641 RAISE G_EXCEPTION_HALT_VALIDATION;
1642 ELSE
1643 -- record that there was an error
1644 l_return_status := x_return_status;
1645 END IF;
1646 END IF;
1647
1648
1649 --ATTRIBUTE3
1650
1651 validate_attribute3(x_return_status, p_savv_rec.attribute3);
1652
1653 -- store the highest degree of error
1654 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1655 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1656 -- need to leave
1657 l_return_status := x_return_status;
1658 RAISE G_EXCEPTION_HALT_VALIDATION;
1659 ELSE
1660 -- record that there was an error
1661 l_return_status := x_return_status;
1662 END IF;
1663 END IF;
1664
1665
1666 --ATTRIBUTE4
1667 validate_attribute4(x_return_status, p_savv_rec.attribute4);
1668
1669 -- store the highest degree of error
1670 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1671 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1672 -- need to leave
1673 l_return_status := x_return_status;
1674 RAISE G_EXCEPTION_HALT_VALIDATION;
1675 ELSE
1676 -- record that there was an error
1677 l_return_status := x_return_status;
1678 END IF;
1679 END IF;
1680
1681
1682 --ATTRIBUTE5
1683 validate_attribute5(x_return_status, p_savv_rec.attribute5);
1684
1685 -- store the highest degree of error
1686 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1687 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1688 -- need to leave
1689 l_return_status := x_return_status;
1690 RAISE G_EXCEPTION_HALT_VALIDATION;
1691 ELSE
1692 -- record that there was an error
1693 l_return_status := x_return_status;
1694 END IF;
1695 END IF;
1696
1697
1698 --ATTRIBUTE6
1699
1700 validate_attribute6(x_return_status, p_savv_rec.attribute6);
1701
1702 -- store the highest degree of error
1703 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1704 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1705 -- need to leave
1706 l_return_status := x_return_status;
1707 RAISE G_EXCEPTION_HALT_VALIDATION;
1708 ELSE
1709 -- record that there was an error
1710 l_return_status := x_return_status;
1711 END IF;
1712 END IF;
1713
1714
1715 --ATTRIBUTE7
1716
1717 validate_attribute7(x_return_status, p_savv_rec.attribute7);
1718
1719 -- store the highest degree of error
1720 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1721 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1722 -- need to leave
1723 l_return_status := x_return_status;
1724 RAISE G_EXCEPTION_HALT_VALIDATION;
1725 ELSE
1726 -- record that there was an error
1727 l_return_status := x_return_status;
1728 END IF;
1729 END IF;
1730
1731
1732 --ATTRIBUTE8
1733 validate_attribute8(x_return_status, p_savv_rec.attribute8);
1734
1735 -- store the highest degree of error
1736 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1737 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1738 -- need to leave
1739 l_return_status := x_return_status;
1740 RAISE G_EXCEPTION_HALT_VALIDATION;
1741 ELSE
1742 -- record that there was an error
1743 l_return_status := x_return_status;
1744 END IF;
1745 END IF;
1746
1747
1748 --ATTRIBUTE9
1749 validate_attribute9(x_return_status, p_savv_rec.attribute9);
1750
1751 -- store the highest degree of error
1752 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1753 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1754 -- need to leave
1755 l_return_status := x_return_status;
1756 RAISE G_EXCEPTION_HALT_VALIDATION;
1757 ELSE
1758 -- record that there was an error
1759 l_return_status := x_return_status;
1760 END IF;
1761 END IF;
1762
1763
1764 --ATTRIBUTE10
1765
1766 validate_attribute10(x_return_status, p_savv_rec.attribute10);
1767
1768 -- store the highest degree of error
1769 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1770 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1771 -- need to leave
1772 l_return_status := x_return_status;
1773 RAISE G_EXCEPTION_HALT_VALIDATION;
1774 ELSE
1775 -- record that there was an error
1776 l_return_status := x_return_status;
1777 END IF;
1778 END IF;
1779
1780
1781 --ATTRIBUTE11
1782
1783 validate_attribute11(x_return_status, p_savv_rec.attribute11);
1784
1785 -- store the highest degree of error
1786 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1787 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1788 -- need to leave
1789 l_return_status := x_return_status;
1790 RAISE G_EXCEPTION_HALT_VALIDATION;
1791 ELSE
1792 -- record that there was an error
1793 l_return_status := x_return_status;
1794 END IF;
1795 END IF;
1796
1797
1798 --ATTRIBUTE12
1799
1800 validate_attribute12(x_return_status, p_savv_rec.attribute12);
1801
1802 -- store the highest degree of error
1803 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1804 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1805 -- need to leave
1806 l_return_status := x_return_status;
1807 RAISE G_EXCEPTION_HALT_VALIDATION;
1808 ELSE
1809 -- record that there was an error
1810 l_return_status := x_return_status;
1811 END IF;
1812 END IF;
1813
1814
1815 --ATTRIBUTE13
1816 validate_attribute13(x_return_status, p_savv_rec.attribute13);
1817
1818 -- store the highest degree of error
1819 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1820 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1821 -- need to leave
1822 l_return_status := x_return_status;
1823 RAISE G_EXCEPTION_HALT_VALIDATION;
1824 ELSE
1825 -- record that there was an error
1826 l_return_status := x_return_status;
1827 END IF;
1828 END IF;
1829
1830
1831 --ATTRIBUTE14
1832
1833 validate_attribute14(x_return_status, p_savv_rec.attribute14);
1834
1835 -- store the highest degree of error
1836 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1837 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1838 -- need to leave
1839 l_return_status := x_return_status;
1840 RAISE G_EXCEPTION_HALT_VALIDATION;
1841 ELSE
1842 -- record that there was an error
1843 l_return_status := x_return_status;
1844 END IF;
1845 END IF;
1846
1847 --ATTRIBUTE15
1848
1849 validate_attribute15(x_return_status, p_savv_rec.attribute15);
1850
1851 -- store the highest degree of error
1852 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1853 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1854 -- need to leave
1855 l_return_status := x_return_status;
1856 RAISE G_EXCEPTION_HALT_VALIDATION;
1857 ELSE
1858 -- record that there was an error
1859 l_return_status := x_return_status;
1860 END IF;
1861 END IF;
1862
1863 Raise G_EXCEPTION_HALT_VALIDATION;
1864
1865 Exception
1866
1867 When G_EXCEPTION_HALT_VALIDATION Then
1868
1869 Return (l_return_status);
1870
1871 When OTHERS Then
1872 -- store SQL error message on message stack for caller
1873 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1874 p_msg_name => g_unexpected_error,
1875 p_token1 => g_sqlcode_token,
1876 p_token1_value => sqlcode,
1877 p_token2 => g_sqlerrm_token,
1878 p_token2_value => sqlerrm);
1879
1880 -- notify caller of an UNEXPECTED error
1881 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1882 Return(l_return_status);
1883
1884 END validate_attributes;
1885
1886
1887 /*
1888 -----------------------------------------------
1889 -- Validate_Attributes for:OKS_SERV_AVAILS_V --
1890 -----------------------------------------------
1891 FUNCTION Validate_Attributes (
1892 p_savv_rec IN savv_rec_type
1893 ) RETURN VARCHAR2 IS
1894 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1895 BEGIN
1896 IF p_savv_rec.id = OKC_API.G_MISS_NUM OR
1897 p_savv_rec.id IS NULL
1898 THEN
1899 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
1900 l_return_status := OKC_API.G_RET_STS_ERROR;
1901 ELSIF p_savv_rec.object_version_number = OKC_API.G_MISS_NUM OR
1902 p_savv_rec.object_version_number IS NULL
1903 THEN
1904 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
1905 l_return_status := OKC_API.G_RET_STS_ERROR;
1906 ELSIF p_savv_rec.general_yn = OKC_API.G_MISS_CHAR OR
1907 p_savv_rec.general_yn IS NULL
1908 THEN
1909 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'general_yn');
1910 l_return_status := OKC_API.G_RET_STS_ERROR;
1911 ELSIF p_savv_rec.except_object_type = OKC_API.G_MISS_CHAR OR
1912 p_savv_rec.except_object_type IS NULL
1913 THEN
1914 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'except_object_type');
1915 l_return_status := OKC_API.G_RET_STS_ERROR;
1916 END IF;
1917 RETURN(l_return_status);
1918 END Validate_Attributes;
1919 */
1920
1921 ---------------------------------------------------------------------------
1922 -- PROCEDURE Validate_Record
1923 ---------------------------------------------------------------------------
1924 -------------------------------------------
1925 -- Validate_Record for:OKS_SERV_AVAILS_V --
1926 -------------------------------------------
1927 FUNCTION Validate_Record (
1928 p_savv_rec IN savv_rec_type
1929 ) RETURN VARCHAR2 IS
1930 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1931 BEGIN
1932 RETURN (l_return_status);
1933 END Validate_Record;
1934
1935 ---------------------------------------------------------------------------
1936 -- PROCEDURE Migrate
1937 ---------------------------------------------------------------------------
1938 PROCEDURE migrate (
1939 p_from IN savv_rec_type,
1940 p_to IN OUT NOCOPY sav_rec_type
1941 ) IS
1942 BEGIN
1943 p_to.id := p_from.id;
1944 p_to.object1_id1 := p_from.object1_id1;
1945 p_to.object1_id2 := p_from.object1_id2;
1946 p_to.JTOT_OBJECT1_CODE := p_from.JTOT_OBJECT1_CODE;
1947 p_to.object_version_number := p_from.object_version_number;
1948 p_to.created_by := p_from.created_by;
1949 p_to.creation_date := p_from.creation_date;
1950 p_to.last_updated_by := p_from.last_updated_by;
1951 p_to.last_update_date := p_from.last_update_date;
1952 p_to.general_yn := p_from.general_yn;
1953 p_to.except_object_type := p_from.except_object_type;
1954 p_to.start_date_active := p_from.start_date_active;
1955 p_to.end_date_active := p_from.end_date_active;
1956 p_to.last_update_login := p_from.last_update_login;
1957 p_to.attribute_category := p_from.attribute_category;
1958 p_to.attribute1 := p_from.attribute1;
1959 p_to.attribute2 := p_from.attribute2;
1960 p_to.attribute3 := p_from.attribute3;
1961 p_to.attribute4 := p_from.attribute4;
1962 p_to.attribute5 := p_from.attribute5;
1963 p_to.attribute6 := p_from.attribute6;
1964 p_to.attribute7 := p_from.attribute7;
1965 p_to.attribute8 := p_from.attribute8;
1966 p_to.attribute9 := p_from.attribute9;
1967 p_to.attribute10 := p_from.attribute10;
1968 p_to.attribute11 := p_from.attribute11;
1969 p_to.attribute12 := p_from.attribute12;
1970 p_to.attribute13 := p_from.attribute13;
1971 p_to.attribute14 := p_from.attribute14;
1972 p_to.attribute15 := p_from.attribute15;
1973 END migrate;
1974 PROCEDURE migrate (
1975 p_from IN sav_rec_type,
1976 p_to IN OUT NOCOPY savv_rec_type
1977 ) IS
1978 BEGIN
1979 p_to.id := p_from.id;
1980 p_to.object1_id1 := p_from.object1_id1;
1981 p_to.object1_id2 := p_from.object1_id2;
1982 p_to.JTOT_OBJECT1_CODE := p_from.JTOT_OBJECT1_CODE;
1983 p_to.object_version_number := p_from.object_version_number;
1984 p_to.created_by := p_from.created_by;
1985 p_to.creation_date := p_from.creation_date;
1986 p_to.last_updated_by := p_from.last_updated_by;
1987 p_to.last_update_date := p_from.last_update_date;
1988 p_to.general_yn := p_from.general_yn;
1989 p_to.except_object_type := p_from.except_object_type;
1990 p_to.start_date_active := p_from.start_date_active;
1991 p_to.end_date_active := p_from.end_date_active;
1992 p_to.last_update_login := p_from.last_update_login;
1993 p_to.attribute_category := p_from.attribute_category;
1994 p_to.attribute1 := p_from.attribute1;
1995 p_to.attribute2 := p_from.attribute2;
1996 p_to.attribute3 := p_from.attribute3;
1997 p_to.attribute4 := p_from.attribute4;
1998 p_to.attribute5 := p_from.attribute5;
1999 p_to.attribute6 := p_from.attribute6;
2000 p_to.attribute7 := p_from.attribute7;
2001 p_to.attribute8 := p_from.attribute8;
2002 p_to.attribute9 := p_from.attribute9;
2003 p_to.attribute10 := p_from.attribute10;
2004 p_to.attribute11 := p_from.attribute11;
2005 p_to.attribute12 := p_from.attribute12;
2006 p_to.attribute13 := p_from.attribute13;
2007 p_to.attribute14 := p_from.attribute14;
2008 p_to.attribute15 := p_from.attribute15;
2009 END migrate;
2010
2011 ---------------------------------------------------------------------------
2012 -- PROCEDURE validate_row
2013 ---------------------------------------------------------------------------
2014 ----------------------------------------
2015 -- validate_row for:OKS_SERV_AVAILS_V --
2016 ----------------------------------------
2017 PROCEDURE validate_row(
2018 p_api_version IN NUMBER,
2019 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2020 x_return_status OUT NOCOPY VARCHAR2,
2021 x_msg_count OUT NOCOPY NUMBER,
2022 x_msg_data OUT NOCOPY VARCHAR2,
2023 p_savv_rec IN savv_rec_type) IS
2024
2025 l_api_version CONSTANT NUMBER := 1;
2026 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2027 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2028 l_savv_rec savv_rec_type := p_savv_rec;
2029 l_sav_rec sav_rec_type;
2030 BEGIN
2031 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2032 G_PKG_NAME,
2033 p_init_msg_list,
2034 l_api_version,
2035 p_api_version,
2036 '_PVT',
2037 x_return_status);
2038 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2039 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2040 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2041 RAISE OKC_API.G_EXCEPTION_ERROR;
2042 END IF;
2043 --- Validate all non-missing attributes (Item Level Validation)
2044
2045 l_return_status := Validate_Attributes(l_savv_rec);
2046
2047
2048 --- If any errors happen abort API
2049 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2050
2051
2052 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2053 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2054 RAISE OKC_API.G_EXCEPTION_ERROR;
2055 END IF;
2056 l_return_status := Validate_Record(l_savv_rec);
2057 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2058 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2059 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2060 RAISE OKC_API.G_EXCEPTION_ERROR;
2061 END IF;
2062 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2063 EXCEPTION
2064 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2065 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2066 (
2067 l_api_name,
2068 G_PKG_NAME,
2069 'OKC_API.G_RET_STS_ERROR',
2070 x_msg_count,
2071 x_msg_data,
2072 '_PVT'
2073 );
2074 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2075 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2076 (
2077 l_api_name,
2078 G_PKG_NAME,
2079 'OKC_API.G_RET_STS_UNEXP_ERROR',
2080 x_msg_count,
2081 x_msg_data,
2082 '_PVT'
2083 );
2084 WHEN OTHERS THEN
2085 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2086 (
2087 l_api_name,
2088 G_PKG_NAME,
2089 'OTHERS',
2090 x_msg_count,
2091 x_msg_data,
2092 '_PVT'
2093 );
2094 END validate_row;
2095 ------------------------------------------
2096 -- PL/SQL TBL validate_row for:SAVV_TBL --
2097 ------------------------------------------
2098 PROCEDURE validate_row(
2099 p_api_version IN NUMBER,
2100 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2101 x_return_status OUT NOCOPY VARCHAR2,
2102 x_msg_count OUT NOCOPY NUMBER,
2103 x_msg_data OUT NOCOPY VARCHAR2,
2104 p_savv_tbl IN savv_tbl_type) IS
2105
2106 l_api_version CONSTANT NUMBER := 1;
2107 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2108 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2109 i NUMBER := 0;
2110 BEGIN
2111 OKC_API.init_msg_list(p_init_msg_list);
2112 -- Make sure PL/SQL table has records in it before passing
2113 IF (p_savv_tbl.COUNT > 0) THEN
2114 i := p_savv_tbl.FIRST;
2115 LOOP
2116 validate_row (
2117 p_api_version => p_api_version,
2118 p_init_msg_list => OKC_API.G_FALSE,
2119 x_return_status => x_return_status,
2120 x_msg_count => x_msg_count,
2121 x_msg_data => x_msg_data,
2122 p_savv_rec => p_savv_tbl(i));
2123 EXIT WHEN (i = p_savv_tbl.LAST);
2124 i := p_savv_tbl.NEXT(i);
2125 END LOOP;
2126 END IF;
2127 EXCEPTION
2128 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2129 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2130 (
2131 l_api_name,
2132 G_PKG_NAME,
2133 'OKC_API.G_RET_STS_ERROR',
2134 x_msg_count,
2135 x_msg_data,
2136 '_PVT'
2137 );
2138 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2139
2140 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2141 (
2142 l_api_name,
2143 G_PKG_NAME,
2144 'OKC_API.G_RET_STS_UNEXP_ERROR',
2145 x_msg_count,
2146 x_msg_data,
2147 '_PVT'
2148 );
2149 WHEN OTHERS THEN
2150 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2151 (
2152 l_api_name,
2153 G_PKG_NAME,
2154 'OTHERS',
2155 x_msg_count,
2156 x_msg_data,
2157 '_PVT'
2158 );
2159 END validate_row;
2160
2161 ---------------------------------------------------------------------------
2162 -- PROCEDURE insert_row
2163 ---------------------------------------------------------------------------
2164 ------------------------------------
2165 -- insert_row for:OKS_SERV_AVAILS --
2166 ------------------------------------
2167 PROCEDURE insert_row(
2168 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2169 x_return_status OUT NOCOPY VARCHAR2,
2170 x_msg_count OUT NOCOPY NUMBER,
2171 x_msg_data OUT NOCOPY VARCHAR2,
2172 p_sav_rec IN sav_rec_type,
2173 x_sav_rec OUT NOCOPY sav_rec_type) IS
2174
2175 l_api_version CONSTANT NUMBER := 1;
2176 l_api_name CONSTANT VARCHAR2(30) := 'AVAILS_insert_row';
2177 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2178 l_sav_rec sav_rec_type := p_sav_rec;
2179 l_def_sav_rec sav_rec_type;
2180 ----------------------------------------
2181 -- Set_Attributes for:OKS_SERV_AVAILS --
2182 ----------------------------------------
2183
2184 FUNCTION Set_Attributes (
2185 p_sav_rec IN sav_rec_type,
2186 x_sav_rec OUT NOCOPY sav_rec_type
2187 ) RETURN VARCHAR2 IS
2188 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2189 BEGIN
2190 x_sav_rec := p_sav_rec;
2191 RETURN(l_return_status);
2192 END Set_Attributes;
2193 BEGIN
2194
2195
2196 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2197 p_init_msg_list,
2198 '_PVT',
2199 x_return_status);
2200 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2201 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2202 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2203 RAISE OKC_API.G_EXCEPTION_ERROR;
2204 END IF;
2205 --- Setting item attributes
2206 l_return_status := Set_Attributes(
2207 p_sav_rec, -- IN
2208 l_sav_rec); -- OUT
2209 --- If any errors happen abort API
2210 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2211 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2212 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2213 RAISE OKC_API.G_EXCEPTION_ERROR;
2214 END IF;
2215
2216 INSERT INTO OKS_SERV_AVAILS(
2217 id,
2218 object1_id1,
2219 object1_id2,
2220 JTOT_OBJECT1_CODE,
2221 object_version_number,
2222 created_by,
2223 creation_date,
2224 last_updated_by,
2225 last_update_date,
2226 general_yn,
2227 except_object_type,
2228 start_date_active,
2229 end_date_active,
2230 last_update_login,
2231 attribute_category,
2232 attribute1,
2233 attribute2,
2234 attribute3,
2235 attribute4,
2236 attribute5,
2237 attribute6,
2238 attribute7,
2239 attribute8,
2240 attribute9,
2241 attribute10,
2242 attribute11,
2243 attribute12,
2244 attribute13,
2245 attribute14,
2246 attribute15)
2247 VALUES (
2248 l_sav_rec.id,
2249 l_sav_rec.object1_id1,
2250 l_sav_rec.object1_id2,
2251 l_sav_rec.JTOT_OBJECT1_CODE,
2252 l_sav_rec.object_version_number,
2253 l_sav_rec.created_by,
2254 l_sav_rec.creation_date,
2255 l_sav_rec.last_updated_by,
2256 l_sav_rec.last_update_date,
2257 l_sav_rec.general_yn,
2258 l_sav_rec.except_object_type,
2259 l_sav_rec.start_date_active,
2260 l_sav_rec.end_date_active,
2261 l_sav_rec.last_update_login,
2262 l_sav_rec.attribute_category,
2263 l_sav_rec.attribute1,
2264 l_sav_rec.attribute2,
2265 l_sav_rec.attribute3,
2266 l_sav_rec.attribute4,
2267 l_sav_rec.attribute5,
2268 l_sav_rec.attribute6,
2269 l_sav_rec.attribute7,
2270 l_sav_rec.attribute8,
2271 l_sav_rec.attribute9,
2272 l_sav_rec.attribute10,
2273 l_sav_rec.attribute11,
2274 l_sav_rec.attribute12,
2275 l_sav_rec.attribute13,
2276 l_sav_rec.attribute14,
2277 l_sav_rec.attribute15);
2278
2279 -- Set OUT values
2280
2281 x_sav_rec := l_sav_rec;
2282 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2283 EXCEPTION
2284 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2285 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2286 (
2287 l_api_name,
2288 G_PKG_NAME,
2289 'OKC_API.G_RET_STS_ERROR',
2290 x_msg_count,
2291 x_msg_data,
2292 '_PVT'
2293 );
2294 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2295 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2296 (
2297 l_api_name,
2298 G_PKG_NAME,
2299 'OKC_API.G_RET_STS_UNEXP_ERROR',
2300 x_msg_count,
2301 x_msg_data,
2302 '_PVT'
2303 );
2304 WHEN OTHERS THEN
2305 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2306 (
2307 l_api_name,
2308 G_PKG_NAME,
2309 'OTHERS',
2310 x_msg_count,
2311 x_msg_data,
2312 '_PVT'
2313 );
2314 END insert_row;
2315 --------------------------------------
2316 -- insert_row for:OKS_SERV_AVAILS_V --
2317 --------------------------------------
2318 PROCEDURE insert_row(
2319 p_api_version IN NUMBER,
2320 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2321 x_return_status OUT NOCOPY VARCHAR2,
2322 x_msg_count OUT NOCOPY NUMBER,
2323 x_msg_data OUT NOCOPY VARCHAR2,
2324 p_savv_rec IN savv_rec_type,
2325 x_savv_rec OUT NOCOPY savv_rec_type) IS
2326
2327 l_api_version CONSTANT NUMBER := 1;
2328 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2329 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2330 l_savv_rec savv_rec_type;
2331 l_def_savv_rec savv_rec_type;
2332 l_sav_rec sav_rec_type;
2333 lx_sav_rec sav_rec_type;
2334 -------------------------------
2335 -- FUNCTION fill_who_columns --
2336 -------------------------------
2337 FUNCTION fill_who_columns (
2338 p_savv_rec IN savv_rec_type
2339 ) RETURN savv_rec_type IS
2340 l_savv_rec savv_rec_type := p_savv_rec;
2341 BEGIN
2342 l_savv_rec.CREATION_DATE := SYSDATE;
2343 l_savv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2344 l_savv_rec.LAST_UPDATE_DATE := SYSDATE;
2345 l_savv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2346 l_savv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2347 RETURN(l_savv_rec);
2348 END fill_who_columns;
2349 ------------------------------------------
2350 -- Set_Attributes for:OKS_SERV_AVAILS_V --
2351 ------------------------------------------
2352 FUNCTION Set_Attributes (
2353 p_savv_rec IN savv_rec_type,
2354 x_savv_rec OUT NOCOPY savv_rec_type
2355 ) RETURN VARCHAR2 IS
2356 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2357 BEGIN
2358 x_savv_rec := p_savv_rec;
2359 x_savv_rec.OBJECT_VERSION_NUMBER := 1;
2360 RETURN(l_return_status);
2361 END Set_Attributes;
2362 BEGIN
2363 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2364 G_PKG_NAME,
2365 p_init_msg_list,
2366 l_api_version,
2367 p_api_version,
2368 '_PVT',
2369 x_return_status);
2370 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2371 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2372 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2373 RAISE OKC_API.G_EXCEPTION_ERROR;
2374 END IF;
2375 l_savv_rec := null_out_defaults(p_savv_rec);
2376 -- Set primary key value
2377 l_savv_rec.ID := get_seq_id;
2378 --- Setting item attributes
2379 l_return_status := Set_Attributes(
2380 l_savv_rec, -- IN
2381 l_def_savv_rec); -- OUT
2382 --- If any errors happen abort API
2383 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2384 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2385 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2386 RAISE OKC_API.G_EXCEPTION_ERROR;
2387 END IF;
2388 l_def_savv_rec := fill_who_columns(l_def_savv_rec);
2389 --- Validate all non-missing attributes (Item Level Validation)
2390
2391
2392
2393
2394 l_return_status := Validate_Attributes(l_def_savv_rec);
2395
2396
2397
2398
2399 --- If any errors happen abort API
2400 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2401 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2402 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2403 RAISE OKC_API.G_EXCEPTION_ERROR;
2404 END IF;
2405 l_return_status := Validate_Record(l_def_savv_rec);
2406 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2407 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2408 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2409 RAISE OKC_API.G_EXCEPTION_ERROR;
2410 END IF;
2411 --------------------------------------
2412 -- Move VIEW record to "Child" records
2413 --------------------------------------
2414 migrate(l_def_savv_rec, l_sav_rec);
2415 --------------------------------------------
2416 -- Call the INSERT_ROW for each child record
2417 --------------------------------------------
2418 insert_row(
2419 p_init_msg_list,
2420 x_return_status,
2421 x_msg_count,
2422 x_msg_data,
2423 l_sav_rec,
2424 lx_sav_rec
2425 );
2426 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2427 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2428 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2429 RAISE OKC_API.G_EXCEPTION_ERROR;
2430 END IF;
2431 migrate(lx_sav_rec, l_def_savv_rec);
2432 -- Set OUT values
2433 x_savv_rec := l_def_savv_rec;
2434 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2435 EXCEPTION
2436 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2437
2438 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2439 (
2440 l_api_name,
2441 G_PKG_NAME,
2442 'OKC_API.G_RET_STS_ERROR',
2443 x_msg_count,
2444 x_msg_data,
2445 '_PVT'
2446 );
2447 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2448
2449 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2450 (
2451 l_api_name,
2452 G_PKG_NAME,
2453 'OKC_API.G_RET_STS_UNEXP_ERROR',
2454 x_msg_count,
2455 x_msg_data,
2456 '_PVT'
2457 );
2458 WHEN OTHERS THEN
2459 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2460 (
2461 l_api_name,
2462 G_PKG_NAME,
2463 'OTHERS',
2464 x_msg_count,
2465 x_msg_data,
2466 '_PVT'
2467 );
2468 END insert_row;
2469 ----------------------------------------
2470 -- PL/SQL TBL insert_row for:SAVV_TBL --
2471 ----------------------------------------
2472 PROCEDURE insert_row(
2473 p_api_version IN NUMBER,
2474 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2475 x_return_status OUT NOCOPY VARCHAR2,
2476 x_msg_count OUT NOCOPY NUMBER,
2477 x_msg_data OUT NOCOPY VARCHAR2,
2478 p_savv_tbl IN savv_tbl_type,
2479 x_savv_tbl OUT NOCOPY savv_tbl_type) IS
2480
2481 l_api_version CONSTANT NUMBER := 1;
2482 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2483 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2484 i NUMBER := 0;
2485 BEGIN
2486 OKC_API.init_msg_list(p_init_msg_list);
2487 -- Make sure PL/SQL table has records in it before passing
2488 IF (p_savv_tbl.COUNT > 0) THEN
2489 i := p_savv_tbl.FIRST;
2490 LOOP
2491 insert_row (
2492 p_api_version => p_api_version,
2493 p_init_msg_list => OKC_API.G_FALSE,
2494 x_return_status => x_return_status,
2495 x_msg_count => x_msg_count,
2496 x_msg_data => x_msg_data,
2497 p_savv_rec => p_savv_tbl(i),
2498 x_savv_rec => x_savv_tbl(i));
2499 EXIT WHEN (i = p_savv_tbl.LAST);
2500 i := p_savv_tbl.NEXT(i);
2501 END LOOP;
2502 END IF;
2503 EXCEPTION
2504 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2505 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2506 (
2507 l_api_name,
2508 G_PKG_NAME,
2509 'OKC_API.G_RET_STS_ERROR',
2510 x_msg_count,
2511 x_msg_data,
2512 '_PVT'
2513 );
2514 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2515
2516 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2517 (
2518 l_api_name,
2519 G_PKG_NAME,
2520 'OKC_API.G_RET_STS_UNEXP_ERROR',
2521 x_msg_count,
2522 x_msg_data,
2523 '_PVT'
2524 );
2525 WHEN OTHERS THEN
2526 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2527 (
2528 l_api_name,
2529 G_PKG_NAME,
2530 'OTHERS',
2531 x_msg_count,
2532 x_msg_data,
2533 '_PVT'
2534 );
2535 END insert_row;
2536
2537 ---------------------------------------------------------------------------
2538 -- PROCEDURE lock_row
2539 ---------------------------------------------------------------------------
2540 ----------------------------------
2541 -- lock_row for:OKS_SERV_AVAILS --
2542 ----------------------------------
2543 PROCEDURE lock_row(
2544 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2545 x_return_status OUT NOCOPY VARCHAR2,
2546 x_msg_count OUT NOCOPY NUMBER,
2547 x_msg_data OUT NOCOPY VARCHAR2,
2548 p_sav_rec IN sav_rec_type) IS
2549
2550 E_Resource_Busy EXCEPTION;
2551 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2552 CURSOR lock_csr (p_sav_rec IN sav_rec_type) IS
2553 SELECT OBJECT_VERSION_NUMBER
2554 FROM OKS_SERV_AVAILS
2555 WHERE ID = p_sav_rec.id
2556 AND OBJECT_VERSION_NUMBER = p_sav_rec.object_version_number
2557 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2558
2559 CURSOR lchk_csr (p_sav_rec IN sav_rec_type) IS
2560 SELECT OBJECT_VERSION_NUMBER
2561 FROM OKS_SERV_AVAILS
2562 WHERE ID = p_sav_rec.id;
2563 l_api_version CONSTANT NUMBER := 1;
2564 l_api_name CONSTANT VARCHAR2(30) := 'AVAILS_lock_row';
2565 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2566 l_object_version_number OKS_SERV_AVAILS.OBJECT_VERSION_NUMBER%TYPE;
2567 lc_object_version_number OKS_SERV_AVAILS.OBJECT_VERSION_NUMBER%TYPE;
2568 l_row_notfound BOOLEAN := FALSE;
2569 lc_row_notfound BOOLEAN := FALSE;
2570 BEGIN
2571 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2572 p_init_msg_list,
2573 '_PVT',
2574 x_return_status);
2575 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2576 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2577 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2578 RAISE OKC_API.G_EXCEPTION_ERROR;
2579 END IF;
2580 BEGIN
2581 OPEN lock_csr(p_sav_rec);
2582 FETCH lock_csr INTO l_object_version_number;
2583 l_row_notfound := lock_csr%NOTFOUND;
2584 CLOSE lock_csr;
2585 EXCEPTION
2586 WHEN E_Resource_Busy THEN
2587 IF (lock_csr%ISOPEN) THEN
2588 CLOSE lock_csr;
2589 END IF;
2590 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2591 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2592 END;
2593
2594 IF ( l_row_notfound ) THEN
2595 OPEN lchk_csr(p_sav_rec);
2596 FETCH lchk_csr INTO lc_object_version_number;
2597 lc_row_notfound := lchk_csr%NOTFOUND;
2598 CLOSE lchk_csr;
2599 END IF;
2600 IF (lc_row_notfound) THEN
2601 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2602 RAISE OKC_API.G_EXCEPTION_ERROR;
2603 ELSIF lc_object_version_number > p_sav_rec.object_version_number THEN
2604 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2605 RAISE OKC_API.G_EXCEPTION_ERROR;
2606 ELSIF lc_object_version_number <> p_sav_rec.object_version_number THEN
2607 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2608 RAISE OKC_API.G_EXCEPTION_ERROR;
2609 ELSIF lc_object_version_number = -1 THEN
2610 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2611 RAISE OKC_API.G_EXCEPTION_ERROR;
2612 END IF;
2613 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2614 EXCEPTION
2615 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2616 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2617 (
2618 l_api_name,
2619 G_PKG_NAME,
2620 'OKC_API.G_RET_STS_ERROR',
2621 x_msg_count,
2622 x_msg_data,
2623 '_PVT'
2624 );
2625 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2626 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2627 (
2628 l_api_name,
2629 G_PKG_NAME,
2630 'OKC_API.G_RET_STS_UNEXP_ERROR',
2631 x_msg_count,
2632 x_msg_data,
2633 '_PVT'
2634 );
2635 WHEN OTHERS THEN
2636 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2637 (
2638 l_api_name,
2639 G_PKG_NAME,
2640 'OTHERS',
2641 x_msg_count,
2642 x_msg_data,
2643 '_PVT'
2644 );
2645 END lock_row;
2646 ------------------------------------
2647 -- lock_row for:OKS_SERV_AVAILS_V --
2648 ------------------------------------
2649 PROCEDURE lock_row(
2650 p_api_version IN NUMBER,
2651 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2652 x_return_status OUT NOCOPY VARCHAR2,
2653 x_msg_count OUT NOCOPY NUMBER,
2654 x_msg_data OUT NOCOPY VARCHAR2,
2655 p_savv_rec IN savv_rec_type) IS
2656
2657 l_api_version CONSTANT NUMBER := 1;
2658 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2659 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2660 l_sav_rec sav_rec_type;
2661 BEGIN
2662 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2663 G_PKG_NAME,
2664 p_init_msg_list,
2665 l_api_version,
2666 p_api_version,
2667 '_PVT',
2668 x_return_status);
2669 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2670 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2671 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2672 RAISE OKC_API.G_EXCEPTION_ERROR;
2673 END IF;
2674 --------------------------------------
2675 -- Move VIEW record to "Child" records
2676 --------------------------------------
2677 migrate(p_savv_rec, l_sav_rec);
2678 --------------------------------------------
2679 -- Call the LOCK_ROW for each child record
2680 --------------------------------------------
2681 lock_row(
2682 p_init_msg_list,
2683 x_return_status,
2684 x_msg_count,
2685 x_msg_data,
2686 l_sav_rec
2687 );
2688 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2689 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2690 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2691 RAISE OKC_API.G_EXCEPTION_ERROR;
2692 END IF;
2693 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2694 EXCEPTION
2695 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2696 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2697 (
2698 l_api_name,
2699 G_PKG_NAME,
2700 'OKC_API.G_RET_STS_ERROR',
2701 x_msg_count,
2702 x_msg_data,
2703 '_PVT'
2704 );
2705 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2706 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2707 (
2708 l_api_name,
2709 G_PKG_NAME,
2710 'OKC_API.G_RET_STS_UNEXP_ERROR',
2711 x_msg_count,
2712 x_msg_data,
2713 '_PVT'
2714 );
2715 WHEN OTHERS THEN
2716 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2717 (
2718 l_api_name,
2719 G_PKG_NAME,
2720 'OTHERS',
2721 x_msg_count,
2722 x_msg_data,
2723 '_PVT'
2724 );
2725 END lock_row;
2726 --------------------------------------
2727 -- PL/SQL TBL lock_row for:SAVV_TBL --
2728 --------------------------------------
2729 PROCEDURE lock_row(
2730 p_api_version IN NUMBER,
2731 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2732 x_return_status OUT NOCOPY VARCHAR2,
2733 x_msg_count OUT NOCOPY NUMBER,
2734 x_msg_data OUT NOCOPY VARCHAR2,
2735 p_savv_tbl IN savv_tbl_type) IS
2736
2737 l_api_version CONSTANT NUMBER := 1;
2738 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2739 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2740 i NUMBER := 0;
2741 BEGIN
2742 OKC_API.init_msg_list(p_init_msg_list);
2743 -- Make sure PL/SQL table has records in it before passing
2744 IF (p_savv_tbl.COUNT > 0) THEN
2745 i := p_savv_tbl.FIRST;
2746 LOOP
2747 lock_row (
2748 p_api_version => p_api_version,
2749 p_init_msg_list => OKC_API.G_FALSE,
2750 x_return_status => x_return_status,
2751 x_msg_count => x_msg_count,
2752 x_msg_data => x_msg_data,
2753 p_savv_rec => p_savv_tbl(i));
2754 EXIT WHEN (i = p_savv_tbl.LAST);
2755 i := p_savv_tbl.NEXT(i);
2756 END LOOP;
2757 END IF;
2758 EXCEPTION
2759 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2760 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2761 (
2762 l_api_name,
2763 G_PKG_NAME,
2764 'OKC_API.G_RET_STS_ERROR',
2765 x_msg_count,
2766 x_msg_data,
2767 '_PVT'
2768 );
2769 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2770 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2771 (
2772 l_api_name,
2773 G_PKG_NAME,
2774 'OKC_API.G_RET_STS_UNEXP_ERROR',
2775 x_msg_count,
2776 x_msg_data,
2777 '_PVT'
2778 );
2779 WHEN OTHERS THEN
2780 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2781 (
2782 l_api_name,
2783 G_PKG_NAME,
2784 'OTHERS',
2785 x_msg_count,
2786 x_msg_data,
2787 '_PVT'
2788 );
2789 END lock_row;
2790
2791 ---------------------------------------------------------------------------
2792 -- PROCEDURE update_row
2793 ---------------------------------------------------------------------------
2794 ------------------------------------
2795 -- update_row for:OKS_SERV_AVAILS --
2796 ------------------------------------
2797 PROCEDURE update_row(
2798 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2799 x_return_status OUT NOCOPY VARCHAR2,
2800 x_msg_count OUT NOCOPY NUMBER,
2801 x_msg_data OUT NOCOPY VARCHAR2,
2802 p_sav_rec IN sav_rec_type,
2803 x_sav_rec OUT NOCOPY sav_rec_type) IS
2804
2805 l_api_version CONSTANT NUMBER := 1;
2806 l_api_name CONSTANT VARCHAR2(30) := 'AVAILS_update_row';
2807 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2808 l_sav_rec sav_rec_type := p_sav_rec;
2809 l_def_sav_rec sav_rec_type;
2810 l_row_notfound BOOLEAN := TRUE;
2811 ----------------------------------
2812 -- FUNCTION populate_new_record --
2813 ----------------------------------
2814 FUNCTION populate_new_record (
2815 p_sav_rec IN sav_rec_type,
2816 x_sav_rec OUT NOCOPY sav_rec_type
2817 ) RETURN VARCHAR2 IS
2818 l_sav_rec sav_rec_type;
2819 l_row_notfound BOOLEAN := TRUE;
2820 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2821 BEGIN
2822 x_sav_rec := p_sav_rec;
2823 -- Get current database values
2824 l_sav_rec := get_rec(p_sav_rec, l_row_notfound);
2825 IF (l_row_notfound) THEN
2826 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2827 END IF;
2828 IF (x_sav_rec.id = OKC_API.G_MISS_NUM)
2829 THEN
2830 x_sav_rec.id := l_sav_rec.id;
2831 END IF;
2832 IF (x_sav_rec.object1_id1 = OKC_API.G_MISS_CHAR)
2833 THEN
2834 x_sav_rec.object1_id1 := l_sav_rec.object1_id1;
2835 END IF;
2836 IF (x_sav_rec.object1_id2 = OKC_API.G_MISS_CHAR)
2837 THEN
2838 x_sav_rec.object1_id2 := l_sav_rec.object1_id2;
2839 END IF;
2840 IF (x_sav_rec.JTOT_OBJECT1_CODE = OKC_API.G_MISS_CHAR)
2841 THEN
2842 x_sav_rec.JTOT_OBJECT1_CODE := l_sav_rec.JTOT_OBJECT1_CODE;
2843 END IF;
2844 IF (x_sav_rec.object_version_number = OKC_API.G_MISS_NUM)
2845 THEN
2846 x_sav_rec.object_version_number := l_sav_rec.object_version_number;
2847 END IF;
2848 IF (x_sav_rec.created_by = OKC_API.G_MISS_NUM)
2849 THEN
2850 x_sav_rec.created_by := l_sav_rec.created_by;
2851 END IF;
2852 IF (x_sav_rec.creation_date = OKC_API.G_MISS_DATE)
2853 THEN
2854 x_sav_rec.creation_date := l_sav_rec.creation_date;
2855 END IF;
2856 IF (x_sav_rec.last_updated_by = OKC_API.G_MISS_NUM)
2857 THEN
2858 x_sav_rec.last_updated_by := l_sav_rec.last_updated_by;
2859 END IF;
2860 IF (x_sav_rec.last_update_date = OKC_API.G_MISS_DATE)
2861 THEN
2862 x_sav_rec.last_update_date := l_sav_rec.last_update_date;
2863 END IF;
2864 IF (x_sav_rec.general_yn = OKC_API.G_MISS_CHAR)
2865 THEN
2866 x_sav_rec.general_yn := l_sav_rec.general_yn;
2867 END IF;
2868 IF (x_sav_rec.except_object_type = OKC_API.G_MISS_CHAR)
2869 THEN
2870 x_sav_rec.except_object_type := l_sav_rec.except_object_type;
2871 END IF;
2872 IF (x_sav_rec.start_date_active = OKC_API.G_MISS_DATE)
2873 THEN
2874 x_sav_rec.start_date_active := l_sav_rec.start_date_active;
2875 END IF;
2876 IF (x_sav_rec.end_date_active = OKC_API.G_MISS_DATE)
2877 THEN
2878 x_sav_rec.end_date_active := l_sav_rec.end_date_active;
2879 END IF;
2880 IF (x_sav_rec.last_update_login = OKC_API.G_MISS_NUM)
2881 THEN
2882 x_sav_rec.last_update_login := l_sav_rec.last_update_login;
2883 END IF;
2884 IF (x_sav_rec.attribute_category = OKC_API.G_MISS_CHAR)
2885 THEN
2886 x_sav_rec.attribute_category := l_sav_rec.attribute_category;
2887 END IF;
2888 IF (x_sav_rec.attribute1 = OKC_API.G_MISS_CHAR)
2889 THEN
2890 x_sav_rec.attribute1 := l_sav_rec.attribute1;
2891 END IF;
2892 IF (x_sav_rec.attribute2 = OKC_API.G_MISS_CHAR)
2893 THEN
2894 x_sav_rec.attribute2 := l_sav_rec.attribute2;
2895 END IF;
2896 IF (x_sav_rec.attribute3 = OKC_API.G_MISS_CHAR)
2897 THEN
2898 x_sav_rec.attribute3 := l_sav_rec.attribute3;
2899 END IF;
2900 IF (x_sav_rec.attribute4 = OKC_API.G_MISS_CHAR)
2901 THEN
2902 x_sav_rec.attribute4 := l_sav_rec.attribute4;
2903 END IF;
2904 IF (x_sav_rec.attribute5 = OKC_API.G_MISS_CHAR)
2905 THEN
2906 x_sav_rec.attribute5 := l_sav_rec.attribute5;
2907 END IF;
2908 IF (x_sav_rec.attribute6 = OKC_API.G_MISS_CHAR)
2909 THEN
2910 x_sav_rec.attribute6 := l_sav_rec.attribute6;
2911 END IF;
2912 IF (x_sav_rec.attribute7 = OKC_API.G_MISS_CHAR)
2913 THEN
2914 x_sav_rec.attribute7 := l_sav_rec.attribute7;
2915 END IF;
2916 IF (x_sav_rec.attribute8 = OKC_API.G_MISS_CHAR)
2917 THEN
2918 x_sav_rec.attribute8 := l_sav_rec.attribute8;
2919 END IF;
2920 IF (x_sav_rec.attribute9 = OKC_API.G_MISS_CHAR)
2921 THEN
2922 x_sav_rec.attribute9 := l_sav_rec.attribute9;
2923 END IF;
2924 IF (x_sav_rec.attribute10 = OKC_API.G_MISS_CHAR)
2925 THEN
2926 x_sav_rec.attribute10 := l_sav_rec.attribute10;
2927 END IF;
2928 IF (x_sav_rec.attribute11 = OKC_API.G_MISS_CHAR)
2929 THEN
2930 x_sav_rec.attribute11 := l_sav_rec.attribute11;
2931 END IF;
2932 IF (x_sav_rec.attribute12 = OKC_API.G_MISS_CHAR)
2933 THEN
2934 x_sav_rec.attribute12 := l_sav_rec.attribute12;
2935 END IF;
2936 IF (x_sav_rec.attribute13 = OKC_API.G_MISS_CHAR)
2937 THEN
2938 x_sav_rec.attribute13 := l_sav_rec.attribute13;
2939 END IF;
2940 IF (x_sav_rec.attribute14 = OKC_API.G_MISS_CHAR)
2941 THEN
2942 x_sav_rec.attribute14 := l_sav_rec.attribute14;
2943 END IF;
2944 IF (x_sav_rec.attribute15 = OKC_API.G_MISS_CHAR)
2945 THEN
2946 x_sav_rec.attribute15 := l_sav_rec.attribute15;
2947 END IF;
2948 RETURN(l_return_status);
2949 END populate_new_record;
2950 ----------------------------------------
2951 -- Set_Attributes for:OKS_SERV_AVAILS --
2952 ----------------------------------------
2953 FUNCTION Set_Attributes (
2954 p_sav_rec IN sav_rec_type,
2955 x_sav_rec OUT NOCOPY sav_rec_type
2956 ) RETURN VARCHAR2 IS
2957 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2958 BEGIN
2959 x_sav_rec := p_sav_rec;
2960 RETURN(l_return_status);
2961 END Set_Attributes;
2962 BEGIN
2963 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2964 p_init_msg_list,
2965 '_PVT',
2966 x_return_status);
2967 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2968 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2969 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2970 RAISE OKC_API.G_EXCEPTION_ERROR;
2971 END IF;
2972 --- Setting item attributes
2973 l_return_status := Set_Attributes(
2974 p_sav_rec, -- IN
2975 l_sav_rec); -- OUT
2976 --- If any errors happen abort API
2977 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2978 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2979 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2980 RAISE OKC_API.G_EXCEPTION_ERROR;
2981 END IF;
2982 l_return_status := populate_new_record(l_sav_rec, l_def_sav_rec);
2983 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2984 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2985 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2986 RAISE OKC_API.G_EXCEPTION_ERROR;
2987 END IF;
2988 UPDATE OKS_SERV_AVAILS
2989 SET OBJECT1_ID1 = l_def_sav_rec.object1_id1,
2990 OBJECT1_ID2 = l_def_sav_rec.object1_id2,
2991 JTOT_OBJECT1_CODE = l_def_sav_rec.JTOT_OBJECT1_CODE,
2992 OBJECT_VERSION_NUMBER = l_def_sav_rec.object_version_number,
2993 CREATED_BY = l_def_sav_rec.created_by,
2994 CREATION_DATE = l_def_sav_rec.creation_date,
2995 LAST_UPDATED_BY = l_def_sav_rec.last_updated_by,
2996 LAST_UPDATE_DATE = l_def_sav_rec.last_update_date,
2997 GENERAL_YN = l_def_sav_rec.general_yn,
2998 EXCEPT_OBJECT_TYPE = l_def_sav_rec.except_object_type,
2999 START_DATE_ACTIVE = l_def_sav_rec.start_date_active,
3000 END_DATE_ACTIVE = l_def_sav_rec.end_date_active,
3001 LAST_UPDATE_LOGIN = l_def_sav_rec.last_update_login,
3002 ATTRIBUTE_CATEGORY = l_def_sav_rec.attribute_category,
3003 ATTRIBUTE1 = l_def_sav_rec.attribute1,
3004 ATTRIBUTE2 = l_def_sav_rec.attribute2,
3005 ATTRIBUTE3 = l_def_sav_rec.attribute3,
3006 ATTRIBUTE4 = l_def_sav_rec.attribute4,
3007 ATTRIBUTE5 = l_def_sav_rec.attribute5,
3008 ATTRIBUTE6 = l_def_sav_rec.attribute6,
3009 ATTRIBUTE7 = l_def_sav_rec.attribute7,
3010 ATTRIBUTE8 = l_def_sav_rec.attribute8,
3011 ATTRIBUTE9 = l_def_sav_rec.attribute9,
3012 ATTRIBUTE10 = l_def_sav_rec.attribute10,
3013 ATTRIBUTE11 = l_def_sav_rec.attribute11,
3014 ATTRIBUTE12 = l_def_sav_rec.attribute12,
3015 ATTRIBUTE13 = l_def_sav_rec.attribute13,
3016 ATTRIBUTE14 = l_def_sav_rec.attribute14,
3017
3018 ATTRIBUTE15 = l_def_sav_rec.attribute15
3019 WHERE ID = l_def_sav_rec.id;
3020
3021 x_sav_rec := l_def_sav_rec;
3022 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3023 EXCEPTION
3024 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3025 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3026 (
3027 l_api_name,
3028 G_PKG_NAME,
3029 'OKC_API.G_RET_STS_ERROR',
3030 x_msg_count,
3031 x_msg_data,
3032 '_PVT'
3033 );
3034 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3035 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3036 (
3037 l_api_name,
3038 G_PKG_NAME,
3039 'OKC_API.G_RET_STS_UNEXP_ERROR',
3040 x_msg_count,
3041 x_msg_data,
3042 '_PVT'
3043 );
3044 WHEN OTHERS THEN
3045 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3046 (
3047 l_api_name,
3048 G_PKG_NAME,
3049 'OTHERS',
3050 x_msg_count,
3051 x_msg_data,
3052 '_PVT'
3053 );
3054 END update_row;
3055 --------------------------------------
3056 -- update_row for:OKS_SERV_AVAILS_V --
3057 --------------------------------------
3058 PROCEDURE update_row(
3059 p_api_version IN NUMBER,
3060 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3061 x_return_status OUT NOCOPY VARCHAR2,
3062 x_msg_count OUT NOCOPY NUMBER,
3063 x_msg_data OUT NOCOPY VARCHAR2,
3064 p_savv_rec IN savv_rec_type,
3065 x_savv_rec OUT NOCOPY savv_rec_type) IS
3066
3067 l_api_version CONSTANT NUMBER := 1;
3068 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3069 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3070 l_savv_rec savv_rec_type := p_savv_rec;
3071 l_def_savv_rec savv_rec_type;
3072 l_sav_rec sav_rec_type;
3073 lx_sav_rec sav_rec_type;
3074 -------------------------------
3075 -- FUNCTION fill_who_columns --
3076 -------------------------------
3077 FUNCTION fill_who_columns (
3078 p_savv_rec IN savv_rec_type
3079 ) RETURN savv_rec_type IS
3080 l_savv_rec savv_rec_type := p_savv_rec;
3081 BEGIN
3082 l_savv_rec.LAST_UPDATE_DATE := SYSDATE;
3083 l_savv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3084 l_savv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3085 RETURN(l_savv_rec);
3086 END fill_who_columns;
3087 ----------------------------------
3088 -- FUNCTION populate_new_record --
3089 ----------------------------------
3090 FUNCTION populate_new_record (
3091 p_savv_rec IN savv_rec_type,
3092 x_savv_rec OUT NOCOPY savv_rec_type
3093 ) RETURN VARCHAR2 IS
3094 l_savv_rec savv_rec_type;
3095 l_row_notfound BOOLEAN := TRUE;
3096 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3097 BEGIN
3098 x_savv_rec := p_savv_rec;
3099 -- Get current database values
3100 l_savv_rec := get_rec(p_savv_rec, l_row_notfound);
3101 IF (l_row_notfound) THEN
3102 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3103 END IF;
3104 IF (x_savv_rec.id = OKC_API.G_MISS_NUM)
3105 THEN
3106 x_savv_rec.id := l_savv_rec.id;
3107 END IF;
3108 IF (x_savv_rec.object1_id1 = OKC_API.G_MISS_CHAR)
3109 THEN
3110 x_savv_rec.object1_id1 := l_savv_rec.object1_id1;
3111 END IF;
3112 IF (x_savv_rec.object1_id2 = OKC_API.G_MISS_CHAR)
3113 THEN
3114 x_savv_rec.object1_id2 := l_savv_rec.object1_id2;
3115 END IF;
3116 IF (x_savv_rec.JTOT_OBJECT1_CODE = OKC_API.G_MISS_CHAR)
3117 THEN
3118 x_savv_rec.JTOT_OBJECT1_CODE := l_savv_rec.JTOT_OBJECT1_CODE;
3119 END IF;
3120 IF (x_savv_rec.object_version_number = OKC_API.G_MISS_NUM)
3121 THEN
3122 x_savv_rec.object_version_number := l_savv_rec.object_version_number;
3123 END IF;
3124 IF (x_savv_rec.created_by = OKC_API.G_MISS_NUM)
3125 THEN
3126 x_savv_rec.created_by := l_savv_rec.created_by;
3127 END IF;
3128 IF (x_savv_rec.creation_date = OKC_API.G_MISS_DATE)
3129 THEN
3130 x_savv_rec.creation_date := l_savv_rec.creation_date;
3131 END IF;
3132 IF (x_savv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3133 THEN
3134 x_savv_rec.last_updated_by := l_savv_rec.last_updated_by;
3135 END IF;
3136 IF (x_savv_rec.last_update_date = OKC_API.G_MISS_DATE)
3137 THEN
3138 x_savv_rec.last_update_date := l_savv_rec.last_update_date;
3139 END IF;
3140 IF (x_savv_rec.general_yn = OKC_API.G_MISS_CHAR)
3141 THEN
3142 x_savv_rec.general_yn := l_savv_rec.general_yn;
3143 END IF;
3144 IF (x_savv_rec.except_object_type = OKC_API.G_MISS_CHAR)
3145 THEN
3146 x_savv_rec.except_object_type := l_savv_rec.except_object_type;
3147 END IF;
3148 IF (x_savv_rec.start_date_active = OKC_API.G_MISS_DATE)
3149 THEN
3150 x_savv_rec.start_date_active := l_savv_rec.start_date_active;
3151 END IF;
3152 IF (x_savv_rec.end_date_active = OKC_API.G_MISS_DATE)
3153 THEN
3154 x_savv_rec.end_date_active := l_savv_rec.end_date_active;
3155 END IF;
3156 IF (x_savv_rec.last_update_login = OKC_API.G_MISS_NUM)
3157 THEN
3158 x_savv_rec.last_update_login := l_savv_rec.last_update_login;
3159 END IF;
3160 IF (x_savv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3161 THEN
3162 x_savv_rec.attribute_category := l_savv_rec.attribute_category;
3163 END IF;
3164 IF (x_savv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3165 THEN
3166 x_savv_rec.attribute1 := l_savv_rec.attribute1;
3167 END IF;
3168 IF (x_savv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3169 THEN
3170 x_savv_rec.attribute2 := l_savv_rec.attribute2;
3171 END IF;
3172 IF (x_savv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3173 THEN
3174 x_savv_rec.attribute3 := l_savv_rec.attribute3;
3175 END IF;
3176 IF (x_savv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3177 THEN
3178 x_savv_rec.attribute4 := l_savv_rec.attribute4;
3179 END IF;
3180 IF (x_savv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3181 THEN
3182 x_savv_rec.attribute5 := l_savv_rec.attribute5;
3183 END IF;
3184 IF (x_savv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3185 THEN
3186 x_savv_rec.attribute6 := l_savv_rec.attribute6;
3187 END IF;
3188 IF (x_savv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3189 THEN
3190 x_savv_rec.attribute7 := l_savv_rec.attribute7;
3191 END IF;
3192 IF (x_savv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3193 THEN
3194 x_savv_rec.attribute8 := l_savv_rec.attribute8;
3195 END IF;
3196 IF (x_savv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3197 THEN
3198 x_savv_rec.attribute9 := l_savv_rec.attribute9;
3199 END IF;
3200 IF (x_savv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3201 THEN
3202 x_savv_rec.attribute10 := l_savv_rec.attribute10;
3203 END IF;
3204 IF (x_savv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3205 THEN
3206 x_savv_rec.attribute11 := l_savv_rec.attribute11;
3207 END IF;
3208 IF (x_savv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3209 THEN
3210 x_savv_rec.attribute12 := l_savv_rec.attribute12;
3211 END IF;
3212 IF (x_savv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3213 THEN
3214 x_savv_rec.attribute13 := l_savv_rec.attribute13;
3215 END IF;
3216 IF (x_savv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3217 THEN
3218 x_savv_rec.attribute14 := l_savv_rec.attribute14;
3219 END IF;
3220 IF (x_savv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3221 THEN
3222 x_savv_rec.attribute15 := l_savv_rec.attribute15;
3223 END IF;
3224 RETURN(l_return_status);
3225 END populate_new_record;
3226 ------------------------------------------
3227 -- Set_Attributes for:OKS_SERV_AVAILS_V --
3228 ------------------------------------------
3229
3230 FUNCTION Set_Attributes (
3231 p_savv_rec IN savv_rec_type,
3232 x_savv_rec OUT NOCOPY savv_rec_type
3233 ) RETURN VARCHAR2 IS
3234 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3235 BEGIN
3236 x_savv_rec := p_savv_rec;
3237 x_savv_rec.OBJECT_VERSION_NUMBER := NVL(x_savv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3238 RETURN(l_return_status);
3239 END Set_Attributes;
3240 BEGIN
3241 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3242 G_PKG_NAME,
3243 p_init_msg_list,
3244 l_api_version,
3245 p_api_version,
3246 '_PVT',
3247 x_return_status);
3248 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3249 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3250 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3251 RAISE OKC_API.G_EXCEPTION_ERROR;
3252 END IF;
3253 --- Setting item attributes
3254 l_return_status := Set_Attributes(
3255 p_savv_rec, -- IN
3256 l_savv_rec); -- OUT
3257 --- If any errors happen abort API
3258 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3259 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3260 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3261 RAISE OKC_API.G_EXCEPTION_ERROR;
3262 END IF;
3263 l_return_status := populate_new_record(l_savv_rec, l_def_savv_rec);
3264 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3265 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3266 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3267 RAISE OKC_API.G_EXCEPTION_ERROR;
3268 END IF;
3269 l_def_savv_rec := fill_who_columns(l_def_savv_rec);
3270 --- Validate all non-missing attributes (Item Level Validation)
3271 l_return_status := Validate_Attributes(l_def_savv_rec);
3272 --- If any errors happen abort API
3273 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3274 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3275 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3276 RAISE OKC_API.G_EXCEPTION_ERROR;
3277 END IF;
3278 l_return_status := Validate_Record(l_def_savv_rec);
3279 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3280 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3281 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3282 RAISE OKC_API.G_EXCEPTION_ERROR;
3283 END IF;
3284
3285 --------------------------------------
3286 -- Move VIEW record to "Child" records
3287 --------------------------------------
3288 migrate(l_def_savv_rec, l_sav_rec);
3289 --------------------------------------------
3290 -- Call the UPDATE_ROW for each child record
3291 --------------------------------------------
3292 update_row(
3293 p_init_msg_list,
3294 x_return_status,
3295 x_msg_count,
3296 x_msg_data,
3297 l_sav_rec,
3298 lx_sav_rec
3299 );
3300 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3301 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3302 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3303 RAISE OKC_API.G_EXCEPTION_ERROR;
3304 END IF;
3305 migrate(lx_sav_rec, l_def_savv_rec);
3306 x_savv_rec := l_def_savv_rec;
3307 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3308 EXCEPTION
3309 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3310 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3311 (
3312 l_api_name,
3313 G_PKG_NAME,
3314 'OKC_API.G_RET_STS_ERROR',
3315 x_msg_count,
3316 x_msg_data,
3317 '_PVT'
3318 );
3319 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3320 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3321 (
3322 l_api_name,
3323 G_PKG_NAME,
3324 'OKC_API.G_RET_STS_UNEXP_ERROR',
3325 x_msg_count,
3326 x_msg_data,
3327 '_PVT'
3328 );
3329 WHEN OTHERS THEN
3330 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3331 (
3332 l_api_name,
3333 G_PKG_NAME,
3334 'OTHERS',
3335 x_msg_count,
3336 x_msg_data,
3337 '_PVT'
3338 );
3339 END update_row;
3340 ----------------------------------------
3341 -- PL/SQL TBL update_row for:SAVV_TBL --
3342 ----------------------------------------
3343 PROCEDURE update_row(
3344 p_api_version IN NUMBER,
3345 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3346 x_return_status OUT NOCOPY VARCHAR2,
3347 x_msg_count OUT NOCOPY NUMBER,
3348 x_msg_data OUT NOCOPY VARCHAR2,
3349 p_savv_tbl IN savv_tbl_type,
3350 x_savv_tbl OUT NOCOPY savv_tbl_type) IS
3351
3352 l_api_version CONSTANT NUMBER := 1;
3353 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3354 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3355 i NUMBER := 0;
3356 BEGIN
3357 OKC_API.init_msg_list(p_init_msg_list);
3358 -- Make sure PL/SQL table has records in it before passing
3359 IF (p_savv_tbl.COUNT > 0) THEN
3360 i := p_savv_tbl.FIRST;
3361 LOOP
3362 update_row (
3363 p_api_version => p_api_version,
3364 p_init_msg_list => OKC_API.G_FALSE,
3365 x_return_status => x_return_status,
3366 x_msg_count => x_msg_count,
3367 x_msg_data => x_msg_data,
3368 p_savv_rec => p_savv_tbl(i),
3369 x_savv_rec => x_savv_tbl(i));
3370 EXIT WHEN (i = p_savv_tbl.LAST);
3371 i := p_savv_tbl.NEXT(i);
3372 END LOOP;
3373 END IF;
3374 EXCEPTION
3375 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3376 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3377 (
3378 l_api_name,
3379 G_PKG_NAME,
3380 'OKC_API.G_RET_STS_ERROR',
3381 x_msg_count,
3382 x_msg_data,
3383 '_PVT'
3384 );
3385 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3386 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3387 (
3388 l_api_name,
3389 G_PKG_NAME,
3390 'OKC_API.G_RET_STS_UNEXP_ERROR',
3391 x_msg_count,
3392 x_msg_data,
3393 '_PVT'
3394 );
3395 WHEN OTHERS THEN
3396 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3397 (
3398 l_api_name,
3399 G_PKG_NAME,
3400 'OTHERS',
3401 x_msg_count,
3402 x_msg_data,
3403 '_PVT'
3404 );
3405 END update_row;
3406
3407 ---------------------------------------------------------------------------
3408 -- PROCEDURE delete_row
3409 ---------------------------------------------------------------------------
3410 ------------------------------------
3411 -- delete_row for:OKS_SERV_AVAILS --
3412 ------------------------------------
3413 PROCEDURE delete_row(
3414 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3415 x_return_status OUT NOCOPY VARCHAR2,
3416 x_msg_count OUT NOCOPY NUMBER,
3417 x_msg_data OUT NOCOPY VARCHAR2,
3418 p_sav_rec IN sav_rec_type) IS
3419
3420 l_api_version CONSTANT NUMBER := 1;
3421 l_api_name CONSTANT VARCHAR2(30) := 'AVAILS_delete_row';
3422 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3423 l_sav_rec sav_rec_type:= p_sav_rec;
3424 l_row_notfound BOOLEAN := TRUE;
3425 BEGIN
3426 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3427 p_init_msg_list,
3428 '_PVT',
3429 x_return_status);
3430 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3431 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3432 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3433 RAISE OKC_API.G_EXCEPTION_ERROR;
3434 END IF;
3435 DELETE FROM OKS_SERV_AVAILS
3436 WHERE ID = l_sav_rec.id;
3437
3438 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3439 EXCEPTION
3440 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3441 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3442 (
3443 l_api_name,
3444 G_PKG_NAME,
3445 'OKC_API.G_RET_STS_ERROR',
3446 x_msg_count,
3447 x_msg_data,
3448 '_PVT'
3449 );
3450 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3451 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3452 (
3453 l_api_name,
3454 G_PKG_NAME,
3455 'OKC_API.G_RET_STS_UNEXP_ERROR',
3456 x_msg_count,
3457 x_msg_data,
3458 '_PVT'
3459 );
3460 WHEN OTHERS THEN
3461 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3462 (
3463 l_api_name,
3464 G_PKG_NAME,
3465 'OTHERS',
3466 x_msg_count,
3467 x_msg_data,
3468 '_PVT'
3469 );
3470 END delete_row;
3471 --------------------------------------
3472 -- delete_row for:OKS_SERV_AVAILS_V --
3473 --------------------------------------
3474 PROCEDURE delete_row(
3475 p_api_version IN NUMBER,
3476 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3477 x_return_status OUT NOCOPY VARCHAR2,
3478 x_msg_count OUT NOCOPY NUMBER,
3479 x_msg_data OUT NOCOPY VARCHAR2,
3480 p_savv_rec IN savv_rec_type) IS
3481
3482 l_api_version CONSTANT NUMBER := 1;
3483 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3484 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3485 l_savv_rec savv_rec_type := p_savv_rec;
3486 l_sav_rec sav_rec_type;
3487 BEGIN
3488 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3489 G_PKG_NAME,
3490 p_init_msg_list,
3491 l_api_version,
3492 p_api_version,
3493 '_PVT',
3494 x_return_status);
3495 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3496 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3497 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3498 RAISE OKC_API.G_EXCEPTION_ERROR;
3499 END IF;
3500 --------------------------------------
3501 -- Move VIEW record to "Child" records
3502 --------------------------------------
3503 migrate(l_savv_rec, l_sav_rec);
3504 --------------------------------------------
3505 -- Call the DELETE_ROW for each child record
3506 --------------------------------------------
3507 delete_row(
3508 p_init_msg_list,
3509 x_return_status,
3510 x_msg_count,
3511 x_msg_data,
3512 l_sav_rec
3513 );
3514 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3515 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3516 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3517 RAISE OKC_API.G_EXCEPTION_ERROR;
3518 END IF;
3519 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3520 EXCEPTION
3521 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3522 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3523 (
3524 l_api_name,
3525 G_PKG_NAME,
3526 'OKC_API.G_RET_STS_ERROR',
3527 x_msg_count,
3528 x_msg_data,
3529 '_PVT'
3530 );
3531 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3532 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3533 (
3534 l_api_name,
3535 G_PKG_NAME,
3536 'OKC_API.G_RET_STS_UNEXP_ERROR',
3537 x_msg_count,
3538 x_msg_data,
3539 '_PVT'
3540 );
3541 WHEN OTHERS THEN
3542 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3543 (
3544 l_api_name,
3545 G_PKG_NAME,
3546 'OTHERS',
3547 x_msg_count,
3548 x_msg_data,
3549 '_PVT'
3550 );
3551 END delete_row;
3552 ----------------------------------------
3553 -- PL/SQL TBL delete_row for:SAVV_TBL --
3554 ----------------------------------------
3555 PROCEDURE delete_row(
3556 p_api_version IN NUMBER,
3557 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3558 x_return_status OUT NOCOPY VARCHAR2,
3559 x_msg_count OUT NOCOPY NUMBER,
3560 x_msg_data OUT NOCOPY VARCHAR2,
3561 p_savv_tbl IN savv_tbl_type) IS
3562
3563 l_api_version CONSTANT NUMBER := 1;
3564 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3565 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3566 i NUMBER := 0;
3567 BEGIN
3568 OKC_API.init_msg_list(p_init_msg_list);
3569 -- Make sure PL/SQL table has records in it before passing
3570 IF (p_savv_tbl.COUNT > 0) THEN
3571 i := p_savv_tbl.FIRST;
3572 LOOP
3573 delete_row (
3574 p_api_version => p_api_version,
3575 p_init_msg_list => OKC_API.G_FALSE,
3576 x_return_status => x_return_status,
3577 x_msg_count => x_msg_count,
3578 x_msg_data => x_msg_data,
3579 p_savv_rec => p_savv_tbl(i));
3580 EXIT WHEN (i = p_savv_tbl.LAST);
3581 i := p_savv_tbl.NEXT(i);
3582 END LOOP;
3583 END IF;
3584 EXCEPTION
3585 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3586 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3587 (
3588 l_api_name,
3589 G_PKG_NAME,
3590 'OKC_API.G_RET_STS_ERROR',
3591 x_msg_count,
3592 x_msg_data,
3593 '_PVT'
3594 );
3595 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3596 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3597 (
3598 l_api_name,
3599 G_PKG_NAME,
3600 'OKC_API.G_RET_STS_UNEXP_ERROR',
3601 x_msg_count,
3602 x_msg_data,
3603 '_PVT'
3604 );
3605 WHEN OTHERS THEN
3606 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3607 (
3608 l_api_name,
3609 G_PKG_NAME,
3610 'OTHERS',
3611 x_msg_count,
3612 x_msg_data,
3613 '_PVT'
3614 );
3615 END delete_row;
3616
3617 PROCEDURE INSERT_ROW_UPG(p_savv_tbl savv_tbl_type ) IS
3618 l_tabsize NUMBER := p_savv_tbl.COUNT;
3619 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
3620 TYPE Var720TabTyp IS TABLE OF Varchar2(720)
3621 INDEX BY BINARY_INTEGER;
3622
3623 in_id OKC_DATATYPES.NumberTabTyp;
3624 in_object1_id1 OKC_DATATYPES.Var40TabTyp;
3625 in_object1_id2 OKC_DATATYPES.Var200TabTyp;
3626 in_jtot_object1_code OKC_DATATYPES.Var30TabTyp;
3627 in_general_yn OKC_DATATYPES.Var3TabTyp;
3628 in_except_object_type OKC_DATATYPES.Var30TabTyp;
3629 in_start_date_active OKC_DATATYPES.DateTabTyp;
3630 in_end_date_active OKC_DATATYPES.DateTabTyp;
3631 in_object_version_number OKC_DATATYPES.NumberTabTyp;
3632 in_attribute_category OKC_DATATYPES.Var90TabTyp;
3633 in_attribute1 OKC_DATATYPES.Var450TabTyp;
3634 in_attribute2 OKC_DATATYPES.Var450TabTyp;
3635 in_attribute3 OKC_DATATYPES.Var450TabTyp;
3636 in_attribute4 OKC_DATATYPES.Var450TabTyp;
3637 in_attribute5 OKC_DATATYPES.Var450TabTyp;
3638 in_attribute6 OKC_DATATYPES.Var450TabTyp;
3639 in_attribute7 OKC_DATATYPES.Var450TabTyp;
3640 in_attribute8 OKC_DATATYPES.Var450TabTyp;
3641 in_attribute9 OKC_DATATYPES.Var450TabTyp;
3642 in_attribute10 OKC_DATATYPES.Var450TabTyp;
3643 in_attribute11 OKC_DATATYPES.Var450TabTyp;
3644 in_attribute12 OKC_DATATYPES.Var450TabTyp;
3645 in_attribute13 OKC_DATATYPES.Var450TabTyp;
3646 in_attribute14 OKC_DATATYPES.Var450TabTyp;
3647 in_attribute15 OKC_DATATYPES.Var450TabTyp;
3648 in_created_by OKC_DATATYPES.NumberTabTyp;
3649 in_creation_date OKC_DATATYPES.DateTabTyp;
3650 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
3651 in_last_update_date OKC_DATATYPES.DateTabTyp;
3652 in_last_update_login OKC_DATATYPES.NumberTabTyp;
3653
3654
3655 i number;
3656 j number;
3657 BEGIN
3658 i := p_savv_tbl.FIRST; j:=0;
3659 while i is not null
3660 LOOP
3661 j:=j+1;
3662 in_id (j) := p_savv_tbl(i).id;
3663 in_object1_id1 (j) := p_savv_tbl(i).object1_id1;
3664 in_object1_id2 (j) := p_savv_tbl(i).object1_id2;
3665 in_jtot_object1_code (j) := p_savv_tbl(i).jtot_object1_code;
3666 in_general_yn (j) := p_savv_tbl(i).general_yn;
3667 in_except_object_type (j) := p_savv_tbl(i).except_object_type;
3668 in_start_date_active (j) := p_savv_tbl(i).start_date_active;
3669 in_end_date_active (j) := p_savv_tbl(i).end_date_active;
3670 in_object_version_number (j) := p_savv_tbl(i).object_version_number;
3671 in_attribute_category (j) := p_savv_tbl(i).attribute_category;
3672 in_attribute1 (j) := p_savv_tbl(i).attribute1;
3673 in_attribute2 (j) := p_savv_tbl(i).attribute2;
3674 in_attribute3 (j) := p_savv_tbl(i).attribute3;
3675 in_attribute4 (j) := p_savv_tbl(i).attribute4;
3676 in_attribute5 (j) := p_savv_tbl(i).attribute5;
3677 in_attribute6 (j) := p_savv_tbl(i).attribute6;
3678 in_attribute7 (j) := p_savv_tbl(i).attribute7;
3679 in_attribute8 (j) := p_savv_tbl(i).attribute8;
3680 in_attribute9 (j) := p_savv_tbl(i).attribute9;
3681 in_attribute10 (j) := p_savv_tbl(i).attribute10;
3682 in_attribute11 (j) := p_savv_tbl(i).attribute11;
3683 in_attribute12 (j) := p_savv_tbl(i).attribute12;
3684 in_attribute13 (j) := p_savv_tbl(i).attribute13;
3685 in_attribute14 (j) := p_savv_tbl(i).attribute14;
3686 in_attribute15 (j) := p_savv_tbl(i).attribute15;
3687 in_created_by (j) := p_savv_tbl(i).created_by;
3688 in_creation_date (j) := p_savv_tbl(i).creation_date;
3689 in_last_updated_by (j) := p_savv_tbl(i).last_updated_by;
3690 in_last_update_date (j) := p_savv_tbl(i).last_update_date;
3691 in_last_update_login (j) := p_savv_tbl(i).last_update_login;
3692
3693
3694
3695 i:=p_savv_tbl.next(i);
3696 END LOOP;
3697 FORALL i in 1..l_tabsize
3698 Insert into
3699 OKS_SERV_AVAILS
3700 (
3701 id
3702 , object1_id1
3703 , object1_id2
3704 , jtot_object1_code
3705 , general_yn
3706 , except_object_type
3707 , start_date_active
3708 , end_date_active
3709 , object_version_number
3710 , attribute_category
3711 , attribute1
3712 , attribute2
3713 , attribute3
3714 , attribute4
3715 , attribute5
3716 , attribute6
3717 , attribute7
3718 , attribute8
3719 , attribute9
3720 , attribute10
3721 , attribute11
3722 , attribute12
3723 , attribute13
3724 , attribute14
3725 , attribute15
3726 , created_by
3727 , creation_date
3728 , last_updated_by
3729 , last_update_date
3730 , last_update_login
3731 )
3732 Values
3733 (
3734 in_id(i),
3735 in_object1_id1(i),
3736 in_object1_id2(i),
3737 in_jtot_object1_code(i),
3738 in_general_yn(i),
3739 in_except_object_type(i),
3740 in_start_date_active(i),
3741 in_end_date_active(i),
3742 in_object_version_number(i),
3743 in_attribute_category(i),
3744 in_attribute1(i),
3745 in_attribute2(i),
3746 in_attribute3(i),
3747 in_attribute4(i),
3748 in_attribute5(i),
3749 in_attribute6(i),
3750 in_attribute7(i),
3751 in_attribute8(i),
3752 in_attribute9(i),
3753 in_attribute10(i),
3754 in_attribute11(i),
3755 in_attribute12(i),
3756 in_attribute13(i),
3757 in_attribute14(i),
3758 in_attribute15(i),
3759 in_created_by(i),
3760 in_creation_date(i),
3761 in_last_updated_by(i),
3762 in_last_update_date(i),
3763 in_last_update_login(i)
3764 );
3765 EXCEPTION
3766 WHEN OTHERS THEN
3767 RAISE;
3768 END INSERT_ROW_UPG;
3769 END OKS_AVL_PVT;