[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.2.12010000.2 2008/11/18 10:23:21 kkorrapo ship $ */
3
4
5 ----------------------------
6 -- PROCEDURE validate_record
7 ----------------------------
8 PROCEDURE validate_record (
9 p_insurance_estimate_rec IN ins_est_rec_type
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ) IS
12
13 l_program_name CONSTANT VARCHAR2(30) := 'validate_record';
14 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
15
16 l_mpp BINARY_INTEGER;
17
18 BEGIN
19
20 IF p_insurance_estimate_rec.policy_term <= 0 THEN
21 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_PERIOD_ZERO');
22 RAISE OKL_API.G_EXCEPTION_ERROR;
23 END IF;
24
25 IF TRUNC(p_insurance_estimate_rec.policy_term) <> p_insurance_estimate_rec.policy_term THEN
26 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_PERIOD_FRACTION');
27 RAISE OKL_API.G_EXCEPTION_ERROR;
28 END IF;
29
30 IF p_insurance_estimate_rec.periodic_amount < 0 THEN
31 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LEVEL_AMOUNT_ZERO');
32 RAISE OKL_API.G_EXCEPTION_ERROR;
33 END IF;
34
35 IF p_insurance_estimate_rec.payment_frequency = 'A' THEN
36 l_mpp := 12;
37 ELSIF p_insurance_estimate_rec.payment_frequency = 'S' THEN
38 l_mpp := 6;
39 ELSIF p_insurance_estimate_rec.payment_frequency = 'Q' THEN
40 l_mpp := 3;
41 ELSIF p_insurance_estimate_rec.payment_frequency = 'M' THEN
42 l_mpp := 1;
43 END IF;
44
45 IF (p_insurance_estimate_rec.policy_term / l_mpp) <> TRUNC (p_insurance_estimate_rec.policy_term / l_mpp) THEN
46 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_TERM_FREQ_MISMATCH');
47 RAISE OKL_API.G_EXCEPTION_ERROR;
48 END IF;
49
50 x_return_status := G_RET_STS_SUCCESS;
51
52 EXCEPTION
53
54 WHEN OKL_API.G_EXCEPTION_ERROR THEN
55 x_return_status := G_RET_STS_ERROR;
56
57 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
58 x_return_status := G_RET_STS_UNEXP_ERROR;
59
60 WHEN OTHERS THEN
61 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
62 p_msg_name => G_DB_ERROR,
63 p_token1 => G_PROG_NAME_TOKEN,
64 p_token1_value => l_program_name,
65 p_token2 => G_SQLCODE_TOKEN,
66 p_token2_value => sqlcode,
67 p_token3 => G_SQLERRM_TOKEN,
68 p_token3_value => sqlerrm);
69
70 x_return_status := G_RET_STS_UNEXP_ERROR;
71
72 END validate_record;
73
74
75 -----------------------
76 -- PROCEDURE insert_row
77 -----------------------
78 PROCEDURE insert_row (
79 p_insurance_estimate_rec IN OUT NOCOPY ins_est_rec_type
80 ,x_return_status OUT NOCOPY VARCHAR2
81 ,x_msg_count OUT NOCOPY NUMBER
82 ,x_msg_data OUT NOCOPY VARCHAR2
83 ) IS
84
85 l_program_name CONSTANT VARCHAR2(30) := 'insert_row';
86 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
87
88 l_quev_rec okl_que_pvt.quev_rec_type;
89 lx_quev_rec okl_que_pvt.quev_rec_type;
90
91 BEGIN
92
93 l_quev_rec.lease_quote_id := p_insurance_estimate_rec.lease_quote_id;
94 l_quev_rec.policy_term := p_insurance_estimate_rec.policy_term;
95 l_quev_rec.description := p_insurance_estimate_rec.description;
96 --Bug#6935907 -Added by kkorrapo
97 l_quev_rec.attribute_category := p_insurance_estimate_rec.attribute_category;
98 l_quev_rec.attribute1 := p_insurance_estimate_rec.attribute1;
99 l_quev_rec.attribute2 := p_insurance_estimate_rec.attribute2;
100 l_quev_rec.attribute3 := p_insurance_estimate_rec.attribute3;
101 l_quev_rec.attribute4 := p_insurance_estimate_rec.attribute4;
102 l_quev_rec.attribute5 := p_insurance_estimate_rec.attribute5;
103 l_quev_rec.attribute6 := p_insurance_estimate_rec.attribute6;
104 l_quev_rec.attribute7 := p_insurance_estimate_rec.attribute7;
105 l_quev_rec.attribute8 := p_insurance_estimate_rec.attribute8;
106 l_quev_rec.attribute9 := p_insurance_estimate_rec.attribute9;
107 l_quev_rec.attribute10 := p_insurance_estimate_rec.attribute10;
108 l_quev_rec.attribute11 := p_insurance_estimate_rec.attribute11;
109 l_quev_rec.attribute12 := p_insurance_estimate_rec.attribute12;
110 l_quev_rec.attribute13 := p_insurance_estimate_rec.attribute13;
111 l_quev_rec.attribute14 := p_insurance_estimate_rec.attribute14;
112 l_quev_rec.attribute15 := p_insurance_estimate_rec.attribute15;
113 --Bug#6935907 -Addition end
114
115 okl_que_pvt.insert_row (
116 p_api_version => G_API_VERSION
117 ,p_init_msg_list => G_FALSE
118 ,x_return_status => x_return_status
119 ,x_msg_count => x_msg_count
120 ,x_msg_data => x_msg_data
121 ,p_quev_rec => l_quev_rec
122 ,x_quev_rec => lx_quev_rec
123 );
124
125 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
126 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
127 ELSIF x_return_status = G_RET_STS_ERROR THEN
128 RAISE OKL_API.G_EXCEPTION_ERROR;
129 END IF;
130
131 p_insurance_estimate_rec.id := lx_quev_rec.id;
132
133 EXCEPTION
134
135 WHEN OKL_API.G_EXCEPTION_ERROR THEN
136 x_return_status := G_RET_STS_ERROR;
137
138 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
139 x_return_status := G_RET_STS_UNEXP_ERROR;
140
141 WHEN OTHERS THEN
142 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
143 p_msg_name => G_DB_ERROR,
144 p_token1 => G_PROG_NAME_TOKEN,
145 p_token1_value => l_program_name,
146 p_token2 => G_SQLCODE_TOKEN,
147 p_token2_value => sqlcode,
148 p_token3 => G_SQLERRM_TOKEN,
149 p_token3_value => sqlerrm);
150
151 x_return_status := G_RET_STS_UNEXP_ERROR;
152
153 END insert_row;
154
155
156 -----------------------
157 -- PROCEDURE update_row
158 -----------------------
159 PROCEDURE update_row (
160 p_insurance_estimate_rec IN ins_est_rec_type
161 ,x_return_status OUT NOCOPY VARCHAR2
162 ,x_msg_count OUT NOCOPY NUMBER
163 ,x_msg_data OUT NOCOPY VARCHAR2
164 ) IS
165
166 l_program_name CONSTANT VARCHAR2(30) := 'update_row';
167 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
168
169 l_quev_rec okl_que_pvt.quev_rec_type;
170 lx_quev_rec okl_que_pvt.quev_rec_type;
171
172 BEGIN
173
174 l_quev_rec.id := p_insurance_estimate_rec.id;
175 l_quev_rec.object_version_number := p_insurance_estimate_rec.ovn;
176 l_quev_rec.lease_quote_id := p_insurance_estimate_rec.lease_quote_id;
177 l_quev_rec.policy_term := p_insurance_estimate_rec.policy_term;
178 l_quev_rec.description := p_insurance_estimate_rec.description;
179 --Bug#6935907 -Added by kkorrapo
180 l_quev_rec.attribute_category := p_insurance_estimate_rec.attribute_category;
181 l_quev_rec.attribute1 := p_insurance_estimate_rec.attribute1;
182 l_quev_rec.attribute2 := p_insurance_estimate_rec.attribute2;
183 l_quev_rec.attribute3 := p_insurance_estimate_rec.attribute3;
184 l_quev_rec.attribute4 := p_insurance_estimate_rec.attribute4;
185 l_quev_rec.attribute5 := p_insurance_estimate_rec.attribute5;
186 l_quev_rec.attribute6 := p_insurance_estimate_rec.attribute6;
187 l_quev_rec.attribute7 := p_insurance_estimate_rec.attribute7;
188 l_quev_rec.attribute8 := p_insurance_estimate_rec.attribute8;
189 l_quev_rec.attribute9 := p_insurance_estimate_rec.attribute9;
190 l_quev_rec.attribute10 := p_insurance_estimate_rec.attribute10;
191 l_quev_rec.attribute11 := p_insurance_estimate_rec.attribute11;
192 l_quev_rec.attribute12 := p_insurance_estimate_rec.attribute12;
193 l_quev_rec.attribute13 := p_insurance_estimate_rec.attribute13;
194 l_quev_rec.attribute14 := p_insurance_estimate_rec.attribute14;
195 l_quev_rec.attribute15 := p_insurance_estimate_rec.attribute15;
196 --Bug#6935907 -Addition end
197
198 okl_que_pvt.update_row (
199 p_api_version => G_API_VERSION
200 ,p_init_msg_list => G_FALSE
201 ,x_return_status => x_return_status
202 ,x_msg_count => x_msg_count
203 ,x_msg_data => x_msg_data
204 ,p_quev_rec => l_quev_rec
205 ,x_quev_rec => lx_quev_rec
206 );
207
208 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
209 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
210 ELSIF x_return_status = G_RET_STS_ERROR THEN
211 RAISE OKL_API.G_EXCEPTION_ERROR;
212 END IF;
213
214 EXCEPTION
215
216 WHEN OKL_API.G_EXCEPTION_ERROR THEN
217 x_return_status := G_RET_STS_ERROR;
218
219 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
220 x_return_status := G_RET_STS_UNEXP_ERROR;
221
222 WHEN OTHERS THEN
223 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
224 p_msg_name => G_DB_ERROR,
225 p_token1 => G_PROG_NAME_TOKEN,
226 p_token1_value => l_program_name,
227 p_token2 => G_SQLCODE_TOKEN,
228 p_token2_value => sqlcode,
229 p_token3 => G_SQLERRM_TOKEN,
230 p_token3_value => sqlerrm);
231
232 x_return_status := G_RET_STS_UNEXP_ERROR;
233
234 END update_row;
235
236
237 ----------------------------
238 -- PROCEDURE create_cashflow
239 ----------------------------
240 PROCEDURE create_cashflow (
241 p_insurance_estimate_rec IN ins_est_rec_type
242 ,x_return_status OUT NOCOPY VARCHAR2
243 ,x_msg_count OUT NOCOPY NUMBER
244 ,x_msg_data OUT NOCOPY VARCHAR2
245 ) IS
246
247 l_program_name CONSTANT VARCHAR2(30) := 'create_cashflow';
248 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
249
250 l_cashflow_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
251 l_cashflow_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
252
253 l_mpp BINARY_INTEGER;
254
255 BEGIN
256
257 l_cashflow_header_rec.type_code := 'INFLOW';
258 l_cashflow_header_rec.stream_type_id := p_insurance_estimate_rec.stream_type_id;
259 l_cashflow_header_rec.arrears_flag := 'N';
260 l_cashflow_header_rec.frequency_code := p_insurance_estimate_rec.payment_frequency;
261 l_cashflow_header_rec.parent_object_code := 'QUOTED_INSURANCE';
262 l_cashflow_header_rec.parent_object_id := p_insurance_estimate_rec.id;
263 l_cashflow_header_rec.quote_type_code := p_insurance_estimate_rec.quote_type_code;
264 l_cashflow_header_rec.quote_id := p_insurance_estimate_rec.lease_quote_id;
265
266 IF p_insurance_estimate_rec.payment_frequency = 'A' THEN
267 l_mpp := 12;
268 ELSIF p_insurance_estimate_rec.payment_frequency = 'S' THEN
269 l_mpp := 6;
270 ELSIF p_insurance_estimate_rec.payment_frequency = 'Q' THEN
271 l_mpp := 3;
272 ELSIF p_insurance_estimate_rec.payment_frequency = 'M' THEN
273 l_mpp := 1;
274 END IF;
275
276 l_cashflow_level_tbl(1).periods := p_insurance_estimate_rec.policy_term / l_mpp;
277 l_cashflow_level_tbl(1).periodic_amount := p_insurance_estimate_rec.periodic_amount;
278 l_cashflow_level_tbl(1).record_mode := 'CREATE';
279
280 okl_lease_quote_cashflow_pvt.create_cashflow (
281 p_api_version => G_API_VERSION
282 ,p_init_msg_list => G_FALSE
283 ,p_transaction_control => G_FALSE
284 ,p_cashflow_header_rec => l_cashflow_header_rec
285 ,p_cashflow_level_tbl => l_cashflow_level_tbl
286 ,x_return_status => x_return_status
287 ,x_msg_count => x_msg_count
288 ,x_msg_data => x_msg_data
289 );
290
291 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
292 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
293 ELSIF x_return_status = G_RET_STS_ERROR THEN
294 RAISE OKL_API.G_EXCEPTION_ERROR;
295 END IF;
296
297 x_return_status := G_RET_STS_SUCCESS;
298
299 EXCEPTION
300
301 WHEN OKL_API.G_EXCEPTION_ERROR THEN
302 x_return_status := G_RET_STS_ERROR;
303
304 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
305 x_return_status := G_RET_STS_UNEXP_ERROR;
306
307 WHEN OTHERS THEN
308 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
309 p_msg_name => G_DB_ERROR,
310 p_token1 => G_PROG_NAME_TOKEN,
311 p_token1_value => l_program_name,
312 p_token2 => G_SQLCODE_TOKEN,
313 p_token2_value => sqlcode,
314 p_token3 => G_SQLERRM_TOKEN,
315 p_token3_value => sqlerrm);
316
317 x_return_status := G_RET_STS_UNEXP_ERROR;
318
319 END create_cashflow;
320
321
322 ----------------------------
323 -- PROCEDURE update_cashflow
324 ----------------------------
325 PROCEDURE update_cashflow (
326 p_insurance_estimate_rec IN ins_est_rec_type
327 ,x_return_status OUT NOCOPY VARCHAR2
328 ,x_msg_count OUT NOCOPY NUMBER
329 ,x_msg_data OUT NOCOPY VARCHAR2
330 ) IS
331
332 l_program_name CONSTANT VARCHAR2(30) := 'update_cashflow';
333 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
334
335 l_cashflow_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
336 l_cashflow_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
337
338 l_mpp BINARY_INTEGER;
339
340 BEGIN
341
342 l_cashflow_header_rec.type_code := 'INFLOW';
343 l_cashflow_header_rec.stream_type_id := p_insurance_estimate_rec.stream_type_id;
344 l_cashflow_header_rec.arrears_flag := 'N';
345 l_cashflow_header_rec.frequency_code := p_insurance_estimate_rec.payment_frequency;
346 l_cashflow_header_rec.parent_object_code := 'QUOTED_INSURANCE';
347 l_cashflow_header_rec.parent_object_id := p_insurance_estimate_rec.id;
348 l_cashflow_header_rec.quote_type_code := p_insurance_estimate_rec.quote_type_code;
349 l_cashflow_header_rec.quote_id := p_insurance_estimate_rec.lease_quote_id;
350 l_cashflow_header_rec.cashflow_header_id := p_insurance_estimate_rec.cashflow_header_id;
351 l_cashflow_header_rec.cashflow_object_id := p_insurance_estimate_rec.cashflow_object_id;
352 l_cashflow_header_rec.cashflow_header_ovn := p_insurance_estimate_rec.cashflow_header_ovn;
353
354 IF p_insurance_estimate_rec.payment_frequency = 'A' THEN
355 l_mpp := 12;
356 ELSIF p_insurance_estimate_rec.payment_frequency = 'S' THEN
357 l_mpp := 6;
358 ELSIF p_insurance_estimate_rec.payment_frequency = 'Q' THEN
359 l_mpp := 3;
360 ELSIF p_insurance_estimate_rec.payment_frequency = 'M' THEN
361 l_mpp := 1;
362 END IF;
363
364 l_cashflow_level_tbl(1).cashflow_level_id := p_insurance_estimate_rec.cashflow_level_id;
365 l_cashflow_level_tbl(1).cashflow_level_ovn := p_insurance_estimate_rec.cashflow_level_ovn;
366 l_cashflow_level_tbl(1).periods := p_insurance_estimate_rec.policy_term / l_mpp;
367 l_cashflow_level_tbl(1).periodic_amount := p_insurance_estimate_rec.periodic_amount;
368 l_cashflow_level_tbl(1).record_mode := 'UPDATE';
369
370 okl_lease_quote_cashflow_pvt.update_cashflow (
371 p_api_version => G_API_VERSION
372 ,p_init_msg_list => G_FALSE
373 ,p_transaction_control => G_FALSE
374 ,p_cashflow_header_rec => l_cashflow_header_rec
375 ,p_cashflow_level_tbl => l_cashflow_level_tbl
376 ,x_return_status => x_return_status
377 ,x_msg_count => x_msg_count
378 ,x_msg_data => x_msg_data
379 );
380
381 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
382 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
383 ELSIF x_return_status = G_RET_STS_ERROR THEN
384 RAISE OKL_API.G_EXCEPTION_ERROR;
385 END IF;
386
387 x_return_status := G_RET_STS_SUCCESS;
388
389 EXCEPTION
390
391 WHEN OKL_API.G_EXCEPTION_ERROR THEN
392 x_return_status := G_RET_STS_ERROR;
393
394 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
395 x_return_status := G_RET_STS_UNEXP_ERROR;
396
397 WHEN OTHERS THEN
398 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
399 p_msg_name => G_DB_ERROR,
400 p_token1 => G_PROG_NAME_TOKEN,
401 p_token1_value => l_program_name,
402 p_token2 => G_SQLCODE_TOKEN,
403 p_token2_value => sqlcode,
404 p_token3 => G_SQLERRM_TOKEN,
405 p_token3_value => sqlerrm);
406
407 x_return_status := G_RET_STS_UNEXP_ERROR;
408
409 END update_cashflow;
410
411
412 --------------------------------------
413 -- PROCEDURE create_insurance_estimate
414 --------------------------------------
415 PROCEDURE create_insurance_estimate (
416 p_api_version IN NUMBER
417 ,p_init_msg_list IN VARCHAR2
418 ,p_transaction_control IN VARCHAR2
419 ,p_insurance_estimate_rec IN ins_est_rec_type
420 ,x_insurance_estimate_id OUT NOCOPY NUMBER
421 ,x_return_status OUT NOCOPY VARCHAR2
422 ,x_msg_count OUT NOCOPY NUMBER
423 ,x_msg_data OUT NOCOPY VARCHAR2
424 ) IS
425
426 l_program_name CONSTANT VARCHAR2(30) := 'create_insurance_estimate';
427 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
428
429 l_insurance_estimate_rec ins_est_rec_type;
430
431
432 --Bug#6935907 -Added by kkorrapo
433 Cursor c_par_obj_code IS
434 SELECT parent_object_code
435 FROM okl_lease_quotes_b
436 WHERE id = p_insurance_estimate_rec.lease_quote_id;
437
438 l_parent_object_code VARCHAR2(30);
439 --Bug#6935907 -Addition end
440
441
442 BEGIN
443
444 IF p_transaction_control = G_TRUE THEN
445 SAVEPOINT l_program_name;
446 END IF;
447
448 IF p_init_msg_list = G_TRUE THEN
449 FND_MSG_PUB.initialize;
450 END IF;
451
452 l_insurance_estimate_rec := p_insurance_estimate_rec;
453
454
455 --Bug#6935907 -Added by kkorrapo
456 OPEN c_par_obj_code;
457 FETCH c_par_obj_code INTO l_parent_object_code;
458 CLOSE c_par_obj_code;
459
460 IF (l_parent_object_code = 'LEASEOPP') THEN
461 l_insurance_estimate_rec.quote_type_code := 'LQ';
462 ELSIF (l_parent_object_code = 'LEASEAPP') THEN
463 l_insurance_estimate_rec.quote_type_code := 'LA';
464 ELSE
465 l_insurance_estimate_rec.quote_type_code := 'QQ';
466 END IF;
467 --Bug#6935907 -Addition end
468 validate_record (
469 p_insurance_estimate_rec => l_insurance_estimate_rec
470 ,x_return_status => x_return_status
471 );
472
473 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
474 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
475 ELSIF x_return_status = G_RET_STS_ERROR THEN
476 RAISE OKL_API.G_EXCEPTION_ERROR;
477 END IF;
478
479 insert_row (
480 p_insurance_estimate_rec => l_insurance_estimate_rec
481 ,x_return_status => x_return_status
482 ,x_msg_count => x_msg_count
483 ,x_msg_data => x_msg_data
484 );
485
486 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
487 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
488 ELSIF x_return_status = G_RET_STS_ERROR THEN
489 RAISE OKL_API.G_EXCEPTION_ERROR;
490 END IF;
491
492 create_cashflow (
493 p_insurance_estimate_rec => l_insurance_estimate_rec
494 ,x_return_status => x_return_status
495 ,x_msg_count => x_msg_count
496 ,x_msg_data => x_msg_data
497 );
498
499 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
500 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
501 ELSIF x_return_status = G_RET_STS_ERROR THEN
502 RAISE OKL_API.G_EXCEPTION_ERROR;
503 END IF;
504
505 x_insurance_estimate_id := l_insurance_estimate_rec.id;
506 x_return_status := G_RET_STS_SUCCESS;
507
508 EXCEPTION
509
510 WHEN OKL_API.G_EXCEPTION_ERROR THEN
511
512 IF p_transaction_control = G_TRUE THEN
513 ROLLBACK TO l_program_name;
514 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
515 END IF;
516
517 x_return_status := G_RET_STS_ERROR;
518
519 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
520
521 IF p_transaction_control = G_TRUE THEN
522 ROLLBACK TO l_program_name;
523 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
524 END IF;
525
526 x_return_status := G_RET_STS_UNEXP_ERROR;
527
528 WHEN OTHERS THEN
529
530 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
531 p_msg_name => G_DB_ERROR,
532 p_token1 => G_PROG_NAME_TOKEN,
533 p_token1_value => l_api_name,
534 p_token2 => G_SQLCODE_TOKEN,
535 p_token2_value => sqlcode,
536 p_token3 => G_SQLERRM_TOKEN,
537 p_token3_value => sqlerrm);
538
539 IF p_transaction_control = G_TRUE THEN
540 ROLLBACK TO l_program_name;
541 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
542 END IF;
543
544 x_return_status := G_RET_STS_UNEXP_ERROR;
545
546 END create_insurance_estimate;
547
548
549 --------------------------------------
550 -- PROCEDURE update_insurance_estimate
551 --------------------------------------
552 PROCEDURE update_insurance_estimate (
553 p_api_version IN NUMBER
554 ,p_init_msg_list IN VARCHAR2
555 ,p_transaction_control IN VARCHAR2
556 ,p_insurance_estimate_rec IN ins_est_rec_type
557 ,x_return_status OUT NOCOPY VARCHAR2
558 ,x_msg_count OUT NOCOPY NUMBER
559 ,x_msg_data OUT NOCOPY VARCHAR2
560 ) IS
561
562 l_program_name CONSTANT VARCHAR2(30) := 'update_insurance_estimate';
563 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
564
565 l_insurance_estimate_rec ins_est_rec_type;
566
567 --Bug#6935907 -Added by kkorrapo
568 Cursor c_par_obj_code IS
569 SELECT parent_object_code
570 FROM okl_lease_quotes_b
571 WHERE id = p_insurance_estimate_rec.lease_quote_id;
572
573 l_parent_object_code VARCHAR2(30);
574 --Bug#6935907 -Addition end
575 BEGIN
576
577 IF p_transaction_control = G_TRUE THEN
578 SAVEPOINT l_program_name;
579 END IF;
580
581 IF p_init_msg_list = G_TRUE THEN
582 FND_MSG_PUB.initialize;
583 END IF;
584
585 l_insurance_estimate_rec := p_insurance_estimate_rec;
586 --Bug#6935907 -Added by kkorrapo
587 OPEN c_par_obj_code;
588 FETCH c_par_obj_code INTO l_parent_object_code;
589 CLOSE c_par_obj_code;
590
591 IF (l_parent_object_code = 'LEASEOPP') THEN
592 l_insurance_estimate_rec.quote_type_code := 'LQ';
593 ELSIF (l_parent_object_code = 'LEASEAPP') THEN
594 l_insurance_estimate_rec.quote_type_code := 'LA';
595 ELSE
596 l_insurance_estimate_rec.quote_type_code := 'QQ';
597 END IF;
598 --Bug#6935907 -Addition end
599 validate_record (
600 p_insurance_estimate_rec => l_insurance_estimate_rec
601 ,x_return_status => x_return_status
602 );
603
604 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
605 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
606 ELSIF x_return_status = G_RET_STS_ERROR THEN
607 RAISE OKL_API.G_EXCEPTION_ERROR;
608 END IF;
609
610 update_row (
611 p_insurance_estimate_rec => l_insurance_estimate_rec
612 ,x_return_status => x_return_status
613 ,x_msg_count => x_msg_count
614 ,x_msg_data => x_msg_data
615 );
616
617 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
618 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
619 ELSIF x_return_status = G_RET_STS_ERROR THEN
620 RAISE OKL_API.G_EXCEPTION_ERROR;
621 END IF;
622
623 update_cashflow (
624 p_insurance_estimate_rec => l_insurance_estimate_rec
625 ,x_return_status => x_return_status
626 ,x_msg_count => x_msg_count
627 ,x_msg_data => x_msg_data
628 );
629
630 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
631 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
632 ELSIF x_return_status = G_RET_STS_ERROR THEN
633 RAISE OKL_API.G_EXCEPTION_ERROR;
634 END IF;
635
636 x_return_status := G_RET_STS_SUCCESS;
637
638 EXCEPTION
639
640 WHEN OKL_API.G_EXCEPTION_ERROR THEN
641
642 IF p_transaction_control = G_TRUE THEN
643 ROLLBACK TO l_program_name;
644 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
645 END IF;
646
647 x_return_status := G_RET_STS_ERROR;
648
649 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
650
651 IF p_transaction_control = G_TRUE THEN
652 ROLLBACK TO l_program_name;
653 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
654 END IF;
655
656 x_return_status := G_RET_STS_UNEXP_ERROR;
657
658 WHEN OTHERS THEN
659
660 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
661 p_msg_name => G_DB_ERROR,
662 p_token1 => G_PROG_NAME_TOKEN,
663 p_token1_value => l_api_name,
664 p_token2 => G_SQLCODE_TOKEN,
665 p_token2_value => sqlcode,
666 p_token3 => G_SQLERRM_TOKEN,
667 p_token3_value => sqlerrm);
668
669 IF p_transaction_control = G_TRUE THEN
670 ROLLBACK TO l_program_name;
671 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
672 END IF;
673
674 x_return_status := G_RET_STS_UNEXP_ERROR;
675
676 END update_insurance_estimate;
677
678
679 --------------------------------------
680 -- PROCEDURE delete_insurance_estimate
681 --------------------------------------
682 PROCEDURE delete_insurance_estimate (
683 p_api_version IN NUMBER
684 ,p_init_msg_list IN VARCHAR2
685 ,p_transaction_control IN VARCHAR2
686 ,p_insurance_estimate_id IN NUMBER
687 ,x_return_status OUT NOCOPY VARCHAR2
688 ,x_msg_count OUT NOCOPY NUMBER
689 ,x_msg_data OUT NOCOPY VARCHAR2
690 ) IS
691
692 l_program_name CONSTANT VARCHAR2(30) := 'delete_insurance_estimate';
693 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
694
695 BEGIN
696
697 IF p_transaction_control = G_TRUE THEN
698 SAVEPOINT l_program_name;
699 END IF;
700
701 IF p_init_msg_list = G_TRUE THEN
702 FND_MSG_PUB.initialize;
703 END IF;
704
705 okl_lease_quote_cashflow_pvt.delete_cashflows (
706 p_api_version => G_API_VERSION
707 ,p_init_msg_list => G_FALSE
708 ,p_transaction_control => G_FALSE
709 ,p_source_object_code => 'QUOTED_INSURANCE'
710 ,p_source_object_id => p_insurance_estimate_id
711 ,x_return_status => x_return_status
712 ,x_msg_count => x_msg_count
713 ,x_msg_data => x_msg_data
714 );
715
716 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
717 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
718 ELSIF x_return_status = G_RET_STS_ERROR THEN
719 RAISE OKL_API.G_EXCEPTION_ERROR;
720 END IF;
721
722 DELETE FROM okl_insurance_estimates_tl WHERE id = p_insurance_estimate_id;
723 DELETE FROM okl_insurance_estimates_b WHERE id = p_insurance_estimate_id;
724
725 x_return_status := G_RET_STS_SUCCESS;
726
727 EXCEPTION
728
729 WHEN OKL_API.G_EXCEPTION_ERROR THEN
730
731 IF p_transaction_control = G_TRUE THEN
732 ROLLBACK TO l_program_name;
733 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
734 END IF;
735
736 x_return_status := G_RET_STS_ERROR;
737
738 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
739
740 IF p_transaction_control = G_TRUE THEN
741 ROLLBACK TO l_program_name;
742 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
743 END IF;
744
745 x_return_status := G_RET_STS_UNEXP_ERROR;
746
747 WHEN OTHERS THEN
748
749 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
750 p_msg_name => G_DB_ERROR,
751 p_token1 => G_PROG_NAME_TOKEN,
752 p_token1_value => l_api_name,
753 p_token2 => G_SQLCODE_TOKEN,
754 p_token2_value => sqlcode,
755 p_token3 => G_SQLERRM_TOKEN,
756 p_token3_value => sqlerrm);
757
758 IF p_transaction_control = G_TRUE THEN
759 ROLLBACK TO l_program_name;
760 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
761 END IF;
762
763 x_return_status := G_RET_STS_UNEXP_ERROR;
764
765 END delete_insurance_estimate;
766
767
768 END OKL_LEASE_QUOTE_INS_PVT;