[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);
75: --
76: cursor get_assignments(p_person_id number,p_date date) is
77: select asg.assignment_id
78: ,asg.object_version_number
79: from per_all_assignments_f asg
80: where asg.person_id=p_person_id
81: and p_date=asg.effective_start_date;
82: --
83: cursor csr_asg_status(p_assignment_id number, p_date date) is
115: --
116: -- Bug 4755015 Starts
117: cursor get_business_group(p_asg_id number) is
118: select distinct PAAF.business_group_id
119: from per_all_assignments_f PAAF
120: where PAAF.assignment_id=p_asg_id;
121: l_bg_id number;
122: -- Bug 4755015 Ends
123: begin
208: --
209: update per_letter_request_lines
210: set date_from=l_hire_date-1
211: where assignment_id=(select asg2.assignment_id
212: from per_all_assignments_f asg2
213: where asg2.person_id=p_person_id
214: and asg2.assignment_type='A'
215: and asg2.effective_start_date=l_hire_date)
216: and date_from=l_hire_date;
310: close c2;
311: --
312: -- we only need to move the start date, there can be no end dated asg before it.
313: --
314: update per_all_assignments_f
315: set effective_start_date=l_hire_date-1
316: where person_id=p_person_id
317: and assignment_type='A'
318: and effective_start_date=l_hire_date;
374: --
375: update per_letter_request_lines
376: set date_from=l_hire_date-1
377: where assignment_id=(select asg2.assignment_id
378: from per_all_assignments_f asg2
379: where asg2.person_id=p_person_id
380: and asg2.assignment_type='A'
381: and asg2.effective_start_date=l_hire_date)
382: and date_from=l_hire_date;
380: and asg2.assignment_type='A'
381: and asg2.effective_start_date=l_hire_date)
382: and date_from=l_hire_date;
383: --
384: update per_all_assignments_f
385: set effective_start_date=l_hire_date-1
386: where person_id=p_person_id
387: and assignment_type='A'
388: and effective_start_date=l_hire_date;
408: --
409: update per_letter_request_lines
410: set date_from=l_hire_date-1
411: where assignment_id=(select asg2.assignment_id
412: from per_all_assignments_f asg2
413: where asg2.person_id=p_person_id
414: and asg2.assignment_type='A'
415: and asg2.effective_start_date=l_hire_date)
416: and date_from=l_hire_date;
414: and asg2.assignment_type='A'
415: and asg2.effective_start_date=l_hire_date)
416: and date_from=l_hire_date;
417: --
418: update per_all_assignments_f
419: set effective_start_date=l_hire_date-1
420: where person_id=p_person_id
421: and assignment_type='A'
422: and effective_start_date=l_hire_date;
420: where person_id=p_person_id
421: and assignment_type='A'
422: and effective_start_date=l_hire_date;
423: --
424: update per_all_assignments_f
425: set effective_end_date=l_hire_date-2
426: where person_id=p_person_id
427: and assignment_type='A'
428: and effective_end_date=l_hire_date-1;
595: hr_utility.set_location(l_proc,330);
596: --
597: -- the assignment started today, so move it back one day to accept it yesterday
598: --
599: update per_all_assignments_f
600: set effective_start_date=l_hire_date-1
601: where person_id=p_person_id
602: and assignment_type='A'
603: and effective_start_date=l_hire_date;
632: --
633: -- the earlier assignment row starts before yesterday, so we can move the
634: -- recent end date back one day
635: --
636: update per_all_assignments_f
637: set effective_start_date=l_hire_date-1
638: where person_id=p_person_id
639: and assignment_type='A'
640: and effective_start_date=l_hire_date;
638: where person_id=p_person_id
639: and assignment_type='A'
640: and effective_start_date=l_hire_date;
641: --
642: update per_all_assignments_f
643: set effective_end_date=l_hire_date-2
644: where person_id=p_person_id
645: and assignment_type='A'
646: and effective_end_date=l_hire_date-1;
658: hr_utility.set_location(l_proc,385);
659: -- we should make sure that any applications that start on the hire date
660: -- are moved back one day, along with the application
661: begin
662: update per_all_assignments_f
663: set effective_start_date=l_hire_date-1
664: where person_id=p_person_id
665: and assignment_type='A'
666: and effective_start_date=l_hire_date;
749: (p_notification wf_messages.name%type
750: ,p_wf_name wf_item_types.name%type
751: ,p_role varchar2 --wf_roles.name%type Fix for bug 2741492
752: ,p_person_id per_all_people_f.person_id%type
753: ,p_assignment_id per_all_assignments_f.assignment_id%type
754: ,p_effective_date date
755: ,p_hire_date per_periods_of_service.date_start%type
756: ,p_full_name per_all_people_f.full_name%type
757: ,p_per_effective_start_date per_all_people_f.effective_start_date%type
760: ,p_last_name per_all_people_f.last_name%type
761: ,p_employee_number per_all_people_f.employee_number%type
762: ,p_applicant_number per_all_people_f.applicant_number%type
763: ,p_national_identifier per_all_people_f.national_identifier%type
764: ,p_asg_effective_start_date per_all_assignments_f.effective_start_date%type
765: ,p_organization hr_all_organization_units.name%type
766: ,p_grade per_grades.name%type
767: ,p_job per_jobs.name%type
768: ,p_position hr_all_positions_f.name%type
772: ,p_location hr_locations.location_code%type
773: ,p_salary per_pay_proposals.proposed_salary_n%type
774: ,p_salary_currency pay_element_types_f.input_currency_code%type
775: ,p_pay_basis hr_lookups.meaning%type
776: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
777: ,p_npw_number per_all_people_f.npw_number%type
778: ,p_vendor po_vendors.vendor_name%type
779: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
780: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
775: ,p_pay_basis hr_lookups.meaning%type
776: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
777: ,p_npw_number per_all_people_f.npw_number%type
778: ,p_vendor po_vendors.vendor_name%type
779: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
780: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
781: ,p_grade_ladder ben_pgm_f.name%type
782: ) return number is
783: l_nid number;
776: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
777: ,p_npw_number per_all_people_f.npw_number%type
778: ,p_vendor po_vendors.vendor_name%type
779: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
780: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
781: ,p_grade_ladder ben_pgm_f.name%type
782: ) return number is
783: l_nid number;
784: l_ff1 varchar2(240);
939: (p_notification wf_messages.name%type
940: ,p_wf_name wf_item_types.name%type
941: ,p_role varchar2 --wf_roles.name%type Fix for bug 2741492
942: ,p_person_id per_all_people_f.person_id%type
943: ,p_assignment_id per_all_assignments_f.assignment_id%type
944: ,p_effective_date date
945: ,p_hire_date per_periods_of_service.date_start%type
946: ,p_full_name per_all_people_f.full_name%type
947: ,p_per_effective_start_date per_all_people_f.effective_start_date%type
950: ,p_last_name per_all_people_f.last_name%type
951: ,p_employee_number per_all_people_f.employee_number%type
952: ,p_applicant_number per_all_people_f.applicant_number%type
953: ,p_national_identifier per_all_people_f.national_identifier%type
954: ,p_asg_effective_start_date per_all_assignments_f.effective_start_date%type
955: ,p_organization hr_all_organization_units.name%type
956: ,p_grade per_grades.name%type
957: ,p_job per_jobs.name%type
958: ,p_position hr_all_positions_f.name%type
962: ,p_location hr_locations.location_code%type
963: ,p_salary per_pay_proposals.proposed_salary_n%type
964: ,p_salary_currency pay_element_types_f.input_currency_code%type
965: ,p_pay_basis hr_lookups.meaning%type
966: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
967: ,p_npw_number per_all_people_f.npw_number%type
968: ,p_vendor po_vendors.vendor_name%type
969: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
970: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
965: ,p_pay_basis hr_lookups.meaning%type
966: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
967: ,p_npw_number per_all_people_f.npw_number%type
968: ,p_vendor po_vendors.vendor_name%type
969: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
970: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
971: ,p_grade_ladder ben_pgm_f.name%type
972: ) is
973: pragma autonomous_transaction;
966: ,p_date_probation_end per_all_assignments_f.date_probation_end%type
967: ,p_npw_number per_all_people_f.npw_number%type
968: ,p_vendor po_vendors.vendor_name%type
969: ,p_supplier_reference per_all_assignments_f.vendor_employee_number%type
970: ,p_placement_date_start per_all_assignments_f.period_of_placement_date_start%type
971: ,p_grade_ladder ben_pgm_f.name%type
972: ) is
973: pragma autonomous_transaction;
974: l_nid number;
1026: (p_notification in wf_messages.name%type
1027: ,p_wf_name in wf_item_types.name%type
1028: ,p_role in varchar2 --wf_roles.name%type Fix for bug 2741492
1029: ,p_person_id in per_all_people_f.person_id%type
1030: ,p_assignment_id in per_all_assignments_f.assignment_id%type
1031: ,p_effective_date in date
1032: ,p_hire_date in per_periods_of_service.date_start%type
1033: ,p_full_name in per_all_people_f.full_name%type
1034: ,p_per_effective_start_date in per_all_people_f.effective_start_date%type
1037: ,p_last_name in per_all_people_f.last_name%type
1038: ,p_employee_number in per_all_people_f.employee_number%type
1039: ,p_applicant_number in per_all_people_f.applicant_number%type
1040: ,p_national_identifier in per_all_people_f.national_identifier%type
1041: ,p_asg_effective_start_date in per_all_assignments_f.effective_start_date%type
1042: ,p_organization in hr_all_organization_units.name%type
1043: ,p_grade in per_grades.name%type
1044: ,p_job in per_jobs.name%type
1045: ,p_position in hr_all_positions_f.name%type
1049: ,p_location in hr_locations.location_code%type
1050: ,p_salary in per_pay_proposals.proposed_salary_n%type
1051: ,p_salary_currency in pay_element_types_f.input_currency_code%type
1052: ,p_pay_basis in hr_lookups.meaning%type
1053: ,p_date_probation_end in per_all_assignments_f.date_probation_end%type
1054: ,p_npw_number in per_all_people_f.npw_number%type
1055: ,p_vendor in po_vendors.vendor_name%type
1056: ,p_supplier_reference in per_all_assignments_f.vendor_employee_number%type
1057: ,p_placement_date_start in per_all_assignments_f.period_of_placement_date_start%type
1052: ,p_pay_basis in hr_lookups.meaning%type
1053: ,p_date_probation_end in per_all_assignments_f.date_probation_end%type
1054: ,p_npw_number in per_all_people_f.npw_number%type
1055: ,p_vendor in po_vendors.vendor_name%type
1056: ,p_supplier_reference in per_all_assignments_f.vendor_employee_number%type
1057: ,p_placement_date_start in per_all_assignments_f.period_of_placement_date_start%type
1058: ,p_grade_ladder in ben_pgm_f.name%type
1059: ,p_subject out nocopy varchar2
1060: ,p_body out nocopy varchar2
1053: ,p_date_probation_end in per_all_assignments_f.date_probation_end%type
1054: ,p_npw_number in per_all_people_f.npw_number%type
1055: ,p_vendor in po_vendors.vendor_name%type
1056: ,p_supplier_reference in per_all_assignments_f.vendor_employee_number%type
1057: ,p_placement_date_start in per_all_assignments_f.period_of_placement_date_start%type
1058: ,p_grade_ladder in ben_pgm_f.name%type
1059: ,p_subject out nocopy varchar2
1060: ,p_body out nocopy varchar2
1061: ) is