[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_OPPORTUNITY_PVT
Source
1 PACKAGE BODY OKL_LEASE_OPPORTUNITY_PVT AS
2 /* $Header: OKLRLOPB.pls 120.17.12010000.2 2008/11/13 13:06:39 kkorrapo ship $ */
3
4 CURSOR c_functional_currency IS
5 SELECT OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE
6 FROM DUAL;
7
8 -------------------------------
9 -- PROCEDURE validate_lease_opp
10 -------------------------------
11 PROCEDURE validate_lease_opp (p_lease_opp_rec IN lease_opp_rec_type,
12 x_return_status OUT NOCOPY VARCHAR2) IS
13
14 l_return_status VARCHAR2(1);
15
16 l_program_name CONSTANT VARCHAR2(30) := 'validate_lease_opp';
17 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
18 l_db_lease_opp_rec lease_opp_rec_type;
19
20 CURSOR chk_uniquness (p_reference_number VARCHAR2) IS
21 SELECT '1'
22 FROM okl_lease_opportunities_b
23 WHERE reference_number = p_reference_number
24 AND id <> NVL(p_lease_opp_rec.id, -9999);
25
26 l_refno_unq_chk NUMBER;
27
28 --Bug 7022258-Added by kkorrapo
29 l_valid varchar2(3);
30 --Bug 7022258--Addition end
31
32 l_functional_currency VARCHAR2(15);
33
34 BEGIN
35
36 OPEN chk_uniquness(p_lease_opp_rec.reference_number);
37 FETCH chk_uniquness INTO l_refno_unq_chk;
38 CLOSE chk_uniquness;
39
40 IF l_refno_unq_chk IS NOT NULL THEN
41 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
42 p_msg_name => 'OKL_REFNO_UNIQUE_CHECK');
43 RAISE OKL_API.G_EXCEPTION_ERROR;
44 END IF;
45
46 -- viselvar added this validation for Bug 5042858
47 l_return_status := OKL_LEASE_APP_PVT.is_curr_conv_valid(
48 p_curr_code => p_lease_opp_rec.currency_code
49 ,p_curr_type => p_lease_opp_rec.currency_conversion_type
50 ,p_curr_rate => p_lease_opp_rec.currency_conversion_rate
51 ,p_curr_date => p_lease_opp_rec.currency_conversion_date);
52
53 IF (l_return_status = OKL_API.G_RET_STS_ERROR)
54 THEN
55 RAISE OKL_API.G_EXCEPTION_ERROR;
56 END IF;
57
58 /*OPEN c_functional_currency;
59 FETCH c_functional_currency INTO l_functional_currency;
60 CLOSE c_functional_currency;
61
62 IF p_lease_opp_rec.currency_code <> l_functional_currency THEN
63
64 IF p_lease_opp_rec.currency_conversion_type IS NULL THEN
65 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
66 p_msg_name => 'OKL_REQUIRED_CURRENCY_TYPE');
67 RAISE OKL_API.G_EXCEPTION_ERROR;
68 END IF;
69
70 IF p_lease_opp_rec.currency_conversion_date IS NULL THEN
71 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
72 p_msg_name => 'OKL_REQUIRED_CURRENCY_DATE');
73 RAISE OKL_API.G_EXCEPTION_ERROR;
74 END IF;
75
76 IF p_lease_opp_rec.currency_conversion_type = 'User' AND p_lease_opp_rec.CURRENCY_CONVERSION_RATE IS NOT NULL THEN
77 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
78 p_msg_name => 'OKL_REQUIRED_CURRENCY_RATE');
79 RAISE OKL_API.G_EXCEPTION_ERROR;
80 END IF;
81
82 ELSE
83
84 IF p_lease_opp_rec.CURRENCY_CONVERSION_TYPE IS NOT NULL THEN
85 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
86 p_msg_name => 'OKL_NOTREQUIRED_CURRENCY_TYPE');
87 RAISE OKL_API.G_EXCEPTION_ERROR;
88 END IF;
89
90 IF p_lease_opp_rec.CURRENCY_CONVERSION_DATE IS NOT NULL THEN
91 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
92 p_msg_name => 'OKL_NOTREQUIRED_CURRENCY_DATE');
93 RAISE OKL_API.G_EXCEPTION_ERROR;
94 END IF;
95
96 IF p_lease_opp_rec.CURRENCY_CONVERSION_RATE IS NOT NULL THEN
97 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
98 p_msg_name => 'OKL_NOTREQUIRED_CURRENCY_RATE');
99 RAISE OKL_API.G_EXCEPTION_ERROR;
100 END IF;
101
102 END IF;*/
103
104 -- Date Validations
105 IF p_lease_opp_rec.expected_start_date < p_lease_opp_rec.valid_from THEN
106 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
107 p_msg_name => 'OKL_LOP_INVALID_START_DATE');
108 RAISE OKL_API.G_EXCEPTION_ERROR;
109 END IF;
110
111 IF p_lease_opp_rec.funding_date < p_lease_opp_rec.valid_from THEN
112 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
113 p_msg_name => 'OKL_LOP_INVALID_FUNDING_DATE');
114 RAISE OKL_API.G_EXCEPTION_ERROR;
115 END IF;
116
117 IF p_lease_opp_rec.delivery_date < p_lease_opp_rec.valid_from THEN
118 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
119 p_msg_name => 'OKL_LOP_INVALID_DELV_DATE');
120 RAISE OKL_API.G_EXCEPTION_ERROR;
121 END IF;
122
123 EXCEPTION
124
125 WHEN OKL_API.G_EXCEPTION_ERROR THEN
126
127 x_return_status := G_RET_STS_ERROR;
128
129 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
130
131 x_return_status := G_RET_STS_UNEXP_ERROR;
132
133 WHEN OTHERS THEN
134
135 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
136 p_msg_name => G_DB_ERROR,
137 p_token1 => G_PROG_NAME_TOKEN,
138 p_token1_value => l_api_name,
139 p_token2 => G_SQLCODE_TOKEN,
140 p_token2_value => sqlcode,
141 p_token3 => G_SQLERRM_TOKEN,
142 p_token3_value => sqlerrm);
143
144 x_return_status := G_RET_STS_UNEXP_ERROR;
145
146 END validate_lease_opp;
147
148
149 -----------------------------
150 -- PROCEDURE create_lease_opp
151 -----------------------------
152 PROCEDURE create_lease_opp (p_api_version IN NUMBER,
153 p_init_msg_list IN VARCHAR2,
154 p_transaction_control IN VARCHAR2,
155 p_lease_opp_rec IN lease_opp_rec_type,
156 p_quick_quote_id IN NUMBER,
157 x_lease_opp_rec OUT NOCOPY lease_opp_rec_type,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_msg_count OUT NOCOPY NUMBER,
160 x_msg_data OUT NOCOPY VARCHAR2) IS
161
162 l_program_name CONSTANT VARCHAR2(30) := 'create_lease_opp';
163 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
164
165 l_lease_opp_rec lease_opp_rec_type;
166 l_functional_currency VARCHAR2(15);
167
168 l_return_status VARCHAR2(1);
169
170 BEGIN
171
172 IF p_transaction_control = G_TRUE THEN
173 SAVEPOINT l_program_name;
174 END IF;
175
176 IF p_init_msg_list = G_TRUE THEN
177 FND_MSG_PUB.initialize;
178 END IF;
179
180 l_lease_opp_rec := p_lease_opp_rec;
181
182 l_lease_opp_rec.valid_from := TRUNC(l_lease_opp_rec.valid_from);
183 l_lease_opp_rec.expected_start_date := TRUNC(l_lease_opp_rec.expected_start_date);
184 l_lease_opp_rec.delivery_date := TRUNC(l_lease_opp_rec.delivery_date);
185 l_lease_opp_rec.funding_date := TRUNC(l_lease_opp_rec.funding_date);
186 l_lease_opp_rec.currency_conversion_date := TRUNC(l_lease_opp_rec.currency_conversion_date);
187
188 l_lease_opp_rec.status := 'INCOMPLETE';
189
190 --Bug 7022258-Modified by kkorrapo
191 l_lease_opp_rec.reference_number := l_lease_opp_rec.reference_number;
192 --Bug 7022258--Modification end
193
194
195 --Bug 5100228 Begin
196 l_return_status := okl_lease_app_pvt.is_curr_conv_valid(
197 p_curr_code => l_lease_opp_rec.currency_code
198 ,p_curr_type => l_lease_opp_rec.currency_conversion_type
199 ,p_curr_rate => l_lease_opp_rec.currency_conversion_rate
200 ,p_curr_date => l_lease_opp_rec.currency_conversion_date);
201
202 IF (l_return_status = OKL_API.G_RET_STS_ERROR)
203 THEN
204 RAISE OKL_API.G_EXCEPTION_ERROR;
205 END IF;
206 --Bug 5100228 End
207
208 validate_lease_opp(p_lease_opp_rec => l_lease_opp_rec,
209 x_return_status => l_return_status);
210
211 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
212 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
213 ELSIF l_return_status = G_RET_STS_ERROR THEN
214 RAISE OKL_API.G_EXCEPTION_ERROR;
215 END IF;
216
217 okl_lop_pvt.insert_row(
218 p_api_version => G_API_VERSION
219 ,p_init_msg_list => G_TRUE
220 ,x_return_status => l_return_status
221 ,x_msg_count => x_msg_count
222 ,x_msg_data => x_msg_data
223 ,p_lopv_rec => l_lease_opp_rec
224 ,x_lopv_rec => x_lease_opp_rec
225 );
226
227 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
228 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
229 ELSIF l_return_status = G_RET_STS_ERROR THEN
230 RAISE OKL_API.G_EXCEPTION_ERROR;
231 END IF;
232
233 IF p_quick_quote_id IS NOT NULL THEN
234
235 UPDATE okl_quick_quotes_b SET
236 lease_opportunity_id = x_lease_opp_rec.id
237 ,currency_code = l_lease_opp_rec.currency_code
238 ,program_agreement_id = l_lease_opp_rec.program_agreement_id
239 WHERE id = p_quick_quote_id;
240
241 END IF;
242
243 x_return_status := l_return_status;
244
245 EXCEPTION
246
247 WHEN OKL_API.G_EXCEPTION_ERROR THEN
248
249 IF p_transaction_control = G_TRUE THEN
250 ROLLBACK TO l_program_name;
251 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
252 END IF;
253
254 x_return_status := G_RET_STS_ERROR;
255
256 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
257
258 IF p_transaction_control = G_TRUE THEN
259 ROLLBACK TO l_program_name;
260 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
261 END IF;
262
263 x_return_status := G_RET_STS_UNEXP_ERROR;
264
265 WHEN OTHERS THEN
266
267 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
268 p_msg_name => G_DB_ERROR,
269 p_token1 => G_PROG_NAME_TOKEN,
270 p_token1_value => l_api_name,
271 p_token2 => G_SQLCODE_TOKEN,
272 p_token2_value => sqlcode,
273 p_token3 => G_SQLERRM_TOKEN,
274 p_token3_value => sqlerrm);
275
276 IF p_transaction_control = G_TRUE THEN
277 ROLLBACK TO l_program_name;
278 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
279 END IF;
280
281 x_return_status := G_RET_STS_UNEXP_ERROR;
282
283 END create_lease_opp;
284
285
286 -----------------------------
287 -- PROCEDURE update_lease_opp
288 -----------------------------
289 PROCEDURE update_lease_opp (p_api_version IN NUMBER,
290 p_init_msg_list IN VARCHAR2,
291 p_transaction_control IN VARCHAR2,
292 p_lease_opp_rec IN lease_opp_rec_type,
293 x_lease_opp_rec OUT NOCOPY lease_opp_rec_type,
294 x_return_status OUT NOCOPY VARCHAR2,
295 x_msg_count OUT NOCOPY NUMBER,
296 x_msg_data OUT NOCOPY VARCHAR2) IS
297
298 l_program_name CONSTANT VARCHAR2(30) := 'update_lease_opp';
299 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
300
301 l_lease_opp_rec lease_opp_rec_type;
302 l_functional_currency VARCHAR2(15);
303
304 l_return_status VARCHAR2(1);
305
306 BEGIN
307
308 IF p_transaction_control = G_TRUE THEN
309 SAVEPOINT l_program_name;
310 END IF;
311
312 IF p_init_msg_list = G_TRUE THEN
313 FND_MSG_PUB.initialize;
314 END IF;
315
316 l_lease_opp_rec := p_lease_opp_rec;
317
318 --Bug 4895154 Beging
319 IF l_lease_opp_rec.STATUS <> 'CANCELLED' THEN
320 --Bug 4895154 END
321
322 l_lease_opp_rec.valid_from := TRUNC(l_lease_opp_rec.valid_from);
323 l_lease_opp_rec.expected_start_date := TRUNC(l_lease_opp_rec.expected_start_date);
324 l_lease_opp_rec.delivery_date := TRUNC(l_lease_opp_rec.delivery_date);
325 l_lease_opp_rec.funding_date := TRUNC(l_lease_opp_rec.funding_date);
326 l_lease_opp_rec.currency_conversion_date := TRUNC(l_lease_opp_rec.currency_conversion_date);
327
328 --Bug 5100228 Begin
329 l_return_status := okl_lease_app_pvt.is_curr_conv_valid(
330 p_curr_code => l_lease_opp_rec.currency_code
331 ,p_curr_type => l_lease_opp_rec.currency_conversion_type
332 ,p_curr_rate => l_lease_opp_rec.currency_conversion_rate
333 ,p_curr_date => l_lease_opp_rec.currency_conversion_date);
334
335 IF (l_return_status = OKL_API.G_RET_STS_ERROR)
336 THEN
337 RAISE OKL_API.G_EXCEPTION_ERROR;
338 END IF;
339 --Bug 5100228 End
340
341 validate_lease_opp(p_lease_opp_rec => l_lease_opp_rec,
342 x_return_status => l_return_status);
343
344 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
345 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
346 ELSIF l_return_status = G_RET_STS_ERROR THEN
347 RAISE OKL_API.G_EXCEPTION_ERROR;
348 END IF;
349
350 --Bug 4895154 Beging
351 END IF;
352 --Bug 4895154 END
353
354 okl_lop_pvt.update_row(
355 p_api_version => G_API_VERSION
356 ,p_init_msg_list => G_TRUE
357 ,x_return_status => l_return_status
358 ,x_msg_count => x_msg_count
359 ,x_msg_data => x_msg_data
360 ,p_lopv_rec => l_lease_opp_rec
361 ,x_lopv_rec => x_lease_opp_rec);
362
363 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
364 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
365 ELSIF l_return_status = G_RET_STS_ERROR THEN
366 RAISE OKL_API.G_EXCEPTION_ERROR;
367 END IF;
368
369 x_return_status := l_return_status;
370 EXCEPTION
371
372 WHEN OKL_API.G_EXCEPTION_ERROR THEN
373
374 IF p_transaction_control = G_TRUE THEN
375 ROLLBACK TO l_program_name;
376 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
377 END IF;
378
379 x_return_status := G_RET_STS_ERROR;
380
381 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
382
383 IF p_transaction_control = G_TRUE THEN
384 ROLLBACK TO l_program_name;
385 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
386 END IF;
387
388 x_return_status := G_RET_STS_UNEXP_ERROR;
389
390 WHEN OTHERS THEN
391
392 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
393 p_msg_name => G_DB_ERROR,
394 p_token1 => G_PROG_NAME_TOKEN,
395 p_token1_value => l_api_name,
396 p_token2 => G_SQLCODE_TOKEN,
397 p_token2_value => sqlcode,
398 p_token3 => G_SQLERRM_TOKEN,
399 p_token3_value => sqlerrm);
400
401 IF p_transaction_control = G_TRUE THEN
402 ROLLBACK TO l_program_name;
403 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
404 END IF;
405
406 x_return_status := G_RET_STS_UNEXP_ERROR;
407
408 END update_lease_opp;
409
410
411 ------------------------------------
412 -- PROCEDURE cancel_lease_opp_childs
413 ------------------------------------
414 PROCEDURE cancel_lease_opp_childs (
415 p_lease_opp_id IN NUMBER
416 ,x_return_status OUT NOCOPY VARCHAR2
417 ,x_msg_count OUT NOCOPY NUMBER
418 ,x_msg_data OUT NOCOPY VARCHAR2
419 ) IS
420
421 l_program_name CONSTANT VARCHAR2(30) := 'cancel_lease_opp_childs';
422 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
423
424 i PLS_INTEGER;
425 l_return_status VARCHAR2(1);
426 l_del_lease_qte_tbl okl_lease_quote_pvt.lease_qte_tbl_type;
427
428 CURSOR c_lsq IS
429 SELECT lsq.id
430 FROM okl_lease_quotes_b lsq
431 WHERE lsq.parent_object_code = 'LEASEOPP'
432 AND lsq.parent_object_id = p_lease_opp_id;
433
434 CURSOR c_qqh IS
435 SELECT qqh.id
436 FROM okl_quick_quotes_b qqh
437 WHERE lease_opportunity_id = p_lease_opp_id;
438
439 BEGIN
440
441 FOR l_qqh IN c_qqh LOOP
442
443 DELETE FROM okl_quick_quote_lines_tl WHERE id IN (SELECT id FROM okl_quick_quote_lines_b WHERE quick_quote_id = l_qqh.id);
444 DELETE FROM okl_quick_quote_lines_b WHERE quick_quote_id = l_qqh.id;
445 DELETE FROM okl_quick_quotes_b WHERE id = l_qqh.id;
446 DELETE FROM okl_quick_quotes_tl WHERE id = l_qqh.id;
447
448 END LOOP;
449
450 FOR l_lsq IN c_lsq LOOP
451 i := i + 1;
452 l_del_lease_qte_tbl(i).id := l_lsq.id;
453 END LOOP;
454
455 IF l_del_lease_qte_tbl.COUNT > 0 THEN
456
457 okl_lease_quote_pvt.cancel_lease_qte(
458 p_api_version => G_API_VERSION
459 ,p_init_msg_list => G_TRUE
460 ,p_transaction_control => G_TRUE
461 ,x_return_status => l_return_status
462 ,x_msg_count => x_msg_count
463 ,x_msg_data => x_msg_data
464 ,p_lease_qte_tbl => l_del_lease_qte_tbl);
465
466
467 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
468 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
469 ELSIF l_return_status = G_RET_STS_ERROR THEN
470 RAISE OKL_API.G_EXCEPTION_ERROR;
471 END IF;
472
473 END IF;
474
475 x_return_status := G_RET_STS_SUCCESS;
476
477 EXCEPTION
478
479 WHEN OKL_API.G_EXCEPTION_ERROR THEN
480
481 x_return_status := G_RET_STS_ERROR;
482
483 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
484
485 x_return_status := G_RET_STS_UNEXP_ERROR;
486
487 WHEN OTHERS THEN
488
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_api_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 cancel_lease_opp_childs;
501
502
503 ------------------------------
504 -- PROCEDURE cancel_lease_opp
505 ------------------------------
506 PROCEDURE cancel_lease_opp (p_api_version IN NUMBER,
507 p_init_msg_list IN VARCHAR2,
508 p_transaction_control IN VARCHAR2,
509 p_lease_opp_id IN NUMBER,
510 x_return_status OUT NOCOPY VARCHAR2,
511 x_msg_count OUT NOCOPY NUMBER,
512 x_msg_data OUT NOCOPY VARCHAR2) IS
513
514 l_program_name CONSTANT VARCHAR2(30) := 'cancel_lease_opp';
515 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
516
517 l_lopv_rec lease_opp_rec_type;
518 q_lopv_rec lease_opp_rec_type;
519
520 i PLS_INTEGER;
521
522 l_return_status VARCHAR2(1);
523
524 BEGIN
525
526 IF p_transaction_control = G_TRUE THEN
527 SAVEPOINT l_program_name;
528 END IF;
529
530 IF p_init_msg_list = G_TRUE THEN
531 FND_MSG_PUB.initialize;
532 END IF;
533
534
535 l_lopv_rec.id := p_lease_opp_id;
536 l_lopv_rec.status := 'CANCELLED';
537
538 Select object_version_number
539 into l_lopv_rec.object_version_number
540 from Okl_lease_opportunities_b
541 where id = p_lease_opp_id;
542
543 update_lease_opp (p_api_version => p_api_version
544 ,p_init_msg_list => p_init_msg_list
545 ,p_transaction_control => p_transaction_control
546 ,p_lease_opp_rec => l_lopv_rec
547 ,x_lease_opp_rec => q_lopv_rec
548 ,x_return_status => l_return_status
549 ,x_msg_count => x_msg_count
550 ,x_msg_data => x_msg_data);
551
552
553 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
554 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
555 ELSIF l_return_status = G_RET_STS_ERROR THEN
556 RAISE OKL_API.G_EXCEPTION_ERROR;
557 END IF;
558
559 -- Handle Subsidy pool usage
560 okl_lease_quote_subpool_pvt.process_cancel_leaseopp(
561 p_api_version => G_API_VERSION
562 ,p_init_msg_list => G_TRUE
563 ,p_transaction_control => G_TRUE
564 ,p_parent_object_id => p_lease_opp_id
565 ,x_return_status => l_return_status
566 ,x_msg_count => x_msg_count
567 ,x_msg_data => x_msg_data);
568 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
569 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
570 ELSIF l_return_status = G_RET_STS_ERROR THEN
571 RAISE OKL_API.G_EXCEPTION_ERROR;
572 END IF;
573
574 x_return_status := G_RET_STS_SUCCESS;
575
576 EXCEPTION
577
578 WHEN OKL_API.G_EXCEPTION_ERROR THEN
579
580 IF p_transaction_control = G_TRUE THEN
581 ROLLBACK TO l_program_name;
582 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
583 END IF;
584
585 x_return_status := G_RET_STS_ERROR;
586
587 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
588
589 IF p_transaction_control = G_TRUE THEN
590 ROLLBACK TO l_program_name;
591 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
592 END IF;
593
594 x_return_status := G_RET_STS_UNEXP_ERROR;
595
596 WHEN OTHERS THEN
597
598 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
599 p_msg_name => G_DB_ERROR,
600 p_token1 => G_PROG_NAME_TOKEN,
601 p_token1_value => l_api_name,
602 p_token2 => G_SQLCODE_TOKEN,
603 p_token2_value => sqlcode,
604 p_token3 => G_SQLERRM_TOKEN,
605 p_token3_value => sqlerrm);
606
607 IF p_transaction_control = G_TRUE THEN
608 ROLLBACK TO l_program_name;
609 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
610 END IF;
611
612 x_return_status := G_RET_STS_UNEXP_ERROR;
613
614 END cancel_lease_opp;
615
616
617 -----------------------------------
618 -- PROCEDURE defaults_for_lease_opp
619 -----------------------------------
620 PROCEDURE defaults_for_lease_opp (p_api_version IN NUMBER,
621 p_init_msg_list IN VARCHAR2,
622 p_transaction_control IN VARCHAR2,
623 p_lease_opp_rec IN lease_opp_rec_type,
624 p_user_id IN VARCHAR2,
625 x_sales_rep_name OUT NOCOPY VARCHAR2,
626 x_lease_opp_rec OUT NOCOPY lease_opp_rec_type,
627 x_dff_name OUT NOCOPY VARCHAR2,
628 x_return_status OUT NOCOPY VARCHAR2,
629 x_msg_count OUT NOCOPY NUMBER,
630 x_msg_data OUT NOCOPY VARCHAR2) IS
631
632 l_program_name CONSTANT VARCHAR2(30) := 'defaults_for_lease_opp';
633 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
634
635 l_lease_opp_rec lease_opp_rec_type;
636 l_sales_rep_name VARCHAR2(240);
637 l_dff_name VARCHAR2(40);
638
639 l_return_status VARCHAR2(1);
640
641 CURSOR c_prop_tax_dflts IS
642 SELECT property_tax_applicable,
643 bill_property_tax
644 FROM okl_property_tax_setups
645 WHERE org_id = mo_global.get_current_org_id();
646
647 CURSOR c_reference_number IS
648 SELECT okl_util.get_next_seq_num('OKL_LOP_REF_SEQ','OKL_LEASE_OPPORTUNITIES_B','REFERENCE_NUMBER') FROM DUAL;--Bug 7022258-Modified by kkorrapo
649
650 CURSOR c_sales_rep_dflts IS
651 SELECT rep.salesrep_id sales_rep_id
652 ,rep.name sales_rep_name
653 FROM jtf_rs_salesreps rep
654 ,jtf_rs_resource_extns res
655 WHERE rep.org_id = mo_global.get_current_org_id()
656 AND rep.resource_id = res.resource_id
657 AND res.user_id = G_USER_ID;
658
659 CURSOR c_dff_name IS
660 SELECT descriptive_flexfield_name
661 FROM fnd_descriptive_flexs
662 WHERE table_application_id = 540
663 AND application_table_name = 'OKL_LEASE_OPPORTUNITIES_B'
664 AND context_column_name = 'ATTRIBUTE_CATEGORY'
665 AND freeze_flex_definition_flag = 'Y';
666
667 BEGIN
668
669 IF p_transaction_control = G_TRUE THEN
670 SAVEPOINT l_program_name;
671 END IF;
672
673 IF p_init_msg_list = G_TRUE THEN
674 FND_MSG_PUB.initialize;
675 END IF;
676
677 OPEN c_functional_currency;
678 FETCH c_functional_currency INTO l_lease_opp_rec.currency_code;
679 CLOSE c_functional_currency;
680
681 OPEN c_prop_tax_dflts;
682 FETCH c_prop_tax_dflts INTO
683 l_lease_opp_rec.property_tax_applicable,
684 l_lease_opp_rec.property_tax_billing_type;
685 CLOSE c_prop_tax_dflts;
686
687 OPEN c_reference_number;
688 FETCH c_reference_number INTO l_lease_opp_rec.reference_number;
689 CLOSE c_reference_number;
690
691 OPEN c_sales_rep_dflts;
692 FETCH c_sales_rep_dflts INTO
693 l_lease_opp_rec.sales_rep_id,
694 l_sales_rep_name;
695 CLOSE c_sales_rep_dflts;
696
697 OPEN c_dff_name;
698 FETCH c_dff_name INTO l_dff_name;
699 CLOSE c_dff_name;
700
701 x_sales_rep_name := l_sales_rep_name;
702 x_dff_name := l_dff_name;
703 x_lease_opp_rec := l_lease_opp_rec;
704
705 x_return_status := G_RET_STS_SUCCESS;
706
707 EXCEPTION
708
709 WHEN OKL_API.G_EXCEPTION_ERROR THEN
710
711 IF p_transaction_control = G_TRUE THEN
712 ROLLBACK TO l_program_name;
713 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
714 END IF;
715
716 x_return_status := G_RET_STS_ERROR;
717
718 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
719
720 IF p_transaction_control = G_TRUE THEN
721 ROLLBACK TO l_program_name;
722 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
723 END IF;
724
725 x_return_status := G_RET_STS_UNEXP_ERROR;
726
727 WHEN OTHERS THEN
728
729 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
730 p_msg_name => G_DB_ERROR,
731 p_token1 => G_PROG_NAME_TOKEN,
732 p_token1_value => l_api_name,
733 p_token2 => G_SQLCODE_TOKEN,
734 p_token2_value => sqlcode,
735 p_token3 => G_SQLERRM_TOKEN,
736 p_token3_value => sqlerrm);
737
738 IF p_transaction_control = G_TRUE THEN
739 ROLLBACK TO l_program_name;
740 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
741 END IF;
742
743 x_return_status := G_RET_STS_UNEXP_ERROR;
744
745 END defaults_for_lease_opp;
746
747
748 --------------------------------
749 -- PROCEDURE duplicate_lease_opp
750 --------------------------------
751 PROCEDURE duplicate_lease_opp (p_api_version IN NUMBER,
752 p_init_msg_list IN VARCHAR2,
753 p_transaction_control IN VARCHAR2,
754 p_source_leaseopp_id IN NUMBER,
755 p_lease_opp_rec IN lease_opp_rec_type,
756 x_lease_opp_rec OUT NOCOPY lease_opp_rec_type,
757 x_return_status OUT NOCOPY VARCHAR2,
758 x_msg_count OUT NOCOPY NUMBER,
759 x_msg_data OUT NOCOPY VARCHAR2) IS
760
761 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_lease_opp';
762 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
763
764 lx_lease_qte_rec okl_lease_quote_pvt.lease_qte_rec_type;
765
766 BEGIN
767
768 IF p_transaction_control = G_TRUE THEN
769 SAVEPOINT l_program_name;
770 END IF;
771
772 IF p_init_msg_list = G_TRUE THEN
773 FND_MSG_PUB.initialize;
774 END IF;
775
776 create_lease_opp (
777 p_api_version => G_API_VERSION
778 ,p_init_msg_list => G_TRUE
779 ,p_transaction_control => G_TRUE
780 ,p_lease_opp_rec => p_lease_opp_rec
781 ,p_quick_quote_id => NULL
782 ,x_lease_opp_rec => x_lease_opp_rec
783 ,x_return_status => x_return_status
784 ,x_msg_count => x_msg_count
785 ,x_msg_data => x_msg_data
786 );
787
788 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
789 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
790 ELSIF x_return_status = G_RET_STS_ERROR THEN
791 RAISE OKL_API.G_EXCEPTION_ERROR;
792 END IF;
793
794 -- Duplicate Estimates
795 okl_quick_quotes_pvt.duplicate_estimate(
796 p_api_version => G_API_VERSION
797 ,p_init_msg_list => G_TRUE
798 ,source_lopp_id => p_source_leaseopp_id
799 ,target_lopp_id => x_lease_opp_rec.id
800 ,x_return_status => x_return_status
801 ,x_msg_count => x_msg_count
802 ,x_msg_data => x_msg_data );
803
804 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
805 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
806 ELSIF x_return_status = G_RET_STS_ERROR THEN
807 RAISE OKL_API.G_EXCEPTION_ERROR;
808 END IF;
809
810 -- Duplicate Quotes
811 okl_lease_quote_pvt.duplicate_quotes (
812 p_api_version => G_API_VERSION
813 ,p_init_msg_list => G_TRUE
814 ,p_transaction_control => G_TRUE
815 ,p_source_leaseopp_id => p_source_leaseopp_id
816 ,p_target_leaseopp_id => x_lease_opp_rec.id
817 ,x_return_status => x_return_status
818 ,x_msg_count => x_msg_count
819 ,x_msg_data => x_msg_data );
820
821 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
822 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
823 ELSIF x_return_status = G_RET_STS_ERROR THEN
824 RAISE OKL_API.G_EXCEPTION_ERROR;
825 END IF;
826
827 EXCEPTION
828
829 WHEN OKL_API.G_EXCEPTION_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_ERROR;
837
838 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
839
840 IF p_transaction_control = G_TRUE THEN
841 ROLLBACK TO l_program_name;
842 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
843 END IF;
844
845 x_return_status := G_RET_STS_UNEXP_ERROR;
846
847 WHEN OTHERS THEN
848
849 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
850 p_msg_name => G_DB_ERROR,
851 p_token1 => G_PROG_NAME_TOKEN,
852 p_token1_value => l_api_name,
853 p_token2 => G_SQLCODE_TOKEN,
854 p_token2_value => sqlcode,
855 p_token3 => G_SQLERRM_TOKEN,
856 p_token3_value => sqlerrm);
857
858 IF p_transaction_control = G_TRUE THEN
859 ROLLBACK TO l_program_name;
860 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
861 END IF;
862
863 x_return_status := G_RET_STS_UNEXP_ERROR;
864
865 END duplicate_lease_opp;
866
867 END OKL_LEASE_OPPORTUNITY_PVT;