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