[Home] [Help]
63: service.date_start,
64: nvl (service.final_process_date, hr_general.end_of_time) FINAL_PROCESS_DATE
65: from ssp_maternities MATERNITY,
66: per_all_people_f PERSON,
67: per_periods_of_service SERVICE
68: where person.person_id = maternity.person_id
69: and person.person_id = service.person_id
70: and maternity.maternity_id = p_maternity_id
71: and service.date_start between person.effective_start_date
70: and maternity.maternity_id = p_maternity_id
71: and service.date_start between person.effective_start_date
72: and person.effective_end_date
73: and service.date_start = (select max(serv.date_start)
74: from per_periods_of_service serv
75: where serv.person_id = person.person_id);
76: --------------------------------------------------------------------------------
77: person csr_personal_details%rowtype;
78: g_APAB_element csr_APAB_element_details%rowtype;
529: procedure check_employment_end is
530:
531: cursor csr_termination_date is
532: select actual_termination_date
533: from per_periods_of_service
534: where person_id = person.person_id
535: -- 3516539 Added condition to obtain termination date for latest assignment
536: and period_of_service_id = (select max(period_of_service_id)
537: from per_periods_of_service ppos
533: from per_periods_of_service
534: where person_id = person.person_id
535: -- 3516539 Added condition to obtain termination date for latest assignment
536: and period_of_service_id = (select max(period_of_service_id)
537: from per_periods_of_service ppos
538: where ppos.person_id = person.person_id);
539: --
540: l_termination_date date := null;
541: l_proc varchar2(72) := g_package||'check_employment_end';
570: --
571: -- Check the period of service length up to the MW start date
572: --
573: select 1
574: from per_periods_of_service
575: where person_id = person.person_id
576: and date_start <= ssp_APAB_pkg.continuous_employment_date(person.due_date)
577: and nvl (actual_termination_date, hr_general.end_of_time) >= person.QW;
578: --