[Home] [Help]
617: --------------------------------------------------------------------------------
618: function person_enrolled_in_choice(
619: p_person_id number,
620: p_epe_rec ben_epe_shd.g_rec_type,
621: p_old_result_id number default hr_api.g_number,
622: p_effective_date date) return boolean is
623:
624: l_proc varchar2(72) := g_package||'.person_enrolled_in_choice';
625:
630: per.business_group_id=p_epe_rec.business_group_id and
631: p_effective_date between
632: per.effective_start_date and per.effective_end_date and
633: (enrt_cvg_thru_dt is null or
634: enrt_cvg_thru_dt=hr_api.g_eot) and
635: per.effective_end_date = hr_api.g_eot and
636: per.pgm_id=p_epe_rec.pgm_id and
637: per.prtt_enrt_rslt_id<>p_old_result_id and
638: per.prtt_enrt_rslt_stat_cd is null and
631: p_effective_date between
632: per.effective_start_date and per.effective_end_date and
633: (enrt_cvg_thru_dt is null or
634: enrt_cvg_thru_dt=hr_api.g_eot) and
635: per.effective_end_date = hr_api.g_eot and
636: per.pgm_id=p_epe_rec.pgm_id and
637: per.prtt_enrt_rslt_id<>p_old_result_id and
638: per.prtt_enrt_rslt_stat_cd is null and
639: -- Bug 4538041
650: per.business_group_id=p_epe_rec.business_group_id and
651: p_effective_date between
652: per.effective_start_date and per.effective_end_date and
653: (enrt_cvg_thru_dt is null or
654: enrt_cvg_thru_dt=hr_api.g_eot) and
655: per.effective_end_date = hr_api.g_eot and
656: per.pl_id=pl.pl_id and
657: pl.business_group_id=p_epe_rec.business_group_id and
658: per.prtt_enrt_rslt_id<>p_old_result_id and
651: p_effective_date between
652: per.effective_start_date and per.effective_end_date and
653: (enrt_cvg_thru_dt is null or
654: enrt_cvg_thru_dt=hr_api.g_eot) and
655: per.effective_end_date = hr_api.g_eot and
656: per.pl_id=pl.pl_id and
657: pl.business_group_id=p_epe_rec.business_group_id and
658: per.prtt_enrt_rslt_id<>p_old_result_id and
659: per.sspndd_flag='N' and
672: per.business_group_id=p_epe_rec.business_group_id and
673: p_effective_date between
674: per.effective_start_date and per.effective_end_date and
675: (enrt_cvg_thru_dt is null or
676: enrt_cvg_thru_dt=hr_api.g_eot) and
677: per.effective_end_date = hr_api.g_eot and
678: per.pl_id=pl.pl_id and
679: pl.business_group_id=p_epe_rec.business_group_id and
680: per.prtt_enrt_rslt_id<>p_old_result_id and
673: p_effective_date between
674: per.effective_start_date and per.effective_end_date and
675: (enrt_cvg_thru_dt is null or
676: enrt_cvg_thru_dt=hr_api.g_eot) and
677: per.effective_end_date = hr_api.g_eot and
678: per.pl_id=pl.pl_id and
679: pl.business_group_id=p_epe_rec.business_group_id and
680: per.prtt_enrt_rslt_id<>p_old_result_id and
681: per.sspndd_flag='N' and
694: per.business_group_id=p_epe_rec.business_group_id and
695: p_effective_date between
696: per.effective_start_date and per.effective_end_date and
697: (enrt_cvg_thru_dt is null or
698: enrt_cvg_thru_dt=hr_api.g_eot) and
699: per.effective_end_date = hr_api.g_eot and
700: per.pl_id=pl.pl_id and
701: pl.business_group_id=p_epe_rec.business_group_id and
702: per.prtt_enrt_rslt_id<>p_old_result_id and
695: p_effective_date between
696: per.effective_start_date and per.effective_end_date and
697: (enrt_cvg_thru_dt is null or
698: enrt_cvg_thru_dt=hr_api.g_eot) and
699: per.effective_end_date = hr_api.g_eot and
700: per.pl_id=pl.pl_id and
701: pl.business_group_id=p_epe_rec.business_group_id and
702: per.prtt_enrt_rslt_id<>p_old_result_id and
703: per.sspndd_flag='N' and
718: per.business_group_id=p_epe_rec.business_group_id and
719: p_effective_date between
720: per.effective_start_date and per.effective_end_date and
721: (enrt_cvg_thru_dt is null or
722: enrt_cvg_thru_dt=hr_api.g_eot) and
723: per.pl_id=pl.pl_id and
724: pl.business_group_id=p_epe_rec.business_group_id and
725: per.prtt_enrt_rslt_id<>p_old_result_id and
726: per.sspndd_flag='N' and
749: per.business_group_id=p_epe_rec.business_group_id and
750: p_effective_date between
751: per.effective_start_date and per.effective_end_date and
752: (enrt_cvg_thru_dt is null or
753: enrt_cvg_thru_dt=hr_api.g_eot) and
754: per.effective_end_date = hr_api.g_eot and
755: per.pl_id=pl.pl_id and
756: pl.business_group_id=p_epe_rec.business_group_id and
757: per.prtt_enrt_rslt_id<>p_old_result_id and
750: p_effective_date between
751: per.effective_start_date and per.effective_end_date and
752: (enrt_cvg_thru_dt is null or
753: enrt_cvg_thru_dt=hr_api.g_eot) and
754: per.effective_end_date = hr_api.g_eot and
755: per.pl_id=pl.pl_id and
756: pl.business_group_id=p_epe_rec.business_group_id and
757: per.prtt_enrt_rslt_id<>p_old_result_id and
758: per.sspndd_flag='N' and
780: per.prtt_enrt_rslt_stat_cd is null and
781: p_effective_date between
782: per.effective_start_date and per.effective_end_date and
783: (enrt_cvg_thru_dt is null or
784: enrt_cvg_thru_dt=hr_api.g_eot) and
785: per.effective_end_date = hr_api.g_eot and
786: p_epe_rec.pl_id=per.pl_id and
787: p_epe_rec.pgm_id = per.pgm_id; -- Added : 4964766
788:
781: p_effective_date between
782: per.effective_start_date and per.effective_end_date and
783: (enrt_cvg_thru_dt is null or
784: enrt_cvg_thru_dt=hr_api.g_eot) and
785: per.effective_end_date = hr_api.g_eot and
786: p_epe_rec.pl_id=per.pl_id and
787: p_epe_rec.pgm_id = per.pgm_id; -- Added : 4964766
788:
789: cursor c_oipl_enrolment is
796: per.prtt_enrt_rslt_stat_cd is null and
797: p_effective_date between
798: per.effective_start_date and per.effective_end_date and
799: (enrt_cvg_thru_dt is null or
800: enrt_cvg_thru_dt=hr_api.g_eot) and
801: per.effective_end_date = hr_api.g_eot and
802: p_epe_rec.oipl_id=per.oipl_id and
803: p_epe_rec.pgm_id = per.pgm_id; -- Added : 4964766
804:
797: p_effective_date between
798: per.effective_start_date and per.effective_end_date and
799: (enrt_cvg_thru_dt is null or
800: enrt_cvg_thru_dt=hr_api.g_eot) and
801: per.effective_end_date = hr_api.g_eot and
802: p_epe_rec.oipl_id=per.oipl_id and
803: p_epe_rec.pgm_id = per.pgm_id; -- Added : 4964766
804:
805: cursor c_oiplip_enrolment is
812: per.prtt_enrt_rslt_stat_cd is null and
813: p_effective_date between
814: per.effective_start_date and per.effective_end_date and
815: (enrt_cvg_thru_dt is null or
816: enrt_cvg_thru_dt=hr_api.g_eot) and
817: per.effective_end_date = hr_api.g_eot and
818: cpp.pgm_id = p_epe_rec.pgm_id and
819: per.pl_id = cpp.pl_id and
820: cpp.plip_id = oiplip.plip_id and
813: p_effective_date between
814: per.effective_start_date and per.effective_end_date and
815: (enrt_cvg_thru_dt is null or
816: enrt_cvg_thru_dt=hr_api.g_eot) and
817: per.effective_end_date = hr_api.g_eot and
818: cpp.pgm_id = p_epe_rec.pgm_id and
819: per.pl_id = cpp.pl_id and
820: cpp.plip_id = oiplip.plip_id and
821: per.oipl_id = oiplip.oipl_id and
1213: --
1214: elsif l_val<>l_prvdd_val and nvl(p_enrt_mthd_cd,'E') <> 'O' then
1215: /*
1216: if l_effective_start_date=p_effective_date then
1217: l_datetrack_mode:=hr_api.g_correction;
1218: else
1219: l_datetrack_mode:=hr_api.g_update;
1220: end if;
1221: */
1215: /*
1216: if l_effective_start_date=p_effective_date then
1217: l_datetrack_mode:=hr_api.g_correction;
1218: else
1219: l_datetrack_mode:=hr_api.g_update;
1220: end if;
1221: */
1222: Get_DT_Upd_Mode
1223: (p_effective_date => p_effective_date,
1460: );
1461: hr_utility.set_location('CREATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),41);
1462: else
1463: /*if l_effective_start_date=p_effective_date then
1464: l_datetrack_mode:=hr_api.g_correction;
1465: else
1466: l_datetrack_mode:=hr_api.g_update;
1467: end if;*/
1468: Get_DT_Upd_Mode
1462: else
1463: /*if l_effective_start_date=p_effective_date then
1464: l_datetrack_mode:=hr_api.g_correction;
1465: else
1466: l_datetrack_mode:=hr_api.g_update;
1467: end if;*/
1468: Get_DT_Upd_Mode
1469: (p_effective_date => p_effective_date,
1470: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
1643: where person_id = p_person_id
1644: and comp_lvl_cd = 'PLANFC'
1645: and prtt_enrt_rslt_stat_cd is null
1646: -- added effective_end_date line Bug# 2254326
1647: and effective_end_date = hr_api.g_eot
1648: and p_effective_date between enrt_cvg_strt_dt
1649: and enrt_cvg_thru_dt;
1650: --
1651: cursor c_bnft_prvdd_ldgr is
1656: from ben_bnft_prvdd_ldgr_f bpl,
1657: ben_per_in_ler pil
1658: where bpl.acty_base_rt_id = l_acty_base_rt_id
1659: and bpl.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1660: -- and bpl.effective_end_date = hr_api.g_eot
1661: and p_effective_date between bpl.effective_start_date
1662: and bpl.effective_end_date
1663: and bpl.per_in_ler_id = pil.per_in_ler_id
1664: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1699: ben_per_in_ler pil
1700: where bpl.bnft_prvdr_pool_id = l_epe.bnft_prvdr_pool_id
1701: and bpl.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1702: and bpl.acty_base_rt_id = p_acty_base_rt_id
1703: --and bpl.effective_end_date = hr_api.g_eot
1704: and p_effective_date between bpl.effective_start_date
1705: and bpl.effective_end_date
1706: and bpl.per_in_ler_id = pil.per_in_ler_id
1707: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1730: and pen.person_id = p_person_id
1731: and pen.prtt_enrt_rslt_stat_cd is null
1732: and epe.business_group_id = pen.business_group_id
1733: and pen.sspndd_flag = 'N'
1734: and pen.enrt_cvg_thru_dt = hr_api.g_eot
1735: and pen.effective_end_date = hr_api.g_eot
1736: and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
1737: and enb.enrt_bnft_id = ecr.enrt_bnft_id
1738: and ecr.acty_base_rt_id = l_acty_base_rt_id;
1731: and pen.prtt_enrt_rslt_stat_cd is null
1732: and epe.business_group_id = pen.business_group_id
1733: and pen.sspndd_flag = 'N'
1734: and pen.enrt_cvg_thru_dt = hr_api.g_eot
1735: and pen.effective_end_date = hr_api.g_eot
1736: and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
1737: and enb.enrt_bnft_id = ecr.enrt_bnft_id
1738: and ecr.acty_base_rt_id = l_acty_base_rt_id;
1739: --
1803: if c_ldgr_exist%notfound then
1804: close c_ldgr_exist;
1805: else
1806:
1807: --nvl(l_rt_usg_cd,hr_api.g_varchar2)<>'FLXCR') then
1808: hr_utility.set_location('Deleting ledger='||to_char(l_bnft_prvdd_ldgr_id), 50);
1809: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
1810: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
1811: p_effective_start_date => l_effective_start_date,
1811: p_effective_start_date => l_effective_start_date,
1812: p_effective_end_date => l_effective_end_date,
1813: p_object_version_number => l_object_version_number,
1814: p_effective_date => p_effective_date,
1815: p_datetrack_mode => hr_api.g_zap,
1816: p_business_group_id => p_business_group_id
1817: );
1818: close c_ldgr_exist;
1819: end if;
1828: close c_rslt;
1829: hr_utility.set_location('Thru dt ' ||l_rslt.enrt_cvg_thru_dt, 70);
1830: hr_utility.set_location('stat Cd '||l_rslt.prtt_enrt_rslt_stat_cd, 70);
1831: hr_utility.set_location('sspndd '||l_rslt.sspndd_flag, 70);
1832: if l_rslt.enrt_cvg_thru_dt <>hr_api.g_eot or
1833: l_rslt.prtt_enrt_rslt_stat_cd in ('VOIDD','BCKDT') or
1834: nvl(l_rslt.sspndd_flag,'N') = 'Y' then -- Bug 5185351
1835: l_purge := 'Y';
1836: end if;
1882: -- Following call is delete of USED_VAL BPL entries.
1883: --
1884: if p_effective_date = l_bpl_esd
1885: then
1886: l_datetrack_mode := hr_api.g_zap;
1887: l_effective_date := l_bpl_esd ;
1888: else
1889: l_datetrack_mode := hr_api.g_delete;
1890: l_effective_date := p_effective_date - 1 ;
1885: then
1886: l_datetrack_mode := hr_api.g_zap;
1887: l_effective_date := l_bpl_esd ;
1888: else
1889: l_datetrack_mode := hr_api.g_delete;
1890: l_effective_date := p_effective_date - 1 ;
1891: end if;
1892: --
1893: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
1969: --
1970: -- Bug 5500864
1971: if p_effective_date = l_bpl_esd
1972: then
1973: l_datetrack_mode := hr_api.g_zap;
1974: l_effective_date := l_bpl_esd ;
1975: else
1976: l_datetrack_mode := hr_api.g_delete;
1977: l_effective_date := p_effective_date - 1 ;
1972: then
1973: l_datetrack_mode := hr_api.g_zap;
1974: l_effective_date := l_bpl_esd ;
1975: else
1976: l_datetrack_mode := hr_api.g_delete;
1977: l_effective_date := p_effective_date - 1 ;
1978: end if;
1979: --
1980: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
2119: -- rt_usg_cd<>'FLXCR' and not directly enrolled
2120: -- then remove the ledger entry
2121: --
2122: if ((not l_person_enrolled) or
2123: (nvl(l_rt_usg_cd,hr_api.g_varchar2)<>'FLXCR' and
2124: l_epe.prtt_enrt_rslt_id is null)) then
2125: hr_utility.set_location('Deleting ledger='||to_char(l_bnft_prvdd_ldgr_id), 50);
2126: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
2127: p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
2128: p_effective_start_date => l_effective_start_date,
2129: p_effective_end_date => l_effective_end_date,
2130: p_object_version_number => l_object_version_number,
2131: p_effective_date => p_effective_date,
2132: p_datetrack_mode => hr_api.g_zap,
2133: p_business_group_id => p_business_group_id
2134: );
2135: hr_utility.set_location(l_proc, 60);
2136: end if;
2278: open c_flex_credit_choice;
2279: fetch c_flex_credit_choice into l_flex_credit_choice;
2280: --
2281: if (c_flex_credit_choice%notfound and
2282: nvl(p_rt_val,hr_api.g_number) <> hr_api.g_number ) then --Bug 3864152
2283: hr_utility.set_location('BEN_91726_NO_FLX_CR_CHOICE', 20);
2284: fnd_message.set_name('BEN','BEN_91726_NO_FLX_CR_CHOICE');
2285: fnd_message.set_token('PROC',l_proc);
2286: fnd_message.set_token('PERSON_ID', to_char(p_person_id));
2457: p_effective_end_date => l_effective_end_date,
2458: p_enrt_mthd_cd => p_enrt_mthd_cd,
2459: p_enrt_bnft_id => null,
2460: p_bnft_val => null,
2461: p_datetrack_mode => hr_api.g_insert,
2462: p_suspend_flag => l_suspend_flag,
2463: p_object_version_number => l_flex_credit_choice.object_version_number,
2464: p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
2465: p_rt_val1 => p_rt_val,
2495: p_effective_end_date => l_effective_end_date,
2496: p_enrt_mthd_cd => p_enrt_mthd_cd,
2497: p_enrt_bnft_id => null,
2498: p_bnft_val => null,
2499: p_datetrack_mode => hr_api.g_correction,
2500: p_suspend_flag => l_suspend_flag,
2501: p_object_version_number => l_flex_credit_choice.object_version_number,
2502: p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
2503: p_rt_val1 => p_rt_val,
2533: p_effective_end_date => l_effective_end_date,
2534: p_enrt_mthd_cd => p_enrt_mthd_cd,
2535: p_enrt_bnft_id => null,
2536: p_bnft_val => null,
2537: p_datetrack_mode => hr_api.g_correction,
2538: p_suspend_flag => l_suspend_flag,
2539: p_object_version_number => l_flex_credit_choice.object_version_number,
2540: p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
2541: p_rt_val1 => l_prnt_rt_val,
2732: -- update ledger row
2733: --
2734: /*
2735: if l_effective_start_date=p_effective_date then
2736: l_datetrack_mode:=hr_api.g_correction;
2737: else
2738: l_datetrack_mode:=hr_api.g_update;
2739: end if;
2740: */
2734: /*
2735: if l_effective_start_date=p_effective_date then
2736: l_datetrack_mode:=hr_api.g_correction;
2737: else
2738: l_datetrack_mode:=hr_api.g_update;
2739: end if;
2740: */
2741: Get_DT_Upd_Mode
2742: (p_effective_date => p_effective_date,
3221: then
3222: hr_utility.set_location(l_proc, 310);
3223: /* if l_effective_start_date=p_effective_date then
3224: hr_utility.set_location(l_proc,320 );
3225: l_datetrack_mode:=hr_api.g_correction;
3226: else
3227: hr_utility.set_location(l_proc,330 );
3228: l_datetrack_mode:=hr_api.g_update;
3229: end if; */
3224: hr_utility.set_location(l_proc,320 );
3225: l_datetrack_mode:=hr_api.g_correction;
3226: else
3227: hr_utility.set_location(l_proc,330 );
3228: l_datetrack_mode:=hr_api.g_update;
3229: end if; */
3230: Get_DT_Upd_Mode
3231: (p_effective_date => p_effective_date,
3232: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
3243: ,p_prvdd_val => null
3244: ,p_used_val => null
3245: ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
3246: ,p_acty_base_rt_id => l_acty_base_rt_id
3247: ,p_per_in_ler_id => nvl(p_per_in_ler_id, hr_api.g_number) /* Bug 4251187 */
3248: ,p_prtt_enrt_rslt_id => g_credit_pool_result_id
3249: ,p_business_group_id => p_business_group_id
3250: ,p_object_version_number => l_object_version_number
3251: ,p_cash_recd_val => l_cash_val
3827: elsif l_old_rlovr_val<>l_balance then
3828: hr_utility.set_location(l_proc, 110);
3829: /*
3830: if l_effective_start_date=p_effective_date then
3831: l_datetrack_mode:=hr_api.g_correction;
3832: else
3833: l_datetrack_mode:=hr_api.g_update;
3834: end if;
3835: */
3829: /*
3830: if l_effective_start_date=p_effective_date then
3831: l_datetrack_mode:=hr_api.g_correction;
3832: else
3833: l_datetrack_mode:=hr_api.g_update;
3834: end if;
3835: */
3836: Get_DT_Upd_Mode
3837: (p_effective_date => p_effective_date,
4274: -- call election_information api to create prtt enrt result
4275: --
4276: --
4277: if l_rlovr_chc.prtt_enrt_rslt_id is not null then
4278: l_datetrack_mode:=hr_api.g_correction;
4279: else
4280: l_datetrack_mode:=hr_api.g_insert;
4281: end if;
4282: --
4276: --
4277: if l_rlovr_chc.prtt_enrt_rslt_id is not null then
4278: l_datetrack_mode:=hr_api.g_correction;
4279: else
4280: l_datetrack_mode:=hr_api.g_insert;
4281: end if;
4282: --
4283: hr_utility.set_location(l_proc, 70);
4284: ben_election_information.election_information(
4505: and pen.prtt_enrt_rslt_stat_cd is null
4506: and pen.enrt_mthd_cd = 'D'
4507: and p_effective_date between
4508: pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
4509: and pen.effective_end_date = hr_api.g_eot ;
4510:
4511: l_default_enrt_flag varchar2(1) := 'N' ;
4512: l_dummy_var varchar2(1) ;
4513: begin
5222: ,p_mlt_cd => l_prv_rec.mlt_cd
5223: ,p_acty_ref_perd_cd => l_prv_rec.acty_ref_perd_cd
5224: ,p_rt_val => p_val
5225: ,p_rt_strt_dt => l_rt_strt_dt
5226: ,p_rt_end_dt => hr_api.g_eot
5227: ,p_ann_rt_val => null
5228: ,p_bnft_rt_typ_cd => l_prv_rec.bnft_rt_typ_cd
5229: ,p_cmcd_ref_perd_cd => l_prv_rec.cmcd_ref_perd_cd
5230: ,p_cmcd_rt_val => p_val
5304: and prv.prtt_rt_val_stat_cd is null
5305: and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
5306: and pen.person_id = p_person_id
5307: and pen.prtt_enrt_rslt_stat_cd is null
5308: and pen.enrt_cvg_thru_dt = hr_api.g_eot
5309: and p_effective_date between
5310: pen.effective_start_date and pen.effective_end_date
5311: and prv.acty_base_rt_id = abr.acty_base_rt_id
5312: and p_effective_date between
5332: --
5333: hr_utility.set_location(l_proc,10);
5334: if p_effective_date = l_bpl_rec.effective_start_date then
5335: --
5336: l_datetrack_mode := hr_api.g_zap;
5337: l_effective_date := p_effective_date;
5338: --
5339: else
5340: --
5337: l_effective_date := p_effective_date;
5338: --
5339: else
5340: --
5341: l_datetrack_mode := hr_api.g_delete;
5342: l_effective_date := p_effective_date -1;
5343: --
5344: end if;
5345: --
5387: -- enrollment.
5388: --
5389: if p_effective_date = l_pen_effective_start_date then
5390: --
5391: l_datetrack_mode := hr_api.g_zap;
5392: --
5393: else
5394: --
5395: l_datetrack_mode := hr_api.g_delete;
5391: l_datetrack_mode := hr_api.g_zap;
5392: --
5393: else
5394: --
5395: l_datetrack_mode := hr_api.g_delete;
5396: --
5397: end if;
5398: --
5399: ben_prtt_enrt_result_api.delete_enrollment(
5502: prv.prtt_enrt_rslt_id = rslt.prtt_enrt_rslt_id and
5503: rslt.person_id=p_person_id and
5504: rslt.business_group_id=p_business_group_id and
5505: rslt.prtt_enrt_rslt_stat_cd is null and
5506: rslt.enrt_cvg_thru_dt = hr_api.g_eot and
5507: prv.business_group_id =p_business_group_id and
5508: prv.prtt_rt_val_stat_cd is null and
5509: p_effective_date between
5510: rslt.effective_start_date and rslt.effective_end_date and
5821: --
5822: hr_utility.set_location(l_proc, 110);
5823: /*
5824: if l_effective_start_date=p_effective_date then
5825: l_datetrack_mode:=hr_api.g_correction;
5826: else
5827: l_datetrack_mode:=hr_api.g_update;
5828: end if;
5829: */
5823: /*
5824: if l_effective_start_date=p_effective_date then
5825: l_datetrack_mode:=hr_api.g_correction;
5826: else
5827: l_datetrack_mode:=hr_api.g_update;
5828: end if;
5829: */
5830: Get_DT_Upd_Mode
5831: (p_effective_date => p_effective_date,
6451: ,p_mlt_cd => l_abr_rec.rt_mlt_cd
6452: ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
6453: ,p_rt_val => p_def_exc_amount
6454: ,p_rt_strt_dt => l_rt_strt_dt
6455: ,p_rt_end_dt => hr_api.g_eot
6456: ,p_ann_rt_val => null
6457: ,p_bnft_rt_typ_cd => l_abr_rec.bnft_rt_typ_cd
6458: ,p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd
6459: ,p_cmcd_rt_val => p_def_exc_amount
6542: ,p_mlt_cd => l_abr_rec.rt_mlt_cd
6543: ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
6544: ,p_rt_val => l_child_rt_val
6545: ,p_rt_strt_dt => l_rt_strt_dt
6546: ,p_rt_end_dt => hr_api.g_eot
6547: ,p_ann_rt_val => null
6548: ,p_bnft_rt_typ_cd => l_abr_rec.bnft_rt_typ_cd
6549: ,p_cmcd_ref_perd_cd => l_cmcd_ref_perd_cd
6550: ,p_cmcd_rt_val => l_child_rt_val
6626: ben_per_in_ler pil,
6627: ben_prtt_enrt_rslt_f flex_pen
6628: where enrt_pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6629: and enrt_pen.prtt_enrt_rslt_stat_cd is null
6630: and enrt_pen.enrt_cvg_thru_dt = hr_api.g_eot
6631: and p_effective_date between
6632: enrt_pen.effective_start_date and enrt_pen.effective_end_date
6633: and enrt_pen.prtt_enrt_rslt_id = enrt_prv.prtt_enrt_rslt_id
6634: and enrt_prv.prtt_rt_val_stat_cd is null
6638: bpl.effective_start_date and bpl.effective_end_date
6639: and bpl.prtt_enrt_rslt_id = flex_pen.prtt_enrt_rslt_id
6640: and flex_pen.person_id = p_person_id
6641: and flex_pen.prtt_enrt_rslt_stat_cd is null
6642: and flex_pen.enrt_cvg_thru_dt = hr_api.g_eot
6643: and p_effective_date between
6644: flex_pen.effective_start_date and flex_pen.effective_end_date
6645: and pil.per_in_ler_id=bpl.per_in_ler_id
6646: and pil.business_group_id=bpl.business_group_id
6670: ben_bnft_prvdr_pool_f bpp
6671: where flex_pen.person_id = p_person_id
6672: and flex_pen.pgm_id = p_pgm_id
6673: and flex_pen.prtt_enrt_rslt_stat_cd is null
6674: and flex_pen.enrt_cvg_thru_dt = hr_api.g_eot
6675: and p_effective_date between
6676: flex_pen.effective_start_date and flex_pen.effective_end_date
6677: and flex_pen.prtt_enrt_rslt_id = bpl.prtt_enrt_rslt_id
6678: and bpl.prvdd_val is not null
6806: l_flex_rslt_id := l_dbt_bpl_rec.prtt_enrt_rslt_id;
6807: --
6808: /* if l_dbt_bpl_rec.effective_start_date = p_effective_date then
6809: --
6810: l_datetrack_mode := hr_api.g_zap;
6811: l_effective_date := p_effective_date;
6812: --
6813: else
6814: --
6811: l_effective_date := p_effective_date;
6812: --
6813: else
6814: --
6815: l_datetrack_mode := hr_api.g_delete;
6816: l_effective_date := p_effective_date -1;
6817: --
6818: end if;*/
6819: Open c_bpl_esd(l_dbt_bpl_rec.bnft_prvdd_ldgr_id);
6823: if l_bpl_esd_rec.effective_start_date = p_effective_date and
6824: l_count = 1 then
6825: --
6826: l_ovn := l_bpl_esd_rec.object_version_number;
6827: l_datetrack_mode := hr_api.g_zap;
6828: l_effective_date := p_effective_date;
6829: exit;
6830: --
6831: elsif (p_effective_date-1) between l_bpl_esd_rec.effective_start_date
6831: elsif (p_effective_date-1) between l_bpl_esd_rec.effective_start_date
6832: and l_bpl_esd_rec.effective_end_date then
6833: --
6834: l_ovn := l_bpl_esd_rec.object_version_number;
6835: l_datetrack_mode := hr_api.g_delete;
6836: l_effective_date := p_effective_date - 1;
6837: exit;
6838: --
6839: end if;