[Home] [Help]
152: ,per.dpdnt_vlntry_svce_flag
153: ,per.comment_id
154: ,hc.comment_text
155: ,pos.adjusted_svc_date
156: FROM per_all_people_f per
157: ,hr_comments hc
158: ,per_periods_of_service pos
159: WHERE per.person_id = p_person_id
160: AND p_eff_date BETWEEN per.effective_start_date and per.effective_end_date
173: IS
174: SELECT per.current_applicant_flag,
175: per.current_employee_flag,
176: per.current_npw_flag
177: FROM per_all_people_f per
178: WHERE per.person_id = p_person_id
179: AND p_eff_date BETWEEN per.effective_start_date and per.effective_end_date;
180:
181: --
238: -- ------------------------------------------------------------------------
239: -- -------------------------
240: -- ------------------------------------------------------------------------
241: -- Purpose: This procedure retrieves the max effective start date from
242: -- per_all_people_f for the person_id passed in.
243: -- ------------------------------------------------------------------------
244: Function get_max_effective_date(p_person_id in number)
245: return Date is
246: --
250: Begin
251: --
252: hr_utility.set_location('Entering:'||l_proc, 5);
253: select max(EFFECTIVE_START_DATE) into l_effective_date
254: from per_all_people_f where person_id = p_person_id;
255: hr_utility.set_location('Exiting:'||l_proc,10);
256: --
257: return l_effective_date;
258: --
765: ,p_review_proc_call out nocopy varchar2
766: ,p_action_type out nocopy varchar2
767: )is
768:
769: l_title per_all_people_f.title%type default null;
770: l_marital_status per_all_people_f.marital_status%type
771: default null;
772: l_title_meaning hr_lookups.meaning%type default null;
773: l_marital_status_meaning hr_lookups.meaning%type default null;
766: ,p_action_type out nocopy varchar2
767: )is
768:
769: l_title per_all_people_f.title%type default null;
770: l_marital_status per_all_people_f.marital_status%type
771: default null;
772: l_title_meaning hr_lookups.meaning%type default null;
773: l_marital_status_meaning hr_lookups.meaning%type default null;
774: l_proc varchar2(72) := g_package||'get_person_data_from_tt';
1681:
1682: l_name_combination_warning boolean default false;
1683: l_assign_payroll_warning boolean default false;
1684: l_orig_hire_warning boolean default false;
1685: l_employee_number per_all_people_f.employee_number%type
1686: default null;
1687: l_effective_start_date date default null;
1688: l_effective_end_date date default null;
1689: l_full_name per_all_people_f.full_name%type default null;
1685: l_employee_number per_all_people_f.employee_number%type
1686: default null;
1687: l_effective_start_date date default null;
1688: l_effective_end_date date default null;
1689: l_full_name per_all_people_f.full_name%type default null;
1690: l_comment_id per_all_people_f.comment_id%type default null;
1691: l_basic_details_changed boolean default null;
1692: l_full_name_duplicate_flag varchar2(1) default null;
1693: l_vendor_id number default null;
1686: default null;
1687: l_effective_start_date date default null;
1688: l_effective_end_date date default null;
1689: l_full_name per_all_people_f.full_name%type default null;
1690: l_comment_id per_all_people_f.comment_id%type default null;
1691: l_basic_details_changed boolean default null;
1692: l_full_name_duplicate_flag varchar2(1) default null;
1693: l_vendor_id number default null;
1694: l_benefit_group_id number default null;
1703: l_per_effective_end_date date;
1704: l_appl_assignment_id number;
1705: l_unaccepted_asg_del_warning boolean;
1706: l_per_assign_payroll_warning boolean;
1707: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1708: l_current_employee_flag per_all_people_f.current_employee_flag%type;
1709: l_current_npw_flag per_all_people_f.current_npw_flag%type;
1710: l_person_type_id per_all_people_f.person_type_id%type;
1711: l_proc varchar2(72) := g_package||'update_person';
1704: l_appl_assignment_id number;
1705: l_unaccepted_asg_del_warning boolean;
1706: l_per_assign_payroll_warning boolean;
1707: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1708: l_current_employee_flag per_all_people_f.current_employee_flag%type;
1709: l_current_npw_flag per_all_people_f.current_npw_flag%type;
1710: l_person_type_id per_all_people_f.person_type_id%type;
1711: l_proc varchar2(72) := g_package||'update_person';
1712: l_comments varchar2(100) := hr_api.g_varchar2;
1705: l_unaccepted_asg_del_warning boolean;
1706: l_per_assign_payroll_warning boolean;
1707: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1708: l_current_employee_flag per_all_people_f.current_employee_flag%type;
1709: l_current_npw_flag per_all_people_f.current_npw_flag%type;
1710: l_person_type_id per_all_people_f.person_type_id%type;
1711: l_proc varchar2(72) := g_package||'update_person';
1712: l_comments varchar2(100) := hr_api.g_varchar2;
1713:
1706: l_per_assign_payroll_warning boolean;
1707: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
1708: l_current_employee_flag per_all_people_f.current_employee_flag%type;
1709: l_current_npw_flag per_all_people_f.current_npw_flag%type;
1710: l_person_type_id per_all_people_f.person_type_id%type;
1711: l_proc varchar2(72) := g_package||'update_person';
1712: l_comments varchar2(100) := hr_api.g_varchar2;
1713:
1714: BEGIN
1855: end if;
1856:
1857: ---------------------------------------------------------------------------
1858: -- Bug 1937643 Fix Begins - 08/04/2002
1859: -- With the PTU model, the per_all_people_f.person_type_id stores only the
1860: -- default user flavor of the system_person_type. The true user flavor
1861: -- for the system_person_type is stored in per_person_type_usages_f table.
1862: -- Since the current Personal Information Basic Details region and the
1863: -- New Hire page does not allow a user to choose any user flavor, so we'll
1864: -- zap the p_person_type_id to hr_api.g_number value when it is not an
1865: -- applicant when calling the hr_person_api.update_person. That way, the api
1866: -- will understand that the person_type is not changed and will not update
1867: -- the person_type_id in per_person_type_usages_f table as is. If we pass
1868: -- the per_all_people_f.person_type_id to the api, the person_type_id in
1869: -- per_person_type_usages_f table will be updated with that value which will
1870: -- overwrite the true user flavor of the system person type with the
1871: -- default user flavor person type. This may not be desirable.
1872: -- When we allow a user to select user flavors of person type in New Hire
3337:
3338: CURSOR get_current_rec(
3339: p_eff_date in date default trunc(sysdate)) is
3340: SELECT effective_start_date, object_version_number
3341: FROM per_all_people_f
3342: WHERE person_id = p_person_id
3343: AND p_eff_date between effective_start_date
3344: and effective_end_date;
3345:
3347: l_name_combination_warning boolean default false;
3348: l_assign_payroll_warning boolean default false;
3349: l_orig_hire_warning boolean default false;
3350: l_ovn number default null;
3351: l_employee_number per_all_people_f.employee_number%type
3352: default hr_api.g_varchar2;
3353: l_effective_start_date date default null;
3354: l_effective_end_date date default null;
3355: l_full_name per_all_people_f.full_name%type default null;
3351: l_employee_number per_all_people_f.employee_number%type
3352: default hr_api.g_varchar2;
3353: l_effective_start_date date default null;
3354: l_effective_end_date date default null;
3355: l_full_name per_all_people_f.full_name%type default null;
3356: l_comment_id per_all_people_f.comment_id%type default null;
3357: l_message_number VARCHAR2(10);
3358: l_proc varchar2(72) := g_package||'validate_basic_details';
3359: --
3352: default hr_api.g_varchar2;
3353: l_effective_start_date date default null;
3354: l_effective_end_date date default null;
3355: l_full_name per_all_people_f.full_name%type default null;
3356: l_comment_id per_all_people_f.comment_id%type default null;
3357: l_message_number VARCHAR2(10);
3358: l_proc varchar2(72) := g_package||'validate_basic_details';
3359: --
3360: --
5161: IS
5162: --
5163: l_effective_start_date date default null;
5164: l_effective_end_date date default null;
5165: l_full_name per_all_people_f.full_name%type;
5166: l_comment_id per_all_people_f.comment_id%type;
5167: l_name_combination_warning boolean default null;
5168: l_assign_payroll_warning boolean default null;
5169: l_orig_hire_warning boolean default null;
5162: --
5163: l_effective_start_date date default null;
5164: l_effective_end_date date default null;
5165: l_full_name per_all_people_f.full_name%type;
5166: l_comment_id per_all_people_f.comment_id%type;
5167: l_name_combination_warning boolean default null;
5168: l_assign_payroll_warning boolean default null;
5169: l_orig_hire_warning boolean default null;
5170: l_employee_number per_all_people_f.employee_number%type
5166: l_comment_id per_all_people_f.comment_id%type;
5167: l_name_combination_warning boolean default null;
5168: l_assign_payroll_warning boolean default null;
5169: l_orig_hire_warning boolean default null;
5170: l_employee_number per_all_people_f.employee_number%type
5171: := hr_api.g_varchar2;
5172: l_npw_number per_all_people_f.npw_number%type
5173: := hr_api.g_varchar2;
5174: l_ovn number default null;
5168: l_assign_payroll_warning boolean default null;
5169: l_orig_hire_warning boolean default null;
5170: l_employee_number per_all_people_f.employee_number%type
5171: := hr_api.g_varchar2;
5172: l_npw_number per_all_people_f.npw_number%type
5173: := hr_api.g_varchar2;
5174: l_ovn number default null;
5175: l_person_id per_all_people_f.person_id%type
5176: default null;
5171: := hr_api.g_varchar2;
5172: l_npw_number per_all_people_f.npw_number%type
5173: := hr_api.g_varchar2;
5174: l_ovn number default null;
5175: l_person_id per_all_people_f.person_id%type
5176: default null;
5177:
5178: --Start Registration
5179:
5225: l_per_effective_start_date date;
5226: l_per_effective_end_date date;
5227: l_unaccepted_asg_del_warning boolean;
5228: l_per_assign_payroll_warning boolean;
5229: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
5230: l_current_employee_flag per_all_people_f.current_employee_flag%type;
5231: l_current_npw_flag per_all_people_f.current_npw_flag%type;
5232: l_effective_date date;
5233: l_appl_assignment_id number;
5226: l_per_effective_end_date date;
5227: l_unaccepted_asg_del_warning boolean;
5228: l_per_assign_payroll_warning boolean;
5229: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
5230: l_current_employee_flag per_all_people_f.current_employee_flag%type;
5231: l_current_npw_flag per_all_people_f.current_npw_flag%type;
5232: l_effective_date date;
5233: l_appl_assignment_id number;
5234: l_person_type_id per_all_people_f.person_type_id%type;
5227: l_unaccepted_asg_del_warning boolean;
5228: l_per_assign_payroll_warning boolean;
5229: l_current_applicant_flag per_all_people_f.current_applicant_flag%type;
5230: l_current_employee_flag per_all_people_f.current_employee_flag%type;
5231: l_current_npw_flag per_all_people_f.current_npw_flag%type;
5232: l_effective_date date;
5233: l_appl_assignment_id number;
5234: l_person_type_id per_all_people_f.person_type_id%type;
5235: l_sys_person_type per_person_types.system_person_type%type;
5230: l_current_employee_flag per_all_people_f.current_employee_flag%type;
5231: l_current_npw_flag per_all_people_f.current_npw_flag%type;
5232: l_effective_date date;
5233: l_appl_assignment_id number;
5234: l_person_type_id per_all_people_f.person_type_id%type;
5235: l_sys_person_type per_person_types.system_person_type%type;
5236: l_proc varchar2(72) := g_package||'process_api';
5237: l_business_grp_Id number;
5238: login_person_id number;
5423: hr_person_info_util_ss.create_ignore_df_validation('PER_PERIODS_OF_SERVICE');
5424:
5425: ---------------------------------------------------------------------------
5426: -- Bug 1937643 Fix Begins - 08/04/2002
5427: -- With the PTU model, the per_all_people_f.person_type_id stores only the
5428: -- default user flavor of the system_person_type. The true user flavor
5429: -- for the system_person_type is stored in per_person_type_usages_f table.
5430: -- Since the current Personal Information Basic Details region and the
5431: -- New Hire page does not allow a user to choose any user flavor, so we'll
5432: -- zap the p_person_type_id to hr_api.g_number value when it is not an
5433: -- applicant when calling the hr_person_api.update_person. That way, the api
5434: -- will understand that the person_type is not changed and will not update
5435: -- the person_type_id in per_person_type_usages_f table as is. If we pass
5436: -- the per_all_people_f.person_type_id to the api, the person_type_id in
5437: -- per_person_type_usages_f table will be updated with that value which will
5438: -- overwrite the true user flavor of the system person type with the
5439: -- default user flavor person type. This may not be desirable.
5440: -- When we allow a user to select user flavors of person type in New Hire
7224: l_result varchar2(50);
7225: l_per_ovn number default null;
7226: l_pdp_ovn number default null;
7227: -- bug# 2693580 : changing l_employee_number from number to varchar2
7228: l_employee_number per_all_people_f.employee_number%type default null;
7229: l_npw_number per_all_people_f.npw_number%type default null;
7230: l_asg_ovn number default null;
7231: l_full_name per_all_people_f.full_name%type default null;
7232: l_person_id number;
7225: l_per_ovn number default null;
7226: l_pdp_ovn number default null;
7227: -- bug# 2693580 : changing l_employee_number from number to varchar2
7228: l_employee_number per_all_people_f.employee_number%type default null;
7229: l_npw_number per_all_people_f.npw_number%type default null;
7230: l_asg_ovn number default null;
7231: l_full_name per_all_people_f.full_name%type default null;
7232: l_person_id number;
7233: l_assignment_id number;
7227: -- bug# 2693580 : changing l_employee_number from number to varchar2
7228: l_employee_number per_all_people_f.employee_number%type default null;
7229: l_npw_number per_all_people_f.npw_number%type default null;
7230: l_asg_ovn number default null;
7231: l_full_name per_all_people_f.full_name%type default null;
7232: l_person_id number;
7233: l_assignment_id number;
7234: l_per_effective_start_date date;
7235: l_asg_effective_end_date date;
8789: ,p_is_payroll_upd out nocopy boolean
8790: ) is
8791:
8792: l_address_line1 per_addresses.address_line1%type;
8793: l_date_of_birth per_all_people_f.date_of_birth%type;
8794: l_legislation_code per_business_groups.legislation_code%TYPE;
8795:
8796: cursor csr_get_person_dob is
8797: select date_of_birth from per_people_f