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