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