[Home] [Help]
88: service.date_start,
89: nvl (service.final_process_date, hr_general.end_of_time) FINAL_PROCESS_DATE
90: from ssp_maternities MATERNITY,
91: per_all_people_f PERSON,
92: per_periods_of_service SERVICE
93: where person.person_id = maternity.person_id
94: and person.person_id = service.person_id
95: and maternity.maternity_id = p_maternity_id
96: and service.date_start between person.effective_start_date
95: and maternity.maternity_id = p_maternity_id
96: and service.date_start between person.effective_start_date
97: and person.effective_end_date
98: and service.date_start = (select max(serv.date_start)
99: from per_periods_of_service serv
100: where serv.person_id = person.person_id);
101: --------------------------------------------------------------------------------
102: person csr_personal_details%rowtype;
103: g_PAD_element csr_PAD_element_details%rowtype;
461: procedure check_employment_end is
462:
463: cursor csr_termination_date is
464: select actual_termination_date
465: from per_periods_of_service
466: where person_id = person.person_id
467: -- 3516539 Added condition to obtain termination date for latest assignment
468: and period_of_service_id = (select max(period_of_service_id)
469: from per_periods_of_service ppos
465: from per_periods_of_service
466: where person_id = person.person_id
467: -- 3516539 Added condition to obtain termination date for latest assignment
468: and period_of_service_id = (select max(period_of_service_id)
469: from per_periods_of_service ppos
470: where ppos.person_id = person.person_id);
471: --
472: l_termination_date date := null;
473: l_proc varchar2 (72) := g_package||'check_employment_end';
502: --
503: -- Check the period of service length up to the MW start date
504: --
505: select 1
506: from per_periods_of_service
507: where person_id = person.person_id
508: and date_start <= ssp_pad_pkg.continuous_employment_date
509: (person.matching_date)
510: and nvl (actual_termination_date, hr_general.end_of_time)