[Home] [Help]
PACKAGE BODY: APPS.OKC_CAC_PVT
Source
1 PACKAGE BODY OKC_CAC_PVT AS
2 /* $Header: OKCSCACB.pls 120.0 2005/05/25 23:07:06 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /************************ HAND-CODED *********************************/
7 FUNCTION Validate_Attributes ( p_cacv_rec IN cacv_rec_type)
8 RETURN VARCHAR2;
9 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13 G_VIEW CONSTANT VARCHAR2(200) := 'OKC_K_ACCESSES_V';
14 G_EXCEPTION_HALT_VALIDATION exception;
15 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
16
17 -- Start of comments
18 --
19 -- Procedure Name : validate_group_id
20 -- Description :
21 -- Business Rules :
22 -- Parameters :
23 -- Version : 1.0
24 -- End of comments
25 PROCEDURE validate_group_id(x_return_status OUT NOCOPY VARCHAR2,
26 p_cacv_rec IN cacv_rec_type) is
27 l_dummy_var VARCHAR2(1) := '?';
28 Cursor l_jtfv_csr Is
29 select 'x'
30 from JTF_RS_GROUPS_B
31 where GROUP_ID = p_cacv_rec.group_id;
32 Begin
33
34 IF (l_debug = 'Y') THEN
35 okc_debug.Set_Indentation('OKC_CAC_PVT');
36 okc_debug.log('100: Entered validate_group_id', 2);
37 END IF;
38
39 -- initialize return status
40 x_return_status := OKC_API.G_RET_STS_SUCCESS;
41
42 -- Check JTF_RS_GROUPS_B for valid group id
43 If (p_cacv_rec.group_id <> OKC_API.G_MISS_NUM and
44 p_cacv_rec.group_id IS NOT NULL)
45 Then
46 -- enforce foreign key
47 Open l_jtfv_csr;
48 Fetch l_jtfv_csr Into l_dummy_var;
49 Close l_jtfv_csr;
50
51 -- if l_dummy_var still set to default, data was not found
52 If (l_dummy_var = '?') Then
53 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
54 p_msg_name => g_no_parent_record,
55 p_token1 => g_col_name_token,
56 p_token1_value => 'Group Id',
57 p_token2 => g_child_table_token,
58 p_token2_value => G_VIEW,
59 p_token3 => g_parent_table_token,
60 p_token3_value => 'JTF_RS_GROUPS_B');
61
62 -- notify caller of an error
63 x_return_status := OKC_API.G_RET_STS_ERROR;
64 End If;
65 End If;
66
67 IF (l_debug = 'Y') THEN
68 okc_debug.log('200: Leaving validate_group_id', 2);
69 okc_debug.Reset_Indentation;
70 END IF;
71
72 exception
73 when OTHERS then
74
75 IF (l_debug = 'Y') THEN
76 okc_debug.log('300: Leaving validate_group_id:OTHERS Exception', 2);
77 okc_debug.Reset_Indentation;
78 END IF;
79
80 -- store SQL error message on message stack
81 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
82 p_msg_name => g_unexpected_error,
83 p_token1 => g_sqlcode_token,
84 p_token1_value => sqlcode,
85 p_token2 => g_sqlerrm_token,
86 p_token2_value => sqlerrm);
87 -- set error flag as UNEXPETED error
88 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
89
90 End validate_group_id;
91
92 -- Start of comments
93 --
94 -- Procedure Name : validate_chr_id
95 -- Description :
96 -- Business Rules :
97 -- Parameters :
98 -- Version : 1.0
99 -- End of comments
100 PROCEDURE validate_chr_id(x_return_status OUT NOCOPY VARCHAR2,
101 p_cacv_rec IN cacv_rec_type) is
102
103 l_dummy_var VARCHAR2(1) := '?';
104 Cursor l_chrv_csr Is
105 select 'x'
106 from OKC_K_HEADERS_B
107 where ID = p_cacv_rec.chr_id;
108 Begin
109
110 IF (l_debug = 'Y') THEN
111 okc_debug.Set_Indentation('OKC_CAC_PVT');
112 okc_debug.log('400: Entered validate_chr_id', 2);
113 END IF;
114
115 -- initialize return status
116 x_return_status := OKC_API.G_RET_STS_SUCCESS;
117
118 -- check that data exists
119 If (p_cacv_rec.chr_id = OKC_API.G_MISS_NUM or
120 p_cacv_rec.chr_id IS NULL)
121 Then
122 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
123 p_msg_name => g_required_value,
124 p_token1 => g_col_name_token,
125 p_token1_value => 'chr_id');
126 -- set error flag
127 x_return_status := OKC_API.G_RET_STS_ERROR;
128
129 -- halt validation
130 raise G_EXCEPTION_HALT_VALIDATION;
131 End If;
132
133 -- enforce foreign key
134 Open l_chrv_csr;
135 Fetch l_chrv_csr Into l_dummy_var;
136 Close l_chrv_csr;
137
138 -- if l_dummy_var still set to default, data was not found
139 If (l_dummy_var = '?') Then
140 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
141 p_msg_name => g_no_parent_record,
142 p_token1 => g_col_name_token,
143 p_token1_value => 'chr_id',
144 p_token2 => g_child_table_token,
145 p_token2_value => G_VIEW,
146 p_token3 => g_parent_table_token,
147 p_token3_value => 'OKC_K_HEADERS_V');
148
149 -- notify caller of an error
150 x_return_status := OKC_API.G_RET_STS_ERROR;
151 End If;
152
153 IF (l_debug = 'Y') THEN
154 okc_debug.log('500: Leaving validate_chr_id', 2);
155 okc_debug.Reset_Indentation;
156 END IF;
157
158 exception
159 when G_EXCEPTION_HALT_VALIDATION then
160
161 IF (l_debug = 'Y') THEN
162 okc_debug.log('600: Exiting validate_chr_id:G_EXCEPTION_HALT_VALIDATION Exception', 2);
163 okc_debug.Reset_Indentation;
164 END IF;
165
166 -- no processing necessary; validation can continue with next column
167 null;
168
169 when OTHERS then
170
171 IF (l_debug = 'Y') THEN
172 okc_debug.log('700: Exiting validate_chr_id:OTHERS Exception', 2);
173 okc_debug.Reset_Indentation;
174 END IF;
175
176 -- store SQL error message on message stack
177 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
178 p_msg_name => g_unexpected_error,
179 p_token1 => g_sqlcode_token,
180 p_token1_value => sqlcode,
181 p_token2 => g_sqlerrm_token,
182 p_token2_value => sqlerrm);
183 -- set error flag as UNEXPETED error
184 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
185
186 -- verify that cursor was closed
187 if l_chrv_csr%ISOPEN then
188 close l_chrv_csr;
189 end if;
190
191 End validate_chr_id;
192
193 PROCEDURE validate_resource_id(x_return_status OUT NOCOPY VARCHAR2,
194 p_cacv_rec IN cacv_rec_type) is
195
196 l_dummy_var VARCHAR2(1) := '?';
197 Cursor l_jtfv_csr Is
198 select 'x'
199 from JTF_RS_RESOURCE_EXTNS
200 where RESOURCE_ID = p_cacv_rec.resource_id;
201 Begin
202
203 IF (l_debug = 'Y') THEN
204 okc_debug.Set_Indentation('OKC_CAC_PVT');
205 okc_debug.log('800: Entered validate_resource_id', 2);
206 END IF;
207
208 -- initialize return status
209 x_return_status := OKC_API.G_RET_STS_SUCCESS;
210
211 -- verify foreign key, if data exists
212 If (p_cacv_rec.resource_id <> OKC_API.G_MISS_NUM and
213 p_cacv_rec.resource_id IS NOT NULL)
214 Then
215 Open l_jtfv_csr;
216 Fetch l_jtfv_csr Into l_dummy_var;
217 Close l_jtfv_csr;
218 -- if l_dummy_var still set to default, data was not found
219 If (l_dummy_var = '?') Then
220 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
221 p_msg_name => g_no_parent_record,
222 p_token1 => g_col_name_token,
223 p_token1_value => 'Resource Id',
224 p_token2 => g_child_table_token,
225 p_token2_value => G_VIEW,
226 p_token3 => g_parent_table_token,
227 p_token3_value => 'JTF_RS_RESOURCE_EXTNS');
228 -- set error flag
229 x_return_status := OKC_API.G_RET_STS_ERROR;
230 End If;
231 End If;
232
233 IF (l_debug = 'Y') THEN
234 okc_debug.log('850: Leaving validate_resource_id', 2);
235 okc_debug.Reset_Indentation;
236 END IF;
237
238 exception
239 when OTHERS then
240
241 IF (l_debug = 'Y') THEN
242 okc_debug.log('900: Exiting validate_resource_id:OTHERS Exception', 2);
243 okc_debug.Reset_Indentation;
244 END IF;
245
246 -- store SQL error message on message stack
247 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
248 p_msg_name => g_unexpected_error,
249 p_token1 => g_sqlcode_token,
250 p_token1_value => sqlcode,
251 p_token2 => g_sqlerrm_token,
252 p_token2_value => sqlerrm);
253 -- set error flag as UNEXPETED error
254 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
255
256 -- verify that cursor was closed
257 if l_jtfv_csr%ISOPEN then
258 close l_jtfv_csr;
259 end if;
260
261 End validate_resource_id;
262
263 -- Start of comments
264 --
265 -- Procedure Name : validate_access_level
266 -- Description :
267 -- Business Rules :
268 -- Parameters :
269 -- Version : 1.0
270 -- End of comments
271 procedure validate_access_level(x_return_status OUT NOCOPY VARCHAR2,
272 p_cacv_rec IN cacv_rec_type) is
273 Begin
274
275 IF (l_debug = 'Y') THEN
276 okc_debug.Set_Indentation('OKC_CAC_PVT');
277 okc_debug.log('1000: Entered validate_access_level', 2);
278 END IF;
279
280 -- initialize return status
281 x_return_status := OKC_API.G_RET_STS_SUCCESS;
282
283 -- check that data exists
284 If (p_cacv_rec.access_level = OKC_API.G_MISS_CHAR or
285 p_cacv_rec.access_level IS NULL)
286 Then
287 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
288 p_msg_name => g_required_value,
289 p_token1 => g_col_name_token,
290 p_token1_value => 'access_level');
291 -- set error flag
292 x_return_status := OKC_API.G_RET_STS_ERROR;
293
294 -- halt validation
295 raise G_EXCEPTION_HALT_VALIDATION;
296 End If;
297
298 -- check allowed values
299 If (upper(p_cacv_rec.access_level) NOT IN ('U','R')) Then
300 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
301 p_msg_name => g_invalid_value,
302 p_token1 => g_col_name_token,
303 p_token1_value => 'access_level');
304 -- set error flag
305 x_return_status := OKC_API.G_RET_STS_ERROR;
306 End If;
307
308 IF (l_debug = 'Y') THEN
309 okc_debug.log('1100: Leaving validate_access_level', 2);
310 okc_debug.Reset_Indentation;
311 END IF;
312
313 exception
314 when G_EXCEPTION_HALT_VALIDATION then
315
316 IF (l_debug = 'Y') THEN
317 okc_debug.log('1200: Exiting validate_access_level:G_EXCEPTION_HALT_VALIDATION Exception', 2);
318 okc_debug.Reset_Indentation;
319 END IF;
320
321 -- no processing necessary; validation can continue with next column
322 null;
323
324 when OTHERS then
325
326 IF (l_debug = 'Y') THEN
327 okc_debug.log('1300: Exiting validate_access_level:OTHERS Exception', 2);
328 okc_debug.Reset_Indentation;
329 END IF;
330
331 -- store SQL error message on message stack
332 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
333 p_msg_name => g_unexpected_error,
334 p_token1 => g_sqlcode_token,
335 p_token1_value => sqlcode,
336 p_token2 => g_sqlerrm_token,
337 p_token2_value => sqlerrm);
338 -- set error flag as UNEXPETED error
339 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
340
341 End validate_access_level;
342
343 /*********************** END HAND-CODED ********************************/
344 ---------------------------------------------------------------------------
345 -- FUNCTION get_seq_id
346 ---------------------------------------------------------------------------
347 FUNCTION get_seq_id RETURN NUMBER IS
348 BEGIN
349
350 RETURN(okc_p_util.raw_to_number(sys_guid()));
351
352 END get_seq_id;
353
354 ---------------------------------------------------------------------------
355 -- PROCEDURE qc
356 ---------------------------------------------------------------------------
357 PROCEDURE qc IS
358 BEGIN
359
360 null;
361
362 END qc;
363
364 ---------------------------------------------------------------------------
365 -- PROCEDURE change_version
366 ---------------------------------------------------------------------------
367 PROCEDURE change_version IS
368 BEGIN
369
370 null;
371
372 END change_version;
373
374 ---------------------------------------------------------------------------
375 -- PROCEDURE api_copy
376 ---------------------------------------------------------------------------
377 PROCEDURE api_copy IS
378 BEGIN
379
380 null;
381
382 END api_copy;
383
384 ---------------------------------------------------------------------------
385 -- FUNCTION get_rec for: OKC_K_ACCESSES
386 ---------------------------------------------------------------------------
387 FUNCTION get_rec (
388 p_cac_rec IN cac_rec_type,
389 x_no_data_found OUT NOCOPY BOOLEAN
390 ) RETURN cac_rec_type IS
391 CURSOR cac_pk_csr (p_id IN NUMBER) IS
392 SELECT
393 ID,
394 CHR_ID,
395 GROUP_ID,
396 RESOURCE_ID,
397 ACCESS_LEVEL,
398 OBJECT_VERSION_NUMBER,
399 CREATED_BY,
400 CREATION_DATE,
401 LAST_UPDATED_BY,
402 LAST_UPDATE_DATE,
403 LAST_UPDATE_LOGIN
404 FROM Okc_K_Accesses
405 WHERE okc_k_accesses.id = p_id;
406 l_cac_pk cac_pk_csr%ROWTYPE;
407 l_cac_rec cac_rec_type;
408 BEGIN
409
410 IF (l_debug = 'Y') THEN
411 okc_debug.Set_Indentation('OKC_CAC_PVT');
412 okc_debug.log('1500: Entered get_rec', 2);
413 END IF;
414
415 x_no_data_found := TRUE;
416 -- Get current database values
417 OPEN cac_pk_csr (p_cac_rec.id);
418 FETCH cac_pk_csr INTO
419 l_cac_rec.ID,
420 l_cac_rec.CHR_ID,
421 l_cac_rec.GROUP_ID,
422 l_cac_rec.RESOURCE_ID,
423 l_cac_rec.ACCESS_LEVEL,
424 l_cac_rec.OBJECT_VERSION_NUMBER,
425 l_cac_rec.CREATED_BY,
426 l_cac_rec.CREATION_DATE,
427 l_cac_rec.LAST_UPDATED_BY,
428 l_cac_rec.LAST_UPDATE_DATE,
429 l_cac_rec.LAST_UPDATE_LOGIN;
430 x_no_data_found := cac_pk_csr%NOTFOUND;
431 CLOSE cac_pk_csr;
432
433 IF (l_debug = 'Y') THEN
434 okc_debug.log('1600: Leaving get_rec ', 2);
435 okc_debug.Reset_Indentation;
436 END IF;
437
438 RETURN(l_cac_rec);
439
440 END get_rec;
441
442 FUNCTION get_rec (
443 p_cac_rec IN cac_rec_type
444 ) RETURN cac_rec_type IS
445 l_row_notfound BOOLEAN := TRUE;
446 BEGIN
447
448 RETURN(get_rec(p_cac_rec, l_row_notfound));
449
450 END get_rec;
451 ---------------------------------------------------------------------------
452 -- FUNCTION get_rec for: OKC_K_ACCESSES_V
453 ---------------------------------------------------------------------------
454 FUNCTION get_rec (
455 p_cacv_rec IN cacv_rec_type,
456 x_no_data_found OUT NOCOPY BOOLEAN
457 ) RETURN cacv_rec_type IS
458 CURSOR okc_cacv_pk_csr (p_id IN NUMBER) IS
459 SELECT
460 ID,
461 OBJECT_VERSION_NUMBER,
462 GROUP_ID,
463 CHR_ID,
464 RESOURCE_ID,
465 ACCESS_LEVEL,
466 CREATED_BY,
467 CREATION_DATE,
468 LAST_UPDATED_BY,
469 LAST_UPDATE_DATE,
470 LAST_UPDATE_LOGIN
471 FROM Okc_K_Accesses_V
472 WHERE okc_k_accesses_v.id = p_id;
473 l_okc_cacv_pk okc_cacv_pk_csr%ROWTYPE;
474 l_cacv_rec cacv_rec_type;
475 BEGIN
476
477 IF (l_debug = 'Y') THEN
478 okc_debug.Set_Indentation('OKC_CAC_PVT');
479 okc_debug.log('1700: Entered get_rec', 2);
480 END IF;
481
482 x_no_data_found := TRUE;
483 -- Get current database values
484 OPEN okc_cacv_pk_csr (p_cacv_rec.id);
485 FETCH okc_cacv_pk_csr INTO
486 l_cacv_rec.ID,
487 l_cacv_rec.OBJECT_VERSION_NUMBER,
488 l_cacv_rec.GROUP_ID,
489 l_cacv_rec.CHR_ID,
490 l_cacv_rec.RESOURCE_ID,
491 l_cacv_rec.ACCESS_LEVEL,
492 l_cacv_rec.CREATED_BY,
493 l_cacv_rec.CREATION_DATE,
494 l_cacv_rec.LAST_UPDATED_BY,
495 l_cacv_rec.LAST_UPDATE_DATE,
496 l_cacv_rec.LAST_UPDATE_LOGIN;
497 x_no_data_found := okc_cacv_pk_csr%NOTFOUND;
498 CLOSE okc_cacv_pk_csr;
499
500 IF (l_debug = 'Y') THEN
501 okc_debug.log('1800: Leaving get_rec ', 2);
502 okc_debug.Reset_Indentation;
503 END IF;
504
505 RETURN(l_cacv_rec);
506
507 END get_rec;
508
509 FUNCTION get_rec (
510 p_cacv_rec IN cacv_rec_type
511 ) RETURN cacv_rec_type IS
512 l_row_notfound BOOLEAN := TRUE;
513 BEGIN
514
515 RETURN(get_rec(p_cacv_rec, l_row_notfound));
516
517 END get_rec;
518
519 ------------------------------------------------------
520 -- FUNCTION null_out_defaults for: OKC_K_ACCESSES_V --
521 ------------------------------------------------------
522 FUNCTION null_out_defaults (
523 p_cacv_rec IN cacv_rec_type
524 ) RETURN cacv_rec_type IS
525 l_cacv_rec cacv_rec_type := p_cacv_rec;
526 BEGIN
527
528 IF (l_debug = 'Y') THEN
529 okc_debug.Set_Indentation('OKC_CAC_PVT');
530 okc_debug.log('1900: Entered null_out_defaults', 2);
531 END IF;
532
533 IF (l_cacv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
534 l_cacv_rec.object_version_number := NULL;
535 END IF;
536 IF (l_cacv_rec.group_id = OKC_API.G_MISS_NUM) THEN
537 l_cacv_rec.group_id := NULL;
538 END IF;
539 IF (l_cacv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
540 l_cacv_rec.chr_id := NULL;
541 END IF;
542 IF (l_cacv_rec.resource_id = OKC_API.G_MISS_NUM) THEN
543 l_cacv_rec.resource_id := NULL;
544 END IF;
545 IF (l_cacv_rec.access_level = OKC_API.G_MISS_CHAR) THEN
546 l_cacv_rec.access_level := NULL;
547 END IF;
548 IF (l_cacv_rec.created_by = OKC_API.G_MISS_NUM) THEN
549 l_cacv_rec.created_by := NULL;
550 END IF;
551 IF (l_cacv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
552 l_cacv_rec.creation_date := NULL;
553 END IF;
554 IF (l_cacv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
555 l_cacv_rec.last_updated_by := NULL;
556 END IF;
557 IF (l_cacv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
558 l_cacv_rec.last_update_date := NULL;
559 END IF;
560 IF (l_cacv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
561 l_cacv_rec.last_update_login := NULL;
562 END IF;
563
564 IF (l_debug = 'Y') THEN
565 okc_debug.log('2000: Leaving null_out_defaults ', 2);
566 okc_debug.Reset_Indentation;
567 END IF;
568
569 RETURN(l_cacv_rec);
570
571 END null_out_defaults;
572 ---------------------------------------------------------------------------
573 -- PROCEDURE Validate_Attributes
574 ---------------------------------------------------------------------------
575 ----------------------------------------------
576 -- Validate_Attributes for:OKC_K_ACCESSES_V --
577 ----------------------------------------------
578 FUNCTION Validate_Attributes (
579 p_cacv_rec IN cacv_rec_type
580 ) RETURN VARCHAR2 IS
581 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
582 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
583 BEGIN
584
585 IF (l_debug = 'Y') THEN
586 okc_debug.Set_Indentation('OKC_CAC_PVT');
587 okc_debug.log('2100: Entered Validate_Attributes', 2);
588 END IF;
589
590 /************************ HAND-CODED *********************************/
591 validate_group_id
592 (x_return_status => l_return_status,
593 p_cacv_rec => p_cacv_rec);
594
595 -- store the highest degree of error
596 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
597 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
598 x_return_status := l_return_status;
599 End If;
600 End If;
601
602 --dbms_output.put_line('2 Status : ' || l_return_status);
603 validate_chr_id
604 (x_return_status => l_return_status,
605 p_cacv_rec => p_cacv_rec);
606
607 -- store the highest degree of error
608 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
609 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
610 x_return_status := l_return_status;
611 End If;
612 End If;
613
614 --dbms_output.put_line('3 Status : ' || l_return_status);
615 validate_resource_id
616 (x_return_status => l_return_status,
617 p_cacv_rec => p_cacv_rec);
618
619 -- store the highest degree of error
620 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
621 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
622 x_return_status := l_return_status;
623 End If;
624 End If;
625
626 --dbms_output.put_line('4 Status : ' || l_return_status);
627 validate_access_level
628 (x_return_status => l_return_status,
629 p_cacv_rec => p_cacv_rec);
630
631 -- store the highest degree of error
632 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
633 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
634 x_return_status := l_return_status;
635 End If;
636 End If;
637
638 --dbms_output.put_line('5 Status : ' || l_return_status);
639 RETURN(x_return_status);
640
641 IF (l_debug = 'Y') THEN
642 okc_debug.log('2150: Leaving Validate_Attributes', 2);
643 okc_debug.Reset_Indentation;
644 END IF;
645
646 exception
647 when OTHERS then
648
649 IF (l_debug = 'Y') THEN
650 okc_debug.log('2200: Exiting Validate_Attributes:OTHERS Exception', 2);
651 okc_debug.Reset_Indentation;
652 END IF;
653
654 -- store SQL error message on message stack
655 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
656 p_msg_name => g_unexpected_error,
657 p_token1 => g_sqlcode_token,
658 p_token1_value => sqlcode,
659 p_token2 => g_sqlerrm_token,
660 p_token2_value => sqlerrm);
661
662 -- notify caller of an UNEXPETED error
663 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
664
665 -- return status to caller
666 RETURN(x_return_status);
667 /*********************** END HAND-CODED ********************************/
668
669 END Validate_Attributes;
670
671 ---------------------------------------------------------------------------
672 -- PROCEDURE Validate_Record
673 ---------------------------------------------------------------------------
674 ------------------------------------------
675 -- Validate_Record for:OKC_K_ACCESSES_V --
676 ------------------------------------------
677 FUNCTION Validate_Record (
678 p_cacv_rec IN cacv_rec_type
679 ) RETURN VARCHAR2 IS
680
681 -- ------------------------------------------------------
682 -- To check for any matching row, for unique combination.
683 -- The cursor includes id check filter to handle updates
684 -- for case K2 should not overwrite already existing K1
685 -- Two cursors with and without null condition on columns.
686 -- ------------------------------------------------------
687 CURSOR cur_cac_1 IS
688 SELECT 'x'
689 FROM okc_k_accesses
690 WHERE chr_id = p_cacv_rec.CHR_ID
691 AND resource_id = p_cacv_rec.RESOURCE_ID
692 AND group_id IS NULL
693 AND id <> NVL(p_cacv_rec.ID,-9999);
694
695 CURSOR cur_cac_2 IS
696 SELECT 'x'
697 FROM okc_k_accesses
698 WHERE chr_id = p_cacv_rec.CHR_ID
699 AND resource_id IS NULL
700 AND group_id = p_cacv_rec.GROUP_ID
701 AND id <> NVL(p_cacv_rec.ID,-9999);
702
703 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
704 l_row_found BOOLEAN := FALSE;
705 l_dummy VARCHAR2(1);
706
707 ----Bug-2314758
708 CURSOR group_name IS
709 SELECT name
710 FROM OKC_RESOURCE_GROUPS_V
711 WHERE group_id = p_cacv_rec.GROUP_ID;
712
713 CURSOR user_name IS
714 SELECT name
715 FROM OKC_RESOURCE_USERS_V
716 WHERE ID = p_cacv_rec.RESOURCE_ID;
717
718 l_user_group_name OKC_RESOURCE_GROUPS_V.NAME%TYPE;
719
720 l_token1_value VARCHAR2(25);
721 ----Bug-2314758
722
723 BEGIN
724
725 IF (l_debug = 'Y') THEN
726 okc_debug.Set_Indentation('OKC_CAC_PVT');
727 okc_debug.log('2400: Entered Validate_Record', 2);
728 END IF;
729
730 /************************ HAND-CODED ****************************/
731 -- ------------------------------------------------
732 -- RESOURCE_ID and GROUP_ID are mutually exclusive
733 -- ------------------------------------------------
734 IF ( p_cacv_rec.RESOURCE_ID IS NULL
735 AND p_cacv_rec.GROUP_ID IS NULL)
736 OR
737 ( p_cacv_rec.RESOURCE_ID IS NOT NULL
738 AND p_cacv_rec.GROUP_ID IS NOT NULL)
739 THEN
740 l_return_status := OKC_API.G_RET_STS_ERROR;
741 OKC_API.SET_MESSAGE(
742 p_app_name => g_app_name,
743 p_msg_name => g_invalid_value,
744 p_token1 => g_col_name_token,
745 p_token1_value => 'RESOURCE_ID',
746 p_token2 => g_col_name_token,
747 p_token2_value => 'GROUP_ID');
748
749 -- Set the return status as error
750 raise G_EXCEPTION_HALT_VALIDATION;
751 END IF;
752
753 -- ---------------------------------------------------------------------
754 -- Bug 1636056 related changes - Shyam
755 -- OKC_UTIL.check_comp_unique call replaced with
756 -- the explicit cursors above, for identical function to
757 -- check uniqueness for composite key CHR_ID + RESOURCE_ID + GROUP_ID.
758 -- RESOURCE_ID and GROUP_ID are mutually exclusive
759 -- ---------------------------------------------------------------------
760 IF ( p_cacv_rec.RESOURCE_ID IS NOT NULL
761 AND p_cacv_rec.RESOURCE_ID <> OKC_API.G_MISS_NUM )
762 THEN
763 OPEN cur_cac_1;
764 FETCH cur_cac_1 INTO l_dummy;
765 l_row_found := cur_cac_1%FOUND;
766 CLOSE cur_cac_1;
767 ELSIF ( p_cacv_rec.GROUP_ID IS NOT NULL
768 AND p_cacv_rec.GROUP_ID <> OKC_API.G_MISS_NUM )
769 THEN
770 OPEN cur_cac_2;
771 FETCH cur_cac_2 INTO l_dummy;
772 l_row_found := cur_cac_2%FOUND;
773 CLOSE cur_cac_2;
774 END IF;
775
776 IF (l_row_found) THEN
777 IF p_cacv_rec.GROUP_ID IS NOT NULL THEN
778 OPEN group_name;
779 FETCH group_name INTO l_user_group_name;
780 CLOSE group_name;
781 ELSIF p_cacv_rec.RESOURCE_ID IS NOT NULL THEN
782 OPEN user_name;
783 FETCH user_name INTO l_user_group_name;
784 CLOSE user_name;
785 END IF;
786
787 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
788 p_msg_name => 'OKC_DUP_K_ACCESS_COMP_KEY',
789 p_token1 => 'TOKEN1',
790 p_token1_value => l_user_group_name );
791
792 l_return_status := OKC_API.G_RET_STS_ERROR;
793 RAISE G_EXCEPTION_HALT_VALIDATION;
794 END IF;
795
796 /*********************** END HAND-CODED *************************/
797
798 IF (l_debug = 'Y') THEN
799 okc_debug.log('2500: Leaving Validate_Record', 2);
800 okc_debug.Reset_Indentation;
801 END IF;
802
803 RETURN (l_return_status);
804
805 EXCEPTION
806 WHEN G_EXCEPTION_HALT_VALIDATION THEN
807
808 IF (l_debug = 'Y') THEN
809 okc_debug.log('2600: Exiting Validate_Record:G_EXCEPTION_HALT_VALIDATION Exception', 2);
810 okc_debug.Reset_Indentation;
811 END IF;
812
813 -- no processing necessary; validation can continue with next column
814 RETURN (l_return_status);
815
816 END Validate_Record;
817
818 ---------------------------------------------------------------------------
819 -- PROCEDURE Migrate
820 ---------------------------------------------------------------------------
821 PROCEDURE migrate (
822 p_from IN cacv_rec_type,
823 p_to IN OUT NOCOPY cac_rec_type
824 ) IS
825 BEGIN
826
827 p_to.id := p_from.id;
828 p_to.chr_id := p_from.chr_id;
829 p_to.group_id := p_from.group_id;
830 p_to.resource_id := p_from.resource_id;
831 p_to.access_level := p_from.access_level;
832 p_to.object_version_number := p_from.object_version_number;
833 p_to.created_by := p_from.created_by;
834 p_to.creation_date := p_from.creation_date;
835 p_to.last_updated_by := p_from.last_updated_by;
836 p_to.last_update_date := p_from.last_update_date;
837 p_to.last_update_login := p_from.last_update_login;
838
839 END migrate;
840 PROCEDURE migrate (
841 p_from IN cac_rec_type,
842 p_to IN OUT NOCOPY cacv_rec_type
843 ) IS
844 BEGIN
845
846 p_to.id := p_from.id;
847 p_to.chr_id := p_from.chr_id;
848 p_to.group_id := p_from.group_id;
849 p_to.resource_id := p_from.resource_id;
850 p_to.access_level := p_from.access_level;
851 p_to.object_version_number := p_from.object_version_number;
852 p_to.created_by := p_from.created_by;
853 p_to.creation_date := p_from.creation_date;
854 p_to.last_updated_by := p_from.last_updated_by;
855 p_to.last_update_date := p_from.last_update_date;
856 p_to.last_update_login := p_from.last_update_login;
857
858 END migrate;
859
860 ---------------------------------------------------------------------------
861 -- PROCEDURE validate_row
862 ---------------------------------------------------------------------------
863 ---------------------------------------
864 -- validate_row for:OKC_K_ACCESSES_V --
865 ---------------------------------------
866 PROCEDURE validate_row(
867 p_api_version IN NUMBER,
868 p_init_msg_list IN VARCHAR2 ,
869 x_return_status OUT NOCOPY VARCHAR2,
870 x_msg_count OUT NOCOPY NUMBER,
871 x_msg_data OUT NOCOPY VARCHAR2,
872 p_cacv_rec IN cacv_rec_type) IS
873
874 l_api_version CONSTANT NUMBER := 1;
875 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
876 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
877 l_cacv_rec cacv_rec_type := p_cacv_rec;
878 l_cac_rec cac_rec_type;
879 BEGIN
880
881 IF (l_debug = 'Y') THEN
882 okc_debug.Set_Indentation('OKC_CAC_PVT');
883 okc_debug.log('2700: Entered validate_row', 2);
884 END IF;
885
886 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
887 G_PKG_NAME,
888 p_init_msg_list,
889 l_api_version,
890 p_api_version,
891 '_PVT',
892 x_return_status);
893 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
894 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
895 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
896 RAISE OKC_API.G_EXCEPTION_ERROR;
897 END IF;
898 --- Validate all non-missing attributes (Item Level Validation)
899 l_return_status := Validate_Attributes(l_cacv_rec);
900 --- If any errors happen abort API
901 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
902 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
903 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
904 RAISE OKC_API.G_EXCEPTION_ERROR;
905 END IF;
906 l_return_status := Validate_Record(l_cacv_rec);
907 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
908 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
909 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
910 RAISE OKC_API.G_EXCEPTION_ERROR;
911 END IF;
912 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
913
914 IF (l_debug = 'Y') THEN
915 okc_debug.log('2750: Leaving validate_row', 2);
916 okc_debug.Reset_Indentation;
917 END IF;
918
919 EXCEPTION
920 WHEN OKC_API.G_EXCEPTION_ERROR THEN
921
922 IF (l_debug = 'Y') THEN
923 okc_debug.log('2800: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
924 okc_debug.Reset_Indentation;
925 END IF;
926
927 x_return_status := OKC_API.HANDLE_EXCEPTIONS
928 (
929 l_api_name,
930 G_PKG_NAME,
931 'OKC_API.G_RET_STS_ERROR',
932 x_msg_count,
933 x_msg_data,
934 '_PVT'
935 );
936 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
937
938 IF (l_debug = 'Y') THEN
939 okc_debug.log('2900: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
940 okc_debug.Reset_Indentation;
941 END IF;
942
943 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
944 (
945 l_api_name,
946 G_PKG_NAME,
947 'OKC_API.G_RET_STS_UNEXP_ERROR',
948 x_msg_count,
949 x_msg_data,
950 '_PVT'
951 );
952 WHEN OTHERS THEN
953
954 IF (l_debug = 'Y') THEN
955 okc_debug.log('3000: Exiting validate_row:OTHERS Exception', 2);
956 okc_debug.Reset_Indentation;
957 END IF;
958
959 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
960 (
961 l_api_name,
962 G_PKG_NAME,
963 'OTHERS',
964 x_msg_count,
965 x_msg_data,
966 '_PVT'
967 );
968
969 END validate_row;
970 ------------------------------------------
971 -- PL/SQL TBL validate_row for:CACV_TBL --
972 ------------------------------------------
973 PROCEDURE validate_row(
974 p_api_version IN NUMBER,
975 p_init_msg_list IN VARCHAR2 ,
976 x_return_status OUT NOCOPY VARCHAR2,
977 x_msg_count OUT NOCOPY NUMBER,
978 x_msg_data OUT NOCOPY VARCHAR2,
979 p_cacv_tbl IN cacv_tbl_type) IS
980
981 l_api_version CONSTANT NUMBER := 1;
982 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
983 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
984 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
985 i NUMBER := 0;
986 BEGIN
987
988 IF (l_debug = 'Y') THEN
989 okc_debug.Set_Indentation('OKC_CAC_PVT');
990 okc_debug.log('3200: Entered validate_row', 2);
991 END IF;
992
993 OKC_API.init_msg_list(p_init_msg_list);
994 -- Make sure PL/SQL table has records in it before passing
995 IF (p_cacv_tbl.COUNT > 0) THEN
996 i := p_cacv_tbl.FIRST;
997 LOOP
998 validate_row (
999 p_api_version => p_api_version,
1000 p_init_msg_list => OKC_API.G_FALSE,
1001 x_return_status => x_return_status,
1002 x_msg_count => x_msg_count,
1003 x_msg_data => x_msg_data,
1004 p_cacv_rec => p_cacv_tbl(i));
1005
1006 -- store the highest degree of error
1007 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1008 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1009 l_overall_status := x_return_status;
1010 End If;
1011 End If;
1012
1013 EXIT WHEN (i = p_cacv_tbl.LAST);
1014 i := p_cacv_tbl.NEXT(i);
1015 END LOOP;
1016 -- return overall status
1017 x_return_status := l_overall_status;
1018 END IF;
1019
1020 IF (l_debug = 'Y') THEN
1021 okc_debug.log('3250: leaving validate_row', 2);
1022 okc_debug.Reset_Indentation;
1023 END IF;
1024
1025 EXCEPTION
1026 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1027
1028 IF (l_debug = 'Y') THEN
1029 okc_debug.log('3300: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1030 okc_debug.Reset_Indentation;
1031 END IF;
1032
1033 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1034 (
1035 l_api_name,
1036 G_PKG_NAME,
1037 'OKC_API.G_RET_STS_ERROR',
1038 x_msg_count,
1039 x_msg_data,
1040 '_PVT'
1041 );
1042 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1043
1044 IF (l_debug = 'Y') THEN
1045 okc_debug.log('3400: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1046 okc_debug.Reset_Indentation;
1047 END IF;
1048
1049 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1050 (
1051 l_api_name,
1052 G_PKG_NAME,
1053 'OKC_API.G_RET_STS_UNEXP_ERROR',
1054 x_msg_count,
1055 x_msg_data,
1056 '_PVT'
1057 );
1058 WHEN OTHERS THEN
1059
1060 IF (l_debug = 'Y') THEN
1061 okc_debug.log('3500: Exiting validate_row:OTHERS Exception', 2);
1062 okc_debug.Reset_Indentation;
1063 END IF;
1064
1065 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1066 (
1067 l_api_name,
1068 G_PKG_NAME,
1069 'OTHERS',
1070 x_msg_count,
1071 x_msg_data,
1072 '_PVT'
1073 );
1074
1075 END validate_row;
1076
1077 ---------------------------------------------------------------------------
1078 -- PROCEDURE insert_row
1079 ---------------------------------------------------------------------------
1080 -----------------------------------
1081 -- insert_row for:OKC_K_ACCESSES --
1082 -----------------------------------
1083 PROCEDURE insert_row(
1084 p_init_msg_list IN VARCHAR2 ,
1085 x_return_status OUT NOCOPY VARCHAR2,
1086 x_msg_count OUT NOCOPY NUMBER,
1087 x_msg_data OUT NOCOPY VARCHAR2,
1088 p_cac_rec IN cac_rec_type,
1089 x_cac_rec OUT NOCOPY cac_rec_type) IS
1090
1091 l_api_version CONSTANT NUMBER := 1;
1092 l_api_name CONSTANT VARCHAR2(30) := 'ACCESSES_insert_row';
1093 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1094 l_cac_rec cac_rec_type := p_cac_rec;
1095 l_def_cac_rec cac_rec_type;
1096 ---------------------------------------
1097 -- Set_Attributes for:OKC_K_ACCESSES --
1098 ---------------------------------------
1099 FUNCTION Set_Attributes (
1100 p_cac_rec IN cac_rec_type,
1101 x_cac_rec OUT NOCOPY cac_rec_type
1102 ) RETURN VARCHAR2 IS
1103 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1104 BEGIN
1105
1106 x_cac_rec := p_cac_rec;
1107 RETURN(l_return_status);
1108
1109 END Set_Attributes;
1110 BEGIN
1111
1112 IF (l_debug = 'Y') THEN
1113 okc_debug.Set_Indentation('OKC_CAC_PVT');
1114 okc_debug.log('3800: Entered insert_row', 2);
1115 END IF;
1116
1117 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1118 p_init_msg_list,
1119 '_PVT',
1120 x_return_status);
1121 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1122 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1123 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1124 RAISE OKC_API.G_EXCEPTION_ERROR;
1125 END IF;
1126 --- Setting item attributes
1127 l_return_status := Set_Attributes(
1128 p_cac_rec, -- IN
1129 l_cac_rec); -- OUT
1130 --- If any errors happen abort API
1131 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1132 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1133 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1134 RAISE OKC_API.G_EXCEPTION_ERROR;
1135 END IF;
1136 INSERT INTO OKC_K_ACCESSES(
1137 id,
1138 chr_id,
1139 group_id,
1140 resource_id,
1141 access_level,
1142 object_version_number,
1143 created_by,
1144 creation_date,
1145 last_updated_by,
1146 last_update_date,
1147 last_update_login)
1148 VALUES (
1149 l_cac_rec.id,
1150 l_cac_rec.chr_id,
1151 l_cac_rec.group_id,
1152 l_cac_rec.resource_id,
1153 l_cac_rec.access_level,
1154 l_cac_rec.object_version_number,
1155 l_cac_rec.created_by,
1156 l_cac_rec.creation_date,
1157 l_cac_rec.last_updated_by,
1158 l_cac_rec.last_update_date,
1159 l_cac_rec.last_update_login);
1160 -- Set OUT values
1161 x_cac_rec := l_cac_rec;
1162 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1163
1164 IF (l_debug = 'Y') THEN
1165 okc_debug.log('3900: Exiting insert_row', 2);
1166 okc_debug.Reset_Indentation;
1167 END IF;
1168
1169 EXCEPTION
1170 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1171
1172 IF (l_debug = 'Y') THEN
1173 okc_debug.log('4000: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1174 okc_debug.Reset_Indentation;
1175 END IF;
1176
1177 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1178 (
1179 l_api_name,
1180 G_PKG_NAME,
1181 'OKC_API.G_RET_STS_ERROR',
1182 x_msg_count,
1183 x_msg_data,
1184 '_PVT'
1185 );
1186 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1187
1188 IF (l_debug = 'Y') THEN
1189 okc_debug.log('4100: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1190 okc_debug.Reset_Indentation;
1191 END IF;
1192
1193 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1194 (
1195 l_api_name,
1196 G_PKG_NAME,
1197 'OKC_API.G_RET_STS_UNEXP_ERROR',
1198 x_msg_count,
1199 x_msg_data,
1200 '_PVT'
1201 );
1202 WHEN OTHERS THEN
1203
1204 IF (l_debug = 'Y') THEN
1205 okc_debug.log('4200: Exiting insert_row:OTHERS Exception', 2);
1206 okc_debug.Reset_Indentation;
1207 END IF;
1208
1209 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1210 (
1211 l_api_name,
1212 G_PKG_NAME,
1213 'OTHERS',
1214 x_msg_count,
1215 x_msg_data,
1216 '_PVT'
1217 );
1218
1219 END insert_row;
1220 -------------------------------------
1221 -- insert_row for:OKC_K_ACCESSES_V --
1222 -------------------------------------
1223 PROCEDURE insert_row(
1224 p_api_version IN NUMBER,
1225 p_init_msg_list IN VARCHAR2 ,
1226 x_return_status OUT NOCOPY VARCHAR2,
1227 x_msg_count OUT NOCOPY NUMBER,
1228 x_msg_data OUT NOCOPY VARCHAR2,
1229 p_cacv_rec IN cacv_rec_type,
1230 x_cacv_rec OUT NOCOPY cacv_rec_type) IS
1231
1232 l_api_version CONSTANT NUMBER := 1;
1233 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1234 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1235 l_cacv_rec cacv_rec_type;
1236 l_def_cacv_rec cacv_rec_type;
1237 l_cac_rec cac_rec_type;
1238 lx_cac_rec cac_rec_type;
1239 -------------------------------
1240 -- FUNCTION fill_who_columns --
1241 -------------------------------
1242 FUNCTION fill_who_columns (
1243 p_cacv_rec IN cacv_rec_type
1244 ) RETURN cacv_rec_type IS
1245 l_cacv_rec cacv_rec_type := p_cacv_rec;
1246 BEGIN
1247
1248 IF (l_debug = 'Y') THEN
1249 okc_debug.Set_Indentation('OKC_CAC_PVT');
1250 okc_debug.log('4400: Entered fill_who_columns', 2);
1251 END IF;
1252
1253 l_cacv_rec.CREATION_DATE := SYSDATE;
1254 l_cacv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1255 l_cacv_rec.LAST_UPDATE_DATE := l_cacv_rec.CREATION_DATE;
1256 l_cacv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1257 l_cacv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1258
1259 IF (l_debug = 'Y') THEN
1260 okc_debug.log('4500: Leaving fill_who_columns', 2);
1261 okc_debug.Reset_Indentation;
1262 END IF;
1263
1264 RETURN(l_cacv_rec);
1265
1266 END fill_who_columns;
1267 -----------------------------------------
1268 -- Set_Attributes for:OKC_K_ACCESSES_V --
1269 -----------------------------------------
1270 FUNCTION Set_Attributes (
1271 p_cacv_rec IN cacv_rec_type,
1272 x_cacv_rec OUT NOCOPY cacv_rec_type
1273 ) RETURN VARCHAR2 IS
1274 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1275 BEGIN
1276
1277 IF (l_debug = 'Y') THEN
1278 okc_debug.Set_Indentation('OKC_CAC_PVT');
1279 okc_debug.log('4600: Entered Set_Attributes', 2);
1280 END IF;
1281
1282 x_cacv_rec := p_cacv_rec;
1283 /************************ HAND-CODED *********************************/
1284 x_cacv_rec.ACCESS_LEVEL := UPPER(x_cacv_rec.ACCESS_LEVEL);
1285 /*********************** END HAND-CODED ********************************/
1286 x_cacv_rec.OBJECT_VERSION_NUMBER := 1;
1287
1288 IF (l_debug = 'Y') THEN
1289 okc_debug.log('4700: Leaving Set_Attributes', 2);
1290 okc_debug.Reset_Indentation;
1291 END IF;
1292
1293 RETURN(l_return_status);
1294 END Set_Attributes;
1295
1296 BEGIN
1297
1298 IF (l_debug = 'Y') THEN
1299 okc_debug.Set_Indentation('OKC_CAC_PVT');
1300 okc_debug.log('4800: Entered insert_row', 2);
1301 END IF;
1302
1303 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1304 G_PKG_NAME,
1305 p_init_msg_list,
1306 l_api_version,
1307 p_api_version,
1308 '_PVT',
1309 x_return_status);
1310 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1311 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1312 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1313 RAISE OKC_API.G_EXCEPTION_ERROR;
1314 END IF;
1315 l_cacv_rec := null_out_defaults(p_cacv_rec);
1316 -- Set primary key value
1317 l_cacv_rec.ID := get_seq_id;
1318 --- Setting item attributes
1319 l_return_status := Set_Attributes(
1320 l_cacv_rec, -- IN
1321 l_def_cacv_rec); -- OUT
1322 --- If any errors happen abort API
1323 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1324 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1325 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1326 RAISE OKC_API.G_EXCEPTION_ERROR;
1327 END IF;
1328 l_def_cacv_rec := fill_who_columns(l_def_cacv_rec);
1329 --- Validate all non-missing attributes (Item Level Validation)
1330 l_return_status := Validate_Attributes(l_def_cacv_rec);
1331 --- If any errors happen abort API
1332 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1333 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1334 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1335 RAISE OKC_API.G_EXCEPTION_ERROR;
1336 END IF;
1337 l_return_status := Validate_Record(l_def_cacv_rec);
1338 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1339 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1340 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1341 RAISE OKC_API.G_EXCEPTION_ERROR;
1342 END IF;
1343 --------------------------------------
1344 -- Move VIEW record to "Child" records
1345 --------------------------------------
1346 migrate(l_def_cacv_rec, l_cac_rec);
1347 --------------------------------------------
1348 -- Call the INSERT_ROW for each child record
1349 --------------------------------------------
1350 insert_row(
1351 p_init_msg_list,
1352 x_return_status,
1353 x_msg_count,
1354 x_msg_data,
1355 l_cac_rec,
1356 lx_cac_rec
1357 );
1358 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1359 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1360 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1361 RAISE OKC_API.G_EXCEPTION_ERROR;
1362 END IF;
1363 migrate(lx_cac_rec, l_def_cacv_rec);
1364 -- Set OUT values
1365 x_cacv_rec := l_def_cacv_rec;
1366 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1367
1368 IF (l_debug = 'Y') THEN
1369 okc_debug.log('4850: Leaving insert_row', 2);
1370 okc_debug.Reset_Indentation;
1371 END IF;
1372
1373 EXCEPTION
1374 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1375
1376 IF (l_debug = 'Y') THEN
1377 okc_debug.log('4900: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1378 okc_debug.Reset_Indentation;
1379 END IF;
1380
1381 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1382 (
1383 l_api_name,
1384 G_PKG_NAME,
1385 'OKC_API.G_RET_STS_ERROR',
1386 x_msg_count,
1387 x_msg_data,
1388 '_PVT'
1389 );
1390 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1391
1392 IF (l_debug = 'Y') THEN
1393 okc_debug.log('5000: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1394 okc_debug.Reset_Indentation;
1395 END IF;
1396
1397 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1398 (
1399 l_api_name,
1400 G_PKG_NAME,
1401 'OKC_API.G_RET_STS_UNEXP_ERROR',
1402 x_msg_count,
1403 x_msg_data,
1404 '_PVT'
1405 );
1406 WHEN OTHERS THEN
1407
1408 IF (l_debug = 'Y') THEN
1409 okc_debug.log('5100: Exiting insert_row:OTHERS Exception', 2);
1410 okc_debug.Reset_Indentation;
1411 END IF;
1412
1413 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1414 (
1415 l_api_name,
1416 G_PKG_NAME,
1417 'OTHERS',
1418 x_msg_count,
1419 x_msg_data,
1420 '_PVT'
1421 );
1422
1423 END insert_row;
1424 ----------------------------------------
1425 -- PL/SQL TBL insert_row for:CACV_TBL --
1426 ----------------------------------------
1427 PROCEDURE insert_row(
1428 p_api_version IN NUMBER,
1429 p_init_msg_list IN VARCHAR2 ,
1430 x_return_status OUT NOCOPY VARCHAR2,
1431 x_msg_count OUT NOCOPY NUMBER,
1432 x_msg_data OUT NOCOPY VARCHAR2,
1433 p_cacv_tbl IN cacv_tbl_type,
1434 x_cacv_tbl OUT NOCOPY cacv_tbl_type) IS
1435
1436 l_api_version CONSTANT NUMBER := 1;
1437 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1438 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1439 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1440 i NUMBER := 0;
1441 BEGIN
1442
1443 IF (l_debug = 'Y') THEN
1444 okc_debug.Set_Indentation('OKC_CAC_PVT');
1445 okc_debug.log('5300: Entered insert_row', 2);
1446 END IF;
1447
1448 OKC_API.init_msg_list(p_init_msg_list);
1449 -- Make sure PL/SQL table has records in it before passing
1450 IF (p_cacv_tbl.COUNT > 0) THEN
1451 i := p_cacv_tbl.FIRST;
1452 LOOP
1453 insert_row (
1454 p_api_version => p_api_version,
1455 p_init_msg_list => OKC_API.G_FALSE,
1456 x_return_status => x_return_status,
1457 x_msg_count => x_msg_count,
1458 x_msg_data => x_msg_data,
1459 p_cacv_rec => p_cacv_tbl(i),
1460 x_cacv_rec => x_cacv_tbl(i));
1461
1462 -- store the highest degree of error
1463 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1464 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1465 l_overall_status := x_return_status;
1466 End If;
1467 End If;
1468
1469 EXIT WHEN (i = p_cacv_tbl.LAST);
1470 i := p_cacv_tbl.NEXT(i);
1471 END LOOP;
1472 -- return overall status
1473 x_return_status := l_overall_status;
1474 END IF;
1475
1476 IF (l_debug = 'Y') THEN
1477 okc_debug.log('5350: Leaving insert_row', 2);
1478 okc_debug.Reset_Indentation;
1479 END IF;
1480
1481 EXCEPTION
1482 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1483
1484 IF (l_debug = 'Y') THEN
1485 okc_debug.log('5400: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1486 okc_debug.Reset_Indentation;
1487 END IF;
1488
1489 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1490 (
1491 l_api_name,
1492 G_PKG_NAME,
1493 'OKC_API.G_RET_STS_ERROR',
1494 x_msg_count,
1495 x_msg_data,
1496 '_PVT'
1497 );
1498 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1499
1500 IF (l_debug = 'Y') THEN
1501 okc_debug.log('5500: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1502 okc_debug.Reset_Indentation;
1503 END IF;
1504
1505 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1506 (
1507 l_api_name,
1508 G_PKG_NAME,
1509 'OKC_API.G_RET_STS_UNEXP_ERROR',
1510 x_msg_count,
1511 x_msg_data,
1512 '_PVT'
1513 );
1514 WHEN OTHERS THEN
1515
1516 IF (l_debug = 'Y') THEN
1517 okc_debug.log('5600: Exiting insert_row:OTHERS Exception', 2);
1518 okc_debug.Reset_Indentation;
1519 END IF;
1520
1521 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1522 (
1523 l_api_name,
1524 G_PKG_NAME,
1525 'OTHERS',
1526 x_msg_count,
1527 x_msg_data,
1528 '_PVT'
1529 );
1530
1531 END insert_row;
1532
1533 ---------------------------------------------------------------------------
1534 -- PROCEDURE lock_row
1535 ---------------------------------------------------------------------------
1536 ---------------------------------
1537 -- lock_row for:OKC_K_ACCESSES --
1538 ---------------------------------
1539 PROCEDURE lock_row(
1540 p_init_msg_list IN VARCHAR2 ,
1541 x_return_status OUT NOCOPY VARCHAR2,
1542 x_msg_count OUT NOCOPY NUMBER,
1543 x_msg_data OUT NOCOPY VARCHAR2,
1544 p_cac_rec IN cac_rec_type) IS
1545
1546 E_Resource_Busy EXCEPTION;
1547 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1548 CURSOR lock_csr (p_cac_rec IN cac_rec_type) IS
1549 SELECT OBJECT_VERSION_NUMBER
1550 FROM OKC_K_ACCESSES
1551 WHERE ID = p_cac_rec.id
1552 AND OBJECT_VERSION_NUMBER = p_cac_rec.object_version_number
1553 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1554
1555 CURSOR lchk_csr (p_cac_rec IN cac_rec_type) IS
1556 SELECT OBJECT_VERSION_NUMBER
1557 FROM OKC_K_ACCESSES
1558 WHERE ID = p_cac_rec.id;
1559 l_api_version CONSTANT NUMBER := 1;
1560 l_api_name CONSTANT VARCHAR2(30) := 'ACCESSES_lock_row';
1561 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1562 l_object_version_number OKC_K_ACCESSES.OBJECT_VERSION_NUMBER%TYPE;
1563 lc_object_version_number OKC_K_ACCESSES.OBJECT_VERSION_NUMBER%TYPE;
1564 l_row_notfound BOOLEAN := FALSE;
1565 lc_row_notfound BOOLEAN := FALSE;
1566 BEGIN
1567
1568 IF (l_debug = 'Y') THEN
1569 okc_debug.Set_Indentation('OKC_CAC_PVT');
1570 okc_debug.log('5800: Entered lock_row', 2);
1571 END IF;
1572
1573 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1574 p_init_msg_list,
1575 '_PVT',
1576 x_return_status);
1577 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1578 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1579 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1580 RAISE OKC_API.G_EXCEPTION_ERROR;
1581 END IF;
1582 BEGIN
1583
1584 IF (l_debug = 'Y') THEN
1585 okc_debug.Set_Indentation('OKC_CAC_PVT');
1586 okc_debug.log('5810: Entered lock_row', 2);
1587 END IF;
1588
1589 OPEN lock_csr(p_cac_rec);
1590 FETCH lock_csr INTO l_object_version_number;
1591 l_row_notfound := lock_csr%NOTFOUND;
1592 CLOSE lock_csr;
1593
1594 IF (l_debug = 'Y') THEN
1595 okc_debug.log('5820: Leaving LOCK_row', 2);
1596 okc_debug.Reset_Indentation;
1597 END IF;
1598
1599 EXCEPTION
1600 WHEN E_Resource_Busy THEN
1601
1602 IF (l_debug = 'Y') THEN
1603 okc_debug.log('5830: Exiting lock_row:E_Resource_Busy Exception', 2);
1604 okc_debug.Reset_Indentation;
1605 END IF;
1606
1607 IF (lock_csr%ISOPEN) THEN
1608 CLOSE lock_csr;
1609 END IF;
1610 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1611 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1612 END;
1613
1614 IF ( l_row_notfound ) THEN
1615 OPEN lchk_csr(p_cac_rec);
1616 FETCH lchk_csr INTO lc_object_version_number;
1617 lc_row_notfound := lchk_csr%NOTFOUND;
1618 CLOSE lchk_csr;
1619 END IF;
1620 IF (lc_row_notfound) THEN
1621 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1622 RAISE OKC_API.G_EXCEPTION_ERROR;
1623 ELSIF lc_object_version_number > p_cac_rec.object_version_number THEN
1624 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1625 RAISE OKC_API.G_EXCEPTION_ERROR;
1626 ELSIF lc_object_version_number <> p_cac_rec.object_version_number THEN
1627 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1628 RAISE OKC_API.G_EXCEPTION_ERROR;
1629 ELSIF lc_object_version_number = -1 THEN
1630 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1631 RAISE OKC_API.G_EXCEPTION_ERROR;
1632 END IF;
1633 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1634
1635 IF (l_debug = 'Y') THEN
1636 okc_debug.log('6000: Exiting lock_row', 2);
1637 okc_debug.Reset_Indentation;
1638 END IF;
1639
1640 EXCEPTION
1641 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1642
1643 IF (l_debug = 'Y') THEN
1644 okc_debug.log('6100: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1645 okc_debug.Reset_Indentation;
1646 END IF;
1647
1648 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1649 (
1650 l_api_name,
1651 G_PKG_NAME,
1652 'OKC_API.G_RET_STS_ERROR',
1653 x_msg_count,
1654 x_msg_data,
1655 '_PVT'
1656 );
1657 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1658
1659 IF (l_debug = 'Y') THEN
1660 okc_debug.log('6200: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1661 okc_debug.Reset_Indentation;
1662 END IF;
1663
1664 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1665 (
1666 l_api_name,
1667 G_PKG_NAME,
1668 'OKC_API.G_RET_STS_UNEXP_ERROR',
1669 x_msg_count,
1670 x_msg_data,
1671 '_PVT'
1672 );
1673 WHEN OTHERS THEN
1674
1675 IF (l_debug = 'Y') THEN
1676 okc_debug.log('6300: Exiting lock_row:OTHERS Exception', 2);
1677 okc_debug.Reset_Indentation;
1678 END IF;
1679
1680 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1681 (
1682 l_api_name,
1683 G_PKG_NAME,
1684 'OTHERS',
1685 x_msg_count,
1686 x_msg_data,
1687 '_PVT'
1688 );
1689
1690 END lock_row;
1691 -----------------------------------
1692 -- lock_row for:OKC_K_ACCESSES_V --
1693 -----------------------------------
1694 PROCEDURE lock_row(
1695 p_api_version IN NUMBER,
1696 p_init_msg_list IN VARCHAR2 ,
1697 x_return_status OUT NOCOPY VARCHAR2,
1698 x_msg_count OUT NOCOPY NUMBER,
1699 x_msg_data OUT NOCOPY VARCHAR2,
1700 p_cacv_rec IN cacv_rec_type) IS
1701
1702 l_api_version CONSTANT NUMBER := 1;
1703 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1704 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1705 l_cac_rec cac_rec_type;
1706 BEGIN
1707
1708 IF (l_debug = 'Y') THEN
1709 okc_debug.Set_Indentation('OKC_CAC_PVT');
1710 okc_debug.log('6500: Entered lock_row', 2);
1711 END IF;
1712
1713 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1714 G_PKG_NAME,
1715 p_init_msg_list,
1716 l_api_version,
1717 p_api_version,
1718 '_PVT',
1719 x_return_status);
1720 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1721 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1722 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1723 RAISE OKC_API.G_EXCEPTION_ERROR;
1724 END IF;
1725 --------------------------------------
1726 -- Move VIEW record to "Child" records
1727 --------------------------------------
1728 migrate(p_cacv_rec, l_cac_rec);
1729 --------------------------------------------
1730 -- Call the LOCK_ROW for each child record
1731 --------------------------------------------
1732 lock_row(
1733 p_init_msg_list,
1734 x_return_status,
1735 x_msg_count,
1736 x_msg_data,
1737 l_cac_rec
1738 );
1739 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1740 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1741 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1742 RAISE OKC_API.G_EXCEPTION_ERROR;
1743 END IF;
1744 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1745
1746 IF (l_debug = 'Y') THEN
1747 okc_debug.log('6550: Leaving lock_row ', 2);
1748 okc_debug.Reset_Indentation;
1749 END IF;
1750
1751 EXCEPTION
1752 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1753
1754 IF (l_debug = 'Y') THEN
1755 okc_debug.log('6600: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1756 okc_debug.Reset_Indentation;
1757 END IF;
1758
1759 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1760 (
1761 l_api_name,
1762 G_PKG_NAME,
1763 'OKC_API.G_RET_STS_ERROR',
1764 x_msg_count,
1765 x_msg_data,
1766 '_PVT'
1767 );
1768 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1769
1770 IF (l_debug = 'Y') THEN
1771 okc_debug.log('6700: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1772 okc_debug.Reset_Indentation;
1773 END IF;
1774
1775 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1776 (
1777 l_api_name,
1778 G_PKG_NAME,
1779 'OKC_API.G_RET_STS_UNEXP_ERROR',
1780 x_msg_count,
1781 x_msg_data,
1782 '_PVT'
1783 );
1784 WHEN OTHERS THEN
1785
1786 IF (l_debug = 'Y') THEN
1787 okc_debug.log('6800: Exiting lock_row:OTHERS Exception', 2);
1788 okc_debug.Reset_Indentation;
1789 END IF;
1790
1791 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1792 (
1793 l_api_name,
1794 G_PKG_NAME,
1795 'OTHERS',
1796 x_msg_count,
1797 x_msg_data,
1798 '_PVT'
1799 );
1800
1801 END lock_row;
1802 --------------------------------------
1803 -- PL/SQL TBL lock_row for:CACV_TBL --
1804 --------------------------------------
1805 PROCEDURE lock_row(
1806 p_api_version IN NUMBER,
1807 p_init_msg_list IN VARCHAR2 ,
1808 x_return_status OUT NOCOPY VARCHAR2,
1809 x_msg_count OUT NOCOPY NUMBER,
1810 x_msg_data OUT NOCOPY VARCHAR2,
1811 p_cacv_tbl IN cacv_tbl_type) IS
1812
1813 l_api_version CONSTANT NUMBER := 1;
1814 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1815 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1816 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1817 i NUMBER := 0;
1818 BEGIN
1819
1820 IF (l_debug = 'Y') THEN
1821 okc_debug.Set_Indentation('OKC_CAC_PVT');
1822 okc_debug.log('7000: Entered lock_row', 2);
1823 END IF;
1824
1825 OKC_API.init_msg_list(p_init_msg_list);
1826 -- Make sure PL/SQL table has records in it before passing
1827 IF (p_cacv_tbl.COUNT > 0) THEN
1828 i := p_cacv_tbl.FIRST;
1829 LOOP
1830 lock_row (
1831 p_api_version => p_api_version,
1832 p_init_msg_list => OKC_API.G_FALSE,
1833 x_return_status => x_return_status,
1834 x_msg_count => x_msg_count,
1835 x_msg_data => x_msg_data,
1836 p_cacv_rec => p_cacv_tbl(i));
1837
1838 -- store the highest degree of error
1839 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1840 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1841 l_overall_status := x_return_status;
1842 End If;
1843 End If;
1844
1845 EXIT WHEN (i = p_cacv_tbl.LAST);
1846 i := p_cacv_tbl.NEXT(i);
1847 END LOOP;
1848 -- return overall status
1849 x_return_status := l_overall_status;
1850 END IF;
1851
1852 IF (l_debug = 'Y') THEN
1853 okc_debug.log('7050: Leaving lock_row', 2);
1854 okc_debug.Reset_Indentation;
1855 END IF;
1856
1857 EXCEPTION
1858 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1859
1860 IF (l_debug = 'Y') THEN
1861 okc_debug.log('7100: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1862 okc_debug.Reset_Indentation;
1863 END IF;
1864
1865 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1866 (
1867 l_api_name,
1868 G_PKG_NAME,
1869 'OKC_API.G_RET_STS_ERROR',
1870 x_msg_count,
1871 x_msg_data,
1872 '_PVT'
1873 );
1874 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1875
1876 IF (l_debug = 'Y') THEN
1877 okc_debug.log('7200: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1878 okc_debug.Reset_Indentation;
1879 END IF;
1880
1881 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1882 (
1883 l_api_name,
1884 G_PKG_NAME,
1885 'OKC_API.G_RET_STS_UNEXP_ERROR',
1886 x_msg_count,
1887 x_msg_data,
1888 '_PVT'
1889 );
1890 WHEN OTHERS THEN
1891
1892 IF (l_debug = 'Y') THEN
1893 okc_debug.log('7300: Exiting lock_row:OTHERS Exception', 2);
1894 okc_debug.Reset_Indentation;
1895 END IF;
1896
1897 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1898 (
1899 l_api_name,
1900 G_PKG_NAME,
1901 'OTHERS',
1902 x_msg_count,
1903 x_msg_data,
1904 '_PVT'
1905 );
1906
1907 END lock_row;
1908
1909 ---------------------------------------------------------------------------
1910 -- PROCEDURE update_row
1911 ---------------------------------------------------------------------------
1912 -----------------------------------
1913 -- update_row for:OKC_K_ACCESSES --
1914 -----------------------------------
1915 PROCEDURE update_row(
1916 p_init_msg_list IN VARCHAR2 ,
1917 x_return_status OUT NOCOPY VARCHAR2,
1918 x_msg_count OUT NOCOPY NUMBER,
1919 x_msg_data OUT NOCOPY VARCHAR2,
1920 p_cac_rec IN cac_rec_type,
1921 x_cac_rec OUT NOCOPY cac_rec_type) IS
1922
1923 l_api_version CONSTANT NUMBER := 1;
1924 l_api_name CONSTANT VARCHAR2(30) := 'ACCESSES_update_row';
1925 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1926 l_cac_rec cac_rec_type := p_cac_rec;
1927 l_def_cac_rec cac_rec_type;
1928 l_row_notfound BOOLEAN := TRUE;
1929 ----------------------------------
1930 -- FUNCTION populate_new_record --
1931 ----------------------------------
1932 FUNCTION populate_new_record (
1933 p_cac_rec IN cac_rec_type,
1934 x_cac_rec OUT NOCOPY cac_rec_type
1935 ) RETURN VARCHAR2 IS
1936 l_cac_rec cac_rec_type;
1937 l_row_notfound BOOLEAN := TRUE;
1938 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1939 BEGIN
1940
1941 IF (l_debug = 'Y') THEN
1942 okc_debug.Set_Indentation('OKC_CAC_PVT');
1943 okc_debug.log('7500: Entered populate_new_record', 2);
1944 END IF;
1945
1946 x_cac_rec := p_cac_rec;
1947 -- Get current database values
1948 l_cac_rec := get_rec(p_cac_rec, l_row_notfound);
1949 IF (l_row_notfound) THEN
1950 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1951 END IF;
1952 IF (x_cac_rec.id = OKC_API.G_MISS_NUM)
1953 THEN
1954 x_cac_rec.id := l_cac_rec.id;
1955 END IF;
1956 IF (x_cac_rec.chr_id = OKC_API.G_MISS_NUM)
1957 THEN
1958 x_cac_rec.chr_id := l_cac_rec.chr_id;
1959 END IF;
1960 IF (x_cac_rec.group_id = OKC_API.G_MISS_NUM)
1961 THEN
1962 x_cac_rec.group_id := l_cac_rec.group_id;
1963 END IF;
1964 IF (x_cac_rec.resource_id = OKC_API.G_MISS_NUM)
1965 THEN
1966 x_cac_rec.resource_id := l_cac_rec.resource_id;
1967 END IF;
1968 IF (x_cac_rec.access_level = OKC_API.G_MISS_CHAR)
1969 THEN
1970 x_cac_rec.access_level := l_cac_rec.access_level;
1971 END IF;
1972 IF (x_cac_rec.object_version_number = OKC_API.G_MISS_NUM)
1973 THEN
1974 x_cac_rec.object_version_number := l_cac_rec.object_version_number;
1975 END IF;
1976 IF (x_cac_rec.created_by = OKC_API.G_MISS_NUM)
1977 THEN
1978 x_cac_rec.created_by := l_cac_rec.created_by;
1979 END IF;
1980 IF (x_cac_rec.creation_date = OKC_API.G_MISS_DATE)
1981 THEN
1982 x_cac_rec.creation_date := l_cac_rec.creation_date;
1983 END IF;
1984 IF (x_cac_rec.last_updated_by = OKC_API.G_MISS_NUM)
1985 THEN
1986 x_cac_rec.last_updated_by := l_cac_rec.last_updated_by;
1987 END IF;
1988 IF (x_cac_rec.last_update_date = OKC_API.G_MISS_DATE)
1989 THEN
1990 x_cac_rec.last_update_date := l_cac_rec.last_update_date;
1991 END IF;
1992 IF (x_cac_rec.last_update_login = OKC_API.G_MISS_NUM)
1993 THEN
1994 x_cac_rec.last_update_login := l_cac_rec.last_update_login;
1995 END IF;
1996
1997 IF (l_debug = 'Y') THEN
1998 okc_debug.log('7600: Leaving lock_row', 2);
1999 okc_debug.Reset_Indentation;
2000 END IF;
2001
2002 RETURN(l_return_status);
2003
2004 END populate_new_record;
2005 ---------------------------------------
2006 -- Set_Attributes for:OKC_K_ACCESSES --
2007 ---------------------------------------
2008 FUNCTION Set_Attributes (
2009 p_cac_rec IN cac_rec_type,
2010 x_cac_rec OUT NOCOPY cac_rec_type
2011 ) RETURN VARCHAR2 IS
2012 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2013 BEGIN
2014 x_cac_rec := p_cac_rec;
2015 RETURN(l_return_status);
2016
2017 END Set_Attributes;
2018 BEGIN
2019
2020 IF (l_debug = 'Y') THEN
2021 okc_debug.Set_Indentation('OKC_CAC_PVT');
2022 okc_debug.log('7700: Entered update_row', 2);
2023 END IF;
2024
2025 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2026 p_init_msg_list,
2027 '_PVT',
2028 x_return_status);
2029 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2030 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2031 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2032 RAISE OKC_API.G_EXCEPTION_ERROR;
2033 END IF;
2034 --- Setting item attributes
2035 l_return_status := Set_Attributes(
2036 p_cac_rec, -- IN
2037 l_cac_rec); -- OUT
2038 --- If any errors happen abort API
2039 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2040 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2041 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2042 RAISE OKC_API.G_EXCEPTION_ERROR;
2043 END IF;
2044 l_return_status := populate_new_record(l_cac_rec, l_def_cac_rec);
2045 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2046 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2047 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2048 RAISE OKC_API.G_EXCEPTION_ERROR;
2049 END IF;
2050 UPDATE OKC_K_ACCESSES
2051 SET CHR_ID = l_def_cac_rec.chr_id,
2052 GROUP_ID = l_def_cac_rec.group_id,
2053 RESOURCE_ID = l_def_cac_rec.resource_id,
2054 ACCESS_LEVEL = l_def_cac_rec.access_level,
2055 OBJECT_VERSION_NUMBER = l_def_cac_rec.object_version_number,
2056 CREATED_BY = l_def_cac_rec.created_by,
2057 CREATION_DATE = l_def_cac_rec.creation_date,
2058 LAST_UPDATED_BY = l_def_cac_rec.last_updated_by,
2059 LAST_UPDATE_DATE = l_def_cac_rec.last_update_date,
2060 LAST_UPDATE_LOGIN = l_def_cac_rec.last_update_login
2061 WHERE ID = l_def_cac_rec.id;
2062
2063 x_cac_rec := l_def_cac_rec;
2064 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2065
2066 IF (l_debug = 'Y') THEN
2067 okc_debug.log('7750: Leaving update_row', 2);
2068 okc_debug.Reset_Indentation;
2069 END IF;
2070
2071 EXCEPTION
2072 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2073
2074 IF (l_debug = 'Y') THEN
2075 okc_debug.log('7800: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2076 okc_debug.Reset_Indentation;
2077 END IF;
2078
2079 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2080 (
2081 l_api_name,
2082 G_PKG_NAME,
2083 'OKC_API.G_RET_STS_ERROR',
2084 x_msg_count,
2085 x_msg_data,
2086 '_PVT'
2087 );
2088 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2089
2090 IF (l_debug = 'Y') THEN
2091 okc_debug.log('7900: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2092 okc_debug.Reset_Indentation;
2093 END IF;
2094
2095 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2096 (
2097 l_api_name,
2098 G_PKG_NAME,
2099 'OKC_API.G_RET_STS_UNEXP_ERROR',
2100 x_msg_count,
2101 x_msg_data,
2102 '_PVT'
2103 );
2104 WHEN OTHERS THEN
2105
2106 IF (l_debug = 'Y') THEN
2107 okc_debug.log('8000: Exiting update_row:OTHERS Exception', 2);
2108 okc_debug.Reset_Indentation;
2109 END IF;
2110
2111 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2112 (
2113 l_api_name,
2114 G_PKG_NAME,
2115 'OTHERS',
2116 x_msg_count,
2117 x_msg_data,
2118 '_PVT'
2119 );
2120
2121 END update_row;
2122 -------------------------------------
2123 -- update_row for:OKC_K_ACCESSES_V --
2124 -------------------------------------
2125 PROCEDURE update_row(
2126 p_api_version IN NUMBER,
2127 p_init_msg_list IN VARCHAR2 ,
2128 x_return_status OUT NOCOPY VARCHAR2,
2129 x_msg_count OUT NOCOPY NUMBER,
2130 x_msg_data OUT NOCOPY VARCHAR2,
2131 p_cacv_rec IN cacv_rec_type,
2132 x_cacv_rec OUT NOCOPY cacv_rec_type) IS
2133
2134 l_api_version CONSTANT NUMBER := 1;
2135 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2136 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2137 l_cacv_rec cacv_rec_type := p_cacv_rec;
2138 l_def_cacv_rec cacv_rec_type;
2139 l_cac_rec cac_rec_type;
2140 lx_cac_rec cac_rec_type;
2141 -------------------------------
2142 -- FUNCTION fill_who_columns --
2143 -------------------------------
2144 FUNCTION fill_who_columns (
2145 p_cacv_rec IN cacv_rec_type
2146 ) RETURN cacv_rec_type IS
2147 l_cacv_rec cacv_rec_type := p_cacv_rec;
2148 BEGIN
2149
2150 l_cacv_rec.LAST_UPDATE_DATE := SYSDATE;
2151 l_cacv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2152 l_cacv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2153 RETURN(l_cacv_rec);
2154
2155 END fill_who_columns;
2156 ----------------------------------
2157 -- FUNCTION populate_new_record --
2158 ----------------------------------
2159 FUNCTION populate_new_record (
2160 p_cacv_rec IN cacv_rec_type,
2161 x_cacv_rec OUT NOCOPY cacv_rec_type
2162 ) RETURN VARCHAR2 IS
2163 l_cacv_rec cacv_rec_type;
2164 l_row_notfound BOOLEAN := TRUE;
2165 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2166 BEGIN
2167
2168 IF (l_debug = 'Y') THEN
2169 okc_debug.Set_Indentation('OKC_CAC_PVT');
2170 okc_debug.log('8200: Entered populate_new_record', 2);
2171 END IF;
2172
2173 x_cacv_rec := p_cacv_rec;
2174 -- Get current database values
2175 l_cacv_rec := get_rec(p_cacv_rec, l_row_notfound);
2176 IF (l_row_notfound) THEN
2177 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2178 END IF;
2179 IF (x_cacv_rec.id = OKC_API.G_MISS_NUM)
2180 THEN
2181 x_cacv_rec.id := l_cacv_rec.id;
2182 END IF;
2183 IF (x_cacv_rec.object_version_number = OKC_API.G_MISS_NUM)
2184 THEN
2185 x_cacv_rec.object_version_number := l_cacv_rec.object_version_number;
2186 END IF;
2187 IF (x_cacv_rec.group_id = OKC_API.G_MISS_NUM)
2188 THEN
2189 x_cacv_rec.group_id := l_cacv_rec.group_id;
2190 END IF;
2191 IF (x_cacv_rec.chr_id = OKC_API.G_MISS_NUM)
2192 THEN
2193 x_cacv_rec.chr_id := l_cacv_rec.chr_id;
2194 END IF;
2195 IF (x_cacv_rec.resource_id = OKC_API.G_MISS_NUM)
2196 THEN
2197 x_cacv_rec.resource_id := l_cacv_rec.resource_id;
2198 END IF;
2199 IF (x_cacv_rec.access_level = OKC_API.G_MISS_CHAR)
2200 THEN
2201 x_cacv_rec.access_level := l_cacv_rec.access_level;
2202 END IF;
2203 IF (x_cacv_rec.created_by = OKC_API.G_MISS_NUM)
2204 THEN
2205 x_cacv_rec.created_by := l_cacv_rec.created_by;
2206 END IF;
2207 IF (x_cacv_rec.creation_date = OKC_API.G_MISS_DATE)
2208 THEN
2209 x_cacv_rec.creation_date := l_cacv_rec.creation_date;
2210 END IF;
2211 IF (x_cacv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2212 THEN
2213 x_cacv_rec.last_updated_by := l_cacv_rec.last_updated_by;
2214 END IF;
2215 IF (x_cacv_rec.last_update_date = OKC_API.G_MISS_DATE)
2216 THEN
2217 x_cacv_rec.last_update_date := l_cacv_rec.last_update_date;
2218 END IF;
2219 IF (x_cacv_rec.last_update_login = OKC_API.G_MISS_NUM)
2220 THEN
2221 x_cacv_rec.last_update_login := l_cacv_rec.last_update_login;
2222 END IF;
2223
2224 IF (l_debug = 'Y') THEN
2225 okc_debug.log('8300: Exiting populate_new_record ', 2);
2226 okc_debug.Reset_Indentation;
2227 END IF;
2228
2229 RETURN(l_return_status);
2230
2231 END populate_new_record;
2232 -----------------------------------------
2233 -- Set_Attributes for:OKC_K_ACCESSES_V --
2234 -----------------------------------------
2235 FUNCTION Set_Attributes (
2236 p_cacv_rec IN cacv_rec_type,
2237 x_cacv_rec OUT NOCOPY cacv_rec_type
2238 ) RETURN VARCHAR2 IS
2239 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2240 BEGIN
2241
2242 x_cacv_rec := p_cacv_rec;
2243 x_cacv_rec.OBJECT_VERSION_NUMBER := NVL(x_cacv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2244 /************************ HAND-CODED *********************************/
2245 x_cacv_rec.ACCESS_LEVEL := UPPER(x_cacv_rec.ACCESS_LEVEL);
2246 /*********************** END HAND-CODED ********************************/
2247 RETURN(l_return_status);
2248
2249 END Set_Attributes;
2250 BEGIN
2251
2252 IF (l_debug = 'Y') THEN
2253 okc_debug.Set_Indentation('OKC_CAC_PVT');
2254 okc_debug.log('8400: Entered update_row', 2);
2255 END IF;
2256
2257 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2258 G_PKG_NAME,
2259 p_init_msg_list,
2260 l_api_version,
2261 p_api_version,
2262 '_PVT',
2263 x_return_status);
2264 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2265 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2266 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2267 RAISE OKC_API.G_EXCEPTION_ERROR;
2268 END IF;
2269 --- Setting item attributes
2270 l_return_status := Set_Attributes(
2271 p_cacv_rec, -- IN
2272 l_cacv_rec); -- OUT
2273 --- If any errors happen abort API
2274 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2275 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2276 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2277 RAISE OKC_API.G_EXCEPTION_ERROR;
2278 END IF;
2279 l_return_status := populate_new_record(l_cacv_rec, l_def_cacv_rec);
2280 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2281 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2282 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2283 RAISE OKC_API.G_EXCEPTION_ERROR;
2284 END IF;
2285 l_def_cacv_rec := fill_who_columns(l_def_cacv_rec);
2286 --- Validate all non-missing attributes (Item Level Validation)
2287 l_return_status := Validate_Attributes(l_def_cacv_rec);
2288 --- If any errors happen abort API
2289 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2290 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2291 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2292 RAISE OKC_API.G_EXCEPTION_ERROR;
2293 END IF;
2294 l_return_status := Validate_Record(l_def_cacv_rec);
2295 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2296 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2297 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2298 RAISE OKC_API.G_EXCEPTION_ERROR;
2299 END IF;
2300
2301 --------------------------------------
2302 -- Move VIEW record to "Child" records
2303 --------------------------------------
2304 migrate(l_def_cacv_rec, l_cac_rec);
2305 --------------------------------------------
2306 -- Call the UPDATE_ROW for each child record
2307 --------------------------------------------
2308 update_row(
2309 p_init_msg_list,
2310 x_return_status,
2311 x_msg_count,
2312 x_msg_data,
2313 l_cac_rec,
2314 lx_cac_rec
2315 );
2316 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2317 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2318 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2319 RAISE OKC_API.G_EXCEPTION_ERROR;
2320 END IF;
2321 migrate(lx_cac_rec, l_def_cacv_rec);
2322 x_cacv_rec := l_def_cacv_rec;
2323 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2324
2325 IF (l_debug = 'Y') THEN
2326 okc_debug.log('8450: Leaving update_row', 2);
2327 okc_debug.Reset_Indentation;
2328 END IF;
2329
2330 EXCEPTION
2331 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2332
2333 IF (l_debug = 'Y') THEN
2334 okc_debug.log('8500: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2335 okc_debug.Reset_Indentation;
2336 END IF;
2337
2338 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2339 (
2340 l_api_name,
2341 G_PKG_NAME,
2342 'OKC_API.G_RET_STS_ERROR',
2343 x_msg_count,
2344 x_msg_data,
2345 '_PVT'
2346 );
2347 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2348
2349 IF (l_debug = 'Y') THEN
2350 okc_debug.log('8600: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2351 okc_debug.Reset_Indentation;
2352 END IF;
2353
2354 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2355 (
2356 l_api_name,
2357 G_PKG_NAME,
2358 'OKC_API.G_RET_STS_UNEXP_ERROR',
2359 x_msg_count,
2360 x_msg_data,
2361 '_PVT'
2362 );
2363 WHEN OTHERS THEN
2364
2365 IF (l_debug = 'Y') THEN
2366 okc_debug.log('8700: Exiting update_row:OTHERS Exception', 2);
2367 okc_debug.Reset_Indentation;
2368 END IF;
2369
2370 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2371 (
2372 l_api_name,
2373 G_PKG_NAME,
2374 'OTHERS',
2375 x_msg_count,
2376 x_msg_data,
2377 '_PVT'
2378 );
2379
2380 END update_row;
2381 ----------------------------------------
2382 -- PL/SQL TBL update_row for:CACV_TBL --
2383 ----------------------------------------
2384 PROCEDURE update_row(
2385 p_api_version IN NUMBER,
2386 p_init_msg_list IN VARCHAR2 ,
2387 x_return_status OUT NOCOPY VARCHAR2,
2388 x_msg_count OUT NOCOPY NUMBER,
2389 x_msg_data OUT NOCOPY VARCHAR2,
2390 p_cacv_tbl IN cacv_tbl_type,
2391 x_cacv_tbl OUT NOCOPY cacv_tbl_type) IS
2392
2393 l_api_version CONSTANT NUMBER := 1;
2394 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2395 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2396 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2397 i NUMBER := 0;
2398 BEGIN
2399
2400 IF (l_debug = 'Y') THEN
2401 okc_debug.Set_Indentation('OKC_CAC_PVT');
2402 okc_debug.log('8900: Entered update_row', 2);
2403 END IF;
2404
2405 OKC_API.init_msg_list(p_init_msg_list);
2406 -- Make sure PL/SQL table has records in it before passing
2407 IF (p_cacv_tbl.COUNT > 0) THEN
2408 i := p_cacv_tbl.FIRST;
2409 LOOP
2410 update_row (
2411 p_api_version => p_api_version,
2412 p_init_msg_list => OKC_API.G_FALSE,
2413 x_return_status => x_return_status,
2414 x_msg_count => x_msg_count,
2415 x_msg_data => x_msg_data,
2416 p_cacv_rec => p_cacv_tbl(i),
2417 x_cacv_rec => x_cacv_tbl(i));
2418
2419 -- store the highest degree of error
2420 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2421 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2422 l_overall_status := x_return_status;
2423 End If;
2424 End If;
2425
2426 EXIT WHEN (i = p_cacv_tbl.LAST);
2427 i := p_cacv_tbl.NEXT(i);
2428 END LOOP;
2429 -- return overall status
2430 x_return_status := l_overall_status;
2431 END IF;
2432
2433 IF (l_debug = 'Y') THEN
2434 okc_debug.log('8950: Leaving update_row', 2);
2435 okc_debug.Reset_Indentation;
2436 END IF;
2437
2438 EXCEPTION
2439 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2440
2441 IF (l_debug = 'Y') THEN
2442 okc_debug.log('9000: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2443 okc_debug.Reset_Indentation;
2444 END IF;
2445
2446 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2447 (
2448 l_api_name,
2449 G_PKG_NAME,
2450 'OKC_API.G_RET_STS_ERROR',
2451 x_msg_count,
2452 x_msg_data,
2453 '_PVT'
2454 );
2455 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2456
2457 IF (l_debug = 'Y') THEN
2458 okc_debug.log('9100: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2459 okc_debug.Reset_Indentation;
2460 END IF;
2461
2462 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2463 (
2464 l_api_name,
2465 G_PKG_NAME,
2466 'OKC_API.G_RET_STS_UNEXP_ERROR',
2467 x_msg_count,
2468 x_msg_data,
2469 '_PVT'
2470 );
2471 WHEN OTHERS THEN
2472
2473 IF (l_debug = 'Y') THEN
2474 okc_debug.log('9200: Exiting update_row:OTHERS Exception', 2);
2475 okc_debug.Reset_Indentation;
2476 END IF;
2477
2478 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2479 (
2480 l_api_name,
2481 G_PKG_NAME,
2482 'OTHERS',
2483 x_msg_count,
2484 x_msg_data,
2485 '_PVT'
2486 );
2487
2488 END update_row;
2489
2490 ---------------------------------------------------------------------------
2491 -- PROCEDURE delete_row
2492 ---------------------------------------------------------------------------
2493 -----------------------------------
2494 -- delete_row for:OKC_K_ACCESSES --
2495 -----------------------------------
2496 PROCEDURE delete_row(
2497 p_init_msg_list IN VARCHAR2 ,
2498 x_return_status OUT NOCOPY VARCHAR2,
2499 x_msg_count OUT NOCOPY NUMBER,
2500 x_msg_data OUT NOCOPY VARCHAR2,
2501 p_cac_rec IN cac_rec_type) IS
2502
2503 l_api_version CONSTANT NUMBER := 1;
2504 l_api_name CONSTANT VARCHAR2(30) := 'ACCESSES_delete_row';
2505 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2506 l_cac_rec cac_rec_type:= p_cac_rec;
2507 l_row_notfound BOOLEAN := TRUE;
2508 BEGIN
2509
2510 IF (l_debug = 'Y') THEN
2511 okc_debug.Set_Indentation('OKC_CAC_PVT');
2512 okc_debug.log('9400: Entered delete_row', 2);
2513 END IF;
2514
2515 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2516 p_init_msg_list,
2517 '_PVT',
2518 x_return_status);
2519 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2520 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2521 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2522 RAISE OKC_API.G_EXCEPTION_ERROR;
2523 END IF;
2524 DELETE FROM OKC_K_ACCESSES
2525 WHERE ID = l_cac_rec.id;
2526
2527 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2528
2529 IF (l_debug = 'Y') THEN
2530 okc_debug.log('9450: Leaving delete_row', 2);
2531 okc_debug.Reset_Indentation;
2532 END IF;
2533
2534 EXCEPTION
2535 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2536
2537 IF (l_debug = 'Y') THEN
2538 okc_debug.log('9500: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2539 okc_debug.Reset_Indentation;
2540 END IF;
2541
2542 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2543 (
2544 l_api_name,
2545 G_PKG_NAME,
2546 'OKC_API.G_RET_STS_ERROR',
2547 x_msg_count,
2548 x_msg_data,
2549 '_PVT'
2550 );
2551 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2552
2553 IF (l_debug = 'Y') THEN
2554 okc_debug.log('9600: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2555 okc_debug.Reset_Indentation;
2556 END IF;
2557
2558 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2559 (
2560 l_api_name,
2561 G_PKG_NAME,
2562 'OKC_API.G_RET_STS_UNEXP_ERROR',
2563 x_msg_count,
2564 x_msg_data,
2565 '_PVT'
2566 );
2567 WHEN OTHERS THEN
2568
2569 IF (l_debug = 'Y') THEN
2570 okc_debug.log('9700: Exiting delete_row:OTHERS Exception', 2);
2571 okc_debug.Reset_Indentation;
2572 END IF;
2573
2574 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2575 (
2576 l_api_name,
2577 G_PKG_NAME,
2578 'OTHERS',
2579 x_msg_count,
2580 x_msg_data,
2581 '_PVT'
2582 );
2583
2584 END delete_row;
2585 -------------------------------------
2586 -- delete_row for:OKC_K_ACCESSES_V --
2587 -------------------------------------
2588 PROCEDURE delete_row(
2589 p_api_version IN NUMBER,
2590 p_init_msg_list IN VARCHAR2 ,
2591 x_return_status OUT NOCOPY VARCHAR2,
2592 x_msg_count OUT NOCOPY NUMBER,
2593 x_msg_data OUT NOCOPY VARCHAR2,
2594 p_cacv_rec IN cacv_rec_type) IS
2595
2596 l_api_version CONSTANT NUMBER := 1;
2597 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2598 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2599 l_cacv_rec cacv_rec_type := p_cacv_rec;
2600 l_cac_rec cac_rec_type;
2601 BEGIN
2602
2603 IF (l_debug = 'Y') THEN
2604 okc_debug.Set_Indentation('OKC_CAC_PVT');
2605 okc_debug.log('9900: Entered delete_row', 2);
2606 END IF;
2607
2608 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2609 G_PKG_NAME,
2610 p_init_msg_list,
2611 l_api_version,
2612 p_api_version,
2613 '_PVT',
2614 x_return_status);
2615 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2616 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2617 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2618 RAISE OKC_API.G_EXCEPTION_ERROR;
2619 END IF;
2620 --------------------------------------
2621 -- Move VIEW record to "Child" records
2622 --------------------------------------
2623 migrate(l_cacv_rec, l_cac_rec);
2624 --------------------------------------------
2625 -- Call the DELETE_ROW for each child record
2626 --------------------------------------------
2627 delete_row(
2628 p_init_msg_list,
2629 x_return_status,
2630 x_msg_count,
2631 x_msg_data,
2632 l_cac_rec
2633 );
2634 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2635 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2636 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2637 RAISE OKC_API.G_EXCEPTION_ERROR;
2638 END IF;
2639 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2640
2641 IF (l_debug = 'Y') THEN
2642 okc_debug.log('9950: Leaving delete_row', 2);
2643 okc_debug.Reset_Indentation;
2644 END IF;
2645
2646 EXCEPTION
2647 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2648
2649 IF (l_debug = 'Y') THEN
2650 okc_debug.log('10000: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2651 okc_debug.Reset_Indentation;
2652 END IF;
2653
2654 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2655 (
2656 l_api_name,
2657 G_PKG_NAME,
2658 'OKC_API.G_RET_STS_ERROR',
2659 x_msg_count,
2660 x_msg_data,
2661 '_PVT'
2662 );
2663 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2664
2665 IF (l_debug = 'Y') THEN
2666 okc_debug.log('10100: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2667 okc_debug.Reset_Indentation;
2668 END IF;
2669
2670 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2671 (
2672 l_api_name,
2673 G_PKG_NAME,
2674 'OKC_API.G_RET_STS_UNEXP_ERROR',
2675 x_msg_count,
2676 x_msg_data,
2677 '_PVT'
2678 );
2679 WHEN OTHERS THEN
2680
2681 IF (l_debug = 'Y') THEN
2682 okc_debug.log('10200: Exiting delete_row:OTHERS Exception', 2);
2683 okc_debug.Reset_Indentation;
2684 END IF;
2685
2686 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2687 (
2688 l_api_name,
2689 G_PKG_NAME,
2690 'OTHERS',
2691 x_msg_count,
2692 x_msg_data,
2693 '_PVT'
2694 );
2695
2696 END delete_row;
2697 ----------------------------------------
2698 -- PL/SQL TBL delete_row for:CACV_TBL --
2699 ----------------------------------------
2700 PROCEDURE delete_row(
2701 p_api_version IN NUMBER,
2702 p_init_msg_list IN VARCHAR2 ,
2703 x_return_status OUT NOCOPY VARCHAR2,
2704 x_msg_count OUT NOCOPY NUMBER,
2705 x_msg_data OUT NOCOPY VARCHAR2,
2706 p_cacv_tbl IN cacv_tbl_type) IS
2707
2708 l_api_version CONSTANT NUMBER := 1;
2709 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2710 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2711 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2712 i NUMBER := 0;
2713 BEGIN
2714
2715 IF (l_debug = 'Y') THEN
2716 okc_debug.Set_Indentation('OKC_CAC_PVT');
2717 okc_debug.log('10400: Entered delete_row', 2);
2718 END IF;
2719
2720 OKC_API.init_msg_list(p_init_msg_list);
2721 -- Make sure PL/SQL table has records in it before passing
2722 IF (p_cacv_tbl.COUNT > 0) THEN
2723 i := p_cacv_tbl.FIRST;
2724 LOOP
2725 delete_row (
2726 p_api_version => p_api_version,
2727 p_init_msg_list => OKC_API.G_FALSE,
2728 x_return_status => x_return_status,
2729 x_msg_count => x_msg_count,
2730 x_msg_data => x_msg_data,
2731 p_cacv_rec => p_cacv_tbl(i));
2732
2733 -- store the highest degree of error
2734 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2735 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2736 l_overall_status := x_return_status;
2737 End If;
2738 End If;
2739
2740 EXIT WHEN (i = p_cacv_tbl.LAST);
2741 i := p_cacv_tbl.NEXT(i);
2742 END LOOP;
2743 -- return overall status
2744 x_return_status := l_overall_status;
2745 END IF;
2746
2747 IF (l_debug = 'Y') THEN
2748 okc_debug.log('10450: Leaving delete_row', 2);
2749 okc_debug.Reset_Indentation;
2750 END IF;
2751
2752 EXCEPTION
2753 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2754
2755 IF (l_debug = 'Y') THEN
2756 okc_debug.log('10500: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2757 okc_debug.Reset_Indentation;
2758 END IF;
2759
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
2771 IF (l_debug = 'Y') THEN
2772 okc_debug.log('10600: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2773 okc_debug.Reset_Indentation;
2774 END IF;
2775
2776 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2777 (
2778 l_api_name,
2779 G_PKG_NAME,
2780 'OKC_API.G_RET_STS_UNEXP_ERROR',
2781 x_msg_count,
2782 x_msg_data,
2783 '_PVT'
2784 );
2785 WHEN OTHERS THEN
2786
2787 IF (l_debug = 'Y') THEN
2788 okc_debug.log('10700: Exiting delete_row:OTHERS Exception', 2);
2789 okc_debug.Reset_Indentation;
2790 END IF;
2791
2792 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2793 (
2794 l_api_name,
2795 G_PKG_NAME,
2796 'OTHERS',
2797 x_msg_count,
2798 x_msg_data,
2799 '_PVT'
2800 );
2801
2802 END delete_row;
2803
2804 --This function is called from versioning API OKC_VERSION_PVT
2805 --Old Location: OKCRVERB.pls
2806 --New Location: Base Table API
2807
2808 FUNCTION create_version(
2809 p_chr_id IN NUMBER,
2810 p_major_version IN NUMBER
2811 ) RETURN VARCHAR2 IS
2812
2813 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2814
2815 BEGIN
2816
2817 IF (l_debug = 'Y') THEN
2818 okc_debug.Set_Indentation('OKC_CAC_PVT');
2819 okc_debug.log('10900: Entered create_version', 2);
2820 END IF;
2821
2822 INSERT INTO okc_k_accesses_h
2823 (
2824 major_version,
2825 id,
2826 chr_id,
2827 group_id,
2828 resource_id,
2829 access_level,
2830 object_version_number,
2831 created_by,
2832 creation_date,
2833 last_updated_by,
2834 last_update_date,
2835 last_update_login
2836 )
2837 SELECT
2838 p_major_version,
2839 id,
2840 chr_id,
2841 group_id,
2842 resource_id,
2843 access_level,
2844 object_version_number,
2845 created_by,
2846 creation_date,
2847 last_updated_by,
2848 last_update_date,
2849 last_update_login
2850 FROM okc_k_accesses
2851 WHERE chr_id = p_chr_id;
2852
2853 IF (l_debug = 'Y') THEN
2854 okc_debug.log('10000: Leaving create_version', 2);
2855 okc_debug.Reset_Indentation;
2856 END IF;
2857
2858 RETURN l_return_status;
2859
2860 EXCEPTION
2861 -- other appropriate handlers
2862 WHEN OTHERS THEN
2863
2864 IF (l_debug = 'Y') THEN
2865 okc_debug.log('11000: Exiting create_version:OTHERS Exception', 2);
2866 okc_debug.Reset_Indentation;
2867 END IF;
2868
2869 -- store SQL error message on message stack
2870 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2871 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2872 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2873 p_token1_value => sqlcode,
2874 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2875 p_token2_value => sqlerrm);
2876
2877 -- notify UNEXPECTED error
2878 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2879 return l_return_status;
2880
2881 END create_version;
2882
2883 --This Function is called from Versioning API OKC_VERSION_PVT
2884 --Old Location:OKCRVERB.pls
2885 --New Location:Base Table API
2886
2887 FUNCTION restore_version(
2888 p_chr_id IN NUMBER,
2889 p_major_version IN NUMBER
2890 ) RETURN VARCHAR2 IS
2891
2892 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2893
2894 BEGIN
2895
2896 IF (l_debug = 'Y') THEN
2897 okc_debug.Set_Indentation('OKC_CAC_PVT');
2898 okc_debug.log('11300: Entered restore_version', 2);
2899 END IF;
2900
2901 INSERT INTO okc_k_accesses
2902 (
2903 id,
2904 chr_id,
2905 group_id,
2906 resource_id,
2907 access_level,
2908 object_version_number,
2909 created_by,
2910 creation_date,
2911 last_updated_by,
2912 last_update_date,
2913 last_update_login
2914 )
2915 SELECT
2916 id,
2917 chr_id,
2918 group_id,
2919 resource_id,
2920 access_level,
2921 object_version_number,
2922 created_by,
2923 creation_date,
2924 last_updated_by,
2925 last_update_date,
2926 last_update_login
2927 FROM okc_k_accesses_h
2928 WHERE chr_id = p_chr_id
2929 AND major_version = p_major_version;
2930
2931 IF (l_debug = 'Y') THEN
2932 okc_debug.log('11400: Leaving restore_version', 2);
2933 okc_debug.Reset_Indentation;
2934 END IF;
2935
2936 RETURN l_return_status;
2937
2938 EXCEPTION
2939 -- other appropriate handlers
2940 WHEN OTHERS THEN
2941
2942 IF (l_debug = 'Y') THEN
2943 okc_debug.log('11500: Exiting restore_version:OTHERS Exception', 2);
2944 okc_debug.Reset_Indentation;
2945 END IF;
2946
2947 -- store SQL error message on message stack
2948 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2949 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2950 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2951 p_token1_value => sqlcode,
2952 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2953 p_token2_value => sqlerrm);
2954
2955 -- notify UNEXPECTED error
2956 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2957 return l_return_status;
2958
2959 END restore_version;
2960 --
2961
2962 END OKC_CAC_PVT;