[Home] [Help]
189: l_dummy varchar2(1);
190: --
191: cursor c1 is
192: select null
193: from per_all_people_f a
194: where a.person_id = p_person_id
195: and p_effective_date
196: between a.effective_start_date
197: and a.effective_end_date;
208: and nvl(p_person_id,hr_api.g_number)
209: <> nvl(ben_pil_shd.g_old_rec.person_id,hr_api.g_number)
210: or not l_api_updating) then
211: --
212: -- check if person_id value exists in per_all_people_f table
213: --
214: open c1;
215: --
216: fetch c1 into l_dummy;
217: if c1%notfound then
218: --
219: close c1;
220: --
221: -- raise error as FK does not relate to PK in per_all_people_f
222: -- table.
223: --
224: ben_pil_shd.constraint_error('BEN_PER_IN_LER_DT2');
225: --
468: select per1.full_name person1,
469: per2.full_name person2
470: from ben_cwb_group_hrchy cwb1,
471: ben_per_in_ler pil1,
472: per_all_people_f per1,
473: per_all_people_f per2
474: where cwb1.mgr_per_in_ler_id = p_per_in_ler_id
475: and cwb1.lvl_num > 0
476: and pil1.per_in_ler_id = cwb1.mgr_per_in_ler_id
469: per2.full_name person2
470: from ben_cwb_group_hrchy cwb1,
471: ben_per_in_ler pil1,
472: per_all_people_f per1,
473: per_all_people_f per2
474: where cwb1.mgr_per_in_ler_id = p_per_in_ler_id
475: and cwb1.lvl_num > 0
476: and pil1.per_in_ler_id = cwb1.mgr_per_in_ler_id
477: and per1.person_id = pil1.person_id
487: and pil2.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
488: and pil2.ler_id = pil1.ler_id
489: and pil2.per_in_ler_id = cwb1.emp_per_in_ler_id);
490:
491: l_person1 per_all_people_f.full_name%type;
492: l_person2 per_all_people_f.full_name%type;
493: l_proc varchar2(72) := g_package||'chk_ws_mgr_id';
494:
495: begin
488: and pil2.ler_id = pil1.ler_id
489: and pil2.per_in_ler_id = cwb1.emp_per_in_ler_id);
490:
491: l_person1 per_all_people_f.full_name%type;
492: l_person2 per_all_people_f.full_name%type;
493: l_proc varchar2(72) := g_package||'chk_ws_mgr_id';
494:
495: begin
496: