[Home] [Help]
36: IS
37: SELECT asg.assignment_id
38: ,asg.object_version_number
39: FROM per_assignments_f asg
40: ,per_assignment_status_types ast
41: WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
42: AND asg.person_id = csr_nonaccepted_asgs.p_person_id
43: AND csr_nonaccepted_asgs.p_effective_date BETWEEN asg.effective_start_date
44: AND asg.effective_end_date
54: SELECT asg.assignment_id
55: ,asg.object_version_number
56: ,asg.vacancy_id
57: FROM per_assignments_f asg
58: ,per_assignment_status_types ast
59: WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
60: AND asg.person_id = csr_accepted_asgs.p_person_id
61: AND ( asg.assignment_id = csr_accepted_asgs.p_assignment_id
62: OR csr_accepted_asgs.p_assignment_id = hr_api.g_number)
72: IS
73: SELECT asg.assignment_id
74: ,asg.object_version_number
75: FROM per_assignments_f asg
76: ,per_assignment_status_types ast
77: WHERE asg.assignment_status_type_id = ast.assignment_status_type_id
78: AND asg.person_id = csr_primary_asgs.p_person_id
79: AND csr_primary_asgs.p_effective_date BETWEEN asg.effective_start_date
80: AND asg.effective_end_date
848: l_future_asgs_count INTEGER;
849: l_nonaccepted_asgs_count INTEGER;
850: l_accepted_asgs_count INTEGER;
851: l_primary_asgs_count INTEGER;
852: l_assignment_status_type_id per_assignment_status_types.assignment_status_type_id%TYPE;
853: l_primary_flag per_all_assignments_f.primary_flag%TYPE;
854: --
855: l_effective_start_date DATE;
856: l_effective_end_date DATE;
1707: l_proc varchar2(72) := g_package||'hire_employee_applicant';
1708: --
1709: l_exists varchar2(1);
1710: l_count number;
1711: l_chk_system_status per_assignment_status_types.per_system_status%TYPE;
1712: l_chk_person_id per_all_people_f.person_id%TYPE;
1713: --
1714: l_person_type_id number := p_person_type_id;
1715: l_person_type_id1 number;
1724: l_apl_object_version_number per_applications.application_id%TYPE;
1725: --
1726: l_hire_date date;
1727: --
1728: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1729: l_assignment_id per_assignments_f.assignment_id%TYPE;
1730: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1731: --
1732: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1818: --
1819: cursor csr_chk_asg_status is
1820: select count(asg.assignment_id)
1821: from per_assignments_f asg,
1822: per_assignment_status_types pas
1823: where asg.assignment_status_type_id = pas.assignment_status_type_id
1824: and asg.person_id = p_person_id
1825: and pas.per_system_status = 'ACCEPTED'
1826: and l_hire_date between asg.effective_start_date
1830: select per.person_id,
1831: pas.per_system_status
1832: from per_all_people_f per,
1833: per_assignments_f asg,
1834: per_assignment_status_types pas
1835: where asg.assignment_status_type_id = pas.assignment_status_type_id
1836: and per.person_id = asg.person_id
1837: and l_hire_date between per.effective_start_date
1838: and per.effective_end_date
1843: cursor csr_get_un_accepted is
1844: select asg.assignment_id,
1845: asg.object_version_number
1846: from per_assignments_f asg,
1847: per_assignment_status_types pas
1848: where asg.assignment_status_type_id = pas.assignment_status_type_id
1849: and asg.person_id = p_person_id
1850: and asg.assignment_type='A'
1851: and l_hire_date between asg.effective_start_date
1859: asg.object_version_number,
1860: asg.effective_start_date,
1861: asg.vacancy_id
1862: from per_assignments_f asg,
1863: per_assignment_status_types pas
1864: where asg.assignment_status_type_id = pas.assignment_status_type_id
1865: and asg.person_id = p_person_id
1866: and l_hire_date between asg.effective_start_date
1867: and asg.effective_end_date
1875: asg.effective_start_date,
1876: asg.vacancy_id
1877:
1878: from per_assignments_f asg,
1879: per_assignment_status_types pas
1880: where asg.assignment_status_type_id = pas.assignment_status_type_id
1881: and asg.person_id = p_person_id
1882: and l_hire_date between asg.effective_start_date
1883: and asg.effective_end_date
4172: l_proc varchar2(72) := g_package||'hire_employee_applicant';
4173: --
4174: l_exists varchar2(1);
4175: l_count number;
4176: l_chk_system_status per_assignment_status_types.per_system_status%TYPE;
4177: l_chk_person_id per_all_people_f.person_id%TYPE;
4178: --
4179: l_person_type_id number := p_person_type_id;
4180: l_person_type_id1 number;
4189: l_apl_object_version_number per_applications.application_id%TYPE;
4190: --
4191: l_hire_date date;
4192: --
4193: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
4194: l_assignment_id per_assignments_f.assignment_id%TYPE;
4195: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
4196: --
4197: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
4291: --
4292: cursor csr_chk_asg_status is
4293: select count(asg.assignment_id)
4294: from per_assignments_f asg,
4295: per_assignment_status_types pas
4296: where asg.assignment_status_type_id = pas.assignment_status_type_id
4297: and asg.person_id = p_person_id
4298: and pas.per_system_status = 'ACCEPTED'
4299: and l_hire_date between asg.effective_start_date
4303: select per.person_id,
4304: pas.per_system_status
4305: from per_all_people_f per,
4306: per_assignments_f asg,
4307: per_assignment_status_types pas
4308: where asg.assignment_status_type_id = pas.assignment_status_type_id
4309: and per.person_id = asg.person_id
4310: and l_hire_date between per.effective_start_date
4311: and per.effective_end_date
4316: cursor csr_get_un_accepted is
4317: select asg.assignment_id,
4318: asg.object_version_number
4319: from per_assignments_f asg,
4320: per_assignment_status_types pas
4321: where asg.assignment_status_type_id = pas.assignment_status_type_id
4322: and asg.person_id = p_person_id
4323: and asg.assignment_type='A'
4324: and l_hire_date between asg.effective_start_date
4332: asg.object_version_number,
4333: asg.effective_start_date,
4334: asg.vacancy_id
4335: from per_assignments_f asg,
4336: per_assignment_status_types pas
4337: where asg.assignment_status_type_id = pas.assignment_status_type_id
4338: and asg.person_id = p_person_id
4339: and l_hire_date between asg.effective_start_date
4340: and asg.effective_end_date
4355: asg.vacancy_id,
4356: asg.assignment_status_type_id -- 10376122
4357:
4358: from per_assignments_f asg,
4359: per_assignment_status_types pas
4360: where asg.assignment_status_type_id = pas.assignment_status_type_id
4361: and asg.person_id = p_person_id
4362: and l_hire_date between asg.effective_start_date
4363: and asg.effective_end_date
4477: cursor csr_get_all_appl_asgs is
4478: select 'X' from dual where exists
4479: ( select *
4480: from per_assignments_f asg,
4481: per_assignment_status_types pas
4482: where asg.assignment_status_type_id = pas.assignment_status_type_id
4483: and asg.person_id = p_person_id
4484: and asg.assignment_type='A'
4485: and l_hire_date between asg.effective_start_date