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