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