[Home] [Help]
PACKAGE BODY: APPS.OKC_SRE_PVT
Source
1 PACKAGE BODY OKC_SRE_PVT AS
2 /* $Header: OKCSSREB.pls 120.0 2005/05/26 09:48:48 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_SUBCLASS_ROLES_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)
20 from
17 cursor seed_c is
18 select
19 nvl(max(id), 11000) + 1
21 OKC_SUBCLASS_ROLES_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_SUBCLASS_ROLES_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_SUBCLASS_ROLES
69 ---------------------------------------------------------------------------
70 FUNCTION get_rec (
71 p_sre_rec IN sre_rec_type,
72 x_no_data_found OUT NOCOPY BOOLEAN
73 ) RETURN sre_rec_type IS
74 CURSOR sre_pk_csr (p_id IN NUMBER) IS
75 SELECT
76 ID,
77 SCS_CODE,
78 RLE_CODE,
79 OBJECT_VERSION_NUMBER,
80 CREATED_BY,
81 CREATION_DATE,
82 LAST_UPDATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATE_LOGIN,
85 START_DATE,
86 END_DATE,
87 ACCESS_LEVEL
88 FROM Okc_Subclass_Roles
89 WHERE okc_subclass_roles.id = p_id;
90 l_sre_pk sre_pk_csr%ROWTYPE;
91 l_sre_rec sre_rec_type;
92 BEGIN
93 x_no_data_found := TRUE;
94 -- Get current database values
95 OPEN sre_pk_csr (p_sre_rec.id);
96 FETCH sre_pk_csr INTO
97 l_sre_rec.ID,
98 l_sre_rec.SCS_CODE,
99 l_sre_rec.RLE_CODE,
100 l_sre_rec.OBJECT_VERSION_NUMBER,
101 l_sre_rec.CREATED_BY,
102 l_sre_rec.CREATION_DATE,
103 l_sre_rec.LAST_UPDATED_BY,
104 l_sre_rec.LAST_UPDATE_DATE,
105 l_sre_rec.LAST_UPDATE_LOGIN,
106 l_sre_rec.START_DATE,
107 l_sre_rec.END_DATE,
108 l_sre_rec.ACCESS_LEVEL;
109 x_no_data_found := sre_pk_csr%NOTFOUND;
110 CLOSE sre_pk_csr;
111 RETURN(l_sre_rec);
112 END get_rec;
113
114 FUNCTION get_rec (
115 p_sre_rec IN sre_rec_type
116 ) RETURN sre_rec_type IS
117 l_row_notfound BOOLEAN := TRUE;
118 BEGIN
119 RETURN(get_rec(p_sre_rec, l_row_notfound));
120 END get_rec;
121 ---------------------------------------------------------------------------
122 -- FUNCTION get_rec for: OKC_SUBCLASS_ROLES_V
123 ---------------------------------------------------------------------------
124 FUNCTION get_rec (
125 p_srev_rec IN srev_rec_type,
126 x_no_data_found OUT NOCOPY BOOLEAN
127 ) RETURN srev_rec_type IS
128 CURSOR okc_srev_pk_csr (p_id IN NUMBER) IS
129 SELECT
130 ID,
131 OBJECT_VERSION_NUMBER,
132 RLE_CODE,
133 SCS_CODE,
134 CREATED_BY,
135 CREATION_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_DATE,
138 LAST_UPDATE_LOGIN,
139 START_DATE,
140 END_DATE,
141 ACCESS_LEVEL
142 FROM Okc_Subclass_Roles_V
143 WHERE okc_subclass_roles_v.id = p_id;
144 l_okc_srev_pk okc_srev_pk_csr%ROWTYPE;
145 l_srev_rec srev_rec_type;
146 BEGIN
147 x_no_data_found := TRUE;
148 -- Get current database values
149 OPEN okc_srev_pk_csr (p_srev_rec.id);
150 FETCH okc_srev_pk_csr INTO
151 l_srev_rec.ID,
152 l_srev_rec.OBJECT_VERSION_NUMBER,
153 l_srev_rec.RLE_CODE,
154 l_srev_rec.SCS_CODE,
155 l_srev_rec.CREATED_BY,
156 l_srev_rec.CREATION_DATE,
157 l_srev_rec.LAST_UPDATED_BY,
158 l_srev_rec.LAST_UPDATE_DATE,
159 l_srev_rec.LAST_UPDATE_LOGIN,
160 l_srev_rec.START_DATE,
161 l_srev_rec.END_DATE,
162 l_srev_rec.ACCESS_LEVEL;
163 x_no_data_found := okc_srev_pk_csr%NOTFOUND;
164 CLOSE okc_srev_pk_csr;
165 RETURN(l_srev_rec);
166 END get_rec;
167
168 FUNCTION get_rec (
169 p_srev_rec IN srev_rec_type
170 ) RETURN srev_rec_type IS
171 l_row_notfound BOOLEAN := TRUE;
172 BEGIN
173 RETURN(get_rec(p_srev_rec, l_row_notfound));
174 END get_rec;
175
176 ----------------------------------------------------------
177 -- FUNCTION null_out_defaults for: OKC_SUBCLASS_ROLES_V --
178 ----------------------------------------------------------
179 FUNCTION null_out_defaults (
180 p_srev_rec IN srev_rec_type
181 ) RETURN srev_rec_type IS
182 l_srev_rec srev_rec_type := p_srev_rec;
183 BEGIN
184 IF (l_srev_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
185 l_srev_rec.object_version_number := NULL;
186 END IF;
187 IF (l_srev_rec.rle_code = OKC_API.G_MISS_CHAR) THEN
188 l_srev_rec.rle_code := NULL;
189 END IF;
190 IF (l_srev_rec.scs_code = OKC_API.G_MISS_CHAR) THEN
191 l_srev_rec.scs_code := NULL;
192 END IF;
193 IF (l_srev_rec.created_by = OKC_API.G_MISS_NUM) THEN
194 l_srev_rec.created_by := NULL;
195 END IF;
196 IF (l_srev_rec.creation_date = OKC_API.G_MISS_DATE) THEN
197 l_srev_rec.creation_date := NULL;
198 END IF;
199 IF (l_srev_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
200 l_srev_rec.last_updated_by := NULL;
201 END IF;
202 IF (l_srev_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
203 l_srev_rec.last_update_date := NULL;
204 END IF;
205 IF (l_srev_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
206 l_srev_rec.last_update_login := NULL;
207 END IF;
208 IF (l_srev_rec.start_date = OKC_API.G_MISS_DATE) THEN
209 l_srev_rec.start_date := NULL;
210 END IF;
211 IF (l_srev_rec.end_date = OKC_API.G_MISS_DATE) THEN
212 l_srev_rec.end_date := NULL;
213 END IF;
214 IF (l_srev_rec.access_level = OKC_API.G_MISS_CHAR) THEN
215 l_srev_rec.access_level := NULL;
216 END IF;
217 RETURN(l_srev_rec);
218 END null_out_defaults;
219 ---------------------------------------------------------------------------------------
220 --Attribute Level Validattion Procedures Starts(Modification on TAPI generated Code.)--
221 ---------------------------------------------------------------------------------------
222 ---------------------------------------------------------------------------
223 -- PROCEDURE Validate_id
224 ---------------------------------------------------------------------------
225 PROCEDURE validate_id(
226 p_srev_rec IN srev_rec_type,
227 x_return_status OUT NOCOPY VARCHAR2) IS
228 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
229 BEGIN
230 IF p_srev_rec.id = OKC_API.G_MISS_NUM OR
231 p_srev_rec.id IS NULL
232 THEN
233 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
234 l_return_status := OKC_API.G_RET_STS_ERROR;
235 END IF;
236 x_return_status := l_return_status;
237 EXCEPTION
238 WHEN OTHERS THEN
239 -- store SQL error message on message stack for caller
240 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
241 -- notify caller of an UNEXPECTED error
242 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
243 END validate_id;
244
245 ---------------------------------------------------------------------------
246 -- PROCEDURE Validate_object_version_number
247 ---------------------------------------------------------------------------
248 PROCEDURE validate_object_version_number(
249 p_srev_rec IN srev_rec_type,
250 x_return_status OUT NOCOPY VARCHAR2) IS
251 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
252 BEGIN
253 IF p_srev_rec.object_version_number = OKC_API.G_MISS_NUM OR
254 p_srev_rec.object_version_number IS NULL
255 THEN
256 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
257 l_return_status := OKC_API.G_RET_STS_ERROR;
258 RAISE G_EXCEPTION_HALT_VALIDATION;
259 END IF;
260
261 x_return_status := l_return_status;
262 EXCEPTION
263 WHEN G_EXCEPTION_HALT_VALIDATION THEN
264 x_return_status := l_return_status;
265 WHEN OTHERS THEN
266 -- store SQL error message on message stack for caller
267 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
268 -- notify caller of an UNEXPECTED error
269 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
270 END validate_object_version_number;
271
272 ---------------------------------------------------------------------------
273 -- PROCEDURE Validate_rle_code
274 ---------------------------------------------------------------------------
275 PROCEDURE validate_rle_code(
276 p_srev_rec IN srev_rec_type,
277 x_return_status OUT NOCOPY VARCHAR2) IS
278 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
279 BEGIN
280 IF p_srev_rec.rle_code = OKC_API.G_MISS_CHAR OR
281 p_srev_rec.rle_code IS NULL
282 THEN
283 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rle_code');
284 l_return_status := OKC_API.G_RET_STS_ERROR;
285 END IF;
286 x_return_status := l_return_status;
287 EXCEPTION
288 WHEN OTHERS THEN
289 -- store SQL error message on message stack for caller
290 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
291 -- notify caller of an UNEXPECTED error
292 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
293 END validate_rle_code;
294
295 ---------------------------------------------------------------------------
296 -- PROCEDURE Validate_scs_code
297 ---------------------------------------------------------------------------
298 PROCEDURE validate_scs_code(
299 p_srev_rec IN srev_rec_type,
300 x_return_status OUT NOCOPY VARCHAR2) IS
301 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
302 BEGIN
303 IF p_srev_rec.scs_code = OKC_API.G_MISS_CHAR OR
304 p_srev_rec.scs_code IS NULL
305 THEN
306 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'scs_code');
307 l_return_status := OKC_API.G_RET_STS_ERROR;
308 END IF;
309 x_return_status := l_return_status;
310 EXCEPTION
311 WHEN OTHERS THEN
312 -- store SQL error message on message stack for caller
313 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
314 -- notify caller of an UNEXPECTED error
315 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
316 END validate_scs_code;
317
318 ---------------------------------------------------------------------------
319 -- PROCEDURE Validate_start_date
320 ---------------------------------------------------------------------------
321 PROCEDURE validate_start_date(
322 p_srev_rec IN srev_rec_type,
323 x_return_status OUT NOCOPY VARCHAR2) IS
324 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
325 BEGIN
326 IF p_srev_rec.start_date = OKC_API.G_MISS_DATE OR
327 p_srev_rec.start_date IS NULL
328 THEN
329 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'start_date');
330 l_return_status := OKC_API.G_RET_STS_ERROR;
331 END IF;
332 x_return_status := l_return_status;
333 EXCEPTION
334 WHEN OTHERS THEN
335 -- store SQL error message on message stack for caller
336 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
337 -- notify caller of an UNEXPECTED error
338 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
339 END validate_start_date;
340 --------------------------------------------------------------------------
341 -- PROCEDURE validate_access_level
342 ---------------------------------------------------------------------------
343 PROCEDURE validate_access_level(
344 p_srev_rec IN srev_rec_type,
345 x_return_status OUT NOCOPY VARCHAR2) IS
346 BEGIN
347 x_return_status :=OKC_API.G_RET_STS_SUCCESS;
348
349 IF (p_srev_rec.access_level = OKC_API.G_MISS_CHAR OR
350 p_srev_rec.access_level IS NULL)
351 THEN
352
353 raise G_EXCEPTION_HALT_VALIDATION;
354 END IF;
355 IF upper(p_srev_rec.access_level) not in ('S','E','U') Then
356 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
357 p_msg_name => g_invalid_value,
358 p_token1 => g_col_name_token,
359 p_token1_value => 'Access Level');
360 x_return_status :=OKC_API.G_RET_STS_ERROR;
361 End If;
362 exception
363 when G_EXCEPTION_HALT_VALIDATION then
364 null;
365 when OTHERS then
366 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
367 p_msg_name => g_unexpected_error,
368 p_token1 => g_sqlcode_token,
369 p_token1_value => sqlcode,
370 p_token2 => g_sqlerrm_token,
371 p_token2_value => sqlerrm );
372 x_return_status :=OKC_API.G_RET_STS_UNEXP_ERROR;
373
374 END validate_access_level;
375 ---------------------------------------------------------------------------
376 -- PROCEDURE Validate_Attributes
377 ---------------------------------------------------------------------------
378 --------------------------------------------------
379 -- Validate_Attributes for:OKC_SUBCLASS_ROLES_V --
380 --------------------------------------------------
381 FUNCTION Validate_Attributes (
382 p_srev_rec IN srev_rec_type
383 ) RETURN VARCHAR2 IS
384 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
385 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
386 ---------------------------------------------------------------------------------------
387 --Attribute Level Validation Procedure Calls Starts(Modification on TAPI generated Code.
388 ---------------------------------------------------------------------------------------
389 BEGIN
390 VALIDATE_id(p_srev_rec, l_return_status);
391 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
392 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
393 x_return_status := l_return_status;
394 RAISE G_EXCEPTION_HALT_VALIDATION;
395 ELSE
396 x_return_status := l_return_status; -- record that there was an error
397 END IF;
398 END IF;
399
400 VALIDATE_object_version_number(p_srev_rec, l_return_status);
401 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
402 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
403 x_return_status := l_return_status;
404 RAISE G_EXCEPTION_HALT_VALIDATION;
405 ELSE
406 x_return_status := l_return_status; -- record that there was an error
407 END IF;
408 END IF;
409
410 VALIDATE_rle_code(p_srev_rec, l_return_status);
411 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
412 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
413 x_return_status := l_return_status;
414 RAISE G_EXCEPTION_HALT_VALIDATION;
415 ELSE
416 x_return_status := l_return_status; -- record that there was an error
417 END IF;
418 END IF;
419
420 VALIDATE_scs_code(p_srev_rec, l_return_status);
421 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
422 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
423 x_return_status := l_return_status;
424 RAISE G_EXCEPTION_HALT_VALIDATION;
425 ELSE
426 x_return_status := l_return_status; -- record that there was an error
427 END IF;
428 END IF;
429
430 VALIDATE_start_date(p_srev_rec, l_return_status);
431 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
432 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
433 x_return_status := l_return_status;
434 RAISE G_EXCEPTION_HALT_VALIDATION;
435 ELSE
436 x_return_status := l_return_status; -- record that there was an error
437 END IF;
438 END IF;
439
440 VALIDATE_access_level(p_srev_rec, l_return_status);
441 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
442 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
443 x_return_status := l_return_status;
444 RAISE G_EXCEPTION_HALT_VALIDATION;
445 ELSE
446 x_return_status := l_return_status; -- record that there was an error
447 END IF;
448 END IF;
449 RETURN(x_return_status);
450 EXCEPTION
451 WHEN G_EXCEPTION_HALT_VALIDATION THEN
452 return(x_return_status);
453 WHEN OTHERS THEN
454 -- store SQL error message on message stack for caller
455 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
456 -- notify caller of an UNEXPECTED error
457 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
458 return(x_return_status);
459 ---------------------------------------------------------------------------------------
460 --Attribute Level Validation Procedure Calls Ends(Modification on TAPI generated Code.)--
461 ---------------------------------------------------------------------------------------
462
463 END Validate_Attributes;
464
465 ---------------------------------------------------------------------------
466 -- PROCEDURE Validate_Record
467 ---------------------------------------------------------------------------
468 ----------------------------------------------
469 -- Validate_Record for:OKC_SUBCLASS_ROLES_V --
470 ----------------------------------------------
471 FUNCTION Validate_Record (
472 p_srev_rec IN srev_rec_type
473 ) RETURN VARCHAR2 IS
474 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
475 ------------------------------------
476 -- FUNCTION validate_foreign_keys --
477 ------------------------------------
478 FUNCTION validate_foreign_keys (
479 p_srev_rec IN srev_rec_type
480 ) RETURN VARCHAR2 IS
484 FROM Okc_Subclasses_V
481 item_not_found_error EXCEPTION;
482 CURSOR okc_scsv_pk_csr (p_code IN VARCHAR2) IS
483 SELECT 'x'
485 WHERE okc_subclasses_v.code = p_code;
486 CURSOR fnd_lookups_pk_csr (p_lookup_code IN VARCHAR2) IS
487 SELECT 'x'
488 FROM Fnd_Lookups
489 WHERE fnd_lookups.lookup_code = p_lookup_code
490 AND fnd_lookups.lookup_type = 'OKC_ROLE';
491 l_dummy VARCHAR2(1);
492 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
493 l_row_notfound BOOLEAN := TRUE;
494 BEGIN
495 IF (p_srev_rec.SCS_CODE IS NOT NULL)
496 THEN
497 OPEN okc_scsv_pk_csr(p_srev_rec.SCS_CODE);
498 FETCH okc_scsv_pk_csr INTO l_dummy;
499 l_row_notfound := okc_scsv_pk_csr%NOTFOUND;
500 CLOSE okc_scsv_pk_csr;
501 IF (l_row_notfound) THEN
502 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SCS_CODE');
503 RAISE item_not_found_error;
504 END IF;
505 END IF;
506 IF (p_srev_rec.RLE_CODE IS NOT NULL)
507 THEN
508 OPEN fnd_lookups_pk_csr(p_srev_rec.RLE_CODE);
509 FETCH fnd_lookups_pk_csr INTO l_dummy;
510 l_row_notfound := fnd_lookups_pk_csr%NOTFOUND;
511 CLOSE fnd_lookups_pk_csr;
512 IF (l_row_notfound) THEN
513 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RLE_CODE');
514 RAISE item_not_found_error;
515 END IF;
516 END IF;
517 RETURN (l_return_status);
518 EXCEPTION
519 WHEN item_not_found_error THEN
520 l_return_status := OKC_API.G_RET_STS_ERROR;
521 RETURN (l_return_status);
522 END validate_foreign_keys;
523 ----------------------------------------------------
524 FUNCTION validate_unique_keys (
525 p_srev_rec IN srev_rec_type
526 ) RETURN VARCHAR2 IS
527 unique_key_error EXCEPTION;
528 CURSOR c1 (p_id IN okc_subclass_roles_v.id%TYPE,
529 p_scs_code IN okc_subclass_roles_v.scs_code%TYPE,
530 p_rle_code IN okc_subclass_roles_v.rle_code%TYPE) IS
531 SELECT 'x'
532 FROM Okc_Subclass_Roles_V
533 WHERE scs_code = p_scs_code
534 AND rle_code = p_rle_code
535 AND ((p_id IS NULL)
536 OR (p_id IS NOT NULL
537 AND id <> p_id));
538 l_dummy VARCHAR2(1);
539 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
540 l_row_found BOOLEAN := FALSE;
541 BEGIN
542 IF (p_srev_rec.SCS_CODE IS NOT NULL AND
543 p_srev_rec.RLE_CODE IS NOT NULL) THEN
544 OPEN c1(p_srev_rec.ID,
545 p_srev_rec.SCS_CODE,
546 p_srev_rec.RLE_CODE);
547 FETCH c1 INTO l_dummy;
548 l_row_found := c1%FOUND;
549 CLOSE c1;
550 IF (l_row_found) THEN
551 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'SCS_CODE');
552 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'RLE_CODE');
553 RAISE unique_key_error;
554 END IF;
555 END IF;
556 RETURN (l_return_status);
557 EXCEPTION
558 WHEN unique_key_error THEN
559 l_return_status := OKC_API.G_RET_STS_ERROR;
560 RETURN (l_return_status);
561 END validate_unique_keys;
562 BEGIN
563 IF p_srev_rec.start_date IS NOT NULL AND
564 p_srev_rec.end_date IS NOT NULL THEN
565 IF p_srev_rec.end_date < p_srev_rec.start_date THEN
566 OKC_API.set_message(G_APP_NAME, 'OKC_INVALID_END_DATE');
567 l_return_status := OKC_API.G_RET_STS_ERROR;
568 raise G_EXCEPTION_HALT_VALIDATION;
569 END IF;
570 END IF;
571 l_return_status := validate_foreign_keys (p_srev_rec);
572 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
573 raise G_EXCEPTION_HALT_VALIDATION;
574 END IF;
575 l_return_status := validate_unique_keys (p_srev_rec);
576 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
577 raise G_EXCEPTION_HALT_VALIDATION;
578 END IF;
579 RETURN (l_return_status);
580 EXCEPTION
581 WHEN G_EXCEPTION_HALT_VALIDATION THEN
582 RETURN(l_return_status);
583 WHEN OTHERS THEN
584 OKC_API.set_message(p_app_name => g_app_name,
585 p_msg_name => g_unexpected_error,
586 p_token1 => g_sqlcode_token,
587 p_token1_value => sqlcode,
588 p_token2 => g_sqlerrm_token,
589 p_token2_value => sqlerrm);
590 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
591 RETURN(l_return_status);
592 END Validate_Record;
593
594 ---------------------------------------------------------------------------
595 -- PROCEDURE Migrate
596 ---------------------------------------------------------------------------
597 PROCEDURE migrate (
598 p_from IN srev_rec_type,
599 p_to OUT NOCOPY sre_rec_type
600 ) IS
601 BEGIN
602 p_to.id := p_from.id;
603 p_to.scs_code := p_from.scs_code;
604 p_to.rle_code := p_from.rle_code;
605 p_to.object_version_number := p_from.object_version_number;
606 p_to.created_by := p_from.created_by;
607 p_to.creation_date := p_from.creation_date;
608 p_to.last_updated_by := p_from.last_updated_by;
609 p_to.last_update_date := p_from.last_update_date;
610 p_to.last_update_login := p_from.last_update_login;
611 p_to.start_date := p_from.start_date;
612 p_to.end_date := p_from.end_date;
613 p_to.access_level := p_from.access_level;
614 END migrate;
615 PROCEDURE migrate (
616 p_from IN sre_rec_type,
617 p_to IN OUT NOCOPY srev_rec_type
618 ) IS
619 BEGIN
620 p_to.id := p_from.id;
621 p_to.scs_code := p_from.scs_code;
622 p_to.rle_code := p_from.rle_code;
623 p_to.object_version_number := p_from.object_version_number;
624 p_to.created_by := p_from.created_by;
625 p_to.creation_date := p_from.creation_date;
626 p_to.last_updated_by := p_from.last_updated_by;
627 p_to.last_update_date := p_from.last_update_date;
628 p_to.last_update_login := p_from.last_update_login;
629 p_to.start_date := p_from.start_date;
630 p_to.end_date := p_from.end_date;
631 p_to.access_level := p_from.access_level;
632 END migrate;
633
634 ---------------------------------------------------------------------------
635 -- PROCEDURE validate_row
636 ---------------------------------------------------------------------------
637 -------------------------------------------
638 -- validate_row for:OKC_SUBCLASS_ROLES_V --
639 -------------------------------------------
640 PROCEDURE validate_row(
641 p_api_version IN NUMBER,
642 p_init_msg_list IN VARCHAR2,
643 x_return_status OUT NOCOPY VARCHAR2,
644 x_msg_count OUT NOCOPY NUMBER,
645 x_msg_data OUT NOCOPY VARCHAR2,
646 p_srev_rec IN srev_rec_type) IS
647
648 l_api_version CONSTANT NUMBER := 1;
649 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
650 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
651 l_srev_rec srev_rec_type := p_srev_rec;
652 l_sre_rec sre_rec_type;
653 BEGIN
654 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
655 G_PKG_NAME,
656 p_init_msg_list,
657 l_api_version,
658 p_api_version,
659 '_PVT',
660 x_return_status);
661 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
662 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
663 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
664 RAISE OKC_API.G_EXCEPTION_ERROR;
665 END IF;
666 --- Validate all non-missing attributes (Item Level Validation)
667 l_return_status := Validate_Attributes(l_srev_rec);
668 --- If any errors happen abort API
669 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
670 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
671 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
672 RAISE OKC_API.G_EXCEPTION_ERROR;
673 END IF;
674 l_return_status := Validate_Record(l_srev_rec);
675 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
676 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
677 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
678 RAISE OKC_API.G_EXCEPTION_ERROR;
679 END IF;
680 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
681 EXCEPTION
682 WHEN OKC_API.G_EXCEPTION_ERROR THEN
683 x_return_status := OKC_API.HANDLE_EXCEPTIONS
684 (
685 l_api_name,
686 G_PKG_NAME,
687 'OKC_API.G_RET_STS_ERROR',
688 x_msg_count,
689 x_msg_data,
690 '_PVT'
691 );
692 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
693 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
694 (
695 l_api_name,
696 G_PKG_NAME,
697 'OKC_API.G_RET_STS_UNEXP_ERROR',
698 x_msg_count,
699 x_msg_data,
700 '_PVT'
701 );
702 WHEN OTHERS THEN
703 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
704 (
705 l_api_name,
706 G_PKG_NAME,
707 'OTHERS',
708 x_msg_count,
709 x_msg_data,
710 '_PVT'
711 );
712 END validate_row;
713 ------------------------------------------
714 -- PL/SQL TBL validate_row for:SREV_TBL --
715 ------------------------------------------
716 PROCEDURE validate_row(
717 p_api_version IN NUMBER,
718 p_init_msg_list IN VARCHAR2,
719 x_return_status OUT NOCOPY VARCHAR2,
720 x_msg_count OUT NOCOPY NUMBER,
721 x_msg_data OUT NOCOPY VARCHAR2,
722 p_srev_tbl IN srev_tbl_type) IS
723
724 l_api_version CONSTANT NUMBER := 1;
728 BEGIN
725 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
726 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
727 i NUMBER := 0;
729 OKC_API.init_msg_list(p_init_msg_list);
730 -- Make sure PL/SQL table has records in it before passing
731 IF (p_srev_tbl.COUNT > 0) THEN
732 i := p_srev_tbl.FIRST;
733 LOOP
734 validate_row (
735 p_api_version => p_api_version,
736 p_init_msg_list => OKC_API.G_FALSE,
737 x_return_status => x_return_status,
738 x_msg_count => x_msg_count,
739 x_msg_data => x_msg_data,
740 p_srev_rec => p_srev_tbl(i));
741 EXIT WHEN (i = p_srev_tbl.LAST);
742 i := p_srev_tbl.NEXT(i);
743 END LOOP;
744 END IF;
745 EXCEPTION
746 WHEN OKC_API.G_EXCEPTION_ERROR THEN
747 x_return_status := OKC_API.HANDLE_EXCEPTIONS
748 (
749 l_api_name,
750 G_PKG_NAME,
751 'OKC_API.G_RET_STS_ERROR',
752 x_msg_count,
753 x_msg_data,
754 '_PVT'
755 );
756 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
757 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
758 (
759 l_api_name,
760 G_PKG_NAME,
761 'OKC_API.G_RET_STS_UNEXP_ERROR',
762 x_msg_count,
763 x_msg_data,
764 '_PVT'
765 );
766 WHEN OTHERS THEN
767 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
768 (
769 l_api_name,
770 G_PKG_NAME,
771 'OTHERS',
772 x_msg_count,
773 x_msg_data,
774 '_PVT'
775 );
776 END validate_row;
777
778 ---------------------------------------------------------------------------
779 -- PROCEDURE insert_row
780 ---------------------------------------------------------------------------
781 ---------------------------------------
782 -- insert_row for:OKC_SUBCLASS_ROLES --
783 ---------------------------------------
784 PROCEDURE insert_row(
785 p_init_msg_list IN VARCHAR2,
786 x_return_status OUT NOCOPY VARCHAR2,
787 x_msg_count OUT NOCOPY NUMBER,
788 x_msg_data OUT NOCOPY VARCHAR2,
789 p_sre_rec IN sre_rec_type,
790 x_sre_rec OUT NOCOPY sre_rec_type) IS
791
792 l_api_version CONSTANT NUMBER := 1;
793 l_api_name CONSTANT VARCHAR2(30) := 'ROLES_insert_row';
794 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
795 l_sre_rec sre_rec_type := p_sre_rec;
796 l_def_sre_rec sre_rec_type;
797 -------------------------------------------
798 -- Set_Attributes for:OKC_SUBCLASS_ROLES --
799 -------------------------------------------
800 FUNCTION Set_Attributes (
801 p_sre_rec IN sre_rec_type,
802 x_sre_rec OUT NOCOPY sre_rec_type
803 ) RETURN VARCHAR2 IS
804 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
805 BEGIN
806 x_sre_rec := p_sre_rec;
807 RETURN(l_return_status);
808 END Set_Attributes;
809 BEGIN
810 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
811 p_init_msg_list,
812 '_PVT',
813 x_return_status);
814 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
815 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
816 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
817 RAISE OKC_API.G_EXCEPTION_ERROR;
818 END IF;
819 --- Setting item attributes
820 l_return_status := Set_Attributes(
821 p_sre_rec, -- IN
822 l_sre_rec); -- OUT
823 --- If any errors happen abort API
824 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
825 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
826 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
827 RAISE OKC_API.G_EXCEPTION_ERROR;
828 END IF;
829 INSERT INTO OKC_SUBCLASS_ROLES(
830 id,
831 scs_code,
832 rle_code,
833 object_version_number,
834 created_by,
835 creation_date,
836 last_updated_by,
837 last_update_date,
838 last_update_login,
839 start_date,
840 end_date,
841 access_level)
842 VALUES (
843 l_sre_rec.id,
844 l_sre_rec.scs_code,
845 l_sre_rec.rle_code,
846 l_sre_rec.object_version_number,
847 l_sre_rec.created_by,
848 l_sre_rec.creation_date,
849 l_sre_rec.last_updated_by,
850 l_sre_rec.last_update_date,
851 l_sre_rec.last_update_login,
852 l_sre_rec.start_date,
853 l_sre_rec.end_date,
854 l_sre_rec.access_level);
855 -- Set OUT values
856 x_sre_rec := l_sre_rec;
857 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
858 EXCEPTION
859 WHEN OKC_API.G_EXCEPTION_ERROR THEN
860 x_return_status := OKC_API.HANDLE_EXCEPTIONS
861 (
862 l_api_name,
863 G_PKG_NAME,
864 'OKC_API.G_RET_STS_ERROR',
865 x_msg_count,
866 x_msg_data,
867 '_PVT'
868 );
869 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
870 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
871 (
875 x_msg_count,
872 l_api_name,
873 G_PKG_NAME,
874 'OKC_API.G_RET_STS_UNEXP_ERROR',
876 x_msg_data,
877 '_PVT'
878 );
879 WHEN OTHERS THEN
880 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
881 (
882 l_api_name,
883 G_PKG_NAME,
884 'OTHERS',
885 x_msg_count,
886 x_msg_data,
887 '_PVT'
888 );
889 END insert_row;
890 -----------------------------------------
891 -- insert_row for:OKC_SUBCLASS_ROLES_V --
892 -----------------------------------------
893 PROCEDURE insert_row(
894 p_api_version IN NUMBER,
895 p_init_msg_list IN VARCHAR2,
896 x_return_status OUT NOCOPY VARCHAR2,
897 x_msg_count OUT NOCOPY NUMBER,
898 x_msg_data OUT NOCOPY VARCHAR2,
899 p_srev_rec IN srev_rec_type,
900 x_srev_rec OUT NOCOPY srev_rec_type) IS
901
902 l_api_version CONSTANT NUMBER := 1;
903 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
904 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
905 l_srev_rec srev_rec_type;
906 l_def_srev_rec srev_rec_type;
907 l_sre_rec sre_rec_type;
908 lx_sre_rec sre_rec_type;
909 -------------------------------
910 -- FUNCTION fill_who_columns --
911 -------------------------------
912 FUNCTION fill_who_columns (
913 p_srev_rec IN srev_rec_type
914 ) RETURN srev_rec_type IS
915 l_srev_rec srev_rec_type := p_srev_rec;
916 BEGIN
917 l_srev_rec.CREATION_DATE := SYSDATE;
918 l_srev_rec.CREATED_BY := FND_GLOBAL.USER_ID;
919 l_srev_rec.LAST_UPDATE_DATE := l_srev_rec.CREATION_DATE;
920 l_srev_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
921 l_srev_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
922 RETURN(l_srev_rec);
923 END fill_who_columns;
924 ---------------------------------------------
925 -- Set_Attributes for:OKC_SUBCLASS_ROLES_V --
926 ---------------------------------------------
927 FUNCTION Set_Attributes (
928 p_srev_rec IN srev_rec_type,
929 x_srev_rec OUT NOCOPY srev_rec_type
930 ) RETURN VARCHAR2 IS
931 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
932 BEGIN
933 x_srev_rec := p_srev_rec;
934 x_srev_rec.OBJECT_VERSION_NUMBER := 1;
935 RETURN(l_return_status);
936 END Set_Attributes;
937 BEGIN
938 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
939 G_PKG_NAME,
940 p_init_msg_list,
941 l_api_version,
942 p_api_version,
943 '_PVT',
944 x_return_status);
945 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
946 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
947 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
948 RAISE OKC_API.G_EXCEPTION_ERROR;
949 END IF;
950 l_srev_rec := null_out_defaults(p_srev_rec);
951 -- Set primary key value
952 l_srev_rec.ID := get_seq_id;
953 --- Setting item attributes
954 l_return_status := Set_Attributes(
955 l_srev_rec, -- IN
956 l_def_srev_rec); -- OUT
957 --- If any errors happen abort API
958 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
959 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
960 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
961 RAISE OKC_API.G_EXCEPTION_ERROR;
962 END IF;
963 l_def_srev_rec := fill_who_columns(l_def_srev_rec);
964 --- Validate all non-missing attributes (Item Level Validation)
965 l_return_status := Validate_Attributes(l_def_srev_rec);
966 --- If any errors happen abort API
967 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
968 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
969 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
970 RAISE OKC_API.G_EXCEPTION_ERROR;
971 END IF;
972 l_return_status := Validate_Record(l_def_srev_rec);
973 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
974 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
975 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
976 RAISE OKC_API.G_EXCEPTION_ERROR;
977 END IF;
978 --------------------------------------
979 -- Move VIEW record to "Child" records
980 --------------------------------------
981 migrate(l_def_srev_rec, l_sre_rec);
982 --------------------------------------------
983 -- Call the INSERT_ROW for each child record
984 --------------------------------------------
985 insert_row(
986 p_init_msg_list,
987 x_return_status,
988 x_msg_count,
989 x_msg_data,
990 l_sre_rec,
991 lx_sre_rec
992 );
993 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
994 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
995 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
996 RAISE OKC_API.G_EXCEPTION_ERROR;
997 END IF;
998 migrate(lx_sre_rec, l_def_srev_rec);
999 -- Set OUT values
1000 x_srev_rec := l_def_srev_rec;
1001 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1002 EXCEPTION
1003 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1004 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1005 (
1006 l_api_name,
1010 x_msg_data,
1007 G_PKG_NAME,
1008 'OKC_API.G_RET_STS_ERROR',
1009 x_msg_count,
1011 '_PVT'
1012 );
1013 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1014 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1015 (
1016 l_api_name,
1017 G_PKG_NAME,
1018 'OKC_API.G_RET_STS_UNEXP_ERROR',
1019 x_msg_count,
1020 x_msg_data,
1021 '_PVT'
1022 );
1023 WHEN OTHERS THEN
1024 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1025 (
1026 l_api_name,
1027 G_PKG_NAME,
1028 'OTHERS',
1029 x_msg_count,
1030 x_msg_data,
1031 '_PVT'
1032 );
1033 END insert_row;
1034 ----------------------------------------
1035 -- PL/SQL TBL insert_row for:SREV_TBL --
1036 ----------------------------------------
1037 PROCEDURE insert_row(
1038 p_api_version IN NUMBER,
1039 p_init_msg_list IN VARCHAR2,
1040 x_return_status OUT NOCOPY VARCHAR2,
1041 x_msg_count OUT NOCOPY NUMBER,
1042 x_msg_data OUT NOCOPY VARCHAR2,
1043 p_srev_tbl IN srev_tbl_type,
1044 x_srev_tbl OUT NOCOPY srev_tbl_type) IS
1045
1046 l_api_version CONSTANT NUMBER := 1;
1047 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1048 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1049 i NUMBER := 0;
1050 BEGIN
1051 OKC_API.init_msg_list(p_init_msg_list);
1052 -- Make sure PL/SQL table has records in it before passing
1053 IF (p_srev_tbl.COUNT > 0) THEN
1054 i := p_srev_tbl.FIRST;
1055 LOOP
1056 insert_row (
1057 p_api_version => p_api_version,
1058 p_init_msg_list => OKC_API.G_FALSE,
1059 x_return_status => x_return_status,
1060 x_msg_count => x_msg_count,
1061 x_msg_data => x_msg_data,
1062 p_srev_rec => p_srev_tbl(i),
1063 x_srev_rec => x_srev_tbl(i));
1064 EXIT WHEN (i = p_srev_tbl.LAST);
1065 i := p_srev_tbl.NEXT(i);
1066 END LOOP;
1067 END IF;
1068 EXCEPTION
1069 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1070 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1071 (
1072 l_api_name,
1073 G_PKG_NAME,
1074 'OKC_API.G_RET_STS_ERROR',
1075 x_msg_count,
1076 x_msg_data,
1077 '_PVT'
1078 );
1079 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1080 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1081 (
1082 l_api_name,
1083 G_PKG_NAME,
1084 'OKC_API.G_RET_STS_UNEXP_ERROR',
1085 x_msg_count,
1086 x_msg_data,
1087 '_PVT'
1088 );
1089 WHEN OTHERS THEN
1090 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1091 (
1092 l_api_name,
1093 G_PKG_NAME,
1094 'OTHERS',
1095 x_msg_count,
1096 x_msg_data,
1097 '_PVT'
1098 );
1099 END insert_row;
1100
1101 ---------------------------------------------------------------------------
1102 -- PROCEDURE lock_row
1103 ---------------------------------------------------------------------------
1104 -------------------------------------
1105 -- lock_row for:OKC_SUBCLASS_ROLES --
1106 -------------------------------------
1107 PROCEDURE lock_row(
1108 p_init_msg_list IN VARCHAR2,
1109 x_return_status OUT NOCOPY VARCHAR2,
1110 x_msg_count OUT NOCOPY NUMBER,
1111 x_msg_data OUT NOCOPY VARCHAR2,
1112 p_sre_rec IN sre_rec_type) IS
1113
1114 E_Resource_Busy EXCEPTION;
1115 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1116 CURSOR lock_csr (p_sre_rec IN sre_rec_type) IS
1117 SELECT OBJECT_VERSION_NUMBER
1118 FROM OKC_SUBCLASS_ROLES
1119 WHERE ID = p_sre_rec.id
1120 AND OBJECT_VERSION_NUMBER = p_sre_rec.object_version_number
1121 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1122
1123 CURSOR lchk_csr (p_sre_rec IN sre_rec_type) IS
1124 SELECT OBJECT_VERSION_NUMBER
1125 FROM OKC_SUBCLASS_ROLES
1126 WHERE ID = p_sre_rec.id;
1127 l_api_version CONSTANT NUMBER := 1;
1128 l_api_name CONSTANT VARCHAR2(30) := 'ROLES_lock_row';
1129 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1130 l_object_version_number OKC_SUBCLASS_ROLES.OBJECT_VERSION_NUMBER%TYPE;
1131 lc_object_version_number OKC_SUBCLASS_ROLES.OBJECT_VERSION_NUMBER%TYPE;
1132 l_row_notfound BOOLEAN := FALSE;
1133 lc_row_notfound BOOLEAN := FALSE;
1134 BEGIN
1135 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1136 p_init_msg_list,
1137 '_PVT',
1138 x_return_status);
1139 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1140 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1141 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1142 RAISE OKC_API.G_EXCEPTION_ERROR;
1143 END IF;
1144 BEGIN
1145 OPEN lock_csr(p_sre_rec);
1146 FETCH lock_csr INTO l_object_version_number;
1147 l_row_notfound := lock_csr%NOTFOUND;
1148 CLOSE lock_csr;
1149 EXCEPTION
1150 WHEN E_Resource_Busy THEN
1151 IF (lock_csr%ISOPEN) THEN
1155 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1152 CLOSE lock_csr;
1153 END IF;
1154 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1156 END;
1157
1158 IF ( l_row_notfound ) THEN
1159 OPEN lchk_csr(p_sre_rec);
1160 FETCH lchk_csr INTO lc_object_version_number;
1161 lc_row_notfound := lchk_csr%NOTFOUND;
1162 CLOSE lchk_csr;
1163 END IF;
1164 IF (lc_row_notfound) THEN
1165 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1166 RAISE OKC_API.G_EXCEPTION_ERROR;
1167 ELSIF lc_object_version_number > p_sre_rec.object_version_number THEN
1168 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1169 RAISE OKC_API.G_EXCEPTION_ERROR;
1170 ELSIF lc_object_version_number <> p_sre_rec.object_version_number THEN
1171 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1172 RAISE OKC_API.G_EXCEPTION_ERROR;
1173 ELSIF lc_object_version_number = -1 THEN
1174 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1175 RAISE OKC_API.G_EXCEPTION_ERROR;
1176 END IF;
1177 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1178 EXCEPTION
1179 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1180 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1181 (
1182 l_api_name,
1183 G_PKG_NAME,
1184 'OKC_API.G_RET_STS_ERROR',
1185 x_msg_count,
1186 x_msg_data,
1187 '_PVT'
1188 );
1189 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1190 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1191 (
1192 l_api_name,
1193 G_PKG_NAME,
1194 'OKC_API.G_RET_STS_UNEXP_ERROR',
1195 x_msg_count,
1196 x_msg_data,
1197 '_PVT'
1198 );
1199 WHEN OTHERS THEN
1200 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1201 (
1202 l_api_name,
1203 G_PKG_NAME,
1204 'OTHERS',
1205 x_msg_count,
1206 x_msg_data,
1207 '_PVT'
1208 );
1209 END lock_row;
1210 ---------------------------------------
1211 -- lock_row for:OKC_SUBCLASS_ROLES_V --
1212 ---------------------------------------
1213 PROCEDURE lock_row(
1214 p_api_version IN NUMBER,
1215 p_init_msg_list IN VARCHAR2,
1216 x_return_status OUT NOCOPY VARCHAR2,
1217 x_msg_count OUT NOCOPY NUMBER,
1218 x_msg_data OUT NOCOPY VARCHAR2,
1219 p_srev_rec IN srev_rec_type) IS
1220
1221 l_api_version CONSTANT NUMBER := 1;
1222 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1223 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1224 l_sre_rec sre_rec_type;
1225 BEGIN
1226 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1227 G_PKG_NAME,
1228 p_init_msg_list,
1229 l_api_version,
1230 p_api_version,
1231 '_PVT',
1232 x_return_status);
1233 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1234 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1235 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1236 RAISE OKC_API.G_EXCEPTION_ERROR;
1237 END IF;
1238 --------------------------------------
1239 -- Move VIEW record to "Child" records
1240 --------------------------------------
1241 migrate(p_srev_rec, l_sre_rec);
1242 --------------------------------------------
1243 -- Call the LOCK_ROW for each child record
1244 --------------------------------------------
1245 lock_row(
1246 p_init_msg_list,
1247 x_return_status,
1248 x_msg_count,
1249 x_msg_data,
1250 l_sre_rec
1251 );
1252 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1253 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1254 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1255 RAISE OKC_API.G_EXCEPTION_ERROR;
1256 END IF;
1257 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1258 EXCEPTION
1259 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1260 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1261 (
1262 l_api_name,
1263 G_PKG_NAME,
1264 'OKC_API.G_RET_STS_ERROR',
1265 x_msg_count,
1266 x_msg_data,
1267 '_PVT'
1268 );
1269 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1270 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1271 (
1272 l_api_name,
1273 G_PKG_NAME,
1274 'OKC_API.G_RET_STS_UNEXP_ERROR',
1275 x_msg_count,
1276 x_msg_data,
1277 '_PVT'
1278 );
1279 WHEN OTHERS THEN
1280 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1281 (
1282 l_api_name,
1283 G_PKG_NAME,
1284 'OTHERS',
1285 x_msg_count,
1286 x_msg_data,
1287 '_PVT'
1288 );
1289 END lock_row;
1290 --------------------------------------
1291 -- PL/SQL TBL lock_row for:SREV_TBL --
1292 --------------------------------------
1293 PROCEDURE lock_row(
1294 p_api_version IN NUMBER,
1295 p_init_msg_list IN VARCHAR2,
1296 x_return_status OUT NOCOPY VARCHAR2,
1297 x_msg_count OUT NOCOPY NUMBER,
1298 x_msg_data OUT NOCOPY VARCHAR2,
1299 p_srev_tbl IN srev_tbl_type) IS
1300
1304 i NUMBER := 0;
1301 l_api_version CONSTANT NUMBER := 1;
1302 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1303 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1305 BEGIN
1306 OKC_API.init_msg_list(p_init_msg_list);
1307 -- Make sure PL/SQL table has records in it before passing
1308 IF (p_srev_tbl.COUNT > 0) THEN
1309 i := p_srev_tbl.FIRST;
1310 LOOP
1311 lock_row (
1312 p_api_version => p_api_version,
1313 p_init_msg_list => OKC_API.G_FALSE,
1314 x_return_status => x_return_status,
1315 x_msg_count => x_msg_count,
1316 x_msg_data => x_msg_data,
1317 p_srev_rec => p_srev_tbl(i));
1318 EXIT WHEN (i = p_srev_tbl.LAST);
1319 i := p_srev_tbl.NEXT(i);
1320 END LOOP;
1321 END IF;
1322 EXCEPTION
1323 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1324 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1325 (
1326 l_api_name,
1327 G_PKG_NAME,
1328 'OKC_API.G_RET_STS_ERROR',
1329 x_msg_count,
1330 x_msg_data,
1331 '_PVT'
1332 );
1333 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1334 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1335 (
1336 l_api_name,
1337 G_PKG_NAME,
1338 'OKC_API.G_RET_STS_UNEXP_ERROR',
1339 x_msg_count,
1340 x_msg_data,
1341 '_PVT'
1342 );
1343 WHEN OTHERS THEN
1344 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1345 (
1346 l_api_name,
1347 G_PKG_NAME,
1348 'OTHERS',
1349 x_msg_count,
1350 x_msg_data,
1351 '_PVT'
1352 );
1353 END lock_row;
1354
1355 ---------------------------------------------------------------------------
1356 -- PROCEDURE update_row
1357 ---------------------------------------------------------------------------
1358 ---------------------------------------
1359 -- update_row for:OKC_SUBCLASS_ROLES --
1360 ---------------------------------------
1361 PROCEDURE update_row(
1362 p_init_msg_list IN VARCHAR2,
1363 x_return_status OUT NOCOPY VARCHAR2,
1364 x_msg_count OUT NOCOPY NUMBER,
1365 x_msg_data OUT NOCOPY VARCHAR2,
1366 p_sre_rec IN sre_rec_type,
1367 x_sre_rec OUT NOCOPY sre_rec_type) IS
1368
1369 l_api_version CONSTANT NUMBER := 1;
1370 l_api_name CONSTANT VARCHAR2(30) := 'ROLES_update_row';
1371 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1372 l_sre_rec sre_rec_type := p_sre_rec;
1373 l_def_sre_rec sre_rec_type;
1374 l_row_notfound BOOLEAN := TRUE;
1375 ----------------------------------
1376 -- FUNCTION populate_new_record --
1377 ----------------------------------
1378 FUNCTION populate_new_record (
1379 p_sre_rec IN sre_rec_type,
1380 x_sre_rec OUT NOCOPY sre_rec_type
1381 ) RETURN VARCHAR2 IS
1382 l_sre_rec sre_rec_type;
1383 l_row_notfound BOOLEAN := TRUE;
1384 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1385 BEGIN
1386 x_sre_rec := p_sre_rec;
1387 -- Get current database values
1388 l_sre_rec := get_rec(p_sre_rec, l_row_notfound);
1389 IF (l_row_notfound) THEN
1390 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1391 END IF;
1392 IF (x_sre_rec.id = OKC_API.G_MISS_NUM)
1393 THEN
1394 x_sre_rec.id := l_sre_rec.id;
1395 END IF;
1396 IF (x_sre_rec.scs_code = OKC_API.G_MISS_CHAR)
1397 THEN
1398 x_sre_rec.scs_code := l_sre_rec.scs_code;
1399 END IF;
1400 IF (x_sre_rec.rle_code = OKC_API.G_MISS_CHAR)
1401 THEN
1402 x_sre_rec.rle_code := l_sre_rec.rle_code;
1403 END IF;
1404 IF (x_sre_rec.object_version_number = OKC_API.G_MISS_NUM)
1405 THEN
1406 x_sre_rec.object_version_number := l_sre_rec.object_version_number;
1407 END IF;
1408 IF (x_sre_rec.created_by = OKC_API.G_MISS_NUM)
1409 THEN
1410 x_sre_rec.created_by := l_sre_rec.created_by;
1411 END IF;
1412 IF (x_sre_rec.creation_date = OKC_API.G_MISS_DATE)
1413 THEN
1414 x_sre_rec.creation_date := l_sre_rec.creation_date;
1415 END IF;
1416 IF (x_sre_rec.last_updated_by = OKC_API.G_MISS_NUM)
1417 THEN
1418 x_sre_rec.last_updated_by := l_sre_rec.last_updated_by;
1419 END IF;
1420 IF (x_sre_rec.last_update_date = OKC_API.G_MISS_DATE)
1421 THEN
1422 x_sre_rec.last_update_date := l_sre_rec.last_update_date;
1423 END IF;
1424 IF (x_sre_rec.last_update_login = OKC_API.G_MISS_NUM)
1425 THEN
1426 x_sre_rec.last_update_login := l_sre_rec.last_update_login;
1427 END IF;
1428 IF (x_sre_rec.start_date = OKC_API.G_MISS_DATE)
1429 THEN
1430 x_sre_rec.start_date := l_sre_rec.start_date;
1431 END IF;
1432 IF (x_sre_rec.end_date = OKC_API.G_MISS_DATE)
1433 THEN
1434 x_sre_rec.end_date := l_sre_rec.end_date;
1435 END IF;
1436 IF (x_sre_rec.access_level = OKC_API.G_MISS_CHAR)
1437 THEN
1438 x_sre_rec.access_level := l_sre_rec.access_level;
1439 END IF;
1440 RETURN(l_return_status);
1441 END populate_new_record;
1442 -------------------------------------------
1446 p_sre_rec IN sre_rec_type,
1443 -- Set_Attributes for:OKC_SUBCLASS_ROLES --
1444 -------------------------------------------
1445 FUNCTION Set_Attributes (
1447 x_sre_rec OUT NOCOPY sre_rec_type
1448 ) RETURN VARCHAR2 IS
1449 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1450 BEGIN
1451 x_sre_rec := p_sre_rec;
1452 RETURN(l_return_status);
1453 END Set_Attributes;
1454 BEGIN
1455 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1456 p_init_msg_list,
1457 '_PVT',
1458 x_return_status);
1459 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1460 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1461 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1462 RAISE OKC_API.G_EXCEPTION_ERROR;
1463 END IF;
1464 --- Setting item attributes
1465 l_return_status := Set_Attributes(
1466 p_sre_rec, -- IN
1467 l_sre_rec); -- OUT
1468 --- If any errors happen abort API
1469 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1470 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1471 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1472 RAISE OKC_API.G_EXCEPTION_ERROR;
1473 END IF;
1474 l_return_status := populate_new_record(l_sre_rec, l_def_sre_rec);
1475 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1476 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1477 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1478 RAISE OKC_API.G_EXCEPTION_ERROR;
1479 END IF;
1480 UPDATE OKC_SUBCLASS_ROLES
1481 SET SCS_CODE = l_def_sre_rec.scs_code,
1482 RLE_CODE = l_def_sre_rec.rle_code,
1483 OBJECT_VERSION_NUMBER = l_def_sre_rec.object_version_number,
1484 CREATED_BY = l_def_sre_rec.created_by,
1485 CREATION_DATE = l_def_sre_rec.creation_date,
1486 LAST_UPDATED_BY = l_def_sre_rec.last_updated_by,
1487 LAST_UPDATE_DATE = l_def_sre_rec.last_update_date,
1488 LAST_UPDATE_LOGIN = l_def_sre_rec.last_update_login,
1489 START_DATE = l_def_sre_rec.start_date,
1490 END_DATE = l_def_sre_rec.end_date,
1491 ACCESS_LEVEL = l_def_sre_rec.access_level
1492 WHERE ID = l_def_sre_rec.id;
1493
1494 x_sre_rec := l_def_sre_rec;
1495 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1496 EXCEPTION
1497 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1498 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1499 (
1500 l_api_name,
1501 G_PKG_NAME,
1502 'OKC_API.G_RET_STS_ERROR',
1503 x_msg_count,
1504 x_msg_data,
1505 '_PVT'
1506 );
1507 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1508 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1509 (
1510 l_api_name,
1511 G_PKG_NAME,
1512 'OKC_API.G_RET_STS_UNEXP_ERROR',
1513 x_msg_count,
1514 x_msg_data,
1515 '_PVT'
1516 );
1517 WHEN OTHERS THEN
1518 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1519 (
1520 l_api_name,
1521 G_PKG_NAME,
1522 'OTHERS',
1523 x_msg_count,
1524 x_msg_data,
1525 '_PVT'
1526 );
1527 END update_row;
1528 -----------------------------------------
1529 -- update_row for:OKC_SUBCLASS_ROLES_V --
1530 -----------------------------------------
1531 PROCEDURE update_row(
1532 p_api_version IN NUMBER,
1533 p_init_msg_list IN VARCHAR2,
1534 x_return_status OUT NOCOPY VARCHAR2,
1535 x_msg_count OUT NOCOPY NUMBER,
1536 x_msg_data OUT NOCOPY VARCHAR2,
1537 p_srev_rec IN srev_rec_type,
1538 x_srev_rec OUT NOCOPY srev_rec_type) IS
1539
1540 l_api_version CONSTANT NUMBER := 1;
1541 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1542 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1543 l_srev_rec srev_rec_type := p_srev_rec;
1544 l_def_srev_rec srev_rec_type;
1545 l_sre_rec sre_rec_type;
1546 lx_sre_rec sre_rec_type;
1547 -------------------------------
1548 -- FUNCTION fill_who_columns --
1549 -------------------------------
1550 FUNCTION fill_who_columns (
1551 p_srev_rec IN srev_rec_type
1552 ) RETURN srev_rec_type IS
1553 l_srev_rec srev_rec_type := p_srev_rec;
1554 BEGIN
1555 l_srev_rec.LAST_UPDATE_DATE := SYSDATE;
1556 l_srev_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1557 l_srev_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1558 RETURN(l_srev_rec);
1559 END fill_who_columns;
1560 ----------------------------------
1561 -- FUNCTION populate_new_record --
1562 ----------------------------------
1563 FUNCTION populate_new_record (
1564 p_srev_rec IN srev_rec_type,
1565 x_srev_rec OUT NOCOPY srev_rec_type
1566 ) RETURN VARCHAR2 IS
1567 l_srev_rec srev_rec_type;
1568 l_row_notfound BOOLEAN := TRUE;
1569 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1570 BEGIN
1571 x_srev_rec := p_srev_rec;
1572 -- Get current database values
1573 l_srev_rec := get_rec(p_srev_rec, l_row_notfound);
1574 IF (l_row_notfound) THEN
1575 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1576 END IF;
1577 IF (x_srev_rec.id = OKC_API.G_MISS_NUM)
1578 THEN
1579 x_srev_rec.id := l_srev_rec.id;
1580 END IF;
1581 IF (x_srev_rec.object_version_number = OKC_API.G_MISS_NUM)
1582 THEN
1583 x_srev_rec.object_version_number := l_srev_rec.object_version_number;
1584 END IF;
1585 IF (x_srev_rec.rle_code = OKC_API.G_MISS_CHAR)
1586 THEN
1587 x_srev_rec.rle_code := l_srev_rec.rle_code;
1588 END IF;
1589 IF (x_srev_rec.scs_code = OKC_API.G_MISS_CHAR)
1590 THEN
1591 x_srev_rec.scs_code := l_srev_rec.scs_code;
1592 END IF;
1593 IF (x_srev_rec.created_by = OKC_API.G_MISS_NUM)
1594 THEN
1595 x_srev_rec.created_by := l_srev_rec.created_by;
1596 END IF;
1597 IF (x_srev_rec.creation_date = OKC_API.G_MISS_DATE)
1598 THEN
1599 x_srev_rec.creation_date := l_srev_rec.creation_date;
1600 END IF;
1601 IF (x_srev_rec.last_updated_by = OKC_API.G_MISS_NUM)
1602 THEN
1603 x_srev_rec.last_updated_by := l_srev_rec.last_updated_by;
1604 END IF;
1605 IF (x_srev_rec.last_update_date = OKC_API.G_MISS_DATE)
1606 THEN
1607 x_srev_rec.last_update_date := l_srev_rec.last_update_date;
1608 END IF;
1609 IF (x_srev_rec.last_update_login = OKC_API.G_MISS_NUM)
1610 THEN
1611 x_srev_rec.last_update_login := l_srev_rec.last_update_login;
1612 END IF;
1613 IF (x_srev_rec.start_date = OKC_API.G_MISS_DATE)
1614 THEN
1615 x_srev_rec.start_date := l_srev_rec.start_date;
1616 END IF;
1617 IF (x_srev_rec.end_date = OKC_API.G_MISS_DATE)
1618 THEN
1619 x_srev_rec.end_date := l_srev_rec.end_date;
1620 END IF;
1621 IF (x_srev_rec.access_level = OKC_API.G_MISS_CHAR)
1622 THEN
1623 x_srev_rec.access_level := l_srev_rec.access_level;
1624 END IF;
1625 RETURN(l_return_status);
1626 END populate_new_record;
1627 ---------------------------------------------
1628 -- Set_Attributes for:OKC_SUBCLASS_ROLES_V --
1629 ---------------------------------------------
1630 FUNCTION Set_Attributes (
1631 p_srev_rec IN srev_rec_type,
1632 x_srev_rec OUT NOCOPY srev_rec_type
1633 ) RETURN VARCHAR2 IS
1634 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1635 BEGIN
1636 x_srev_rec := p_srev_rec;
1637 x_srev_rec.OBJECT_VERSION_NUMBER := NVL(x_srev_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1638 RETURN(l_return_status);
1639 END Set_Attributes;
1640 BEGIN
1641 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1642 G_PKG_NAME,
1643 p_init_msg_list,
1644 l_api_version,
1645 p_api_version,
1646 '_PVT',
1647 x_return_status);
1648 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1649 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1650 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1651 RAISE OKC_API.G_EXCEPTION_ERROR;
1652 END IF;
1653 --- Setting item attributes
1654 l_return_status := Set_Attributes(
1655 p_srev_rec, -- IN
1656 l_srev_rec); -- OUT
1657 --- If any errors happen abort API
1658 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1659 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1660 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1661 RAISE OKC_API.G_EXCEPTION_ERROR;
1662 END IF;
1663 l_return_status := populate_new_record(l_srev_rec, l_def_srev_rec);
1664 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1665 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1666 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1667 RAISE OKC_API.G_EXCEPTION_ERROR;
1668 END IF;
1669 l_def_srev_rec := fill_who_columns(l_def_srev_rec);
1670 --- Validate all non-missing attributes (Item Level Validation)
1671 l_return_status := Validate_Attributes(l_def_srev_rec);
1672 --- If any errors happen abort API
1673 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1674 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1675 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1676 RAISE OKC_API.G_EXCEPTION_ERROR;
1677 END IF;
1678 l_return_status := Validate_Record(l_def_srev_rec);
1679 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1680 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1681 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1682 RAISE OKC_API.G_EXCEPTION_ERROR;
1683 END IF;
1684
1685 --------------------------------------
1686 -- Move VIEW record to "Child" records
1687 --------------------------------------
1688 migrate(l_def_srev_rec, l_sre_rec);
1689 --------------------------------------------
1690 -- Call the UPDATE_ROW for each child record
1691 --------------------------------------------
1692 update_row(
1693 p_init_msg_list,
1694 x_return_status,
1695 x_msg_count,
1696 x_msg_data,
1697 l_sre_rec,
1698 lx_sre_rec
1699 );
1700 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1701 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1702 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1703 RAISE OKC_API.G_EXCEPTION_ERROR;
1704 END IF;
1705 migrate(lx_sre_rec, l_def_srev_rec);
1706 x_srev_rec := l_def_srev_rec;
1707 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1708 EXCEPTION
1709 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1710 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1711 (
1712 l_api_name,
1713 G_PKG_NAME,
1714 'OKC_API.G_RET_STS_ERROR',
1715 x_msg_count,
1716 x_msg_data,
1717 '_PVT'
1718 );
1719 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1720 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1721 (
1722 l_api_name,
1723 G_PKG_NAME,
1724 'OKC_API.G_RET_STS_UNEXP_ERROR',
1725 x_msg_count,
1726 x_msg_data,
1727 '_PVT'
1728 );
1729 WHEN OTHERS THEN
1730 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1731 (
1732 l_api_name,
1733 G_PKG_NAME,
1734 'OTHERS',
1735 x_msg_count,
1736 x_msg_data,
1737 '_PVT'
1738 );
1739 END update_row;
1740 ----------------------------------------
1741 -- PL/SQL TBL update_row for:SREV_TBL --
1742 ----------------------------------------
1743 PROCEDURE update_row(
1744 p_api_version IN NUMBER,
1745 p_init_msg_list IN VARCHAR2,
1746 x_return_status OUT NOCOPY VARCHAR2,
1747 x_msg_count OUT NOCOPY NUMBER,
1748 x_msg_data OUT NOCOPY VARCHAR2,
1749 p_srev_tbl IN srev_tbl_type,
1750 x_srev_tbl OUT NOCOPY srev_tbl_type) IS
1751
1752 l_api_version CONSTANT NUMBER := 1;
1753 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1754 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1755 i NUMBER := 0;
1756 BEGIN
1757 OKC_API.init_msg_list(p_init_msg_list);
1758 -- Make sure PL/SQL table has records in it before passing
1759 IF (p_srev_tbl.COUNT > 0) THEN
1760 i := p_srev_tbl.FIRST;
1761 LOOP
1762 update_row (
1763 p_api_version => p_api_version,
1764 p_init_msg_list => OKC_API.G_FALSE,
1765 x_return_status => x_return_status,
1766 x_msg_count => x_msg_count,
1767 x_msg_data => x_msg_data,
1768 p_srev_rec => p_srev_tbl(i),
1769 x_srev_rec => x_srev_tbl(i));
1770 EXIT WHEN (i = p_srev_tbl.LAST);
1771 i := p_srev_tbl.NEXT(i);
1772 END LOOP;
1773 END IF;
1774 EXCEPTION
1775 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1776 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1777 (
1778 l_api_name,
1779 G_PKG_NAME,
1780 'OKC_API.G_RET_STS_ERROR',
1781 x_msg_count,
1782 x_msg_data,
1783 '_PVT'
1784 );
1785 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1786 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1787 (
1788 l_api_name,
1789 G_PKG_NAME,
1790 'OKC_API.G_RET_STS_UNEXP_ERROR',
1791 x_msg_count,
1792 x_msg_data,
1793 '_PVT'
1794 );
1795 WHEN OTHERS THEN
1796 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1797 (
1798 l_api_name,
1799 G_PKG_NAME,
1800 'OTHERS',
1801 x_msg_count,
1802 x_msg_data,
1803 '_PVT'
1804 );
1805 END update_row;
1806
1807 ---------------------------------------------------------------------------
1808 -- PROCEDURE delete_row
1809 ---------------------------------------------------------------------------
1810 ---------------------------------------
1811 -- delete_row for:OKC_SUBCLASS_ROLES --
1812 ---------------------------------------
1813 PROCEDURE delete_row(
1814 p_init_msg_list IN VARCHAR2,
1815 x_return_status OUT NOCOPY VARCHAR2,
1816 x_msg_count OUT NOCOPY NUMBER,
1817 x_msg_data OUT NOCOPY VARCHAR2,
1818 p_sre_rec IN sre_rec_type) IS
1819
1820 l_api_version CONSTANT NUMBER := 1;
1821 l_api_name CONSTANT VARCHAR2(30) := 'ROLES_delete_row';
1822 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1823 l_sre_rec sre_rec_type:= p_sre_rec;
1824 l_row_notfound BOOLEAN := TRUE;
1825 BEGIN
1826 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1827 p_init_msg_list,
1828 '_PVT',
1829 x_return_status);
1830 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1831 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1832 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1833 RAISE OKC_API.G_EXCEPTION_ERROR;
1834 END IF;
1835 DELETE FROM OKC_SUBCLASS_ROLES
1836 WHERE ID = l_sre_rec.id;
1837
1838 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1839 EXCEPTION
1840 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1841 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1842 (
1843 l_api_name,
1844 G_PKG_NAME,
1845 'OKC_API.G_RET_STS_ERROR',
1846 x_msg_count,
1847 x_msg_data,
1848 '_PVT'
1849 );
1850 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1851 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1852 (
1853 l_api_name,
1854 G_PKG_NAME,
1855 'OKC_API.G_RET_STS_UNEXP_ERROR',
1856 x_msg_count,
1857 x_msg_data,
1858 '_PVT'
1859 );
1860 WHEN OTHERS THEN
1861 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1862 (
1863 l_api_name,
1864 G_PKG_NAME,
1865 'OTHERS',
1866 x_msg_count,
1867 x_msg_data,
1868 '_PVT'
1869 );
1870 END delete_row;
1871 -----------------------------------------
1872 -- delete_row for:OKC_SUBCLASS_ROLES_V --
1873 -----------------------------------------
1874 PROCEDURE delete_row(
1875 p_api_version IN NUMBER,
1876 p_init_msg_list IN VARCHAR2,
1877 x_return_status OUT NOCOPY VARCHAR2,
1878 x_msg_count OUT NOCOPY NUMBER,
1879 x_msg_data OUT NOCOPY VARCHAR2,
1880 p_srev_rec IN srev_rec_type) IS
1881
1882 l_api_version CONSTANT NUMBER := 1;
1883 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1884 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1885 l_srev_rec srev_rec_type := p_srev_rec;
1886 l_sre_rec sre_rec_type;
1887 BEGIN
1888 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1889 G_PKG_NAME,
1890 p_init_msg_list,
1891 l_api_version,
1892 p_api_version,
1893 '_PVT',
1894 x_return_status);
1895 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1896 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1897 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1898 RAISE OKC_API.G_EXCEPTION_ERROR;
1899 END IF;
1900 --------------------------------------
1901 -- Move VIEW record to "Child" records
1902 --------------------------------------
1903 migrate(l_srev_rec, l_sre_rec);
1904 --------------------------------------------
1905 -- Call the DELETE_ROW for each child record
1906 --------------------------------------------
1907 delete_row(
1908 p_init_msg_list,
1909 x_return_status,
1910 x_msg_count,
1911 x_msg_data,
1912 l_sre_rec
1913 );
1914 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1915 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1916 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1917 RAISE OKC_API.G_EXCEPTION_ERROR;
1918 END IF;
1919 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1920 EXCEPTION
1921 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1922 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1923 (
1924 l_api_name,
1925 G_PKG_NAME,
1926 'OKC_API.G_RET_STS_ERROR',
1927 x_msg_count,
1928 x_msg_data,
1929 '_PVT'
1930 );
1931 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1932 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1933 (
1934 l_api_name,
1935 G_PKG_NAME,
1936 'OKC_API.G_RET_STS_UNEXP_ERROR',
1937 x_msg_count,
1938 x_msg_data,
1939 '_PVT'
1940 );
1941 WHEN OTHERS THEN
1942 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1943 (
1944 l_api_name,
1945 G_PKG_NAME,
1946 'OTHERS',
1947 x_msg_count,
1948 x_msg_data,
1949 '_PVT'
1950 );
1951 END delete_row;
1952 ----------------------------------------
1953 -- PL/SQL TBL delete_row for:SREV_TBL --
1954 ----------------------------------------
1955 PROCEDURE delete_row(
1956 p_api_version IN NUMBER,
1957 p_init_msg_list IN VARCHAR2,
1958 x_return_status OUT NOCOPY VARCHAR2,
1959 x_msg_count OUT NOCOPY NUMBER,
1960 x_msg_data OUT NOCOPY VARCHAR2,
1961 p_srev_tbl IN srev_tbl_type) IS
1962
1963 l_api_version CONSTANT NUMBER := 1;
1964 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1965 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1966 i NUMBER := 0;
1967 BEGIN
1968 OKC_API.init_msg_list(p_init_msg_list);
1969 -- Make sure PL/SQL table has records in it before passing
1970 IF (p_srev_tbl.COUNT > 0) THEN
1971 i := p_srev_tbl.FIRST;
1972 LOOP
1973 delete_row (
1974 p_api_version => p_api_version,
1975 p_init_msg_list => OKC_API.G_FALSE,
1976 x_return_status => x_return_status,
1977 x_msg_count => x_msg_count,
1978 x_msg_data => x_msg_data,
1979 p_srev_rec => p_srev_tbl(i));
1980 EXIT WHEN (i = p_srev_tbl.LAST);
1981 i := p_srev_tbl.NEXT(i);
1982 END LOOP;
1983 END IF;
1984 EXCEPTION
1985 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1986 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1987 (
1988 l_api_name,
1989 G_PKG_NAME,
1990 'OKC_API.G_RET_STS_ERROR',
1991 x_msg_count,
1992 x_msg_data,
1993 '_PVT'
1994 );
1995 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1996 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1997 (
1998 l_api_name,
1999 G_PKG_NAME,
2000 'OKC_API.G_RET_STS_UNEXP_ERROR',
2001 x_msg_count,
2002 x_msg_data,
2003 '_PVT'
2004 );
2005 WHEN OTHERS THEN
2006 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2007 (
2008 l_api_name,
2009 G_PKG_NAME,
2010 'OTHERS',
2011 x_msg_count,
2012 x_msg_data,
2013 '_PVT'
2014 );
2015 END delete_row;
2016 END OKC_SRE_PVT;