[Home] [Help]
9: g_bckt_csd_lf_evt_ocrd_dt date;
10: --
11: type g_pgm_rec is record
12: (pgm_id ben_pgm_f.pgm_id%type,
13: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
14: multi_row_edit_done boolean,
15: non_automatics_flag boolean,
16: max_enrt_esd date);
17: --
16: max_enrt_esd date);
17: --
18: type g_pl_rec is record
19: (pl_id ben_pl_f.pl_id%type,
20: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
21: multi_row_edit_done boolean,
22: max_enrt_esd date);
23: --
24: type g_enrt_rec is record
21: multi_row_edit_done boolean,
22: max_enrt_esd date);
23: --
24: type g_enrt_rec is record
25: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
26: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
27: bckdt_enrt_ovridn_flag varchar2(1),
28: bckdt_enrt_cvg_strt_dt date,
29: bckdt_enrt_cvg_thru_dt date,
22: max_enrt_esd date);
23: --
24: type g_enrt_rec is record
25: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
26: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
27: bckdt_enrt_ovridn_flag varchar2(1),
28: bckdt_enrt_cvg_strt_dt date,
29: bckdt_enrt_cvg_thru_dt date,
30: enrt_ovrid_thru_dt date,
27: bckdt_enrt_ovridn_flag varchar2(1),
28: bckdt_enrt_cvg_strt_dt date,
29: bckdt_enrt_cvg_thru_dt date,
30: enrt_ovrid_thru_dt date,
31: enrt_ovrid_rsn_cd ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%type,
32: g_sys_date date,
33: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
34: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
35: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
29: bckdt_enrt_cvg_thru_dt date,
30: enrt_ovrid_thru_dt date,
31: enrt_ovrid_rsn_cd ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%type,
32: g_sys_date date,
33: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
34: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
35: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
36: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
37: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
30: enrt_ovrid_thru_dt date,
31: enrt_ovrid_rsn_cd ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%type,
32: g_sys_date date,
33: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
34: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
35: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
36: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
37: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
38: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
31: enrt_ovrid_rsn_cd ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%type,
32: g_sys_date date,
33: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
34: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
35: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
36: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
37: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
38: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
39: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
32: g_sys_date date,
33: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
34: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
35: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
36: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
37: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
38: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
39: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
40: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
33: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
34: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
35: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
36: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
37: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
38: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
39: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
40: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
41: ler_id ben_ler_f.ler_id%type,
34: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
35: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
36: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
37: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
38: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
39: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
40: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
41: ler_id ben_ler_f.ler_id%type,
42: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
35: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
36: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
37: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
38: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
39: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
40: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
41: ler_id ben_ler_f.ler_id%type,
42: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
43: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
36: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
37: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
38: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
39: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
40: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
41: ler_id ben_ler_f.ler_id%type,
42: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
43: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
44: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
41: ler_id ben_ler_f.ler_id%type,
42: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
43: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
44: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
45: effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%type
46: );
47: --
48: type g_pgm_table is table of g_pgm_rec index by binary_integer;
49: type g_pl_table is table of g_pl_rec index by binary_integer;
134: pen.REQUEST_ID,
135: pen.RPLCS_SSPNDD_RSLT_ID,
136: pen.SSPNDD_FLAG,
137: pen.UOM
138: from ben_prtt_enrt_rslt_f pen,
139: ben_per_in_ler pil,
140: ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
141: where pil.per_in_ler_id = c_bckdt_per_in_ler_id
142: and pil.person_id = c_person_id
154: and ( c_pl_id is null or
155: pen.pl_id = c_pl_id )
156: and (pen.effective_end_date = hr_api.g_eot or
157: pen.effective_end_date = (select max(effective_end_date)
158: from ben_prtt_enrt_rslt_f
159: where prtt_enrt_rslt_id =
160: pen.prtt_enrt_rslt_id))
161: and (pen.enrt_cvg_thru_dt is null or
162: pen.enrt_cvg_thru_dt = hr_api.g_eot
163: )
164: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
165: and pen.prtt_enrt_rslt_id not in (
166: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
167: from ben_prtt_enrt_rslt_f pen_inner,
168: ben_per_in_ler pil_inner
169: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
170: and pil_inner.person_id = c_person_id
171: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
179: ben_per_in_ler pil_inner
180: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
181: and pil_inner.person_id = c_person_id
182: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
183: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
184: and (pen_inner.enrt_cvg_thru_dt is null or
185: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
186: )
187: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
277: pel.pl_id = c_pl_id )
278: and (pel.dflt_asnd_dt is not null or
279: pel.elcns_made_dt is not null)
280: AND pil.per_in_ler_id = pen.per_in_ler_id
281: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
282: --To Handle Programs and Plans not in Program
283: and ((pen.pgm_id is null and c_pgm_id is null) or
284: pen.pgm_id = c_pgm_id )
285: and ( c_pl_id is null or
290: )
291: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
292: and pen.bkup_tbl_id not in (
293: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
294: from ben_prtt_enrt_rslt_f pen_inner,
295: ben_per_in_ler pil_inner
296: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
297: and pil_inner.person_id = c_person_id
298: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
306: ben_per_in_ler pil_inner
307: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
308: and pil_inner.person_id = c_person_id
309: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
310: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
311: and (pen_inner.enrt_cvg_thru_dt is null or
312: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
313: )
314: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
568: -- life events.
569: --
570: cursor c_enrt_rslt_exists is
571: select 'Y'
572: from ben_prtt_enrt_rslt_f pen,
573: ben_per_in_ler pil
574: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
575: and pil.business_group_id = p_business_group_id
576: and pen.per_in_ler_id = pil.per_in_ler_id
620: --
621: -- Following are the tables whose data will be compared to
622: -- find any differences exists between the two runs of same ler.
623: --
624: -- ben_prtt_enrt_rslt_f
625: --
626: cursor c_pen_dat(p_pil_id number) is
627: select
628: pen.OIPL_ID,
628: pen.OIPL_ID,
629: pen.PGM_ID,
630: pen.PL_ID,
631: pen.SSPNDD_FLAG
632: from ben_prtt_enrt_rslt_f pen,
633: ben_per_in_ler pil
634: where pil.per_in_ler_id = p_pil_id
635: and pil.person_id = p_person_id
636: and pil.business_group_id = p_business_group_id
842: -- life events, if so stop the restore process.
843: --
844: cursor c_ele_md_for_inter_pil is
845: select 'Y'
846: from ben_prtt_enrt_rslt_f pen,
847: ben_ler_f ler
848: where pen.per_in_ler_id <> p_bckdt_per_in_ler_id
849: and pen.person_id = p_person_id
850: and pen.prtt_enrt_rslt_stat_cd is null
3023: --
3024: -- Following are the tables whose data will be compared to
3025: -- find any differences exists between the two runs of same ler.
3026: --
3027: -- ben_prtt_enrt_rslt_f
3028: -- ben_prtt_rt_val
3029: --
3030: l_bckdt_pen_cnt number := 0;
3031: l_bckdt_pen_cnt_temp number := 0;
3110: pen.REQUEST_ID,
3111: pen.RPLCS_SSPNDD_RSLT_ID,
3112: pen.SSPNDD_FLAG,
3113: pen.UOM
3114: from ben_prtt_enrt_rslt_f pen,
3115: ben_per_in_ler pil
3116: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3117: and pil.person_id = p_person_id
3118: and pil.business_group_id = p_business_group_id
3202: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3203: and pil.person_id = p_person_id
3204: and pil.business_group_id = p_business_group_id
3205: AND pil.per_in_ler_id = pen.per_in_ler_id
3206: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
3207: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP');
3208: -- and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3209: --
3210: cursor c_curr_pen_dat is
3208: -- and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3209: --
3210: cursor c_curr_pen_dat is
3211: select pen.*
3212: from ben_prtt_enrt_rslt_f pen,
3213: ben_per_in_ler pil
3214: where pil.per_in_ler_id = p_per_in_ler_id
3215: and pil.person_id = p_person_id
3216: and pil.business_group_id = p_business_group_id
3237: -- Bug 1266433 : Do not consider the results of flex credits and
3238: -- imputed income as they are not considered in c_curr_pen_dat
3239: --
3240: select count(*) into l_bckdt_pen_cnt_temp
3241: from ben_prtt_enrt_rslt_f pen,
3242: ben_per_in_ler pil
3243: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3244: and pil.person_id = p_person_id
3245: and pil.business_group_id = p_business_group_id
3253: and pil.person_id = p_person_id
3254: and pil.business_group_id = p_business_group_id
3255: AND pil.per_in_ler_id = pen.per_in_ler_id
3256: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
3257: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F';
3258: --
3259: l_bckdt_pen_cnt := l_bckdt_pen_cnt_temp + l_bckdt_pen_cnt;
3260: l_curr_pen_table.delete;
3261: l_next_row := nvl(l_curr_pen_table.LAST, 0) + 1;
3616: pen.PGM_ID,
3617: pen.PL_ID,
3618: pen.PL_TYP_ID,
3619: pen.PTIP_ID
3620: from ben_prtt_enrt_rslt_f pen,
3621: ben_per_in_ler pil
3622: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3623: and pil.person_id = p_person_id
3624: and pil.business_group_id = p_business_group_id
3638: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3639: and pil.person_id = p_person_id
3640: and pil.business_group_id = p_business_group_id
3641: AND pil.per_in_ler_id = pen.per_in_ler_id
3642: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
3643: and pen.comp_lvl_cd = 'PLANFC'
3644: order by 1; -- pen.effective_end_date; -- Low to High
3645: --
3646: cursor c_curr_pen(cp_pl_id in number, cp_oipl_id in number,
3653: pen.PL_ID,
3654: pen.PL_TYP_ID,
3655: pen.PTIP_ID,
3656: pen.effective_start_date
3657: from ben_prtt_enrt_rslt_f pen,
3658: ben_per_in_ler pil
3659: where pil.per_in_ler_id = p_per_in_ler_id
3660: and pil.person_id = p_person_id
3661: and pil.business_group_id = p_business_group_id
4783: l_epe_dpnt_rec c_epe_dpnt%rowtype;
4784: -- bug 5895645
4785: cursor get_dt_c is
4786: select enrt_cvg_strt_dt
4787: from ben_prtt_enrt_rslt_f
4788: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4789: and p_effective_date between effective_start_date
4790: and effective_end_date;
4791: -- end 5895645
5703: pen.REQUEST_ID,
5704: pen.RPLCS_SSPNDD_RSLT_ID,
5705: pen.SSPNDD_FLAG,
5706: pen.UOM
5707: from ben_prtt_enrt_rslt_f pen,
5708: ben_per_in_ler pil
5709: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
5710: and pil.person_id = p_person_id
5711: and pil.business_group_id = p_business_group_id
5712: and pil.per_in_ler_id = pen.per_in_ler_id
5713: /*Pick up both end-dated and non-end-dated results*/
5714: and (pen.effective_end_date = hr_api.g_eot or
5715: pen.effective_end_date = (select max(effective_end_date)
5716: from ben_prtt_enrt_rslt_f
5717: where prtt_enrt_rslt_id =
5718: pen.prtt_enrt_rslt_id))
5719: and (pen.enrt_cvg_thru_dt is null or
5720: pen.enrt_cvg_thru_dt = hr_api.g_eot
5721: )
5722: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
5723: and pen.prtt_enrt_rslt_id not in (
5724: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
5725: from ben_prtt_enrt_rslt_f pen_inner,
5726: ben_per_in_ler pil_inner
5727: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
5728: and pil_inner.person_id = p_person_id
5729: and pil_inner.business_group_id = p_business_group_id
5739: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
5740: and pil_inner.person_id = p_person_id
5741: and pil_inner.business_group_id = p_business_group_id
5742: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
5743: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
5744: and (pen_inner.enrt_cvg_thru_dt is null or
5745: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
5746: )
5747: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
5830: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
5831: and pil.person_id = p_person_id
5832: and pil.business_group_id = p_business_group_id
5833: AND pil.per_in_ler_id = pen.per_in_ler_id
5834: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
5835: and ((pen.enrt_cvg_thru_dt is null or
5836: pen.enrt_cvg_thru_dt = hr_api.g_eot) and
5837: --bug#2604375 - added to control updated result rows for the same per_in_ler
5838: pen.effective_end_date = hr_api.g_eot
5839: )
5840: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
5841: and pen.bkup_tbl_id not in (
5842: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
5843: from ben_prtt_enrt_rslt_f pen_inner,
5844: ben_per_in_ler pil_inner
5845: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
5846: and pil_inner.person_id = p_person_id
5847: and pil_inner.business_group_id = p_business_group_id
5857: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
5858: and pil_inner.person_id = p_person_id
5859: and pil_inner.business_group_id = p_business_group_id
5860: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
5861: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
5862: and (pen_inner.enrt_cvg_thru_dt is null or
5863: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
5864: )
5865: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
5953: l_count number;
5954: --
5955: type pgm_rec is record
5956: (pgm_id ben_pgm_f.pgm_id%type,
5957: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
5958: multi_row_edit_done boolean,
5959: non_automatics_flag boolean,
5960: max_enrt_esd date);
5961: --
5960: max_enrt_esd date);
5961: --
5962: type pl_rec is record
5963: (pl_id ben_pl_f.pl_id%type,
5964: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
5965: multi_row_edit_done boolean,
5966: max_enrt_esd date);
5967: --
5968: type enrt_rec is record
5965: multi_row_edit_done boolean,
5966: max_enrt_esd date);
5967: --
5968: type enrt_rec is record
5969: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
5970: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
5971: bckdt_enrt_ovridn_flag varchar2(1),
5972: bckdt_enrt_cvg_strt_dt date,
5973: bckdt_enrt_cvg_thru_dt date,
5966: max_enrt_esd date);
5967: --
5968: type enrt_rec is record
5969: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
5970: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
5971: bckdt_enrt_ovridn_flag varchar2(1),
5972: bckdt_enrt_cvg_strt_dt date,
5973: bckdt_enrt_cvg_thru_dt date,
5974: g_sys_date date,
5971: bckdt_enrt_ovridn_flag varchar2(1),
5972: bckdt_enrt_cvg_strt_dt date,
5973: bckdt_enrt_cvg_thru_dt date,
5974: g_sys_date date,
5975: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
5976: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5977: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5978: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5979: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5972: bckdt_enrt_cvg_strt_dt date,
5973: bckdt_enrt_cvg_thru_dt date,
5974: g_sys_date date,
5975: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
5976: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5977: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5978: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5979: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5980: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5973: bckdt_enrt_cvg_thru_dt date,
5974: g_sys_date date,
5975: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
5976: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5977: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5978: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5979: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5980: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5981: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5974: g_sys_date date,
5975: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
5976: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5977: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5978: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5979: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5980: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5981: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5982: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
5975: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
5976: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5977: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5978: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5979: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5980: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5981: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5982: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
5983: ler_id ben_ler_f.ler_id%type,
5976: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5977: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5978: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5979: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5980: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5981: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5982: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
5983: ler_id ben_ler_f.ler_id%type,
5984: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
5977: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
5978: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5979: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5980: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5981: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5982: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
5983: ler_id ben_ler_f.ler_id%type,
5984: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
5985: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
5978: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5979: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
5980: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5981: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
5982: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
5983: ler_id ben_ler_f.ler_id%type,
5984: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
5985: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
5986: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
5983: ler_id ben_ler_f.ler_id%type,
5984: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
5985: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
5986: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
5987: effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%type
5988: );
5989: --
5990: type t_pgm_table is table of pgm_rec index by binary_integer;
5991: type t_pl_table is table of pl_rec index by binary_integer;
6012: l_prv_rec_nulls c_prv%rowtype;
6013: --
6014: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
6015: select pen.*, pil.lf_evt_ocrd_dt
6016: from ben_prtt_enrt_rslt_f pen,
6017: ben_per_in_ler pil
6018: where pen.per_in_ler_id = cv_per_in_ler_id
6019: and pen.per_in_ler_id = pil.per_in_ler_id
6020: and pen.business_group_id = p_business_group_id
6064: and pdp2.effective_end_date;
6065: --
6066: cursor c_ovn(v_prtt_enrt_rslt_id number) is
6067: select object_version_number
6068: from ben_prtt_enrt_rslt_f
6069: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
6070: and effective_end_date = hr_api.g_eot;
6071: --
6072: cursor c_prv_ovn (v_prtt_rt_val_id number) is
7744: l_prv_rec_nulls c_prv%rowtype;
7745: --
7746: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
7747: select pen.*, pil.lf_evt_ocrd_dt
7748: from ben_prtt_enrt_rslt_f pen,
7749: ben_per_in_ler pil
7750: where pen.per_in_ler_id = cv_per_in_ler_id
7751: and pen.per_in_ler_id = pil.per_in_ler_id
7752: and pen.business_group_id = p_business_group_id
8137: -- changed 7176884 begin
8138: delete from ben_le_clsn_n_rstr
8139: where bkup_tbl_id = l_bckdt_pen_rec.prtt_enrt_rslt_id
8140: and per_in_ler_id = p_bckdt_per_in_ler_id
8141: and bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
8142: and person_id = p_person_id;
8143: -- changed 7176884 end
8144: --
8145: l_prtt_rt_val_table(1) := l_prtt_rt_val_id1;
8668: and pdp2.effective_end_date;
8669: --
8670: cursor c_ovn(v_prtt_enrt_rslt_id number) is
8671: select object_version_number
8672: from ben_prtt_enrt_rslt_f
8673: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
8674: and effective_end_date = hr_api.g_eot;
8675: --
8676: cursor c_epe_enrt_rt(v_elig_per_elctbl_chc_id number,
8691: and ecr.acty_base_rt_id = v_acty_base_rt_id ;
8692: --
8693: cursor c_flx_pen(c_person_id number,c_per_in_ler_id number,c_effective_date date ) is
8694: select pen.prtt_enrt_rslt_id,pen.pgm_id,pen.pl_id
8695: from ben_prtt_enrt_rslt_f pen,
8696: ben_pl_f pln
8697: where pen.person_id = c_person_id
8698: and pen.per_in_ler_id = c_per_in_ler_id
8699: and pen.pl_id = pln.pl_id
9206: WHERE pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
9207: AND pil_inner.person_id = p_person_id
9208: AND pil_inner.business_group_id = p_business_group_id
9209: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
9210: AND pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
9211: AND ( (pen_inner.enrt_cvg_thru_dt IS NOT NULL and pen_inner.enrt_cvg_thru_dt <> hr_api.g_eot )
9212: and pen_inner.effective_end_date = hr_api.g_eot
9213: )
9214: AND pen_inner.comp_lvl_cd NOT IN('PLANFC','PLANIMP');
10051: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
10052: and pil.person_id = p_person_id
10053: and pil.business_group_id = p_business_group_id
10054: AND pil.per_in_ler_id = pen.per_in_ler_id
10055: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
10056: and ((pen.enrt_cvg_thru_dt is null or
10057: pen.enrt_cvg_thru_dt = hr_api.g_eot) and
10058: --bug#2604375 - added to control updated result rows for the same per_in_ler
10059: pen.effective_end_date = hr_api.g_eot
10060: )
10061: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
10062: and pen.bkup_tbl_id not in (
10063: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
10064: from ben_prtt_enrt_rslt_f pen_inner,
10065: ben_per_in_ler pil_inner
10066: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
10067: and pil_inner.person_id = p_person_id
10068: and pil_inner.business_group_id = p_business_group_id
10078: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
10079: and pil_inner.person_id = p_person_id
10080: and pil_inner.business_group_id = p_business_group_id
10081: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
10082: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
10083: and (pen_inner.enrt_cvg_thru_dt is null or
10084: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
10085: )
10086: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
10179: l_count number;
10180: --
10181: type pgm_rec is record
10182: (pgm_id ben_pgm_f.pgm_id%type,
10183: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
10184: multi_row_edit_done boolean,
10185: non_automatics_flag boolean,
10186: max_enrt_esd date);
10187: --
10186: max_enrt_esd date);
10187: --
10188: type pl_rec is record
10189: (pl_id ben_pl_f.pl_id%type,
10190: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
10191: multi_row_edit_done boolean,
10192: max_enrt_esd date);
10193: --
10194: type enrt_rec is record
10191: multi_row_edit_done boolean,
10192: max_enrt_esd date);
10193: --
10194: type enrt_rec is record
10195: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
10196: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
10197: bckdt_enrt_ovridn_flag varchar2(1),
10198: bckdt_enrt_cvg_strt_dt date,
10199: bckdt_enrt_cvg_thru_dt date,
10192: max_enrt_esd date);
10193: --
10194: type enrt_rec is record
10195: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
10196: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
10197: bckdt_enrt_ovridn_flag varchar2(1),
10198: bckdt_enrt_cvg_strt_dt date,
10199: bckdt_enrt_cvg_thru_dt date,
10200: g_sys_date date,
10197: bckdt_enrt_ovridn_flag varchar2(1),
10198: bckdt_enrt_cvg_strt_dt date,
10199: bckdt_enrt_cvg_thru_dt date,
10200: g_sys_date date,
10201: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
10202: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10203: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10204: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10205: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10198: bckdt_enrt_cvg_strt_dt date,
10199: bckdt_enrt_cvg_thru_dt date,
10200: g_sys_date date,
10201: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
10202: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10203: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10204: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10205: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10206: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10199: bckdt_enrt_cvg_thru_dt date,
10200: g_sys_date date,
10201: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
10202: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10203: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10204: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10205: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10206: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10207: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10200: g_sys_date date,
10201: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
10202: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10203: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10204: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10205: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10206: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10207: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10208: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
10201: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
10202: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10203: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10204: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10205: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10206: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10207: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10208: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
10209: ler_id ben_ler_f.ler_id%type,
10202: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10203: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10204: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10205: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10206: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10207: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10208: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
10209: ler_id ben_ler_f.ler_id%type,
10210: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
10203: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
10204: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10205: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10206: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10207: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10208: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
10209: ler_id ben_ler_f.ler_id%type,
10210: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
10211: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
10204: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10205: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
10206: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10207: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
10208: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
10209: ler_id ben_ler_f.ler_id%type,
10210: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
10211: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
10212: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
10209: ler_id ben_ler_f.ler_id%type,
10210: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
10211: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
10212: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
10213: effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%type
10214: );
10215: --
10216: --
10217: type t_pgm_table is table of pgm_rec index by binary_integer;
10241: l_prv_rec_nulls c_prv%rowtype;
10242: --
10243: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
10244: select pen.*, pil.lf_evt_ocrd_dt
10245: from ben_prtt_enrt_rslt_f pen,
10246: ben_per_in_ler pil
10247: where pen.per_in_ler_id = cv_per_in_ler_id
10248: and pen.per_in_ler_id = pil.per_in_ler_id
10249: and pen.business_group_id = p_business_group_id
10293: and pdp2.effective_end_date;
10294: --
10295: cursor c_ovn(v_prtt_enrt_rslt_id number) is
10296: select object_version_number
10297: from ben_prtt_enrt_rslt_f
10298: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
10299: and effective_end_date = hr_api.g_eot;
10300: --
10301: cursor c_prv_ovn (v_prtt_rt_val_id number) is
10313: cursor c_rslt_exists (p_person_id number,
10314: p_pl_id number,
10315: p_oipl_id number) is
10316: select 'Y'
10317: from ben_prtt_enrt_rslt_f pen
10318: where pen.person_id = p_person_id
10319: and pen.pl_id = p_pl_id
10320: and nvl(pen.oipl_id,-9999) = nvl(p_oipl_id,-9999) --5287988
10321: and pen.prtt_enrt_rslt_stat_cd is null