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