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