[Home] [Help]
86: service.date_start,
87: nvl (service.final_process_date, hr_general.end_of_time) FINAL_PROCESS_DATE
88: from ssp_maternities MATERNITY,
89: per_all_people_f PERSON,
90: per_periods_of_service SERVICE
91: where person.person_id = maternity.person_id
92: and person.person_id = service.person_id
93: and maternity.maternity_id = p_maternity_id
94: and service.date_start between person.effective_start_date
93: and maternity.maternity_id = p_maternity_id
94: and service.date_start between person.effective_start_date
95: and person.effective_end_date
96: and service.date_start = (select max(serv.date_start)
97: from per_periods_of_service serv
98: where serv.person_id = person.person_id);
99: --------------------------------------------------------------------------------
100: person csr_personal_details%rowtype;
101: g_PAB_element csr_PAB_element_details%rowtype;
500: procedure check_employment_end is
501:
502: cursor csr_termination_date is
503: select actual_termination_date
504: from per_periods_of_service
505: where person_id = person.person_id
506: -- 3516539 Added condition to obtain termination date for latest assignment
507: and period_of_service_id = (select max(period_of_service_id)
508: from per_periods_of_service ppos
504: from per_periods_of_service
505: where person_id = person.person_id
506: -- 3516539 Added condition to obtain termination date for latest assignment
507: and period_of_service_id = (select max(period_of_service_id)
508: from per_periods_of_service ppos
509: where ppos.person_id = person.person_id);
510: --
511: l_termination_date date := null;
512: l_proc varchar2(72) := g_package||'check_employment_end';
541: --
542: -- Check the period of service length up to the MW start date
543: --
544: select 1
545: from per_periods_of_service
546: where person_id = person.person_id
547: and date_start <= ssp_pab_pkg.continuous_employment_date(person.due_date)
548: and nvl (actual_termination_date, hr_general.end_of_time) >= person.QW;
549: --