[Home] [Help]
PACKAGE BODY: APPS.OKC_RRD_PVT
Source
1 PACKAGE BODY OKC_RRD_PVT AS
2 /* $Header: OKCSRRDB.pls 120.0 2005/05/25 18:42:01 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ---------------------------------------------------------------------------
6 -- FUNCTION get_seq_id
7 ---------------------------------------------------------------------------
8 FUNCTION get_seq_id RETURN NUMBER IS
9 l_id Number;
10 -- for customer's data (for non-seeded date id should be 50000 or above)
11 cursor nonseed_c is
12 select
13 OKC_RG_ROLE_DEFS_S1.nextval
14 from
15 dual;
16 -- for datamerge's data (for seeded date id should be greater than or equal to 11000 and less than 50000)
17 cursor seed_c is
18 select
19 nvl(max(id), 11000) + 1
20 from
21 OKC_RG_ROLE_DEFS_V
22 where
23 id >= 11000 AND id < 50000;
24 BEGIN
25 if fnd_global.user_id = 1 then
26 open seed_c;
27 fetch seed_c into l_id;
28 close seed_c;
29 else
30 open nonseed_c;
31 fetch nonseed_c into l_id;
32 close nonseed_c;
33 end if;
34 /*
35 SELECT OKC_RG_ROLE_DEFS_S1.nextval
36 INTO l_id
37 FROM DUAL;
38 */
39 RETURN(l_id);
40 -- RETURN(okc_p_util.raw_to_number(sys_guid()));
41 END get_seq_id;
42
43 ---------------------------------------------------------------------------
44 -- PROCEDURE qc
45 ---------------------------------------------------------------------------
46 PROCEDURE qc IS
47 BEGIN
48 null;
49 END qc;
50
51 ---------------------------------------------------------------------------
52 -- PROCEDURE change_version
53 ---------------------------------------------------------------------------
54 PROCEDURE change_version IS
55 BEGIN
56 null;
57 END change_version;
58
59 ---------------------------------------------------------------------------
60 -- PROCEDURE api_copy
61 ---------------------------------------------------------------------------
62 PROCEDURE api_copy IS
63 BEGIN
64 null;
65 END api_copy;
66
67 ---------------------------------------------------------------------------
68 -- FUNCTION get_rec for: OKC_RG_ROLE_DEFS
69 ---------------------------------------------------------------------------
70 FUNCTION get_rec (
71 p_rrd_rec IN rrd_rec_type,
72 x_no_data_found OUT NOCOPY BOOLEAN
73 ) RETURN rrd_rec_type IS
74 CURSOR rrd_pk_csr (p_id IN NUMBER) IS
75 SELECT
76 ID,
77 SRD_ID,
78 SRE_ID,
79 OBJECT_VERSION_NUMBER,
80 CREATED_BY,
81 CREATION_DATE,
82 LAST_UPDATED_BY,
83 LAST_UPDATE_DATE,
84 SUBJECT_OBJECT_FLAG,
85 OPTIONAL_YN,
86 LAST_UPDATE_LOGIN,
87 ATTRIBUTE_CATEGORY,
88 ATTRIBUTE1,
89 ATTRIBUTE2,
90 ATTRIBUTE3,
91 ATTRIBUTE4,
92 ATTRIBUTE5,
93 ATTRIBUTE6,
94 ATTRIBUTE7,
95 ATTRIBUTE8,
96 ATTRIBUTE9,
97 ATTRIBUTE10,
98 ATTRIBUTE11,
99 ATTRIBUTE12,
100 ATTRIBUTE13,
101 ATTRIBUTE14,
102 ATTRIBUTE15,
103 ACCESS_LEVEL
104 FROM Okc_Rg_Role_Defs
105 WHERE okc_rg_role_defs.id = p_id;
106 l_rrd_pk rrd_pk_csr%ROWTYPE;
107 l_rrd_rec rrd_rec_type;
108 BEGIN
109 x_no_data_found := TRUE;
110 -- Get current database values
111 OPEN rrd_pk_csr (p_rrd_rec.id);
112 FETCH rrd_pk_csr INTO
113 l_rrd_rec.ID,
114 l_rrd_rec.SRD_ID,
115 l_rrd_rec.SRE_ID,
116 l_rrd_rec.OBJECT_VERSION_NUMBER,
117 l_rrd_rec.CREATED_BY,
118 l_rrd_rec.CREATION_DATE,
119 l_rrd_rec.LAST_UPDATED_BY,
120 l_rrd_rec.LAST_UPDATE_DATE,
121 l_rrd_rec.SUBJECT_OBJECT_FLAG,
122 l_rrd_rec.OPTIONAL_YN,
123 l_rrd_rec.LAST_UPDATE_LOGIN,
124 l_rrd_rec.ATTRIBUTE_CATEGORY,
125 l_rrd_rec.ATTRIBUTE1,
126 l_rrd_rec.ATTRIBUTE2,
127 l_rrd_rec.ATTRIBUTE3,
128 l_rrd_rec.ATTRIBUTE4,
129 l_rrd_rec.ATTRIBUTE5,
130 l_rrd_rec.ATTRIBUTE6,
131 l_rrd_rec.ATTRIBUTE7,
132 l_rrd_rec.ATTRIBUTE8,
133 l_rrd_rec.ATTRIBUTE9,
134 l_rrd_rec.ATTRIBUTE10,
135 l_rrd_rec.ATTRIBUTE11,
136 l_rrd_rec.ATTRIBUTE12,
137 l_rrd_rec.ATTRIBUTE13,
138 l_rrd_rec.ATTRIBUTE14,
139 l_rrd_rec.ATTRIBUTE15,
140 l_rrd_rec.ACCESS_LEVEL;
141 x_no_data_found := rrd_pk_csr%NOTFOUND;
142 CLOSE rrd_pk_csr;
143 RETURN(l_rrd_rec);
144 END get_rec;
145
146 FUNCTION get_rec (
147 p_rrd_rec IN rrd_rec_type
148 ) RETURN rrd_rec_type IS
149 l_row_notfound BOOLEAN := TRUE;
150 BEGIN
151 RETURN(get_rec(p_rrd_rec, l_row_notfound));
152 END get_rec;
153 ---------------------------------------------------------------------------
154 -- FUNCTION get_rec for: OKC_RG_ROLE_DEFS_V
155 ---------------------------------------------------------------------------
156 FUNCTION get_rec (
157 p_rrdv_rec IN rrdv_rec_type,
158 x_no_data_found OUT NOCOPY BOOLEAN
159 ) RETURN rrdv_rec_type IS
160 CURSOR okc_rrdv_pk_csr (p_id IN NUMBER) IS
161 SELECT
162 ID,
163 OBJECT_VERSION_NUMBER,
164 SRD_ID,
165 SRE_ID,
166 OPTIONAL_YN,
167 SUBJECT_OBJECT_FLAG,
168 ATTRIBUTE_CATEGORY,
169 ATTRIBUTE1,
170 ATTRIBUTE2,
171 ATTRIBUTE3,
172 ATTRIBUTE4,
173 ATTRIBUTE5,
174 ATTRIBUTE6,
175 ATTRIBUTE7,
176 ATTRIBUTE8,
177 ATTRIBUTE9,
178 ATTRIBUTE10,
179 ATTRIBUTE11,
180 ATTRIBUTE12,
181 ATTRIBUTE13,
182 ATTRIBUTE14,
183 ATTRIBUTE15,
184 CREATED_BY,
185 CREATION_DATE,
186 LAST_UPDATED_BY,
187 LAST_UPDATE_DATE,
188 LAST_UPDATE_LOGIN,
189 ACCESS_LEVEL
190 FROM Okc_Rg_Role_Defs_V
191 WHERE okc_rg_role_defs_v.id = p_id;
192 l_okc_rrdv_pk okc_rrdv_pk_csr%ROWTYPE;
193 l_rrdv_rec rrdv_rec_type;
194 BEGIN
195 x_no_data_found := TRUE;
196 -- Get current database values
197 OPEN okc_rrdv_pk_csr (p_rrdv_rec.id);
198 FETCH okc_rrdv_pk_csr INTO
199 l_rrdv_rec.ID,
200 l_rrdv_rec.OBJECT_VERSION_NUMBER,
201 l_rrdv_rec.SRD_ID,
202 l_rrdv_rec.SRE_ID,
203 l_rrdv_rec.OPTIONAL_YN,
204 l_rrdv_rec.SUBJECT_OBJECT_FLAG,
205 l_rrdv_rec.ATTRIBUTE_CATEGORY,
206 l_rrdv_rec.ATTRIBUTE1,
207 l_rrdv_rec.ATTRIBUTE2,
208 l_rrdv_rec.ATTRIBUTE3,
209 l_rrdv_rec.ATTRIBUTE4,
210 l_rrdv_rec.ATTRIBUTE5,
211 l_rrdv_rec.ATTRIBUTE6,
212 l_rrdv_rec.ATTRIBUTE7,
213 l_rrdv_rec.ATTRIBUTE8,
214 l_rrdv_rec.ATTRIBUTE9,
215 l_rrdv_rec.ATTRIBUTE10,
216 l_rrdv_rec.ATTRIBUTE11,
217 l_rrdv_rec.ATTRIBUTE12,
218 l_rrdv_rec.ATTRIBUTE13,
219 l_rrdv_rec.ATTRIBUTE14,
220 l_rrdv_rec.ATTRIBUTE15,
221 l_rrdv_rec.CREATED_BY,
222 l_rrdv_rec.CREATION_DATE,
223 l_rrdv_rec.LAST_UPDATED_BY,
224 l_rrdv_rec.LAST_UPDATE_DATE,
225 l_rrdv_rec.LAST_UPDATE_LOGIN,
226 l_rrdv_rec.ACCESS_LEVEL;
227 x_no_data_found := okc_rrdv_pk_csr%NOTFOUND;
228 CLOSE okc_rrdv_pk_csr;
229 RETURN(l_rrdv_rec);
230 END get_rec;
231
232 FUNCTION get_rec (
233 p_rrdv_rec IN rrdv_rec_type
234 ) RETURN rrdv_rec_type IS
235 l_row_notfound BOOLEAN := TRUE;
236 BEGIN
237 RETURN(get_rec(p_rrdv_rec, l_row_notfound));
238 END get_rec;
239
240 --------------------------------------------------------
241 -- FUNCTION null_out_defaults for: OKC_RG_ROLE_DEFS_V --
242 --------------------------------------------------------
243 FUNCTION null_out_defaults (
244 p_rrdv_rec IN rrdv_rec_type
245 ) RETURN rrdv_rec_type IS
246 l_rrdv_rec rrdv_rec_type := p_rrdv_rec;
247 BEGIN
248 IF (l_rrdv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
249 l_rrdv_rec.object_version_number := NULL;
250 END IF;
251 IF (l_rrdv_rec.srd_id = OKC_API.G_MISS_NUM) THEN
252 l_rrdv_rec.srd_id := NULL;
253 END IF;
254 IF (l_rrdv_rec.sre_id = OKC_API.G_MISS_NUM) THEN
255 l_rrdv_rec.sre_id := NULL;
256 END IF;
257 IF (l_rrdv_rec.optional_yn = OKC_API.G_MISS_CHAR) THEN
258 l_rrdv_rec.optional_yn := NULL;
259 END IF;
260 IF (l_rrdv_rec.subject_object_flag = OKC_API.G_MISS_CHAR) THEN
261 l_rrdv_rec.subject_object_flag := NULL;
262 END IF;
263 IF (l_rrdv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
264 l_rrdv_rec.attribute_category := NULL;
265 END IF;
266 IF (l_rrdv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
267 l_rrdv_rec.attribute1 := NULL;
268 END IF;
269 IF (l_rrdv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
270 l_rrdv_rec.attribute2 := NULL;
271 END IF;
272 IF (l_rrdv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
273 l_rrdv_rec.attribute3 := NULL;
274 END IF;
275 IF (l_rrdv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
276 l_rrdv_rec.attribute4 := NULL;
277 END IF;
278 IF (l_rrdv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
279 l_rrdv_rec.attribute5 := NULL;
280 END IF;
281 IF (l_rrdv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
282 l_rrdv_rec.attribute6 := NULL;
283 END IF;
284 IF (l_rrdv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
285 l_rrdv_rec.attribute7 := NULL;
286 END IF;
287 IF (l_rrdv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
288 l_rrdv_rec.attribute8 := NULL;
289 END IF;
290 IF (l_rrdv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
291 l_rrdv_rec.attribute9 := NULL;
292 END IF;
293 IF (l_rrdv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
294 l_rrdv_rec.attribute10 := NULL;
295 END IF;
296 IF (l_rrdv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
297 l_rrdv_rec.attribute11 := NULL;
298 END IF;
299 IF (l_rrdv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
300 l_rrdv_rec.attribute12 := NULL;
301 END IF;
302 IF (l_rrdv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
303 l_rrdv_rec.attribute13 := NULL;
304 END IF;
305 IF (l_rrdv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
306 l_rrdv_rec.attribute14 := NULL;
307 END IF;
308 IF (l_rrdv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
309 l_rrdv_rec.attribute15 := NULL;
310 END IF;
311 IF (l_rrdv_rec.created_by = OKC_API.G_MISS_NUM) THEN
312 l_rrdv_rec.created_by := NULL;
313 END IF;
314 IF (l_rrdv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
315 l_rrdv_rec.creation_date := NULL;
316 END IF;
317 IF (l_rrdv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
318 l_rrdv_rec.last_updated_by := NULL;
319 END IF;
320 IF (l_rrdv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
321 l_rrdv_rec.last_update_date := NULL;
322 END IF;
323 IF (l_rrdv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
324 l_rrdv_rec.last_update_login := NULL;
325 END IF;
326 IF (l_rrdv_rec.access_level = OKC_API.G_MISS_CHAR) THEN
327 l_rrdv_rec.access_level := NULL;
328 END IF;
329 RETURN(l_rrdv_rec);
330 END null_out_defaults;
331 ---------------------------------------------------------------------------------------
332 --Attribute Level Validattion Procedures Starts(Modification on TAPI generated Code.)--
333 ---------------------------------------------------------------------------------------
334 ---------------------------------------------------------------------------
335 -- PROCEDURE Validate_id
336 ---------------------------------------------------------------------------
337 PROCEDURE validate_id(
338 p_rrdv_rec IN rrdv_rec_type,
339 x_return_status OUT NOCOPY VARCHAR2) IS
340 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
341 BEGIN
342 IF p_rrdv_rec.id = OKC_API.G_MISS_NUM OR
343 p_rrdv_rec.id IS NULL
344 THEN
345 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
346 l_return_status := OKC_API.G_RET_STS_ERROR;
347 END IF;
348 x_return_status := l_return_status;
349 EXCEPTION
350 WHEN OTHERS THEN
351 -- store SQL error message on message stack for caller
352 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
353 -- notify caller of an UNEXPECTED error
354 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
355 END validate_id;
356
357 ---------------------------------------------------------------------------
358 -- PROCEDURE Validate_object_version_number
359 ---------------------------------------------------------------------------
360 PROCEDURE validate_object_version_number(
361 p_rrdv_rec IN rrdv_rec_type,
362 x_return_status OUT NOCOPY VARCHAR2) IS
363 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
364 BEGIN
365 IF p_rrdv_rec.object_version_number = OKC_API.G_MISS_NUM OR
366 p_rrdv_rec.object_version_number IS NULL
367 THEN
368 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
369 l_return_status := OKC_API.G_RET_STS_ERROR;
370 RAISE G_EXCEPTION_HALT_VALIDATION;
371 END IF;
372
373 x_return_status := l_return_status;
374 EXCEPTION
375 WHEN G_EXCEPTION_HALT_VALIDATION THEN
376 x_return_status := l_return_status;
377 WHEN OTHERS THEN
378 -- store SQL error message on message stack for caller
379 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
380 -- notify caller of an UNEXPECTED error
381 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
382 END validate_object_version_number;
383
384 ---------------------------------------------------------------------------
385 -- PROCEDURE Validate_srd_id
386 ---------------------------------------------------------------------------
387 PROCEDURE validate_srd_id(
388 p_rrdv_rec IN rrdv_rec_type,
389 x_return_status OUT NOCOPY VARCHAR2) IS
390 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
391 BEGIN
392 IF p_rrdv_rec.srd_id = OKC_API.G_MISS_NUM OR
393 p_rrdv_rec.srd_id IS NULL
394 THEN
395 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'srd_id');
396 l_return_status := OKC_API.G_RET_STS_ERROR;
397 END IF;
398 x_return_status := l_return_status;
399 EXCEPTION
400 WHEN OTHERS THEN
401 -- store SQL error message on message stack for caller
402 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
403 -- notify caller of an UNEXPECTED error
404 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
405 END validate_srd_id;
406
407 ---------------------------------------------------------------------------
408 -- PROCEDURE Validate_sre_id
409 ---------------------------------------------------------------------------
410 PROCEDURE validate_sre_id(
411 p_rrdv_rec IN rrdv_rec_type,
412 x_return_status OUT NOCOPY VARCHAR2) IS
413 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
414 BEGIN
415 IF p_rrdv_rec.sre_id = OKC_API.G_MISS_NUM OR
416 p_rrdv_rec.sre_id IS NULL
417 THEN
418 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'sre_id');
419 l_return_status := OKC_API.G_RET_STS_ERROR;
420 END IF;
421 x_return_status := l_return_status;
422 EXCEPTION
423 WHEN OTHERS THEN
424 -- store SQL error message on message stack for caller
425 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
426 -- notify caller of an UNEXPECTED error
427 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
428 END validate_sre_id;
429
430 ---------------------------------------------------------------------------
431 -- PROCEDURE Validate_optional_yn
432 ---------------------------------------------------------------------------
433 PROCEDURE validate_optional_yn(
434 p_rrdv_rec IN rrdv_rec_type,
435 x_return_status OUT NOCOPY VARCHAR2) IS
436 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
437 BEGIN
438 IF p_rrdv_rec.optional_yn = OKC_API.G_MISS_CHAR OR
439 p_rrdv_rec.optional_yn IS NULL
440 THEN
441 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'optional_yn');
442 x_return_status := OKC_API.G_RET_STS_ERROR;
443 raise G_EXCEPTION_HALT_VALIDATION;
444 END IF;
445 IF UPPER(p_rrdv_rec.optional_yn) IN ('Y', 'N') Then
446 IF p_rrdv_rec.optional_yn <> UPPER(p_rrdv_rec.optional_yn) Then
447 OKC_API.set_message(G_APP_NAME, G_UPPERCASE_REQUIRED, G_COL_NAME_TOKEN, 'optional_yn');
448 x_return_status := OKC_API.G_RET_STS_ERROR;
449 raise G_EXCEPTION_HALT_VALIDATION;
450 END IF;
451 ELSE
452 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'optional_yn');
453 x_return_status := OKC_API.G_RET_STS_ERROR;
454 raise G_EXCEPTION_HALT_VALIDATION;
455 END IF;
456 x_return_status := l_return_status;
457 EXCEPTION
458 WHEN G_EXCEPTION_HALT_VALIDATION THEN
459 NULL;
460 WHEN OTHERS THEN
461 -- store SQL error message on message stack for caller
462 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
463 -- notify caller of an UNEXPECTED error
464 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
465 END validate_optional_yn;
466
467 ---------------------------------------------------------------------------
468 -- PROCEDURE Validate_subject_object_flag
469 ---------------------------------------------------------------------------
470 PROCEDURE validate_subject_object_flag(
471 p_rrdv_rec IN rrdv_rec_type,
472 x_return_status OUT NOCOPY VARCHAR2) IS
473 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
474 BEGIN
475 IF p_rrdv_rec.subject_object_flag = OKC_API.G_MISS_CHAR OR
476 p_rrdv_rec.subject_object_flag IS NULL
477 THEN
478 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'subject_object_flag');
479 x_return_status := OKC_API.G_RET_STS_ERROR;
480 raise G_EXCEPTION_HALT_VALIDATION;
481 END IF;
482 IF UPPER(p_rrdv_rec.subject_object_flag) IN ('S', 'O') Then
483 IF p_rrdv_rec.subject_object_flag <> UPPER(p_rrdv_rec.subject_object_flag) Then
484 OKC_API.set_message(G_APP_NAME, G_UPPERCASE_REQUIRED, G_COL_NAME_TOKEN, 'subject_object_flag');
485 x_return_status := OKC_API.G_RET_STS_ERROR;
486 raise G_EXCEPTION_HALT_VALIDATION;
487 END IF;
488 ELSE
489 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'subject_object_flag');
490 x_return_status := OKC_API.G_RET_STS_ERROR;
491 raise G_EXCEPTION_HALT_VALIDATION;
492 END IF;
493 x_return_status := l_return_status;
494 EXCEPTION
495 WHEN G_EXCEPTION_HALT_VALIDATION THEN
496 NULL;
497 WHEN OTHERS THEN
498 -- store SQL error message on message stack for caller
499 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
500 -- notify caller of an UNEXPECTED error
501 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
502 END validate_subject_object_flag;
503
504 ---------------------------------------------------------------------------
505 -- PROCEDURE Validate_ACCESS_LEVEL
506 ---------------------------------------------------------------------------
507 PROCEDURE validate_access_level(
508 p_rrdv_rec IN rrdv_rec_type,
509 x_return_status OUT NOCOPY VARCHAR2) IS
510 Begin
511
512 -- initialize return status
513 x_return_status := OKC_API.G_RET_STS_SUCCESS;
514
515 -- check that data exists
516 If (p_rrdv_rec.access_level <> OKC_API.G_MISS_CHAR and
517 p_rrdv_rec.access_level IS NOT NULL)
518 Then
519 If p_rrdv_rec.access_level NOT IN ('S','E', 'U') Then
520 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
521 p_msg_name => g_invalid_value,
522 p_token1 => g_col_name_token,
523 p_token1_value => 'access_level');
524 -- notify caller of an error
525 x_return_status := OKC_API.G_RET_STS_ERROR;
526
527 -- halt validation
528 raise G_EXCEPTION_HALT_VALIDATION;
529 end if;
530 End If;
531 exception
532 when G_EXCEPTION_HALT_VALIDATION then
533 -- no processing necessary; validation can continue with next column
534 null;
535
536 when OTHERS then
537 -- store SQL error message on message stack
538 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
539 p_msg_name => g_unexpected_error,
540 p_token1 => g_sqlcode_token,
541 p_token1_value => sqlcode,
542 p_token2 => g_sqlerrm_token,
543 p_token2_value => sqlerrm);
544 -- notify caller of an error as UNEXPETED error
545 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
546 End validate_access_level;
547
548 ---------------------------------------------------------------------------
549 -- PROCEDURE Validate_Attributes
550 ---------------------------------------------------------------------------
551 ------------------------------------------------
552 -- Validate_Attributes for:OKC_RG_ROLE_DEFS_V --
553 ------------------------------------------------
554 FUNCTION Validate_Attributes (
555 p_rrdv_rec IN rrdv_rec_type
556 ) RETURN VARCHAR2 IS
557 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
558 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
559 BEGIN
560 ---------------------------------------------------------------------------------------
561 --Attribute Level Validation Procedure Calls Starts(Modification on TAPI generated Code.
562 ---------------------------------------------------------------------------------------
563
564 VALIDATE_id(p_rrdv_rec, l_return_status);
565 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
566 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
567 x_return_status := l_return_status;
568 RAISE G_EXCEPTION_HALT_VALIDATION;
569 ELSE
570 x_return_status := l_return_status; -- record that there was an error
571 END IF;
572 END IF;
573
574 VALIDATE_object_version_number(p_rrdv_rec, l_return_status);
575 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
576 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
577 x_return_status := l_return_status;
578 RAISE G_EXCEPTION_HALT_VALIDATION;
579 ELSE
580 x_return_status := l_return_status; -- record that there was an error
581 END IF;
582 END IF;
583
584 VALIDATE_srd_id(p_rrdv_rec, l_return_status);
585 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
586 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
587 x_return_status := l_return_status;
588 RAISE G_EXCEPTION_HALT_VALIDATION;
589 ELSE
590 x_return_status := l_return_status; -- record that there was an error
591 END IF;
592 END IF;
593
594 VALIDATE_sre_id(p_rrdv_rec, l_return_status);
595 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
596 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
597 x_return_status := l_return_status;
598 RAISE G_EXCEPTION_HALT_VALIDATION;
599 ELSE
600 x_return_status := l_return_status; -- record that there was an error
601 END IF;
602 END IF;
603
604 VALIDATE_optional_yn(p_rrdv_rec, l_return_status);
605 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
606 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
607 x_return_status := l_return_status;
608 RAISE G_EXCEPTION_HALT_VALIDATION;
609 ELSE
610 x_return_status := l_return_status; -- record that there was an error
611 END IF;
612 END IF;
613
614 VALIDATE_subject_object_flag(p_rrdv_rec, l_return_status);
615 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
616 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
617 x_return_status := l_return_status;
618 RAISE G_EXCEPTION_HALT_VALIDATION;
619 ELSE
620 x_return_status := l_return_status; -- record that there was an error
621 END IF;
622 END IF;
623
624 VALIDATE_access_level(p_rrdv_rec, l_return_status);
625 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
626 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
627 x_return_status := l_return_status;
628 RAISE G_EXCEPTION_HALT_VALIDATION;
629 ELSE
630 x_return_status := l_return_status; -- record that there was an error
631 END IF;
632 END IF;
633
634 return(x_return_status);
635 ---------------------------------------------------------------------------------------
636 --Attribute Level Validation Procedure Calls Ends(Modification on TAPI generated Code.)--
637 ---------------------------------------------------------------------------------------
638
639 END Validate_Attributes;
640
641 ---------------------------------------------------------------------------
642 -- PROCEDURE Validate_Record
643 ---------------------------------------------------------------------------
644 --------------------------------------------
645 -- Validate_Record for:OKC_RG_ROLE_DEFS_V --
646 --------------------------------------------
647 FUNCTION Validate_Record (
648 p_rrdv_rec IN rrdv_rec_type
649 ) RETURN VARCHAR2 IS
650 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
651 ------------------------------------
652 -- FUNCTION validate_foreign_keys --
653 ------------------------------------
654 FUNCTION validate_foreign_keys (
655 p_rrdv_rec IN rrdv_rec_type
656 ) RETURN VARCHAR2 IS
657 item_not_found_error EXCEPTION;
658 CURSOR okc_srev_pk_csr (p_id IN NUMBER) IS
659 SELECT 'x'
660 FROM Okc_Subclass_Roles_V
661 WHERE okc_subclass_roles_v.id = p_id;
662 CURSOR okc_srdv_pk_csr (p_id IN NUMBER) IS
663 SELECT 'x'
664 FROM Okc_Subclass_Rg_Defs_V
665 WHERE okc_subclass_rg_defs_v.id = p_id;
666 l_dummy VARCHAR2(1);
667 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
668 l_row_notfound BOOLEAN := TRUE;
669 BEGIN
670 IF (p_rrdv_rec.SRE_ID IS NOT NULL)
671 THEN
672 OPEN okc_srev_pk_csr(p_rrdv_rec.SRE_ID);
673 FETCH okc_srev_pk_csr INTO l_dummy;
674 l_row_notfound := okc_srev_pk_csr%NOTFOUND;
675 CLOSE okc_srev_pk_csr;
676 IF (l_row_notfound) THEN
677 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SRE_ID');
678 RAISE item_not_found_error;
679 END IF;
680 END IF;
681 IF (p_rrdv_rec.SRD_ID IS NOT NULL)
682 THEN
683 OPEN okc_srdv_pk_csr(p_rrdv_rec.SRD_ID);
684 FETCH okc_srdv_pk_csr INTO l_dummy;
685 l_row_notfound := okc_srdv_pk_csr%NOTFOUND;
686 CLOSE okc_srdv_pk_csr;
687 IF (l_row_notfound) THEN
688 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SRD_ID');
689 RAISE item_not_found_error;
690 END IF;
691 END IF;
692 RETURN (l_return_status);
693 EXCEPTION
694 WHEN item_not_found_error THEN
695 l_return_status := OKC_API.G_RET_STS_ERROR;
696 RETURN (l_return_status);
697 END validate_foreign_keys;
698 ----------------------------------------------------
699 FUNCTION validate_unique_keys (
700 p_rrdv_rec IN rrdv_rec_type
701 ) RETURN VARCHAR2 IS
702 unique_key_error EXCEPTION;
703 CURSOR c1 (p_id IN okc_rg_role_defs_v.id%TYPE,
704 p_srd_id IN okc_rg_role_defs_v.srd_id%TYPE,
705 p_sre_id IN okc_rg_role_defs_v.sre_id%TYPE) IS
706 SELECT 'x'
707 FROM Okc_Rg_Role_Defs_V
708 WHERE srd_id = p_srd_id
709 AND sre_id = p_sre_id
710 AND ((p_id IS NULL)
711 OR (p_id IS NOT NULL
712 AND id <> p_id));
713 l_dummy VARCHAR2(1);
714 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
715 l_row_found BOOLEAN := FALSE;
716 BEGIN
717 IF (p_rrdv_rec.SRD_ID IS NOT NULL AND
718 p_rrdv_rec.SRE_ID IS NOT NULL) THEN
719 OPEN c1(p_rrdv_rec.ID,
720 p_rrdv_rec.SRD_ID,
721 p_rrdv_rec.SRE_ID);
722 FETCH c1 INTO l_dummy;
723 l_row_found := c1%FOUND;
724 CLOSE c1;
725 IF (l_row_found) THEN
726 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'SRD_ID');
727 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'SRE_ID');
728 RAISE unique_key_error;
729 END IF;
730 END IF;
731 RETURN (l_return_status);
732 EXCEPTION
733 WHEN unique_key_error THEN
734 l_return_status := OKC_API.G_RET_STS_ERROR;
735 RETURN (l_return_status);
736 END validate_unique_keys;
737 BEGIN
738 l_return_status := validate_foreign_keys (p_rrdv_rec);
739 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
740 raise G_EXCEPTION_HALT_VALIDATION;
741 END IF;
742 l_return_status := validate_unique_keys (p_rrdv_rec);
743 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
744 raise G_EXCEPTION_HALT_VALIDATION;
745 END IF;
746 RETURN (l_return_status);
747 EXCEPTION
748 WHEN G_EXCEPTION_HALT_VALIDATION THEN
749 RETURN(l_return_status);
750 WHEN OTHERS THEN
751 OKC_API.set_message(p_app_name => g_app_name,
752 p_msg_name => g_unexpected_error,
753 p_token1 => g_sqlcode_token,
754 p_token1_value => sqlcode,
755 p_token2 => g_sqlerrm_token,
756 p_token2_value => sqlerrm);
757 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
758 RETURN(l_return_status);
759 END Validate_Record;
760
761 ---------------------------------------------------------------------------
762 -- PROCEDURE Migrate
763 ---------------------------------------------------------------------------
764 PROCEDURE migrate (
765 p_from IN rrdv_rec_type,
766 p_to OUT NOCOPY rrd_rec_type
767 ) IS
768 BEGIN
769 p_to.id := p_from.id;
770 p_to.srd_id := p_from.srd_id;
771 p_to.sre_id := p_from.sre_id;
772 p_to.object_version_number := p_from.object_version_number;
773 p_to.created_by := p_from.created_by;
774 p_to.creation_date := p_from.creation_date;
775 p_to.last_updated_by := p_from.last_updated_by;
776 p_to.last_update_date := p_from.last_update_date;
777 p_to.subject_object_flag := p_from.subject_object_flag;
778 p_to.optional_yn := p_from.optional_yn;
779 p_to.last_update_login := p_from.last_update_login;
780 p_to.attribute_category := p_from.attribute_category;
781 p_to.attribute1 := p_from.attribute1;
782 p_to.attribute2 := p_from.attribute2;
783 p_to.attribute3 := p_from.attribute3;
784 p_to.attribute4 := p_from.attribute4;
785 p_to.attribute5 := p_from.attribute5;
786 p_to.attribute6 := p_from.attribute6;
787 p_to.attribute7 := p_from.attribute7;
788 p_to.attribute8 := p_from.attribute8;
789 p_to.attribute9 := p_from.attribute9;
790 p_to.attribute10 := p_from.attribute10;
791 p_to.attribute11 := p_from.attribute11;
792 p_to.attribute12 := p_from.attribute12;
793 p_to.attribute13 := p_from.attribute13;
794 p_to.attribute14 := p_from.attribute14;
795 p_to.attribute15 := p_from.attribute15;
796 p_to.access_level := p_from.access_level;
797 END migrate;
798 PROCEDURE migrate (
799 p_from IN rrd_rec_type,
800 p_to IN OUT NOCOPY rrdv_rec_type
801 ) IS
802 BEGIN
803 p_to.id := p_from.id;
804 p_to.srd_id := p_from.srd_id;
805 p_to.sre_id := p_from.sre_id;
806 p_to.object_version_number := p_from.object_version_number;
807 p_to.created_by := p_from.created_by;
808 p_to.creation_date := p_from.creation_date;
809 p_to.last_updated_by := p_from.last_updated_by;
810 p_to.last_update_date := p_from.last_update_date;
811 p_to.subject_object_flag := p_from.subject_object_flag;
812 p_to.optional_yn := p_from.optional_yn;
813 p_to.last_update_login := p_from.last_update_login;
814 p_to.attribute_category := p_from.attribute_category;
815 p_to.attribute1 := p_from.attribute1;
816 p_to.attribute2 := p_from.attribute2;
817 p_to.attribute3 := p_from.attribute3;
818 p_to.attribute4 := p_from.attribute4;
819 p_to.attribute5 := p_from.attribute5;
820 p_to.attribute6 := p_from.attribute6;
821 p_to.attribute7 := p_from.attribute7;
822 p_to.attribute8 := p_from.attribute8;
823 p_to.attribute9 := p_from.attribute9;
824 p_to.attribute10 := p_from.attribute10;
825 p_to.attribute11 := p_from.attribute11;
826 p_to.attribute12 := p_from.attribute12;
827 p_to.attribute13 := p_from.attribute13;
828 p_to.attribute14 := p_from.attribute14;
829 p_to.attribute15 := p_from.attribute15;
830 p_to.access_level := p_from.access_level;
831 END migrate;
832
833 ---------------------------------------------------------------------------
834 -- PROCEDURE validate_row
835 ---------------------------------------------------------------------------
836 -----------------------------------------
837 -- validate_row for:OKC_RG_ROLE_DEFS_V --
838 -----------------------------------------
839 PROCEDURE validate_row(
840 p_api_version IN NUMBER,
841 p_init_msg_list IN VARCHAR2,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2,
845 p_rrdv_rec IN rrdv_rec_type) IS
846
847 l_api_version CONSTANT NUMBER := 1;
848 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
849 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
850 l_rrdv_rec rrdv_rec_type := p_rrdv_rec;
851 l_rrd_rec rrd_rec_type;
852 BEGIN
853 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
854 G_PKG_NAME,
855 p_init_msg_list,
856 l_api_version,
857 p_api_version,
858 '_PVT',
859 x_return_status);
860 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
861 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
862 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
863 RAISE OKC_API.G_EXCEPTION_ERROR;
864 END IF;
865 --- Validate all non-missing attributes (Item Level Validation)
866 l_return_status := Validate_Attributes(l_rrdv_rec);
867 --- If any errors happen abort API
868 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
869 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
870 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
871 RAISE OKC_API.G_EXCEPTION_ERROR;
872 END IF;
873 l_return_status := Validate_Record(l_rrdv_rec);
874 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
875 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
876 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
877 RAISE OKC_API.G_EXCEPTION_ERROR;
878 END IF;
879 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
880 EXCEPTION
881 WHEN OKC_API.G_EXCEPTION_ERROR THEN
882 x_return_status := OKC_API.HANDLE_EXCEPTIONS
883 (
884 l_api_name,
885 G_PKG_NAME,
886 'OKC_API.G_RET_STS_ERROR',
887 x_msg_count,
888 x_msg_data,
889 '_PVT'
890 );
891 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
892 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
893 (
894 l_api_name,
895 G_PKG_NAME,
896 'OKC_API.G_RET_STS_UNEXP_ERROR',
897 x_msg_count,
898 x_msg_data,
899 '_PVT'
900 );
901 WHEN OTHERS THEN
902 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
903 (
904 l_api_name,
905 G_PKG_NAME,
906 'OTHERS',
907 x_msg_count,
908 x_msg_data,
909 '_PVT'
910 );
911 END validate_row;
912 ------------------------------------------
913 -- PL/SQL TBL validate_row for:RRDV_TBL --
914 ------------------------------------------
915 PROCEDURE validate_row(
916 p_api_version IN NUMBER,
917 p_init_msg_list IN VARCHAR2,
918 x_return_status OUT NOCOPY VARCHAR2,
919 x_msg_count OUT NOCOPY NUMBER,
920 x_msg_data OUT NOCOPY VARCHAR2,
921 p_rrdv_tbl IN rrdv_tbl_type) IS
922
923 l_api_version CONSTANT NUMBER := 1;
924 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
925 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
926 i NUMBER := 0;
927 BEGIN
928 OKC_API.init_msg_list(p_init_msg_list);
929 -- Make sure PL/SQL table has records in it before passing
930 IF (p_rrdv_tbl.COUNT > 0) THEN
931 i := p_rrdv_tbl.FIRST;
932 LOOP
933 validate_row (
934 p_api_version => p_api_version,
935 p_init_msg_list => OKC_API.G_FALSE,
936 x_return_status => x_return_status,
937 x_msg_count => x_msg_count,
938 x_msg_data => x_msg_data,
939 p_rrdv_rec => p_rrdv_tbl(i));
940 EXIT WHEN (i = p_rrdv_tbl.LAST);
941 i := p_rrdv_tbl.NEXT(i);
942 END LOOP;
943 END IF;
944 EXCEPTION
945 WHEN OKC_API.G_EXCEPTION_ERROR THEN
946 x_return_status := OKC_API.HANDLE_EXCEPTIONS
947 (
948 l_api_name,
949 G_PKG_NAME,
950 'OKC_API.G_RET_STS_ERROR',
951 x_msg_count,
952 x_msg_data,
953 '_PVT'
954 );
955 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
956 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
957 (
958 l_api_name,
959 G_PKG_NAME,
960 'OKC_API.G_RET_STS_UNEXP_ERROR',
961 x_msg_count,
962 x_msg_data,
963 '_PVT'
964 );
965 WHEN OTHERS THEN
966 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
967 (
968 l_api_name,
969 G_PKG_NAME,
970 'OTHERS',
971 x_msg_count,
972 x_msg_data,
973 '_PVT'
974 );
975 END validate_row;
976
977 ---------------------------------------------------------------------------
978 -- PROCEDURE insert_row
979 ---------------------------------------------------------------------------
980 -------------------------------------
981 -- insert_row for:OKC_RG_ROLE_DEFS --
982 -------------------------------------
983 PROCEDURE insert_row(
984 p_init_msg_list IN VARCHAR2,
985 x_return_status OUT NOCOPY VARCHAR2,
986 x_msg_count OUT NOCOPY NUMBER,
987 x_msg_data OUT NOCOPY VARCHAR2,
988 p_rrd_rec IN rrd_rec_type,
989 x_rrd_rec OUT NOCOPY rrd_rec_type) IS
990
991 l_api_version CONSTANT NUMBER := 1;
992 l_api_name CONSTANT VARCHAR2(30) := 'DEFS_insert_row';
993 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
994 l_rrd_rec rrd_rec_type := p_rrd_rec;
995 l_def_rrd_rec rrd_rec_type;
996 -----------------------------------------
997 -- Set_Attributes for:OKC_RG_ROLE_DEFS --
998 -----------------------------------------
999 FUNCTION Set_Attributes (
1000 p_rrd_rec IN rrd_rec_type,
1001 x_rrd_rec OUT NOCOPY rrd_rec_type
1002 ) RETURN VARCHAR2 IS
1003 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1004 BEGIN
1005 x_rrd_rec := p_rrd_rec;
1006 RETURN(l_return_status);
1007 END Set_Attributes;
1008 BEGIN
1009 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1010 p_init_msg_list,
1011 '_PVT',
1012 x_return_status);
1013 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1014 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1015 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1016 RAISE OKC_API.G_EXCEPTION_ERROR;
1017 END IF;
1018 --- Setting item attributes
1019 l_return_status := Set_Attributes(
1020 p_rrd_rec, -- IN
1021 l_rrd_rec); -- OUT
1022 --- If any errors happen abort API
1023 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1024 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1025 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1026 RAISE OKC_API.G_EXCEPTION_ERROR;
1027 END IF;
1028 INSERT INTO OKC_RG_ROLE_DEFS(
1029 id,
1030 srd_id,
1031 sre_id,
1032 object_version_number,
1033 created_by,
1034 creation_date,
1035 last_updated_by,
1036 last_update_date,
1037 subject_object_flag,
1038 optional_yn,
1039 last_update_login,
1040 attribute_category,
1041 attribute1,
1042 attribute2,
1043 attribute3,
1044 attribute4,
1045 attribute5,
1046 attribute6,
1047 attribute7,
1048 attribute8,
1049 attribute9,
1050 attribute10,
1051 attribute11,
1052 attribute12,
1053 attribute13,
1054 attribute14,
1055 attribute15,
1056 access_level)
1057 VALUES (
1058 l_rrd_rec.id,
1059 l_rrd_rec.srd_id,
1060 l_rrd_rec.sre_id,
1061 l_rrd_rec.object_version_number,
1062 l_rrd_rec.created_by,
1063 l_rrd_rec.creation_date,
1064 l_rrd_rec.last_updated_by,
1065 l_rrd_rec.last_update_date,
1066 l_rrd_rec.subject_object_flag,
1067 l_rrd_rec.optional_yn,
1068 l_rrd_rec.last_update_login,
1069 l_rrd_rec.attribute_category,
1070 l_rrd_rec.attribute1,
1071 l_rrd_rec.attribute2,
1072 l_rrd_rec.attribute3,
1073 l_rrd_rec.attribute4,
1074 l_rrd_rec.attribute5,
1075 l_rrd_rec.attribute6,
1076 l_rrd_rec.attribute7,
1077 l_rrd_rec.attribute8,
1078 l_rrd_rec.attribute9,
1079 l_rrd_rec.attribute10,
1080 l_rrd_rec.attribute11,
1081 l_rrd_rec.attribute12,
1082 l_rrd_rec.attribute13,
1083 l_rrd_rec.attribute14,
1084 l_rrd_rec.attribute15,
1085 l_rrd_rec.access_level);
1086 -- Set OUT values
1087 x_rrd_rec := l_rrd_rec;
1088 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1089 EXCEPTION
1090 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1091 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1092 (
1093 l_api_name,
1094 G_PKG_NAME,
1095 'OKC_API.G_RET_STS_ERROR',
1096 x_msg_count,
1097 x_msg_data,
1098 '_PVT'
1099 );
1100 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1101 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1102 (
1103 l_api_name,
1104 G_PKG_NAME,
1105 'OKC_API.G_RET_STS_UNEXP_ERROR',
1106 x_msg_count,
1107 x_msg_data,
1108 '_PVT'
1109 );
1110 WHEN OTHERS THEN
1111 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1112 (
1113 l_api_name,
1114 G_PKG_NAME,
1115 'OTHERS',
1116 x_msg_count,
1117 x_msg_data,
1118 '_PVT'
1119 );
1120 END insert_row;
1121 ---------------------------------------
1122 -- insert_row for:OKC_RG_ROLE_DEFS_V --
1123 ---------------------------------------
1124 PROCEDURE insert_row(
1125 p_api_version IN NUMBER,
1126 p_init_msg_list IN VARCHAR2,
1127 x_return_status OUT NOCOPY VARCHAR2,
1128 x_msg_count OUT NOCOPY NUMBER,
1129 x_msg_data OUT NOCOPY VARCHAR2,
1130 p_rrdv_rec IN rrdv_rec_type,
1131 x_rrdv_rec OUT NOCOPY rrdv_rec_type) IS
1132
1133 l_api_version CONSTANT NUMBER := 1;
1134 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1135 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1136 l_rrdv_rec rrdv_rec_type;
1137 l_def_rrdv_rec rrdv_rec_type;
1138 l_rrd_rec rrd_rec_type;
1139 lx_rrd_rec rrd_rec_type;
1140 -------------------------------
1141 -- FUNCTION fill_who_columns --
1142 -------------------------------
1143 FUNCTION fill_who_columns (
1144 p_rrdv_rec IN rrdv_rec_type
1145 ) RETURN rrdv_rec_type IS
1146 l_rrdv_rec rrdv_rec_type := p_rrdv_rec;
1147 BEGIN
1148 l_rrdv_rec.CREATION_DATE := SYSDATE;
1149 l_rrdv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1150 l_rrdv_rec.LAST_UPDATE_DATE := l_rrdv_rec.CREATION_DATE;
1151 l_rrdv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1152 l_rrdv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1153 RETURN(l_rrdv_rec);
1154 END fill_who_columns;
1155 -------------------------------------------
1156 -- Set_Attributes for:OKC_RG_ROLE_DEFS_V --
1157 -------------------------------------------
1158 FUNCTION Set_Attributes (
1159 p_rrdv_rec IN rrdv_rec_type,
1160 x_rrdv_rec OUT NOCOPY rrdv_rec_type
1161 ) RETURN VARCHAR2 IS
1162 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1163 BEGIN
1164 x_rrdv_rec := p_rrdv_rec;
1165 x_rrdv_rec.OBJECT_VERSION_NUMBER := 1;
1166 RETURN(l_return_status);
1167 END Set_Attributes;
1168 BEGIN
1169 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1170 G_PKG_NAME,
1171 p_init_msg_list,
1172 l_api_version,
1173 p_api_version,
1174 '_PVT',
1175 x_return_status);
1176 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1177 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1178 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1179 RAISE OKC_API.G_EXCEPTION_ERROR;
1180 END IF;
1181 l_rrdv_rec := null_out_defaults(p_rrdv_rec);
1182 -- Set primary key value
1183 l_rrdv_rec.ID := get_seq_id;
1184 --- Setting item attributes
1185 l_return_status := Set_Attributes(
1186 l_rrdv_rec, -- IN
1187 l_def_rrdv_rec); -- OUT
1188 --- If any errors happen abort API
1189 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1190 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1191 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1192 RAISE OKC_API.G_EXCEPTION_ERROR;
1193 END IF;
1194 l_def_rrdv_rec := fill_who_columns(l_def_rrdv_rec);
1195 --- Validate all non-missing attributes (Item Level Validation)
1196 l_return_status := Validate_Attributes(l_def_rrdv_rec);
1197 --- If any errors happen abort API
1198 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1199 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1200 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1201 RAISE OKC_API.G_EXCEPTION_ERROR;
1202 END IF;
1203 l_return_status := Validate_Record(l_def_rrdv_rec);
1204 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1205 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1206 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1207 RAISE OKC_API.G_EXCEPTION_ERROR;
1208 END IF;
1209 --------------------------------------
1210 -- Move VIEW record to "Child" records
1211 --------------------------------------
1212 migrate(l_def_rrdv_rec, l_rrd_rec);
1213 --------------------------------------------
1214 -- Call the INSERT_ROW for each child record
1215 --------------------------------------------
1216 insert_row(
1217 p_init_msg_list,
1218 x_return_status,
1219 x_msg_count,
1220 x_msg_data,
1221 l_rrd_rec,
1222 lx_rrd_rec
1223 );
1224 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1225 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1226 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1227 RAISE OKC_API.G_EXCEPTION_ERROR;
1228 END IF;
1229 migrate(lx_rrd_rec, l_def_rrdv_rec);
1230 -- Set OUT values
1231 x_rrdv_rec := l_def_rrdv_rec;
1232 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1233 EXCEPTION
1234 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1235 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1236 (
1237 l_api_name,
1238 G_PKG_NAME,
1239 'OKC_API.G_RET_STS_ERROR',
1240 x_msg_count,
1241 x_msg_data,
1242 '_PVT'
1243 );
1244 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1245 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1246 (
1247 l_api_name,
1248 G_PKG_NAME,
1249 'OKC_API.G_RET_STS_UNEXP_ERROR',
1250 x_msg_count,
1251 x_msg_data,
1252 '_PVT'
1253 );
1254 WHEN OTHERS THEN
1255 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1256 (
1257 l_api_name,
1258 G_PKG_NAME,
1259 'OTHERS',
1260 x_msg_count,
1261 x_msg_data,
1262 '_PVT'
1263 );
1264 END insert_row;
1265 ----------------------------------------
1266 -- PL/SQL TBL insert_row for:RRDV_TBL --
1267 ----------------------------------------
1268 PROCEDURE insert_row(
1269 p_api_version IN NUMBER,
1270 p_init_msg_list IN VARCHAR2,
1271 x_return_status OUT NOCOPY VARCHAR2,
1272 x_msg_count OUT NOCOPY NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2,
1274 p_rrdv_tbl IN rrdv_tbl_type,
1275 x_rrdv_tbl OUT NOCOPY rrdv_tbl_type) IS
1276
1277 l_api_version CONSTANT NUMBER := 1;
1278 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1279 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1280 i NUMBER := 0;
1281 BEGIN
1282 OKC_API.init_msg_list(p_init_msg_list);
1283 -- Make sure PL/SQL table has records in it before passing
1284 IF (p_rrdv_tbl.COUNT > 0) THEN
1285 i := p_rrdv_tbl.FIRST;
1286 LOOP
1287 insert_row (
1288 p_api_version => p_api_version,
1289 p_init_msg_list => OKC_API.G_FALSE,
1290 x_return_status => x_return_status,
1291 x_msg_count => x_msg_count,
1292 x_msg_data => x_msg_data,
1293 p_rrdv_rec => p_rrdv_tbl(i),
1294 x_rrdv_rec => x_rrdv_tbl(i));
1295 EXIT WHEN (i = p_rrdv_tbl.LAST);
1296 i := p_rrdv_tbl.NEXT(i);
1297 END LOOP;
1298 END IF;
1299 EXCEPTION
1300 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1301 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1302 (
1303 l_api_name,
1304 G_PKG_NAME,
1305 'OKC_API.G_RET_STS_ERROR',
1306 x_msg_count,
1307 x_msg_data,
1308 '_PVT'
1309 );
1310 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1311 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1312 (
1313 l_api_name,
1314 G_PKG_NAME,
1315 'OKC_API.G_RET_STS_UNEXP_ERROR',
1316 x_msg_count,
1317 x_msg_data,
1318 '_PVT'
1319 );
1320 WHEN OTHERS THEN
1321 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1322 (
1323 l_api_name,
1324 G_PKG_NAME,
1325 'OTHERS',
1326 x_msg_count,
1327 x_msg_data,
1328 '_PVT'
1329 );
1330 END insert_row;
1331
1332 ---------------------------------------------------------------------------
1333 -- PROCEDURE lock_row
1334 ---------------------------------------------------------------------------
1335 -----------------------------------
1336 -- lock_row for:OKC_RG_ROLE_DEFS --
1337 -----------------------------------
1338 PROCEDURE lock_row(
1339 p_init_msg_list IN VARCHAR2,
1340 x_return_status OUT NOCOPY VARCHAR2,
1341 x_msg_count OUT NOCOPY NUMBER,
1342 x_msg_data OUT NOCOPY VARCHAR2,
1343 p_rrd_rec IN rrd_rec_type) IS
1344
1345 E_Resource_Busy EXCEPTION;
1346 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1347 CURSOR lock_csr (p_rrd_rec IN rrd_rec_type) IS
1348 SELECT OBJECT_VERSION_NUMBER
1349 FROM OKC_RG_ROLE_DEFS
1350 WHERE ID = p_rrd_rec.id
1351 AND OBJECT_VERSION_NUMBER = p_rrd_rec.object_version_number
1352 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1353
1354 CURSOR lchk_csr (p_rrd_rec IN rrd_rec_type) IS
1355 SELECT OBJECT_VERSION_NUMBER
1356 FROM OKC_RG_ROLE_DEFS
1357 WHERE ID = p_rrd_rec.id;
1358 l_api_version CONSTANT NUMBER := 1;
1359 l_api_name CONSTANT VARCHAR2(30) := 'DEFS_lock_row';
1360 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1361 l_object_version_number OKC_RG_ROLE_DEFS.OBJECT_VERSION_NUMBER%TYPE;
1362 lc_object_version_number OKC_RG_ROLE_DEFS.OBJECT_VERSION_NUMBER%TYPE;
1363 l_row_notfound BOOLEAN := FALSE;
1364 lc_row_notfound BOOLEAN := FALSE;
1365 BEGIN
1366 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1367 p_init_msg_list,
1368 '_PVT',
1369 x_return_status);
1370 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1371 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1372 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1373 RAISE OKC_API.G_EXCEPTION_ERROR;
1374 END IF;
1375 BEGIN
1376 OPEN lock_csr(p_rrd_rec);
1377 FETCH lock_csr INTO l_object_version_number;
1378 l_row_notfound := lock_csr%NOTFOUND;
1379 CLOSE lock_csr;
1380 EXCEPTION
1381 WHEN E_Resource_Busy THEN
1382 IF (lock_csr%ISOPEN) THEN
1383 CLOSE lock_csr;
1384 END IF;
1385 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1386 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1387 END;
1388
1389 IF ( l_row_notfound ) THEN
1390 OPEN lchk_csr(p_rrd_rec);
1391 FETCH lchk_csr INTO lc_object_version_number;
1392 lc_row_notfound := lchk_csr%NOTFOUND;
1393 CLOSE lchk_csr;
1394 END IF;
1395 IF (lc_row_notfound) THEN
1396 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1397 RAISE OKC_API.G_EXCEPTION_ERROR;
1398 ELSIF lc_object_version_number > p_rrd_rec.object_version_number THEN
1399 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1400 RAISE OKC_API.G_EXCEPTION_ERROR;
1401 ELSIF lc_object_version_number <> p_rrd_rec.object_version_number THEN
1402 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1403 RAISE OKC_API.G_EXCEPTION_ERROR;
1404 ELSIF lc_object_version_number = -1 THEN
1405 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1406 RAISE OKC_API.G_EXCEPTION_ERROR;
1407 END IF;
1408 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1409 EXCEPTION
1410 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1411 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1412 (
1413 l_api_name,
1414 G_PKG_NAME,
1415 'OKC_API.G_RET_STS_ERROR',
1416 x_msg_count,
1417 x_msg_data,
1418 '_PVT'
1419 );
1420 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1421 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1422 (
1423 l_api_name,
1424 G_PKG_NAME,
1425 'OKC_API.G_RET_STS_UNEXP_ERROR',
1426 x_msg_count,
1427 x_msg_data,
1428 '_PVT'
1429 );
1430 WHEN OTHERS THEN
1431 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1432 (
1433 l_api_name,
1434 G_PKG_NAME,
1435 'OTHERS',
1436 x_msg_count,
1437 x_msg_data,
1438 '_PVT'
1439 );
1440 END lock_row;
1441 -------------------------------------
1442 -- lock_row for:OKC_RG_ROLE_DEFS_V --
1443 -------------------------------------
1444 PROCEDURE lock_row(
1445 p_api_version IN NUMBER,
1446 p_init_msg_list IN VARCHAR2,
1447 x_return_status OUT NOCOPY VARCHAR2,
1448 x_msg_count OUT NOCOPY NUMBER,
1449 x_msg_data OUT NOCOPY VARCHAR2,
1450 p_rrdv_rec IN rrdv_rec_type) IS
1451
1452 l_api_version CONSTANT NUMBER := 1;
1453 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1454 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1455 l_rrd_rec rrd_rec_type;
1456 BEGIN
1457 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1458 G_PKG_NAME,
1459 p_init_msg_list,
1460 l_api_version,
1461 p_api_version,
1462 '_PVT',
1463 x_return_status);
1464 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1465 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1466 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1467 RAISE OKC_API.G_EXCEPTION_ERROR;
1468 END IF;
1469 --------------------------------------
1470 -- Move VIEW record to "Child" records
1471 --------------------------------------
1472 migrate(p_rrdv_rec, l_rrd_rec);
1473 --------------------------------------------
1474 -- Call the LOCK_ROW for each child record
1475 --------------------------------------------
1476 lock_row(
1477 p_init_msg_list,
1478 x_return_status,
1479 x_msg_count,
1480 x_msg_data,
1481 l_rrd_rec
1482 );
1483 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1484 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1485 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1486 RAISE OKC_API.G_EXCEPTION_ERROR;
1487 END IF;
1488 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1489 EXCEPTION
1490 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1491 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1492 (
1493 l_api_name,
1494 G_PKG_NAME,
1495 'OKC_API.G_RET_STS_ERROR',
1496 x_msg_count,
1497 x_msg_data,
1498 '_PVT'
1499 );
1500 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1501 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1502 (
1503 l_api_name,
1504 G_PKG_NAME,
1505 'OKC_API.G_RET_STS_UNEXP_ERROR',
1506 x_msg_count,
1507 x_msg_data,
1508 '_PVT'
1509 );
1510 WHEN OTHERS THEN
1511 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1512 (
1513 l_api_name,
1514 G_PKG_NAME,
1515 'OTHERS',
1516 x_msg_count,
1517 x_msg_data,
1518 '_PVT'
1519 );
1520 END lock_row;
1521 --------------------------------------
1522 -- PL/SQL TBL lock_row for:RRDV_TBL --
1523 --------------------------------------
1524 PROCEDURE lock_row(
1525 p_api_version IN NUMBER,
1526 p_init_msg_list IN VARCHAR2,
1527 x_return_status OUT NOCOPY VARCHAR2,
1528 x_msg_count OUT NOCOPY NUMBER,
1529 x_msg_data OUT NOCOPY VARCHAR2,
1530 p_rrdv_tbl IN rrdv_tbl_type) IS
1531
1532 l_api_version CONSTANT NUMBER := 1;
1533 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1534 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1535 i NUMBER := 0;
1536 BEGIN
1537 OKC_API.init_msg_list(p_init_msg_list);
1538 -- Make sure PL/SQL table has records in it before passing
1539 IF (p_rrdv_tbl.COUNT > 0) THEN
1540 i := p_rrdv_tbl.FIRST;
1541 LOOP
1542 lock_row (
1543 p_api_version => p_api_version,
1544 p_init_msg_list => OKC_API.G_FALSE,
1545 x_return_status => x_return_status,
1546 x_msg_count => x_msg_count,
1547 x_msg_data => x_msg_data,
1548 p_rrdv_rec => p_rrdv_tbl(i));
1549 EXIT WHEN (i = p_rrdv_tbl.LAST);
1550 i := p_rrdv_tbl.NEXT(i);
1551 END LOOP;
1552 END IF;
1553 EXCEPTION
1554 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1555 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1556 (
1557 l_api_name,
1558 G_PKG_NAME,
1559 'OKC_API.G_RET_STS_ERROR',
1560 x_msg_count,
1561 x_msg_data,
1562 '_PVT'
1563 );
1564 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1565 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1566 (
1567 l_api_name,
1568 G_PKG_NAME,
1569 'OKC_API.G_RET_STS_UNEXP_ERROR',
1570 x_msg_count,
1571 x_msg_data,
1572 '_PVT'
1573 );
1574 WHEN OTHERS THEN
1575 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1576 (
1577 l_api_name,
1578 G_PKG_NAME,
1579 'OTHERS',
1580 x_msg_count,
1581 x_msg_data,
1582 '_PVT'
1583 );
1584 END lock_row;
1585
1586 ---------------------------------------------------------------------------
1587 -- PROCEDURE update_row
1588 ---------------------------------------------------------------------------
1589 -------------------------------------
1590 -- update_row for:OKC_RG_ROLE_DEFS --
1591 -------------------------------------
1592 PROCEDURE update_row(
1593 p_init_msg_list IN VARCHAR2,
1594 x_return_status OUT NOCOPY VARCHAR2,
1595 x_msg_count OUT NOCOPY NUMBER,
1596 x_msg_data OUT NOCOPY VARCHAR2,
1597 p_rrd_rec IN rrd_rec_type,
1598 x_rrd_rec OUT NOCOPY rrd_rec_type) IS
1599
1600 l_api_version CONSTANT NUMBER := 1;
1601 l_api_name CONSTANT VARCHAR2(30) := 'DEFS_update_row';
1602 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1603 l_rrd_rec rrd_rec_type := p_rrd_rec;
1604 l_def_rrd_rec rrd_rec_type;
1605 l_row_notfound BOOLEAN := TRUE;
1606 ----------------------------------
1607 -- FUNCTION populate_new_record --
1608 ----------------------------------
1609 FUNCTION populate_new_record (
1610 p_rrd_rec IN rrd_rec_type,
1611 x_rrd_rec OUT NOCOPY rrd_rec_type
1612 ) RETURN VARCHAR2 IS
1613 l_rrd_rec rrd_rec_type;
1614 l_row_notfound BOOLEAN := TRUE;
1615 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1616 BEGIN
1617 x_rrd_rec := p_rrd_rec;
1618 -- Get current database values
1619 l_rrd_rec := get_rec(p_rrd_rec, l_row_notfound);
1620 IF (l_row_notfound) THEN
1621 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1622 END IF;
1623 IF (x_rrd_rec.id = OKC_API.G_MISS_NUM)
1624 THEN
1625 x_rrd_rec.id := l_rrd_rec.id;
1626 END IF;
1627 IF (x_rrd_rec.srd_id = OKC_API.G_MISS_NUM)
1628 THEN
1629 x_rrd_rec.srd_id := l_rrd_rec.srd_id;
1630 END IF;
1631 IF (x_rrd_rec.sre_id = OKC_API.G_MISS_NUM)
1632 THEN
1633 x_rrd_rec.sre_id := l_rrd_rec.sre_id;
1634 END IF;
1635 IF (x_rrd_rec.object_version_number = OKC_API.G_MISS_NUM)
1636 THEN
1637 x_rrd_rec.object_version_number := l_rrd_rec.object_version_number;
1638 END IF;
1639 IF (x_rrd_rec.created_by = OKC_API.G_MISS_NUM)
1640 THEN
1641 x_rrd_rec.created_by := l_rrd_rec.created_by;
1642 END IF;
1643 IF (x_rrd_rec.creation_date = OKC_API.G_MISS_DATE)
1644 THEN
1645 x_rrd_rec.creation_date := l_rrd_rec.creation_date;
1646 END IF;
1647 IF (x_rrd_rec.last_updated_by = OKC_API.G_MISS_NUM)
1648 THEN
1649 x_rrd_rec.last_updated_by := l_rrd_rec.last_updated_by;
1650 END IF;
1651 IF (x_rrd_rec.last_update_date = OKC_API.G_MISS_DATE)
1652 THEN
1653 x_rrd_rec.last_update_date := l_rrd_rec.last_update_date;
1654 END IF;
1655 IF (x_rrd_rec.subject_object_flag = OKC_API.G_MISS_CHAR)
1656 THEN
1657 x_rrd_rec.subject_object_flag := l_rrd_rec.subject_object_flag;
1658 END IF;
1659 IF (x_rrd_rec.optional_yn = OKC_API.G_MISS_CHAR)
1660 THEN
1661 x_rrd_rec.optional_yn := l_rrd_rec.optional_yn;
1662 END IF;
1663 IF (x_rrd_rec.last_update_login = OKC_API.G_MISS_NUM)
1664 THEN
1665 x_rrd_rec.last_update_login := l_rrd_rec.last_update_login;
1666 END IF;
1667 IF (x_rrd_rec.attribute_category = OKC_API.G_MISS_CHAR)
1668 THEN
1669 x_rrd_rec.attribute_category := l_rrd_rec.attribute_category;
1670 END IF;
1671 IF (x_rrd_rec.attribute1 = OKC_API.G_MISS_CHAR)
1672 THEN
1673 x_rrd_rec.attribute1 := l_rrd_rec.attribute1;
1674 END IF;
1675 IF (x_rrd_rec.attribute2 = OKC_API.G_MISS_CHAR)
1676 THEN
1677 x_rrd_rec.attribute2 := l_rrd_rec.attribute2;
1678 END IF;
1679 IF (x_rrd_rec.attribute3 = OKC_API.G_MISS_CHAR)
1680 THEN
1681 x_rrd_rec.attribute3 := l_rrd_rec.attribute3;
1682 END IF;
1683 IF (x_rrd_rec.attribute4 = OKC_API.G_MISS_CHAR)
1684 THEN
1685 x_rrd_rec.attribute4 := l_rrd_rec.attribute4;
1686 END IF;
1687 IF (x_rrd_rec.attribute5 = OKC_API.G_MISS_CHAR)
1688 THEN
1689 x_rrd_rec.attribute5 := l_rrd_rec.attribute5;
1690 END IF;
1691 IF (x_rrd_rec.attribute6 = OKC_API.G_MISS_CHAR)
1692 THEN
1693 x_rrd_rec.attribute6 := l_rrd_rec.attribute6;
1694 END IF;
1695 IF (x_rrd_rec.attribute7 = OKC_API.G_MISS_CHAR)
1696 THEN
1697 x_rrd_rec.attribute7 := l_rrd_rec.attribute7;
1698 END IF;
1699 IF (x_rrd_rec.attribute8 = OKC_API.G_MISS_CHAR)
1700 THEN
1701 x_rrd_rec.attribute8 := l_rrd_rec.attribute8;
1702 END IF;
1703 IF (x_rrd_rec.attribute9 = OKC_API.G_MISS_CHAR)
1704 THEN
1705 x_rrd_rec.attribute9 := l_rrd_rec.attribute9;
1706 END IF;
1707 IF (x_rrd_rec.attribute10 = OKC_API.G_MISS_CHAR)
1708 THEN
1709 x_rrd_rec.attribute10 := l_rrd_rec.attribute10;
1710 END IF;
1711 IF (x_rrd_rec.attribute11 = OKC_API.G_MISS_CHAR)
1712 THEN
1713 x_rrd_rec.attribute11 := l_rrd_rec.attribute11;
1714 END IF;
1715 IF (x_rrd_rec.attribute12 = OKC_API.G_MISS_CHAR)
1716 THEN
1717 x_rrd_rec.attribute12 := l_rrd_rec.attribute12;
1718 END IF;
1719 IF (x_rrd_rec.attribute13 = OKC_API.G_MISS_CHAR)
1720 THEN
1721 x_rrd_rec.attribute13 := l_rrd_rec.attribute13;
1722 END IF;
1723 IF (x_rrd_rec.attribute14 = OKC_API.G_MISS_CHAR)
1724 THEN
1725 x_rrd_rec.attribute14 := l_rrd_rec.attribute14;
1726 END IF;
1727 IF (x_rrd_rec.attribute15 = OKC_API.G_MISS_CHAR)
1728 THEN
1729 x_rrd_rec.attribute15 := l_rrd_rec.attribute15;
1730 END IF;
1731 IF (x_rrd_rec.access_level = OKC_API.G_MISS_CHAR)
1732 THEN
1733 x_rrd_rec.access_level := l_rrd_rec.access_level;
1734 END IF;
1735 RETURN(l_return_status);
1736 END populate_new_record;
1737 -----------------------------------------
1738 -- Set_Attributes for:OKC_RG_ROLE_DEFS --
1739 -----------------------------------------
1740 FUNCTION Set_Attributes (
1741 p_rrd_rec IN rrd_rec_type,
1742 x_rrd_rec OUT NOCOPY rrd_rec_type
1743 ) RETURN VARCHAR2 IS
1744 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1745 BEGIN
1746 x_rrd_rec := p_rrd_rec;
1747 RETURN(l_return_status);
1748 END Set_Attributes;
1749 BEGIN
1750 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1751 p_init_msg_list,
1752 '_PVT',
1753 x_return_status);
1754 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1755 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1756 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1757 RAISE OKC_API.G_EXCEPTION_ERROR;
1758 END IF;
1759 --- Setting item attributes
1760 l_return_status := Set_Attributes(
1761 p_rrd_rec, -- IN
1762 l_rrd_rec); -- OUT
1763 --- If any errors happen abort API
1764 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1765 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1766 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1767 RAISE OKC_API.G_EXCEPTION_ERROR;
1768 END IF;
1769 l_return_status := populate_new_record(l_rrd_rec, l_def_rrd_rec);
1770 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1771 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1772 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1773 RAISE OKC_API.G_EXCEPTION_ERROR;
1774 END IF;
1775 UPDATE OKC_RG_ROLE_DEFS
1776 SET SRD_ID = l_def_rrd_rec.srd_id,
1777 SRE_ID = l_def_rrd_rec.sre_id,
1778 OBJECT_VERSION_NUMBER = l_def_rrd_rec.object_version_number,
1779 CREATED_BY = l_def_rrd_rec.created_by,
1780 CREATION_DATE = l_def_rrd_rec.creation_date,
1781 LAST_UPDATED_BY = l_def_rrd_rec.last_updated_by,
1782 LAST_UPDATE_DATE = l_def_rrd_rec.last_update_date,
1783 SUBJECT_OBJECT_FLAG = l_def_rrd_rec.subject_object_flag,
1784 OPTIONAL_YN = l_def_rrd_rec.optional_yn,
1785 LAST_UPDATE_LOGIN = l_def_rrd_rec.last_update_login,
1786 ATTRIBUTE_CATEGORY = l_def_rrd_rec.attribute_category,
1787 ATTRIBUTE1 = l_def_rrd_rec.attribute1,
1788 ATTRIBUTE2 = l_def_rrd_rec.attribute2,
1789 ATTRIBUTE3 = l_def_rrd_rec.attribute3,
1790 ATTRIBUTE4 = l_def_rrd_rec.attribute4,
1791 ATTRIBUTE5 = l_def_rrd_rec.attribute5,
1792 ATTRIBUTE6 = l_def_rrd_rec.attribute6,
1793 ATTRIBUTE7 = l_def_rrd_rec.attribute7,
1794 ATTRIBUTE8 = l_def_rrd_rec.attribute8,
1795 ATTRIBUTE9 = l_def_rrd_rec.attribute9,
1796 ATTRIBUTE10 = l_def_rrd_rec.attribute10,
1797 ATTRIBUTE11 = l_def_rrd_rec.attribute11,
1798 ATTRIBUTE12 = l_def_rrd_rec.attribute12,
1799 ATTRIBUTE13 = l_def_rrd_rec.attribute13,
1800 ATTRIBUTE14 = l_def_rrd_rec.attribute14,
1801 ATTRIBUTE15 = l_def_rrd_rec.attribute15,
1802 ACCESS_LEVEL = l_def_rrd_rec.access_level
1803 WHERE ID = l_def_rrd_rec.id;
1804
1805 x_rrd_rec := l_def_rrd_rec;
1806 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1807 EXCEPTION
1808 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1809 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1810 (
1811 l_api_name,
1812 G_PKG_NAME,
1813 'OKC_API.G_RET_STS_ERROR',
1814 x_msg_count,
1815 x_msg_data,
1816 '_PVT'
1817 );
1818 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1819 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1820 (
1821 l_api_name,
1822 G_PKG_NAME,
1823 'OKC_API.G_RET_STS_UNEXP_ERROR',
1824 x_msg_count,
1825 x_msg_data,
1826 '_PVT'
1827 );
1828 WHEN OTHERS THEN
1829 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1830 (
1831 l_api_name,
1832 G_PKG_NAME,
1833 'OTHERS',
1834 x_msg_count,
1835 x_msg_data,
1836 '_PVT'
1837 );
1838 END update_row;
1839 ---------------------------------------
1840 -- update_row for:OKC_RG_ROLE_DEFS_V --
1841 ---------------------------------------
1842 PROCEDURE update_row(
1843 p_api_version IN NUMBER,
1844 p_init_msg_list IN VARCHAR2,
1845 x_return_status OUT NOCOPY VARCHAR2,
1846 x_msg_count OUT NOCOPY NUMBER,
1847 x_msg_data OUT NOCOPY VARCHAR2,
1848 p_rrdv_rec IN rrdv_rec_type,
1849 x_rrdv_rec OUT NOCOPY rrdv_rec_type) IS
1850
1851 l_api_version CONSTANT NUMBER := 1;
1852 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1853 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1854 l_rrdv_rec rrdv_rec_type := p_rrdv_rec;
1855 l_def_rrdv_rec rrdv_rec_type;
1856 l_rrd_rec rrd_rec_type;
1857 lx_rrd_rec rrd_rec_type;
1858 -------------------------------
1859 -- FUNCTION fill_who_columns --
1860 -------------------------------
1861 FUNCTION fill_who_columns (
1862 p_rrdv_rec IN rrdv_rec_type
1863 ) RETURN rrdv_rec_type IS
1864 l_rrdv_rec rrdv_rec_type := p_rrdv_rec;
1865 BEGIN
1866 l_rrdv_rec.LAST_UPDATE_DATE := SYSDATE;
1867 l_rrdv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1868 l_rrdv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1869 RETURN(l_rrdv_rec);
1870 END fill_who_columns;
1871 ----------------------------------
1872 -- FUNCTION populate_new_record --
1873 ----------------------------------
1874 FUNCTION populate_new_record (
1875 p_rrdv_rec IN rrdv_rec_type,
1876 x_rrdv_rec OUT NOCOPY rrdv_rec_type
1877 ) RETURN VARCHAR2 IS
1878 l_rrdv_rec rrdv_rec_type;
1879 l_row_notfound BOOLEAN := TRUE;
1880 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1881 BEGIN
1882 x_rrdv_rec := p_rrdv_rec;
1883 -- Get current database values
1884 l_rrdv_rec := get_rec(p_rrdv_rec, l_row_notfound);
1885 IF (l_row_notfound) THEN
1886 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1887 END IF;
1888 IF (x_rrdv_rec.id = OKC_API.G_MISS_NUM)
1889 THEN
1890 x_rrdv_rec.id := l_rrdv_rec.id;
1891 END IF;
1892 IF (x_rrdv_rec.object_version_number = OKC_API.G_MISS_NUM)
1893 THEN
1894 x_rrdv_rec.object_version_number := l_rrdv_rec.object_version_number;
1895 END IF;
1896 IF (x_rrdv_rec.srd_id = OKC_API.G_MISS_NUM)
1897 THEN
1898 x_rrdv_rec.srd_id := l_rrdv_rec.srd_id;
1899 END IF;
1900 IF (x_rrdv_rec.sre_id = OKC_API.G_MISS_NUM)
1901 THEN
1902 x_rrdv_rec.sre_id := l_rrdv_rec.sre_id;
1903 END IF;
1904 IF (x_rrdv_rec.optional_yn = OKC_API.G_MISS_CHAR)
1905 THEN
1906 x_rrdv_rec.optional_yn := l_rrdv_rec.optional_yn;
1907 END IF;
1908 IF (x_rrdv_rec.subject_object_flag = OKC_API.G_MISS_CHAR)
1909 THEN
1910 x_rrdv_rec.subject_object_flag := l_rrdv_rec.subject_object_flag;
1911 END IF;
1912 IF (x_rrdv_rec.attribute_category = OKC_API.G_MISS_CHAR)
1913 THEN
1914 x_rrdv_rec.attribute_category := l_rrdv_rec.attribute_category;
1915 END IF;
1916 IF (x_rrdv_rec.attribute1 = OKC_API.G_MISS_CHAR)
1917 THEN
1918 x_rrdv_rec.attribute1 := l_rrdv_rec.attribute1;
1919 END IF;
1920 IF (x_rrdv_rec.attribute2 = OKC_API.G_MISS_CHAR)
1921 THEN
1922 x_rrdv_rec.attribute2 := l_rrdv_rec.attribute2;
1923 END IF;
1924 IF (x_rrdv_rec.attribute3 = OKC_API.G_MISS_CHAR)
1925 THEN
1926 x_rrdv_rec.attribute3 := l_rrdv_rec.attribute3;
1927 END IF;
1928 IF (x_rrdv_rec.attribute4 = OKC_API.G_MISS_CHAR)
1929 THEN
1930 x_rrdv_rec.attribute4 := l_rrdv_rec.attribute4;
1931 END IF;
1932 IF (x_rrdv_rec.attribute5 = OKC_API.G_MISS_CHAR)
1933 THEN
1934 x_rrdv_rec.attribute5 := l_rrdv_rec.attribute5;
1935 END IF;
1936 IF (x_rrdv_rec.attribute6 = OKC_API.G_MISS_CHAR)
1937 THEN
1938 x_rrdv_rec.attribute6 := l_rrdv_rec.attribute6;
1939 END IF;
1940 IF (x_rrdv_rec.attribute7 = OKC_API.G_MISS_CHAR)
1941 THEN
1942 x_rrdv_rec.attribute7 := l_rrdv_rec.attribute7;
1943 END IF;
1944 IF (x_rrdv_rec.attribute8 = OKC_API.G_MISS_CHAR)
1945 THEN
1946 x_rrdv_rec.attribute8 := l_rrdv_rec.attribute8;
1947 END IF;
1948 IF (x_rrdv_rec.attribute9 = OKC_API.G_MISS_CHAR)
1949 THEN
1950 x_rrdv_rec.attribute9 := l_rrdv_rec.attribute9;
1951 END IF;
1952 IF (x_rrdv_rec.attribute10 = OKC_API.G_MISS_CHAR)
1953 THEN
1954 x_rrdv_rec.attribute10 := l_rrdv_rec.attribute10;
1955 END IF;
1956 IF (x_rrdv_rec.attribute11 = OKC_API.G_MISS_CHAR)
1957 THEN
1958 x_rrdv_rec.attribute11 := l_rrdv_rec.attribute11;
1959 END IF;
1960 IF (x_rrdv_rec.attribute12 = OKC_API.G_MISS_CHAR)
1961 THEN
1962 x_rrdv_rec.attribute12 := l_rrdv_rec.attribute12;
1963 END IF;
1964 IF (x_rrdv_rec.attribute13 = OKC_API.G_MISS_CHAR)
1965 THEN
1966 x_rrdv_rec.attribute13 := l_rrdv_rec.attribute13;
1967 END IF;
1968 IF (x_rrdv_rec.attribute14 = OKC_API.G_MISS_CHAR)
1969 THEN
1970 x_rrdv_rec.attribute14 := l_rrdv_rec.attribute14;
1971 END IF;
1972 IF (x_rrdv_rec.attribute15 = OKC_API.G_MISS_CHAR)
1973 THEN
1974 x_rrdv_rec.attribute15 := l_rrdv_rec.attribute15;
1975 END IF;
1976 IF (x_rrdv_rec.created_by = OKC_API.G_MISS_NUM)
1977 THEN
1978 x_rrdv_rec.created_by := l_rrdv_rec.created_by;
1979 END IF;
1980 IF (x_rrdv_rec.creation_date = OKC_API.G_MISS_DATE)
1981 THEN
1982 x_rrdv_rec.creation_date := l_rrdv_rec.creation_date;
1983 END IF;
1984 IF (x_rrdv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1985 THEN
1986 x_rrdv_rec.last_updated_by := l_rrdv_rec.last_updated_by;
1987 END IF;
1988 IF (x_rrdv_rec.last_update_date = OKC_API.G_MISS_DATE)
1989 THEN
1990 x_rrdv_rec.last_update_date := l_rrdv_rec.last_update_date;
1991 END IF;
1992 IF (x_rrdv_rec.last_update_login = OKC_API.G_MISS_NUM)
1993 THEN
1994 x_rrdv_rec.last_update_login := l_rrdv_rec.last_update_login;
1995 END IF;
1996 IF (x_rrdv_rec.access_level = OKC_API.G_MISS_CHAR)
1997 THEN
1998 x_rrdv_rec.access_level := l_rrdv_rec.access_level;
1999 END IF;
2000 RETURN(l_return_status);
2001 END populate_new_record;
2002 -------------------------------------------
2003 -- Set_Attributes for:OKC_RG_ROLE_DEFS_V --
2004 -------------------------------------------
2005 FUNCTION Set_Attributes (
2006 p_rrdv_rec IN rrdv_rec_type,
2007 x_rrdv_rec OUT NOCOPY rrdv_rec_type
2008 ) RETURN VARCHAR2 IS
2009 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2010 BEGIN
2011 x_rrdv_rec := p_rrdv_rec;
2012 x_rrdv_rec.OBJECT_VERSION_NUMBER := NVL(x_rrdv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2013 RETURN(l_return_status);
2014 END Set_Attributes;
2015 BEGIN
2016 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2017 G_PKG_NAME,
2018 p_init_msg_list,
2019 l_api_version,
2020 p_api_version,
2021 '_PVT',
2022 x_return_status);
2023 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2024 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2025 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2026 RAISE OKC_API.G_EXCEPTION_ERROR;
2027 END IF;
2028 --- Setting item attributes
2029 l_return_status := Set_Attributes(
2030 p_rrdv_rec, -- IN
2031 l_rrdv_rec); -- OUT
2032 --- If any errors happen abort API
2033 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2034 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2035 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2036 RAISE OKC_API.G_EXCEPTION_ERROR;
2037 END IF;
2038 l_return_status := populate_new_record(l_rrdv_rec, l_def_rrdv_rec);
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_def_rrdv_rec := fill_who_columns(l_def_rrdv_rec);
2045 --- Validate all non-missing attributes (Item Level Validation)
2046 l_return_status := Validate_Attributes(l_def_rrdv_rec);
2047 --- If any errors happen abort API
2048 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2049 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2050 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2051 RAISE OKC_API.G_EXCEPTION_ERROR;
2052 END IF;
2053 l_return_status := Validate_Record(l_def_rrdv_rec);
2054 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2055 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2056 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2057 RAISE OKC_API.G_EXCEPTION_ERROR;
2058 END IF;
2059
2060 --------------------------------------
2061 -- Move VIEW record to "Child" records
2062 --------------------------------------
2063 migrate(l_def_rrdv_rec, l_rrd_rec);
2064 --------------------------------------------
2065 -- Call the UPDATE_ROW for each child record
2066 --------------------------------------------
2067 update_row(
2068 p_init_msg_list,
2069 x_return_status,
2070 x_msg_count,
2071 x_msg_data,
2072 l_rrd_rec,
2073 lx_rrd_rec
2074 );
2075 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2076 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2077 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2078 RAISE OKC_API.G_EXCEPTION_ERROR;
2079 END IF;
2080 migrate(lx_rrd_rec, l_def_rrdv_rec);
2081 x_rrdv_rec := l_def_rrdv_rec;
2082 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2083 EXCEPTION
2084 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2085 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2086 (
2087 l_api_name,
2088 G_PKG_NAME,
2089 'OKC_API.G_RET_STS_ERROR',
2090 x_msg_count,
2091 x_msg_data,
2092 '_PVT'
2093 );
2094 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
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 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2106 (
2107 l_api_name,
2108 G_PKG_NAME,
2109 'OTHERS',
2110 x_msg_count,
2111 x_msg_data,
2112 '_PVT'
2113 );
2114 END update_row;
2115 ----------------------------------------
2116 -- PL/SQL TBL update_row for:RRDV_TBL --
2117 ----------------------------------------
2118 PROCEDURE update_row(
2119 p_api_version IN NUMBER,
2120 p_init_msg_list IN VARCHAR2,
2121 x_return_status OUT NOCOPY VARCHAR2,
2122 x_msg_count OUT NOCOPY NUMBER,
2123 x_msg_data OUT NOCOPY VARCHAR2,
2124 p_rrdv_tbl IN rrdv_tbl_type,
2125 x_rrdv_tbl OUT NOCOPY rrdv_tbl_type) IS
2126
2127 l_api_version CONSTANT NUMBER := 1;
2128 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2129 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2130 i NUMBER := 0;
2131 BEGIN
2132 OKC_API.init_msg_list(p_init_msg_list);
2133 -- Make sure PL/SQL table has records in it before passing
2134 IF (p_rrdv_tbl.COUNT > 0) THEN
2135 i := p_rrdv_tbl.FIRST;
2136 LOOP
2137 update_row (
2138 p_api_version => p_api_version,
2139 p_init_msg_list => OKC_API.G_FALSE,
2140 x_return_status => x_return_status,
2141 x_msg_count => x_msg_count,
2142 x_msg_data => x_msg_data,
2143 p_rrdv_rec => p_rrdv_tbl(i),
2144 x_rrdv_rec => x_rrdv_tbl(i));
2145 EXIT WHEN (i = p_rrdv_tbl.LAST);
2146 i := p_rrdv_tbl.NEXT(i);
2147 END LOOP;
2148 END IF;
2149 EXCEPTION
2150 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2151 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2152 (
2153 l_api_name,
2154 G_PKG_NAME,
2155 'OKC_API.G_RET_STS_ERROR',
2156 x_msg_count,
2157 x_msg_data,
2158 '_PVT'
2159 );
2160 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2161 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2162 (
2163 l_api_name,
2164 G_PKG_NAME,
2165 'OKC_API.G_RET_STS_UNEXP_ERROR',
2166 x_msg_count,
2167 x_msg_data,
2168 '_PVT'
2169 );
2170 WHEN OTHERS THEN
2171 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2172 (
2173 l_api_name,
2174 G_PKG_NAME,
2175 'OTHERS',
2176 x_msg_count,
2177 x_msg_data,
2178 '_PVT'
2179 );
2180 END update_row;
2181
2182 ---------------------------------------------------------------------------
2183 -- PROCEDURE delete_row
2184 ---------------------------------------------------------------------------
2185 -------------------------------------
2186 -- delete_row for:OKC_RG_ROLE_DEFS --
2187 -------------------------------------
2188 PROCEDURE delete_row(
2189 p_init_msg_list IN VARCHAR2,
2190 x_return_status OUT NOCOPY VARCHAR2,
2191 x_msg_count OUT NOCOPY NUMBER,
2192 x_msg_data OUT NOCOPY VARCHAR2,
2193 p_rrd_rec IN rrd_rec_type) IS
2194
2195 l_api_version CONSTANT NUMBER := 1;
2196 l_api_name CONSTANT VARCHAR2(30) := 'DEFS_delete_row';
2197 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2198 l_rrd_rec rrd_rec_type:= p_rrd_rec;
2199 l_row_notfound BOOLEAN := TRUE;
2200 BEGIN
2201 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2202 p_init_msg_list,
2203 '_PVT',
2204 x_return_status);
2205 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2206 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2207 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2208 RAISE OKC_API.G_EXCEPTION_ERROR;
2209 END IF;
2210 DELETE FROM OKC_RG_ROLE_DEFS
2211 WHERE ID = l_rrd_rec.id;
2212
2213 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2214 EXCEPTION
2215 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2216 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2217 (
2218 l_api_name,
2219 G_PKG_NAME,
2220 'OKC_API.G_RET_STS_ERROR',
2221 x_msg_count,
2222 x_msg_data,
2223 '_PVT'
2224 );
2225 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2226 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2227 (
2228 l_api_name,
2229 G_PKG_NAME,
2230 'OKC_API.G_RET_STS_UNEXP_ERROR',
2231 x_msg_count,
2232 x_msg_data,
2233 '_PVT'
2234 );
2235 WHEN OTHERS THEN
2236 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2237 (
2238 l_api_name,
2239 G_PKG_NAME,
2240 'OTHERS',
2241 x_msg_count,
2242 x_msg_data,
2243 '_PVT'
2244 );
2245 END delete_row;
2246 ---------------------------------------
2247 -- delete_row for:OKC_RG_ROLE_DEFS_V --
2248 ---------------------------------------
2249 PROCEDURE delete_row(
2250 p_api_version IN NUMBER,
2251 p_init_msg_list IN VARCHAR2,
2252 x_return_status OUT NOCOPY VARCHAR2,
2253 x_msg_count OUT NOCOPY NUMBER,
2254 x_msg_data OUT NOCOPY VARCHAR2,
2255 p_rrdv_rec IN rrdv_rec_type) IS
2256
2257 l_api_version CONSTANT NUMBER := 1;
2258 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2259 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2260 l_rrdv_rec rrdv_rec_type := p_rrdv_rec;
2261 l_rrd_rec rrd_rec_type;
2262 BEGIN
2263 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2264 G_PKG_NAME,
2265 p_init_msg_list,
2266 l_api_version,
2267 p_api_version,
2268 '_PVT',
2269 x_return_status);
2270 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2272 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2273 RAISE OKC_API.G_EXCEPTION_ERROR;
2274 END IF;
2275 --------------------------------------
2276 -- Move VIEW record to "Child" records
2277 --------------------------------------
2278 migrate(l_rrdv_rec, l_rrd_rec);
2279 --------------------------------------------
2280 -- Call the DELETE_ROW for each child record
2281 --------------------------------------------
2282 delete_row(
2283 p_init_msg_list,
2284 x_return_status,
2285 x_msg_count,
2286 x_msg_data,
2287 l_rrd_rec
2288 );
2289 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2290 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2291 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2292 RAISE OKC_API.G_EXCEPTION_ERROR;
2293 END IF;
2294 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2295 EXCEPTION
2296 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2297 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2298 (
2299 l_api_name,
2300 G_PKG_NAME,
2301 'OKC_API.G_RET_STS_ERROR',
2302 x_msg_count,
2303 x_msg_data,
2304 '_PVT'
2305 );
2306 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2307 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2308 (
2309 l_api_name,
2310 G_PKG_NAME,
2311 'OKC_API.G_RET_STS_UNEXP_ERROR',
2312 x_msg_count,
2313 x_msg_data,
2314 '_PVT'
2315 );
2316 WHEN OTHERS THEN
2317 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2318 (
2319 l_api_name,
2320 G_PKG_NAME,
2321 'OTHERS',
2322 x_msg_count,
2323 x_msg_data,
2324 '_PVT'
2325 );
2326 END delete_row;
2327 ----------------------------------------
2328 -- PL/SQL TBL delete_row for:RRDV_TBL --
2329 ----------------------------------------
2330 PROCEDURE delete_row(
2331 p_api_version IN NUMBER,
2332 p_init_msg_list IN VARCHAR2,
2333 x_return_status OUT NOCOPY VARCHAR2,
2334 x_msg_count OUT NOCOPY NUMBER,
2335 x_msg_data OUT NOCOPY VARCHAR2,
2336 p_rrdv_tbl IN rrdv_tbl_type) IS
2337
2338 l_api_version CONSTANT NUMBER := 1;
2339 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2340 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2341 i NUMBER := 0;
2342 BEGIN
2343 OKC_API.init_msg_list(p_init_msg_list);
2344 -- Make sure PL/SQL table has records in it before passing
2345 IF (p_rrdv_tbl.COUNT > 0) THEN
2346 i := p_rrdv_tbl.FIRST;
2347 LOOP
2348 delete_row (
2349 p_api_version => p_api_version,
2350 p_init_msg_list => OKC_API.G_FALSE,
2351 x_return_status => x_return_status,
2352 x_msg_count => x_msg_count,
2353 x_msg_data => x_msg_data,
2354 p_rrdv_rec => p_rrdv_tbl(i));
2355 EXIT WHEN (i = p_rrdv_tbl.LAST);
2356 i := p_rrdv_tbl.NEXT(i);
2357 END LOOP;
2358 END IF;
2359 EXCEPTION
2360 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2361 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2362 (
2363 l_api_name,
2364 G_PKG_NAME,
2365 'OKC_API.G_RET_STS_ERROR',
2366 x_msg_count,
2367 x_msg_data,
2368 '_PVT'
2369 );
2370 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2371 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2372 (
2373 l_api_name,
2374 G_PKG_NAME,
2375 'OKC_API.G_RET_STS_UNEXP_ERROR',
2376 x_msg_count,
2377 x_msg_data,
2378 '_PVT'
2379 );
2380 WHEN OTHERS THEN
2381 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2382 (
2383 l_api_name,
2384 G_PKG_NAME,
2385 'OTHERS',
2386 x_msg_count,
2387 x_msg_data,
2388 '_PVT'
2389 );
2390 END delete_row;
2391 END OKC_RRD_PVT;