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