[Home] [Help]
1715: ,ecd.cvg_pndg_flag
1716: ,ecd.ovrdn_flag
1717: ,ecd.ovrdn_thru_dt
1718: ,ecd.dpnt_person_id
1719: from ben_elig_cvrd_dpnt_f ecd,
1720: ben_per_in_ler pil
1721: where ecd.business_group_id=p_business_group_id
1722: and p_effective_date between
1723: ecd.effective_start_date and ecd.effective_end_date
1737: ,ecd.cvg_pndg_flag
1738: ,ecd.ovrdn_flag
1739: ,ecd.ovrdn_thru_dt
1740: ,ecd.dpnt_person_id
1741: from ben_elig_cvrd_dpnt_f ecd,
1742: ben_per_in_ler pil
1743: where p_effective_date between
1744: ecd.effective_start_date and ecd.effective_end_date
1745: and pil.per_in_ler_id=ecd.per_in_ler_id
1763: --
1764: hr_utility.set_location ('Entering '||l_proc,10);
1765: --
1766: select count(*) into l_bckdt_ecd_count
1767: from ben_elig_cvrd_dpnt_f ecd,
1768: ben_per_in_ler pil
1769: where ecd.business_group_id=p_business_group_id
1770: and p_effective_date between
1771: ecd.effective_start_date and ecd.effective_end_date
1866: --
1867: -- Following are the tables whose data will be compared to
1868: -- find any differences exists between the two runs of same ler.
1869: --
1870: -- ben_elig_cvrd_dpnt_f
1871: -- ben_prtt_enrt_actn_f
1872: -- ben_elig_per_elctbl_chc -- 9999 done
1873: -- ben_enrt_bnft
1874: -- ben_enrt_rt
2252: --
2253: -- Following are the tables whose data will be compared to
2254: -- find any differences exists between the two runs of same ler.
2255: --
2256: -- ben_elig_cvrd_dpnt_f
2257: -- ben_prtt_enrt_actn_f
2258: -- ben_elig_per_elctbl_chc
2259: -- ben_enrt_bnft
2260: -- ben_enrt_rt
4665: pdp_old.PROGRAM_UPDATE_DATE,
4666: pdp_old.OBJECT_VERSION_NUMBER,
4667: pdp_old.elig_cvrd_dpnt_id,
4668: pdp_old.EFFECTIVE_START_DATE
4669: from ben_elig_cvrd_dpnt_f pdp_old
4670: where pdp_old.per_in_ler_id = p_bckdt_per_in_ler_id
4671: and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
4672: and pdp_old.business_group_id = p_business_group_id
4673: union
4728: from ben_le_clsn_n_rstr pdp_old
4729: where pdp_old.per_in_ler_id = p_bckdt_per_in_ler_id
4730: and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
4731: and pdp_old.business_group_id = p_business_group_id
4732: and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'
4733: order by 1; -- pdp_old.effective_end_date; -- Low to High
4734: --
4735: --5692797
4736: cursor c_chk_valid_pdp(p_elig_cvrd_dpnt_id number) is
4734: --
4735: --5692797
4736: cursor c_chk_valid_pdp(p_elig_cvrd_dpnt_id number) is
4737: select 'Y'
4738: from ben_elig_cvrd_dpnt_f pdp_old
4739: where pdp_old.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
4740: and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
4741: and (pdp_old.effective_end_date = hr_api.g_eot or
4742: pdp_old.effective_end_date = (select max(effective_end_date)
4739: where pdp_old.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
4740: and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
4741: and (pdp_old.effective_end_date = hr_api.g_eot or
4742: pdp_old.effective_end_date = (select max(effective_end_date)
4743: from ben_elig_cvrd_dpnt_f
4744: where elig_cvrd_dpnt_id = pdp_old.elig_cvrd_dpnt_id
4745: and prtt_enrt_rslt_id = pdp_old.prtt_enrt_rslt_id))
4746: and (pdp_old.cvg_thru_dt is null or pdp_old.cvg_thru_dt = hr_api.g_eot)
4747: union
4747: union
4748: select 'Y'
4749: from ben_le_clsn_n_rstr pdp_old
4750: where pdp_old.bkup_tbl_id = p_elig_cvrd_dpnt_id
4751: and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'
4752: and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
4753: and ((pdp_old.cvg_thru_dt is null or pdp_old.cvg_thru_dt = hr_api.g_eot) and
4754: pdp_old.effective_end_date = hr_api.g_eot
4755: );
4757: l_chk_valid_pdp char;
4758: --
4759: cursor c_pen_dpnts(cp_dpnt_person_id in number) is
4760: select pdp.*
4761: from ben_elig_cvrd_dpnt_f pdp ,
4762: ben_per_in_ler pil
4763: where pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4764: and pdp.cvg_strt_dt is not null
4765: and nvl(pdp.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
6051: ,v_effective_date date) is
6052: select pdp2.elig_cvrd_dpnt_id new_pdp_id,
6053: pdp2.object_version_number new_pdp_ovn,
6054: pdp1.*
6055: from ben_elig_cvrd_dpnt_f pdp1,
6056: ben_elig_cvrd_dpnt_f pdp2
6057: where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
6058: and pdp2.prtt_enrt_rslt_id = v_new_pen_id
6059: and pdp1.dpnt_person_id = pdp2.dpnt_person_id
6052: select pdp2.elig_cvrd_dpnt_id new_pdp_id,
6053: pdp2.object_version_number new_pdp_ovn,
6054: pdp1.*
6055: from ben_elig_cvrd_dpnt_f pdp1,
6056: ben_elig_cvrd_dpnt_f pdp2
6057: where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
6058: and pdp2.prtt_enrt_rslt_id = v_new_pen_id
6059: and pdp1.dpnt_person_id = pdp2.dpnt_person_id
6060: and pdp1.ovrdn_flag = 'Y'
8655: ,v_effective_date date) is
8656: select pdp2.elig_cvrd_dpnt_id new_pdp_id,
8657: pdp2.object_version_number new_pdp_ovn,
8658: pdp1.*
8659: from ben_elig_cvrd_dpnt_f pdp1,
8660: ben_elig_cvrd_dpnt_f pdp2
8661: where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
8662: and pdp2.prtt_enrt_rslt_id = v_new_pen_id
8663: and pdp1.dpnt_person_id = pdp2.dpnt_person_id
8656: select pdp2.elig_cvrd_dpnt_id new_pdp_id,
8657: pdp2.object_version_number new_pdp_ovn,
8658: pdp1.*
8659: from ben_elig_cvrd_dpnt_f pdp1,
8660: ben_elig_cvrd_dpnt_f pdp2
8661: where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
8662: and pdp2.prtt_enrt_rslt_id = v_new_pen_id
8663: and pdp1.dpnt_person_id = pdp2.dpnt_person_id
8664: and pdp1.ovrdn_flag = 'Y'
10280: ,v_effective_date date) is
10281: select pdp2.elig_cvrd_dpnt_id new_pdp_id,
10282: pdp2.object_version_number new_pdp_ovn,
10283: pdp1.*
10284: from ben_elig_cvrd_dpnt_f pdp1,
10285: ben_elig_cvrd_dpnt_f pdp2
10286: where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
10287: and pdp2.prtt_enrt_rslt_id = v_new_pen_id
10288: and pdp1.dpnt_person_id = pdp2.dpnt_person_id
10281: select pdp2.elig_cvrd_dpnt_id new_pdp_id,
10282: pdp2.object_version_number new_pdp_ovn,
10283: pdp1.*
10284: from ben_elig_cvrd_dpnt_f pdp1,
10285: ben_elig_cvrd_dpnt_f pdp2
10286: where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
10287: and pdp2.prtt_enrt_rslt_id = v_new_pen_id
10288: and pdp1.dpnt_person_id = pdp2.dpnt_person_id
10289: and pdp1.ovrdn_flag = 'Y'