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