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