[Home] [Help]
1922: Select 'X'
1923: into v_dummy
1924: from sys.dual
1925: where exists (select 'Assignments Exist'
1926: from per_all_assignments_f paf
1927: where paf.supervisor_id = p_person_id
1928: and paf.business_group_id = p_business_group_id
1929: and p_session_date between paf.effective_start_date
1930: and paf.effective_end_date);
2125: --
2126: cursor csr_get_asgs_to_terminate is
2127: select asg.assignment_id
2128: , asg.object_version_number
2129: from per_all_assignments_f asg
2130: where asg.person_id = l_person_id
2131: and asg.period_of_placement_date_start = l_date_start
2132: and l_actual_termination_date + 1 between asg.effective_start_date
2133: and asg.effective_end_date
2153: --
2154: cursor csr_get_max_tpe_end_date is
2155: select max(tpe.end_date)
2156: from per_time_periods tpe
2157: ,per_all_assignments_f asg
2158: where asg.person_id = l_person_id
2159: and asg.period_of_placement_date_start = l_date_start
2160: and l_actual_termination_date between asg.effective_start_date
2161: and asg.effective_end_date
2774: cursor csr_get_asgs_to_final_proc is
2775: select asg.assignment_id
2776: , asg.object_version_number
2777: , asg.primary_flag
2778: from per_all_assignments_f asg
2779: where asg.person_id = p_person_id
2780: and asg.period_of_placement_date_start = p_date_start
2781: and l_final_process_date between asg.effective_start_date
2782: and asg.effective_end_date
3675: CURSOR c_assignment IS
3676: SELECT assignment_id
3677: , assignment_status_type_id
3678: , business_group_id
3679: FROM per_all_assignments_f ass
3680: WHERE ass.person_id = p_person_id
3681: AND ass.effective_end_date = p_actual_termination_date
3682: AND ass.assignment_type = 'C' -- Added for bug 12637271
3683: FOR UPDATE;
3856: WHERE exists
3857: (SELECT null
3858: FROM pay_payroll_actions pac,
3859: pay_assignment_actions act,
3860: per_all_assignments_f asg
3861: WHERE asg.person_id = p_person_id
3862: AND act.assignment_id = asg.assignment_id
3863: AND pac.payroll_action_id = act.payroll_action_id
3864: AND pac.action_type not in ('X','BEE') -- Bug 889806,2711532
3876: WHERE exists
3877: (SELECT null
3878: FROM pay_payroll_actions pac,
3879: pay_assignment_actions act,
3880: per_all_assignments_f asg
3881: WHERE asg.person_id = p_person_id
3882: AND act.assignment_id = asg.assignment_id
3883: AND pac.payroll_action_id = act.payroll_action_id
3884: AND pac.action_status = 'C'
4083: -- are currently TERM_CWK_ASG back to the appropriate ACTIVE_CWK_ASG status.
4084: --
4085: SELECT assignment_status_type_id
4086: INTO l_asg_status_type_id
4087: FROM per_all_assignments_f
4088: WHERE assignment_id = c_asg_rec.assignment_id
4089: AND effective_end_date = p_actual_termination_date;
4090: --
4091: hr_utility.set_location(l_proc,80);
4091: hr_utility.set_location(l_proc,80);
4092:
4093: SELECT max(asg.effective_end_date)
4094: INTO l_max_end_date
4095: FROM per_all_assignments_f asg
4096: WHERE asg.assignment_id = c_asg_rec.assignment_id;
4097:
4098: --
4099: if l_per_system_status <> 'TERM_CWK_ASG' then
4115: --
4116: -- Open out the last dated assignment record to the end of time or
4117: -- max_end_date based on above logic.
4118: --
4119: UPDATE per_all_assignments_f ass
4120: SET ass.effective_end_date = l_effective_end_date
4121: WHERE assignment_id = c_asg_rec.assignment_id
4122: AND effective_end_date = l_max_end_date;
4123: --
4130: -- record will exist which runs from ATD+1 to the start date-1
4131: -- of the next date effective record for the person or EOT
4132: -- depending on the data present at time of termination.
4133: --
4134: UPDATE per_all_assignments_f ass
4135: SET ass.assignment_status_type_id = l_asg_status_type_id
4136: WHERE assignment_id = c_asg_rec.assignment_id
4137: AND effective_start_date >= p_actual_termination_date;
4138: --