[Home] [Help]
14: )
15: IS
16: SELECT asg.assignment_id
17: ,asg.object_version_number
18: FROM per_assignments_f asg
19: WHERE asg.person_id = csr_future_asgs.p_person_id
20: AND asg.effective_start_date >= csr_future_asgs.p_effective_date;
21: --
22: -- -----------------------------------------------------------------------------
1517: l_hire_date date;
1518: l_original_date_of_hire date;
1519: --
1520: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1521: l_assignment_id per_assignments_f.assignment_id%TYPE;
1522: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1523: --
1524: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1525: l_employee_number per_all_people_f.employee_number%TYPE;
1518: l_original_date_of_hire date;
1519: --
1520: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1521: l_assignment_id per_assignments_f.assignment_id%TYPE;
1522: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1523: --
1524: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1525: l_employee_number per_all_people_f.employee_number%TYPE;
1526: l_npw_number per_all_people_f.npw_number%TYPE;
1525: l_employee_number per_all_people_f.employee_number%TYPE;
1526: l_npw_number per_all_people_f.npw_number%TYPE;
1527: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1528: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1529: l_comment_id per_assignments_f.comment_id%TYPE;
1530: l_current_applicant_flag varchar2(1);
1531: l_current_emp_or_apl_flag varchar2(1);
1532: l_current_employee_flag varchar2(1);
1533: l_full_name per_all_people_f.full_name%TYPE;
1540: L_ASG_STATUS_OVN number;
1541: l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
1542: l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
1543: --
1544: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1545: --
1546: l_primary_flag per_assignments_f.primary_flag%TYPE;
1547: --
1548: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1542: l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
1543: --
1544: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1545: --
1546: l_primary_flag per_assignments_f.primary_flag%TYPE;
1547: --
1548: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1549: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1550: l_validation_start_date date;
1544: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1545: --
1546: l_primary_flag per_assignments_f.primary_flag%TYPE;
1547: --
1548: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1549: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1550: l_validation_start_date date;
1551: l_validation_end_date date;
1552: l_payroll_id_updated boolean;
1545: --
1546: l_primary_flag per_assignments_f.primary_flag%TYPE;
1547: --
1548: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1549: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1550: l_validation_start_date date;
1551: l_validation_end_date date;
1552: l_payroll_id_updated boolean;
1553: l_other_manager_warning boolean;
1596: -- Bug# 2273304 End Here
1597: --
1598: cursor csr_future_asg_changes is
1599: select 'x'
1600: from per_assignments_f asg
1601: where asg.person_id = p_person_id
1602: --bug no 5105005
1603: and asg.effective_start_date > p_hire_date;
1604: --bug no 5105005
1626: and nvl(pap.date_end,hr_api.g_eot);
1627: --
1628: cursor csr_chk_asg_status is
1629: select count(asg.assignment_id)
1630: from per_assignments_f asg,
1631: per_assignment_status_types pas
1632: where asg.assignment_status_type_id = pas.assignment_status_type_id
1633: and asg.person_id = p_person_id
1634: and pas.per_system_status = 'ACCEPTED'
1638: cursor csr_chk_assignment_id is
1639: select per.person_id,
1640: pas.per_system_status
1641: from per_all_people_f per,
1642: per_assignments_f asg,
1643: per_assignment_status_types pas
1644: where asg.assignment_status_type_id = pas.assignment_status_type_id
1645: and per.person_id = asg.person_id
1646: and l_hire_date between per.effective_start_date
1651: --
1652: cursor csr_get_un_accepted is
1653: select asg.assignment_id,
1654: asg.object_version_number
1655: from per_assignments_f asg,
1656: per_assignment_status_types pas
1657: where asg.assignment_status_type_id = pas.assignment_status_type_id
1658: and asg.person_id = p_person_id
1659: and l_hire_date between asg.effective_start_date
1665: cursor csr_get_accepted is
1666: select asg.assignment_id,
1667: asg.object_version_number,
1668: asg.vacancy_id
1669: from per_assignments_f asg,
1670: per_assignment_status_types pas
1671: where asg.assignment_status_type_id = pas.assignment_status_type_id
1672: and asg.person_id = p_person_id
1673: and l_hire_date between asg.effective_start_date
1680: cursor csr_get_accepted_pmry is
1681: select asg.assignment_id,
1682: asg.object_version_number,
1683: asg.vacancy_id
1684: from per_assignments_f asg,
1685: per_assignment_status_types pas
1686: where asg.assignment_status_type_id = pas.assignment_status_type_id
1687: and asg.person_id = p_person_id
1688: and l_hire_date between asg.effective_start_date
1693: cursor csr_get_accepted_non is
1694: select asg.assignment_id,
1695: asg.object_version_number,
1696: asg.vacancy_id
1697: from per_assignments_f asg,
1698: per_assignment_status_types pas
1699: where asg.assignment_status_type_id = pas.assignment_status_type_id
1700: and asg.person_id = p_person_id
1701: and l_hire_date between asg.effective_start_date
2114: hr_utility.set_location(l_proc,120);
2115: --
2116: -- Check p_assignment is corresponding data.
2117: -- The assignment record specified by P_ASSIGNMENT_ID on the hire
2118: -- date in the PER_ASSIGNMENTS_F table has assignment status
2119: -- 'ACCEPTED'.
2120: --
2121: if p_assignment_id is not null then
2122: --
2291: -- p_actual_termination_date => p_hire_date-1);
2292: --
2293: --
2294: -- Set all unaccepted applicant assignments to have end date = p_hire_date -1
2295: -- by calling the del procedure in the PER_ASSIGNMENTS_F table handler
2296: -- (This is a datetrack DELETE mode operation)
2297: --
2298: open csr_get_un_accepted;
2299: loop
2494: -- All accepted applicant assignments are changed to employee assignments
2495: -- with default employee assignment.(ACTIVE_ASSIGN)
2496: -- 1) Derive assignment_status_type_id for default 'ACTIVE_ASSIGN'.
2497: -- 2) Update the assignments by calling the upd procedure in the
2498: -- PER_ASSIGNMENTS_F table handler(This is a datetrack UPDATE mode
2499: -- operation)
2500: -- 3) When the accepted assignments are multiple, the primary flag of the
2501: -- record not specified by P_ASSIGNMENT_ID is set to 'N'.
2502: --
4130: l_legislation_code per_business_groups.legislation_code%type;
4131: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
4132: l_person_type_id1 per_people_f.person_type_id%type;
4133: l_application_id per_applications.application_id%type;
4134: l_comment_id per_assignments_f.comment_id%type;
4135: l_assignment_sequence per_assignments_f.assignment_sequence%type;
4136: l_assignment_id per_assignments_f.assignment_id%type;
4137: l_object_version_number per_assignments_f.object_version_number%type;
4138: l_current_applicant_flag per_people_f.current_applicant_flag%type;
4131: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
4132: l_person_type_id1 per_people_f.person_type_id%type;
4133: l_application_id per_applications.application_id%type;
4134: l_comment_id per_assignments_f.comment_id%type;
4135: l_assignment_sequence per_assignments_f.assignment_sequence%type;
4136: l_assignment_id per_assignments_f.assignment_id%type;
4137: l_object_version_number per_assignments_f.object_version_number%type;
4138: l_current_applicant_flag per_people_f.current_applicant_flag%type;
4139: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
4132: l_person_type_id1 per_people_f.person_type_id%type;
4133: l_application_id per_applications.application_id%type;
4134: l_comment_id per_assignments_f.comment_id%type;
4135: l_assignment_sequence per_assignments_f.assignment_sequence%type;
4136: l_assignment_id per_assignments_f.assignment_id%type;
4137: l_object_version_number per_assignments_f.object_version_number%type;
4138: l_current_applicant_flag per_people_f.current_applicant_flag%type;
4139: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
4140: l_current_employee_flag per_people_f.current_employee_flag%type;
4133: l_application_id per_applications.application_id%type;
4134: l_comment_id per_assignments_f.comment_id%type;
4135: l_assignment_sequence per_assignments_f.assignment_sequence%type;
4136: l_assignment_id per_assignments_f.assignment_id%type;
4137: l_object_version_number per_assignments_f.object_version_number%type;
4138: l_current_applicant_flag per_people_f.current_applicant_flag%type;
4139: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
4140: l_current_employee_flag per_people_f.current_employee_flag%type;
4141: l_employee_number per_people_f.employee_number%type;