[Home] [Help]
21: )
22: IS
23: SELECT asg.assignment_id
24: ,asg.object_version_number
25: FROM per_assignments_f asg
26: WHERE asg.person_id = csr_future_asgs.p_person_id
27: AND asg.effective_start_date > csr_future_asgs.p_effective_date
28: AND (p_assignment_id is null OR
29: (p_assignment_id is not null AND
35: )
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
53: IS
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
71: )
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
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;
1733: l_ovn per_all_people_f.object_version_number%TYPE := p_per_object_version_number;
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;
1733: l_ovn per_all_people_f.object_version_number%TYPE := p_per_object_version_number;
1734: l_employee_number per_all_people_f.employee_number%TYPE;
1735: l_applicant_number per_all_people_f.applicant_number%TYPE;
1736: l_npw_number per_all_people_f.npw_number%TYPE;
1737: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
1738: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
1739: l_comment_id per_assignments_f.comment_id%TYPE;
1740: l_current_applicant_flag varchar2(1);
1741: l_current_emp_or_apl_flag varchar2(1);
1742: l_current_employee_flag varchar2(1);
1743: l_full_name per_all_people_f.full_name%TYPE;
1750: --
1751: l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
1752: l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
1753: --
1754: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1755: --
1756: l_primary_flag per_assignments_f.primary_flag%TYPE;
1757: --
1758: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1752: l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
1753: --
1754: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1755: --
1756: l_primary_flag per_assignments_f.primary_flag%TYPE;
1757: --
1758: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1759: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1760: l_validation_start_date date;
1754: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1755: --
1756: l_primary_flag per_assignments_f.primary_flag%TYPE;
1757: --
1758: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1759: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1760: l_validation_start_date date;
1761: l_validation_end_date date;
1762: l_payroll_id_updated boolean;
1755: --
1756: l_primary_flag per_assignments_f.primary_flag%TYPE;
1757: --
1758: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1759: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1760: l_validation_start_date date;
1761: l_validation_end_date date;
1762: l_payroll_id_updated boolean;
1763: l_other_manager_warning boolean;
1782: --
1783: --
1784: cursor csr_future_asg_changes is
1785: select 'x'
1786: from per_assignments_f asg
1787: where asg.person_id = p_person_id
1788: and asg.effective_start_date > p_hire_date;
1789: -- ER FPT
1790: cursor fpt_future_asg_changes is
1817: and nvl(pap.date_end,hr_api.g_eot);
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'
1829: cursor csr_chk_assignment_id is
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
1842: --
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'
1858: select asg.assignment_id,
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
1874: asg.object_version_number,
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
2215: hr_utility.set_location(l_proc,120);
2216: --
2217: -- Check p_assignment is corresponding data.
2218: -- The assignment record specified by P_ASSIGNMENT_ID on the hire
2219: -- date in the PER_ASSIGNMENTS_F table has assignment status
2220: -- 'ACCEPTED'.
2221: --
2222: if p_primary_assignment_id is not null then
2223: --
2324: );
2325: hr_utility.set_location(l_proc,190);
2326: --
2327: -- Set all unaccepted applicant assignments to have end date = p_hire_date -1
2328: -- by calling the del procedure in the PER_ASSIGNMENTS_F table handler
2329: -- (This is a datetrack DELETE mode operation)
2330: --
2331: open csr_get_un_accepted;
2332: loop
2440: -- All accepted applicant assignments are changed to employee assignments
2441: -- with default employee assignment.(ACTIVE_ASSIGN)
2442: -- 1) Derive assignment_status_type_id for default 'ACTIVE_ASSIGN'.
2443: -- 2) Update the assignments by calling the upd procedure in the
2444: -- PER_ASSIGNMENTS_F table handler(This is a datetrack UPDATE mode
2445: -- operation)
2446: -- 3) When the accepted assignments are multiple, the primary flag of the
2447: -- record not specified by P_ASSIGNMENT_ID is set to 'N'.
2448: --
2930: hr_utility.set_location(l_proc, 271);
2931:
2932: --Fix For Bug # 5987409 Starts -----
2933:
2934: UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
2935: PAF.RECRUITER_ID =l_asg_rec.recruiter_id
2936: WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
2937: PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
2938: PAF.EFFECTIVE_END_DATE = l_effective_end_date;
3566:
3567:
3568: --Fix For Bug # 5987409 Starts -----
3569:
3570: UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
3571: PAF.RECRUITER_ID =l_asg_rec.recruiter_id
3572: WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
3573: PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
3574: PAF.EFFECTIVE_END_DATE = l_effective_end_date;
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;
4198: l_ovn per_all_people_f.object_version_number%TYPE := p_per_object_version_number;
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;
4198: l_ovn per_all_people_f.object_version_number%TYPE := p_per_object_version_number;
4199: l_employee_number per_all_people_f.employee_number%TYPE;
4200: l_applicant_number per_all_people_f.applicant_number%TYPE;
4201: l_npw_number per_all_people_f.npw_number%TYPE;
4202: l_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
4203: l_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
4204: l_comment_id per_assignments_f.comment_id%TYPE;
4205: l_current_applicant_flag varchar2(1);
4206: l_current_emp_or_apl_flag varchar2(1);
4207: l_current_employee_flag varchar2(1);
4208: l_full_name per_all_people_f.full_name%TYPE;
4215: --
4216: l_period_of_service_id per_periods_of_service.period_of_service_id%TYPE;
4217: l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
4218: --
4219: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
4220: --
4221: l_primary_flag per_assignments_f.primary_flag%TYPE;
4222: --
4223: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
4217: l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
4218: --
4219: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
4220: --
4221: l_primary_flag per_assignments_f.primary_flag%TYPE;
4222: --
4223: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
4224: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
4225: l_validation_start_date date;
4219: l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
4220: --
4221: l_primary_flag per_assignments_f.primary_flag%TYPE;
4222: --
4223: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
4224: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
4225: l_validation_start_date date;
4226: l_validation_end_date date;
4227: l_payroll_id_updated boolean;
4220: --
4221: l_primary_flag per_assignments_f.primary_flag%TYPE;
4222: --
4223: l_effective_start_date per_assignments_f.effective_start_date%TYPE;
4224: l_effective_end_date per_assignments_f.effective_end_date%TYPE;
4225: l_validation_start_date date;
4226: l_validation_end_date date;
4227: l_payroll_id_updated boolean;
4228: l_other_manager_warning boolean;
4254: l_asg_status_ovn number;
4255: -- bug 10376122
4256: cursor csr_future_asg_changes is
4257: select 'x'
4258: from per_assignments_f asg
4259: where asg.person_id = p_person_id
4260: and asg.effective_start_date > p_hire_date;
4261:
4262: -- ER FPT
4290: and nvl(pap.date_end,hr_api.g_eot);
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'
4302: cursor csr_chk_assignment_id is
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
4315: --
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'
4331: select asg.assignment_id,
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
4354: asg.effective_start_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
4476: ---into which we are hiring.
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'
4749: hr_utility.set_location(l_proc,120);
4750: --
4751: -- Check p_assignment is corresponding data.
4752: -- The assignment record specified by P_ASSIGNMENT_ID on the hire
4753: -- date in the PER_ASSIGNMENTS_F table has assignment status
4754: -- 'ACCEPTED'.
4755: --
4756: if p_primary_assignment_id is not null then
4757: --
4866: hr_utility.set_location(l_proc,190);
4867: end if; -- SSHR Enhancement (Bug # 8536819).
4868: --
4869: -- Set all unaccepted applicant assignments to have end date = p_hire_date -1
4870: -- by calling the del procedure in the PER_ASSIGNMENTS_F table handler
4871: -- (This is a datetrack DELETE mode operation)
4872: --
4873: --Commented the below cursor loop as it is not required to close the
4874: --unaccepted applications when hiring the employee.applicant in SSHR.
5020: -- All accepted applicant assignments are changed to employee assignments
5021: -- with default employee assignment.(ACTIVE_ASSIGN)
5022: -- 1) Derive assignment_status_type_id for default 'ACTIVE_ASSIGN'.
5023: -- 2) Update the assignments by calling the upd procedure in the
5024: -- PER_ASSIGNMENTS_F table handler(This is a datetrack UPDATE mode
5025: -- operation)
5026: -- 3) When the accepted assignments are multiple, the primary flag of the
5027: -- record not specified by P_ASSIGNMENT_ID is set to 'N'.
5028: --
5612: hr_utility.set_location('1_asg_rec.vacany_id'||l_asg_rec.vacancy_id,103);
5613: hr_utility.set_location('l_asg_rec.assignment_id'||l_asg_rec.assignment_id,103);
5614:
5615: /*
5616: UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
5617: PAF.RECRUITER_ID =l_asg_rec.recruiter_id
5618: WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
5619: PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
5620: PAF.EFFECTIVE_END_DATE = l_effective_end_date;
5619: PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
5620: PAF.EFFECTIVE_END_DATE = l_effective_end_date;
5621: */
5622: -- vb 10376122
5623: UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_aplasg_vacancy_id ,
5624: PAF.RECRUITER_ID = l_aplasg_RECRUITER_ID
5625: WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
5626: PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
5627: PAF.EFFECTIVE_END_DATE = l_effective_end_date;
6336:
6337:
6338: --Fix For Bug # 5987409 Starts -----
6339: /*
6340: UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_asg_rec.vacancy_id ,
6341: PAF.RECRUITER_ID =l_asg_rec.recruiter_id
6342: WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
6343: PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
6344: PAF.EFFECTIVE_END_DATE = l_effective_end_date;
6352:
6353: hr_utility.set_location('p_asg_rec.assignment_id'||p_asg_rec.assignment_id,180);
6354: hr_utility.set_location('l_asg_rec.assignment_id'||l_asg_rec.assignment_id,180);
6355:
6356: UPDATE PER_ASSIGNMENTS_F PAF SET PAF.VACANCY_ID =l_aplasg_vacancy_id ,
6357: PAF.RECRUITER_ID = l_aplasg_RECRUITER_ID
6358: WHERE PAF.ASSIGNMENT_ID = l_primary_asg_id AND
6359: PAF.EFFECTIVE_START_DATE = l_effective_start_date AND
6360: PAF.EFFECTIVE_END_DATE = l_effective_end_date;