1 PACKAGE ghr_pay_calc AUTHID CURRENT_USER AS
2 /* $Header: ghpaycal.pkh 120.14 2011/11/09 14:19:14 utokachi ship $ */
3
4
5 --GMIT Pay calcultions.
6 g_gm_unadjd_basic_pay NUMBER;
7 gm_unadjusted_pay_flg VARCHAR2(1);
8 -- FWFA Changes Bug#4444609
9 g_pay_table_upd_flag BOOLEAN;
10 -- GL Change Flag
11 g_gl_upd_flag BOOLEAN;
12 -- Bug#4680403
13 g_fwfa_pay_calc_flag BOOLEAN;
14 g_fw_equiv_pay_plan BOOLEAN; -- Will be TRUE for FW equivalent employees with PRD 6, E or F
15 g_out_to_pay_plan ghr_pay_plans.pay_plan%TYPE;
16 l_spl491_table_name CONSTANT varchar2(80) := '0491 Oracle Federal Special Rate Pay Table (GS) No. 0491';
17 -- FWFA Changes
18
19 ---- Bug# 13103038 PRD2 Modification
20 prd2_process BOOLEAN;
21
22 -- Global constants: (NB: Probably should have begun with with g - too late now as I assume people have used it!!1)
23 l_standard_table_name CONSTANT varchar2(80) := '0000 Oracle Federal Standard Pay Table (AL, ES, EX, GS, GG) No. 0000';
24
25 --
26 -- This exception is used to indicate that we do not know how to calculate pay given the set of parameters
27 -- NOTE: It is not used if we should be able to calculate but just we had an error
28 unable_to_calculate EXCEPTION;
29 pay_calc_message EXCEPTION;
30 open_pay_range_mesg EXCEPTION;
31
32 --
33 -- want to see if this will work!
34 --
35 form_item_name VARCHAR2(61); -- This will be the block_name.item_name
36 --
37 FUNCTION get_form_item_name
38 RETURN VARCHAR2;
39 --
40 PROCEDURE set_form_item_name(p_value IN VARCHAR2);
41 --
42 -- This record structure will keep all the in parameters that were passed to the main pay calc process
43 TYPE pay_calc_in_rec_type IS RECORD
44 (person_id per_people_f.person_id%TYPE
45 ,position_id hr_all_positions_f.position_id%TYPE
46 ,noa_family_code ghr_families.noa_family_code%TYPE
47 ,noa_code ghr_nature_of_actions.code%TYPE
48 ,second_noa_code ghr_nature_of_actions.code%TYPE
49 --GPPA Update46
50 ,first_action_la_code1 ghr_pa_requests.first_action_la_code1%TYPE
51 ,effective_date DATE
52 ,pay_rate_determinant VARCHAR2(30)
53 ,pay_plan VARCHAR2(30)
54 ,grade_or_level VARCHAR2(60)
55 ,step_or_rate VARCHAR2(30)
56 ,pay_basis VARCHAR2(30)
57 ,user_table_id NUMBER
58 ,duty_station_id NUMBER
59 ,auo_premium_pay_indicator VARCHAR2(30)
60 ,ap_premium_pay_indicator VARCHAR2(30)
61 ,retention_allowance NUMBER
62 ,to_ret_allow_percentage NUMBER(15,2)
63 ,supervisory_differential NUMBER
64 ,staffing_differential NUMBER
65 ,current_basic_pay NUMBER
66 ,current_adj_basic_pay NUMBER
67 ,current_step_or_rate VARCHAR2(30)
68 ,pa_request_id NUMBER
69 ,open_range_out_basic_pay NUMBER
70 -- Bug#5482191 Added personnel_system_ind.
71 ,personnel_system_indicator VARCHAR2(30)
72 --Bug #5132113 added Locality adjustment
73 ,open_out_locality_adj NUMBER
74 );
75 -- FWFA Changes. Added new columns to the OUT record type
76 TYPE pay_calc_out_rec_type IS RECORD
77 (basic_pay NUMBER
78 ,locality_adj NUMBER
79 ,adj_basic_pay NUMBER
80 ,total_salary NUMBER
81 ,other_pay_amount NUMBER
82 ,retention_allowance NUMBER
83 ,ret_allow_perc_out NUMBER
84 ,au_overtime NUMBER
85 ,availability_pay NUMBER
86 ,out_step_or_rate VARCHAR2(30)
87 ,out_pay_rate_determinant VARCHAR2(30)
88 ,PT_eff_start_date DATE
89 ,open_basicpay_field BOOLEAN
90 ,open_pay_fields BOOLEAN
91 -- FWFA Changes
92 --Bug#5132113
93 ,open_localityadj_field BOOLEAN
94 ,calculation_pay_table_id NUMBER
95 ,pay_table_id NUMBER
96 ,out_to_grade_id NUMBER
97 ,out_to_pay_plan VARCHAR2(2)
98 ,out_to_grade_or_level VARCHAR2(30)
99 );
100 --
101 TYPE retained_grade_rec_type IS RECORD
102 (person_extra_info_id NUMBER(15)
103 -- Bug#4423679 Added date_from,date_to columns in the record.
104 ,date_from DATE
105 ,date_to DATE
106 ,pay_plan VARCHAR2(30)
107 ,grade_or_level VARCHAR2(60)
108 ,step_or_rate VARCHAR2(30)
109 ,pay_basis VARCHAR2(30)
110 ,user_table_id NUMBER
111 ,locality_percent NUMBER
112 ,temp_step VARCHAR2(30)
113 );
114 --
115 FUNCTION get_default_prd (p_position_id IN NUMBER
116 ,p_effective_date IN DATE)
117 RETURN VARCHAR2;
118 --
119 -- This function returns TRUE if Pay Calc is going to set the step so the form knows to
120 -- grey it out, this is especially hard to work out after it has been routed!!
121 FUNCTION pay_calc_sets_step(p_first_noa_code IN VARCHAR2
122 ,p_second_noa_code IN VARCHAR2
123 ,p_pay_plan IN VARCHAR2
124 ,p_prd IN VARCHAR2
125 ,p_pa_request_id IN NUMBER)
126 RETURN BOOLEAN;
127 --
128 FUNCTION convert_amount (p_amount IN NUMBER
129 ,p_in_pay_basis IN VARCHAR2
130 ,p_out_pay_basis IN VARCHAR2)
131 RETURN NUMBER;
132 --
133 FUNCTION get_lpa_percentage (p_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE
134 ,p_effective_date DATE)
135 RETURN NUMBER;
136 pragma restrict_references (get_lpa_percentage, WNDS, WNPS);
137 --
138 -- Bug#5482191
139 FUNCTION get_leo_lpa_percentage (p_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE
140 ,p_effective_date DATE)
141 RETURN NUMBER;
142 --
143 --
144
145 FUNCTION get_user_table_name (p_user_table_id IN NUMBER)
146 RETURN VARCHAR2;
147 --
148 FUNCTION get_user_table_id (p_position_id IN hr_all_positions_f.position_id%TYPE
149 ,p_effective_date IN date)
150 RETURN NUMBER;
151 --
152 FUNCTION get_open_pay_range (p_position_id IN hr_all_positions_f.position_id%TYPE
153 ,p_person_id IN per_all_people_f.person_id%type
154 ,p_prd IN ghr_pa_requests.pay_rate_determinant%type
155 ,p_pa_request_id IN ghr_pa_requests.pa_request_id%type
156 ,p_effective_date IN date)
157
158 RETURN BOOLEAN;
159 --
160 PROCEDURE get_pay_table_value (p_user_table_id IN NUMBER
161 ,p_pay_plan IN VARCHAR2
162 ,p_grade_or_level IN VARCHAR2
163 ,p_step_or_rate IN VARCHAR2
164 ,p_effective_date IN DATE
165 ,p_PT_value OUT NOCOPY NUMBER
166 ,p_PT_eff_start_date OUT NOCOPY DATE
167 ,p_PT_eff_end_date OUT NOCOPY DATE);
168 --
169 --
170 FUNCTION get_standard_pay_table_value (p_pay_plan IN VARCHAR2
171 ,p_grade_or_level IN VARCHAR2
172 ,p_step_or_rate IN VARCHAR2
173 ,p_effective_date IN DATE)
174 RETURN NUMBER;
175
176 -- Bug 3021003
177 -- This procedure determines whether any intervening Retained grade exists or not.
178 PROCEDURE is_retained_ia(
179 p_person_id IN NUMBER,
180 p_effective_date IN DATE,
181 p_retained_pay_plan IN OUT NOCOPY VARCHAR2,
182 p_retained_grade IN OUT NOCOPY VARCHAR2,
183 p_retained_step_or_rate IN OUT NOCOPY VARCHAR2,
184 p_temp_step IN OUT NOCOPY VARCHAR2,
185 p_return_flag OUT NOCOPY BOOLEAN);
186 --
187 --
188 -- This function is used to determine if the given position is a 'LEO'
189 -- The definition of a LEO is the 'LEO Position Indicator' on information type 'GHR_US_POS_GRP2'
190 -- is 1 or 2
191 -- Returns TRUE if it is a LEO Position
192 FUNCTION LEO_position (p_prd IN VARCHAR2
193 ,p_position_id IN NUMBER
194 ,p_retained_user_table_id IN NUMBER
195 ,p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
196 ,p_effective_date IN DATE)
197 RETURN BOOLEAN;
198 --
199 FUNCTION get_ppi_amount (p_ppi_code IN VARCHAR2
200 ,p_amount IN NUMBER
201 ,p_pay_basis IN VARCHAR2)
202 RETURN NUMBER;
203 --
204
205 --Bug# 5132113 added new parameters p_open_out_locality_adj,p_open_localityadj_field
206 PROCEDURE main_pay_calc (p_person_id IN per_people_f.person_id%TYPE
207 ,p_position_id IN hr_all_positions_f.position_id%TYPE
208 ,p_noa_family_code IN ghr_families.noa_family_code%TYPE
209 ,p_noa_code IN ghr_nature_of_actions.code%TYPE
210 ,p_second_noa_code IN ghr_nature_of_actions.code%TYPE
211 ,p_first_action_la_code1 IN ghr_pa_requests.first_action_la_code1%TYPE
212 ,p_effective_date IN DATE
213 ,p_pay_rate_determinant IN VARCHAR2
214 ,p_pay_plan IN VARCHAR2
215 ,p_grade_or_level IN VARCHAR2
216 ,p_step_or_rate IN VARCHAR2
217 ,p_pay_basis IN VARCHAR2
218 ,p_user_table_id IN NUMBER
219 ,p_duty_station_id IN NUMBER
220 ,p_auo_premium_pay_indicator IN VARCHAR2
221 ,p_ap_premium_pay_indicator IN VARCHAR2
222 ,p_retention_allowance IN NUMBER
223 ,p_to_ret_allow_percentage IN NUMBER
224 ,p_supervisory_differential IN NUMBER
225 ,p_staffing_differential IN NUMBER
226 ,p_current_basic_pay IN NUMBER
227 ,p_current_adj_basic_pay IN NUMBER
228 ,p_current_step_or_rate IN VARCHAR2
229 ,p_pa_request_id IN NUMBER
230 ,p_open_range_out_basic_pay IN NUMBER DEFAULT NULL
231 ,p_open_out_locality_adj IN NUMBER DEFAULT NULL
232 ,p_basic_pay OUT NOCOPY NUMBER
233 ,p_locality_adj OUT NOCOPY NUMBER
234 ,p_adj_basic_pay OUT NOCOPY NUMBER
235 ,p_total_salary OUT NOCOPY NUMBER
236 ,p_other_pay_amount OUT NOCOPY NUMBER
237 ,p_to_retention_allowance OUT NOCOPY NUMBER
238 ,p_ret_allow_perc_out OUT NOCOPY NUMBER
239 ,p_au_overtime OUT NOCOPY NUMBER
240 ,p_availability_pay OUT NOCOPY NUMBER
241 -- FWFA Changes
242 ,p_calc_pay_table_id OUT NOCOPY NUMBER
243 ,p_pay_table_id OUT NOCOPY NUMBER
244 -- FWFA Changes
245 ,p_out_step_or_rate OUT NOCOPY VARCHAR2
246 ,p_out_pay_rate_determinant OUT NOCOPY VARCHAR2
247 ,p_out_to_grade_id OUT NOCOPY NUMBER
248 ,p_out_to_pay_plan OUT NOCOPY VARCHAR2
249 ,p_out_to_grade_or_level OUT NOCOPY VARCHAR2
250 ,p_PT_eff_start_date OUT NOCOPY DATE
251 ,p_open_basicpay_field OUT NOCOPY BOOLEAN
252 ,p_open_pay_fields OUT NOCOPY BOOLEAN
253 ,p_message_set OUT NOCOPY BOOLEAN
254 ,p_calculated OUT NOCOPY BOOLEAN
255 ,p_open_localityadj_field OUT NOCOPY BOOLEAN
256 );
257 --
258 PROCEDURE sql_main_pay_calc (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
259 ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
260 ,p_message_set OUT NOCOPY BOOLEAN
261 ,p_calculated OUT NOCOPY BOOLEAN
262 );
263 --AVR
264 PROCEDURE get_locality_adj_894_PRDM_GS
265 (p_user_table_id IN NUMBER
266 ,p_pay_plan IN VARCHAR2
267 ,p_grade_or_level IN VARCHAR2
268 ,p_step_or_rate IN VARCHAR2
269 ,p_effective_date IN DATE
270 ,p_cur_adj_basic_pay IN NUMBER
271 ,p_new_basic_pay IN NUMBER
272 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
273 ,p_new_locality_adj OUT NOCOPY NUMBER);
274
275 PROCEDURE get_locality_adj_894_PRDM_GM
276 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
277 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
278 ,p_new_std_relative_rate OUT NOCOPY NUMBER
279 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
280 ,p_new_locality_adj OUT NOCOPY NUMBER);
281 --AVR
282 PROCEDURE get_open_pay_table_values (p_user_table_id IN NUMBER
283 ,p_pay_plan IN VARCHAR2
284 ,p_grade_or_level IN VARCHAR2
285 ,p_effective_date IN DATE
286 ,p_row_high OUT NOCOPY NUMBER
287 ,p_row_low OUT NOCOPY NUMBER);
288
289 FUNCTION get_pos_pay_basis (p_position_id IN per_positions.position_id%TYPE
290 ,p_effective_date IN date)
291 RETURN VARCHAR2;
292
293 FUNCTION get_pay_basis(
294 p_effective_date IN ghr_pa_requests.effective_date%type,
295 p_pa_request_id IN ghr_pa_requests.pa_request_id%type,
296 p_person_id IN ghr_pa_requests.person_id%type
297 ) RETURN ghr_pa_requests.from_pay_basis%type;
298
299 PROCEDURE get_locality_894_itpay
300 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
301 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
302 ,p_new_basic_pay IN NUMBER
303 ,p_GM_unadjusted_rate OUT NOCOPY NUMBER
304 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
305 ,p_new_locality_adj OUT NOCOPY NUMBER);
306
307 PROCEDURE get_locality_892_itpay
308 (p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
309 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type
310 ,p_new_basic_pay IN NUMBER
311 ,p_new_adj_basic_pay OUT NOCOPY NUMBER
312 ,p_new_locality_adj OUT NOCOPY NUMBER);
313
314 -- FWFA Changes. Created procedures get_special_pay_table_value, special_rate_pay_calc
315 FUNCTION fwfa_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
316 ,p_retained_grade IN ghr_pay_calc.retained_grade_rec_type)
317 RETURN BOOLEAN;
318
319 PROCEDURE get_special_pay_table_value (p_pay_plan IN VARCHAR2
320 ,p_grade_or_level IN VARCHAR2
321 ,p_step_or_rate IN VARCHAR2
322 ,p_user_table_id IN NUMBER
323 ,p_effective_date IN DATE
324 ,p_pt_value OUT NOCOPY NUMBER
325 ,p_PT_eff_start_date OUT NOCOPY DATE
326 ,p_PT_eff_end_date OUT NOCOPY DATE
327 ,p_pp_grd_exists OUT NOCOPY BOOLEAN);
328
329 PROCEDURE special_rate_pay_calc(p_pay_calc_data IN ghr_pay_calc.pay_calc_in_rec_type
330 ,p_pay_calc_out_data OUT NOCOPY ghr_pay_calc.pay_calc_out_rec_type
331 ,p_retained_grade IN OUT NOCOPY ghr_pay_calc.retained_grade_rec_type);
332 -- Bug# 4748927 Begin
333 PROCEDURE award_amount_calc (
334 p_position_id IN NUMBER
335 ,p_pay_plan IN VARCHAR2
336 ,p_award_percentage IN NUMBER
337 ,p_user_table_id IN NUMBER
338 ,p_grade_or_level IN VARCHAR2
339 ,p_effective_date IN DATE
340 ,p_basic_pay IN NUMBER
341 ,p_adj_basic_pay IN NUMBER
342 ,p_duty_station_id IN ghr_duty_stations_f.duty_station_id%TYPE
343 ,p_prd IN ghr_pa_requests.pay_rate_determinant%type
344 ,p_pay_basis IN VARCHAR2
345 ,p_person_id IN per_people_f.person_id%TYPE
346 ,p_award_amount OUT NOCOPY NUMBER
347 ,p_award_salary OUT NOCOPY NUMBER
348 );
349 -- Bug# 4748927 end
350 END ghr_pay_calc;
351
[Home] [Help]