[Home] [Help]
53: Keep old orgnl_enrt_dt if plan stays
54: same
55: 26 Oct 98 jcarpent 115.14 Updated prv to non-datetracked
56: 27 Oct 98 jcarpent 115.15 Changed election_rate_information
57: 29 Oct 98 jcarpent 115.16 Fixed calls to ben_prtt_rt_val_api.
58: Don't compare old elctns made date
59: 29 Oct 98 stee 115.17 Get rt_typ_cd and rt_mlt_cd from
60: enrt_rt.
61: 31 Oct 98 bbulusu 115.18 Added per_in_ler_d to delet_enrolment
67: 10 Dec 98 stee 115.22 Fixed bnf warning.
68: 15 Dec 98 jcarpent 115.23 Added call to Create_debit_ledger..
69: 31 Dec 98 lmcdonal 115.24 Better debug messages.
70: 12 Jan 98 maagrawa 115.25 Modified calls to
71: ben_prtt_rt_val_api. (Column
72: acty_base_rt_id added to
73: table ben_prtt_rt_val).
74: 01 Feb 99 jcarpent 115.26 Changed calls to update prv to use
75: ended_per_in_ler_id.
69: 31 Dec 98 lmcdonal 115.24 Better debug messages.
70: 12 Jan 98 maagrawa 115.25 Modified calls to
71: ben_prtt_rt_val_api. (Column
72: acty_base_rt_id added to
73: table ben_prtt_rt_val).
74: 01 Feb 99 jcarpent 115.26 Changed calls to update prv to use
75: ended_per_in_ler_id.
76: 24 Feb 99 jcarpent 115.27 Changed rate cursor in ele_rt_info
77: to include union to join to choice
603: to cursor c_get_ended_result.
604: 01-May-12 stee 115.296 Bug 12583091 - Check for per_in_ler_id when
605: determining if enrollment is suspended.
606: 9-Nov-12 velvanop 115.297 Bug 14803995 : Do not insert future coverage records in backup table if the Life event is Unrestricted
607: 9-Mar-13 velvanop 115.298 Bug 16396102 : While inserting record into backup table ben_le_clsn_n_rstr for rate adjustment records 'BEN_PRTT_RT_VAL_ADJ', populate person_id
608: while inserting into the table
609: */
610:
611: -------------------------------------------------------------------------------
792: prv.rt_strt_dt,
793: prv.prtt_enrt_rslt_id, -- 10415806
794: prv.rt_val, -- 10415806
795: prv.object_version_number
796: from ben_prtt_rt_val prv
797: where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
798: and ((prv.rt_strt_dt > p_rt_strt_dt) or -- old prv in future
799: (p_unrestricted = 'N' and -- old prv on same date
800: prv.per_in_ler_id <> p_per_in_ler_id and
825: acty_base_rt_id,
826: rt_strt_dt,
827: rt_end_dt,
828: object_version_number
829: from ben_prtt_rt_val prv
830: where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
831: and p_rt_strt_dt between prv.rt_strt_dt
832: and prv.rt_end_dt
833: and prv.acty_base_rt_id = l_future_prv.acty_base_rt_id
877: prv.rt_strt_dt,
878: prv.prtt_enrt_rslt_id, -- 10415806
879: prv.rt_val, -- 10415806
880: prv.object_version_number
881: from ben_prtt_rt_val prv
882: where prtt_rt_val_id = p_prtt_rt_val_id;
883: --9614678
884:
885: l_dummy varchar2(1);
890: --9614678
891: l_found boolean := false;
892:
893: type prv_rec is record
894: (prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%type);
895:
896: type prv_table is table of prv_rec index by binary_integer;
897: l_prv_table prv_table;
898: l_prv_count number := 1;
984: ,p_dt_delete_mode => hr_api.g_zap
985: ,p_acty_base_rt_id => l_future_prv.acty_base_rt_id
986: ,p_amt => l_future_prv.rt_val);
987: --
988: ben_prtt_rt_val_api.delete_prtt_rt_val
989: (p_prtt_rt_val_id => l_future_prv.prtt_rt_val_id
990: ,p_person_id => p_person_id
991: ,p_business_group_id => p_business_group_id
992: ,p_object_version_number => l_future_prv.object_version_number
991: ,p_business_group_id => p_business_group_id
992: ,p_object_version_number => l_future_prv.object_version_number
993: ,p_effective_date => l_future_prv.rt_strt_dt);
994: else
995: ben_prtt_rt_val_api.update_prtt_rt_val
996: (p_validate => false,
997: p_person_id => p_person_id,
998: p_business_group_id => p_business_group_id,
999: p_prtt_rt_val_id => l_future_prv.prtt_rt_val_id,
1024: l_prv_count := l_prv_count + 1;
1025: l_found := true;
1026: --9614678
1027:
1028: ben_prtt_rt_val_api.update_prtt_rt_val
1029: (p_prtt_rt_val_id => l_current_prv.prtt_rt_val_id
1030: ,p_person_id => p_person_id
1031: ,p_rt_end_dt => hr_api.g_eot
1032: ,p_business_group_id => p_business_group_id
1261: prv.element_entry_value_id,
1262: prv.elctns_made_dt,
1263: prv.rt_ovridn_flag,
1264: prv.rt_ovridn_thru_dt
1265: from ben_prtt_rt_val prv
1266: where prtt_rt_val_id=l_enrt_rt.prtt_rt_val_id and
1267: p_business_group_id=business_group_id;
1268:
1269: cursor c_prtt_rt_val_2 is
1291: prv.element_entry_value_id,
1292: prv.elctns_made_dt,
1293: prv.rt_ovridn_flag,
1294: prv.rt_ovridn_thru_dt
1295: from ben_prtt_rt_val prv
1296: where prv.acty_base_rt_id = l_enrt_rt.acty_base_rt_id
1297: and prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1298: and prv.prtt_rt_val_stat_cd is null
1299: and l_enrt_rt.rt_strt_dt between prv.rt_strt_dt
1333: )
1334: is
1335: select rt_val,
1336: ann_rt_val
1337: from ben_prtt_rt_val
1338: where acty_base_rt_id = c_acty_base_rt_id
1339: and prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1340: and per_in_ler_id = p_per_in_ler_id
1341: -- bug 6022111
1347: rt_strt_dt = rt_end_dt
1348: AND rt_strt_dt <= c_effective_date
1349: AND rt_strt_dt =
1350: (SELECT MAX (rt_strt_dt)
1351: FROM ben_prtt_rt_val
1352: WHERE acty_base_rt_id = c_acty_base_rt_id
1353: AND prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1354: AND per_in_ler_id = p_per_in_ler_id
1355: AND rt_strt_dt = rt_end_dt
1519: select prv.prtt_rt_val_id,
1520: prv.rt_strt_dt,
1521: prv.acty_ref_perd_cd,
1522: prv.object_version_number
1523: from ben_prtt_rt_val prv
1524: where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1525: and prv.acty_base_rt_id = p_acty_base_rt_id
1526: and prv.rt_strt_dt >= p_rt_strt_dt
1527: and prv.per_in_ler_id <> p_per_in_ler_id
1609: from ben_per_in_ler pil, ben_per_in_ler pil2
1610: where pil.per_in_ler_id <> p_per_in_ler_id
1611: /* Bug 8945818: Added 'or' condition. Check for future rates for the previous life event */
1612: and ( (trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')) or
1613: ('Y' = ( select 'Y' from ben_prtt_rt_val prv,
1614: ben_prtt_enrt_rslt_f pen_n,
1615: ben_prtt_enrt_rslt_f pen_o
1616: where pen_n.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1617: and pen_n.prtt_enrt_rslt_stat_cd is null
1618: and pen_n.per_in_ler_id = p_per_in_ler_id
1619: /* Bug 10419741; If for previous life event there are no rate records created, using the value returned
1620: by the cursor c_prev_per_in_ler will not fetch any values */
1621: and pen_o.per_in_ler_id in (select prv3.per_in_ler_id
1622: from ben_prtt_rt_val prv3,ben_prtt_enrt_rslt_f pen3
1623: where prv3.ended_per_in_ler_id = p_per_in_ler_id
1624: and pen3.prtt_enrt_rslt_id = prv3.prtt_enrt_rslt_id
1625: and pen3.per_in_ler_id = prv3.per_in_ler_id
1626: and pen3.enrt_cvg_strt_dt <= pen3.enrt_cvg_thru_dt
1633: and pen_o.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
1634: /* Bug 10419741; If for previous life event there are no rate records created, using the value returned
1635: by the cursor c_prev_per_in_ler will not fetch any values */
1636: and prv.per_in_ler_id in (select prv3.per_in_ler_id
1637: from ben_prtt_rt_val prv3,ben_prtt_enrt_rslt_f pen3
1638: where prv3.ended_per_in_ler_id = p_per_in_ler_id
1639: and pen3.prtt_enrt_rslt_id = prv3.prtt_enrt_rslt_id
1640: and pen3.per_in_ler_id = prv3.per_in_ler_id
1641: and pen3.enrt_cvg_strt_dt <= pen3.enrt_cvg_thru_dt
1714: ,abr.element_type_id
1715: ,abr.input_value_id
1716: ,pen.person_id
1717: from ben_prtt_enrt_rslt_f pen
1718: ,ben_prtt_rt_val prv
1719: ,ben_acty_base_rt_f abr
1720: where pen.effective_end_date = hr_api.g_eot
1721: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
1722: and pen.prtt_enrt_rslt_stat_cd is null
1733: cursor c_prtt_rt_val_adj (p_per_in_ler_id number,
1734: p_prtt_rt_val_id number) is
1735: select null
1736: from ben_le_clsn_n_rstr
1737: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
1738: AND BKUP_TBL_ID = p_prtt_rt_val_id
1739: AND PER_IN_LER_ID = p_per_in_ler_id;
1740:
1741: /* added for Bug 12576710 */
1743: ,c_per_in_ler_id number
1744: ,c_rt_end_dt date
1745: ,c_acty_base_rt_id number) is
1746: select prv.* from
1747: ben_prtt_rt_val prv
1748: where prv.prtt_rt_val_id = c_prtt_rt_val_id
1749: and prv.rt_end_dt > c_rt_end_dt
1750: --and prv.ENDED_PER_IN_LER_ID <> c_per_in_ler_id
1751: and prv.prtt_rt_val_stat_cd is null
1752: and prv.acty_base_rt_id = c_acty_base_rt_id
1753: and prv.rt_end_dt <> hr_api.g_eot
1754: and not exists
1755: (select '1' from ben_le_clsn_n_rstr bkp
1756: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
1757: and BKUP_TBL_ID = c_prtt_rt_val_id
1758: and bkp.PER_IN_LER_ENDED_ID = c_per_in_ler_id
1759: and bkp.acty_base_rt_id = c_acty_base_rt_id);
1760:
3530: -- delete future dated rate
3531: --
3532: if not p_calculate_only_mode then
3533: --
3534: ben_prtt_rt_val_api.delete_prtt_rt_val
3535: (p_prtt_rt_val_id => p_prtt_rt_val_id
3536: ,p_enrt_rt_id => p_enrt_rt_id
3537: ,p_person_id => p_person_id
3538: ,p_business_group_id => l_enrt_rt.business_group_id
3649: --from benraten. This needs to happed only for recurring rates.
3650: --
3651: if not l_non_recurring_rt then
3652: --
3653: ben_prtt_rt_val_api.update_prtt_rt_val
3654: (p_prtt_rt_val_id => p_prtt_rt_val_id
3655: ,p_rt_end_dt => hr_api.g_eot
3656: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
3657: ,p_input_value_id => l_enrt_rt.input_value_id
3679: end if;
3680: if not p_calculate_only_mode then
3681: --
3682: --
3683: ben_prtt_rt_val_api.update_prtt_rt_val
3684: (p_prtt_rt_val_id => p_prtt_rt_val_id
3685: ,p_rt_end_dt => l_xrt_end_dt
3686: ,p_ended_per_in_ler_id => p_per_in_ler_id
3687: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
3734: business_group_id,
3735: object_version_number)
3736: values (
3737: p_person_id, -- Bug 16396102
3738: 'BEN_PRTT_RT_VAL_ADJ',
3739: p_prtt_rt_val_id,
3740: l_future_end_rec.per_in_ler_id,
3741: p_per_in_ler_id,
3742: l_future_end_rec.rt_end_dt,
3745: );
3746: end if;
3747: close c_future_ended_rates;
3748:
3749: ben_prtt_rt_val_api.update_prtt_rt_val
3750: (p_prtt_rt_val_id => p_prtt_rt_val_id
3751: ,p_rt_end_dt => l_xrt_end_dt
3752: ,p_ended_per_in_ler_id => p_per_in_ler_id
3753: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
3840: RT_END_DT,
3841: business_group_id,
3842: object_version_number)
3843: values (
3844: 'BEN_PRTT_RT_VAL_ADJ',
3845: l_pen.prtt_rt_val_id,
3846: p_per_in_ler_id,
3847: l_pen.person_id,
3848: l_pen.rt_end_dt,
3851: );
3852: end if;
3853: close c_prtt_rt_val_adj;
3854: --
3855: ben_prtt_rt_val_api.update_prtt_rt_val
3856: (P_VALIDATE => FALSE
3857: ,P_PRTT_RT_VAL_ID => l_pen.prtt_rt_val_id
3858: ,P_RT_END_DT => l_enrt_rt.rt_strt_dt - 1 --Bug 8507247
3859: ,p_person_id => l_pen.person_id
3871: end if;
3872: close c_get_prior_per_in_ler;
3873: -- end 7206471
3874:
3875: ben_prtt_rt_val_api.create_prtt_rt_val
3876: (p_prtt_rt_val_id => p_prtt_rt_val_id
3877: ,p_enrt_rt_id => p_enrt_rt_id
3878: ,p_per_in_ler_id => p_per_in_ler_id
3879: ,p_rt_typ_cd => l_enrt_rt.rt_typ_cd
3928: --from benraten. This needs to happed only for recurring rates.
3929: --
3930: if not l_non_recurring_rt then
3931: --
3932: ben_prtt_rt_val_api.update_prtt_rt_val
3933: (p_prtt_rt_val_id => p_prtt_rt_val_id
3934: ,p_rt_end_dt => hr_api.g_eot
3935: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
3936: ,p_input_value_id => l_enrt_rt.input_value_id
4024: RT_END_DT,
4025: business_group_id,
4026: object_version_number)
4027: values (
4028: 'BEN_PRTT_RT_VAL_ADJ',
4029: l_pen.prtt_rt_val_id,
4030: p_per_in_ler_id,
4031: l_pen.person_id,
4032: l_pen.rt_end_dt,
4035: );
4036: end if;
4037: close c_prtt_rt_val_adj;
4038: --
4039: ben_prtt_rt_val_api.update_prtt_rt_val
4040: (P_VALIDATE => FALSE
4041: ,P_PRTT_RT_VAL_ID => l_pen.prtt_rt_val_id
4042: ,P_RT_END_DT => l_enrt_rt.rt_strt_dt - 1 --Bug 8507247
4043: ,p_person_id => l_pen.person_id
4055: end if;
4056: close c_get_prior_per_in_ler;
4057: -- end 7206471
4058: -- if no prtt_rt_val_id passed do insert
4059: ben_prtt_rt_val_api.create_prtt_rt_val
4060: (p_prtt_rt_val_id => p_prtt_rt_val_id
4061: ,p_enrt_rt_id => p_enrt_rt_id
4062: ,p_per_in_ler_id => p_per_in_ler_id
4063: ,p_rt_typ_cd => l_enrt_rt.rt_typ_cd
4129: ,p_effective_date => p_effective_date);
4130: end if;
4131: --
4132: if l_xrt_end_dt_cd = 'WAENT' then
4133: ben_prtt_rt_val_api.update_prtt_rt_val
4134: (p_prtt_rt_val_id => p_prtt_rt_val_id
4135: ,p_rt_end_dt => l_rt_end_dt
4136: ,p_ended_per_in_ler_id => p_per_in_ler_id
4137: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
4990: --
4991: cursor c_prtt_rt_val_id is
4992: select prv.prtt_rt_val_id,
4993: prv.acty_base_rt_id
4994: from ben_prtt_rt_val prv
4995: where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4996: and prv.prtt_rt_val_stat_cd is null
4997: order by prv.rt_strt_dt asc;
4998: --
5119: -- end 12421421
5120: --
5121: cursor c_prv(p_prtt_enrt_rslt_id number, p_per_in_ler_id number) is
5122: select prv.*
5123: from ben_prtt_rt_val prv
5124: where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5125: and prv.per_in_ler_id = p_per_in_ler_id
5126: and prv.prtt_rt_val_stat_cd is NULL ;
5127: --
5875: );
5876: --
5877: END IF;
5878: --
5879: ben_prtt_rt_val_api.update_prtt_rt_val
5880: (p_validate => false,
5881: p_person_id => l_global_pil_rec.person_id,
5882: p_business_group_id => p_business_group_id,
5883: p_prtt_rt_val_id => l_rec.prtt_rt_val_id,
5923: --
5924: hr_utility.set_location('Interim l_rec.prtt_rt_val_id ' || l_rec.prtt_rt_val_id,99);
5925: hr_utility.set_location('l_rec.rt_end_dt ' || l_rec.rt_end_dt,99);
5926: --
5927: ben_prtt_rt_val_api.update_prtt_rt_val
5928: (p_validate => false,
5929: p_person_id => l_global_pil_rec.person_id,
5930: p_business_group_id => p_business_group_id,
5931: p_prtt_rt_val_id => l_rec.prtt_rt_val_id,