[Home] [Help]
PACKAGE BODY: APPS.FA_C_INSURE
Source
4 g_log_level_rec fa_api_types.log_level_rec_type;
1 PACKAGE BODY FA_C_INSURE AS
2 /* $Header: faxinsub.pls 120.9 2009/03/27 03:26:27 bridgway ship $ */
3
5
6 PROCEDURE plsqlmsg (p_msg IN VARCHAR2);
7
8 PROCEDURE plsqlmsg_put (p_msg IN VARCHAR2);
9
10 PROCEDURE Get_Period_Counters_Proc
11 (p_asset_book IN VARCHAR2,
12 p_year IN VARCHAR2,
13 px_last_period_closed IN OUT NOCOPY NUMBER,
14 px_last_period_closed_date IN OUT NOCOPY DATE,
15 px_year_date_start IN OUT NOCOPY DATE,
16 px_year_counter_start IN OUT NOCOPY NUMBER,
17 px_year_date_end IN OUT NOCOPY DATE,
18 px_year_counter_end IN OUT NOCOPY NUMBER,
19 px_year_effective_end IN OUT NOCOPY DATE,
20 px_year_effective_start IN OUT NOCOPY DATE,
21 px_year_prev_end_date IN OUT NOCOPY DATE
22 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type);
23
24 PROCEDURE insert_values_record
25 (p_pol_asset_policy_id IN NUMBER,
26 px_indexation_id IN OUT NOCOPY NUMBER,
27 p_pol_vendor_id IN NUMBER,
28 p_pol_policy_number IN VARCHAR2,
29 p_pol_asset_id IN NUMBER,
30 p_year IN NUMBER,
31 p_last_period_closed_date IN DATE,
32 p_pol_price_index_id IN NUMBER,
33 p_pol_price_index_value IN NUMBER,
34 p_cal_insurance_value IN NUMBER
35 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type);
36
37 PROCEDURE update_policies_record
38 (p_pol_asset_policy_id IN NUMBER,
39 p_pol_policy_number IN VARCHAR2,
40 p_pol_asset_id IN NUMBER,
41 p_cal_insurance_value IN NUMBER,
42 p_indexation_id IN NUMBER,
43 p_new_price_index_value IN NUMBER,
44 p_pol_retirement_value IN NUMBER,
45 p_last_period_closed_date IN DATE
46 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type);
47
48 PROCEDURE Get_New_Price_index_Proc
49 (p_pol_price_index_id IN NUMBER,
50 px_price_index_value IN OUT NOCOPY NUMBER,
51 px_price_index_id IN OUT NOCOPY NUMBER,
52 p_year_date_end IN DATE
53 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type);
54
55 PROCEDURE process_adjustments
56 (transaction_id NUMBER,
57 pol_asset_id NUMBER,
58 pol_policy_number VARCHAR2,
59 last_period_closed NUMBER,
63 pol_calculation_method VARCHAR2,
60 last_period_closed_date DATE,
61 p_asset_book IN VARCHAR2,
62 p_year IN VARCHAR2,
64 px_pol_insurance_value IN OUT NOCOPY NUMBER,
65 year_counter_end NUMBER,
66 pol_swiss_building VARCHAR2,
67 px_cal_insurance_value IN OUT NOCOPY NUMBER,
68 pol_price_index_value NUMBER,
69 new_price_index_value NUMBER,
70 pol_base_index_year VARCHAR2,
71 pol_price_index_id NUMBER,
72 pol_base_index_date DATE,
73 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
74 pol_indexation_date DATE,
75 px_pol_retirement_value IN OUT NOCOPY NUMBER,
76 pol_period_fully_reserved NUMBER
77 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type);
78
79 PROCEDURE process_retirements
80 (pol_asset_id NUMBER,
81 pol_policy_number VARCHAR2,
82 last_period_closed NUMBER,
83 last_period_closed_date DATE,
84 p_asset_book IN VARCHAR2,
85 p_year IN VARCHAR2,
86 pol_calculation_method VARCHAR2,
87 px_pol_insurance_value IN OUT NOCOPY NUMBER,
88 year_counter_end NUMBER,
89 pol_swiss_building VARCHAR2,
90 px_cal_insurance_value IN OUT NOCOPY NUMBER,
91 pol_price_index_value NUMBER,
92 new_price_index_value NUMBER,
93 pol_base_index_year VARCHAR2,
94 pol_price_index_id NUMBER,
95 pol_base_index_date DATE,
96 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
97 pol_indexation_date DATE,
98 transaction_type VARCHAR2,
99 bks_cost NUMBER,
100 transaction_id NUMBER,
101 px_pol_retirement_value IN OUT NOCOPY NUMBER,
102 px_reinstatement_ret_type IN OUT NOCOPY VARCHAR2,
103 px_retirement_date IN OUT NOCOPY DATE,
104 px_reinstatement_without_ret
105 IN OUT NOCOPY VARCHAR2,
106 pol_period_fully_reserved NUMBER
107 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type);
108
109 PROCEDURE Get_base_index_value
113 px_base_price_index_value IN OUT NOCOPY NUMBER,
110 (p_asset_book IN VARCHAR2,
111 pol_asset_id NUMBER,
112 pol_base_index_date DATE,
114 pol_price_index_id NUMBER,
115 px_base_price_index_id IN OUT NOCOPY NUMBER
116 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type);
117
118 PROCEDURE Calculate_Insurance_Value_Proc
119 (pol_asset_id NUMBER,
120 pol_policy_number VARCHAR2,
121 last_period_closed NUMBER,
122 last_period_closed_date DATE,
123 p_asset_book IN VARCHAR2,
124 p_year IN VARCHAR2,
125 pol_calculation_method VARCHAR2,
126 px_pol_insurance_value IN OUT NOCOPY NUMBER,
127 year_counter_end NUMBER,
128 pol_swiss_building VARCHAR2,
129 px_cal_insurance_value IN OUT NOCOPY NUMBER,
130 pol_price_index_value NUMBER,
131 new_price_index_value NUMBER,
132 pol_base_index_year VARCHAR2,
133 pol_price_index_id NUMBER,
134 pol_base_index_date DATE,
135 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
136 pol_indexation_date DATE,
137 px_pol_retirement_value IN OUT NOCOPY NUMBER,
138 retirement_flag VARCHAR2,
139 pol_period_fully_reserved NUMBER
140 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type);
141
142 PROCEDURE insurance ( Errbuf OUT NOCOPY VARCHAR2,
143 Retcode OUT NOCOPY NUMBER,
144 P_Asset_book VARCHAR2,
145 P_Year VARCHAR2,
146 P_Ins_company_id NUMBER,
147 P_Asset_start VARCHAR2,
148 P_Asset_end VARCHAR2) IS
149
150 year_date_start DATE;
151 year_date_end DATE;
152 year_counter_start NUMBER;
153 year_counter_end NUMBER;
154 last_period_closed NUMBER;
155 last_period_closed_date DATE;
156 year_effective_end DATE;
157 year_effective_start DATE;
158 year_prev_end_date DATE;
159 c_request_id NUMBER(15);
160 c_appid NUMBER(15);
161 c_program_id NUMBER(15);
162 c_user_id NUMBER(15);
163 pol_asset_number VARCHAR2(15);
164 pol_asset_policy_id NUMBER(18);
165 pol_vendor_id NUMBER(18);
166 pol_policy_number VARCHAR2(18);
167 pol_asset_id NUMBER;
168 pol_retirement_value NUMBER;
169 pol_calculation_method VARCHAR2(30);
170 pol_last_indexation_id NUMBER;
171 pol_insurance_value NUMBER;
172 pol_swiss_indexation_date DATE;
173 pol_indexation_year VARCHAR2(4);
174 pol_indexation_date DATE;
175 pol_day_after_indexation DATE;
176 pol_indexation_record_type VARCHAR2(2);
177 pol_price_index_id NUMBER;
178 pol_price_index_value NUMBER;
179 pol_base_index_year NUMBER;
180 pol_base_index_date DATE;
181 pol_swiss_building VARCHAR2(1);
182 new_price_index_value NUMBER;
183 new_price_index_id NUMBER;
184 price_index_value NUMBER;
185 price_index_id NUMBER;
186 base_price_index_value NUMBER;
187 base_price_index_id NUMBER;
188 cal_insurance_value NUMBER;
189 bks_cost NUMBER;
190 transaction_date DATE;
191 transaction_type VARCHAR2(1);
192 retirement_adjustment_amount NUMBER;
196 cal_ret_reserve NUMBER;
193 transaction_id NUMBER(15);
194 reinstatement_ret_type VARCHAR2(1);
195 retirement_date DATE;
197 cal_ret_type VARCHAR2(1);
198 indexation_id NUMBER;
199 cmv_null_flag VARCHAR2(1);
200 asset_status VARCHAR2(40);
201 msg VARCHAR2(80);
202 process_policy_flag VARCHAR2(1);
203 transactions_flag VARCHAR2(1);
204 reinstatement_without_ret VARCHAR2(1);
205 already_indexed VARCHAR2(1);
206 pol_period_fully_reserved NUMBER(15);
207
208
209 /* Cursor to select policy details entered via FA Insurance form - FAIS */
210
211 CURSOR Policy ( P_Asset_start VARCHAR2,
212 P_Asset_end VARCHAR2,
213 P_Ins_company_id NUMBER,
214 P_Asset_book VARCHAR2,
215 year_date_end DATE
216 ) IS
217
218 SELECT pol.asset_policy_id,
219 mpol.vendor_id,
220 pol.policy_number,
221 pol.asset_id,
222 fad.asset_number,
223 pol.swiss_building,
224 mpol.calculation_method,
225 pol.last_indexation_id,
226 TO_CHAR(pol.base_index_date,'YYYY') pol_base_index_year,
227 pol.base_index_date,
228 nvl(pol.current_insurance_value,
229 pol.base_insurance_value),
230 pol.last_indexation_date,
231 pol.last_indexation_date + 1,
232 pol.current_price_index_id,
233 pol.value_before_retirement,
234 nvl(pol.last_price_index_value,pii.price_index_value),
235 bks.period_counter_fully_reserved
236 FROM fa_additions fad,
237 fa_books bks,
238 fa_ins_policies pol,
239 fa_ins_mst_pols mpol,
240 fa_price_index_values pii
241 WHERE fad.asset_number BETWEEN NVL(p_asset_start, fad.asset_number)
242 AND NVL(p_asset_end,fad.asset_number)
243 AND pol.asset_policy_id = mpol.asset_policy_id
244 AND pol.asset_id = bks.asset_id
245 AND bks.book_type_code = p_asset_book
246 AND bks.period_counter_fully_retired is null
247 AND bks.date_ineffective is null
248 AND bks.transaction_header_id_out is null
249 AND fad.asset_id = pol.asset_id
250 AND pol.book_type_code = p_asset_book
251 AND mpol.vendor_id = NVL(p_ins_company_id,mpol.vendor_id)
252 AND pii.price_index_id(+) = pol.current_price_index_id
253 AND NVL(pol.last_indexation_date,pol.base_index_date)
254 BETWEEN pii.from_date(+) AND pii.to_date(+)
255 ORDER BY pol.asset_id,pol.asset_policy_id
256 FOR UPDATE OF pol.last_indexation_id, pol.current_insurance_value;
257
258 /* Cursor to select Reirement details */
259
260 CURSOR Get_Transactions (P_Asset_book VARCHAR2,
261 pol_asset_id NUMBER,
262 pol_day_after_indexation DATE,
263 last_period_closed_date DATE) IS
264
265 SELECT DECODE(fth.transaction_type_code,
266 'PARTIAL RETIREMENT', 'P',
267 'FULL RETIREMENT', 'F',
268 'REINSTATEMENT', 'R', 'A'),
269 fth.transaction_header_id,
270 fth.transaction_date_entered,
271 bks.cost
272 FROM fa_transaction_headers fth,
273 fa_books bks
274 WHERE bks.date_ineffective is not null
275 AND fth.transaction_date_entered BETWEEN
276 nvl(pol_day_after_indexation,fth.transaction_date_entered)
277 AND last_period_closed_date
278 AND bks.book_type_code = fth.book_type_code
279 AND bks.asset_id = fth.asset_id
280 AND bks.asset_id = pol_asset_id
281 AND fth.book_type_code = p_asset_book
282 AND fth.transaction_header_id = bks.transaction_header_id_out
283 AND fth.transaction_type_code IN
284 ('FULL RETIREMENT','PARTIAL RETIREMENT', 'REINSTATEMENT',
285 'ADJUSTMENT', 'CIP ADJUSTMENT')
286 ORDER BY fth.transaction_header_id;
287
288
289 /* Cursor to get details of any additions */
290
291 CURSOR Additions ( p_asset_book VARCHAR2,
292 pol_Asset_id NUMBER,
293 pol_day_after_indexation DATE,
294 last_period_closed_date DATE
295 ) IS
296
297 SELECT DECODE(adj.DEBIT_CREDIT_FLAG ,
298 'CR', -1 * nvl(adj.adjustment_amount,0),
299 NVL(adj.adjustment_amount,0))
300 FROM fa_adjustments adj,
301 fa_transaction_headers fth,
302 fa_books bks
303 WHERE bks.date_ineffective is not null
304 AND fth.transaction_date_entered BETWEEN
305 nvl(pol_day_after_indexation,fth.transaction_date_entered)
306 AND last_period_closed_date
307 AND fth.transaction_header_id = adj.transaction_header_id
308 AND bks.transaction_header_id_out = fth.transaction_header_id
309 AND bks.book_type_code = fth.book_type_code
310 AND bks.asset_id = fth.asset_id
311 AND bks.asset_id = pol_asset_id
312 AND fth.book_type_code = p_asset_book
313 AND fth.transaction_type_code = 'ADJUSTMENT'
314 AND adj.source_type_code = 'ADJUSTMENT'
315 AND adj.adjustment_type = 'COST'
316 AND adj.book_type_code = p_asset_book
320 num_loops number;
317 AND adj.asset_id = pol_asset_id;
318
319 current_loop number := 0;
321 loop_pol_price_index_value number;
322 loop_new_price_index_value number;
323
324
325 BEGIN
326
327 plsqlmsg('Started : ' || to_char(sysdate,'HH:MI:SS'));
328 retcode := 0;
329
330
331 if (not g_log_level_rec.initialized) then
332 if (NOT fa_util_pub.get_log_level_rec (
333 x_log_level_rec => g_log_level_rec
334 )) then
335 Raise_Application_Error(-20000, 'log init failed');
336 end if;
337 end if;
338
339 Get_Period_Counters_Proc ( P_Asset_book,
340 P_year,
341 last_period_closed,
342 last_period_closed_date,
343 year_date_start,
344 year_counter_start,
345 year_date_end,
346 year_counter_end,
347 year_effective_end,
348 year_effective_start,
349 year_prev_end_date,
350 g_log_level_rec
351 );
352
353 --
354 -- start looping round the asset policies
355 --
356
357
358 plsqlmsg('Running Indexation for ' || to_char(last_period_closed_date,'DD-MM-RRRR'));
359
360
361 OPEN Policy ( p_asset_start,
362 p_asset_end,
363 p_ins_company_id,
364 p_asset_book,
365 year_date_end
366 );
367
368 LOOP
369
370 asset_status := '';
371 pol_price_index_id := NULL;
372 new_price_index_id := NULL;
373 new_price_index_value := NULL;
374 pol_price_index_value := NULL;
375 pol_retirement_value := NULL;
376 retirement_adjustment_amount := NULL;
377 retirement_date := NULL;
378 process_policy_flag := 'Y';
379 transactions_flag := 'Y';
380 reinstatement_without_ret := 'N';
381 already_indexed := 'N';
382
383
384 FETCH Policy
385 INTO pol_asset_policy_id,
386 pol_vendor_id,
387 pol_policy_number,
388 pol_asset_id,
389 pol_asset_number,
390 pol_swiss_building,
391 pol_calculation_method,
392 pol_last_indexation_id,
393 pol_base_index_year,
394 pol_base_index_date,
395 pol_insurance_value,
396 pol_indexation_date,
397 pol_day_after_indexation,
398 pol_price_index_id,
399 pol_retirement_value,
400 pol_price_index_value,
401 pol_period_fully_reserved;
402
403 IF policy%NOTFOUND and policy%rowcount = 0 THEN
404 CLOSE Policy;
405 Raise_Application_Error(-20000, 'ERROR: No Policy Information.');
406
407 ELSIF policy%NOTFOUND THEN
408 EXIT;
409 END IF;
410
411
412 plsqlmsg_put ('Processing ' || rpad(pol_asset_number,19,' ' ) || rpad(pol_policy_number,18,' ') || '...');
413
414 plsqlmsg(' ');
415
416
417 IF pol_base_index_date > last_period_closed_date THEN
418
419 asset_status := 'Index Date > Last Period Closed Date';
420 process_policy_flag := 'N';
421
422 ELSIF pol_indexation_date = last_period_closed_date THEN
423 process_policy_flag := 'N';
424 asset_status := 'Already Indexed';
425
426 ELSIF pol_base_index_date is null THEN
427
428 asset_status := 'No Base Index Date';
429 process_policy_flag := 'N';
430
431 END IF;
432
433 IF process_policy_flag = 'Y' THEN
434
435 cmv_null_flag := 'N';
436
437 Get_New_Price_index_Proc (pol_price_index_id,
438 new_price_index_value,
439 new_price_index_id,
440 last_period_closed_date,
441 g_log_level_rec);
442
443
444 IF pol_swiss_building = 'Y' AND
445 pol_base_index_date >= pol_indexation_date THEN
446
447 pol_day_after_indexation := pol_base_index_date + 1;
448
449 END IF;
450
451
452 IF pol_calculation_method <> 'CMV' or
453 (pol_calculation_method = 'CMV' and pol_swiss_building = 'Y') THEN
454
455 Select count(*)
456 Into num_loops
457 FROM fa_transaction_headers fth,
458 fa_books bks
459 WHERE bks.date_ineffective is not null
460 AND fth.transaction_date_entered BETWEEN
461 nvl(pol_day_after_indexation,fth.transaction_date_entered)
462 AND last_period_closed_date
463 AND bks.book_type_code = fth.book_type_code
464 AND bks.asset_id = fth.asset_id
465 AND bks.asset_id = pol_asset_id
466 AND fth.book_type_code = p_asset_book
467 AND fth.transaction_header_id = bks.transaction_header_id_out
468 AND fth.transaction_type_code IN
469 ('FULL RETIREMENT','PARTIAL RETIREMENT', 'REINSTATEMENT',
470 'ADJUSTMENT', 'CIP ADJUSTMENT');
471
472
476 last_period_closed_date);
473 OPEN get_transactions (p_asset_book,
474 pol_asset_id,
475 pol_day_after_indexation,
477
478 LOOP
479
480 transaction_type := NULL;
481 transaction_id := NULL;
482 bks_cost := NULL;
483 transaction_date := NULL;
484 current_loop := current_loop + 1;
485
486 FETCH get_transactions
487 INTO transaction_type,
488 transaction_id,
489 transaction_date,
490 bks_cost;
491
492 -- bug 5631765. Only apply the rate when all adjustments and retirements are looped through.
493
494 if num_loops > current_loop then
495 loop_pol_price_index_value := 1;
496 loop_new_price_index_value := 1;
497 else
498 loop_pol_price_index_value := pol_price_index_value;
499 loop_new_price_index_value := new_price_index_value;
500 end if;
501 --
502
503 IF get_transactions%NOTFOUND THEN
504
505 transactions_flag := 'N';
506
507 IF get_transactions%rowcount = 0 THEN
508
509 Calculate_Insurance_Value_Proc (pol_asset_id,
510 pol_policy_number,
511 last_period_closed,
512 last_period_closed_date,
513 P_Asset_book,
514 P_Year,
515 pol_calculation_method,
516 pol_insurance_value,
517 year_counter_end,
518 pol_swiss_building,
519 cal_insurance_value,
520 pol_price_index_value,
521 new_price_index_value,
522 pol_base_index_year,
523 pol_price_index_id,
524 pol_base_index_date,
525 cmv_null_flag,
526 pol_indexation_date,
527 pol_retirement_value,
528 'N',
529 pol_period_fully_reserved,
530 g_log_level_rec);
531
532
533
534 END IF;
535
536 ELSIF transaction_type IN ('P', 'R') THEN
537
538 process_retirements (pol_asset_id,
539 pol_policy_number ,
540 last_period_closed,
541 last_period_closed_date,
542 P_Asset_book,
543 P_Year,
544 pol_calculation_method,
545 pol_insurance_value,
546 year_counter_end,
547 pol_swiss_building,
548 cal_insurance_value,
549 loop_pol_price_index_value, --
550 loop_new_price_index_value, --
551 pol_base_index_year,
552 pol_price_index_id,
553 pol_base_index_date,
554 cmv_null_flag,
555 pol_indexation_date,
556 transaction_type,
557 bks_cost,
558 transaction_id,
559 pol_retirement_value,
560 reinstatement_ret_type,
561 retirement_date,
562 reinstatement_without_ret,
563 pol_period_fully_reserved,
564 g_log_level_rec);
565
566 ELSE
567
568 process_adjustments (transaction_id,
569 pol_asset_id,
570 pol_policy_number,
571 last_period_closed,
572 last_period_closed_date,
573 P_Asset_book,
574 P_Year,
575 pol_calculation_method,
576 pol_insurance_value,
577 year_counter_end,
578 pol_swiss_building,
579 cal_insurance_value,
580 loop_pol_price_index_value, --
581 loop_new_price_index_value, --
582 pol_base_index_year,
583 pol_price_index_id,
584 pol_base_index_date,
585 cmv_null_flag,
586 pol_indexation_date,
587 pol_retirement_value,
588 pol_period_fully_reserved,
589 g_log_level_rec);
590
591
592 END IF;
593
594 IF transactions_flag = 'N' or
595 reinstatement_without_ret = 'Y' THEN
596
597 EXIT;
598
599 END IF;
600
601 END LOOP;
602
603 CLOSE get_transactions;
604
608 pol_policy_number,
605 ELSE
606
607 Calculate_Insurance_Value_Proc (pol_asset_id,
609 last_period_closed,
610 last_period_closed_date,
611 P_Asset_book,
612 P_Year,
613 pol_calculation_method,
614 pol_insurance_value,
615 year_counter_end,
616 pol_swiss_building,
617 cal_insurance_value,
618 pol_price_index_value,
619 new_price_index_value,
620 pol_base_index_year,
621 pol_price_index_id,
622 pol_base_index_date,
623 cmv_null_flag,
624 pol_indexation_date,
625 pol_retirement_value,
626 'N',
627 pol_period_fully_reserved,
628 g_log_level_rec);
629
630
631 END IF;
632
633 IF cmv_null_flag = 'N' and reinstatement_without_ret = 'N' THEN
634
635 asset_status := 'Indexed';
636
637
638 insert_values_record ( pol_asset_policy_id,
639 indexation_id,
640 pol_vendor_id,
641 pol_policy_number,
642 pol_asset_id,
643 p_year,
644 last_period_closed_date,
645 pol_price_index_id,
646 pol_price_index_value,
647 cal_insurance_value,
648 g_log_level_rec);
649
650
651 update_policies_record (pol_asset_policy_id,
652 pol_policy_number,
653 pol_asset_id,
654 cal_insurance_value,
655 indexation_id,
656 new_price_index_value,
657 pol_retirement_value,
658 last_period_closed_date,
659 g_log_level_rec);
660
661 /* commenting this for bug fix 2051129 */
662 -- COMMIT;
663
664 ELSE
665
666 IF reinstatement_without_ret = 'Y' THEN
667 asset_status := 'No Retirement to Reinstatement';
668 ELSE
669 asset_status := 'Not Depreciated';
670 END IF;
671
672 END IF;
673
674 END IF;
675
676 plsqlmsg(asset_status);
677
678 END LOOP;
679
680 /* adding this for bug fix 2051129 */
681 COMMIT;
682
683 CLOSE Policy;
684
685 retcode := 0;
686
687 plsqlmsg('Ended : ' || to_char(sysdate,'HH:MI:SS'));
688
689
690 END insurance;
691
692 PROCEDURE get_market_value
693 (pol_asset_id NUMBER,
694 last_period_closed NUMBER,
695 px_market_value IN OUT NOCOPY NUMBER,
696 px_market_ytd_deprn IN OUT NOCOPY NUMBER,
697 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
698 pol_period_fully_reserved NUMBER
699 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
700
701 CURSOR Get_market_value ( pol_asset_id NUMBER,
702 last_period_closed NUMBER
703 ) IS
704 SELECT bks.cost- fdd.deprn_reserve,
705 fdd.ytd_deprn
706 FROM fa_books bks,
707 fa_deprn_summary fdd
708 WHERE bks.book_type_code = fdd.book_type_code
709 AND bks.asset_id = fdd.asset_id
710 AND fdd.asset_id = pol_asset_id
711 AND fdd.deprn_source_code = 'DEPRN'
712 AND fdd.period_counter = last_period_closed
713 AND date_ineffective is null;
714
715 BEGIN
716
717 OPEN Get_Market_Value (pol_asset_id, last_period_closed);
718 FETCH get_market_value
719 INTO px_market_value,
720 px_market_ytd_deprn;
721
722 IF get_market_value%NOTFOUND and pol_period_fully_reserved IS NULL THEN
723
724 px_cmv_null_flag := 'Y';
725
729
726 ELSE
727
728 px_cmv_null_flag := 'N';
730 END IF;
731
732 END get_market_value;
733
734 PROCEDURE get_remaining_life
735 (p_asset_book IN VARCHAR2,
736 pol_asset_id NUMBER,
737 pol_indexation_date DATE,
738 px_asset_total_life IN OUT NOCOPY NUMBER,
739 px_asset_remaining_life IN OUT NOCOPY NUMBER
740 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
741
742 CURSOR get_asset_life_details (p_asset_book VARCHAR2,
743 pol_asset_id NUMBER)
744 IS
745
746 SELECT bks.life_in_months asset_total_life,
747 bks.life_in_months -
748 floor(months_between(fdp.calendar_period_close_date,
749 bks.date_placed_in_service))
750 asset_remaining_life
751 FROM fa_books bks,
752 fa_deprn_periods fdp
753 WHERE bks.book_type_code = P_asset_book
754 AND fdp.book_type_code = P_asset_book
755 AND bks.book_type_code = fdp.book_type_code
756 AND bks.asset_id = pol_asset_id
757 AND bks.date_ineffective is null
758 AND fdp.period_close_date is null;
759
760
761 CURSOR get_last_indexation_tot_life (P_asset_book VARCHAR2,
762 pol_asset_id NUMBER,
763 pol_indexation_date DATE)
764 IS
765 /* 1 is added to the figure subtracted from the life_in_months to create a
766 remaining life at last indexation date that would equate to the remaining life
767 shown on the FA view transaction screen at that time */
768
769 SELECT (bks.life_in_months -
770 floor(months_between(fdp.calendar_period_close_date,
771 bks.date_placed_in_service)+1))
772 FROM fa_books bks,
773 fa_deprn_periods fdp
774 WHERE bks.book_type_code = P_asset_book
775 AND fdp.book_type_code = P_asset_book
776 AND bks.asset_id = pol_asset_id
777 AND pol_indexation_date between
778 fdp.calendar_period_open_date and fdp.calendar_period_close_date;
779
780
781 BEGIN
782
783
784 OPEN get_asset_life_details (P_asset_book,
785 pol_asset_id);
786
787 FETCH get_asset_life_details
788 INTO px_asset_total_life,
789 px_asset_remaining_life;
790
791 CLOSE get_asset_life_details;
792
793 IF pol_indexation_date is not NULL THEN
794
795 OPEN get_last_indexation_tot_life (P_asset_book,
796 pol_asset_id,
797 pol_indexation_date
798 );
799
800 FETCH get_last_indexation_tot_life
801 INTO px_asset_total_life;
802
803 CLOSE get_last_indexation_tot_life;
804
805
806 END IF;
807
808
809 END get_remaining_life;
810
811
812 PROCEDURE process_van (pol_asset_id NUMBER,
813 last_period_closed NUMBER,
814 px_market_value IN OUT NOCOPY NUMBER,
815 px_market_ytd_deprn IN OUT NOCOPY NUMBER,
816 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
817 px_cal_insurance_value IN OUT NOCOPY NUMBER,
818 px_pol_insurance_value IN OUT NOCOPY NUMBER,
819 new_price_index_value NUMBER,
820 pol_price_index_value NUMBER,
821 px_pol_retirement_value IN OUT NOCOPY NUMBER,
822 retirement_flag VARCHAR2,
823 pol_period_fully_reserved NUMBER
824 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
825
826 BEGIN
827
828 /* Check market value purely to see if asset has been depreciated */
829
830 Get_Market_Value (pol_asset_id, last_period_closed, px_market_value,
831 px_market_ytd_deprn, px_cmv_null_flag,
832 pol_period_fully_reserved,
833 p_log_level_rec);
834
835 IF px_cmv_null_flag = 'N' THEN
836
837 IF retirement_flag = 'Y' THEN
838
839 px_pol_retirement_value := nvl(px_pol_insurance_value,0)/
840 nvl(pol_price_index_value,1);
841 END IF;
842
843 px_cal_insurance_value := nvl(px_pol_insurance_value,0) *
844 (nvl(new_price_index_value,1)/
845 nvl(pol_price_index_value,1)
846 );
847
848 END IF;
849 END process_van;
850
851 PROCEDURE process_cmv (p_asset_book IN VARCHAR2,
852 pol_asset_id NUMBER,
853 last_period_closed NUMBER,
854 px_market_value IN OUT NOCOPY NUMBER,
855 px_market_ytd_deprn IN OUT NOCOPY NUMBER,
856 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
857 px_cal_insurance_value IN OUT NOCOPY NUMBER,
858 px_base_price_index_value IN OUT NOCOPY NUMBER,
862 new_price_index_value NUMBER,
859 pol_price_index_id NUMBER,
860 px_base_price_index_id IN OUT NOCOPY NUMBER,
861 pol_base_index_date DATE,
863 px_pol_retirement_value IN OUT NOCOPY NUMBER,
864 retirement_flag VARCHAR2,
865 pol_period_fully_reserved NUMBER
866 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
867
868 BEGIN
869
870
871 Get_Market_Value (pol_asset_id, last_period_closed, px_market_value,
872 px_market_ytd_deprn, px_cmv_null_flag,
873 pol_period_fully_reserved,
874 p_log_level_rec);
875
876 IF px_cmv_null_flag = 'N' THEN
877
878 Get_base_index_value (p_asset_book,
879 pol_asset_id,
883 px_base_price_index_id,
880 pol_base_index_date,
881 px_base_price_index_value,
882 pol_price_index_id,
884 p_log_level_rec
885 );
886
887 px_cal_insurance_value := nvl(px_market_value,0) *
888 (nvl(new_price_index_value,1)/
889 nvl(px_base_price_index_value,1)
890 );
891
892
893 END IF;
894
895 END process_cmv;
896
897
898 PROCEDURE process_cmv_swiss
899 (p_asset_book IN VARCHAR2,
900 pol_asset_id NUMBER,
901 pol_indexation_date DATE,
902 last_period_closed_date DATE,
903 last_period_closed NUMBER,
904 px_market_value IN OUT NOCOPY NUMBER,
905 px_market_ytd_deprn IN OUT NOCOPY NUMBER,
906 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
907 pol_insurance_value NUMBER,
908 px_cal_insurance_value IN OUT NOCOPY NUMBER,
909 px_base_price_index_value IN OUT NOCOPY NUMBER,
910 pol_price_index_id NUMBER,
911 pol_price_index_value NUMBER,
912 px_base_price_index_id IN OUT NOCOPY NUMBER,
913 pol_base_index_date DATE,
914 new_price_index_value NUMBER,
915 px_pol_retirement_value IN OUT NOCOPY NUMBER,
916 retirement_flag VARCHAR2,
917 pol_period_fully_reserved NUMBER
921 asset_remaining_life NUMBER;
918 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
919
920 asset_total_life NUMBER;
922
923 BEGIN
924
925 get_remaining_life (P_Asset_book ,
926 pol_asset_id ,
927 pol_indexation_date ,
928 asset_total_life ,
929 asset_remaining_life ,
930 p_log_level_rec);
931
932 if last_period_closed_date >= pol_base_index_date and
933 pol_base_index_date >= pol_indexation_date THEN
934
935 Get_base_index_value (p_asset_book,
936 pol_asset_id,
937 pol_base_index_date,
938 px_base_price_index_value,
939 pol_price_index_id,
940 px_base_price_index_id,
941 p_log_level_rec
942 );
943
944 IF retirement_flag = 'Y' THEN
945 px_pol_retirement_value := nvl(pol_insurance_value,0) /
946 (nvl(px_base_price_index_value,1) * nvl(asset_total_life,1));
947 END IF;
948
949 px_cal_insurance_value := (nvl(pol_insurance_value,0) *
950 (nvl(new_price_index_value,1)/
951 nvl(px_base_price_index_value,1))) *
952 (nvl(asset_remaining_life,0)/
953 nvl(asset_total_life,1));
954
955 ELSE
956
957 IF retirement_flag = 'Y' THEN
958 px_pol_retirement_value := nvl(pol_insurance_value,0) /
959 (nvl(pol_price_index_value,1) * nvl(asset_total_life,1));
960 END IF;
961
962 px_cal_insurance_value := (nvl(pol_insurance_value,0) *
963 (nvl(new_price_index_value,1)/
964 nvl(pol_price_index_value,1))) *
965 (asset_remaining_life/asset_total_life);
966 END IF;
967
968 END process_cmv_swiss;
969
970
971 PROCEDURE process_van_swiss
972 (p_asset_book IN VARCHAR2,
973 pol_asset_id NUMBER,
974 last_period_closed NUMBER,
975 px_market_value IN OUT NOCOPY NUMBER,
976 px_market_ytd_deprn IN OUT NOCOPY NUMBER,
977 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
978 px_cal_insurance_value IN OUT NOCOPY NUMBER,
979 px_pol_insurance_value IN OUT NOCOPY NUMBER,
980 px_base_price_index_value IN OUT NOCOPY NUMBER,
981 pol_price_index_id NUMBER,
982 px_base_price_index_id IN OUT NOCOPY NUMBER,
983 new_price_index_value NUMBER,
984 pol_base_index_date DATE,
985 pol_price_index_value NUMBER,
986 px_pol_retirement_value IN OUT NOCOPY NUMBER,
987 retirement_flag VARCHAR2,
988 pol_period_fully_reserved NUMBER
989 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
990
991 BEGIN
992
993 /* Check market value purely to see if asset has been depreciated */
994
995 Get_Market_Value (pol_asset_id, last_period_closed, px_market_value,
996 px_market_ytd_deprn, px_cmv_null_flag,
997 pol_period_fully_reserved,
998 p_log_level_rec);
999
1000 IF px_cmv_null_flag = 'N' THEN
1001
1002 Get_base_index_value (p_asset_book,
1003 pol_asset_id,
1004 pol_base_index_date,
1005 px_base_price_index_value,
1006 pol_price_index_id,
1007 px_base_price_index_id,
1008 p_log_level_rec);
1009
1010 IF retirement_flag = 'Y' THEN
1011 px_pol_retirement_value := nvl(px_pol_insurance_value,0) /
1012 nvl(px_base_price_index_value,1);
1013 END IF;
1014
1015 px_cal_insurance_value := nvl(px_pol_insurance_value,0) *
1016 (nvl(new_price_index_value,1) /
1017 nvl(px_base_price_index_value,1));
1018
1019 END IF;
1020
1021 END process_van_swiss;
1022
1023 PROCEDURE Get_Period_Counters_Proc
1024 (p_asset_book IN VARCHAR2,
1025 p_year IN VARCHAR2,
1026 px_last_period_closed IN OUT NOCOPY NUMBER,
1027 px_last_period_closed_date IN OUT NOCOPY DATE,
1028 px_year_date_start IN OUT NOCOPY DATE,
1029 px_year_counter_start IN OUT NOCOPY NUMBER,
1030 px_year_date_end IN OUT NOCOPY DATE,
1031 px_year_counter_end IN OUT NOCOPY NUMBER,
1032 px_year_effective_end IN OUT NOCOPY DATE,
1033 px_year_effective_start IN OUT NOCOPY DATE,
1034 px_year_prev_end_date IN OUT NOCOPY DATE
1035 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1036
1037
1041
1038 CURSOR Get_Period_Counters ( P_Asset_book VARCHAR2,
1039 P_Year VARCHAR2
1040 ) IS
1042 SELECT fdp1.calendar_period_open_date,
1043 fdp1.period_counter,
1044 fdp2.calendar_period_close_date,
1045 fdp2.period_counter,
1046 fdp2.period_close_date,
1047 fdp1.period_open_date,
1048 fdp1.calendar_period_open_date - 1
1049 FROM fa_Deprn_periods fdp1,
1050 fa_Deprn_periods fdp2
1051 WHERE fdp1.period_counter =
1052 (SELECT MIN(x.period_counter)
1053 FROM fa_Deprn_periods x
1054 WHERE x.fiscal_year = p_year
1055 AND x.book_type_code = p_asset_book
1056 )
1057 AND fdp2.period_counter =
1058 (SELECT MAX(x.period_counter)
1059 FROM fa_Deprn_periods x
1060 WHERE x.fiscal_year = p_year
1061 AND x.book_type_code = p_asset_book
1062 )
1063 AND fdp2.book_type_code = fdp1.book_type_code
1064 AND fdp2.fiscal_year = fdp1.fiscal_year
1065 AND fdp1.fiscal_year = p_year
1066 AND fdp1.book_type_code = p_asset_book;
1067
1068
1069 CURSOR Get_Last_Period_Closed ( P_Asset_book VARCHAR2,
1070 P_Year VARCHAR2
1071 ) IS
1072 SELECT fdp1.CALENDAR_PERIOD_CLOSE_DATE,
1073 fdp1.period_counter
1074 FROM fa_deprn_periods fdp1
1075 WHERE fdp1.book_type_code = p_asset_book
1076 AND fdp1.fiscal_year = p_year
1077 AND fdp1.period_counter =
1078 (SELECT MAX(fdp.period_counter)
1079 FROM fa_deprn_periods fdp
1080 WHERE fdp.book_type_code = p_asset_book
1081 AND fdp.fiscal_year = p_year
1082 AND fdp.period_close_date IS NOT NULL
1083 );
1084
1085
1086 BEGIN
1087
1088 OPEN get_period_counters(p_asset_book, p_year);
1089 FETCH get_period_counters
1090 INTO px_year_date_start,
1091 px_year_counter_start,
1092 px_year_date_end,
1093 px_year_counter_end,
1094 px_year_effective_end,
1095 px_year_effective_start,
1096 px_year_prev_end_date;
1097
1098 IF SQL%NOTFOUND THEN
1099 CLOSE get_period_counters;
1100 raise_application_error(-20000, 'ERROR: Get Period Counters.');
1101 END IF;
1102
1103 CLOSE get_period_counters;
1104
1105 OPEN get_last_period_closed(p_asset_book, p_year);
1106 FETCH get_last_period_closed
1107 INTO px_last_period_closed_date,
1108 px_last_period_closed;
1109
1110 IF SQL%NOTFOUND THEN
1111
1112 CLOSE get_last_period_closed;
1113 raise_application_error(-20000, 'ERROR: Get Last Period Closed.');
1114
1115 END IF;
1116
1117 CLOSE get_last_period_closed;
1118
1119 END Get_Period_Counters_Proc;
1120
1121
1122 PROCEDURE Get_New_Price_index_Proc
1123 (p_pol_price_index_id IN NUMBER,
1124 px_price_index_value IN OUT NOCOPY NUMBER,
1125 px_price_index_id IN OUT NOCOPY NUMBER,
1126 p_year_date_end IN DATE
1127 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1128
1129 CURSOR Get_new_price_index ( pol_price_index_id NUMBER,
1130 year_date_end DATE
1131 ) IS
1132
1133 SELECT pii.price_index_id,
1134 pii.price_index_value
1135 FROM fa_price_index_values pii
1136 WHERE pii.price_index_id = nvl(p_pol_price_index_id,0)
1137 AND year_date_end BETWEEN pii.from_date AND pii.to_date;
1138
1139 CURSOR Get_last_price_index (pol_price_index_id NUMBER) IS
1140 SELECT pii.price_index_id,
1141 pii.price_index_value
1142 FROM fa_price_index_values pii
1143 WHERE pii.price_index_id = nvl(p_pol_price_index_id,0)
1144 AND pii.to_date = (SELECT max(pii2.to_date)
1145 FROM fa_price_index_values pii2
1146 WHERE pii2.price_index_id = pii.price_index_id);
1147
1148 BEGIN
1149
1150 OPEN get_new_price_index (p_pol_price_index_id , p_year_date_end);
1151
1152 FETCH get_new_price_index
1153 INTO px_price_index_id,
1154 px_price_index_value;
1155
1156 IF get_new_price_index%NOTFOUND THEN
1157
1158 OPEN get_last_price_index (p_pol_price_index_id);
1159
1160 FETCH get_last_price_index
1161 INTO px_price_index_id,
1162 px_price_index_value;
1163
1164 CLOSE get_last_price_index;
1165
1166 END IF;
1167
1171 END Get_New_Price_index_Proc;
1168 CLOSE get_new_price_index;
1169
1170
1172
1173
1174 PROCEDURE Calculate_Insurance_Value_Proc
1175 (pol_asset_id NUMBER,
1176 pol_policy_number VARCHAR2,
1177 last_period_closed NUMBER,
1178 last_period_closed_date DATE,
1179 p_asset_book IN VARCHAR2,
1180 p_year IN VARCHAR2,
1181 pol_calculation_method VARCHAR2,
1182 px_pol_insurance_value IN OUT NOCOPY NUMBER,
1183 year_counter_end NUMBER,
1184 pol_swiss_building VARCHAR2,
1185 px_cal_insurance_value IN OUT NOCOPY NUMBER,
1186 pol_price_index_value NUMBER,
1187 new_price_index_value NUMBER,
1188 pol_base_index_year VARCHAR2,
1189 pol_price_index_id NUMBER,
1190 pol_base_index_date DATE,
1191 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
1192 pol_indexation_date DATE,
1193 px_pol_retirement_value IN OUT NOCOPY NUMBER,
1194 retirement_flag VARCHAR2,
1195 pol_period_fully_reserved NUMBER
1196 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1197
1198 market_value NUMBER;
1199 market_ytd_deprn NUMBER;
1200 base_price_index_value NUMBER;
1201 base_price_index_id NUMBER;
1202
1203
1204 BEGIN
1205
1206 IF pol_calculation_method = 'CMV' THEN
1207
1208 IF pol_swiss_building = 'Y' THEN
1209
1210 process_cmv_swiss ( P_Asset_book,
1211 pol_asset_id,
1212 pol_indexation_date,
1213 last_period_closed_date,
1214 last_period_closed,
1215 market_value,
1216 market_ytd_deprn,
1217 px_cmv_null_flag,
1218 px_pol_insurance_value,
1219 px_cal_insurance_value,
1220 base_price_index_value,
1221 pol_price_index_id,
1222 pol_price_index_value,
1223 base_price_index_id,
1224 pol_base_index_date,
1225 new_price_index_value,
1226 px_pol_retirement_value,
1227 retirement_flag,
1228 pol_period_fully_reserved,
1229 p_log_level_rec);
1230
1231 ELSE
1232
1233 process_cmv ( P_Asset_book,
1234 pol_asset_id,
1235 last_period_closed,
1236 market_value,
1237 market_ytd_deprn,
1238 px_cmv_null_flag,
1239 px_cal_insurance_value,
1240 base_price_index_value,
1241 pol_price_index_id,
1242 base_price_index_id,
1243 pol_base_index_date,
1244 new_price_index_value,
1245 px_pol_retirement_value,
1246 retirement_flag,
1247 pol_period_fully_reserved,
1248 p_log_level_rec);
1249
1250 END IF;
1251
1252 ELSIF pol_calculation_method = 'VAN' and pol_swiss_building = 'Y' THEN
1253
1254 IF pol_base_index_date > pol_indexation_date THEN
1255
1256 process_van_swiss ( P_Asset_book,
1257 pol_asset_id,
1258 last_period_closed,
1259 market_value,
1260 market_ytd_deprn,
1261 px_cmv_null_flag,
1262 px_cal_insurance_value,
1263 px_pol_insurance_value,
1264 base_price_index_value,
1265 pol_price_index_id,
1266 base_price_index_id,
1267 new_price_index_value,
1268 pol_base_index_date,
1269 pol_price_index_value,
1270 px_pol_retirement_value,
1271 retirement_flag,
1272 pol_period_fully_reserved,
1273 p_log_level_rec);
1274 ELSE
1275
1276 process_van ( pol_asset_id,
1277 last_period_closed,
1278 market_value,
1282 px_pol_insurance_value,
1279 market_ytd_deprn,
1280 px_cmv_null_flag,
1281 px_cal_insurance_value,
1283 new_price_index_value,
1284 pol_price_index_value,
1285 px_pol_retirement_value,
1286 retirement_flag,
1287 pol_period_fully_reserved,
1288 p_log_level_rec);
1289
1290
1291 END IF;
1292
1293 ELSIF (pol_calculation_method = 'MNL' AND
1294 pol_base_index_date <= last_period_closed_date)
1295 OR pol_calculation_method = 'VAN' THEN
1296
1297 process_van ( pol_asset_id,
1298 last_period_closed,
1299 market_value,
1300 market_ytd_deprn,
1301 px_cmv_null_flag,
1302 px_cal_insurance_value,
1303 px_pol_insurance_value,
1304 new_price_index_value,
1305 pol_price_index_value,
1306 px_pol_retirement_value,
1307 retirement_flag,
1308 pol_period_fully_reserved,
1309 p_log_level_rec);
1310
1311
1312 END IF;
1313
1314 END Calculate_Insurance_Value_Proc;
1315
1316
1317 PROCEDURE process_adjustments
1318 (transaction_id NUMBER,
1319 pol_asset_id NUMBER,
1320 pol_policy_number VARCHAR2,
1324 p_year IN VARCHAR2,
1321 last_period_closed NUMBER,
1322 last_period_closed_date DATE,
1323 p_asset_book IN VARCHAR2,
1325 pol_calculation_method VARCHAR2,
1326 px_pol_insurance_value IN OUT NOCOPY NUMBER,
1327 year_counter_end NUMBER,
1328 pol_swiss_building VARCHAR2,
1329 px_cal_insurance_value IN OUT NOCOPY NUMBER,
1330 pol_price_index_value NUMBER,
1331 new_price_index_value NUMBER,
1332 pol_base_index_year VARCHAR2,
1333 pol_price_index_id NUMBER,
1334 pol_base_index_date DATE,
1335 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
1336 pol_indexation_date DATE,
1337 px_pol_retirement_value IN OUT NOCOPY NUMBER,
1338 pol_period_fully_reserved NUMBER
1339 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1340
1341 CURSOR Additions (transaction_id NUMBER,
1342 p_book_type_code VARCHAR2,
1343 p_asset_id NUMBER) IS
1344
1345 SELECT SUM(DECODE(adj.DEBIT_CREDIT_FLAG ,
1346 'CR', -1 * nvl(adj.adjustment_amount,0),
1347 NVL(adj.adjustment_amount,0)))
1348 FROM fa_adjustments adj
1349 WHERE adj.transaction_header_id = transaction_id
1350 AND adj.source_type_code = 'ADJUSTMENT'
1351 AND adj.adjustment_type = 'COST'
1352 AND adj.book_type_code = p_book_type_code
1353 AND adj.asset_id = p_asset_id;
1354
1355 addition_adjustment_amount NUMBER;
1356
1357 BEGIN
1358
1359 OPEN additions (transaction_id, p_asset_book, pol_asset_id);
1360
1361 FETCH Additions
1365
1362 INTO addition_adjustment_amount;
1363
1364 CLOSE additions;
1366 -- bug 5631765. Placed it before call to calculate_insurance_value_proc
1367 -- instead of after.
1368 px_cal_insurance_value := px_cal_insurance_value +
1369 nvl(addition_adjustment_amount,0);
1370
1371 Calculate_Insurance_Value_Proc (pol_asset_id,
1372 pol_policy_number,
1373 last_period_closed,
1374 last_period_closed_date,
1375 P_Asset_book,
1376 P_Year,
1377 pol_calculation_method,
1378 px_pol_insurance_value,
1379 year_counter_end,
1380 pol_swiss_building,
1381 px_cal_insurance_value,
1382 pol_price_index_value,
1383 new_price_index_value,
1384 pol_base_index_year,
1385 pol_price_index_id,
1386 pol_base_index_date,
1387 px_cmv_null_flag,
1388 pol_indexation_date,
1389 px_pol_retirement_value,
1390 'N',
1391 pol_period_fully_reserved,
1392 p_log_level_rec);
1393
1394
1395 px_pol_insurance_value := px_cal_insurance_value;
1396
1397 END process_adjustments;
1398
1399 PROCEDURE process_reinstatements(P_Asset_book VARCHAR2,
1400 pol_asset_id NUMBER,
1401 pol_indexation_date DATE,
1402 pol_calculation_method VARCHAR2,
1403 pol_swiss_building VARCHAR2,
1404 pol_retirement_value NUMBER,
1405 new_price_index_value NUMBER,
1406 cal_insurance_value IN OUT NOCOPY NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1407
1408
1409 asset_total_life NUMBER;
1410 asset_remaining_life NUMBER;
1411
1412 BEGIN
1413
1414 IF pol_swiss_building = 'Y' THEN
1415
1416 IF pol_calculation_method IN ('VAN', 'MNL') THEN
1417
1418 /* pol_retirement_value would have been set to insurance_value / base_index_value
1419 when the asset was retired. Therefore to reinstate it - multiply by the
1420 current new index value */
1421
1422 cal_insurance_value := pol_retirement_value * new_price_index_value;
1423
1424 ELSE
1425
1426 /* pol_retirement_value would have been set to (insurance_value / base_index_value)/
1427 remaining life at last indexation date when the asset was retired.
1428 Therefore to reinstate it - multiply by the current new index value x Current
1429 remaing life */
1430
1431 get_remaining_life (P_Asset_book ,
1432 pol_asset_id ,
1433 pol_indexation_date ,
1434 asset_total_life ,
1435 asset_remaining_life ,
1436 p_log_level_rec);
1437
1438 cal_insurance_value := pol_retirement_value *
1439 new_price_index_value * asset_remaining_life;
1440
1441 END IF;
1442
1443 ELSE
1444
1445 /* pol_retirement_value would have been set to insurance_value / base_index_value
1446 when the asset was retired. Therefore to reinstate it - multiply by the
1447 current new index value */
1448
1449 cal_insurance_value := pol_retirement_value * new_price_index_value;
1450
1451 END IF;
1452
1453 END process_reinstatements;
1454
1455 PROCEDURE process_retirements
1456 (pol_asset_id NUMBER,
1457 pol_policy_number VARCHAR2,
1458 last_period_closed NUMBER,
1459 last_period_closed_date DATE,
1460 p_asset_book IN VARCHAR2,
1461 p_year IN VARCHAR2,
1462 pol_calculation_method VARCHAR2,
1463 px_pol_insurance_value IN OUT NOCOPY NUMBER,
1464 year_counter_end NUMBER,
1465 pol_swiss_building VARCHAR2,
1466 px_cal_insurance_value IN OUT NOCOPY NUMBER,
1467 pol_price_index_value NUMBER,
1468 new_price_index_value NUMBER,
1469 pol_base_index_year VARCHAR2,
1470 pol_price_index_id NUMBER,
1471 pol_base_index_date DATE,
1472 px_cmv_null_flag IN OUT NOCOPY VARCHAR2,
1473 pol_indexation_date DATE,
1474 transaction_type VARCHAR2,
1475 bks_cost NUMBER,
1476 transaction_id NUMBER,
1477 px_pol_retirement_value IN OUT NOCOPY NUMBER,
1478 px_reinstatement_ret_type IN OUT NOCOPY VARCHAR2,
1479 px_retirement_date IN OUT NOCOPY DATE,
1480 px_reinstatement_without_ret
1484
1481 IN OUT NOCOPY VARCHAR2,
1482 pol_period_fully_reserved NUMBER
1483 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1485 Cursor retirements ( transaction_id NUMBER) IS
1486
1487 SELECT nvl(ret.cost_retired,0)
1488 FROM fa_retirements ret
1489 WHERE ret.transaction_header_id_in (+) = transaction_id
1490 ;
1491
1492 Cursor Get_reinstatement_Details ( transaction_id NUMBER) IS
1493
1494 SELECT DECODE(fth.transaction_type_code, 'FULL_RETIREMENT','F',
1495 'PARTIAL RETIREMENT','P','R'),
1496 nvl(ret.cost_retired,0),
1497 nvl(bks.cost,0)
1498 FROM fa_retirements ret,
1499 fa_transaction_headers fth,
1500 fa_books bks
1501 WHERE ret.transaction_header_id_out = transaction_id
1502 AND ret.transaction_header_id_in = fth.transaction_header_id
1503 AND bks.transaction_header_id_out = fth.transaction_header_id
1504 ;
1505
1506
1507
1508 cost_retired NUMBER;
1509 reinst_cost_retired NUMBER;
1510 reinst_bks_cost NUMBER;
1511
1512 BEGIN
1513
1514 OPEN retirements (transaction_id);
1515
1516 FETCH retirements
1517 INTO cost_retired;
1518
1519 CLOSE retirements;
1520
1521 IF transaction_type = 'R' THEN
1522
1523 OPEN Get_reinstatement_details (transaction_id);
1524
1525 FETCH Get_reinstatement_details
1526 INTO px_reinstatement_ret_type,
1527 reinst_cost_retired,
1528 reinst_bks_cost;
1529
1530 CLOSE Get_reinstatement_details;
1531
1532 IF px_reinstatement_ret_type = 'P' THEN
1533
1534 IF px_pol_retirement_value = 1 THEN
1535 px_reinstatement_without_ret := 'Y';
1536
1537 ELSE
1538 process_reinstatements(p_asset_book,
1539 pol_asset_id,
1540 pol_indexation_date,
1541 pol_calculation_method,
1542 pol_swiss_building,
1543 px_pol_retirement_value,
1544 new_price_index_value,
1545 px_cal_insurance_value,
1546 p_log_level_rec);
1547
1548 END IF;
1549 ELSE
1550
1551 Calculate_Insurance_Value_Proc (pol_asset_id,
1552 pol_policy_number,
1553 last_period_closed,
1554 last_period_closed_date,
1555 P_Asset_book,
1556 P_Year,
1557 pol_calculation_method,
1558 px_pol_insurance_value,
1559 year_counter_end,
1560 pol_swiss_building,
1561 px_cal_insurance_value,
1562 pol_price_index_value,
1563 new_price_index_value,
1564 pol_base_index_year,
1565 pol_price_index_id,
1566 pol_base_index_date,
1567 px_cmv_null_flag,
1568 pol_indexation_date,
1569 px_pol_retirement_value,
1570 'N',
1571 pol_period_fully_reserved,
1572 p_log_level_rec);
1573
1574 END IF;
1575
1576 ELSE
1577
1578 Calculate_Insurance_Value_Proc (pol_asset_id,
1579 pol_policy_number,
1580 last_period_closed,
1581 last_period_closed_date,
1582 P_Asset_book,
1583 P_Year,
1584 pol_calculation_method,
1585 px_pol_insurance_value,
1586 year_counter_end,
1587 pol_swiss_building,
1588 px_cal_insurance_value,
1589 pol_price_index_value,
1590 new_price_index_value,
1591 pol_base_index_year,
1592 pol_price_index_id,
1593 pol_base_index_date,
1594 px_cmv_null_flag,
1595 pol_indexation_date,
1596 px_pol_retirement_value,
1597 'Y',
1598 pol_period_fully_reserved,
1599 p_log_level_rec);
1600
1601 px_cal_insurance_value := px_cal_insurance_value *
1602 (1-(cost_retired/bks_cost));
1603 END IF;
1604
1605 px_pol_insurance_value := px_cal_insurance_value;
1606
1607 END process_retirements;
1608
1609
1610 PROCEDURE update_policies_record
1611 (p_pol_asset_policy_id IN NUMBER,
1615 p_indexation_id IN NUMBER,
1612 p_pol_policy_number IN VARCHAR2,
1613 p_pol_asset_id IN NUMBER,
1614 p_cal_insurance_value IN NUMBER,
1616 p_new_price_index_value IN NUMBER,
1617 p_pol_retirement_value IN NUMBER,
1618 p_last_period_closed_date IN DATE
1619 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1620
1621 BEGIN
1622
1623 UPDATE fa_ins_policies pol
1624 SET pol.current_insurance_value = round(p_cal_insurance_value,2),
1625 pol.last_indexation_id = p_indexation_id,
1626 pol.last_price_index_value = nvl(p_new_price_index_value,1),
1627 pol.value_before_retirement = nvl(p_pol_retirement_value,-1),
1628 pol.last_indexation_date = p_last_period_closed_date
1629 WHERE pol.asset_policy_id = p_pol_asset_policy_id
1630 AND pol.policy_number = p_pol_policy_number
1631 AND pol.asset_id = p_pol_asset_id;
1632
1633 END update_policies_record;
1634
1635 PROCEDURE insert_values_record
1636 (p_pol_asset_policy_id IN NUMBER,
1637 px_indexation_id IN OUT NOCOPY NUMBER,
1638 p_pol_vendor_id IN NUMBER,
1639 p_pol_policy_number IN VARCHAR2,
1640 p_pol_asset_id IN NUMBER,
1641 p_year IN NUMBER,
1642 p_last_period_closed_date IN DATE,
1643 p_pol_price_index_id IN NUMBER,
1644 p_pol_price_index_value IN NUMBER,
1645 p_cal_insurance_value IN NUMBER
1646 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1647
1648 BEGIN
1649
1650 SELECT FA_INDEXATION_S.nextval
1651 INTO px_indexation_id
1652 FROM dual;
1653
1654 INSERT INTO fa_ins_values
1655 ( asset_policy_id,
1656 indexation_id,
1657 vendor_id,
1658 policy_number,
1659 asset_id,
1660 indexation_year,
1661 indexation_date,
1662 price_index_id,
1663 last_price_index_value,
1664 insurance_value,
1665 created_by,
1666 creation_date,
1667 last_updated_by,
1668 last_update_date,
1669 last_update_login,
1670 request_id,
1671 program_application_id,
1672 program_id,
1673 program_update_date
1674 ) VALUES (
1675 p_pol_asset_policy_id,
1676 px_indexation_id,
1677 p_pol_vendor_id,
1678 p_pol_policy_number,
1679 p_pol_asset_id,
1680 p_year,
1681 p_last_period_closed_date,
1682 p_pol_price_index_id,
1683 p_pol_price_index_value,
1684 round(p_cal_insurance_value,2),
1685 TO_NUMBER(FND_PROFILE.Value('USER_ID')),
1686 SYSDATE,
1687 TO_NUMBER(FND_PROFILE.Value('USER_ID')),
1688 SYSDATE,
1689 TO_NUMBER(FND_PROFILE.Value('LOGIN_ID')),
1690 TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID')),
1691 TO_NUMBER(FND_PROFILE.Value('CONC_PROGRAM_APPLICATION_ID')),
1692 TO_NUMBER(FND_PROFILE.Value('CONC_PROGRAM_ID')),
1693 SYSDATE);
1694
1695 END insert_values_record;
1696
1697 PROCEDURE Get_base_index_value
1698 (p_asset_book IN VARCHAR2,
1699 pol_asset_id NUMBER,
1700 pol_base_index_date DATE,
1701 px_base_price_index_value IN OUT NOCOPY NUMBER,
1702 pol_price_index_id NUMBER,
1703 px_base_price_index_id IN OUT NOCOPY NUMBER
1704 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1705
1706 BEGIN
1707 Get_New_Price_Index_Proc(pol_price_index_id,
1708 px_base_price_index_value,
1709 px_base_price_index_id,
1710 pol_base_index_date,
1711 p_log_level_rec);
1712
1713 END Get_base_index_value;
1714
1715
1716 PROCEDURE plsqlmsg (p_msg IN VARCHAR2) IS
1717 /* This is an R11 AOL routine to allow messages to be written to the */
1718 /* log (set the first parameter = 1) or an output file (set the first */
1719 /* parameter = 2). */
1720
1721 BEGIN
1722 fnd_file.put_line(1, p_msg);
1723 END plsqlmsg;
1724
1725 PROCEDURE plsqlmsg_put (p_msg IN VARCHAR2) IS
1726 /* This is an R11 AOL routine to allow messages to be written to the */
1727 /* log (set the first parameter = 1) or an output file (set the first */
1728 /* parameter = 2). */
1729
1730 BEGIN
1731 fnd_file.put(1, p_msg);
1732 END plsqlmsg_put ;
1733
1734 END FA_C_INSURE;