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