[Home] [Help]
158: ,per.dpdnt_vlntry_svce_flag
159: ,per.comment_id
160: ,hc.comment_text
161: ,pos.adjusted_svc_date
162: FROM per_all_people_f per
163: ,hr_comments hc
164: ,per_periods_of_service pos
165: WHERE per.person_id = p_person_id
166: AND p_eff_date BETWEEN per.effective_start_date and per.effective_end_date
179: IS
180: SELECT per.current_applicant_flag,
181: per.current_employee_flag,
182: per.current_npw_flag
183: FROM per_all_people_f per
184: WHERE per.person_id = p_person_id
185: AND p_eff_date BETWEEN per.effective_start_date and per.effective_end_date;
186:
187: --
244: -- ------------------------------------------------------------------------
245: -- -------------------------
246: -- ------------------------------------------------------------------------
247: -- Purpose: This procedure retrieves the max effective start date from
248: -- per_all_people_f for the person_id passed in.
249: -- ------------------------------------------------------------------------
250: Function get_max_effective_date(p_person_id in number)
251: return Date is
252: --
256: Begin
257: --
258: hr_utility.set_location('Entering:'||l_proc, 5);
259: select max(EFFECTIVE_START_DATE) into l_effective_date
260: from per_all_people_f where person_id = p_person_id;
261: hr_utility.set_location('Exiting:'||l_proc,10);
262: --
263: return l_effective_date;
264: --
771: ,p_review_proc_call out nocopy varchar2
772: ,p_action_type out nocopy varchar2
773: )is
774:
775: l_title per_all_people_f.title%type default null;
776: l_marital_status per_all_people_f.marital_status%type
777: default null;
778: l_title_meaning hr_lookups.meaning%type default null;
779: l_marital_status_meaning hr_lookups.meaning%type default null;
772: ,p_action_type out nocopy varchar2
773: )is
774:
775: l_title per_all_people_f.title%type default null;
776: l_marital_status per_all_people_f.marital_status%type
777: default null;
778: l_title_meaning hr_lookups.meaning%type default null;
779: l_marital_status_meaning hr_lookups.meaning%type default null;
780: l_proc varchar2(72) := g_package||'get_person_data_from_tt';
1891:
1892: l_name_combination_warning boolean default false;
1893: l_assign_payroll_warning boolean default false;
1894: l_orig_hire_warning boolean default false;
1895: l_employee_number per_all_people_f.employee_number%type
1896: default null;
1897: l_effective_start_date date default null;
1898: l_effective_end_date date default null;
1899: l_full_name per_all_people_f.full_name%type default null;
1895: l_employee_number per_all_people_f.employee_number%type
1896: default null;
1897: l_effective_start_date date default null;
1898: l_effective_end_date date default null;
1899: l_full_name per_all_people_f.full_name%type default null;
1900: l_comment_id per_all_people_f.comment_id%type default null;
1901: l_basic_details_changed boolean default null;
1902: l_full_name_duplicate_flag varchar2(1) default null;
1903: l_vendor_id number default null;
1896: default null;
1897: l_effective_start_date date default null;
1898: l_effective_end_date date default null;
1899: l_full_name per_all_people_f.full_name%type default null;
1900: l_comment_id per_all_people_f.comment_id%type default null;
1901: l_basic_details_changed boolean default null;
1902: l_full_name_duplicate_flag varchar2(1) default null;
1903: l_vendor_id number default null;
1904: l_benefit_group_id number default null;
1913: l_per_effective_end_date date;
1914: l_appl_assignment_id number;
1915: l_unaccepted_asg_del_warning boolean;
1916: l_per_assign_payroll_warning boolean;
1917: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1918: l_current_employee_flag per_all_people_f.current_employee_flag%type;
1919: l_current_npw_flag per_all_people_f.current_npw_flag%type;
1920: l_person_type_id per_all_people_f.person_type_id%type;
1921: l_proc varchar2(72) := g_package||'update_person';
1914: l_appl_assignment_id number;
1915: l_unaccepted_asg_del_warning boolean;
1916: l_per_assign_payroll_warning boolean;
1917: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1918: l_current_employee_flag per_all_people_f.current_employee_flag%type;
1919: l_current_npw_flag per_all_people_f.current_npw_flag%type;
1920: l_person_type_id per_all_people_f.person_type_id%type;
1921: l_proc varchar2(72) := g_package||'update_person';
1922: l_comments varchar2(100) := hr_api.g_varchar2;
1915: l_unaccepted_asg_del_warning boolean;
1916: l_per_assign_payroll_warning boolean;
1917: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1918: l_current_employee_flag per_all_people_f.current_employee_flag%type;
1919: l_current_npw_flag per_all_people_f.current_npw_flag%type;
1920: l_person_type_id per_all_people_f.person_type_id%type;
1921: l_proc varchar2(72) := g_package||'update_person';
1922: l_comments varchar2(100) := hr_api.g_varchar2;
1923: l_eth_dff_exists varchar2(30):= null;
1916: l_per_assign_payroll_warning boolean;
1917: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1918: l_current_employee_flag per_all_people_f.current_employee_flag%type;
1919: l_current_npw_flag per_all_people_f.current_npw_flag%type;
1920: l_person_type_id per_all_people_f.person_type_id%type;
1921: l_proc varchar2(72) := g_package||'update_person';
1922: l_comments varchar2(100) := hr_api.g_varchar2;
1923: l_eth_dff_exists varchar2(30):= null;
1924:
2112: end if;
2113:
2114: ---------------------------------------------------------------------------
2115: -- Bug 1937643 Fix Begins - 08/04/2002
2116: -- With the PTU model, the per_all_people_f.person_type_id stores only the
2117: -- default user flavor of the system_person_type. The true user flavor
2118: -- for the system_person_type is stored in per_person_type_usages_f table.
2119: -- Since the current Personal Information Basic Details region and the
2120: -- New Hire page does not allow a user to choose any user flavor, so we'll
2121: -- zap the p_person_type_id to hr_api.g_number value when it is not an
2122: -- applicant when calling the hr_person_api.update_person. That way, the api
2123: -- will understand that the person_type is not changed and will not update
2124: -- the person_type_id in per_person_type_usages_f table as is. If we pass
2125: -- the per_all_people_f.person_type_id to the api, the person_type_id in
2126: -- per_person_type_usages_f table will be updated with that value which will
2127: -- overwrite the true user flavor of the system person type with the
2128: -- default user flavor person type. This may not be desirable.
2129: -- When we allow a user to select user flavors of person type in New Hire
3713:
3714: CURSOR get_current_rec(
3715: p_eff_date in date default trunc(sysdate)) is
3716: SELECT effective_start_date, object_version_number
3717: FROM per_all_people_f
3718: WHERE person_id = p_person_id
3719: AND p_eff_date between effective_start_date
3720: and effective_end_date;
3721:
3723: l_name_combination_warning boolean default false;
3724: l_assign_payroll_warning boolean default false;
3725: l_orig_hire_warning boolean default false;
3726: l_ovn number default null;
3727: l_employee_number per_all_people_f.employee_number%type
3728: default hr_api.g_varchar2;
3729: l_effective_start_date date default null;
3730: l_effective_end_date date default null;
3731: l_full_name per_all_people_f.full_name%type default null;
3727: l_employee_number per_all_people_f.employee_number%type
3728: default hr_api.g_varchar2;
3729: l_effective_start_date date default null;
3730: l_effective_end_date date default null;
3731: l_full_name per_all_people_f.full_name%type default null;
3732: l_comment_id per_all_people_f.comment_id%type default null;
3733: l_message_number VARCHAR2(10);
3734: l_proc varchar2(72) := g_package||'validate_basic_details';
3735: --
3728: default hr_api.g_varchar2;
3729: l_effective_start_date date default null;
3730: l_effective_end_date date default null;
3731: l_full_name per_all_people_f.full_name%type default null;
3732: l_comment_id per_all_people_f.comment_id%type default null;
3733: l_message_number VARCHAR2(10);
3734: l_proc varchar2(72) := g_package||'validate_basic_details';
3735: --
3736: --
5537: IS
5538: --
5539: l_effective_start_date date default null;
5540: l_effective_end_date date default null;
5541: l_full_name per_all_people_f.full_name%type;
5542: l_comment_id per_all_people_f.comment_id%type;
5543: l_name_combination_warning boolean default null;
5544: l_assign_payroll_warning boolean default null;
5545: l_orig_hire_warning boolean default null;
5538: --
5539: l_effective_start_date date default null;
5540: l_effective_end_date date default null;
5541: l_full_name per_all_people_f.full_name%type;
5542: l_comment_id per_all_people_f.comment_id%type;
5543: l_name_combination_warning boolean default null;
5544: l_assign_payroll_warning boolean default null;
5545: l_orig_hire_warning boolean default null;
5546: l_employee_number per_all_people_f.employee_number%type
5542: l_comment_id per_all_people_f.comment_id%type;
5543: l_name_combination_warning boolean default null;
5544: l_assign_payroll_warning boolean default null;
5545: l_orig_hire_warning boolean default null;
5546: l_employee_number per_all_people_f.employee_number%type
5547: := hr_api.g_varchar2;
5548: l_npw_number per_all_people_f.npw_number%type
5549: := hr_api.g_varchar2;
5550: l_ovn number default null;
5544: l_assign_payroll_warning boolean default null;
5545: l_orig_hire_warning boolean default null;
5546: l_employee_number per_all_people_f.employee_number%type
5547: := hr_api.g_varchar2;
5548: l_npw_number per_all_people_f.npw_number%type
5549: := hr_api.g_varchar2;
5550: l_ovn number default null;
5551: l_person_id per_all_people_f.person_id%type
5552: default null;
5547: := hr_api.g_varchar2;
5548: l_npw_number per_all_people_f.npw_number%type
5549: := hr_api.g_varchar2;
5550: l_ovn number default null;
5551: l_person_id per_all_people_f.person_id%type
5552: default null;
5553:
5554: --Start Registration
5555:
5601: l_per_effective_start_date date;
5602: l_per_effective_end_date date;
5603: l_unaccepted_asg_del_warning boolean;
5604: l_per_assign_payroll_warning boolean;
5605: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
5606: l_current_employee_flag per_all_people_f.current_employee_flag%type;
5607: l_current_npw_flag per_all_people_f.current_npw_flag%type;
5608: l_effective_date date;
5609: l_appl_assignment_id number;
5602: l_per_effective_end_date date;
5603: l_unaccepted_asg_del_warning boolean;
5604: l_per_assign_payroll_warning boolean;
5605: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
5606: l_current_employee_flag per_all_people_f.current_employee_flag%type;
5607: l_current_npw_flag per_all_people_f.current_npw_flag%type;
5608: l_effective_date date;
5609: l_appl_assignment_id number;
5610: l_person_type_id per_all_people_f.person_type_id%type;
5603: l_unaccepted_asg_del_warning boolean;
5604: l_per_assign_payroll_warning boolean;
5605: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
5606: l_current_employee_flag per_all_people_f.current_employee_flag%type;
5607: l_current_npw_flag per_all_people_f.current_npw_flag%type;
5608: l_effective_date date;
5609: l_appl_assignment_id number;
5610: l_person_type_id per_all_people_f.person_type_id%type;
5611: l_sys_person_type per_person_types.system_person_type%type;
5606: l_current_employee_flag per_all_people_f.current_employee_flag%type;
5607: l_current_npw_flag per_all_people_f.current_npw_flag%type;
5608: l_effective_date date;
5609: l_appl_assignment_id number;
5610: l_person_type_id per_all_people_f.person_type_id%type;
5611: l_sys_person_type per_person_types.system_person_type%type;
5612: l_proc varchar2(72) := g_package||'process_api';
5613: l_business_grp_Id number;
5614: login_person_id number;
5980: hr_person_info_util_ss.create_ignore_df_validation('PER_PERIODS_OF_SERVICE');
5981:
5982: ---------------------------------------------------------------------------
5983: -- Bug 1937643 Fix Begins - 08/04/2002
5984: -- With the PTU model, the per_all_people_f.person_type_id stores only the
5985: -- default user flavor of the system_person_type. The true user flavor
5986: -- for the system_person_type is stored in per_person_type_usages_f table.
5987: -- Since the current Personal Information Basic Details region and the
5988: -- New Hire page does not allow a user to choose any user flavor, so we'll
5989: -- zap the p_person_type_id to hr_api.g_number value when it is not an
5990: -- applicant when calling the hr_person_api.update_person. That way, the api
5991: -- will understand that the person_type is not changed and will not update
5992: -- the person_type_id in per_person_type_usages_f table as is. If we pass
5993: -- the per_all_people_f.person_type_id to the api, the person_type_id in
5994: -- per_person_type_usages_f table will be updated with that value which will
5995: -- overwrite the true user flavor of the system person type with the
5996: -- default user flavor person type. This may not be desirable.
5997: -- When we allow a user to select user flavors of person type in New Hire
7796: l_result varchar2(50);
7797: l_per_ovn number default null;
7798: l_pdp_ovn number default null;
7799: -- bug# 2693580 : changing l_employee_number from number to varchar2
7800: l_employee_number per_all_people_f.employee_number%type default null;
7801: l_npw_number per_all_people_f.npw_number%type default null;
7802: l_asg_ovn number default null;
7803: l_full_name per_all_people_f.full_name%type default null;
7804: l_person_id number;
7797: l_per_ovn number default null;
7798: l_pdp_ovn number default null;
7799: -- bug# 2693580 : changing l_employee_number from number to varchar2
7800: l_employee_number per_all_people_f.employee_number%type default null;
7801: l_npw_number per_all_people_f.npw_number%type default null;
7802: l_asg_ovn number default null;
7803: l_full_name per_all_people_f.full_name%type default null;
7804: l_person_id number;
7805: l_assignment_id number;
7799: -- bug# 2693580 : changing l_employee_number from number to varchar2
7800: l_employee_number per_all_people_f.employee_number%type default null;
7801: l_npw_number per_all_people_f.npw_number%type default null;
7802: l_asg_ovn number default null;
7803: l_full_name per_all_people_f.full_name%type default null;
7804: l_person_id number;
7805: l_assignment_id number;
7806: l_per_effective_start_date date;
7807: l_asg_effective_end_date date;
9368: ,p_is_payroll_upd out nocopy boolean
9369: ) is
9370:
9371: l_address_line1 per_addresses.address_line1%type;
9372: l_date_of_birth per_all_people_f.date_of_birth%type;
9373: l_legislation_code per_business_groups.legislation_code%TYPE;
9374: l_leg_code per_business_groups.legislation_code%TYPE;
9375:
9376: cursor csr_get_person_dob is