[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_QUOTE_FEE_PVT
Source
1 PACKAGE BODY OKL_LEASE_QUOTE_FEE_PVT AS
2 /* $Header: OKLRQUFB.pls 120.24.12020000.2 2012/08/07 13:44:12 rpillay ship $ */
3
4 -----------------------------------
5 -- FUNCTION is_pricing_method_equal
6 -----------------------------------
7 FUNCTION is_pricing_method_equal(p_source_quote_id IN NUMBER,
8 p_target_quote_id IN NUMBER)
9 RETURN VARCHAR2 IS
10
11 lv_source_pricing_type VARCHAR2(15);
12 lv_target_pricing_type VARCHAR2(15);
13 BEGIN
14 select pricing_method
15 into lv_source_pricing_type
16 from okl_lease_quotes_b
17 where id = p_source_quote_id;
18
19 select pricing_method
20 into lv_target_pricing_type
21 from okl_lease_quotes_b
22 where id = p_target_quote_id;
23
24 IF (lv_source_pricing_type = lv_target_pricing_type) THEN
25 RETURN 'Y';
26 ELSE
27 RETURN 'N';
28 END IF;
29 END is_pricing_method_equal;
30
31 ---------------------------------------
32 -- PROCEDURE populate_fee_details
33 ---------------------------------------
34 PROCEDURE populate_fee_details (
35 p_fee_rec IN OUT NOCOPY fee_rec_type
36 ,p_expense_header_rec IN OUT NOCOPY cashflow_hdr_rec_type
37 ,p_expense_level_tbl IN OUT NOCOPY cashflow_level_tbl_type
38 ,p_payment_level_tbl IN cashflow_level_tbl_type
39 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
40
41 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'populate_fee_details';
42
43 l_fee_rec fee_rec_type;
44 l_expense_header_rec cashflow_hdr_rec_type;
45 l_expense_level_tbl cashflow_level_tbl_type;
46
47 l_k_effective_from DATE;
48
49 BEGIN
50 l_fee_rec := p_fee_rec;
51 l_expense_header_rec := p_expense_header_rec;
52 l_expense_level_tbl := p_expense_level_tbl;
53
54 SELECT expected_start_date
55 INTO l_k_effective_from
56 FROM okl_lease_quotes_b
57 WHERE id = l_fee_rec.parent_object_id;
58
59 --bug 4737394 start
60 IF l_fee_rec.fee_type = 'INCOME' THEN
61 IF (p_payment_level_tbl.COUNT > 0) THEN
62 l_fee_rec.fee_amount := 0;
63 FOR i IN p_payment_level_tbl.FIRST..p_payment_level_tbl.LAST LOOP
64 IF p_payment_level_tbl.exists(i) THEn
65 l_fee_rec.fee_amount := l_fee_rec.fee_amount + p_payment_level_tbl(i).periodic_amount * p_payment_level_tbl(i).periods;
66 END IF;
67 END LOOP;
68 END IF;
69 END IF;
70 --bug 4737394 end
71
72 IF l_fee_rec.fee_type IN ('ABSORBED', 'INCOME', 'ROLLOVER') THEN
73 l_fee_rec.effective_from := l_k_effective_from;
74 END IF;
75
76 IF l_fee_rec.fee_type IN ('SEC_DEPOSIT') THEN
77 l_fee_rec.fee_amount := p_payment_level_tbl(1).periodic_amount * p_payment_level_tbl(1).periods;
78 END IF;
79
80
81 IF l_fee_rec.fee_type IN ('ABSORBED') THEN
82 l_expense_level_tbl(1).periods := 1;
83 l_expense_level_tbl(1).periodic_amount := l_fee_rec.fee_amount;
84 l_expense_header_rec.frequency_code := 'M';
85 END IF;
86
87 IF l_fee_rec.fee_type IN ('EXPENSE' , 'MISCELLANEOUS') THEN
88 l_fee_rec.fee_amount := l_expense_level_tbl(1).periodic_amount * l_expense_level_tbl(1).periods;
89 END IF;
90
91 p_fee_rec := l_fee_rec;
92 p_expense_header_rec := l_expense_header_rec;
93 p_expense_level_tbl := l_expense_level_tbl;
94
95 x_return_status := G_RET_STS_SUCCESS;
96
97 EXCEPTION
98
99 WHEN OKL_API.G_EXCEPTION_ERROR THEN
100 x_return_status := G_RET_STS_ERROR;
101
102 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
103 x_return_status := G_RET_STS_UNEXP_ERROR;
104
105 WHEN OTHERS THEN
106 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
107 p_msg_name => G_DB_ERROR,
108 p_token1 => G_PROG_NAME_TOKEN,
109 p_token1_value => l_program_name,
110 p_token2 => G_SQLCODE_TOKEN,
111 p_token2_value => sqlcode,
112 p_token3 => G_SQLERRM_TOKEN,
113 p_token3_value => sqlerrm);
114
115 x_return_status := G_RET_STS_UNEXP_ERROR;
116 END populate_fee_details;
117
118 -------------------------------
119 -- PROCEDURE validate_quote_fee
120 -------------------------------
121 PROCEDURE validate_quote_fee (p_fee_rec IN fee_rec_type,
122 p_link_asset_tbl IN line_relation_tbl_type,
123 p_payment_levels_tbl IN cashflow_level_tbl_type,
124 p_expense_header_rec IN cashflow_hdr_rec_type,
125 p_expense_levels_tbl IN cashflow_level_tbl_type,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2) IS
129
130 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_quote_fee';
131
132 l_quote_start_date DATE;
133 l_quote_end_date DATE;
134 l_last_rec_exp_date DATE;
135 l_mpp BINARY_INTEGER;
136 lx_return_status VARCHAR2(1);
137
138 BEGIN
139
140 IF p_fee_rec.fee_type IN ('MISCELLANEOUS', 'INCOME', 'SEC_DEPOSIT') AND
141 p_payment_levels_tbl.COUNT = 0 THEN
142
143 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
144 p_msg_name => 'OKL_FEETYPE_PAYMENT_REQD');
145
146 RAISE OKL_API.G_EXCEPTION_ERROR;
147
148 END IF;
149
150 IF p_fee_rec.fee_type = 'SEC_DEPOSIT' THEN
151
152 IF (p_payment_levels_tbl.COUNT > 1) OR (p_payment_levels_tbl(p_payment_levels_tbl.FIRST).periods > 1) THEN
153
154 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
155 p_msg_name => 'OKL_SD_PAYMENT_MULTIPLE');
156
157 RAISE OKL_API.G_EXCEPTION_ERROR;
158
159 END IF;
160
161 IF p_payment_levels_tbl(p_payment_levels_tbl.FIRST).stub_days IS NOT NULL OR
162 p_payment_levels_tbl(p_payment_levels_tbl.FIRST).stub_amount IS NOT NULL THEN
163
164 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
165 p_msg_name => 'OKL_SD_PAYMENT_STUB');
166
167 RAISE OKL_API.G_EXCEPTION_ERROR;
168
169 END IF;
170
171 END IF;
172
173 SELECT expected_start_date,
174 ADD_MONTHS(expected_start_date, term)-1
175 INTO l_quote_start_date,
176 l_quote_end_date
177 FROM okl_lease_quotes_b
178 WHERE id = p_fee_rec.parent_object_id;
179
180 IF p_fee_rec.fee_type IN ('EXPENSE', 'MISCELLANEOUS') THEN
181
182 IF p_expense_header_rec.frequency_code = 'M' THEN
183 l_mpp := 1;
184 ELSIF p_expense_header_rec.frequency_code = 'Q' THEN
185 l_mpp := 3;
186 ELSIF p_expense_header_rec.frequency_code = 'S' THEN
187 l_mpp := 6;
188 ELSIF p_expense_header_rec.frequency_code = 'A' THEN
189 l_mpp := 12;
190 END IF;
191
192 -- last recurring expense date was not calculated properly
193 -- previously we were just adding the moths per period
194 -- we should be adding (moths per period*periods)
195 l_last_rec_exp_date := ADD_MONTHS(p_fee_rec.effective_from, l_mpp*(p_expense_levels_tbl(1).periods-1));
196
197 IF l_last_rec_exp_date > l_quote_end_date THEN
198
199 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
200 p_msg_name => 'OKL_RECEXP_PAST_K_END');
201
202 RAISE OKL_API.G_EXCEPTION_ERROR;
203
204 END IF;
205
206 END IF;
207
208 IF p_fee_rec.fee_type IN ('CAPITALIZED', 'FINANCED') THEN
209
210 IF p_link_asset_tbl.COUNT > 0 AND TRUNC(l_quote_start_date) <> TRUNC(p_fee_rec.effective_from) THEN
211
212 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
213 p_msg_name => 'OKL_QTE_ASST_FEE_DATE_MISMATCH');
214
215 RAISE OKL_API.G_EXCEPTION_ERROR;
216
217 END IF;
218
219 END IF;
220
221 /*IF p_fee_rec.fee_type = 'ROLLOVER' THEN
222
223 check_redundant_rollover (p_chr_id => p_quote_fee_rec.chr_id,
224 p_cle_id => p_quote_fee_rec.cle_id,
225 p_qte_id => p_quote_fee_rec.qte_id,
226 x_return_status => lx_return_status);
227
228 IF lx_return_status = G_RET_STS_UNEXP_ERROR THEN
229 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
230 ELSIF lx_return_status = G_RET_STS_ERROR THEN
231 RAISE OKL_API.G_EXCEPTION_ERROR;
232 END IF;
233
234 END IF;*/
235
236 IF p_fee_rec.effective_from NOT BETWEEN l_quote_start_date AND l_quote_end_date THEN
237
238 IF p_fee_rec.fee_type IN ('FINANCED', 'CAPITALIZED') THEN
239
240 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
241 p_msg_name => 'OKL_QTE_FUNDING_DATE_NA');
242
243 RAISE OKL_API.G_EXCEPTION_ERROR;
244
245 ELSIF p_fee_rec.fee_type IN ('EXPENSE', 'MISCELLANEOUS') THEN
246
247 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
248 p_msg_name => 'OKL_QTE_EXPENSE_DATE_NA');
249
250 RAISE OKL_API.G_EXCEPTION_ERROR;
251
252 ELSIF p_fee_rec.fee_type = 'SEC_DEPOSIT' THEN
253
254 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
255 p_msg_name => 'OKL_QTE_DEPOSIT_DATE_NA');
256
257 RAISE OKL_API.G_EXCEPTION_ERROR;
258
259 END IF;
260
261 END IF;
262
263 x_return_status := G_RET_STS_SUCCESS;
264
265 EXCEPTION
266
267 WHEN OKL_API.G_EXCEPTION_ERROR THEN
268
269 x_return_status := G_RET_STS_ERROR;
270
271 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
272
273 x_return_status := G_RET_STS_UNEXP_ERROR;
274
275 WHEN OTHERS THEN
276
277 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
278 p_msg_name => G_DB_ERROR,
279 p_token1 => G_PROG_NAME_TOKEN,
280 p_token1_value => l_program_name,
281 p_token2 => G_SQLCODE_TOKEN,
282 p_token2_value => sqlcode,
283 p_token3 => G_SQLERRM_TOKEN,
284 p_token3_value => sqlerrm);
285
286 x_return_status := G_RET_STS_UNEXP_ERROR;
287
288 END validate_quote_fee;
289
290 -----------------------------------
291 -- PROCEDURE get_line_relations_tbl
292 -----------------------------------
293 PROCEDURE get_line_relations_tbl (
294 p_fee_id IN NUMBER
295 ,x_line_relation_tbl OUT NOCOPY line_relation_tbl_type
296 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
297
298 l_program_name CONSTANT VARCHAR2(30) := 'get_line_relations_tbl';
299 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
300 i BINARY_INTEGER := 0;
301
302 CURSOR c_db_line_relations IS
303 SELECT
304 id
305 ,object_version_number
306 ,source_line_type
307 ,source_line_id
308 ,related_line_type
309 ,related_line_id
310 ,amount
311 ,short_description
312 ,description
313 ,comments
314 FROM okl_line_relationships_v
315 WHERE related_line_id = p_fee_id;
316 BEGIN
317 FOR l_db_line_relations IN c_db_line_relations LOOP
318 x_line_relation_tbl(i).id := l_db_line_relations.id;
319 x_line_relation_tbl(i).object_version_number := l_db_line_relations.object_version_number;
320 x_line_relation_tbl(i).source_line_type := l_db_line_relations.source_line_type;
321 x_line_relation_tbl(i).source_line_id := l_db_line_relations.source_line_id;
322 x_line_relation_tbl(i).related_line_type := l_db_line_relations.related_line_type;
323 x_line_relation_tbl(i).related_line_id := l_db_line_relations.related_line_id;
324 x_line_relation_tbl(i).amount := l_db_line_relations.amount;
325 x_line_relation_tbl(i).short_description := l_db_line_relations.short_description;
326 x_line_relation_tbl(i).description := l_db_line_relations.description;
327 x_line_relation_tbl(i).comments := l_db_line_relations.comments;
328 i := i + 1;
329 END LOOP;
330
331 x_return_status := G_RET_STS_SUCCESS;
332 EXCEPTION
333
334 WHEN OKL_API.G_EXCEPTION_ERROR THEN
335 x_return_status := G_RET_STS_ERROR;
336
337 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
338 x_return_status := G_RET_STS_UNEXP_ERROR;
339
340 WHEN OTHERS THEN
341 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
342 p_msg_name => G_DB_ERROR,
343 p_token1 => G_PROG_NAME_TOKEN,
344 p_token1_value => l_program_name,
345 p_token2 => G_SQLCODE_TOKEN,
346 p_token2_value => sqlcode,
347 p_token3 => G_SQLERRM_TOKEN,
348 p_token3_value => sqlerrm);
349
350 x_return_status := G_RET_STS_UNEXP_ERROR;
351
352 END get_line_relations_tbl;
353
354 ------------------------
355 -- PROCEDURE get_fee_rec
356 ------------------------
357 PROCEDURE get_fee_rec (
358 p_fee_id IN NUMBER
359 ,x_fee_rec OUT NOCOPY okl_fee_pvt.feev_rec_type
360 ,x_return_status OUT NOCOPY VARCHAR2
361 ) IS
362
363 l_program_name CONSTANT VARCHAR2(30) := 'get_fee_rec';
364 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
365
366 BEGIN
367
368 SELECT
369 id
370 ,attribute_category
371 ,attribute1
372 ,attribute2
373 ,attribute3
374 ,attribute4
375 ,attribute5
376 ,attribute6
377 ,attribute7
378 ,attribute8
379 ,attribute9
380 ,attribute10
381 ,attribute11
382 ,attribute12
383 ,attribute13
384 ,attribute14
385 ,attribute15
386 ,object_version_number
387 ,parent_object_id
388 ,parent_object_code
389 ,stream_type_id
390 ,fee_type
391 ,rate_card_id
392 ,rate_template_id
393 ,effective_from
394 ,effective_to
395 ,supplier_id
396 ,rollover_quote_id
397 ,initial_direct_cost
398 ,fee_amount
399 ,structured_pricing
400 ,target_arrears
401 ,lease_rate_factor
402 ,target_frequency
403 ,target_amount
404 ,short_description
405 ,description
406 ,comments
407 ,payment_type_id
408 ,fee_purpose_code -- Bug#9708535
409 INTO
410 x_fee_rec.id
411 ,x_fee_rec.attribute_category
412 ,x_fee_rec.attribute1
413 ,x_fee_rec.attribute2
414 ,x_fee_rec.attribute3
415 ,x_fee_rec.attribute4
416 ,x_fee_rec.attribute5
417 ,x_fee_rec.attribute6
418 ,x_fee_rec.attribute7
419 ,x_fee_rec.attribute8
420 ,x_fee_rec.attribute9
421 ,x_fee_rec.attribute10
422 ,x_fee_rec.attribute11
423 ,x_fee_rec.attribute12
424 ,x_fee_rec.attribute13
425 ,x_fee_rec.attribute14
426 ,x_fee_rec.attribute15
427 ,x_fee_rec.object_version_number
428 ,x_fee_rec.parent_object_id
429 ,x_fee_rec.parent_object_code
430 ,x_fee_rec.stream_type_id
431 ,x_fee_rec.fee_type
432 ,x_fee_rec.rate_card_id
433 ,x_fee_rec.rate_template_id
434 ,x_fee_rec.effective_from
435 ,x_fee_rec.effective_to
436 ,x_fee_rec.supplier_id
437 ,x_fee_rec.rollover_quote_id
438 ,x_fee_rec.initial_direct_cost
439 ,x_fee_rec.fee_amount
440 ,x_fee_rec.structured_pricing
441 ,x_fee_rec.target_arrears
442 ,x_fee_rec.lease_rate_factor
443 ,x_fee_rec.target_frequency
444 ,x_fee_rec.target_amount
445 ,x_fee_rec.short_description
446 ,x_fee_rec.description
447 ,x_fee_rec.comments
448 ,x_fee_rec.payment_type_id
449 ,x_fee_rec.fee_purpose_code -- Bug#9708535
450 FROM okl_fees_v
451 WHERE id = p_fee_id;
452
453 x_return_status := G_RET_STS_SUCCESS;
454
455 EXCEPTION
456
457 WHEN OKL_API.G_EXCEPTION_ERROR THEN
458 x_return_status := G_RET_STS_ERROR;
459
460 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
461 x_return_status := G_RET_STS_UNEXP_ERROR;
462
463 WHEN OTHERS THEN
464 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
465 p_msg_name => G_DB_ERROR,
466 p_token1 => G_PROG_NAME_TOKEN,
467 p_token1_value => l_program_name,
468 p_token2 => G_SQLCODE_TOKEN,
469 p_token2_value => sqlcode,
470 p_token3 => G_SQLERRM_TOKEN,
471 p_token3_value => sqlerrm);
472
473 x_return_status := G_RET_STS_UNEXP_ERROR;
474
475 END get_fee_rec;
476
477
478 ---------------------------------
479 -- PROCEDURE validate_link_assets
480 ---------------------------------
481 PROCEDURE validate_link_assets (p_fee_amount IN NUMBER,
482 p_assoc_assets_tbl IN line_relation_tbl_type,
483 x_derive_assoc_amt OUT NOCOPY VARCHAR2,
484 x_return_status OUT NOCOPY VARCHAR2) IS
485
486 l_program_name CONSTANT VARCHAR2(30) := 'validate_link_assets';
487 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
488
489 l_link_exists_yn VARCHAR2(1);
490 l_assoc_total NUMBER;
491 l_amt_flag VARCHAR2(1);
492
493 BEGIN
494
495 l_assoc_total := 0;
496 l_amt_flag := 'N';
497
498 FOR i IN p_assoc_assets_tbl.FIRST .. p_assoc_assets_tbl.LAST LOOP
499 IF p_assoc_assets_tbl.EXISTS(i) THEN
500 IF p_assoc_assets_tbl(i).amount IS NOT NULL THEN
501 l_amt_flag := 'Y';
502 l_assoc_total := l_assoc_total + p_assoc_assets_tbl(i).amount;
503 END IF;
504 IF (p_assoc_assets_tbl(i).amount IS NULL) AND l_amt_flag = 'Y' THEN
505 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME, p_msg_name => 'OKL_LINKASSET_NULL_FOUND');
506 RAISE OKL_API.G_EXCEPTION_ERROR;
507 END IF;
508 END IF;
509 END LOOP;
510
511 IF l_amt_flag = 'Y' AND l_assoc_total <> p_fee_amount THEN
512 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
513 p_msg_name => 'OKL_LINKASSET_AMT_MISMATCH',
514 p_token1 => 'LINE_COST',
515 p_token1_value => p_fee_amount,
516 p_token2 => 'ASSOC_TOTAL',
517 p_token2_value => l_assoc_total);
518 RAISE OKL_API.G_EXCEPTION_ERROR;
519 END IF;
520
521 IF l_amt_flag = 'Y' THEN
522 x_derive_assoc_amt := 'N';
523 ELSE
524 x_derive_assoc_amt := 'Y';
525 END IF;
526
527 x_return_status := G_RET_STS_SUCCESS;
528
529 EXCEPTION
530
531 WHEN OKL_API.G_EXCEPTION_ERROR THEN
532 x_return_status := G_RET_STS_ERROR;
533
534 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
535 x_return_status := G_RET_STS_UNEXP_ERROR;
536
537 WHEN OTHERS THEN
538 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
539 p_msg_name => G_DB_ERROR,
540 p_token1 => G_PROG_NAME_TOKEN,
541 p_token1_value => l_program_name,
542 p_token2 => G_SQLCODE_TOKEN,
543 p_token2_value => sqlcode,
544 p_token3 => G_SQLERRM_TOKEN,
545 p_token3_value => sqlerrm);
546 x_return_status := G_RET_STS_UNEXP_ERROR;
547
548 END validate_link_assets;
549
550
551 ---------------------------------------
552 -- PROCEDURE process_link_asset_amounts
553 ---------------------------------------
554 PROCEDURE process_link_asset_amounts (
555 p_quote_id IN NUMBER
556 ,p_fee_amount IN NUMBER
557 ,p_link_asset_tbl IN OUT NOCOPY line_relation_tbl_type
558 ,p_derive_assoc_amt IN VARCHAR2
559 ,p_override_pricing_type IN VARCHAR2 DEFAULT 'N'
560 ,x_return_status OUT NOCOPY VARCHAR2
561 ,x_msg_count OUT NOCOPY NUMBER
562 ,x_msg_data OUT NOCOPY VARCHAR2
563 ) IS
564
565 l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'process_link_asset_amounts';
566
567 l_link_asset_tbl line_relation_tbl_type;
568
569 l_line_amount NUMBER;
570 l_asset_oec NUMBER;
571 l_oec_total NUMBER;
572 l_assoc_amount NUMBER;
573 l_assoc_total NUMBER;
574 l_currency_code VARCHAR2(15);
575 lv_parent_object_code VARCHAR2(30);
576 l_compare_amt NUMBER;
577 l_diff NUMBER;
578 l_adj_rec BINARY_INTEGER;
579 lx_return_status VARCHAR2(1);
580 lv_pricing_method OKL_LEASE_QUOTES_B.PRICING_METHOD%TYPE;
581
582 CURSOR c_get_parent_object_code IS
583 SELECT parent_object_code
584 FROM okl_lease_quotes_b
585 WHERE id = p_quote_id;
586 BEGIN
587
588 l_link_asset_tbl := p_link_asset_tbl;
589
590 SELECT PRICING_METHOD
591 INTO lv_pricing_method
592 FROM OKL_LEASE_QUOTES_B
593 WHERE ID = p_quote_id;
594
595 -- Fee Asset amount will be null in case of 'Solve for Financed Amount' pricing
596 -- method .. the values will be populated after the Pricing call is made
597 IF (lv_pricing_method = 'SF' AND p_override_pricing_type = 'N') THEN
598 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
599 IF l_link_asset_tbl.EXISTS(i) THEN
600 l_link_asset_tbl(i).amount := null;
601 END IF;
602 END LOOP;
603
604 p_link_asset_tbl := l_link_asset_tbl;
605 RETURN;
606 END IF;
607
608 l_oec_total := 0;
609 l_assoc_total := 0;
610
611 OPEN c_get_parent_object_code;
612 FETCH c_get_parent_object_code INTO lv_parent_object_code;
613 CLOSE c_get_parent_object_code;
614
615 IF (lv_parent_object_code = 'LEASEOPP') THEN
616 SELECT currency_code
617 INTO l_currency_code
618 FROM okl_lease_opportunities_b lop,
619 okl_lease_quotes_b lsq
620 WHERE lsq.parent_object_code = lv_parent_object_code
621 AND lsq.parent_object_id = lop.id
622 AND lsq.id = p_quote_id;
623 ELSIF (lv_parent_object_code = 'LEASEAPP') THEN
624 SELECT currency_code
625 INTO l_currency_code
626 FROM okl_lease_applications_b lap,
627 okl_lease_quotes_b lsq
628 WHERE lsq.parent_object_code = lv_parent_object_code
629 AND lsq.parent_object_id = lap.id
630 AND lsq.id = p_quote_id;
631 END IF;
632
633 l_line_amount := p_fee_amount;
634
635 ------------------------------------------------------------------
636 -- 1. Loop through to get OEC total of all assets being associated
637 ------------------------------------------------------------------
638 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
639
640 IF l_link_asset_tbl.EXISTS(i) THEN
641
642 SELECT NVL(OEC, 0)
643 INTO l_asset_oec
644 FROM okl_assets_b
645 WHERE id = l_link_asset_tbl(i).source_line_id;
646
647 l_oec_total := l_oec_total + l_asset_oec;
648
649 END IF;
650
651 END LOOP;
652
653 ----------------------------------------------------------------------------
654 -- 2. Loop through to determine associated amounts and round off the amounts
655 ----------------------------------------------------------------------------
656 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
657
658 IF l_link_asset_tbl.EXISTS(i) THEN
659
660 IF p_derive_assoc_amt = 'N' THEN
661
662 l_assoc_amount := l_link_asset_tbl(i).amount;
663
664 ELSIF l_oec_total = 0 THEN
665
666 l_assoc_amount := l_line_amount / l_link_asset_tbl.COUNT;
667
668 ELSE
669
670 SELECT NVL(OEC, 0)
671 INTO l_asset_oec
672 FROM okl_assets_b
673 WHERE id = l_link_asset_tbl(i).source_line_id;
674
675 IF l_link_asset_tbl.COUNT = 1 THEN
676
677 l_assoc_amount := l_line_amount;
678
679 ELSE
680
681 l_assoc_amount := l_line_amount * l_asset_oec / l_oec_total;
682
683 END IF;
684 END IF;
685
686 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
687 p_currency_code => l_currency_code);
688
689 l_assoc_total := l_assoc_total + l_assoc_amount;
690
691
692 l_link_asset_tbl(i).amount := l_assoc_amount;
693
694 END IF;
695
696 END LOOP;
697
698 ----------------------------------------------------------------------------------------------------
699 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
700 ----------------------------------------------------------------------------------------------------
701 IF l_assoc_total <> l_line_amount THEN
702
703 l_diff := ABS(l_assoc_total - l_line_amount);
704
705 FOR i IN l_link_asset_tbl.FIRST .. l_link_asset_tbl.LAST LOOP
706
707 IF l_link_asset_tbl.EXISTS(i) THEN
708
709 -- if the total split amount is less than line amount add the difference amount to the
710 -- asset with less amount and if the total split amount is greater than the line amount
711 -- than subtract the difference amount from the asset with highest amount
712
713 IF i = l_link_asset_tbl.FIRST THEN
714
715 l_adj_rec := i;
716 l_compare_amt := l_link_asset_tbl(i).amount;
717
718 ELSIF (l_assoc_total < l_line_amount) AND (l_link_asset_tbl(i).amount <= l_compare_amt) OR
719 (l_assoc_total > l_line_amount) AND (l_link_asset_tbl(i).amount >= l_compare_amt) THEN
720
721 l_adj_rec := i;
722 l_compare_amt := l_link_asset_tbl(i).amount;
723
724 END IF;
725
726 END IF;
727
728 END LOOP;
729
730 IF l_assoc_total < l_line_amount THEN
731
732 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount + l_diff;
733
734 ELSE
735
736 l_link_asset_tbl(l_adj_rec).amount := l_link_asset_tbl(l_adj_rec).amount - l_diff;
737
738 END IF;
739
740 END IF;
741
742 p_link_asset_tbl := l_link_asset_tbl;
743 x_return_status := G_RET_STS_SUCCESS;
744
745 EXCEPTION
746
747 WHEN OKL_API.G_EXCEPTION_ERROR THEN
748 x_return_status := G_RET_STS_ERROR;
749
750 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
751 x_return_status := G_RET_STS_UNEXP_ERROR;
752
753 WHEN OTHERS THEN
754 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
755 p_msg_name => G_DB_ERROR,
756 p_token1 => G_PROG_NAME_TOKEN,
757 p_token1_value => l_program_name,
758 p_token2 => G_SQLCODE_TOKEN,
759 p_token2_value => sqlcode,
760 p_token3 => G_SQLERRM_TOKEN,
761 p_token3_value => sqlerrm);
762
763 x_return_status := G_RET_STS_UNEXP_ERROR;
764
765 END process_link_asset_amounts;
766
767
768 -------------------------------------
769 -- PROCEDURE get_deleted_assoc_assets
770 -------------------------------------
771 PROCEDURE get_deleted_assoc_assets (p_fee_id IN NUMBER,
772 p_fee_type IN VARCHAR2,
773 p_assoc_asset_tbl IN line_relation_tbl_type,
774 x_deleted_assoc_asset_tbl OUT NOCOPY assoc_asset_tbl_type,
775 x_return_status OUT NOCOPY VARCHAR2) IS
776
777 l_program_name CONSTANT VARCHAR2(30) := 'get_deleted_assoc_assets';
778 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
779
780 CURSOR c_db_assoc_assets IS
781 SELECT id
782 FROM okl_line_relationships_b
783 WHERE related_line_type = p_fee_type
784 AND related_line_id = p_fee_id;
785
786 l_assoc_asset_tbl assoc_asset_tbl_type;
787 l_delete_flag VARCHAR2(1);
788 i BINARY_INTEGER := 0;
789
790 BEGIN
791 IF (p_assoc_asset_tbl.COUNT > 0) THEN
792 FOR l_db_assoc_assets IN c_db_assoc_assets LOOP
793 l_delete_flag := 'Y';
794 FOR j IN p_assoc_asset_tbl.FIRST .. p_assoc_asset_tbl.LAST LOOP
795 IF p_assoc_asset_tbl.EXISTS(j) THEN
796 IF l_db_assoc_assets.id = p_assoc_asset_tbl(j).id THEN
797 l_delete_flag := 'N';
798 END IF;
799 END IF;
800 END LOOP;
801
802 IF l_delete_flag = 'Y' THEN
803 l_assoc_asset_tbl(i).id := l_db_assoc_assets.id;
804 i := i + 1;
805 END IF;
806 END LOOP;
807 ELSE
808 FOR l_db_assoc_assets IN c_db_assoc_assets LOOP
809 l_assoc_asset_tbl(i).id := l_db_assoc_assets.id;
810 i := i + 1;
811 END LOOP;
812 END IF;
813
814 x_deleted_assoc_asset_tbl := l_assoc_asset_tbl;
815 x_return_status := G_RET_STS_SUCCESS;
816
817 EXCEPTION
818
819 WHEN OKL_API.G_EXCEPTION_ERROR THEN
820 x_return_status := G_RET_STS_ERROR;
821
822 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
823 x_return_status := G_RET_STS_UNEXP_ERROR;
824
825 WHEN OTHERS THEN
826 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
827 p_msg_name => G_DB_ERROR,
828 p_token1 => G_PROG_NAME_TOKEN,
829 p_token1_value => l_program_name,
830 p_token2 => G_SQLCODE_TOKEN,
831 p_token2_value => sqlcode,
832 p_token3 => G_SQLERRM_TOKEN,
833 p_token3_value => sqlerrm);
834
835 x_return_status := G_RET_STS_UNEXP_ERROR;
836
837 END get_deleted_assoc_assets;
838
839
840 -------------------------------------
841 -- PROCEDURE create_line_associations
842 -------------------------------------
843 PROCEDURE create_line_associations (
844 p_fee_id IN NUMBER
845 ,p_assoc_assets_tbl IN line_relation_tbl_type
846 ,x_return_status OUT NOCOPY VARCHAR2
847 ,x_msg_count OUT NOCOPY NUMBER
848 ,x_msg_data OUT NOCOPY VARCHAR2) IS
849
850 l_program_name CONSTANT VARCHAR2(30) := 'create_line_associations';
851 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
852
853 l_assoc_assets_tbl assoc_asset_tbl_type;
854 lx_assoc_assets_tbl assoc_asset_tbl_type;
855
856 l_line_relation_tbl line_relation_tbl_type;
857
858 BEGIN
859
860 l_line_relation_tbl := p_assoc_assets_tbl;
861
862 FOR i IN l_line_relation_tbl.FIRST .. l_line_relation_tbl.LAST LOOP
863 IF l_line_relation_tbl.EXISTS(i) THEN
864 l_assoc_assets_tbl(i).related_line_id := p_fee_id;
865 l_assoc_assets_tbl(i).related_line_type := l_line_relation_tbl(i).related_line_type;
866 l_assoc_assets_tbl(i).source_line_type := l_line_relation_tbl(i).source_line_type;
867 l_assoc_assets_tbl(i).source_line_id := l_line_relation_tbl(i).source_line_id;
868 l_assoc_assets_tbl(i).amount := l_line_relation_tbl(i).amount;
869 l_assoc_assets_tbl(i).short_description := l_line_relation_tbl(i).short_description;
870 l_assoc_assets_tbl(i).description := l_line_relation_tbl(i).description;
871 l_assoc_assets_tbl(i).comments := l_line_relation_tbl(i).comments;
872 --Added by jjuneja
873 l_assoc_assets_tbl(i).origination_income := l_line_relation_tbl(i).origination_income;
874 --End of addition by jjuneja
875
876 END IF;
877 END LOOP;
878
879 okl_lre_pvt.insert_row (
880 p_api_version => G_API_VERSION
881 ,p_init_msg_list => G_FALSE
882 ,x_return_status => x_return_status
883 ,x_msg_count => x_msg_count
884 ,x_msg_data => x_msg_data
885 ,p_lrev_tbl => l_assoc_assets_tbl
886 ,x_lrev_tbl => lx_assoc_assets_tbl
887 );
888
889 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
890 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
891 ELSIF x_return_status = G_RET_STS_ERROR THEN
892 RAISE OKL_API.G_EXCEPTION_ERROR;
893 END IF;
894
895 EXCEPTION
896
897 WHEN OKL_API.G_EXCEPTION_ERROR THEN
898 x_return_status := G_RET_STS_ERROR;
899
900 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
901 x_return_status := G_RET_STS_UNEXP_ERROR;
902
903 WHEN OTHERS THEN
904 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
905 p_msg_name => G_DB_ERROR,
906 p_token1 => G_PROG_NAME_TOKEN,
907 p_token1_value => l_program_name,
908 p_token2 => G_SQLCODE_TOKEN,
909 p_token2_value => sqlcode,
910 p_token3 => G_SQLERRM_TOKEN,
911 p_token3_value => sqlerrm);
912
913 x_return_status := G_RET_STS_UNEXP_ERROR;
914
915 END create_line_associations;
916
917
918 -------------------------------------
919 -- PROCEDURE update_line_associations
920 -------------------------------------
921 PROCEDURE update_line_associations (
922 p_fee_id IN NUMBER
923 ,p_fee_type IN VARCHAR2
924 ,p_assoc_assets_tbl IN line_relation_tbl_type
925 ,x_return_status OUT NOCOPY VARCHAR2
926 ,x_msg_count OUT NOCOPY NUMBER
927 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
928
929 l_program_name CONSTANT VARCHAR2(30) := 'update_line_associations';
930 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
931
932 l_assoc_create_tbl assoc_asset_tbl_type;
933 l_assoc_update_tbl assoc_asset_tbl_type;
934 lx_assoc_assets_tbl assoc_asset_tbl_type;
935
936 l_line_relation_tbl line_relation_tbl_type;
937 l_deleted_assoc_assets_tbl assoc_asset_tbl_type;
938
939 BEGIN
940
941 l_line_relation_tbl := p_assoc_assets_tbl;
942
943 get_deleted_assoc_assets (
944 p_fee_id => p_fee_id
945 ,p_fee_type => p_fee_type
946 ,p_assoc_asset_tbl => l_line_relation_tbl
947 ,x_deleted_assoc_asset_tbl => l_deleted_assoc_assets_tbl
948 ,x_return_status => x_return_status
949 );
950
951 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
952 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
953 ELSIF x_return_status = G_RET_STS_ERROR THEN
954 RAISE OKL_API.G_EXCEPTION_ERROR;
955 END IF;
956
957 IF l_deleted_assoc_assets_tbl.COUNT > 0 THEN
958 okl_lre_pvt.delete_row (
959 p_api_version => G_API_VERSION
960 ,p_init_msg_list => G_FALSE
961 ,x_return_status => x_return_status
962 ,x_msg_count => x_msg_count
963 ,x_msg_data => x_msg_data
964 ,p_lrev_tbl => l_deleted_assoc_assets_tbl );
965 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
966 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
967 ELSIF x_return_status = G_RET_STS_ERROR THEN
968 RAISE OKL_API.G_EXCEPTION_ERROR;
969 END IF;
970 END IF;
971
972 IF l_line_relation_tbl.COUNT > 0 THEN
973
974 FOR i IN l_line_relation_tbl.FIRST .. l_line_relation_tbl.LAST LOOP
975
976 IF l_line_relation_tbl.EXISTS(i) THEN
977
978 IF UPPER(l_line_relation_tbl(i).record_mode) = 'UPDATE' THEN
979
980 l_assoc_update_tbl(i).id := l_line_relation_tbl(i).id;
981 l_assoc_update_tbl(i).object_version_number := l_line_relation_tbl(i).object_version_number;
982 l_assoc_update_tbl(i).related_line_id := l_line_relation_tbl(i).related_line_id;
983 l_assoc_update_tbl(i).related_line_type := l_line_relation_tbl(i).related_line_type;
984 l_assoc_update_tbl(i).source_line_type := l_line_relation_tbl(i).source_line_type;
985 l_assoc_update_tbl(i).source_line_id := l_line_relation_tbl(i).source_line_id;
986 l_assoc_update_tbl(i).amount := l_line_relation_tbl(i).amount;
987 --Added by jjuneja
988 l_assoc_update_tbl(i).origination_income := l_line_relation_tbl(i).origination_income;
989 --End of addition by jjuneja
990
991 ELSIF UPPER(l_line_relation_tbl(i).record_mode) = 'CREATE' THEN
992
993 l_assoc_create_tbl(i).related_line_id := l_line_relation_tbl(i).related_line_id;
994 l_assoc_create_tbl(i).related_line_type := l_line_relation_tbl(i).related_line_type;
995 l_assoc_create_tbl(i).source_line_type := l_line_relation_tbl(i).source_line_type;
996 l_assoc_create_tbl(i).source_line_id := l_line_relation_tbl(i).source_line_id;
997 l_assoc_create_tbl(i).amount := l_line_relation_tbl(i).amount;
998 --Added by jjuneja
999 l_assoc_create_tbl(i).origination_income:= l_line_relation_tbl(i).origination_income;
1000 --End of addition by jjuneja
1001
1002 END IF;
1003
1004 END IF;
1005
1006 END LOOP;
1007
1008 END IF;
1009
1010 IF l_assoc_update_tbl.COUNT > 0 THEN
1011
1012 okl_lre_pvt.update_row (
1013 p_api_version => G_API_VERSION
1014 ,p_init_msg_list => G_FALSE
1015 ,x_return_status => x_return_status
1016 ,x_msg_count => x_msg_count
1017 ,x_msg_data => x_msg_data
1018 ,p_lrev_tbl => l_assoc_update_tbl
1019 ,x_lrev_tbl => lx_assoc_assets_tbl
1020 );
1021
1022 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1023 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1024 ELSIF x_return_status = G_RET_STS_ERROR THEN
1025 RAISE OKL_API.G_EXCEPTION_ERROR;
1026 END IF;
1027
1028 END IF;
1029
1030 IF l_assoc_create_tbl.COUNT > 0 THEN
1031
1032 okl_lre_pvt.insert_row (
1033 p_api_version => G_API_VERSION
1034 ,p_init_msg_list => G_FALSE
1035 ,x_return_status => x_return_status
1036 ,x_msg_count => x_msg_count
1037 ,x_msg_data => x_msg_data
1038 ,p_lrev_tbl => l_assoc_create_tbl
1039 ,x_lrev_tbl => lx_assoc_assets_tbl
1040 );
1041
1042 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1043 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1044 ELSIF x_return_status = G_RET_STS_ERROR THEN
1045 RAISE OKL_API.G_EXCEPTION_ERROR;
1046 END IF;
1047
1048 END IF;
1049
1050 x_return_status := G_RET_STS_SUCCESS;
1051
1052 EXCEPTION
1053
1054 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1055 x_return_status := G_RET_STS_ERROR;
1056
1057 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1058 x_return_status := G_RET_STS_UNEXP_ERROR;
1059
1060 WHEN OTHERS THEN
1061 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1062 p_msg_name => G_DB_ERROR,
1063 p_token1 => G_PROG_NAME_TOKEN,
1064 p_token1_value => l_program_name,
1065 p_token2 => G_SQLCODE_TOKEN,
1066 p_token2_value => sqlcode,
1067 p_token3 => G_SQLERRM_TOKEN,
1068 p_token3_value => sqlerrm);
1069
1070 x_return_status := G_RET_STS_UNEXP_ERROR;
1071
1072 END update_line_associations;
1073
1074
1075 --------------------------
1076 -- PROCEDURE create_header
1077 --------------------------
1078 PROCEDURE create_header (
1079 p_fee_rec IN okl_fee_pvt.feev_rec_type
1080 ,x_fee_id OUT NOCOPY NUMBER
1081 ,x_return_status OUT NOCOPY VARCHAR2
1082 ,x_msg_count OUT NOCOPY NUMBER
1083 ,x_msg_data OUT NOCOPY VARCHAR2
1084 ) IS
1085
1086 l_program_name CONSTANT VARCHAR2(30) := 'create_header';
1087 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1088
1089 l_fee_rec okl_fee_pvt.feev_rec_type;
1090
1091 BEGIN
1092
1093 okl_fee_pvt.insert_row (
1094 p_api_version => G_API_VERSION
1095 ,p_init_msg_list => G_FALSE
1096 ,x_return_status => x_return_status
1097 ,x_msg_count => x_msg_count
1098 ,x_msg_data => x_msg_data
1099 ,p_feev_rec => p_fee_rec
1100 ,x_feev_rec => l_fee_rec
1101 );
1102
1103 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1104 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1105 ELSIF x_return_status = G_RET_STS_ERROR THEN
1106 RAISE OKL_API.G_EXCEPTION_ERROR;
1107 END IF;
1108
1109 x_fee_id := l_fee_rec.id;
1110
1111 EXCEPTION
1112
1113 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1114 x_return_status := G_RET_STS_ERROR;
1115
1116 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1117 x_return_status := G_RET_STS_UNEXP_ERROR;
1118
1119 WHEN OTHERS THEN
1120 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1121 p_msg_name => G_DB_ERROR,
1122 p_token1 => G_PROG_NAME_TOKEN,
1123 p_token1_value => l_program_name,
1124 p_token2 => G_SQLCODE_TOKEN,
1125 p_token2_value => sqlcode,
1126 p_token3 => G_SQLERRM_TOKEN,
1127 p_token3_value => sqlerrm);
1128
1129 x_return_status := G_RET_STS_UNEXP_ERROR;
1130
1131 END create_header;
1132
1133
1134 --------------------------
1135 -- PROCEDURE update_header
1136 --------------------------
1137 PROCEDURE update_header (
1138 p_fee_rec IN okl_fee_pvt.feev_rec_type
1139 ,x_return_status OUT NOCOPY VARCHAR2
1140 ,x_msg_count OUT NOCOPY NUMBER
1141 ,x_msg_data OUT NOCOPY VARCHAR2
1142 ) IS
1143
1144 l_program_name CONSTANT VARCHAR2(30) := 'update_header';
1145 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1146
1147 l_fee_rec okl_fee_pvt.feev_rec_type;
1148
1149 BEGIN
1150
1151 okl_fee_pvt.update_row (
1152 p_api_version => G_API_VERSION
1153 ,p_init_msg_list => G_FALSE
1154 ,x_return_status => x_return_status
1155 ,x_msg_count => x_msg_count
1156 ,x_msg_data => x_msg_data
1157 ,p_feev_rec => p_fee_rec
1158 ,x_feev_rec => l_fee_rec
1159 );
1160
1161 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1162 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1163 ELSIF x_return_status = G_RET_STS_ERROR THEN
1164 RAISE OKL_API.G_EXCEPTION_ERROR;
1165 END IF;
1166
1167 EXCEPTION
1168
1169 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1170 x_return_status := G_RET_STS_ERROR;
1171
1172 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1173 x_return_status := G_RET_STS_UNEXP_ERROR;
1174
1175 WHEN OTHERS THEN
1176 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1177 p_msg_name => G_DB_ERROR,
1178 p_token1 => G_PROG_NAME_TOKEN,
1179 p_token1_value => l_program_name,
1180 p_token2 => G_SQLCODE_TOKEN,
1181 p_token2_value => sqlcode,
1182 p_token3 => G_SQLERRM_TOKEN,
1183 p_token3_value => sqlerrm);
1184
1185 x_return_status := G_RET_STS_UNEXP_ERROR;
1186
1187 END update_header;
1188
1189
1190 ---------------------------
1191 -- PROCEDURE create_payment
1192 ---------------------------
1193 PROCEDURE create_payment (
1194 p_fee_id IN NUMBER
1195 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1196 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1197 ,x_return_status OUT NOCOPY VARCHAR2
1198 ,x_msg_count OUT NOCOPY NUMBER
1199 ,x_msg_data OUT NOCOPY VARCHAR2
1200 ) IS
1201
1202 l_program_name CONSTANT VARCHAR2(30) := 'create_payment';
1203 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1204
1205 l_payment_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1206 l_payment_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1207
1208 BEGIN
1209
1210 l_payment_header_rec := p_payment_header_rec;
1211 l_payment_header_rec.parent_object_id := p_fee_id;
1212 l_payment_level_tbl := p_payment_level_tbl;
1213
1214 okl_lease_quote_cashflow_pvt.create_cashflow (
1215 p_api_version => G_API_VERSION
1216 ,p_init_msg_list => G_FALSE
1217 ,p_transaction_control => G_FALSE
1218 ,p_cashflow_header_rec => l_payment_header_rec
1219 ,p_cashflow_level_tbl => l_payment_level_tbl
1220 ,x_return_status => x_return_status
1221 ,x_msg_count => x_msg_count
1222 ,x_msg_data => x_msg_data
1223 );
1224
1225 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1226 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1227 ELSIF x_return_status = G_RET_STS_ERROR THEN
1228 RAISE OKL_API.G_EXCEPTION_ERROR;
1229 END IF;
1230
1231 EXCEPTION
1232
1233 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1234 x_return_status := G_RET_STS_ERROR;
1235
1236 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1237 x_return_status := G_RET_STS_UNEXP_ERROR;
1238
1239 WHEN OTHERS THEN
1240 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1241 p_msg_name => G_DB_ERROR,
1242 p_token1 => G_PROG_NAME_TOKEN,
1243 p_token1_value => l_program_name,
1244 p_token2 => G_SQLCODE_TOKEN,
1245 p_token2_value => sqlcode,
1246 p_token3 => G_SQLERRM_TOKEN,
1247 p_token3_value => sqlerrm);
1248
1249 x_return_status := G_RET_STS_UNEXP_ERROR;
1250
1251 END create_payment;
1252
1253
1254 ---------------------------
1255 -- PROCEDURE update_payment
1256 ---------------------------
1257 PROCEDURE update_payment (
1258 p_fee_id IN NUMBER
1259 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1260 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1261 ,x_return_status OUT NOCOPY VARCHAR2
1262 ,x_msg_count OUT NOCOPY NUMBER
1263 ,x_msg_data OUT NOCOPY VARCHAR2
1264 ) IS
1265
1266 l_program_name CONSTANT VARCHAR2(30) := 'update_payment';
1267 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1268
1269 l_payment_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1270 l_payment_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1271
1272 BEGIN
1273
1274 l_payment_header_rec := p_payment_header_rec;
1275 l_payment_level_tbl := p_payment_level_tbl;
1276
1277 okl_lease_quote_cashflow_pvt.update_cashflow (
1278 p_api_version => G_API_VERSION
1279 ,p_init_msg_list => G_FALSE
1280 ,p_transaction_control => G_FALSE
1281 ,p_cashflow_header_rec => l_payment_header_rec
1282 ,p_cashflow_level_tbl => l_payment_level_tbl
1283 ,x_return_status => x_return_status
1284 ,x_msg_count => x_msg_count
1285 ,x_msg_data => x_msg_data
1286 );
1287
1288 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1289 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1290 ELSIF x_return_status = G_RET_STS_ERROR THEN
1291 RAISE OKL_API.G_EXCEPTION_ERROR;
1292 END IF;
1293
1294 EXCEPTION
1295
1296 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1297 x_return_status := G_RET_STS_ERROR;
1298
1299 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1300 x_return_status := G_RET_STS_UNEXP_ERROR;
1301
1302 WHEN OTHERS THEN
1303 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1304 p_msg_name => G_DB_ERROR,
1305 p_token1 => G_PROG_NAME_TOKEN,
1306 p_token1_value => l_program_name,
1307 p_token2 => G_SQLCODE_TOKEN,
1308 p_token2_value => sqlcode,
1309 p_token3 => G_SQLERRM_TOKEN,
1310 p_token3_value => sqlerrm);
1311
1312 x_return_status := G_RET_STS_UNEXP_ERROR;
1313
1314 END update_payment;
1315
1316
1317 ---------------------------
1318 -- PROCEDURE create_expense
1319 ---------------------------
1320 PROCEDURE create_expense (
1321 p_fee_id IN NUMBER
1322 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1323 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1324 ,x_return_status OUT NOCOPY VARCHAR2
1325 ,x_msg_count OUT NOCOPY NUMBER
1326 ,x_msg_data OUT NOCOPY VARCHAR2
1327 ) IS
1328
1329 l_program_name CONSTANT VARCHAR2(30) := 'create_expense';
1330 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1331
1332 l_expense_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1333 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1334
1335 BEGIN
1336
1337 l_expense_header_rec := p_expense_header_rec;
1338 l_expense_header_rec.parent_object_id := p_fee_id;
1339 l_expense_level_tbl := p_expense_level_tbl;
1340
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_expense_header_rec
1346 ,p_cashflow_level_tbl => l_expense_level_tbl
1347 ,x_return_status => x_return_status
1348 ,x_msg_count => x_msg_count
1349 ,x_msg_data => x_msg_data
1350 );
1351
1352 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1353 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1354 ELSIF x_return_status = G_RET_STS_ERROR THEN
1355 RAISE OKL_API.G_EXCEPTION_ERROR;
1356 END IF;
1357
1358 EXCEPTION
1359
1360 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1361 x_return_status := G_RET_STS_ERROR;
1362
1363 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1364 x_return_status := G_RET_STS_UNEXP_ERROR;
1365
1366 WHEN OTHERS THEN
1367 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1368 p_msg_name => G_DB_ERROR,
1369 p_token1 => G_PROG_NAME_TOKEN,
1370 p_token1_value => l_program_name,
1371 p_token2 => G_SQLCODE_TOKEN,
1372 p_token2_value => sqlcode,
1373 p_token3 => G_SQLERRM_TOKEN,
1374 p_token3_value => sqlerrm);
1375
1376 x_return_status := G_RET_STS_UNEXP_ERROR;
1377
1378 END create_expense;
1379
1380
1381 ---------------------------
1382 -- PROCEDURE update_expense
1383 ---------------------------
1384 PROCEDURE update_expense (
1385 p_fee_id IN NUMBER
1386 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1387 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1388 ,x_return_status OUT NOCOPY VARCHAR2
1389 ,x_msg_count OUT NOCOPY NUMBER
1390 ,x_msg_data OUT NOCOPY VARCHAR2
1391 ) IS
1392
1393 l_program_name CONSTANT VARCHAR2(30) := 'update_expense';
1394 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1395
1396 l_expense_header_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1397 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1398
1399 BEGIN
1400
1401 l_expense_header_rec := p_expense_header_rec;
1402 l_expense_level_tbl := p_expense_level_tbl;
1403
1404 okl_lease_quote_cashflow_pvt.update_cashflow (
1405 p_api_version => G_API_VERSION
1406 ,p_init_msg_list => G_FALSE
1407 ,p_transaction_control => G_FALSE
1408 ,p_cashflow_header_rec => l_expense_header_rec
1409 ,p_cashflow_level_tbl => l_expense_level_tbl
1410 ,x_return_status => x_return_status
1411 ,x_msg_count => x_msg_count
1412 ,x_msg_data => x_msg_data
1413 );
1414
1415 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1416 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1417 ELSIF x_return_status = G_RET_STS_ERROR THEN
1418 RAISE OKL_API.G_EXCEPTION_ERROR;
1419 END IF;
1420
1421 EXCEPTION
1422
1423 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1424 x_return_status := G_RET_STS_ERROR;
1425
1426 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1427 x_return_status := G_RET_STS_UNEXP_ERROR;
1428
1429 WHEN OTHERS THEN
1430 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1431 p_msg_name => G_DB_ERROR,
1432 p_token1 => G_PROG_NAME_TOKEN,
1433 p_token1_value => l_program_name,
1434 p_token2 => G_SQLCODE_TOKEN,
1435 p_token2_value => sqlcode,
1436 p_token3 => G_SQLERRM_TOKEN,
1437 p_token3_value => sqlerrm);
1438
1439 x_return_status := G_RET_STS_UNEXP_ERROR;
1440
1441 END update_expense;
1442
1443 -----------------------
1444 -- PROCEDURE create_fee
1445 -----------------------
1446 PROCEDURE create_fee (
1447 p_api_version IN NUMBER
1448 ,p_init_msg_list IN VARCHAR2
1449 ,p_transaction_control IN VARCHAR2
1450 ,p_fee_rec IN fee_rec_type
1451 ,p_assoc_asset_tbl IN line_relation_tbl_type
1452 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1453 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1454 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1455 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1456 ,x_fee_id OUT NOCOPY NUMBER
1457 ,x_return_status OUT NOCOPY VARCHAR2
1458 ,x_msg_count OUT NOCOPY NUMBER
1459 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1460
1461 l_program_name CONSTANT VARCHAR2(30) := 'create_fee';
1462 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1463
1464 l_line_relation_tbl line_relation_tbl_type;
1465
1466 l_return_status VARCHAR2(1);
1467 l_derive_assoc_amt VARCHAR2(1);
1468
1469 l_fee_rec fee_rec_type;
1470 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1471 l_expense_hdr_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1472 l_p_id NUMBER;
1473 l_p_code VARCHAR2(30);
1474 BEGIN
1475
1476 IF p_transaction_control = G_TRUE THEN
1477 SAVEPOINT l_program_name;
1478 END IF;
1479
1480 IF p_init_msg_list = G_TRUE THEN
1481 FND_MSG_PUB.initialize;
1482 END IF;
1483
1484 l_fee_rec := p_fee_rec;
1485 l_expense_level_tbl := p_expense_level_tbl;
1486 l_expense_hdr_rec := p_expense_header_rec;
1487
1488 populate_fee_details (p_fee_rec => l_fee_rec,
1489 p_expense_header_rec => l_expense_hdr_rec,
1490 p_expense_level_tbl => l_expense_level_tbl,
1491 p_payment_level_tbl => p_payment_level_tbl,
1492 x_return_status => l_return_status);
1493 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1494 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1495 ELSIF l_return_status = G_RET_STS_ERROR THEN
1496 RAISE OKL_API.G_EXCEPTION_ERROR;
1497 END IF;
1498
1499 validate_quote_fee (p_fee_rec => l_fee_rec,
1500 p_link_asset_tbl => p_assoc_asset_tbl,
1501 p_payment_levels_tbl => p_payment_level_tbl,
1502 p_expense_header_rec => l_expense_hdr_rec,
1503 p_expense_levels_tbl => l_expense_level_tbl,
1504 x_return_status => l_return_status,
1505 x_msg_count => x_msg_count,
1506 x_msg_data => x_msg_data);
1507 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1508 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1509 ELSIF l_return_status = G_RET_STS_ERROR THEN
1510 RAISE OKL_API.G_EXCEPTION_ERROR;
1511 END IF;
1512
1513 IF p_assoc_asset_tbl.COUNT > 0 THEN
1514
1515 l_line_relation_tbl := p_assoc_asset_tbl;
1516
1517 validate_link_assets (
1518 p_fee_amount => l_fee_rec.fee_amount
1519 ,p_assoc_assets_tbl => l_line_relation_tbl
1520 ,x_derive_assoc_amt => l_derive_assoc_amt
1521 ,x_return_status => l_return_status
1522 );
1523
1524 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1525 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1526 ELSIF l_return_status = G_RET_STS_ERROR THEN
1527 RAISE OKL_API.G_EXCEPTION_ERROR;
1528 END IF;
1529
1530 IF (l_derive_assoc_amt = 'Y') THEN
1531
1532 process_link_asset_amounts (
1533 p_quote_id => l_fee_rec.parent_object_id
1534 ,p_fee_amount => l_fee_rec.fee_amount
1535 ,p_link_asset_tbl => l_line_relation_tbl
1536 ,p_derive_assoc_amt => 'Y'
1537 ,x_return_status => l_return_status
1538 ,x_msg_count => x_msg_count
1539 ,x_msg_data => x_msg_data
1540 );
1541
1542 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1543 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1544 ELSIF l_return_status = G_RET_STS_ERROR THEN
1545 RAISE OKL_API.G_EXCEPTION_ERROR;
1546 END IF;
1547
1548 END IF;
1549
1550 END IF;
1551 create_header (
1552 p_fee_rec => l_fee_rec
1553 ,x_fee_id => x_fee_id
1554 ,x_return_status => l_return_status
1555 ,x_msg_count => x_msg_count
1556 ,x_msg_data => x_msg_data
1557 );
1558
1559 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1560 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1561 ELSIF l_return_status = G_RET_STS_ERROR THEN
1562 RAISE OKL_API.G_EXCEPTION_ERROR;
1563 END IF;
1564
1565 IF l_line_relation_tbl.COUNT > 0 THEN
1566
1567 create_line_associations (
1568 p_fee_id => x_fee_id
1569 ,p_assoc_assets_tbl => l_line_relation_tbl
1570 ,x_return_status => l_return_status
1571 ,x_msg_count => x_msg_count
1572 ,x_msg_data => x_msg_data
1573 );
1574
1575 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1577 ELSIF l_return_status = G_RET_STS_ERROR THEN
1578 RAISE OKL_API.G_EXCEPTION_ERROR;
1579 END IF;
1580
1581 END IF;
1582
1583 IF (p_payment_level_tbl.COUNT > 0 AND p_payment_header_rec.stream_type_id IS NULL) THEN
1584 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1585 p_msg_name => 'OKL_SALES_NO_PAYMENTHEAD');
1586 RAISE OKL_API.G_EXCEPTION_ERROR;
1587 ELSIF (p_payment_header_rec.stream_type_id IS NOT NULL AND p_payment_level_tbl.COUNT = 0 ) THEN
1588 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1589 p_msg_name => 'OKL_SALES_NO_PAYMENTLINES');
1590 RAISE OKL_API.G_EXCEPTION_ERROR;
1591 END IF;
1592
1593 IF l_fee_rec.fee_type IN ('INCOME', 'MISCELLANEOUS', 'SEC_DEPOSIT') THEN
1594
1595 create_payment (
1596 p_fee_id => x_fee_id
1597 ,p_payment_header_rec => p_payment_header_rec
1598 ,p_payment_level_tbl => p_payment_level_tbl
1599 ,x_return_status => l_return_status
1600 ,x_msg_count => x_msg_count
1601 ,x_msg_data => x_msg_data
1602 );
1603
1604 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1605 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1606 ELSIF l_return_status = G_RET_STS_ERROR THEN
1607 RAISE OKL_API.G_EXCEPTION_ERROR;
1608 END IF;
1609
1610 END IF;
1611
1612 IF l_fee_rec.fee_type IN ('EXPENSE' , 'MISCELLANEOUS') THEN
1613
1614 create_expense (
1615 p_fee_id => x_fee_id
1616 ,p_expense_header_rec => l_expense_hdr_rec
1617 ,p_expense_level_tbl => l_expense_level_tbl
1618 ,x_return_status => l_return_status
1619 ,x_msg_count => x_msg_count
1620 ,x_msg_data => x_msg_data
1621 );
1622
1623 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1624 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1625 ELSIF l_return_status = G_RET_STS_ERROR THEN
1626 RAISE OKL_API.G_EXCEPTION_ERROR;
1627 END IF;
1628
1629 END IF;
1630
1631 /*
1632 SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
1633 FROM okl_lease_quotes_b where ID = l_fee_rec.parent_object_id;
1634
1635 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
1636 p_api_version => G_API_VERSION
1637 ,p_init_msg_list => G_FALSE
1638 ,x_return_status => x_return_status
1639 ,x_msg_count => x_msg_count
1640 ,x_msg_data => x_msg_data
1641 ,p_parent_object_code => l_p_code
1642 ,p_parent_object_id => l_p_id
1643 );
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 x_return_status := G_RET_STS_SUCCESS;
1651
1652 EXCEPTION
1653
1654 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1655
1656 IF p_transaction_control = G_TRUE THEN
1657 ROLLBACK TO l_program_name;
1658 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1659 END IF;
1660
1661 x_return_status := G_RET_STS_ERROR;
1662
1663 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1664
1665 IF p_transaction_control = G_TRUE THEN
1666 ROLLBACK TO l_program_name;
1667 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1668 END IF;
1669
1670 x_return_status := G_RET_STS_UNEXP_ERROR;
1671
1672 WHEN OTHERS THEN
1673
1674 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1675 p_msg_name => G_DB_ERROR,
1676 p_token1 => G_PROG_NAME_TOKEN,
1677 p_token1_value => l_api_name,
1678 p_token2 => G_SQLCODE_TOKEN,
1679 p_token2_value => sqlcode,
1680 p_token3 => G_SQLERRM_TOKEN,
1681 p_token3_value => sqlerrm);
1682
1683 IF p_transaction_control = G_TRUE THEN
1684 ROLLBACK TO l_program_name;
1685 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1686 END IF;
1687
1688 x_return_status := G_RET_STS_UNEXP_ERROR;
1689
1690 END create_fee;
1691
1692
1693 ------------------------------
1694 -- PROCEDURE sync_fee_values
1695 ------------------------------
1696 PROCEDURE sync_fee_values(x_fee_rec IN OUT NOCOPY fee_rec_type,
1697 p_input_rec IN fee_rec_type,
1698 x_return_status OUT NOCOPY VARCHAR2) IS
1699
1700 l_program_name CONSTANT VARCHAR2(30) := 'sync_fee_values';
1701 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1702
1703 BEGIN
1704
1705 IF (p_input_rec.rate_card_id IS NOT NULL) THEN
1706 x_fee_rec.rate_card_id := p_input_rec.rate_card_id;
1707 END IF;
1708
1709 IF (p_input_rec.rate_template_id IS NOT NULL) THEN
1710 x_fee_rec.rate_template_id := p_input_rec.rate_template_id;
1711 END IF;
1712
1713 IF (p_input_rec.structured_pricing IS NOT NULL) THEN
1714 x_fee_rec.structured_pricing := p_input_rec.structured_pricing;
1715 END IF;
1716
1717 IF (p_input_rec.target_arrears IS NOT NULL) THEN
1718 x_fee_rec.target_arrears := p_input_rec.target_arrears;
1719 END IF;
1720
1721 IF (p_input_rec.lease_rate_factor IS NOT NULL) THEN
1722 x_fee_rec.lease_rate_factor := p_input_rec.lease_rate_factor;
1723 END IF;
1724
1725 x_return_status := G_RET_STS_SUCCESS;
1726
1727 EXCEPTION
1728
1729 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1730 x_return_status := G_RET_STS_ERROR;
1731
1732 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1733 x_return_status := G_RET_STS_UNEXP_ERROR;
1734
1735 WHEN OTHERS THEN
1736 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1737 p_msg_name => G_DB_ERROR,
1738 p_token1 => G_PROG_NAME_TOKEN,
1739 p_token1_value => l_program_name,
1740 p_token2 => G_SQLCODE_TOKEN,
1741 p_token2_value => sqlcode,
1742 p_token3 => G_SQLERRM_TOKEN,
1743 p_token3_value => sqlerrm);
1744
1745 x_return_status := G_RET_STS_UNEXP_ERROR;
1746
1747 END sync_fee_values;
1748
1749 -----------------------
1750 -- PROCEDURE update_fee
1751 -----------------------
1752 PROCEDURE update_fee (
1753 p_api_version IN NUMBER
1754 ,p_init_msg_list IN VARCHAR2
1755 ,p_transaction_control IN VARCHAR2
1756 ,p_fee_rec IN fee_rec_type
1757 ,p_sync_fee_header IN VARCHAR2 DEFAULT 'Y'
1758 ,p_sync_line_relations IN VARCHAR2 DEFAULT 'N'
1759 ,x_return_status OUT NOCOPY VARCHAR2
1760 ,x_msg_count OUT NOCOPY NUMBER
1761 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1762
1763 l_program_name CONSTANT VARCHAR2(30) := 'update_fee';
1764 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1765
1766 l_fee_rec fee_rec_type;
1767 l_copy_relation_tbl line_relation_tbl_type;
1768
1769 l_line_relation_tbl assoc_asset_tbl_type;
1770 lx_line_relation_tbl assoc_asset_tbl_type;
1771 l_p_id NUMBER;
1772 l_p_code VARCHAR2(30);
1773
1774 BEGIN
1775
1776 IF p_transaction_control = G_TRUE THEN
1777 SAVEPOINT l_program_name;
1778 END IF;
1779
1780 IF p_init_msg_list = G_TRUE THEN
1781 FND_MSG_PUB.initialize;
1782 END IF;
1783
1784 get_fee_rec (p_fee_id => p_fee_rec.id
1785 ,x_fee_rec => l_fee_rec
1786 ,x_return_status => x_return_status);
1787 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1788 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1789 ELSIF x_return_status = G_RET_STS_ERROR THEN
1790 RAISE OKL_API.G_EXCEPTION_ERROR;
1791 END IF;
1792
1793 IF (p_sync_fee_header = 'Y') THEN
1794 -- Sync Fee Info
1795 sync_fee_values(x_fee_rec => l_fee_rec,
1796 p_input_rec => p_fee_rec,
1797 x_return_status => x_return_status);
1798
1799 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1800 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1801 ELSIF x_return_status = G_RET_STS_ERROR THEN
1802 RAISE OKL_API.G_EXCEPTION_ERROR;
1803 END IF;
1804
1805 -- Update Fee Header
1806 update_header (
1807 p_fee_rec => l_fee_rec
1808 ,x_return_status => x_return_status
1809 ,x_msg_count => x_msg_count
1810 ,x_msg_data => x_msg_data );
1811
1812 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1813 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1814 ELSIF x_return_status = G_RET_STS_ERROR THEN
1815 RAISE OKL_API.G_EXCEPTION_ERROR;
1816 END IF;
1817 END IF;
1818
1819 -- Fetch Line Relationships info
1820 IF (p_sync_line_relations = 'Y') THEN
1821 get_line_relations_tbl (p_fee_id => l_fee_rec.id
1822 ,x_line_relation_tbl => l_copy_relation_tbl
1823 ,x_return_status => x_return_status);
1824 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1825 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1826 ELSIF x_return_status = G_RET_STS_ERROR THEN
1827 RAISE OKL_API.G_EXCEPTION_ERROR;
1828 END IF;
1829
1830 IF (l_copy_relation_tbl.COUNT > 0) THEN
1831 process_link_asset_amounts (
1832 p_quote_id => l_fee_rec.parent_object_id
1833 ,p_fee_amount => l_fee_rec.fee_amount
1834 ,p_link_asset_tbl => l_copy_relation_tbl
1835 ,p_derive_assoc_amt => 'Y'
1836 ,p_override_pricing_type => 'Y'
1837 ,x_return_status => x_return_status
1838 ,x_msg_count => x_msg_count
1839 ,x_msg_data => x_msg_data );
1840
1841 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1842 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1843 ELSIF x_return_status = G_RET_STS_ERROR THEN
1844 RAISE OKL_API.G_EXCEPTION_ERROR;
1845 END IF;
1846
1847 FOR i IN l_copy_relation_tbl.FIRST .. l_copy_relation_tbl.LAST LOOP
1848 IF l_copy_relation_tbl.EXISTS(i) THEN
1849 l_line_relation_tbl(i).id := l_copy_relation_tbl(i).id;
1850 l_line_relation_tbl(i).object_version_number := l_copy_relation_tbl(i).object_version_number;
1851 l_line_relation_tbl(i).source_line_type := l_copy_relation_tbl(i).source_line_type;
1852 l_line_relation_tbl(i).source_line_id := l_copy_relation_tbl(i).source_line_id;
1853 l_line_relation_tbl(i).related_line_type := l_copy_relation_tbl(i).related_line_type;
1854 l_line_relation_tbl(i).related_line_id := l_copy_relation_tbl(i).related_line_id;
1855 l_line_relation_tbl(i).amount := l_copy_relation_tbl(i).amount;
1856 END IF;
1857 END LOOP;
1858
1859 IF (l_line_relation_tbl.COUNT > 0) THEN
1860 okl_lre_pvt.update_row (
1861 p_api_version => G_API_VERSION
1862 ,p_init_msg_list => G_FALSE
1863 ,x_return_status => x_return_status
1864 ,x_msg_count => x_msg_count
1865 ,x_msg_data => x_msg_data
1866 ,p_lrev_tbl => l_line_relation_tbl
1867 ,x_lrev_tbl => lx_line_relation_tbl);
1868
1869 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1870 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1871 ELSIF x_return_status = G_RET_STS_ERROR THEN
1872 RAISE OKL_API.G_EXCEPTION_ERROR;
1873 END IF;
1874 END IF;
1875 END IF;
1876 END IF;
1877
1878 /*
1879 SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
1880 FROM okl_lease_quotes_b where ID = l_fee_rec.parent_object_id;
1881
1882 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
1883 p_api_version => G_API_VERSION
1884 ,p_init_msg_list => G_FALSE
1885 ,x_return_status => x_return_status
1886 ,x_msg_count => x_msg_count
1887 ,x_msg_data => x_msg_data
1888 ,p_parent_object_code => l_p_code
1889 ,p_parent_object_id => l_p_id
1890 );
1891
1892 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
1893 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1894 ELSIF x_return_status = G_RET_STS_ERROR THEN
1895 RAISE OKL_API.G_EXCEPTION_ERROR;
1896 END IF;*/
1897
1898 x_return_status := G_RET_STS_SUCCESS;
1899
1900 EXCEPTION
1901
1902 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1903
1904 IF p_transaction_control = G_TRUE THEN
1905 ROLLBACK TO l_program_name;
1906 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1907 END IF;
1908
1909 x_return_status := G_RET_STS_ERROR;
1910
1911 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1912
1913 IF p_transaction_control = G_TRUE THEN
1914 ROLLBACK TO l_program_name;
1915 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1916 END IF;
1917
1918 x_return_status := G_RET_STS_UNEXP_ERROR;
1919
1920 WHEN OTHERS THEN
1921
1922 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1923 p_msg_name => G_DB_ERROR,
1924 p_token1 => G_PROG_NAME_TOKEN,
1925 p_token1_value => l_api_name,
1926 p_token2 => G_SQLCODE_TOKEN,
1927 p_token2_value => sqlcode,
1928 p_token3 => G_SQLERRM_TOKEN,
1929 p_token3_value => sqlerrm);
1930
1931 IF p_transaction_control = G_TRUE THEN
1932 ROLLBACK TO l_program_name;
1933 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1934 END IF;
1935
1936 x_return_status := G_RET_STS_UNEXP_ERROR;
1937
1938 END update_fee ;
1939
1940 -----------------------
1941 -- PROCEDURE update_fee
1942 -----------------------
1943 PROCEDURE update_fee (
1944 p_api_version IN NUMBER
1945 ,p_init_msg_list IN VARCHAR2
1946 ,p_transaction_control IN VARCHAR2
1947 ,p_fee_rec IN fee_rec_type
1948 ,p_assoc_asset_tbl IN line_relation_tbl_type
1949 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1950 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1951 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
1952 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
1953 ,x_return_status OUT NOCOPY VARCHAR2
1954 ,x_msg_count OUT NOCOPY NUMBER
1955 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
1956
1957 l_program_name CONSTANT VARCHAR2(30) := 'update_fee';
1958 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
1959
1960 l_line_relation_tbl line_relation_tbl_type;
1961 l_fee_id NUMBER;
1962 l_fee_type VARCHAR2(30);
1963 l_return_status VARCHAR2(1);
1964 l_derive_assoc_amt VARCHAR2(1);
1965
1966 l_fee_rec fee_rec_type;
1967 l_expense_level_tbl okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type;
1968 l_expense_hdr_rec okl_lease_quote_cashflow_pvt.cashflow_header_rec_type;
1969 l_p_id NUMBER;
1970 l_p_code VARCHAR2(30);
1971
1972
1973 --sechawla 5-aug-2010 9962315 : begin
1974 cursor l_okl_fees_b(cp_id in number) IS
1975 select fee_purpose_code
1976 FROM okl_fees_b
1977 where id = cp_id;
1978 l_fee_purpose_code varchar2(100) ;
1979 --sechawla 5-aug-2010 9962315 : end
1980
1981
1982 BEGIN
1983
1984 IF p_transaction_control = G_TRUE THEN
1985 SAVEPOINT l_program_name;
1986 END IF;
1987
1988 IF p_init_msg_list = G_TRUE THEN
1989 FND_MSG_PUB.initialize;
1990 END IF;
1991
1992 l_fee_rec := p_fee_rec;
1993 l_expense_level_tbl := p_expense_level_tbl;
1994 l_expense_hdr_rec := p_expense_header_rec;
1995
1996 --sechawla 5-aug-2010 9962315 : begin
1997 open l_okl_fees_b(l_fee_rec.id);
1998 fetch l_okl_fees_b into l_fee_purpose_code;
1999 close l_okl_fees_b;
2000
2001 l_fee_rec.fee_purpose_code := l_fee_purpose_code;
2002 --sechawla 5-aug-2010 9962315 : end
2003
2004
2005 populate_fee_details (p_fee_rec => l_fee_rec,
2006 p_expense_header_rec => l_expense_hdr_rec,
2007 p_expense_level_tbl => l_expense_level_tbl,
2008 p_payment_level_tbl => p_payment_level_tbl,
2009 x_return_status => l_return_status);
2010 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2011 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2012 ELSIF l_return_status = G_RET_STS_ERROR THEN
2013 RAISE OKL_API.G_EXCEPTION_ERROR;
2014 END IF;
2015
2016 validate_quote_fee (p_fee_rec => l_fee_rec,
2017 p_link_asset_tbl => p_assoc_asset_tbl,
2018 p_payment_levels_tbl => p_payment_level_tbl,
2019 p_expense_header_rec => l_expense_hdr_rec,
2020 p_expense_levels_tbl => l_expense_level_tbl,
2021 x_return_status => l_return_status,
2022 x_msg_count => x_msg_count,
2023 x_msg_data => x_msg_data);
2024 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2025 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2026 ELSIF l_return_status = G_RET_STS_ERROR THEN
2027 RAISE OKL_API.G_EXCEPTION_ERROR;
2028 END IF;
2029
2030 IF p_assoc_asset_tbl.COUNT > 0 THEN
2031
2032 l_line_relation_tbl := p_assoc_asset_tbl;
2033
2034 validate_link_assets (
2035 p_fee_amount => l_fee_rec.fee_amount
2036 ,p_assoc_assets_tbl => l_line_relation_tbl
2037 ,x_derive_assoc_amt => l_derive_assoc_amt
2038 ,x_return_status => l_return_status
2039 );
2040
2041 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2042 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2043 ELSIF l_return_status = G_RET_STS_ERROR THEN
2044 RAISE OKL_API.G_EXCEPTION_ERROR;
2045 END IF;
2046
2047 IF (l_derive_assoc_amt = 'Y') THEN
2048
2049 process_link_asset_amounts (
2050 p_quote_id => l_fee_rec.parent_object_id
2051 ,p_fee_amount => l_fee_rec.fee_amount
2052 ,p_link_asset_tbl => l_line_relation_tbl
2053 ,p_derive_assoc_amt => 'Y'
2054 ,x_return_status => l_return_status
2055 ,x_msg_count => x_msg_count
2056 ,x_msg_data => x_msg_data );
2057
2058 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2059 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2060 ELSIF l_return_status = G_RET_STS_ERROR THEN
2061 RAISE OKL_API.G_EXCEPTION_ERROR;
2062 END IF;
2063
2064 END IF;
2065
2066 END IF;
2067
2068 update_header (
2069 p_fee_rec => l_fee_rec
2070 ,x_return_status => l_return_status
2071 ,x_msg_count => x_msg_count
2072 ,x_msg_data => x_msg_data );
2073
2074 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2075 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2076 ELSIF l_return_status = G_RET_STS_ERROR THEN
2077 RAISE OKL_API.G_EXCEPTION_ERROR;
2078 END IF;
2079
2080 l_fee_id := l_fee_rec.id;
2081 l_fee_type := l_fee_rec.fee_type;
2082
2083 update_line_associations (
2084 p_fee_id => l_fee_id
2085 ,p_fee_type => l_fee_type
2086 ,p_assoc_assets_tbl => l_line_relation_tbl
2087 ,x_return_status => l_return_status
2088 ,x_msg_count => x_msg_count
2089 ,x_msg_data => x_msg_data
2090 );
2091
2092 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2093 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2094 ELSIF l_return_status = G_RET_STS_ERROR THEN
2095 RAISE OKL_API.G_EXCEPTION_ERROR;
2096 END IF;
2097
2098 IF (p_payment_level_tbl.COUNT > 0 AND p_payment_header_rec.stream_type_id IS NULL) THEN
2099 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2100 p_msg_name => 'OKL_SALES_NO_PAYMENTHEAD');
2101 RAISE OKL_API.G_EXCEPTION_ERROR;
2102 ELSIF (p_payment_header_rec.stream_type_id IS NOT NULL AND p_payment_level_tbl.COUNT = 0 ) THEN
2103 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2104 p_msg_name => 'OKL_SALES_NO_PAYMENTLINES');
2105 RAISE OKL_API.G_EXCEPTION_ERROR;
2106 END IF;
2107
2108 IF l_fee_rec.fee_type IN ('INCOME', 'MISCELLANEOUS', 'SEC_DEPOSIT') THEN
2109
2110 update_payment (
2111 p_fee_id => l_fee_id
2112 ,p_payment_header_rec => p_payment_header_rec
2113 ,p_payment_level_tbl => p_payment_level_tbl
2114 ,x_return_status => l_return_status
2115 ,x_msg_count => x_msg_count
2116 ,x_msg_data => x_msg_data
2117 );
2118
2119 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2120 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2121 ELSIF l_return_status = G_RET_STS_ERROR THEN
2122 RAISE OKL_API.G_EXCEPTION_ERROR;
2123 END IF;
2124
2125 END IF;
2126
2127 IF l_fee_rec.fee_type IN ('EXPENSE' , 'MISCELLANEOUS') THEN
2128
2129 update_expense (
2130 p_fee_id => l_fee_id
2131 ,p_expense_header_rec => l_expense_hdr_rec
2132 ,p_expense_level_tbl => l_expense_level_tbl
2133 ,x_return_status => l_return_status
2134 ,x_msg_count => x_msg_count
2135 ,x_msg_data => x_msg_data
2136 );
2137
2138 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2139 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2140 ELSIF l_return_status = G_RET_STS_ERROR THEN
2141 RAISE OKL_API.G_EXCEPTION_ERROR;
2142 END IF;
2143
2144 END IF;
2145
2146 /*
2147 SELECT parent_object_id,parent_object_code INTO l_p_id,l_p_code
2148 FROM okl_lease_quotes_b where ID = l_fee_rec.parent_object_id;
2149
2150 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
2151 p_api_version => G_API_VERSION
2152 ,p_init_msg_list => G_FALSE
2153 ,x_return_status => x_return_status
2154 ,x_msg_count => x_msg_count
2155 ,x_msg_data => x_msg_data
2156 ,p_parent_object_code => l_p_code
2157 ,p_parent_object_id => l_p_id
2158 );
2159
2160 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2161 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2162 ELSIF x_return_status = G_RET_STS_ERROR THEN
2163 RAISE OKL_API.G_EXCEPTION_ERROR;
2164 END IF;*/
2165
2166 x_return_status := G_RET_STS_SUCCESS;
2167
2168 EXCEPTION
2169
2170 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2171
2172 IF p_transaction_control = G_TRUE THEN
2173 ROLLBACK TO l_program_name;
2174 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2175 END IF;
2176
2177 x_return_status := G_RET_STS_ERROR;
2178
2179 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2180
2181 IF p_transaction_control = G_TRUE THEN
2182 ROLLBACK TO l_program_name;
2183 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2184 END IF;
2185
2186 x_return_status := G_RET_STS_UNEXP_ERROR;
2187
2188 WHEN OTHERS THEN
2189
2190 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2191 p_msg_name => G_DB_ERROR,
2192 p_token1 => G_PROG_NAME_TOKEN,
2193 p_token1_value => l_api_name,
2194 p_token2 => G_SQLCODE_TOKEN,
2195 p_token2_value => sqlcode,
2196 p_token3 => G_SQLERRM_TOKEN,
2197 p_token3_value => sqlerrm);
2198
2199 IF p_transaction_control = G_TRUE THEN
2200 ROLLBACK TO l_program_name;
2201 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2202 END IF;
2203
2204 x_return_status := G_RET_STS_UNEXP_ERROR;
2205
2206 END update_fee;
2207
2208 -------------------------------
2209 -- PROCEDURE get_lrship_tbl
2210 -------------------------------
2211 PROCEDURE get_lrship_tbl (p_source_fee_id IN NUMBER
2212 ,p_target_fee_id IN NUMBER
2213 ,x_lrship_tbl OUT NOCOPY lr_tbl_type
2214 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
2215
2216 l_program_name CONSTANT VARCHAR2(30) := 'get_lrship_tbl';
2217 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2218 i BINARY_INTEGER := 0;
2219
2220 CURSOR c_db_lrships IS
2221 SELECT
2222 attribute_category
2223 ,attribute1
2224 ,attribute2
2225 ,attribute3
2226 ,attribute4
2227 ,attribute5
2228 ,attribute6
2229 ,attribute7
2230 ,attribute8
2231 ,attribute9
2232 ,attribute10
2233 ,attribute11
2234 ,attribute12
2235 ,attribute13
2236 ,attribute14
2237 ,attribute15
2238 ,source_line_type
2239 ,source_line_id
2240 ,related_line_type
2241 ,related_line_id
2242 ,amount
2243 ,short_description
2244 ,description
2245 ,comments
2246 --added following field by jjuneja
2247 ,origination_income
2248 from okl_line_relationships_v
2249 where source_line_type = 'ASSET'
2250 and related_line_id = p_source_fee_id;
2251
2252 BEGIN
2253 FOR l_db_lrships IN c_db_lrships LOOP
2254 x_lrship_tbl(i).attribute_category := l_db_lrships.attribute_category;
2255 x_lrship_tbl(i).attribute1 := l_db_lrships.attribute1;
2256 x_lrship_tbl(i).attribute2 := l_db_lrships.attribute2;
2257 x_lrship_tbl(i).attribute3 := l_db_lrships.attribute3;
2258 x_lrship_tbl(i).attribute4 := l_db_lrships.attribute4;
2259 x_lrship_tbl(i).attribute5 := l_db_lrships.attribute5;
2260 x_lrship_tbl(i).attribute6 := l_db_lrships.attribute6;
2261 x_lrship_tbl(i).attribute7 := l_db_lrships.attribute7;
2262 x_lrship_tbl(i).attribute8 := l_db_lrships.attribute8;
2263 x_lrship_tbl(i).attribute9 := l_db_lrships.attribute9;
2264 x_lrship_tbl(i).attribute10 := l_db_lrships.attribute10;
2265 x_lrship_tbl(i).attribute11 := l_db_lrships.attribute11;
2266 x_lrship_tbl(i).attribute12 := l_db_lrships.attribute12;
2267 x_lrship_tbl(i).attribute13 := l_db_lrships.attribute13;
2268 x_lrship_tbl(i).attribute14 := l_db_lrships.attribute14;
2269 x_lrship_tbl(i).attribute15 := l_db_lrships.attribute15;
2270 x_lrship_tbl(i).source_line_type := l_db_lrships.source_line_type;
2271 x_lrship_tbl(i).source_line_id := l_db_lrships.source_line_id ;
2272 x_lrship_tbl(i).related_line_type := l_db_lrships.related_line_type;
2273 x_lrship_tbl(i).related_line_id := p_target_fee_id;
2274 x_lrship_tbl(i).amount := l_db_lrships.amount;
2275 x_lrship_tbl(i).short_description := l_db_lrships.short_description;
2276 x_lrship_tbl(i).description := l_db_lrships.description;
2277 x_lrship_tbl(i).comments := l_db_lrships.comments;
2278 --added by jjuneja
2279 x_lrship_tbl(i).origination_income := l_db_lrships.origination_income;
2280 --end of addition by jjuneja
2281 i := i + 1;
2282 END LOOP;
2283
2284 x_return_status := G_RET_STS_SUCCESS;
2285 EXCEPTION
2286
2287 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2288 x_return_status := G_RET_STS_ERROR;
2289
2290 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2291 x_return_status := G_RET_STS_UNEXP_ERROR;
2292
2293 WHEN OTHERS THEN
2294 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2295 p_msg_name => G_DB_ERROR,
2296 p_token1 => G_PROG_NAME_TOKEN,
2297 p_token1_value => l_program_name,
2298 p_token2 => G_SQLCODE_TOKEN,
2299 p_token2_value => sqlcode,
2300 p_token3 => G_SQLERRM_TOKEN,
2301 p_token3_value => sqlerrm);
2302
2303 x_return_status := G_RET_STS_UNEXP_ERROR;
2304
2305 END get_lrship_tbl;
2306
2307 -----------------------------------
2308 -- PROCEDURE copy_line_associations
2309 -----------------------------------
2310 PROCEDURE copy_line_associations( p_source_fee_id IN NUMBER,
2311 p_target_fee_id IN NUMBER,
2312 x_return_status OUT NOCOPY VARCHAR2,
2313 x_msg_count OUT NOCOPY VARCHAR2,
2314 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2315
2316 l_program_name CONSTANT VARCHAR2(30) := 'copy_line_associations';
2317 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2318
2319 l_lrship_tbl lr_tbl_type;
2320 lx_lrship_tbl lr_tbl_type;
2321
2322 ln_target_quote_id NUMBER;
2323
2324 CURSOR c_get_new_asset_id (p_source_asset_id IN NUMBER,
2325 p_target_quote_id In NUMBER) IS
2326 SELECT id
2327 FROM OKL_ASSETS_B
2328 WHERE ORIG_ASSET_ID = p_source_asset_id
2329 AND PARENT_OBJECT_ID = p_target_quote_id;
2330
2331 BEGIN
2332 -- Get line relationships table
2333 get_lrship_tbl (p_source_fee_id => p_source_fee_id,
2334 p_target_fee_id => p_target_fee_id,
2335 x_lrship_tbl => l_lrship_tbl,
2336 x_return_status => x_return_status);
2337 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2338 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2339 ELSIF x_return_status = G_RET_STS_ERROR THEN
2340 RAISE OKL_API.G_EXCEPTION_ERROR;
2341 END IF;
2342
2343 SELECT PARENT_OBJECT_ID
2344 INTO ln_target_quote_id
2345 FROM OKL_FEES_B
2346 WHERE ID = p_target_fee_id;
2347
2348 -- Set the original asset id for the records
2349 IF (l_lrship_tbl.COUNT > 0) THEN
2350 FOR i IN l_lrship_tbl.FIRST .. l_lrship_tbl.LAST LOOP
2351 IF l_lrship_tbl.EXISTS(i) THEN
2352 OPEN c_get_new_asset_id(p_source_asset_id => l_lrship_tbl(i).source_line_id,
2353 p_target_quote_id => ln_target_quote_id);
2354 FETCH c_get_new_asset_id INTO l_lrship_tbl(i).source_line_id;
2355 CLOSE c_get_new_asset_id;
2356 END IF;
2357 END LOOP;
2358
2359 okl_lre_pvt.insert_row (
2360 p_api_version => G_API_VERSION
2361 ,p_init_msg_list => G_FALSE
2362 ,x_return_status => x_return_status
2363 ,x_msg_count => x_msg_count
2364 ,x_msg_data => x_msg_data
2365 ,p_lrev_tbl => l_lrship_tbl
2366 ,x_lrev_tbl => lx_lrship_tbl );
2367
2368 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2369 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2370 ELSIF x_return_status = G_RET_STS_ERROR THEN
2371 RAISE OKL_API.G_EXCEPTION_ERROR;
2372 END IF;
2373 END IF;
2374
2375 x_return_status := G_RET_STS_SUCCESS;
2376
2377 EXCEPTION
2378
2379 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2380
2381 x_return_status := G_RET_STS_ERROR;
2382
2383 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2384
2385 x_return_status := G_RET_STS_UNEXP_ERROR;
2386
2387 WHEN OTHERS THEN
2388
2389 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2390 p_msg_name => G_DB_ERROR,
2391 p_token1 => G_PROG_NAME_TOKEN,
2392 p_token1_value => l_api_name,
2393 p_token2 => G_SQLCODE_TOKEN,
2394 p_token2_value => sqlcode,
2395 p_token3 => G_SQLERRM_TOKEN,
2396 p_token3_value => sqlerrm);
2397
2398 x_return_status := G_RET_STS_UNEXP_ERROR;
2399 END copy_line_associations;
2400
2401 --------------------------
2402 -- PROCEDURE duplicate_fee
2403 --------------------------
2404 PROCEDURE duplicate_fee (
2405 p_api_version IN NUMBER
2406 ,p_init_msg_list IN VARCHAR2
2407 ,p_transaction_control IN VARCHAR2
2408 ,p_source_fee_id IN NUMBER
2409 ,p_fee_rec IN fee_rec_type
2410 ,p_assoc_asset_tbl IN line_relation_tbl_type
2411 ,p_payment_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
2412 ,p_payment_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
2413 ,p_expense_header_rec IN okl_lease_quote_cashflow_pvt.cashflow_header_rec_type
2414 ,p_expense_level_tbl IN okl_lease_quote_cashflow_pvt.cashflow_level_tbl_type
2415 ,x_fee_id OUT NOCOPY NUMBER
2416 ,x_return_status OUT NOCOPY VARCHAR2
2417 ,x_msg_count OUT NOCOPY NUMBER
2418 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
2419
2420 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_fee';
2421 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2422
2423 l_line_relation_tbl line_relation_tbl_type;
2424 lx_line_relation_tbl line_relation_tbl_type;
2425
2426 l_return_status VARCHAR2(1);
2427 l_derive_assoc_amt VARCHAR2(1);
2428
2429 BEGIN
2430
2431 IF p_transaction_control = G_TRUE THEN
2432 SAVEPOINT l_program_name;
2433 END IF;
2434
2435 IF p_init_msg_list = G_TRUE THEN
2436 FND_MSG_PUB.initialize;
2437 END IF;
2438
2439 create_fee (
2440 p_api_version => G_API_VERSION
2441 ,p_init_msg_list => G_FALSE
2442 ,p_transaction_control => G_FALSE
2443 ,p_fee_rec => p_fee_rec
2444 ,p_assoc_asset_tbl => p_assoc_asset_tbl
2445 ,p_payment_header_rec => p_payment_header_rec
2446 ,p_payment_level_tbl => p_payment_level_tbl
2447 ,p_expense_header_rec => p_expense_header_rec
2448 ,p_expense_level_tbl => p_expense_level_tbl
2449 ,x_fee_id => x_fee_id
2450 ,x_return_status => x_return_status
2451 ,x_msg_count => x_msg_count
2452 ,x_msg_data => x_msg_data);
2453
2454 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2455 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2456 ELSIF x_return_status = G_RET_STS_ERROR THEN
2457 RAISE OKL_API.G_EXCEPTION_ERROR;
2458 END IF;
2459
2460 x_return_status := G_RET_STS_SUCCESS;
2461
2462 EXCEPTION
2463
2464 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2465
2466 IF p_transaction_control = G_TRUE THEN
2467 ROLLBACK TO l_program_name;
2468 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2469 END IF;
2470
2471 x_return_status := G_RET_STS_ERROR;
2472
2473 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2474
2475 IF p_transaction_control = G_TRUE THEN
2476 ROLLBACK TO l_program_name;
2477 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2478 END IF;
2479
2480 x_return_status := G_RET_STS_UNEXP_ERROR;
2481
2482 WHEN OTHERS THEN
2483
2484 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2485 p_msg_name => G_DB_ERROR,
2486 p_token1 => G_PROG_NAME_TOKEN,
2487 p_token1_value => l_api_name,
2488 p_token2 => G_SQLCODE_TOKEN,
2489 p_token2_value => sqlcode,
2490 p_token3 => G_SQLERRM_TOKEN,
2491 p_token3_value => sqlerrm);
2492
2493 IF p_transaction_control = G_TRUE THEN
2494 ROLLBACK TO l_program_name;
2495 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2496 END IF;
2497
2498 x_return_status := G_RET_STS_UNEXP_ERROR;
2499
2500 END duplicate_fee;
2501
2502 --------------------------
2503 -- PROCEDURE duplicate_fee
2504 --------------------------
2505 PROCEDURE duplicate_fee (
2506 p_api_version IN NUMBER
2507 ,p_init_msg_list IN VARCHAR2
2508 ,p_transaction_control IN VARCHAR2
2509 ,p_source_fee_id IN NUMBER
2510 ,p_target_quote_id IN NUMBER
2511 ,x_fee_id OUT NOCOPY NUMBER
2512 ,x_return_status OUT NOCOPY VARCHAR2
2513 ,x_msg_count OUT NOCOPY NUMBER
2514 ,x_msg_data OUT NOCOPY VARCHAR2
2515 ) IS
2516
2517 l_program_name CONSTANT VARCHAR2(30) := 'duplicate_fee';
2518 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2519
2520 l_fee_rec okl_fee_pvt.feev_rec_type;
2521 lx_fee_rec okl_fee_pvt.feev_rec_type;
2522
2523 lb_copy_cashflow BOOLEAN := TRUE;
2524 lb_config_fee BOOLEAN := FALSE;
2525 lv_pricing_type_equal VARCHAR2(1) := 'Y';
2526 lb_copy_lr BOOLEAN := TRUE;
2527
2528 ld_src_start_date DATE;
2529 ld_tgt_start_date DATE;
2530 ln_src_pdt_id NUMBER;
2531 ln_tgt_pdt_id NUMBER;
2532 l_parent_object_code okl_lease_quotes_b.parent_object_code%TYPE;
2533 ln_src_eot_id NUMBER;
2534 ln_tgt_eot_id NUMBER;
2535
2536 BEGIN
2537
2538 IF p_transaction_control = G_TRUE THEN
2539 SAVEPOINT l_program_name;
2540 END IF;
2541
2542 IF p_init_msg_list = G_TRUE THEN
2543 FND_MSG_PUB.initialize;
2544 END IF;
2545
2546 get_fee_rec (
2547 p_fee_id => p_source_fee_id
2548 ,x_fee_rec => l_fee_rec
2549 ,x_return_status => x_return_status);
2550
2551 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2552 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2553 ELSIF x_return_status = G_RET_STS_ERROR THEN
2554 RAISE OKL_API.G_EXCEPTION_ERROR;
2555 END IF;
2556
2557 -- Check if the Source and Target Quote's Pricing type are equal and if it
2558 -- is a Configuration fee, cash flows are not copied.
2559 IF l_fee_rec.fee_type IN ('FINANCED', 'CAPITALIZED', 'ROLLOVER') THEN
2560 lb_config_fee := TRUE;
2561 END IF;
2562 --
2563 SELECT expected_start_date,
2564 product_id,
2565 parent_object_code,
2566 end_of_term_option_id
2567 INTO ld_tgt_start_date, ln_tgt_pdt_id,l_parent_object_code, ln_tgt_eot_id
2568 FROM
2569 okl_lease_quotes_b
2570 WHERE
2571 id = p_target_quote_id;
2572 --
2573 IF (lb_config_fee) THEN
2574 lv_pricing_type_equal := is_pricing_method_equal(p_source_quote_id => l_fee_rec.parent_object_id,
2575 p_target_quote_id => p_target_quote_id);
2576 ---
2577 IF (l_parent_object_code <> 'LEASEAPP' AND lv_pricing_type_equal = 'N') THEN
2578 lb_copy_cashflow := FALSE;
2579
2580 -- Nullify Pricing Params when the Pricing method is changed
2581 l_fee_rec.structured_pricing := null;
2582 l_fee_rec.target_arrears := null;
2583 l_fee_rec.lease_rate_factor := null;
2584 l_fee_rec.target_frequency := null;
2585 l_fee_rec.target_amount := null;
2586 --Bug # 5021937 start
2587 --Not nullfying all Pricing Parameters
2588 l_fee_rec.rate_card_id := null;
2589 l_fee_rec.rate_template_id := null;
2590 l_fee_rec.payment_type_id := null;
2591 --Bug # 5021937 end
2592 END IF;
2593 END IF;
2594 -- End
2595
2596 -- Assign the target quote id to parent object
2597 l_fee_rec.parent_object_id := p_target_quote_id;
2598 l_fee_rec.id := null;
2599
2600 create_header (
2601 p_fee_rec => l_fee_rec
2602 ,x_fee_id => x_fee_id
2603 ,x_return_status => x_return_status
2604 ,x_msg_count => x_msg_count
2605 ,x_msg_data => x_msg_data );
2606
2607 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2608 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2609 ELSIF x_return_status = G_RET_STS_ERROR THEN
2610 RAISE OKL_API.G_EXCEPTION_ERROR;
2611 END IF;
2612
2613 -- Validation to check if the product and expected start date for source
2614 -- and target contracts are equal, if not cash flows are not copied.
2615 SELECT quote.expected_start_date,
2616 quote.product_id,
2617 quote.end_of_term_option_id
2618 INTO ld_src_start_date, ln_src_pdt_id, ln_src_eot_id
2619 FROM
2620 okl_fees_b fee,
2621 okl_lease_quotes_b quote
2622 WHERE
2623 fee.id = p_source_fee_id
2624 AND fee.parent_object_id = quote.id
2625 AND fee.parent_object_code = 'LEASEQUOTE';
2626
2627
2628 IF l_parent_object_code = 'LEASEAPP' THEN
2629 lb_copy_cashflow := TRUE;
2630 END IF;
2631
2632 IF ((ld_src_start_date <> ld_tgt_start_date) OR (ln_src_pdt_id <> ln_tgt_pdt_id)) THEN
2633 lb_copy_cashflow := FALSE;
2634 END IF;
2635 -- End
2636
2637 IF (ln_src_eot_id <> ln_tgt_eot_id) THEN
2638 lb_copy_lr := FALSE;
2639 END IF;
2640
2641 IF (lb_copy_lr) THEN
2642 copy_line_associations( p_source_fee_id => p_source_fee_id,
2643 p_target_fee_id => x_fee_id,
2644 x_return_status => x_return_status,
2645 x_msg_count => x_msg_count,
2646 x_msg_data => x_msg_data );
2647 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2648 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2649 ELSIF x_return_status = G_RET_STS_ERROR THEN
2650 RAISE OKL_API.G_EXCEPTION_ERROR;
2651 END IF;
2652 END IF;
2653
2654 IF (lb_copy_cashflow) THEN
2655 okl_lease_quote_cashflow_pvt.duplicate_cashflows (
2656 p_api_version => G_API_VERSION
2657 ,p_init_msg_list => G_FALSE
2658 ,p_transaction_control => G_FALSE
2659 ,p_source_object_code => 'QUOTED_FEE'
2660 ,p_source_object_id => p_source_fee_id
2661 ,p_target_object_id => x_fee_id
2662 ,p_quote_id => p_target_quote_id
2663 ,x_return_status => x_return_status
2664 ,x_msg_count => x_msg_count
2665 ,x_msg_data => x_msg_data
2666 );
2667
2668 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2669 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2670 ELSIF x_return_status = G_RET_STS_ERROR THEN
2671 RAISE OKL_API.G_EXCEPTION_ERROR;
2672 END IF;
2673 END IF;
2674
2675 EXCEPTION
2676
2677 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2678
2679 IF p_transaction_control = G_TRUE THEN
2680 ROLLBACK TO l_program_name;
2681 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2682 END IF;
2683
2684 x_return_status := G_RET_STS_ERROR;
2685
2686 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2687
2688 IF p_transaction_control = G_TRUE THEN
2689 ROLLBACK TO l_program_name;
2690 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2691 END IF;
2692
2693 x_return_status := G_RET_STS_UNEXP_ERROR;
2694
2695 WHEN OTHERS THEN
2696
2697 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2698 p_msg_name => G_DB_ERROR,
2699 p_token1 => G_PROG_NAME_TOKEN,
2700 p_token1_value => l_api_name,
2701 p_token2 => G_SQLCODE_TOKEN,
2702 p_token2_value => sqlcode,
2703 p_token3 => G_SQLERRM_TOKEN,
2704 p_token3_value => sqlerrm);
2705
2706 IF p_transaction_control = G_TRUE THEN
2707 ROLLBACK TO l_program_name;
2708 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2709 END IF;
2710
2711 x_return_status := G_RET_STS_UNEXP_ERROR;
2712
2713 END duplicate_fee;
2714
2715
2716 -----------------------
2717 -- PROCEDURE delete_fee
2718 -----------------------
2719 PROCEDURE delete_fee (
2720 p_api_version IN NUMBER
2721 ,p_init_msg_list IN VARCHAR2
2722 ,p_transaction_control IN VARCHAR2
2723 ,p_fee_id IN NUMBER
2724 ,x_return_status OUT NOCOPY VARCHAR2
2725 ,x_msg_count OUT NOCOPY NUMBER
2726 ,x_msg_data OUT NOCOPY VARCHAR2
2727 ) IS
2728
2729 l_program_name CONSTANT VARCHAR2(30) := 'delete_fee';
2730 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_program_name;
2731
2732 l_fee_type VARCHAR2(30);
2733 l_assoc_asset_tbl assoc_asset_tbl_type;
2734 l_fee_rec fee_rec_type;
2735 i BINARY_INTEGER;
2736 l_p_id NUMBER;
2737 l_p_code VARCHAR2(30);
2738
2739 CURSOR c_fee_sublines (p_fee_type VARCHAR2) IS
2740 SELECT id
2741 FROM okl_line_relationships_b
2742 WHERE related_line_type = p_fee_type
2743 AND related_line_id = p_fee_id;
2744
2745 BEGIN
2746
2747 IF p_transaction_control = G_TRUE THEN
2748 SAVEPOINT l_program_name;
2749 END IF;
2750
2751 IF p_init_msg_list = G_TRUE THEN
2752 FND_MSG_PUB.initialize;
2753 END IF;
2754
2755 SELECT fee_type INTO l_fee_type FROM okl_fees_b WHERE id = p_fee_id;
2756
2757 i := 0;
2758 FOR l_assoc_assets IN c_fee_sublines (p_fee_type => l_fee_type) LOOP
2759 l_assoc_asset_tbl(i).id := l_assoc_assets.id;
2760 i := i + 1;
2761 END LOOP;
2762
2763 IF l_assoc_asset_tbl.COUNT > 0 THEN
2764
2765 okl_lre_pvt.delete_row (
2766 p_api_version => G_API_VERSION
2767 ,p_init_msg_list => G_FALSE
2768 ,x_return_status => x_return_status
2769 ,x_msg_count => x_msg_count
2770 ,x_msg_data => x_msg_data
2771 ,p_lrev_tbl => l_assoc_asset_tbl
2772 );
2773
2774 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2775 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2776 ELSIF x_return_status = G_RET_STS_ERROR THEN
2777 RAISE OKL_API.G_EXCEPTION_ERROR;
2778 END IF;
2779
2780 END IF;
2781
2782 okl_lease_quote_cashflow_pvt.delete_cashflows (
2783 p_api_version => G_API_VERSION
2784 ,p_init_msg_list => G_FALSE
2785 ,p_transaction_control => G_FALSE
2786 ,p_source_object_code => 'QUOTED_FEE'
2787 ,p_source_object_id => p_fee_id
2788 ,x_return_status => x_return_status
2789 ,x_msg_count => x_msg_count
2790 ,x_msg_data => x_msg_data
2791 );
2792
2793 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2794 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2795 ELSIF x_return_status = G_RET_STS_ERROR THEN
2796 RAISE OKL_API.G_EXCEPTION_ERROR;
2797 END IF;
2798
2799 l_fee_rec.id := p_fee_id;
2800
2801 okl_fee_pvt.delete_row (
2802 p_api_version => G_API_VERSION
2803 ,p_init_msg_list => G_FALSE
2804 ,x_return_status => x_return_status
2805 ,x_msg_count => x_msg_count
2806 ,x_msg_data => x_msg_data
2807 ,p_feev_rec => l_fee_rec
2808 );
2809
2810 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2811 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2812 ELSIF x_return_status = G_RET_STS_ERROR THEN
2813 RAISE OKL_API.G_EXCEPTION_ERROR;
2814 END IF;
2815
2816 /*
2817 SELECT qte.parent_object_id,qte.parent_object_code
2818 INTO l_p_id,l_p_code
2819 FROM okl_lease_quotes_b qte,
2820 okl_fees_b fee
2821 WHERE fee.parent_object_id = qte.id
2822 AND fee.parent_object_code= 'LEASEQUOTE'
2823 AND fee.ID = p_fee_id;
2824 OKL_LEASE_QUOTE_PRICING_PVT.handle_parent_object_status(
2825 p_api_version => G_API_VERSION
2826 ,p_init_msg_list => G_FALSE
2827 ,x_return_status => x_return_status
2828 ,x_msg_count => x_msg_count
2829 ,x_msg_data => x_msg_data
2830 ,p_parent_object_code => l_p_code
2831 ,p_parent_object_id => l_p_id
2832 );
2833
2834 IF x_return_status = G_RET_STS_UNEXP_ERROR THEN
2835 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2836 ELSIF x_return_status = G_RET_STS_ERROR THEN
2837 RAISE OKL_API.G_EXCEPTION_ERROR;
2838 END IF;*/
2839
2840 x_return_status := G_RET_STS_SUCCESS;
2841
2842 EXCEPTION
2843
2844 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2845
2846 IF p_transaction_control = G_TRUE THEN
2847 ROLLBACK TO l_program_name;
2848 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2849 END IF;
2850
2851 x_return_status := G_RET_STS_ERROR;
2852
2853 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2854
2855 IF p_transaction_control = G_TRUE THEN
2856 ROLLBACK TO l_program_name;
2857 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2858 END IF;
2859
2860 x_return_status := G_RET_STS_UNEXP_ERROR;
2861
2862 WHEN OTHERS THEN
2863
2864 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2865 p_msg_name => G_DB_ERROR,
2866 p_token1 => G_PROG_NAME_TOKEN,
2867 p_token1_value => l_api_name,
2868 p_token2 => G_SQLCODE_TOKEN,
2869 p_token2_value => sqlcode,
2870 p_token3 => G_SQLERRM_TOKEN,
2871 p_token3_value => sqlerrm);
2872
2873 IF p_transaction_control = G_TRUE THEN
2874 ROLLBACK TO l_program_name;
2875 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2876 END IF;
2877
2878 x_return_status := G_RET_STS_UNEXP_ERROR;
2879
2880 END delete_fee;
2881
2882 END OKL_LEASE_QUOTE_FEE_PVT;