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