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