[Home] [Help]
64: service.date_start,
65: nvl (service.final_process_date, hr_general.end_of_time) FINAL_PROCESS_DATE
66: from ssp_maternities MATERNITY,
67: per_all_people_f PERSON,
68: per_periods_of_service SERVICE
69: where person.person_id = maternity.person_id
70: and person.person_id = service.person_id
71: and maternity.maternity_id = p_maternity_id
72: and service.date_start between person.effective_start_date
71: and maternity.maternity_id = p_maternity_id
72: and service.date_start between person.effective_start_date
73: and person.effective_end_date
74: and service.date_start = (select max(serv.date_start)
75: from per_periods_of_service serv
76: where serv.person_id = person.person_id);
77: --------------------------------------------------------------------------------
78: person csr_personal_details%rowtype;
79: g_APAD_element csr_APAD_element_details%rowtype;
484: procedure check_employment_end is
485:
486: cursor csr_termination_date is
487: select actual_termination_date
488: from per_periods_of_service
489: where person_id = person.person_id
490: -- 3516539 Added condition to obtain termination date for latest assignment
491: and period_of_service_id = (select max(period_of_service_id)
492: from per_periods_of_service ppos
488: from per_periods_of_service
489: where person_id = person.person_id
490: -- 3516539 Added condition to obtain termination date for latest assignment
491: and period_of_service_id = (select max(period_of_service_id)
492: from per_periods_of_service ppos
493: where ppos.person_id = person.person_id);
494: --
495: l_termination_date date := null;
496: l_proc varchar2 (72) := g_package||'check_employment_end';
525: --
526: -- Check the period of service length up to the MW start date
527: --
528: select 1
529: from per_periods_of_service
530: where person_id = person.person_id
531: and date_start <= ssp_APAD_pkg.continuous_employment_date
532: (person.matching_date)
533: and nvl (actual_termination_date, hr_general.end_of_time)