[Home] [Help]
183: l_dummy varchar2(1);
184: --
185: cursor c1 is
186: select null
187: from ben_per_in_ler a
188: where a.per_in_ler_id = p_per_in_ler_id
189: and a.per_in_ler_stat_cd = 'STRTD';
190: --
191: Begin
200: and nvl(p_per_in_ler_id,hr_api.g_number)
201: <> nvl(ben_pel_shd.g_old_rec.per_in_ler_id,hr_api.g_number)
202: or not l_api_updating) then
203: --
204: -- check if per_in_ler_id value exists in ben_per_in_ler table
205: --
206: open c1;
207: --
208: fetch c1 into l_dummy;
209: if c1%notfound then
210: --
211: close c1;
212: --
213: -- raise error as FK does not relate to PK in ben_per_in_ler
214: -- table.
215: --
216: ben_pel_shd.constraint_error('BEN_PIL_ELCTBL_CHC_POPL_DT2');
217: --
991: --
992: -- check if value of lookup falls within lookup type.
993: --
994: if hr_api.not_exists_in_hr_lookups
995: (p_lookup_type => 'BEN_PER_IN_LER_STAT',
996: p_lookup_code => p_pil_elctbl_popl_stat_cd,
997: p_effective_date => p_effective_date) then
998: --
999: -- raise error as does not exist as lookup
999: -- raise error as does not exist as lookup
1000: --
1001: fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1002: fnd_message.set_token('FIELD', p_pil_elctbl_popl_stat_cd);
1003: fnd_message.set_token('TYPE' ,'BEN_PER_IN_LER_STAT');
1004: fnd_message.raise_error;
1005: --
1006: end if;
1007: --
1255: select per1.full_name person1,
1256: per2.full_name person2
1257: from ben_cwb_hrchy cwb1,
1258: ben_pil_elctbl_chc_popl pel1,
1259: ben_per_in_ler pil1,
1260: per_all_people_f per1,
1261: per_all_people_f per2
1262: where cwb1.mgr_pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
1263: and cwb1.lvl_num > 0
1271: and per2.effective_end_date
1272: and exists
1273: ( select 'x'
1274: from ben_pil_elctbl_chc_popl pel2,
1275: ben_per_in_ler pil2
1276: where pil2.person_id = p_ws_mgr_id
1277: and pil2.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
1278: and pil2.ler_id = pil1.ler_id
1279: and pel2.per_in_ler_id = pil2.per_in_ler_id