[Home] [Help]
PACKAGE BODY: APPS.OKL_CLM_PVT
Source
1 PACKAGE BODY OKL_CLM_PVT AS
2 /* $Header: OKLSCLMB.pls 120.5.12010000.2 2008/09/03 23:15:09 rmunjulu ship $ */
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 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
10 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
11 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
12 BEGIN
13 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
14 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
15 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
16 -- automatically increments the index by 1, (making it 2), however, when the GET function
17 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
18 -- message 2. To circumvent this problem, check the amount of messages and compensate.
19 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
20 -- will only update the index variable when 1 and only 1 message is on the stack.
21 IF (last_msg_idx = 1) THEN
22 l_msg_idx := FND_MSG_PUB.G_FIRST;
23 END IF;
24 LOOP
25 fnd_msg_pub.get(
26 p_msg_index => l_msg_idx,
27 p_encoded => fnd_api.g_false,
28 p_data => px_error_rec.msg_data,
29 p_msg_index_out => px_error_rec.msg_count);
30 px_error_tbl(j) := px_error_rec;
31 j := j + 1;
32 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
33 END LOOP;
34 END load_error_tbl;
35 ---------------------------------------------------------------------------
36 -- FUNCTION find_highest_exception
37 ---------------------------------------------------------------------------
38 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
39 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
40 FUNCTION find_highest_exception(
41 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
42 ) RETURN VARCHAR2 IS
43 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
44 i INTEGER := 1;
45 BEGIN
46 IF (p_error_tbl.COUNT > 0) THEN
47 i := p_error_tbl.FIRST;
48 LOOP
49 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
50 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
51 l_return_status := p_error_tbl(i).error_type;
52 END IF;
53 END IF;
54 EXIT WHEN (i = p_error_tbl.LAST);
55 i := p_error_tbl.NEXT(i);
56 END LOOP;
57 END IF;
58 RETURN(l_return_status);
59 END find_highest_exception;
60 ---------------------------------------------------------------------------
61 -- FUNCTION get_seq_id
62 ---------------------------------------------------------------------------
63 FUNCTION get_seq_id RETURN NUMBER IS
64 BEGIN
65 RETURN(okc_p_util.raw_to_number(sys_guid()));
66 END get_seq_id;
67 ---------------------------------------------------------------------------
68 --Smoduga added for inventory org fix 3348924
69 -- FUNCTION get_inv_org_id
70 ---------------------------------------------------------------------------
71 FUNCTION get_inv_org_id(policy_id IN NUMBER) RETURN NUMBER IS
72 cursor c_inv_org(p_id number) is
73 select org_id
74 from okl_ins_policies_b
75 where id = p_id;
76 l_inv_org_id NUMBER;
77 BEGIN
78 open c_inv_org(policy_id);
79 fetch c_inv_org into l_inv_org_id;
80 close c_inv_org;
81 RETURN(l_inv_org_id);
82 END get_inv_org_id;
83 ---------------------------------------------------------------------------
84 -- PROCEDURE qc
85 ---------------------------------------------------------------------------
86 PROCEDURE qc IS
87 BEGIN
88 null;
89 END qc;
90 ---------------------------------------------------------------------------
91 -- PROCEDURE change_version
92 ---------------------------------------------------------------------------
93 PROCEDURE change_version IS
94 BEGIN
95 null;
96 END change_version;
97 ---------------------------------------------------------------------------
98 -- PROCEDURE api_copy
99 ---------------------------------------------------------------------------
100 PROCEDURE api_copy IS
101 BEGIN
102 null;
103 END api_copy;
104 ---------------------------------------------------------------------------
105 -- PROCEDURE add_language
106 ---------------------------------------------------------------------------
107 PROCEDURE add_language IS
108 BEGIN
109 DELETE FROM OKL_INS_CLAIMS_TL T
110 WHERE NOT EXISTS (
111 SELECT NULL
112 FROM OKL_INS_CLAIMS_ALL_B B --fixed bug 3321017 by kmotepal
113 WHERE B.ID =T.ID
114 );
115 UPDATE OKL_INS_CLAIMS_TL T SET(
116 DESCRIPTION,
117 POLICE_REPORT,
118 COMMENTS) = (SELECT
119 B.DESCRIPTION,
120 B.POLICE_REPORT,
121 B.COMMENTS
122 FROM OKL_INS_CLAIMS_TL B
123 WHERE B.ID = T.ID
124 AND B.LANGUAGE = T.SOURCE_LANG)
125 WHERE ( T.ID,
126 T.LANGUAGE)
127 IN (SELECT
128 SUBT.ID,
129 SUBT.LANGUAGE
130 FROM OKL_INS_CLAIMS_TL SUBB, OKL_INS_CLAIMS_TL SUBT
131 WHERE SUBB.ID = SUBT.ID
132 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
133 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
134 OR SUBB.POLICE_REPORT <> SUBT.POLICE_REPORT
135 OR SUBB.COMMENTS <> SUBT.COMMENTS
136 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
137 OR (SUBB.POLICE_REPORT IS NULL AND SUBT.POLICE_REPORT IS NOT NULL)
138 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
139 ));
140 INSERT INTO OKL_INS_CLAIMS_TL (
141 ID,
142 LANGUAGE,
143 DESCRIPTION,
144 POLICE_REPORT,
145 COMMENTS,
146 SOURCE_LANG,
147 SFWT_FLAG,
148 CREATED_BY,
149 CREATION_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_DATE,
152 LAST_UPDATE_LOGIN)
153 SELECT
154 B.ID,
155 L.LANGUAGE_CODE,
156 B.DESCRIPTION,
157 B.POLICE_REPORT,
158 B.COMMENTS,
159 B.SOURCE_LANG,
160 B.SFWT_FLAG,
161 B.CREATED_BY,
162 B.CREATION_DATE,
163 B.LAST_UPDATED_BY,
164 B.LAST_UPDATE_DATE,
165 B.LAST_UPDATE_LOGIN
166 FROM OKL_INS_CLAIMS_TL B, FND_LANGUAGES L
167 WHERE L.INSTALLED_FLAG IN ('I', 'B')
168 AND B.LANGUAGE = USERENV('LANG')
169 AND NOT EXISTS (
170 SELECT NULL
171 FROM OKL_INS_CLAIMS_TL T
172 WHERE T.ID = B.ID
173 AND T.LANGUAGE = L.LANGUAGE_CODE
174 );
175 END add_language;
176 ---------------------------------------------------------------------------
177 -- FUNCTION get_rec for: OKL_INS_CLAIMS_V
178 ---------------------------------------------------------------------------
179 FUNCTION get_rec (
180 p_clmv_rec IN clmv_rec_type,
181 x_no_data_found OUT NOCOPY BOOLEAN
182 ) RETURN clmv_rec_type IS
183 CURSOR okl_clmv_pk_csr (p_id IN NUMBER) IS
184 SELECT
185 ID,
186 OBJECT_VERSION_NUMBER,
187 SFWT_FLAG,
188 IPY_ID,
189 LTP_CODE,
190 CSU_CODE,
191 CLAIM_NUMBER,
192 CLAIM_DATE,
193 LOSS_DATE,
194 DESCRIPTION,
195 POLICE_CONTACT,
196 POLICE_REPORT,
197 AMOUNT,
198 ATTRIBUTE_CATEGORY,
199 ATTRIBUTE1,
200 ATTRIBUTE2,
201 ATTRIBUTE3,
202 ATTRIBUTE4,
203 ATTRIBUTE5,
204 ATTRIBUTE6,
205 ATTRIBUTE7,
206 ATTRIBUTE8,
207 ATTRIBUTE9,
208 ATTRIBUTE10,
209 ATTRIBUTE11,
210 ATTRIBUTE12,
211 ATTRIBUTE13,
212 ATTRIBUTE14,
213 ATTRIBUTE15,
214 HOLD_DATE,
215 ORG_ID,
216 REQUEST_ID,
217 PROGRAM_APPLICATION_ID,
218 PROGRAM_ID,
219 PROGRAM_UPDATE_DATE,
220 CREATED_BY,
221 CREATION_DATE,
222 LAST_UPDATED_BY,
223 LAST_UPDATE_DATE,
224 LAST_UPDATE_LOGIN
225 FROM Okl_Ins_Claims_V
226 WHERE okl_ins_claims_v.id = p_id;
227 l_okl_clmv_pk okl_clmv_pk_csr%ROWTYPE;
228 l_clmv_rec clmv_rec_type;
229 BEGIN
230 x_no_data_found := TRUE;
231 -- Get current database values
232 OPEN okl_clmv_pk_csr (p_clmv_rec.id);
233 FETCH okl_clmv_pk_csr INTO
234 l_clmv_rec.id,
235 l_clmv_rec.object_version_number,
236 l_clmv_rec.sfwt_flag,
237 l_clmv_rec.ipy_id,
238 l_clmv_rec.ltp_code,
239 l_clmv_rec.csu_code,
240 l_clmv_rec.claim_number,
241 l_clmv_rec.claim_date,
242 l_clmv_rec.loss_date,
243 l_clmv_rec.description,
244 l_clmv_rec.police_contact,
245 l_clmv_rec.police_report,
246 l_clmv_rec.amount,
247 l_clmv_rec.attribute_category,
248 l_clmv_rec.attribute1,
249 l_clmv_rec.attribute2,
250 l_clmv_rec.attribute3,
251 l_clmv_rec.attribute4,
252 l_clmv_rec.attribute5,
253 l_clmv_rec.attribute6,
254 l_clmv_rec.attribute7,
255 l_clmv_rec.attribute8,
256 l_clmv_rec.attribute9,
257 l_clmv_rec.attribute10,
258 l_clmv_rec.attribute11,
259 l_clmv_rec.attribute12,
260 l_clmv_rec.attribute13,
261 l_clmv_rec.attribute14,
262 l_clmv_rec.attribute15,
263 l_clmv_rec.hold_date,
264 l_clmv_rec.org_id,
265 l_clmv_rec.request_id,
266 l_clmv_rec.program_application_id,
267 l_clmv_rec.program_id,
268 l_clmv_rec.program_update_date,
269 l_clmv_rec.created_by,
270 l_clmv_rec.creation_date,
271 l_clmv_rec.last_updated_by,
272 l_clmv_rec.last_update_date,
273 l_clmv_rec.last_update_login;
274 x_no_data_found := okl_clmv_pk_csr%NOTFOUND;
275 CLOSE okl_clmv_pk_csr;
276 RETURN(l_clmv_rec);
277 END get_rec;
278 ------------------------------------------------------------------
279 -- This version of get_rec sets error messages if no data found --
280 ------------------------------------------------------------------
281 FUNCTION get_rec (
282 p_clmv_rec IN clmv_rec_type,
283 x_return_status OUT NOCOPY VARCHAR2
284 ) RETURN clmv_rec_type IS
285 l_clmv_rec clmv_rec_type;
286 l_row_notfound BOOLEAN := TRUE;
287 BEGIN
288 x_return_status := OKC_API.G_RET_STS_SUCCESS;
289 l_clmv_rec := get_rec(p_clmv_rec, l_row_notfound);
290 IF (l_row_notfound) THEN
291 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
292 x_return_status := OKC_API.G_RET_STS_ERROR;
293 END IF;
294 RETURN(l_clmv_rec);
295 END get_rec;
296 -----------------------------------------------------------
297 -- So we don't have to pass an "l_row_notfound" variable --
298 -----------------------------------------------------------
299 FUNCTION get_rec (
300 p_clmv_rec IN clmv_rec_type
301 ) RETURN clmv_rec_type IS
302 l_row_not_found BOOLEAN := TRUE;
303 BEGIN
304 RETURN(get_rec(p_clmv_rec, l_row_not_found));
305 END get_rec;
306 ---------------------------------------------------------------------------
307 -- FUNCTION get_rec for: OKL_INS_CLAIMS_B
308 ---------------------------------------------------------------------------
309 FUNCTION get_rec (
310 p_clm_rec IN clm_rec_type,
311 x_no_data_found OUT NOCOPY BOOLEAN
312 ) RETURN clm_rec_type IS
313 CURSOR clm_pk_csr (p_id IN NUMBER) IS
314 SELECT
315 ID,
316 CLAIM_NUMBER,
317 CSU_CODE,
318 IPY_ID,
319 LTP_CODE,
320 PROGRAM_UPDATE_DATE,
321 CLAIM_DATE,
322 PROGRAM_ID,
323 LOSS_DATE,
324 POLICE_CONTACT,
325 AMOUNT,
326 OBJECT_VERSION_NUMBER,
327 REQUEST_ID,
328 PROGRAM_APPLICATION_ID,
329 ATTRIBUTE_CATEGORY,
330 ATTRIBUTE1,
331 ATTRIBUTE2,
332 ATTRIBUTE3,
333 ATTRIBUTE4,
334 ATTRIBUTE5,
335 ATTRIBUTE6,
336 ATTRIBUTE7,
337 ATTRIBUTE8,
338 ATTRIBUTE9,
339 ATTRIBUTE10,
340 ATTRIBUTE11,
341 ATTRIBUTE12,
342 ATTRIBUTE13,
343 ATTRIBUTE14,
344 ATTRIBUTE15,
345 HOLD_DATE,
346 ORG_ID,
347 CREATED_BY,
348 CREATION_DATE,
349 LAST_UPDATED_BY,
350 LAST_UPDATE_DATE,
351 LAST_UPDATE_LOGIN
352 FROM Okl_Ins_Claims_B
353 WHERE okl_ins_claims_b.id = p_id;
354 l_clm_pk clm_pk_csr%ROWTYPE;
355 l_clm_rec clm_rec_type;
356 BEGIN
357 x_no_data_found := TRUE;
358 -- Get current database values
359 OPEN clm_pk_csr (p_clm_rec.id);
360 FETCH clm_pk_csr INTO
361 l_clm_rec.id,
362 l_clm_rec.claim_number,
363 l_clm_rec.csu_code,
364 l_clm_rec.ipy_id,
365 l_clm_rec.ltp_code,
366 l_clm_rec.program_update_date,
367 l_clm_rec.claim_date,
368 l_clm_rec.program_id,
369 l_clm_rec.loss_date,
370 l_clm_rec.police_contact,
371 l_clm_rec.amount,
372 l_clm_rec.object_version_number,
373 l_clm_rec.request_id,
374 l_clm_rec.program_application_id,
375 l_clm_rec.attribute_category,
376 l_clm_rec.attribute1,
377 l_clm_rec.attribute2,
378 l_clm_rec.attribute3,
379 l_clm_rec.attribute4,
380 l_clm_rec.attribute5,
381 l_clm_rec.attribute6,
382 l_clm_rec.attribute7,
383 l_clm_rec.attribute8,
384 l_clm_rec.attribute9,
385 l_clm_rec.attribute10,
386 l_clm_rec.attribute11,
387 l_clm_rec.attribute12,
388 l_clm_rec.attribute13,
389 l_clm_rec.attribute14,
390 l_clm_rec.attribute15,
391 l_clm_rec.hold_date,
392 l_clm_rec.org_id,
393 l_clm_rec.created_by,
394 l_clm_rec.creation_date,
395 l_clm_rec.last_updated_by,
396 l_clm_rec.last_update_date,
397 l_clm_rec.last_update_login;
398 x_no_data_found := clm_pk_csr%NOTFOUND;
399 CLOSE clm_pk_csr;
400 RETURN(l_clm_rec);
401 END get_rec;
402 ------------------------------------------------------------------
403 -- This version of get_rec sets error messages if no data found --
404 ------------------------------------------------------------------
405 FUNCTION get_rec (
406 p_clm_rec IN clm_rec_type,
407 x_return_status OUT NOCOPY VARCHAR2
408 ) RETURN clm_rec_type IS
409 l_clm_rec clm_rec_type;
410 l_row_notfound BOOLEAN := TRUE;
411 BEGIN
412 x_return_status := OKC_API.G_RET_STS_SUCCESS;
413 l_clm_rec := get_rec(p_clm_rec, l_row_notfound);
414 IF (l_row_notfound) THEN
415 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
416 x_return_status := OKC_API.G_RET_STS_ERROR;
417 END IF;
418 RETURN(l_clm_rec);
419 END get_rec;
420 -----------------------------------------------------------
421 -- So we don't have to pass an "l_row_notfound" variable --
422 -----------------------------------------------------------
423 FUNCTION get_rec (
424 p_clm_rec IN clm_rec_type
425 ) RETURN clm_rec_type IS
426 l_row_not_found BOOLEAN := TRUE;
427 BEGIN
428 RETURN(get_rec(p_clm_rec, l_row_not_found));
429 END get_rec;
430 ---------------------------------------------------------------------------
431 -- FUNCTION get_rec for: OKL_INS_CLAIMS_TL
432 ---------------------------------------------------------------------------
433 FUNCTION get_rec (
434 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type,
435 x_no_data_found OUT NOCOPY BOOLEAN
436 ) RETURN okl_ins_claims_tl_rec_type IS
437 CURSOR clmt_pk_csr (p_id IN NUMBER,
438 p_language IN VARCHAR2) IS
439 SELECT
440 ID,
441 LANGUAGE,
442 DESCRIPTION,
443 POLICE_REPORT,
444 COMMENTS,
445 SOURCE_LANG,
446 SFWT_FLAG,
447 CREATED_BY,
448 CREATION_DATE,
449 LAST_UPDATED_BY,
450 LAST_UPDATE_DATE,
451 LAST_UPDATE_LOGIN
452 FROM Okl_Ins_Claims_Tl
453 WHERE okl_ins_claims_tl.id = p_id
454 AND okl_ins_claims_tl.language = p_language;
455 l_clmt_pk clmt_pk_csr%ROWTYPE;
456 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
457 BEGIN
458 x_no_data_found := TRUE;
459 -- Get current database values
460 OPEN clmt_pk_csr (p_okl_ins_claims_tl_rec.id,
461 p_okl_ins_claims_tl_rec.language);
462 FETCH clmt_pk_csr INTO
463 l_okl_ins_claims_tl_rec.id,
464 l_okl_ins_claims_tl_rec.language,
465 l_okl_ins_claims_tl_rec.description,
466 l_okl_ins_claims_tl_rec.police_report,
467 l_okl_ins_claims_tl_rec.comments,
468 l_okl_ins_claims_tl_rec.source_lang,
469 l_okl_ins_claims_tl_rec.sfwt_flag,
470 l_okl_ins_claims_tl_rec.created_by,
471 l_okl_ins_claims_tl_rec.creation_date,
472 l_okl_ins_claims_tl_rec.last_updated_by,
473 l_okl_ins_claims_tl_rec.last_update_date,
474 l_okl_ins_claims_tl_rec.last_update_login;
475 x_no_data_found := clmt_pk_csr%NOTFOUND;
476 CLOSE clmt_pk_csr;
477 RETURN(l_okl_ins_claims_tl_rec);
478 END get_rec;
479 ------------------------------------------------------------------
480 -- This version of get_rec sets error messages if no data found --
481 ------------------------------------------------------------------
482 FUNCTION get_rec (
483 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type,
484 x_return_status OUT NOCOPY VARCHAR2
485 ) RETURN okl_ins_claims_tl_rec_type IS
486 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
487 l_row_notfound BOOLEAN := TRUE;
488 BEGIN
489 x_return_status := OKC_API.G_RET_STS_SUCCESS;
490 l_okl_ins_claims_tl_rec := get_rec(p_okl_ins_claims_tl_rec, l_row_notfound);
491 IF (l_row_notfound) THEN
492 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
493 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'LANGUAGE');
494 x_return_status := OKC_API.G_RET_STS_ERROR;
495 END IF;
496 RETURN(l_okl_ins_claims_tl_rec);
497 END get_rec;
498 -----------------------------------------------------------
499 -- So we don't have to pass an "l_row_notfound" variable --
500 -----------------------------------------------------------
501 FUNCTION get_rec (
502 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type
503 ) RETURN okl_ins_claims_tl_rec_type IS
504 l_row_not_found BOOLEAN := TRUE;
505 BEGIN
506 RETURN(get_rec(p_okl_ins_claims_tl_rec, l_row_not_found));
507 END get_rec;
508 ---------------------------------------------------------------------------
509 -- FUNCTION null_out_defaults for: OKL_INS_CLAIMS_V
510 ---------------------------------------------------------------------------
511 FUNCTION null_out_defaults (
512 p_clmv_rec IN clmv_rec_type
513 ) RETURN clmv_rec_type IS
514 l_clmv_rec clmv_rec_type := p_clmv_rec;
515 BEGIN
516 IF (l_clmv_rec.id = OKC_API.G_MISS_NUM ) THEN
517 l_clmv_rec.id := NULL;
518 END IF;
519 IF (l_clmv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
520 l_clmv_rec.object_version_number := NULL;
521 END IF;
522 IF (l_clmv_rec.sfwt_flag = OKC_API.G_MISS_CHAR ) THEN
523 l_clmv_rec.sfwt_flag := NULL;
524 END IF;
525 IF (l_clmv_rec.ipy_id = OKC_API.G_MISS_NUM ) THEN
526 l_clmv_rec.ipy_id := NULL;
527 END IF;
528 IF (l_clmv_rec.ltp_code = OKC_API.G_MISS_CHAR ) THEN
529 l_clmv_rec.ltp_code := NULL;
530 END IF;
531 IF (l_clmv_rec.csu_code = OKC_API.G_MISS_CHAR ) THEN
532 l_clmv_rec.csu_code := NULL;
533 END IF;
534 IF (l_clmv_rec.claim_number = OKC_API.G_MISS_CHAR ) THEN
535 l_clmv_rec.claim_number := NULL;
536 END IF;
537 IF (l_clmv_rec.claim_date = OKC_API.G_MISS_DATE ) THEN
538 l_clmv_rec.claim_date := NULL;
539 END IF;
540 IF (l_clmv_rec.loss_date = OKC_API.G_MISS_DATE ) THEN
541 l_clmv_rec.loss_date := NULL;
542 END IF;
543 IF (l_clmv_rec.description = OKC_API.G_MISS_CHAR ) THEN
544 l_clmv_rec.description := NULL;
545 END IF;
546 IF (l_clmv_rec.police_contact = OKC_API.G_MISS_CHAR ) THEN
547 l_clmv_rec.police_contact := NULL;
548 END IF;
549 IF (l_clmv_rec.police_report = OKC_API.G_MISS_CHAR ) THEN
550 l_clmv_rec.police_report := NULL;
551 END IF;
552 IF (l_clmv_rec.amount = OKC_API.G_MISS_NUM ) THEN
553 l_clmv_rec.amount := NULL;
554 END IF;
555 IF (l_clmv_rec.attribute_category = OKC_API.G_MISS_CHAR ) THEN
556 l_clmv_rec.attribute_category := NULL;
557 END IF;
558 IF (l_clmv_rec.attribute1 = OKC_API.G_MISS_CHAR ) THEN
559 l_clmv_rec.attribute1 := NULL;
560 END IF;
561 IF (l_clmv_rec.attribute2 = OKC_API.G_MISS_CHAR ) THEN
562 l_clmv_rec.attribute2 := NULL;
563 END IF;
564 IF (l_clmv_rec.attribute3 = OKC_API.G_MISS_CHAR ) THEN
565 l_clmv_rec.attribute3 := NULL;
566 END IF;
567 IF (l_clmv_rec.attribute4 = OKC_API.G_MISS_CHAR ) THEN
568 l_clmv_rec.attribute4 := NULL;
569 END IF;
570 IF (l_clmv_rec.attribute5 = OKC_API.G_MISS_CHAR ) THEN
571 l_clmv_rec.attribute5 := NULL;
572 END IF;
573 IF (l_clmv_rec.attribute6 = OKC_API.G_MISS_CHAR ) THEN
574 l_clmv_rec.attribute6 := NULL;
575 END IF;
576 IF (l_clmv_rec.attribute7 = OKC_API.G_MISS_CHAR ) THEN
577 l_clmv_rec.attribute7 := NULL;
578 END IF;
579 IF (l_clmv_rec.attribute8 = OKC_API.G_MISS_CHAR ) THEN
580 l_clmv_rec.attribute8 := NULL;
581 END IF;
582 IF (l_clmv_rec.attribute9 = OKC_API.G_MISS_CHAR ) THEN
583 l_clmv_rec.attribute9 := NULL;
584 END IF;
585 IF (l_clmv_rec.attribute10 = OKC_API.G_MISS_CHAR ) THEN
586 l_clmv_rec.attribute10 := NULL;
587 END IF;
588 IF (l_clmv_rec.attribute11 = OKC_API.G_MISS_CHAR ) THEN
589 l_clmv_rec.attribute11 := NULL;
590 END IF;
591 IF (l_clmv_rec.attribute12 = OKC_API.G_MISS_CHAR ) THEN
592 l_clmv_rec.attribute12 := NULL;
593 END IF;
594 IF (l_clmv_rec.attribute13 = OKC_API.G_MISS_CHAR ) THEN
595 l_clmv_rec.attribute13 := NULL;
596 END IF;
597 IF (l_clmv_rec.attribute14 = OKC_API.G_MISS_CHAR ) THEN
598 l_clmv_rec.attribute14 := NULL;
599 END IF;
600 IF (l_clmv_rec.attribute15 = OKC_API.G_MISS_CHAR ) THEN
601 l_clmv_rec.attribute15 := NULL;
602 END IF;
603 IF (l_clmv_rec.hold_date = OKC_API.G_MISS_DATE ) THEN
604 l_clmv_rec.hold_date := NULL;
605 END IF;
606 IF (l_clmv_rec.org_id = OKC_API.G_MISS_NUM ) THEN
607 l_clmv_rec.org_id := NULL;
608 END IF;
609 IF (l_clmv_rec.request_id = OKC_API.G_MISS_NUM ) THEN
610 l_clmv_rec.request_id := NULL;
611 END IF;
612 IF (l_clmv_rec.program_application_id = OKC_API.G_MISS_NUM ) THEN
613 l_clmv_rec.program_application_id := NULL;
614 END IF;
615 IF (l_clmv_rec.program_id = OKC_API.G_MISS_NUM ) THEN
616 l_clmv_rec.program_id := NULL;
617 END IF;
618 IF (l_clmv_rec.program_update_date = OKC_API.G_MISS_DATE ) THEN
619 l_clmv_rec.program_update_date := NULL;
620 END IF;
621 IF (l_clmv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
622 l_clmv_rec.created_by := NULL;
623 END IF;
624 IF (l_clmv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
625 l_clmv_rec.creation_date := NULL;
626 END IF;
627 IF (l_clmv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
628 l_clmv_rec.last_updated_by := NULL;
629 END IF;
630 IF (l_clmv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
631 l_clmv_rec.last_update_date := NULL;
632 END IF;
633 IF (l_clmv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
634 l_clmv_rec.last_update_login := NULL;
635 END IF;
636 RETURN(l_clmv_rec);
637 END null_out_defaults;
638 ---------------------------------
639 -- Validate_Attributes for: ID --
640 ---------------------------------
641 PROCEDURE validate_id(
642 x_return_status OUT NOCOPY VARCHAR2,
643 p_id IN NUMBER) IS
644 BEGIN
645 x_return_status := OKC_API.G_RET_STS_SUCCESS;
646 IF (p_id = OKC_API.G_MISS_NUM OR
647 p_id IS NULL)
648 THEN
649 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
650 x_return_status := OKC_API.G_RET_STS_ERROR;
651 RAISE G_EXCEPTION_HALT_VALIDATION;
652 END IF;
653 EXCEPTION
654 WHEN G_EXCEPTION_HALT_VALIDATION THEN
655 null;
656 WHEN OTHERS THEN
657 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
658 ,p_msg_name => G_UNEXPECTED_ERROR
659 ,p_token1 => G_SQLCODE_TOKEN
660 ,p_token1_value => SQLCODE
661 ,p_token2 => G_SQLERRM_TOKEN
662 ,p_token2_value => SQLERRM);
663 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
664 END validate_id;
665 ----------------------------------------------------
666 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
667 ----------------------------------------------------
668 PROCEDURE validate_object_version_number(
669 x_return_status OUT NOCOPY VARCHAR2,
670 p_object_version_number IN NUMBER) IS
671 BEGIN
672 x_return_status := OKC_API.G_RET_STS_SUCCESS;
673 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
674 p_object_version_number IS NULL)
675 THEN
676 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
677 x_return_status := OKC_API.G_RET_STS_ERROR;
678 RAISE G_EXCEPTION_HALT_VALIDATION;
679 END IF;
680 EXCEPTION
681 WHEN G_EXCEPTION_HALT_VALIDATION THEN
682 null;
683 WHEN OTHERS THEN
684 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
685 ,p_msg_name => G_UNEXPECTED_ERROR
686 ,p_token1 => G_SQLCODE_TOKEN
687 ,p_token1_value => SQLCODE
688 ,p_token2 => G_SQLERRM_TOKEN
689 ,p_token2_value => SQLERRM);
690 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
691 END validate_object_version_number;
692 ----------------------------------------
693 -- Validate_Attributes for: SFWT_FLAG --
694 ----------------------------------------
695 PROCEDURE validate_sfwt_flag(
696 x_return_status OUT NOCOPY VARCHAR2,
697 p_sfwt_flag IN VARCHAR2) IS
698 BEGIN
699 x_return_status := OKC_API.G_RET_STS_SUCCESS;
700 IF (p_sfwt_flag = OKC_API.G_MISS_CHAR OR
701 p_sfwt_flag IS NULL)
702 THEN
703 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
704 x_return_status := OKC_API.G_RET_STS_ERROR;
705 RAISE G_EXCEPTION_HALT_VALIDATION;
706 END IF;
707 EXCEPTION
708 WHEN G_EXCEPTION_HALT_VALIDATION THEN
709 null;
710 WHEN OTHERS THEN
711 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
712 ,p_msg_name => G_UNEXPECTED_ERROR
713 ,p_token1 => G_SQLCODE_TOKEN
714 ,p_token1_value => SQLCODE
715 ,p_token2 => G_SQLERRM_TOKEN
716 ,p_token2_value => SQLERRM);
717 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
718 END validate_sfwt_flag;
719 -------------------------------------------
720 -- Validate_Attributes for: CLAIM_NUMBER --
721 -------------------------------------------
722 PROCEDURE validate_claim_number(
723 x_return_status OUT NOCOPY VARCHAR2,
724 p_claim_number IN VARCHAR2) IS
725 BEGIN
726 x_return_status := OKC_API.G_RET_STS_SUCCESS;
727 IF (p_claim_number = OKC_API.G_MISS_CHAR OR
728 p_claim_number IS NULL)
729 THEN
730 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Claim Number');
731 x_return_status := OKC_API.G_RET_STS_ERROR;
732 RAISE G_EXCEPTION_HALT_VALIDATION;
733 END IF;
734 EXCEPTION
735 WHEN G_EXCEPTION_HALT_VALIDATION THEN
736 null;
737 WHEN OTHERS THEN
738 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
739 ,p_msg_name => G_UNEXPECTED_ERROR
740 ,p_token1 => G_SQLCODE_TOKEN
741 ,p_token1_value => SQLCODE
742 ,p_token2 => G_SQLERRM_TOKEN
743 ,p_token2_value => SQLERRM);
744 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
745 END validate_claim_number;
746 ----------------------------------------------------
747 -- Validate_Attributes for: IPY_ID --
748 ----------------------------------------------------
749 PROCEDURE validate_ipy_id(
750 x_return_status OUT NOCOPY VARCHAR2,
751 p_ipy_id IN NUMBER) IS
752 l_dummy_var VARCHAR2(1) := '?';
753 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
754 CURSOR okl_ipy_csr IS
755 SELECT 'x'
756 FROM OKL_INS_POLICIES_V
757 WHERE id = p_ipy_id;
758 Begin
759 IF ((p_ipy_id IS NOT NULL) AND (p_ipy_id = OKC_API.G_MISS_NUM)) THEN
760 -- enforce foreign key
761 OPEN okl_ipy_csr;
762 FETCH okl_ipy_csr INTO l_dummy_var;
763 CLOSE okl_ipy_csr;
764 -- if l_dummy_var is still set to default ,data was not found
765 IF (l_dummy_var ='?') THEN
766 OKC_API.set_message(p_app_name => G_APP_NAME,
767 p_msg_name => G_NO_PARENT_RECORD,
768 p_token1 => G_COL_NAME_TOKEN,
769 p_token1_value => 'Policy ID',
770 p_token2 => g_parent_table_token,
771 p_token2_value => 'OKL_INS_POLICIES_V',
772 p_token3 => g_child_table_token,
773 p_token3_value => 'OKL_INS_CLAIMS_B');
774 -- notify caller of an error
775 l_return_status := OKC_API.G_RET_STS_ERROR;
776 x_return_status := l_return_status;
777 END IF;
778 END IF;
779 EXCEPTION
780 WHEN OTHERS THEN
781 -- store SQL error message on message stack for caller
782 Okc_Api.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
783 -- Notify the caller of an unexpected error
784 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
785 -- Verify that cursor was closed
786 IF okl_ipy_csr%ISOPEN THEN
787 CLOSE okl_ipy_csr;
788 END IF;
789 END validate_ipy_id;
790 ----------------------------------------------------
791 -- Validate_Attributes for: csu_code --
792 ----------------------------------------------------
793 PROCEDURE validate_csu_code(x_return_status OUT NOCOPY VARCHAR2,p_clmv_rec IN clmv_rec_type ) IS
794 l_dummy_var VARCHAR2(1) :='?';
795 BEGIN
796 --initialize the return status
797 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
798 --data is required
799 IF p_clmv_rec.csu_code = Okc_Api.G_MISS_CHAR OR
800 p_clmv_rec.csu_code IS NULL
801 THEN
802 Okc_Api.set_message(p_app_name => G_APP_NAME,
803 p_msg_name => G_REQUIRED_VALUE,
804 p_token1 => G_COL_NAME_TOKEN,
805 p_token1_value => 'Claim Status');
806 --Notify caller of an error
807 x_return_status := Okc_Api.G_RET_STS_ERROR;
808 ELSE
809 x_return_status := Okl_Util.check_lookup_code('OKL_CLAIM_STATUS',p_clmv_rec.csu_code);
810 IF (x_return_status = Okc_Api.G_RET_STS_ERROR) THEN
811 Okc_Api.set_message(p_app_name => G_APP_NAME,
812 p_msg_name => G_NO_PARENT_RECORD,
813 p_token1 => G_COL_NAME_TOKEN,
814 p_token1_value => 'Claim Status',
815 p_token2 => g_parent_table_token,
816 p_token2_value => 'FND_LOOKUPS',
817 p_token3 => g_child_table_token,
818 p_token3_value => 'OKL_INS_CLAIMS_B');--Added child table token for fixing 3745151
819 ELSIF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
820 RAISE G_EXCEPTION_HALT_VALIDATION;
821 END IF;
822 END IF;
823 EXCEPTION
824 WHEN OTHERS THEN
825 -- store SQL error message on message stack for caller
826 Okc_Api.set_message(p_app_name => G_APP_NAME,
827 p_msg_name => G_UNEXPECTED_ERROR,
828 p_token1 => G_SQLCODE_TOKEN,
829 p_token1_value => SQLCODE,
830 p_token2 => G_SQLERRM_TOKEN,
831 p_token2_value => SQLERRM
832 );
833 -- Notify the caller of an unexpected error
834 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
835 END validate_csu_code;
836 ----------------------------------------------------
837 -- Validate_Attributes for: ltp_code --
838 ----------------------------------------------------
839 PROCEDURE validate_ltp_code(x_return_status OUT NOCOPY VARCHAR2,p_clmv_rec IN clmv_rec_type ) IS
840 l_dummy_var VARCHAR2(1) :='?';
841 BEGIN
842 --initialize the return status
843 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
844
845 --smoduga modified for bug 2395753 and 2522390
846 IF p_clmv_rec.ltp_code = Okc_Api.G_MISS_CHAR --OR
847 --p_clmv_rec.ltp_code IS NULL
848 THEN
849 Okc_Api.set_message(p_app_name => G_APP_NAME,
850 p_msg_name => G_REQUIRED_VALUE,
851 p_token1 => G_COL_NAME_TOKEN,
852 p_token1_value => 'Claim Loss Type');
853 --Notify caller of an error
854 x_return_status := Okc_Api.G_RET_STS_ERROR;
855 ELSIF p_clmv_rec.ltp_code IS NOT NULL THEN
856 x_return_status := Okl_Util.check_lookup_code('OKL_LOSS_TYPE',p_clmv_rec.ltp_code);
857 IF (x_return_status = Okc_Api.G_RET_STS_ERROR) THEN
858 Okc_Api.set_message(p_app_name => G_APP_NAME,
859 p_msg_name => G_NO_PARENT_RECORD,
860 p_token1 => G_COL_NAME_TOKEN,
861 p_token1_value => 'Claim Loss Type',
862 p_token2 => g_parent_table_token,
863 p_token2_value => 'FND_LOOKUPS',
864 p_token3 => g_child_table_token,
865 p_token3_value => 'OKL_INS_CLAIMS_B'); -- Added child table token for 3745151.
866 ELSIF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
867 RAISE G_EXCEPTION_HALT_VALIDATION;
868 END IF;
869 END IF;
870 EXCEPTION
871 WHEN OTHERS THEN
872 -- store SQL error message on message stack for caller
873 Okc_Api.set_message(p_app_name => G_APP_NAME,
874 p_msg_name => G_UNEXPECTED_ERROR,
875 p_token1 => G_SQLCODE_TOKEN,
876 p_token1_value => SQLCODE,
877 p_token2 => G_SQLERRM_TOKEN,
878 p_token2_value => SQLERRM
879 );
880 -- Notify the caller of an unexpected error
881 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
882 END validate_ltp_code;
883 ----------------------------------------------------
884 -- Validate_Attributes for: claim date --
885 ----------------------------------------------------
886 PROCEDURE validate_claim_date(x_return_status OUT NOCOPY VARCHAR2,p_clmv_rec IN clmv_rec_type ) IS
887 BEGIN
888 --initialize the return status
889 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
890
891 --smoduga modified for bug 2395753 and 2522390
892 IF p_clmv_rec.claim_date = Okc_Api.G_MISS_DATE --OR
893 --p_clmv_rec.claim_date IS NULL
894 THEN
895 Okc_Api.set_message(p_app_name => G_APP_NAME,
896 p_msg_name => G_REQUIRED_VALUE,
897 p_token1 => G_COL_NAME_TOKEN,
898 p_token1_value => 'Claim Date');
899 --Notify caller of an error
900 x_return_status := Okc_Api.G_RET_STS_ERROR;
901 END IF;
902 EXCEPTION
903 WHEN OTHERS THEN
904 -- store SQL error message on message stack for caller
905 Okc_Api.set_message(p_app_name => G_APP_NAME,
906 p_msg_name => G_UNEXPECTED_ERROR,
907 p_token1 => G_SQLCODE_TOKEN,
908 p_token1_value => SQLCODE,
909 p_token2 => G_SQLERRM_TOKEN,
910 p_token2_value => SQLERRM
911 );
912 -- Notify the caller of an unexpected error
913 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
914 END validate_claim_date;
915 ----------------------------------------------------
916 -- Validate_Attributes for: Loss date --
917 ----------------------------------------------------
918 PROCEDURE validate_loss_date(x_return_status OUT NOCOPY VARCHAR2,p_clmv_rec IN clmv_rec_type ) IS
919 BEGIN
920 --initialize the return status
921 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
922
923 --smoduga modified for bug 2395753 and 2522390
924 IF p_clmv_rec.loss_date = Okc_Api.G_MISS_DATE --OR
925 --p_clmv_rec.loss_date IS NULL
926 THEN
927 Okc_Api.set_message(p_app_name => G_APP_NAME,
928 p_msg_name => G_REQUIRED_VALUE,
929 p_token1 => G_COL_NAME_TOKEN,
930 p_token1_value => 'Accident Date');
931 --Notify caller of an error
932 x_return_status := Okc_Api.G_RET_STS_ERROR;
933 END IF;
934 EXCEPTION
935 WHEN OTHERS THEN
936 -- store SQL error message on message stack for caller
937 Okc_Api.set_message(p_app_name => G_APP_NAME,
938 p_msg_name => G_UNEXPECTED_ERROR,
939 p_token1 => G_SQLCODE_TOKEN,
940 p_token1_value => SQLCODE,
941 p_token2 => G_SQLERRM_TOKEN,
942 p_token2_value => SQLERRM
943 );
944 -- Notify the caller of an unexpected error
945 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
946 END validate_loss_date;
947 ----------------------------------------------------
948 -- Validate_Attributes for: amount --
949 ----------------------------------------------------
950 PROCEDURE validate_amount(x_return_status OUT NOCOPY VARCHAR2,p_clmv_rec IN clmv_rec_type ) IS
951 l_dummy_var VARCHAR2(1) :='?';
952 BEGIN
953 --initialize the return status
954 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
955
956 --smoduga modified for bug 2395753 and 2522390
957 IF p_clmv_rec.amount = Okc_Api.G_MISS_NUM --OR
958 --p_clmv_rec.amount IS NULL
959 THEN
960 Okc_Api.set_message(p_app_name => G_APP_NAME,
961 p_msg_name => G_REQUIRED_VALUE,
962 p_token1 => G_COL_NAME_TOKEN,
963 p_token1_value => 'AMOUNT');
964 -- Notify caller of an error
965 x_return_status := Okc_Api.G_RET_STS_ERROR;
966 END IF;
967 EXCEPTION
968 WHEN OTHERS THEN
969 -- store SQL error message on message stack for caller
970 Okc_Api.set_message(p_app_name => G_APP_NAME,
971 p_msg_name => G_UNEXPECTED_ERROR,
972 p_token1 => G_SQLCODE_TOKEN,
973 p_token1_value => SQLCODE,
974 p_token2 => G_SQLERRM_TOKEN,
975 p_token2_value => SQLERRM
976 );
977 -- Notify the caller of an unexpected error
978 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
979 END validate_amount;
980 ----------------------------------------------------
981 -- Validate_Attributes for: police_contact --
982 ----------------------------------------------------
983 PROCEDURE validate_police_contact(x_return_status OUT NOCOPY VARCHAR2,p_clmv_rec IN clmv_rec_type ) IS
984 l_dummy_var VARCHAR2(1) :='?';
985 BEGIN
986 --initialize the return status
987 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
988
989 --smoduga modified for bug 2395753 and 2522390
990 IF p_clmv_rec.police_contact = Okc_Api.G_MISS_CHAR --OR
991 --p_clmv_rec.police_contact IS NULL
992 THEN
993 Okc_Api.set_message(p_app_name => G_APP_NAME,
994 p_msg_name => G_REQUIRED_VALUE,
995 p_token1 => G_COL_NAME_TOKEN,
996 p_token1_value => 'POLICE CONTACT');
997 -- Notify caller of an error
998 x_return_status := Okc_Api.G_RET_STS_ERROR;
999 END IF;
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002 -- store SQL error message on message stack for caller
1003 Okc_Api.set_message(p_app_name => G_APP_NAME,
1004 p_msg_name => G_UNEXPECTED_ERROR,
1005 p_token1 => G_SQLCODE_TOKEN,
1006 p_token1_value => SQLCODE,
1007 p_token2 => G_SQLERRM_TOKEN,
1008 p_token2_value => SQLERRM
1009 );
1010 -- Notify the caller of an unexpected error
1011 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1012 END validate_police_contact;
1013 ----------------------------------------------------
1014 -- Validate_Attributes for: police_Report --
1015 ----------------------------------------------------
1016 PROCEDURE validate_police_report(x_return_status OUT NOCOPY VARCHAR2,p_clmv_rec IN clmv_rec_type ) IS
1017 l_dummy_var VARCHAR2(1) :='?';
1018 BEGIN
1019 --initialize the return status
1020 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1021
1022 --smoduga modified for bug 2395753 and 2522390
1023 IF p_clmv_rec.police_report = Okc_Api.G_MISS_CHAR --OR
1024 --p_clmv_rec.police_report IS NULL
1025 THEN
1026 Okc_Api.set_message(p_app_name => G_APP_NAME,
1027 p_msg_name => G_REQUIRED_VALUE,
1028 p_token1 => G_COL_NAME_TOKEN,
1029 p_token1_value => 'POLICE REPORT');
1030 -- Notify caller of an error
1031 x_return_status := Okc_Api.G_RET_STS_ERROR;
1032 END IF;
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 -- store SQL error message on message stack for caller
1036 Okc_Api.set_message(p_app_name => G_APP_NAME,
1037 p_msg_name => G_UNEXPECTED_ERROR,
1038 p_token1 => G_SQLCODE_TOKEN,
1039 p_token1_value => SQLCODE,
1040 p_token2 => G_SQLERRM_TOKEN,
1041 p_token2_value => SQLERRM
1042 );
1043 -- Notify the caller of an unexpected error
1044 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1045 END validate_police_report;
1046
1047
1048 ---------------------------------------------------------------------------
1049 -- FUNCTION Validate_Attributes
1050 ---------------------------------------------------------------------------
1051 ----------------------------------------------
1052 -- Validate_Attributes for:OKL_INS_CLAIMS_V --
1053 ----------------------------------------------
1054 FUNCTION Validate_Attributes (
1055 p_clmv_rec IN clmv_rec_type
1056 ) RETURN VARCHAR2 IS
1057 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1058 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1059 BEGIN
1060 -----------------------------
1061 -- Column Level Validation --
1062 -----------------------------
1063 -- ***
1064 -- id
1065 -- ***
1066 validate_id(x_return_status, p_clmv_rec.id);
1067 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1068 l_return_status := x_return_status;
1069 RAISE G_EXCEPTION_HALT_VALIDATION;
1070 END IF;
1071 -- ***
1072 -- object_version_number
1073 -- ***
1074 validate_object_version_number(x_return_status, p_clmv_rec.object_version_number);
1075 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1076 l_return_status := x_return_status;
1077 RAISE G_EXCEPTION_HALT_VALIDATION;
1078 END IF;
1079 -- ***
1080 -- sfwt_flag
1081 -- ***
1082 validate_sfwt_flag(x_return_status, p_clmv_rec.sfwt_flag);
1083 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1084 l_return_status := x_return_status;
1085 RAISE G_EXCEPTION_HALT_VALIDATION;
1086 END IF;
1087 -- ***
1088 -- claim_number
1089 -- ***
1090 validate_claim_number(x_return_status, p_clmv_rec.claim_number);
1091 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1092 l_return_status := x_return_status;
1093 RAISE G_EXCEPTION_HALT_VALIDATION;
1094 END IF;
1095 -- ***
1096 -- ipy_id
1097 -- ***
1098 validate_ipy_id(x_return_status, p_clmv_rec.ipy_id);
1099 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1100 l_return_status := x_return_status;
1101 RAISE G_EXCEPTION_HALT_VALIDATION;
1102 END IF;
1103 -- ***
1104 -- amount
1105 -- ***
1106 validate_amount(x_return_status, p_clmv_rec);
1107 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1108 l_return_status := x_return_status;
1109 RAISE G_EXCEPTION_HALT_VALIDATION;
1110 END IF;
1111 -- ***
1112 -- claim_date
1113 -- ***
1114 validate_claim_date(x_return_status, p_clmv_rec);
1115 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1116 l_return_status := x_return_status;
1117 RAISE G_EXCEPTION_HALT_VALIDATION;
1118 END IF;
1119 -- ***
1120 -- csu_code
1121 -- ***
1122 validate_csu_code(x_return_status, p_clmv_rec);
1123 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1124 l_return_status := x_return_status;
1125 RAISE G_EXCEPTION_HALT_VALIDATION;
1126 END IF;
1127 -- ***
1128 -- ltp_code
1129 -- ***
1130 validate_ltp_code(x_return_status, p_clmv_rec);
1131 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1132 l_return_status := x_return_status;
1133 RAISE G_EXCEPTION_HALT_VALIDATION;
1134 END IF;
1135 -- ***
1136 -- loss_date
1137 -- ***
1138 validate_loss_date(x_return_status, p_clmv_rec);
1139 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1140 l_return_status := x_return_status;
1141 RAISE G_EXCEPTION_HALT_VALIDATION;
1142 END IF;
1143 -- ***
1144 -- police_contact
1145 -- ***
1146 validate_police_contact(x_return_status, p_clmv_rec);
1147 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1148 l_return_status := x_return_status;
1149 RAISE G_EXCEPTION_HALT_VALIDATION;
1150 END IF;
1151 -- ***
1152 -- police_report
1153 -- ***
1154 validate_police_report(x_return_status, p_clmv_rec);
1155 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1156 l_return_status := x_return_status;
1157 RAISE G_EXCEPTION_HALT_VALIDATION;
1158 END IF;
1159
1160 RETURN(l_return_status);
1161 EXCEPTION
1162 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1163 RETURN(l_return_status);
1164 WHEN OTHERS THEN
1165 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1166 ,p_msg_name => G_UNEXPECTED_ERROR
1167 ,p_token1 => G_SQLCODE_TOKEN
1168 ,p_token1_value => SQLCODE
1169 ,p_token2 => G_SQLERRM_TOKEN
1170 ,p_token2_value => SQLERRM);
1171 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1172 RETURN(l_return_status);
1173 END Validate_Attributes;
1174 ---------------------------------------------------------------------------
1175 -- PROCEDURE Validate_Record
1176 ---------------------------------------------------------------------------
1177 FUNCTION validate_foreign_keys (
1178 p_clmv_rec IN clmv_rec_type,
1179 p_db_clmv_rec IN clmv_rec_type
1180 ) RETURN VARCHAR2 IS
1181 item_not_found_error EXCEPTION;
1182 /*
1183 CURSOR okl_clmv_inav_fk_csr ( SELECT 'x'
1184 FROM Okl_Ins_Assets_V
1185 WHERE l_okl_clmv_inav_fk
1186 okl_clmv_inav_fk_csr%ROWTYPE;
1187 CURSOR okl_clmv_ltpv_fk_csr (p_lookup_code IN VARCHAR2) IS
1188 SELECT 'x'
1189 FROM Fnd_Common_Lookups
1190 WHERE fnd_common_lookups.lookup_code = p_lookup_code;
1191 */
1192 -- l_okl_clmv_ltpv_fk okl_clmv_ltpv_fk_csr%ROWTYPE;
1193 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1194 l_row_notfound BOOLEAN := TRUE;
1195 BEGIN
1196 NULL;
1197 RETURN (l_return_status);
1198 /*
1199 IF ( AND
1200 THEN
1201 OPEN okl_clmv_inav_fk_csr ( FETCH okl_clmv_inav_fk_csr INTO l_okl_clmv_inav_fk;
1202 l_row_notfound := okl_clmv_inav_fk_csr%NOTFOUND;
1203 CLOSE okl_clmv_inav_fk_csr;
1204 IF (l_row_notfound) THEN
1205 RAISE item_not_found_error;
1206 END IF;
1207 END IF;
1208 IF ((p_clmv_rec.LTP_CODE IS NOT NULL)
1209 AND
1210 (p_clmv_rec.LTP_CODE <> p_db_clmv_rec.LTP_CODE))
1211 THEN
1212 OPEN okl_clmv_ltpv_fk_csr (p_clmv_rec.LTP_CODE);
1213 FETCH okl_clmv_ltpv_fk_csr INTO l_okl_clmv_ltpv_fk;
1214 l_row_notfound := okl_clmv_ltpv_fk_csr%NOTFOUND;
1215 CLOSE okl_clmv_ltpv_fk_csr;
1216 IF (l_row_notfound) THEN
1217 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'LTP_CODE');
1218 RAISE item_not_found_error;
1219 END IF;
1220 END IF;
1221 IF ((p_clmv_rec.CSU_CODE IS NOT NULL)
1222 AND
1223 (p_clmv_rec.CSU_CODE <> p_db_clmv_rec.CSU_CODE))
1224 THEN
1225 OPEN okl_clmv_csuv_fk_csr (p_clmv_rec.CSU_CODE);
1226 FETCH okl_clmv_csuv_fk_csr INTO l_okl_clmv_csuv_fk;
1227 l_row_notfound := okl_clmv_csuv_fk_csr%NOTFOUND;
1228 CLOSE okl_clmv_csuv_fk_csr;
1229 IF (l_row_notfound) THEN
1230 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CSU_CODE');
1231 RAISE item_not_found_error;
1232 END IF;
1233 END IF;
1234 RETURN (l_return_status);
1235 EXCEPTION
1236 WHEN item_not_found_error THEN
1237 l_return_status := OKC_API.G_RET_STS_ERROR;
1238 RETURN (l_return_status);
1239 */
1240 END validate_foreign_keys;
1241
1242 ---------------------------------------------------------------------------
1243 -- PROCEDURE Validate_Duplicates
1244 ---------------------------------------------------------------------------
1245 PROCEDURE validate_duplicates(
1246 p_clmv_rec IN clmv_rec_type,
1247 x_return_status OUT NOCOPY VARCHAR2) IS
1248 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1249 l_dummy_var VARCHAR2(1) := '?';
1250 CURSOR l_clmv_csr IS
1251 SELECT 'x'
1252 FROM okl_ins_claims_v
1253 WHERE ipy_id = p_clmv_rec.ipy_id
1254 AND claim_number = p_clmv_rec.claim_number
1255 AND ID <> p_clmv_rec.id;
1256 BEGIN
1257 OPEN l_clmv_csr;
1258 FETCH l_clmv_csr INTO l_dummy_var;
1259 CLOSE l_clmv_csr;
1260 -- if l_dummy_var is still set to default, data was not found
1261 IF (l_dummy_var = 'x') THEN
1262 OKC_API.set_message(p_app_name => G_APP_NAME,
1263 p_msg_name => 'OKL_UNIQUE'
1264 );
1265 l_return_status := Okc_Api.G_RET_STS_ERROR;
1266 END IF;
1267 x_return_status := l_return_status;
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 -- store SQL error message on message stack for caller
1271 Okc_Api.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1272 -- notify caller of an UNEXPECTED error
1273 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1274 END validate_duplicates;
1275
1276 ------------------------------------------
1277 -- Validate Record for:OKL_INS_CLAIMS_V --
1278 ------------------------------------------
1279 FUNCTION Validate_Record (
1280 p_clmv_rec IN clmv_rec_type,
1281 p_db_clmv_rec IN clmv_rec_type
1282 ) RETURN VARCHAR2 IS
1283 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1284 ------------------------------------
1285 -- FUNCTION validate_foreign_keys --
1286 ------------------------------------
1287 BEGIN
1288 --Validate Duplicate records
1289 validate_duplicates(p_clmv_rec,l_return_status);
1290 IF (l_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1291 OKC_API.set_message(p_app_name => G_APP_NAME,
1292 p_msg_name => 'OKL_UNIQUE'
1293 );
1294 l_return_status := Okc_Api.G_RET_STS_ERROR;
1295 END IF;
1296
1297 l_return_status := validate_foreign_keys(p_clmv_rec, p_db_clmv_rec);
1298 RETURN (l_return_status);
1299 END Validate_Record;
1300
1301 FUNCTION Validate_Record (
1302 p_clmv_rec IN clmv_rec_type
1303 ) RETURN VARCHAR2 IS
1304 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1305 l_db_clmv_rec clmv_rec_type := get_rec(p_clmv_rec);
1306 BEGIN
1307 l_return_status := Validate_Record(p_clmv_rec => p_clmv_rec,
1308 p_db_clmv_rec => l_db_clmv_rec);
1309 --Validate Duplicate records
1310 validate_duplicates(p_clmv_rec,l_return_status);
1311 IF (l_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1312 OKC_API.set_message(p_app_name => G_APP_NAME,
1313 p_msg_name => 'OKL_UNIQUE'
1314 );
1315 l_return_status := Okc_Api.G_RET_STS_ERROR;
1316 END IF;
1317
1318 RETURN (l_return_status);
1319 END Validate_Record;
1320 ---------------------------------------------------------------------------
1321 -- PROCEDURE Migrate
1322 ---------------------------------------------------------------------------
1323 PROCEDURE migrate (
1324 p_from IN clmv_rec_type,
1325 p_to IN OUT NOCOPY clm_rec_type
1326 ) IS
1327 BEGIN
1328 p_to.id := p_from.id;
1329 p_to.claim_number := p_from.claim_number;
1330 p_to.csu_code := p_from.csu_code;
1331 p_to.ipy_id := p_from.ipy_id;
1332 p_to.ltp_code := p_from.ltp_code;
1333 p_to.program_update_date := p_from.program_update_date;
1334 p_to.claim_date := p_from.claim_date;
1335 p_to.program_id := p_from.program_id;
1336 p_to.loss_date := p_from.loss_date;
1337 p_to.police_contact := p_from.police_contact;
1338 p_to.amount := p_from.amount;
1339 p_to.object_version_number := p_from.object_version_number;
1340 p_to.request_id := p_from.request_id;
1341 p_to.program_application_id := p_from.program_application_id;
1342 p_to.attribute_category := p_from.attribute_category;
1343 p_to.attribute1 := p_from.attribute1;
1344 p_to.attribute2 := p_from.attribute2;
1345 p_to.attribute3 := p_from.attribute3;
1346 p_to.attribute4 := p_from.attribute4;
1347 p_to.attribute5 := p_from.attribute5;
1348 p_to.attribute6 := p_from.attribute6;
1349 p_to.attribute7 := p_from.attribute7;
1350 p_to.attribute8 := p_from.attribute8;
1351 p_to.attribute9 := p_from.attribute9;
1352 p_to.attribute10 := p_from.attribute10;
1353 p_to.attribute11 := p_from.attribute11;
1354 p_to.attribute12 := p_from.attribute12;
1355 p_to.attribute13 := p_from.attribute13;
1356 p_to.attribute14 := p_from.attribute14;
1357 p_to.attribute15 := p_from.attribute15;
1358 p_to.hold_date := p_from.hold_date;
1359 p_to.org_id := p_from.org_id;
1360 p_to.created_by := p_from.created_by;
1361 p_to.creation_date := p_from.creation_date;
1362 p_to.last_updated_by := p_from.last_updated_by;
1363 p_to.last_update_date := p_from.last_update_date;
1364 p_to.last_update_login := p_from.last_update_login;
1365 END migrate;
1366 PROCEDURE migrate (
1367 p_from IN clm_rec_type,
1368 p_to IN OUT NOCOPY clmv_rec_type
1369 ) IS
1370 BEGIN
1371 p_to.id := p_from.id;
1372 p_to.object_version_number := p_from.object_version_number;
1373 p_to.ipy_id := p_from.ipy_id;
1374 p_to.ltp_code := p_from.ltp_code;
1375 p_to.csu_code := p_from.csu_code;
1376 p_to.claim_number := p_from.claim_number;
1377 p_to.claim_date := p_from.claim_date;
1378 p_to.loss_date := p_from.loss_date;
1379 p_to.police_contact := p_from.police_contact;
1380 p_to.amount := p_from.amount;
1381 p_to.attribute_category := p_from.attribute_category;
1382 p_to.attribute1 := p_from.attribute1;
1383 p_to.attribute2 := p_from.attribute2;
1384 p_to.attribute3 := p_from.attribute3;
1385 p_to.attribute4 := p_from.attribute4;
1386 p_to.attribute5 := p_from.attribute5;
1387 p_to.attribute6 := p_from.attribute6;
1388 p_to.attribute7 := p_from.attribute7;
1389 p_to.attribute8 := p_from.attribute8;
1390 p_to.attribute9 := p_from.attribute9;
1391 p_to.attribute10 := p_from.attribute10;
1392 p_to.attribute11 := p_from.attribute11;
1393 p_to.attribute12 := p_from.attribute12;
1394 p_to.attribute13 := p_from.attribute13;
1395 p_to.attribute14 := p_from.attribute14;
1396 p_to.attribute15 := p_from.attribute15;
1397 p_to.hold_date := p_from.hold_date;
1398 p_to.org_id := p_from.org_id;
1399 p_to.request_id := p_from.request_id;
1400 p_to.program_application_id := p_from.program_application_id;
1401 p_to.program_id := p_from.program_id;
1402 p_to.program_update_date := p_from.program_update_date;
1403 p_to.created_by := p_from.created_by;
1404 p_to.creation_date := p_from.creation_date;
1405 p_to.last_updated_by := p_from.last_updated_by;
1406 p_to.last_update_date := p_from.last_update_date;
1407 p_to.last_update_login := p_from.last_update_login;
1408 END migrate;
1409 PROCEDURE migrate (
1410 p_from IN clmv_rec_type,
1411 p_to IN OUT NOCOPY okl_ins_claims_tl_rec_type
1412 ) IS
1413 BEGIN
1414 p_to.id := p_from.id;
1415 p_to.description := p_from.description;
1416 p_to.police_report := p_from.police_report;
1417 p_to.sfwt_flag := p_from.sfwt_flag;
1418 p_to.created_by := p_from.created_by;
1419 p_to.creation_date := p_from.creation_date;
1420 p_to.last_updated_by := p_from.last_updated_by;
1421 p_to.last_update_date := p_from.last_update_date;
1422 p_to.last_update_login := p_from.last_update_login;
1423 END migrate;
1424 PROCEDURE migrate (
1425 p_from IN okl_ins_claims_tl_rec_type,
1426 p_to IN OUT NOCOPY clmv_rec_type
1427 ) IS
1428 BEGIN
1429 p_to.id := p_from.id;
1430 p_to.sfwt_flag := p_from.sfwt_flag;
1431 p_to.description := p_from.description;
1432 p_to.police_report := p_from.police_report;
1433 p_to.created_by := p_from.created_by;
1434 p_to.creation_date := p_from.creation_date;
1435 p_to.last_updated_by := p_from.last_updated_by;
1436 p_to.last_update_date := p_from.last_update_date;
1437 p_to.last_update_login := p_from.last_update_login;
1438 END migrate;
1439 ---------------------------------------------------------------------------
1440 -- PROCEDURE validate_row
1441 ---------------------------------------------------------------------------
1442 ---------------------------------------
1443 -- validate_row for:OKL_INS_CLAIMS_V --
1444 ---------------------------------------
1445 PROCEDURE validate_row(
1446 p_api_version IN NUMBER,
1447 p_init_msg_list IN VARCHAR2 ,
1448 x_return_status OUT NOCOPY VARCHAR2,
1449 x_msg_count OUT NOCOPY NUMBER,
1450 x_msg_data OUT NOCOPY VARCHAR2,
1451 p_clmv_rec IN clmv_rec_type) IS
1452 l_api_version CONSTANT NUMBER := 1;
1453 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1454 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1455 l_clmv_rec clmv_rec_type := p_clmv_rec;
1456 l_clm_rec clm_rec_type;
1457 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
1458 BEGIN
1459 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1460 G_PKG_NAME,
1461 p_init_msg_list,
1462 l_api_version,
1463 p_api_version,
1464 '_PVT',
1465 x_return_status);
1466 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1467 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1468 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1469 RAISE OKC_API.G_EXCEPTION_ERROR;
1470 END IF;
1471 --- Validate all non-missing attributes (Item Level Validation)
1472 l_return_status := Validate_Attributes(l_clmv_rec);
1473 --- If any errors happen abort API
1474 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1475 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1476 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1477 RAISE OKC_API.G_EXCEPTION_ERROR;
1478 END IF;
1479 l_return_status := Validate_Record(l_clmv_rec);
1480 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1481 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1482 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1483 RAISE OKC_API.G_EXCEPTION_ERROR;
1484 END IF;
1485 x_return_status := l_return_status;
1486 EXCEPTION
1487 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1488 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1489 (
1490 l_api_name,
1491 G_PKG_NAME,
1492 'OKC_API.G_RET_STS_ERROR',
1493 x_msg_count,
1494 x_msg_data,
1495 '_PVT'
1496 );
1497 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1498 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1499 (
1500 l_api_name,
1501 G_PKG_NAME,
1502 'OKC_API.G_RET_STS_UNEXP_ERROR',
1503 x_msg_count,
1504 x_msg_data,
1505 '_PVT'
1506 );
1507 WHEN OTHERS THEN
1508 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1509 (
1510 l_api_name,
1511 G_PKG_NAME,
1512 'OTHERS',
1513 x_msg_count,
1514 x_msg_data,
1515 '_PVT'
1516 );
1517 END validate_row;
1518 --------------------------------------------------
1519 -- PL/SQL TBL validate_row for:OKL_INS_CLAIMS_V --
1520 --------------------------------------------------
1521 PROCEDURE validate_row(
1522 p_api_version IN NUMBER,
1523 p_init_msg_list IN VARCHAR2 ,
1524 x_return_status OUT NOCOPY VARCHAR2,
1525 x_msg_count OUT NOCOPY NUMBER,
1526 x_msg_data OUT NOCOPY VARCHAR2,
1527 p_clmv_tbl IN clmv_tbl_type,
1528 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1529 l_api_version CONSTANT NUMBER := 1;
1530 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
1531 i NUMBER := 0;
1532 BEGIN
1533 OKC_API.init_msg_list(p_init_msg_list);
1534 -- Make sure PL/SQL table has records in it before passing
1535 IF (p_clmv_tbl.COUNT > 0) THEN
1536 i := p_clmv_tbl.FIRST;
1537 LOOP
1538 DECLARE
1539 l_error_rec OKL_API.ERROR_REC_TYPE;
1540 BEGIN
1541 l_error_rec.api_name := l_api_name;
1542 l_error_rec.api_package := G_PKG_NAME;
1543 l_error_rec.idx := i;
1544 validate_row (
1545 p_api_version => p_api_version,
1546 p_init_msg_list => OKC_API.G_FALSE,
1547 x_return_status => l_error_rec.error_type,
1548 x_msg_count => l_error_rec.msg_count,
1549 x_msg_data => l_error_rec.msg_data,
1550 p_clmv_rec => p_clmv_tbl(i));
1551 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1552 l_error_rec.sqlcode := SQLCODE;
1553 load_error_tbl(l_error_rec, px_error_tbl);
1554 ELSE
1555 x_msg_count := l_error_rec.msg_count;
1556 x_msg_data := l_error_rec.msg_data;
1557 END IF;
1558 EXCEPTION
1559 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1560 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1561 l_error_rec.sqlcode := SQLCODE;
1562 load_error_tbl(l_error_rec, px_error_tbl);
1563 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1564 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1565 l_error_rec.sqlcode := SQLCODE;
1566 load_error_tbl(l_error_rec, px_error_tbl);
1567 WHEN OTHERS THEN
1568 l_error_rec.error_type := 'OTHERS';
1569 l_error_rec.sqlcode := SQLCODE;
1570 load_error_tbl(l_error_rec, px_error_tbl);
1571 END;
1572 EXIT WHEN (i = p_clmv_tbl.LAST);
1573 i := p_clmv_tbl.NEXT(i);
1574 END LOOP;
1575 END IF;
1576 -- Loop through the error_tbl to find the error with the highest severity
1577 -- and return it.
1578 x_return_status := find_highest_exception(px_error_tbl);
1579 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1580 EXCEPTION
1581 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1582 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1583 (
1584 l_api_name,
1585 G_PKG_NAME,
1586 'OKC_API.G_RET_STS_ERROR',
1587 x_msg_count,
1588 x_msg_data,
1589 '_PVT'
1590 );
1591 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1592 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1593 (
1594 l_api_name,
1595 G_PKG_NAME,
1596 'OKC_API.G_RET_STS_UNEXP_ERROR',
1597 x_msg_count,
1598 x_msg_data,
1599 '_PVT'
1600 );
1601 WHEN OTHERS THEN
1602 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1603 (
1604 l_api_name,
1605 G_PKG_NAME,
1606 'OTHERS',
1607 x_msg_count,
1608 x_msg_data,
1609 '_PVT'
1610 );
1611 END validate_row;
1612 --------------------------------------------------
1613 -- PL/SQL TBL validate_row for:OKL_INS_CLAIMS_V --
1614 --------------------------------------------------
1615 PROCEDURE validate_row(
1616 p_api_version IN NUMBER,
1617 p_init_msg_list IN VARCHAR2 ,
1618 x_return_status OUT NOCOPY VARCHAR2,
1619 x_msg_count OUT NOCOPY NUMBER,
1620 x_msg_data OUT NOCOPY VARCHAR2,
1621 p_clmv_tbl IN clmv_tbl_type) IS
1622 l_api_version CONSTANT NUMBER := 1;
1623 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1624 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1625 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1626 BEGIN
1627 OKC_API.init_msg_list(p_init_msg_list);
1628 -- Make sure PL/SQL table has records in it before passing
1629 IF (p_clmv_tbl.COUNT > 0) THEN
1630 validate_row (
1631 p_api_version => p_api_version,
1632 p_init_msg_list => OKC_API.G_FALSE,
1633 x_return_status => x_return_status,
1634 x_msg_count => x_msg_count,
1635 x_msg_data => x_msg_data,
1636 p_clmv_tbl => p_clmv_tbl,
1637 px_error_tbl => l_error_tbl);
1638 END IF;
1639 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1640 EXCEPTION
1641 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1642 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1643 (
1644 l_api_name,
1645 G_PKG_NAME,
1646 'OKC_API.G_RET_STS_ERROR',
1647 x_msg_count,
1648 x_msg_data,
1649 '_PVT'
1650 );
1651 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1652 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1653 (
1654 l_api_name,
1655 G_PKG_NAME,
1656 'OKC_API.G_RET_STS_UNEXP_ERROR',
1657 x_msg_count,
1658 x_msg_data,
1659 '_PVT'
1660 );
1661 WHEN OTHERS THEN
1662 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1663 (
1664 l_api_name,
1665 G_PKG_NAME,
1666 'OTHERS',
1667 x_msg_count,
1668 x_msg_data,
1669 '_PVT'
1670 );
1671 END validate_row;
1672 ---------------------------------------------------------------------------
1673 -- PROCEDURE insert_row
1674 ---------------------------------------------------------------------------
1675 -------------------------------------
1676 -- insert_row for:OKL_INS_CLAIMS_B --
1677 -------------------------------------
1678 PROCEDURE insert_row(
1679 p_init_msg_list IN VARCHAR2 ,
1680 x_return_status OUT NOCOPY VARCHAR2,
1681 x_msg_count OUT NOCOPY NUMBER,
1682 x_msg_data OUT NOCOPY VARCHAR2,
1683 p_clm_rec IN clm_rec_type,
1684 x_clm_rec OUT NOCOPY clm_rec_type) IS
1685 l_api_version CONSTANT NUMBER := 1;
1686 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1687 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1688 l_clm_rec clm_rec_type := p_clm_rec;
1689 l_def_clm_rec clm_rec_type;
1690 -----------------------------------------
1691 -- Set_Attributes for:OKL_INS_CLAIMS_B --
1692 -----------------------------------------
1693 FUNCTION Set_Attributes (
1694 p_clm_rec IN clm_rec_type,
1695 x_clm_rec OUT NOCOPY clm_rec_type
1696 ) RETURN VARCHAR2 IS
1697 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1698 BEGIN
1699 x_clm_rec := p_clm_rec;
1700 --Smoduga added for inventory org fix 3348924
1701 x_clm_rec.org_id := get_inv_org_id(p_clm_rec.ipy_id);
1702 RETURN(l_return_status);
1703 END Set_Attributes;
1704 BEGIN
1705 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1706 p_init_msg_list,
1707 '_PVT',
1708 x_return_status);
1709 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1710 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1711 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1712 RAISE OKC_API.G_EXCEPTION_ERROR;
1713 END IF;
1714 --- Setting item atributes
1715 l_return_status := Set_Attributes(
1716 p_clm_rec, -- IN
1717 l_clm_rec); -- OUT
1718 --- If any errors happen abort API
1719 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1720 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1721 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1722 RAISE OKC_API.G_EXCEPTION_ERROR;
1723 END IF;
1724 INSERT INTO OKL_INS_CLAIMS_B(
1725 id,
1726 claim_number,
1727 csu_code,
1728 ipy_id,
1729 ltp_code,
1730 program_update_date,
1731 claim_date,
1732 program_id,
1733 loss_date,
1734 police_contact,
1735 amount,
1736 object_version_number,
1737 request_id,
1738 program_application_id,
1739 attribute_category,
1740 attribute1,
1741 attribute2,
1742 attribute3,
1743 attribute4,
1744 attribute5,
1745 attribute6,
1746 attribute7,
1747 attribute8,
1748 attribute9,
1749 attribute10,
1750 attribute11,
1751 attribute12,
1752 attribute13,
1753 attribute14,
1754 attribute15,
1755 hold_date,
1756 org_id,
1757 created_by,
1758 creation_date,
1759 last_updated_by,
1760 last_update_date,
1761 last_update_login)
1762 VALUES (
1763 l_clm_rec.id,
1764 l_clm_rec.claim_number,
1765 l_clm_rec.csu_code,
1766 l_clm_rec.ipy_id,
1767 l_clm_rec.ltp_code,
1768 l_clm_rec.program_update_date,
1769 l_clm_rec.claim_date,
1770 l_clm_rec.program_id,
1771 l_clm_rec.loss_date,
1772 l_clm_rec.police_contact,
1773 l_clm_rec.amount,
1774 l_clm_rec.object_version_number,
1775 l_clm_rec.request_id,
1776 l_clm_rec.program_application_id,
1777 l_clm_rec.attribute_category,
1778 l_clm_rec.attribute1,
1779 l_clm_rec.attribute2,
1780 l_clm_rec.attribute3,
1781 l_clm_rec.attribute4,
1782 l_clm_rec.attribute5,
1783 l_clm_rec.attribute6,
1784 l_clm_rec.attribute7,
1785 l_clm_rec.attribute8,
1786 l_clm_rec.attribute9,
1787 l_clm_rec.attribute10,
1788 l_clm_rec.attribute11,
1789 l_clm_rec.attribute12,
1790 l_clm_rec.attribute13,
1791 l_clm_rec.attribute14,
1792 l_clm_rec.attribute15,
1793 l_clm_rec.hold_date,
1794 l_clm_rec.org_id,
1795 l_clm_rec.created_by,
1796 l_clm_rec.creation_date,
1797 l_clm_rec.last_updated_by,
1798 l_clm_rec.last_update_date,
1799 l_clm_rec.last_update_login);
1800 -- Set OUT values
1801 x_clm_rec := l_clm_rec;
1802 x_return_status := l_return_status;
1803 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1804 EXCEPTION
1805 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1806 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1807 (
1808 l_api_name,
1809 G_PKG_NAME,
1810 'OKC_API.G_RET_STS_ERROR',
1811 x_msg_count,
1812 x_msg_data,
1813 '_PVT'
1814 );
1815 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1816 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1817 (
1818 l_api_name,
1819 G_PKG_NAME,
1820 'OKC_API.G_RET_STS_UNEXP_ERROR',
1821 x_msg_count,
1822 x_msg_data,
1823 '_PVT'
1824 );
1825 WHEN OTHERS THEN
1826 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1827 (
1828 l_api_name,
1829 G_PKG_NAME,
1830 'OTHERS',
1831 x_msg_count,
1832 x_msg_data,
1833 '_PVT'
1834 );
1835 END insert_row;
1836 --------------------------------------
1837 -- insert_row for:OKL_INS_CLAIMS_TL --
1838 --------------------------------------
1839 PROCEDURE insert_row(
1840 p_init_msg_list IN VARCHAR2 ,
1841 x_return_status OUT NOCOPY VARCHAR2,
1842 x_msg_count OUT NOCOPY NUMBER,
1843 x_msg_data OUT NOCOPY VARCHAR2,
1844 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type,
1845 x_okl_ins_claims_tl_rec OUT NOCOPY okl_ins_claims_tl_rec_type) IS
1846 l_api_version CONSTANT NUMBER := 1;
1847 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1848 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1849 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type := p_okl_ins_claims_tl_rec;
1850 l_def_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
1851 CURSOR get_languages IS
1852 SELECT *
1853 FROM FND_LANGUAGES
1854 WHERE INSTALLED_FLAG IN ('I', 'B');
1855 ------------------------------------------
1856 -- Set_Attributes for:OKL_INS_CLAIMS_TL --
1857 ------------------------------------------
1858 FUNCTION Set_Attributes (
1859 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type,
1860 x_okl_ins_claims_tl_rec OUT NOCOPY okl_ins_claims_tl_rec_type
1861 ) RETURN VARCHAR2 IS
1862 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1863 BEGIN
1864 x_okl_ins_claims_tl_rec := p_okl_ins_claims_tl_rec;
1865 x_okl_ins_claims_tl_rec.LANGUAGE := USERENV('LANG');
1866 x_okl_ins_claims_tl_rec.SOURCE_LANG := USERENV('LANG');
1867 RETURN(l_return_status);
1868 END Set_Attributes;
1869 BEGIN
1870 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1871 p_init_msg_list,
1872 '_PVT',
1873 x_return_status);
1874 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1875 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1876 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1877 RAISE OKC_API.G_EXCEPTION_ERROR;
1878 END IF;
1879 --- Setting item attributes
1880 l_return_status := Set_Attributes(
1881 p_okl_ins_claims_tl_rec, -- IN
1882 l_okl_ins_claims_tl_rec); -- OUT
1883 --- If any errors happen abort API
1884 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1885 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1886 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1887 RAISE OKC_API.G_EXCEPTION_ERROR;
1888 END IF;
1889 FOR l_lang_rec IN get_languages LOOP
1890 l_okl_ins_claims_tl_rec.language := l_lang_rec.language_code;
1891 INSERT INTO OKL_INS_CLAIMS_TL(
1892 id,
1893 language,
1894 description,
1895 police_report,
1896 comments,
1897 source_lang,
1898 sfwt_flag,
1899 created_by,
1900 creation_date,
1901 last_updated_by,
1902 last_update_date,
1903 last_update_login)
1904 VALUES (
1905 l_okl_ins_claims_tl_rec.id,
1906 l_okl_ins_claims_tl_rec.language,
1907 l_okl_ins_claims_tl_rec.description,
1908 l_okl_ins_claims_tl_rec.police_report,
1909 l_okl_ins_claims_tl_rec.comments,
1910 l_okl_ins_claims_tl_rec.source_lang,
1911 l_okl_ins_claims_tl_rec.sfwt_flag,
1912 l_okl_ins_claims_tl_rec.created_by,
1913 l_okl_ins_claims_tl_rec.creation_date,
1914 l_okl_ins_claims_tl_rec.last_updated_by,
1915 l_okl_ins_claims_tl_rec.last_update_date,
1916 l_okl_ins_claims_tl_rec.last_update_login);
1917 END LOOP;
1918 -- Set OUT values
1919 x_okl_ins_claims_tl_rec := l_okl_ins_claims_tl_rec;
1920 x_return_status := l_return_status;
1921 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1922 EXCEPTION
1923 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1924 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1925 (
1926 l_api_name,
1927 G_PKG_NAME,
1928 'OKC_API.G_RET_STS_ERROR',
1929 x_msg_count,
1930 x_msg_data,
1931 '_PVT'
1932 );
1933 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1934 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1935 (
1936 l_api_name,
1937 G_PKG_NAME,
1938 'OKC_API.G_RET_STS_UNEXP_ERROR',
1939 x_msg_count,
1940 x_msg_data,
1941 '_PVT'
1942 );
1943 WHEN OTHERS THEN
1944 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1945 (
1946 l_api_name,
1947 G_PKG_NAME,
1948 'OTHERS',
1949 x_msg_count,
1950 x_msg_data,
1951 '_PVT'
1952 );
1953 END insert_row;
1954 --------------------------------------
1955 -- insert_row for :OKL_INS_CLAIMS_V --
1956 --------------------------------------
1957 PROCEDURE insert_row(
1958 p_api_version IN NUMBER,
1959 p_init_msg_list IN VARCHAR2 ,
1960 x_return_status OUT NOCOPY VARCHAR2,
1961 x_msg_count OUT NOCOPY NUMBER,
1962 x_msg_data OUT NOCOPY VARCHAR2,
1963 p_clmv_rec IN clmv_rec_type,
1964 x_clmv_rec OUT NOCOPY clmv_rec_type) IS
1965 l_api_version CONSTANT NUMBER := 1;
1966 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1967 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1968 l_clmv_rec clmv_rec_type := p_clmv_rec;
1969 l_def_clmv_rec clmv_rec_type;
1970 l_clm_rec clm_rec_type;
1971 lx_clm_rec clm_rec_type;
1972 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
1973 lx_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
1974
1975 -- SMODUGA added for claim number generation . Bug 3234773
1976 l_policy_number VARCHAR2(20);
1977 l_ipy_type VARCHAR2(30);
1978 l_claim_like VARCHAR2(30);
1979 l_policy_string_len NUMBER;
1980 l_seq NUMBER ;
1981
1982 -- Get next highest number for claim sequence for given policy
1983 --Bug 3941692
1984 CURSOR c_claim_seq(c_ipy_id NUMBER,c_ipy_type varchar2,c_claim_like VARCHAR2) is
1985 SELECT nvl(max(to_number(substrb(clmb.claim_number,instr(clmb.claim_number,'-')+1,
1986 length(clmb.claim_number)))) + 1,-1) SEQUENCE_NUMBER
1987 FROM okl_ins_claims_b clmb,okl_ins_policies_b ipyb
1988 WHERE clmb.ipy_id = ipyb.id
1989 and clmb.claim_number like c_claim_like
1990 and ipyb.ipy_type = c_ipy_type
1991 and ipyb.id = c_ipy_id;
1992 ------------------------------------------
1993 -- get policy number ---------------------
1994 -------------------------------------------
1995 FUNCTION get_policy_number (
1996 p_clmv_rec IN clmv_rec_type,
1997 x_policy_number OUT NOCOPY VARCHAR2,
1998 x_ipy_type OUT NOCOPY VARCHAR2
1999 ) RETURN VARCHAR2 IS
2000 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2001 lx_policy_number VARCHAR2(20);
2002 lx_ipy_type VARCHAR2(30);
2003 l_row_notfound BOOLEAN := FALSE;
2004 cursor policy_number(p_ipy_id NUMBER) IS
2005 SELECT policy_number ,ipy_type, authoring_org_id -- bug 7335139
2006 FROM OKL_INS_POLICIES_all_b ins, okc_k_headers_all_b chr -- bug 7335139
2007 WHERE ins.ID = p_ipy_id
2008 and ins.khr_id = chr.id; -- bug 7335139
2009
2010 l_id number; -- bug 7335139
2011 BEGIN
2012 open policy_number(p_clmv_rec.ipy_id);
2013 fetch policy_number into lx_policy_number,lx_ipy_type, l_id; -- bug 7335139
2014 l_row_notfound := policy_number%NOTFOUND;
2015 close policy_number;
2016
2017 -- bug 7335139 if the org_id context is not set then set it
2018 if mo_global.get_current_org_id is null then
2019 MO_GLOBAL.set_policy_context('S',l_id);
2020 end if;
2021 IF (l_row_notfound) THEN
2022 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,' Policy Number ');
2023 l_return_status := OKC_API.G_RET_STS_ERROR;
2024 RETURN(l_return_status);
2025 ELSE
2026 x_policy_number := lx_policy_number;
2027 x_ipy_type := lx_ipy_type;
2028 RETURN(l_return_status);
2029 END IF;
2030 END get_policy_number;
2031 ------------------------------------------
2032 -- claim number generator
2033 -- Check if claim number generated already
2034 -- exists.If existing call claim number generator
2035 -- check again for existing claim numbers
2036 -- till a new claim number is generated.
2037 -------------------------------------------
2038 Function check_duplicate(p_claim_number IN varchar2,
2039 p_seq IN NUMBER,
2040 p_policy_number IN varchar2)
2041 RETURN VARCHAR2 IS
2042 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2043 l_claim_number VARCHAR2(30); --change for claimnumber generation old 15
2044 l_dummy_var VARCHAR2(1) :='?';
2045
2046 --check if this claim number alreadu exists
2047 cursor c_claim_exist(c_claim_number VARCHAR2) IS
2048 select 'x'
2049 from okl_Ins_claims_b
2050 where claim_number = c_claim_number;
2051 ------------------------------------------
2052 -- claim number generator
2053 -- Called in case claim number already exists
2054 -- and new claim number is generated and
2055 -- checked if it exists.
2056 -------------------------------------------
2057 FUNCTION claim_number_generator(p_policy_number IN VARCHAR2,
2058 p_seq IN NUMBER)
2059 RETURN VARCHAR2 IS
2060 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2061 l_seq NUMBER;
2062 lx_claim_number VARCHAR2(30); --change for claimnumber generation old 15
2063 l_policy_number VARCHAR2(256);
2064 l_row_notfound BOOLEAN := FALSE;
2065 lx_dummy_var VARCHAR2(1):='?';
2066 BEGIN
2067 l_seq := p_seq+1;
2068 lx_claim_number := p_policy_number || ' - '||l_seq;
2069 open c_claim_exist(lx_claim_number);
2070 fetch c_claim_exist into lx_dummy_var;
2071 l_row_notfound := c_claim_exist%NOTFOUND;
2072 close c_claim_exist;
2073 IF (lx_dummy_var = 'x') THEN
2074 l_policy_number := p_policy_number;
2075 lx_claim_number := claim_number_generator(l_policy_number,
2076 l_seq);
2077 ELSE
2078 return(lx_claim_number);
2079 END IF;
2080 END claim_number_generator;
2081 BEGIN
2082 open c_claim_exist(p_claim_number);
2083 fetch c_claim_exist into l_dummy_var;
2084 close c_claim_exist;
2085 -- if l_dummy_var is still set to default, data was not found
2086 IF (l_dummy_var = 'x') THEN
2087 l_claim_number := claim_number_generator(p_policy_number,
2088 p_seq);
2089 return(l_claim_number);
2090 ELSE
2091 l_claim_number := p_claim_number;
2092 return(l_claim_number);
2093 END IF;
2094 END check_duplicate;
2095
2096 -- END SMODUGA added for claim number generation. Bug 3234773
2097
2098
2099 -------------------------------
2100 -- FUNCTION fill_who_columns --
2101 -------------------------------
2102 FUNCTION fill_who_columns (
2103 p_clmv_rec IN clmv_rec_type
2104 ) RETURN clmv_rec_type IS
2105 l_clmv_rec clmv_rec_type := p_clmv_rec;
2106 BEGIN
2107 l_clmv_rec.CREATION_DATE := SYSDATE;
2108 l_clmv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2109 l_clmv_rec.LAST_UPDATE_DATE := l_clmv_rec.CREATION_DATE;
2110 l_clmv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2111 l_clmv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2112 RETURN(l_clmv_rec);
2113 END fill_who_columns;
2114 -----------------------------------------
2115 -- Set_Attributes for:OKL_INS_CLAIMS_V --
2116 -----------------------------------------
2117 FUNCTION Set_Attributes (
2118 p_clmv_rec IN clmv_rec_type,
2119 x_clmv_rec OUT NOCOPY clmv_rec_type
2120 ) RETURN VARCHAR2 IS
2121 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2122 BEGIN
2123 x_clmv_rec := p_clmv_rec;
2124 x_clmv_rec.OBJECT_VERSION_NUMBER := 1;
2125 x_clmv_rec.SFWT_FLAG := 'N';
2126 --Smoduga added for inventory org fix 3348924
2127 x_clmv_rec.ORG_ID := get_inv_org_id(p_clmv_rec.ipy_id);
2128 RETURN(l_return_status);
2129 END Set_Attributes;
2130 BEGIN
2131 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2132 G_PKG_NAME,
2133 p_init_msg_list,
2134 l_api_version,
2135 p_api_version,
2136 '_PVT',
2137 x_return_status);
2138 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2139 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2140 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2141 RAISE OKC_API.G_EXCEPTION_ERROR;
2142 END IF;
2143 l_clmv_rec := null_out_defaults(p_clmv_rec);
2144 -- Set primary key value
2145 l_clmv_rec.ID := get_seq_id;
2146 -- Setting item attributes
2147 l_return_Status := Set_Attributes(
2148 l_clmv_rec, -- IN
2149 l_def_clmv_rec); -- OUT
2150 --- If any errors happen abort API
2151 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2152 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2153 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2154 RAISE OKC_API.G_EXCEPTION_ERROR;
2155 END IF;
2156 l_def_clmv_rec := fill_who_columns(l_def_clmv_rec);
2157
2158 -- SMODUGA added for claim number generation. Bug 3234773
2159 l_return_status := get_policy_number(l_def_clmv_rec ,
2160 l_policy_number,
2161 l_ipy_type);
2162 --- If any errors happen abort API
2163 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2164 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2165 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2166 RAISE OKC_API.G_EXCEPTION_ERROR;
2167 END IF;
2168 l_policy_string_len := length(l_policy_number);
2169 l_claim_like := l_policy_number || ' - %';
2170 -- get sequence number
2171 open c_claim_seq(l_def_clmv_rec.ipy_id,l_ipy_type,l_claim_like); --Bug 3941692
2172 fetch c_claim_seq into l_seq;
2173 close c_claim_seq;
2174 IF (l_seq < 1) THEN
2175 l_seq := 1;
2176 l_def_clmv_rec.CLAIM_NUMBER := l_policy_number ||' - '|| TO_CHAR(l_seq) ;
2177 ELSE
2178 l_def_clmv_rec.CLAIM_NUMBER := l_policy_number ||' - '|| TO_CHAR(l_seq) ;
2179 --check for existing claims with same claim number.
2180 l_def_clmv_rec.claim_number := check_duplicate(l_def_clmv_rec.claim_number,
2181 l_seq,
2182 l_policy_number);
2183 END IF;
2184 -- END SMODUGA added for claim number generation. Bug 3234773
2185
2186
2187 --- Validate all non-missing attributes (Item Level Validation)
2188 l_return_status := Validate_Attributes(l_def_clmv_rec);
2189 --- If any errors happen abort API
2190 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2191 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2192 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2193 RAISE OKC_API.G_EXCEPTION_ERROR;
2194 END IF;
2195 l_return_status := Validate_Record(l_def_clmv_rec);
2196 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2197 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2198 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2199 RAISE OKC_API.G_EXCEPTION_ERROR;
2200 END IF;
2201 -----------------------------------------
2202 -- Move VIEW record to "Child" records --
2203 -----------------------------------------
2204 migrate(l_def_clmv_rec, l_clm_rec);
2205 migrate(l_def_clmv_rec, l_okl_ins_claims_tl_rec);
2206 -----------------------------------------------
2207 -- Call the INSERT_ROW for each child record --
2208 -----------------------------------------------
2209 insert_row(
2210 p_init_msg_list,
2211 l_return_status,
2212 x_msg_count,
2213 x_msg_data,
2214 l_clm_rec,
2215 lx_clm_rec
2216 );
2217 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2218 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2219 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2220 RAISE OKC_API.G_EXCEPTION_ERROR;
2221 END IF;
2222 migrate(lx_clm_rec, l_def_clmv_rec);
2223 insert_row(
2224 p_init_msg_list,
2225 l_return_status,
2226 x_msg_count,
2227 x_msg_data,
2228 l_okl_ins_claims_tl_rec,
2229 lx_okl_ins_claims_tl_rec
2230 );
2231 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2232 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2233 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2234 RAISE OKC_API.G_EXCEPTION_ERROR;
2235 END IF;
2236 migrate(lx_okl_ins_claims_tl_rec, l_def_clmv_rec);
2237 -- Set OUT values
2238 x_clmv_rec := l_def_clmv_rec;
2239 x_return_status := l_return_status;
2240 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2241 EXCEPTION
2242 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2243 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2244 (
2245 l_api_name,
2246 G_PKG_NAME,
2247 'OKC_API.G_RET_STS_ERROR',
2248 x_msg_count,
2249 x_msg_data,
2250 '_PVT'
2251 );
2252 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2253 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2254 (
2255 l_api_name,
2256 G_PKG_NAME,
2257 'OKC_API.G_RET_STS_UNEXP_ERROR',
2258 x_msg_count,
2259 x_msg_data,
2260 '_PVT'
2261 );
2262 WHEN OTHERS THEN
2263 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2264 (
2265 l_api_name,
2266 G_PKG_NAME,
2267 'OTHERS',
2268 x_msg_count,
2269 x_msg_data,
2270 '_PVT'
2271 );
2272 END insert_row;
2273 ----------------------------------------
2274 -- PL/SQL TBL insert_row for:CLMV_TBL --
2275 ----------------------------------------
2276 PROCEDURE insert_row(
2277 p_api_version IN NUMBER,
2278 p_init_msg_list IN VARCHAR2 ,
2279 x_return_status OUT NOCOPY VARCHAR2,
2280 x_msg_count OUT NOCOPY NUMBER,
2281 x_msg_data OUT NOCOPY VARCHAR2,
2282 p_clmv_tbl IN clmv_tbl_type,
2283 x_clmv_tbl OUT NOCOPY clmv_tbl_type,
2284 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2285 l_api_version CONSTANT NUMBER := 1;
2286 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
2287 i NUMBER := 0;
2288 BEGIN
2289 OKC_API.init_msg_list(p_init_msg_list);
2290 -- Make sure PL/SQL table has records in it before passing
2291 IF (p_clmv_tbl.COUNT > 0) THEN
2292 i := p_clmv_tbl.FIRST;
2293 LOOP
2294 DECLARE
2295 l_error_rec OKL_API.ERROR_REC_TYPE;
2296 BEGIN
2297 l_error_rec.api_name := l_api_name;
2298 l_error_rec.api_package := G_PKG_NAME;
2299 l_error_rec.idx := i;
2300 insert_row (
2301 p_api_version => p_api_version,
2302 p_init_msg_list => OKC_API.G_FALSE,
2303 x_return_status => l_error_rec.error_type,
2304 x_msg_count => l_error_rec.msg_count,
2305 x_msg_data => l_error_rec.msg_data,
2306 p_clmv_rec => p_clmv_tbl(i),
2307 x_clmv_rec => x_clmv_tbl(i));
2308 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2309 l_error_rec.sqlcode := SQLCODE;
2310 load_error_tbl(l_error_rec, px_error_tbl);
2311 ELSE
2312 x_msg_count := l_error_rec.msg_count;
2313 x_msg_data := l_error_rec.msg_data;
2314 END IF;
2315 EXCEPTION
2316 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2317 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2318 l_error_rec.sqlcode := SQLCODE;
2319 load_error_tbl(l_error_rec, px_error_tbl);
2320 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2321 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2322 l_error_rec.sqlcode := SQLCODE;
2323 load_error_tbl(l_error_rec, px_error_tbl);
2324 WHEN OTHERS THEN
2325 l_error_rec.error_type := 'OTHERS';
2326 l_error_rec.sqlcode := SQLCODE;
2327 load_error_tbl(l_error_rec, px_error_tbl);
2328 END;
2329 EXIT WHEN (i = p_clmv_tbl.LAST);
2330 i := p_clmv_tbl.NEXT(i);
2331 END LOOP;
2332 END IF;
2333 -- Loop through the error_tbl to find the error with the highest severity
2334 -- and return it.
2335 x_return_status := find_highest_exception(px_error_tbl);
2336 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2337 EXCEPTION
2338 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2339 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2340 (
2341 l_api_name,
2342 G_PKG_NAME,
2343 'OKC_API.G_RET_STS_ERROR',
2344 x_msg_count,
2345 x_msg_data,
2346 '_PVT'
2347 );
2348 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2349 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2350 (
2351 l_api_name,
2352 G_PKG_NAME,
2353 'OKC_API.G_RET_STS_UNEXP_ERROR',
2354 x_msg_count,
2355 x_msg_data,
2356 '_PVT'
2357 );
2358 WHEN OTHERS THEN
2359 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2360 (
2361 l_api_name,
2362 G_PKG_NAME,
2363 'OTHERS',
2364 x_msg_count,
2365 x_msg_data,
2366 '_PVT'
2367 );
2368 END insert_row;
2369 ----------------------------------------
2370 -- PL/SQL TBL insert_row for:CLMV_TBL --
2371 ----------------------------------------
2372 PROCEDURE insert_row(
2373 p_api_version IN NUMBER,
2374 p_init_msg_list IN VARCHAR2 ,
2375 x_return_status OUT NOCOPY VARCHAR2,
2376 x_msg_count OUT NOCOPY NUMBER,
2377 x_msg_data OUT NOCOPY VARCHAR2,
2378 p_clmv_tbl IN clmv_tbl_type,
2379 x_clmv_tbl OUT NOCOPY clmv_tbl_type) IS
2380 l_api_version CONSTANT NUMBER := 1;
2381 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2382 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2383 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2384 BEGIN
2385 OKC_API.init_msg_list(p_init_msg_list);
2386 -- Make sure PL/SQL table has records in it before passing
2387 IF (p_clmv_tbl.COUNT > 0) THEN
2388 insert_row (
2389 p_api_version => p_api_version,
2390 p_init_msg_list => OKC_API.G_FALSE,
2391 x_return_status => x_return_status,
2392 x_msg_count => x_msg_count,
2393 x_msg_data => x_msg_data,
2394 p_clmv_tbl => p_clmv_tbl,
2395 x_clmv_tbl => x_clmv_tbl,
2396 px_error_tbl => l_error_tbl);
2397 END IF;
2398 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2399 EXCEPTION
2400 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2401 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2402 (
2403 l_api_name,
2404 G_PKG_NAME,
2405 'OKC_API.G_RET_STS_ERROR',
2406 x_msg_count,
2407 x_msg_data,
2408 '_PVT'
2409 );
2410 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2411 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2412 (
2413 l_api_name,
2414 G_PKG_NAME,
2415 'OKC_API.G_RET_STS_UNEXP_ERROR',
2416 x_msg_count,
2417 x_msg_data,
2418 '_PVT'
2419 );
2420 WHEN OTHERS THEN
2421 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2422 (
2423 l_api_name,
2424 G_PKG_NAME,
2425 'OTHERS',
2426 x_msg_count,
2427 x_msg_data,
2428 '_PVT'
2429 );
2430 END insert_row;
2431 ---------------------------------------------------------------------------
2432 -- PROCEDURE lock_row
2433 ---------------------------------------------------------------------------
2434 -----------------------------------
2435 -- lock_row for:OKL_INS_CLAIMS_B --
2436 -----------------------------------
2437 PROCEDURE lock_row(
2438 p_init_msg_list IN VARCHAR2 ,
2439 x_return_status OUT NOCOPY VARCHAR2,
2440 x_msg_count OUT NOCOPY NUMBER,
2441 x_msg_data OUT NOCOPY VARCHAR2,
2442 p_clm_rec IN clm_rec_type) IS
2443 E_Resource_Busy EXCEPTION;
2444 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2445 CURSOR lock_csr (p_clm_rec IN clm_rec_type) IS
2446 SELECT OBJECT_VERSION_NUMBER
2447 FROM OKL_INS_CLAIMS_B
2448 WHERE ID = p_clm_rec.id
2449 AND OBJECT_VERSION_NUMBER = p_clm_rec.object_version_number
2450 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2451 CURSOR lchk_csr (p_clm_rec IN clm_rec_type) IS
2452 SELECT OBJECT_VERSION_NUMBER
2453 FROM OKL_INS_CLAIMS_B
2454 WHERE ID = p_clm_rec.id;
2455 l_api_version CONSTANT NUMBER := 1;
2456 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2457 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2458 l_object_version_number OKL_INS_CLAIMS_B.OBJECT_VERSION_NUMBER%TYPE;
2459 lc_object_version_number OKL_INS_CLAIMS_B.OBJECT_VERSION_NUMBER%TYPE;
2460 l_row_notfound BOOLEAN := FALSE;
2461 lc_row_notfound BOOLEAN := FALSE;
2462 BEGIN
2463 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2464 p_init_msg_list,
2465 '_PVT',
2466 x_return_status);
2467 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2468 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2469 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2470 RAISE OKC_API.G_EXCEPTION_ERROR;
2471 END IF;
2472 BEGIN
2473 OPEN lock_csr(p_clm_rec);
2474 FETCH lock_csr INTO l_object_version_number;
2475 l_row_notfound := lock_csr%NOTFOUND;
2476 CLOSE lock_csr;
2477 EXCEPTION
2478 WHEN E_Resource_Busy THEN
2479 IF (lock_csr%ISOPEN) THEN
2480 CLOSE lock_csr;
2481 END IF;
2482 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2483 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2484 END;
2485 IF ( l_row_notfound ) THEN
2486 OPEN lchk_csr(p_clm_rec);
2487 FETCH lchk_csr INTO lc_object_version_number;
2488 lc_row_notfound := lchk_csr%NOTFOUND;
2489 CLOSE lchk_csr;
2490 END IF;
2491 IF (lc_row_notfound) THEN
2492 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2493 RAISE OKC_API.G_EXCEPTION_ERROR;
2494 ELSIF lc_object_version_number > p_clm_rec.object_version_number THEN
2495 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2496 RAISE OKC_API.G_EXCEPTION_ERROR;
2497 ELSIF lc_object_version_number <> p_clm_rec.object_version_number THEN
2498 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2499 RAISE OKC_API.G_EXCEPTION_ERROR;
2500 ELSIF lc_object_version_number = -1 THEN
2501 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2502 RAISE OKC_API.G_EXCEPTION_ERROR;
2503 END IF;
2504 x_return_status := l_return_status;
2505 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2506 EXCEPTION
2507 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2508 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2509 (
2510 l_api_name,
2511 G_PKG_NAME,
2512 'OKC_API.G_RET_STS_ERROR',
2513 x_msg_count,
2514 x_msg_data,
2515 '_PVT'
2516 );
2517 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2518 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2519 (
2520 l_api_name,
2521 G_PKG_NAME,
2522 'OKC_API.G_RET_STS_UNEXP_ERROR',
2523 x_msg_count,
2524 x_msg_data,
2525 '_PVT'
2526 );
2527 WHEN OTHERS THEN
2528 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2529 (
2530 l_api_name,
2531 G_PKG_NAME,
2532 'OTHERS',
2533 x_msg_count,
2534 x_msg_data,
2535 '_PVT'
2536 );
2537 END lock_row;
2538 ------------------------------------
2539 -- lock_row for:OKL_INS_CLAIMS_TL --
2540 ------------------------------------
2541 PROCEDURE lock_row(
2542 p_init_msg_list IN VARCHAR2 ,
2543 x_return_status OUT NOCOPY VARCHAR2,
2544 x_msg_count OUT NOCOPY NUMBER,
2545 x_msg_data OUT NOCOPY VARCHAR2,
2546 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type) IS
2547 E_Resource_Busy EXCEPTION;
2548 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2549 CURSOR lock_csr (p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type) IS
2550 SELECT *
2551 FROM OKL_INS_CLAIMS_TL
2552 WHERE ID = p_okl_ins_claims_tl_rec.id
2553 FOR UPDATE NOWAIT;
2554 l_api_version CONSTANT NUMBER := 1;
2555 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2556 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2557 l_lock_var lock_csr%ROWTYPE;
2558 l_row_notfound BOOLEAN := FALSE;
2559 lc_row_notfound BOOLEAN := FALSE;
2560 BEGIN
2561 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2562 p_init_msg_list,
2563 '_PVT',
2564 x_return_status);
2565 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2566 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2567 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2568 RAISE OKC_API.G_EXCEPTION_ERROR;
2569 END IF;
2570 BEGIN
2571 OPEN lock_csr(p_okl_ins_claims_tl_rec);
2572 FETCH lock_csr INTO l_lock_var;
2573 l_row_notfound := lock_csr%NOTFOUND;
2574 CLOSE lock_csr;
2575 EXCEPTION
2576 WHEN E_Resource_Busy THEN
2577 IF (lock_csr%ISOPEN) THEN
2578 CLOSE lock_csr;
2579 END IF;
2580 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2581 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2582 END;
2583 IF ( l_row_notfound ) THEN
2584 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2585 RAISE OKC_API.G_EXCEPTION_ERROR;
2586 END IF;
2587 x_return_status := l_return_status;
2588 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2589 EXCEPTION
2590 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2591 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2592 (
2593 l_api_name,
2594 G_PKG_NAME,
2595 'OKC_API.G_RET_STS_ERROR',
2596 x_msg_count,
2597 x_msg_data,
2598 '_PVT'
2599 );
2600 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2601 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2602 (
2603 l_api_name,
2604 G_PKG_NAME,
2605 'OKC_API.G_RET_STS_UNEXP_ERROR',
2606 x_msg_count,
2607 x_msg_data,
2608 '_PVT'
2609 );
2610 WHEN OTHERS THEN
2611 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2612 (
2613 l_api_name,
2614 G_PKG_NAME,
2615 'OTHERS',
2616 x_msg_count,
2617 x_msg_data,
2618 '_PVT'
2619 );
2620 END lock_row;
2621 ------------------------------------
2622 -- lock_row for: OKL_INS_CLAIMS_V --
2623 ------------------------------------
2624 PROCEDURE lock_row(
2625 p_api_version IN NUMBER,
2626 p_init_msg_list IN VARCHAR2 ,
2627 x_return_status OUT NOCOPY VARCHAR2,
2628 x_msg_count OUT NOCOPY NUMBER,
2629 x_msg_data OUT NOCOPY VARCHAR2,
2630 p_clmv_rec IN clmv_rec_type) IS
2631 l_api_version CONSTANT NUMBER := 1;
2632 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2633 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2634 l_clm_rec clm_rec_type;
2635 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
2636 BEGIN
2637 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2638 G_PKG_NAME,
2639 p_init_msg_list,
2640 l_api_version,
2641 p_api_version,
2642 '_PVT',
2643 x_return_status);
2644 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2645 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2646 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2647 RAISE OKC_API.G_EXCEPTION_ERROR;
2648 END IF;
2649 -----------------------------------------
2650 -- Move VIEW record to "Child" records --
2651 -----------------------------------------
2652 migrate(p_clmv_rec, l_clm_rec);
2653 migrate(p_clmv_rec, l_okl_ins_claims_tl_rec);
2654 ---------------------------------------------
2655 -- Call the LOCK_ROW for each child record --
2656 ---------------------------------------------
2657 lock_row(
2658 p_init_msg_list,
2659 l_return_status,
2660 x_msg_count,
2661 x_msg_data,
2662 l_clm_rec
2663 );
2664 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2665 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2666 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2667 RAISE OKC_API.G_EXCEPTION_ERROR;
2668 END IF;
2669 lock_row(
2670 p_init_msg_list,
2671 l_return_status,
2672 x_msg_count,
2673 x_msg_data,
2674 l_okl_ins_claims_tl_rec
2675 );
2676 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2677 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2678 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2679 RAISE OKC_API.G_EXCEPTION_ERROR;
2680 END IF;
2681 x_return_status := l_return_status;
2682 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2683 EXCEPTION
2684 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2685 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2686 (
2687 l_api_name,
2688 G_PKG_NAME,
2689 'OKC_API.G_RET_STS_ERROR',
2690 x_msg_count,
2691 x_msg_data,
2692 '_PVT'
2693 );
2694 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2695 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2696 (
2697 l_api_name,
2698 G_PKG_NAME,
2699 'OKC_API.G_RET_STS_UNEXP_ERROR',
2700 x_msg_count,
2701 x_msg_data,
2702 '_PVT'
2703 );
2704 WHEN OTHERS THEN
2705 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2706 (
2707 l_api_name,
2708 G_PKG_NAME,
2709 'OTHERS',
2710 x_msg_count,
2711 x_msg_data,
2712 '_PVT'
2713 );
2714 END lock_row;
2715 --------------------------------------
2716 -- PL/SQL TBL lock_row for:CLMV_TBL --
2717 --------------------------------------
2718 PROCEDURE lock_row(
2719 p_api_version IN NUMBER,
2720 p_init_msg_list IN VARCHAR2 ,
2721 x_return_status OUT NOCOPY VARCHAR2,
2722 x_msg_count OUT NOCOPY NUMBER,
2723 x_msg_data OUT NOCOPY VARCHAR2,
2724 p_clmv_tbl IN clmv_tbl_type,
2725 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2726 l_api_version CONSTANT NUMBER := 1;
2727 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2728 i NUMBER := 0;
2729 BEGIN
2730 OKC_API.init_msg_list(p_init_msg_list);
2731 -- Make sure PL/SQL table has recrods in it before passing
2732 IF (p_clmv_tbl.COUNT > 0) THEN
2733 i := p_clmv_tbl.FIRST;
2734 LOOP
2735 DECLARE
2736 l_error_rec OKL_API.ERROR_REC_TYPE;
2737 BEGIN
2738 l_error_rec.api_name := l_api_name;
2739 l_error_rec.api_package := G_PKG_NAME;
2740 l_error_rec.idx := i;
2741 lock_row(
2742 p_api_version => p_api_version,
2743 p_init_msg_list => OKC_API.G_FALSE,
2744 x_return_status => l_error_rec.error_type,
2745 x_msg_count => l_error_rec.msg_count,
2746 x_msg_data => l_error_rec.msg_data,
2747 p_clmv_rec => p_clmv_tbl(i));
2748 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2749 l_error_rec.sqlcode := SQLCODE;
2750 load_error_tbl(l_error_rec, px_error_tbl);
2751 ELSE
2752 x_msg_count := l_error_rec.msg_count;
2753 x_msg_data := l_error_rec.msg_data;
2754 END IF;
2755 EXCEPTION
2756 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2757 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2758 l_error_rec.sqlcode := SQLCODE;
2759 load_error_tbl(l_error_rec, px_error_tbl);
2760 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2761 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2762 l_error_rec.sqlcode := SQLCODE;
2763 load_error_tbl(l_error_rec, px_error_tbl);
2764 WHEN OTHERS THEN
2765 l_error_rec.error_type := 'OTHERS';
2766 l_error_rec.sqlcode := SQLCODE;
2767 load_error_tbl(l_error_rec, px_error_tbl);
2768 END;
2769 EXIT WHEN (i = p_clmv_tbl.LAST);
2770 i := p_clmv_tbl.NEXT(i);
2771 END LOOP;
2772 END IF;
2773 -- Loop through the error_tbl to find the error with the highest severity
2774 -- and return it.
2775 x_return_status := find_highest_exception(px_error_tbl);
2776 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2777 EXCEPTION
2778 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2779 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2780 (
2781 l_api_name,
2782 G_PKG_NAME,
2783 'OKC_API.G_RET_STS_ERROR',
2784 x_msg_count,
2785 x_msg_data,
2786 '_PVT'
2787 );
2788 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2789 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2790 (
2791 l_api_name,
2792 G_PKG_NAME,
2793 'OKC_API.G_RET_STS_UNEXP_ERROR',
2794 x_msg_count,
2795 x_msg_data,
2796 '_PVT'
2797 );
2798 WHEN OTHERS THEN
2799 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2800 (
2801 l_api_name,
2802 G_PKG_NAME,
2803 'OTHERS',
2804 x_msg_count,
2805 x_msg_data,
2806 '_PVT'
2807 );
2808 END lock_row;
2809 --------------------------------------
2810 -- PL/SQL TBL lock_row for:CLMV_TBL --
2811 --------------------------------------
2812 PROCEDURE lock_row(
2813 p_api_version IN NUMBER,
2814 p_init_msg_list IN VARCHAR2 ,
2815 x_return_status OUT NOCOPY VARCHAR2,
2816 x_msg_count OUT NOCOPY NUMBER,
2817 x_msg_data OUT NOCOPY VARCHAR2,
2818 p_clmv_tbl IN clmv_tbl_type) IS
2819 l_api_version CONSTANT NUMBER := 1;
2820 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2821 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2822 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2823 BEGIN
2824 OKC_API.init_msg_list(p_init_msg_list);
2825 -- Make sure PL/SQL table has recrods in it before passing
2826 IF (p_clmv_tbl.COUNT > 0) THEN
2827 lock_row(
2828 p_api_version => p_api_version,
2829 p_init_msg_list => OKC_API.G_FALSE,
2830 x_return_status => x_return_status,
2831 x_msg_count => x_msg_count,
2832 x_msg_data => x_msg_data,
2833 p_clmv_tbl => p_clmv_tbl,
2834 px_error_tbl => l_error_tbl);
2835 END IF;
2836 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2837 EXCEPTION
2838 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2839 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2840 (
2841 l_api_name,
2842 G_PKG_NAME,
2843 'OKC_API.G_RET_STS_ERROR',
2844 x_msg_count,
2845 x_msg_data,
2846 '_PVT'
2847 );
2848 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2849 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2850 (
2851 l_api_name,
2852 G_PKG_NAME,
2853 'OKC_API.G_RET_STS_UNEXP_ERROR',
2854 x_msg_count,
2855 x_msg_data,
2856 '_PVT'
2857 );
2858 WHEN OTHERS THEN
2859 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2860 (
2861 l_api_name,
2862 G_PKG_NAME,
2863 'OTHERS',
2864 x_msg_count,
2865 x_msg_data,
2866 '_PVT'
2867 );
2868 END lock_row;
2869 ---------------------------------------------------------------------------
2870 -- PROCEDURE update_row
2871 ---------------------------------------------------------------------------
2872 -------------------------------------
2873 -- update_row for:OKL_INS_CLAIMS_B --
2874 -------------------------------------
2875 PROCEDURE update_row(
2876 p_init_msg_list IN VARCHAR2 ,
2877 x_return_status OUT NOCOPY VARCHAR2,
2878 x_msg_count OUT NOCOPY NUMBER,
2879 x_msg_data OUT NOCOPY VARCHAR2,
2880 p_clm_rec IN clm_rec_type,
2881 x_clm_rec OUT NOCOPY clm_rec_type) IS
2882 l_api_version CONSTANT NUMBER := 1;
2883 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2884 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2885 l_clm_rec clm_rec_type := p_clm_rec;
2886 l_def_clm_rec clm_rec_type;
2887 l_row_notfound BOOLEAN := TRUE;
2888 ----------------------------------
2889 -- FUNCTION populate_new_record --
2890 ----------------------------------
2891 FUNCTION populate_new_record (
2892 p_clm_rec IN clm_rec_type,
2893 x_clm_rec OUT NOCOPY clm_rec_type
2894 ) RETURN VARCHAR2 IS
2895 l_clm_rec clm_rec_type;
2896 l_row_notfound BOOLEAN := TRUE;
2897 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2898 BEGIN
2899 x_clm_rec := p_clm_rec;
2900 -- Get current database values
2901 l_clm_rec := get_rec(p_clm_rec, l_return_status);
2902 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2903 IF (x_clm_rec.id = OKC_API.G_MISS_NUM)
2904 THEN
2905 x_clm_rec.id := l_clm_rec.id;
2906 END IF;
2907 IF (x_clm_rec.claim_number = OKC_API.G_MISS_CHAR)
2908 THEN
2909 x_clm_rec.claim_number := l_clm_rec.claim_number;
2910 END IF;
2911 IF (x_clm_rec.csu_code = OKC_API.G_MISS_CHAR)
2912 THEN
2913 x_clm_rec.csu_code := l_clm_rec.csu_code;
2914 END IF;
2915 IF (x_clm_rec.ipy_id = OKC_API.G_MISS_NUM)
2916 THEN
2917 x_clm_rec.ipy_id := l_clm_rec.ipy_id;
2918 END IF;
2919 IF (x_clm_rec.ltp_code = OKC_API.G_MISS_CHAR)
2920 THEN
2921 x_clm_rec.ltp_code := l_clm_rec.ltp_code;
2922 END IF;
2923 IF (x_clm_rec.program_update_date = OKC_API.G_MISS_DATE)
2924 THEN
2925 x_clm_rec.program_update_date := l_clm_rec.program_update_date;
2926 END IF;
2927 IF (x_clm_rec.claim_date = OKC_API.G_MISS_DATE)
2928 THEN
2929 x_clm_rec.claim_date := l_clm_rec.claim_date;
2930 END IF;
2931 IF (x_clm_rec.program_id = OKC_API.G_MISS_NUM)
2932 THEN
2933 x_clm_rec.program_id := l_clm_rec.program_id;
2934 END IF;
2935 IF (x_clm_rec.loss_date = OKC_API.G_MISS_DATE)
2936 THEN
2937 x_clm_rec.loss_date := l_clm_rec.loss_date;
2938 END IF;
2939 IF (x_clm_rec.police_contact = OKC_API.G_MISS_CHAR)
2940 THEN
2941 x_clm_rec.police_contact := l_clm_rec.police_contact;
2942 END IF;
2943 IF (x_clm_rec.amount = OKC_API.G_MISS_NUM)
2944 THEN
2945 x_clm_rec.amount := l_clm_rec.amount;
2946 END IF;
2947 IF (x_clm_rec.object_version_number = OKC_API.G_MISS_NUM)
2948 THEN
2949 x_clm_rec.object_version_number := l_clm_rec.object_version_number;
2950 END IF;
2951 IF (x_clm_rec.request_id = OKC_API.G_MISS_NUM)
2952 THEN
2953 x_clm_rec.request_id := l_clm_rec.request_id;
2954 END IF;
2955 IF (x_clm_rec.program_application_id = OKC_API.G_MISS_NUM)
2956 THEN
2957 x_clm_rec.program_application_id := l_clm_rec.program_application_id;
2958 END IF;
2959 IF (x_clm_rec.attribute_category = OKC_API.G_MISS_CHAR)
2960 THEN
2961 x_clm_rec.attribute_category := l_clm_rec.attribute_category;
2962 END IF;
2963 IF (x_clm_rec.attribute1 = OKC_API.G_MISS_CHAR)
2964 THEN
2965 x_clm_rec.attribute1 := l_clm_rec.attribute1;
2966 END IF;
2967 IF (x_clm_rec.attribute2 = OKC_API.G_MISS_CHAR)
2968 THEN
2969 x_clm_rec.attribute2 := l_clm_rec.attribute2;
2970 END IF;
2971 IF (x_clm_rec.attribute3 = OKC_API.G_MISS_CHAR)
2972 THEN
2973 x_clm_rec.attribute3 := l_clm_rec.attribute3;
2974 END IF;
2975 IF (x_clm_rec.attribute4 = OKC_API.G_MISS_CHAR)
2976 THEN
2977 x_clm_rec.attribute4 := l_clm_rec.attribute4;
2978 END IF;
2979 IF (x_clm_rec.attribute5 = OKC_API.G_MISS_CHAR)
2980 THEN
2981 x_clm_rec.attribute5 := l_clm_rec.attribute5;
2982 END IF;
2983 IF (x_clm_rec.attribute6 = OKC_API.G_MISS_CHAR)
2984 THEN
2985 x_clm_rec.attribute6 := l_clm_rec.attribute6;
2986 END IF;
2987 IF (x_clm_rec.attribute7 = OKC_API.G_MISS_CHAR)
2988 THEN
2989 x_clm_rec.attribute7 := l_clm_rec.attribute7;
2990 END IF;
2991 IF (x_clm_rec.attribute8 = OKC_API.G_MISS_CHAR)
2992 THEN
2993 x_clm_rec.attribute8 := l_clm_rec.attribute8;
2994 END IF;
2995 IF (x_clm_rec.attribute9 = OKC_API.G_MISS_CHAR)
2996 THEN
2997 x_clm_rec.attribute9 := l_clm_rec.attribute9;
2998 END IF;
2999 IF (x_clm_rec.attribute10 = OKC_API.G_MISS_CHAR)
3000 THEN
3001 x_clm_rec.attribute10 := l_clm_rec.attribute10;
3002 END IF;
3003 IF (x_clm_rec.attribute11 = OKC_API.G_MISS_CHAR)
3004 THEN
3005 x_clm_rec.attribute11 := l_clm_rec.attribute11;
3006 END IF;
3007 IF (x_clm_rec.attribute12 = OKC_API.G_MISS_CHAR)
3008 THEN
3009 x_clm_rec.attribute12 := l_clm_rec.attribute12;
3010 END IF;
3011 IF (x_clm_rec.attribute13 = OKC_API.G_MISS_CHAR)
3012 THEN
3013 x_clm_rec.attribute13 := l_clm_rec.attribute13;
3014 END IF;
3015 IF (x_clm_rec.attribute14 = OKC_API.G_MISS_CHAR)
3016 THEN
3017 x_clm_rec.attribute14 := l_clm_rec.attribute14;
3018 END IF;
3019 IF (x_clm_rec.attribute15 = OKC_API.G_MISS_CHAR)
3020 THEN
3021 x_clm_rec.attribute15 := l_clm_rec.attribute15;
3022 END IF;
3023 IF (x_clm_rec.hold_date = OKC_API.G_MISS_DATE)
3024 THEN
3025 x_clm_rec.hold_date := l_clm_rec.hold_date;
3026 END IF;
3027 IF (x_clm_rec.org_id = OKC_API.G_MISS_NUM)
3028 THEN
3029 x_clm_rec.org_id := l_clm_rec.org_id;
3030 END IF;
3031 IF (x_clm_rec.created_by = OKC_API.G_MISS_NUM)
3032 THEN
3033 x_clm_rec.created_by := l_clm_rec.created_by;
3034 END IF;
3035 IF (x_clm_rec.creation_date = OKC_API.G_MISS_DATE)
3036 THEN
3037 x_clm_rec.creation_date := l_clm_rec.creation_date;
3038 END IF;
3039 IF (x_clm_rec.last_updated_by = OKC_API.G_MISS_NUM)
3040 THEN
3041 x_clm_rec.last_updated_by := l_clm_rec.last_updated_by;
3042 END IF;
3043 IF (x_clm_rec.last_update_date = OKC_API.G_MISS_DATE)
3044 THEN
3045 x_clm_rec.last_update_date := l_clm_rec.last_update_date;
3046 END IF;
3047 IF (x_clm_rec.last_update_login = OKC_API.G_MISS_NUM)
3048 THEN
3049 x_clm_rec.last_update_login := l_clm_rec.last_update_login;
3050 END IF;
3051 END IF;
3052 RETURN(l_return_status);
3053 END populate_new_record;
3054 -----------------------------------------
3055 -- Set_Attributes for:OKL_INS_CLAIMS_B --
3056 -----------------------------------------
3057 FUNCTION Set_Attributes (
3058 p_clm_rec IN clm_rec_type,
3059 x_clm_rec OUT NOCOPY clm_rec_type
3060 ) RETURN VARCHAR2 IS
3061 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3062 BEGIN
3063 x_clm_rec := p_clm_rec;
3064 --Smoduga added for inventory org fix 3348924
3065 x_clm_rec.org_id := get_inv_org_id(p_clm_rec.ipy_id);
3066 x_clm_rec.OBJECT_VERSION_NUMBER := p_clm_rec.OBJECT_VERSION_NUMBER + 1;
3067 RETURN(l_return_status);
3068 END Set_Attributes;
3069 BEGIN
3070 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3071 p_init_msg_list,
3072 '_PVT',
3073 x_return_status);
3074 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3075 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3076 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3077 RAISE OKC_API.G_EXCEPTION_ERROR;
3078 END IF;
3079 --- Setting item attributes
3080 l_return_status := Set_Attributes(
3081 p_clm_rec, -- IN
3082 l_clm_rec); -- OUT
3083 --- If any errors happen abort API
3084 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3085 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3086 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3087 RAISE OKC_API.G_EXCEPTION_ERROR;
3088 END IF;
3089 l_return_status := populate_new_record(l_clm_rec, l_def_clm_rec);
3090 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3091 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3092 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3093 RAISE OKC_API.G_EXCEPTION_ERROR;
3094 END IF;
3095 UPDATE OKL_INS_CLAIMS_B
3096 SET CLAIM_NUMBER = l_def_clm_rec.claim_number,
3097 CSU_CODE = l_def_clm_rec.csu_code,
3098 IPY_ID = l_def_clm_rec.ipy_id,
3099 LTP_CODE = l_def_clm_rec.ltp_code,
3100 PROGRAM_UPDATE_DATE = l_def_clm_rec.program_update_date,
3101 CLAIM_DATE = l_def_clm_rec.claim_date,
3102 PROGRAM_ID = l_def_clm_rec.program_id,
3103 LOSS_DATE = l_def_clm_rec.loss_date,
3104 POLICE_CONTACT = l_def_clm_rec.police_contact,
3105 AMOUNT = l_def_clm_rec.amount,
3106 OBJECT_VERSION_NUMBER = l_def_clm_rec.object_version_number,
3107 REQUEST_ID = l_def_clm_rec.request_id,
3108 PROGRAM_APPLICATION_ID = l_def_clm_rec.program_application_id,
3109 ATTRIBUTE_CATEGORY = l_def_clm_rec.attribute_category,
3110 ATTRIBUTE1 = l_def_clm_rec.attribute1,
3111 ATTRIBUTE2 = l_def_clm_rec.attribute2,
3112 ATTRIBUTE3 = l_def_clm_rec.attribute3,
3113 ATTRIBUTE4 = l_def_clm_rec.attribute4,
3114 ATTRIBUTE5 = l_def_clm_rec.attribute5,
3115 ATTRIBUTE6 = l_def_clm_rec.attribute6,
3116 ATTRIBUTE7 = l_def_clm_rec.attribute7,
3117 ATTRIBUTE8 = l_def_clm_rec.attribute8,
3118 ATTRIBUTE9 = l_def_clm_rec.attribute9,
3119 ATTRIBUTE10 = l_def_clm_rec.attribute10,
3120 ATTRIBUTE11 = l_def_clm_rec.attribute11,
3121 ATTRIBUTE12 = l_def_clm_rec.attribute12,
3122 ATTRIBUTE13 = l_def_clm_rec.attribute13,
3123 ATTRIBUTE14 = l_def_clm_rec.attribute14,
3124 ATTRIBUTE15 = l_def_clm_rec.attribute15,
3125 HOLD_DATE = l_def_clm_rec.hold_date,
3126 ORG_ID = l_def_clm_rec.org_id,
3127 CREATED_BY = l_def_clm_rec.created_by,
3128 CREATION_DATE = l_def_clm_rec.creation_date,
3129 LAST_UPDATED_BY = l_def_clm_rec.last_updated_by,
3130 LAST_UPDATE_DATE = l_def_clm_rec.last_update_date,
3131 LAST_UPDATE_LOGIN = l_def_clm_rec.last_update_login
3132 WHERE ID = l_def_clm_rec.id;
3133 x_clm_rec := l_clm_rec;
3134 x_return_status := l_return_status;
3135 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3136 EXCEPTION
3137 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3138 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3139 (
3140 l_api_name,
3141 G_PKG_NAME,
3142 'OKC_API.G_RET_STS_ERROR',
3143 x_msg_count,
3144 x_msg_data,
3145 '_PVT'
3146 );
3147 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3148 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3149 (
3150 l_api_name,
3151 G_PKG_NAME,
3152 'OKC_API.G_RET_STS_UNEXP_ERROR',
3153 x_msg_count,
3154 x_msg_data,
3155 '_PVT'
3156 );
3157 WHEN OTHERS THEN
3158 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3159 (
3160 l_api_name,
3161 G_PKG_NAME,
3162 'OTHERS',
3163 x_msg_count,
3164 x_msg_data,
3165 '_PVT'
3166 );
3167 END update_row;
3168 --------------------------------------
3169 -- update_row for:OKL_INS_CLAIMS_TL --
3170 --------------------------------------
3171 PROCEDURE update_row(
3172 p_init_msg_list IN VARCHAR2 ,
3173 x_return_status OUT NOCOPY VARCHAR2,
3174 x_msg_count OUT NOCOPY NUMBER,
3175 x_msg_data OUT NOCOPY VARCHAR2,
3176 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type,
3177 x_okl_ins_claims_tl_rec OUT NOCOPY okl_ins_claims_tl_rec_type) IS
3178 l_api_version CONSTANT NUMBER := 1;
3179 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
3180 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3181 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type := p_okl_ins_claims_tl_rec;
3182 l_def_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
3183 l_row_notfound BOOLEAN := TRUE;
3184 ----------------------------------
3185 -- FUNCTION populate_new_record --
3186 ----------------------------------
3187 FUNCTION populate_new_record (
3188 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type,
3189 x_okl_ins_claims_tl_rec OUT NOCOPY okl_ins_claims_tl_rec_type
3190 ) RETURN VARCHAR2 IS
3191 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
3192 l_row_notfound BOOLEAN := TRUE;
3193 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3194 BEGIN
3195 x_okl_ins_claims_tl_rec := p_okl_ins_claims_tl_rec;
3196 -- Get current database values
3197 l_okl_ins_claims_tl_rec := get_rec(p_okl_ins_claims_tl_rec, l_return_status);
3198 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3199 IF (x_okl_ins_claims_tl_rec.id = OKC_API.G_MISS_NUM)
3200 THEN
3201 x_okl_ins_claims_tl_rec.id := l_okl_ins_claims_tl_rec.id;
3202 END IF;
3203 IF (x_okl_ins_claims_tl_rec.language = OKC_API.G_MISS_CHAR)
3204 THEN
3205 x_okl_ins_claims_tl_rec.language := l_okl_ins_claims_tl_rec.language;
3206 END IF;
3207 IF (x_okl_ins_claims_tl_rec.description = OKC_API.G_MISS_CHAR)
3208 THEN
3209 x_okl_ins_claims_tl_rec.description := l_okl_ins_claims_tl_rec.description;
3210 END IF;
3211 IF (x_okl_ins_claims_tl_rec.police_report = OKC_API.G_MISS_CHAR)
3212 THEN
3213 x_okl_ins_claims_tl_rec.police_report := l_okl_ins_claims_tl_rec.police_report;
3214 END IF;
3215 IF (x_okl_ins_claims_tl_rec.comments = OKC_API.G_MISS_CHAR)
3216 THEN
3217 x_okl_ins_claims_tl_rec.comments := l_okl_ins_claims_tl_rec.comments;
3218 END IF;
3219 IF (x_okl_ins_claims_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
3220 THEN
3221 x_okl_ins_claims_tl_rec.source_lang := l_okl_ins_claims_tl_rec.source_lang;
3222 END IF;
3223 IF (x_okl_ins_claims_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3224 THEN
3225 x_okl_ins_claims_tl_rec.sfwt_flag := l_okl_ins_claims_tl_rec.sfwt_flag;
3226 END IF;
3227 IF (x_okl_ins_claims_tl_rec.created_by = OKC_API.G_MISS_NUM)
3228 THEN
3229 x_okl_ins_claims_tl_rec.created_by := l_okl_ins_claims_tl_rec.created_by;
3230 END IF;
3231 IF (x_okl_ins_claims_tl_rec.creation_date = OKC_API.G_MISS_DATE)
3232 THEN
3233 x_okl_ins_claims_tl_rec.creation_date := l_okl_ins_claims_tl_rec.creation_date;
3234 END IF;
3235 IF (x_okl_ins_claims_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
3236 THEN
3237 x_okl_ins_claims_tl_rec.last_updated_by := l_okl_ins_claims_tl_rec.last_updated_by;
3238 END IF;
3239 IF (x_okl_ins_claims_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
3240 THEN
3241 x_okl_ins_claims_tl_rec.last_update_date := l_okl_ins_claims_tl_rec.last_update_date;
3242 END IF;
3243 IF (x_okl_ins_claims_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
3244 THEN
3245 x_okl_ins_claims_tl_rec.last_update_login := l_okl_ins_claims_tl_rec.last_update_login;
3246 END IF;
3247 END IF;
3248 RETURN(l_return_status);
3249 END populate_new_record;
3250 ------------------------------------------
3251 -- Set_Attributes for:OKL_INS_CLAIMS_TL --
3252 ------------------------------------------
3253 FUNCTION Set_Attributes (
3254 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type,
3255 x_okl_ins_claims_tl_rec OUT NOCOPY okl_ins_claims_tl_rec_type
3256 ) RETURN VARCHAR2 IS
3257 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3258 BEGIN
3259 x_okl_ins_claims_tl_rec := p_okl_ins_claims_tl_rec;
3260 x_okl_ins_claims_tl_rec.LANGUAGE := USERENV('LANG');
3261 x_okl_ins_claims_tl_rec.LANGUAGE := USERENV('LANG');
3262 RETURN(l_return_status);
3263 END Set_Attributes;
3264 BEGIN
3265 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3266 p_init_msg_list,
3267 '_PVT',
3268 x_return_status);
3269 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3270 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3271 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3272 RAISE OKC_API.G_EXCEPTION_ERROR;
3273 END IF;
3274 --- Setting item attributes
3275 l_return_status := Set_Attributes(
3276 p_okl_ins_claims_tl_rec, -- IN
3277 l_okl_ins_claims_tl_rec); -- OUT
3278 --- If any errors happen abort API
3279 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3280 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3281 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3282 RAISE OKC_API.G_EXCEPTION_ERROR;
3283 END IF;
3284 l_return_status := populate_new_record(l_okl_ins_claims_tl_rec, l_def_okl_ins_claims_tl_rec);
3285 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3286 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3287 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3288 RAISE OKC_API.G_EXCEPTION_ERROR;
3289 END IF;
3290 UPDATE OKL_INS_CLAIMS_TL
3291 SET DESCRIPTION = l_def_okl_ins_claims_tl_rec.description,
3292 SOURCE_LANG = l_def_okl_ins_claims_tl_rec.source_lang, --Smoduga added for bug 3637102
3293 POLICE_REPORT = l_def_okl_ins_claims_tl_rec.police_report,
3294 COMMENTS = l_def_okl_ins_claims_tl_rec.comments,
3295 CREATED_BY = l_def_okl_ins_claims_tl_rec.created_by,
3296 CREATION_DATE = l_def_okl_ins_claims_tl_rec.creation_date,
3297 LAST_UPDATED_BY = l_def_okl_ins_claims_tl_rec.last_updated_by,
3298 LAST_UPDATE_DATE = l_def_okl_ins_claims_tl_rec.last_update_date,
3299 LAST_UPDATE_LOGIN = l_def_okl_ins_claims_tl_rec.last_update_login
3300 WHERE ID = l_def_okl_ins_claims_tl_rec.id
3301 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE); --Fix for 3637102 added
3302 -- LANGUAGE
3303 UPDATE OKL_INS_CLAIMS_TL
3304 SET SFWT_FLAG = 'Y'
3305 WHERE ID = l_def_okl_ins_claims_tl_rec.id
3306 AND SOURCE_LANG <> USERENV('LANG');
3307 x_okl_ins_claims_tl_rec := l_okl_ins_claims_tl_rec;
3308 x_return_status := l_return_status;
3309 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3310 EXCEPTION
3311 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3312 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3313 (
3314 l_api_name,
3315 G_PKG_NAME,
3316 'OKC_API.G_RET_STS_ERROR',
3317 x_msg_count,
3318 x_msg_data,
3319 '_PVT'
3320 );
3321 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3322 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3323 (
3324 l_api_name,
3325 G_PKG_NAME,
3326 'OKC_API.G_RET_STS_UNEXP_ERROR',
3327 x_msg_count,
3328 x_msg_data,
3329 '_PVT'
3330 );
3331 WHEN OTHERS THEN
3332 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3333 (
3334 l_api_name,
3335 G_PKG_NAME,
3336 'OTHERS',
3337 x_msg_count,
3338 x_msg_data,
3339 '_PVT'
3340 );
3341 END update_row;
3342 -------------------------------------
3343 -- update_row for:OKL_INS_CLAIMS_V --
3344 -------------------------------------
3345 PROCEDURE update_row(
3346 p_api_version IN NUMBER,
3347 p_init_msg_list IN VARCHAR2 ,
3348 x_return_status OUT NOCOPY VARCHAR2,
3349 x_msg_count OUT NOCOPY NUMBER,
3350 x_msg_data OUT NOCOPY VARCHAR2,
3351 p_clmv_rec IN clmv_rec_type,
3352 x_clmv_rec OUT NOCOPY clmv_rec_type) IS
3353 l_api_version CONSTANT NUMBER := 1;
3354 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3355 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3356 l_clmv_rec clmv_rec_type := p_clmv_rec;
3357 l_def_clmv_rec clmv_rec_type;
3358 l_db_clmv_rec clmv_rec_type;
3359 l_clm_rec clm_rec_type;
3360 lx_clm_rec clm_rec_type;
3361 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
3362 lx_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
3363 -------------------------------
3364 -- FUNCTION fill_who_columns --
3365 -------------------------------
3366 FUNCTION fill_who_columns (
3367 p_clmv_rec IN clmv_rec_type
3368 ) RETURN clmv_rec_type IS
3369 l_clmv_rec clmv_rec_type := p_clmv_rec;
3370 BEGIN
3371 l_clmv_rec.LAST_UPDATE_DATE := SYSDATE;
3372 l_clmv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3373 l_clmv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3374 RETURN(l_clmv_rec);
3375 END fill_who_columns;
3376 ----------------------------------
3377 -- FUNCTION populate_new_record --
3378 ----------------------------------
3379 FUNCTION populate_new_record (
3380 p_clmv_rec IN clmv_rec_type,
3381 x_clmv_rec OUT NOCOPY clmv_rec_type
3382 ) RETURN VARCHAR2 IS
3383 l_row_notfound BOOLEAN := TRUE;
3384 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3385 BEGIN
3386 x_clmv_rec := p_clmv_rec;
3387 -- Get current database values
3388 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
3389 -- so it may be verified through LOCK_ROW.
3390 l_db_clmv_rec := get_rec(p_clmv_rec, l_return_status);
3391 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3392 IF (x_clmv_rec.id = OKC_API.G_MISS_NUM)
3393 THEN
3394 x_clmv_rec.id := l_db_clmv_rec.id;
3395 END IF;
3396 IF (x_clmv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3397 THEN
3398 x_clmv_rec.sfwt_flag := l_db_clmv_rec.sfwt_flag;
3399 END IF;
3400 IF (x_clmv_rec.ipy_id = OKC_API.G_MISS_NUM)
3401 THEN
3402 x_clmv_rec.ipy_id := l_db_clmv_rec.ipy_id;
3403 END IF;
3404 IF (x_clmv_rec.ltp_code = OKC_API.G_MISS_CHAR)
3405 THEN
3406 x_clmv_rec.ltp_code := l_db_clmv_rec.ltp_code;
3407 END IF;
3408 IF (x_clmv_rec.csu_code = OKC_API.G_MISS_CHAR)
3409 THEN
3410 x_clmv_rec.csu_code := l_db_clmv_rec.csu_code;
3411 END IF;
3412 IF (x_clmv_rec.claim_number = OKC_API.G_MISS_CHAR)
3413 THEN
3414 x_clmv_rec.claim_number := l_db_clmv_rec.claim_number;
3415 END IF;
3416 IF (x_clmv_rec.claim_date = OKC_API.G_MISS_DATE)
3417 THEN
3418 x_clmv_rec.claim_date := l_db_clmv_rec.claim_date;
3419 END IF;
3420 IF (x_clmv_rec.loss_date = OKC_API.G_MISS_DATE)
3421 THEN
3422 x_clmv_rec.loss_date := l_db_clmv_rec.loss_date;
3423 END IF;
3424 IF (x_clmv_rec.description = OKC_API.G_MISS_CHAR)
3425 THEN
3426 x_clmv_rec.description := l_db_clmv_rec.description;
3427 END IF;
3428 IF (x_clmv_rec.police_contact = OKC_API.G_MISS_CHAR)
3429 THEN
3430 x_clmv_rec.police_contact := l_db_clmv_rec.police_contact;
3431 END IF;
3432 IF (x_clmv_rec.police_report = OKC_API.G_MISS_CHAR)
3433 THEN
3434 x_clmv_rec.police_report := l_db_clmv_rec.police_report;
3435 END IF;
3436 IF (x_clmv_rec.amount = OKC_API.G_MISS_NUM)
3437 THEN
3438 x_clmv_rec.amount := l_db_clmv_rec.amount;
3439 END IF;
3440 IF (x_clmv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3441 THEN
3442 x_clmv_rec.attribute_category := l_db_clmv_rec.attribute_category;
3443 END IF;
3444 IF (x_clmv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3445 THEN
3446 x_clmv_rec.attribute1 := l_db_clmv_rec.attribute1;
3447 END IF;
3448 IF (x_clmv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3449 THEN
3450 x_clmv_rec.attribute2 := l_db_clmv_rec.attribute2;
3451 END IF;
3452 IF (x_clmv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3453 THEN
3454 x_clmv_rec.attribute3 := l_db_clmv_rec.attribute3;
3455 END IF;
3456 IF (x_clmv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3457 THEN
3458 x_clmv_rec.attribute4 := l_db_clmv_rec.attribute4;
3459 END IF;
3460 IF (x_clmv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3461 THEN
3462 x_clmv_rec.attribute5 := l_db_clmv_rec.attribute5;
3463 END IF;
3464 IF (x_clmv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3465 THEN
3466 x_clmv_rec.attribute6 := l_db_clmv_rec.attribute6;
3467 END IF;
3468 IF (x_clmv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3469 THEN
3470 x_clmv_rec.attribute7 := l_db_clmv_rec.attribute7;
3471 END IF;
3472 IF (x_clmv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3473 THEN
3474 x_clmv_rec.attribute8 := l_db_clmv_rec.attribute8;
3475 END IF;
3476 IF (x_clmv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3477 THEN
3478 x_clmv_rec.attribute9 := l_db_clmv_rec.attribute9;
3479 END IF;
3480 IF (x_clmv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3481 THEN
3482 x_clmv_rec.attribute10 := l_db_clmv_rec.attribute10;
3483 END IF;
3484 IF (x_clmv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3485 THEN
3486 x_clmv_rec.attribute11 := l_db_clmv_rec.attribute11;
3487 END IF;
3488 IF (x_clmv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3489 THEN
3490 x_clmv_rec.attribute12 := l_db_clmv_rec.attribute12;
3491 END IF;
3492 IF (x_clmv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3493 THEN
3494 x_clmv_rec.attribute13 := l_db_clmv_rec.attribute13;
3495 END IF;
3496 IF (x_clmv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3497 THEN
3498 x_clmv_rec.attribute14 := l_db_clmv_rec.attribute14;
3499 END IF;
3500 IF (x_clmv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3501 THEN
3502 x_clmv_rec.attribute15 := l_db_clmv_rec.attribute15;
3503 END IF;
3504 IF (x_clmv_rec.hold_date = OKC_API.G_MISS_DATE)
3505 THEN
3506 x_clmv_rec.hold_date := l_db_clmv_rec.hold_date;
3507 END IF;
3508 IF (x_clmv_rec.org_id = OKC_API.G_MISS_NUM)
3509 THEN
3510 x_clmv_rec.org_id := l_db_clmv_rec.org_id;
3511 END IF;
3512 IF (x_clmv_rec.request_id = OKC_API.G_MISS_NUM)
3513 THEN
3514 x_clmv_rec.request_id := l_db_clmv_rec.request_id;
3515 END IF;
3516 IF (x_clmv_rec.program_application_id = OKC_API.G_MISS_NUM)
3517 THEN
3518 x_clmv_rec.program_application_id := l_db_clmv_rec.program_application_id;
3519 END IF;
3520 IF (x_clmv_rec.program_id = OKC_API.G_MISS_NUM)
3521 THEN
3522 x_clmv_rec.program_id := l_db_clmv_rec.program_id;
3523 END IF;
3524 IF (x_clmv_rec.program_update_date = OKC_API.G_MISS_DATE)
3525 THEN
3526 x_clmv_rec.program_update_date := l_db_clmv_rec.program_update_date;
3527 END IF;
3528 IF (x_clmv_rec.created_by = OKC_API.G_MISS_NUM)
3529 THEN
3530 x_clmv_rec.created_by := l_db_clmv_rec.created_by;
3531 END IF;
3532 IF (x_clmv_rec.creation_date = OKC_API.G_MISS_DATE)
3533 THEN
3534 x_clmv_rec.creation_date := l_db_clmv_rec.creation_date;
3535 END IF;
3536 IF (x_clmv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3537 THEN
3538 x_clmv_rec.last_updated_by := l_db_clmv_rec.last_updated_by;
3539 END IF;
3540 IF (x_clmv_rec.last_update_date = OKC_API.G_MISS_DATE)
3541 THEN
3542 x_clmv_rec.last_update_date := l_db_clmv_rec.last_update_date;
3543 END IF;
3544 IF (x_clmv_rec.last_update_login = OKC_API.G_MISS_NUM)
3545 THEN
3546 x_clmv_rec.last_update_login := l_db_clmv_rec.last_update_login;
3547 END IF;
3548 END IF;
3549 RETURN(l_return_status);
3550 END populate_new_record;
3551 -----------------------------------------
3552 -- Set_Attributes for:OKL_INS_CLAIMS_V --
3553 -----------------------------------------
3554 FUNCTION Set_Attributes (
3555 p_clmv_rec IN clmv_rec_type,
3556 x_clmv_rec OUT NOCOPY clmv_rec_type
3557 ) RETURN VARCHAR2 IS
3558 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3559 BEGIN
3560 x_clmv_rec := p_clmv_rec;
3561 --Smoduga added for inventory org fix 3348924
3562 x_clmv_rec.org_id := get_inv_org_id(p_clmv_rec.ipy_id);
3563 RETURN(l_return_status);
3564 END Set_Attributes;
3565 BEGIN
3566 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3567 G_PKG_NAME,
3568 p_init_msg_list,
3569 l_api_version,
3570 p_api_version,
3571 '_PVT',
3572 x_return_status);
3573 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3574 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3575 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3576 RAISE OKC_API.G_EXCEPTION_ERROR;
3577 END IF;
3578 --- Setting item attributes
3579 l_return_status := Set_Attributes(
3580 p_clmv_rec, -- IN
3581 x_clmv_rec); -- OUT
3582 --- If any errors happen abort API
3583 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3584 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3585 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3586 RAISE OKC_API.G_EXCEPTION_ERROR;
3587 END IF;
3588 l_return_status := populate_new_record(l_clmv_rec, l_def_clmv_rec);
3589 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3590 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3591 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3592 RAISE OKC_API.G_EXCEPTION_ERROR;
3593 END IF;
3594 l_def_clmv_rec := fill_who_columns(l_def_clmv_rec);
3595 --- Validate all non-missing attributes (Item Level Validation)
3596 l_return_status := Validate_Attributes(l_def_clmv_rec);
3597 --- If any errors happen abort API
3598 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3599 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3600 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3601 RAISE OKC_API.G_EXCEPTION_ERROR;
3602 END IF;
3603 l_return_status := Validate_Record(l_def_clmv_rec, l_db_clmv_rec);
3604 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3605 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3606 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3607 RAISE OKC_API.G_EXCEPTION_ERROR;
3608 END IF;
3609 -- Lock the Record
3610 lock_row(
3611 p_api_version => p_api_version,
3612 p_init_msg_list => p_init_msg_list,
3613 x_return_status => l_return_status,
3614 x_msg_count => x_msg_count,
3615 x_msg_data => x_msg_data,
3616 p_clmv_rec => p_clmv_rec);
3617 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3618 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3619 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3620 RAISE OKC_API.G_EXCEPTION_ERROR;
3621 END IF;
3622 -----------------------------------------
3623 -- Move VIEW record to "Child" records --
3624 -----------------------------------------
3625 migrate(l_def_clmv_rec, l_clm_rec);
3626 migrate(l_def_clmv_rec, l_okl_ins_claims_tl_rec);
3627 -----------------------------------------------
3628 -- Call the UPDATE_ROW for each child record --
3629 -----------------------------------------------
3630 update_row(
3631 p_init_msg_list,
3632 l_return_status,
3633 x_msg_count,
3634 x_msg_data,
3635 l_clm_rec,
3636 lx_clm_rec
3637 );
3638 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3639 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3640 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3641 RAISE OKC_API.G_EXCEPTION_ERROR;
3642 END IF;
3643 migrate(lx_clm_rec, l_def_clmv_rec);
3644 update_row(
3645 p_init_msg_list,
3646 l_return_status,
3647 x_msg_count,
3648 x_msg_data,
3649 l_okl_ins_claims_tl_rec,
3650 lx_okl_ins_claims_tl_rec
3651 );
3652 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3653 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3654 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3655 RAISE OKC_API.G_EXCEPTION_ERROR;
3656 END IF;
3657 migrate(lx_okl_ins_claims_tl_rec, l_def_clmv_rec);
3658 x_clmv_rec := l_def_clmv_rec;
3659 x_return_status := l_return_status;
3660 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3661 EXCEPTION
3662 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3663 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3664 (
3665 l_api_name,
3666 G_PKG_NAME,
3667 'OKC_API.G_RET_STS_ERROR',
3668 x_msg_count,
3669 x_msg_data,
3670 '_PVT'
3671 );
3672 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3673 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3674 (
3675 l_api_name,
3676 G_PKG_NAME,
3677 'OKC_API.G_RET_STS_UNEXP_ERROR',
3678 x_msg_count,
3679 x_msg_data,
3680 '_PVT'
3681 );
3682 WHEN OTHERS THEN
3683 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3684 (
3685 l_api_name,
3686 G_PKG_NAME,
3687 'OTHERS',
3688 x_msg_count,
3689 x_msg_data,
3690 '_PVT'
3691 );
3692 END update_row;
3693 ----------------------------------------
3694 -- PL/SQL TBL update_row for:clmv_tbl --
3695 ----------------------------------------
3696 PROCEDURE update_row(
3697 p_api_version IN NUMBER,
3698 p_init_msg_list IN VARCHAR2 ,
3699 x_return_status OUT NOCOPY VARCHAR2,
3700 x_msg_count OUT NOCOPY NUMBER,
3701 x_msg_data OUT NOCOPY VARCHAR2,
3702 p_clmv_tbl IN clmv_tbl_type,
3703 x_clmv_tbl OUT NOCOPY clmv_tbl_type,
3704 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3705 l_api_version CONSTANT NUMBER := 1;
3706 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
3707 i NUMBER := 0;
3708 BEGIN
3709 OKC_API.init_msg_list(p_init_msg_list);
3710 -- Make sure PL/SQL table has records in it before passing
3711 IF (p_clmv_tbl.COUNT > 0) THEN
3712 i := p_clmv_tbl.FIRST;
3713 LOOP
3714 DECLARE
3715 l_error_rec OKL_API.ERROR_REC_TYPE;
3716 BEGIN
3717 l_error_rec.api_name := l_api_name;
3718 l_error_rec.api_package := G_PKG_NAME;
3719 l_error_rec.idx := i;
3720 update_row (
3721 p_api_version => p_api_version,
3722 p_init_msg_list => OKC_API.G_FALSE,
3723 x_return_status => l_error_rec.error_type,
3724 x_msg_count => l_error_rec.msg_count,
3725 x_msg_data => l_error_rec.msg_data,
3726 p_clmv_rec => p_clmv_tbl(i),
3727 x_clmv_rec => x_clmv_tbl(i));
3728 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
3729 l_error_rec.sqlcode := SQLCODE;
3730 load_error_tbl(l_error_rec, px_error_tbl);
3731 ELSE
3732 x_msg_count := l_error_rec.msg_count;
3733 x_msg_data := l_error_rec.msg_data;
3734 END IF;
3735 EXCEPTION
3736 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3737 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
3738 l_error_rec.sqlcode := SQLCODE;
3739 load_error_tbl(l_error_rec, px_error_tbl);
3740 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3741 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
3742 l_error_rec.sqlcode := SQLCODE;
3743 load_error_tbl(l_error_rec, px_error_tbl);
3744 WHEN OTHERS THEN
3745 l_error_rec.error_type := 'OTHERS';
3746 l_error_rec.sqlcode := SQLCODE;
3747 load_error_tbl(l_error_rec, px_error_tbl);
3748 END;
3749 EXIT WHEN (i = p_clmv_tbl.LAST);
3750 i := p_clmv_tbl.NEXT(i);
3751 END LOOP;
3752 END IF;
3753 -- Loop through the error_tbl to find the error with the highest severity
3754 -- and return it.
3755 x_return_status := find_highest_exception(px_error_tbl);
3756 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3757 EXCEPTION
3758 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3759 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3760 (
3761 l_api_name,
3762 G_PKG_NAME,
3763 'OKC_API.G_RET_STS_ERROR',
3764 x_msg_count,
3765 x_msg_data,
3766 '_PVT'
3767 );
3768 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3769 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3770 (
3771 l_api_name,
3772 G_PKG_NAME,
3773 'OKC_API.G_RET_STS_UNEXP_ERROR',
3774 x_msg_count,
3775 x_msg_data,
3776 '_PVT'
3777 );
3778 WHEN OTHERS THEN
3779 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3780 (
3781 l_api_name,
3782 G_PKG_NAME,
3783 'OTHERS',
3784 x_msg_count,
3785 x_msg_data,
3786 '_PVT'
3787 );
3788 END update_row;
3789 ----------------------------------------
3790 -- PL/SQL TBL update_row for:CLMV_TBL --
3791 ----------------------------------------
3792 PROCEDURE update_row(
3793 p_api_version IN NUMBER,
3794 p_init_msg_list IN VARCHAR2 ,
3795 x_return_status OUT NOCOPY VARCHAR2,
3796 x_msg_count OUT NOCOPY NUMBER,
3797 x_msg_data OUT NOCOPY VARCHAR2,
3798 p_clmv_tbl IN clmv_tbl_type,
3799 x_clmv_tbl OUT NOCOPY clmv_tbl_type) IS
3800 l_api_version CONSTANT NUMBER := 1;
3801 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3802 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3803 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3804 BEGIN
3805 OKC_API.init_msg_list(p_init_msg_list);
3806 -- Make sure PL/SQL table has records in it before passing
3807 IF (p_clmv_tbl.COUNT > 0) THEN
3808 update_row (
3809 p_api_version => p_api_version,
3810 p_init_msg_list => OKC_API.G_FALSE,
3811 x_return_status => x_return_status,
3812 x_msg_count => x_msg_count,
3813 x_msg_data => x_msg_data,
3814 p_clmv_tbl => p_clmv_tbl,
3815 x_clmv_tbl => x_clmv_tbl,
3816 px_error_tbl => l_error_tbl);
3817 END IF;
3818 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3819 EXCEPTION
3820 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3821 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3822 (
3823 l_api_name,
3824 G_PKG_NAME,
3825 'OKC_API.G_RET_STS_ERROR',
3826 x_msg_count,
3827 x_msg_data,
3828 '_PVT'
3829 );
3830 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3831 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3832 (
3833 l_api_name,
3834 G_PKG_NAME,
3835 'OKC_API.G_RET_STS_UNEXP_ERROR',
3836 x_msg_count,
3837 x_msg_data,
3838 '_PVT'
3839 );
3840 WHEN OTHERS THEN
3841 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3842 (
3843 l_api_name,
3844 G_PKG_NAME,
3845 'OTHERS',
3846 x_msg_count,
3847 x_msg_data,
3848 '_PVT'
3849 );
3850 END update_row;
3851 ---------------------------------------------------------------------------
3852 -- PROCEDURE delete_row
3853 ---------------------------------------------------------------------------
3854 -------------------------------------
3855 -- delete_row for:OKL_INS_CLAIMS_B --
3856 -------------------------------------
3857 PROCEDURE delete_row(
3858 p_init_msg_list IN VARCHAR2 ,
3859 x_return_status OUT NOCOPY VARCHAR2,
3860 x_msg_count OUT NOCOPY NUMBER,
3861 x_msg_data OUT NOCOPY VARCHAR2,
3862 p_clm_rec IN clm_rec_type) IS
3863 l_api_version CONSTANT NUMBER := 1;
3864 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3865 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3866 l_clm_rec clm_rec_type := p_clm_rec;
3867 l_row_notfound BOOLEAN := TRUE;
3868 BEGIN
3869 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3870 p_init_msg_list,
3871 '_PVT',
3872 x_return_status);
3873 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3874 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3875 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3876 RAISE OKC_API.G_EXCEPTION_ERROR;
3877 END IF;
3878 DELETE FROM OKL_INS_CLAIMS_B
3879 WHERE ID = p_clm_rec.id;
3880 x_return_status := l_return_status;
3881 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3882 EXCEPTION
3883 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3884 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3885 (
3886 l_api_name,
3887 G_PKG_NAME,
3888 'OKC_API.G_RET_STS_ERROR',
3889 x_msg_count,
3890 x_msg_data,
3891 '_PVT'
3892 );
3893 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3894 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3895 (
3896 l_api_name,
3897 G_PKG_NAME,
3898 'OKC_API.G_RET_STS_UNEXP_ERROR',
3899 x_msg_count,
3900 x_msg_data,
3901 '_PVT'
3902 );
3903 WHEN OTHERS THEN
3904 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3905 (
3906 l_api_name,
3907 G_PKG_NAME,
3908 'OTHERS',
3909 x_msg_count,
3910 x_msg_data,
3911 '_PVT'
3912 );
3913 END delete_row;
3914 --------------------------------------
3915 -- delete_row for:OKL_INS_CLAIMS_TL --
3916 --------------------------------------
3917 PROCEDURE delete_row(
3918 p_init_msg_list IN VARCHAR2 ,
3919 x_return_status OUT NOCOPY VARCHAR2,
3920 x_msg_count OUT NOCOPY NUMBER,
3921 x_msg_data OUT NOCOPY VARCHAR2,
3922 p_okl_ins_claims_tl_rec IN okl_ins_claims_tl_rec_type) IS
3923 l_api_version CONSTANT NUMBER := 1;
3924 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3925 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3926 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type := p_okl_ins_claims_tl_rec;
3927 l_row_notfound BOOLEAN := TRUE;
3928 BEGIN
3929 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3930 p_init_msg_list,
3931 '_PVT',
3932 x_return_status);
3933 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3934 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3935 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3936 RAISE OKC_API.G_EXCEPTION_ERROR;
3937 END IF;
3938 DELETE FROM OKL_INS_CLAIMS_TL
3939 WHERE ID = p_okl_ins_claims_tl_rec.id;
3940 x_return_status := l_return_status;
3941 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3942 EXCEPTION
3943 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3944 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3945 (
3946 l_api_name,
3947 G_PKG_NAME,
3948 'OKC_API.G_RET_STS_ERROR',
3949 x_msg_count,
3950 x_msg_data,
3951 '_PVT'
3952 );
3953 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3954 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3955 (
3956 l_api_name,
3957 G_PKG_NAME,
3958 'OKC_API.G_RET_STS_UNEXP_ERROR',
3959 x_msg_count,
3960 x_msg_data,
3961 '_PVT'
3962 );
3963 WHEN OTHERS THEN
3964 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3965 (
3966 l_api_name,
3967 G_PKG_NAME,
3968 'OTHERS',
3969 x_msg_count,
3970 x_msg_data,
3971 '_PVT'
3972 );
3973 END delete_row;
3974 -------------------------------------
3975 -- delete_row for:OKL_INS_CLAIMS_V --
3976 -------------------------------------
3977 PROCEDURE delete_row(
3978 p_api_version IN NUMBER,
3979 p_init_msg_list IN VARCHAR2 ,
3980 x_return_status OUT NOCOPY VARCHAR2,
3981 x_msg_count OUT NOCOPY NUMBER,
3982 x_msg_data OUT NOCOPY VARCHAR2,
3983 p_clmv_rec IN clmv_rec_type) IS
3984 l_api_version CONSTANT NUMBER := 1;
3985 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3986 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3987 l_clmv_rec clmv_rec_type := p_clmv_rec;
3988 l_okl_ins_claims_tl_rec okl_ins_claims_tl_rec_type;
3989 l_clm_rec clm_rec_type;
3990 BEGIN
3991 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3992 G_PKG_NAME,
3993 p_init_msg_list,
3994 l_api_version,
3995 p_api_version,
3996 '_PVT',
3997 x_return_status);
3998 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3999 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4000 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4001 RAISE OKC_API.G_EXCEPTION_ERROR;
4002 END IF;
4003 -----------------------------------------
4004 -- Move VIEW record to "Child" records --
4005 -----------------------------------------
4006 migrate(l_clmv_rec, l_okl_ins_claims_tl_rec);
4007 migrate(l_clmv_rec, l_clm_rec);
4008 -----------------------------------------------
4009 -- Call the DELETE_ROW for each child record --
4010 -----------------------------------------------
4011 delete_row(
4012 p_init_msg_list,
4013 l_return_status,
4014 x_msg_count,
4015 x_msg_data,
4016 l_okl_ins_claims_tl_rec
4017 );
4018 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4019 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4020 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4021 RAISE OKC_API.G_EXCEPTION_ERROR;
4022 END IF;
4023 delete_row(
4024 p_init_msg_list,
4025 l_return_status,
4026 x_msg_count,
4027 x_msg_data,
4028 l_clm_rec
4029 );
4030 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4031 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4032 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4033 RAISE OKC_API.G_EXCEPTION_ERROR;
4034 END IF;
4035 x_return_status := l_return_status;
4036 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4037 EXCEPTION
4038 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4039 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4040 (
4041 l_api_name,
4042 G_PKG_NAME,
4043 'OKC_API.G_RET_STS_ERROR',
4044 x_msg_count,
4045 x_msg_data,
4046 '_PVT'
4047 );
4048 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4049 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4050 (
4051 l_api_name,
4052 G_PKG_NAME,
4053 'OKC_API.G_RET_STS_UNEXP_ERROR',
4054 x_msg_count,
4055 x_msg_data,
4056 '_PVT'
4057 );
4058 WHEN OTHERS THEN
4059 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4060 (
4061 l_api_name,
4062 G_PKG_NAME,
4063 'OTHERS',
4064 x_msg_count,
4065 x_msg_data,
4066 '_PVT'
4067 );
4068 END delete_row;
4069 ------------------------------------------------
4070 -- PL/SQL TBL delete_row for:OKL_INS_CLAIMS_V --
4071 ------------------------------------------------
4072 PROCEDURE delete_row(
4073 p_api_version IN NUMBER,
4074 p_init_msg_list IN VARCHAR2 ,
4075 x_return_status OUT NOCOPY VARCHAR2,
4076 x_msg_count OUT NOCOPY NUMBER,
4077 x_msg_data OUT NOCOPY VARCHAR2,
4078 p_clmv_tbl IN clmv_tbl_type,
4079 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
4080 l_api_version CONSTANT NUMBER := 1;
4081 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
4082 i NUMBER := 0;
4083 BEGIN
4084 OKC_API.init_msg_list(p_init_msg_list);
4085 -- Make sure PL/SQL table has records in it before passing
4086 IF (p_clmv_tbl.COUNT > 0) THEN
4087 i := p_clmv_tbl.FIRST;
4088 LOOP
4089 DECLARE
4090 l_error_rec OKL_API.ERROR_REC_TYPE;
4091 BEGIN
4092 l_error_rec.api_name := l_api_name;
4093 l_error_rec.api_package := G_PKG_NAME;
4094 l_error_rec.idx := i;
4095 delete_row (
4096 p_api_version => p_api_version,
4097 p_init_msg_list => OKC_API.G_FALSE,
4098 x_return_status => l_error_rec.error_type,
4099 x_msg_count => l_error_rec.msg_count,
4100 x_msg_data => l_error_rec.msg_data,
4101 p_clmv_rec => p_clmv_tbl(i));
4102 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
4103 l_error_rec.sqlcode := SQLCODE;
4104 load_error_tbl(l_error_rec, px_error_tbl);
4105 ELSE
4106 x_msg_count := l_error_rec.msg_count;
4107 x_msg_data := l_error_rec.msg_data;
4108 END IF;
4109 EXCEPTION
4110 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4111 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
4112 l_error_rec.sqlcode := SQLCODE;
4113 load_error_tbl(l_error_rec, px_error_tbl);
4114 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4115 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
4116 l_error_rec.sqlcode := SQLCODE;
4117 load_error_tbl(l_error_rec, px_error_tbl);
4118 WHEN OTHERS THEN
4119 l_error_rec.error_type := 'OTHERS';
4120 l_error_rec.sqlcode := SQLCODE;
4121 load_error_tbl(l_error_rec, px_error_tbl);
4122 END;
4123 EXIT WHEN (i = p_clmv_tbl.LAST);
4124 i := p_clmv_tbl.NEXT(i);
4125 END LOOP;
4126 END IF;
4127 -- Loop through the error_tbl to find the error with the highest severity
4128 -- and return it.
4129 x_return_status := find_highest_exception(px_error_tbl);
4130 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4131 EXCEPTION
4132 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4133 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4134 (
4135 l_api_name,
4136 G_PKG_NAME,
4137 'OKC_API.G_RET_STS_ERROR',
4138 x_msg_count,
4139 x_msg_data,
4140 '_PVT'
4141 );
4142 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4143 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4144 (
4145 l_api_name,
4146 G_PKG_NAME,
4147 'OKC_API.G_RET_STS_UNEXP_ERROR',
4148 x_msg_count,
4149 x_msg_data,
4150 '_PVT'
4151 );
4152 WHEN OTHERS THEN
4153 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4154 (
4155 l_api_name,
4156 G_PKG_NAME,
4157 'OTHERS',
4158 x_msg_count,
4159 x_msg_data,
4160 '_PVT'
4161 );
4162 END delete_row;
4163 ------------------------------------------------
4164 -- PL/SQL TBL delete_row for:OKL_INS_CLAIMS_V --
4165 ------------------------------------------------
4166 PROCEDURE delete_row(
4167 p_api_version IN NUMBER,
4168 p_init_msg_list IN VARCHAR2 ,
4169 x_return_status OUT NOCOPY VARCHAR2,
4170 x_msg_count OUT NOCOPY NUMBER,
4171 x_msg_data OUT NOCOPY VARCHAR2,
4172 p_clmv_tbl IN clmv_tbl_type) IS
4173 l_api_version CONSTANT NUMBER := 1;
4174 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
4175 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4176 l_error_tbl OKL_API.ERROR_TBL_TYPE;
4177 BEGIN
4178 OKC_API.init_msg_list(p_init_msg_list);
4179 -- Make sure PL/SQL table has records in it before passing
4180 IF (p_clmv_tbl.COUNT > 0) THEN
4181 delete_row (
4182 p_api_version => p_api_version,
4183 p_init_msg_list => OKC_API.G_FALSE,
4184 x_return_status => x_return_status,
4185 x_msg_count => x_msg_count,
4186 x_msg_data => x_msg_data,
4187 p_clmv_tbl => p_clmv_tbl,
4188 px_error_tbl => l_error_tbl);
4189 END IF;
4190 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4191 EXCEPTION
4192 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4193 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4194 (
4195 l_api_name,
4196 G_PKG_NAME,
4197 'OKC_API.G_RET_STS_ERROR',
4198 x_msg_count,
4199 x_msg_data,
4200 '_PVT'
4201 );
4202 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4203 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4204 (
4205 l_api_name,
4206 G_PKG_NAME,
4207 'OKC_API.G_RET_STS_UNEXP_ERROR',
4208 x_msg_count,
4209 x_msg_data,
4210 '_PVT'
4211 );
4212 WHEN OTHERS THEN
4213 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4214 (
4215 l_api_name,
4216 G_PKG_NAME,
4217 'OTHERS',
4218 x_msg_count,
4219 x_msg_data,
4220 '_PVT'
4221 );
4222 END delete_row;
4223 END OKL_CLM_PVT;