[Home] [Help]
52: <> ben_pil_shd.g_old_rec.per_in_ler_id) then
53: --
54: -- raise error as PK has changed
55: --
56: ben_pil_shd.constraint_error('BEN_PER_IN_LER_PK');
57: --
58: elsif not l_api_updating then
59: --
60: -- check if PK is null
62: if p_per_in_ler_id is not null then
63: --
64: -- raise error as PK is not null
65: --
66: ben_pil_shd.constraint_error('BEN_PER_IN_LER_PK');
67: --
68: end if;
69: --
70: end if;
140: --
141: -- raise error as FK does not relate to PK in ben_ler_f
142: -- table.
143: --
144: ben_pil_shd.constraint_error('BEN_PER_IN_LER_DT1');
145: --
146: end if;
147: --
148: 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: --
226: end if;
227: --
228: close c1;
284: --
285: -- check if value of lookup falls within lookup type.
286: --
287: if hr_api.not_exists_in_hr_lookups
288: (p_lookup_type => 'BEN_PER_IN_LER_STAT',
289: p_lookup_code => p_prvs_stat_cd,
290: p_effective_date => p_effective_date) then
291: --
292: -- raise error as does not exist as lookup
292: -- raise error as does not exist as lookup
293: --
294: fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
295: fnd_message.set_token('FIELD','p_prvs_stat_cd');
296: fnd_message.set_token('TYPE','BEN_PER_IN_LER_STAT_CD');
297: fnd_message.raise_error;
298: --
299: end if;
300: --
354: --
355: -- check if value of lookup falls within lookup type.
356: --
357: if hr_api.not_exists_in_hr_lookups
358: (p_lookup_type => 'BEN_PER_IN_LER_STAT',
359: p_lookup_code => p_per_in_ler_stat_cd,
360: p_effective_date => p_effective_date) then
361: --
362: -- raise error as does not exist as lookup
362: -- raise error as does not exist as lookup
363: --
364: fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
365: fnd_message.set_token('FIELD','p_per_in_ler_stat_cd');
366: fnd_message.set_token('TYPE','BEN_PER_IN_LER_STAT_CD');
367: fnd_message.raise_error;
368: --
369: end if;
370: --
410: l_dummy varchar2(1);
411: --
412: cursor c1 is
413: select null
414: from ben_per_in_ler a
415: where a.per_in_ler_id = p_bckt_per_in_ler_id;
416: --
417: Begin
418: --
428: or not l_api_updating)
429: and p_bckt_per_in_ler_id is not null
430: then
431: --
432: -- check if bckt_per_in_ler_id value exists in ben_per_in_ler table
433: --
434: open c1;
435: --
436: fetch c1 into l_dummy;
437: if c1%notfound then
438: --
439: close c1;
440: --
441: -- raise error as FK does not relate to PK in ben_per_in_ler
442: -- table.
443: --
444: ben_pil_shd.constraint_error('BEN_PER_IN_LER_FK2');
445: --
440: --
441: -- raise error as FK does not relate to PK in ben_per_in_ler
442: -- table.
443: --
444: ben_pil_shd.constraint_error('BEN_PER_IN_LER_FK2');
445: --
446: end if;
447: --
448: close c1;
467: cursor c1 is
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
481: and trunc(p_effective_date) between per2.effective_start_date
482: and per2.effective_end_date
483: and exists
484: ( select 'x'
485: from ben_per_in_ler pil2
486: where pil2.person_id = p_ws_mgr_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);
630: --
631: cursor csr_leg_code is
632: select a.legislation_code
633: from per_business_groups a,
634: ben_per_in_ler b
635: where b.per_in_ler_id = p_per_in_ler_id
636: and a.business_group_id = b.business_group_id;
637: --
638: -- Declare local variables