[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_PRICING_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_PRICING_PVT AS
2 /* $Header: OKLRQUPB.pls 120.23 2010/06/01 11:27:14 smadhava noship $ */
3 -------------------------------------------------------------------------------
4 -- FUNCTION populate_quote_rec
5 -------------------------------------------------------------------------------
6 -- Start of comments
7 --
8 -- Function Name : populate_quote_rec
9 -- Description : Populate the lease quote Records
10 --
11 -- Business Rules :
12 --
13 --
14 -- Parameters :
15 -- Version : 1.0
16 -- History : 15-SEP-2005 SKGAUTAM created
17 -- End of comments
18 FUNCTION populate_quote_rec(p_quote_id IN NUMBER
19 ,x_return_status OUT NOCOPY VARCHAR2)
20 RETURN lease_qte_rec_type IS
21 -- Variables Declarations
22 l_api_version CONSTANT NUMBER DEFAULT 1.0;
23 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'POPULATE_QUOTE_REC';
24 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
25 l_debug_enabled VARCHAR2(10);
26
27 -- Record/Table Type Declarations
28 l_lsqv_rec lease_qte_rec_type;
29
30 BEGIN
31 SELECT id
32 ,object_version_number
33 ,attribute_category
34 ,attribute1
35 ,attribute2
36 ,attribute3
37 ,attribute4
38 ,attribute5
39 ,attribute6
40 ,attribute7
41 ,attribute8
42 ,attribute9
43 ,attribute10
44 ,attribute11
45 ,attribute12
46 ,attribute13
47 ,attribute14
48 ,attribute15
49 ,reference_number
50 ,status
51 ,parent_object_code
52 ,parent_object_id
53 ,valid_from
54 ,valid_to
55 ,customer_bookclass
56 ,customer_taxowner
57 ,expected_start_date
58 ,expected_funding_date
59 ,expected_delivery_date
60 ,pricing_method
61 ,term
62 ,product_id
63 ,end_of_term_option_id
64 ,rate_card_id
65 ,rate_template_id
66 ,target_rate_type
67 ,target_rate
68 ,target_amount
69 ,target_frequency
70 ,target_arrears_yn
71 ,target_periods
72 ,iir
73 ,booking_yield
74 ,pirr
75 ,airr
76 ,sub_iir
77 ,sub_booking_yield
78 ,sub_pirr
79 ,sub_airr
80 ,usage_category
81 ,usage_industry_class
82 ,usage_industry_code
83 ,usage_amount
84 ,usage_location_id
85 ,property_tax_applicable
86 ,property_tax_billing_type
87 ,upfront_tax_treatment
88 ,upfront_tax_stream_type
89 ,transfer_of_title
90 ,age_of_equipment
91 ,purchase_of_lease
92 ,sale_and_lease_back
93 ,interest_disclosed
94 ,structured_pricing
95 ,line_level_pricing
96 ,short_description
97 ,description
98 ,comments
99 ,PRIMARY_QUOTE
100 INTO
101 l_lsqv_rec.id
102 ,l_lsqv_rec.object_version_number
103 ,l_lsqv_rec.attribute_category
104 ,l_lsqv_rec.attribute1
105 ,l_lsqv_rec.attribute2
106 ,l_lsqv_rec.attribute3
107 ,l_lsqv_rec.attribute4
108 ,l_lsqv_rec.attribute5
109 ,l_lsqv_rec.attribute6
110 ,l_lsqv_rec.attribute7
111 ,l_lsqv_rec.attribute8
112 ,l_lsqv_rec.attribute9
113 ,l_lsqv_rec.attribute10
114 ,l_lsqv_rec.attribute11
115 ,l_lsqv_rec.attribute12
116 ,l_lsqv_rec.attribute13
117 ,l_lsqv_rec.attribute14
118 ,l_lsqv_rec.attribute15
119 ,l_lsqv_rec.reference_number
120 ,l_lsqv_rec.status
121 ,l_lsqv_rec.parent_object_code
122 ,l_lsqv_rec.parent_object_id
123 ,l_lsqv_rec.valid_from
124 ,l_lsqv_rec.valid_to
125 ,l_lsqv_rec.customer_bookclass
126 ,l_lsqv_rec.customer_taxowner
127 ,l_lsqv_rec.expected_start_date
128 ,l_lsqv_rec.expected_funding_date
129 ,l_lsqv_rec.expected_delivery_date
130 ,l_lsqv_rec.pricing_method
131 ,l_lsqv_rec.term
132 ,l_lsqv_rec.product_id
133 ,l_lsqv_rec.end_of_term_option_id
134 ,l_lsqv_rec.rate_card_id
135 ,l_lsqv_rec.rate_template_id
136 ,l_lsqv_rec.target_rate_type
137 ,l_lsqv_rec.target_rate
138 ,l_lsqv_rec.target_amount
139 ,l_lsqv_rec.target_frequency
140 ,l_lsqv_rec.target_arrears_yn
141 ,l_lsqv_rec.target_periods
142 ,l_lsqv_rec.iir
143 ,l_lsqv_rec.booking_yield
144 ,l_lsqv_rec.pirr
145 ,l_lsqv_rec.airr
146 ,l_lsqv_rec.sub_iir
147 ,l_lsqv_rec.sub_booking_yield
148 ,l_lsqv_rec.sub_pirr
149 ,l_lsqv_rec.sub_airr
150 ,l_lsqv_rec.usage_category
151 ,l_lsqv_rec.usage_industry_class
152 ,l_lsqv_rec.usage_industry_code
153 ,l_lsqv_rec.usage_amount
154 ,l_lsqv_rec.usage_location_id
155 ,l_lsqv_rec.property_tax_applicable
156 ,l_lsqv_rec.property_tax_billing_type
157 ,l_lsqv_rec.upfront_tax_treatment
158 ,l_lsqv_rec.upfront_tax_stream_type
159 ,l_lsqv_rec.transfer_of_title
160 ,l_lsqv_rec.age_of_equipment
161 ,l_lsqv_rec.purchase_of_lease
162 ,l_lsqv_rec.sale_and_lease_back
163 ,l_lsqv_rec.interest_disclosed
164 ,l_lsqv_rec.structured_pricing
165 ,l_lsqv_rec.line_level_pricing
166 ,l_lsqv_rec.short_description
167 ,l_lsqv_rec.description
168 ,l_lsqv_rec.comments
169 ,l_lsqv_rec.PRIMARY_QUOTE
170 FROM OKL_LEASE_QUOTES_V
171 WHERE id = p_quote_id;
172
173 x_return_status := G_RET_STS_SUCCESS;
174 RETURN l_lsqv_rec;
175
176 EXCEPTION
177 -- other appropriate handlers
178 WHEN OTHERS THEN
179 -- store SQL error message on message stack
180 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
181 p_msg_name => G_UNEXPECTED_ERROR,
182 p_token1 => G_SQLCODE_TOKEN,
183 p_token1_value => sqlcode,
184 p_token2 => G_SQLERRM_TOKEN,
185 p_token2_value => sqlerrm);
186
187 -- notify UNEXPECTED error
188 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
189
190 END populate_quote_rec;
191
192 -------------------------------------------------------------------------------
193 -- FUNCTION populate_fee_rec
194 -------------------------------------------------------------------------------
195 -- Start of comments
196 --
197 -- Function Name : populate_fee_rec
198 -- Description : Populate the lease fee Records
199 --
200 -- Business Rules :
201 --
202 --
203 -- Parameters :
204 -- Version : 1.0
205 -- History : 15-SEP-2005 SKGAUTAM created
206 FUNCTION populate_fee_rec(p_fee_id IN NUMBER
207 ,x_return_status OUT NOCOPY VARCHAR2)
208 RETURN fee_rec_type IS
209 -- Variables Declarations
210 l_api_version CONSTANT NUMBER DEFAULT 1.0;
211 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'POPULATE_FEE_REC';
212 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
213 l_debug_enabled VARCHAR2(10);
214
215 -- Record/Table Type Declarations
216 l_feev_rec fee_rec_type;
217
218 BEGIN
219 SELECT id
220 ,object_version_number
221 ,attribute_category
222 ,attribute1
223 ,attribute2
224 ,attribute3
225 ,attribute4
226 ,attribute5
227 ,attribute6
228 ,attribute7
229 ,attribute8
230 ,attribute9
231 ,attribute10
232 ,attribute11
233 ,attribute12
234 ,attribute13
235 ,attribute14
236 ,attribute15
237 ,parent_object_code
238 ,parent_object_id
239 ,stream_type_id
240 ,fee_type
241 ,rate_card_id
242 ,rate_template_id
243 ,structured_pricing
244 ,target_arrears
245 ,effective_from
246 ,effective_to
247 ,supplier_id
248 ,rollover_quote_id
249 ,initial_direct_cost
250 ,fee_amount
251 ,target_amount
252 ,target_frequency
253 ,payment_type_id
254 ,fee_purpose_code -- Bug#9708535
255 INTO
256 l_feev_rec.id
257 ,l_feev_rec.object_version_number
258 ,l_feev_rec.attribute_category
259 ,l_feev_rec.attribute1
260 ,l_feev_rec.attribute2
261 ,l_feev_rec.attribute3
262 ,l_feev_rec.attribute4
263 ,l_feev_rec.attribute5
264 ,l_feev_rec.attribute6
265 ,l_feev_rec.attribute7
266 ,l_feev_rec.attribute8
267 ,l_feev_rec.attribute9
268 ,l_feev_rec.attribute10
269 ,l_feev_rec.attribute11
270 ,l_feev_rec.attribute12
271 ,l_feev_rec.attribute13
272 ,l_feev_rec.attribute14
273 ,l_feev_rec.attribute15
274 ,l_feev_rec.parent_object_code
275 ,l_feev_rec.parent_object_id
276 ,l_feev_rec.stream_type_id
277 ,l_feev_rec.fee_type
278 ,l_feev_rec.rate_card_id
279 ,l_feev_rec.rate_template_id
280 ,l_feev_rec.structured_pricing
281 ,l_feev_rec.target_arrears
282 ,l_feev_rec.effective_from
283 ,l_feev_rec.effective_to
284 ,l_feev_rec.supplier_id
285 ,l_feev_rec.rollover_quote_id
286 ,l_feev_rec.initial_direct_cost
287 ,l_feev_rec.fee_amount
288 ,l_feev_rec.target_amount
289 ,l_feev_rec.target_frequency
290 ,l_feev_rec.payment_type_id
291 ,l_feev_rec.fee_purpose_code -- Bug#9708535
292 FROM okL_fees_V
293 WHERE id = p_fee_id;
294
295 x_return_status := G_RET_STS_SUCCESS;
296 RETURN l_feev_rec;
297
298 EXCEPTION
299 -- other appropriate handlers
300 WHEN OTHERS THEN
301 -- store SQL error message on message stack
302 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
303 p_msg_name => G_UNEXPECTED_ERROR,
304 p_token1 => G_SQLCODE_TOKEN,
305 p_token1_value => sqlcode,
306 p_token2 => G_SQLERRM_TOKEN,
307 p_token2_value => sqlerrm);
308
309 -- notify UNEXPECTED error
310 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
311
312 END populate_fee_rec;
313
314 -------------------------------------------------------------------------------
315 -- FUNCTION populate_asset_rec
316 -------------------------------------------------------------------------------
317 -- Start of comments
318 --
319 -- Function Name : populate_asset_rec
320 -- Description : Populate the lease Asset Records
321 --
322 -- Business Rules :
323 --
324 --
325 -- Parameters :
326 -- Version : 1.0
327 -- History : 15-SEP-2005 SKGAUTAM created
328 FUNCTION populate_asset_rec (p_asset_id IN NUMBER
329 ,x_return_status OUT NOCOPY VARCHAR2)
330 RETURN asset_rec_type IS
331 -- Variables Declarations
332 l_api_version CONSTANT NUMBER DEFAULT 1.0;
333 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'POPULATE_ASSET_REC';
334 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
335 l_debug_enabled VARCHAR2(10);
336
337 -- Record/Table Type Declarations
338 l_assv_rec asset_rec_type;
339
340 BEGIN
341 SELECT id
342 ,object_version_number
343 ,attribute_category
344 ,attribute1
345 ,attribute2
346 ,attribute3
347 ,attribute4
348 ,attribute5
349 ,attribute6
350 ,attribute7
351 ,attribute8
352 ,attribute9
353 ,attribute10
354 ,attribute11
355 ,attribute12
356 ,attribute13
357 ,attribute14
358 ,attribute15
359 ,parent_object_code
360 ,parent_object_id
361 ,asset_number
362 ,install_site_id
363 ,rate_card_id
364 ,rate_template_id
365 ,oec
366 ,end_of_term_value_default
367 ,end_of_term_value
368 ,oec_percentage
369 ,target_amount
370 ,target_frequency
371 ,short_description
372 ,description
373 ,comments
374 INTO
375 l_assv_rec.id
376 ,l_assv_rec.object_version_number
377 ,l_assv_rec.attribute_category
378 ,l_assv_rec.attribute1
379 ,l_assv_rec.attribute2
380 ,l_assv_rec.attribute3
381 ,l_assv_rec.attribute4
382 ,l_assv_rec.attribute5
383 ,l_assv_rec.attribute6
384 ,l_assv_rec.attribute7
385 ,l_assv_rec.attribute8
386 ,l_assv_rec.attribute9
387 ,l_assv_rec.attribute10
388 ,l_assv_rec.attribute11
389 ,l_assv_rec.attribute12
390 ,l_assv_rec.attribute13
391 ,l_assv_rec.attribute14
392 ,l_assv_rec.attribute15
393 ,l_assv_rec.parent_object_code
394 ,l_assv_rec.parent_object_id
395 ,l_assv_rec.asset_number
396 ,l_assv_rec.install_site_id
397 ,l_assv_rec.rate_card_id
398 ,l_assv_rec.rate_template_id
399 ,l_assv_rec.oec
400 ,l_assv_rec.end_of_term_value_default
401 ,l_assv_rec.end_of_term_value
402 ,l_assv_rec.oec_percentage
403 ,l_assv_rec.target_amount
404 ,l_assv_rec.target_frequency
405 ,l_assv_rec.short_description
406 ,l_assv_rec.description
407 ,l_assv_rec.comments
408 FROM OKL_ASSETS_V
409 WHERE id = p_asset_id;
410
411 x_return_status := G_RET_STS_SUCCESS;
412 RETURN l_assv_rec;
413
414 EXCEPTION
415 -- other appropriate handlers
416 WHEN OTHERS THEN
417 -- store SQL error message on message stack
418 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
419 p_msg_name => G_UNEXPECTED_ERROR,
420 p_token1 => G_SQLCODE_TOKEN,
421 p_token1_value => sqlcode,
422 p_token2 => G_SQLERRM_TOKEN,
423 p_token2_value => sqlerrm);
424
425 -- notify UNEXPECTED error
426 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
427
428 END populate_asset_rec;
429 --Bug # 4688662 ssdeshpa start
430 --Moved this method to OKL_SALES_QUOTE_QA_PVT
431 /*FUNCTION are_all_lines_overriden(p_quote_id IN NUMBER
432 ,p_pricing_method IN VARCHAR2
433 ,p_line_level_pricing IN VARCHAR2
434 ,x_return_status OUT NOCOPY VARCHAR2)
435 RETURN VARCHAR2 IS
436 -- Variables Declarations
437 l_api_version CONSTANT NUMBER DEFAULT 1.0;
438 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'all_lns_ovr';
439 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
440 l_all_lines_overriden VARCHAR2(3) := 'N';
441 l_ovr_cnt NUMBER;
442 l_ast_cnt NUMBER;
443 CURSOR llo_flag_csr IS
444 SELECT count(*) overriden_assets_count
445 FROM OKL_LEASE_QUOTES_B QTE,
446 OKL_ASSETS_B AST
447 WHERE AST.PARENT_OBJECT_ID = QTE.ID
448 AND ( AST.RATE_TEMPLATE_ID IS NOT NULL
449 OR AST.STRUCTURED_PRICING = 'Y' )
450 AND QTE.ID = p_quote_id
451 AND p_line_level_pricing = 'Y';
452
453 CURSOR rc_llo_flag_csr IS
454 SELECT count(*) overriden_assets_count
455 FROM OKL_LEASE_QUOTES_B QTE,
456 OKL_ASSETS_B AST
457 WHERE AST.PARENT_OBJECT_ID = QTE.ID
458 AND ( AST.RATE_CARD_ID IS NOT NULL
459 OR AST.LEASE_RATE_FACTOR IS NOT NULL )
460 AND QTE.ID = p_quote_id
461 AND p_line_level_pricing = 'Y';
462
463 CURSOR sy_llo_flag_csr IS
464 SELECT count(*) overriden_assets_count
465 FROM OKL_LEASE_QUOTES_B QTE,
466 OKL_ASSETS_B AST
467 WHERE AST.PARENT_OBJECT_ID = QTE.ID
468 AND ( AST.STRUCTURED_PRICING = 'Y')
469 AND QTE.ID = p_quote_id
470 AND p_line_level_pricing = 'Y';
471
472 CURSOR ast_cnt_csr IS
473 SELECT count(*) assets_count
474 FROM OKL_LEASE_QUOTES_B QTE,
475 OKL_ASSETS_B AST
476 WHERE AST.PARENT_OBJECT_ID = QTE.ID
477 AND QTE.ID = p_quote_id;
478
479 BEGIN
480 IF p_pricing_method = 'SY' THEN
481 OPEN sy_llo_flag_csr;
482 FETCH sy_llo_flag_csr INTO l_ovr_cnt;
483 CLOSE sy_llo_flag_csr;
484 ELSIF p_pricing_method = 'RC' THEN
485 OPEN rc_llo_flag_csr;
486 FETCH rc_llo_flag_csr INTO l_ovr_cnt;
487 CLOSE rc_llo_flag_csr;
488 ELSIF p_pricing_method <> 'TR' THEN
489 OPEN llo_flag_csr;
490 FETCH llo_flag_csr INTO l_ovr_cnt;
491 CLOSE llo_flag_csr;
492 END IF;
493 OPEN ast_cnt_csr;
494 FETCH ast_cnt_csr INTO l_ast_cnt;
495 CLOSE ast_cnt_csr;
496 IF l_ast_cnt = 0 THEN
497 l_all_lines_overriden := 'N';
498 ELSIF l_Ast_cnt = l_ovr_cnt THEN
499 l_all_lines_overriden := 'Y';
500 ELSE
501 l_all_lines_overriden := 'N';
502 END IF;
503 x_return_status := G_RET_STS_SUCCESS;
504 RETURN l_all_lines_overriden;
505
506 EXCEPTION
507 -- other appropriate handlers
508 WHEN OTHERS THEN
509 -- store SQL error message on message stack
510 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
511 p_msg_name => G_UNEXPECTED_ERROR,
512 p_token1 => G_SQLCODE_TOKEN,
513 p_token1_value => sqlcode,
514 p_token2 => G_SQLERRM_TOKEN,
515 p_token2_value => sqlerrm);
516
517 -- notify UNEXPECTED error
518 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
519
520 END are_all_lines_overriden;*/
521 --Bug # 4688662 ssdeshpa start
522
523 FUNCTION are_qte_pricing_opts_entered(p_lease_qte_rec IN lease_qte_rec_type
524 ,p_payment_count IN NUMBER
525 ,x_return_status OUT NOCOPY VARCHAR2)
526 RETURN VARCHAR2 IS
527 -- Variables Declarations
528 l_api_version CONSTANT NUMBER DEFAULT 1.0;
529 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'qte_pr_entr';
530 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
531 l_falg VARCHAR2(3) := 'N';
532 BEGIN
533 IF p_lease_qte_rec.pricing_method = 'SY' THEN
534 IF p_payment_count <> 0 THEN
535 return 'Y';
536 END IF;
537 ELSIF p_lease_qte_rec.pricing_method = 'RC' THEN
538 IF p_lease_qte_rec.structured_pricing = 'N' AND p_lease_qte_rec.rate_card_id IS NOT NULL
539 OR p_lease_qte_rec.structured_pricing = 'Y' AND p_lease_qte_rec.lease_rate_factor IS NOT NULL
540 THEN
541 return 'Y';
542 END IF;
543 ELSIF p_lease_qte_rec.pricing_method = 'SM' THEN
544 IF (p_lease_qte_rec.structured_pricing = 'N' AND ( p_lease_qte_rec.rate_template_id IS NOT NULL OR p_payment_count <> 0 ) )
545 OR ( p_lease_qte_rec.structured_pricing = 'Y' AND p_payment_count <> 0 )
546 THEN
547 return 'Y';
548 END IF;
549 ELSIF p_lease_qte_rec.pricing_method <> 'TR' THEN
550 IF p_lease_qte_rec.structured_pricing = 'N' AND p_lease_qte_rec.rate_template_id IS NOT NULL
551 OR p_lease_qte_rec.structured_pricing = 'Y' AND p_payment_count <> 0
552 THEN
553 return 'Y';
554 END IF;
555 END IF;
556
557
558 x_return_status := G_RET_STS_SUCCESS;
559 RETURN 'N';
560
561 EXCEPTION
562 -- other appropriate handlers
563 WHEN OTHERS THEN
564 -- store SQL error message on message stack
565 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
566 p_msg_name => G_UNEXPECTED_ERROR,
567 p_token1 => G_SQLCODE_TOKEN,
568 p_token1_value => sqlcode,
569 p_token2 => G_SQLERRM_TOKEN,
570 p_token2_value => sqlerrm);
571
572 -- notify UNEXPECTED error
573 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
574
575 END are_qte_pricing_opts_entered;
576 -------------------------------------------------------------------------------
577 -- PROCEDURE validate
578 -------------------------------------------------------------------------------
579 -- Start of comments
580 --
581 -- Procedure Name : validate
582 -- Description : Validate the lease quote and call the validation set wrapper API
583 --
584 -- Business Rules :
585 --
586 --
587 -- Parameters :
588 -- Version : 1.0
589 -- History : 15-SEP-2005 SKGAUTAM created
590 -- End of comments
591 PROCEDURE validate (p_api_version IN NUMBER
592 ,p_init_msg_list IN VARCHAR2
593 ,p_quote_id IN NUMBER
594 ,x_qa_result OUT NOCOPY VARCHAR2
595 ,x_return_status OUT NOCOPY VARCHAR2
596 ,x_msg_count OUT NOCOPY NUMBER
597 ,x_msg_data OUT NOCOPY VARCHAR2) IS
598 -- Variables Declarations
599 l_api_version CONSTANT NUMBER DEFAULT 1.0;
600 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'VALIDATE';
601 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
602 l_debug_enabled VARCHAR2(10);
603
604 l_object_type VARCHAR2(15):= 'LEASEQUOTE';
605
606 -- Record/Table Type Declarations
607 l_lease_qte_rec lease_qte_rec_type;
608 lx_lease_qte_rec lease_qte_rec_type;
609
610 BEGIN
611 x_return_status := OKL_API.G_RET_STS_SUCCESS;
612 -- check for logging on PROCEDURE level
613 l_debug_enabled := okl_debug_pub.check_log_enabled;
614 -- call START_ACTIVITY to create savepoint, check compatibility
615 -- and initialize message list
616 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
617 ,p_pkg_name => G_PKG_NAME
618 ,p_init_msg_list => p_init_msg_list
619 ,l_api_version => l_api_version
620 ,p_api_version => p_api_version
621 ,p_api_type => g_api_type
622 ,x_return_status => x_return_status);
623 -- check if activity started successfully
624 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
625 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
626 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
627 RAISE OKL_API.G_EXCEPTION_ERROR;
628 END IF;
629 --Pupulate lease quote rec
630 l_lease_qte_rec := populate_quote_rec(p_quote_id,x_return_status);
631
632 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
633 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
634 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
635 RAISE OKL_API.G_EXCEPTION_ERROR;
636 END IF;
637
638 IF(l_debug_enabled='Y') THEN
639 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_SALES_QUOTE_QA_PVT.run_qa_checker'
640 ,'begin debug call run_qa_checker');
641 END IF;
642 OKL_SALES_QUOTE_QA_PVT.run_qa_checker(
643 p_api_version => G_API_VERSION
644 ,p_init_msg_list => G_FALSE
645 ,p_object_type => l_object_type
646 ,p_object_id => p_quote_id
647 ,x_qa_result => x_qa_result
648 ,x_return_status => x_return_status
649 ,x_msg_count => x_msg_count
650 ,x_msg_data => x_msg_data);
651 IF(l_debug_enabled='Y') THEN
652 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_SALES_QUOTE_QA_PVT.run_qa_checker'
653 ,'end debug call run_qa_checker');
654 END IF;
655
656 IF(x_return_status = G_RET_STS_UNEXP_ERROR) THEN
657 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
658 ELSIF(x_return_status = G_RET_STS_ERROR) THEN
659 RAISE OKL_API.G_EXCEPTION_ERROR;
660 END IF;
661
662 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
663 ,x_msg_data => x_msg_data);
664 EXCEPTION
665 WHEN OKL_API.G_EXCEPTION_ERROR THEN
666 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
667 p_api_name => l_api_name,
668 p_pkg_name => G_PKG_NAME,
669 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
670 x_msg_count => x_msg_count,
671 x_msg_data => x_msg_data,
672 p_api_type => g_api_type);
673 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
674 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
675 p_api_name => l_api_name,
676 p_pkg_name => G_PKG_NAME,
677 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
678 x_msg_count => x_msg_count,
679 x_msg_data => x_msg_data,
680 p_api_type => g_api_type);
681 WHEN OTHERS THEN
682 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
683 p_api_name => l_api_name,
684 p_pkg_name => G_PKG_NAME,
685 p_exc_name => 'OTHERS',
686 x_msg_count => x_msg_count,
687 x_msg_data => x_msg_data,
688 p_api_type => g_api_type);
689
690 END validate;
691 -------------------------------------------------------------------------------
692 -- PROCEDURE price
693 -------------------------------------------------------------------------------
694 -- Start of comments
695 --
696 -- Procedure Name : price
697 -- Description : Price the lease quote and call the validation API
698 --
699 -- Business Rules :
700 --
701 --
702 -- Parameters :
703 -- Version : 1.0
704 -- History : 15-SEP-2005 SKGAUTAM created
705 -- End of comments
706 PROCEDURE price(
707 p_api_version IN NUMBER
708 ,p_init_msg_list IN VARCHAR2
709 ,p_quote_id IN NUMBER
710 ,x_return_status OUT NOCOPY VARCHAR2
711 ,x_msg_count OUT NOCOPY NUMBER
712 ,x_msg_data OUT NOCOPY VARCHAR2
713 ) IS
714 -- Variables Declarations
715 l_api_version CONSTANT NUMBER DEFAULT 1.0;
716 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'PRICE';
717 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
718 l_debug_enabled VARCHAR2(10);
719
720 l_quote_id OKL_LEASE_QUOTES_B.ID%TYPE;
721
722 -- Record/Table Type Declarations
723
724
725 BEGIN
726 x_return_status := OKL_API.G_RET_STS_SUCCESS;
727 -- check for logging on PROCEDURE level
728 l_debug_enabled := okl_debug_pub.check_log_enabled;
729 -- call START_ACTIVITY to create savepoint, check compatibility
730 -- and initialize message list
731 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
732 ,p_pkg_name => G_PKG_NAME
733 ,p_init_msg_list => p_init_msg_list
734 ,l_api_version => l_api_version
735 ,p_api_version => p_api_version
736 ,p_api_type => g_api_type
737 ,x_return_status => x_return_status);
738 -- check if activity started successfully
739 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
740 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
741 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
742 RAISE OKL_API.G_EXCEPTION_ERROR;
743 END IF;
744 l_quote_id := p_quote_id;
745
746 IF(l_debug_enabled='Y') THEN
747 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_PRICING_UTILS_PVT.price_standard_quote'
748 ,'begin debug call price_standard_quote');
749 END IF;
750 OKL_PRICING_UTILS_PVT.price_standard_quote(
751 x_return_status => x_return_status
752 ,x_msg_count => x_msg_count
753 ,x_msg_data => x_msg_data
754 ,p_api_version => G_API_VERSION
755 ,p_init_msg_list => G_FALSE
756 ,p_qte_id => l_quote_id);
757 IF(l_debug_enabled='Y') THEN
758 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_PRICING_UTILS_PVT.price_standard_quote'
759 ,'end debug call price_standard_quote');
760 END IF;
761
762 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
763 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
764 ELSIF x_return_status = G_RET_STS_ERROR THEN
765 RAISE OKL_API.G_EXCEPTION_ERROR;
766 END IF;
767
768 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
769 ,x_msg_data => x_msg_data);
770 EXCEPTION
771 WHEN OKL_API.G_EXCEPTION_ERROR THEN
772 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
773 p_api_name => l_api_name,
774 p_pkg_name => G_PKG_NAME,
775 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
776 x_msg_count => x_msg_count,
777 x_msg_data => x_msg_data,
778 p_api_type => g_api_type);
779 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
780 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
781 p_api_name => l_api_name,
782 p_pkg_name => G_PKG_NAME,
783 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
784 x_msg_count => x_msg_count,
785 x_msg_data => x_msg_data,
786 p_api_type => g_api_type);
787 WHEN OTHERS THEN
788 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
789 p_api_name => l_api_name,
790 p_pkg_name => G_PKG_NAME,
791 p_exc_name => 'OTHERS',
792 x_msg_count => x_msg_count,
793 x_msg_data => x_msg_data,
794 p_api_type => g_api_type);
795
796 END price;
797
798 -------------------------------------------------------------------------------
799 -- PROCEDURE calculate_tax
800 -------------------------------------------------------------------------------
801 -- Start of comments
802 --
803 -- Procedure Name : calculate_tax
804 -- Description : calculate the upfront tax
805 --
806 -- Business Rules :
807 --
808 --
809 -- Parameters :
810 -- Version : 1.0
811 -- History : 15-SEP-2005 SKGAUTAM created
812 -- End of comments
813 PROCEDURE calculate_tax(
814 p_api_version IN NUMBER
815 ,p_init_msg_list IN VARCHAR2
816 ,p_quote_id IN NUMBER
817 ,x_return_status OUT NOCOPY VARCHAR2
818 ,x_msg_count OUT NOCOPY NUMBER
819 ,x_msg_data OUT NOCOPY VARCHAR2
820 ) IS
821 -- Variables Declarations
822 l_api_version CONSTANT NUMBER DEFAULT 1.0;
823 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CALCULATE TAX';
824 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
825 l_debug_enabled VARCHAR2(10);
826
827 l_source_table VARCHAR2(30):= 'OKL_LEASE_QUOTES_B';
828 l_source_trx_name VARCHAR2(15):='Quoting';
829 l_quote_id OKL_LEASE_QUOTES_B.ID%TYPE;
830
831 -- Record/Table Type Declarations
832
833
834 BEGIN
835 x_return_status := OKL_API.G_RET_STS_SUCCESS;
836 -- check for logging on PROCEDURE level
837 l_debug_enabled := okl_debug_pub.check_log_enabled;
838 -- call START_ACTIVITY to create savepoint, check compatibility
839 -- and initialize message list
840 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
841 ,p_pkg_name => G_PKG_NAME
842 ,p_init_msg_list => p_init_msg_list
843 ,l_api_version => l_api_version
844 ,p_api_version => p_api_version
845 ,p_api_type => g_api_type
846 ,x_return_status => x_return_status);
847 -- check if activity started successfully
848 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
849 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
850 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
851 RAISE OKL_API.G_EXCEPTION_ERROR;
852 END IF;
853 l_quote_id := p_quote_id;
854
855 IF(l_debug_enabled='Y') THEN
856 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_PROCESS_SALES_TAX_PVT.calculate_sales_tax'
857 ,'begin debug call validate');
858 END IF;
859 OKL_PROCESS_SALES_TAX_PVT.calculate_sales_tax(
860 p_api_version => G_API_VERSION
861 ,p_init_msg_list => G_FALSE
862 ,x_return_status => x_return_status
863 ,x_msg_count => x_msg_count
864 ,x_msg_data => x_msg_data
865 ,p_source_trx_id => l_quote_id
866 ,p_source_trx_name => l_source_trx_name
867 ,p_source_table => l_source_table);
868
869 IF(l_debug_enabled='Y') THEN
870 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_PROCESS_SALES_TAX_PVT.calculate_sales_tax'
871 ,'end debug call price_standard_quote');
872 END IF;
873
874 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
875 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
876 ELSIF x_return_status = G_RET_STS_ERROR THEN
877 RAISE OKL_API.G_EXCEPTION_ERROR;
878 END IF;
879
880 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
881 ,x_msg_data => x_msg_data);
882 EXCEPTION
883 WHEN OKL_API.G_EXCEPTION_ERROR THEN
884 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
885 p_api_name => l_api_name,
886 p_pkg_name => G_PKG_NAME,
887 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
888 x_msg_count => x_msg_count,
889 x_msg_data => x_msg_data,
890 p_api_type => g_api_type);
891 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
892 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
893 p_api_name => l_api_name,
894 p_pkg_name => G_PKG_NAME,
895 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
896 x_msg_count => x_msg_count,
897 x_msg_data => x_msg_data,
898 p_api_type => g_api_type);
899 WHEN OTHERS THEN
900 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
901 p_api_name => l_api_name,
902 p_pkg_name => G_PKG_NAME,
903 p_exc_name => 'OTHERS',
904 x_msg_count => x_msg_count,
905 x_msg_data => x_msg_data,
906 p_api_type => g_api_type);
907
908 END calculate_tax;
909 -------------------------------------------------------------------------------
910 -- PROCEDURE handle_parent_object_status
911 -------------------------------------------------------------------------------
912 -- Start of comments
913 --
914 -- Procedure Name : handle_parent_object_status
915 -- Description : Update status of parent of lease quote
916 --
917 -- Business Rules :
918 --
919 --
920 -- Parameters :
921 -- Version : 1.0
922 -- History : 15-NOV-2005 ASAWANKA created
923 -- End of comments
924
925 PROCEDURE handle_parent_object_status(
926 p_api_version IN NUMBER
927 ,p_init_msg_list IN VARCHAR2
928 ,x_return_status OUT NOCOPY VARCHAR2
929 ,x_msg_count OUT NOCOPY NUMBER
930 ,x_msg_data OUT NOCOPY VARCHAR2
931 ,p_parent_object_code IN VARCHAR2
932 ,p_parent_object_id IN NUMBER
933 ) IS
934
935 -- Variables Declarations
936 l_api_version CONSTANT NUMBER DEFAULT 1.0;
937 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'HNDL_PRNT_STS';
938 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
939 l_debug_enabled VARCHAR2(10);
940 lx_return_status VARCHAR2(1);
941
942 x_lapv_rec OKL_LAP_PVT.LAPV_REC_TYPE;
943 lx_lsqv_rec lease_qte_rec_type;
944 lx_lapv_rec OKL_LAP_PVT.LAPV_REC_TYPE;
945 l_lsqv_rec lease_qte_rec_type;
946 l_quote_id NUMBER;
947
948 CURSOR get_primary_quote
949 IS
950 SELECT ID
951 FROM OKL_LEASE_QUOTES_B
952 WHERE parent_object_id =p_parent_object_id
953 AND primary_quote = 'Y';
954
955 BEGIN
956 x_return_status := OKL_API.G_RET_STS_SUCCESS;
957 -- check for logging on PROCEDURE level
958 l_debug_enabled := okl_debug_pub.check_log_enabled;
959 -- call START_ACTIVITY to create savepoint, check compatibility
960 -- and initialize message list
961 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
962 ,p_pkg_name => G_PKG_NAME
963 ,p_init_msg_list => p_init_msg_list
964 ,l_api_version => l_api_version
965 ,p_api_version => p_api_version
966 ,p_api_type => g_api_type
967 ,x_return_status => x_return_status);
968 -- check if activity started successfully
969 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
970 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
971 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
972 RAISE OKL_API.G_EXCEPTION_ERROR;
973 END IF;
974
975 IF(l_debug_enabled='Y') THEN
976 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_APP_PVT.populate_lease_app'
977 ,'begin debug call populate_lease_app');
978 END IF;
979
980 IF p_parent_object_code = 'LEASEAPP' THEN
981
982 OPEN get_primary_quote;
983 FETCH get_primary_quote into l_quote_id;
984 CLOSE get_primary_quote;
985
986 l_lsqv_rec := populate_quote_rec ( p_quote_id => l_quote_id,
987 x_return_status => x_return_status );
988
989 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
990 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
991 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
992 RAISE OKL_API.G_EXCEPTION_ERROR;
993 END IF;
994 l_lsqv_rec.STATUS := 'PR-INCOMPLETE';
995
996 OKL_LEASE_QUOTE_PVT.update_lease_qte(
997 p_api_version => G_API_VERSION
998 ,p_init_msg_list => G_FALSE
999 ,p_transaction_control => G_FALSE
1000 ,p_lease_qte_rec => l_lsqv_rec
1001 ,x_lease_qte_rec => lx_lsqv_rec
1002 ,x_return_status => x_return_status
1003 ,x_msg_count => x_msg_count
1004 ,x_msg_data => x_msg_data);
1005
1006 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1007 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1008 ELSIF x_return_status = G_RET_STS_ERROR THEN
1009 RAISE OKL_API.G_EXCEPTION_ERROR;
1010 END IF;
1011
1012
1013 OKL_LEASE_APP_PVT.populate_lease_app(p_api_version => p_api_version,
1014 p_init_msg_list => p_init_msg_list,
1015 x_return_status => x_return_status,
1016 x_msg_count => x_msg_count,
1017 x_msg_data => x_msg_data,
1018 p_lap_id => p_parent_object_id,
1019 x_lapv_rec => x_lapv_rec,
1020 x_lsqv_rec => lx_lsqv_rec);
1021 IF(l_debug_enabled='Y') THEN
1022 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_APP_PVT.populate_lease_app'
1023 ,'end debug call populate_lease_app');
1024 END IF;
1025 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1026 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1027 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1028 RAISE OKL_API.G_EXCEPTION_ERROR;
1029 END IF;
1030
1031 IF x_lapv_rec.APPLICATION_STATUS IN ('PR-COMPLETE', 'PR-APPROVED','PR-REJECTED') THEN
1032 x_lapv_rec.APPLICATION_STATUS := 'INCOMPLETE';
1033 IF(l_debug_enabled='Y') THEN
1034 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_APP_PVT.lease_app_upd'
1035 ,'begin debug call lease_app_upd');
1036 END IF;
1037
1038 OKL_LEASE_APP_PVT.set_lease_app_status(p_api_version => p_api_version,
1039 p_init_msg_list => p_init_msg_list,
1040 x_return_status => x_return_status,
1041 x_msg_count => x_msg_count,
1042 x_msg_data => x_msg_data,
1043 p_lap_id => x_lapv_rec.id,
1044 p_lap_status => x_lapv_rec.APPLICATION_STATUS
1045 );
1046 IF(l_debug_enabled='Y') THEN
1047 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_APP_PVT.lease_app_upd'
1048 ,'end debug call lease_app_upd');
1049 END IF;
1050 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1051 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1052 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1053 RAISE OKL_API.G_EXCEPTION_ERROR;
1054 END IF;
1055 END IF;
1056 END IF;
1057
1058 x_return_status := okc_api.G_RET_STS_SUCCESS;
1059 OKL_API.END_ACTIVITY(
1060 x_msg_count => x_msg_count
1061 ,x_msg_data => x_msg_data);
1062 EXCEPTION
1063 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1064 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1065 p_api_name => l_api_name,
1066 p_pkg_name => G_PKG_NAME,
1067 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1068 x_msg_count => x_msg_count,
1069 x_msg_data => x_msg_data,
1070 p_api_type => G_API_TYPE);
1071 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1072 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1073 p_api_name => l_api_name,
1074 p_pkg_name => G_PKG_NAME,
1075 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1076 x_msg_count => x_msg_count,
1077 x_msg_data => x_msg_data,
1078 p_api_type => G_API_TYPE);
1079 WHEN OTHERS THEN
1080 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1081 p_api_name => l_api_name,
1082 p_pkg_name => G_PKG_NAME,
1083 p_exc_name => 'OTHERS',
1084 x_msg_count => x_msg_count,
1085 x_msg_data => x_msg_data,
1086 p_api_type => G_API_TYPE);
1087
1088 END handle_parent_object_status;
1089
1090 -------------------------------------------------------------------------------
1091 -- PROCEDURE create_update_payment
1092 -------------------------------------------------------------------------------
1093 -- Start of comments
1094 --
1095 -- Procedure Name : create_update_payment
1096 -- Description : Create/Update Lease Quote pricing options and payments
1097 --
1098 -- Business Rules :
1099 --
1100 --
1101 -- Parameters :
1102 -- Version : 1.0
1103 -- History : 15-SEP-2005 SKGAUTAM created
1104 -- End of comments
1105 PROCEDURE create_update_payment (
1106 p_api_version IN NUMBER
1107 ,p_init_msg_list IN VARCHAR2
1108 ,p_lease_qte_rec IN lease_qte_rec_type
1109 ,p_payment_header_rec IN cashflow_hdr_rec_type
1110 ,p_payment_level_tbl IN cashflow_level_tbl_type
1111 ,x_return_status OUT NOCOPY VARCHAR2
1112 ,x_msg_count OUT NOCOPY NUMBER
1113 ,x_msg_data OUT NOCOPY VARCHAR2
1114 ) IS
1115
1116 -- Variables Declarations
1117 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1118 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE_UPDATE_PAYMENT';
1119 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1120 l_debug_enabled VARCHAR2(10);
1121
1122 -- Record/Table Type Declarations
1123 l_payment_header_rec cashflow_hdr_rec_type;
1124 l_payment_level_tbl cashflow_level_tbl_type;
1125 l_lease_qte_rec lease_qte_rec_type;
1126 lx_lease_qte_rec lease_qte_rec_type;
1127 l_fee_rec fee_rec_type;
1128 lx_fee_rec fee_rec_type;
1129 l_asset_rec asset_rec_type;
1130 lx_asset_rec asset_rec_type;
1131
1132 lv_cash_flow_exists VARCHAR2(3);
1133 l_all_lines_overriden VARCHAR2(3);
1134 l_entered VARCHAR2(3);
1135
1136 CURSOR c_check_cash_flow(p_quote_id OKL_LEASE_QUOTES_B.ID%TYPE)
1137 IS
1138 SELECT 'YES'
1139 FROM OKL_CASH_FLOW_OBJECTS
1140 WHERE OTY_CODE = 'LEASE_QUOTE'
1141 AND SOURCE_TABLE = 'OKL_LEASE_QUOTES_B'
1142 AND SOURCE_ID = p_quote_id;
1143
1144 CURSOR quote_assets_csr(lc_quote_id IN NUMBER) IS
1145 SELECT ID
1146 FROM OKL_ASSETS_B
1147 WHERE parent_object_id = lc_quote_id
1148 AND parent_object_code = 'LEASEQUOTE';
1149
1150 CURSOR quote_fees_csr(lc_quote_id IN NUMBER) IS
1151 SELECT ID
1152 FROM OKL_FEES_B
1153 WHERE parent_object_id = lc_quote_id
1154 AND parent_object_code = 'LEASEQUOTE'
1155 AND fee_type <> 'CAPITALIZED';
1156 BEGIN
1157 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1158 -- check for logging on PROCEDURE level
1159 l_debug_enabled := okl_debug_pub.check_log_enabled;
1160 -- call START_ACTIVITY to create savepoint, check compatibility
1161 -- and initialize message list
1162 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
1163 ,p_pkg_name => G_PKG_NAME
1164 ,p_init_msg_list => p_init_msg_list
1165 ,l_api_version => l_api_version
1166 ,p_api_version => p_api_version
1167 ,p_api_type => g_api_type
1168 ,x_return_status => x_return_status);
1169 -- check if activity started successfully
1170 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1171 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1172 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1173 RAISE OKL_API.G_EXCEPTION_ERROR;
1174 END IF;
1175 --Pupulate quote record
1176 l_lease_qte_rec := populate_quote_rec(p_lease_qte_rec.id,x_return_status);
1177
1178 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1179 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1180 ELSIF x_return_status = G_RET_STS_ERROR THEN
1181 RAISE OKL_API.G_EXCEPTION_ERROR;
1182 END IF;
1183
1184 l_lease_qte_rec.TARGET_RATE_TYPE := p_lease_qte_rec.TARGET_RATE_TYPE;
1185 l_lease_qte_rec.TARGET_RATE := p_lease_qte_rec.TARGET_RATE;
1186 l_lease_qte_rec.TARGET_AMOUNT := p_lease_qte_rec.TARGET_AMOUNT;
1187 l_lease_qte_rec.TARGET_FREQUENCY := p_lease_qte_rec.TARGET_FREQUENCY;
1188 l_lease_qte_rec.TARGET_ARREARS_YN := p_lease_qte_rec.TARGET_ARREARS_YN;
1189 l_lease_qte_rec.TARGET_PERIODS := p_lease_qte_rec.TARGET_PERIODS;
1190 l_lease_qte_rec.STRUCTURED_PRICING := p_lease_qte_rec.STRUCTURED_PRICING;
1191 l_lease_qte_rec.LINE_LEVEL_PRICING := p_lease_qte_rec.LINE_LEVEL_PRICING;
1192 l_lease_qte_rec.LEASE_RATE_FACTOR := p_lease_qte_rec.LEASE_RATE_FACTOR;
1193 l_lease_qte_rec.rate_template_id := p_lease_qte_rec.rate_template_id;
1194 l_lease_qte_rec.rate_card_id := p_lease_qte_rec.rate_card_id;
1195
1196 l_all_lines_overriden := okl_sales_quote_qa_pvt.are_all_lines_overriden(l_lease_qte_rec.id,l_lease_qte_rec.pricing_method,l_lease_qte_rec.LINE_LEVEL_PRICING ,x_return_status);
1197
1198 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1199 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1200 ELSIF x_return_status = G_RET_STS_ERROR THEN
1201 RAISE OKL_API.G_EXCEPTION_ERROR;
1202 END IF;
1203
1204 IF l_all_lines_overriden = 'Y' THEN
1205 l_entered := are_qte_pricing_opts_entered(l_lease_qte_rec,p_payment_level_tbl.count,x_return_status);
1206
1207 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1208 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1209 ELSIF x_return_status = G_RET_STS_ERROR THEN
1210 RAISE OKL_API.G_EXCEPTION_ERROR;
1211 END IF;
1212
1213 IF l_entered = 'Y' THEN
1214
1215 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1216 p_msg_name => 'OKL_QTE_PMT_ENTERED');
1217 RAISE OKL_API.G_EXCEPTION_ERROR;
1218 END IF;
1219 l_lease_qte_rec.TARGET_RATE_TYPE := NULL;
1220 l_lease_qte_rec.TARGET_RATE := NULL;
1221 l_lease_qte_rec.TARGET_AMOUNT := NULL;
1222 l_lease_qte_rec.TARGET_FREQUENCY := NULL;
1223 l_lease_qte_rec.TARGET_ARREARS_YN := NULL;
1224 l_lease_qte_rec.TARGET_PERIODS := NULL;
1225 l_lease_qte_rec.STRUCTURED_PRICING := NULL;
1226 l_lease_qte_rec.LEASE_RATE_FACTOR := NULL;
1227 l_lease_qte_rec.rate_template_id := NULL;
1228 l_lease_qte_rec.rate_card_id := NULL;
1229 ELSE
1230 IF l_lease_qte_rec.pricing_method NOT IN ( 'TR','RC','SY') THEN
1231 IF l_lease_qte_rec.structured_pricing = 'N' AND l_lease_qte_rec.rate_template_id IS NULL THEN
1232 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1233 p_msg_name => 'OKL_SRT_MANDATORY');
1234 RAISE OKL_API.G_EXCEPTION_ERROR;
1235 ELSIF l_lease_qte_rec.structured_pricing = 'N' AND l_lease_qte_rec.target_amount IS NULL
1236 AND l_lease_qte_rec.pricing_method NOT IN ('SP','SM')
1237 THEN
1238 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1239 p_msg_name => 'OKL_PA_MANDATORY');
1240 RAISE OKL_API.G_EXCEPTION_ERROR;
1241 END IF;
1242 END IF;
1243 END IF;
1244 IF l_all_lines_overriden = 'N' OR l_entered = 'N' THEN
1245 IF(l_debug_enabled='Y') THEN
1246 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_PVT.update_lease_qte'
1247 ,'begin debug call update_lease_qte');
1248 END IF;
1249 OKL_LEASE_QUOTE_PVT.update_lease_qte(
1250 p_api_version => G_API_VERSION
1251 ,p_init_msg_list => G_FALSE
1252 ,p_transaction_control => G_FALSE
1253 ,p_lease_qte_rec => l_lease_qte_rec
1254 ,x_lease_qte_rec => lx_lease_qte_rec
1255 ,x_return_status => x_return_status
1256 ,x_msg_count => x_msg_count
1257 ,x_msg_data => x_msg_data);
1258 IF(l_debug_enabled='Y') THEN
1259 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_PVT.update_lease_qte'
1260 ,'end debug call update_lease_qte');
1261 END IF;
1262
1263 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1264 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1265 ELSIF x_return_status = G_RET_STS_ERROR THEN
1266 RAISE OKL_API.G_EXCEPTION_ERROR;
1267 END IF;
1268 IF l_all_lines_overriden = 'Y' THEN
1269 IF(l_debug_enabled='Y') THEN
1270 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.okl_lease_quote_cashflow_pvt.delete_cash_flows'
1271 ,'begin debug call okl_lease_quote_cashflow_pvt.delete_cash_flows');
1272 END IF;
1273 okl_lease_quote_cashflow_pvt.delete_cashflows(
1274 p_api_version => G_API_VERSION
1275 ,p_init_msg_list => G_FALSE
1276 ,p_transaction_control => G_FALSE
1277 ,p_source_object_code => 'LEASE_QUOTE'
1278 ,p_source_object_id => p_lease_qte_rec.id
1279 ,x_return_status => x_return_status
1280 ,x_msg_count => x_msg_count
1281 ,x_msg_data => x_msg_data);
1282 IF(l_debug_enabled='Y') THEN
1283 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.okl_lease_quote_cashflow_pvt.delete_cash_flows'
1284 ,'End debug call okl_lease_quote_cashflow_pvt.delete_cash_flows');
1285 END IF;
1286
1287 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1288 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1289 ELSIF x_return_status = G_RET_STS_ERROR THEN
1290 RAISE OKL_API.G_EXCEPTION_ERROR;
1291 END IF;
1292
1293 ELSE
1294 l_payment_header_rec := p_payment_header_rec;
1295 l_payment_level_tbl := p_payment_level_tbl;
1296
1297 l_payment_header_rec.parent_object_id := l_lease_qte_rec.id;
1298 l_payment_header_rec.quote_id := l_lease_qte_rec.id;
1299 l_payment_header_rec.type_code:= 'INFLOW';
1300
1301 IF ((l_payment_level_tbl.COUNT > 0 AND l_payment_header_rec.stream_type_id IS NULL) OR
1302 (l_payment_header_rec.stream_type_id IS NOT NULL AND l_payment_level_tbl.COUNT = 0 ))
1303 THEN
1304 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1305 p_msg_name => 'OKL_EPT_PAYMENT_NA');
1306 RAISE OKL_API.G_EXCEPTION_ERROR;
1307 ELSE
1308 -- Check if the Cash flows already exists
1309 OPEN c_check_cash_flow(p_quote_id => l_payment_header_rec.quote_id);
1310 FETCH c_check_cash_flow into lv_cash_flow_exists;
1311 CLOSE c_check_cash_flow;
1312
1313 IF (lv_cash_flow_exists = 'YES') THEN
1314
1315 IF(l_debug_enabled='Y') THEN
1316 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow'
1317 ,'begin debug call update_cashflow');
1318 END IF;
1319 OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow (
1320 p_api_version => G_API_VERSION
1321 ,p_init_msg_list => G_FALSE
1322 ,p_transaction_control => G_FALSE
1323 ,p_cashflow_header_rec => l_payment_header_rec
1324 ,p_cashflow_level_tbl => l_payment_level_tbl
1325 ,x_return_status => x_return_status
1326 ,x_msg_count => x_msg_count
1327 ,x_msg_data => x_msg_data);
1328 IF(l_debug_enabled='Y') THEN
1329 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow'
1330 ,'end debug call update_cashflow');
1331 END IF;
1332
1333 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1334 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1335 ELSIF x_return_status = G_RET_STS_ERROR THEN
1336 RAISE OKL_API.G_EXCEPTION_ERROR;
1337 END IF;
1338 ELSE
1339 IF(l_debug_enabled='Y') THEN
1340 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow'
1341 ,'begin debug call create_cashflow');
1342 END IF;
1343 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow (
1344 p_api_version => G_API_VERSION
1345 ,p_init_msg_list => G_FALSE
1346 ,p_transaction_control => G_FALSE
1347 ,p_cashflow_header_rec => l_payment_header_rec
1348 ,p_cashflow_level_tbl => l_payment_level_tbl
1349 ,x_return_status => x_return_status
1350 ,x_msg_count => x_msg_count
1351 ,x_msg_data => x_msg_data);
1352 IF(l_debug_enabled='Y') THEN
1353 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow'
1354 ,'end debug call create_cashflow');
1355 END IF;
1356
1357 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1358 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1359 ELSIF x_return_status = G_RET_STS_ERROR THEN
1360 RAISE OKL_API.G_EXCEPTION_ERROR;
1361 END IF;
1362 END IF;
1363 END IF;
1364 END IF;
1365 END IF;
1366 IF p_lease_qte_rec.line_level_pricing = 'N' THEN
1367 FOR l_quote_asset_rec IN quote_assets_csr(l_lease_qte_rec.id) LOOP
1368
1369 delete_line_payment(
1370 p_api_version => G_API_VERSION
1371 ,p_init_msg_list => G_FALSE
1372 ,p_source_object_code => 'QUOTED_ASSET'
1373 ,p_source_object_id => l_quote_asset_rec.id
1374 ,x_return_status => x_return_status
1375 ,x_msg_count => x_msg_count
1376 ,x_msg_data => x_msg_data);
1377
1378 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1379 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1380 ELSIF x_return_status = G_RET_STS_ERROR THEN
1381 RAISE OKL_API.G_EXCEPTION_ERROR;
1382 END IF;
1383
1384 END LOOP;
1385 /* FOR l_quote_fees_rec IN quote_fees_csr(l_lease_qte_rec.id) LOOP
1386
1387 delete_line_payment(
1388 p_api_version => G_API_VERSION
1389 ,p_init_msg_list => G_FALSE
1390 ,p_source_object_code => 'QUOTED_FEE'
1391 ,p_source_object_id => l_quote_fees_rec.id
1392 ,x_return_status => x_return_status
1393 ,x_msg_count => x_msg_count
1394 ,x_msg_data => x_msg_data);
1395
1396 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1397 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1398 ELSIF x_return_status = G_RET_STS_ERROR THEN
1399 RAISE OKL_API.G_EXCEPTION_ERROR;
1400 END IF;
1401
1402 END LOOP; */
1403 END IF;
1404
1405 /*handle_parent_object_status(
1406 p_api_version => G_API_VERSION
1407 ,p_init_msg_list => G_FALSE
1408 ,x_return_status => x_return_status
1409 ,x_msg_count => x_msg_count
1410 ,x_msg_data => x_msg_data
1411 ,p_parent_object_code => l_lease_qte_rec.parent_object_code
1412 ,p_parent_object_id => l_lease_qte_rec.parent_object_id
1413 );
1414
1415 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1416 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1417 ELSIF x_return_status = G_RET_STS_ERROR THEN
1418 RAISE OKL_API.G_EXCEPTION_ERROR;
1419 END IF;*/
1420
1421
1422 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
1423 ,x_msg_data => x_msg_data);
1424 EXCEPTION
1425 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1426 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1427 p_api_name => l_api_name,
1428 p_pkg_name => G_PKG_NAME,
1429 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1430 x_msg_count => x_msg_count,
1431 x_msg_data => x_msg_data,
1432 p_api_type => g_api_type);
1433 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1434 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1435 p_api_name => l_api_name,
1436 p_pkg_name => G_PKG_NAME,
1437 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1438 x_msg_count => x_msg_count,
1439 x_msg_data => x_msg_data,
1440 p_api_type => g_api_type);
1441 WHEN OTHERS THEN
1442 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1443 p_api_name => l_api_name,
1444 p_pkg_name => G_PKG_NAME,
1445 p_exc_name => 'OTHERS',
1446 x_msg_count => x_msg_count,
1447 x_msg_data => x_msg_data,
1448 p_api_type => g_api_type);
1449 END create_update_payment;
1450
1451 -------------------------------------------------------------------------------
1452 -- PROCEDURE create_update_payment
1453 -------------------------------------------------------------------------------
1454 -- Start of comments
1455 --
1456 -- Procedure Name : create_update_payment
1457 -- Description : Create/Update Lease Quote pricing options
1458 --
1459 -- Business Rules :
1460 --
1461 --
1462 -- Parameters :
1463 -- Version : 1.0
1464 -- History : 15-SEP-2005 SKGAUTAM created
1465 -- End of comments
1466 PROCEDURE create_update_payment (
1467 p_api_version IN NUMBER
1468 ,p_init_msg_list IN VARCHAR2
1469 ,p_lease_qte_rec IN lease_qte_rec_type
1470 ,x_return_status OUT NOCOPY VARCHAR2
1471 ,x_msg_count OUT NOCOPY NUMBER
1472 ,x_msg_data OUT NOCOPY VARCHAR2
1473 ) IS
1474
1475 -- Variables Declarations
1476 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1477 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE_UPDATE_PAYMENT';
1478 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1479 l_debug_enabled VARCHAR2(10);
1480
1481 -- Record/Table Type Declarations
1482 l_payment_header_rec cashflow_hdr_rec_type;
1483 l_payment_level_tbl cashflow_level_tbl_type;
1484 l_lease_qte_rec lease_qte_rec_type;
1485 lx_lease_qte_rec lease_qte_rec_type;
1486 l_fee_rec fee_rec_type;
1487 lx_fee_rec fee_rec_type;
1488 l_asset_rec asset_rec_type;
1489 lx_asset_rec asset_rec_type;
1490 l_all_lines_overriden VARCHAR2(3);
1491 l_entered VARCHAR2(3);
1492
1493 CURSOR quote_assets_csr(lc_quote_id IN NUMBER) IS
1494 SELECT ID
1495 FROM OKL_ASSETS_B
1496 WHERE parent_object_id = lc_quote_id
1497 AND parent_object_code = 'LEASEQUOTE';
1498
1499 CURSOR quote_fees_csr(lc_quote_id IN NUMBER) IS
1500 SELECT ID
1501 FROM OKL_FEES_B
1502 WHERE parent_object_id = lc_quote_id
1503 AND parent_object_code = 'LEASEQUOTE'
1504 AND fee_type <> 'CAPITALIZED';
1505
1506 BEGIN
1507 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1508 -- check for logging on PROCEDURE level
1509 l_debug_enabled := okl_debug_pub.check_log_enabled;
1510 -- call START_ACTIVITY to create savepoint, check compatibility
1511 -- and initialize message list
1512 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
1513 ,p_pkg_name => G_PKG_NAME
1514 ,p_init_msg_list => p_init_msg_list
1515 ,l_api_version => l_api_version
1516 ,p_api_version => p_api_version
1517 ,p_api_type => g_api_type
1518 ,x_return_status => x_return_status);
1519 -- check if activity started successfully
1520 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1521 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1522 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1523 RAISE OKL_API.G_EXCEPTION_ERROR;
1524 END IF;
1525
1526 --Populate quote record
1527 l_lease_qte_rec := populate_quote_rec(p_lease_qte_rec.id,x_return_status);
1528
1529 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1530 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1531 ELSIF x_return_status = G_RET_STS_ERROR THEN
1532 RAISE OKL_API.G_EXCEPTION_ERROR;
1533 END IF;
1534
1535 l_lease_qte_rec.TARGET_RATE_TYPE := p_lease_qte_rec.TARGET_RATE_TYPE;
1536 l_lease_qte_rec.TARGET_RATE := p_lease_qte_rec.TARGET_RATE;
1537 l_lease_qte_rec.TARGET_AMOUNT := p_lease_qte_rec.TARGET_AMOUNT;
1538 l_lease_qte_rec.TARGET_FREQUENCY := p_lease_qte_rec.TARGET_FREQUENCY;
1539 l_lease_qte_rec.TARGET_ARREARS_YN := p_lease_qte_rec.TARGET_ARREARS_YN;
1540 l_lease_qte_rec.TARGET_PERIODS := p_lease_qte_rec.TARGET_PERIODS;
1541 l_lease_qte_rec.STRUCTURED_PRICING := p_lease_qte_rec.STRUCTURED_PRICING;
1542 l_lease_qte_rec.LINE_LEVEL_PRICING := p_lease_qte_rec.LINE_LEVEL_PRICING;
1543 l_lease_qte_rec.LEASE_RATE_FACTOR := p_lease_qte_rec.LEASE_RATE_FACTOR;
1544 l_lease_qte_rec.rate_template_id := p_lease_qte_rec.rate_template_id;
1545 l_lease_qte_rec.rate_card_id := p_lease_qte_rec.rate_card_id;
1546 l_all_lines_overriden := okl_sales_quote_qa_pvt.are_all_lines_overriden(l_lease_qte_rec.id,l_lease_qte_rec.pricing_method,l_lease_qte_rec.LINE_LEVEL_PRICING ,x_return_status);
1547
1548 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1549 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1550 ELSIF x_return_status = G_RET_STS_ERROR THEN
1551 RAISE OKL_API.G_EXCEPTION_ERROR;
1552 END IF;
1553 IF l_all_lines_overriden = 'Y' THEN
1554 l_entered := are_qte_pricing_opts_entered(l_lease_qte_rec,0,x_return_status);
1555
1556 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1557 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1558 ELSIF x_return_status = G_RET_STS_ERROR THEN
1559 RAISE OKL_API.G_EXCEPTION_ERROR;
1560 END IF;
1561
1562 IF l_entered = 'Y' THEN
1563
1564 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1565 p_msg_name => 'OKL_QTE_PMT_ENTERED');
1566 RAISE OKL_API.G_EXCEPTION_ERROR;
1567 END IF;
1568 l_lease_qte_rec.TARGET_RATE_TYPE := NULL;
1569 l_lease_qte_rec.TARGET_RATE := NULL;
1570 l_lease_qte_rec.TARGET_AMOUNT := NULL;
1571 l_lease_qte_rec.TARGET_FREQUENCY := NULL;
1572 l_lease_qte_rec.TARGET_ARREARS_YN := NULL;
1573 l_lease_qte_rec.TARGET_PERIODS := NULL;
1574 l_lease_qte_rec.STRUCTURED_PRICING := NULL;
1575 l_lease_qte_rec.LEASE_RATE_FACTOR := NULL;
1576 l_lease_qte_rec.rate_template_id := NULL;
1577 l_lease_qte_rec.rate_card_id := NULL;
1578 ELSE
1579
1580 IF l_lease_qte_rec.pricing_method = 'RC' THEN
1581 IF l_lease_qte_rec.structured_pricing = 'N' AND l_lease_qte_rec.rate_card_id IS NULL THEN
1582 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1583 p_msg_name => 'OKL_RC_MANDATORY');
1584 RAISE OKL_API.G_EXCEPTION_ERROR;
1585 ELSIF l_lease_qte_rec.structured_pricing = 'Y' AND l_lease_qte_rec.lease_rate_factor IS NULL THEN
1586 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1587 p_msg_name => 'OKL_RF_MANDATORY');
1588 RAISE OKL_API.G_EXCEPTION_ERROR;
1589 END IF;
1590 ELSIF l_lease_qte_rec.pricing_method NOT IN ('TR','SY') THEN
1591 IF l_lease_qte_rec.structured_pricing = 'N' AND l_lease_qte_rec.rate_template_id IS NULL THEN
1592 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1593 p_msg_name => 'OKL_SRT_MANDATORY');
1594 RAISE OKL_API.G_EXCEPTION_ERROR;
1595 ELSIF l_lease_qte_rec.structured_pricing = 'N' AND l_lease_qte_rec.target_amount IS NULL
1596 AND l_lease_qte_rec.pricing_method NOT IN ('SP','SM')
1597 THEN
1598 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1599 p_msg_name => 'OKL_PA_MANDATORY');
1600 RAISE OKL_API.G_EXCEPTION_ERROR;
1601 END IF;
1602 END IF;
1603 END IF;
1604 IF l_all_lines_overriden = 'N' OR l_entered = 'N' THEN
1605 IF(l_debug_enabled='Y') THEN
1606 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_PVT.update_lease_qte'
1607 ,'begin debug call update_lease_qte');
1608 END IF;
1609 OKL_LEASE_QUOTE_PVT.update_lease_qte(
1610 p_api_version => G_API_VERSION
1611 ,p_init_msg_list => G_FALSE
1612 ,p_transaction_control => G_FALSE
1613 ,p_lease_qte_rec => l_lease_qte_rec
1614 ,x_lease_qte_rec => lx_lease_qte_rec
1615 ,x_return_status => x_return_status
1616 ,x_msg_count => x_msg_count
1617 ,x_msg_data => x_msg_data);
1618 IF(l_debug_enabled='Y') THEN
1619 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_PVT.update_lease_qte'
1620 ,'End debug call update_lease_qte');
1621 END IF;
1622
1623 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1624 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1625 ELSIF x_return_status = G_RET_STS_ERROR THEN
1626 RAISE OKL_API.G_EXCEPTION_ERROR;
1627 END IF;
1628
1629 IF(l_debug_enabled='Y') THEN
1630 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.okl_lease_quote_cashflow_pvt.delete_cash_flows'
1631 ,'begin debug call okl_lease_quote_cashflow_pvt.delete_cash_flows');
1632 END IF;
1633 okl_lease_quote_cashflow_pvt.delete_cashflows(
1634 p_api_version => G_API_VERSION
1635 ,p_init_msg_list => G_FALSE
1636 ,p_transaction_control => G_FALSE
1637 ,p_source_object_code => 'LEASE_QUOTE'
1638 ,p_source_object_id => p_lease_qte_rec.id
1639 ,x_return_status => x_return_status
1640 ,x_msg_count => x_msg_count
1641 ,x_msg_data => x_msg_data);
1642 IF(l_debug_enabled='Y') THEN
1643 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.okl_lease_quote_cashflow_pvt.delete_cash_flows'
1644 ,'End debug call okl_lease_quote_cashflow_pvt.delete_cash_flows');
1645 END IF;
1646
1647 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1648 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1649 ELSIF x_return_status = G_RET_STS_ERROR THEN
1650 RAISE OKL_API.G_EXCEPTION_ERROR;
1651 END IF;
1652 END IF;
1653 IF p_lease_qte_rec.line_level_pricing = 'N' THEN
1654 FOR l_quote_asset_rec IN quote_assets_csr(l_lease_qte_rec.id) LOOP
1655
1656 delete_line_payment(
1657 p_api_version => G_API_VERSION
1658 ,p_init_msg_list => G_FALSE
1659 ,p_source_object_code => 'QUOTED_ASSET'
1660 ,p_source_object_id => l_quote_asset_rec.id
1661 ,x_return_status => x_return_status
1662 ,x_msg_count => x_msg_count
1663 ,x_msg_data => x_msg_data);
1664
1665 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1666 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1667 ELSIF x_return_status = G_RET_STS_ERROR THEN
1668 RAISE OKL_API.G_EXCEPTION_ERROR;
1669 END IF;
1670
1671 END LOOP;
1672 /* FOR l_quote_fees_rec IN quote_fees_csr(l_lease_qte_rec.id) LOOP
1673
1674 delete_line_payment(
1675 p_api_version => G_API_VERSION
1676 ,p_init_msg_list => G_FALSE
1677 ,p_source_object_code => 'QUOTED_FEE'
1678 ,p_source_object_id => l_quote_fees_rec.id
1679 ,x_return_status => x_return_status
1680 ,x_msg_count => x_msg_count
1681 ,x_msg_data => x_msg_data);
1682
1683 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1684 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1685 ELSIF x_return_status = G_RET_STS_ERROR THEN
1686 RAISE OKL_API.G_EXCEPTION_ERROR;
1687 END IF;
1688
1689 END LOOP; */
1690 END IF;
1691
1692 /*handle_parent_object_status(
1693 p_api_version => G_API_VERSION
1694 ,p_init_msg_list => G_FALSE
1695 ,x_return_status => x_return_status
1696 ,x_msg_count => x_msg_count
1697 ,x_msg_data => x_msg_data
1698 ,p_parent_object_code => l_lease_qte_rec.parent_object_code
1699 ,p_parent_object_id => l_lease_qte_rec.parent_object_id
1700 );
1701
1702 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1703 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1704 ELSIF x_return_status = G_RET_STS_ERROR THEN
1705 RAISE OKL_API.G_EXCEPTION_ERROR;
1706 END IF;*/
1707
1708 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
1709 ,x_msg_data => x_msg_data);
1710 EXCEPTION
1711 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1712 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1713 p_api_name => l_api_name,
1714 p_pkg_name => G_PKG_NAME,
1715 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1716 x_msg_count => x_msg_count,
1717 x_msg_data => x_msg_data,
1718 p_api_type => g_api_type);
1719 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1720 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1721 p_api_name => l_api_name,
1722 p_pkg_name => G_PKG_NAME,
1723 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1724 x_msg_count => x_msg_count,
1725 x_msg_data => x_msg_data,
1726 p_api_type => g_api_type);
1727 WHEN OTHERS THEN
1728 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1729 p_api_name => l_api_name,
1730 p_pkg_name => G_PKG_NAME,
1731 p_exc_name => 'OTHERS',
1732 x_msg_count => x_msg_count,
1733 x_msg_data => x_msg_data,
1734 p_api_type => g_api_type);
1735
1736 END create_update_payment;
1737
1738 -------------------------------------------------------------------------------
1739 -- PROCEDURE create_update_line_payment
1740 -------------------------------------------------------------------------------
1741 -- Start of comments
1742 --
1743 -- Procedure Name : create_update_line_payment
1744 -- Description : Create/Update Lease Quote Aseet/Fee Pricing options and payments
1745 --
1746 -- Business Rules :
1747 --
1748 --
1749 -- Parameters :
1750 -- Version : 1.0
1751 -- History : 15-SEP-2005 SKGAUTAM created
1752 -- End of comments
1753 PROCEDURE create_update_line_payment (
1754 p_api_version IN NUMBER
1755 ,p_init_msg_list IN VARCHAR2
1756 ,p_fee_rec IN fee_rec_type
1757 ,p_asset_rec IN asset_rec_type
1758 ,p_payment_header_rec IN cashflow_hdr_rec_type
1759 ,p_payment_level_tbl IN cashflow_level_tbl_type
1760 ,x_return_status OUT NOCOPY VARCHAR2
1761 ,x_msg_count OUT NOCOPY NUMBER
1762 ,x_msg_data OUT NOCOPY VARCHAR2
1763 ) IS
1764
1765 -- Variables Declarations
1766 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1767 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE3';
1768 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1769 l_debug_enabled VARCHAR2(10);
1770
1771
1772 -- Record/Table Type Declarations
1773 l_payment_header_rec cashflow_hdr_rec_type;
1774 l_payment_level_tbl cashflow_level_tbl_type;
1775 l_lease_qte_rec lease_qte_rec_type;
1776 lx_lease_qte_rec lease_qte_rec_type;
1777 l_fee_rec fee_rec_type;
1778 lx_fee_rec fee_rec_type;
1779 l_asset_rec asset_rec_type;
1780 lx_asset_rec asset_rec_type;
1781 l_sp VARCHAR2(10);
1782 l_missing varchar2(3):= 'N';
1783 l_pricing_method VARCHAR2(30);
1784 l_srt NUMBER;
1785 lv_cash_flow_exists VARCHAR2(3);
1786 l_p_id NUMBER;
1787 l_p_code VARCHAR2(30);
1788
1789 CURSOR c_check_asset_cash_flow(p_asset_id OKL_ASSETS_B.ID%TYPE)
1790 IS
1791 SELECT 'YES'
1792 FROM OKL_CASH_FLOW_OBJECTS
1793 WHERE OTY_CODE = 'QUOTED_ASSET'
1794 AND SOURCE_TABLE = 'OKL_ASSETS_B'
1795 AND SOURCE_ID = p_asset_id;
1796
1797 CURSOR c_check_fee_cash_flow(p_fee_id OKL_FEES_B.ID%TYPE)
1798 IS
1799 SELECT 'YES'
1800 FROM OKL_CASH_FLOW_OBJECTS
1801 WHERE OTY_CODE = 'QUOTED_FEE'
1802 AND SOURCE_TABLE = 'OKL_FEES_B'
1803 AND SOURCE_ID = p_fee_id;
1804
1805 BEGIN
1806 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1807 -- check for logging on PROCEDURE level
1808 l_debug_enabled := okl_debug_pub.check_log_enabled;
1809 -- call START_ACTIVITY to create savepoint, check compatibility
1810 -- and initialize message list
1811 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
1812 ,p_pkg_name => G_PKG_NAME
1813 ,p_init_msg_list => p_init_msg_list
1814 ,l_api_version => l_api_version
1815 ,p_api_version => p_api_version
1816 ,p_api_type => g_api_type
1817 ,x_return_status => x_return_status);
1818 -- check if activity started successfully
1819 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1820 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1821 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1822 RAISE OKL_API.G_EXCEPTION_ERROR;
1823 END IF;
1824 IF p_payment_header_rec.parent_object_code = 'QUOTED_FEE' THEN
1825 --Populate fee record
1826 l_fee_rec := populate_fee_rec(p_fee_rec.id,x_return_status);
1827
1828 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1829 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1830 ELSIF x_return_status = G_RET_STS_ERROR THEN
1831 RAISE OKL_API.G_EXCEPTION_ERROR;
1832 END IF;
1833
1834 l_fee_rec.RATE_CARD_ID := p_fee_rec.RATE_CARD_ID;
1835 l_fee_rec.RATE_TEMPLATE_ID := p_fee_rec.RATE_TEMPLATE_ID;
1836 l_fee_rec.STRUCTURED_PRICING := p_fee_rec.STRUCTURED_PRICING;
1837 l_fee_rec.TARGET_ARREARS := p_fee_rec.TARGET_ARREARS;
1838 l_fee_rec.LEASE_RATE_FACTOR := p_fee_rec.LEASE_RATE_FACTOR;
1839 l_fee_rec.TARGET_AMOUNT := p_fee_rec.TARGET_AMOUNT;
1840 l_fee_rec.TARGET_FREQUENCY := p_fee_rec.TARGET_FREQUENCY;
1841 l_fee_rec.PAYMENT_TYPE_ID := p_fee_rec.PAYMENT_TYPE_ID;
1842
1843 l_payment_header_rec := p_payment_header_rec;
1844 l_payment_level_tbl := p_payment_level_tbl;
1845
1846 l_payment_header_rec.parent_object_id := l_fee_rec.id;
1847 l_payment_header_rec.quote_id := l_fee_rec.parent_object_id;
1848 l_payment_header_rec.type_code:= 'INFLOW';
1849
1850 IF(l_debug_enabled='Y') THEN
1851 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_FEE_PVT.update_row'
1852 ,'begin debug call update_row');
1853 END IF;
1854 OKL_FEE_PVT.update_row(
1855 p_api_version => G_API_VERSION
1856 ,p_init_msg_list => G_FALSE
1857 ,x_return_status => x_return_status
1858 ,x_msg_count => x_msg_count
1859 ,x_msg_data => x_msg_data
1860 ,p_feev_rec => l_fee_rec
1861 ,x_feev_rec => lx_fee_rec
1862 );
1863 IF(l_debug_enabled='Y') THEN
1864 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_FEE_PVT.update_row'
1865 ,'end debug call update_row');
1866 END IF;
1867
1868 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1869 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1870 ELSIF x_return_status = G_RET_STS_ERROR THEN
1871 RAISE OKL_API.G_EXCEPTION_ERROR;
1872 END IF;
1873
1874 -- Check if the Cash flows already exists
1875 OPEN c_check_fee_cash_flow(p_fee_id => lx_fee_rec.id);
1876 FETCH c_check_fee_cash_flow into lv_cash_flow_exists;
1877 CLOSE c_check_fee_cash_flow;
1878 l_sp := l_fee_rec.structured_pricing;
1879 l_srt := l_fee_rec.rate_template_id;
1880 SELECT pricing_method
1881 INTO l_pricing_method
1882 FROM okl_lease_quotes_b
1883 where id = l_fee_Rec.parent_object_id;
1884 ELSIF p_payment_header_rec.parent_object_code = 'QUOTED_ASSET' THEN
1885 l_asset_rec := populate_asset_rec(p_asset_rec.id,x_return_status);
1886
1887 l_asset_rec.RATE_CARD_ID := p_asset_rec.RATE_CARD_ID;
1888 l_asset_rec.RATE_TEMPLATE_ID := p_asset_rec.RATE_TEMPLATE_ID;
1889 l_asset_rec.STRUCTURED_PRICING := p_asset_rec.STRUCTURED_PRICING;
1890 l_asset_rec.TARGET_ARREARS := p_asset_rec.TARGET_ARREARS;
1891 l_asset_rec.LEASE_RATE_FACTOR := p_asset_rec.LEASE_RATE_FACTOR;
1892 l_asset_rec.TARGET_AMOUNT := p_asset_rec.TARGET_AMOUNT;
1893 l_asset_rec.TARGET_FREQUENCY := p_asset_rec.TARGET_FREQUENCY;
1894
1895 l_payment_header_rec := p_payment_header_rec;
1896 l_payment_level_tbl := p_payment_level_tbl;
1897
1898 l_payment_header_rec.parent_object_id := l_asset_rec.id;
1899 l_payment_header_rec.quote_id := l_asset_rec.parent_object_id;
1900 l_payment_header_rec.type_code:= 'INFLOW';
1901 IF(l_debug_enabled='Y') THEN
1902 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_ASS_PVT.update_row'
1903 ,'begin debug call update_row');
1904 END IF;
1905 OKL_ASS_PVT.update_row (p_api_version => G_API_VERSION
1906 ,p_init_msg_list => G_FALSE
1907 ,x_return_status => x_return_status
1908 ,x_msg_count => x_msg_count
1909 ,x_msg_data => x_msg_data
1910 ,p_assv_rec => l_asset_rec
1911 ,x_assv_rec => lx_asset_rec );
1912 IF(l_debug_enabled='Y') THEN
1913 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_ASS_PVT.update_row'
1914 ,'end debug call update_row');
1915 END IF;
1916 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1917 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1918 ELSIF x_return_status = G_RET_STS_ERROR THEN
1919 RAISE OKL_API.G_EXCEPTION_ERROR;
1920 END IF;
1921
1922 -- Check if the Cash flows already exists
1923 OPEN c_check_asset_cash_flow(p_asset_id => lx_asset_rec.id);
1924 FETCH c_check_asset_cash_flow into lv_cash_flow_exists;
1925 CLOSE c_check_asset_cash_flow;
1926 l_sp := l_asset_rec.structured_pricing;
1927 l_srt := l_asset_rec.rate_template_id;
1928 SELECT pricing_method
1929 INTO l_pricing_method
1930 FROM okl_lease_quotes_b
1931 where id = l_asset_rec.parent_object_id;
1932 END IF;
1933 IF l_pricing_method = 'SM' THEN
1934 FOR k IN l_payment_level_tbl.FIRST..l_payment_level_tbl.LAST LOOP
1935 IF l_payment_level_tbl.exists(k) THEN
1936 IF (l_payment_level_tbl(k).stub_days IS NOT NULL AND l_payment_level_tbl(k).stub_amount IS NULL )
1937 OR (l_payment_level_tbl(k).periods IS NOT NULL AND l_payment_level_tbl(k).periodic_amount IS NULL )
1938 THEN
1939 l_missing := 'Y';
1940 EXIT;
1941 END IF;
1942 END IF;
1943 END LOOP;
1944 IF l_missing = 'N' THEN
1945 IF l_sp = 'N' THEN
1946 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1947 p_msg_name => 'OKL_NO_MP_STR');
1948 RAISE OKL_API.G_EXCEPTION_ERROR;
1949 END IF;
1950 END IF;
1951 IF l_missing = 'Y' THEN
1952 IF l_sp = 'N' AND l_srt IS NULL THEN
1953 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1954 p_msg_name => 'OKL_SRT_MANDATORY');
1955 RAISE OKL_API.G_EXCEPTION_ERROR;
1956 END IF;
1957 END IF;
1958 END IF;
1959 IF ((l_payment_level_tbl.COUNT > 0 AND l_payment_header_rec.stream_type_id IS NULL) OR
1960 (l_payment_header_rec.stream_type_id IS NOT NULL AND l_payment_level_tbl.COUNT = 0 )) THEN
1961 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1962 p_msg_name => 'OKL_EPT_PAYMENT_NA');
1963 RAISE OKL_API.G_EXCEPTION_ERROR;
1964 ELSE
1965 IF (lv_cash_flow_exists = 'YES') THEN
1966
1967 IF(l_debug_enabled='Y') THEN
1968 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow'
1969 ,'begin debug call update_cashflow');
1970 END IF;
1971 OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow (
1972 p_api_version => G_API_VERSION
1973 ,p_init_msg_list => G_FALSE
1974 ,p_transaction_control => G_FALSE
1975 ,p_cashflow_header_rec => l_payment_header_rec
1976 ,p_cashflow_level_tbl => l_payment_level_tbl
1977 ,x_return_status => x_return_status
1978 ,x_msg_count => x_msg_count
1979 ,x_msg_data => x_msg_data);
1980 IF(l_debug_enabled='Y') THEN
1981 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow'
1982 ,'end debug call update_cashflow');
1983 END IF;
1984
1985 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1986 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1987 ELSIF x_return_status = G_RET_STS_ERROR THEN
1988 RAISE OKL_API.G_EXCEPTION_ERROR;
1989 END IF;
1990 ELSE
1991
1992 IF(l_debug_enabled='Y') THEN
1993 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow'
1994 ,'begin debug call create_cashflow');
1995 END IF;
1996 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow (
1997 p_api_version => G_API_VERSION
1998 ,p_init_msg_list => G_FALSE
1999 ,p_transaction_control => G_FALSE
2000 ,p_cashflow_header_rec => l_payment_header_rec
2001 ,p_cashflow_level_tbl => l_payment_level_tbl
2002 ,x_return_status => x_return_status
2003 ,x_msg_count => x_msg_count
2004 ,x_msg_data => x_msg_data);
2005 IF(l_debug_enabled='Y') THEN
2006 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow'
2007 ,'end debug call create_cashflow');
2008 END IF;
2009
2010 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2011 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2012 ELSIF x_return_status = G_RET_STS_ERROR THEN
2013 RAISE OKL_API.G_EXCEPTION_ERROR;
2014 END IF;
2015 END IF;
2016 END IF;
2017
2018 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2019 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2020 ELSIF x_return_status = G_RET_STS_ERROR THEN
2021 RAISE OKL_API.G_EXCEPTION_ERROR;
2022 END IF;
2023
2024 /*SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
2025 FROM okl_lease_quotes_b where ID = nvl(l_fee_rec.parent_object_id,l_Asset_rec.parent_object_id);
2026
2027 handle_parent_object_status(
2028 p_api_version => G_API_VERSION
2029 ,p_init_msg_list => G_FALSE
2030 ,x_return_status => x_return_status
2031 ,x_msg_count => x_msg_count
2032 ,x_msg_data => x_msg_data
2033 ,p_parent_object_code => l_p_code
2034 ,p_parent_object_id => l_p_id
2035 );
2036
2037 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2038 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2039 ELSIF x_return_status = G_RET_STS_ERROR THEN
2040 RAISE OKL_API.G_EXCEPTION_ERROR;
2041 END IF;*/
2042
2043 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
2044 ,x_msg_data => x_msg_data);
2045 EXCEPTION
2046 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2047 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2048 p_api_name => l_api_name,
2049 p_pkg_name => G_PKG_NAME,
2050 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2051 x_msg_count => x_msg_count,
2052 x_msg_data => x_msg_data,
2053 p_api_type => g_api_type);
2054 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2055 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2056 p_api_name => l_api_name,
2057 p_pkg_name => G_PKG_NAME,
2058 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2059 x_msg_count => x_msg_count,
2060 x_msg_data => x_msg_data,
2061 p_api_type => g_api_type);
2062 WHEN OTHERS THEN
2063 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2064 p_api_name => l_api_name,
2065 p_pkg_name => G_PKG_NAME,
2066 p_exc_name => 'OTHERS',
2067 x_msg_count => x_msg_count,
2068 x_msg_data => x_msg_data,
2069 p_api_type => g_api_type);
2070
2071 END create_update_line_payment;
2072 -------------------------------------------------------------------------------
2073 -- PROCEDURE create_update_line_payment
2074 -------------------------------------------------------------------------------
2075 -- Start of comments
2076 --
2077 -- Procedure Name : create_update_line_payment
2078 -- Description : Create/Update Lease Quote Fee Pricing options
2079 --
2080 -- Business Rules :
2081 --
2082 --
2083 -- Parameters :
2084 -- Version : 1.0
2085 -- History : 15-SEP-2005 SKGAUTAM created
2086 -- End of comments
2087 PROCEDURE create_update_line_payment (
2088 p_api_version IN NUMBER
2089 ,p_init_msg_list IN VARCHAR2
2090 ,p_fee_rec IN fee_rec_type
2091 ,x_return_status OUT NOCOPY VARCHAR2
2092 ,x_msg_count OUT NOCOPY NUMBER
2093 ,x_msg_data OUT NOCOPY VARCHAR2
2094 ) IS
2095
2096 -- Variables Declarations
2097 l_api_version CONSTANT NUMBER DEFAULT 1.0;
2098 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREAT2';
2099 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2100 l_debug_enabled VARCHAR2(10);
2101
2102
2103 -- Record/Table Type Declarations
2104
2105 l_payment_header_rec cashflow_hdr_rec_type;
2106 l_payment_level_tbl cashflow_level_tbl_type;
2107 l_lease_qte_rec lease_qte_rec_type;
2108 lx_lease_qte_rec lease_qte_rec_type;
2109 l_fee_rec fee_rec_type;
2110 lx_fee_rec fee_rec_type;
2111 l_asset_rec asset_rec_type;
2112 lx_asset_rec asset_rec_type;
2113 l_p_id NUMBER;
2114 l_p_code VARCHAR2(30);
2115
2116
2117 BEGIN
2118 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2119 -- check for logging on PROCEDURE level
2120 l_debug_enabled := okl_debug_pub.check_log_enabled;
2121 -- call START_ACTIVITY to create savepoint, check compatibility
2122 -- and initialize message list
2123 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
2124 ,p_pkg_name => G_PKG_NAME
2125 ,p_init_msg_list => p_init_msg_list
2126 ,l_api_version => l_api_version
2127 ,p_api_version => p_api_version
2128 ,p_api_type => g_api_type
2129 ,x_return_status => x_return_status);
2130 -- check if activity started successfully
2131 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2132 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2133 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2134 RAISE OKL_API.G_EXCEPTION_ERROR;
2135 END IF;
2136 --populate fee record
2137 l_fee_rec := populate_fee_rec(p_fee_rec.id,x_return_status);
2138
2139 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2140 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2141 ELSIF x_return_status = G_RET_STS_ERROR THEN
2142 RAISE OKL_API.G_EXCEPTION_ERROR;
2143 END IF;
2144
2145 l_fee_rec.RATE_CARD_ID := p_fee_rec.RATE_CARD_ID;
2146 l_fee_rec.RATE_TEMPLATE_ID := p_fee_rec.RATE_TEMPLATE_ID;
2147 l_fee_rec.STRUCTURED_PRICING := p_fee_rec.STRUCTURED_PRICING;
2148 l_fee_rec.TARGET_ARREARS := p_fee_rec.TARGET_ARREARS;
2149 l_fee_rec.LEASE_RATE_FACTOR := p_fee_rec.LEASE_RATE_FACTOR;
2150 l_fee_rec.TARGET_AMOUNT := p_fee_rec.TARGET_AMOUNT;
2151 l_fee_rec.TARGET_FREQUENCY := p_fee_rec.TARGET_FREQUENCY;
2152 l_fee_rec.PAYMENT_TYPE_ID := p_fee_rec.PAYMENT_TYPE_ID;
2153
2154 IF(l_debug_enabled='Y') THEN
2155 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_FEE_PVT.update_row'
2156 ,'begin debug call update_row');
2157 END IF;
2158 OKL_FEE_PVT.update_row(
2159 p_api_version => G_API_VERSION
2160 ,p_init_msg_list => G_FALSE
2161 ,x_return_status => x_return_status
2162 ,x_msg_count => x_msg_count
2163 ,x_msg_data => x_msg_data
2164 ,p_feev_rec => l_fee_rec
2165 ,x_feev_rec => lx_fee_rec
2166 );
2167 IF(l_debug_enabled='Y') THEN
2168 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_FEE_PVT.update_row'
2169 ,'end debug call update_row');
2170 END IF;
2171
2172 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2173 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2174 ELSIF x_return_status = G_RET_STS_ERROR THEN
2175 RAISE OKL_API.G_EXCEPTION_ERROR;
2176 END IF;
2177
2178 IF(l_debug_enabled='Y') THEN
2179 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.okl_lease_quote_cashflow_pvt.delete_cash_flows'
2180 ,'begin debug call okl_lease_quote_cashflow_pvt.delete_cash_flows');
2181 END IF;
2182 okl_lease_quote_cashflow_pvt.delete_cashflows(
2183 p_api_version => G_API_VERSION
2184 ,p_init_msg_list => G_FALSE
2185 ,p_transaction_control => G_FALSE
2186 ,p_source_object_code => 'QUOTED_FEE'
2187 ,p_source_object_id => p_fee_rec.id
2188 ,x_return_status => x_return_status
2189 ,x_msg_count => x_msg_count
2190 ,x_msg_data => x_msg_data);
2191 IF(l_debug_enabled='Y') THEN
2192 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.okl_lease_quote_cashflow_pvt.delete_cash_flows'
2193 ,'End debug call okl_lease_quote_cashflow_pvt.delete_cash_flows');
2194 END IF;
2195
2196 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2197 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2198 ELSIF x_return_status = G_RET_STS_ERROR THEN
2199 RAISE OKL_API.G_EXCEPTION_ERROR;
2200 END IF;
2201
2202 /*SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
2203 FROM okl_lease_quotes_b where ID = l_fee_rec.parent_object_id;
2204
2205 handle_parent_object_status(
2206 p_api_version => G_API_VERSION
2207 ,p_init_msg_list => G_FALSE
2208 ,x_return_status => x_return_status
2209 ,x_msg_count => x_msg_count
2210 ,x_msg_data => x_msg_data
2211 ,p_parent_object_code => l_p_code
2212 ,p_parent_object_id => l_p_id
2213 );
2214
2215 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2216 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2217 ELSIF x_return_status = G_RET_STS_ERROR THEN
2218 RAISE OKL_API.G_EXCEPTION_ERROR;
2219 END IF;*/
2220
2221
2222
2223 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
2224 ,x_msg_data => x_msg_data);
2225 EXCEPTION
2226 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2227 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2228 p_api_name => l_api_name,
2229 p_pkg_name => G_PKG_NAME,
2230 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2231 x_msg_count => x_msg_count,
2232 x_msg_data => x_msg_data,
2233 p_api_type => g_api_type);
2234 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2235 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2236 p_api_name => l_api_name,
2237 p_pkg_name => G_PKG_NAME,
2238 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2239 x_msg_count => x_msg_count,
2240 x_msg_data => x_msg_data,
2241 p_api_type => g_api_type);
2242 WHEN OTHERS THEN
2243 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2244 p_api_name => l_api_name,
2245 p_pkg_name => G_PKG_NAME,
2246 p_exc_name => 'OTHERS',
2247 x_msg_count => x_msg_count,
2248 x_msg_data => x_msg_data,
2249 p_api_type => g_api_type);
2250 END create_update_line_payment;
2251
2252 -------------------------------------------------------------------------------
2253 -- PROCEDURE create_update_line_payment
2254 -------------------------------------------------------------------------------
2255 -- Start of comments
2256 --
2257 -- Procedure Name : create_update_line_payment
2258 -- Description : Create/Update Lease Quote Asset Pricing options
2259 --
2260 -- Business Rules :
2261 --
2262 --
2263 -- Parameters :
2264 -- Version : 1.0
2265 -- History : 15-SEP-2005 SKGAUTAM created
2266 -- End of comments
2267 PROCEDURE create_update_line_payment (
2268 p_api_version IN NUMBER
2269 ,p_init_msg_list IN VARCHAR2
2270 ,p_asset_rec IN asset_rec_type
2271 ,x_return_status OUT NOCOPY VARCHAR2
2272 ,x_msg_count OUT NOCOPY NUMBER
2273 ,x_msg_data OUT NOCOPY VARCHAR2
2274 ) IS
2275
2276 -- Variables Declarations
2277 l_api_version CONSTANT NUMBER DEFAULT 1.0;
2278 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE1';
2279 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2280 l_debug_enabled VARCHAR2(10);
2281
2282
2283 -- Record/Table Type Declarations
2284 l_payment_header_rec cashflow_hdr_rec_type;
2285 l_payment_level_tbl cashflow_level_tbl_type;
2286 l_lease_qte_rec lease_qte_rec_type;
2287 lx_lease_qte_rec lease_qte_rec_type;
2288 l_fee_rec fee_rec_type;
2289 lx_fee_rec fee_rec_type;
2290 l_asset_rec asset_rec_type;
2291 lx_asset_rec asset_rec_type;
2292 l_p_id NUMBER;
2293 l_p_code VARCHAR2(30);
2294
2295
2296 BEGIN
2297 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2298 -- check for logging on PROCEDURE level
2299 l_debug_enabled := okl_debug_pub.check_log_enabled;
2300 -- call START_ACTIVITY to create savepoint, check compatibility
2301 -- and initialize message list
2302 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
2303 ,p_pkg_name => G_PKG_NAME
2304 ,p_init_msg_list => p_init_msg_list
2305 ,l_api_version => l_api_version
2306 ,p_api_version => p_api_version
2307 ,p_api_type => g_api_type
2308 ,x_return_status => x_return_status);
2309 -- check if activity started successfully
2310 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2311 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2312 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2313 RAISE OKL_API.G_EXCEPTION_ERROR;
2314 END IF;
2315 l_asset_rec := populate_asset_rec(p_asset_rec.id,x_return_status);
2316
2317 l_asset_rec.RATE_CARD_ID := p_asset_rec.RATE_CARD_ID;
2318 l_asset_rec.RATE_TEMPLATE_ID := p_asset_rec.RATE_TEMPLATE_ID;
2319 l_asset_rec.STRUCTURED_PRICING := p_asset_rec.STRUCTURED_PRICING;
2320 l_asset_rec.TARGET_ARREARS := p_asset_rec.TARGET_ARREARS;
2321 l_asset_rec.LEASE_RATE_FACTOR := p_asset_rec.LEASE_RATE_FACTOR;
2322 l_asset_rec.TARGET_AMOUNT := p_asset_rec.TARGET_AMOUNT;
2323 l_asset_rec.TARGET_FREQUENCY := p_asset_rec.TARGET_FREQUENCY;
2324
2325 IF(l_debug_enabled='Y') THEN
2326 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_ASS_PVT.update_row'
2327 ,'begin debug call update_row');
2328 END IF;
2329 OKL_ASS_PVT.update_row (p_api_version => G_API_VERSION
2330 ,p_init_msg_list => G_FALSE
2331 ,x_return_status => x_return_status
2332 ,x_msg_count => x_msg_count
2333 ,x_msg_data => x_msg_data
2334 ,p_assv_rec => l_asset_rec
2335 ,x_assv_rec => lx_asset_rec );
2336 IF(l_debug_enabled='Y') THEN
2337 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_ASS_PVT.update_row'
2338 ,'begin debug call update_row');
2339 END IF;
2340
2341 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2342 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2343 ELSIF x_return_status = G_RET_STS_ERROR THEN
2344 RAISE OKL_API.G_EXCEPTION_ERROR;
2345 END IF;
2346
2347 IF(l_debug_enabled='Y') THEN
2348 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.okl_lease_quote_cashflow_pvt.delete_cash_flows'
2349 ,'begin debug call okl_lease_quote_cashflow_pvt.delete_cash_flows');
2350 END IF;
2351 okl_lease_quote_cashflow_pvt.delete_cashflows(
2352 p_api_version => G_API_VERSION
2353 ,p_init_msg_list => G_FALSE
2354 ,p_transaction_control => G_FALSE
2355 ,p_source_object_code => 'QUOTED_ASSET'
2356 ,p_source_object_id => p_asset_rec.id
2357 ,x_return_status => x_return_status
2358 ,x_msg_count => x_msg_count
2359 ,x_msg_data => x_msg_data);
2360 IF(l_debug_enabled='Y') THEN
2361 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.okl_lease_quote_cashflow_pvt.delete_cash_flows'
2362 ,'End debug call okl_lease_quote_cashflow_pvt.delete_cash_flows');
2363 END IF;
2364
2365 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2366 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2367 ELSIF x_return_status = G_RET_STS_ERROR THEN
2368 RAISE OKL_API.G_EXCEPTION_ERROR;
2369 END IF;
2370
2371 /*SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
2372 FROM okl_lease_quotes_b where ID = l_asset_rec.parent_object_id;
2373
2374 handle_parent_object_status(
2375 p_api_version => G_API_VERSION
2376 ,p_init_msg_list => G_FALSE
2377 ,x_return_status => x_return_status
2378 ,x_msg_count => x_msg_count
2379 ,x_msg_data => x_msg_data
2380 ,p_parent_object_code => l_p_code
2381 ,p_parent_object_id => l_p_id
2382 );
2383
2384 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2386 ELSIF x_return_status = G_RET_STS_ERROR THEN
2387 RAISE OKL_API.G_EXCEPTION_ERROR;
2388 END IF;*/
2389
2390
2391
2392 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
2393 ,x_msg_data => x_msg_data);
2394 EXCEPTION
2395 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2396 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2397 p_api_name => l_api_name,
2398 p_pkg_name => G_PKG_NAME,
2399 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2400 x_msg_count => x_msg_count,
2401 x_msg_data => x_msg_data,
2402 p_api_type => g_api_type);
2403 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2404 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2405 p_api_name => l_api_name,
2406 p_pkg_name => G_PKG_NAME,
2407 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2408 x_msg_count => x_msg_count,
2409 x_msg_data => x_msg_data,
2410 p_api_type => g_api_type);
2411 WHEN OTHERS THEN
2412 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2413 p_api_name => l_api_name,
2414 p_pkg_name => G_PKG_NAME,
2415 p_exc_name => 'OTHERS',
2416 x_msg_count => x_msg_count,
2417 x_msg_data => x_msg_data,
2418 p_api_type => g_api_type);
2419 END create_update_line_payment;
2420
2421 -------------------------------------------------------------------------------
2422 -- PROCEDURE delete_line_payment
2423 -------------------------------------------------------------------------------
2424 -- Start of comments
2425 --
2426 -- Procedure Name : delete_line_payment
2427 -- Description : Delete Lease Quote Aseet/Fee Pricing options and payments
2428 --
2429 -- Business Rules :
2430 --
2431 --
2432 -- Parameters :
2433 -- Version : 1.0
2434 -- History : 15-SEP-2005 SKGAUTAM created
2435 -- End of comments
2436 PROCEDURE delete_line_payment(
2437 p_api_version IN NUMBER
2438 ,p_init_msg_list IN VARCHAR2
2439 ,p_source_object_code IN VARCHAR2
2440 ,p_source_object_id IN NUMBER
2441 ,x_return_status OUT NOCOPY VARCHAR2
2442 ,x_msg_count OUT NOCOPY NUMBER
2443 ,x_msg_data OUT NOCOPY VARCHAR2
2444 ) IS
2445
2446 -- Variables Declarations
2447 l_api_version CONSTANT NUMBER DEFAULT 1.0;
2448 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'DELETE_LINE_PAYMENT';
2449 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2450 l_debug_enabled VARCHAR2(10);
2451
2452
2453 -- Record/Table Type Declarations
2454 l_source_object_code okl_cash_flow_objects.OTY_CODE%TYPE;
2455 l_source_object_id okl_cash_flow_objects.SOURCE_ID%TYPE;
2456 l_lease_qte_rec lease_qte_rec_type;
2457 lx_lease_qte_rec lease_qte_rec_type;
2458 l_fee_rec fee_rec_type;
2459 lx_fee_rec fee_rec_type;
2460 l_asset_rec asset_rec_type;
2461 lx_asset_rec asset_rec_type;
2462
2463 lv_cash_flow_exists VARCHAR2(3);
2464
2465 CURSOR c_check_cash_flow(p_source_id NUMBER,p_source_code VARCHAR2)
2466 IS
2467 SELECT 'YES'
2468 FROM OKL_CASH_FLOW_OBJECTS
2469 WHERE OTY_CODE = p_source_code
2470 AND SOURCE_ID = p_source_id;
2471
2472
2473 BEGIN
2474
2475 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2476 -- check for logging on PROCEDURE level
2477 l_debug_enabled := okl_debug_pub.check_log_enabled;
2478 -- call START_ACTIVITY to create savepoint, check compatibility
2479 -- and initialize message list
2480 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
2481 ,p_pkg_name => G_PKG_NAME
2482 ,p_init_msg_list => p_init_msg_list
2483 ,l_api_version => l_api_version
2484 ,p_api_version => p_api_version
2485 ,p_api_type => g_api_type
2486 ,x_return_status => x_return_status);
2487 -- check if activity started successfully
2488 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2489 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2490 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2491 RAISE OKL_API.G_EXCEPTION_ERROR;
2492 END IF;
2493
2494 l_source_object_id := p_source_object_id;
2495 l_source_object_code := p_source_object_code;
2496
2497 IF l_source_object_code = 'QUOTED_FEE' THEN
2498 --Populate fee record
2499 l_fee_rec := populate_fee_rec(l_source_object_id,x_return_status);
2500
2501 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2502 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2503 ELSIF x_return_status = G_RET_STS_ERROR THEN
2504 RAISE OKL_API.G_EXCEPTION_ERROR;
2505 END IF;
2506
2507 l_fee_rec.RATE_CARD_ID := NULL;
2508 l_fee_rec.RATE_TEMPLATE_ID := NULL;
2509 l_fee_rec.STRUCTURED_PRICING := NULL;
2510 l_fee_rec.TARGET_ARREARS := NULL;
2511 l_fee_rec.LEASE_RATE_FACTOR := NULL;
2512 l_fee_rec.TARGET_AMOUNT := NULL;
2513
2514 IF(l_debug_enabled='Y') THEN
2515 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_FEE_PVT.update_row'
2516 ,'begin debug call update_row');
2517 END IF;
2518 OKL_FEE_PVT.update_row(
2519 p_api_version => G_API_VERSION
2520 ,p_init_msg_list => G_FALSE
2521 ,x_return_status => x_return_status
2522 ,x_msg_count => x_msg_count
2523 ,x_msg_data => x_msg_data
2524 ,p_feev_rec => l_fee_rec
2525 ,x_feev_rec => lx_fee_rec
2526 );
2527 IF(l_debug_enabled='Y') THEN
2528 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_FEE_PVT.update_row'
2529 ,'end debug call update_row');
2530 END IF;
2531
2532 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2533 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2534 ELSIF x_return_status = G_RET_STS_ERROR THEN
2535 RAISE OKL_API.G_EXCEPTION_ERROR;
2536 END IF;
2537
2538 ELSIF l_source_object_code = 'QUOTED_ASSET' THEN
2539 l_asset_rec := populate_asset_rec(l_source_object_id,x_return_status);
2540
2541 l_asset_rec.RATE_CARD_ID := NULL;
2542 l_asset_rec.RATE_TEMPLATE_ID := NULL;
2543 l_asset_rec.STRUCTURED_PRICING := NULL;
2544 l_asset_rec.TARGET_ARREARS := NULL;
2545 l_asset_rec.LEASE_RATE_FACTOR := NULL;
2546 l_asset_rec.TARGET_AMOUNT := NULL;
2547
2548 IF(l_debug_enabled='Y') THEN
2549 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_ASS_PVT.update_row'
2550 ,'begin debug call update_row');
2551 END IF;
2552 OKL_ASS_PVT.update_row (p_api_version => G_API_VERSION
2553 ,p_init_msg_list => G_FALSE
2554 ,x_return_status => x_return_status
2555 ,x_msg_count => x_msg_count
2556 ,x_msg_data => x_msg_data
2557 ,p_assv_rec => l_asset_rec
2558 ,x_assv_rec => lx_asset_rec );
2559
2560 IF(l_debug_enabled='Y') THEN
2561 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_ASS_PVT.update_row'
2562 ,'end debug call update_row');
2563 END IF;
2564 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2565 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2566 ELSIF x_return_status = G_RET_STS_ERROR THEN
2567 RAISE OKL_API.G_EXCEPTION_ERROR;
2568 END IF;
2569 END IF;
2570 -- Check if the Cash flows already exists
2571 OPEN c_check_cash_flow(p_source_id => l_source_object_id,p_source_code => l_source_object_code);
2572 FETCH c_check_cash_flow into lv_cash_flow_exists;
2573 CLOSE c_check_cash_flow;
2574
2575 IF lv_cash_flow_exists = 'YES' THEN
2576
2577 IF(l_debug_enabled='Y') THEN
2578 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows'
2579 ,'begin debug call delete_cashflows');
2580 END IF;
2581 OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows (
2582 p_api_version => G_API_VERSION
2583 ,p_init_msg_list => G_FALSE
2584 ,p_transaction_control => G_FALSE
2585 ,p_source_object_code => l_source_object_code
2586 ,p_source_object_id => l_source_object_id
2587 ,x_return_status => x_return_status
2588 ,x_msg_count => x_msg_count
2589 ,x_msg_data => x_msg_data);
2590
2591 IF(l_debug_enabled='Y') THEN
2592 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows'
2593 ,'end debug call delete_cashflows');
2594 END IF;
2595
2596 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2597 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2598 ELSIF x_return_status = G_RET_STS_ERROR THEN
2599 RAISE OKL_API.G_EXCEPTION_ERROR;
2600 END IF;
2601 END IF;
2602
2603 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2604 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2605 ELSIF x_return_status = G_RET_STS_ERROR THEN
2606 RAISE OKL_API.G_EXCEPTION_ERROR;
2607 END IF;
2608
2609 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
2610 ,x_msg_data => x_msg_data);
2611 EXCEPTION
2612 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2613 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2614 p_api_name => l_api_name,
2615 p_pkg_name => G_PKG_NAME,
2616 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2617 x_msg_count => x_msg_count,
2618 x_msg_data => x_msg_data,
2619 p_api_type => g_api_type);
2620 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2621 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2622 p_api_name => l_api_name,
2623 p_pkg_name => G_PKG_NAME,
2624 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2625 x_msg_count => x_msg_count,
2626 x_msg_data => x_msg_data,
2627 p_api_type => g_api_type);
2628 WHEN OTHERS THEN
2629 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2630 p_api_name => l_api_name,
2631 p_pkg_name => G_PKG_NAME,
2632 p_exc_name => 'OTHERS',
2633 x_msg_count => x_msg_count,
2634 x_msg_data => x_msg_data,
2635 p_api_type => g_api_type);
2636
2637 END delete_line_payment;
2638
2639 -------------------------------------------------------------------------------
2640 -- FUNCTION get_periods
2641 -------------------------------------------------------------------------------
2642 -- Start of comments
2643 --
2644 -- Function Name : get_periods
2645 -- Description : returns the periods for line pricing options table
2646 --
2647 -- Business Rules :
2648 --
2649 --
2650 -- Parameters :
2651 -- Version : 1.0
2652 -- History : 15-SEP-2005 SKGAUTAM created
2653 -- End of comments
2654 FUNCTION get_periods(p_casflow_id IN NUMBER)
2655 RETURN VARCHAR2 IS
2656 -- Variables Declarations
2657 l_api_version CONSTANT NUMBER DEFAULT 1.0;
2658 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'GET PERIODS';
2659
2660 CURSOR check_stub(l_cashflow_id NUMBER) IS
2661 SELECT STUB_DAYS,
2662 STUB_AMOUNT
2663 FROM OKL_CASH_FLOW_LEVELS
2664 WHERE CAF_ID = l_cashflow_id;
2665
2666 CURSOR get_periods(l_cashflow_id NUMBER) IS
2667 SELECT SUM(NUMBER_OF_PERIODS)
2668 FROM OKL_CASH_FLOW_LEVELS
2669 WHERE CAF_ID = l_cashflow_id;
2670
2671 l_stub_days NUMBER;
2672 l_stub_amount NUMBER;
2673 l_count NUMBER;
2674 l_periods VARCHAR2(10);
2675 l_stub_flg VARCHAR2(1):= 'N';
2676
2677
2678 BEGIN
2679
2680 OPEN get_periods(p_casflow_id);
2681 FETCH get_periods INTO l_periods;
2682 CLOSE get_periods;
2683 FOR l_stub_rec IN check_stub(p_casflow_id) LOOP
2684 IF (l_stub_rec.stub_days IS NOT NULL OR
2685 l_stub_rec.stub_amount IS NOT NULL) THEN
2686 l_stub_flg := 'Y';
2687 END IF;
2688 END LOOP;
2689
2690 IF l_stub_flg = 'Y' THEN
2691 RETURN OKL_ACCOUNTING_UTIL.get_lookup_meaning('OKL_QUOTE_PRICING_OPTIONS','STU');
2692 ELSE
2693 RETURN l_periods;
2694 END IF;
2695
2696 EXCEPTION
2697 -- other appropriate handlers
2698 WHEN OTHERS THEN
2699 -- store SQL error message on message stack
2700 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2701 p_msg_name => G_UNEXPECTED_ERROR,
2702 p_token1 => G_SQLCODE_TOKEN,
2703 p_token1_value => sqlcode,
2704 p_token2 => G_SQLERRM_TOKEN,
2705 p_token2_value => sqlerrm);
2706 RETURN NULL;
2707
2708
2709 END;
2710
2711 -------------------------------------------------------------------------------
2712 -- FUNCTION get_amount
2713 -------------------------------------------------------------------------------
2714 -- Start of comments
2715 --
2716 -- Function Name : get_amount
2717 -- Description : returns the periodic amount for line pricing options table
2718 --
2719 -- Business Rules :
2720 --
2721 --
2722 -- Parameters :
2723 -- Version : 1.0
2724 -- History : 15-SEP-2005 SK
2725
2726 FUNCTION get_amount(p_casflow_id IN NUMBER)
2727 RETURN VARCHAR2 IS
2728 -- Variables Declarations
2729 l_api_version CONSTANT NUMBER DEFAULT 1.0;
2730 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'GET_AMOUNT';
2731
2732 CURSOR check_stub(l_cashflow_id NUMBER) IS
2733 SELECT STUB_DAYS,
2734 STUB_AMOUNT
2735 FROM OKL_CASH_FLOW_LEVELS
2736 WHERE CAF_ID = l_cashflow_id;
2737
2738 CURSOR check_count(l_cashflow_id NUMBER) IS
2739 SELECT COUNT(ID)
2740 FROM OKL_CASH_FLOW_LEVELS
2741 WHERE CAF_ID = l_cashflow_id;
2742
2743 CURSOR get_amount(l_cashflow_id NUMBER) IS
2744 SELECT AMOUNT
2745 FROM OKL_CASH_FLOW_LEVELS
2746 WHERE CAF_ID = l_cashflow_id;
2747
2748 l_stub_days NUMBER;
2749 l_stub_amount NUMBER;
2750 l_count NUMBER;
2751 l_amount VARCHAR2(10);
2752 l_stub_flg VARCHAR2(1):= 'N';
2753
2754
2755 BEGIN
2756
2757 OPEN check_count(p_casflow_id);
2758 FETCH check_count INTO l_count;
2759 CLOSE check_count;
2760
2761 OPEN get_amount(p_casflow_id);
2762 FETCH get_amount INTO l_amount;
2763 CLOSE get_amount;
2764 FOR l_stub_rec IN check_stub(p_casflow_id) LOOP
2765 IF (l_stub_rec.stub_days IS NOT NULL OR
2766 l_stub_rec.stub_amount IS NOT NULL) THEN
2767 l_stub_flg := 'Y';
2768 END IF;
2769 END LOOP;
2770
2771 IF l_stub_flg = 'Y' THEN
2772 RETURN OKL_ACCOUNTING_UTIL.get_lookup_meaning('OKL_QUOTE_PRICING_OPTIONS','VAR');
2773 ELSIF l_count > 1 THEN
2774 RETURN OKL_ACCOUNTING_UTIL.get_lookup_meaning('OKL_QUOTE_PRICING_OPTIONS','VAR');
2775 ELSE
2776 RETURN l_amount;
2777 END IF;
2778
2779 EXCEPTION
2780 -- other appropriate handlers
2781 WHEN OTHERS THEN
2782 -- store SQL error message on message stack
2783 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2784 p_msg_name => G_UNEXPECTED_ERROR,
2785 p_token1 => G_SQLCODE_TOKEN,
2786 p_token1_value => sqlcode,
2787 p_token2 => G_SQLERRM_TOKEN,
2788 p_token2_value => sqlerrm);
2789 RETURN NULL;
2790
2791
2792 END;
2793
2794 END OKL_LEASE_QUOTE_PRICING_PVT;