1 PACKAGE GHR_MSL_PKG AUTHID CURRENT_USER AS
2 /* $Header: ghmslexe.pkh 120.10 2010/06/28 10:37:52 vmididho ship $ */
3
4 msl_error EXCEPTION;
5
6 g_ses_msl_process VARCHAR2(1);
7 g_ses_bp_capped BOOLEAN := FALSE;
8 g_msl_bp_paycap NUMBER;
9 g_sl_payband_conv BOOLEAN := FALSE; --8320557
10
11 ---GPPA Update 46 changes
12 g_first_noa_code ghr_nature_of_actions.code%type;
13
14 -- Bug#5063304 Moved this type definition from execute_msl to global level.
15 TYPE pay_plan_prd IS RECORD
16 (
17 pay_plan ghr_mass_salary_criteria.pay_plan%type,
18 prd ghr_mass_salary_criteria.pay_rate_determinant%type
19 );
20
21 TYPE pp_prd IS TABLE OF pay_plan_prd INDEX BY BINARY_INTEGER;
22
23 TYPE pay_plan_prd_per_gr IS RECORD
24 (
25 pay_plan ghr_mass_salary_criteria.pay_plan%type,
26 prd ghr_mass_salary_criteria.pay_rate_determinant%type,
27 percent ghr_mass_salary_criteria_ext.increase_percent%type,
28 grade ghr_mass_salary_criteria_ext.grade%type
29 );
30
31 TYPE pp_prd_per_gr IS TABLE OF pay_plan_prd_per_gr INDEX BY BINARY_INTEGER;
32
33 PROCEDURE execute_msl (p_errbuf out nocopy varchar2,
34 p_retcode out nocopy number,
35 p_mass_salary_id in number,
36 p_action in varchar2
37 );
38 --p_bus_grp_id in number);
42 p_retcode out nocopy number,
39
40
41 PROCEDURE execute_msl_perc (p_errbuf out nocopy varchar2,
43 p_mass_salary_id in number,
44 p_action in varchar2);
45
46 function SUBMIT_CONC_REQ (P_APPLICATION IN VARCHAR2,
47 P_PROGRAM IN VARCHAR2,
48 P_DESCRIPTION IN VARCHAR2,
49 P_START_TIME IN VARCHAR2,
50 P_SUB_REQUEST IN BOOLEAN,
51 P_ARGUMENT1 IN VARCHAR2,
52 P_ARGUMENT2 IN VARCHAR2)
53 return number;
54
55 procedure purge_processed_recs(p_session_id in number,
56 p_err_buf out nocopy varchar2);
57
58 --Bug#9329643
59 procedure pop_dtls_from_pa_req(p_person_id in number,p_effective_date in date,
60 p_mass_salary_id in number, p_org_name in varchar2, p_employee_number in varchar2);
61
62 FUNCTION GET_PAY_PLAN_NAME (PP IN VARCHAR2) RETURN VARCHAR2;
63 FUNCTION GET_USER_TABLE_name (P_USER_TABLE_id IN NUMBER) RETURN VARCHAR2;
64
65 procedure ins_upd_per_extra_info
66 (p_person_id in number,p_effective_date in date,
67 p_sel_flag in varchar2, p_comment in varchar2,
68 p_msl_id in number, p_increase_percent in number default NULL);
69
70 procedure ins_upd_per_ses_extra_info
71 (p_person_id in number,p_effective_date in date,
72 p_sel_flag in varchar2, p_comment in varchar2,
73 p_msl_id in number, p_ses_basic_pay in number default NULL);
74
75 PROCEDURE get_extra_info_comments
76 (p_person_id in number,
77 p_effective_date in date,
78 p_sel_flag in out nocopy varchar2,
79 p_comments in out nocopy varchar2,
80 p_mass_salary_id in number,
81 p_increase_percent out nocopy number,
82 p_ses_basic_pay out nocopy number);
83
84 -- Bug#5063304 Created this new procedure
85 PROCEDURE fetch_and_validate_emp(
86 p_action IN VARCHAR2
87 ,p_mass_salary_id IN NUMBER
88 ,p_mass_salary_name IN VARCHAR2
89 ,p_full_name IN per_people_f.full_name%TYPE
90 ,p_national_identifier IN per_people_f.national_identifier%TYPE
91 --Bug # 9329643
92 ,p_employee_number IN per_people_f.employee_number%TYPE
93 ,p_assignment_id IN per_assignments_f.assignment_id%TYPE
94 ,p_person_id IN per_assignments_f.person_id%TYPE
95 ,p_position_id IN per_assignments_f.position_id%TYPE
96 ,p_grade_id IN per_assignments_f.grade_id%TYPE
97 ,p_business_group_id IN per_assignments_f.business_group_iD%TYPE
98 ,p_location_id IN per_assignments_f.location_id%TYPE
99 ,p_organization_id IN per_assignments_f.organization_id%TYPE
100 ,p_msl_organization_id IN per_assignments_f.organization_id%TYPE
101 ,p_msl_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
102 ,p_msl_personnel_office_id IN VARCHAR2
103 ,p_msl_agency_code_subelement IN VARCHAR2
104 ,p_msl_user_table_id IN NUMBER
105 ,p_rec_pp_prd IN pp_prd
106 ,p_personnel_office_id OUT NOCOPY VARCHAR2
107 ,p_org_structure_id OUT NOCOPY VARCHAR2
108 ,p_position_title OUT NOCOPY VARCHAR2
109 ,p_position_number OUT NOCOPY VARCHAR2
110 ,p_position_seq_no OUT NOCOPY VARCHAR2
111 ,p_subelem_code OUT NOCOPY VARCHAR2
112 ,p_duty_station_id OUT NOCOPY ghr_duty_stations_f.duty_station_id%TYPE
113 ,p_tenure OUT NOCOPY VARCHAR2
114 ,p_annuitant_indicator OUT NOCOPY VARCHAR2
115 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
116 ,p_work_schedule OUT NOCOPY VARCHAR2
117 ,p_part_time_hour OUT NOCOPY VARCHAR2
118 ,p_to_grade_id OUT NOCOPY per_assignments_f.grade_id%type
119 ,p_pay_plan OUT NOCOPY VARCHAR2
120 ,p_to_pay_plan OUT NOCOPY VARCHAR2
121 ,p_pay_table_id OUT NOCOPY NUMBER
122 ,p_grade_or_level OUT NOCOPY VARCHAR2
123 ,p_to_grade_or_level OUT NOCOPY VARCHAR2
124 ,p_step_or_rate OUT NOCOPY VARCHAR2
125 ,p_pay_basis OUT NOCOPY VARCHAR2
126 ,p_elig_flag OUT NOCOPY BOOLEAN
127 );
128 --
129 --
130 --
131 PROCEDURE fetch_and_validate_emp_perc(
132 p_action IN VARCHAR2
133 ,p_mass_salary_id IN NUMBER
134 ,p_mass_salary_name IN VARCHAR2
135 ,p_full_name IN per_people_f.full_name%TYPE
136 ,p_national_identifier IN per_people_f.national_identifier%TYPE
137 --Bug # 9329643
138 ,p_employee_number IN per_people_f.employee_number%TYPE
139 ,p_assignment_id IN per_assignments_f.assignment_id%TYPE
140 ,p_person_id IN per_assignments_f.person_id%TYPE
141 ,p_position_id IN per_assignments_f.position_id%TYPE
142 ,p_grade_id IN per_assignments_f.grade_id%TYPE
146 ,p_msl_organization_id IN per_assignments_f.organization_id%TYPE
143 ,p_business_group_id IN per_assignments_f.business_group_iD%TYPE
144 ,p_location_id IN per_assignments_f.location_id%TYPE
145 ,p_organization_id IN per_assignments_f.organization_id%TYPE
147 ,p_msl_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
148 ,p_msl_personnel_office_id IN VARCHAR2
149 ,p_msl_agency_code_subelement IN VARCHAR2
150 ,p_msl_user_table_id IN NUMBER
151 ,p_rec_pp_prd_per_gr IN pp_prd_per_gr
152 ,p_personnel_office_id OUT NOCOPY VARCHAR2
153 ,p_org_structure_id OUT NOCOPY VARCHAR2
154 ,p_position_title OUT NOCOPY VARCHAR2
155 ,p_position_number OUT NOCOPY VARCHAR2
156 ,p_position_seq_no OUT NOCOPY VARCHAR2
157 ,p_subelem_code OUT NOCOPY VARCHAR2
158 ,p_duty_station_id OUT NOCOPY ghr_duty_stations_f.duty_station_id%TYPE
159 ,p_tenure OUT NOCOPY VARCHAR2
160 ,p_annuitant_indicator OUT NOCOPY VARCHAR2
161 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
162 ,p_work_schedule OUT NOCOPY VARCHAR2
163 ,p_part_time_hour OUT NOCOPY VARCHAR2
164 ,p_pay_plan OUT NOCOPY VARCHAR2
165 ,p_pay_table_id OUT NOCOPY NUMBER
166 ,p_grade_or_level OUT NOCOPY VARCHAR2
167 ,p_step_or_rate OUT NOCOPY VARCHAR2
168 ,p_pay_basis OUT NOCOPY VARCHAR2
169 ,p_increase_percent OUT NOCOPY NUMBER
170 ,p_elig_flag OUT NOCOPY BOOLEAN
171 );
172 --
173 --
174 --
175 FUNCTION check_init_eligibility(p_duty_station_id in number,
176 p_PERSONNEL_OFFICE_ID in varchar2,
177 p_AGENCY_CODE_SUBELEMENT in varchar2,
178
179 p_l_duty_station_id in number,
180 p_l_personnel_office_id in varchar2,
181 p_l_sub_element_code in varchar2)
182 RETURN boolean;
183
184 FUNCTION check_eligibility(p_mass_salary_id in number,
185 p_user_table_id in number,
186 p_pay_table_id in number,
187 p_pay_plan in varchar2,
188
189 p_pay_rate_determinant in varchar2,
190 p_person_id in number,
191 p_effective_date in date,
192 p_action in varchar2)
193 RETURN boolean;
194
195 function person_in_pa_req_1noa
196 (p_person_id in number,
197 p_effective_date in date,
198 p_first_noa_code in varchar2,
199 p_pay_plan in varchar2,
200 p_days in number default 350
201 )
202 return boolean;
203
204 function person_in_pa_req_2noa
205 (p_person_id in number,
206 p_effective_date in date,
207 p_second_noa_code in varchar2,
208 p_days in number default 350
209 )
210 return boolean;
211
212 PROCEDURE get_from_sf52_data_elements (p_assignment_id in number,
213 p_effective_date in date,
214 p_old_basic_pay out nocopy number,
215 p_old_avail_pay out nocopy number,
216 p_old_loc_diff out nocopy number,
217 p_tot_old_sal out nocopy number,
218 p_old_auo_pay out nocopy number,
219 p_old_adj_basic_pay out nocopy number,
220 p_other_pay out nocopy number,
221 p_auo_premium_pay_indicator out nocopy varchar2,
222 p_ap_premium_pay_indicator out nocopy varchar2,
223 p_retention_allowance out nocopy number,
224 p_retention_allow_perc out nocopy number,
225 p_supervisory_differential out nocopy number,
226 p_supervisory_diff_perc out nocopy number,
227 p_staffing_differential out nocopy number);
228
229 procedure get_sub_element_code_pos_title
230 (p_position_id in per_assignments_f.position_id%type,
231 p_person_id in number,
232 p_business_group_id in per_assignments_f.business_group_id%type,
233 p_assignment_id in per_assignments_f.assignment_id%type,
234 p_effective_date in date,
235 p_sub_element_code out nocopy varchar2,
236 p_position_title out nocopy varchar2,
237 p_position_number out nocopy varchar2,
238 p_position_seq_no out nocopy varchar2);
239
240 procedure get_other_dtls_for_rep(p_prd in varchar2,
241 p_first_lac2_information1 in varchar2,
242 p_first_lac2_information2 in varchar2,
243 p_first_action_la_code1 out nocopy varchar2,
244 p_first_action_la_code2 out nocopy varchar2,
245 p_remark_code1 out nocopy varchar2,
246 p_remark_code2 out nocopy varchar2
247 );
251 p_effective_date in date,
248
249 function check_select_flg_msl_perc(p_person_id in number,
250 p_action in varchar2,
252 p_mass_salary_id in number,
253 p_sel_flg in out nocopy varchar2,
254 p_increase_percent in out nocopy number)
255 return boolean;
256
257
258 function check_select_flg(p_person_id in number,
259 p_action in varchar2,
260 p_effective_date in date,
261 p_mass_salary_id in number,
262 p_sel_flg in out nocopy varchar2)
263 return boolean;
264
265 procedure purge_old_data (p_mass_salary_id in number);
266
267 PROCEDURE get_pay_plan_and_table_id (p_prd in varchar2,
268 p_person_id in number,
269 p_position_id in per_assignments_f.position_id%type,
270 p_effective_date in date,
271 p_grade_id in per_assignments_f.grade_id%type,
272 p_assignment_id in per_assignments_f.assignment_id%type,
273 p_action in varchar2,
274 p_pay_plan out nocopy varchar2,
275 p_pay_table_id out nocopy number,
276 p_grade_or_level out nocopy varchar2,
277 p_step_or_rate out nocopy varchar2,
278 p_pay_basis out nocopy varchar2);
279
280 --Bug#5089732 Created new overloaded procedure get_pay_plan_and_table_id
281 PROCEDURE get_pay_plan_and_table_id (p_prd in varchar2,
282 p_person_id in number,
283 p_position_id in per_assignments_f.position_id%type,
284 p_effective_date in date,
285 p_grade_id in per_assignments_f.grade_id%type,
286 p_to_grade_id out nocopy per_assignments_f.grade_id%type,
287 p_assignment_id in per_assignments_f.assignment_id%type,
288 p_action in varchar2,
289 p_pay_plan out nocopy varchar2,
290 p_to_pay_plan out nocopy varchar2,
291 p_pay_table_id out nocopy number,
292 p_grade_or_level out nocopy varchar2,
293 p_to_grade_or_level out nocopy varchar2,
294 p_step_or_rate out nocopy varchar2,
295 p_pay_basis out nocopy varchar2);
296
297 procedure update_sel_flg (p_person_id in number,p_effective_date date);
298
299 FUNCTION check_grade_retention(p_prd in varchar2
300 ,p_person_id in number
301 ,p_effective_date in date) return varchar2;
302
303 procedure get_pos_grp1_ddf (p_position_id in per_assignments_f.position_id%type,
304 p_effective_date in date,
305 p_pos_ei_data out nocopy per_position_extra_info%rowtype);
306
307 procedure create_mass_act_prev (
308 p_effective_date in date,
309 p_date_of_birth in date,
310 p_full_name in varchar2,
311 p_national_identifier in varchar2,
312 --Bug # 9329643
313 p_employee_number in varchar2,
314 p_duty_station_code in varchar2,
315 p_duty_station_desc in varchar2,
316 p_personnel_office_id in varchar2,
317 p_basic_pay in number,
318 p_new_basic_pay in number,
319 -- Bug#2383992
320 p_adj_basic_pay in number,
321 p_new_adj_basic_pay in number,
322 -- Bug#2383992
323 p_old_avail_pay in number,
324 p_new_avail_pay in number,
325 p_old_loc_diff in number,
326 p_new_loc_diff in number,
327 p_tot_old_sal in number,
328 p_tot_new_sal in number,
329 p_old_auo_pay in number,
330 p_new_auo_pay in number,
331 p_position_id in per_assignments_f.position_id%type,
332 p_position_title in varchar2,
333 -- FWFA Changes Bug#4444609
334 p_position_number in varchar2,
335 p_position_seq_no in varchar2,
336 -- FWFA Changes
337 p_org_structure_id in varchar2,
338 p_agency_sub_element_code in varchar2,
339 p_person_id in number,
340 p_mass_salary_id in number,
341 p_sel_flg in varchar2,
342 p_first_action_la_code1 in varchar2,
343 p_first_action_la_code2 in varchar2,
344 p_remark_code1 in varchar2,
345 p_remark_code2 in varchar2,
346 p_grade_or_level in varchar2,
347 p_step_or_rate in varchar2,
348 p_pay_plan in varchar2,
349 p_pay_rate_determinant in varchar2,
350 p_tenure in varchar2,
351 p_action in varchar2,
352 p_assignment_id in number,
353 p_old_other_pay in number,
354 p_new_other_pay in number,
355 -- Bug#2383992
356 p_old_capped_other_pay in number,
357 p_new_capped_other_pay in number,
358 p_old_retention_allowance in number,
359 p_new_retention_allowance in number,
360 p_old_supervisory_differential in number,
361 p_new_supervisory_differential in number,
362 p_organization_name in varchar2,
363 -- Bug#2383992
364 p_increase_percent in number default null,
365 -- FWFA Changes Bug#4444609
366 p_input_pay_rate_determinant in varchar2,
367 p_from_pay_table_id number,
368 p_to_pay_table_id number
369 -- FWFA Changes
370 );
371
372 procedure get_lac_dtls
373 (p_pa_request_id in number,
374 p_sf52_rec out nocopy ghr_pa_requests%rowtype);
375
376 procedure create_lac_remarks
377 (p_pa_request_id in number,
378 p_new_pa_request_id in number);
379
380 procedure upd_ext_info_to_null(p_effective_date in date);
381
382 PROCEDURE assign_to_sf52_rec(
383 p_person_id in number,
387 p_national_identifier in varchar2,
384 p_first_name in varchar2,
385 p_last_name in varchar2,
386 p_middle_names in varchar2,
388 p_date_of_birth in date,
389 p_effective_date in date,
390 p_assignment_id in number,
391 p_tenure in varchar2,
392 -- Bug#5089732 Added to_grade_id, to_pay_plan,to_grade_or_level parameters.
393 p_to_grade_id in number,
394 p_to_pay_plan in varchar2,
395 p_to_grade_or_level in varchar2,
396 -- Bug35089732
397 p_step_or_rate in varchar2,
398 p_annuitant_indicator in varchar2,
399 p_pay_rate_determinant in varchar2,
400 p_work_schedule in varchar2,
401 p_part_time_hour in varchar2,
402 p_flsa_category in varchar2,
403 p_bargaining_unit_status in varchar2,
404 p_functional_class in varchar2,
405 p_supervisory_status in varchar2,
406 p_basic_pay in number,
407 p_to_locality_adj in number,
408 p_to_adj_basic_pay in number,
409 p_to_total_salary in number,
410 p_from_other_pay_amount in number,
411 p_to_other_pay_amount in number,
412 p_to_au_overtime in number,
413 p_to_availability_pay in number,
414 p_to_retention_allowance in number,
415 p_to_retention_allow_perce in number,
416 p_to_supervisory_differential in number,
417 p_to_supervisory_diff_perce in number,
418 p_to_staffing_differential in number,
419 p_duty_station_id in number,
420 p_duty_station_code in ghr_pa_requests.duty_station_code%type,
421 p_duty_station_desc in ghr_pa_requests.duty_station_desc%type,
422 -- FWFA Changes Bug#4444609
423 p_input_pay_rate_determinant in ghr_pa_requests.input_pay_rate_determinant%type,
424 p_from_pay_table_id in ghr_pa_requests.from_pay_table_identifier%type,
425 p_to_pay_table_id in ghr_pa_requests.to_pay_table_identifier%type,
426 -- FWFA Changes
427 p_lac_sf52_rec in ghr_pa_requests%rowtype,
428 p_sf52_rec out nocopy ghr_pa_requests%rowtype);
429
430 procedure pr (msg varchar2,par1 in varchar2 default null,
431 par2 in varchar2 default null);
432
433 PROCEDURE set_ses_msl_process(ses_flag varchar2);
434
435 --added for 5470182
436 PROCEDURE execute_msl_ses_range (p_errbuf out nocopy varchar2,
437 p_retcode out nocopy number,
438 p_mass_salary_id in number,
439 p_action in varchar2);
440
441
442 PROCEDURE fetch_and_validate_emp_ses(
443 p_action IN VARCHAR2
444 ,p_mass_salary_id IN NUMBER
445 ,p_mass_salary_name IN VARCHAR2
446 ,p_full_name IN per_people_f.full_name%TYPE
447 ,p_national_identifier IN per_people_f.national_identifier%TYPE
448 --Bug # 9329643
449 ,p_employee_number IN per_people_f.employee_number%TYPE
450 ,p_assignment_id IN per_assignments_f.assignment_id%TYPE
451 ,p_person_id IN per_assignments_f.person_id%TYPE
452 ,p_position_id IN per_assignments_f.position_id%TYPE
453 ,p_grade_id IN per_assignments_f.grade_id%TYPE
454 ,p_business_group_id IN per_assignments_f.business_group_iD%TYPE
455 ,p_location_id IN per_assignments_f.location_id%TYPE
456 ,p_organization_id IN per_assignments_f.organization_id%TYPE
457 ,p_msl_organization_id IN per_assignments_f.organization_id%TYPE
458 ,p_msl_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
459 ,p_msl_personnel_office_id IN VARCHAR2
460 ,p_msl_agency_code_subelement IN VARCHAR2
461 ,p_msl_user_table_id IN NUMBER
462 ,p_rec_pp_prd IN pp_prd
463 ,p_personnel_office_id OUT NOCOPY VARCHAR2
464 ,p_org_structure_id OUT NOCOPY VARCHAR2
465 ,p_position_title OUT NOCOPY VARCHAR2
466 ,p_position_number OUT NOCOPY VARCHAR2
467 ,p_position_seq_no OUT NOCOPY VARCHAR2
468 ,p_subelem_code OUT NOCOPY VARCHAR2
469 ,p_duty_station_id OUT NOCOPY ghr_duty_stations_f.duty_station_id%TYPE
470 ,p_tenure OUT NOCOPY VARCHAR2
471 ,p_annuitant_indicator OUT NOCOPY VARCHAR2
472 ,p_pay_rate_determinant OUT NOCOPY VARCHAR2
473 ,p_work_schedule OUT NOCOPY VARCHAR2
474 ,p_part_time_hour OUT NOCOPY VARCHAR2
475 ,p_to_grade_id OUT NOCOPY per_assignments_f.grade_id%type
476 ,p_pay_plan OUT NOCOPY VARCHAR2
477 ,p_to_pay_plan OUT NOCOPY VARCHAR2
478 ,p_pay_table_id OUT NOCOPY NUMBER
479 ,p_grade_or_level OUT NOCOPY VARCHAR2
480 ,p_to_grade_or_level OUT NOCOPY VARCHAR2
481 ,p_step_or_rate OUT NOCOPY VARCHAR2
482 ,p_pay_basis OUT NOCOPY VARCHAR2
483 ,p_elig_flag OUT NOCOPY BOOLEAN
484 );
485
486 function check_select_flg_ses(p_person_id in number,
487 p_action in varchar2,
488 p_effective_date in date,
489 p_mass_salary_id in number,
490 p_sel_flg in out nocopy varchar2,
491 p_ses_basic_pay in out nocopy number
492 ) return boolean;
493
494
495
496 END GHR_MSL_PKG;
[Home] [Help]