[Home] [Help]
75: SELECT lf_evt_ocrd_dt
76: FROM ben_ptnl_ler_for_per ptn
77: WHERE ptn.person_id = p_person_id
78: AND ptn.business_group_id = p_business_group_id
79: AND ptn.ler_id IN ( SELECT ler_id FROM ben_ler_f ler WHERE ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
80: AND ptn.ptnl_ler_for_per_stat_cd NOT IN('VOIDD','PROCD');
81: --
82: l_exists VARCHAR2(2) := 'N';
83: l_lf_evt_ocrd_dt DATE;
117: --
118: CURSOR csr_chk_cnflt_ler IS
119: SELECT ptn.ler_id,ler.name,ler.ovridg_le_flag
120: FROM ben_ptnl_ler_for_per ptn,
121: ben_ler_f ler
122: WHERE ptn.person_id = p_person_id
123: AND ptn.business_group_id = p_business_group_id
124: AND ptn.ler_id =ler.ler_id
125: AND p_effective_date between ler.effective_start_date and effective_end_date
189: UPDATE ben_ptnl_ler_for_per
190: SET ptnl_ler_for_per_stat_cd = 'VOIDD'
191: WHERE person_id = p_person_id
192: AND business_group_id = p_business_group_id
193: AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
194: AND lf_evt_ocrd_dt <= p_effective_date
195: AND ptnl_ler_for_per_stat_cd IN ('UNPROCD', 'DTCTD'); -- 5763776 Removed 'PROCD'. Should not require this.
196:
197: if g_debug then
219: SET per_in_ler_stat_cd = 'VOIDD'
220: ,voidd_dt = p_effective_date
221: WHERE person_id = p_person_id
222: AND business_group_id = p_business_group_id
223: AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO'))
224: AND per_in_ler_stat_cd = 'STRTD';
225:
226: if g_debug then
227: hr_utility.set_location('Leaving: ' || l_proc,20);
249: UPDATE ben_ptnl_ler_for_per
250: SET ptnl_ler_for_per_stat_cd = 'VOIDD'
251: WHERE person_id = p_person_id
252: AND business_group_id = p_business_group_id
253: AND ler_id in ( select ler_id from ben_ler_f ler where ler.typ_cd NOT IN ('COMP','SCHEDDU','SCHEDDO')
254: and ler.ler_id <> p_winning_ler_id)
255: AND lf_evt_ocrd_dt <= p_effective_date
256: AND ptnl_ler_for_per_stat_cd IN ('UNPROCD', 'DTCTD');
257:
409: order by transaction_value_id, datatype;
410:
411: Cursor c_ler (p_typ_cd in varchar2) is
412: select ler.ler_id
413: from ben_ler_f ler
414: where ler.typ_cd = p_typ_cd
415: and ler.business_group_id = p_business_group_id
416: and p_effective_date between
417: ler.effective_start_date and ler.effective_end_date;
2288: Select pil.per_in_ler_id,
2289: ler.name name,
2290: ler.ler_id
2291: From ben_per_in_ler pil,
2292: ben_ler_f ler
2293: Where pil.person_id = p_person_id
2294: AND ler.ler_id = pil.ler_id
2295: AND p_effective_date BETWEEN ler.effective_start_date and ler.effective_end_date
2296: AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
2943: Select pil.per_in_ler_id,
2944: ler.name name,
2945: ler.ler_id
2946: From ben_per_in_ler pil,
2947: ben_ler_f ler
2948: Where pil.person_id = p_person_id
2949: AND ler.ler_id = pil.ler_id
2950: AND p_effective_date BETWEEN ler.effective_start_date and ler.effective_end_date
2951: AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
3926: select ptn.ptnl_ler_for_per_id,
3927: ptn.ptnl_ler_for_per_stat_cd,
3928: ptn.lf_evt_ocrd_dt lf_evt_ocrd_dt
3929: from ben_ptnl_ler_for_per ptn,
3930: ben_ler_f ler
3931: where ptn.person_id = p_person_id
3932: and ptn.business_group_id = p_business_group_id
3933: and ptn.lf_evt_ocrd_dt > l_lf_evt_ocrd_dt
3934: and ptn.ler_id = ler.ler_id