[Home] [Help]
PACKAGE BODY: APPS.OKL_QUE_PVT
Source
1 PACKAGE BODY OKL_QUE_PVT AS
2 /* $Header: OKLSQUEB.pls 120.0 2005/11/30 17:18:18 stmathew noship $ */
3
4 -------------------------
5 -- PROCEDURE add_language
6 -------------------------
7 PROCEDURE add_language IS
8
9 BEGIN
10
11 DELETE FROM OKL_INSURANCE_ESTIMATES_TL T
12 WHERE NOT EXISTS (SELECT NULL FROM OKL_INSURANCE_ESTIMATES_B B WHERE B.ID =T.ID);
13
14 UPDATE OKL_INSURANCE_ESTIMATES_TL T
15 SET (SHORT_DESCRIPTION,
16 DESCRIPTION,
17 COMMENTS) =
18 (SELECT
19 B.SHORT_DESCRIPTION,
20 B.DESCRIPTION,
21 B.COMMENTS
22 FROM
23 OKL_INSURANCE_ESTIMATES_TL B
24 WHERE
25 B.ID = T.ID
26 AND B.LANGUAGE = T.SOURCE_LANG)
27 WHERE (T.ID, T.LANGUAGE) IN (SELECT
28 SUBT.ID,
29 SUBT.LANGUAGE
30 FROM
31 OKL_INSURANCE_ESTIMATES_TL SUBB,
32 OKL_INSURANCE_ESTIMATES_TL SUBT
33 WHERE
34 SUBB.ID = SUBT.ID
35 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
36 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
37 OR (SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
38 OR (SUBB.COMMENTS <> SUBT.COMMENTS)
39 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
40 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
41 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
42 )
43 );
44
45 INSERT INTO OKL_INSURANCE_ESTIMATES_TL (
46 ID,
47 LANGUAGE,
48 SOURCE_LANG,
49 SFWT_FLAG,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 SHORT_DESCRIPTION,
56 DESCRIPTION,
57 COMMENTS)
58 SELECT
59 B.ID,
60 L.LANGUAGE_CODE,
61 B.SOURCE_LANG,
62 B.SFWT_FLAG,
63 B.CREATED_BY,
64 B.CREATION_DATE,
65 B.LAST_UPDATED_BY,
66 B.LAST_UPDATE_DATE,
67 B.LAST_UPDATE_LOGIN,
68 B.SHORT_DESCRIPTION,
69 B.DESCRIPTION,
70 B.COMMENTS
71 FROM OKL_INSURANCE_ESTIMATES_TL B, FND_LANGUAGES L
72 WHERE L.INSTALLED_FLAG IN ('I', 'B')
73 AND B.LANGUAGE = USERENV('LANG')
74 AND NOT EXISTS (
75 SELECT NULL
76 FROM OKL_INSURANCE_ESTIMATES_TL T
77 WHERE T.ID = B.ID
78 AND T.LANGUAGE = L.LANGUAGE_CODE
79 );
80
81 END add_language;
82
83
84 -----------------------------
85 -- FUNCTION null_out_defaults
86 -----------------------------
87 FUNCTION null_out_defaults (p_quev_rec IN quev_rec_type) RETURN quev_rec_type IS
88
89 l_quev_rec quev_rec_type;
90
91 BEGIN
92
93 l_quev_rec := p_quev_rec;
94
95 -- Not applicable to ID and OBJECT_VERSION_NUMBER
96
97 IF l_quev_rec.attribute_category = FND_API.G_MISS_CHAR THEN
98 l_quev_rec.attribute_category := NULL;
99 END IF;
100 IF l_quev_rec.attribute1 = FND_API.G_MISS_CHAR THEN
101 l_quev_rec.attribute1 := NULL;
102 END IF;
103 IF l_quev_rec.attribute2 = FND_API.G_MISS_CHAR THEN
104 l_quev_rec.attribute2 := NULL;
105 END IF;
106 IF l_quev_rec.attribute3 = FND_API.G_MISS_CHAR THEN
107 l_quev_rec.attribute3 := NULL;
108 END IF;
109 IF l_quev_rec.attribute4 = FND_API.G_MISS_CHAR THEN
110 l_quev_rec.attribute4 := NULL;
111 END IF;
112 IF l_quev_rec.attribute5 = FND_API.G_MISS_CHAR THEN
113 l_quev_rec.attribute5 := NULL;
114 END IF;
115 IF l_quev_rec.attribute6 = FND_API.G_MISS_CHAR THEN
116 l_quev_rec.attribute6 := NULL;
117 END IF;
118 IF l_quev_rec.attribute7 = FND_API.G_MISS_CHAR THEN
119 l_quev_rec.attribute7 := NULL;
120 END IF;
121 IF l_quev_rec.attribute8 = FND_API.G_MISS_CHAR THEN
122 l_quev_rec.attribute8 := NULL;
123 END IF;
124 IF l_quev_rec.attribute9 = FND_API.G_MISS_CHAR THEN
125 l_quev_rec.attribute9 := NULL;
126 END IF;
127 IF l_quev_rec.attribute10 = FND_API.G_MISS_CHAR THEN
128 l_quev_rec.attribute10 := NULL;
129 END IF;
130 IF l_quev_rec.attribute11 = FND_API.G_MISS_CHAR THEN
131 l_quev_rec.attribute11 := NULL;
132 END IF;
133 IF l_quev_rec.attribute12 = FND_API.G_MISS_CHAR THEN
134 l_quev_rec.attribute12 := NULL;
135 END IF;
136 IF l_quev_rec.attribute13 = FND_API.G_MISS_CHAR THEN
137 l_quev_rec.attribute13 := NULL;
138 END IF;
139 IF l_quev_rec.attribute14 = FND_API.G_MISS_CHAR THEN
140 l_quev_rec.attribute14 := NULL;
141 END IF;
142 IF l_quev_rec.attribute15 = FND_API.G_MISS_CHAR THEN
143 l_quev_rec.attribute15 := NULL;
144 END IF;
145 IF l_quev_rec.lease_quote_id = FND_API.G_MISS_NUM THEN
146 l_quev_rec.lease_quote_id := NULL;
147 END IF;
148 IF l_quev_rec.policy_term = FND_API.G_MISS_NUM THEN
149 l_quev_rec.policy_term := NULL;
150 END IF;
151 IF l_quev_rec.short_description = FND_API.G_MISS_CHAR THEN
152 l_quev_rec.short_description := NULL;
153 END IF;
154 IF l_quev_rec.description = FND_API.G_MISS_CHAR THEN
155 l_quev_rec.description := NULL;
156 END IF;
157 IF l_quev_rec.comments = FND_API.G_MISS_CHAR THEN
158 l_quev_rec.comments := NULL;
159 END IF;
160
161 RETURN l_quev_rec;
162
163 END null_out_defaults;
164
165
166 -------------------
167 -- FUNCTION get_rec
168 -------------------
169 FUNCTION get_rec (p_id IN NUMBER
170 ,x_return_status OUT NOCOPY VARCHAR2) RETURN quev_rec_type IS
171
172 l_quev_rec quev_rec_type;
173 l_prog_name VARCHAR2(61);
174
175 BEGIN
176
177 l_prog_name := G_PKG_NAME||'.get_rec';
178
179 SELECT
180 id
181 ,object_version_number
182 ,attribute_category
183 ,attribute1
184 ,attribute2
185 ,attribute3
186 ,attribute4
187 ,attribute5
188 ,attribute6
189 ,attribute7
190 ,attribute8
191 ,attribute9
192 ,attribute10
193 ,attribute11
194 ,attribute12
195 ,attribute13
196 ,attribute14
197 ,attribute15
198 ,lease_quote_id
199 ,policy_term
200 ,short_description
201 ,description
202 ,comments
203 INTO
204 l_quev_rec.id
205 ,l_quev_rec.object_version_number
206 ,l_quev_rec.attribute_category
207 ,l_quev_rec.attribute1
208 ,l_quev_rec.attribute2
209 ,l_quev_rec.attribute3
210 ,l_quev_rec.attribute4
211 ,l_quev_rec.attribute5
212 ,l_quev_rec.attribute6
213 ,l_quev_rec.attribute7
214 ,l_quev_rec.attribute8
215 ,l_quev_rec.attribute9
216 ,l_quev_rec.attribute10
217 ,l_quev_rec.attribute11
218 ,l_quev_rec.attribute12
219 ,l_quev_rec.attribute13
220 ,l_quev_rec.attribute14
221 ,l_quev_rec.attribute15
222 ,l_quev_rec.lease_quote_id
223 ,l_quev_rec.policy_term
224 ,l_quev_rec.short_description
225 ,l_quev_rec.description
226 ,l_quev_rec.comments
227 FROM OKL_INSURANCE_ESTIMATES_V
228 WHERE id = p_id;
229
230 x_return_status := G_RET_STS_SUCCESS;
231 RETURN l_quev_rec;
232
233 EXCEPTION
234
235 WHEN OTHERS THEN
236
237 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
238 p_msg_name => G_DB_ERROR,
239 p_token1 => G_PROG_NAME_TOKEN,
240 p_token1_value => l_prog_name,
241 p_token2 => G_SQLCODE_TOKEN,
242 p_token2_value => sqlcode,
243 p_token3 => G_SQLERRM_TOKEN,
244 p_token3_value => sqlerrm);
245
246 x_return_status := G_RET_STS_UNEXP_ERROR;
247
248 END get_rec;
249
250
251 ------------------------
252 -- PROCEDURE validate_id
253 ------------------------
254 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
255 BEGIN
256 IF p_id IS NULL THEN
257 OKL_API.set_message(p_app_name => G_APP_NAME,
258 p_msg_name => G_COL_ERROR,
259 p_token1 => G_COL_NAME_TOKEN,
260 p_token1_value => 'id',
261 p_token2 => G_PKG_NAME_TOKEN,
262 p_token2_value => G_PKG_NAME);
263 RAISE OKL_API.G_EXCEPTION_ERROR;
264 END IF;
265 x_return_status := G_RET_STS_SUCCESS;
266 END validate_id;
267
268
269 -------------------------------------------
270 -- PROCEDURE validate_object_version_number
271 -------------------------------------------
272 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
273 BEGIN
274 IF p_object_version_number IS NULL THEN
275 OKL_API.set_message(p_app_name => G_APP_NAME,
276 p_msg_name => G_COL_ERROR,
277 p_token1 => G_COL_NAME_TOKEN,
278 p_token1_value => 'object_version_number',
279 p_token2 => G_PKG_NAME_TOKEN,
280 p_token2_value => G_PKG_NAME);
281 RAISE OKL_API.G_EXCEPTION_ERROR;
282 END IF;
283 x_return_status := G_RET_STS_SUCCESS;
284 END validate_object_version_number;
285
286
287 --------------------------------------
288 -- PROCEDURE validate_lease_quote_id
289 --------------------------------------
290 PROCEDURE validate_lease_quote_id (x_return_status OUT NOCOPY VARCHAR2, p_lease_quote_id IN NUMBER) IS
291 BEGIN
292 IF p_lease_quote_id IS NULL THEN
293 OKL_API.set_message(p_app_name => G_APP_NAME,
294 p_msg_name => G_COL_ERROR,
295 p_token1 => G_COL_NAME_TOKEN,
296 p_token1_value => 'lease_quote_id',
297 p_token2 => G_PKG_NAME_TOKEN,
298 p_token2_value => G_PKG_NAME);
299 RAISE OKL_API.G_EXCEPTION_ERROR;
300 END IF;
301 x_return_status := G_RET_STS_SUCCESS;
302 END validate_lease_quote_id;
303
304
305 --------------------------
306 -- PROCEDURE validate_policy_term
307 --------------------------
308 PROCEDURE validate_policy_term (x_return_status OUT NOCOPY VARCHAR2, p_policy_term IN NUMBER) IS
309 BEGIN
310 IF p_policy_term IS NULL THEN
311 OKL_API.set_message(p_app_name => G_APP_NAME,
312 p_msg_name => G_COL_ERROR,
313 p_token1 => G_COL_NAME_TOKEN,
314 p_token1_value => 'policy_term',
315 p_token2 => G_PKG_NAME_TOKEN,
316 p_token2_value => G_PKG_NAME);
317 RAISE OKL_API.G_EXCEPTION_ERROR;
318 END IF;
319 x_return_status := G_RET_STS_SUCCESS;
320 END validate_policy_term;
321
322
323 -------------------------------
324 -- FUNCTION validate_attributes
325 -------------------------------
326 FUNCTION validate_attributes (p_quev_rec IN quev_rec_type) RETURN VARCHAR2 IS
327
328 l_return_status VARCHAR2(1);
329
330 BEGIN
331
332 validate_id (l_return_status, p_quev_rec.id);
333 validate_object_version_number (l_return_status, p_quev_rec.object_version_number);
334 validate_lease_quote_id (l_return_status, p_quev_rec.lease_quote_id);
335 validate_policy_term (l_return_status, p_quev_rec.policy_term);
336
337 RETURN l_return_status;
338
339 END validate_attributes;
340
341 ----------------------------
342 -- PROCEDURE validate_record
343 ----------------------------
344 FUNCTION validate_record (p_quev_rec IN quev_rec_type) RETURN VARCHAR2 IS
345 l_return_status VARCHAR2(1);
346 BEGIN
347 RETURN G_RET_STS_SUCCESS;
351 -----------------------------
348 END validate_record;
349
350
352 -- PROECDURE migrate (V -> B)
353 -----------------------------
354 PROCEDURE migrate (p_from IN quev_rec_type, p_to IN OUT NOCOPY que_rec_type) IS
355
356 BEGIN
357
358 p_to.id := p_from.id;
359 p_to.object_version_number := p_from.object_version_number;
360 p_to.attribute_category := p_from.attribute_category;
361 p_to.attribute1 := p_from.attribute1;
362 p_to.attribute2 := p_from.attribute2;
363 p_to.attribute3 := p_from.attribute3;
364 p_to.attribute4 := p_from.attribute4;
365 p_to.attribute5 := p_from.attribute5;
366 p_to.attribute6 := p_from.attribute6;
367 p_to.attribute7 := p_from.attribute7;
368 p_to.attribute8 := p_from.attribute8;
369 p_to.attribute9 := p_from.attribute9;
370 p_to.attribute10 := p_from.attribute10;
371 p_to.attribute11 := p_from.attribute11;
372 p_to.attribute12 := p_from.attribute12;
373 p_to.attribute13 := p_from.attribute13;
374 p_to.attribute14 := p_from.attribute14;
375 p_to.attribute15 := p_from.attribute15;
376 p_to.lease_quote_id := p_from.lease_quote_id;
377 p_to.policy_term := p_from.policy_term;
378
379 END migrate;
380
381
382 -----------------------------
383 -- PROCEDURE migrate (V -> TL)
384 -----------------------------
385 PROCEDURE migrate (p_from IN quev_rec_type, p_to IN OUT NOCOPY quetl_rec_type) IS
386 BEGIN
387 p_to.id := p_from.id;
388 p_to.short_description := p_from.short_description;
389 p_to.description := p_from.description;
390 p_to.comments := p_from.comments;
391 END migrate;
392
393
394 ---------------------------
395 -- PROCEDURE insert_row (B)
396 ---------------------------
397 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_que_rec IN que_rec_type) IS
398
399 l_prog_name VARCHAR2(61);
400
401 BEGIN
402
403 l_prog_name := G_PKG_NAME||'.insert_row (B)';
404
405 INSERT INTO okl_insurance_estimates_b (
406 id
407 ,object_version_number
408 ,attribute_category
409 ,attribute1
410 ,attribute2
411 ,attribute3
412 ,attribute4
413 ,attribute5
414 ,attribute6
415 ,attribute7
416 ,attribute8
417 ,attribute9
418 ,attribute10
419 ,attribute11
420 ,attribute12
421 ,attribute13
422 ,attribute14
423 ,attribute15
424 ,created_by
425 ,creation_date
426 ,last_updated_by
427 ,last_update_date
428 ,last_update_login
429 ,lease_quote_id
430 ,policy_term
431 )
432 VALUES
433 (
434 p_que_rec.id
435 ,p_que_rec.object_version_number
436 ,p_que_rec.attribute_category
437 ,p_que_rec.attribute1
438 ,p_que_rec.attribute2
439 ,p_que_rec.attribute3
440 ,p_que_rec.attribute4
441 ,p_que_rec.attribute5
442 ,p_que_rec.attribute6
443 ,p_que_rec.attribute7
444 ,p_que_rec.attribute8
445 ,p_que_rec.attribute9
446 ,p_que_rec.attribute10
447 ,p_que_rec.attribute11
448 ,p_que_rec.attribute12
449 ,p_que_rec.attribute13
450 ,p_que_rec.attribute14
451 ,p_que_rec.attribute15
452 ,G_USER_ID
453 ,SYSDATE
454 ,G_USER_ID
455 ,SYSDATE
456 ,G_LOGIN_ID
457 ,p_que_rec.lease_quote_id
458 ,p_que_rec.policy_term
459 );
460
461 x_return_status := G_RET_STS_SUCCESS;
462
463 EXCEPTION
464
465 WHEN OTHERS THEN
466
467 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
468 p_msg_name => G_DB_ERROR,
469 p_token1 => G_PROG_NAME_TOKEN,
470 p_token1_value => l_prog_name,
471 p_token2 => G_SQLCODE_TOKEN,
472 p_token2_value => sqlcode,
473 p_token3 => G_SQLERRM_TOKEN,
474 p_token3_value => sqlerrm);
475
476 x_return_status := G_RET_STS_UNEXP_ERROR;
477
478 END insert_row;
479
480
481 ----------------------------
482 -- PROCEDURE insert_row (TL)
483 ----------------------------
484 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_quetl_rec IN quetl_rec_type) IS
485
486 CURSOR get_languages IS
487 SELECT language_code
488 FROM FND_LANGUAGES
489 WHERE INSTALLED_FLAG IN ('I', 'B');
490
491 l_sfwt_flag VARCHAR2(1);
492
493 l_prog_name VARCHAR2(61);
494
495 BEGIN
496
497 l_prog_name := G_PKG_NAME||'.insert_row (TL)';
498
499 FOR l_lang_rec IN get_languages LOOP
500
501 IF l_lang_rec.language_code = USERENV('LANG') THEN
502 l_sfwt_flag := 'N';
503 ELSE
504 l_sfwt_flag := 'Y';
505 END IF;
506
507 INSERT INTO OKL_INSURANCE_ESTIMATES_TL (
508 id
509 ,language
510 ,source_lang
511 ,sfwt_flag
512 ,created_by
513 ,creation_date
517 ,short_description
514 ,last_updated_by
515 ,last_update_date
516 ,last_update_login
518 ,description
519 ,comments)
520 VALUES (
521 p_quetl_rec.id
522 ,l_lang_rec.language_code
523 ,USERENV('LANG')
524 ,l_sfwt_flag
525 ,G_USER_ID
526 ,SYSDATE
527 ,G_USER_ID
528 ,SYSDATE
529 ,G_LOGIN_ID
530 ,p_quetl_rec.short_description
531 ,p_quetl_rec.description
532 ,p_quetl_rec.comments);
533
534 END LOOP;
535
536 x_return_status := G_RET_STS_SUCCESS;
537
538 EXCEPTION
539
540 WHEN OTHERS THEN
541
542 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
543 p_msg_name => G_DB_ERROR,
544 p_token1 => G_PROG_NAME_TOKEN,
545 p_token1_value => l_prog_name,
546 p_token2 => G_SQLCODE_TOKEN,
547 p_token2_value => sqlcode,
548 p_token3 => G_SQLERRM_TOKEN,
549 p_token3_value => sqlerrm);
550
551 x_return_status := G_RET_STS_UNEXP_ERROR;
552
553 END insert_row;
554
555
556 ---------------------------
557 -- PROCEDURE insert_row (V)
558 ---------------------------
559 PROCEDURE insert_row (
560 x_return_status OUT NOCOPY VARCHAR2,
561 p_quev_rec IN quev_rec_type,
562 x_quev_rec OUT NOCOPY quev_rec_type) IS
563
564 l_return_status VARCHAR2(1);
565
566 l_quev_rec quev_rec_type;
567 l_que_rec que_rec_type;
568 l_quetl_rec quetl_rec_type;
569
570 l_prog_name VARCHAR2(61);
571
572 BEGIN
573
574 l_prog_name := G_PKG_NAME||'.insert_row (V)';
575
576 l_quev_rec := null_out_defaults (p_quev_rec);
577
578 SELECT okl_que_seq.nextval INTO l_quev_rec.ID FROM DUAL;
579
580 l_quev_rec.OBJECT_VERSION_NUMBER := 1;
581
582 l_return_status := validate_attributes(l_quev_rec);
583
584 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
585 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
586 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
587 RAISE OKL_API.G_EXCEPTION_ERROR;
588 END IF;
589
590 l_return_status := validate_record(l_quev_rec);
591
592 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
593 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
594 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
595 RAISE OKL_API.G_EXCEPTION_ERROR;
596 END IF;
597
598 migrate (l_quev_rec, l_que_rec);
599 migrate (l_quev_rec, l_quetl_rec);
600
601 insert_row (x_return_status => l_return_status, p_que_rec => l_que_rec);
602
603 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
604 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
605 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
606 RAISE OKL_API.G_EXCEPTION_ERROR;
607 END IF;
608
609 insert_row (x_return_status => l_return_status, p_quetl_rec => l_quetl_rec);
610
611 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
612 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
613 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
614 RAISE OKL_API.G_EXCEPTION_ERROR;
615 END IF;
616
617 x_quev_rec := l_quev_rec;
618 x_return_status := l_return_status;
619
620 EXCEPTION
621
622 WHEN OKL_API.G_EXCEPTION_ERROR THEN
623
624 x_return_status := G_RET_STS_ERROR;
625
626 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
627
628 x_return_status := G_RET_STS_UNEXP_ERROR;
629
630 WHEN OTHERS THEN
631
632 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
633 p_msg_name => G_DB_ERROR,
634 p_token1 => G_PROG_NAME_TOKEN,
635 p_token1_value => l_prog_name,
636 p_token2 => G_SQLCODE_TOKEN,
637 p_token2_value => sqlcode,
638 p_token3 => G_SQLERRM_TOKEN,
639 p_token3_value => sqlerrm);
640
641 x_return_status := G_RET_STS_UNEXP_ERROR;
642
643 END insert_row;
644
645
646 -----------------------------
647 -- PROCEDURE insert_row (REC)
648 -----------------------------
649 PROCEDURE insert_row(
650 p_api_version IN NUMBER,
651 p_init_msg_list IN VARCHAR2,
652 x_return_status OUT NOCOPY VARCHAR2,
653 x_msg_count OUT NOCOPY NUMBER,
654 x_msg_data OUT NOCOPY VARCHAR2,
655 p_quev_rec IN quev_rec_type,
656 x_quev_rec OUT NOCOPY quev_rec_type) IS
657
658 l_return_status VARCHAR2(1);
659
660 l_prog_name VARCHAR2(61);
661
662 BEGIN
663
664 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
665
666 IF p_init_msg_list = G_TRUE THEN
667 FND_MSG_PUB.initialize;
668 END IF;
669
670 insert_row (x_return_status => l_return_status,
671 p_quev_rec => p_quev_rec,
672 x_quev_rec => x_quev_rec);
673
674 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
675 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
679
676 ELSIF l_return_status = G_RET_STS_ERROR THEN
677 RAISE OKL_API.G_EXCEPTION_ERROR;
678 END IF;
680 x_return_status := l_return_status;
681
682 EXCEPTION
683
684 WHEN OKL_API.G_EXCEPTION_ERROR THEN
685
686 x_return_status := G_RET_STS_ERROR;
687
688 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
689
690 x_return_status := G_RET_STS_UNEXP_ERROR;
691
692 WHEN OTHERS THEN
693
694 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
695 p_msg_name => G_DB_ERROR,
696 p_token1 => G_PROG_NAME_TOKEN,
697 p_token1_value => l_prog_name,
698 p_token2 => G_SQLCODE_TOKEN,
699 p_token2_value => sqlcode,
700 p_token3 => G_SQLERRM_TOKEN,
701 p_token3_value => sqlerrm);
702
703 x_return_status := G_RET_STS_UNEXP_ERROR;
704
705 END insert_row;
706
707
708 -----------------------------
709 -- PROCEDURE insert_row (TBL)
710 -----------------------------
711 PROCEDURE insert_row(
712 p_api_version IN NUMBER,
713 p_init_msg_list IN VARCHAR2,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_msg_count OUT NOCOPY NUMBER,
716 x_msg_data OUT NOCOPY VARCHAR2,
717 p_quev_tbl IN quev_tbl_type,
718 x_quev_tbl OUT NOCOPY quev_tbl_type) IS
719
720 l_return_status VARCHAR2(1);
721 i BINARY_INTEGER;
722
723 l_prog_name VARCHAR2(61);
724
725 BEGIN
726
727 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
728
729 IF p_init_msg_list = G_TRUE THEN
730 FND_MSG_PUB.initialize;
731 END IF;
732
733 IF (p_quev_tbl.COUNT > 0) THEN
734 i := p_quev_tbl.FIRST;
735 LOOP
736 IF p_quev_tbl.EXISTS(i) THEN
737
738 insert_row (x_return_status => l_return_status,
739 p_quev_rec => p_quev_tbl(i),
740 x_quev_rec => x_quev_tbl(i));
741
742 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
743 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
744 ELSIF l_return_status = G_RET_STS_ERROR THEN
745 RAISE OKL_API.G_EXCEPTION_ERROR;
746 END IF;
747
748 EXIT WHEN (i = p_quev_tbl.LAST);
749 i := p_quev_tbl.NEXT(i);
750
751 END IF;
752
753 END LOOP;
754
755 ELSE
756
757 l_return_status := G_RET_STS_SUCCESS;
758
759 END IF;
760
761 x_return_status := l_return_status;
762
763 EXCEPTION
764
765 WHEN OKL_API.G_EXCEPTION_ERROR THEN
766
767 x_return_status := G_RET_STS_ERROR;
768
769 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
770
771 x_return_status := G_RET_STS_UNEXP_ERROR;
772
773 WHEN OTHERS THEN
774
775 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
776 p_msg_name => G_DB_ERROR,
777 p_token1 => G_PROG_NAME_TOKEN,
778 p_token1_value => l_prog_name,
779 p_token2 => G_SQLCODE_TOKEN,
780 p_token2_value => sqlcode,
781 p_token3 => G_SQLERRM_TOKEN,
782 p_token3_value => sqlerrm);
783
784 x_return_status := G_RET_STS_UNEXP_ERROR;
785
786 END insert_row;
787
788
789 ---------------------
790 -- PROCEDURE lock_row
791 ---------------------
792 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_que_rec IN que_rec_type) IS
793
794 E_Resource_Busy EXCEPTION;
795
796 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
797
798 CURSOR lock_csr IS
799 SELECT OBJECT_VERSION_NUMBER
800 FROM OKL_INSURANCE_ESTIMATES_B
801 WHERE ID = p_que_rec.id
802 AND OBJECT_VERSION_NUMBER = p_que_rec.object_version_number
803 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
804
805 CURSOR lchk_csr IS
806 SELECT OBJECT_VERSION_NUMBER
807 FROM OKL_INSURANCE_ESTIMATES_B
808 WHERE ID = p_que_rec.id;
809
810 l_object_version_number NUMBER;
811 lc_object_version_number NUMBER;
812
813 l_prog_name VARCHAR2(61);
814
815 BEGIN
816
817 l_prog_name := G_PKG_NAME||'.lock_row';
818
819 BEGIN
820 OPEN lock_csr;
821 FETCH lock_csr INTO l_object_version_number;
822 CLOSE lock_csr;
823 EXCEPTION
824 WHEN E_Resource_Busy THEN
825
826 IF (lock_csr%ISOPEN) THEN
827 CLOSE lock_csr;
828 END IF;
829 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
830 p_msg_name => G_OVN_ERROR2,
831 p_token1 => G_PROG_NAME_TOKEN,
832 p_token1_value => l_prog_name);
833 RAISE OKL_API.G_EXCEPTION_ERROR;
834 END;
835
836 IF l_object_version_number IS NULL THEN
837
838 OPEN lchk_csr;
839 FETCH lchk_csr INTO lc_object_version_number;
840 CLOSE lchk_csr;
841
842 IF lc_object_version_number IS NULL THEN
843
847 p_token1_value => l_prog_name);
844 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
845 p_msg_name => G_OVN_ERROR3,
846 p_token1 => G_PROG_NAME_TOKEN,
848
849 ELSIF lc_object_version_number <> p_que_rec.object_version_number THEN
850
851 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
852 p_msg_name => G_OVN_ERROR,
853 p_token1 => G_PROG_NAME_TOKEN,
854 p_token1_value => l_prog_name);
855
856 END IF;
857
858 RAISE OKL_API.G_EXCEPTION_ERROR;
859
860 END IF;
861
862 x_return_status := G_RET_STS_SUCCESS;
863
864 EXCEPTION
865
866 WHEN OKL_API.G_EXCEPTION_ERROR THEN
867
868 x_return_status := G_RET_STS_ERROR;
869
870 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
871
872 x_return_status := G_RET_STS_UNEXP_ERROR;
873
874 WHEN OTHERS THEN
875
876 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
877 p_msg_name => G_DB_ERROR,
878 p_token1 => G_PROG_NAME_TOKEN,
879 p_token1_value => l_prog_name,
880 p_token2 => G_SQLCODE_TOKEN,
881 p_token2_value => sqlcode,
882 p_token3 => G_SQLERRM_TOKEN,
883 p_token3_value => sqlerrm);
884
885 x_return_status := G_RET_STS_UNEXP_ERROR;
886
887 END lock_row;
888
889
890 ---------------------------
891 -- PROCEDURE update_row (B)
892 ---------------------------
893 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_que_rec IN que_rec_type) IS
894
895 l_return_status VARCHAR2(1);
896
897 l_prog_name VARCHAR2(61);
898
899 BEGIN
900
901 l_prog_name := G_PKG_NAME||'.update_row (B)';
902
903 lock_row (x_return_status => l_return_status, p_que_rec => p_que_rec);
904
905 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
906 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
907 ELSIF l_return_status = G_RET_STS_ERROR THEN
908 RAISE OKL_API.G_EXCEPTION_ERROR;
909 END IF;
910
911 UPDATE okl_insurance_estimates_b
912 SET
913 object_version_number = p_que_rec.object_version_number+1
914 ,attribute_category = p_que_rec.attribute_category
915 ,attribute1 = p_que_rec.attribute1
916 ,attribute2 = p_que_rec.attribute2
917 ,attribute3 = p_que_rec.attribute3
918 ,attribute4 = p_que_rec.attribute4
919 ,attribute5 = p_que_rec.attribute5
920 ,attribute6 = p_que_rec.attribute6
921 ,attribute7 = p_que_rec.attribute7
922 ,attribute8 = p_que_rec.attribute8
923 ,attribute9 = p_que_rec.attribute9
924 ,attribute10 = p_que_rec.attribute10
925 ,attribute11 = p_que_rec.attribute11
926 ,attribute12 = p_que_rec.attribute12
927 ,attribute13 = p_que_rec.attribute13
928 ,attribute14 = p_que_rec.attribute14
929 ,attribute15 = p_que_rec.attribute15
930 ,lease_quote_id = p_que_rec.lease_quote_id
931 ,policy_term = p_que_rec.policy_term
932 WHERE id = p_que_rec.id;
933
934 x_return_status := l_return_status;
935
936 EXCEPTION
937
938 WHEN OKL_API.G_EXCEPTION_ERROR THEN
939 x_return_status := G_RET_STS_ERROR;
940
941 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
942 x_return_status := G_RET_STS_UNEXP_ERROR;
943
944 WHEN OTHERS THEN
945 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
946 p_msg_name => G_DB_ERROR,
947 p_token1 => G_PROG_NAME_TOKEN,
948 p_token1_value => l_prog_name,
949 p_token2 => G_SQLCODE_TOKEN,
950 p_token2_value => sqlcode,
951 p_token3 => G_SQLERRM_TOKEN,
952 p_token3_value => sqlerrm);
953
954 x_return_status := G_RET_STS_UNEXP_ERROR;
955
956 END update_row;
957
958
959 ----------------------------
960 -- PROCEDURE update_row (TL)
961 ----------------------------
962 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_quetl_rec IN quetl_rec_type) IS
963
964 l_prog_name VARCHAR2(61);
965
966 BEGIN
967
968 l_prog_name := G_PKG_NAME||'.update_row (TL)';
969
970 UPDATE OKL_INSURANCE_ESTIMATES_TL
971 SET
972 source_lang = USERENV('LANG')
973 ,sfwt_flag = 'Y'
974 ,last_updated_by = G_USER_ID
975 ,last_update_date = SYSDATE
976 ,last_update_login = G_LOGIN_ID
977 ,short_description = p_quetl_rec.short_description
978 ,description = p_quetl_rec.description
979 ,comments = p_quetl_rec.comments
980 WHERE ID = p_quetl_rec.id;
981
982 UPDATE OKL_INSURANCE_ESTIMATES_TL
983 SET SFWT_FLAG = 'N'
984 WHERE ID = p_quetl_rec.id
985 AND SOURCE_LANG = LANGUAGE;
986
987 x_return_status := G_RET_STS_SUCCESS;
988
989 EXCEPTION
990
991 WHEN OTHERS THEN
992
993 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
994 p_msg_name => G_DB_ERROR,
995 p_token1 => G_PROG_NAME_TOKEN,
996 p_token1_value => l_prog_name,
997 p_token2 => G_SQLCODE_TOKEN,
1001
998 p_token2_value => sqlcode,
999 p_token3 => G_SQLERRM_TOKEN,
1000 p_token3_value => sqlerrm);
1002 x_return_status := G_RET_STS_UNEXP_ERROR;
1003
1004 END update_row;
1005
1006
1007 ---------------------------
1008 -- PROCEDURE update_row (V)
1009 ---------------------------
1010 PROCEDURE update_row (
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 p_quev_rec IN quev_rec_type,
1013 x_quev_rec OUT NOCOPY quev_rec_type) IS
1014
1015 l_prog_name VARCHAR2(61);
1016
1017 l_return_status VARCHAR2(1);
1018 l_quev_rec quev_rec_type;
1019 l_que_rec que_rec_type;
1020 l_quetl_rec quetl_rec_type;
1021
1022 ----------------------
1023 -- populate_new_record
1024 ----------------------
1025 FUNCTION populate_new_record (p_quev_rec IN quev_rec_type,
1026 x_quev_rec OUT NOCOPY quev_rec_type) RETURN VARCHAR2 IS
1027
1028 l_prog_name VARCHAR2(61);
1029 l_return_status VARCHAR2(1);
1030 l_db_quev_rec quev_rec_type;
1031
1032 BEGIN
1033
1034 l_prog_name := G_PKG_NAME||'.populate_new_record';
1035
1036 x_quev_rec := p_quev_rec;
1037 l_db_quev_rec := get_rec (p_quev_rec.id, l_return_status);
1038
1039 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1040 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1041 ELSIF l_return_status = G_RET_STS_ERROR THEN
1042 RAISE OKL_API.G_EXCEPTION_ERROR;
1043 END IF;
1044
1045 IF x_quev_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1046 x_quev_rec.attribute_category := l_db_quev_rec.attribute_category;
1047 END IF;
1048 IF x_quev_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1049 x_quev_rec.attribute1 := l_db_quev_rec.attribute1;
1050 END IF;
1051 IF x_quev_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1052 x_quev_rec.attribute2 := l_db_quev_rec.attribute2;
1053 END IF;
1054 IF x_quev_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1055 x_quev_rec.attribute3 := l_db_quev_rec.attribute3;
1056 END IF;
1057 IF x_quev_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1058 x_quev_rec.attribute4 := l_db_quev_rec.attribute4;
1059 END IF;
1060 IF x_quev_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1061 x_quev_rec.attribute5 := l_db_quev_rec.attribute5;
1062 END IF;
1063 IF x_quev_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1064 x_quev_rec.attribute6 := l_db_quev_rec.attribute6;
1065 END IF;
1066 IF x_quev_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1067 x_quev_rec.attribute7 := l_db_quev_rec.attribute7;
1068 END IF;
1069 IF x_quev_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1070 x_quev_rec.attribute8 := l_db_quev_rec.attribute8;
1071 END IF;
1072 IF x_quev_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1073 x_quev_rec.attribute9 := l_db_quev_rec.attribute9;
1074 END IF;
1075 IF x_quev_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1076 x_quev_rec.attribute10 := l_db_quev_rec.attribute10;
1077 END IF;
1078 IF x_quev_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1079 x_quev_rec.attribute11 := l_db_quev_rec.attribute11;
1080 END IF;
1081 IF x_quev_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1082 x_quev_rec.attribute12 := l_db_quev_rec.attribute12;
1083 END IF;
1084 IF x_quev_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1085 x_quev_rec.attribute13 := l_db_quev_rec.attribute13;
1086 END IF;
1087 IF x_quev_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1088 x_quev_rec.attribute14 := l_db_quev_rec.attribute14;
1089 END IF;
1090 IF x_quev_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1091 x_quev_rec.attribute15 := l_db_quev_rec.attribute15;
1092 END IF;
1093 IF x_quev_rec.lease_quote_id = FND_API.G_MISS_NUM THEN
1094 x_quev_rec.lease_quote_id := l_db_quev_rec.lease_quote_id;
1095 END IF;
1096 IF x_quev_rec.policy_term = FND_API.G_MISS_NUM THEN
1097 x_quev_rec.policy_term := l_db_quev_rec.policy_term;
1098 END IF;
1099 IF x_quev_rec.short_description = FND_API.G_MISS_CHAR THEN
1100 x_quev_rec.short_description := l_db_quev_rec.short_description;
1101 END IF;
1102 IF x_quev_rec.description = FND_API.G_MISS_CHAR THEN
1103 x_quev_rec.description := l_db_quev_rec.description;
1104 END IF;
1105 IF x_quev_rec.comments = FND_API.G_MISS_CHAR THEN
1106 x_quev_rec.comments := l_db_quev_rec.comments;
1107 END IF;
1108
1109 RETURN l_return_status;
1110
1111 EXCEPTION
1112
1113 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1114
1115 x_return_status := G_RET_STS_ERROR;
1116
1117 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1118
1119 x_return_status := G_RET_STS_UNEXP_ERROR;
1120
1121 WHEN OTHERS THEN
1122
1123 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1124 p_msg_name => G_DB_ERROR,
1125 p_token1 => G_PROG_NAME_TOKEN,
1126 p_token1_value => l_prog_name,
1127 p_token2 => G_SQLCODE_TOKEN,
1128 p_token2_value => sqlcode,
1129 p_token3 => G_SQLERRM_TOKEN,
1130 p_token3_value => sqlerrm);
1131
1132 x_return_status := G_RET_STS_UNEXP_ERROR;
1133
1134 END populate_new_record;
1135
1136 BEGIN
1137
1141
1138 l_prog_name := G_PKG_NAME||'.update_row (V)';
1139
1140 l_return_status := populate_new_record (p_quev_rec, l_quev_rec);
1142 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1143 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1144 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1145 RAISE OKL_API.G_EXCEPTION_ERROR;
1146 END IF;
1147
1148 l_return_status := validate_attributes (l_quev_rec);
1149
1150 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1151 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1152 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1153 RAISE OKL_API.G_EXCEPTION_ERROR;
1154 END IF;
1155
1156 l_return_status := validate_record (l_quev_rec);
1157
1158 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1159 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1160 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1161 RAISE OKL_API.G_EXCEPTION_ERROR;
1162 END IF;
1163
1164 migrate (l_quev_rec, l_que_rec);
1165 migrate (l_quev_rec, l_quetl_rec);
1166
1167 update_row (x_return_status => l_return_status, p_que_rec => l_que_rec);
1168
1169 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1170 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1171 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1172 RAISE OKL_API.G_EXCEPTION_ERROR;
1173 END IF;
1174
1175 update_row (x_return_status => l_return_status, p_quetl_rec => l_quetl_rec);
1176
1177 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1178 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1179 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1180 RAISE OKL_API.G_EXCEPTION_ERROR;
1181 END IF;
1182
1183 x_return_status := l_return_status;
1184 x_quev_rec := l_quev_rec;
1185
1186 EXCEPTION
1187
1188 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1189
1190 x_return_status := G_RET_STS_ERROR;
1191
1192 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1193
1194 x_return_status := G_RET_STS_UNEXP_ERROR;
1195
1196 WHEN OTHERS THEN
1197
1198 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1199 p_msg_name => G_DB_ERROR,
1200 p_token1 => G_PROG_NAME_TOKEN,
1201 p_token1_value => l_prog_name,
1202 p_token2 => G_SQLCODE_TOKEN,
1203 p_token2_value => sqlcode,
1204 p_token3 => G_SQLERRM_TOKEN,
1205 p_token3_value => sqlerrm);
1206
1207 x_return_status := G_RET_STS_UNEXP_ERROR;
1208
1209 END update_row;
1210
1211
1212 -----------------------------
1213 -- PROCEDURE update_row (REC)
1214 -----------------------------
1215 PROCEDURE update_row(
1216 p_api_version IN NUMBER,
1217 p_init_msg_list IN VARCHAR2,
1218 x_return_status OUT NOCOPY VARCHAR2,
1219 x_msg_count OUT NOCOPY NUMBER,
1220 x_msg_data OUT NOCOPY VARCHAR2,
1221 p_quev_rec IN quev_rec_type,
1222 x_quev_rec OUT NOCOPY quev_rec_type) IS
1223
1224 l_return_status VARCHAR2(1);
1225
1226 l_prog_name VARCHAR2(61);
1227
1228 BEGIN
1229
1230 l_prog_name := G_PKG_NAME||'.update_row (REC)';
1231
1232 IF p_init_msg_list = G_TRUE THEN
1233 FND_MSG_PUB.initialize;
1234 END IF;
1235
1236 update_row (x_return_status => l_return_status,
1237 p_quev_rec => p_quev_rec,
1238 x_quev_rec => x_quev_rec);
1239
1240 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1241 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1242 ELSIF l_return_status = G_RET_STS_ERROR THEN
1243 RAISE OKL_API.G_EXCEPTION_ERROR;
1244 END IF;
1245
1246 x_return_status := l_return_status;
1247
1248 EXCEPTION
1249
1250 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1251
1252 x_return_status := G_RET_STS_ERROR;
1253
1254 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1255
1256 x_return_status := G_RET_STS_UNEXP_ERROR;
1257
1258 WHEN OTHERS THEN
1259
1260 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1261 p_msg_name => G_DB_ERROR,
1262 p_token1 => G_PROG_NAME_TOKEN,
1263 p_token1_value => l_prog_name,
1264 p_token2 => G_SQLCODE_TOKEN,
1265 p_token2_value => sqlcode,
1266 p_token3 => G_SQLERRM_TOKEN,
1267 p_token3_value => sqlerrm);
1268
1269 x_return_status := G_RET_STS_UNEXP_ERROR;
1270
1271 END update_row;
1272
1273
1274 -----------------------------
1275 -- PROCEDURE update_row (TBL)
1276 -----------------------------
1277 PROCEDURE update_row(
1278 p_api_version IN NUMBER,
1279 p_init_msg_list IN VARCHAR2,
1280 x_return_status OUT NOCOPY VARCHAR2,
1281 x_msg_count OUT NOCOPY NUMBER,
1282 x_msg_data OUT NOCOPY VARCHAR2,
1283 p_quev_tbl IN quev_tbl_type,
1284 x_quev_tbl OUT NOCOPY quev_tbl_type) IS
1285
1286 l_return_status VARCHAR2(1);
1287 i BINARY_INTEGER;
1288 l_prog_name VARCHAR2(61);
1289
1290 BEGIN
1291
1295 FND_MSG_PUB.initialize;
1292 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
1293
1294 IF p_init_msg_list = G_TRUE THEN
1296 END IF;
1297
1298 x_quev_tbl := p_quev_tbl;
1299
1300 IF (p_quev_tbl.COUNT > 0) THEN
1301
1302 i := p_quev_tbl.FIRST;
1303
1304 LOOP
1305
1306 IF p_quev_tbl.EXISTS(i) THEN
1307 update_row (x_return_status => l_return_status,
1308 p_quev_rec => p_quev_tbl(i),
1309 x_quev_rec => x_quev_tbl(i));
1310
1311 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1312 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1313 ELSIF l_return_status = G_RET_STS_ERROR THEN
1314 RAISE OKL_API.G_EXCEPTION_ERROR;
1315 END IF;
1316
1317 EXIT WHEN (i = p_quev_tbl.LAST);
1318 i := p_quev_tbl.NEXT(i);
1319
1320 END IF;
1321
1322 END LOOP;
1323
1324 ELSE
1325
1326 l_return_status := G_RET_STS_SUCCESS;
1327
1328 END IF;
1329
1330 x_return_status := l_return_status;
1331
1332 EXCEPTION
1333
1334 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1335
1336 x_return_status := G_RET_STS_ERROR;
1337
1338 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1339
1340 x_return_status := G_RET_STS_UNEXP_ERROR;
1341
1342 WHEN OTHERS THEN
1343
1344 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1345 p_msg_name => G_DB_ERROR,
1346 p_token1 => G_PROG_NAME_TOKEN,
1347 p_token1_value => l_prog_name,
1348 p_token2 => G_SQLCODE_TOKEN,
1349 p_token2_value => sqlcode,
1350 p_token3 => G_SQLERRM_TOKEN,
1351 p_token3_value => sqlerrm);
1352
1353 x_return_status := G_RET_STS_UNEXP_ERROR;
1354
1355 END update_row;
1356
1357
1358 -----------------
1359 -- delete_row (V)
1360 -----------------
1361 PROCEDURE delete_row(
1362 x_return_status OUT NOCOPY VARCHAR2,
1363 p_id IN NUMBER) IS
1364
1365 l_prog_name VARCHAR2(61);
1366
1367 BEGIN
1368
1369 l_prog_name := G_PKG_NAME||'.delete_row (V)';
1370
1371 DELETE FROM OKL_INSURANCE_ESTIMATES_B WHERE id = p_id;
1372 DELETE FROM OKL_INSURANCE_ESTIMATES_TL WHERE id = p_id;
1373
1374 x_return_status := G_RET_STS_SUCCESS;
1375
1376 EXCEPTION
1377
1378 WHEN OTHERS THEN
1379
1380 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1381 p_msg_name => G_DB_ERROR,
1382 p_token1 => G_PROG_NAME_TOKEN,
1383 p_token1_value => l_prog_name,
1384 p_token2 => G_SQLCODE_TOKEN,
1385 p_token2_value => sqlcode,
1386 p_token3 => G_SQLERRM_TOKEN,
1387 p_token3_value => sqlerrm);
1388
1389 x_return_status := G_RET_STS_UNEXP_ERROR;
1390
1391 END delete_row;
1392
1393
1394 -----------------------------
1395 -- PROCEDURE delete_row (REC)
1396 -----------------------------
1397 PROCEDURE delete_row(
1398 p_api_version IN NUMBER,
1399 p_init_msg_list IN VARCHAR2,
1400 x_return_status OUT NOCOPY VARCHAR2,
1401 x_msg_count OUT NOCOPY NUMBER,
1402 x_msg_data OUT NOCOPY VARCHAR2,
1403 p_quev_rec IN quev_rec_type) IS
1404
1405 l_return_status VARCHAR2(1);
1406
1407 l_prog_name VARCHAR2(61);
1408
1409 BEGIN
1410
1411 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
1412
1413 IF p_init_msg_list = G_TRUE THEN
1414 FND_MSG_PUB.initialize;
1415 END IF;
1416
1417 delete_row (x_return_status => l_return_status,
1418 p_id => p_quev_rec.id);
1419
1420 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1421 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1422 ELSIF l_return_status = G_RET_STS_ERROR THEN
1423 RAISE OKL_API.G_EXCEPTION_ERROR;
1424 END IF;
1425
1426 x_return_status := l_return_status;
1427
1428 EXCEPTION
1429
1430 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1431
1432 x_return_status := G_RET_STS_ERROR;
1433
1434 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1435
1436 x_return_status := G_RET_STS_UNEXP_ERROR;
1437
1438 WHEN OTHERS THEN
1439
1440 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1441 p_msg_name => G_DB_ERROR,
1442 p_token1 => G_PROG_NAME_TOKEN,
1443 p_token1_value => l_prog_name,
1444 p_token2 => G_SQLCODE_TOKEN,
1445 p_token2_value => sqlcode,
1446 p_token3 => G_SQLERRM_TOKEN,
1447 p_token3_value => sqlerrm);
1448
1449 x_return_status := G_RET_STS_UNEXP_ERROR;
1450
1451 END delete_row;
1452
1453
1454 -------------------
1455 -- delete_row (TBL)
1456 -------------------
1457 PROCEDURE delete_row(
1458 p_api_version IN NUMBER,
1459 p_init_msg_list IN VARCHAR2,
1460 x_return_status OUT NOCOPY VARCHAR2,
1461 x_msg_count OUT NOCOPY NUMBER,
1462 x_msg_data OUT NOCOPY VARCHAR2,
1463 p_quev_tbl IN quev_tbl_type) IS
1464
1465 l_return_status VARCHAR2(1);
1466 i BINARY_INTEGER;
1467
1468 l_prog_name VARCHAR2(61);
1469
1470 BEGIN
1471
1472 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
1473
1474 IF p_init_msg_list = G_TRUE THEN
1475 FND_MSG_PUB.initialize;
1476 END IF;
1477
1478 IF (p_quev_tbl.COUNT > 0) THEN
1479
1480 i := p_quev_tbl.FIRST;
1481
1482 LOOP
1483
1484 IF p_quev_tbl.EXISTS(i) THEN
1485
1486 delete_row (x_return_status => l_return_status,
1487 p_id => p_quev_tbl(i).id);
1488
1489 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1490 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1491 ELSIF l_return_status = G_RET_STS_ERROR THEN
1492 RAISE OKL_API.G_EXCEPTION_ERROR;
1493 END IF;
1494
1495 EXIT WHEN (i = p_quev_tbl.LAST);
1496 i := p_quev_tbl.NEXT(i);
1497
1498 END IF;
1499
1500 END LOOP;
1501
1502 ELSE
1503
1504 l_return_status := G_RET_STS_SUCCESS;
1505
1506 END IF;
1507
1508 x_return_status := l_return_status;
1509
1510 EXCEPTION
1511
1512 WHEN OTHERS THEN
1513
1514 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1515 p_msg_name => G_DB_ERROR,
1516 p_token1 => G_PROG_NAME_TOKEN,
1517 p_token1_value => l_prog_name,
1518 p_token2 => G_SQLCODE_TOKEN,
1519 p_token2_value => sqlcode,
1520 p_token3 => G_SQLERRM_TOKEN,
1521 p_token3_value => sqlerrm);
1522
1523 x_return_status := G_RET_STS_UNEXP_ERROR;
1524
1525 END delete_row;
1526
1527
1528 END OKL_QUE_PVT;