[Home] [Help]
691: ,p_old_start_date DATE) is
692: cursor csr_people_change is
693: select 'x' from dual where exists
694: (select 1
695: from per_people_f p
696: where p.effective_start_date between p_earlier_date and p_later_date
697: and p.effective_start_date <> p_old_start_date
698: and p.person_id = p_person_id
699: union
697: and p.effective_start_date <> p_old_start_date
698: and p.person_id = p_person_id
699: union
700: select 1
701: from per_people_f p
702: where p.effective_start_date = p_old_start_date
703: and p.current_applicant_flag = 'Y'
704: and p.person_id = p_person_id);
705: -- union added to take care of cases when an employee is made
1160: procedure update_tax(p_person_id number
1161: ,p_new_start_date date) is
1162: cursor csr_get_bg is
1163: select business_group_id
1164: from per_people_f
1165: where person_id = p_person_id;
1166: --
1167: l_business_group_id number;
1168: l_ret_code number;
1259: update per_applications a1
1260: set a1.date_end = p_new_start_date - 1
1261: where a1.application_id = l_application_id
1262: and not exists (select 1
1263: from per_people_f peo
1264: where peo.person_id = p_person_id
1265: and a1.person_id = peo.person_id
1266: and peo.effective_start_date = p_old_start_date
1267: and peo.current_applicant_flag = 'Y');
1388: ,p_new_start_date date
1389: ,p_type VARCHAR2) is
1390: cursor csr_get_bg is
1391: select business_group_id
1392: from per_people_f
1393: where person_id = p_person_id;
1394: --
1395: cursor ass_cur is
1396: select assignment_id
2016: (p_person_id IN per_all_people_f.person_id%TYPE
2017: ,p_effective_date IN DATE
2018: ) IS
2019: SELECT bus.legislation_code
2020: FROM per_people_f per
2021: ,per_business_groups bus
2022: WHERE per.person_id = csr_legislation.p_person_id
2023: AND per.business_group_id+0 = bus.business_group_id
2024: AND csr_legislation.p_effective_date BETWEEN per.effective_start_date
2355: -- Process Logic: now we must update the relevant records in line with the new start date
2356: --
2357: -- Fix for bug 3390731 starts here. update the start_date with min(effective_start_date).
2358: --
2359: update per_people_f p
2360: set p.effective_start_date = decode(p.effective_start_date
2361: ,l_old_start_date,l_new_start_date,p.effective_start_date)
2362: ,p.effective_end_date = decode(p.effective_end_date
2363: ,l_old_start_date-1,l_new_start_date-1,p.effective_end_date)
2369: hr_utility.set_message_token('TABLE','PER_ALL_PEOPLE_F');
2370: hr_utility.raise_error;
2371: end if;
2372: --
2373: update per_people_f p
2374: set p.start_date = (select min(ppf.effective_start_date)
2375: from per_people_f ppf
2376: where ppf.person_id = p_person_id)
2377: where p.person_id = p_person_id;
2371: end if;
2372: --
2373: update per_people_f p
2374: set p.start_date = (select min(ppf.effective_start_date)
2375: from per_people_f ppf
2376: where ppf.person_id = p_person_id)
2377: where p.person_id = p_person_id;
2378: --
2379: -- Fix for bug 3390731 ends here.