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