[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_PVT AS
2 /* $Header: OKLRLSQB.pls 120.54.12020000.2 2012/08/07 12:14:52 bkatraga ship $ */
3
4 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(25) := 'OKL_QUOTE_UNEXP_ERROR';
5
6 /*========================================================================
7 | PUBLIC PROCEDURE delete_quote_tax_fee
8 |
9 | DESCRIPTION
10 | This procedure deletes the fee created as part of tax creation,
11 | if the upfront tax treatment is modified from 'CAPITALIZED'/'FINANCED' to
12 \ 'BILLED'.
13 |
14 | CALLED FROM Sales component
15 |
16 |
17 | CALLS PROCEDURES/FUNCTIONS
18 |
19 |
20 | PARAMETERS
21 | p_quote_id -- Quote Identifier
22 |
23 | KNOWN ISSUES
24 |
25 | NOTES
26 |
27 |
28 | MODIFICATION HISTORY
29 | Date Author Description of Changes
30 | 03-OCT-07 RRAVIKIR Created
31 |
32 *=======================================================================*/
33 PROCEDURE delete_quote_tax_fee(p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 p_quote_id IN NUMBER) IS
39
40 l_program_name CONSTANT VARCHAR2(30) := 'delete_quote_tax_fee';
41 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
42
43 l_tax_treatment VARCHAR2(30);
44 l_fee_id NUMBER;
45 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
46
47 CURSOR l_get_tax_treatment(cp_quote_id IN NUMBER) IS
48 SELECT upfront_tax_treatment
49 FROM okl_lease_quotes_b
50 WHERE id = cp_quote_id;
51
52 CURSOR l_check_tax_fee_exists(cp_quote_id IN NUMBER) IS
53 SELECT id
54 FROM okl_fees_b
55 WHERE parent_object_id = cp_quote_id
56 AND parent_object_code = 'LEASEQUOTE'
57 AND fee_purpose_code = 'SALESTAX';
58
59 BEGIN
60
61 IF p_init_msg_list = G_TRUE THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64
65 IF p_quote_id IS NULL THEN
66 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
67 p_msg_name => 'OKL_INVALID_SALES_QUOTE');
68 RAISE OKL_API.G_EXCEPTION_ERROR;
69 END IF;
70
71 OPEN l_get_tax_treatment(p_quote_id);
72 FETCH l_get_tax_treatment INTO l_tax_treatment;
73 CLOSE l_get_tax_treatment;
74
75 IF (l_tax_treatment = 'BILLED') THEN
76
77 -- Check if the tax fee exists on the quote
78 OPEN l_check_tax_fee_exists(p_quote_id);
79 FETCH l_check_tax_fee_exists INTO l_fee_id;
80 CLOSE l_check_tax_fee_exists;
81
82 IF (l_fee_id IS NOT NULL) THEN -- Tax fee exist, delete it
83
84 OKL_LEASE_QUOTE_FEE_PVT.delete_fee ( p_api_version => p_api_version
85 ,p_init_msg_list => p_init_msg_list
86 ,p_transaction_control => 'T'
87 ,p_fee_id => l_fee_id
88 ,x_return_status => l_return_status
89 ,x_msg_count => x_msg_count
90 ,x_msg_data => x_msg_data );
91
92 IF(l_return_status = G_RET_STS_UNEXP_ERROR) THEN
93 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
94 ELSIF(l_return_status = G_RET_STS_ERROR) THEN
95 RAISE OKL_API.G_EXCEPTION_ERROR;
96 END IF;
97
98 END IF;
99
100 END IF;
101
102 x_return_status := l_return_status;
103
104 EXCEPTION
105
106 WHEN OKL_API.G_EXCEPTION_ERROR THEN
107
108 x_return_status := G_RET_STS_ERROR;
109
110 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
111
112 x_return_status := G_RET_STS_UNEXP_ERROR;
113
114 WHEN OTHERS THEN
115 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
116 p_msg_name => G_DB_ERROR,
117 p_token1 => G_PROG_NAME_TOKEN,
118 p_token1_value => l_api_name,
119 p_token2 => G_SQLCODE_TOKEN,
120 p_token2_value => sqlcode,
121 p_token3 => G_SQLERRM_TOKEN,
122 p_token3_value => sqlerrm);
123
124 x_return_status := G_RET_STS_UNEXP_ERROR;
125
126 END delete_quote_tax_fee;
127
128 -- Added Bug # 5647107 ssdeshpa start
129 -----------------------------------
130 -- PROCEDURE validate_le_id
131 -----------------------------------
132 PROCEDURE validate_le_id(p_le_id IN NUMBER ,
133 p_parent_obj_code IN VARCHAR2,
134 x_return_status OUT NOCOPY VARCHAR2) IS
135
136 l_program_name CONSTANT VARCHAR2(30) := 'validate_le_id';
137 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
138 l_ou_tax_upfront_yn VARCHAR2(1);
139 l_err_msg VARCHAR2(80);
140
141 CURSOR l_systemparams_csr IS
142 SELECT NVL(tax_upfront_yn,'N')
143 FROM OKL_SYSTEM_PARAMS;
144
145 BEGIN
146 OPEN l_systemparams_csr;
147 FETCH l_systemparams_csr INTO l_ou_tax_upfront_yn;
148 CLOSE l_systemparams_csr;
149
150 IF(l_ou_tax_upfront_yn = 'Y') THEN
151 IF(p_le_id IS NULL) THEN
152 IF(p_parent_obj_code = 'LEASEAPP') THEN
153 l_err_msg := 'OKL_SO_LSE_APP_LE_ERR';
154 ELSE
155 l_err_msg := 'OKL_LEASE_QUOTE_LE_ERR';
156 END IF;
157 OKL_API.SET_MESSAGE (
158 p_app_name => G_APP_NAME
159 ,p_msg_name => l_err_msg);
160 RAISE OKL_API.G_EXCEPTION_ERROR;
161 END IF;
162 END IF;
163
164 x_return_status := G_RET_STS_SUCCESS;
165 EXCEPTION
166
167 WHEN OKL_API.G_EXCEPTION_ERROR THEN
168
169 x_return_status := G_RET_STS_ERROR;
170
171 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
172
173 x_return_status := G_RET_STS_UNEXP_ERROR;
174
175 WHEN OTHERS THEN
176
177 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
178 p_msg_name => G_DB_ERROR,
179 p_token1 => G_PROG_NAME_TOKEN,
180 p_token1_value => l_api_name,
181 p_token2 => G_SQLCODE_TOKEN,
182 p_token2_value => sqlcode,
183 p_token3 => G_SQLERRM_TOKEN,
184 p_token3_value => sqlerrm);
185
186 x_return_status := G_RET_STS_UNEXP_ERROR;
187 END validate_le_id;
188 -- Added Bug # 5647107 ssdeshpa end
189 -----------------------------------
190 -- FUNCTION is_pricing_method_equal
191 -----------------------------------
192 FUNCTION is_pricing_method_equal(p_source_quote_id IN NUMBER,
193 p_target_pricing_type IN VARCHAR2)
194 RETURN VARCHAR2 IS
195
196 lv_source_pricing_type VARCHAR2(15);
197 BEGIN
198 select pricing_method
199 into lv_source_pricing_type
200 from okl_lease_quotes_b
201 where id = p_source_quote_id;
202
203 IF (lv_source_pricing_type = p_target_pricing_type) THEN
204 RETURN 'Y';
205 ELSE
206 RETURN 'N';
207 END IF;
208 END is_pricing_method_equal;
209
210 ---------------------------------------
211 -- FUNCTION copy_quote_payments_allowed
212 ---------------------------------------
213 FUNCTION copy_quote_payments_allowed(p_source_quote_id IN NUMBER,
214 p_target_pdt_id IN NUMBER,
215 p_target_exp_start_date IN DATE)
216 RETURN VARCHAR2 IS
217
218 ln_source_pdt_id NUMBER;
219 ld_source_exp_date DATE;
220 BEGIN
221 select product_id, expected_start_date
222 into ln_source_pdt_id, ld_source_exp_date
223 from okl_lease_quotes_b
224 where id = p_source_quote_id;
225
226 IF (ln_source_pdt_id = p_target_pdt_id AND ld_source_exp_date = p_target_exp_start_date) THEN
227 RETURN 'Y';
228 ELSE
229 RETURN 'N';
230 END IF;
231 END copy_quote_payments_allowed;
232
233 FUNCTION is_contterm_payperiod_equal(p_contract_start_date IN DATE,
234 p_contract_term IN NUMBER, p_quote_id IN NUMBER)
235 RETURN VARCHAR2 IS
236 -- Cursor to fetch cashflow header id
237 CURSOR lq_cash_flows_csr(p_quote_id NUMBER) IS
238 SELECT cf.id caf_id
239 FROM OKL_CASH_FLOWS cf,
240 OKL_CASH_FLOW_OBJECTS cfo
241 WHERE cf.cfo_id = cfo.id
242 AND cfo.source_table = 'OKL_LEASE_QUOTES_B'
243 AND cfo.source_id = p_quote_id;
244
245 -- Cursor to fetch the Cash Flow Details
246 CURSOR lq_cash_flow_det_csr(p_caf_id NUMBER) IS
247 SELECT fqy_code, number_of_periods,
248 stub_days, start_date
249 FROM OKL_CASH_FLOW_LEVELS
250 WHERE caf_id = p_caf_id
251 ORDER BY start_date;
252
253 l_cur_fetch NUMBER;
254 l_caf_id NUMBER;
255 l_mpp NUMBER;
256 l_end_date DATE := FND_API.G_MISS_DATE;
257 l_next_start_date DATE := FND_API.G_MISS_DATE;
258 l_contract_end_date DATE := FND_API.G_MISS_DATE;
259
260 TYPE dat_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
261 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
262 TYPE vr1_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
263
264 l_fqy_code vr1_tbl_type;
265 l_stub_days num_tbl_type;
266 l_start_date dat_tbl_type;
267 l_number_of_periods num_tbl_type;
268
269
270 BEGIN
271
272 OPEN lq_cash_flows_csr(p_quote_id);
273 FETCH lq_cash_flows_csr INTO l_caf_id;
274 CLOSE lq_cash_flows_csr;
275
276 OPEN lq_cash_flow_det_csr(l_caf_id);
277 FETCH lq_cash_flow_det_csr BULK COLLECT INTO
278 l_fqy_code, l_number_of_periods, l_stub_days, l_start_date;
279 CLOSE lq_cash_flow_det_csr;
280
281 l_next_start_date := p_contract_start_date;
282
283 l_cur_fetch := l_number_of_periods.COUNT;
284 IF l_cur_fetch > 0 THEN
285 FOR i IN 1..l_cur_fetch LOOP
286
287 IF l_fqy_code(i) = 'A' THEN l_mpp := 12;
288 ELSIF l_fqy_code(i) = 'S' THEN l_mpp := 6;
289 ELSIF l_fqy_code(i) = 'Q' THEN l_mpp := 3;
290 ELSIF l_fqy_code(i) = 'M' THEN l_mpp := 1;
291 ELSE NULL;
292 END IF;
293
294 IF l_stub_days(i) IS NOT NULL THEN
295 l_end_date := l_next_start_date + l_stub_days(i) - 1;
296 ELSE
297 l_end_date := ADD_MONTHS(l_next_start_date, l_mpp*l_number_of_periods(i)) - 1;
298 END IF;
299
300 l_start_date(i) := l_next_start_date;
301 l_next_start_date := l_end_date + 1;
302 END LOOP;
303 END IF;
304
305 l_contract_end_date := ADD_MONTHS(p_contract_start_date, p_contract_term) - 1;
306 IF l_end_date > l_contract_end_date THEN
307 RETURN 'N';
308 ELSE
309 RETURN 'Y';
310 END IF;
311
312 END is_contterm_payperiod_equal;
313
314 -------------------------------
315 -- PROCEDURE validate_lease_qte
316 -------------------------------
317 PROCEDURE validate_lease_qte (p_lease_qte_rec IN lease_qte_rec_type,
318 x_return_status OUT NOCOPY VARCHAR2) IS
319
320 l_program_name CONSTANT VARCHAR2(30) := 'validate_lease_opp';
321 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
322
323 CURSOR chk_uniquness IS
324 SELECT '1'
325 FROM okl_lease_quotes_b
326 WHERE reference_number = p_lease_qte_rec.reference_number
327 AND id <> NVL(p_lease_qte_rec.id, -9999);
328
329 CURSOR chk_parent_dates_leaseopp IS
330 SELECT TRUNC(valid_from)
331 FROM okl_lease_opportunities_b
332 WHERE id = p_lease_qte_rec.parent_object_id;
333
334 CURSOR chk_parent_dates_leaseapp IS
335 SELECT TRUNC(valid_from)
336 FROM okl_lease_applications_b
337 WHERE id = p_lease_qte_rec.parent_object_id;
338
339 l_refno_unq_chk NUMBER;
340 l_parent_valid_from DATE;
341 l_format_mask VARCHAR2(50);
342 l_formatted_date VARCHAR2(50);
343
344 BEGIN
345
346 OPEN chk_uniquness;
347 FETCH chk_uniquness INTO l_refno_unq_chk;
348 CLOSE chk_uniquness;
349
350 IF l_refno_unq_chk IS NOT NULL THEN
351 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
352 p_msg_name => 'OKL_REFNO_UNIQUE_CHECK');
353 RAISE OKL_API.G_EXCEPTION_ERROR;
354 END IF;
355
356 IF p_lease_qte_rec.parent_object_code IS NULL THEN
357 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
358 p_msg_name => 'OKL_INVALID_SALES_QUOTE');
359 RAISE OKL_API.G_EXCEPTION_ERROR;
360 END IF;
361
362 IF (p_lease_qte_rec.parent_object_code = 'LEASEOPP') THEN
363 OPEN chk_parent_dates_leaseopp;
364 FETCH chk_parent_dates_leaseopp INTO l_parent_valid_from;
365 CLOSE chk_parent_dates_leaseopp;
366 ELSIF (p_lease_qte_rec.parent_object_code = 'LEASEAPP') THEN
367 OPEN chk_parent_dates_leaseapp;
368 FETCH chk_parent_dates_leaseapp INTO l_parent_valid_from;
369 CLOSE chk_parent_dates_leaseapp;
370 END IF;
371
372 l_format_mask := NVL(FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'), 'DD-MON-YYYY');
373 l_formatted_date := TO_CHAR(l_parent_valid_from , l_format_mask);
374
375 IF p_lease_qte_rec.expected_start_date < l_parent_valid_from THEN
376 OKL_API.SET_MESSAGE (
377 p_app_name => G_APP_NAME
378 ,p_msg_name => 'OKL_QUOTE_INVALID_START_DATE'
379 ,p_token1 => 'LEASEOPP_DATE'
380 ,p_token1_value => l_formatted_date
381 );
382 RAISE OKL_API.G_EXCEPTION_ERROR;
383 END IF;
384
385 IF p_lease_qte_rec.expected_funding_date < l_parent_valid_from THEN
386 OKL_API.SET_MESSAGE (
387 p_app_name => G_APP_NAME
388 ,p_msg_name => 'OKL_QUOTE_INVALID_FUNDING_DATE'
389 ,p_token1 => 'LEASEOPP_DATE'
390 ,p_token1_value => l_formatted_date
391 );
392 RAISE OKL_API.G_EXCEPTION_ERROR;
393 END IF;
394
395 IF p_lease_qte_rec.expected_delivery_date < l_parent_valid_from THEN
396 IF (p_lease_qte_rec.parent_object_code = 'LEASEOPP') THEN
397 OKL_API.SET_MESSAGE (
398 p_app_name => G_APP_NAME
399 ,p_msg_name => 'OKL_QUOTE_INVALID_DELV_DATE'
400 ,p_token1 => 'LEASEOPP_DATE'
401 ,p_token1_value => l_formatted_date
402 );
403 ELSIF (p_lease_qte_rec.parent_object_code = 'LEASEAPP') THEN
404 OKL_API.SET_MESSAGE (
405 p_app_name => G_APP_NAME
406 ,p_msg_name => 'OKL_QTE_LAP_INVALID_DELV_DATE'
407 ,p_token1 => 'LEASEAPP_DATE'
408 ,p_token1_value => l_formatted_date
409 );
410 end if;
411 RAISE OKL_API.G_EXCEPTION_ERROR;
412
413 END IF;
414
415 IF p_lease_qte_rec.term <= 0 THEN
416 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_INVALID_QUOTE_TERM');
417 RAISE OKL_API.G_EXCEPTION_ERROR;
418 END IF;
419
420 IF (p_lease_qte_rec.term <> TRUNC(p_lease_qte_rec.term)) THEN
421 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_INVALID_QUOTE_TERM2');
422 RAISE OKL_API.G_EXCEPTION_ERROR;
423 END IF;
424 --asawanka bug 4923624 changes start
425 IF (p_lease_qte_rec.valid_from IS NULL OR p_lease_qte_rec.valid_from = OKL_API.G_MISS_DATE )
426 AND(p_lease_qte_rec.valid_to IS NOT NULL AND p_lease_qte_rec.valid_to <> OKL_API.G_MISS_DATE )THEN
427 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_REQUIRED_VALID_FROM');
428 RAISE OKL_API.G_EXCEPTION_ERROR;
429 END IF;
430
431 IF p_lease_qte_rec.valid_to <> OKL_API.G_MISS_DATE AND p_lease_qte_rec.valid_from <> OKL_API.G_MISS_DATE THEN
432 IF p_lease_qte_rec.valid_to < p_lease_qte_rec.valid_from THEN
433 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_INVALID_VALID_TO');
434 RAISE OKL_API.G_EXCEPTION_ERROR;
435 END IF;
436 END IF;
437 --asawanka bug 4923624 changes end
438
439 --Fixed Bug # 5647107 added Bug ssdeshpa start
440 validate_le_id(p_le_id => p_lease_qte_rec.legal_entity_id,
441 p_parent_obj_code => p_lease_qte_rec.parent_object_code,
442 x_return_status => x_return_status );
443 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
444 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
445 ELSIF x_return_status = G_RET_STS_ERROR THEN
446 RAISE OKL_API.G_EXCEPTION_ERROR;
447 END IF;
448 --Fixed Bug # 5647107 added Bug ssdeshpa end
449
450 x_return_status := G_RET_STS_SUCCESS;
451
452 EXCEPTION
453
454 WHEN OKL_API.G_EXCEPTION_ERROR THEN
455
456 x_return_status := G_RET_STS_ERROR;
457
458 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
459
460 x_return_status := G_RET_STS_UNEXP_ERROR;
461
462 WHEN OTHERS THEN
463
464 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
465 p_msg_name => G_DB_ERROR,
466 p_token1 => G_PROG_NAME_TOKEN,
467 p_token1_value => l_api_name,
468 p_token2 => G_SQLCODE_TOKEN,
469 p_token2_value => sqlcode,
470 p_token3 => G_SQLERRM_TOKEN,
471 p_token3_value => sqlerrm);
472
473 x_return_status := G_RET_STS_UNEXP_ERROR;
474
475 END validate_lease_qte;
476
477 -----------------------------------
478 -- PROCEDURE populate_quote_attribs
479 -----------------------------------
480 PROCEDURE populate_quote_attribs (
481 p_source_quote_id IN NUMBER
482 ,x_quote_rec IN OUT NOCOPY lease_qte_rec_type
483 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
484
485 l_program_name CONSTANT VARCHAR2(30) := 'populate_quote_attribs';
486 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
487 --Bug # 5021937 ssdeshpa start
488 BEGIN
489
490 SELECT
491 structured_pricing
492 ,line_level_pricing
493 ,lease_rate_factor
494 ,target_rate_type
495 ,target_rate
496 ,target_amount
497 ,target_frequency
498 ,target_arrears_yn
499 ,target_periods
500 ,rate_card_id
501 ,rate_template_id
502 ,lease_rate_factor
503 INTO
504 x_quote_rec.structured_pricing
505 ,x_quote_rec.line_level_pricing
506 ,x_quote_rec.lease_rate_factor
507 ,x_quote_rec.target_rate_type
508 ,x_quote_rec.target_rate
509 ,x_quote_rec.target_amount
510 ,x_quote_rec.target_frequency
511 ,x_quote_rec.target_arrears_yn
512 ,x_quote_rec.target_periods
513 ,x_quote_rec.rate_card_id
514 ,x_quote_rec.rate_template_id
515 ,x_quote_rec.lease_rate_factor
516 FROM okl_lease_quotes_v
517 WHERE id = p_source_quote_id;
518 --Bug # 5021937 ssdeshpa end
519
520 x_return_status := G_RET_STS_SUCCESS;
521
522 EXCEPTION
523
524 WHEN OKL_API.G_EXCEPTION_ERROR THEN
525 x_return_status := G_RET_STS_ERROR;
526
527 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
528 x_return_status := G_RET_STS_UNEXP_ERROR;
529
530 WHEN OTHERS THEN
531 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
532 p_msg_name => G_DB_ERROR,
533 p_token1 => G_PROG_NAME_TOKEN,
534 p_token1_value => l_program_name,
535 p_token2 => G_SQLCODE_TOKEN,
536 p_token2_value => sqlcode,
537 p_token3 => G_SQLERRM_TOKEN,
538 p_token3_value => sqlerrm);
539
540 x_return_status := G_RET_STS_UNEXP_ERROR;
541
542 END populate_quote_attribs;
543 ----------------------------------------------
544 -- PROCEDURE copy_yields added for bug 4936130
545 ----------------------------------------------
546 PROCEDURE copy_yields (
547 p_source_quote_id IN NUMBER
548 ,x_quote_rec IN OUT NOCOPY lease_qte_rec_type
549 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
550
551 l_program_name CONSTANT VARCHAR2(30) := 'copy_yields';
552 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
553
554 BEGIN
555
556 SELECT
557 iir,
558 booking_yield,
559 pirr,
560 airr,
561 sub_iir,
562 sub_booking_yield,
563 sub_pirr,
564 sub_airr
565 INTO
566 x_quote_rec.iir
567 ,x_quote_rec.booking_yield
568 ,x_quote_rec.pirr
569 ,x_quote_rec.airr
570 ,x_quote_rec.sub_iir
571 ,x_quote_rec.sub_booking_yield
572 ,x_quote_rec.sub_pirr
573 ,x_quote_rec.sub_airr
574 FROM okl_lease_quotes_v
575 WHERE id = p_source_quote_id;
576
577 x_return_status := G_RET_STS_SUCCESS;
578
579 EXCEPTION
580
581 WHEN OKL_API.G_EXCEPTION_ERROR THEN
582 x_return_status := G_RET_STS_ERROR;
583
584 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
585 x_return_status := G_RET_STS_UNEXP_ERROR;
586
587 WHEN OTHERS THEN
588 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
589 p_msg_name => G_DB_ERROR,
590 p_token1 => G_PROG_NAME_TOKEN,
591 p_token1_value => l_program_name,
592 p_token2 => G_SQLCODE_TOKEN,
593 p_token2_value => sqlcode,
594 p_token3 => G_SQLERRM_TOKEN,
595 p_token3_value => sqlerrm);
596
597 x_return_status := G_RET_STS_UNEXP_ERROR;
598
599 END copy_yields;
600
601 --------------------------
602 -- PROCEDURE get_quote_rec
603 --------------------------
604 PROCEDURE get_quote_rec ( p_quote_id IN NUMBER
605 ,x_quote_rec OUT NOCOPY lease_qte_rec_type
606 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
607
608 l_program_name CONSTANT VARCHAR2(30) := 'get_quote_rec';
609 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
610
611 BEGIN
612
613 SELECT
614 attribute_category
615 ,attribute1
616 ,attribute2
617 ,attribute3
618 ,attribute4
619 ,attribute5
620 ,attribute6
621 ,attribute7
622 ,attribute8
623 ,attribute9
624 ,attribute10
625 ,attribute11
626 ,attribute12
627 ,attribute13
628 ,attribute14
629 ,attribute15
630 ,reference_number
631 ,object_version_number
632 ,parent_object_id
633 ,parent_object_code
634 ,valid_from
635 ,valid_to
636 ,customer_bookclass
637 ,customer_taxowner
638 ,expected_start_date
639 ,expected_funding_date
640 ,expected_delivery_date
641 ,pricing_method
642 ,term
643 ,product_id
644 ,end_of_term_option_id
645 ,usage_category
646 ,usage_industry_class
647 ,usage_industry_code
648 ,usage_amount
649 ,usage_location_id
650 ,property_tax_applicable
651 ,property_tax_billing_type
652 ,upfront_tax_treatment
653 ,upfront_tax_stream_type
654 ,transfer_of_title
655 ,age_of_equipment
656 ,purchase_of_lease
657 ,sale_and_lease_back
658 ,interest_disclosed
659 ,target_rate_type
660 ,target_rate
661 ,target_amount
662 ,target_frequency
663 ,target_arrears_yn
664 ,target_periods
665 ,structured_pricing
666 ,line_level_pricing
667 ,lease_rate_factor
668 ,rate_card_id
669 ,rate_template_id
670 ,iir
671 ,booking_yield
672 ,pirr
673 ,airr
674 ,sub_iir
675 ,sub_booking_yield
676 ,sub_pirr
677 ,sub_airr
678 ,primary_quote
679 ,legal_entity_id
680 ,short_description
681 ,description
682 ,comments
683 INTO
684 x_quote_rec.attribute_category
685 ,x_quote_rec.attribute1
686 ,x_quote_rec.attribute2
687 ,x_quote_rec.attribute3
688 ,x_quote_rec.attribute4
689 ,x_quote_rec.attribute5
690 ,x_quote_rec.attribute6
691 ,x_quote_rec.attribute7
692 ,x_quote_rec.attribute8
693 ,x_quote_rec.attribute9
694 ,x_quote_rec.attribute10
695 ,x_quote_rec.attribute11
696 ,x_quote_rec.attribute12
697 ,x_quote_rec.attribute13
698 ,x_quote_rec.attribute14
699 ,x_quote_rec.attribute15
700 ,x_quote_rec.reference_number
701 ,x_quote_rec.object_version_number
702 ,x_quote_rec.parent_object_id
703 ,x_quote_rec.parent_object_code
704 ,x_quote_rec.valid_from
705 ,x_quote_rec.valid_to
706 ,x_quote_rec.customer_bookclass
707 ,x_quote_rec.customer_taxowner
708 ,x_quote_rec.expected_start_date
709 ,x_quote_rec.expected_funding_date
710 ,x_quote_rec.expected_delivery_date
711 ,x_quote_rec.pricing_method
712 ,x_quote_rec.term
713 ,x_quote_rec.product_id
714 ,x_quote_rec.end_of_term_option_id
715 ,x_quote_rec.usage_category
716 ,x_quote_rec.usage_industry_class
717 ,x_quote_rec.usage_industry_code
718 ,x_quote_rec.usage_amount
719 ,x_quote_rec.usage_location_id
720 ,x_quote_rec.property_tax_applicable
721 ,x_quote_rec.property_tax_billing_type
722 ,x_quote_rec.upfront_tax_treatment
723 ,x_quote_rec.upfront_tax_stream_type
724 ,x_quote_rec.transfer_of_title
725 ,x_quote_rec.age_of_equipment
726 ,x_quote_rec.purchase_of_lease
727 ,x_quote_rec.sale_and_lease_back
728 ,x_quote_rec.interest_disclosed
729 ,x_quote_rec.target_rate_type
730 ,x_quote_rec.target_rate
731 ,x_quote_rec.target_amount
732 ,x_quote_rec.target_frequency
733 ,x_quote_rec.target_arrears_yn
734 ,x_quote_rec.target_periods
735 ,x_quote_rec.structured_pricing
736 ,x_quote_rec.line_level_pricing
737 ,x_quote_rec.lease_rate_factor
738 ,x_quote_rec.rate_card_id
739 ,x_quote_rec.rate_template_id
740 ,x_quote_rec.iir
741 ,x_quote_rec.booking_yield
742 ,x_quote_rec.pirr
743 ,x_quote_rec.airr
744 ,x_quote_rec.sub_iir
745 ,x_quote_rec.sub_booking_yield
746 ,x_quote_rec.sub_pirr
747 ,x_quote_rec.sub_airr
748 ,x_quote_rec.primary_quote
749 ,x_quote_rec.legal_entity_id
750 ,x_quote_rec.short_description
751 ,x_quote_rec.description
752 ,x_quote_rec.comments
753 FROM okl_lease_quotes_v
754 WHERE id = p_quote_id;
755
756 x_return_status := G_RET_STS_SUCCESS;
757
758 EXCEPTION
759
760 WHEN OKL_API.G_EXCEPTION_ERROR THEN
761 x_return_status := G_RET_STS_ERROR;
762
763 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
764 x_return_status := G_RET_STS_UNEXP_ERROR;
765
766 WHEN OTHERS THEN
767 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
768 p_msg_name => G_DB_ERROR,
769 p_token1 => G_PROG_NAME_TOKEN,
770 p_token1_value => l_program_name,
771 p_token2 => G_SQLCODE_TOKEN,
772 p_token2_value => sqlcode,
773 p_token3 => G_SQLERRM_TOKEN,
774 p_token3_value => sqlerrm);
775
776 x_return_status := G_RET_STS_UNEXP_ERROR;
777
778 END get_quote_rec;
779
780 -------------------------------------
781 -- PROCEDURE cancel_quote_lines
782 -------------------------------------
783 PROCEDURE cancel_quote_lines(p_api_version IN NUMBER,
784 p_init_msg_list IN VARCHAR2,
785 p_quote_id IN NUMBER,
786 x_msg_count OUT NOCOPY NUMBER,
787 x_msg_data OUT NOCOPY VARCHAR2,
788 x_return_status OUT NOCOPY VARCHAR2) IS
789
790 l_program_name CONSTANT VARCHAR2(30) := 'cancel_quote_lines';
791 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
792
793 CURSOR c_get_assets IS
794 SELECT id
795 FROM OKL_ASSETS_B
796 WHERE PARENT_OBJECT_ID = p_quote_id
797 AND PARENT_OBJECT_CODE = 'LEASEQUOTE';
798
799 CURSOR c_get_fees IS
800 SELECT id
801 FROM OKL_FEES_B
802 WHERE PARENT_OBJECT_ID = p_quote_id
803 AND PARENT_OBJECT_CODE = 'LEASEQUOTE';
804
805 CURSOR c_get_services IS
806 SELECT id
807 FROM OKL_SERVICES_B
808 WHERE PARENT_OBJECT_ID = p_quote_id
809 AND PARENT_OBJECT_CODE = 'LEASEQUOTE';
810
811 CURSOR c_get_ins_estimates IS
812 SELECT id
813 FROM OKL_INSURANCE_ESTIMATES_B
814 WHERE LEASE_QUOTE_ID = p_quote_id;
815
816 BEGIN
817
818 -- Cancel Assets
819 FOR l_get_assets IN c_get_assets LOOP
820 OKL_LEASE_QUOTE_ASSET_PVT.delete_asset (
821 p_api_version => p_api_version
822 ,p_init_msg_list => 'T'
823 ,p_transaction_control => 'T'
824 ,p_asset_id => l_get_assets.id
825 ,x_return_status => x_return_status
826 ,x_msg_count => x_msg_count
827 ,x_msg_data => x_msg_data );
828
829 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
831 ELSIF x_return_status = G_RET_STS_ERROR THEN
832 RAISE OKL_API.G_EXCEPTION_ERROR;
833 END IF;
834 END LOOP;
835
836 -- Cancel Fees
837 FOR l_get_fees IN c_get_fees LOOP
838 OKL_LEASE_QUOTE_FEE_PVT.delete_fee (
839 p_api_version => p_api_version
840 ,p_init_msg_list => 'T'
841 ,p_transaction_control => 'T'
842 ,p_fee_id => l_get_fees.id
843 ,x_return_status => x_return_status
844 ,x_msg_count => x_msg_count
845 ,x_msg_data => x_msg_data );
846 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
847 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
848 ELSIF x_return_status = G_RET_STS_ERROR THEN
849 RAISE OKL_API.G_EXCEPTION_ERROR;
850 END IF;
851 END LOOP;
852
853 -- Cancel Services
854 FOR l_get_services IN c_get_services LOOP
855 OKL_LEASE_QUOTE_SERVICE_PVT.delete_service (
856 p_api_version => p_api_version
857 ,p_init_msg_list => 'T'
858 ,p_transaction_control => 'T'
859 ,p_service_id => l_get_services.id
860 ,x_return_status => x_return_status
861 ,x_msg_count => x_msg_count
862 ,x_msg_data => x_msg_data );
863 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
864 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
865 ELSIF x_return_status = G_RET_STS_ERROR THEN
866 RAISE OKL_API.G_EXCEPTION_ERROR;
867 END IF;
868 END LOOP;
869
870 -- Cancel Insurance Estimates
871 FOR l_get_ins_estimates IN c_get_ins_estimates LOOP
872 OKL_LEASE_QUOTE_INS_PVT.delete_insurance_estimate (
873 p_api_version => p_api_version
874 ,p_init_msg_list => 'T'
875 ,p_transaction_control => 'T'
876 ,p_insurance_estimate_id => l_get_ins_estimates.id
877 ,x_return_status => x_return_status
878 ,x_msg_count => x_msg_count
879 ,x_msg_data => x_msg_data );
880 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
881 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 ELSIF x_return_status = G_RET_STS_ERROR THEN
883 RAISE OKL_API.G_EXCEPTION_ERROR;
884 END IF;
885 END LOOP;
886
887 x_return_status := G_RET_STS_SUCCESS;
888
889 EXCEPTION
890
891 WHEN OKL_API.G_EXCEPTION_ERROR THEN
892
893 x_return_status := G_RET_STS_ERROR;
894
895 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
896
897 x_return_status := G_RET_STS_UNEXP_ERROR;
898
899 WHEN OTHERS THEN
900
901 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
902 p_msg_name => G_DB_ERROR,
903 p_token1 => G_PROG_NAME_TOKEN,
904 p_token1_value => l_api_name,
905 p_token2 => G_SQLCODE_TOKEN,
906 p_token2_value => sqlcode,
907 p_token3 => G_SQLERRM_TOKEN,
908 p_token3_value => sqlerrm);
909
910 x_return_status := G_RET_STS_UNEXP_ERROR;
911 END cancel_quote_lines;
912
913 -----------------------------
914 -- PROCEDURE create_lease_qte
915 -----------------------------
916 PROCEDURE create_lease_qte (p_api_version IN NUMBER,
917 p_init_msg_list IN VARCHAR2,
918 p_transaction_control IN VARCHAR2,
919 p_lease_qte_rec IN lease_qte_rec_type,
920 x_lease_qte_rec OUT NOCOPY lease_qte_rec_type,
921 x_return_status OUT NOCOPY VARCHAR2,
922 x_msg_count OUT NOCOPY NUMBER,
923 x_msg_data OUT NOCOPY VARCHAR2) IS
924
925 l_return_status VARCHAR2(1);
926
927 l_program_name CONSTANT VARCHAR2(30) := 'create_lease_qte';
928 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
929 l_lease_qte_rec lease_qte_rec_type;
930 -- Bug#4741121 - viselvar - Modified - Start
931 l_parameter_list wf_parameter_list_t;
932 p_event_name VARCHAR2(240) := 'oracle.apps.okl.sales.leaseapplication.alternate_offers_created';
933 -- Bug#4741121 - viselvar - Modified - End
934
935 l_refno_unq_chk VARCHAR2(1);--Bug 7022258
936
937 BEGIN
938
939 IF p_transaction_control = G_TRUE THEN
940 SAVEPOINT l_program_name;
941 END IF;
942
943 IF p_init_msg_list = G_TRUE THEN
944 FND_MSG_PUB.initialize;
945 END IF;
946
947 l_lease_qte_rec := p_lease_qte_rec;
948
949 --Bug 7022258-Modified by kkorrapo
950 l_lease_qte_rec.reference_number := l_lease_qte_rec.reference_number;
951 --Bug 7022258--Modification end
952
953
954 l_lease_qte_rec.valid_from := TRUNC(l_lease_qte_rec.valid_from);
955 l_lease_qte_rec.valid_to := TRUNC(l_lease_qte_rec.valid_to);
956 l_lease_qte_rec.expected_start_date := TRUNC(l_lease_qte_rec.expected_start_date);
957 l_lease_qte_rec.expected_delivery_date := TRUNC(l_lease_qte_rec.expected_delivery_date);
958 l_lease_qte_rec.expected_funding_date := TRUNC(l_lease_qte_rec.expected_funding_date);
959
960 IF (l_lease_qte_rec.structured_pricing IS NULL AND
961 l_lease_qte_rec.line_level_pricing IS NULL) THEN
962 l_lease_qte_rec.structured_pricing := 'N';
963 l_lease_qte_rec.line_level_pricing := 'N';
964 END IF;
965
966 IF (l_lease_qte_rec.status IS NULL) THEN
967 l_lease_qte_rec.status := 'PR-INCOMPLETE';
968 END IF;
969
970 validate_lease_qte(p_lease_qte_rec => l_lease_qte_rec,
971 x_return_status => l_return_status);
972
973 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
974 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
975 ELSIF l_return_status = G_RET_STS_ERROR THEN
976 RAISE OKL_API.G_EXCEPTION_ERROR;
977 END IF;
978
979 --Bug 7022258-Added by kkorrapo
980 l_refno_unq_chk := okl_util.validate_seq_num('OKL_LSQ_REF_SEQ','OKL_LEASE_QUOTES_B','REFERENCE_NUMBER',l_lease_qte_rec.reference_number);
981
982 IF (l_refno_unq_chk = 'N') THEN
983 RAISE OKL_API.G_EXCEPTION_ERROR;
984 END IF;
985 --Bug 7022258--Addition end
986
987 okl_lsq_pvt.insert_row(
988 p_api_version => G_API_VERSION
989 ,p_init_msg_list => G_FALSE
990 ,x_return_status => l_return_status
991 ,x_msg_count => x_msg_count
992 ,x_msg_data => x_msg_data
993 ,p_lsqv_rec => l_lease_qte_rec
994 ,x_lsqv_rec => x_lease_qte_rec );
995
996 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
997 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
998 ELSIF l_return_status = G_RET_STS_ERROR THEN
999 RAISE OKL_API.G_EXCEPTION_ERROR;
1000 END IF;
1001
1002 -- if the quote is created from OCM, it is an alternate offer
1003 -- Bug#4741121 - viselvar - Modified - Start
1004 IF (l_lease_qte_rec.status = 'CR-INCOMPLETE') THEN
1005 -- raise the business event passing the version id added to the parameter list
1006 wf_event.addparametertolist('QUOTE_ID'
1007 ,x_lease_qte_rec.id
1008 ,l_parameter_list);
1009 okl_wf_pvt.raise_event(p_api_version => p_api_version
1010 ,p_init_msg_list => p_init_msg_list
1011 ,x_return_status => l_return_status
1012 ,x_msg_count => x_msg_count
1013 ,x_msg_data => x_msg_data
1014 ,p_event_name => p_event_name
1015 ,p_parameters => l_parameter_list);
1016
1017 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1018 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1019 ELSIF l_return_status = G_RET_STS_ERROR THEN
1020 RAISE OKL_API.G_EXCEPTION_ERROR;
1021 END IF;
1022
1023 END IF;
1024 -- Bug#4741121 - viselvar - Modified - End
1025
1026 x_return_status := l_return_status;
1027
1028 EXCEPTION
1029
1030 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1031
1032 IF p_transaction_control = G_TRUE THEN
1033 ROLLBACK TO l_program_name;
1034 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1035 END IF;
1036
1037 x_return_status := G_RET_STS_ERROR;
1038
1039 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1040
1041 IF p_transaction_control = G_TRUE THEN
1042 ROLLBACK TO l_program_name;
1043 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1044 END IF;
1045
1046 x_return_status := G_RET_STS_UNEXP_ERROR;
1047
1048 WHEN OTHERS THEN
1049
1050 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1051 p_msg_name => G_DB_ERROR,
1052 p_token1 => G_PROG_NAME_TOKEN,
1053 p_token1_value => l_api_name,
1054 p_token2 => G_SQLCODE_TOKEN,
1055 p_token2_value => sqlcode,
1056 p_token3 => G_SQLERRM_TOKEN,
1057 p_token3_value => sqlerrm);
1058
1059 IF p_transaction_control = G_TRUE THEN
1060 ROLLBACK TO l_program_name;
1061 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1062 END IF;
1063
1064 x_return_status := G_RET_STS_UNEXP_ERROR;
1065
1066 END create_lease_qte;
1067
1068 -----------------------------
1069 -- PROCEDURE update_lease_qte
1070 -----------------------------
1071 PROCEDURE update_lease_qte (p_api_version IN NUMBER,
1072 p_init_msg_list IN VARCHAR2,
1073 p_transaction_control IN VARCHAR2,
1074 p_lease_qte_rec IN lease_qte_rec_type,
1075 x_lease_qte_rec OUT NOCOPY lease_qte_rec_type,
1076 x_return_status OUT NOCOPY VARCHAR2,
1077 x_msg_count OUT NOCOPY NUMBER,
1078 x_msg_data OUT NOCOPY VARCHAR2) IS
1079
1080 l_return_status VARCHAR2(1);
1081 l_term_upd_allowed VARCHAR2(1);
1082
1083 l_program_name CONSTANT VARCHAR2(30) := 'update_lease_qte';
1084 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1085 l_lease_qte_rec lease_qte_rec_type;
1086
1087 l_copy_lse_qte lease_qte_rec_type;
1088
1089 lb_handle_subpool BOOLEAN := FALSE;
1090 lb_validate BOOLEAN := TRUE;
1091 lv_status okl_lease_quotes_b.status%TYPE;
1092 lv_leaseapp_status okl_lease_applications_b.application_status%TYPE;
1093 ln_parent_object_id okl_lease_quotes_b.parent_object_id%TYPE;
1094
1095 --gboomina Bug 7033915 start
1096 CURSOR lease_qte_credit_app_csr (p_lap_id OKL_LEASE_QUOTES_B.parent_object_id%TYPE) IS
1097 SELECT 'Y'
1098 FROM okl_lease_quotes_b
1099 WHERE status = 'CR-DECLINED'
1100 AND parent_object_code = 'LEASEAPP'
1101 AND PARENT_OBJECT_ID = p_lap_id;
1102
1103 CURSOR lap_status_csr(cp_lap_id NUMBER)
1104 IS
1105 SELECT APPLICATION_STATUS LAP_STATUS
1106 FROM OKL_LEASE_APPLICATIONS_B
1107 WHERE ID = cp_lap_id;
1108 l_lap_status OKL_LEASE_APPLICATIONS_B.application_status%type;
1109
1110 l_primary_quote VARCHAR2(1) := 'N';
1111 l_ct_declined_exist VARCHAR2(1) := 'N';
1112 --gboomina Bug 7033915 end
1113
1114 BEGIN
1115
1116 IF p_transaction_control = G_TRUE THEN
1117 SAVEPOINT l_program_name;
1118 END IF;
1119
1120 IF p_init_msg_list = G_TRUE THEN
1121 FND_MSG_PUB.initialize;
1122 END IF;
1123
1124 IF ((p_lease_qte_rec.parent_object_code IS NULL OR
1125 p_lease_qte_rec.parent_object_code = OKL_API.G_MISS_CHAR) OR
1126 p_lease_qte_rec.object_version_number IS NULL ) THEN
1127
1128 lb_validate := FALSE;
1129
1130 END IF;
1131
1132 l_lease_qte_rec := p_lease_qte_rec;
1133
1134 -- Begin -- Added for Bug# 6930574
1135 IF l_lease_qte_rec.expected_start_date <> FND_API.G_MISS_DATE THEN
1136 l_term_upd_allowed := is_contterm_payperiod_equal(
1137 p_contract_start_date => l_lease_qte_rec.expected_start_date,
1138 p_contract_term => l_lease_qte_rec.term,
1139 p_quote_id => l_lease_qte_rec.id);
1140 IF l_term_upd_allowed <> 'Y' THEN
1141 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1142 p_msg_name => 'OKL_LEVEL_EXTENDS_K_END');
1143 RAISE OKL_API.G_EXCEPTION_ERROR;
1144 END IF;
1145 END IF;
1146 -- End -- Added for Bug# 6930574
1147
1148 IF (lb_validate) THEN
1149 l_lease_qte_rec.valid_from := TRUNC(l_lease_qte_rec.valid_from);
1150 l_lease_qte_rec.valid_to := TRUNC(l_lease_qte_rec.valid_to);
1151 l_lease_qte_rec.expected_start_date := TRUNC(l_lease_qte_rec.expected_start_date);
1152 l_lease_qte_rec.expected_delivery_date := TRUNC(l_lease_qte_rec.expected_delivery_date);
1153 l_lease_qte_rec.expected_funding_date := TRUNC(l_lease_qte_rec.expected_funding_date);
1154
1155 validate_lease_qte(p_lease_qte_rec => l_lease_qte_rec,
1156 x_return_status => l_return_status);
1157 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1158 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1159 ELSIF l_return_status = G_RET_STS_ERROR THEN
1160 RAISE OKL_API.G_EXCEPTION_ERROR;
1161 END IF;
1162
1163 -- This populates other quote attributes which are not visible from the
1164 -- update quote page
1165 IF (l_lease_qte_rec.structured_pricing IS NULL AND
1166 l_lease_qte_rec.line_level_pricing IS NULL) THEN
1167 populate_quote_attribs(p_source_quote_id => l_lease_qte_rec.id,
1168 x_quote_rec => l_lease_qte_rec,
1169 x_return_status => l_return_status);
1170 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1171 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1172 ELSIF l_return_status = G_RET_STS_ERROR THEN
1173 RAISE OKL_API.G_EXCEPTION_ERROR;
1174 END IF;
1175 END IF;
1176
1177 END IF;
1178
1179 SELECT object_version_number, parent_object_code, status, parent_object_id
1180 , nvl(primary_quote, 'N') -- -- 7033915
1181 INTO l_lease_qte_rec.object_version_number,
1182 l_lease_qte_rec.parent_object_code,
1183 lv_status,
1184 ln_parent_object_id
1185 ,l_primary_quote -- 7033915
1186 FROM okl_lease_quotes_v
1187 WHERE id = l_lease_qte_rec.id;
1188
1189 -- gboomina Bug 7033915 start
1190 l_ct_declined_exist := 'N';
1191 OPEN lease_qte_credit_app_csr (ln_parent_object_id);
1192 FETCH lease_qte_credit_app_csr INTO l_ct_declined_exist;
1193 IF lease_qte_credit_app_csr%NOTFOUND THEN
1194 l_ct_declined_exist := 'N';
1195 END IF;
1196 CLOSE lease_qte_credit_app_csr;
1197 -- gboomina Bug 7033915 end
1198
1199 OPEN lap_status_csr (ln_parent_object_id);
1200 FETCH lap_status_csr INTO l_lap_status;
1201 CLOSE lap_status_csr;
1202
1203 -- Check for 'CR-RECOMMENDATION' quote (Bug 4893112)
1204 -- Bug 5149367
1205 -- gboomin Bug 7033915 start
1206
1207 IF (l_lap_status in ('PR-ACCEPTED')) THEN -- Added for bug 7427166
1208 l_lease_qte_rec.status := 'CT-ACCEPTED';
1209 ELSIF (lv_status = 'CT-ACCEPTED' AND l_primary_quote = 'Y' AND l_ct_declined_exist = 'N') THEN
1210 l_lease_qte_rec.status := 'CR-DECLINED';
1211 ELSIF (lv_status = 'CT-ACCEPTED' AND l_primary_quote = 'Y') THEN
1212 l_lease_qte_rec.status := 'CR-RECOMMENDATION';
1213 ELSIF (lv_status = 'CR-INCOMPLETE' AND l_lease_qte_rec.status = 'PR-COMPLETE') THEN
1214 l_lease_qte_rec.status := 'CR-RECOMMENDATION';
1215 ELSIF (lv_status = 'CR-INCOMPLETE') THEN
1216 l_lease_qte_rec.status := 'CR-INCOMPLETE';
1217 ELSIF (lv_status = 'CR-RECOMMENDATION' AND l_lease_qte_rec.status = 'PR-INCOMPLETE') THEN
1218 l_lease_qte_rec.status := 'CR-INCOMPLETE';
1219 ELSIF (lv_status = 'CR-RECOMMENDATION' AND l_lease_qte_rec.status = 'PR-COMPLETE') THEN
1220 l_lease_qte_rec.status := 'CR-RECOMMENDATION';
1221 ELSIF (lv_status = 'CR-RECOMMENDATION') THEN
1222 l_lease_qte_rec.status := 'CT-ACCEPTED'; -- 'CR-INCOMPLETE';-- 7033915
1223 lb_handle_subpool := TRUE;
1224 END IF;
1225 -- End Bug 5149367
1226
1227 IF (l_lease_qte_rec.status <> 'CT-ACCEPTED') THEN
1228 IF (lv_status = 'PR-APPROVED') THEN
1229 lb_handle_subpool := TRUE;
1230 l_lease_qte_rec.status := 'PR-INCOMPLETE';
1231 END IF;
1232 END IF;
1233
1234 okl_lsq_pvt.update_row(p_api_version => G_API_VERSION
1235 ,p_init_msg_list => G_FALSE
1236 ,x_return_status => l_return_status
1237 ,x_msg_count => x_msg_count
1238 ,x_msg_data => x_msg_data
1239 ,p_lsqv_rec => l_lease_qte_rec
1240 ,x_lsqv_rec => x_lease_qte_rec );
1241
1242 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1243 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1244 ELSIF l_return_status = G_RET_STS_ERROR THEN
1245 RAISE OKL_API.G_EXCEPTION_ERROR;
1246 END IF;
1247
1248 -- Call the Subsidy pool API in case of Counter Offer which has status
1249 -- 'CR-RECOMMENDATION' (Bug 5149367)
1250 IF (l_lease_qte_rec.status = 'CR-RECOMMENDATION' AND l_lease_qte_rec.parent_object_code = 'LEASEAPP') THEN
1251 okl_lease_quote_subpool_pvt.process_leaseapp_subsidy_pool(
1252 p_api_version => p_api_version
1253 ,p_init_msg_list => OKL_API.G_FALSE
1254 ,p_transaction_control => OKL_API.G_TRUE
1255 ,p_leaseapp_id => ln_parent_object_id
1256 ,p_quote_id => l_lease_qte_rec.id
1257 ,p_transaction_reason => 'APPROVE_LEASE_APP_PRICING'
1258 ,x_return_status => l_return_status
1259 ,x_msg_count => x_msg_count
1260 ,x_msg_data => x_msg_data);
1261 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1262 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1263 ELSIF l_return_status = G_RET_STS_ERROR THEN
1264 RAISE OKL_API.G_EXCEPTION_ERROR;
1265 END IF;
1266 END IF;
1267 -- End Bug 5149367
1268
1269 -- If the quote beneath the Lease Application is updated, lease application
1270 -- status is set to 'Incomplete'.
1271 IF (l_lease_qte_rec.parent_object_code = 'LEASEAPP' AND
1272 l_lease_qte_rec.status <> 'CT-ACCEPTED') THEN
1273
1274 SELECT application_status
1275 INTO lv_leaseapp_status
1276 FROM okl_lease_applications_b
1277 where id = ln_parent_object_id;
1278
1279 IF (lv_leaseapp_status IN ('PR-COMPLETE', 'PR-APPROVED')) THEN
1280 OKL_LEASE_APP_PVT.set_lease_app_status(p_api_version => G_API_VERSION,
1281 p_init_msg_list => G_FALSE,
1282 p_lap_id => ln_parent_object_id,
1283 p_lap_status => 'INCOMPLETE',
1284 x_return_status => l_return_status,
1285 x_msg_count => x_msg_count,
1286 x_msg_data => x_msg_data);
1287
1288 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1289 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1290 ELSIF l_return_status = G_RET_STS_ERROR THEN
1291 RAISE OKL_API.G_EXCEPTION_ERROR;
1292 END IF;
1293 END IF;
1294 END IF;
1295
1296 -- Handle Subsidy pool usage
1297 IF (lb_handle_subpool AND l_lease_qte_rec.parent_object_code = 'LEASEOPP') THEN
1298 okl_lease_quote_subpool_pvt.process_quote_subsidy_pool(
1299 p_api_version => G_API_VERSION
1300 ,p_init_msg_list => G_TRUE
1301 ,p_transaction_control => G_TRUE
1302 ,p_quote_id => l_lease_qte_rec.id
1303 ,p_transaction_reason => 'UPDATE_APPROVED_QUOTE'
1304 ,x_return_status => l_return_status
1305 ,x_msg_count => x_msg_count
1306 ,x_msg_data => x_msg_data);
1307 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1308 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1309 ELSIF l_return_status = G_RET_STS_ERROR THEN
1310 RAISE OKL_API.G_EXCEPTION_ERROR;
1311 END IF;
1312 ELSIF (lb_handle_subpool AND l_lease_qte_rec.parent_object_code = 'LEASEAPP') THEN
1313 okl_lease_quote_subpool_pvt.process_leaseapp_subsidy_pool(
1314 p_api_version => p_api_version
1315 ,p_init_msg_list => G_TRUE
1316 ,p_transaction_control => G_TRUE
1317 ,p_leaseapp_id => ln_parent_object_id
1318 ,p_transaction_reason => 'UPDATE_LEASE_APP'
1319 ,p_quote_id => l_lease_qte_rec.id
1320 ,x_return_status => l_return_status
1321 ,x_msg_count => x_msg_count
1322 ,x_msg_data => x_msg_data);
1323 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1324 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1325 ELSIF l_return_status = G_RET_STS_ERROR THEN
1326 RAISE OKL_API.G_EXCEPTION_ERROR;
1327 END IF;
1328 END IF;
1329
1330 -- Delete quote tax fee if one exists
1331 delete_quote_tax_fee( p_api_version => p_api_version
1332 ,p_init_msg_list => G_TRUE
1333 ,x_return_status => l_return_status
1334 ,x_msg_count => x_msg_count
1335 ,x_msg_data => x_msg_data
1336 ,p_quote_id => l_lease_qte_rec.id);
1337 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1338 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1339 ELSIF l_return_status = G_RET_STS_ERROR THEN
1340 RAISE OKL_API.G_EXCEPTION_ERROR;
1341 END IF;
1342
1343 x_return_status := l_return_status;
1344
1345 EXCEPTION
1346
1347 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1348
1349 IF p_transaction_control = G_TRUE THEN
1350 ROLLBACK TO l_program_name;
1351 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1352 END IF;
1353
1354 x_return_status := G_RET_STS_ERROR;
1355
1356 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1357
1358 IF p_transaction_control = G_TRUE THEN
1359 ROLLBACK TO l_program_name;
1360 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1361 END IF;
1362
1363 x_return_status := G_RET_STS_UNEXP_ERROR;
1364
1365 WHEN OTHERS THEN
1366
1367 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1368 p_msg_name => G_DB_ERROR,
1369 p_token1 => G_PROG_NAME_TOKEN,
1370 p_token1_value => l_api_name,
1371 p_token2 => G_SQLCODE_TOKEN,
1372 p_token2_value => sqlcode,
1373 p_token3 => G_SQLERRM_TOKEN,
1374 p_token3_value => sqlerrm);
1375
1376 IF p_transaction_control = G_TRUE THEN
1377 ROLLBACK TO l_program_name;
1378 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1379 END IF;
1380
1381 x_return_status := G_RET_STS_UNEXP_ERROR;
1382
1383 END update_lease_qte;
1384
1385 -----------------------------
1386 -- PROCEDURE get_leaseopp_rec
1387 -----------------------------
1388 PROCEDURE get_leaseopp_rec ( p_leaseopp_id IN NUMBER
1389 ,x_leaseopp_rec OUT NOCOPY okl_lop_pvt.lopv_rec_type
1390 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
1391
1392 l_program_name CONSTANT VARCHAR2(30) := 'get_leaseopp_rec';
1393 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1394
1395 BEGIN
1396
1397 SELECT
1398 id
1399 ,attribute_category
1400 ,attribute1
1401 ,attribute2
1402 ,attribute3
1403 ,attribute4
1404 ,attribute5
1405 ,attribute6
1406 ,attribute7
1407 ,attribute8
1408 ,attribute9
1409 ,attribute10
1410 ,attribute11
1411 ,attribute12
1412 ,attribute13
1413 ,attribute14
1414 ,attribute15
1415 ,object_version_number
1416 ,reference_number
1417 ,valid_from
1418 ,expected_start_date
1419 ,org_id
1420 ,inv_org_id
1421 ,prospect_id
1422 ,prospect_address_id
1423 ,cust_acct_id
1424 ,currency_code
1425 ,currency_conversion_type
1426 ,currency_conversion_rate
1427 ,currency_conversion_date
1428 ,program_agreement_id
1429 ,master_lease_id
1430 ,sales_rep_id
1431 ,sales_territory_id
1432 ,supplier_id
1433 ,delivery_date
1434 ,funding_date
1435 ,property_tax_applicable
1436 ,property_tax_billing_type
1437 ,upfront_tax_treatment
1438 ,install_site_id
1439 ,usage_category
1440 ,usage_industry_class
1441 ,usage_industry_code
1442 ,usage_amount
1443 ,usage_location_id
1444 ,originating_vendor_id
1445 ,short_description
1446 ,description
1447 ,comments
1448 INTO
1449 x_leaseopp_rec.id
1450 ,x_leaseopp_rec.attribute_category
1451 ,x_leaseopp_rec.attribute1
1452 ,x_leaseopp_rec.attribute2
1453 ,x_leaseopp_rec.attribute3
1454 ,x_leaseopp_rec.attribute4
1455 ,x_leaseopp_rec.attribute5
1456 ,x_leaseopp_rec.attribute6
1457 ,x_leaseopp_rec.attribute7
1458 ,x_leaseopp_rec.attribute8
1459 ,x_leaseopp_rec.attribute9
1460 ,x_leaseopp_rec.attribute10
1461 ,x_leaseopp_rec.attribute11
1462 ,x_leaseopp_rec.attribute12
1463 ,x_leaseopp_rec.attribute13
1464 ,x_leaseopp_rec.attribute14
1465 ,x_leaseopp_rec.attribute15
1466 ,x_leaseopp_rec.object_version_number
1467 ,x_leaseopp_rec.reference_number
1468 ,x_leaseopp_rec.valid_from
1469 ,x_leaseopp_rec.expected_start_date
1470 ,x_leaseopp_rec.org_id
1471 ,x_leaseopp_rec.inv_org_id
1472 ,x_leaseopp_rec.prospect_id
1473 ,x_leaseopp_rec.prospect_address_id
1474 ,x_leaseopp_rec.cust_acct_id
1475 ,x_leaseopp_rec.currency_code
1476 ,x_leaseopp_rec.currency_conversion_type
1477 ,x_leaseopp_rec.currency_conversion_rate
1478 ,x_leaseopp_rec.currency_conversion_date
1479 ,x_leaseopp_rec.program_agreement_id
1480 ,x_leaseopp_rec.master_lease_id
1481 ,x_leaseopp_rec.sales_rep_id
1482 ,x_leaseopp_rec.sales_territory_id
1483 ,x_leaseopp_rec.supplier_id
1484 ,x_leaseopp_rec.delivery_date
1485 ,x_leaseopp_rec.funding_date
1486 ,x_leaseopp_rec.property_tax_applicable
1487 ,x_leaseopp_rec.property_tax_billing_type
1488 ,x_leaseopp_rec.upfront_tax_treatment
1489 ,x_leaseopp_rec.install_site_id
1490 ,x_leaseopp_rec.usage_category
1491 ,x_leaseopp_rec.usage_industry_class
1492 ,x_leaseopp_rec.usage_industry_code
1493 ,x_leaseopp_rec.usage_amount
1494 ,x_leaseopp_rec.usage_location_id
1495 ,x_leaseopp_rec.originating_vendor_id
1496 ,x_leaseopp_rec.short_description
1497 ,x_leaseopp_rec.description
1498 ,x_leaseopp_rec.comments
1499 FROM okl_lease_opportunities_v
1500 WHERE id = p_leaseopp_id;
1501
1502 x_return_status := G_RET_STS_SUCCESS;
1503
1504 EXCEPTION
1505
1506 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1507 x_return_status := G_RET_STS_ERROR;
1508
1509 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1510 x_return_status := G_RET_STS_UNEXP_ERROR;
1511
1512 WHEN OTHERS THEN
1513 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1514 p_msg_name => G_DB_ERROR,
1515 p_token1 => G_PROG_NAME_TOKEN,
1516 p_token1_value => l_program_name,
1517 p_token2 => G_SQLCODE_TOKEN,
1518 p_token2_value => sqlcode,
1519 p_token3 => G_SQLERRM_TOKEN,
1520 p_token3_value => sqlerrm);
1521
1522 x_return_status := G_RET_STS_UNEXP_ERROR;
1523
1524 END get_leaseopp_rec;
1525
1526 --------------------------------
1527 -- PROCEDURE copy_quote_payments
1528 --------------------------------
1529 PROCEDURE copy_quote_payments(p_api_version IN NUMBER,
1530 p_init_msg_list IN VARCHAR2,
1531 p_source_quote_id IN NUMBER,
1532 p_target_quote_id IN NUMBER,
1533 x_msg_count OUT NOCOPY NUMBER,
1534 x_msg_data OUT NOCOPY VARCHAR2,
1535 x_return_status OUT NOCOPY VARCHAR2) IS
1536
1537 l_program_name CONSTANT VARCHAR2(30) := 'copy_quote_payments';
1538 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1539
1540 lv_chk_payment VARCHAR2(1);
1541
1542 CURSOR c_chk_payments IS
1543 SELECT 'Y'
1544 FROM OKL_CASH_FLOW_OBJECTS
1545 WHERE SOURCE_ID = p_source_quote_id
1546 AND SOURCE_TABLE = 'OKL_LEASE_QUOTES_B'
1547 AND OTY_CODE = 'LEASE_QUOTE';
1548
1549 CURSOR c_chk_payments_cons IS
1550 SELECT 'Y'
1551 FROM OKL_CASH_FLOW_OBJECTS
1552 WHERE SOURCE_ID = p_source_quote_id
1553 AND SOURCE_TABLE = 'OKL_LEASE_QUOTES_B'
1554 AND OTY_CODE = 'LEASE_QUOTE_CONSOLIDATED';
1555
1556 BEGIN
1557
1558 -- Check if the Quote has header payments defined
1559 OPEN c_chk_payments;
1560 FETCH c_chk_payments INTO lv_chk_payment;
1561 CLOSE c_chk_payments;
1562
1563 -- Copy Header level payments
1564 IF (lv_chk_payment = 'Y') THEN
1565 okl_lease_quote_cashflow_pvt.duplicate_cashflows (
1566 p_api_version => p_api_version
1567 ,p_init_msg_list => p_init_msg_list
1568 ,p_transaction_control => 'T'
1569 ,p_source_object_code => 'LEASE_QUOTE'
1570 ,p_source_object_id => p_source_quote_id
1571 ,p_target_object_id => p_target_quote_id
1572 ,p_quote_id => p_target_quote_id
1573 ,x_return_status => x_return_status
1574 ,x_msg_count => x_msg_count
1575 ,x_msg_data => x_msg_data
1576 );
1577
1578 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1579 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1580 ELSIF x_return_status = G_RET_STS_ERROR THEN
1581 RAISE OKL_API.G_EXCEPTION_ERROR;
1582 END IF;
1583 END IF;
1584
1585 -- Check if the Quote has header payments defined
1586 OPEN c_chk_payments_cons;
1587 FETCH c_chk_payments_cons INTO lv_chk_payment;
1588 CLOSE c_chk_payments_cons;
1589
1590 -- Copy Header level payments
1591 IF (lv_chk_payment = 'Y') THEN
1592 okl_lease_quote_cashflow_pvt.duplicate_cashflows (
1593 p_api_version => p_api_version
1594 ,p_init_msg_list => p_init_msg_list
1595 ,p_transaction_control => 'T'
1596 ,p_source_object_code => 'LEASE_QUOTE_CONSOLIDATED'
1597 ,p_source_object_id => p_source_quote_id
1598 ,p_target_object_id => p_target_quote_id
1599 ,p_quote_id => p_target_quote_id
1600 ,x_return_status => x_return_status
1601 ,x_msg_count => x_msg_count
1602 ,x_msg_data => x_msg_data
1603 );
1604
1605 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1606 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1607 ELSIF x_return_status = G_RET_STS_ERROR THEN
1608 RAISE OKL_API.G_EXCEPTION_ERROR;
1609 END IF;
1610 END IF;
1611
1612 x_return_status := G_RET_STS_SUCCESS;
1613
1614 EXCEPTION
1615
1616 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1617
1618 x_return_status := G_RET_STS_ERROR;
1619
1620 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1621
1622 x_return_status := G_RET_STS_UNEXP_ERROR;
1623
1624 WHEN OTHERS THEN
1625
1626 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1627 p_msg_name => G_DB_ERROR,
1628 p_token1 => G_PROG_NAME_TOKEN,
1629 p_token1_value => l_api_name,
1630 p_token2 => G_SQLCODE_TOKEN,
1631 p_token2_value => sqlcode,
1632 p_token3 => G_SQLERRM_TOKEN,
1633 p_token3_value => sqlerrm);
1634
1635 x_return_status := G_RET_STS_UNEXP_ERROR;
1636 END copy_quote_payments;
1637
1638 -------------------------------------
1639 -- PROCEDURE copy_configuration_lines
1640 -------------------------------------
1641 PROCEDURE copy_configuration_lines(p_api_version IN NUMBER,
1642 p_init_msg_list IN VARCHAR2,
1643 p_source_quote_id IN NUMBER,
1644 p_target_quote_id IN NUMBER,
1645 x_msg_count OUT NOCOPY NUMBER,
1646 x_msg_data OUT NOCOPY VARCHAR2,
1647 x_return_status OUT NOCOPY VARCHAR2) IS
1648
1649 l_program_name CONSTANT VARCHAR2(30) := 'copy_configuration_lines';
1650 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1651
1652 x_asset_id NUMBER;
1653 x_fee_id NUMBER;
1654
1655 CURSOR c_get_assets IS
1656 SELECT id
1657 FROM OKL_ASSETS_B
1658 WHERE PARENT_OBJECT_ID = p_source_quote_id
1659 AND PARENT_OBJECT_CODE = 'LEASEQUOTE';
1660
1661 CURSOR c_get_config_fees IS
1662 SELECT id
1663 FROM OKL_FEES_B
1664 WHERE PARENT_OBJECT_ID = p_source_quote_id
1665 AND PARENT_OBJECT_CODE = 'LEASEQUOTE'
1666 AND FEE_TYPE IN ('FINANCED', 'CAPITALIZED', 'ROLLOVER');
1667 -- AND FEE_PURPOSE_CODE IS NULL; Bug#9708535
1668
1669 BEGIN
1670
1671 -- Copy Assets
1672 FOR l_get_assets IN c_get_assets LOOP
1673 OKL_LEASE_QUOTE_ASSET_PVT.duplicate_asset (
1674 p_api_version => p_api_version
1675 ,p_init_msg_list => 'T'
1676 ,p_transaction_control => 'T'
1677 ,p_source_asset_id => l_get_assets.id
1678 ,p_target_quote_id => p_target_quote_id
1679 ,x_target_asset_id => x_asset_id
1680 ,x_return_status => x_return_status
1681 ,x_msg_count => x_msg_count
1682 ,x_msg_data => x_msg_data );
1683 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1684 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1685 ELSIF x_return_status = G_RET_STS_ERROR THEN
1686 RAISE OKL_API.G_EXCEPTION_ERROR;
1687 END IF;
1688 END LOOP;
1689
1690 -- Copy Configuration Fees
1691 FOR l_get_config_fees IN c_get_config_fees LOOP
1692 OKL_LEASE_QUOTE_FEE_PVT.duplicate_fee (
1693 p_api_version => p_api_version
1694 ,p_init_msg_list => 'T'
1695 ,p_transaction_control => 'T'
1696 ,p_source_fee_id => l_get_config_fees.id
1697 ,p_target_quote_id => p_target_quote_id
1698 ,x_fee_id => x_fee_id
1699 ,x_return_status => x_return_status
1700 ,x_msg_count => x_msg_count
1701 ,x_msg_data => x_msg_data );
1702 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1703 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1704 ELSIF x_return_status = G_RET_STS_ERROR THEN
1705 RAISE OKL_API.G_EXCEPTION_ERROR;
1706 END IF;
1707
1708 END LOOP;
1709
1710 x_return_status := G_RET_STS_SUCCESS;
1711
1712 EXCEPTION
1713
1714 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1715
1716 x_return_status := G_RET_STS_ERROR;
1717
1718 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1719
1720 x_return_status := G_RET_STS_UNEXP_ERROR;
1721
1722 WHEN OTHERS THEN
1723
1724 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1725 p_msg_name => G_DB_ERROR,
1726 p_token1 => G_PROG_NAME_TOKEN,
1727 p_token1_value => l_api_name,
1728 p_token2 => G_SQLCODE_TOKEN,
1729 p_token2_value => sqlcode,
1730 p_token3 => G_SQLERRM_TOKEN,
1731 p_token3_value => sqlerrm);
1732
1733 x_return_status := G_RET_STS_UNEXP_ERROR;
1734 END copy_configuration_lines;
1735
1736 -------------------------------------
1737 -- PROCEDURE copy_addtl_fees_services
1738 -------------------------------------
1739 PROCEDURE copy_addtl_fees_services(p_api_version IN NUMBER,
1740 p_init_msg_list IN VARCHAR2,
1741 p_source_quote_id IN NUMBER,
1742 p_target_quote_id IN NUMBER,
1743 x_msg_count OUT NOCOPY NUMBER,
1744 x_msg_data OUT NOCOPY VARCHAR2,
1745 x_return_status OUT NOCOPY VARCHAR2) IS
1746
1747 l_program_name CONSTANT VARCHAR2(30) := 'copy_addtl_fees_services';
1748 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1749
1750 x_service_id NUMBER;
1751 x_fee_id NUMBER;
1752 --Added by jjuneja
1753 x_insurance_est_id NUMBER;
1754 --End of addition by jjuneja
1755
1756 CURSOR c_get_services IS
1757 SELECT id
1758 FROM OKL_SERVICES_B
1759 WHERE PARENT_OBJECT_ID = p_source_quote_id
1760 AND PARENT_OBJECT_CODE = 'LEASEQUOTE';
1761
1762 CURSOR c_get_nonconfig_fees IS
1763 SELECT id
1764 FROM OKL_FEES_B
1765 WHERE PARENT_OBJECT_ID = p_source_quote_id
1766 AND PARENT_OBJECT_CODE = 'LEASEQUOTE'
1767 AND FEE_TYPE NOT IN ('FINANCED', 'CAPITALIZED', 'ROLLOVER');
1768
1769 --Added by jjuneja
1770 CURSOR c_get_insurance_estimate IS
1771 SELECT id
1772 FROM OKL_INSURANCE_ESTIMATES_B
1773 WHERE lease_quote_id = p_source_quote_id ;
1774 --End of addtion by jjuneja
1775 BEGIN
1776
1777 -- Copy Services
1778 FOR l_get_services IN c_get_services LOOP
1779 OKL_LEASE_QUOTE_SERVICE_PVT.duplicate_service (
1780 p_api_version => p_api_version
1781 ,p_init_msg_list => 'T'
1782 ,p_transaction_control => 'T'
1783 ,p_source_service_id => l_get_services.id
1784 ,p_target_quote_id => p_target_quote_id
1785 ,x_service_id => x_service_id
1786 ,x_return_status => x_return_status
1787 ,x_msg_count => x_msg_count
1788 ,x_msg_data => x_msg_data );
1789 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1790 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1791 ELSIF x_return_status = G_RET_STS_ERROR THEN
1792 RAISE OKL_API.G_EXCEPTION_ERROR;
1793 END IF;
1794 END LOOP;
1795
1796 -- Copy Non-Configuration Fees
1797 FOR l_get_nonconfig_fees IN c_get_nonconfig_fees LOOP
1798 OKL_LEASE_QUOTE_FEE_PVT.duplicate_fee (
1799 p_api_version => p_api_version
1800 ,p_init_msg_list => 'T'
1801 ,p_transaction_control => 'T'
1802 ,p_source_fee_id => l_get_nonconfig_fees.id
1803 ,p_target_quote_id => p_target_quote_id
1804 ,x_fee_id => x_fee_id
1805 ,x_return_status => x_return_status
1806 ,x_msg_count => x_msg_count
1807 ,x_msg_data => x_msg_data );
1808 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1809 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1810 ELSIF x_return_status = G_RET_STS_ERROR THEN
1811 RAISE OKL_API.G_EXCEPTION_ERROR;
1812 END IF;
1813
1814 END LOOP;
1815
1816 --Added by jjuneja
1817 --Copy Insurance Estimates
1818 FOR l_get_insurance_estimate IN c_get_insurance_estimate LOOP
1819 OKL_LEASE_QUOTE_INS_PVT.duplicate_insurance_estimate (
1820 p_api_version => p_api_version
1821 ,p_init_msg_list => 'T'
1822 ,p_transaction_control => 'T'
1823 ,p_source_insurance_id => l_get_insurance_estimate.id
1824 ,p_target_quote_id => p_target_quote_id
1825 ,x_insurance_est_id => x_insurance_est_id
1826 ,x_return_status => x_return_status
1827 ,x_msg_count => x_msg_count
1828 ,x_msg_data => x_msg_data );
1829 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1831 ELSIF x_return_status = G_RET_STS_ERROR THEN
1832 RAISE OKL_API.G_EXCEPTION_ERROR;
1833 END IF;
1834 END LOOP;
1835 --End of addition by jjuneja
1836
1837 x_return_status := G_RET_STS_SUCCESS;
1838
1839 EXCEPTION
1840
1841 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1842
1843 x_return_status := G_RET_STS_ERROR;
1844
1845 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1846
1847 x_return_status := G_RET_STS_UNEXP_ERROR;
1848
1849 WHEN OTHERS THEN
1850
1851 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1852 p_msg_name => G_DB_ERROR,
1853 p_token1 => G_PROG_NAME_TOKEN,
1854 p_token1_value => l_api_name,
1855 p_token2 => G_SQLCODE_TOKEN,
1856 p_token2_value => sqlcode,
1857 p_token3 => G_SQLERRM_TOKEN,
1858 p_token3_value => sqlerrm);
1859
1860 x_return_status := G_RET_STS_UNEXP_ERROR;
1861 END copy_addtl_fees_services;
1862
1863 -------------------------------------
1864 -- PROCEDURE copy_cost_adjustments
1865 -------------------------------------
1866 PROCEDURE copy_cost_adjustments(p_api_version IN NUMBER,
1867 p_init_msg_list IN VARCHAR2,
1868 p_source_quote_id IN NUMBER,
1869 p_target_quote_id IN NUMBER,
1870 x_msg_count OUT NOCOPY NUMBER,
1871 x_msg_data OUT NOCOPY VARCHAR2,
1872 x_return_status OUT NOCOPY VARCHAR2) IS
1873
1874 l_program_name CONSTANT VARCHAR2(30) := 'copy_cost_adjustments';
1875 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1876
1877 ln_src_eot_id NUMBER;
1878 ln_tgt_eot_id NUMBER;
1879
1880 lb_dup_adj BOOLEAN:= TRUE;
1881
1882 BEGIN
1883
1884 -- Validation to check if the End-of-Term Option for source
1885 -- and target quotes are equal, if not assets are not copied, which is
1886 -- taken care in asset api, adjustments which have mandatory asset association
1887 -- are also not not copied
1888 SELECT end_of_term_option_id
1889 INTO ln_src_eot_id
1890 FROM
1891 okl_lease_quotes_b
1892 WHERE
1893 id = p_source_quote_id;
1894
1895 SELECT end_of_term_option_id
1896 INTO ln_tgt_eot_id
1897 FROM
1898 okl_lease_quotes_b
1899 WHERE
1900 id = p_target_quote_id;
1901
1902 IF (ln_src_eot_id <> ln_tgt_eot_id) THEN
1903 lb_dup_adj := FALSE;
1904 END IF;
1905
1906 IF (lb_dup_adj) THEN
1907 OKL_LEASE_QUOTE_ASSET_PVT.duplicate_adjustments (
1908 p_api_version => p_api_version
1909 ,p_init_msg_list => p_init_msg_list
1910 ,p_source_quote_id => p_source_quote_id
1911 ,p_target_quote_id => p_target_quote_id
1912 ,x_return_status => x_return_status
1913 ,x_msg_count => x_msg_count
1914 ,x_msg_data => x_msg_data );
1915 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1916 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1917 ELSIF x_return_status = G_RET_STS_ERROR THEN
1918 RAISE OKL_API.G_EXCEPTION_ERROR;
1919 END IF;
1920 END IF;
1921
1922 x_return_status := G_RET_STS_SUCCESS;
1923
1924 EXCEPTION
1925
1926 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1927
1928 x_return_status := G_RET_STS_ERROR;
1929
1930 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1931
1932 x_return_status := G_RET_STS_UNEXP_ERROR;
1933
1934 WHEN OTHERS THEN
1935
1936 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1937 p_msg_name => G_DB_ERROR,
1938 p_token1 => G_PROG_NAME_TOKEN,
1939 p_token1_value => l_api_name,
1940 p_token2 => G_SQLCODE_TOKEN,
1941 p_token2_value => sqlcode,
1942 p_token3 => G_SQLERRM_TOKEN,
1943 p_token3_value => sqlerrm);
1944
1945 x_return_status := G_RET_STS_UNEXP_ERROR;
1946 END copy_cost_adjustments;
1947
1948 --------------------------------
1949 -- PROCEDURE duplicate_lease_qte
1950 --------------------------------
1951 PROCEDURE duplicate_lease_qte (p_api_version IN NUMBER,
1952 p_init_msg_list IN VARCHAR2,
1953 p_transaction_control IN VARCHAR2,
1954 p_source_quote_id IN NUMBER,
1955 p_lease_qte_rec IN lease_qte_rec_type,
1956 x_lease_qte_rec OUT NOCOPY lease_qte_rec_type,
1957 x_return_status OUT NOCOPY VARCHAR2,
1958 x_msg_count OUT NOCOPY NUMBER,
1959 x_msg_data OUT NOCOPY VARCHAR2) IS
1960
1961 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_lease_qte';
1962 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1963
1964 p_target_quote_id NUMBER;
1965 lv_copy_pymnts_allowed VARCHAR2(1) := 'Y';
1966
1967 l_lease_qte_rec lease_qte_rec_type;
1968
1969 BEGIN
1970 IF p_transaction_control = G_TRUE THEN
1971 SAVEPOINT l_program_name;
1972 END IF;
1973
1974 IF p_init_msg_list = G_TRUE THEN
1975 FND_MSG_PUB.initialize;
1976 END IF;
1977
1978 l_lease_qte_rec := p_lease_qte_rec;
1979
1980 IF (l_lease_qte_rec.parent_object_code = 'LEASEAPP') THEN
1981 -- Check if the Source and Target Quote's Product and Exp Start date are equal
1982 lv_copy_pymnts_allowed := copy_quote_payments_allowed(p_source_quote_id => p_source_quote_id,
1983 p_target_pdt_id => p_lease_qte_rec.product_id,
1984 p_target_exp_start_date => p_lease_qte_rec.expected_start_date);
1985 ELSE
1986 -- Check if the Source and Target Quote's Pricing type are equal
1987 lv_copy_pymnts_allowed := is_pricing_method_equal(p_source_quote_id => p_source_quote_id,
1988 p_target_pricing_type => p_lease_qte_rec.pricing_method);
1989 IF (lv_copy_pymnts_allowed = 'Y') THEN
1990 -- Check if the Source and Target Quote's Product and Exp Start date are equal
1991 lv_copy_pymnts_allowed := copy_quote_payments_allowed(p_source_quote_id => p_source_quote_id,
1992 p_target_pdt_id => p_lease_qte_rec.product_id,
1993 p_target_exp_start_date => p_lease_qte_rec.expected_start_date);
1994 END IF;
1995 END IF;
1996
1997 -- This populates other quote attributes which are not visible from the
1998 -- duplicate quote page
1999 IF (lv_copy_pymnts_allowed = 'Y') THEN
2000 --Bug # 5021937 ssdeshpa start
2001 /*IF (l_lease_qte_rec.structured_pricing IS NULL AND
2002 l_lease_qte_rec.line_level_pricing IS NULL) THEN*/
2003 --Bug # 5021937 ssdeshpa end
2004 populate_quote_attribs(p_source_quote_id => p_source_quote_id,
2005 x_quote_rec => l_lease_qte_rec,
2006 x_return_status => x_return_status);
2007 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2008 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2009 ELSIF x_return_status = G_RET_STS_ERROR THEN
2010 RAISE OKL_API.G_EXCEPTION_ERROR;
2011 END IF;
2012
2013 --asawanka bug 4936130 changes start
2014 IF (l_lease_qte_rec.parent_object_code = 'LEASEAPP'
2015 AND l_lease_qte_rec.status = 'CT-ACCEPTED' ) THEN
2016 copy_yields(p_source_quote_id => p_source_quote_id,
2017 x_quote_rec => l_lease_qte_rec,
2018 x_return_status => x_return_status);
2019 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2020 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2021 ELSIF x_return_status = G_RET_STS_ERROR THEN
2022 RAISE OKL_API.G_EXCEPTION_ERROR;
2023 END IF;
2024 END IF;
2025 --asawanka bug 4936130 changes end
2026 -- END IF;
2027 END IF;
2028
2029 --Bug # 5021937 ssdeshpa start
2030 --If Pricing Method is Solve For Rate (SY) then Structured Pricing Flag
2031 --is always set to 'Y'
2032 IF(l_lease_qte_rec.pricing_method = 'SY') THEN
2033 l_lease_qte_rec.structured_pricing := 'Y';
2034 END IF;
2035 --Bug # 5021937 ssdeshpa end
2036
2037 --Bug 7022258-Added by kkorrapo
2038 --l_lease_qte_rec.reference_number := okl_util.get_next_seq_num('OKL_LSQ_REF_SEQ','OKL_LEASE_QUOTES_B','REFERENCE_NUMBER');
2039 --Bug 7022258--Addition end
2040
2041 create_lease_qte (p_api_version => p_api_version,
2042 p_init_msg_list => p_init_msg_list,
2043 p_transaction_control => p_transaction_control,
2044 p_lease_qte_rec => l_lease_qte_rec,
2045 x_lease_qte_rec => x_lease_qte_rec,
2046 x_return_status => x_return_status,
2047 x_msg_count => x_msg_count,
2048 x_msg_data => x_msg_data);
2049 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2050 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2051 ELSIF x_return_status = G_RET_STS_ERROR THEN
2052 RAISE OKL_API.G_EXCEPTION_ERROR;
2053 END IF;
2054
2055 p_target_quote_id := x_lease_qte_rec.id;
2056
2057 IF (lv_copy_pymnts_allowed = 'Y') THEN
2058 copy_quote_payments(p_api_version => p_api_version,
2059 p_init_msg_list => p_init_msg_list,
2060 p_source_quote_id => p_source_quote_id,
2061 p_target_quote_id => p_target_quote_id,
2062 x_msg_count => x_msg_count,
2063 x_msg_data => x_msg_data,
2064 x_return_status => x_return_status);
2065 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2066 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2067 ELSIF x_return_status = G_RET_STS_ERROR THEN
2068 RAISE OKL_API.G_EXCEPTION_ERROR;
2069 END IF;
2070 END IF;
2071
2072 copy_configuration_lines(p_api_version => p_api_version,
2073 p_init_msg_list => p_init_msg_list,
2074 p_source_quote_id => p_source_quote_id,
2075 p_target_quote_id => p_target_quote_id,
2076 x_msg_count => x_msg_count,
2077 x_msg_data => x_msg_data,
2078 x_return_status => x_return_status);
2079 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2080 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2081 ELSIF x_return_status = G_RET_STS_ERROR THEN
2082 RAISE OKL_API.G_EXCEPTION_ERROR;
2083 END IF;
2084
2085 copy_cost_adjustments(p_api_version => p_api_version,
2086 p_init_msg_list => p_init_msg_list,
2087 p_source_quote_id => p_source_quote_id,
2088 p_target_quote_id => p_target_quote_id,
2089 x_msg_count => x_msg_count,
2090 x_msg_data => x_msg_data,
2091 x_return_status => x_return_status);
2092 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2093 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2094 ELSIF x_return_status = G_RET_STS_ERROR THEN
2095 RAISE OKL_API.G_EXCEPTION_ERROR;
2096 END IF;
2097
2098 copy_addtl_fees_services(p_api_version => p_api_version,
2099 p_init_msg_list => p_init_msg_list,
2100 p_source_quote_id => p_source_quote_id,
2101 p_target_quote_id => p_target_quote_id,
2102 x_msg_count => x_msg_count,
2103 x_msg_data => x_msg_data,
2104 x_return_status => x_return_status);
2105 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2106 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2107 ELSIF x_return_status = G_RET_STS_ERROR THEN
2108 RAISE OKL_API.G_EXCEPTION_ERROR;
2109 END IF;
2110
2111 -- Delete quote tax fee if one exists
2112 delete_quote_tax_fee( p_api_version => p_api_version
2113 ,p_init_msg_list => p_init_msg_list
2114 ,x_return_status => x_return_status
2115 ,x_msg_count => x_msg_count
2116 ,x_msg_data => x_msg_data
2117 ,p_quote_id => p_target_quote_id);
2118 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2119 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2120 ELSIF x_return_status = G_RET_STS_ERROR THEN
2121 RAISE OKL_API.G_EXCEPTION_ERROR;
2122 END IF;
2123
2124 x_return_status := G_RET_STS_SUCCESS;
2125
2126 EXCEPTION
2127
2128 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2129
2130 IF p_transaction_control = G_TRUE THEN
2131 ROLLBACK TO l_program_name;
2132 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2133 END IF;
2134
2135 x_return_status := G_RET_STS_ERROR;
2136
2137 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2138
2139 IF p_transaction_control = G_TRUE THEN
2140 ROLLBACK TO l_program_name;
2141 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2142 END IF;
2143
2144 x_return_status := G_RET_STS_UNEXP_ERROR;
2145
2146 WHEN OTHERS THEN
2147
2148 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2149 p_msg_name => G_DB_ERROR,
2150 p_token1 => G_PROG_NAME_TOKEN,
2151 p_token1_value => l_api_name,
2152 p_token2 => G_SQLCODE_TOKEN,
2153 p_token2_value => sqlcode,
2154 p_token3 => G_SQLERRM_TOKEN,
2155 p_token3_value => sqlerrm);
2156
2157 IF p_transaction_control = G_TRUE THEN
2158 ROLLBACK TO l_program_name;
2159 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2160 END IF;
2161
2162 x_return_status := G_RET_STS_UNEXP_ERROR;
2163
2164 END duplicate_lease_qte;
2165
2166 --------------------------------
2167 -- PROCEDURE duplicate_lease_qte
2168 --------------------------------
2169 PROCEDURE duplicate_lease_qte (p_api_version IN NUMBER,
2170 p_init_msg_list IN VARCHAR2,
2171 p_transaction_control IN VARCHAR2,
2172 p_quote_id IN NUMBER,
2173 x_lease_qte_rec OUT NOCOPY lease_qte_rec_type,
2174 x_return_status OUT NOCOPY VARCHAR2,
2175 x_msg_count OUT NOCOPY NUMBER,
2176 x_msg_data OUT NOCOPY VARCHAR2) IS
2177 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_lease_qte';
2178 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2179
2180 l_quote_rec lease_qte_rec_type;
2181
2182 BEGIN
2183 IF p_transaction_control = G_TRUE THEN
2184 SAVEPOINT l_program_name;
2185 END IF;
2186
2187 IF p_init_msg_list = G_TRUE THEN
2188 FND_MSG_PUB.initialize;
2189 END IF;
2190
2191 get_quote_rec ( p_quote_id => p_quote_id,
2192 x_quote_rec => l_quote_rec,
2193 x_return_status => x_return_status );
2194 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2195 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2196 ELSIF x_return_status = G_RET_STS_ERROR THEN
2197 RAISE OKL_API.G_EXCEPTION_ERROR;
2198 END IF;
2199
2200 -- Generate reference number
2201
2202 --Bug 7022258-Modified by kkorrapo
2203 -- SELECT OKL_LSQ_SEQ.nextval INTO l_quote_rec.reference_number FROM dual;
2204 l_quote_rec.reference_number := okl_util.get_next_seq_num('OKL_LSQ_REF_SEQ','OKL_LEASE_QUOTES_B','REFERENCE_NUMBER');
2205 --Bug 7022258--Modification end
2206
2207 l_quote_rec.status := 'PR-INCOMPLETE';
2208
2209 duplicate_lease_qte (p_api_version => p_api_version,
2210 p_init_msg_list => p_init_msg_list,
2211 p_transaction_control => p_transaction_control,
2212 p_source_quote_id => p_quote_id,
2213 p_lease_qte_rec => l_quote_rec,
2214 x_lease_qte_rec => x_lease_qte_rec,
2215 x_return_status => x_return_status,
2216 x_msg_count => x_msg_count,
2217 x_msg_data => x_msg_data);
2218 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2219 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2220 ELSIF x_return_status = G_RET_STS_ERROR THEN
2221 RAISE OKL_API.G_EXCEPTION_ERROR;
2222 END IF;
2223
2224 x_return_status := G_RET_STS_SUCCESS;
2225
2226 EXCEPTION
2227
2228 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2229
2230 IF p_transaction_control = G_TRUE THEN
2231 ROLLBACK TO l_program_name;
2232 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2233 END IF;
2234
2235 x_return_status := G_RET_STS_ERROR;
2236
2237 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2238
2239 IF p_transaction_control = G_TRUE THEN
2240 ROLLBACK TO l_program_name;
2241 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2242 END IF;
2243
2244 x_return_status := G_RET_STS_UNEXP_ERROR;
2245
2246 WHEN OTHERS THEN
2247
2248 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2249 p_msg_name => G_DB_ERROR,
2250 p_token1 => G_PROG_NAME_TOKEN,
2251 p_token1_value => l_api_name,
2252 p_token2 => G_SQLCODE_TOKEN,
2253 p_token2_value => sqlcode,
2254 p_token3 => G_SQLERRM_TOKEN,
2255 p_token3_value => sqlerrm);
2256
2257 IF p_transaction_control = G_TRUE THEN
2258 ROLLBACK TO l_program_name;
2259 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2260 END IF;
2261
2262 x_return_status := G_RET_STS_UNEXP_ERROR;
2263
2264 END duplicate_lease_qte;
2265
2266 ------------------------------------
2267 -- PROCEDURE cancel_lease_qte_childs
2268 ------------------------------------
2269 PROCEDURE cancel_lease_qte_childs (p_lease_qte_rec IN lease_qte_rec_type
2270 ,x_return_status OUT NOCOPY VARCHAR2
2271 ,x_msg_count OUT NOCOPY NUMBER
2272 ,x_msg_data OUT NOCOPY VARCHAR2) IS
2273
2274 l_program_name CONSTANT VARCHAR2(30) := 'cancel_lease_qte_childs';
2275 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2276
2277 l_return_status VARCHAR2(1);
2278
2279 BEGIN
2280
2281 -- ASO
2282 -- LRE
2283 -- cashflow
2284
2285 -- ASS
2286 DELETE FROM okl_assets_tl WHERE id IN
2287 (SELECT id FROM okl_assets_b WHERE parent_object_code = 'LEASEQUOTE' AND parent_object_id = p_lease_qte_rec.id);
2288 DELETE FROM okl_assets_b WHERE parent_object_code = 'LEASEQUOTE' AND parent_object_id = p_lease_qte_rec.id;
2289
2290 -- FEE
2291 DELETE FROM okl_fees_tl WHERE id IN
2292 (SELECT id FROM okl_fees_b WHERE parent_object_code = 'LEASEQUOTE' AND parent_object_id = p_lease_qte_rec.id);
2293 DELETE FROM okl_fees_b WHERE parent_object_code = 'LEASEQUOTE' AND parent_object_id = p_lease_qte_rec.id;
2294
2295 -- SVC
2296 DELETE FROM okl_services_tl WHERE id IN
2297 (SELECT id FROM okl_services_b WHERE parent_object_code = 'LEASEQUOTE' AND parent_object_id = p_lease_qte_rec.id);
2298 DELETE FROM okl_services_b WHERE parent_object_code = 'LEASEQUOTE' AND parent_object_id = p_lease_qte_rec.id;
2299
2300 -- QUE
2301 DELETE FROM okl_insurance_estimates_tl WHERE id IN
2302 (SELECT id FROM okl_insurance_estimates_b WHERE lease_quote_id = p_lease_qte_rec.id);
2303 DELETE FROM okl_insurance_estimates_b WHERE lease_quote_id = p_lease_qte_rec.id;
2304
2305 x_return_status := G_RET_STS_SUCCESS;
2306
2307 EXCEPTION
2308
2309 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2310
2311 x_return_status := G_RET_STS_ERROR;
2312
2313 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2314
2315 x_return_status := G_RET_STS_UNEXP_ERROR;
2316
2317 WHEN OTHERS THEN
2318
2319 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2320 p_msg_name => G_DB_ERROR,
2321 p_token1 => G_PROG_NAME_TOKEN,
2322 p_token1_value => l_api_name,
2323 p_token2 => G_SQLCODE_TOKEN,
2324 p_token2_value => sqlcode,
2325 p_token3 => G_SQLERRM_TOKEN,
2326 p_token3_value => sqlerrm);
2327
2328 x_return_status := G_RET_STS_UNEXP_ERROR;
2329
2330 END cancel_lease_qte_childs;
2331
2332
2333 ------------------------------
2334 -- PROCEDURE cancel_lease_qte
2335 ------------------------------
2336 PROCEDURE cancel_lease_qte (p_api_version IN NUMBER,
2337 p_init_msg_list IN VARCHAR2,
2338 p_transaction_control IN VARCHAR2,
2339 p_lease_qte_tbl IN lease_qte_tbl_type,
2340 x_return_status OUT NOCOPY VARCHAR2,
2341 x_msg_count OUT NOCOPY NUMBER,
2342 x_msg_data OUT NOCOPY VARCHAR2) IS
2343
2344 l_program_name CONSTANT VARCHAR2(30) := 'cancel_lease_qte';
2345 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2346
2347 i PLS_INTEGER;
2348
2349 l_return_status VARCHAR2(1);
2350
2351 BEGIN
2352
2353 IF p_transaction_control = G_TRUE THEN
2354 SAVEPOINT l_program_name;
2355 END IF;
2356
2357 IF p_init_msg_list = G_TRUE THEN
2358 FND_MSG_PUB.initialize;
2359 END IF;
2360
2361 IF p_lease_qte_tbl.COUNT > 0 THEN
2362
2363 FOR i IN p_lease_qte_tbl.FIRST .. p_lease_qte_tbl.LAST LOOP
2364
2365 IF p_lease_qte_tbl.EXISTS(i) THEN
2366
2367 cancel_quote_lines(p_api_version => p_api_version,
2368 p_init_msg_list => p_init_msg_list,
2369 p_quote_id => p_lease_qte_tbl(i).id,
2370 x_msg_count => x_msg_count,
2371 x_msg_data => x_msg_data,
2372 x_return_status => l_return_status);
2373 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2374 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2375 ELSIF l_return_status = G_RET_STS_ERROR THEN
2376 RAISE OKL_API.G_EXCEPTION_ERROR;
2377 END IF;
2378
2379 /*cancel_lease_qte_childs(p_lease_qte_rec => p_lease_qte_tbl(i)
2380 ,x_msg_count => x_msg_count
2381 ,x_msg_data => x_msg_data
2382 ,x_return_status => l_return_status);*/
2383
2384 okl_lsq_pvt.delete_row(p_api_version => G_API_VERSION
2385 ,p_init_msg_list => G_FALSE
2386 ,x_return_status => l_return_status
2387 ,x_msg_count => x_msg_count
2388 ,x_msg_data => x_msg_data
2389 ,p_lsqv_rec => p_lease_qte_tbl(i));
2390 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2391 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2392 ELSIF l_return_status = G_RET_STS_ERROR THEN
2393 RAISE OKL_API.G_EXCEPTION_ERROR;
2394 END IF;
2395
2396 END IF;
2397
2398 END LOOP;
2399
2400 END IF;
2401
2402 x_return_status := G_RET_STS_SUCCESS;
2403
2404 EXCEPTION
2405
2406 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2407
2408 IF p_transaction_control = G_TRUE THEN
2409 ROLLBACK TO l_program_name;
2410 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2411 END IF;
2412
2413 x_return_status := G_RET_STS_ERROR;
2414
2415 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2416
2417 IF p_transaction_control = G_TRUE THEN
2418 ROLLBACK TO l_program_name;
2419 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2420 END IF;
2421
2422 x_return_status := G_RET_STS_UNEXP_ERROR;
2423
2424 WHEN OTHERS THEN
2425
2426 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2427 p_msg_name => G_DB_ERROR,
2428 p_token1 => G_PROG_NAME_TOKEN,
2429 p_token1_value => l_api_name,
2430 p_token2 => G_SQLCODE_TOKEN,
2431 p_token2_value => sqlcode,
2432 p_token3 => G_SQLERRM_TOKEN,
2433 p_token3_value => sqlerrm);
2434
2435 IF p_transaction_control = G_TRUE THEN
2436 ROLLBACK TO l_program_name;
2437 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2438 END IF;
2439
2440 x_return_status := G_RET_STS_UNEXP_ERROR;
2441
2442 END cancel_lease_qte;
2443
2444 ------------------------------
2445 -- PROCEDURE submit_lease_qte
2446 ------------------------------
2447 PROCEDURE submit_lease_qte (p_api_version IN NUMBER,
2448 p_init_msg_list IN VARCHAR2,
2449 p_transaction_control IN VARCHAR2,
2450 p_quote_id IN NUMBER,
2451 x_return_status OUT NOCOPY VARCHAR2,
2452 x_msg_count OUT NOCOPY NUMBER,
2453 x_msg_data OUT NOCOPY VARCHAR2) IS
2454
2455 l_program_name CONSTANT VARCHAR2(30) := 'submit_lease_qte';
2456 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2457
2458 l_lease_qte_rec lease_qte_rec_type;
2459 x_lease_qte_rec lease_qte_rec_type;
2460
2461 -- Bug#9708535 - Added - Start
2462 CURSOR l_systemparams_csr IS
2463 SELECT NVL(tax_upfront_yn,'N')
2464 FROM OKL_SYSTEM_PARAMS;
2465
2466 -- Check if upfront tax calculation has happened for this quote
2467 CURSOR c_chk_tax_calculation (p_quote_id OKL_LEASE_QUOTES_B.ID%TYPE) IS
2468 SELECT 'X'
2469 FROM OKL_TAX_SOURCES
2470 WHERE tax_call_type_code = 'UPFRONT_TAX'
2471 AND ENTITY_CODE ='SALES_QUOTES'
2472 AND trx_id = p_quote_id;
2473
2474 l_tax_calcualted_yn VARCHAR2(1);
2475 l_ou_tax_upfront_yn VARCHAR2(1);
2476 -- Bug#9708535 - Added - End
2477
2478 BEGIN
2479
2480 IF p_transaction_control = G_TRUE THEN
2481 SAVEPOINT l_program_name;
2482 END IF;
2483
2484 IF p_init_msg_list = G_TRUE THEN
2485 FND_MSG_PUB.initialize;
2486 END IF;
2487
2488 get_quote_rec ( p_quote_id => p_quote_id,
2489 x_quote_rec => l_lease_qte_rec,
2490 x_return_status => x_return_status );
2491 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2492 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2493 ELSIF x_return_status = G_RET_STS_ERROR THEN
2494 RAISE OKL_API.G_EXCEPTION_ERROR;
2495 END IF;
2496
2497 --Validate IF Legal Entity Id is present on Quote Bug # 5647107
2498 --If Upfront Tax setup is complete/Changed
2499 validate_le_id(p_le_id => l_lease_qte_rec.legal_entity_id,
2500 p_parent_obj_code => l_lease_qte_rec.parent_object_code,
2501 x_return_status => x_return_status);
2502 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2503 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2504 ELSIF x_return_status = G_RET_STS_ERROR THEN
2505 RAISE OKL_API.G_EXCEPTION_ERROR;
2506 END IF;
2507
2508 -- Bug#9708535 - Added - Start
2509 IF (l_lease_qte_rec.parent_object_code = 'LEASEOPP') THEN
2510 -- Validate if tax calculation has happened if tax is enabled in system options
2511 OPEN l_systemparams_csr;
2512 FETCH l_systemparams_csr INTO l_ou_tax_upfront_yn;
2513 CLOSE l_systemparams_csr;
2514
2515 OPEN c_chk_tax_calculation(p_quote_id);
2516 FETCH c_chk_tax_calculation INTO l_tax_calcualted_yn;
2517 CLOSE C_CHK_TAX_CALCULATION;
2518
2519 -- If upfront tax calculation is enabled at system options, ensure that
2520 -- quote cannot be accepted until tax is calculated.
2521 IF (l_ou_tax_upfront_yn ='Y' AND l_tax_calcualted_yn IS NULL) THEN
2522 -- Quote QUOTE_NUMBER cannot be accepted until tax is calculated.
2523 -- Please calculate tax for this quote before accepting it.
2524 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2525 p_msg_name => 'OKL_TAXCALC_ACCEPT_QUOTE',
2526 p_token1 => 'QUOTE_NUMBER',
2527 p_token1_value => l_lease_qte_rec.reference_number);
2528 RAISE OKL_API.G_EXCEPTION_ERROR;
2529 END IF;
2530 END IF;
2531 -- Bug#9708535 - Added - End
2532
2533 -- Call Submit workflow
2534 okl_lease_quote_workflow_pvt.raise_quote_submit_event(p_quote_id => p_quote_id,
2535 x_return_status => x_return_status);
2536 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2537 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2538 ELSIF x_return_status = G_RET_STS_ERROR THEN
2539 RAISE OKL_API.G_EXCEPTION_ERROR;
2540 END IF;
2541
2542 -- Handle Subsidy pool usage
2543 IF (l_lease_qte_rec.parent_object_code = 'LEASEOPP') THEN
2544 okl_lease_quote_subpool_pvt.process_quote_subsidy_pool(
2545 p_api_version => G_API_VERSION
2546 ,p_init_msg_list => G_TRUE
2547 ,p_transaction_control => G_TRUE
2548 ,p_quote_id => p_quote_id
2549 ,p_transaction_reason => 'APPROVE_QUOTE'
2550 ,x_return_status => x_return_status
2551 ,x_msg_count => x_msg_count
2552 ,x_msg_data => x_msg_data);
2553 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2554 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2555 ELSIF x_return_status = G_RET_STS_ERROR THEN
2556 RAISE OKL_API.G_EXCEPTION_ERROR;
2557 END IF;
2558 END IF;
2559
2560 x_return_status := G_RET_STS_SUCCESS;
2561
2562 EXCEPTION
2563
2564 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2565
2566 IF p_transaction_control = G_TRUE THEN
2567 ROLLBACK TO l_program_name;
2568 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2569 END IF;
2570
2571 x_return_status := G_RET_STS_ERROR;
2572
2573 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2574
2575 IF p_transaction_control = G_TRUE THEN
2576 ROLLBACK TO l_program_name;
2577 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2578 END IF;
2579
2580 x_return_status := G_RET_STS_UNEXP_ERROR;
2581
2582 WHEN OTHERS THEN
2583
2584 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2585 p_msg_name => G_DB_ERROR,
2586 p_token1 => G_PROG_NAME_TOKEN,
2587 p_token1_value => l_api_name,
2588 p_token2 => G_SQLCODE_TOKEN,
2589 p_token2_value => sqlcode,
2590 p_token3 => G_SQLERRM_TOKEN,
2591 p_token3_value => sqlerrm);
2592
2593 IF p_transaction_control = G_TRUE THEN
2594 ROLLBACK TO l_program_name;
2595 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2596 END IF;
2597
2598 x_return_status := G_RET_STS_UNEXP_ERROR;
2599
2600 END submit_lease_qte;
2601
2602 ------------------------------
2603 -- PROCEDURE accept_lease_qte
2604 ------------------------------
2605 PROCEDURE accept_lease_qte (p_api_version IN NUMBER,
2606 p_init_msg_list IN VARCHAR2,
2607 p_transaction_control IN VARCHAR2,
2608 p_quote_id IN NUMBER,
2609 x_return_status OUT NOCOPY VARCHAR2,
2610 x_msg_count OUT NOCOPY NUMBER,
2611 x_msg_data OUT NOCOPY VARCHAR2) IS
2612
2613 l_program_name CONSTANT VARCHAR2(30) := 'accept_lease_qte';
2614 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2615
2616 l_lease_qte_rec lease_qte_rec_type;
2617 x_lease_qte_rec lease_qte_rec_type;
2618 l_lease_opp_rec okl_lop_pvt.lopv_rec_type;
2619 x_lease_opp_rec okl_lop_pvt.lopv_rec_type;
2620
2621 l_chk_lease_qte VARCHAR2(1) := 'N';
2622
2623 CURSOR c_chk_accept_lease_qte(p_leaseopp_id IN NUMBER) IS
2624 SELECT 'Y'
2625 FROM okl_lease_quotes_b
2626 WHERE parent_object_code = 'LEASEOPP'
2627 AND parent_object_id = p_leaseopp_id
2628 AND status = 'CT-ACCEPTED';
2629
2630 BEGIN
2631
2632 IF p_transaction_control = G_TRUE THEN
2633 SAVEPOINT l_program_name;
2634 END IF;
2635
2636 IF p_init_msg_list = G_TRUE THEN
2637 FND_MSG_PUB.initialize;
2638 END IF;
2639
2640 get_quote_rec ( p_quote_id => p_quote_id,
2641 x_quote_rec => l_lease_qte_rec,
2642 x_return_status => x_return_status );
2643 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2645 ELSIF x_return_status = G_RET_STS_ERROR THEN
2646 RAISE OKL_API.G_EXCEPTION_ERROR;
2647 END IF;
2648
2649 -- Check if any quote is accepted within this Lease Opportunity
2650 IF (l_lease_qte_rec.parent_object_code = 'LEASEOPP') THEN
2651 OPEN c_chk_accept_lease_qte(p_leaseopp_id => l_lease_qte_rec.parent_object_id);
2652 FETCH c_chk_accept_lease_qte INTO l_chk_lease_qte;
2653 CLOSE c_chk_accept_lease_qte;
2654
2655 IF (l_chk_lease_qte = 'Y') THEN
2656 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2657 p_msg_name => 'OKL_CANNOT_ACCEPT_QUOTE');
2658 RAISE OKL_API.G_EXCEPTION_ERROR;
2659 ELSE
2660
2661 --Validate IF Legal Entity Id is present on Quote Bug # 5647107
2662 --If Upfront Tax setup is complete/Changed
2663 validate_le_id(p_le_id => l_lease_qte_rec.legal_entity_id,
2664 p_parent_obj_code => l_lease_qte_rec.parent_object_code,
2665 x_return_status => x_return_status);
2666
2667 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2668 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2669 ELSIF x_return_status = G_RET_STS_ERROR THEN
2670 RAISE OKL_API.G_EXCEPTION_ERROR;
2671 END IF;
2672 -- Call Accept workflow
2673 okl_lease_quote_workflow_pvt.raise_quote_accept_event(p_quote_id => p_quote_id,
2674 x_return_status => x_return_status);
2675 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2676 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2677 ELSIF x_return_status = G_RET_STS_ERROR THEN
2678 RAISE OKL_API.G_EXCEPTION_ERROR;
2679 END IF;
2680
2681 -- Update the Lease opportunity with status to 'Accepted'
2682 get_leaseopp_rec ( p_leaseopp_id => l_lease_qte_rec.parent_object_id,
2683 x_leaseopp_rec => l_lease_opp_rec,
2684 x_return_status => x_return_status );
2685 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2686 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2687 ELSIF x_return_status = G_RET_STS_ERROR THEN
2688 RAISE OKL_API.G_EXCEPTION_ERROR;
2689 END IF;
2690
2691 l_lease_opp_rec.status := 'ACCEPTED';
2692 okl_lop_pvt.update_row(
2693 p_api_version => G_API_VERSION
2694 ,p_init_msg_list => G_FALSE
2695 ,x_return_status => x_return_status
2696 ,x_msg_count => x_msg_count
2697 ,x_msg_data => x_msg_data
2698 ,p_lopv_rec => l_lease_opp_rec
2699 ,x_lopv_rec => x_lease_opp_rec );
2700 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2701 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2702 ELSIF x_return_status = G_RET_STS_ERROR THEN
2703 RAISE OKL_API.G_EXCEPTION_ERROR;
2704 END IF;
2705 END IF;
2706 END IF;
2707
2708 x_return_status := G_RET_STS_SUCCESS;
2709
2710 EXCEPTION
2711
2712 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2713
2714 IF p_transaction_control = G_TRUE THEN
2715 ROLLBACK TO l_program_name;
2716 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2717 END IF;
2718
2719 x_return_status := G_RET_STS_ERROR;
2720
2721 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2722
2723 IF p_transaction_control = G_TRUE THEN
2724 ROLLBACK TO l_program_name;
2725 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2726 END IF;
2727
2728 x_return_status := G_RET_STS_UNEXP_ERROR;
2729
2730 WHEN OTHERS THEN
2731
2732 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2733 p_msg_name => G_DB_ERROR,
2734 p_token1 => G_PROG_NAME_TOKEN,
2735 p_token1_value => l_api_name,
2736 p_token2 => G_SQLCODE_TOKEN,
2737 p_token2_value => sqlcode,
2738 p_token3 => G_SQLERRM_TOKEN,
2739 p_token3_value => sqlerrm);
2740
2741 IF p_transaction_control = G_TRUE THEN
2742 ROLLBACK TO l_program_name;
2743 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2744 END IF;
2745
2746 x_return_status := G_RET_STS_UNEXP_ERROR;
2747
2748 END accept_lease_qte;
2749
2750 ------------------------------
2751 -- PROCEDURE duplicate_quotes
2752 ------------------------------
2753 PROCEDURE duplicate_quotes(p_api_version IN NUMBER,
2754 p_init_msg_list IN VARCHAR2,
2755 p_transaction_control IN VARCHAR2,
2756 p_source_leaseopp_id IN NUMBER,
2757 p_target_leaseopp_id IN NUMBER,
2758 x_return_status OUT NOCOPY VARCHAR2,
2759 x_msg_count OUT NOCOPY NUMBER,
2760 x_msg_data OUT NOCOPY VARCHAR2) IS
2761
2762 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_quotes';
2763 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2764
2765 CURSOR c_get_quotes IS
2766 SELECT id
2767 FROM OKL_LEASE_QUOTES_B
2768 WHERE PARENT_OBJECT_ID = p_source_leaseopp_id
2769 AND PARENT_OBJECT_CODE = 'LEASEOPP';
2770
2771 l_quote_rec lease_qte_rec_type;
2772 x_lease_qte_rec lease_qte_rec_type;
2773
2774 BEGIN
2775
2776 IF p_transaction_control = G_TRUE THEN
2777 SAVEPOINT l_program_name;
2778 END IF;
2779
2780 IF p_init_msg_list = G_TRUE THEN
2781 FND_MSG_PUB.initialize;
2782 END IF;
2783
2784 -- Copy Quotes
2785 FOR l_get_quotes IN c_get_quotes LOOP
2786
2787 get_quote_rec ( p_quote_id => l_get_quotes.id,
2788 x_quote_rec => l_quote_rec,
2789 x_return_status => x_return_status );
2790 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2791 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2792 ELSIF x_return_status = G_RET_STS_ERROR THEN
2793 RAISE OKL_API.G_EXCEPTION_ERROR;
2794 END IF;
2795
2796 -- Generate reference number
2797
2798 --Bug 7022258-Changed by kkorrapo
2799 -- SELECT OKL_LSQ_SEQ.nextval INTO l_quote_rec.reference_number FROM dual;
2800 l_quote_rec.reference_number := okl_util.get_next_seq_num('OKL_LSQ_REF_SEQ','OKL_LEASE_QUOTES_B','REFERENCE_NUMBER');
2801 --Bug 7022258--Change end
2802
2803 l_quote_rec.parent_object_id := p_target_leaseopp_id;
2804 l_quote_rec.status := 'PR-INCOMPLETE';
2805
2806 duplicate_lease_qte (p_api_version => p_api_version,
2807 p_init_msg_list => 'T',
2808 p_transaction_control => 'T',
2809 p_source_quote_id => l_get_quotes.id,
2810 p_lease_qte_rec => l_quote_rec,
2811 x_lease_qte_rec => x_lease_qte_rec,
2812 x_return_status => x_return_status,
2813 x_msg_count => x_msg_count,
2814 x_msg_data => x_msg_data);
2815 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2816 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2817 ELSIF x_return_status = G_RET_STS_ERROR THEN
2818 RAISE OKL_API.G_EXCEPTION_ERROR;
2819 END IF;
2820
2821 END LOOP;
2822
2823 x_return_status := G_RET_STS_SUCCESS;
2824
2825 EXCEPTION
2826
2827 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2828
2829 IF p_transaction_control = G_TRUE THEN
2830 ROLLBACK TO l_program_name;
2831 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2832 END IF;
2833
2834 x_return_status := G_RET_STS_ERROR;
2835
2836 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2837
2838 IF p_transaction_control = G_TRUE THEN
2839 ROLLBACK TO l_program_name;
2840 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2841 END IF;
2842
2843 x_return_status := G_RET_STS_UNEXP_ERROR;
2844
2845 WHEN OTHERS THEN
2846
2847 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2848 p_msg_name => G_DB_ERROR,
2849 p_token1 => G_PROG_NAME_TOKEN,
2850 p_token1_value => l_api_name,
2851 p_token2 => G_SQLCODE_TOKEN,
2852 p_token2_value => sqlcode,
2853 p_token3 => G_SQLERRM_TOKEN,
2854 p_token3_value => sqlerrm);
2855
2856 IF p_transaction_control = G_TRUE THEN
2857 ROLLBACK TO l_program_name;
2858 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2859 END IF;
2860
2861 x_return_status := G_RET_STS_UNEXP_ERROR;
2862
2863 END duplicate_quotes;
2864
2865 --------------------------------
2866 -- PROCEDURE change_quote_status
2867 --------------------------------
2868 PROCEDURE change_quote_status(p_quote_id IN NUMBER,
2869 p_qte_status IN VARCHAR2,
2870 x_return_status OUT NOCOPY VARCHAR2) IS
2871
2872 lx_return_status VARCHAR2(1);
2873 lx_msg_count NUMBER;
2874 lx_msg_data VARCHAR2(4000);
2875
2876 l_lease_qte_rec okl_lsq_pvt.lsqv_rec_type;
2877 x_lease_qte_rec okl_lsq_pvt.lsqv_rec_type;
2878
2879 -- Bug 4713798 - Added cursor
2880 CURSOR c_obj
2881 IS
2882 SELECT object_version_number
2883 FROM okl_lease_quotes_b
2884 WHERE id = p_quote_id;
2885
2886 BEGIN
2887
2888 l_lease_qte_rec.id := p_quote_id;
2889 l_lease_qte_rec.status := p_qte_status;
2890
2891 OPEN c_obj;
2892 FETCH c_obj INTO l_lease_qte_rec.object_version_number;
2893 CLOSE c_obj;
2894
2895 okl_lsq_pvt.update_row(p_api_version => G_API_VERSION
2896 ,p_init_msg_list => G_FALSE
2897 ,x_return_status => lx_return_status
2898 ,x_msg_count => lx_msg_count
2899 ,x_msg_data => lx_msg_data
2900 ,p_lsqv_rec => l_lease_qte_rec
2901 ,x_lsqv_rec => x_lease_qte_rec );
2902
2903 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2904 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2905 ELSIF lx_return_status = G_RET_STS_ERROR THEN
2906 RAISE OKL_API.G_EXCEPTION_ERROR;
2907 END IF;
2908
2909 x_return_status := lx_return_status;
2910
2911 EXCEPTION
2912
2913 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2914
2915 x_return_status := G_RET_STS_ERROR;
2916
2917 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2918
2919 x_return_status := G_RET_STS_UNEXP_ERROR;
2920
2921 WHEN OTHERS THEN
2922
2923 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2924 p_msg_name => G_UNEXPECTED_ERROR,
2925 p_token1 => G_SQLCODE_TOKEN,
2926 p_token1_value => sqlcode,
2927 p_token2 => G_SQLERRM_TOKEN,
2928 p_token2_value => sqlerrm);
2929
2930 x_return_status := G_RET_STS_UNEXP_ERROR;
2931
2932 END change_quote_status;
2933
2934 ------------------------------
2935 -- PROCEDURE unaccept_lease_qte
2936 ------------------------------
2937 PROCEDURE unaccept_lease_qte (p_api_version IN NUMBER,
2938 p_init_msg_list IN VARCHAR2,
2939 p_transaction_control IN VARCHAR2,
2940 p_quote_id IN NUMBER,
2941 x_return_status OUT NOCOPY VARCHAR2,
2942 x_msg_count OUT NOCOPY NUMBER,
2943 x_msg_data OUT NOCOPY VARCHAR2) IS
2944
2945 l_program_name CONSTANT VARCHAR2(30) := 'unacpt_qte';
2946 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2947
2948 l_lease_qte_rec lease_qte_rec_type;
2949 x_lease_qte_rec lease_qte_rec_type;
2950 l_lease_opp_rec okl_lop_pvt.lopv_rec_type;
2951 x_lease_opp_rec okl_lop_pvt.lopv_rec_type;
2952
2953 l_chk_lease_qte VARCHAR2(1) := 'N';
2954
2955 CURSOR c_chk_accept_lease_qte(p_leaseopp_id IN NUMBER) IS
2956 SELECT 'Y'
2957 FROM okl_lease_quotes_b
2958 WHERE parent_object_code = 'LEASEOPP'
2959 AND parent_object_id = p_leaseopp_id
2960 AND status = 'CT-ACCEPTED';
2961
2962 BEGIN
2963
2964 IF p_transaction_control = G_TRUE THEN
2965 SAVEPOINT l_program_name;
2966 END IF;
2967
2968 IF p_init_msg_list = G_TRUE THEN
2969 FND_MSG_PUB.initialize;
2970 END IF;
2971
2972 get_quote_rec ( p_quote_id => p_quote_id,
2973 x_quote_rec => l_lease_qte_rec,
2974 x_return_status => x_return_status );
2975 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2976 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2977 ELSIF x_return_status = G_RET_STS_ERROR THEN
2978 RAISE OKL_API.G_EXCEPTION_ERROR;
2979 END IF;
2980
2981 change_quote_status(p_quote_id => p_quote_id,
2982 p_qte_status => 'PR-APPROVED',
2983 x_return_status => x_return_status);
2984
2985 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2986 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2987 ELSIF x_return_status = G_RET_STS_ERROR THEN
2988 RAISE OKL_API.G_EXCEPTION_ERROR;
2989 END IF;
2990
2991 -- Check if any quote is accepted within this Lease Opportunity
2992 IF (l_lease_qte_rec.parent_object_code = 'LEASEOPP') THEN
2993 -- Update the Lease opportunity with status to 'Incomplete'
2994 get_leaseopp_rec ( p_leaseopp_id => l_lease_qte_rec.parent_object_id,
2995 x_leaseopp_rec => l_lease_opp_rec,
2996 x_return_status => x_return_status );
2997 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2998 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2999 ELSIF x_return_status = G_RET_STS_ERROR THEN
3000 RAISE OKL_API.G_EXCEPTION_ERROR;
3001 END IF;
3002
3003 l_lease_opp_rec.status := 'INCOMPLETE';
3004 okl_lop_pvt.update_row(
3005 p_api_version => G_API_VERSION
3006 ,p_init_msg_list => G_FALSE
3007 ,x_return_status => x_return_status
3008 ,x_msg_count => x_msg_count
3009 ,x_msg_data => x_msg_data
3010 ,p_lopv_rec => l_lease_opp_rec
3011 ,x_lopv_rec => x_lease_opp_rec );
3012 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3013 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3014 ELSIF x_return_status = G_RET_STS_ERROR THEN
3015 RAISE OKL_API.G_EXCEPTION_ERROR;
3016 END IF;
3017 END IF;
3018
3019 x_return_status := G_RET_STS_SUCCESS;
3020
3021 EXCEPTION
3022
3023 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3024
3025 IF p_transaction_control = G_TRUE THEN
3026 ROLLBACK TO l_program_name;
3027 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3028 END IF;
3029
3030 x_return_status := G_RET_STS_ERROR;
3031
3032 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3033
3034 IF p_transaction_control = G_TRUE THEN
3035 ROLLBACK TO l_program_name;
3036 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3037 END IF;
3038
3039 x_return_status := G_RET_STS_UNEXP_ERROR;
3040
3041 WHEN OTHERS THEN
3042
3043 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3044 p_msg_name => G_DB_ERROR,
3045 p_token1 => G_PROG_NAME_TOKEN,
3046 p_token1_value => l_api_name,
3047 p_token2 => G_SQLCODE_TOKEN,
3048 p_token2_value => sqlcode,
3049 p_token3 => G_SQLERRM_TOKEN,
3050 p_token3_value => sqlerrm);
3051
3052 IF p_transaction_control = G_TRUE THEN
3053 ROLLBACK TO l_program_name;
3054 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3055 END IF;
3056
3057 x_return_status := G_RET_STS_UNEXP_ERROR;
3058
3059 END unaccept_lease_qte;
3060 ------------------------------------------------------------------------------
3061 --Bug 5171476 ssdeshpa start
3062 --------------------------
3063 -- PROCEDURE get_asset_rec
3064 --------------------------
3065 PROCEDURE get_asset_rec (
3066 p_asset_id IN NUMBER
3067 ,x_asset_rec OUT NOCOPY okl_ass_pvt.assv_rec_type
3068 ,x_return_status OUT NOCOPY VARCHAR2) IS
3069
3070 l_program_name CONSTANT VARCHAR2(30) := 'get_asset_rec';
3071 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3072
3073 BEGIN
3074
3075 SELECT
3076 id
3077 ,attribute_category
3078 ,attribute1
3079 ,attribute2
3080 ,attribute3
3081 ,attribute4
3082 ,attribute5
3083 ,attribute6
3084 ,attribute7
3085 ,attribute8
3086 ,attribute9
3087 ,attribute10
3088 ,attribute11
3089 ,attribute12
3090 ,attribute13
3091 ,attribute14
3092 ,attribute15
3093 ,object_version_number
3094 ,asset_number
3095 ,parent_object_id
3096 ,parent_object_code
3097 ,install_site_id
3098 ,rate_card_id
3099 ,rate_template_id
3100 ,oec
3101 ,end_of_term_value_default
3102 ,end_of_term_value
3103 ,oec_percentage
3104 ,structured_pricing
3105 ,target_arrears
3106 ,lease_rate_factor
3107 ,target_amount
3108 ,target_frequency
3109 ,short_description
3110 ,description
3111 ,comments
3112 INTO
3113 x_asset_rec.id
3114 ,x_asset_rec.attribute_category
3115 ,x_asset_rec.attribute1
3116 ,x_asset_rec.attribute2
3117 ,x_asset_rec.attribute3
3118 ,x_asset_rec.attribute4
3119 ,x_asset_rec.attribute5
3120 ,x_asset_rec.attribute6
3121 ,x_asset_rec.attribute7
3122 ,x_asset_rec.attribute8
3123 ,x_asset_rec.attribute9
3124 ,x_asset_rec.attribute10
3125 ,x_asset_rec.attribute11
3126 ,x_asset_rec.attribute12
3127 ,x_asset_rec.attribute13
3128 ,x_asset_rec.attribute14
3129 ,x_asset_rec.attribute15
3130 ,x_asset_rec.object_version_number
3131 ,x_asset_rec.asset_number
3132 ,x_asset_rec.parent_object_id
3133 ,x_asset_rec.parent_object_code
3134 ,x_asset_rec.install_site_id
3135 ,x_asset_rec.rate_card_id
3136 ,x_asset_rec.rate_template_id
3137 ,x_asset_rec.oec
3138 ,x_asset_rec.end_of_term_value_default
3139 ,x_asset_rec.end_of_term_value
3140 ,x_asset_rec.oec_percentage
3141 ,x_asset_rec.structured_pricing
3142 ,x_asset_rec.target_arrears
3143 ,x_asset_rec.lease_rate_factor
3144 ,x_asset_rec.target_amount
3145 ,x_asset_rec.target_frequency
3146 ,x_asset_rec.short_description
3147 ,x_asset_rec.description
3148 ,x_asset_rec.comments
3149 FROM okl_assets_v
3150 WHERE id = p_asset_id;
3151
3152 x_return_status := G_RET_STS_SUCCESS;
3153
3154 EXCEPTION
3155
3156 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3157 x_return_status := G_RET_STS_ERROR;
3158
3159 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3160 x_return_status := G_RET_STS_UNEXP_ERROR;
3161
3162 WHEN OTHERS THEN
3163 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3164 p_msg_name => G_DB_ERROR,
3165 p_token1 => G_PROG_NAME_TOKEN,
3166 p_token1_value => l_program_name,
3167 p_token2 => G_SQLCODE_TOKEN,
3168 p_token2_value => sqlcode,
3169 p_token3 => G_SQLERRM_TOKEN,
3170 p_token3_value => sqlerrm);
3171
3172 x_return_status := G_RET_STS_UNEXP_ERROR;
3173
3174 END get_asset_rec;
3175 ------------------------------------------------------------------------------
3176 PROCEDURE update_cash_flows(p_quote_id NUMBER
3177 ,p_source_object_code IN VARCHAR2
3178 ,p_source_object_id IN NUMBER
3179 ,x_return_status OUT NOCOPY VARCHAR2
3180 ,x_msg_count OUT NOCOPY NUMBER
3181 ,x_msg_data OUT NOCOPY VARCHAR2) IS
3182
3183
3184 CURSOR c_get_cashflow_info(p_src_id OKL_CASH_FLOW_OBJECTS.SOURCE_ID%TYPE
3185 ,p_oty_code OKL_CASH_FLOW_OBJECTS.OTY_CODE%TYPE
3186 ,p_source_table OKL_CASH_FLOW_OBJECTS.SOURCE_TABLE%TYPE)
3187 IS
3188 SELECT CFLOW.ID , CFLOW.OBJECT_VERSION_NUMBER
3189 FROM OKL_CASH_FLOWS CFLOW, OKL_CASH_FLOW_OBJECTS CFO
3190 WHERE CFO.SOURCE_ID = p_src_id
3191 AND CFO.OTY_CODE = p_oty_code
3192 AND CFO.SOURCE_TABLE=p_source_table
3193 AND CFLOW.CFO_ID = CFO.ID;
3194
3195 lp_source_table OKL_CASH_FLOW_OBJECTS.SOURCE_TABLE%TYPE;
3196 lv_stream_type_purpose VARCHAR2(150);
3197 i BINARY_INTEGER := 0;
3198 l_program_name CONSTANT VARCHAR2(30) := 'ppltu_cfl';
3199 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3200 l_api_version NUMBER := 1.0;
3201 lp_cafv_tbl OKL_CAF_PVT.cafv_tbl_type;
3202 lx_cafv_tbl OKL_CAF_PVT.cafv_tbl_type;
3203
3204 lx_error_tbl OKL_API.ERROR_TBL_TYPE;
3205 BEGIN
3206 IF(p_source_object_code = 'QUOTED_ASSET') THEN
3207 lp_source_table := 'OKL_ASSETS_B';
3208 ELSIF(p_source_object_code = 'LEASE_QUOTE') THEN
3209 lp_source_table := 'OKL_LEASE_QUOTES_B';
3210 END IF;
3211 i := 1;
3212 FOR l_get_cashflow_object_info IN c_get_cashflow_info(p_src_id => p_source_object_id
3213 ,p_oty_code => p_source_object_code
3214 ,p_source_table => lp_source_table)LOOP
3215 lp_cafv_tbl(i).id := l_get_cashflow_object_info.id;
3216 lp_cafv_tbl(i).sts_code := 'CURRENT';
3217 lp_cafv_tbl(i).object_version_number :=l_get_cashflow_object_info.object_version_number;
3218 i := i + 1;
3219
3220 END LOOP;
3221
3222 OKL_CAF_PVT.update_row(p_api_version => l_api_version
3223 ,p_init_msg_list => G_FALSE
3224 ,p_cafv_tbl => lp_cafv_tbl
3225 ,x_cafv_tbl => lx_cafv_tbl
3226 ,px_error_tbl => lx_error_tbl
3227 ,x_return_status => x_return_status
3228 ,x_msg_count => x_msg_count
3229 ,x_msg_data => x_msg_data);
3230
3231 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3232 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3233 ELSIF x_return_status = G_RET_STS_ERROR THEN
3234 RAISE OKL_API.G_EXCEPTION_ERROR;
3235 END IF;
3236
3237 EXCEPTION
3238
3239 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3240 x_return_status := G_RET_STS_ERROR;
3241
3242 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3243 x_return_status := G_RET_STS_UNEXP_ERROR;
3244
3245 WHEN OTHERS THEN
3246 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3247 p_msg_name => G_DB_ERROR,
3248 p_token1 => G_PROG_NAME_TOKEN,
3249 p_token1_value => l_program_name,
3250 p_token2 => G_SQLCODE_TOKEN,
3251 p_token2_value => sqlcode,
3252 p_token3 => G_SQLERRM_TOKEN,
3253 p_token3_value => sqlerrm);
3254
3255 x_return_status := G_RET_STS_UNEXP_ERROR;
3256
3257
3258
3259 END update_cash_flows;
3260
3261 ------------------------------------------------------------------------------
3262 PROCEDURE change_pricing (p_api_version IN NUMBER,
3263 p_init_msg_list IN VARCHAR2,
3264 p_transaction_control IN VARCHAR2,
3265 p_quote_id IN NUMBER,
3266 x_return_status OUT NOCOPY VARCHAR2,
3267 x_msg_count OUT NOCOPY NUMBER,
3268 x_msg_data OUT NOCOPY VARCHAR2) IS
3269
3270 l_program_name CONSTANT VARCHAR2(30) := 'chng_prcng';
3271 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3272
3273 lp_lease_qte_rec lease_qte_rec_type;
3274 lx_lease_qte_rec lease_qte_rec_type;
3275
3276 lp_asset_count NUMBER;
3277 i NUMBER := 1;
3278 lp_asset_tbl okl_ass_pvt.assv_tbl_type;
3279 lx_asset_rec okl_ass_pvt.assv_rec_type;
3280
3281 lp_cashflow_header_rec OKL_LEASE_QUOTE_CASHFLOW_PVT.cashflow_header_rec_type;
3282 lp_cashflow_level_tbl OKL_LEASE_QUOTE_CASHFLOW_PVT.cashflow_level_tbl_type;
3283
3284
3285 CURSOR get_asset_count(lp_quote_id NUMBER) IS
3286 SELECT COUNT(*)
3287 FROM OKL_ASSETS_B
3288 where parent_object_code = 'LEASEQUOTE'
3289 AND parent_object_id = lp_quote_id;
3290
3291 CURSOR c_get_quote_assets(p_parent_object_id NUMBER) IS
3292 select OAB.id
3293 FROM OKL_LEASE_QUOTES_B OLQ,OKL_ASSETS_B OAB
3294 where OAB.PARENT_OBJECT_ID = OLQ.ID
3295 AND OAB.PARENT_OBJECT_CODE='LEASEQUOTE'
3296 AND OLQ.ID= p_parent_object_id;
3297
3298 begin
3299 IF p_transaction_control = G_TRUE THEN
3300 SAVEPOINT l_program_name;
3301 END IF;
3302
3303 IF p_init_msg_list = G_TRUE THEN
3304 FND_MSG_PUB.initialize;
3305 END IF;
3306
3307 --Get Lease Quote Rec
3308 get_quote_rec( p_quote_id => p_quote_id
3309 ,x_quote_rec => lp_lease_qte_rec
3310 ,x_return_status => x_return_status );
3311 lp_lease_qte_rec.id := p_quote_id;
3312
3313 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3314 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3315 ELSIF x_return_status = G_RET_STS_ERROR THEN
3316 RAISE OKL_API.G_EXCEPTION_ERROR;
3317 END IF;
3318
3319 OPEN get_asset_count(p_quote_id);
3320 FETCH get_asset_count INTO lp_asset_count;
3321 CLOSE get_asset_count;
3322 i := 1;
3323 FOR quote_asset_rec IN c_get_quote_assets(p_quote_id) LOOP
3324
3325 get_asset_rec(p_asset_id => quote_asset_rec.id
3326 ,x_asset_rec => lp_asset_tbl(i)
3327 ,x_return_status => x_return_status);
3328 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3329 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3330 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3331 RAISE OKL_API.G_EXCEPTION_ERROR;
3332 END IF;
3333 i := i + 1;
3334 END LOOP;
3335 --Delete all Lease Quote Consolidated Cash Flows
3336 OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows (p_api_version => G_API_VERSION
3337 ,p_init_msg_list => G_FALSE
3338 ,p_transaction_control => G_FALSE
3339 ,p_source_object_code => 'LEASE_QUOTE_CONSOLIDATED'
3340 ,p_source_object_id => p_quote_id
3341 ,x_return_status => x_return_status
3342 ,x_msg_count => x_msg_count
3343 ,x_msg_data => x_msg_data);
3344
3345 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3346 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3347 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3348 RAISE OKL_API.G_EXCEPTION_ERROR;
3349 END IF;
3350 IF(lp_asset_count > 1) THEN
3351 lp_lease_qte_rec.STRUCTURED_PRICING := NULL;
3352 lp_lease_qte_rec.LINE_LEVEL_PRICING := 'Y';
3353 lp_lease_qte_rec.RATE_TEMPLATE_ID := NULL;
3354 lp_lease_qte_rec.TARGET_AMOUNT := NULL;
3355
3356
3357 OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows (
3358 p_api_version => G_API_VERSION
3359 ,p_init_msg_list => G_FALSE
3360 ,p_transaction_control => G_TRUE --Check this
3361 ,p_source_object_code => 'LEASE_QUOTE'
3362 ,p_source_object_id => p_quote_id
3363 ,x_return_status => x_return_status
3364 ,x_msg_count => x_msg_count
3365 ,x_msg_data => x_msg_data);
3366
3367 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3368 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3369 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3370 RAISE OKL_API.G_EXCEPTION_ERROR;
3371 END IF;
3372 --Delete Cash Flows for Quote Completed
3373 --Loop on All Assets ;Set Sp=Y Nullified SRT and change all cash flows from
3374 --Work to Current
3375 FOR i IN lp_asset_tbl.FIRST..lp_asset_tbl.LAST LOOP
3376 IF(lp_asset_tbl.EXISTS(i)) THEN
3377 --Reset Pricing Values
3378 lp_asset_tbl(i).structured_pricing :='Y';
3379 lp_asset_tbl(i).rate_template_id := NULL;
3380 lp_asset_tbl(i).TARGET_AMOUNT := NULL;
3381 lp_asset_tbl(i).TARGET_FREQUENCY := NULL;
3382 --Reset Pricing Values
3383
3384 update_cash_flows(p_quote_id => p_quote_id
3385 ,p_source_object_code => 'QUOTED_ASSET'
3386 ,p_source_object_id => lp_asset_tbl(i).id
3387 ,x_return_status => x_return_status
3388 ,x_msg_count => x_msg_count
3389 ,x_msg_data => x_msg_data);
3390 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3391 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3392 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3393 RAISE OKL_API.G_EXCEPTION_ERROR;
3394 END IF;
3395
3396 okl_ass_pvt.update_row (p_api_version => G_API_VERSION
3397 ,p_init_msg_list => G_FALSE
3398 ,x_return_status => x_return_status
3399 ,x_msg_count => x_msg_count
3400 ,x_msg_data => x_msg_data
3401 ,p_assv_rec => lp_asset_tbl(i)
3402 ,x_assv_rec => lx_asset_rec);
3403 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3404 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3405 ELSIF x_return_status = G_RET_STS_ERROR THEN
3406 RAISE OKL_API.G_EXCEPTION_ERROR;
3407 END IF;
3408
3409
3410
3411 END IF;
3412 END LOOP;
3413 ELSE
3414
3415 lp_lease_qte_rec.STRUCTURED_PRICING := 'Y';
3416 lp_lease_qte_rec.LINE_LEVEL_PRICING := 'N';
3417
3418 update_cash_flows(p_quote_id => p_quote_id
3419 ,p_source_object_code => 'LEASE_QUOTE'
3420 ,p_source_object_id => lp_lease_qte_rec.id
3421 ,x_return_status => x_return_status
3422 ,x_msg_count => x_msg_count
3423 ,x_msg_data => x_msg_data);
3424 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3425 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3426 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3427 RAISE OKL_API.G_EXCEPTION_ERROR;
3428 END IF;
3429
3430 FOR i IN lp_asset_tbl.FIRST..lp_asset_tbl.LAST LOOP
3431 IF(lp_asset_tbl.EXISTS(i)) THEN
3432 --Reset Pricing Values
3433 lp_asset_tbl(i).structured_pricing :=NULL;
3434 lp_asset_tbl(i).rate_template_id := NULL;
3435 lp_asset_tbl(i).TARGET_AMOUNT := NULL;
3436 lp_asset_tbl(i).TARGET_FREQUENCY := NULL;
3437
3438 OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows (
3439 p_api_version => G_API_VERSION
3440 ,p_init_msg_list => G_FALSE
3441 ,p_transaction_control => G_FALSE --Check this
3442 ,p_source_object_code => 'QUOTED_ASSET'
3443 ,p_source_object_id => lp_asset_tbl(i).id
3444 ,x_return_status => x_return_status
3445 ,x_msg_count => x_msg_count
3446 ,x_msg_data => x_msg_data);
3447
3448 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3449 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3450 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3451 RAISE OKL_API.G_EXCEPTION_ERROR;
3452 END IF;
3453
3454 okl_ass_pvt.update_row (p_api_version => G_API_VERSION
3455 ,p_init_msg_list => G_FALSE
3456 ,x_return_status => x_return_status
3457 ,x_msg_count => x_msg_count
3458 ,x_msg_data => x_msg_data
3459 ,p_assv_rec => lp_asset_tbl(i)
3460 ,x_assv_rec => lx_asset_rec);
3461 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3462 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3463 ELSIF x_return_status = G_RET_STS_ERROR THEN
3464 RAISE OKL_API.G_EXCEPTION_ERROR;
3465 END IF;
3466
3467 END IF;
3468 END LOOP;
3469
3470 END IF;
3471 --Change the Quote Pricing method to 'Solve for Yield'(Lookup Code 'SY')
3472 --change quote Status to incomplete.
3473
3474 lp_lease_qte_rec.pricing_method := 'SY';
3475 lp_lease_qte_rec.status := 'PR-INCOMPLETE';
3476
3477 lp_lease_qte_rec.TARGET_ARREARS_YN := NULL;
3478 lp_lease_qte_rec.IIR := NULL;
3479 lp_lease_qte_rec.BOOKING_YIELD := NULL;
3480 lp_lease_qte_rec.PIRR := NULL;
3481 lp_lease_qte_rec.SUB_IIR := NULL;
3482 lp_lease_qte_rec.SUB_BOOKING_YIELD := NULL;
3483 lp_lease_qte_rec.SUB_PIRR := NULL;
3484 --Check other parameters need to be Reset
3485
3486 okl_lsq_pvt.update_row(p_api_version => G_API_VERSION
3487 ,p_init_msg_list => G_FALSE
3488 ,x_return_status => x_return_status
3489 ,x_msg_count => x_msg_count
3490 ,x_msg_data => x_msg_data
3491 ,p_lsqv_rec => lp_lease_qte_rec
3492 ,x_lsqv_rec => lx_lease_qte_rec );
3493
3494 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3495 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3496 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3497 RAISE OKL_API.G_EXCEPTION_ERROR;
3498 END IF;
3499
3500 EXCEPTION
3501
3502 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3503
3504 IF p_transaction_control = G_TRUE THEN
3505 ROLLBACK TO l_program_name;
3506 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3507 END IF;
3508
3509 x_return_status := G_RET_STS_ERROR;
3510
3511 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3512
3513 IF p_transaction_control = G_TRUE THEN
3514 ROLLBACK TO l_program_name;
3515 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3516 END IF;
3517
3518 x_return_status := G_RET_STS_UNEXP_ERROR;
3519
3520 WHEN OTHERS THEN
3521
3522 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3523 p_msg_name => G_DB_ERROR,
3524 p_token1 => G_PROG_NAME_TOKEN,
3525 p_token1_value => l_api_name,
3526 p_token2 => G_SQLCODE_TOKEN,
3527 p_token2_value => sqlcode,
3528 p_token3 => G_SQLERRM_TOKEN,
3529 p_token3_value => sqlerrm);
3530
3531 IF p_transaction_control = G_TRUE THEN
3532 ROLLBACK TO l_program_name;
3533 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3534 END IF;
3535
3536 x_return_status := G_RET_STS_UNEXP_ERROR;
3537
3538 END change_pricing;
3539 --Bug 5171476 ssdeshpa end
3540
3541 /*========================================================================
3542 | PUBLIC PROCEDURE calculate_sales_tax
3543 |
3544 | DESCRIPTION
3545 | This procedure makes call to calculate sales tax
3546 |
3547 | CALLED FROM Sales component
3548 |
3549 |
3550 | CALLS PROCEDURES/FUNCTIONS
3551 |
3552 |
3553 | PARAMETERS
3554 | p_quote_id -- Quote Identifier
3555 |
3556 | KNOWN ISSUES
3557 |
3558 | NOTES
3559 |
3560 |
3561 | MODIFICATION HISTORY
3562 | Date Author Description of Changes
3563 | 05-JUL-07 RRAVIKIR Created
3564 |
3565 *=======================================================================*/
3566 PROCEDURE calculate_sales_tax(p_api_version IN NUMBER,
3567 p_init_msg_list IN VARCHAR2,
3568 x_return_status OUT NOCOPY VARCHAR2,
3569 x_msg_count OUT NOCOPY NUMBER,
3570 x_msg_data OUT NOCOPY VARCHAR2,
3571 p_transaction_control IN VARCHAR2,
3572 p_quote_id IN NUMBER) IS
3573
3574 l_program_name CONSTANT VARCHAR2(30) := 'calculate_sales_tax';
3575 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
3576
3577 l_assets_total_tax NUMBER;
3578 l_tax_treatment VARCHAR2(30);
3579 l_tax_stream_type NUMBER;
3580 l_pricing_method VARCHAR2(30);
3581 l_parent_object_code VARCHAR2(30);
3582 l_parent_object_id NUMBER;
3583 i NUMBER;
3584 l_asset_tax_amt NUMBER;
3585 lx_fee_id NUMBER;
3586 l_tax_fee_exists VARCHAR2(1);
3587
3588 l_qte_fee_rec lease_qte_fee_rec_type;
3589 l_line_relation_tbl okl_lease_quote_fee_pvt.line_relation_tbl_type;
3590
3591 l_payment_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
3592 l_payment_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
3593
3594 l_expense_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
3595 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
3596
3597 CURSOR l_get_assets_tax_amount(cp_quote_id IN NUMBER) IS
3598 SELECT SUM(total_tax)
3599 FROM okl_tax_sources
3600 WHERE trx_id = cp_quote_id
3601 AND asset_number IS NOT NULL
3602 AND entity_code = OKL_PROCESS_SALES_TAX_PVT.G_SQ_ENTITY_CODE
3603 AND event_class_code = OKL_PROCESS_SALES_TAX_PVT.G_SQ_EVENT_CLASS_CODE
3604 AND application_id = OKL_PROCESS_SALES_TAX_PVT.G_OKL_APPLICATION_ID
3605 AND trx_level_type = OKL_PROCESS_SALES_TAX_PVT.G_TRX_LEVEL_TYPE;
3606
3607 CURSOR l_get_tax_treatment(cp_quote_id IN NUMBER) IS
3608 SELECT upfront_tax_treatment, upfront_tax_stream_type,
3609 pricing_method, parent_object_code, parent_object_id
3610 FROM okl_lease_quotes_b
3611 WHERE id = cp_quote_id;
3612
3613 CURSOR l_get_assets(cp_quote_id IN NUMBER) IS
3614 SELECT id, asset_number
3615 FROM okl_assets_b
3616 WHERE parent_object_id = cp_quote_id
3617 AND parent_object_code = 'LEASEQUOTE';
3618
3619 CURSOR l_get_asset_tax_amount(cp_quote_id IN NUMBER, cp_asset_number IN VARCHAR2) IS
3620 SELECT total_tax
3621 FROM okl_tax_sources
3622 WHERE trx_id = cp_quote_id
3623 AND asset_number = cp_asset_number
3624 AND entity_code = OKL_PROCESS_SALES_TAX_PVT.G_SQ_ENTITY_CODE
3625 AND event_class_code = OKL_PROCESS_SALES_TAX_PVT.G_SQ_EVENT_CLASS_CODE
3626 AND application_id = OKL_PROCESS_SALES_TAX_PVT.G_OKL_APPLICATION_ID
3627 AND trx_level_type = OKL_PROCESS_SALES_TAX_PVT.G_TRX_LEVEL_TYPE;
3628
3629 CURSOR l_check_tax_fee_exists(cp_quote_id IN NUMBER) IS
3630 SELECT '1'
3631 FROM okl_fees_b
3632 WHERE parent_object_id = cp_quote_id
3633 AND parent_object_code = 'LEASEQUOTE'
3634 AND fee_purpose_code = 'SALESTAX';
3635
3636 CURSOR l_fee_details(cp_quote_id IN NUMBER) IS
3637 SELECT id, object_version_number
3638 , effective_from, supplier_id --sechawla 5-aug-2010 9962315 : added
3639 FROM okl_fees_b
3640 WHERE parent_object_id = cp_quote_id
3641 AND parent_object_code = 'LEASEQUOTE'
3642 AND fee_purpose_code = 'SALESTAX';
3643
3644 CURSOR l_fee_assets_details(cp_quote_id IN NUMBER) IS
3645 SELECT lre.id, lre.object_version_number, asset.asset_number,
3646 lre.source_line_id, lre.related_line_id
3647 FROM okl_line_relationships_b lre, okl_fees_b fee, okl_assets_b asset
3648 WHERE fee.parent_object_id = cp_quote_id
3649 AND fee.parent_object_code = 'LEASEQUOTE'
3650 AND fee.fee_purpose_code = 'SALESTAX'
3651 AND lre.related_line_id = fee.id
3652 AND lre.related_line_type = fee.fee_type
3653 AND lre.source_line_type = 'ASSET'
3654 AND lre.source_line_id = asset.id
3655 AND asset.parent_object_id = fee.parent_object_id
3656 AND asset.parent_object_code = fee.parent_object_code;
3657
3658 BEGIN
3659 IF p_transaction_control = G_TRUE THEN
3660 SAVEPOINT l_program_name;
3661 END IF;
3662
3663 IF p_init_msg_list = G_TRUE THEN
3664 FND_MSG_PUB.initialize;
3665 END IF;
3666
3667 IF p_quote_id IS NULL THEN
3668 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3669 p_msg_name => 'OKL_INVALID_SALES_QUOTE');
3670 RAISE OKL_API.G_EXCEPTION_ERROR;
3671 END IF;
3672
3673 -- Calculate Sales tax
3674 OKL_PROCESS_SALES_TAX_PVT.calculate_sales_tax(p_api_version => p_api_version,
3675 p_init_msg_list => p_init_msg_list,
3676 x_return_status => x_return_status,
3677 x_msg_count => x_msg_count,
3678 x_msg_data => x_msg_data,
3679 p_source_trx_id => p_quote_id,
3680 p_source_trx_name => 'Sales Quote',
3681 p_source_table => 'OKL_LEASE_QUOTES_B');
3682 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3683 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3684 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3685 RAISE OKL_API.G_EXCEPTION_ERROR;
3686 END IF;
3687
3688 -- Create the Upfront tax fee only if the tax amount returned is > 0
3689 -- based on the setup defined on Lease Quote/ Lease Application
3690 OPEN l_get_assets_tax_amount(p_quote_id);
3691 FETCH l_get_assets_tax_amount INTO l_assets_total_tax;
3692 CLOSE l_get_assets_tax_amount;
3693
3694 IF (l_assets_total_tax > 0) THEN
3695 -- Get the Upfront tax treatment of Lease quote/ Lease Application
3696 OPEN l_get_tax_treatment(p_quote_id);
3697 FETCH l_get_tax_treatment INTO l_tax_treatment, l_tax_stream_type,
3698 l_pricing_method, l_parent_object_code,
3699 l_parent_object_id;
3700 CLOSE l_get_tax_treatment;
3701
3702 IF (l_tax_treatment IN ('CAPITALIZE', 'FINANCE')) THEN
3703 -- Create the fee line
3704
3705 OPEN l_check_tax_fee_exists(p_quote_id);
3706 FETCH l_check_tax_fee_exists INTO l_tax_fee_exists;
3707 CLOSE l_check_tax_fee_exists;
3708
3709 IF (l_tax_fee_exists IS NULL) THEN -- Tax fee doesn't exist, so create it
3710
3711 IF (l_tax_treatment = 'CAPITALIZE') THEN
3712 l_qte_fee_rec.fee_type := 'CAPITALIZED';
3713 ELSIF (l_tax_treatment = 'FINANCE') THEN
3714 l_qte_fee_rec.fee_type := 'FINANCED';
3715 END IF;
3716
3717 l_qte_fee_rec.parent_object_id := p_quote_id;
3718 l_qte_fee_rec.parent_object_code := 'LEASEQUOTE';
3719 l_qte_fee_rec.stream_type_id := l_tax_stream_type;
3720 l_qte_fee_rec.fee_purpose_code := 'SALESTAX';
3721 l_qte_fee_rec.fee_amount := l_assets_total_tax;
3722
3723 -- Build line relationships table to associate the fee with the assets
3724 IF (l_assets_total_tax > 0) THEN
3725 -- Associate the assets only if atleast one asset has tax amount > 0
3726 i := 1;
3727 FOR l_get_assets_rec IN l_get_assets(p_quote_id) LOOP
3728
3729 OPEN l_get_asset_tax_amount(p_quote_id, l_get_assets_rec.asset_number);
3730 FETCH l_get_asset_tax_amount INTO l_asset_tax_amt;
3731 CLOSE l_get_asset_tax_amount;
3732
3733 l_line_relation_tbl(i).source_line_type := 'ASSET';
3734 l_line_relation_tbl(i).source_line_id := l_get_assets_rec.id;
3735 l_line_relation_tbl(i).related_line_type := l_qte_fee_rec.fee_type;
3736 l_line_relation_tbl(i).amount := l_asset_tax_amt;
3737
3738 i := i+1;
3739 END LOOP;
3740 END IF;
3741
3742 OKL_LEASE_QUOTE_FEE_PVT.create_fee ( p_api_version => p_api_version
3743 ,p_init_msg_list => p_init_msg_list
3744 ,p_transaction_control => p_transaction_control
3745 ,p_fee_rec => l_qte_fee_rec
3746 ,p_assoc_asset_tbl => l_line_relation_tbl
3747 ,p_payment_header_rec => l_payment_header_rec
3748 ,p_payment_level_tbl => l_payment_level_tbl
3749 ,p_expense_header_rec => l_expense_header_rec
3750 ,p_expense_level_tbl => l_expense_level_tbl
3751 ,x_fee_id => lx_fee_id
3752 ,x_return_status => x_return_status
3753 ,x_msg_count => x_msg_count
3754 ,x_msg_data => x_msg_data );
3755 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3756 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3757 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3758 RAISE OKL_API.G_EXCEPTION_ERROR;
3759 END IF;
3760
3761 ELSE -- Tax fee exists .. update it
3762
3763 OPEN l_fee_details(p_quote_id);
3764 FETCH l_fee_details INTO l_qte_fee_rec.id, l_qte_fee_rec.object_version_number,
3765 l_qte_fee_rec.effective_from, l_qte_fee_rec.supplier_id; --sechawla 5-aug-2010 9962315 : added
3766 CLOSE l_fee_details;
3767
3768 IF (l_tax_treatment = 'CAPITALIZE') THEN
3769 l_qte_fee_rec.fee_type := 'CAPITALIZED';
3770 ELSIF (l_tax_treatment = 'FINANCE') THEN
3771 l_qte_fee_rec.fee_type := 'FINANCED';
3772 END IF;
3773
3774 l_qte_fee_rec.parent_object_id := p_quote_id;
3775 l_qte_fee_rec.parent_object_code := 'LEASEQUOTE';
3776 l_qte_fee_rec.stream_type_id := l_tax_stream_type;
3777 l_qte_fee_rec.fee_purpose_code := 'SALESTAX';
3778 l_qte_fee_rec.fee_amount := l_assets_total_tax;
3779
3780 i := 1;
3781 FOR l_fee_assets_details_rec IN l_fee_assets_details(p_quote_id) LOOP
3782
3783 OPEN l_get_asset_tax_amount(p_quote_id, l_fee_assets_details_rec.asset_number);
3784 FETCH l_get_asset_tax_amount INTO l_asset_tax_amt;
3785 CLOSE l_get_asset_tax_amount;
3786
3787 l_line_relation_tbl(i).id := l_fee_assets_details_rec.id;
3788 l_line_relation_tbl(i).object_version_number := l_fee_assets_details_rec.object_version_number;
3789 l_line_relation_tbl(i).source_line_type := 'ASSET';
3790 l_line_relation_tbl(i).source_line_id := l_fee_assets_details_rec.source_line_id;
3791 l_line_relation_tbl(i).related_line_id := l_fee_assets_details_rec.related_line_id;
3792 l_line_relation_tbl(i).related_line_type := l_qte_fee_rec.fee_type;
3793 l_line_relation_tbl(i).amount := l_asset_tax_amt;
3794 l_line_relation_tbl(i).record_mode := 'UPDATE';
3795
3796 i := i+1;
3797 END LOOP;
3798
3799 IF (l_line_relation_tbl.COUNT = 0 AND l_assets_total_tax > 0) THEN
3800 -- Associate the assets only if atleast one asset has tax amount > 0
3801 i := 1;
3802 FOR l_get_assets_rec IN l_get_assets(p_quote_id) LOOP
3803
3804 OPEN l_get_asset_tax_amount(p_quote_id, l_get_assets_rec.asset_number);
3805 FETCH l_get_asset_tax_amount INTO l_asset_tax_amt;
3806 CLOSE l_get_asset_tax_amount;
3807
3808 l_line_relation_tbl(i).source_line_type := 'ASSET';
3809 l_line_relation_tbl(i).source_line_id := l_get_assets_rec.id;
3810 l_line_relation_tbl(i).related_line_type := l_tax_treatment;
3811 l_line_relation_tbl(i).amount := l_asset_tax_amt;
3812
3813 i := i+1;
3814 END LOOP;
3815 END IF;
3816
3817 OKL_LEASE_QUOTE_FEE_PVT.update_fee ( p_api_version => p_api_version
3818 ,p_init_msg_list => p_init_msg_list
3819 ,p_transaction_control => p_transaction_control
3820 ,p_fee_rec => l_qte_fee_rec
3821 ,p_assoc_asset_tbl => l_line_relation_tbl
3822 ,p_payment_header_rec => l_payment_header_rec
3823 ,p_payment_level_tbl => l_payment_level_tbl
3824 ,p_expense_header_rec => l_expense_header_rec
3825 ,p_expense_level_tbl => l_expense_level_tbl
3826 ,x_return_status => x_return_status
3827 ,x_msg_count => x_msg_count
3828 ,x_msg_data => x_msg_data );
3829 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3831 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3832 RAISE OKL_API.G_EXCEPTION_ERROR;
3833 END IF;
3834
3835 END IF;
3836 END IF;
3837
3838 IF (l_tax_treatment IN ('CAPITALIZE', 'FINANCE')) THEN
3839
3840 IF (l_assets_total_tax > 0) THEN
3841
3842 -- Switch the quote status to 'INCOMPLETE'
3843 change_quote_status(p_quote_id => p_quote_id,
3844 p_qte_status => 'PR-INCOMPLETE',
3845 x_return_status => x_return_status);
3846
3847 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3848 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3849 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3850 RAISE OKL_API.G_EXCEPTION_ERROR;
3851 END IF;
3852 END IF;
3853
3854 IF (l_parent_object_code = 'LEASEAPP') THEN
3855 okl_lease_app_pvt.set_lease_app_status(p_api_version => p_api_version,
3856 p_init_msg_list => p_init_msg_list,
3857 p_lap_id => l_parent_object_id,
3858 p_lap_status => 'INCOMPLETE',
3859 x_return_status => x_return_status,
3860 x_msg_count => x_msg_count,
3861 x_msg_data => x_msg_data);
3862 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3864 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
3865 RAISE OKL_API.G_EXCEPTION_ERROR;
3866 END IF;
3867 END IF;
3868
3869 END IF;
3870
3871 END IF;
3872
3873 EXCEPTION
3874
3875 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3876 IF p_transaction_control = G_TRUE THEN
3877 ROLLBACK TO l_program_name;
3878 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3879 END IF;
3880 x_return_status := G_RET_STS_ERROR;
3881
3882 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3883 IF p_transaction_control = G_TRUE THEN
3884 ROLLBACK TO l_program_name;
3885 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3886 END IF;
3887 x_return_status := G_RET_STS_UNEXP_ERROR;
3888
3889 WHEN OTHERS THEN
3890 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
3891 p_msg_name => G_DB_ERROR,
3892 p_token1 => G_PROG_NAME_TOKEN,
3893 p_token1_value => l_api_name,
3894 p_token2 => G_SQLCODE_TOKEN,
3895 p_token2_value => sqlcode,
3896 p_token3 => G_SQLERRM_TOKEN,
3897 p_token3_value => sqlerrm);
3898
3899 IF p_transaction_control = G_TRUE THEN
3900 ROLLBACK TO l_program_name;
3901 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3902 END IF;
3903 x_return_status := G_RET_STS_UNEXP_ERROR;
3904
3905 END calculate_sales_tax;
3906
3907
3908 END OKL_LEASE_QUOTE_PVT;