[Home] [Help]
477: and a.effective_end_date;
478: -- Added cursor for bug 3652731
479: cursor c2(l_cobra_flag varchar2) is
480: select contact_person_id
481: from per_contact_relationships a
482: where a.person_id = p_person_id
483: and decode(l_cobra_flag, 'Y', p_effective_date, p_lf_evt_ocrd_dt) /* Bug 5672925 + 5747460*/
484: between a.date_start
485: and nvl(a.date_end,to_date('31-12-4712','DD-MM-YYYY'));
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;
536: fetch c3 into l_date;
537: close c3;
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;
548: hr_utility.set_location('p_person_id: '||p_person_id,5.5);