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