[Home] [Help]
459: and p_effective_date between ler.effective_start_date and ler.effective_end_date;
460: l_cobra_evt_flag varchar2(30);
461: --
462: --
463: -- Bug 5672925 : Modified cursor C1 and C2 to check existence of PER_ALL_PEOPLE_F record
464: -- as of LF_EVT_OCRD_DT instead of EFFECTIVE_DATE. The problem is, if a person
465: -- is created on 01-Jan-2002 and the latest start date on Person form is changed
466: -- to 06-Jan-2002, then before we create PPL record, the EFFECTIVE_START_DATE
467: -- of PER_ALL_PEOPLE_F record is already changed to 06-Jan-2002, and hence C1, C2
463: -- Bug 5672925 : Modified cursor C1 and C2 to check existence of PER_ALL_PEOPLE_F record
464: -- as of LF_EVT_OCRD_DT instead of EFFECTIVE_DATE. The problem is, if a person
465: -- is created on 01-Jan-2002 and the latest start date on Person form is changed
466: -- to 06-Jan-2002, then before we create PPL record, the EFFECTIVE_START_DATE
467: -- of PER_ALL_PEOPLE_F record is already changed to 06-Jan-2002, and hence C1, C2
468: -- would fail on EFFECTIVE_DATE
469: --
470: cursor c1(l_person_id number,
471: l_cobra_flag varchar2) is
469: --
470: cursor c1(l_person_id number,
471: l_cobra_flag varchar2) is
472: select null
473: from per_all_people_f a
474: where a.person_id = l_person_id
475: and decode(l_cobra_flag, 'Y', p_effective_date, p_lf_evt_ocrd_dt) /* Bug 5672925 + 5747460*/
476: between a.effective_start_date
477: and a.effective_end_date;
484: between a.date_start
485: and nvl(a.date_end,to_date('31-12-4712','DD-MM-YYYY'));
486: --
487: cursor c3 is
488: select effective_start_date from per_all_people_f
489: where person_id = p_person_id;
490:
491: --
492: Begin
507: and nvl(p_person_id,hr_api.g_number)
508: <> nvl(ben_ppl_shd.g_old_rec.person_id,hr_api.g_number)
509: or not l_api_updating) then
510: --
511: -- check if person_id value exists in per_all_people_f table
512: hr_utility.set_location('ace p_lf_evt_ocrd_dt = ' || p_lf_evt_ocrd_dt, 9999);
513: hr_utility.set_location('p_effective_date = ' || p_effective_date, 9999);
514: l_person_id := p_person_id;
515:
527: if c1%notfound then
528: --
529: close c1;
530: --
531: -- If the given person_id is not there in per_all_people_f
532: -- in the given date, then it could be a contact id
533: -- check if the id exists in per_contact_relationships table
534: -- cursor c3 is for debug purpose
535: open c3;
539: open c2(l_cobra_evt_flag);
540: --
541: fetch c2 into l_person_id;
542: if c2%notfound then
543: -- raise error as FK does not relate to PK in per_all_people_f
544: -- or per_contact_relationships
545: -- table.
546: --
547: close c2;