[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: g_migration_flag varchar2(5);
49: type ref_cur_typ is ref cursor;
50:
51: type t_hrempapi is record
52: (person_id per_all_people_f.person_id%type
53: ,assignment_id per_all_assignments_f.assignment_id%type
54: ,per_object_version_number per_all_people_f.object_version_number%type
55: ,asg_object_version_number per_all_assignments_f.object_version_number%type
56: ,per_effective_start_date date
57: ,per_effective_end_date date
51: type t_hrempapi is record
52: (person_id per_all_people_f.person_id%type
53: ,assignment_id per_all_assignments_f.assignment_id%type
54: ,per_object_version_number per_all_people_f.object_version_number%type
55: ,asg_object_version_number per_all_assignments_f.object_version_number%type
56: ,per_effective_start_date date
57: ,per_effective_end_date date
58: ,full_name per_all_people_f.full_name%type
59: ,per_comment_id per_all_people_f.comment_id%type
56: ,per_effective_start_date date
57: ,per_effective_end_date date
58: ,full_name per_all_people_f.full_name%type
59: ,per_comment_id per_all_people_f.comment_id%type
60: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
61: ,assignment_number per_all_assignments_f.assignment_number%type
62: ,pdp_object_version_number per_all_people_f.object_version_number%type
63: ,name_combination_warning boolean
64: ,assign_payroll_warning boolean
57: ,per_effective_end_date date
58: ,full_name per_all_people_f.full_name%type
59: ,per_comment_id per_all_people_f.comment_id%type
60: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
61: ,assignment_number per_all_assignments_f.assignment_number%type
62: ,pdp_object_version_number per_all_people_f.object_version_number%type
63: ,name_combination_warning boolean
64: ,assign_payroll_warning boolean
65: ,orig_hire_warning boolean
65: ,orig_hire_warning boolean
66: );
67:
68: type t_asgupdcrit_api is record
69: (asg_object_version_number per_all_assignments_f.object_version_number%type
70: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
71: ,people_group_id per_all_assignments_f.people_group_id%type
72: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
73: ,group_name pay_people_groups.group_name%type
66: );
67:
68: type t_asgupdcrit_api is record
69: (asg_object_version_number per_all_assignments_f.object_version_number%type
70: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
71: ,people_group_id per_all_assignments_f.people_group_id%type
72: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
73: ,group_name pay_people_groups.group_name%type
74: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
67:
68: type t_asgupdcrit_api is record
69: (asg_object_version_number per_all_assignments_f.object_version_number%type
70: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
71: ,people_group_id per_all_assignments_f.people_group_id%type
72: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
73: ,group_name pay_people_groups.group_name%type
74: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
75: ,asg_effective_end_date per_all_assignments_f.effective_end_date%type
68: type t_asgupdcrit_api is record
69: (asg_object_version_number per_all_assignments_f.object_version_number%type
70: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
71: ,people_group_id per_all_assignments_f.people_group_id%type
72: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
73: ,group_name pay_people_groups.group_name%type
74: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
75: ,asg_effective_end_date per_all_assignments_f.effective_end_date%type
76: ,org_now_no_manager_warning boolean
70: ,special_ceiling_step_id per_all_assignments_f.special_ceiling_step_id%type
71: ,people_group_id per_all_assignments_f.people_group_id%type
72: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
73: ,group_name pay_people_groups.group_name%type
74: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
75: ,asg_effective_end_date per_all_assignments_f.effective_end_date%type
76: ,org_now_no_manager_warning boolean
77: ,other_manager_warning boolean
78: ,spp_delete_warning boolean
71: ,people_group_id per_all_assignments_f.people_group_id%type
72: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
73: ,group_name pay_people_groups.group_name%type
74: ,asg_effective_start_date per_all_assignments_f.effective_start_date%type
75: ,asg_effective_end_date per_all_assignments_f.effective_end_date%type
76: ,org_now_no_manager_warning boolean
77: ,other_manager_warning boolean
78: ,spp_delete_warning boolean
79: ,entries_changed_warning varchar2(50)
79: ,entries_changed_warning varchar2(50)
80: ,tax_district_changed_warning boolean
81: ,concatenated_segments hr_soft_coding_keyflex.concatenated_segments%type
82: ,gsp_post_process_warning varchar2(2000)
83: ,comment_id per_all_assignments_f.comment_id%type
84: );
85:
86: type t_upd_emp_asg_api is record
87: (cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%type
83: ,comment_id per_all_assignments_f.comment_id%type
84: );
85:
86: type t_upd_emp_asg_api is record
87: (cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%type
88: ,cagr_concatenated_segments varchar2(2000)
89: ,concatenated_segments varchar2(2000)
90: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
91: ,comment_id per_all_assignments_f.comment_id%type
86: type t_upd_emp_asg_api is record
87: (cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%type
88: ,cagr_concatenated_segments varchar2(2000)
89: ,concatenated_segments varchar2(2000)
90: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
91: ,comment_id per_all_assignments_f.comment_id%type
92: ,effective_start_date per_all_assignments_f.effective_start_date%type
93: ,effective_end_date per_all_assignments_f.effective_end_date%type
94: ,no_managers_warning boolean
87: (cagr_grade_def_id per_all_assignments_f.cagr_grade_def_id%type
88: ,cagr_concatenated_segments varchar2(2000)
89: ,concatenated_segments varchar2(2000)
90: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
91: ,comment_id per_all_assignments_f.comment_id%type
92: ,effective_start_date per_all_assignments_f.effective_start_date%type
93: ,effective_end_date per_all_assignments_f.effective_end_date%type
94: ,no_managers_warning boolean
95: ,other_manager_warning boolean
88: ,cagr_concatenated_segments varchar2(2000)
89: ,concatenated_segments varchar2(2000)
90: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
91: ,comment_id per_all_assignments_f.comment_id%type
92: ,effective_start_date per_all_assignments_f.effective_start_date%type
93: ,effective_end_date per_all_assignments_f.effective_end_date%type
94: ,no_managers_warning boolean
95: ,other_manager_warning boolean
96: ,hourly_salaried_warning boolean
89: ,concatenated_segments varchar2(2000)
90: ,soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type
91: ,comment_id per_all_assignments_f.comment_id%type
92: ,effective_start_date per_all_assignments_f.effective_start_date%type
93: ,effective_end_date per_all_assignments_f.effective_end_date%type
94: ,no_managers_warning boolean
95: ,other_manager_warning boolean
96: ,hourly_salaried_warning boolean
97: ,gsp_post_process_warning varchar2(2000)
97: ,gsp_post_process_warning varchar2(2000)
98: );
99:
100: type t_rehireemp_api is record
101: (assignment_id per_all_assignments_f.assignment_id%type
102: ,asg_object_version_number per_all_assignments_f.object_version_number%type
103: ,per_effective_start_date per_all_people_f.effective_start_date%type
104: ,per_effective_end_date per_all_people_f.effective_end_date%type
105: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
98: );
99:
100: type t_rehireemp_api is record
101: (assignment_id per_all_assignments_f.assignment_id%type
102: ,asg_object_version_number per_all_assignments_f.object_version_number%type
103: ,per_effective_start_date per_all_people_f.effective_start_date%type
104: ,per_effective_end_date per_all_people_f.effective_end_date%type
105: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
106: ,assignment_number per_all_assignments_f.assignment_number%type
101: (assignment_id per_all_assignments_f.assignment_id%type
102: ,asg_object_version_number per_all_assignments_f.object_version_number%type
103: ,per_effective_start_date per_all_people_f.effective_start_date%type
104: ,per_effective_end_date per_all_people_f.effective_end_date%type
105: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
106: ,assignment_number per_all_assignments_f.assignment_number%type
107: ,assign_payroll_warning boolean
108: );
109:
102: ,asg_object_version_number per_all_assignments_f.object_version_number%type
103: ,per_effective_start_date per_all_people_f.effective_start_date%type
104: ,per_effective_end_date per_all_people_f.effective_end_date%type
105: ,assignment_sequence per_all_assignments_f.assignment_sequence%type
106: ,assignment_number per_all_assignments_f.assignment_number%type
107: ,assign_payroll_warning boolean
108: );
109:
110: type t_updemp_api is record
119:
120: type t_hrtojob_api is record
121: (effective_start_date per_all_people_f.effective_start_date%type
122: ,effective_end_date per_all_people_f.effective_end_date%type
123: ,assignment_id per_all_assignments_f.assignment_id%type
124: ,assign_payroll_warning boolean
125: ,orig_hire_warning boolean
126: );
127:
288: ) is
289: select asg.assignment_id
290: ,asg.object_version_number
291: ,asg.vacancy_id
292: from per_all_assignments_f asg
293: ,per_assignment_status_types ast
294: where asg.assignment_status_type_id = ast.assignment_status_type_id
295: and asg.person_id = c_person_id
296: and asg.business_group_id = c_business_group_id
310: ) is
311: select asg.assignment_id
312: ,asg.object_version_number
313: ,asg.vacancy_id
314: from per_all_assignments_f asg
315: ,per_assignment_status_types ast
316: where asg.assignment_status_type_id = ast.assignment_status_type_id
317: and asg.person_id = c_person_id
318: and asg.business_group_id = c_business_group_id
957: -- =============================================================================
958: -- Default_Asg_Rec:
959: -- =============================================================================
960: function Default_Assignment_Rec
961: return per_all_assignments_f%rowtype is
962: l_proc_name constant varchar2(150) := g_pkg||'Default_Assignment_Rec';
963: l_asg_rec per_all_assignments_f%rowtype;
964: begin
965: Hr_Utility.set_location(' Entering: '||l_proc_name, 5);
959: -- =============================================================================
960: function Default_Assignment_Rec
961: return per_all_assignments_f%rowtype is
962: l_proc_name constant varchar2(150) := g_pkg||'Default_Assignment_Rec';
963: l_asg_rec per_all_assignments_f%rowtype;
964: begin
965: Hr_Utility.set_location(' Entering: '||l_proc_name, 5);
966: /*
967: ======================================================================
1069: -- Get_AsgRecord_Values:
1070: -- =============================================================================
1071: function Get_AsgRecord_Values
1072: (p_interface_code in varchar2 default null)
1073: return per_all_assignments_f%rowtype is
1074:
1075: cursor bne_cols(c_interface_code in varchar2) is
1076: select lower(bic.interface_col_name) interface_col_name
1077: from bne_interface_cols_b bic
1085: where bic.interface_code = c_interface_code
1086: and bic.display_flag ='N';
1087:
1088: l_proc_name constant varchar2(150) := g_pkg||'Get_AsgRecord_Values';
1089: l_asg_rec per_all_assignments_f%rowtype;
1090: col_name varchar2(150);
1091:
1092: begin
1093: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2355: cursor csr_asg (c_effective_date in date
2356: ,c_assignment_id in number
2357: ,c_business_group_id in number)is
2358: select *
2359: from per_all_assignments_f paf
2360: where paf.assignment_id = c_assignment_id
2361: and paf.business_group_id = c_business_group_id
2362: and c_effective_date between paf.effective_start_date
2363: and paf.effective_end_date;
2388: l_dt_upd_override boolean;
2389: l_upd_chg_ins boolean;
2390: e_empasg_notfound exception;
2391: l_UpdEmpAsg_out t_Upd_Emp_Asg_Api;
2392: l_asg_rec per_all_assignments_f%rowtype;
2393: l_grp_rec pay_people_groups%rowtype;
2394: l_scl_rec hr_soft_coding_keyflex%rowtype;
2395: l_expected_system_status varchar2(20);
2396: l_return_status varchar2(100);
2460: -- Get the datetrack mode based on the effective date passed
2461: --
2462: Dt_Api.Find_DT_Upd_Modes
2463: (p_effective_date => p_effective_date
2464: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2465: ,p_base_key_column => 'ASSIGNMENT_ID'
2466: ,p_base_key_value => g_asg_rec.assignment_id
2467: ,p_correction => l_dt_correction
2468: ,p_update => l_dt_update
2616: end if;
2617:
2618: Dt_Api.Find_DT_Upd_Modes
2619: (p_effective_date => p_effective_date
2620: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2621: ,p_base_key_column => 'ASSIGNMENT_ID'
2622: ,p_base_key_value => g_asg_rec.assignment_id
2623: ,p_correction => l_dt_correction
2624: ,p_update => l_dt_update
2842: cursor csr_asg (c_effective_date in date
2843: ,c_assignment_id in number
2844: ,c_business_group_id in number)is
2845: select *
2846: from per_all_assignments_f paf
2847: where paf.assignment_id = c_assignment_id
2848: and paf.business_group_id = c_business_group_id
2849: and c_effective_date between paf.effective_start_date
2850: and paf.effective_end_date;
2875: l_dt_upd_override boolean;
2876: l_upd_chg_ins boolean;
2877: e_empasg_notfound exception;
2878: l_UpdEmpAsg_out t_Upd_Emp_Asg_Api;
2879: l_asg_rec per_all_assignments_f%rowtype;
2880: l_grp_rec pay_people_groups%rowtype;
2881: l_scl_rec hr_soft_coding_keyflex%rowtype;
2882:
2883: begin
2944: -- Get the datetrack mode based on the effective date passed
2945: --
2946: Dt_Api.Find_DT_Upd_Modes
2947: (p_effective_date => p_effective_date
2948: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
2949: ,p_base_key_column => 'ASSIGNMENT_ID'
2950: ,p_base_key_value => g_asg_rec.assignment_id
2951: ,p_correction => l_dt_correction
2952: ,p_update => l_dt_update
3206: cursor csr_asg (c_effective_date in date
3207: ,c_assignment_id in number
3208: ,c_business_group_id in number)is
3209: select *
3210: from per_all_assignments_f paf
3211: where paf.assignment_id = c_assignment_id
3212: and paf.business_group_id = c_business_group_id
3213: and c_effective_date between paf.effective_start_date
3214: and paf.effective_end_date;
3239: l_dt_upd_override boolean;
3240: l_upd_chg_ins boolean;
3241: e_empasg_notfound exception;
3242: l_UpdEmpAsg_out t_Upd_Emp_Asg_Api;
3243: l_asg_rec per_all_assignments_f%rowtype;
3244: l_grp_rec pay_people_groups%rowtype;
3245: l_scl_rec hr_soft_coding_keyflex%rowtype;
3246: begin
3247: hr_utility.set_location('Entering: ' || l_proc_name, 10);
3284: g_asg_rec.soft_coding_keyflex_id, 40);
3285:
3286: Dt_Api.Find_DT_Upd_Modes
3287: (p_effective_date => p_effective_date
3288: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
3289: ,p_base_key_column => 'ASSIGNMENT_ID'
3290: ,p_base_key_value => g_asg_rec.assignment_id
3291: ,p_correction => l_dt_correction
3292: ,p_update => l_dt_update
5259: ,ppf.person_id
5260: ,ppf.object_version_number per_ovn
5261: ,paf.object_version_number asg_ovn
5262: ,paf.effective_start_date -- For Hiring an applicant on the next day
5263: from per_all_assignments_f paf
5264: ,per_all_people_f ppf
5265: where paf.person_id = c_person_id
5266: and paf.business_group_id = c_business_group_id
5267: and paf.assignment_type = c_asg_type
5356: hr_utility.set_location(' Asg Id NOT Accepted : ' || p_assignment_id, 40);
5357: if l_diff_date = 1 then -- Means The Applicant Assignment is to be corrected to
5358: Dt_Api.Find_DT_Upd_Modes -- Accepted.
5359: (p_effective_date => l_perasg_rec.effective_start_date
5360: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
5361: ,p_base_key_column => 'ASSIGNMENT_ID'
5362: ,p_base_key_value => p_assignment_id
5363: ,p_correction => l_dt_correction
5364: ,p_update => l_dt_update
5367: );
5368: else
5369: Dt_Api.Find_DT_Upd_Modes
5370: (p_effective_date => p_hire_date
5371: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
5372: ,p_base_key_column => 'ASSIGNMENT_ID'
5373: ,p_base_key_value => p_assignment_id
5374: ,p_correction => l_dt_correction
5375: ,p_update => l_dt_update
5621: ,c_business_group_id in number
5622: ,c_effective_date in date) is
5623: select paf.assignment_id,
5624: ppf.object_version_number
5625: from per_all_assignments_f paf
5626: ,per_all_people_f ppf
5627: where paf.person_id = c_person_id
5628: and paf.business_group_id = c_business_group_id
5629: and paf.person_id = ppf.person_id
7110: ,c_business_group_id in number
7111: ,c_effective_date in date
7112: ) is
7113: select *
7114: from per_all_assignments_f paf
7115: where paf.person_id = c_person_id
7116: and paf.assignment_number = c_assignment_number
7117: and paf.business_group_id = c_business_group_id
7118: and c_effective_date between paf.effective_start_date
7123: ,c_business_group_id in number
7124: ,c_effective_date in date
7125: ) is
7126: select *
7127: from per_all_assignments_f paf
7128: where paf.person_id = c_person_id
7129: and paf.assignment_id = c_assignment_id
7130: and paf.business_group_id = c_business_group_id
7131: and c_effective_date between paf.effective_start_date
7228: --g_asg_rec.assignment_status_type_id := l_asg_rec.assignment_status_type_id;
7229:
7230: Dt_Api.Find_DT_Upd_Modes
7231: (p_effective_date => p_effective_date
7232: ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
7233: ,p_base_key_column => 'ASSIGNMENT_ID'
7234: ,p_base_key_value => g_asg_rec.assignment_id
7235: ,p_correction => l_dt_correction
7236: ,p_update => l_dt_update
8874: cursor csr_apl_asg (c_effective_date in date
8875: ,c_person_id in number
8876: ,c_business_group_id in number)is
8877: select *
8878: from per_all_assignments_f paf
8879: where paf.person_id = c_person_id
8880: and paf.business_group_id = c_business_group_id
8881: and c_effective_date between paf.effective_start_date
8882: and paf.effective_end_date;
8887: ,c_business_group_id in number
8888: ,c_effective_date in date
8889: ) is
8890: select *
8891: from per_all_assignments_f paf
8892: where paf.person_id = c_person_id
8893: and paf.assignment_id = c_assignment_id
8894: and paf.business_group_id = c_business_group_id
8895: and c_effective_date between paf.effective_start_date
8900: ,c_business_group_id in number
8901: ,c_effective_date in date
8902: ) is
8903: select *
8904: from per_all_assignments_f paf
8905: where paf.person_id = c_person_id
8906: and paf.assignment_number = c_assignment_number
8907: and paf.business_group_id = c_business_group_id
8908: and c_effective_date between paf.effective_start_date
8914:
8915: -- Added by dbansal
8916: l_hrEmpApi_out t_hrEmpApi;
8917: l_UpdEmpAsg_out t_Upd_Emp_Asg_Api;
8918: l_asg_rec1 per_all_assignments_f%rowtype;
8919: l_grp_rec pay_people_groups%rowtype;
8920: l_scl_rec hr_soft_coding_keyflex%rowtype;
8921: l_pty_rec csr_type%rowtype;
8922: l_apl_asg_rec per_all_assignments_f%rowtype;
8918: l_asg_rec1 per_all_assignments_f%rowtype;
8919: l_grp_rec pay_people_groups%rowtype;
8920: l_scl_rec hr_soft_coding_keyflex%rowtype;
8921: l_pty_rec csr_type%rowtype;
8922: l_apl_asg_rec per_all_assignments_f%rowtype;
8923: l_appl_override_warning boolean;
8924: l_concat_segments varchar2(2000);
8925: l_secondary boolean;
8926: begin
9021: end if;*/
9022:
9023:
9024:
9025: /*hr_utility.trace('CURSOR : select * from per_all_assignments_f paf
9026: where paf.person_id =' ||p_person_id || 'and paf.assignment_number = ' ||
9027: g_asg_rec.assignment_number || 'and paf.business_group_id = ' || p_business_group_id
9028: || 'and ' || sysdate|| ' between paf.effective_start_date
9029: and paf.effective_end_date' );*/
9941: ,c_business_group_id in number
9942: ,c_effective_date in date
9943: ) is
9944: select paf.assignment_id
9945: from per_all_assignments_f paf
9946: where paf.person_id = c_person_id
9947: and paf.assignment_type = 'A'
9948: and paf.assignment_number = c_assignment_number
9949: and paf.business_group_id = c_business_group_id
9954: ,c_business_group_id in number
9955: ,c_effective_date in date
9956: ) is
9957: select paf.assignment_id
9958: from per_all_assignments_f paf
9959: where paf.person_id = c_person_id
9960: and paf.assignment_type = 'A'
9961: and paf.business_group_id = c_business_group_id
9962: and c_effective_date between paf.effective_start_date