[Home] [Help]
PACKAGE BODY: APPS.OKL_ACD_PVT
Source
1 PACKAGE BODY OKL_ACD_PVT AS
2 /* $Header: OKLSACDB.pls 120.3 2006/07/13 12:48:24 adagur noship $ */
3 ----------------------------------------
4 -- Developer Generated Code here --
5 -- Developer : Guru Kadarkaraisamy --
6 -- Date : April 15 2001 --
7 -- Reason : Added code so that the validation functionality is accomplished --
8 ----------------------------------------
9
10 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) :='OKC_NO_PARENT_RECORD';
11 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) :='OKC_CONTRACTS_UNEXPECTED_ERROR';
12 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
13 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
14 G_EXCEPTION_HALT_VALIDATION exception;
15
16 -- Start of comments
17 --
18 -- Procedure Name : validate_id
19 -- Description :
20 -- Business Rules :
21 -- Parameters :
22 -- Version : 1.0
23 -- End of comments
24 procedure validate_id(
25 x_return_status out nocopy VARCHAR2,
26 p_acdv_rec in acdv_rec_type) is
27
28 begin
29 -- initialize return status
30 x_return_status := OKC_API.G_RET_STS_SUCCESS;
31
32 -- data is required
33 if (p_acdv_rec.id is null) or (p_acdv_rec.id = OKC_API.G_MISS_NUM) then
34 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
35 p_msg_name => g_required_value,
36 p_token1 => g_col_name_token,
37 p_token1_value => 'id');
38
39 -- notify caller of an error
40 x_return_status := OKC_API.G_RET_STS_ERROR;
41
42 -- halt further validation of this column
43 raise G_EXCEPTION_HALT_VALIDATION;
44 end if;
45
46 exception
47 when G_EXCEPTION_HALT_VALIDATION then
48 -- no processing necessary; validation can continue
49 -- with the next column
50 null;
51
52 when OTHERS then
53 -- store SQL error message on message stack for caller
54 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
55 p_msg_name => g_unexpected_error,
56 p_token1 => g_sqlcode_token,
57 p_token1_value => sqlcode,
58 p_token2 => g_sqlerrm_token,
59 p_token2_value => sqlerrm);
60
61 -- notify caller of an UNEXPECTED error
62 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
63
64 end validate_id;
65
66 -- Start of comments
67 --
68 -- Procedure Name : validate_object_version_number
69 -- Description :
70 -- Business Rules :
71 -- Parameters :
72 -- Version : 1.0
73 -- End of comments
74 procedure validate_object_version_number(
75 x_return_status out nocopy VARCHAR2,
76 p_acdv_rec in acdv_rec_type) is
77
78 begin
79 -- initialize return status
80 x_return_status := OKC_API.G_RET_STS_SUCCESS;
81
82 -- data is required
83 if (p_acdv_rec.object_version_number is null) or (p_acdv_rec.object_version_number = OKC_API.G_MISS_NUM) then
84 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
85 p_msg_name => g_required_value,
86 p_token1 => g_col_name_token,
87 p_token1_value => 'object_version_number');
88
89 -- notify caller of an error
90 x_return_status := OKC_API.G_RET_STS_ERROR;
91
92 -- halt further validation of this column
93 raise G_EXCEPTION_HALT_VALIDATION;
94 end if;
95
96 exception
97 when G_EXCEPTION_HALT_VALIDATION then
98 -- no processing necessary; validation can continue
99 -- with the next column
100 null;
101
102 when OTHERS then
103 -- store SQL error message on message stack for caller
104 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
105 p_msg_name => g_unexpected_error,
106 p_token1 => g_sqlcode_token,
107 p_token1_value => sqlcode,
108 p_token2 => g_sqlerrm_token,
109 p_token2_value => sqlerrm);
110
111 -- notify caller of an UNEXPECTED error
112 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
113
114 end validate_object_version_number;
115
116 -- Start of comments
117 --
118 -- Procedure Name : validate_cdn_code
119 -- Description :
120 -- Business Rules :
121 -- Parameters :
122 -- Version : 1.0
123 -- End of comments
124 procedure validate_cdn_code(
125 x_return_status out nocopy VARCHAR2,
126 p_acdv_rec in acdv_rec_type) is
127 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
128
129 begin
130 -- initialize return status
131 x_return_status := OKC_API.G_RET_STS_SUCCESS;
132
133 -- data is required
134 if (p_acdv_rec.cdn_code is null) or (p_acdv_rec.cdn_code = OKC_API.G_MISS_CHAR) then
135 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
136 p_msg_name => g_required_value,
137 p_token1 => g_col_name_token,
138 p_token1_value => 'cdn_code');
139
140
141 -- notify caller of an error
142 x_return_status := OKC_API.G_RET_STS_ERROR;
143
144 -- halt further validation of this column
145 raise G_EXCEPTION_HALT_VALIDATION;
146 end if;
147
148 l_return_status := OKL_UTIL.check_lookup_code(
149 p_lookup_type => 'OKL_ASSET_CONDITION'
150 ,p_lookup_code => p_acdv_rec.cdn_code);
151
152 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
153 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
154 p_msg_name => g_invalid_value,
155 p_token1 => g_col_name_token,
156 p_token1_value => 'cdn_code');
157
158 -- notify caller of an error
159 x_return_status := OKC_API.G_RET_STS_ERROR;
160 raise G_EXCEPTION_HALT_VALIDATION;
161
162 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
163
164 -- notify caller of an error
165 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
166 raise G_EXCEPTION_HALT_VALIDATION;
167
168 end if;
169
170 exception
171 when G_EXCEPTION_HALT_VALIDATION then
172 -- no processing necessary; validation can continue
173 -- with the next column
174 null;
175
176 when OTHERS then
177 -- store SQL error message on message stack for caller
178 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
179 p_msg_name => g_unexpected_error,
180 p_token1 => g_sqlcode_token,
181 p_token1_value => sqlcode,
182 p_token2 => g_sqlerrm_token,
183 p_token2_value => sqlerrm);
184
185 -- notify caller of an UNEXPECTED error
186 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
187
188 end validate_cdn_code;
189
190 -- Start of comments
191 --
192 -- Procedure Name : validate_iln_id
193 -- Description :
194 -- Business Rules :
195 -- Parameters :
196 -- Version : 1.0
197 -- End of comments
198 procedure validate_iln_id(
199 x_return_status out nocopy VARCHAR2,
200 p_acdv_rec in acdv_rec_type) is
201
202 l_dummy_var VARCHAR2(1) := '?';
203 -- select the ID of the parent record from the parent table
204 /* CURSOR l_ilnv_csr IS
205 select 'x' from OKX_LOCATION_V
206 where ID = p_acdv_rec.iln_id; */
207
208 begin
209 -- initialize return status
210 x_return_status := OKC_API.G_RET_STS_SUCCESS;
211
212 -- data is required
213 if (p_acdv_rec.iln_id is null) or (p_acdv_rec.iln_id = OKC_API.G_MISS_NUM) then
214 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
215 p_msg_name => g_required_value,
216 p_token1 => g_col_name_token,
217 p_token1_value => 'iln_id');
218
219
220 -- notify caller of an error
221 x_return_status := OKC_API.G_RET_STS_ERROR;
222
223 -- halt further validation of this column
224 raise G_EXCEPTION_HALT_VALIDATION;
225 end if;
226
227 -- enforce foreign key
228 /* OPEN l_ilnv_csr;
229 FETCH l_ilnv_csr INTO l_dummy_var;
230 CLOSE l_ilnv_csr;
231
232 -- if l_dummy_var is still set to default, data was not found
233 IF (l_dummy_var = '?') THEN
234 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
235 p_msg_name => G_NO_PARENT_RECORD,
236 p_token1 => G_COL_NAME_TOKEN,
237 p_token1_value => 'iln_id',
238 p_token2 => G_CHILD_TABLE_TOKEN,
239 p_token2_value => 'OKL_ASSET_CNDTNS_V',
240 p_token3 => G_PARENT_TABLE_TOKEN,
241 p_token3_value => 'OKX_LOCATION_V');
242
243 -- notify caller of an error
244 x_return_status := OKC_API.G_RET_STS_ERROR;
245 END IF; */
246
247 exception
248 when G_EXCEPTION_HALT_VALIDATION then
249 -- no processing necessary; validation can continue
250 -- with the next column
251 null;
252
253 when OTHERS then
254 -- store SQL error message on message stack for caller
255 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
256 p_msg_name => g_unexpected_error,
257 p_token1 => g_sqlcode_token,
258 p_token1_value => sqlcode,
259 p_token2 => g_sqlerrm_token,
260 p_token2_value => sqlerrm);
261
262 -- notify caller of an UNEXPECTED error
263 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
264
265 -- verify that cursor was closed
266 /* IF l_ilnv_csr%ISOPEN THEN
267 CLOSE l_ilnv_csr;
268 END IF; */
269
270 end validate_iln_id;
271
272 -- Start of comments
273 --
274 -- Procedure Name : validate_isp_id
275 -- Description :
276 -- Business Rules :
277 -- Parameters :
278 -- Version : 1.0
279 -- End of comments
280 procedure validate_isp_id(
281 x_return_status out nocopy VARCHAR2,
282 p_acdv_rec in acdv_rec_type) is
283 l_dummy_var VARCHAR2(1) := '?';
284 -- select the ID of the parent record from the parent table
285
286 /* CURSOR l_ispv_csr IS
287 select 'x' from OKX_INSPECTORS_V
288 where ID = p_acdv_rec.isp_id; */
289
290 begin
291 -- initialize return status
292 x_return_status := OKC_API.G_RET_STS_SUCCESS;
293
294 -- data is required
295 if (p_acdv_rec.isp_id is null) or (p_acdv_rec.isp_id = OKC_API.G_MISS_NUM) then
296
297 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
298 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
299 p_token1 => 'PROMPT',
300 p_token1_value => OKL_AM_UTIL_PVT.get_ak_attribute('OKL_INSPECTOR'));
301
302 /*
303 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
304 p_msg_name => g_required_value,
305 p_token1 => g_col_name_token,
306 p_token1_value => 'isp_id');
307 */
308
309 -- notify caller of an error
310 x_return_status := OKC_API.G_RET_STS_ERROR;
311
312 -- halt further validation of this column
313 raise G_EXCEPTION_HALT_VALIDATION;
314 end if;
315
316 -- enforce foreign key
317 /* OPEN l_ispv_csr;
318 FETCH l_ispv_csr INTO l_dummy_var;
319 CLOSE l_ispv_csr;
320
321 -- if l_dummy_var is still set to default, data was not found
322 IF (l_dummy_var = '?') THEN
323 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
324 p_msg_name => G_NO_PARENT_RECORD,
325 p_token1 => G_COL_NAME_TOKEN,
326 p_token1_value => 'isp_id',
327 p_token2 => G_CHILD_TABLE_TOKEN,
328 p_token2_value => 'OKL_ASSET_CNDTNS_V',
329 p_token3 => G_PARENT_TABLE_TOKEN,
330 p_token3_value => 'OKX_INSPECTORS_V');
331
332 -- notify caller of an error
333 x_return_status := OKC_API.G_RET_STS_ERROR;
334 END IF; */
335
336 exception
337 when G_EXCEPTION_HALT_VALIDATION then
338 -- no processing necessary; validation can continue
339 -- with the next column
340 null;
341
342 when OTHERS then
343 -- store SQL error message on message stack for caller
344 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
345 p_msg_name => g_unexpected_error,
346 p_token1 => g_sqlcode_token,
347 p_token1_value => sqlcode,
348 p_token2 => g_sqlerrm_token,
349 p_token2_value => sqlerrm);
350
351 -- notify caller of an UNEXPECTED error
352 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
353
354 /*-- verify that cursor was closed
355 IF l_ispv_csr%ISOPEN THEN
356 CLOSE l_ispv_csr;
357 END IF; */
358
359 end validate_isp_id;
360
361 PROCEDURE validate_org_id(
362 x_return_status OUT NOCOPY VARCHAR2,
363 p_acdv_rec IN acdv_rec_type) IS
364 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
365 BEGIN
366 -- initialize return status
367 x_return_status := OKC_API.G_RET_STS_SUCCESS;
368
369 IF ((p_acdv_rec.org_id IS NULL) OR (p_acdv_rec.org_id = OKC_API.G_MISS_NUM)) THEN
370 RAISE G_EXCEPTION_HALT_VALIDATION;
371 END IF;
372
373 -- check org id validity using the generic function okl_util.check_org_id()
374 l_return_status := OKL_UTIL.check_org_id (p_acdv_rec.org_id);
375
376 IF ( l_return_status = OKC_API.G_RET_STS_ERROR) THEN
377 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
378 p_msg_name => g_invalid_value,
379 p_token1 => g_col_name_token,
380 p_token1_value => 'org_id');
381
382 -- notify caller of an error
383 x_return_status := OKC_API.G_RET_STS_ERROR;
384 raise G_EXCEPTION_HALT_VALIDATION;
385
386 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
387
388 -- notify caller of an error
389 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
390 raise G_EXCEPTION_HALT_VALIDATION;
391
392 END IF;
393
394 EXCEPTION
395 WHEN G_EXCEPTION_HALT_VALIDATION then
396 -- No action necessary. Validation can continue to next attribute/column
397 null;
398
399 WHEN OTHERS THEN
400 -- store SQL error message on message stack for caller
401 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
402 p_msg_name => g_unexpected_error,
403 p_token1 => g_sqlcode_token,
404 p_token1_value => sqlcode,
405 p_token2 => g_sqlerrm_token,
406 p_token2_value => sqlerrm);
407
408 -- notify caller of an UNEXPECTED error
409 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
410
411 END validate_org_id;
412
413 -- Foreign Key Validation
414
415 -- Start of comments
416 --
417 -- Procedure Name : validate_clm_id
418 -- Description :
419 -- Business Rules :
420 -- Parameters :
421 -- Version : 1.0
422 -- End of comments
423
424 procedure validate_clm_id(
425 x_return_status OUT NOCOPY VARCHAR2,
426 p_acdv_rec IN acdv_rec_type
427 ) IS
428 CURSOR l_clm_csr IS
429 select 'x' from OKL_INS_CLAIMS_B
430 where ID = p_acdv_rec.clm_id;
431 l_dummy_var VARCHAR2(1) := '?';
432
433 begin
434 -- initialize return status
435 x_return_status := OKC_API.G_RET_STS_SUCCESS;
436
437 -- enforce foreign key
438 IF ((p_acdv_rec.clm_id IS NOT NULL) AND (p_acdv_rec.clm_id <> OKC_API.G_MISS_NUM)) THEN
439 OPEN l_clm_csr;
440 FETCH l_clm_csr INTO l_dummy_var;
441 CLOSE l_clm_csr;
442
443 -- if l_dummy_var is still set to default, data was not found
444 IF (l_dummy_var = '?') THEN
445 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
446 p_msg_name => G_NO_PARENT_RECORD,
447 p_token1 => G_COL_NAME_TOKEN,
448 p_token1_value => 'clm_id',
449 p_token2 => G_CHILD_TABLE_TOKEN,
450 p_token2_value => 'OKL_ASSET_CNDTNS',
451 p_token3 => G_PARENT_TABLE_TOKEN,
452 p_token3_value => 'OKL_INS_CLAIMS_B');
453
454 -- notify caller of an error
455 x_return_status := OKC_API.G_RET_STS_ERROR;
456 END IF;
457 END IF;
458
459 EXCEPTION
460 WHEN G_EXCEPTION_HALT_VALIDATION then
461 -- No action necessary. Validation can continue to next attribute/column
462 null;
463
464 WHEN OTHERS THEN
465 -- store SQL error message on message stack for caller
466 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
467 p_msg_name => g_unexpected_error,
468 p_token1 => g_sqlcode_token,
469 p_token1_value => sqlcode,
470 p_token2 => g_sqlerrm_token,
471 p_token2_value => sqlerrm);
472
473 -- notify caller of an UNEXPECTED error
474 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
475
476 -- verify that cursor was closed
477 IF l_clm_csr%ISOPEN THEN
478 CLOSE l_clm_csr;
479 END IF;
480 end validate_clm_id;
481
482
483 -- Start of comments
484 --
485 -- Procedure Name : validate_kle_id
486 -- Description :
487 -- Business Rules :
488 -- Parameters :
489 -- Version : 1.0
490 -- End of comments
491
492 procedure validate_kle_id(
493 x_return_status OUT NOCOPY VARCHAR2,
494 p_acdv_rec IN acdv_rec_type
495 ) IS
496 CURSOR l_kle_csr IS
497 select 'x' from OKL_K_LINES_V
498 where ID = p_acdv_rec.kle_id;
499
500 l_dummy_var VARCHAR2(1) := '?';
501
502 begin
503 -- initialize return status
504 x_return_status := OKC_API.G_RET_STS_SUCCESS;
505
506 -- enforce foreign key
507 IF ((p_acdv_rec.kle_id IS NOT NULL) AND (p_acdv_rec.kle_id <> OKC_API.G_MISS_NUM)) THEN
508 OPEN l_kle_csr;
509 FETCH l_kle_csr INTO l_dummy_var;
510 CLOSE l_kle_csr;
511
512 -- if l_dummy_var is still set to default, data was not found
513 IF (l_dummy_var = '?') THEN
514 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
515 p_msg_name => G_NO_PARENT_RECORD,
516 p_token1 => G_COL_NAME_TOKEN,
517 p_token1_value => 'kle_id',
518 p_token2 => G_CHILD_TABLE_TOKEN,
519 p_token2_value => 'OKL_ASSET_CNDTNS',
520 p_token3 => G_PARENT_TABLE_TOKEN,
521 p_token3_value => 'OKL_K_LINES_V');
522
523 -- notify caller of an error
524 x_return_status := OKC_API.G_RET_STS_ERROR;
525 END IF;
526 END IF;
527
528 EXCEPTION
529 WHEN G_EXCEPTION_HALT_VALIDATION then
530 -- No action necessary. Validation can continue to next attribute/column
531 null;
532
533 WHEN OTHERS THEN
534 -- store SQL error message on message stack for caller
535 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
536 p_msg_name => g_unexpected_error,
537 p_token1 => g_sqlcode_token,
538 p_token1_value => sqlcode,
539 p_token2 => g_sqlerrm_token,
540 p_token2_value => sqlerrm);
541
542 -- notify caller of an UNEXPECTED error
543 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
544
545 -- verify that cursor was closed
546 IF l_kle_csr%ISOPEN THEN
547 CLOSE l_kle_csr;
548 END IF;
549 end validate_kle_id;
550
551 -- Start of comments
552 --
553 -- Procedure Name : is_unique
554 -- Description : Do not create a new record if any record exists with the same kle_id
555 -- Business Rules :
556 -- Parameters :
557 -- Version : 1.0
558 -- End of comments
559 PROCEDURE is_unique(
560 p_acdv_rec in acdv_rec_type,
561 x_return_status out nocopy VARCHAR2) is
562
563 -- Cursor to check whether conditon is_unique for a given finacial asset line.
564 -- Bug 2524727 Need to check for free form because insurance claims create multiple
565 -- conditions for the same fixed asset line
566 CURSOR okl_acdv_csr (p_id IN NUMBER, p_kle_id IN NUMBER) IS
567 SELECT ACD.ID
568 FROM OKL_ASSET_CNDTNS ACD
569 ,OKC_K_LINES_B CLE
570 ,OKC_LINE_STYLES_B LSE
571 WHERE ACD.KLE_ID = p_kle_id
572 AND ACD.KLE_ID = CLE.ID
573 AND CLE.LSE_ID = LSE.ID
574 AND LSE.LTY_CODE = 'FREE_FORM1'
575 AND ACD.ID <> nvl (p_id, -99999);
576
577 -- Old Cursor before bug 2524727
578 -- SELECT id
579 -- FROM OKL_ASSET_CNDTNS_V
580 -- WHERE kle_id = p_kle_id
581 -- AND id <> nvl (p_id, -99999);
582
583 l_id NUMBER;
584 BEGIN
585 -- initialize return status
586 x_return_status := OKC_API.G_RET_STS_SUCCESS;
587 -- Check if value passed for id
588 IF (p_acdv_rec.kle_id IS NOT NULL) AND (p_acdv_rec.kle_id <> OKC_API.G_MISS_NUM) THEN
589 OPEN okl_acdv_csr(p_acdv_rec.id, p_acdv_rec.kle_id);
590 FETCH okl_acdv_csr INTO l_id;
591 -- If id already exists then update mode
592 IF okl_acdv_csr%FOUND THEN
593 -- Asset Return already exists for this asset with status STATUS so cannot create a new asset return now.
594 OKL_API.SET_MESSAGE( p_app_name => 'OKL'
595 ,p_msg_name => 'OKL_AM_ASSET_CNDTN_EXISTS');
596 -- notify caller of an error
597 x_return_status := OKC_API.G_RET_STS_ERROR;
598 END IF;
599 CLOSE okl_acdv_csr;
600 END IF;
601
602 EXCEPTION
603 WHEN OTHERS THEN
604 IF okl_acdv_csr%ISOPEN THEN
605 CLOSE okl_acdv_csr;
606 END IF;
607 -- store SQL error message on message stack for caller
608 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
609 p_msg_name => g_unexpected_error,
610 p_token1 => g_sqlcode_token,
611 p_token1_value => sqlcode,
612 p_token2 => g_sqlerrm_token,
613 p_token2_value => sqlerrm);
614
615 -- notify caller of an UNEXPECTED error
616 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
617
618 END is_unique;
619
620 ---------------------------------------------------------------------------
621 -- FUNCTION get_seq_id
622 ---------------------------------------------------------------------------
623 FUNCTION get_seq_id RETURN NUMBER IS
624 BEGIN
625 RETURN(okc_p_util.raw_to_number(sys_guid()));
626 END get_seq_id;
627
628 ---------------------------------------------------------------------------
629 -- PROCEDURE qc
630 ---------------------------------------------------------------------------
631 PROCEDURE qc IS
632 BEGIN
633 null;
634 END qc;
635
636 ---------------------------------------------------------------------------
637 -- PROCEDURE change_version
638 ---------------------------------------------------------------------------
639 PROCEDURE change_version IS
640 BEGIN
641 null;
642 END change_version;
643
644 ---------------------------------------------------------------------------
645 -- PROCEDURE api_copy
646 ---------------------------------------------------------------------------
647 PROCEDURE api_copy IS
648 BEGIN
649 null;
650 END api_copy;
651
652 ---------------------------------------------------------------------------
653 -- FUNCTION get_rec for: OKL_ASSET_CNDTNS
654 ---------------------------------------------------------------------------
655 FUNCTION get_rec (
656 p_acd_rec IN acd_rec_type,
657 x_no_data_found OUT NOCOPY BOOLEAN
658 ) RETURN acd_rec_type IS
659 CURSOR acd_pk_csr (p_id IN NUMBER) IS
660 SELECT
661 ID,
662 DATE_REPORT,
663 CDN_CODE,
664 CLM_ID,
665 ISP_ID,
666 ILN_ID,
667 KLE_ID,
668 OBJECT_VERSION_NUMBER,
669 ORG_ID,
670 REQUEST_ID,
671 PROGRAM_APPLICATION_ID,
672 PROGRAM_ID,
673 PROGRAM_UPDATE_DATE,
674 ATTRIBUTE_CATEGORY,
675 ATTRIBUTE1,
676 ATTRIBUTE2,
677 ATTRIBUTE3,
678 ATTRIBUTE4,
679 ATTRIBUTE5,
680 ATTRIBUTE6,
681 ATTRIBUTE7,
682 ATTRIBUTE8,
683 ATTRIBUTE9,
684 ATTRIBUTE10,
685 ATTRIBUTE11,
686 ATTRIBUTE12,
687 ATTRIBUTE13,
688 ATTRIBUTE14,
689 ATTRIBUTE15,
690 CREATED_BY,
691 CREATION_DATE,
692 LAST_UPDATED_BY,
693 LAST_UPDATE_DATE,
694 LAST_UPDATE_LOGIN
695 FROM Okl_Asset_Cndtns
696 WHERE okl_asset_cndtns.id = p_id;
697 l_acd_pk acd_pk_csr%ROWTYPE;
698 l_acd_rec acd_rec_type;
699 BEGIN
700 x_no_data_found := TRUE;
701 -- Get current database values
702 OPEN acd_pk_csr (p_acd_rec.id);
703 FETCH acd_pk_csr INTO
704 l_acd_rec.ID,
705 l_acd_rec.DATE_REPORT,
706 l_acd_rec.CDN_CODE,
707 l_acd_rec.CLM_ID,
708 l_acd_rec.ISP_ID,
709 l_acd_rec.ILN_ID,
710 l_acd_rec.KLE_ID,
711 l_acd_rec.OBJECT_VERSION_NUMBER,
712 l_acd_rec.ORG_ID,
713 l_acd_rec.REQUEST_ID,
714 l_acd_rec.PROGRAM_APPLICATION_ID,
715 l_acd_rec.PROGRAM_ID,
716 l_acd_rec.PROGRAM_UPDATE_DATE,
717 l_acd_rec.ATTRIBUTE_CATEGORY,
718 l_acd_rec.ATTRIBUTE1,
719 l_acd_rec.ATTRIBUTE2,
720 l_acd_rec.ATTRIBUTE3,
721 l_acd_rec.ATTRIBUTE4,
722 l_acd_rec.ATTRIBUTE5,
723 l_acd_rec.ATTRIBUTE6,
724 l_acd_rec.ATTRIBUTE7,
725 l_acd_rec.ATTRIBUTE8,
726 l_acd_rec.ATTRIBUTE9,
727 l_acd_rec.ATTRIBUTE10,
728 l_acd_rec.ATTRIBUTE11,
729 l_acd_rec.ATTRIBUTE12,
730 l_acd_rec.ATTRIBUTE13,
731 l_acd_rec.ATTRIBUTE14,
732 l_acd_rec.ATTRIBUTE15,
733 l_acd_rec.CREATED_BY,
734 l_acd_rec.CREATION_DATE,
735 l_acd_rec.LAST_UPDATED_BY,
736 l_acd_rec.LAST_UPDATE_DATE,
737 l_acd_rec.LAST_UPDATE_LOGIN;
738 x_no_data_found := acd_pk_csr%NOTFOUND;
739 CLOSE acd_pk_csr;
740 RETURN(l_acd_rec);
741 END get_rec;
742
743 FUNCTION get_rec (
744 p_acd_rec IN acd_rec_type
745 ) RETURN acd_rec_type IS
746 l_row_notfound BOOLEAN := TRUE;
747 BEGIN
748 RETURN(get_rec(p_acd_rec, l_row_notfound));
749 END get_rec;
750 ---------------------------------------------------------------------------
751 -- FUNCTION get_rec for: OKL_ASSET_CNDTNS_V
752 ---------------------------------------------------------------------------
753 FUNCTION get_rec (
754 p_acdv_rec IN acdv_rec_type,
755 x_no_data_found OUT NOCOPY BOOLEAN
756 ) RETURN acdv_rec_type IS
757 CURSOR okl_acdv_pk_csr (p_id IN NUMBER) IS
758 SELECT
759 ID,
760 OBJECT_VERSION_NUMBER,
761 CDN_CODE,
762 ILN_ID,
763 ISP_ID,
764 CLM_ID,
765 KLE_ID,
766 DATE_REPORT,
767 ATTRIBUTE_CATEGORY,
768 ATTRIBUTE1,
769 ATTRIBUTE2,
770 ATTRIBUTE3,
771 ATTRIBUTE4,
772 ATTRIBUTE5,
773 ATTRIBUTE6,
774 ATTRIBUTE7,
775 ATTRIBUTE8,
776 ATTRIBUTE9,
777 ATTRIBUTE10,
778 ATTRIBUTE11,
779 ATTRIBUTE12,
780 ATTRIBUTE13,
781 ATTRIBUTE14,
782 ATTRIBUTE15,
783 ORG_ID,
784 REQUEST_ID,
785 PROGRAM_APPLICATION_ID,
786 PROGRAM_ID,
787 PROGRAM_UPDATE_DATE,
788 CREATED_BY,
789 CREATION_DATE,
790 LAST_UPDATED_BY,
791 LAST_UPDATE_DATE,
792 LAST_UPDATE_LOGIN
793 FROM OKL_ASSET_CNDTNS
794 WHERE OKL_ASSET_CNDTNS.id = p_id;
795 l_okl_acdv_pk okl_acdv_pk_csr%ROWTYPE;
796 l_acdv_rec acdv_rec_type;
797 BEGIN
798 x_no_data_found := TRUE;
799 -- Get current database values
800 OPEN okl_acdv_pk_csr (p_acdv_rec.id);
801 FETCH okl_acdv_pk_csr INTO
802 l_acdv_rec.ID,
803 l_acdv_rec.OBJECT_VERSION_NUMBER,
804 l_acdv_rec.CDN_CODE,
805 l_acdv_rec.ILN_ID,
806 l_acdv_rec.ISP_ID,
807 l_acdv_rec.CLM_ID,
808 l_acdv_rec.KLE_ID,
809 l_acdv_rec.DATE_REPORT,
810 l_acdv_rec.ATTRIBUTE_CATEGORY,
811 l_acdv_rec.ATTRIBUTE1,
812 l_acdv_rec.ATTRIBUTE2,
813 l_acdv_rec.ATTRIBUTE3,
814 l_acdv_rec.ATTRIBUTE4,
815 l_acdv_rec.ATTRIBUTE5,
816 l_acdv_rec.ATTRIBUTE6,
817 l_acdv_rec.ATTRIBUTE7,
818 l_acdv_rec.ATTRIBUTE8,
819 l_acdv_rec.ATTRIBUTE9,
820 l_acdv_rec.ATTRIBUTE10,
821 l_acdv_rec.ATTRIBUTE11,
822 l_acdv_rec.ATTRIBUTE12,
823 l_acdv_rec.ATTRIBUTE13,
824 l_acdv_rec.ATTRIBUTE14,
825 l_acdv_rec.ATTRIBUTE15,
826 l_acdv_rec.ORG_ID,
827 l_acdv_rec.REQUEST_ID,
828 l_acdv_rec.PROGRAM_APPLICATION_ID,
829 l_acdv_rec.PROGRAM_ID,
830 l_acdv_rec.PROGRAM_UPDATE_DATE,
831 l_acdv_rec.CREATED_BY,
832 l_acdv_rec.CREATION_DATE,
833 l_acdv_rec.LAST_UPDATED_BY,
834 l_acdv_rec.LAST_UPDATE_DATE,
835 l_acdv_rec.LAST_UPDATE_LOGIN;
836 x_no_data_found := okl_acdv_pk_csr%NOTFOUND;
837 CLOSE okl_acdv_pk_csr;
838 RETURN(l_acdv_rec);
839 END get_rec;
840
841 FUNCTION get_rec (
842 p_acdv_rec IN acdv_rec_type
843 ) RETURN acdv_rec_type IS
844 l_row_notfound BOOLEAN := TRUE;
845 BEGIN
846 RETURN(get_rec(p_acdv_rec, l_row_notfound));
847 END get_rec;
848
849 --------------------------------------------------------
850 -- FUNCTION null_out_defaults for: OKL_ASSET_CNDTNS_V --
851 --------------------------------------------------------
852 FUNCTION null_out_defaults (
853 p_acdv_rec IN acdv_rec_type
854 ) RETURN acdv_rec_type IS
855 l_acdv_rec acdv_rec_type := p_acdv_rec;
856 BEGIN
857 IF (l_acdv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
858 l_acdv_rec.object_version_number := NULL;
859 END IF;
860 IF (l_acdv_rec.cdn_code = OKC_API.G_MISS_CHAR) THEN
861 l_acdv_rec.cdn_code := NULL;
862 END IF;
863 IF (l_acdv_rec.iln_id = OKC_API.G_MISS_NUM) THEN
864 l_acdv_rec.iln_id := NULL;
865 END IF;
866 IF (l_acdv_rec.isp_id = OKC_API.G_MISS_NUM) THEN
867 l_acdv_rec.isp_id := NULL;
868 END IF;
869 IF (l_acdv_rec.clm_id = OKC_API.G_MISS_NUM) THEN
870 l_acdv_rec.clm_id := NULL;
871 END IF;
872 IF (l_acdv_rec.kle_id = OKC_API.G_MISS_NUM) THEN
873 l_acdv_rec.kle_id := NULL;
874 END IF;
875 IF (l_acdv_rec.date_report = OKC_API.G_MISS_DATE) THEN
876 l_acdv_rec.date_report := NULL;
877 END IF;
878 IF (l_acdv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
879 l_acdv_rec.attribute_category := NULL;
880 END IF;
881 IF (l_acdv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
882 l_acdv_rec.attribute1 := NULL;
883 END IF;
884 IF (l_acdv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
885 l_acdv_rec.attribute2 := NULL;
886 END IF;
887 IF (l_acdv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
888 l_acdv_rec.attribute3 := NULL;
889 END IF;
890 IF (l_acdv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
891 l_acdv_rec.attribute4 := NULL;
892 END IF;
893 IF (l_acdv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
894 l_acdv_rec.attribute5 := NULL;
895 END IF;
896 IF (l_acdv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
897 l_acdv_rec.attribute6 := NULL;
898 END IF;
899 IF (l_acdv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
900 l_acdv_rec.attribute7 := NULL;
901 END IF;
902 IF (l_acdv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
903 l_acdv_rec.attribute8 := NULL;
904 END IF;
905 IF (l_acdv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
906 l_acdv_rec.attribute9 := NULL;
907 END IF;
908 IF (l_acdv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
909 l_acdv_rec.attribute10 := NULL;
910 END IF;
911 IF (l_acdv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
912 l_acdv_rec.attribute11 := NULL;
913 END IF;
914 IF (l_acdv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
915 l_acdv_rec.attribute12 := NULL;
916 END IF;
917 IF (l_acdv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
918 l_acdv_rec.attribute13 := NULL;
919 END IF;
920 IF (l_acdv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
921 l_acdv_rec.attribute14 := NULL;
922 END IF;
923 IF (l_acdv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
924 l_acdv_rec.attribute15 := NULL;
925 END IF;
926 IF (l_acdv_rec.org_id = OKC_API.G_MISS_NUM) THEN
927 l_acdv_rec.org_id := NULL;
928 END IF;
929 -- Begin Post-Generation Change
930 /*
931 IF (l_acdv_rec.request_id = OKC_API.G_MISS_NUM) THEN
932 l_acdv_rec.request_id := NULL;
933 END IF;
934 IF (l_acdv_rec.program_application_id = OKC_API.G_MISS_NUM) THEN
935 l_acdv_rec.program_application_id := NULL;
936 END IF;
937 IF (l_acdv_rec.program_id = OKC_API.G_MISS_NUM) THEN
938 l_acdv_rec.program_id := NULL;
939 END IF;
940 IF (l_acdv_rec.program_update_date = OKC_API.G_MISS_DATE) THEN
941 l_acdv_rec.program_update_date := NULL;
942 END IF;
943 */
944 -- End Post-Generation Change
945 IF (l_acdv_rec.created_by = OKC_API.G_MISS_NUM) THEN
946 l_acdv_rec.created_by := NULL;
947 END IF;
948 IF (l_acdv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
949 l_acdv_rec.creation_date := NULL;
950 END IF;
951 IF (l_acdv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
952 l_acdv_rec.last_updated_by := NULL;
953 END IF;
954 IF (l_acdv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
955 l_acdv_rec.last_update_date := NULL;
956 END IF;
957 IF (l_acdv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
958 l_acdv_rec.last_update_login := NULL;
959 END IF;
960 RETURN(l_acdv_rec);
961 END null_out_defaults;
962 ---------------------------------------------------------------------------
963 -- PROCEDURE Validate_Attributes
964 ---------------------------------------------------------------------------
965
966
967 ------------------------------------------------
968 -- Validate_Attributes for:OKL_ASSET_CNDTNS_V --
969 ------------------------------------------------
970 -- Start of comments
971 --
972 -- Procedure Name : validate_attributes
973 -- Description : Modified by : Guru Kadarkaraisamy
974 -- Business Rules :
975 -- Parameters :
976 -- Version : 1.0
977 -- End of comments
978
979 FUNCTION Validate_Attributes (
980 p_acdv_rec IN acdv_rec_type
981 ) RETURN VARCHAR2 IS
982 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
983 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
984
985 begin
986 -- call each column-level validation
987 validate_id(x_return_status => l_return_status,
988 p_acdv_rec => p_acdv_rec);
989
990 -- store the highest degree of error
991 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
992 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
993 x_return_status := l_return_status;
994 end if;
995 end if;
996
997 validate_object_version_number(x_return_status => l_return_status,
998 p_acdv_rec => p_acdv_rec);
999
1000 -- store the highest degree of error
1001 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1002 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
1003 x_return_status := l_return_status;
1004 end if;
1005 end if;
1006
1007 validate_cdn_code(x_return_status => l_return_status,
1008 p_acdv_rec => p_acdv_rec);
1009
1010 -- store the highest degree of error
1011 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1012 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
1013 x_return_status := l_return_status;
1014 end if;
1015 end if;
1016
1017
1018 validate_iln_id(x_return_status => l_return_status,
1019 p_acdv_rec => p_acdv_rec);
1020
1021 -- store the highest degree of error
1022 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1023 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
1024 x_return_status := l_return_status;
1025 end if;
1026 end if;
1027
1028 validate_isp_id(x_return_status => l_return_status,
1029 p_acdv_rec => p_acdv_rec);
1030
1031 -- store the highest degree of error
1032 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1033 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
1034 x_return_status := l_return_status;
1035 end if;
1036 end if;
1037
1038 validate_org_id(x_return_status => l_return_status,
1039 p_acdv_rec => p_acdv_rec);
1040
1041 -- store the highest degree of error
1042 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1043 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
1044 x_return_status := l_return_status;
1045 end if;
1046 end if;
1047
1048 -- call foreign key validation for 'clm_id'
1049 validate_clm_id(x_return_status => l_return_status,
1050 p_acdv_rec => p_acdv_rec);
1051
1052 -- store the highest degree of error
1053 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1054 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1055 x_return_status := l_return_status;
1056 END IF;
1057 END IF;
1058
1059 -- call foreign key validation for 'clm_id'
1060 validate_kle_id(x_return_status => l_return_status,
1061 p_acdv_rec => p_acdv_rec);
1062
1063 -- store the highest degree of error
1064 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1065 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1066 x_return_status := l_return_status;
1067 END IF;
1068 END IF;
1069
1070 return (x_return_status);
1071 END Validate_Attributes;
1072
1073 ---------------------------------------------------------------------------
1074 -- PROCEDURE Validate_Record
1075 ---------------------------------------------------------------------------
1076 --------------------------------------------
1077 -- Validate_Record for:OKL_ASSET_CNDTNS_V --
1078 --------------------------------------------
1079 FUNCTION Validate_Record (
1080 p_acdv_rec IN acdv_rec_type
1081 ) RETURN VARCHAR2 IS
1082 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1083 BEGIN
1084
1085 -- check uniqueness
1086 is_unique(p_acdv_rec,l_return_status);
1087
1088 RETURN (l_return_status);
1089 END Validate_Record;
1090
1091 ---------------------------------------------------------------------------
1092 -- PROCEDURE Migrate
1093 ---------------------------------------------------------------------------
1094 PROCEDURE migrate (
1095 p_from IN acdv_rec_type,
1096 p_to IN OUT NOCOPY acd_rec_type
1097 ) IS
1098 BEGIN
1099 p_to.id := p_from.id;
1100 p_to.date_report := p_from.date_report;
1101 p_to.cdn_code := p_from.cdn_code;
1102 p_to.clm_id := p_from.clm_id;
1103 p_to.isp_id := p_from.isp_id;
1104 p_to.iln_id := p_from.iln_id;
1105 p_to.kle_id := p_from.kle_id;
1106 p_to.object_version_number := p_from.object_version_number;
1107 p_to.org_id := p_from.org_id;
1108 p_to.request_id := p_from.request_id;
1109 p_to.program_application_id := p_from.program_application_id;
1110 p_to.program_id := p_from.program_id;
1111 p_to.program_update_date := p_from.program_update_date;
1112 p_to.attribute_category := p_from.attribute_category;
1113 p_to.attribute1 := p_from.attribute1;
1114 p_to.attribute2 := p_from.attribute2;
1115 p_to.attribute3 := p_from.attribute3;
1116 p_to.attribute4 := p_from.attribute4;
1117 p_to.attribute5 := p_from.attribute5;
1118 p_to.attribute6 := p_from.attribute6;
1119 p_to.attribute7 := p_from.attribute7;
1120 p_to.attribute8 := p_from.attribute8;
1121 p_to.attribute9 := p_from.attribute9;
1122 p_to.attribute10 := p_from.attribute10;
1123 p_to.attribute11 := p_from.attribute11;
1124 p_to.attribute12 := p_from.attribute12;
1125 p_to.attribute13 := p_from.attribute13;
1126 p_to.attribute14 := p_from.attribute14;
1127 p_to.attribute15 := p_from.attribute15;
1128 p_to.created_by := p_from.created_by;
1129 p_to.creation_date := p_from.creation_date;
1130 p_to.last_updated_by := p_from.last_updated_by;
1131 p_to.last_update_date := p_from.last_update_date;
1132 p_to.last_update_login := p_from.last_update_login;
1133 END migrate;
1134 PROCEDURE migrate (
1135 p_from IN acd_rec_type,
1136 p_to IN OUT NOCOPY acdv_rec_type
1137 ) IS
1138 BEGIN
1139 p_to.id := p_from.id;
1140 p_to.date_report := p_from.date_report;
1141 p_to.cdn_code := p_from.cdn_code;
1142 p_to.clm_id := p_from.clm_id;
1143 p_to.isp_id := p_from.isp_id;
1144 p_to.iln_id := p_from.iln_id;
1145 p_to.kle_id := p_from.kle_id;
1146 p_to.object_version_number := p_from.object_version_number;
1147 p_to.org_id := p_from.org_id;
1148 p_to.request_id := p_from.request_id;
1149 p_to.program_application_id := p_from.program_application_id;
1150 p_to.program_id := p_from.program_id;
1151 p_to.program_update_date := p_from.program_update_date;
1152 p_to.attribute_category := p_from.attribute_category;
1153 p_to.attribute1 := p_from.attribute1;
1154 p_to.attribute2 := p_from.attribute2;
1155 p_to.attribute3 := p_from.attribute3;
1156 p_to.attribute4 := p_from.attribute4;
1157 p_to.attribute5 := p_from.attribute5;
1158 p_to.attribute6 := p_from.attribute6;
1159 p_to.attribute7 := p_from.attribute7;
1160 p_to.attribute8 := p_from.attribute8;
1161 p_to.attribute9 := p_from.attribute9;
1162 p_to.attribute10 := p_from.attribute10;
1163 p_to.attribute11 := p_from.attribute11;
1164 p_to.attribute12 := p_from.attribute12;
1165 p_to.attribute13 := p_from.attribute13;
1166 p_to.attribute14 := p_from.attribute14;
1167 p_to.attribute15 := p_from.attribute15;
1168 p_to.created_by := p_from.created_by;
1169 p_to.creation_date := p_from.creation_date;
1170 p_to.last_updated_by := p_from.last_updated_by;
1171 p_to.last_update_date := p_from.last_update_date;
1172 p_to.last_update_login := p_from.last_update_login;
1173 END migrate;
1174
1175 ---------------------------------------------------------------------------
1176 -- PROCEDURE validate_row
1177 ---------------------------------------------------------------------------
1178 -----------------------------------------
1179 -- validate_row for:OKL_ASSET_CNDTNS_V --
1180 -----------------------------------------
1181 PROCEDURE validate_row(
1182 p_api_version IN NUMBER,
1183 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1184 x_return_status OUT NOCOPY VARCHAR2,
1185 x_msg_count OUT NOCOPY NUMBER,
1186 x_msg_data OUT NOCOPY VARCHAR2,
1187 p_acdv_rec IN acdv_rec_type) IS
1188
1189 l_api_version CONSTANT NUMBER := 1;
1190 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1191 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1192 l_acdv_rec acdv_rec_type := p_acdv_rec;
1193 l_acd_rec acd_rec_type;
1194 BEGIN
1195 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1196 G_PKG_NAME,
1197 p_init_msg_list,
1198 l_api_version,
1199 p_api_version,
1200 '_PVT',
1201 x_return_status);
1202 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1203 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1204 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1205 RAISE OKC_API.G_EXCEPTION_ERROR;
1206 END IF;
1207 --- Validate all non-missing attributes (Item Level Validation)
1208 l_return_status := Validate_Attributes(l_acdv_rec);
1209 --- If any errors happen abort API
1210 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1211 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1212 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1213 RAISE OKC_API.G_EXCEPTION_ERROR;
1214 END IF;
1215 l_return_status := Validate_Record(l_acdv_rec);
1216 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1217 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1218 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1219 RAISE OKC_API.G_EXCEPTION_ERROR;
1220 END IF;
1221 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1222 EXCEPTION
1223 WHEN G_EXCEPTION_HALT_VALIDATION then
1224 -- No action necessary. Validation can continue to next attribute/column
1225 null;
1226
1227 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1228 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1229 (
1230 l_api_name,
1231 G_PKG_NAME,
1232 'OKC_API.G_RET_STS_ERROR',
1233 x_msg_count,
1234 x_msg_data,
1235 '_PVT'
1236 );
1237 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1238 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1239 (
1240 l_api_name,
1241 G_PKG_NAME,
1242 'OKC_API.G_RET_STS_UNEXP_ERROR',
1243 x_msg_count,
1244 x_msg_data,
1245 '_PVT'
1246 );
1247 WHEN OTHERS THEN
1248 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1249 (
1250 l_api_name,
1251 G_PKG_NAME,
1252 'OTHERS',
1253 x_msg_count,
1254 x_msg_data,
1255 '_PVT'
1256 );
1257 END validate_row;
1258 ------------------------------------------
1259 -- PL/SQL TBL validate_row for:ACDV_TBL --
1260 ------------------------------------------
1261 PROCEDURE validate_row(
1262 p_api_version IN NUMBER,
1263 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1264 x_return_status OUT NOCOPY VARCHAR2,
1265 x_msg_count OUT NOCOPY NUMBER,
1266 x_msg_data OUT NOCOPY VARCHAR2,
1267 p_acdv_tbl IN acdv_tbl_type) IS
1268
1269 l_api_version CONSTANT NUMBER := 1;
1270 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1271 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1272 i NUMBER := 0;
1273 -- Begin Post-Generation Change
1274 -- overall error status
1275 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1276 -- End Post-Generation Change
1277
1278 BEGIN
1279 OKC_API.init_msg_list(p_init_msg_list);
1280 -- Make sure PL/SQL table has records in it before passing
1281 IF (p_acdv_tbl.COUNT > 0) THEN
1282 i := p_acdv_tbl.FIRST;
1283 LOOP
1284 validate_row (
1285 p_api_version => p_api_version,
1286 p_init_msg_list => OKC_API.G_FALSE,
1287 x_return_status => x_return_status,
1288 x_msg_count => x_msg_count,
1289 x_msg_data => x_msg_data,
1290 p_acdv_rec => p_acdv_tbl(i));
1291 -- Begin Post-Generation Change
1292 -- store the highest degree of error
1293 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1294 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1295 l_overall_status := x_return_status;
1296 END IF;
1297 END IF;
1298 -- End Post-Generation Change
1299 EXIT WHEN (i = p_acdv_tbl.LAST);
1300 i := p_acdv_tbl.NEXT(i);
1301 END LOOP;
1302 -- Begin Post-Generation Change
1303 -- return overall status
1304 x_return_status := l_overall_status;
1305 -- End Post-Generation Change
1306 END IF;
1307 EXCEPTION
1308 WHEN G_EXCEPTION_HALT_VALIDATION then
1309 -- No action necessary. Validation can continue to next attribute/column
1310 null;
1311
1312 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1313 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1314 (
1315 l_api_name,
1316 G_PKG_NAME,
1317 'OKC_API.G_RET_STS_ERROR',
1318 x_msg_count,
1319 x_msg_data,
1320 '_PVT'
1321 );
1322 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1323 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1324 (
1325 l_api_name,
1326 G_PKG_NAME,
1327 'OKC_API.G_RET_STS_UNEXP_ERROR',
1328 x_msg_count,
1329 x_msg_data,
1330 '_PVT'
1331 );
1332 WHEN OTHERS THEN
1333 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1334 (
1335 l_api_name,
1336 G_PKG_NAME,
1337 'OTHERS',
1338 x_msg_count,
1339 x_msg_data,
1340 '_PVT'
1341 );
1342 END validate_row;
1343
1344 ---------------------------------------------------------------------------
1345 -- PROCEDURE insert_row
1346 ---------------------------------------------------------------------------
1347 -------------------------------------
1348 -- insert_row for:OKL_ASSET_CNDTNS --
1349 -------------------------------------
1350 PROCEDURE insert_row(
1351 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1352 x_return_status OUT NOCOPY VARCHAR2,
1353 x_msg_count OUT NOCOPY NUMBER,
1354 x_msg_data OUT NOCOPY VARCHAR2,
1355 p_acd_rec IN acd_rec_type,
1356 x_acd_rec OUT NOCOPY acd_rec_type) IS
1357
1358 l_api_version CONSTANT NUMBER := 1;
1359 l_api_name CONSTANT VARCHAR2(30) := 'CNDTNS_insert_row';
1360 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1361 l_acd_rec acd_rec_type := p_acd_rec;
1362 l_def_acd_rec acd_rec_type;
1363 -----------------------------------------
1364 -- Set_Attributes for:OKL_ASSET_CNDTNS --
1365 -----------------------------------------
1366 FUNCTION Set_Attributes (
1367 p_acd_rec IN acd_rec_type,
1368 x_acd_rec OUT NOCOPY acd_rec_type
1369 ) RETURN VARCHAR2 IS
1370 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1371 BEGIN
1372 x_acd_rec := p_acd_rec;
1373 RETURN(l_return_status);
1374 END Set_Attributes;
1375 BEGIN
1376 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1377 p_init_msg_list,
1378 '_PVT',
1379 x_return_status);
1380 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1381 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1382 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1383 RAISE OKC_API.G_EXCEPTION_ERROR;
1384 END IF;
1385 --- Setting item attributes
1386 l_return_status := Set_Attributes(
1387 p_acd_rec, -- IN
1388 l_acd_rec); -- OUT
1389 --- If any errors happen abort API
1390 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1391 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1392 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1393 RAISE OKC_API.G_EXCEPTION_ERROR;
1394 END IF;
1395 INSERT INTO OKL_ASSET_CNDTNS(
1396 id,
1397 date_report,
1398 cdn_code,
1399 clm_id,
1400 isp_id,
1401 iln_id,
1402 kle_id,
1403 object_version_number,
1404 org_id,
1405 request_id,
1406 program_application_id,
1407 program_id,
1408 program_update_date,
1409 attribute_category,
1410 attribute1,
1411 attribute2,
1412 attribute3,
1413 attribute4,
1414 attribute5,
1415 attribute6,
1416 attribute7,
1417 attribute8,
1418 attribute9,
1419 attribute10,
1420 attribute11,
1421 attribute12,
1422 attribute13,
1423 attribute14,
1424 attribute15,
1425 created_by,
1426 creation_date,
1427 last_updated_by,
1428 last_update_date,
1429 last_update_login)
1430 VALUES (
1431 l_acd_rec.id,
1432 l_acd_rec.date_report,
1433 l_acd_rec.cdn_code,
1434 l_acd_rec.clm_id,
1435 l_acd_rec.isp_id,
1436 l_acd_rec.iln_id,
1437 l_acd_rec.kle_id,
1438 l_acd_rec.object_version_number,
1439 l_acd_rec.org_id,
1440 /* l_acd_rec.request_id,
1441 l_acd_rec.program_application_id,
1442 l_acd_rec.program_id,
1443 l_acd_rec.program_update_date,*/
1444 decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
1445 decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
1446 decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
1447 decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
1448 l_acd_rec.attribute_category,
1449 l_acd_rec.attribute1,
1450 l_acd_rec.attribute2,
1451 l_acd_rec.attribute3,
1452 l_acd_rec.attribute4,
1453 l_acd_rec.attribute5,
1454 l_acd_rec.attribute6,
1455 l_acd_rec.attribute7,
1456 l_acd_rec.attribute8,
1457 l_acd_rec.attribute9,
1458 l_acd_rec.attribute10,
1459 l_acd_rec.attribute11,
1460 l_acd_rec.attribute12,
1461 l_acd_rec.attribute13,
1462 l_acd_rec.attribute14,
1463 l_acd_rec.attribute15,
1464 l_acd_rec.created_by,
1465 l_acd_rec.creation_date,
1466 l_acd_rec.last_updated_by,
1467 l_acd_rec.last_update_date,
1468 l_acd_rec.last_update_login);
1469 -- Set OUT values
1470 x_acd_rec := l_acd_rec;
1471 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1472 EXCEPTION
1473 WHEN G_EXCEPTION_HALT_VALIDATION then
1474 -- No action necessary. Validation can continue to next attribute/column
1475 null;
1476
1477 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1478 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1479 (
1480 l_api_name,
1481 G_PKG_NAME,
1482 'OKC_API.G_RET_STS_ERROR',
1483 x_msg_count,
1484 x_msg_data,
1485 '_PVT'
1486 );
1487 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1488 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1489 (
1490 l_api_name,
1491 G_PKG_NAME,
1492 'OKC_API.G_RET_STS_UNEXP_ERROR',
1493 x_msg_count,
1494 x_msg_data,
1495 '_PVT'
1496 );
1497 WHEN OTHERS THEN
1498 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1499 (
1500 l_api_name,
1501 G_PKG_NAME,
1502 'OTHERS',
1503 x_msg_count,
1504 x_msg_data,
1505 '_PVT'
1506 );
1507 END insert_row;
1508 ---------------------------------------
1509 -- insert_row for:OKL_ASSET_CNDTNS_V --
1510 ---------------------------------------
1511 PROCEDURE insert_row(
1512 p_api_version IN NUMBER,
1513 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1514 x_return_status OUT NOCOPY VARCHAR2,
1515 x_msg_count OUT NOCOPY NUMBER,
1516 x_msg_data OUT NOCOPY VARCHAR2,
1517 p_acdv_rec IN acdv_rec_type,
1518 x_acdv_rec OUT NOCOPY acdv_rec_type) IS
1519
1520 l_api_version CONSTANT NUMBER := 1;
1521 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1522 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1523 l_acdv_rec acdv_rec_type;
1524 l_def_acdv_rec acdv_rec_type;
1525 l_acd_rec acd_rec_type;
1526 lx_acd_rec acd_rec_type;
1527 -------------------------------
1528 -- FUNCTION fill_who_columns --
1529 -------------------------------
1530 FUNCTION fill_who_columns (
1531 p_acdv_rec IN acdv_rec_type
1532 ) RETURN acdv_rec_type IS
1533 l_acdv_rec acdv_rec_type := p_acdv_rec;
1534 BEGIN
1535 l_acdv_rec.CREATION_DATE := SYSDATE;
1536 l_acdv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1537 l_acdv_rec.LAST_UPDATE_DATE := l_acdv_rec.CREATION_DATE;
1538 l_acdv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1539 l_acdv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1540 RETURN(l_acdv_rec);
1541 END fill_who_columns;
1542 -------------------------------------------
1543 -- Set_Attributes for:OKL_ASSET_CNDTNS_V --
1544 -------------------------------------------
1545 FUNCTION Set_Attributes (
1546 p_acdv_rec IN acdv_rec_type,
1547 x_acdv_rec OUT NOCOPY acdv_rec_type
1548 ) RETURN VARCHAR2 IS
1549 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1550 BEGIN
1551 x_acdv_rec := p_acdv_rec;
1552 x_acdv_rec.OBJECT_VERSION_NUMBER := 1;
1553 -- Default the ORG ID if a value is not passed
1554 IF p_acdv_rec.org_id IS NULL
1555 OR p_acdv_rec.org_id = OKC_API.G_MISS_NUM THEN
1556 x_acdv_rec.org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
1557 END IF;
1558 RETURN(l_return_status);
1559 END Set_Attributes;
1560 BEGIN
1561 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1562 G_PKG_NAME,
1563 p_init_msg_list,
1564 l_api_version,
1565 p_api_version,
1566 '_PVT',
1567 x_return_status);
1568 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1569 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1570 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1571 RAISE OKC_API.G_EXCEPTION_ERROR;
1572 END IF;
1573 l_acdv_rec := null_out_defaults(p_acdv_rec);
1574 -- Set primary key value
1575 l_acdv_rec.ID := get_seq_id;
1576 --- Setting item attributes
1577 l_return_status := Set_Attributes(
1578 l_acdv_rec, -- IN
1579 l_def_acdv_rec); -- OUT
1580 --- If any errors happen abort API
1581 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1582 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1583 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1584 RAISE OKC_API.G_EXCEPTION_ERROR;
1585 END IF;
1586 l_def_acdv_rec := fill_who_columns(l_def_acdv_rec);
1587 --- Validate all non-missing attributes (Item Level Validation)
1588 l_return_status := Validate_Attributes(l_def_acdv_rec);
1589 --- If any errors happen abort API
1590 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1591 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1592 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1593 RAISE OKC_API.G_EXCEPTION_ERROR;
1594 END IF;
1595 l_return_status := Validate_Record(l_def_acdv_rec);
1596 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1597 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1598 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1599 RAISE OKC_API.G_EXCEPTION_ERROR;
1600 END IF;
1601 --------------------------------------
1602 -- Move VIEW record to "Child" records
1603 --------------------------------------
1604 migrate(l_def_acdv_rec, l_acd_rec);
1605 --------------------------------------------
1606 -- Call the INSERT_ROW for each child record
1607 --------------------------------------------
1608 insert_row(
1609 p_init_msg_list,
1610 x_return_status,
1611 x_msg_count,
1612 x_msg_data,
1613 l_acd_rec,
1614 lx_acd_rec
1615 );
1616 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1617 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1618 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1619 RAISE OKC_API.G_EXCEPTION_ERROR;
1620 END IF;
1621 migrate(lx_acd_rec, l_def_acdv_rec);
1622 -- Set OUT values
1623 x_acdv_rec := l_def_acdv_rec;
1624 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1625 EXCEPTION
1626 WHEN G_EXCEPTION_HALT_VALIDATION then
1627 -- No action necessary. Validation can continue to next attribute/column
1628 null;
1629
1630 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1631 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1632 (
1633 l_api_name,
1634 G_PKG_NAME,
1635 'OKC_API.G_RET_STS_ERROR',
1636 x_msg_count,
1637 x_msg_data,
1638 '_PVT'
1639 );
1640 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1641 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1642 (
1643 l_api_name,
1644 G_PKG_NAME,
1645 'OKC_API.G_RET_STS_UNEXP_ERROR',
1646 x_msg_count,
1647 x_msg_data,
1648 '_PVT'
1649 );
1650 WHEN OTHERS THEN
1651 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1652 (
1653 l_api_name,
1654 G_PKG_NAME,
1655 'OTHERS',
1656 x_msg_count,
1657 x_msg_data,
1658 '_PVT'
1659 );
1660 END insert_row;
1661 ----------------------------------------
1662 -- PL/SQL TBL insert_row for:ACDV_TBL --
1663 ----------------------------------------
1664 PROCEDURE insert_row(
1665 p_api_version IN NUMBER,
1666 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1667 x_return_status OUT NOCOPY VARCHAR2,
1668 x_msg_count OUT NOCOPY NUMBER,
1669 x_msg_data OUT NOCOPY VARCHAR2,
1670 p_acdv_tbl IN acdv_tbl_type,
1671 x_acdv_tbl OUT NOCOPY acdv_tbl_type) IS
1672
1673 l_api_version CONSTANT NUMBER := 1;
1674 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1675 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1676 i NUMBER := 0;
1677 -- Begin Post-Generation Change
1678 -- overall error status
1679 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1680 -- End Post-Generation Change
1681 BEGIN
1682 OKC_API.init_msg_list(p_init_msg_list);
1683 -- Make sure PL/SQL table has records in it before passing
1684 IF (p_acdv_tbl.COUNT > 0) THEN
1685 i := p_acdv_tbl.FIRST;
1686 LOOP
1687 insert_row (
1688 p_api_version => p_api_version,
1689 p_init_msg_list => OKC_API.G_FALSE,
1690 x_return_status => x_return_status,
1691 x_msg_count => x_msg_count,
1692 x_msg_data => x_msg_data,
1693 p_acdv_rec => p_acdv_tbl(i),
1694 x_acdv_rec => x_acdv_tbl(i));
1695 -- Begin Post-Generation Change
1696 -- store the highest degree of error
1697 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1698 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1699 l_overall_status := x_return_status;
1700 END IF;
1701 END IF;
1702 -- End Post-Generation Change
1703 EXIT WHEN (i = p_acdv_tbl.LAST);
1704 i := p_acdv_tbl.NEXT(i);
1705 END LOOP;
1706 -- Begin Post-Generation Change
1707 -- return overall status
1708 x_return_status := l_overall_status;
1709 -- End Post-Generation Change
1710 END IF;
1711 EXCEPTION
1712 WHEN G_EXCEPTION_HALT_VALIDATION then
1713 -- No action necessary. Validation can continue to next attribute/column
1714 null;
1715
1716 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1717 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1718 (
1719 l_api_name,
1720 G_PKG_NAME,
1721 'OKC_API.G_RET_STS_ERROR',
1722 x_msg_count,
1723 x_msg_data,
1724 '_PVT'
1725 );
1726 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1727 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1728 (
1729 l_api_name,
1730 G_PKG_NAME,
1731 'OKC_API.G_RET_STS_UNEXP_ERROR',
1732 x_msg_count,
1733 x_msg_data,
1734 '_PVT'
1735 );
1736 WHEN OTHERS THEN
1737 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1738 (
1739 l_api_name,
1740 G_PKG_NAME,
1741 'OTHERS',
1742 x_msg_count,
1743 x_msg_data,
1744 '_PVT'
1745 );
1746 END insert_row;
1747
1748 ---------------------------------------------------------------------------
1749 -- PROCEDURE lock_row
1750 ---------------------------------------------------------------------------
1751 -----------------------------------
1752 -- lock_row for:OKL_ASSET_CNDTNS --
1753 -----------------------------------
1754 PROCEDURE lock_row(
1755 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1756 x_return_status OUT NOCOPY VARCHAR2,
1757 x_msg_count OUT NOCOPY NUMBER,
1758 x_msg_data OUT NOCOPY VARCHAR2,
1759 p_acd_rec IN acd_rec_type) IS
1760
1761 E_Resource_Busy EXCEPTION;
1762 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1763 CURSOR lock_csr (p_acd_rec IN acd_rec_type) IS
1764 SELECT OBJECT_VERSION_NUMBER
1765 FROM OKL_ASSET_CNDTNS
1766 WHERE ID = p_acd_rec.id
1767 AND OBJECT_VERSION_NUMBER = p_acd_rec.object_version_number
1768 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1769
1770 CURSOR lchk_csr (p_acd_rec IN acd_rec_type) IS
1771 SELECT OBJECT_VERSION_NUMBER
1772 FROM OKL_ASSET_CNDTNS
1773 WHERE ID = p_acd_rec.id;
1774 l_api_version CONSTANT NUMBER := 1;
1775 l_api_name CONSTANT VARCHAR2(30) := 'CNDTNS_lock_row';
1776 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1777 l_object_version_number OKL_ASSET_CNDTNS.OBJECT_VERSION_NUMBER%TYPE;
1778 lc_object_version_number OKL_ASSET_CNDTNS.OBJECT_VERSION_NUMBER%TYPE;
1779 l_row_notfound BOOLEAN := FALSE;
1780 lc_row_notfound BOOLEAN := FALSE;
1781 BEGIN
1782 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1783 p_init_msg_list,
1784 '_PVT',
1785 x_return_status);
1786 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1787 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1788 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1789 RAISE OKC_API.G_EXCEPTION_ERROR;
1790 END IF;
1791 BEGIN
1792 OPEN lock_csr(p_acd_rec);
1793 FETCH lock_csr INTO l_object_version_number;
1794 l_row_notfound := lock_csr%NOTFOUND;
1795 CLOSE lock_csr;
1796 EXCEPTION
1797 WHEN G_EXCEPTION_HALT_VALIDATION then
1798 -- No action necessary. Validation can continue to next attribute/column
1799 null;
1800
1801 WHEN E_Resource_Busy THEN
1802 IF (lock_csr%ISOPEN) THEN
1803 CLOSE lock_csr;
1804 END IF;
1805 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1806 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1807 END;
1808
1809 IF ( l_row_notfound ) THEN
1810 OPEN lchk_csr(p_acd_rec);
1811 FETCH lchk_csr INTO lc_object_version_number;
1812 lc_row_notfound := lchk_csr%NOTFOUND;
1813 CLOSE lchk_csr;
1814 END IF;
1815 IF (lc_row_notfound) THEN
1816 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1817 RAISE OKC_API.G_EXCEPTION_ERROR;
1818 ELSIF lc_object_version_number > p_acd_rec.object_version_number THEN
1819 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1820 RAISE OKC_API.G_EXCEPTION_ERROR;
1821 ELSIF lc_object_version_number <> p_acd_rec.object_version_number THEN
1822 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1823 RAISE OKC_API.G_EXCEPTION_ERROR;
1824 ELSIF lc_object_version_number = -1 THEN
1825 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1826 RAISE OKC_API.G_EXCEPTION_ERROR;
1827 END IF;
1828 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1829 EXCEPTION
1830 WHEN G_EXCEPTION_HALT_VALIDATION then
1831 -- No action necessary. Validation can continue to next attribute/column
1832 null;
1833
1834 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1835 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1836 (
1837 l_api_name,
1838 G_PKG_NAME,
1839 'OKC_API.G_RET_STS_ERROR',
1840 x_msg_count,
1841 x_msg_data,
1842 '_PVT'
1843 );
1844 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1845 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1846 (
1847 l_api_name,
1848 G_PKG_NAME,
1849 'OKC_API.G_RET_STS_UNEXP_ERROR',
1850 x_msg_count,
1851 x_msg_data,
1852 '_PVT'
1853 );
1854 WHEN OTHERS THEN
1855 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1856 (
1857 l_api_name,
1858 G_PKG_NAME,
1859 'OTHERS',
1860 x_msg_count,
1861 x_msg_data,
1862 '_PVT'
1863 );
1864 END lock_row;
1865 -------------------------------------
1866 -- lock_row for:OKL_ASSET_CNDTNS_V --
1867 -------------------------------------
1868 PROCEDURE lock_row(
1869 p_api_version IN NUMBER,
1870 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1871 x_return_status OUT NOCOPY VARCHAR2,
1872 x_msg_count OUT NOCOPY NUMBER,
1873 x_msg_data OUT NOCOPY VARCHAR2,
1874 p_acdv_rec IN acdv_rec_type) IS
1875
1876 l_api_version CONSTANT NUMBER := 1;
1877 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1878 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1879 l_acd_rec acd_rec_type;
1880 BEGIN
1881 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1882 G_PKG_NAME,
1883 p_init_msg_list,
1884 l_api_version,
1885 p_api_version,
1886 '_PVT',
1887 x_return_status);
1888 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1889 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1890 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1891 RAISE OKC_API.G_EXCEPTION_ERROR;
1892 END IF;
1893 --------------------------------------
1894 -- Move VIEW record to "Child" records
1895 --------------------------------------
1896 migrate(p_acdv_rec, l_acd_rec);
1897 --------------------------------------------
1898 -- Call the LOCK_ROW for each child record
1899 --------------------------------------------
1900 lock_row(
1901 p_init_msg_list,
1902 x_return_status,
1903 x_msg_count,
1904 x_msg_data,
1905 l_acd_rec
1906 );
1907 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1908 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1909 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1910 RAISE OKC_API.G_EXCEPTION_ERROR;
1911 END IF;
1912 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1913 EXCEPTION
1914 WHEN G_EXCEPTION_HALT_VALIDATION then
1915 -- No action necessary. Validation can continue to next attribute/column
1916 null;
1917
1918 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1919 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1920 (
1921 l_api_name,
1922 G_PKG_NAME,
1923 'OKC_API.G_RET_STS_ERROR',
1924 x_msg_count,
1925 x_msg_data,
1926 '_PVT'
1927 );
1928 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1929 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1930 (
1931 l_api_name,
1932 G_PKG_NAME,
1933 'OKC_API.G_RET_STS_UNEXP_ERROR',
1934 x_msg_count,
1935 x_msg_data,
1936 '_PVT'
1937 );
1938 WHEN OTHERS THEN
1939 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1940 (
1941 l_api_name,
1942 G_PKG_NAME,
1943 'OTHERS',
1944 x_msg_count,
1945 x_msg_data,
1946 '_PVT'
1947 );
1948 END lock_row;
1949 --------------------------------------
1950 -- PL/SQL TBL lock_row for:ACDV_TBL --
1951 --------------------------------------
1952 PROCEDURE lock_row(
1953 p_api_version IN NUMBER,
1954 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1955 x_return_status OUT NOCOPY VARCHAR2,
1956 x_msg_count OUT NOCOPY NUMBER,
1957 x_msg_data OUT NOCOPY VARCHAR2,
1958 p_acdv_tbl IN acdv_tbl_type) IS
1959
1960 l_api_version CONSTANT NUMBER := 1;
1961 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1962 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1963 i NUMBER := 0;
1964 -- Begin Post-Generation Change
1965 -- overall error status
1966 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1967 -- End Post-Generation Change
1968 BEGIN
1969 OKC_API.init_msg_list(p_init_msg_list);
1970 -- Make sure PL/SQL table has records in it before passing
1971 IF (p_acdv_tbl.COUNT > 0) THEN
1972 i := p_acdv_tbl.FIRST;
1973 LOOP
1974 lock_row (
1975 p_api_version => p_api_version,
1976 p_init_msg_list => OKC_API.G_FALSE,
1977 x_return_status => x_return_status,
1978 x_msg_count => x_msg_count,
1979 x_msg_data => x_msg_data,
1980 p_acdv_rec => p_acdv_tbl(i));
1981 -- Begin Post-Generation Change
1982 -- store the highest degree of error
1983 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1984 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1985 l_overall_status := x_return_status;
1986 END IF;
1987 END IF;
1988 -- End Post-Generation Change
1989 EXIT WHEN (i = p_acdv_tbl.LAST);
1990 i := p_acdv_tbl.NEXT(i);
1991 END LOOP;
1992 -- Begin Post-Generation Change
1993 -- return overall status
1994 x_return_status := l_overall_status;
1995 -- End Post-Generation Change
1996 END IF;
1997 EXCEPTION
1998 WHEN G_EXCEPTION_HALT_VALIDATION then
1999 -- No action necessary. Validation can continue to next attribute/column
2000 null;
2001
2002 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2003 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2004 (
2005 l_api_name,
2006 G_PKG_NAME,
2007 'OKC_API.G_RET_STS_ERROR',
2008 x_msg_count,
2009 x_msg_data,
2010 '_PVT'
2011 );
2012 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2013 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2014 (
2015 l_api_name,
2016 G_PKG_NAME,
2017 'OKC_API.G_RET_STS_UNEXP_ERROR',
2018 x_msg_count,
2019 x_msg_data,
2020 '_PVT'
2021 );
2022 WHEN OTHERS THEN
2023 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2024 (
2025 l_api_name,
2026 G_PKG_NAME,
2027 'OTHERS',
2028 x_msg_count,
2029 x_msg_data,
2030 '_PVT'
2031 );
2032 END lock_row;
2033
2034 ---------------------------------------------------------------------------
2035 -- PROCEDURE update_row
2036 ---------------------------------------------------------------------------
2037 -------------------------------------
2038 -- update_row for:OKL_ASSET_CNDTNS --
2039 -------------------------------------
2040 PROCEDURE update_row(
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_acd_rec IN acd_rec_type,
2046 x_acd_rec OUT NOCOPY acd_rec_type) IS
2047
2048 l_api_version CONSTANT NUMBER := 1;
2049 l_api_name CONSTANT VARCHAR2(30) := 'CNDTNS_update_row';
2050 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2051 l_acd_rec acd_rec_type := p_acd_rec;
2052 l_def_acd_rec acd_rec_type;
2053 l_row_notfound BOOLEAN := TRUE;
2054 ----------------------------------
2055 -- FUNCTION populate_new_record --
2056 ----------------------------------
2057 FUNCTION populate_new_record (
2058 p_acd_rec IN acd_rec_type,
2059 x_acd_rec OUT NOCOPY acd_rec_type
2060 ) RETURN VARCHAR2 IS
2061 l_acd_rec acd_rec_type;
2062 l_row_notfound BOOLEAN := TRUE;
2063 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2064 BEGIN
2065 x_acd_rec := p_acd_rec;
2066 -- Get current database values
2067 l_acd_rec := get_rec(p_acd_rec, l_row_notfound);
2068 IF (l_row_notfound) THEN
2069 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2070 END IF;
2071 IF (x_acd_rec.id = OKC_API.G_MISS_NUM)
2072 THEN
2073 x_acd_rec.id := l_acd_rec.id;
2074 END IF;
2075 IF (x_acd_rec.date_report = OKC_API.G_MISS_DATE)
2076 THEN
2077 x_acd_rec.date_report := l_acd_rec.date_report;
2078 END IF;
2079 IF (x_acd_rec.cdn_code = OKC_API.G_MISS_CHAR)
2080 THEN
2081 x_acd_rec.cdn_code := l_acd_rec.cdn_code;
2082 END IF;
2083 IF (x_acd_rec.clm_id = OKC_API.G_MISS_NUM)
2084 THEN
2085 x_acd_rec.clm_id := l_acd_rec.clm_id;
2086 END IF;
2087 IF (x_acd_rec.isp_id = OKC_API.G_MISS_NUM)
2088 THEN
2089 x_acd_rec.isp_id := l_acd_rec.isp_id;
2090 END IF;
2091 IF (x_acd_rec.iln_id = OKC_API.G_MISS_NUM)
2092 THEN
2093 x_acd_rec.iln_id := l_acd_rec.iln_id;
2094 END IF;
2095 IF (x_acd_rec.kle_id = OKC_API.G_MISS_NUM)
2096 THEN
2097 x_acd_rec.kle_id := l_acd_rec.kle_id;
2098 END IF;
2099 IF (x_acd_rec.object_version_number = OKC_API.G_MISS_NUM)
2100 THEN
2101 x_acd_rec.object_version_number := l_acd_rec.object_version_number;
2102 END IF;
2103 IF (x_acd_rec.org_id = OKC_API.G_MISS_NUM)
2104 THEN
2105 x_acd_rec.org_id := l_acd_rec.org_id;
2106 END IF;
2107 IF (x_acd_rec.request_id = OKC_API.G_MISS_NUM)
2108 THEN
2109 x_acd_rec.request_id := l_acd_rec.request_id;
2110 END IF;
2111 IF (x_acd_rec.program_application_id = OKC_API.G_MISS_NUM)
2112 THEN
2113 x_acd_rec.program_application_id := l_acd_rec.program_application_id;
2114 END IF;
2115 IF (x_acd_rec.program_id = OKC_API.G_MISS_NUM)
2116 THEN
2117 x_acd_rec.program_id := l_acd_rec.program_id;
2118 END IF;
2119 IF (x_acd_rec.program_update_date = OKC_API.G_MISS_DATE)
2120 THEN
2121 x_acd_rec.program_update_date := l_acd_rec.program_update_date;
2122 END IF;
2123 IF (x_acd_rec.attribute_category = OKC_API.G_MISS_CHAR)
2124 THEN
2125 x_acd_rec.attribute_category := l_acd_rec.attribute_category;
2126 END IF;
2127 IF (x_acd_rec.attribute1 = OKC_API.G_MISS_CHAR)
2128 THEN
2129 x_acd_rec.attribute1 := l_acd_rec.attribute1;
2130 END IF;
2131 IF (x_acd_rec.attribute2 = OKC_API.G_MISS_CHAR)
2132 THEN
2133 x_acd_rec.attribute2 := l_acd_rec.attribute2;
2134 END IF;
2135 IF (x_acd_rec.attribute3 = OKC_API.G_MISS_CHAR)
2136 THEN
2137 x_acd_rec.attribute3 := l_acd_rec.attribute3;
2138 END IF;
2139 IF (x_acd_rec.attribute4 = OKC_API.G_MISS_CHAR)
2140 THEN
2141 x_acd_rec.attribute4 := l_acd_rec.attribute4;
2142 END IF;
2143 IF (x_acd_rec.attribute5 = OKC_API.G_MISS_CHAR)
2144 THEN
2145 x_acd_rec.attribute5 := l_acd_rec.attribute5;
2146 END IF;
2147 IF (x_acd_rec.attribute6 = OKC_API.G_MISS_CHAR)
2148 THEN
2149 x_acd_rec.attribute6 := l_acd_rec.attribute6;
2150 END IF;
2151 IF (x_acd_rec.attribute7 = OKC_API.G_MISS_CHAR)
2152 THEN
2153 x_acd_rec.attribute7 := l_acd_rec.attribute7;
2154 END IF;
2155 IF (x_acd_rec.attribute8 = OKC_API.G_MISS_CHAR)
2156 THEN
2157 x_acd_rec.attribute8 := l_acd_rec.attribute8;
2158 END IF;
2159 IF (x_acd_rec.attribute9 = OKC_API.G_MISS_CHAR)
2160 THEN
2161 x_acd_rec.attribute9 := l_acd_rec.attribute9;
2162 END IF;
2163 IF (x_acd_rec.attribute10 = OKC_API.G_MISS_CHAR)
2164 THEN
2165 x_acd_rec.attribute10 := l_acd_rec.attribute10;
2166 END IF;
2167 IF (x_acd_rec.attribute11 = OKC_API.G_MISS_CHAR)
2168 THEN
2169 x_acd_rec.attribute11 := l_acd_rec.attribute11;
2170 END IF;
2171 IF (x_acd_rec.attribute12 = OKC_API.G_MISS_CHAR)
2172 THEN
2173 x_acd_rec.attribute12 := l_acd_rec.attribute12;
2174 END IF;
2175 IF (x_acd_rec.attribute13 = OKC_API.G_MISS_CHAR)
2176 THEN
2177 x_acd_rec.attribute13 := l_acd_rec.attribute13;
2178 END IF;
2179 IF (x_acd_rec.attribute14 = OKC_API.G_MISS_CHAR)
2180 THEN
2181 x_acd_rec.attribute14 := l_acd_rec.attribute14;
2182 END IF;
2183 IF (x_acd_rec.attribute15 = OKC_API.G_MISS_CHAR)
2184 THEN
2185 x_acd_rec.attribute15 := l_acd_rec.attribute15;
2186 END IF;
2187 IF (x_acd_rec.created_by = OKC_API.G_MISS_NUM)
2188 THEN
2189 x_acd_rec.created_by := l_acd_rec.created_by;
2190 END IF;
2191 IF (x_acd_rec.creation_date = OKC_API.G_MISS_DATE)
2192 THEN
2193 x_acd_rec.creation_date := l_acd_rec.creation_date;
2194 END IF;
2195 IF (x_acd_rec.last_updated_by = OKC_API.G_MISS_NUM)
2196 THEN
2197 x_acd_rec.last_updated_by := l_acd_rec.last_updated_by;
2198 END IF;
2199 IF (x_acd_rec.last_update_date = OKC_API.G_MISS_DATE)
2200 THEN
2201 x_acd_rec.last_update_date := l_acd_rec.last_update_date;
2202 END IF;
2203 IF (x_acd_rec.last_update_login = OKC_API.G_MISS_NUM)
2204 THEN
2205 x_acd_rec.last_update_login := l_acd_rec.last_update_login;
2206 END IF;
2207 RETURN(l_return_status);
2208 END populate_new_record;
2209 -----------------------------------------
2210 -- Set_Attributes for:OKL_ASSET_CNDTNS --
2211 -----------------------------------------
2212 FUNCTION Set_Attributes (
2213 p_acd_rec IN acd_rec_type,
2214 x_acd_rec OUT NOCOPY acd_rec_type
2215 ) RETURN VARCHAR2 IS
2216 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2217 BEGIN
2218 x_acd_rec := p_acd_rec;
2219 RETURN(l_return_status);
2220 END Set_Attributes;
2221 BEGIN
2222 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2223 p_init_msg_list,
2224 '_PVT',
2225 x_return_status);
2226 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2227 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2228 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2229 RAISE OKC_API.G_EXCEPTION_ERROR;
2230 END IF;
2231 --- Setting item attributes
2232 l_return_status := Set_Attributes(
2233 p_acd_rec, -- IN
2234 l_acd_rec); -- OUT
2235 --- If any errors happen abort API
2236 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2237 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2238 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2239 RAISE OKC_API.G_EXCEPTION_ERROR;
2240 END IF;
2241 l_return_status := populate_new_record(l_acd_rec, l_def_acd_rec);
2242 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2243 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2244 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2245 RAISE OKC_API.G_EXCEPTION_ERROR;
2246 END IF;
2247 UPDATE OKL_ASSET_CNDTNS
2248 SET DATE_REPORT = l_def_acd_rec.date_report,
2249 CDN_CODE = l_def_acd_rec.cdn_code,
2250 CLM_ID = l_def_acd_rec.clm_id,
2251 ISP_ID = l_def_acd_rec.isp_id,
2252 ILN_ID = l_def_acd_rec.iln_id,
2253 KLE_ID = l_def_acd_rec.kle_id,
2254 OBJECT_VERSION_NUMBER = l_def_acd_rec.object_version_number,
2255 ORG_ID = l_def_acd_rec.org_id,
2256 /* REQUEST_ID = l_def_acd_rec.request_id,
2257 PROGRAM_APPLICATION_ID = l_def_acd_rec.program_application_id,
2258 PROGRAM_ID = l_def_acd_rec.program_id,
2259 PROGRAM_UPDATE_DATE = l_def_acd_rec.program_update_date,*/
2260 REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),l_def_acd_rec.request_id),
2261 PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),l_def_acd_rec.program_application_id),
2262 PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),l_def_acd_rec.program_id),
2263 PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,l_def_acd_rec.program_update_date,SYSDATE),
2264 ATTRIBUTE_CATEGORY = l_def_acd_rec.attribute_category,
2265 ATTRIBUTE1 = l_def_acd_rec.attribute1,
2266 ATTRIBUTE2 = l_def_acd_rec.attribute2,
2267 ATTRIBUTE3 = l_def_acd_rec.attribute3,
2268 ATTRIBUTE4 = l_def_acd_rec.attribute4,
2269 ATTRIBUTE5 = l_def_acd_rec.attribute5,
2270 ATTRIBUTE6 = l_def_acd_rec.attribute6,
2271 ATTRIBUTE7 = l_def_acd_rec.attribute7,
2272 ATTRIBUTE8 = l_def_acd_rec.attribute8,
2273 ATTRIBUTE9 = l_def_acd_rec.attribute9,
2274 ATTRIBUTE10 = l_def_acd_rec.attribute10,
2275 ATTRIBUTE11 = l_def_acd_rec.attribute11,
2276 ATTRIBUTE12 = l_def_acd_rec.attribute12,
2277 ATTRIBUTE13 = l_def_acd_rec.attribute13,
2278 ATTRIBUTE14 = l_def_acd_rec.attribute14,
2279 ATTRIBUTE15 = l_def_acd_rec.attribute15,
2280 CREATED_BY = l_def_acd_rec.created_by,
2281 CREATION_DATE = l_def_acd_rec.creation_date,
2282 LAST_UPDATED_BY = l_def_acd_rec.last_updated_by,
2283 LAST_UPDATE_DATE = l_def_acd_rec.last_update_date,
2284 LAST_UPDATE_LOGIN = l_def_acd_rec.last_update_login
2285 WHERE ID = l_def_acd_rec.id;
2286
2287 x_acd_rec := l_def_acd_rec;
2288 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2289 EXCEPTION
2290 WHEN G_EXCEPTION_HALT_VALIDATION then
2291 -- No action necessary. Validation can continue to next attribute/column
2292 null;
2293
2294 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2295 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2296 (
2297 l_api_name,
2298 G_PKG_NAME,
2299 'OKC_API.G_RET_STS_ERROR',
2300 x_msg_count,
2301 x_msg_data,
2302 '_PVT'
2303 );
2304 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2305 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2306 (
2307 l_api_name,
2308 G_PKG_NAME,
2309 'OKC_API.G_RET_STS_UNEXP_ERROR',
2310 x_msg_count,
2311 x_msg_data,
2312 '_PVT'
2313 );
2314 WHEN OTHERS THEN
2315 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2316 (
2317 l_api_name,
2318 G_PKG_NAME,
2319 'OTHERS',
2320 x_msg_count,
2321 x_msg_data,
2322 '_PVT'
2323 );
2324 END update_row;
2325 ---------------------------------------
2326 -- update_row for:OKL_ASSET_CNDTNS_V --
2327 ---------------------------------------
2328 PROCEDURE update_row(
2329 p_api_version IN NUMBER,
2330 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2331 x_return_status OUT NOCOPY VARCHAR2,
2332 x_msg_count OUT NOCOPY NUMBER,
2333 x_msg_data OUT NOCOPY VARCHAR2,
2334 p_acdv_rec IN acdv_rec_type,
2335 x_acdv_rec OUT NOCOPY acdv_rec_type) IS
2336
2337 l_api_version CONSTANT NUMBER := 1;
2338 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2339 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2340 l_acdv_rec acdv_rec_type := p_acdv_rec;
2341 l_def_acdv_rec acdv_rec_type;
2342 l_acd_rec acd_rec_type;
2343 lx_acd_rec acd_rec_type;
2344 -------------------------------
2345 -- FUNCTION fill_who_columns --
2346 -------------------------------
2347 FUNCTION fill_who_columns (
2348 p_acdv_rec IN acdv_rec_type
2349 ) RETURN acdv_rec_type IS
2350 l_acdv_rec acdv_rec_type := p_acdv_rec;
2351 BEGIN
2352 l_acdv_rec.LAST_UPDATE_DATE := SYSDATE;
2353 l_acdv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2354 l_acdv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2355 RETURN(l_acdv_rec);
2356 END fill_who_columns;
2357 ----------------------------------
2358 -- FUNCTION populate_new_record --
2359 ----------------------------------
2360 FUNCTION populate_new_record (
2361 p_acdv_rec IN acdv_rec_type,
2362 x_acdv_rec OUT NOCOPY acdv_rec_type
2363 ) RETURN VARCHAR2 IS
2364 l_acdv_rec acdv_rec_type;
2365 l_row_notfound BOOLEAN := TRUE;
2366 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2367 BEGIN
2368 x_acdv_rec := p_acdv_rec;
2369 -- Get current database values
2370 l_acdv_rec := get_rec(p_acdv_rec, l_row_notfound);
2371 IF (l_row_notfound) THEN
2372 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2373 END IF;
2374 IF (x_acdv_rec.id = OKC_API.G_MISS_NUM)
2375 THEN
2376 x_acdv_rec.id := l_acdv_rec.id;
2377 END IF;
2378 IF (x_acdv_rec.object_version_number = OKC_API.G_MISS_NUM)
2379 THEN
2380 x_acdv_rec.object_version_number := l_acdv_rec.object_version_number;
2381 END IF;
2382 IF (x_acdv_rec.cdn_code = OKC_API.G_MISS_CHAR)
2383 THEN
2384 x_acdv_rec.cdn_code := l_acdv_rec.cdn_code;
2385 END IF;
2386 IF (x_acdv_rec.iln_id = OKC_API.G_MISS_NUM)
2387 THEN
2388 x_acdv_rec.iln_id := l_acdv_rec.iln_id;
2389 END IF;
2390 IF (x_acdv_rec.isp_id = OKC_API.G_MISS_NUM)
2391 THEN
2392 x_acdv_rec.isp_id := l_acdv_rec.isp_id;
2393 END IF;
2394 IF (x_acdv_rec.clm_id = OKC_API.G_MISS_NUM)
2395 THEN
2396 x_acdv_rec.clm_id := l_acdv_rec.clm_id;
2397 END IF;
2398 IF (x_acdv_rec.kle_id = OKC_API.G_MISS_NUM)
2399 THEN
2400 x_acdv_rec.kle_id := l_acdv_rec.kle_id;
2401 END IF;
2402 IF (x_acdv_rec.date_report = OKC_API.G_MISS_DATE)
2403 THEN
2404 x_acdv_rec.date_report := l_acdv_rec.date_report;
2405 END IF;
2406 IF (x_acdv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2407 THEN
2408 x_acdv_rec.attribute_category := l_acdv_rec.attribute_category;
2409 END IF;
2410 IF (x_acdv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2411 THEN
2412 x_acdv_rec.attribute1 := l_acdv_rec.attribute1;
2413 END IF;
2414 IF (x_acdv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2415 THEN
2416 x_acdv_rec.attribute2 := l_acdv_rec.attribute2;
2417 END IF;
2418 IF (x_acdv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2419 THEN
2420 x_acdv_rec.attribute3 := l_acdv_rec.attribute3;
2421 END IF;
2422 IF (x_acdv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2423 THEN
2424 x_acdv_rec.attribute4 := l_acdv_rec.attribute4;
2425 END IF;
2426 IF (x_acdv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2427 THEN
2428 x_acdv_rec.attribute5 := l_acdv_rec.attribute5;
2429 END IF;
2430 IF (x_acdv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2431 THEN
2432 x_acdv_rec.attribute6 := l_acdv_rec.attribute6;
2433 END IF;
2434 IF (x_acdv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2435 THEN
2436 x_acdv_rec.attribute7 := l_acdv_rec.attribute7;
2437 END IF;
2438 IF (x_acdv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2439 THEN
2440 x_acdv_rec.attribute8 := l_acdv_rec.attribute8;
2441 END IF;
2442 IF (x_acdv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2443 THEN
2444 x_acdv_rec.attribute9 := l_acdv_rec.attribute9;
2445 END IF;
2446 IF (x_acdv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2447 THEN
2448 x_acdv_rec.attribute10 := l_acdv_rec.attribute10;
2449 END IF;
2450 IF (x_acdv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2451 THEN
2452 x_acdv_rec.attribute11 := l_acdv_rec.attribute11;
2453 END IF;
2454 IF (x_acdv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2455 THEN
2456 x_acdv_rec.attribute12 := l_acdv_rec.attribute12;
2457 END IF;
2458 IF (x_acdv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2459 THEN
2460 x_acdv_rec.attribute13 := l_acdv_rec.attribute13;
2461 END IF;
2462 IF (x_acdv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2463 THEN
2464 x_acdv_rec.attribute14 := l_acdv_rec.attribute14;
2465 END IF;
2466 IF (x_acdv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2467 THEN
2468 x_acdv_rec.attribute15 := l_acdv_rec.attribute15;
2469 END IF;
2470 IF (x_acdv_rec.org_id = OKC_API.G_MISS_NUM)
2471 THEN
2472 x_acdv_rec.org_id := l_acdv_rec.org_id;
2473 END IF;
2474 IF (x_acdv_rec.request_id = OKC_API.G_MISS_NUM)
2475 THEN
2476 x_acdv_rec.request_id := l_acdv_rec.request_id;
2477 END IF;
2478 IF (x_acdv_rec.program_application_id = OKC_API.G_MISS_NUM)
2479 THEN
2480 x_acdv_rec.program_application_id := l_acdv_rec.program_application_id;
2481 END IF;
2482 IF (x_acdv_rec.program_id = OKC_API.G_MISS_NUM)
2483 THEN
2484 x_acdv_rec.program_id := l_acdv_rec.program_id;
2485 END IF;
2486 IF (x_acdv_rec.program_update_date = OKC_API.G_MISS_DATE)
2487 THEN
2488 x_acdv_rec.program_update_date := l_acdv_rec.program_update_date;
2489 END IF;
2490 IF (x_acdv_rec.created_by = OKC_API.G_MISS_NUM)
2491 THEN
2492 x_acdv_rec.created_by := l_acdv_rec.created_by;
2493 END IF;
2494 IF (x_acdv_rec.creation_date = OKC_API.G_MISS_DATE)
2495 THEN
2496 x_acdv_rec.creation_date := l_acdv_rec.creation_date;
2497 END IF;
2498 IF (x_acdv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2499 THEN
2500 x_acdv_rec.last_updated_by := l_acdv_rec.last_updated_by;
2501 END IF;
2502 IF (x_acdv_rec.last_update_date = OKC_API.G_MISS_DATE)
2503 THEN
2504 x_acdv_rec.last_update_date := l_acdv_rec.last_update_date;
2505 END IF;
2506 IF (x_acdv_rec.last_update_login = OKC_API.G_MISS_NUM)
2507 THEN
2508 x_acdv_rec.last_update_login := l_acdv_rec.last_update_login;
2509 END IF;
2510 RETURN(l_return_status);
2511 END populate_new_record;
2512 -------------------------------------------
2513 -- Set_Attributes for:OKL_ASSET_CNDTNS_V --
2514 -------------------------------------------
2515 FUNCTION Set_Attributes (
2516 p_acdv_rec IN acdv_rec_type,
2517 x_acdv_rec OUT NOCOPY acdv_rec_type
2518 ) RETURN VARCHAR2 IS
2519 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2520 BEGIN
2521 x_acdv_rec := p_acdv_rec;
2522 x_acdv_rec.OBJECT_VERSION_NUMBER := NVL(x_acdv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2523 RETURN(l_return_status);
2524 END Set_Attributes;
2525 BEGIN
2526 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2527 G_PKG_NAME,
2528 p_init_msg_list,
2529 l_api_version,
2530 p_api_version,
2531 '_PVT',
2532 x_return_status);
2533 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2534 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2535 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2536 RAISE OKC_API.G_EXCEPTION_ERROR;
2537 END IF;
2538 --- Setting item attributes
2539 l_return_status := Set_Attributes(
2540 p_acdv_rec, -- IN
2541 l_acdv_rec); -- OUT
2542 --- If any errors happen abort API
2543 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2544 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2545 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2546 RAISE OKC_API.G_EXCEPTION_ERROR;
2547 END IF;
2548 l_return_status := populate_new_record(l_acdv_rec, l_def_acdv_rec);
2549 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2550 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2551 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2552 RAISE OKC_API.G_EXCEPTION_ERROR;
2553 END IF;
2554 l_def_acdv_rec := fill_who_columns(l_def_acdv_rec);
2555 --- Validate all non-missing attributes (Item Level Validation)
2556 l_return_status := Validate_Attributes(l_def_acdv_rec);
2557 --- If any errors happen abort API
2558 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2559 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2560 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2561 RAISE OKC_API.G_EXCEPTION_ERROR;
2562 END IF;
2563 l_return_status := Validate_Record(l_def_acdv_rec);
2564 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2565 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2566 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2567 RAISE OKC_API.G_EXCEPTION_ERROR;
2568 END IF;
2569
2570 --------------------------------------
2571 -- Move VIEW record to "Child" records
2572 --------------------------------------
2573 migrate(l_def_acdv_rec, l_acd_rec);
2574 --------------------------------------------
2575 -- Call the UPDATE_ROW for each child record
2576 --------------------------------------------
2577 update_row(
2578 p_init_msg_list,
2579 x_return_status,
2580 x_msg_count,
2581 x_msg_data,
2582 l_acd_rec,
2583 lx_acd_rec
2584 );
2585 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2586 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2587 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2588 RAISE OKC_API.G_EXCEPTION_ERROR;
2589 END IF;
2590 migrate(lx_acd_rec, l_def_acdv_rec);
2591 x_acdv_rec := l_def_acdv_rec;
2592 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2593 EXCEPTION
2594 WHEN G_EXCEPTION_HALT_VALIDATION then
2595 -- No action necessary. Validation can continue to next attribute/column
2596 null;
2597
2598 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2599 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2600 (
2601 l_api_name,
2602 G_PKG_NAME,
2603 'OKC_API.G_RET_STS_ERROR',
2604 x_msg_count,
2605 x_msg_data,
2606 '_PVT'
2607 );
2608 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2609 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2610 (
2611 l_api_name,
2612 G_PKG_NAME,
2613 'OKC_API.G_RET_STS_UNEXP_ERROR',
2614 x_msg_count,
2615 x_msg_data,
2616 '_PVT'
2617 );
2618 WHEN OTHERS THEN
2619 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2620 (
2621 l_api_name,
2622 G_PKG_NAME,
2623 'OTHERS',
2624 x_msg_count,
2625 x_msg_data,
2626 '_PVT'
2627 );
2628 END update_row;
2629 ----------------------------------------
2630 -- PL/SQL TBL update_row for:ACDV_TBL --
2631 ----------------------------------------
2632 PROCEDURE update_row(
2633 p_api_version IN NUMBER,
2634 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2635 x_return_status OUT NOCOPY VARCHAR2,
2636 x_msg_count OUT NOCOPY NUMBER,
2637 x_msg_data OUT NOCOPY VARCHAR2,
2638 p_acdv_tbl IN acdv_tbl_type,
2639 x_acdv_tbl OUT NOCOPY acdv_tbl_type) IS
2640
2641 l_api_version CONSTANT NUMBER := 1;
2642 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2643 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2644 i NUMBER := 0;
2645 -- Begin Post-Generation Change
2646 -- overall error status
2647 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2648 -- End Post-Generation Change
2649 BEGIN
2650 OKC_API.init_msg_list(p_init_msg_list);
2651 -- Make sure PL/SQL table has records in it before passing
2652 IF (p_acdv_tbl.COUNT > 0) THEN
2653 i := p_acdv_tbl.FIRST;
2654 LOOP
2655 update_row (
2656 p_api_version => p_api_version,
2657 p_init_msg_list => OKC_API.G_FALSE,
2658 x_return_status => x_return_status,
2659 x_msg_count => x_msg_count,
2660 x_msg_data => x_msg_data,
2661 p_acdv_rec => p_acdv_tbl(i),
2662 x_acdv_rec => x_acdv_tbl(i));
2663 -- Begin Post-Generation Change
2664 -- store the highest degree of error
2665 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2666 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2667 l_overall_status := x_return_status;
2668 END IF;
2669 END IF;
2670 -- End Post-Generation Change
2671 EXIT WHEN (i = p_acdv_tbl.LAST);
2672 i := p_acdv_tbl.NEXT(i);
2673 END LOOP;
2674 -- Begin Post-Generation Change
2675 -- return overall status
2676 x_return_status := l_overall_status;
2677 -- End Post-Generation Change
2678 END IF;
2679 EXCEPTION
2680 WHEN G_EXCEPTION_HALT_VALIDATION then
2681 -- No action necessary. Validation can continue to next attribute/column
2682 null;
2683
2684 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2685 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2686 (
2687 l_api_name,
2688 G_PKG_NAME,
2689 'OKC_API.G_RET_STS_ERROR',
2690 x_msg_count,
2691 x_msg_data,
2692 '_PVT'
2693 );
2694 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2695 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2696 (
2697 l_api_name,
2698 G_PKG_NAME,
2699 'OKC_API.G_RET_STS_UNEXP_ERROR',
2700 x_msg_count,
2701 x_msg_data,
2702 '_PVT'
2703 );
2704 WHEN OTHERS THEN
2705 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2706 (
2707 l_api_name,
2708 G_PKG_NAME,
2709 'OTHERS',
2710 x_msg_count,
2711 x_msg_data,
2712 '_PVT'
2713 );
2714 END update_row;
2715
2716 ---------------------------------------------------------------------------
2717 -- PROCEDURE delete_row
2718 ---------------------------------------------------------------------------
2719 -------------------------------------
2720 -- delete_row for:OKL_ASSET_CNDTNS --
2721 -------------------------------------
2722 PROCEDURE delete_row(
2723 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2724 x_return_status OUT NOCOPY VARCHAR2,
2725 x_msg_count OUT NOCOPY NUMBER,
2726 x_msg_data OUT NOCOPY VARCHAR2,
2727 p_acd_rec IN acd_rec_type) IS
2728
2729 l_api_version CONSTANT NUMBER := 1;
2730 l_api_name CONSTANT VARCHAR2(30) := 'CNDTNS_delete_row';
2731 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2732 l_acd_rec acd_rec_type:= p_acd_rec;
2733 l_row_notfound BOOLEAN := TRUE;
2734 BEGIN
2735 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2736 p_init_msg_list,
2737 '_PVT',
2738 x_return_status);
2739 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2740 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2741 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2742 RAISE OKC_API.G_EXCEPTION_ERROR;
2743 END IF;
2744 DELETE FROM OKL_ASSET_CNDTNS
2745 WHERE ID = l_acd_rec.id;
2746
2747 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2748 EXCEPTION
2749 WHEN G_EXCEPTION_HALT_VALIDATION then
2750 -- No action necessary. Validation can continue to next attribute/column
2751 null;
2752
2753 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2754 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2755 (
2756 l_api_name,
2757 G_PKG_NAME,
2758 'OKC_API.G_RET_STS_ERROR',
2759 x_msg_count,
2760 x_msg_data,
2761 '_PVT'
2762 );
2763 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2764 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2765 (
2766 l_api_name,
2767 G_PKG_NAME,
2768 'OKC_API.G_RET_STS_UNEXP_ERROR',
2769 x_msg_count,
2770 x_msg_data,
2771 '_PVT'
2772 );
2773 WHEN OTHERS THEN
2774 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2775 (
2776 l_api_name,
2777 G_PKG_NAME,
2778 'OTHERS',
2779 x_msg_count,
2780 x_msg_data,
2781 '_PVT'
2782 );
2783 END delete_row;
2784 ---------------------------------------
2785 -- delete_row for:OKL_ASSET_CNDTNS_V --
2786 ---------------------------------------
2787 PROCEDURE delete_row(
2788 p_api_version IN NUMBER,
2789 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2790 x_return_status OUT NOCOPY VARCHAR2,
2791 x_msg_count OUT NOCOPY NUMBER,
2792 x_msg_data OUT NOCOPY VARCHAR2,
2793 p_acdv_rec IN acdv_rec_type) IS
2794
2795 l_api_version CONSTANT NUMBER := 1;
2796 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2797 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2798 l_acdv_rec acdv_rec_type := p_acdv_rec;
2799 l_acd_rec acd_rec_type;
2800 BEGIN
2801 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2802 G_PKG_NAME,
2803 p_init_msg_list,
2804 l_api_version,
2805 p_api_version,
2806 '_PVT',
2807 x_return_status);
2808 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2809 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2810 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2811 RAISE OKC_API.G_EXCEPTION_ERROR;
2812 END IF;
2813 --------------------------------------
2814 -- Move VIEW record to "Child" records
2815 --------------------------------------
2816 migrate(l_acdv_rec, l_acd_rec);
2817 --------------------------------------------
2818 -- Call the DELETE_ROW for each child record
2819 --------------------------------------------
2820 delete_row(
2821 p_init_msg_list,
2822 x_return_status,
2823 x_msg_count,
2824 x_msg_data,
2825 l_acd_rec
2826 );
2827 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2828 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2829 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2830 RAISE OKC_API.G_EXCEPTION_ERROR;
2831 END IF;
2832 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2833 EXCEPTION
2834 WHEN G_EXCEPTION_HALT_VALIDATION then
2835 -- No action necessary. Validation can continue to next attribute/column
2836 null;
2837
2838 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2839 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2840 (
2841 l_api_name,
2842 G_PKG_NAME,
2843 'OKC_API.G_RET_STS_ERROR',
2844 x_msg_count,
2845 x_msg_data,
2846 '_PVT'
2847 );
2848 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2849 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2850 (
2851 l_api_name,
2852 G_PKG_NAME,
2853 'OKC_API.G_RET_STS_UNEXP_ERROR',
2854 x_msg_count,
2855 x_msg_data,
2856 '_PVT'
2857 );
2858 WHEN OTHERS THEN
2859 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2860 (
2861 l_api_name,
2862 G_PKG_NAME,
2863 'OTHERS',
2864 x_msg_count,
2865 x_msg_data,
2866 '_PVT'
2867 );
2868 END delete_row;
2869 ----------------------------------------
2870 -- PL/SQL TBL delete_row for:ACDV_TBL --
2871 ----------------------------------------
2872 PROCEDURE delete_row(
2873 p_api_version IN NUMBER,
2874 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2875 x_return_status OUT NOCOPY VARCHAR2,
2876 x_msg_count OUT NOCOPY NUMBER,
2877 x_msg_data OUT NOCOPY VARCHAR2,
2878 p_acdv_tbl IN acdv_tbl_type) IS
2879
2880 l_api_version CONSTANT NUMBER := 1;
2881 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2882 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2883 i NUMBER := 0;
2884 -- Begin Post-Generation Change
2885 -- overall error status
2886 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2887 -- End Post-Generation Change
2888 BEGIN
2889 OKC_API.init_msg_list(p_init_msg_list);
2890 -- Make sure PL/SQL table has records in it before passing
2891 IF (p_acdv_tbl.COUNT > 0) THEN
2892 i := p_acdv_tbl.FIRST;
2893 LOOP
2894 delete_row (
2895 p_api_version => p_api_version,
2896 p_init_msg_list => OKC_API.G_FALSE,
2897 x_return_status => x_return_status,
2898 x_msg_count => x_msg_count,
2899 x_msg_data => x_msg_data,
2900 p_acdv_rec => p_acdv_tbl(i));
2901 -- Begin Post-Generation Change
2902 -- store the highest degree of error
2903 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2904 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2905 l_overall_status := x_return_status;
2906 END IF;
2907 END IF;
2908 -- End Post-Generation Change
2909 EXIT WHEN (i = p_acdv_tbl.LAST);
2910 i := p_acdv_tbl.NEXT(i);
2911 END LOOP;
2912 -- Begin Post-Generation Change
2913 -- return overall status
2914 x_return_status := l_overall_status;
2915 -- End Post-Generation Change
2916 END IF;
2917 EXCEPTION
2918 WHEN G_EXCEPTION_HALT_VALIDATION then
2919 -- No action necessary. Validation can continue to next attribute/column
2920 null;
2921
2922 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2923 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2924 (
2925 l_api_name,
2926 G_PKG_NAME,
2927 'OKC_API.G_RET_STS_ERROR',
2928 x_msg_count,
2929 x_msg_data,
2930 '_PVT'
2931 );
2932 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2933 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2934 (
2935 l_api_name,
2936 G_PKG_NAME,
2937 'OKC_API.G_RET_STS_UNEXP_ERROR',
2938 x_msg_count,
2939 x_msg_data,
2940 '_PVT'
2941 );
2942 WHEN OTHERS THEN
2943 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2944 (
2945 l_api_name,
2946 G_PKG_NAME,
2947 'OTHERS',
2948 x_msg_count,
2949 x_msg_data,
2950 '_PVT'
2951 );
2952 END delete_row;
2953 END OKL_ACD_PVT;