[Home] [Help]
35: l_apl_object_version_number per_applications.application_id%TYPE;
36: l_per_effective_start_date per_all_people_f.effective_start_date%type;
37: l_per_effective_start_date2 per_all_people_f.effective_start_date%type;
38: l_per_effective_end_date per_all_people_f.effective_end_date%type;
39: l_asg_effective_start_date per_all_assignments_f.effective_start_date%type;
40: l_asg_effective_end_date per_all_assignments_f.effective_end_date%type;
41: l_comment_id per_all_people_f.comment_id%type;
42: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
43: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%type;
36: l_per_effective_start_date per_all_people_f.effective_start_date%type;
37: l_per_effective_start_date2 per_all_people_f.effective_start_date%type;
38: l_per_effective_end_date per_all_people_f.effective_end_date%type;
39: l_asg_effective_start_date per_all_assignments_f.effective_start_date%type;
40: l_asg_effective_end_date per_all_assignments_f.effective_end_date%type;
41: l_comment_id per_all_people_f.comment_id%type;
42: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
43: l_current_emp_or_apl_flag per_all_people_f.current_emp_or_apl_flag%type;
44: l_current_employee_flag per_all_people_f.current_employee_flag%type;
44: l_current_employee_flag per_all_people_f.current_employee_flag%type;
45: l_full_name per_all_people_f.full_name%type;
46: l_per_object_version_number per_all_people_f.object_version_number%type;
47: l_per_object_version_number2 per_all_people_f.object_version_number%type;
48: l_asg_object_version_number per_all_assignments_f.object_version_number%type;
49: l_per_system_status per_assignment_status_types.per_system_status%type;
50: l_employee_number per_all_people_f.employee_number%type;
51: l_applicant_number per_all_people_f.applicant_number%type;
52: l_datetrack_update_mode varchar2(30);
63: and asg.effective_start_date >= p_hire_date; -- bug 4681265 changed the condition from ' > ' to " >= " .
64: -- ER FPT
65: cursor fpt_future_asg_changes is
66: select 1
67: from per_all_assignments_f asg
68: where asg.person_id = p_person_id
69: and asg.effective_start_date >= p_hire_date
70: and asg.assignment_type = 'E';
71:
83: --
84: cursor get_assignments(p_person_id number,p_date date) is
85: select asg.assignment_id
86: ,asg.object_version_number
87: from per_all_assignments_f asg
88: where asg.person_id=p_person_id
89: and p_date=asg.effective_start_date;
90: --
91: cursor csr_asg_status(p_assignment_id number, p_date date) is
123: --
124: -- Bug 4755015 Starts
125: cursor get_business_group(p_asg_id number) is
126: select distinct PAAF.business_group_id
127: from per_all_assignments_f PAAF
128: where PAAF.assignment_id=p_asg_id;
129: l_bg_id number;
130: -- Bug 4755015 Ends
131: begin
233: --
234: update per_letter_request_lines
235: set date_from=l_hire_date-1
236: where assignment_id=(select asg2.assignment_id
237: from per_all_assignments_f asg2
238: where asg2.person_id=p_person_id
239: and asg2.assignment_type='A'
240: and asg2.effective_start_date=l_hire_date)
241: and date_from=l_hire_date;
335: close c2;
336: --
337: -- we only need to move the start date, there can be no end dated asg before it.
338: --
339: update per_all_assignments_f
340: set effective_start_date=l_hire_date-1
341: where person_id=p_person_id
342: and assignment_type='A'
343: and effective_start_date=l_hire_date;
399: --
400: update per_letter_request_lines
401: set date_from=l_hire_date-1
402: where assignment_id=(select asg2.assignment_id
403: from per_all_assignments_f asg2
404: where asg2.person_id=p_person_id
405: and asg2.assignment_type='A'
406: and asg2.effective_start_date=l_hire_date)
407: and date_from=l_hire_date;
405: and asg2.assignment_type='A'
406: and asg2.effective_start_date=l_hire_date)
407: and date_from=l_hire_date;
408: --
409: update per_all_assignments_f
410: set effective_start_date=l_hire_date-1
411: where person_id=p_person_id
412: and assignment_type='A'
413: and effective_start_date=l_hire_date;
433: --
434: update per_letter_request_lines
435: set date_from=l_hire_date-1
436: where assignment_id=(select asg2.assignment_id
437: from per_all_assignments_f asg2
438: where asg2.person_id=p_person_id
439: and asg2.assignment_type='A'
440: and asg2.effective_start_date=l_hire_date)
441: and date_from=l_hire_date;
439: and asg2.assignment_type='A'
440: and asg2.effective_start_date=l_hire_date)
441: and date_from=l_hire_date;
442: --
443: update per_all_assignments_f
444: set effective_start_date=l_hire_date-1
445: where person_id=p_person_id
446: and assignment_type='A'
447: and effective_start_date=l_hire_date;
445: where person_id=p_person_id
446: and assignment_type='A'
447: and effective_start_date=l_hire_date;
448: --
449: update per_all_assignments_f
450: set effective_end_date=l_hire_date-2
451: where person_id=p_person_id
452: and assignment_type='A'
453: and effective_end_date=l_hire_date-1;
620: hr_utility.set_location(l_proc,330);
621: --
622: -- the assignment started today, so move it back one day to accept it yesterday
623: --
624: update per_all_assignments_f
625: set effective_start_date=l_hire_date-1
626: where person_id=p_person_id
627: and assignment_type='A'
628: and effective_start_date=l_hire_date;
657: --
658: -- the earlier assignment row starts before yesterday, so we can move the
659: -- recent end date back one day
660: --
661: update per_all_assignments_f
662: set effective_start_date=l_hire_date-1
663: where person_id=p_person_id
664: and assignment_type='A'
665: and effective_start_date=l_hire_date;
663: where person_id=p_person_id
664: and assignment_type='A'
665: and effective_start_date=l_hire_date;
666: --
667: update per_all_assignments_f
668: set effective_end_date=l_hire_date-2
669: where person_id=p_person_id
670: and assignment_type='A'
671: and effective_end_date=l_hire_date-1;
683: hr_utility.set_location(l_proc,385);
684: -- we should make sure that any applications that start on the hire date
685: -- are moved back one day, along with the application
686: begin
687: update per_all_assignments_f
688: set effective_start_date=l_hire_date-1
689: where person_id=p_person_id
690: and assignment_type='A'
691: and effective_start_date=l_hire_date;
774: (p_notification wf_messages.name%type
775: ,p_wf_name wf_item_types.name%type
776: ,p_role varchar2 --wf_roles.name%type Fix for bug 2741492
777: ,p_person_id per_all_people_f.person_id%type
778: ,p_assignment_id per_all_assignments_f.assignment_id%type
779: ,p_effective_date date
780: ,p_hire_date per_periods_of_service.date_start%type
781: ,p_full_name per_all_people_f.full_name%type
782: ,p_per_effective_start_date per_all_people_f.effective_start_date%type
785: ,p_last_name per_all_people_f.last_name%type
786: ,p_employee_number per_all_people_f.employee_number%type
787: ,p_applicant_number per_all_people_f.applicant_number%type
788: ,p_national_identifier per_all_people_f.national_identifier%type
789: ,p_asg_effective_start_date per_all_assignments_f.effective_start_date%type
790: ,p_organization hr_all_organization_units.name%type
791: ,p_grade per_grades.name%type
792: ,p_job per_jobs.name%type
793: ,p_position hr_all_positions_f.name%type
797: ,p_location hr_locations.location_code%type
798: ,p_salary per_pay_proposals.proposed_salary_n%type
799: ,p_salary_currency pay_element_types_f.input_currency_code%type
800: ,p_pay_basis hr_lookups.meaning%type
801: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
802: ,p_npw_number per_all_people_f.npw_number%type
803: ,p_vendor po_vendors.vendor_name%type
804: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
805: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
800: ,p_pay_basis hr_lookups.meaning%type
801: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
802: ,p_npw_number per_all_people_f.npw_number%type
803: ,p_vendor po_vendors.vendor_name%type
804: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
805: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
806: ,p_grade_ladder ben_pgm_f.name%type
807: ) return number is
808: l_nid number;
801: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
802: ,p_npw_number per_all_people_f.npw_number%type
803: ,p_vendor po_vendors.vendor_name%type
804: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
805: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
806: ,p_grade_ladder ben_pgm_f.name%type
807: ) return number is
808: l_nid number;
809: l_ff1 varchar2(240);
964: (p_notification wf_messages.name%type
965: ,p_wf_name wf_item_types.name%type
966: ,p_role varchar2 --wf_roles.name%type Fix for bug 2741492
967: ,p_person_id per_all_people_f.person_id%type
968: ,p_assignment_id per_all_assignments_f.assignment_id%type
969: ,p_effective_date date
970: ,p_hire_date per_periods_of_service.date_start%type
971: ,p_full_name per_all_people_f.full_name%type
972: ,p_per_effective_start_date per_all_people_f.effective_start_date%type
975: ,p_last_name per_all_people_f.last_name%type
976: ,p_employee_number per_all_people_f.employee_number%type
977: ,p_applicant_number per_all_people_f.applicant_number%type
978: ,p_national_identifier per_all_people_f.national_identifier%type
979: ,p_asg_effective_start_date per_all_assignments_f.effective_start_date%type
980: ,p_organization hr_all_organization_units.name%type
981: ,p_grade per_grades.name%type
982: ,p_job per_jobs.name%type
983: ,p_position hr_all_positions_f.name%type
987: ,p_location hr_locations.location_code%type
988: ,p_salary per_pay_proposals.proposed_salary_n%type
989: ,p_salary_currency pay_element_types_f.input_currency_code%type
990: ,p_pay_basis hr_lookups.meaning%type
991: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
992: ,p_npw_number per_all_people_f.npw_number%type
993: ,p_vendor po_vendors.vendor_name%type
994: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
995: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
990: ,p_pay_basis hr_lookups.meaning%type
991: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
992: ,p_npw_number per_all_people_f.npw_number%type
993: ,p_vendor po_vendors.vendor_name%type
994: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
995: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
996: ,p_grade_ladder ben_pgm_f.name%type
997: ) is
998: pragma autonomous_transaction;
991: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
992: ,p_npw_number per_all_people_f.npw_number%type
993: ,p_vendor po_vendors.vendor_name%type
994: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
995: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
996: ,p_grade_ladder ben_pgm_f.name%type
997: ) is
998: pragma autonomous_transaction;
999: l_nid number;
1051: (p_notification in wf_messages.name%type
1052: ,p_wf_name in wf_item_types.name%type
1053: ,p_role in varchar2 --wf_roles.name%type Fix for bug 2741492
1054: ,p_person_id in per_all_people_f.person_id%type
1055: ,p_assignment_id in per_all_assignments_f.assignment_id%type
1056: ,p_effective_date in date
1057: ,p_hire_date in per_periods_of_service.date_start%type
1058: ,p_full_name in per_all_people_f.full_name%type
1059: ,p_per_effective_start_date in per_all_people_f.effective_start_date%type
1062: ,p_last_name in per_all_people_f.last_name%type
1063: ,p_employee_number in per_all_people_f.employee_number%type
1064: ,p_applicant_number in per_all_people_f.applicant_number%type
1065: ,p_national_identifier in per_all_people_f.national_identifier%type
1066: ,p_asg_effective_start_date in per_all_assignments_f.effective_start_date%type
1067: ,p_organization in hr_all_organization_units.name%type
1068: ,p_grade in per_grades.name%type
1069: ,p_job in per_jobs.name%type
1070: ,p_position in hr_all_positions_f.name%type
1074: ,p_location in hr_locations.location_code%type
1075: ,p_salary in per_pay_proposals.proposed_salary_n%type
1076: ,p_salary_currency in pay_element_types_f.input_currency_code%type
1077: ,p_pay_basis in hr_lookups.meaning%type
1078: ,p_date_probation_end in per_all_assignments_f.date_probation_end%type
1079: ,p_npw_number in per_all_people_f.npw_number%type
1080: ,p_vendor in po_vendors.vendor_name%type
1081: ,p_supplier_reference in per_all_assignments_f.vendor_employee_number%type
1082: ,p_placement_date_start in per_all_assignments_f.period_of_placement_date_start%type
1077: ,p_pay_basis in hr_lookups.meaning%type
1078: ,p_date_probation_end in per_all_assignments_f.date_probation_end%type
1079: ,p_npw_number in per_all_people_f.npw_number%type
1080: ,p_vendor in po_vendors.vendor_name%type
1081: ,p_supplier_reference in per_all_assignments_f.vendor_employee_number%type
1082: ,p_placement_date_start in per_all_assignments_f.period_of_placement_date_start%type
1083: ,p_grade_ladder in ben_pgm_f.name%type
1084: ,p_subject out nocopy varchar2
1085: ,p_body out nocopy varchar2
1078: ,p_date_probation_end in per_all_assignments_f.date_probation_end%type
1079: ,p_npw_number in per_all_people_f.npw_number%type
1080: ,p_vendor in po_vendors.vendor_name%type
1081: ,p_supplier_reference in per_all_assignments_f.vendor_employee_number%type
1082: ,p_placement_date_start in per_all_assignments_f.period_of_placement_date_start%type
1083: ,p_grade_ladder in ben_pgm_f.name%type
1084: ,p_subject out nocopy varchar2
1085: ,p_body out nocopy varchar2
1086: ) is