[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: -- -----------------------------------------------------------------------------
1844: l_hire_date date;
1845: l_original_date_of_hire date;
1846: --
1847: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1848: l_assignment_id per_assignments_f.assignment_id%TYPE;
1849: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1850: --
1851: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1852: l_employee_number per_all_people_f.employee_number%TYPE;
1845: l_original_date_of_hire date;
1846: --
1847: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1848: l_assignment_id per_assignments_f.assignment_id%TYPE;
1849: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1850: --
1851: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1852: l_employee_number per_all_people_f.employee_number%TYPE;
1853: l_npw_number per_all_people_f.npw_number%TYPE;
1852: l_employee_number per_all_people_f.employee_number%TYPE;
1853: l_npw_number per_all_people_f.npw_number%TYPE;
1854: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1855: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1856: l_comment_id per_assignments_f.comment_id%TYPE;
1857: l_current_applicant_flag varchar2(1);
1858: l_current_emp_or_apl_flag varchar2(1);
1859: l_current_employee_flag varchar2(1);
1860: l_full_name per_all_people_f.full_name%TYPE;
1867: L_ASG_STATUS_OVN number;
1868: l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
1869: l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
1870: --
1871: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1872: --
1873: l_primary_flag per_assignments_f.primary_flag%TYPE;
1874: --
1875: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1869: l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
1870: --
1871: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1872: --
1873: l_primary_flag per_assignments_f.primary_flag%TYPE;
1874: --
1875: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1876: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1877: l_validation_start_date date;
1871: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1872: --
1873: l_primary_flag per_assignments_f.primary_flag%TYPE;
1874: --
1875: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1876: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1877: l_validation_start_date date;
1878: l_validation_end_date date;
1879: l_payroll_id_updated boolean;
1872: --
1873: l_primary_flag per_assignments_f.primary_flag%TYPE;
1874: --
1875: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1876: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1877: l_validation_start_date date;
1878: l_validation_end_date date;
1879: l_payroll_id_updated boolean;
1880: l_other_manager_warning boolean;
2032: and nvl(pap.date_end,hr_api.g_eot);
2033: --
2034: cursor csr_chk_asg_status is
2035: select count(asg.assignment_id)
2036: from per_assignments_f asg,
2037: per_assignment_status_types pas
2038: where asg.assignment_status_type_id = pas.assignment_status_type_id
2039: and asg.person_id = p_person_id
2040: and pas.per_system_status = 'ACCEPTED'
2044: cursor csr_chk_assignment_id is
2045: select per.person_id,
2046: pas.per_system_status
2047: from per_all_people_f per,
2048: per_assignments_f asg,
2049: per_assignment_status_types pas
2050: where asg.assignment_status_type_id = pas.assignment_status_type_id
2051: and per.person_id = asg.person_id
2052: and l_hire_date between per.effective_start_date
2057: --
2058: cursor csr_get_un_accepted is
2059: select asg.assignment_id,
2060: asg.object_version_number
2061: from per_assignments_f asg,
2062: per_assignment_status_types pas
2063: where asg.assignment_status_type_id = pas.assignment_status_type_id
2064: and asg.person_id = p_person_id
2065: and l_hire_date between asg.effective_start_date
2071: cursor csr_get_accepted is
2072: select asg.assignment_id,
2073: asg.object_version_number,
2074: asg.vacancy_id
2075: from per_assignments_f asg,
2076: per_assignment_status_types pas
2077: where asg.assignment_status_type_id = pas.assignment_status_type_id
2078: and asg.person_id = p_person_id
2079: and l_hire_date between asg.effective_start_date
2086: cursor csr_get_accepted_pmry is
2087: select asg.assignment_id,
2088: asg.object_version_number,
2089: asg.vacancy_id
2090: from per_assignments_f asg,
2091: per_assignment_status_types pas
2092: where asg.assignment_status_type_id = pas.assignment_status_type_id
2093: and asg.person_id = p_person_id
2094: and l_hire_date between asg.effective_start_date
2099: cursor csr_get_accepted_non is
2100: select asg.assignment_id,
2101: asg.object_version_number,
2102: asg.vacancy_id
2103: from per_assignments_f asg,
2104: per_assignment_status_types pas
2105: where asg.assignment_status_type_id = pas.assignment_status_type_id
2106: and asg.person_id = p_person_id
2107: and l_hire_date between asg.effective_start_date
2242:
2243: -- Bug 8831084 Fix Starts
2244: cursor csr_get_ex_ass_entmnt is
2245: select asg.assignment_id
2246: from per_assignments_f asg,per_assignment_status_types pas
2247: where asg.assignment_status_type_id = pas.assignment_status_type_id
2248: and asg.person_id = p_person_id
2249: and p_hire_date between asg.effective_start_date
2250: and asg.effective_end_date
2673: hr_utility.set_location(l_proc,120);
2674: --
2675: -- Check p_assignment is corresponding data.
2676: -- The assignment record specified by P_ASSIGNMENT_ID on the hire
2677: -- date in the PER_ASSIGNMENTS_F table has assignment status
2678: -- 'ACCEPTED'.
2679: --
2680: if p_assignment_id is not null then
2681: --
2927: -- p_actual_termination_date => p_hire_date-1);
2928: --
2929: --
2930: -- Set all unaccepted applicant assignments to have end date = p_hire_date -1
2931: -- by calling the del procedure in the PER_ASSIGNMENTS_F table handler
2932: -- (This is a datetrack DELETE mode operation)
2933: --
2934: open csr_get_un_accepted;
2935: loop
3336: -- All accepted applicant assignments are changed to employee assignments
3337: -- with default employee assignment.(ACTIVE_ASSIGN)
3338: -- 1) Derive assignment_status_type_id for default 'ACTIVE_ASSIGN'.
3339: -- 2) Update the assignments by calling the upd procedure in the
3340: -- PER_ASSIGNMENTS_F table handler(This is a datetrack UPDATE mode
3341: -- operation)
3342: -- 3) When the accepted assignments are multiple, the primary flag of the
3343: -- record not specified by P_ASSIGNMENT_ID is set to 'N'.
3344: --
5203: l_legislation_code per_business_groups.legislation_code%type;
5204: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
5205: l_person_type_id1 per_people_f.person_type_id%type;
5206: l_application_id per_applications.application_id%type;
5207: l_comment_id per_assignments_f.comment_id%type;
5208: l_assignment_sequence per_assignments_f.assignment_sequence%type;
5209: l_assignment_id per_assignments_f.assignment_id%type;
5210: l_object_version_number per_assignments_f.object_version_number%type;
5211: l_current_applicant_flag per_people_f.current_applicant_flag%type;
5204: l_person_type_id per_people_f.person_type_id%type := p_person_type_id;
5205: l_person_type_id1 per_people_f.person_type_id%type;
5206: l_application_id per_applications.application_id%type;
5207: l_comment_id per_assignments_f.comment_id%type;
5208: l_assignment_sequence per_assignments_f.assignment_sequence%type;
5209: l_assignment_id per_assignments_f.assignment_id%type;
5210: l_object_version_number per_assignments_f.object_version_number%type;
5211: l_current_applicant_flag per_people_f.current_applicant_flag%type;
5212: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
5205: l_person_type_id1 per_people_f.person_type_id%type;
5206: l_application_id per_applications.application_id%type;
5207: l_comment_id per_assignments_f.comment_id%type;
5208: l_assignment_sequence per_assignments_f.assignment_sequence%type;
5209: l_assignment_id per_assignments_f.assignment_id%type;
5210: l_object_version_number per_assignments_f.object_version_number%type;
5211: l_current_applicant_flag per_people_f.current_applicant_flag%type;
5212: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
5213: l_current_employee_flag per_people_f.current_employee_flag%type;
5206: l_application_id per_applications.application_id%type;
5207: l_comment_id per_assignments_f.comment_id%type;
5208: l_assignment_sequence per_assignments_f.assignment_sequence%type;
5209: l_assignment_id per_assignments_f.assignment_id%type;
5210: l_object_version_number per_assignments_f.object_version_number%type;
5211: l_current_applicant_flag per_people_f.current_applicant_flag%type;
5212: l_current_emp_or_apl_flag per_people_f.current_emp_or_apl_flag%type;
5213: l_current_employee_flag per_people_f.current_employee_flag%type;
5214: l_employee_number per_people_f.employee_number%type;