[Home] [Help]
342: and effective_start_date > p_eff_start_date;
343:
344: cursor csr_get_apln_details(p_hire_date date) is
345: select date_received,date_end
346: from per_applications
347: where person_id= p_person_id and date_received > p_hire_date;
348:
349: cursor csr_back_toback_apln(p_date_end date) is
350: select 'Y' from per_applications
346: from per_applications
347: where person_id= p_person_id and date_received > p_hire_date;
348:
349: cursor csr_back_toback_apln(p_date_end date) is
350: select 'Y' from per_applications
351: where person_id = p_person_id
352: and date_received = p_date_end +1;
353:
354: cursor chk_fut_ex_apl(p_hire_date date) is
367: and assignment_type = 'A'
368: order by effective_start_date;
369:
370: cursor chk_apl_exists(p_asg_start_date date) is
371: select application_id, date_end from per_applications
372: where person_id = p_person_id
373: and p_asg_start_date between date_received and nvl(date_end,hr_api.g_eot);
374:
375: cursor get_apln_id is
988: fetch chk_apl_exists into l_cur_apl_id, l_current_apl_end_date;
989:
990: -- if application not found create a new application
991: if chk_apl_exists%notfound then
992: SELECT per_applications_s.nextval into l_new_application_id FROM sys.dual;
993: l_cur_apl_id := l_new_application_id;
994: begin
995: INSERT INTO PER_APPLICATIONS(
996: application_id,
991: if chk_apl_exists%notfound then
992: SELECT per_applications_s.nextval into l_new_application_id FROM sys.dual;
993: l_cur_apl_id := l_new_application_id;
994: begin
995: INSERT INTO PER_APPLICATIONS(
996: application_id,
997: business_group_id,
998: person_id,
999: date_received,
1012: -- if application is found, then extend the applciation till the end of the asgt's end date
1013: else
1014:
1015: if nvl(l_current_apl_end_date,hr_api.g_eot) < nvl(l_apl_end_date,hr_api.g_eot) then
1016: update per_applications
1017: set date_end = l_apl_end_date
1018: where person_id = p_person_id
1019: and application_id = l_cur_apl_id;
1020: end if;