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