1 PACKAGE GHR_MLC_PKG AUTHID CURRENT_USER AS
2 /* $Header: ghmlcexe.pkh 120.8.12020000.2 2012/07/05 14:31:07 amnaraya ship $ */
3
4 mlc_error EXCEPTION;
5 mtc_error EXCEPTION;
6
7 --------------g_ses_msl_process VARCHAR2(1);
8
9 PROCEDURE execute_mlc (p_errbuf out nocopy varchar2,
10 p_retcode out nocopy number,
11 p_mass_salary_id in number,
12 p_action in varchar2);
13
14 PROCEDURE execute_msl_pay (p_errbuf out nocopy varchar2,
15 p_retcode out nocopy number,
16 p_mass_salary_id in number,
17 p_action in varchar2);
18
19 function SUBMIT_CONC_REQ (P_APPLICATION IN VARCHAR2,
20 P_PROGRAM IN VARCHAR2,
21 P_DESCRIPTION IN VARCHAR2,
22 P_START_TIME IN VARCHAR2,
23 P_SUB_REQUEST IN BOOLEAN,
24 P_ARGUMENT1 IN VARCHAR2,
25 P_ARGUMENT2 IN VARCHAR2)
26 return number;
27
28 procedure purge_processed_recs(p_session_id in number,
29 p_err_buf out nocopy varchar2);
30
31 procedure pop_dtls_from_pa_req(p_person_id in number,
32 p_effective_date in date,
33 p_mass_salary_id in number,
34 p_org_name in varchar2,
35 --Bug # 9329643
36 p_employee_number in varchar2);
37
38 FUNCTION GET_PAY_PLAN_NAME (PP IN VARCHAR2) RETURN VARCHAR2;
39 FUNCTION GET_USER_TABLE_name (P_USER_TABLE_id IN NUMBER) RETURN VARCHAR2;
40
41 procedure ins_upd_per_extra_info
42 (p_person_id in number,
43 p_effective_date in date,
44 p_sel_flag in varchar2,
45 p_comment in varchar2,
46 p_msl_id in number);
47
48 PROCEDURE get_extra_info_comments
49 (p_person_id in number,
50 p_effective_date in date,
51 p_sel_flag in out nocopy varchar2,
52 p_comments in out nocopy varchar2,
53 p_mass_salary_id in number);
54
55 FUNCTION check_init_eligibility(p_duty_station_id in number,
56 p_PERSONNEL_OFFICE_ID in varchar2,
57 p_AGENCY_CODE_SUBELEMENT in varchar2,
58 p_l_duty_station_id in number,
59 p_l_personnel_office_id in varchar2,
60 p_l_sub_element_code in varchar2)
61 RETURN boolean;
62
63 FUNCTION check_eligibility(p_pay_plan in varchar2,
64 p_person_id in number,
65 p_effective_date in date,
66 p_action in varchar2)
67 RETURN boolean;
68
69 function person_in_pa_req_1noa
70 (p_person_id in number,
71 p_effective_date in date,
72 p_first_noa_code in varchar2,
73 p_pay_plan in varchar2,
74 p_days in number default 350
75 )
76 return boolean;
77
78 FUNCTION check_eligibility_mtc(p_pay_plan in varchar2,
79 p_person_id in number,
80 p_effective_date in date,
81 p_action in varchar2)
82 RETURN boolean;
83
84 function person_in_pa_req_1noa_mtc
85 (p_person_id in number,
86 p_effective_date in date,
87 p_first_noa_code in varchar2,
88 p_pay_plan in varchar2,
89 p_days in number default 350
90 )
91 return boolean;
92
93
94
95
96 PROCEDURE get_from_sf52_data_elements (p_assignment_id in number,
97 p_effective_date in date,
98 p_old_basic_pay out nocopy number,
99 p_old_avail_pay out nocopy number,
100 p_old_loc_diff out nocopy number,
101 p_tot_old_sal out nocopy number,
102 p_old_auo_pay out nocopy number,
103 p_old_adj_basic_pay out nocopy number,
104 p_other_pay out nocopy number,
105 p_auo_premium_pay_indicator out nocopy varchar2,
109 p_supervisory_differential out nocopy number,
106 p_ap_premium_pay_indicator out nocopy varchar2,
107 p_retention_allowance out nocopy number,
108 p_retention_allow_perc out nocopy number,
110 p_supervisory_diff_perc out nocopy number,
111 p_staffing_differential out nocopy number);
112
113 procedure get_sub_element_code_pos_title
114 (p_position_id in per_assignments_f.position_id%type,
115 p_person_id in number,
116 p_business_group_id in per_assignments_f.business_group_id%type,
117 p_assignment_id in per_assignments_f.assignment_id%type,
118 p_effective_date in date,
119 p_sub_element_code out nocopy varchar2,
120 p_position_title out nocopy varchar2,
121 p_position_number out nocopy varchar2,
122 p_position_seq_no out nocopy varchar2);
123
124 procedure get_other_dtls_for_rep(p_prd in varchar2,
125 p_first_lac2_information1 in varchar2,
126 p_first_lac2_information2 in varchar2,
127 p_first_action_la_code1 out nocopy varchar2,
128 p_first_action_la_code2 out nocopy varchar2,
129 p_remark_code1 out nocopy varchar2,
130 p_remark_code2 out nocopy varchar2
131 );
132
133 function check_select_flg(p_person_id in number,
134 p_action in varchar2,
135 p_effective_date in date,
136 p_mass_salary_id in number,
137 p_sel_flg in out nocopy varchar2)
138 return boolean;
139
140 procedure purge_old_data (p_mass_salary_id in number);
141
142 procedure update_sel_flg (p_person_id in number,p_effective_date date);
143
144 FUNCTION check_grade_retention(p_prd in varchar2
145 ,p_person_id in number
146 ,p_effective_date in date) return varchar2;
147
148 procedure get_pos_grp1_ddf (p_position_id in per_assignments_f.position_id%type,
149 p_effective_date in date,
150 p_pos_ei_data out nocopy per_position_extra_info%rowtype);
151
152 procedure create_mass_act_prev (
153 p_effective_date in date,
154 p_date_of_birth in date,
155 p_full_name in varchar2,
156 p_national_identifier in varchar2,
157 --Bug # 9329643
158 p_employee_number in varchar2,
159 p_duty_station_code in varchar2,
160 p_duty_station_desc in varchar2,
161 p_personnel_office_id in varchar2,
162 p_basic_pay in number,
163 p_new_basic_pay in number,
164 -- Bug#2383992
165 p_adj_basic_pay in number,
166 p_new_adj_basic_pay in number,
167 -- Bug#2383992
168 p_old_avail_pay in number,
169 p_new_avail_pay in number,
170 p_old_loc_diff in number,
171 p_new_loc_diff in number,
172 p_tot_old_sal in number,
173 p_tot_new_sal in number,
174 p_old_auo_pay in number,
175 p_new_auo_pay in number,
176 p_position_id in per_assignments_f.position_id%type,
177 p_position_title in varchar2,
178 -- FWFA Changes Bug#4444609
179 p_position_number in varchar2,
180 p_position_seq_no in varchar2,
181 -- FWFA Changes
182 p_org_structure_id in varchar2,
183 p_agency_sub_element_code in varchar2,
184 p_person_id in number,
185 p_mass_salary_id in number,
186 p_sel_flg in varchar2,
187 p_first_action_la_code1 in varchar2,
188 p_first_action_la_code2 in varchar2,
189 p_remark_code1 in varchar2,
190 p_remark_code2 in varchar2,
191 p_grade_or_level in varchar2,
192 p_step_or_rate in varchar2,
193 p_pay_plan in varchar2,
194 p_pay_rate_determinant in varchar2,
195 p_tenure in varchar2,
196 p_action in varchar2,
197 p_assignment_id in number,
198 p_old_other_pay in number,
199 p_new_other_pay in number,
200 -- Bug#2383992
201 p_old_capped_other_pay in number,
202 p_new_capped_other_pay in number,
203 p_old_retention_allowance in number,
204 p_new_retention_allowance in number,
205 p_old_supervisory_differential in number,
206 p_new_supervisory_differential in number,
207 p_organization_name in varchar2,
208 -- Bug#2383992
209 -- FWFA Changes Bug#4444609
210 p_input_pay_rate_determinant in varchar2,
211 p_from_pay_table_id in number,
212 p_to_pay_table_id in number
213 -- FWFA Changes
214 );
215
216
217 procedure create_mass_act_prev_mtc (
218 p_effective_date in date,
219 p_date_of_birth in date,
220 p_full_name in varchar2,
221 p_national_identifier in varchar2,
222 --Bug # 9329643
223 p_employee_number in varchar2,
224 p_duty_station_code in varchar2,
225 p_duty_station_desc in varchar2,
226 p_personnel_office_id in varchar2,
227 p_basic_pay in number,
228 p_new_basic_pay in number,
229 p_adj_basic_pay in number,
230 p_new_adj_basic_pay in number,
231 p_old_avail_pay in number,
232 p_new_avail_pay in number,
233 p_old_loc_diff in number,
234 p_new_loc_diff in number,
235 p_tot_old_sal in number,
236 p_tot_new_sal in number,
237 p_old_auo_pay in number,
238 p_new_auo_pay in number,
239 p_position_id in per_assignments_f.position_id%type,
240 p_position_title in varchar2,
241 -- FWFA Changes Bug#4444609
242 p_position_number in varchar2,
243 p_position_seq_no in varchar2,
244 -- FWFA Changes
245 p_org_structure_id in varchar2,
246 p_agency_sub_element_code in varchar2,
247 p_person_id in number,
248 p_mass_salary_id in number,
249 p_sel_flg in varchar2,
250 p_first_action_la_code1 in varchar2,
251 p_first_action_la_code2 in varchar2,
252 p_remark_code1 in varchar2,
253 p_remark_code2 in varchar2,
254 p_grade_or_level in varchar2,
255 p_step_or_rate in varchar2,
256 p_pay_plan in varchar2,
257 p_pay_rate_determinant in varchar2,
258 p_tenure in varchar2,
259 p_action in varchar2,
260 p_assignment_id in number,
261 p_old_other_pay in number,
262 p_new_other_pay in number,
263 p_old_capped_other_pay in number,
264 p_new_capped_other_pay in number,
265 p_old_retention_allowance in number,
266 p_new_retention_allowance in number,
267 p_old_supervisory_differential in number,
268 p_new_supervisory_differential in number,
269 p_organization_name in varchar2,
270 -- FWFA Changes Bug#4444609
271 p_input_pay_rate_determinant in varchar2,
272 p_from_pay_table_id number,
273 p_to_pay_table_id number
274 -- FWFA Changes
275 );
276
277
278 procedure get_lac_dtls
279 (p_pa_request_id in number,
280 p_sf52_rec out nocopy ghr_pa_requests%rowtype);
281
282 procedure create_lac_remarks
283 (p_pa_request_id in number,
284 p_new_pa_request_id in number);
285
286 procedure upd_ext_info_to_null(p_position_id in NUMBER, p_effective_DATE in DATE);
287
288 PROCEDURE assign_to_sf52_rec(
289 p_person_id in number,
290 p_first_name in varchar2,
291 p_last_name in varchar2,
292 p_middle_names in varchar2,
293 p_national_identifier in varchar2,
294 p_date_of_birth in date,
295 p_effective_date in date,
296 p_assignment_id in number,
297 p_tenure in varchar2,
298 p_step_or_rate in varchar2,
299 p_annuitant_indicator in varchar2,
300 p_pay_rate_determinant in varchar2,
301 p_work_schedule in varchar2,
302 p_part_time_hour in varchar2,
303 p_flsa_category in varchar2,
304 p_bargaining_unit_status in varchar2,
305 p_functional_class in varchar2,
306 p_supervisory_status in varchar2,
307 p_basic_pay in number,
308 p_to_locality_adj in number,
309 p_to_adj_basic_pay in number,
310 p_to_total_salary in number,
311 p_from_other_pay_amount in number,
312 p_to_other_pay_amount in number,
313 p_to_au_overtime in number,
314 p_to_availability_pay in number,
315 p_to_retention_allowance in number,
316 p_to_retention_allow_perce in number,
317 p_to_supervisory_differential in number,
318 p_to_supervisory_diff_perce in number,
319 p_to_staffing_differential in number,
320 p_duty_station_id in number,
321 p_duty_station_code in ghr_pa_requests.duty_station_code%type,
322 p_duty_station_desc in ghr_pa_requests.duty_station_desc%type,
323 -- FWFA Changes Bug#4444609
324 p_input_pay_rate_determinant in ghr_pa_requests.input_pay_rate_determinant%type,
325 p_from_pay_table_id in ghr_pa_requests.from_pay_table_identifier%type,
326 p_to_pay_table_id in ghr_pa_requests.to_pay_table_identifier%type,
327 -- FWFA Changes
328 p_lac_sf52_rec in ghr_pa_requests%rowtype,
329 p_sf52_rec out nocopy ghr_pa_requests%rowtype);
330
331 procedure check_select_flg_pos(p_position_id in number,
332 p_action in varchar2,
333 p_effective_date in date,
334 p_msl_id in number,
335 p_sel_flg in out NOCOPY varchar2);
336
337 procedure ins_upd_pos_extra_info
338 (p_position_id in number,p_effective_date in date,
339 p_sel_flag in varchar2, p_comment in varchar2,
340 p_msl_id in number);
341
342 PROCEDURE get_extra_info_comments_pos
343 (p_position_id in number,
344 p_effective_date in date,
345 p_sel_flag in out NOCOPY varchar2,
346 p_comments in out NOCOPY varchar2,
347 p_msl_id in out NOCOPY number);
348
349 procedure position_history_update (p_position_id IN hr_positions_f.position_id%type,
350 p_effective_date IN date,
351 p_table_id IN pay_user_tables.user_table_id%type,
352 p_upd_tableid IN pay_user_tables.user_table_id%type);
353
354 procedure pr (msg varchar2,par1 in varchar2 default null,
355 par2 in varchar2 default null);
356
357 --Bug # 11924741
358 function chk_pa_req_already_exists(p_person_id in number,
359 p_effective_date in date,
360 p_first_noa_code in varchar2,
361 p_action in varchar2)
362 return boolean;
363
364 END GHR_MLC_PKG;