[Home] [Help]
15: g_per_rec per_all_people_f%rowtype;
16: g_scl_rec hr_soft_coding_keyflex%rowtype;
17: g_add_rec per_addresses%rowtype;
18: g_grp_rec pay_people_groups%rowtype;
19: g_asg_rec per_all_assignments_f%rowtype;
20: g_phn_rec per_phones%rowtype;
21: g_cnt_rec per_contact_relationships%rowtype;
22: g_interface_code varchar2(150);
23:
48: type ref_cur_typ is ref cursor;
49:
50: type t_hrempapi is record
51: (person_id per_all_people_f.person_id%type
52: ,assignment_id per_all_assignments_f.assignment_id%type
53: ,per_object_version_number per_all_people_f.object_version_number%type
54: ,asg_object_version_number per_all_assignments_f.object_version_number%type
55: ,per_effective_start_date date
56: ,per_effective_end_date date
50: type t_hrempapi is record
51: (person_id per_all_people_f.person_id%type
52: ,assignment_id per_all_assignments_f.assignment_id%type
53: ,per_object_version_number per_all_people_f.object_version_number%type
54: ,asg_object_version_number per_all_assignments_f.object_version_number%type
55: ,per_effective_start_date date
56: ,per_effective_end_date date
57: ,full_name per_all_people_f.full_name%type
58: ,per_comment_id per_all_people_f.comment_id%type
55: ,per_effective_start_date date
56: ,per_effective_end_date date
57: ,full_name per_all_people_f.full_name%type
58: ,per_comment_id per_all_people_f.comment_id%type
59: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
60: ,assignment_number per_all_assignments_f.assignment_number%type
61: ,pdp_object_version_number per_all_people_f.object_version_number%type
62: ,name_combination_warning boolean
63: ,assign_payroll_warning boolean
56: ,per_effective_end_date date
57: ,full_name per_all_people_f.full_name%type
58: ,per_comment_id per_all_people_f.comment_id%type
59: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
60: ,assignment_number per_all_assignments_f.assignment_number%type
61: ,pdp_object_version_number per_all_people_f.object_version_number%type
62: ,name_combination_warning boolean
63: ,assign_payroll_warning boolean
64: ,orig_hire_warning boolean
64: ,orig_hire_warning boolean
65: );
66:
67: type t_asgupdcrit_api is record
68: (asg_object_version_number per_all_assignments_f.object_version_number%type
69: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
70: ,people_group_id per_all_assignments_f.people_group_id%type
71: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
72: ,group_name pay_people_groups.group_name%type
65: );
66:
67: type t_asgupdcrit_api is record
68: (asg_object_version_number per_all_assignments_f.object_version_number%type
69: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
70: ,people_group_id per_all_assignments_f.people_group_id%type
71: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
72: ,group_name pay_people_groups.group_name%type
73: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
66:
67: type t_asgupdcrit_api is record
68: (asg_object_version_number per_all_assignments_f.object_version_number%type
69: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
70: ,people_group_id per_all_assignments_f.people_group_id%type
71: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
72: ,group_name pay_people_groups.group_name%type
73: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
74: ,asg_effective_end_date per_all_assignments_f.effective_end_date%type
67: type t_asgupdcrit_api is record
68: (asg_object_version_number per_all_assignments_f.object_version_number%type
69: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
70: ,people_group_id per_all_assignments_f.people_group_id%type
71: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
72: ,group_name pay_people_groups.group_name%type
73: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
74: ,asg_effective_end_date per_all_assignments_f.effective_end_date%type
75: ,org_now_no_manager_warning boolean
69: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
70: ,people_group_id per_all_assignments_f.people_group_id%type
71: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
72: ,group_name pay_people_groups.group_name%type
73: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
74: ,asg_effective_end_date per_all_assignments_f.effective_end_date%type
75: ,org_now_no_manager_warning boolean
76: ,other_manager_warning boolean
77: ,spp_delete_warning boolean
70: ,people_group_id per_all_assignments_f.people_group_id%type
71: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
72: ,group_name pay_people_groups.group_name%type
73: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
74: ,asg_effective_end_date per_all_assignments_f.effective_end_date%type
75: ,org_now_no_manager_warning boolean
76: ,other_manager_warning boolean
77: ,spp_delete_warning boolean
78: ,entries_changed_warning varchar2(50)
78: ,entries_changed_warning varchar2(50)
79: ,tax_district_changed_warning boolean
80: ,concatenated_segments hr_soft_coding_keyflex.concatenated_segments%type
81: ,gsp_post_process_warning varchar2(2000)
82: ,comment_id per_all_assignments_f.comment_id%type
83: );
84:
85: type t_upd_emp_asg_api is record
86: (cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%type
82: ,comment_id per_all_assignments_f.comment_id%type
83: );
84:
85: type t_upd_emp_asg_api is record
86: (cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%type
87: ,cagr_concatenated_segments varchar2(2000)
88: ,concatenated_segments varchar2(2000)
89: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
90: ,comment_id per_all_assignments_f.comment_id%type
85: type t_upd_emp_asg_api is record
86: (cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%type
87: ,cagr_concatenated_segments varchar2(2000)
88: ,concatenated_segments varchar2(2000)
89: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
90: ,comment_id per_all_assignments_f.comment_id%type
91: ,effective_start_date per_all_assignments_f.effective_start_date%type
92: ,effective_end_date per_all_assignments_f.effective_end_date%type
93: ,no_managers_warning boolean
86: (cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%type
87: ,cagr_concatenated_segments varchar2(2000)
88: ,concatenated_segments varchar2(2000)
89: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
90: ,comment_id per_all_assignments_f.comment_id%type
91: ,effective_start_date per_all_assignments_f.effective_start_date%type
92: ,effective_end_date per_all_assignments_f.effective_end_date%type
93: ,no_managers_warning boolean
94: ,other_manager_warning boolean
87: ,cagr_concatenated_segments varchar2(2000)
88: ,concatenated_segments varchar2(2000)
89: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
90: ,comment_id per_all_assignments_f.comment_id%type
91: ,effective_start_date per_all_assignments_f.effective_start_date%type
92: ,effective_end_date per_all_assignments_f.effective_end_date%type
93: ,no_managers_warning boolean
94: ,other_manager_warning boolean
95: ,hourly_salaried_warning boolean
88: ,concatenated_segments varchar2(2000)
89: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
90: ,comment_id per_all_assignments_f.comment_id%type
91: ,effective_start_date per_all_assignments_f.effective_start_date%type
92: ,effective_end_date per_all_assignments_f.effective_end_date%type
93: ,no_managers_warning boolean
94: ,other_manager_warning boolean
95: ,hourly_salaried_warning boolean
96: ,gsp_post_process_warning varchar2(2000)
96: ,gsp_post_process_warning varchar2(2000)
97: );
98:
99: type t_rehireemp_api is record
100: (assignment_id per_all_assignments_f.assignment_id%type
101: ,asg_object_version_number per_all_assignments_f.object_version_number%type
102: ,per_effective_start_date per_all_people_f.effective_start_date%type
103: ,per_effective_end_date per_all_people_f.effective_end_date%type
104: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
97: );
98:
99: type t_rehireemp_api is record
100: (assignment_id per_all_assignments_f.assignment_id%type
101: ,asg_object_version_number per_all_assignments_f.object_version_number%type
102: ,per_effective_start_date per_all_people_f.effective_start_date%type
103: ,per_effective_end_date per_all_people_f.effective_end_date%type
104: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
105: ,assignment_number per_all_assignments_f.assignment_number%type
100: (assignment_id per_all_assignments_f.assignment_id%type
101: ,asg_object_version_number per_all_assignments_f.object_version_number%type
102: ,per_effective_start_date per_all_people_f.effective_start_date%type
103: ,per_effective_end_date per_all_people_f.effective_end_date%type
104: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
105: ,assignment_number per_all_assignments_f.assignment_number%type
106: ,assign_payroll_warning boolean
107: );
108:
101: ,asg_object_version_number per_all_assignments_f.object_version_number%type
102: ,per_effective_start_date per_all_people_f.effective_start_date%type
103: ,per_effective_end_date per_all_people_f.effective_end_date%type
104: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
105: ,assignment_number per_all_assignments_f.assignment_number%type
106: ,assign_payroll_warning boolean
107: );
108:
109: type t_updemp_api is record
118:
119: type t_hrtojob_api is record
120: (effective_start_date per_all_people_f.effective_start_date%type
121: ,effective_end_date per_all_people_f.effective_end_date%type
122: ,assignment_id per_all_assignments_f.assignment_id%type
123: ,assign_payroll_warning boolean
124: ,orig_hire_warning boolean
125: );
126:
287: ) is
288: select asg.assignment_id
289: ,asg.object_version_number
290: ,asg.vacancy_id
291: from per_all_assignments_f asg
292: ,per_assignment_status_types ast
293: where asg.assignment_status_type_id = ast.assignment_status_type_id
294: and asg.person_id = c_person_id
295: and asg.business_group_id = c_business_group_id
309: ) is
310: select asg.assignment_id
311: ,asg.object_version_number
312: ,asg.vacancy_id
313: from per_all_assignments_f asg
314: ,per_assignment_status_types ast
315: where asg.assignment_status_type_id = ast.assignment_status_type_id
316: and asg.person_id = c_person_id
317: and asg.business_group_id = c_business_group_id
956: -- =============================================================================
957: -- Default_Asg_Rec:
958: -- =============================================================================
959: function Default_Assignment_Rec
960: return per_all_assignments_f%rowtype is
961: l_proc_name constant varchar2(150) := g_pkg||'Default_Assignment_Rec';
962: l_asg_rec per_all_assignments_f%rowtype;
963: begin
964: Hr_Utility.set_location(' Entering: '||l_proc_name, 5);
958: -- =============================================================================
959: function Default_Assignment_Rec
960: return per_all_assignments_f%rowtype is
961: l_proc_name constant varchar2(150) := g_pkg||'Default_Assignment_Rec';
962: l_asg_rec per_all_assignments_f%rowtype;
963: begin
964: Hr_Utility.set_location(' Entering: '||l_proc_name, 5);
965: /*
966: ======================================================================
1068: -- Get_AsgRecord_Values:
1069: -- =============================================================================
1070: function Get_AsgRecord_Values
1071: (p_interface_code in varchar2 default null)
1072: return per_all_assignments_f%rowtype is
1073:
1074: cursor bne_cols(c_interface_code in varchar2) is
1075: select lower(bic.interface_col_name) interface_col_name
1076: from bne_interface_cols_b bic
1084: where bic.interface_code = c_interface_code
1085: and bic.display_flag ='N';
1086:
1087: l_proc_name constant varchar2(150) := g_pkg||'Get_AsgRecord_Values';
1088: l_asg_rec per_all_assignments_f%rowtype;
1089: col_name varchar2(150);
1090:
1091: begin
1092: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2252: cursor csr_asg (c_effective_date in date
2253: ,c_assignment_id in number
2254: ,c_business_group_id in number)is
2255: select *
2256: from per_all_assignments_f paf
2257: where paf.assignment_id = c_assignment_id
2258: and paf.business_group_id = c_business_group_id
2259: and c_effective_date between paf.effective_start_date
2260: and paf.effective_end_date;
2285: l_dt_upd_override boolean;
2286: l_upd_chg_ins boolean;
2287: e_empasg_notfound exception;
2288: l_UpdEmpAsg_out t_Upd_Emp_Asg_Api;
2289: l_asg_rec per_all_assignments_f%rowtype;
2290: l_grp_rec pay_people_groups%rowtype;
2291: l_scl_rec hr_soft_coding_keyflex%rowtype;
2292:
2293: begin
2355: -- Get the datetrack mode based on the effective date passed
2356: --
2357: Dt_Api.Find_DT_Upd_Modes
2358: (p_effective_date => p_effective_date
2359: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2360: ,p_base_key_column => 'ASSIGNMENT_ID'
2361: ,p_base_key_value => g_asg_rec.assignment_id
2362: ,p_correction => l_dt_correction
2363: ,p_update => l_dt_update
2586: cursor csr_asg (c_effective_date in date
2587: ,c_assignment_id in number
2588: ,c_business_group_id in number)is
2589: select *
2590: from per_all_assignments_f paf
2591: where paf.assignment_id = c_assignment_id
2592: and paf.business_group_id = c_business_group_id
2593: and c_effective_date between paf.effective_start_date
2594: and paf.effective_end_date;
2619: l_dt_upd_override boolean;
2620: l_upd_chg_ins boolean;
2621: e_empasg_notfound exception;
2622: l_UpdEmpAsg_out t_Upd_Emp_Asg_Api;
2623: l_asg_rec per_all_assignments_f%rowtype;
2624: l_grp_rec pay_people_groups%rowtype;
2625: l_scl_rec hr_soft_coding_keyflex%rowtype;
2626:
2627: begin
2688: -- Get the datetrack mode based on the effective date passed
2689: --
2690: Dt_Api.Find_DT_Upd_Modes
2691: (p_effective_date => p_effective_date
2692: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2693: ,p_base_key_column => 'ASSIGNMENT_ID'
2694: ,p_base_key_value => g_asg_rec.assignment_id
2695: ,p_correction => l_dt_correction
2696: ,p_update => l_dt_update
2950: cursor csr_asg (c_effective_date in date
2951: ,c_assignment_id in number
2952: ,c_business_group_id in number)is
2953: select *
2954: from per_all_assignments_f paf
2955: where paf.assignment_id = c_assignment_id
2956: and paf.business_group_id = c_business_group_id
2957: and c_effective_date between paf.effective_start_date
2958: and paf.effective_end_date;
2983: l_dt_upd_override boolean;
2984: l_upd_chg_ins boolean;
2985: e_empasg_notfound exception;
2986: l_UpdEmpAsg_out t_Upd_Emp_Asg_Api;
2987: l_asg_rec per_all_assignments_f%rowtype;
2988: l_grp_rec pay_people_groups%rowtype;
2989: l_scl_rec hr_soft_coding_keyflex%rowtype;
2990: begin
2991: hr_utility.set_location('Entering: ' || l_proc_name, 10);
3028: g_asg_rec.soft_coding_keyflex_id, 40);
3029:
3030: Dt_Api.Find_DT_Upd_Modes
3031: (p_effective_date => p_effective_date
3032: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
3033: ,p_base_key_column => 'ASSIGNMENT_ID'
3034: ,p_base_key_value => g_asg_rec.assignment_id
3035: ,p_correction => l_dt_correction
3036: ,p_update => l_dt_update
4923: ,ppf.person_id
4924: ,ppf.object_version_number per_ovn
4925: ,paf.object_version_number asg_ovn
4926: ,paf.effective_start_date -- For Hiring an applicant on the next day
4927: from per_all_assignments_f paf
4928: ,per_all_people_f ppf
4929: where paf.person_id = c_person_id
4930: and paf.business_group_id = c_business_group_id
4931: and paf.assignment_type = c_asg_type
5020: hr_utility.set_location(' Asg Id NOT Accepted : ' || p_assignment_id, 40);
5021: if l_diff_date = 1 then -- Means The Applicant Assignment is to be corrected to
5022: Dt_Api.Find_DT_Upd_Modes -- Accepted.
5023: (p_effective_date => l_perasg_rec.effective_start_date
5024: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
5025: ,p_base_key_column => 'ASSIGNMENT_ID'
5026: ,p_base_key_value => p_assignment_id
5027: ,p_correction => l_dt_correction
5028: ,p_update => l_dt_update
5031: );
5032: else
5033: Dt_Api.Find_DT_Upd_Modes
5034: (p_effective_date => p_hire_date
5035: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
5036: ,p_base_key_column => 'ASSIGNMENT_ID'
5037: ,p_base_key_value => p_assignment_id
5038: ,p_correction => l_dt_correction
5039: ,p_update => l_dt_update
5285: ,c_business_group_id in number
5286: ,c_effective_date in date) is
5287: select paf.assignment_id,
5288: ppf.object_version_number
5289: from per_all_assignments_f paf
5290: ,per_all_people_f ppf
5291: where paf.person_id = c_person_id
5292: and paf.business_group_id = c_business_group_id
5293: and paf.person_id = ppf.person_id
6773: ,c_business_group_id in number
6774: ,c_effective_date in date
6775: ) is
6776: select *
6777: from per_all_assignments_f paf
6778: where paf.person_id = c_person_id
6779: and paf.assignment_number = c_assignment_number
6780: and paf.business_group_id = c_business_group_id
6781: and c_effective_date between paf.effective_start_date
6823: g_asg_rec.assignment_status_type_id := l_asg_rec.assignment_status_type_id;
6824:
6825: Dt_Api.Find_DT_Upd_Modes
6826: (p_effective_date => p_effective_date
6827: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
6828: ,p_base_key_column => 'ASSIGNMENT_ID'
6829: ,p_base_key_value => g_asg_rec.assignment_id
6830: ,p_correction => l_dt_correction
6831: ,p_update => l_dt_update
8470: cursor csr_apl_asg (c_effective_date in date
8471: ,c_person_id in number
8472: ,c_business_group_id in number)is
8473: select *
8474: from per_all_assignments_f paf
8475: where paf.person_id = c_person_id
8476: and paf.business_group_id = c_business_group_id
8477: and c_effective_date between paf.effective_start_date
8478: and paf.effective_end_date;
8483: ,c_business_group_id in number
8484: ,c_effective_date in date
8485: ) is
8486: select *
8487: from per_all_assignments_f paf
8488: where paf.person_id = c_person_id
8489: and paf.assignment_number = c_assignment_number
8490: and paf.business_group_id = c_business_group_id
8491: and c_effective_date between paf.effective_start_date
8497:
8498: -- Added by dbansal
8499: l_hrEmpApi_out t_hrEmpApi;
8500: l_UpdEmpAsg_out t_Upd_Emp_Asg_Api;
8501: l_asg_rec1 per_all_assignments_f%rowtype;
8502: l_grp_rec pay_people_groups%rowtype;
8503: l_scl_rec hr_soft_coding_keyflex%rowtype;
8504: l_pty_rec csr_type%rowtype;
8505: l_apl_asg_rec per_all_assignments_f%rowtype;
8501: l_asg_rec1 per_all_assignments_f%rowtype;
8502: l_grp_rec pay_people_groups%rowtype;
8503: l_scl_rec hr_soft_coding_keyflex%rowtype;
8504: l_pty_rec csr_type%rowtype;
8505: l_apl_asg_rec per_all_assignments_f%rowtype;
8506: begin
8507: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
8508:
8509: --$ check already made before making call to this procedure from
8526: );
8527: fetch csr_asg into l_asg_rec;
8528:
8529:
8530: /*hr_utility.trace('CURSOR : select * from per_all_assignments_f paf
8531: where paf.person_id =' ||p_person_id || 'and paf.assignment_number = ' ||
8532: g_asg_rec.assignment_number || 'and paf.business_group_id = ' || p_business_group_id
8533: || 'and ' || sysdate|| ' between paf.effective_start_date
8534: and paf.effective_end_date' );*/
9285: ,c_business_group_id in number
9286: ,c_effective_date in date
9287: ) is
9288: select paf.assignment_id
9289: from per_all_assignments_f paf
9290: where paf.person_id = c_person_id
9291: and paf.assignment_type = 'A'
9292: and paf.assignment_number = c_assignment_number
9293: and paf.business_group_id = c_business_group_id
9298: ,c_business_group_id in number
9299: ,c_effective_date in date
9300: ) is
9301: select paf.assignment_id
9302: from per_all_assignments_f paf
9303: where paf.person_id = c_person_id
9304: and paf.assignment_type = 'A'
9305: and paf.business_group_id = c_business_group_id
9306: and c_effective_date between paf.effective_start_date