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