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