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