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