[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_INS_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_INS_PVT AS
2 /* $Header: OKLRQUIB.pls 120.3.12020000.2 2012/08/07 14:04:49 rpillay ship $ */
3
4 -----------------------------------------
5 -- PROCEDURE get_insurance_estimate_rec
6 --Added by jjuneja
7 ----------------------------------------
8
9 PROCEDURE get_insurance_estimate_rec(
10 p_insurance_estimate_id IN NUMBER
11 ,x_l_ins_est_rec OUT NOCOPY okl_que_pvt.quev_rec_type
12 ,x_return_status OUT NOCOPY VARCHAR2
13 )IS
14
15 l_program_name CONSTANT VARCHAR2(30) := 'get_insurance_estimate_rec';
16 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
17
18 BEGIN
19
20
21 SELECT
22 object_version_number
23 ,attribute_category
24 ,attribute1
25 ,attribute2
26 ,attribute3
27 ,attribute4
28 ,attribute5
29 ,attribute6
30 ,attribute7
31 ,attribute8
32 ,attribute9
33 ,attribute10
34 ,attribute11
35 ,attribute12
36 ,attribute13
37 ,attribute14
38 ,attribute15
39 ,lease_quote_id
40 ,policy_term
41 ,short_description
42 ,description
43 ,comments
44 INTO
45 x_l_ins_est_rec.object_version_number
46 ,x_l_ins_est_rec.attribute_category
47 ,x_l_ins_est_rec.attribute1
48 ,x_l_ins_est_rec.attribute2
49 ,x_l_ins_est_rec.attribute3
50 ,x_l_ins_est_rec.attribute4
51 ,x_l_ins_est_rec.attribute5
52 ,x_l_ins_est_rec.attribute6
53 ,x_l_ins_est_rec.attribute7
54 ,x_l_ins_est_rec.attribute8
55 ,x_l_ins_est_rec.attribute9
56 ,x_l_ins_est_rec.attribute10
57 ,x_l_ins_est_rec.attribute11
58 ,x_l_ins_est_rec.attribute12
59 ,x_l_ins_est_rec.attribute13
60 ,x_l_ins_est_rec.attribute14
61 ,x_l_ins_est_rec.attribute15
62 ,x_l_ins_est_rec.lease_quote_id
63 ,x_l_ins_est_rec.policy_term
64 ,x_l_ins_est_rec.short_description
65 ,x_l_ins_est_rec.description
66 ,x_l_ins_est_rec.comments
67 FROM OKL_INSURANCE_ESTIMATES_V
68 WHERE id = p_insurance_estimate_id;
69 x_return_status := G_RET_STS_SUCCESS;
70
71 EXCEPTION
72
73 WHEN OKL_API.G_EXCEPTION_ERROR THEN
74 x_return_status := G_RET_STS_ERROR;
75
76 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
77 x_return_status := G_RET_STS_UNEXP_ERROR;
78
79 WHEN OTHERS THEN
80 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
81 p_msg_name => G_DB_ERROR,
82 p_token1 => G_PROG_NAME_TOKEN,
83 p_token1_value => l_program_name,
84 p_token2 => G_SQLCODE_TOKEN,
85 p_token2_value => sqlcode,
86 p_token3 => G_SQLERRM_TOKEN,
87 p_token3_value => sqlerrm);
88
89 x_return_status := G_RET_STS_UNEXP_ERROR;
90
91 END get_insurance_estimate_rec;
92
93 --End of addition by jjuneja
94
95
96 ----------------------------
97 -- PROCEDURE validate_record
98 ----------------------------
99 PROCEDURE validate_record (
100 p_insurance_estimate_rec IN ins_est_rec_type
101 ,x_return_status OUT NOCOPY VARCHAR2
102 ) IS
103
104 l_program_name CONSTANT VARCHAR2(30) := 'validate_record';
105 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
106
107 l_mpp BINARY_INTEGER;
108
109 BEGIN
110
111 IF p_insurance_estimate_rec.policy_term <= 0 THEN
112 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_PERIOD_ZERO');
113 RAISE OKL_API.G_EXCEPTION_ERROR;
114 END IF;
115
116 IF TRUNC(p_insurance_estimate_rec.policy_term) <> p_insurance_estimate_rec.policy_term THEN
117 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_PERIOD_FRACTION');
118 RAISE OKL_API.G_EXCEPTION_ERROR;
119 END IF;
120
121 IF p_insurance_estimate_rec.periodic_amount < 0 THEN
122 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_AMOUNT_ZERO');
123 RAISE OKL_API.G_EXCEPTION_ERROR;
124 END IF;
125
126 IF p_insurance_estimate_rec.payment_frequency = 'A' THEN
127 l_mpp := 12;
128 ELSIF p_insurance_estimate_rec.payment_frequency = 'S' THEN
129 l_mpp := 6;
130 ELSIF p_insurance_estimate_rec.payment_frequency = 'Q' THEN
131 l_mpp := 3;
132 ELSIF p_insurance_estimate_rec.payment_frequency = 'M' THEN
133 l_mpp := 1;
134 END IF;
135
136 IF (p_insurance_estimate_rec.policy_term / l_mpp) <> TRUNC (p_insurance_estimate_rec.policy_term / l_mpp) THEN
137 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_TERM_FREQ_MISMATCH');
138 RAISE OKL_API.G_EXCEPTION_ERROR;
139 END IF;
140
141 x_return_status := G_RET_STS_SUCCESS;
142
143 EXCEPTION
144
145 WHEN OKL_API.G_EXCEPTION_ERROR THEN
146 x_return_status := G_RET_STS_ERROR;
147
148 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
149 x_return_status := G_RET_STS_UNEXP_ERROR;
150
151 WHEN OTHERS THEN
152 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
153 p_msg_name => G_DB_ERROR,
154 p_token1 => G_PROG_NAME_TOKEN,
155 p_token1_value => l_program_name,
156 p_token2 => G_SQLCODE_TOKEN,
157 p_token2_value => sqlcode,
158 p_token3 => G_SQLERRM_TOKEN,
159 p_token3_value => sqlerrm);
160
161 x_return_status := G_RET_STS_UNEXP_ERROR;
162
163 END validate_record;
164
165
166 -----------------------
167 -- PROCEDURE insert_row
168 -----------------------
169 PROCEDURE insert_row (
170 p_insurance_estimate_rec IN OUT NOCOPY ins_est_rec_type
171 ,x_return_status OUT NOCOPY VARCHAR2
172 ,x_msg_count OUT NOCOPY NUMBER
173 ,x_msg_data OUT NOCOPY VARCHAR2
174 ) IS
175
176 l_program_name CONSTANT VARCHAR2(30) := 'insert_row';
177 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
178
179 l_quev_rec okl_que_pvt.quev_rec_type;
180 lx_quev_rec okl_que_pvt.quev_rec_type;
181
182 BEGIN
183
184 l_quev_rec.lease_quote_id := p_insurance_estimate_rec.lease_quote_id;
185 l_quev_rec.policy_term := p_insurance_estimate_rec.policy_term;
186 l_quev_rec.description := p_insurance_estimate_rec.description;
187 --Bug#6935907 -Added by kkorrapo
188 l_quev_rec.attribute_category := p_insurance_estimate_rec.attribute_category;
189 l_quev_rec.attribute1 := p_insurance_estimate_rec.attribute1;
190 l_quev_rec.attribute2 := p_insurance_estimate_rec.attribute2;
191 l_quev_rec.attribute3 := p_insurance_estimate_rec.attribute3;
192 l_quev_rec.attribute4 := p_insurance_estimate_rec.attribute4;
193 l_quev_rec.attribute5 := p_insurance_estimate_rec.attribute5;
194 l_quev_rec.attribute6 := p_insurance_estimate_rec.attribute6;
195 l_quev_rec.attribute7 := p_insurance_estimate_rec.attribute7;
196 l_quev_rec.attribute8 := p_insurance_estimate_rec.attribute8;
197 l_quev_rec.attribute9 := p_insurance_estimate_rec.attribute9;
198 l_quev_rec.attribute10 := p_insurance_estimate_rec.attribute10;
199 l_quev_rec.attribute11 := p_insurance_estimate_rec.attribute11;
200 l_quev_rec.attribute12 := p_insurance_estimate_rec.attribute12;
201 l_quev_rec.attribute13 := p_insurance_estimate_rec.attribute13;
202 l_quev_rec.attribute14 := p_insurance_estimate_rec.attribute14;
203 l_quev_rec.attribute15 := p_insurance_estimate_rec.attribute15;
204 --Bug#6935907 -Addition end
205
206 okl_que_pvt.insert_row (
207 p_api_version => G_API_VERSION
208 ,p_init_msg_list => G_FALSE
209 ,x_return_status => x_return_status
210 ,x_msg_count => x_msg_count
211 ,x_msg_data => x_msg_data
212 ,p_quev_rec => l_quev_rec
213 ,x_quev_rec => lx_quev_rec
214 );
215
216 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
217 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
218 ELSIF x_return_status = G_RET_STS_ERROR THEN
219 RAISE OKL_API.G_EXCEPTION_ERROR;
220 END IF;
221
222 p_insurance_estimate_rec.id := lx_quev_rec.id;
223
224 EXCEPTION
225
226 WHEN OKL_API.G_EXCEPTION_ERROR THEN
227 x_return_status := G_RET_STS_ERROR;
228
229 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
230 x_return_status := G_RET_STS_UNEXP_ERROR;
231
232 WHEN OTHERS THEN
233 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
234 p_msg_name => G_DB_ERROR,
235 p_token1 => G_PROG_NAME_TOKEN,
236 p_token1_value => l_program_name,
237 p_token2 => G_SQLCODE_TOKEN,
238 p_token2_value => sqlcode,
239 p_token3 => G_SQLERRM_TOKEN,
240 p_token3_value => sqlerrm);
241
242 x_return_status := G_RET_STS_UNEXP_ERROR;
243
244 END insert_row;
245
246
247 -----------------------
248 -- PROCEDURE update_row
249 -----------------------
250 PROCEDURE update_row (
251 p_insurance_estimate_rec IN ins_est_rec_type
252 ,x_return_status OUT NOCOPY VARCHAR2
253 ,x_msg_count OUT NOCOPY NUMBER
254 ,x_msg_data OUT NOCOPY VARCHAR2
255 ) IS
256
257 l_program_name CONSTANT VARCHAR2(30) := 'update_row';
258 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
259
260 l_quev_rec okl_que_pvt.quev_rec_type;
261 lx_quev_rec okl_que_pvt.quev_rec_type;
262
263 BEGIN
264
265 l_quev_rec.id := p_insurance_estimate_rec.id;
266 l_quev_rec.object_version_number := p_insurance_estimate_rec.ovn;
267 l_quev_rec.lease_quote_id := p_insurance_estimate_rec.lease_quote_id;
268 l_quev_rec.policy_term := p_insurance_estimate_rec.policy_term;
269 l_quev_rec.description := p_insurance_estimate_rec.description;
270 --Bug#6935907 -Added by kkorrapo
271 l_quev_rec.attribute_category := p_insurance_estimate_rec.attribute_category;
272 l_quev_rec.attribute1 := p_insurance_estimate_rec.attribute1;
273 l_quev_rec.attribute2 := p_insurance_estimate_rec.attribute2;
274 l_quev_rec.attribute3 := p_insurance_estimate_rec.attribute3;
275 l_quev_rec.attribute4 := p_insurance_estimate_rec.attribute4;
276 l_quev_rec.attribute5 := p_insurance_estimate_rec.attribute5;
277 l_quev_rec.attribute6 := p_insurance_estimate_rec.attribute6;
278 l_quev_rec.attribute7 := p_insurance_estimate_rec.attribute7;
279 l_quev_rec.attribute8 := p_insurance_estimate_rec.attribute8;
280 l_quev_rec.attribute9 := p_insurance_estimate_rec.attribute9;
281 l_quev_rec.attribute10 := p_insurance_estimate_rec.attribute10;
282 l_quev_rec.attribute11 := p_insurance_estimate_rec.attribute11;
283 l_quev_rec.attribute12 := p_insurance_estimate_rec.attribute12;
284 l_quev_rec.attribute13 := p_insurance_estimate_rec.attribute13;
285 l_quev_rec.attribute14 := p_insurance_estimate_rec.attribute14;
286 l_quev_rec.attribute15 := p_insurance_estimate_rec.attribute15;
287 --Bug#6935907 -Addition end
288
289 okl_que_pvt.update_row (
290 p_api_version => G_API_VERSION
291 ,p_init_msg_list => G_FALSE
292 ,x_return_status => x_return_status
293 ,x_msg_count => x_msg_count
294 ,x_msg_data => x_msg_data
295 ,p_quev_rec => l_quev_rec
296 ,x_quev_rec => lx_quev_rec
297 );
298
299 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
300 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
301 ELSIF x_return_status = G_RET_STS_ERROR THEN
302 RAISE OKL_API.G_EXCEPTION_ERROR;
303 END IF;
304
305 EXCEPTION
306
307 WHEN OKL_API.G_EXCEPTION_ERROR THEN
308 x_return_status := G_RET_STS_ERROR;
309
310 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
311 x_return_status := G_RET_STS_UNEXP_ERROR;
312
313 WHEN OTHERS THEN
314 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
315 p_msg_name => G_DB_ERROR,
316 p_token1 => G_PROG_NAME_TOKEN,
317 p_token1_value => l_program_name,
318 p_token2 => G_SQLCODE_TOKEN,
319 p_token2_value => sqlcode,
320 p_token3 => G_SQLERRM_TOKEN,
321 p_token3_value => sqlerrm);
322
323 x_return_status := G_RET_STS_UNEXP_ERROR;
324
325 END update_row;
326
327
328 ----------------------------
329 -- PROCEDURE create_cashflow
330 ----------------------------
331 PROCEDURE create_cashflow (
332 p_insurance_estimate_rec IN ins_est_rec_type
333 ,x_return_status OUT NOCOPY VARCHAR2
334 ,x_msg_count OUT NOCOPY NUMBER
335 ,x_msg_data OUT NOCOPY VARCHAR2
336 ) IS
337
338 l_program_name CONSTANT VARCHAR2(30) := 'create_cashflow';
339 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
340
341 l_cashflow_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
342 l_cashflow_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
343
344 l_mpp BINARY_INTEGER;
345
346 BEGIN
347
348 l_cashflow_header_rec.type_code := 'INFLOW';
349 l_cashflow_header_rec.stream_type_id := p_insurance_estimate_rec.stream_type_id;
350 l_cashflow_header_rec.arrears_flag := 'N';
351 l_cashflow_header_rec.frequency_code := p_insurance_estimate_rec.payment_frequency;
352 l_cashflow_header_rec.parent_object_code := 'QUOTED_INSURANCE';
353 l_cashflow_header_rec.parent_object_id := p_insurance_estimate_rec.id;
354 l_cashflow_header_rec.quote_type_code := p_insurance_estimate_rec.quote_type_code;
355 l_cashflow_header_rec.quote_id := p_insurance_estimate_rec.lease_quote_id;
356
357 IF p_insurance_estimate_rec.payment_frequency = 'A' THEN
358 l_mpp := 12;
359 ELSIF p_insurance_estimate_rec.payment_frequency = 'S' THEN
360 l_mpp := 6;
361 ELSIF p_insurance_estimate_rec.payment_frequency = 'Q' THEN
362 l_mpp := 3;
363 ELSIF p_insurance_estimate_rec.payment_frequency = 'M' THEN
364 l_mpp := 1;
365 END IF;
366
367 l_cashflow_level_tbl(1).periods := p_insurance_estimate_rec.policy_term / l_mpp;
368 l_cashflow_level_tbl(1).periodic_amount := p_insurance_estimate_rec.periodic_amount;
369 l_cashflow_level_tbl(1).record_mode := 'CREATE';
370
371 okl_lease_quote_cashflow_pvt.create_cashflow (
372 p_api_version => G_API_VERSION
373 ,p_init_msg_list => G_FALSE
374 ,p_transaction_control => G_FALSE
375 ,p_cashflow_header_rec => l_cashflow_header_rec
376 ,p_cashflow_level_tbl => l_cashflow_level_tbl
377 ,x_return_status => x_return_status
378 ,x_msg_count => x_msg_count
379 ,x_msg_data => x_msg_data
380 );
381
382 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
383 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
384 ELSIF x_return_status = G_RET_STS_ERROR THEN
385 RAISE OKL_API.G_EXCEPTION_ERROR;
386 END IF;
387
388 x_return_status := G_RET_STS_SUCCESS;
389
390 EXCEPTION
391
392 WHEN OKL_API.G_EXCEPTION_ERROR THEN
393 x_return_status := G_RET_STS_ERROR;
394
395 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
396 x_return_status := G_RET_STS_UNEXP_ERROR;
397
398 WHEN OTHERS THEN
399 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
400 p_msg_name => G_DB_ERROR,
401 p_token1 => G_PROG_NAME_TOKEN,
402 p_token1_value => l_program_name,
403 p_token2 => G_SQLCODE_TOKEN,
404 p_token2_value => sqlcode,
405 p_token3 => G_SQLERRM_TOKEN,
406 p_token3_value => sqlerrm);
407
408 x_return_status := G_RET_STS_UNEXP_ERROR;
409
410 END create_cashflow;
411
412
413 ----------------------------
414 -- PROCEDURE update_cashflow
415 ----------------------------
416 PROCEDURE update_cashflow (
417 p_insurance_estimate_rec IN ins_est_rec_type
418 ,x_return_status OUT NOCOPY VARCHAR2
419 ,x_msg_count OUT NOCOPY NUMBER
420 ,x_msg_data OUT NOCOPY VARCHAR2
421 ) IS
422
423 l_program_name CONSTANT VARCHAR2(30) := 'update_cashflow';
424 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
425
426 l_cashflow_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
427 l_cashflow_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
428
429 l_mpp BINARY_INTEGER;
430
431 BEGIN
432
433 l_cashflow_header_rec.type_code := 'INFLOW';
434 l_cashflow_header_rec.stream_type_id := p_insurance_estimate_rec.stream_type_id;
435 l_cashflow_header_rec.arrears_flag := 'N';
436 l_cashflow_header_rec.frequency_code := p_insurance_estimate_rec.payment_frequency;
437 l_cashflow_header_rec.parent_object_code := 'QUOTED_INSURANCE';
438 l_cashflow_header_rec.parent_object_id := p_insurance_estimate_rec.id;
439 l_cashflow_header_rec.quote_type_code := p_insurance_estimate_rec.quote_type_code;
440 l_cashflow_header_rec.quote_id := p_insurance_estimate_rec.lease_quote_id;
441 l_cashflow_header_rec.cashflow_header_id := p_insurance_estimate_rec.cashflow_header_id;
442 l_cashflow_header_rec.cashflow_object_id := p_insurance_estimate_rec.cashflow_object_id;
443 l_cashflow_header_rec.cashflow_header_ovn := p_insurance_estimate_rec.cashflow_header_ovn;
444
445 IF p_insurance_estimate_rec.payment_frequency = 'A' THEN
446 l_mpp := 12;
447 ELSIF p_insurance_estimate_rec.payment_frequency = 'S' THEN
448 l_mpp := 6;
449 ELSIF p_insurance_estimate_rec.payment_frequency = 'Q' THEN
450 l_mpp := 3;
451 ELSIF p_insurance_estimate_rec.payment_frequency = 'M' THEN
452 l_mpp := 1;
453 END IF;
454
455 l_cashflow_level_tbl(1).cashflow_level_id := p_insurance_estimate_rec.cashflow_level_id;
456 l_cashflow_level_tbl(1).cashflow_level_ovn := p_insurance_estimate_rec.cashflow_level_ovn;
457 l_cashflow_level_tbl(1).periods := p_insurance_estimate_rec.policy_term / l_mpp;
458 l_cashflow_level_tbl(1).periodic_amount := p_insurance_estimate_rec.periodic_amount;
459 l_cashflow_level_tbl(1).record_mode := 'UPDATE';
460
461 okl_lease_quote_cashflow_pvt.update_cashflow (
462 p_api_version => G_API_VERSION
463 ,p_init_msg_list => G_FALSE
464 ,p_transaction_control => G_FALSE
465 ,p_cashflow_header_rec => l_cashflow_header_rec
466 ,p_cashflow_level_tbl => l_cashflow_level_tbl
467 ,x_return_status => x_return_status
468 ,x_msg_count => x_msg_count
469 ,x_msg_data => x_msg_data
470 );
471
472 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
473 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
474 ELSIF x_return_status = G_RET_STS_ERROR THEN
475 RAISE OKL_API.G_EXCEPTION_ERROR;
476 END IF;
477
478 x_return_status := G_RET_STS_SUCCESS;
479
480 EXCEPTION
481
482 WHEN OKL_API.G_EXCEPTION_ERROR THEN
483 x_return_status := G_RET_STS_ERROR;
484
485 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
486 x_return_status := G_RET_STS_UNEXP_ERROR;
487
488 WHEN OTHERS THEN
489 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
490 p_msg_name => G_DB_ERROR,
491 p_token1 => G_PROG_NAME_TOKEN,
492 p_token1_value => l_program_name,
493 p_token2 => G_SQLCODE_TOKEN,
494 p_token2_value => sqlcode,
495 p_token3 => G_SQLERRM_TOKEN,
496 p_token3_value => sqlerrm);
497
498 x_return_status := G_RET_STS_UNEXP_ERROR;
499
500 END update_cashflow;
501
502
503 --------------------------------------
504 -- PROCEDURE create_insurance_estimate
505 --------------------------------------
506 PROCEDURE create_insurance_estimate (
507 p_api_version IN NUMBER
508 ,p_init_msg_list IN VARCHAR2
509 ,p_transaction_control IN VARCHAR2
510 ,p_insurance_estimate_rec IN ins_est_rec_type
511 ,x_insurance_estimate_id OUT NOCOPY NUMBER
512 ,x_return_status OUT NOCOPY VARCHAR2
513 ,x_msg_count OUT NOCOPY NUMBER
514 ,x_msg_data OUT NOCOPY VARCHAR2
515 ) IS
516
517 l_program_name CONSTANT VARCHAR2(30) := 'create_insurance_estimate';
518 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
519
520 l_insurance_estimate_rec ins_est_rec_type;
521
522
523 --Bug#6935907 -Added by kkorrapo
524 Cursor c_par_obj_code IS
525 SELECT parent_object_code
526 FROM okl_lease_quotes_b
527 WHERE id = p_insurance_estimate_rec.lease_quote_id;
528
529 l_parent_object_code VARCHAR2(30);
530 --Bug#6935907 -Addition end
531
532
533 BEGIN
534
535 IF p_transaction_control = G_TRUE THEN
536 SAVEPOINT l_program_name;
537 END IF;
538
539 IF p_init_msg_list = G_TRUE THEN
540 FND_MSG_PUB.initialize;
541 END IF;
542
543 l_insurance_estimate_rec := p_insurance_estimate_rec;
544
545
546 --Bug#6935907 -Added by kkorrapo
547 OPEN c_par_obj_code;
548 FETCH c_par_obj_code INTO l_parent_object_code;
549 CLOSE c_par_obj_code;
550
551 IF (l_parent_object_code = 'LEASEOPP') THEN
552 l_insurance_estimate_rec.quote_type_code := 'LQ';
553 ELSIF (l_parent_object_code = 'LEASEAPP') THEN
554 l_insurance_estimate_rec.quote_type_code := 'LA';
555 ELSE
556 l_insurance_estimate_rec.quote_type_code := 'QQ';
557 END IF;
558 --Bug#6935907 -Addition end
559 validate_record (
560 p_insurance_estimate_rec => l_insurance_estimate_rec
561 ,x_return_status => x_return_status
562 );
563
564 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
565 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
566 ELSIF x_return_status = G_RET_STS_ERROR THEN
567 RAISE OKL_API.G_EXCEPTION_ERROR;
568 END IF;
569
570 insert_row (
571 p_insurance_estimate_rec => l_insurance_estimate_rec
572 ,x_return_status => x_return_status
573 ,x_msg_count => x_msg_count
574 ,x_msg_data => x_msg_data
575 );
576
577 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
578 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
579 ELSIF x_return_status = G_RET_STS_ERROR THEN
580 RAISE OKL_API.G_EXCEPTION_ERROR;
581 END IF;
582
583 create_cashflow (
584 p_insurance_estimate_rec => l_insurance_estimate_rec
585 ,x_return_status => x_return_status
586 ,x_msg_count => x_msg_count
587 ,x_msg_data => x_msg_data
588 );
589
590 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
591 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
592 ELSIF x_return_status = G_RET_STS_ERROR THEN
593 RAISE OKL_API.G_EXCEPTION_ERROR;
594 END IF;
595
596 x_insurance_estimate_id := l_insurance_estimate_rec.id;
597 x_return_status := G_RET_STS_SUCCESS;
598
599 EXCEPTION
600
601 WHEN OKL_API.G_EXCEPTION_ERROR THEN
602
603 IF p_transaction_control = G_TRUE THEN
604 ROLLBACK TO l_program_name;
605 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
606 END IF;
607
608 x_return_status := G_RET_STS_ERROR;
609
610 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
611
612 IF p_transaction_control = G_TRUE THEN
613 ROLLBACK TO l_program_name;
614 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
615 END IF;
616
617 x_return_status := G_RET_STS_UNEXP_ERROR;
618
619 WHEN OTHERS THEN
620
621 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
622 p_msg_name => G_DB_ERROR,
623 p_token1 => G_PROG_NAME_TOKEN,
624 p_token1_value => l_api_name,
625 p_token2 => G_SQLCODE_TOKEN,
626 p_token2_value => sqlcode,
627 p_token3 => G_SQLERRM_TOKEN,
628 p_token3_value => sqlerrm);
629
630 IF p_transaction_control = G_TRUE THEN
631 ROLLBACK TO l_program_name;
632 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
633 END IF;
634
635 x_return_status := G_RET_STS_UNEXP_ERROR;
636
637 END create_insurance_estimate;
638
639
640 --------------------------------------
641 -- PROCEDURE update_insurance_estimate
642 --------------------------------------
643 PROCEDURE update_insurance_estimate (
644 p_api_version IN NUMBER
645 ,p_init_msg_list IN VARCHAR2
646 ,p_transaction_control IN VARCHAR2
647 ,p_insurance_estimate_rec IN ins_est_rec_type
648 ,x_return_status OUT NOCOPY VARCHAR2
649 ,x_msg_count OUT NOCOPY NUMBER
650 ,x_msg_data OUT NOCOPY VARCHAR2
651 ) IS
652
653 l_program_name CONSTANT VARCHAR2(30) := 'update_insurance_estimate';
654 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
655
656 l_insurance_estimate_rec ins_est_rec_type;
657
658 --Bug#6935907 -Added by kkorrapo
659 Cursor c_par_obj_code IS
660 SELECT parent_object_code
661 FROM okl_lease_quotes_b
662 WHERE id = p_insurance_estimate_rec.lease_quote_id;
663
664 l_parent_object_code VARCHAR2(30);
665 --Bug#6935907 -Addition end
666 BEGIN
667
668 IF p_transaction_control = G_TRUE THEN
669 SAVEPOINT l_program_name;
670 END IF;
671
672 IF p_init_msg_list = G_TRUE THEN
673 FND_MSG_PUB.initialize;
674 END IF;
675
676 l_insurance_estimate_rec := p_insurance_estimate_rec;
677 --Bug#6935907 -Added by kkorrapo
678 OPEN c_par_obj_code;
679 FETCH c_par_obj_code INTO l_parent_object_code;
680 CLOSE c_par_obj_code;
681
682 IF (l_parent_object_code = 'LEASEOPP') THEN
683 l_insurance_estimate_rec.quote_type_code := 'LQ';
684 ELSIF (l_parent_object_code = 'LEASEAPP') THEN
685 l_insurance_estimate_rec.quote_type_code := 'LA';
686 ELSE
687 l_insurance_estimate_rec.quote_type_code := 'QQ';
688 END IF;
689 --Bug#6935907 -Addition end
690 validate_record (
691 p_insurance_estimate_rec => l_insurance_estimate_rec
692 ,x_return_status => x_return_status
693 );
694
695 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
696 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
697 ELSIF x_return_status = G_RET_STS_ERROR THEN
698 RAISE OKL_API.G_EXCEPTION_ERROR;
699 END IF;
700
701 update_row (
702 p_insurance_estimate_rec => l_insurance_estimate_rec
703 ,x_return_status => x_return_status
704 ,x_msg_count => x_msg_count
705 ,x_msg_data => x_msg_data
706 );
707
708 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
709 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
710 ELSIF x_return_status = G_RET_STS_ERROR THEN
711 RAISE OKL_API.G_EXCEPTION_ERROR;
712 END IF;
713
714 update_cashflow (
715 p_insurance_estimate_rec => l_insurance_estimate_rec
716 ,x_return_status => x_return_status
717 ,x_msg_count => x_msg_count
718 ,x_msg_data => x_msg_data
719 );
720
721 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
722 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
723 ELSIF x_return_status = G_RET_STS_ERROR THEN
724 RAISE OKL_API.G_EXCEPTION_ERROR;
725 END IF;
726
727 x_return_status := G_RET_STS_SUCCESS;
728
729 EXCEPTION
730
731 WHEN OKL_API.G_EXCEPTION_ERROR THEN
732
733 IF p_transaction_control = G_TRUE THEN
734 ROLLBACK TO l_program_name;
735 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
736 END IF;
737
738 x_return_status := G_RET_STS_ERROR;
739
740 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
741
742 IF p_transaction_control = G_TRUE THEN
743 ROLLBACK TO l_program_name;
744 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
745 END IF;
746
747 x_return_status := G_RET_STS_UNEXP_ERROR;
748
749 WHEN OTHERS THEN
750
751 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
752 p_msg_name => G_DB_ERROR,
753 p_token1 => G_PROG_NAME_TOKEN,
754 p_token1_value => l_api_name,
755 p_token2 => G_SQLCODE_TOKEN,
756 p_token2_value => sqlcode,
757 p_token3 => G_SQLERRM_TOKEN,
758 p_token3_value => sqlerrm);
759
760 IF p_transaction_control = G_TRUE THEN
761 ROLLBACK TO l_program_name;
762 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
763 END IF;
764
765 x_return_status := G_RET_STS_UNEXP_ERROR;
766
767 END update_insurance_estimate;
768
769
770 --------------------------------------
771 -- PROCEDURE delete_insurance_estimate
772 --------------------------------------
773 PROCEDURE delete_insurance_estimate (
774 p_api_version IN NUMBER
775 ,p_init_msg_list IN VARCHAR2
776 ,p_transaction_control IN VARCHAR2
777 ,p_insurance_estimate_id IN NUMBER
778 ,x_return_status OUT NOCOPY VARCHAR2
779 ,x_msg_count OUT NOCOPY NUMBER
780 ,x_msg_data OUT NOCOPY VARCHAR2
781 ) IS
782
783 l_program_name CONSTANT VARCHAR2(30) := 'delete_insurance_estimate';
784 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
785
786 BEGIN
787
788 IF p_transaction_control = G_TRUE THEN
789 SAVEPOINT l_program_name;
790 END IF;
791
792 IF p_init_msg_list = G_TRUE THEN
793 FND_MSG_PUB.initialize;
794 END IF;
795
796 okl_lease_quote_cashflow_pvt.delete_cashflows (
797 p_api_version => G_API_VERSION
798 ,p_init_msg_list => G_FALSE
799 ,p_transaction_control => G_FALSE
800 ,p_source_object_code => 'QUOTED_INSURANCE'
801 ,p_source_object_id => p_insurance_estimate_id
802 ,x_return_status => x_return_status
803 ,x_msg_count => x_msg_count
804 ,x_msg_data => x_msg_data
805 );
806
807 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
808 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
809 ELSIF x_return_status = G_RET_STS_ERROR THEN
810 RAISE OKL_API.G_EXCEPTION_ERROR;
811 END IF;
812
813 DELETE FROM okl_insurance_estimates_tl WHERE id = p_insurance_estimate_id;
814 DELETE FROM okl_insurance_estimates_b WHERE id = p_insurance_estimate_id;
815
816 x_return_status := G_RET_STS_SUCCESS;
817
818 EXCEPTION
819
820 WHEN OKL_API.G_EXCEPTION_ERROR THEN
821
822 IF p_transaction_control = G_TRUE THEN
823 ROLLBACK TO l_program_name;
824 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
825 END IF;
826
827 x_return_status := G_RET_STS_ERROR;
828
829 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
830
831 IF p_transaction_control = G_TRUE THEN
832 ROLLBACK TO l_program_name;
833 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
834 END IF;
835
836 x_return_status := G_RET_STS_UNEXP_ERROR;
837
838 WHEN OTHERS THEN
839
840 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
841 p_msg_name => G_DB_ERROR,
842 p_token1 => G_PROG_NAME_TOKEN,
843 p_token1_value => l_api_name,
844 p_token2 => G_SQLCODE_TOKEN,
845 p_token2_value => sqlcode,
846 p_token3 => G_SQLERRM_TOKEN,
847 p_token3_value => sqlerrm);
848
849 IF p_transaction_control = G_TRUE THEN
850 ROLLBACK TO l_program_name;
851 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
852 END IF;
853
854 x_return_status := G_RET_STS_UNEXP_ERROR;
855
856 END delete_insurance_estimate;
857
858
859 -----------------------------------------
860 -- PROCEDURE duplicate_insurance_estimate
861 -- Added by jjuneja
862 -----------------------------------------
863 PROCEDURE duplicate_insurance_estimate (
864 p_api_version IN NUMBER
865 ,p_init_msg_list IN VARCHAR2
866 ,p_transaction_control IN VARCHAR2
867 ,p_source_insurance_id IN NUMBER
868 ,p_target_quote_id IN NUMBER
869 ,x_insurance_est_id OUT NOCOPY NUMBER
870 ,x_return_status OUT NOCOPY VARCHAR2
871 ,x_msg_count OUT NOCOPY NUMBER
872 ,x_msg_data OUT NOCOPY VARCHAR2
873 ) IS
874
875 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_insurance_estimate';
876 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
877
878 l_ins_est_rec okl_que_pvt.quev_rec_type;
879 lx_ins_est_rec okl_que_pvt.quev_rec_type;
880
881 BEGIN
882 IF p_transaction_control = G_TRUE THEN
883 SAVEPOINT l_program_name;
884 END IF;
885
886 IF p_init_msg_list = G_TRUE THEN
887 FND_MSG_PUB.initialize;
888 END IF;
889
890
891 get_insurance_estimate_rec(
892 p_insurance_estimate_id => p_source_insurance_id
893 ,x_l_ins_est_rec => l_ins_est_rec
894 ,x_return_status => x_return_status);
895
896 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
897 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
898 ELSIF x_return_status = G_RET_STS_ERROR THEN
899 RAISE OKL_API.G_EXCEPTION_ERROR;
900 END IF;
901
902 l_ins_est_rec.lease_quote_id := p_target_quote_id ;
903
904
905 --call insert_row
906
907 okl_que_pvt.insert_row (
908 p_api_version => G_API_VERSION
909 ,p_init_msg_list => G_FALSE
910 ,x_return_status => x_return_status
911 ,x_msg_count => x_msg_count
912 ,x_msg_data => x_msg_data
913 ,p_quev_rec => l_ins_est_rec
914 ,x_quev_rec => lx_ins_est_rec
915 );
916
917 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
918 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
919 ELSIF x_return_status = G_RET_STS_ERROR THEN
920 RAISE OKL_API.G_EXCEPTION_ERROR;
921 END IF;
922
923 x_insurance_est_id := lx_ins_est_rec.id;
924
925 --call duplicate_cashflow
926
927 okl_lease_quote_cashflow_pvt.duplicate_cashflows (
928 p_api_version => G_API_VERSION
929 ,p_init_msg_list => G_FALSE
930 ,p_transaction_control => G_FALSE
931 ,p_source_object_code => 'QUOTED_INSURANCE'
932 ,p_source_object_id => p_source_insurance_id
933 ,p_target_object_id => x_insurance_est_id
934 ,p_quote_id => p_target_quote_id
935 ,x_return_status => x_return_status
936 ,x_msg_count => x_msg_count
937 ,x_msg_data => x_msg_data );
938
939 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
940 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
941 ELSIF x_return_status = G_RET_STS_ERROR THEN
942 RAISE OKL_API.G_EXCEPTION_ERROR;
943 END IF;
944
945 x_return_status := G_RET_STS_SUCCESS;
946
947 EXCEPTION
948
949 WHEN OKL_API.G_EXCEPTION_ERROR THEN
950
951 IF p_transaction_control = G_TRUE THEN
952 ROLLBACK TO l_program_name;
953 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
954 END IF;
955
956 x_return_status := G_RET_STS_ERROR;
957
958 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
959
960 IF p_transaction_control = G_TRUE THEN
961 ROLLBACK TO l_program_name;
962 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
963 END IF;
964
965 x_return_status := G_RET_STS_UNEXP_ERROR;
966
967 WHEN OTHERS THEN
968
969 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
970 p_msg_name => G_DB_ERROR,
971 p_token1 => G_PROG_NAME_TOKEN,
972 p_token1_value => l_api_name,
973 p_token2 => G_SQLCODE_TOKEN,
974 p_token2_value => sqlcode,
975 p_token3 => G_SQLERRM_TOKEN,
976 p_token3_value => sqlerrm);
977
978 IF p_transaction_control = G_TRUE THEN
979 ROLLBACK TO l_program_name;
980 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
981 END IF;
982
983 x_return_status := G_RET_STS_UNEXP_ERROR;
984
985 END duplicate_insurance_estimate;
986
987 --End of addition by jjuneja
988
989 END OKL_LEASE_QUOTE_INS_PVT;