[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
797: l_future_asgs_count INTEGER;
798: l_nonaccepted_asgs_count INTEGER;
799: l_accepted_asgs_count INTEGER;
800: l_primary_asgs_count INTEGER;
801: l_assignment_status_type_id per_assignment_status_types.assignment_status_type_id%TYPE;
802: l_primary_flag per_all_assignments_f.primary_flag%TYPE;
803: --
804: l_effective_start_date DATE;
805: l_effective_end_date DATE;
1493: l_proc varchar2(72) := g_package||'hire_employee_applicant';
1494: --
1495: l_exists varchar2(1);
1496: l_count number;
1497: l_chk_system_status per_assignment_status_types.per_system_status%TYPE;
1498: l_chk_person_id per_all_people_f.person_id%TYPE;
1499: --
1500: l_person_type_id number := p_person_type_id;
1501: l_person_type_id1 number;
1510: l_apl_object_version_number per_applications.application_id%TYPE;
1511: --
1512: l_hire_date date;
1513: --
1514: l_per_system_status per_assignment_status_types.per_system_status%TYPE;
1515: l_assignment_id per_assignments_f.assignment_id%TYPE;
1516: l_asg_object_version_number per_assignments_f.object_version_number%TYPE;
1517: --
1518: l_per_object_version_number per_all_people_f.object_version_number%TYPE;
1597: --
1598: cursor csr_chk_asg_status is
1599: select count(asg.assignment_id)
1600: from per_assignments_f asg,
1601: per_assignment_status_types pas
1602: where asg.assignment_status_type_id = pas.assignment_status_type_id
1603: and asg.person_id = p_person_id
1604: and pas.per_system_status = 'ACCEPTED'
1605: and l_hire_date between asg.effective_start_date
1609: select per.person_id,
1610: pas.per_system_status
1611: from per_all_people_f per,
1612: per_assignments_f asg,
1613: per_assignment_status_types pas
1614: where asg.assignment_status_type_id = pas.assignment_status_type_id
1615: and per.person_id = asg.person_id
1616: and l_hire_date between per.effective_start_date
1617: and per.effective_end_date
1622: cursor csr_get_un_accepted is
1623: select asg.assignment_id,
1624: asg.object_version_number
1625: from per_assignments_f asg,
1626: per_assignment_status_types pas
1627: where asg.assignment_status_type_id = pas.assignment_status_type_id
1628: and asg.person_id = p_person_id
1629: and asg.assignment_type='A'
1630: and l_hire_date between asg.effective_start_date
1638: asg.object_version_number,
1639: asg.effective_start_date,
1640: asg.vacancy_id
1641: from per_assignments_f asg,
1642: per_assignment_status_types pas
1643: where asg.assignment_status_type_id = pas.assignment_status_type_id
1644: and asg.person_id = p_person_id
1645: and l_hire_date between asg.effective_start_date
1646: and asg.effective_end_date
1654: asg.effective_start_date,
1655: asg.vacancy_id
1656:
1657: from per_assignments_f asg,
1658: per_assignment_status_types pas
1659: where asg.assignment_status_type_id = pas.assignment_status_type_id
1660: and asg.person_id = p_person_id
1661: and l_hire_date between asg.effective_start_date
1662: and asg.effective_end_date