1 package PAY_FR_GENERAL AUTHID CURRENT_USER as
2 /* $Header: pyfrgenr.pkh 115.31 2004/04/07 07:01:16 autiwari noship $ */
3
4 type band_tbl_type IS TABLE OF
5 number
6 INDEX BY BINARY_INTEGER;
7
8 type base_code_tbl_type IS TABLE OF
9 varchar2(4)
10 INDEX BY BINARY_INTEGER;
11
12 TYPE t_summary_deduction IS RECORD
13 (
14 base_type VARCHAR2(80)
15 , base NUMBER
16 , Contribution_usage_id_type VARCHAR2(1)
17 , contribution_usage_id NUMBER
18 , rate_type VARCHAR2(80)
19 , rate NUMBER
20 , contribution_code VARCHAR2(30)
21 , pay_value NUMBER
22 , retro VARCHAR2(1));
23
24 TYPE t_summary_deductions IS TABLE OF t_summary_deduction INDEX BY BINARY_INTEGER;
25
26 TYPE t_deduction_rate IS RECORD
27 (
28 contribution_usage_id NUMBER
29 , tax_unit_id NUMBER
30 , contribution_code VARCHAR2(30)
31 , rate NUMBER
32 , user_column_instance_id NUMBER
33 , risk_code VARCHAR2(30));
34
35 TYPE t_deduction_rates IS TABLE OF t_deduction_rate INDEX BY BINARY_INTEGER;
36
37
38 g_process_type varchar2(10);
39 g_monthly_hours number;
40 g_calendar_days_worked number;
41
42 g_band_table band_tbl_type;
43 g_base_code_table base_code_tbl_type;
44
45 -- Added 115.14
46 g_payroll_action_id number;
47 g_prev_start_date date;
48 g_prev_end_date date;
49
50 -- +********************************************************************+
51 -- | PUBLIC FUNCTIONS |
52 -- +********************************************************************+
53 ------------------------------------------------------------------------
54 -- Function GET_CONTRIBUTION_USAGE
55 ------------------------------------------------------------------------
56 function get_contribution_usage
57 (p_process_type in varchar2
58 ,p_element_name in varchar2
59 ,p_usage_type in varchar2
60 ,p_effective_date in date
61 ,p_business_group_id in number default null)
62 return pay_fr_contribution_usages%rowtype;
63
64 ------------------------------------------------------------------------
65 -- Function GET_RATE_VALUE
66 ------------------------------------------------------------------------
67 function get_rate_value(p_assignment_id in number
68 ,p_business_group_id in number default null
69 ,p_date_earned in date
70 ,p_tax_unit_id in number
71 ,p_element_name in varchar2
72 ,p_usage_type in varchar2
73 ,p_override_rate in number default null) return number;
74
75
76 ------------------------------------------------------------------------
77 -- Function GET_FORMULA_INFO
78 ------------------------------------------------------------------------
79 function get_formula_info
80 (p_formula_name in varchar2
81 ,p_effective_date in date
82 ,p_business_group_id in number default -1
83 ,p_effective_start_date out nocopy date
84 ) return number;
85
86 ------------------------------------------------------------------------
87 -- Function SUB_CONTRIB_CODE
88 ------------------------------------------------------------------------
89 function sub_contrib_code(p_contribution_type in varchar2
90 ,p_contribution_code in varchar2) return varchar2;
91
92
93 ------------------------------------------------------------------------
94 -- Function GET_PAYROLL_MESSAGE
95 ------------------------------------------------------------------------
96 function get_payroll_message
97 (p_message_name in varchar2
98 ,p_token1 in varchar2 default null
99 ,p_token2 in varchar2 default null
100 ,p_token3 in varchar2 default null) return varchar2;
101
102 ------------------------------------------------------------------------
103 -- Function INITIALIZE_PAYROLL
104 -- ver 115.15 Added params p_assignment_id and p_tax_unit_id
105 ------------------------------------------------------------------------
106 function initialize_payroll
107 (p_business_group_id in number
108 ,p_effective_date in date
109 ,p_assignment_id in number
110 ,p_tax_unit_id in number
111 ,p_process_type in varchar2
112 ,p_orig_entry_id in number
113 ,p_asg_action_id in number
114 ,p_payroll_id in number
115 ,P_ASG_HOURS in number
116 ,p_asg_frequency in varchar2 ) return number;
117
118 ------------------------------------------------------------------------
119 -- Function GET_URSSAF_BASE_CODE
120 -- 115.15 added params p_business_group_id and p_date_earned.
121 ------------------------------------------------------------------------
122 function get_urssaf_base_code(P_ASSIGNMENT_ID in number
123 ,P_BUSINESS_GROUP_ID in number
124 ,p_date_earned in date
125 ,P_ESTAB_FORMAT_NUMBER in VARCHAR2
126 ,P_ESTAB_WORK_ACCIDENT_ORDER_NO in VARCHAR2
127 ) return varchar2;
128
129 ------------------------------------------------------------------------
130 -- Function GET_ASSEDIC_BASE_CODE
131 -- 115.15 added params p_business_group_id and p_date_earned.
132 ------------------------------------------------------------------------
133
134 function get_assedic_base_code(p_assignment_id in number
135 ,P_BUSINESS_GROUP_ID in number
136 ,p_date_earned in date
137 ,P_ESTAB_ASSEDIC_ORDER_NUMBER in varchar2
138 ) return varchar2;
139
140 ------------------------------------------------------------------------
141 -- Function GET_PENSION_BASE_CODE
142 -- 115.15 added params p_business_group_id and p_date_earned.
143 ------------------------------------------------------------------------
144 function get_pension_base_code(p_establishment_id in number
145 ,p_assignment_id in number
146 ,P_BUSINESS_GROUP_ID in number
147 ,p_date_earned in date
148 ,p_emp_pension_provider_id in number
149 ,p_provider_type in varchar2
150 ,p_emp_pension_category in varchar2
151 ) return varchar2;
152
153 ------------------------------------------------------------------------
154 -- Function GET_CONTRIBUTION_BAND
155 -- 115.15 added params p_business_group_id
156 ------------------------------------------------------------------------
157 function get_contribution_band(
158 p_business_group_id in number
159 ,p_band_type in varchar2
160 ,p_ytd_ss_ceiling in number
161 ,p_ytd_base in number
162 ,p_ytd_band in number
163 ) return number;
164
165
166 ------------------------------------------------------------------------
167 -- Function GET_GMP_BAND
168 ------------------------------------------------------------------------
169 function get_gmp_band(p_ytd_gmp_ceiling in number,
170 p_ytd_gmp_band in number,
171 p_ytd_p3_band in number,
172 p_run_p3_band in number) return number;
173
174
175 ------------------------------------------------------------------------
176 -- Function GET_SALARY_TAX_BAND
177 -- 115.15 added params p_business_group_id
178 ------------------------------------------------------------------------
179 function get_salary_tax_band(p_business_group_id in number,
180 p_band_type in varchar2,
181 p_ptd_base in number,
182 p_ptd_band in number) return number;
183
184
185 ------------------------------------------------------------------------
186 -- Function WRITE_BASE_BAND
187 ------------------------------------------------------------------------
188 function WRITE_BASE_BANDS(p_name in varchar2
189 ,p_value in number) return number;
190
191 ------------------------------------------------------------------------
192 -- Function WRITE_CALENDAR_DAYS_WORKED
193 ------------------------------------------------------------------------
194 function WRITE_CALENDAR_DAYS_WORKED(p_calendar_days_worked in number) return number;
195
196 ------------------------------------------------------------------------
197 -- Function READ_CALENDAR_DAYS_WORKED
198 ------------------------------------------------------------------------
199 function READ_CALENDAR_DAYS_WORKED return number;
200 ------------------------------------------------------------------------
201 -- Function GET_DAYS_OVER_PENSION_LIMIT
202 ------------------------------------------------------------------------
203 function get_days_over_pension_limit(p_assignment_id in number
204 ,p_business_group_id in number
205 ,p_pay_period_start_date in date
206 ,p_pay_period_end_date in date
207 ,p_abs_days_limit in number) return number;
208
209 ------------------------------------------------------------------------
210 -- Function GET_CONTRIBUTION_INFO
211 -- 115.15 added params p_business_group_id and p_date_earned.
212 -- 115.27 p_contribution_code now IN OUT; value passed in used only if
213 -- contribution_code on the contribution usage is null (extra
214 -- validation is performed on the template code in this case)
215 -- Overloaded with no p_contribution_code param to cater for those
216 -- contributions with none (can't pass in null from formula)
217 ------------------------------------------------------------------------
218 function get_contribution_info( p_assignment_id in number
219 ,p_business_group_id in number
220 ,p_date_earned in date
221 ,p_tax_unit_id in number
222 ,p_element_name IN varchar2
223 ,p_usage_type IN varchar2
224 ,p_base OUT NOCOPY number
225 ,p_rate OUT NOCOPY number
226 ,p_contribution_code IN OUT NOCOPY varchar2
227 ,p_contribution_usage_id OUT NOCOPY number
228 ,p_override_rate in number default null) return number;
229
230 function get_contribution_info( p_assignment_id in number
231 ,p_business_group_id in number
232 ,p_date_earned in date
233 ,p_tax_unit_id in number
234 ,p_element_name IN varchar2
235 ,p_usage_type IN varchar2
236 ,p_base OUT NOCOPY number
237 ,p_rate OUT NOCOPY number
238 ,p_contribution_usage_id OUT NOCOPY number
239 ,p_override_rate in number default null) return number;
240
241 ------------------------------------------------------------------------
242 -- Function GET_WORK_ACCIDENT_CONTRIBUTION
243 -- 115.15 added params p_assignment_id, p_business_group_id and p_date_earned.
244 ------------------------------------------------------------------------
245 function GET_WORK_ACCIDENT_CONTRIBUTION(P_ASSIGNMENT_ID in number
246 ,P_BUSINESS_GROUP_ID in number
247 ,P_DATE_EARNED in date
248 ,P_TAX_UNIT_ID in number
249 ,P_ELEMENT_NAME IN varchar2
250 ,P_USAGE_TYPE IN varchar2
251 ,P_RISK_CODE in Varchar2
252 ,P_BASE out nocopy number
253 ,P_RATE out nocopy number
254 ,P_RATE_TYPE out nocopy varchar2
255 ,P_CONTRIBUTION_CODE out nocopy varchar2
256 ,P_CONTRIBUTION_USAGE_ID out nocopy number
257 ,P_REDUCTION_PERCENT in number default null) return number;
258
259 ------------------------------------------------------------------------
260 -- Function GET_TRANSPORT_TAX_CONTRIBUTION
261 -- 115.15 added params p_business_group_id and p_date_earned.
262 ------------------------------------------------------------------------
263 function GET_TRANSPORT_TAX_CONTRIBUTION(P_ASSIGNMENT_ID in number
264 ,P_BUSINESS_GROUP_ID in number
265 ,p_date_earned in date
266 ,P_TAX_UNIT_ID in number
267 ,P_ELEMENT_NAME in varchar2
268 ,P_USAGE_TYPE IN varchar2
269 ,P_TRANSPORT_TAX_REGION in varchar2
270 ,P_REDUCTION in number
274 ,P_CONTRIBUTION_USAGE_ID out nocopy number) return number;
271 ,P_BASE out nocopy number
272 ,P_RATE out nocopy number
273 ,P_CONTRIBUTION_CODE out nocopy varchar2
275
276 ------------------------------------------------------------------------
277 -- Generic Function GET_FIXED_VALUE_CONTRIBUTION (replaces get_annual_apec_contribution)
278 ------------------------------------------------------------------------
279 function get_fixed_value_contribution(P_BUSINESS_GROUP_ID in number
280 ,p_date_earned in date
281 ,p_element_name IN varchar2
282 ,p_usage_type IN varchar2
283 ,p_contribution_code OUT NOCOPY varchar2
284 ,p_contribution_usage_id OUT NOCOPY number) return number;
285
286 ------------------------------------------------------------------------
287 -- Function GET_REDUCED_CONTRIBUTION
288 -- 115.15 added params p_business_group_id and p_date_earned.
289 -- 115.27 p_contribution_code now IN OUT; value passed in used only if
290 -- contribution_code on the contribution usage is null (extra
291 -- validation is performed on the template code in this case)
292 -- Overloaded with no p_contribution_code param to cater for those
293 -- contributions with none (can't pass in null from formula)
294 ------------------------------------------------------------------------
295 function get_reduced_contribution(P_BUSINESS_GROUP_ID in number
296 ,p_date_earned in date
297 ,P_ELEMENT_NAME IN varchar2
298 ,P_USAGE_TYPE IN varchar2
299 ,p_reduction in number
300 ,P_BASE out nocopy number
301 ,P_RATE out nocopy number
302 ,P_CONTRIBUTION_CODE in out nocopy varchar2
303 ,P_CONTRIBUTION_USAGE_ID out nocopy number)
304 return number;
305
306 function get_reduced_contribution(P_BUSINESS_GROUP_ID in number
307 ,p_date_earned in date
308 ,P_ELEMENT_NAME IN varchar2
309 ,P_USAGE_TYPE IN varchar2
310 ,p_reduction in number
311 ,P_BASE out nocopy number
312 ,P_RATE out nocopy number
313 ,P_CONTRIBUTION_USAGE_ID out nocopy number)
314 return number;
315
316 ------------------------------------------------------------------------
317 -- Function CONVERT_HOURS
318 ------------------------------------------------------------------------
319 function convert_hours(p_effective_date in date
320 ,p_business_group_id in number
321 ,p_assignment_id in number
322 ,p_hours in number
323 ,p_from_freq_code in varchar2
324 ,p_to_freq_code in varchar2) return number;
325
326 ------------------------------------------------------------------------
327 -- Function GET_PAY_RATE
328 ------------------------------------------------------------------------
329 function get_pay_rate(p_assignment_id in number
330 ,p_business_group_id in number
331 ,p_effective_date in date
332 ,p_formula varchar2 default 'FR_USER_HOURLY_RATE'
333 ,p_parameter_list varchar2 default null) return number;
334
335 ------------------------------------------------------------------------
336 -- Function GET_MONTHLY_HOURS
337 ------------------------------------------------------------------------
338 function get_monthly_hours return number;
339
340 ------------------------------------------------------------------------
341 -- Function GET_PREV_START_END
342 ------------------------------------------------------------------------
343 function get_prev_start_end (p_payroll_action_id in number
344 ,p_start_date in out nocopy date
345 ,p_end_date in out nocopy date) return number;
346
347 ------------------------------------------------------------------------
348 -- Function SUBSTITUTE_CODE
349 ------------------------------------------------------------------------
350 function substitute_code(p_contribution_code in varchar2) return varchar2;
351
352 ------------------------------------------------------------------------
353 -- Function FORMAT_NAME
354 ------------------------------------------------------------------------
355 Function format_name(p_employee_id in number) RETURN VARCHAR2;
356
357 -----------------------------------------------------------------------
358 -- Function FR_ROLLING_BALANCE
359 ----------------------------------------------------------------------
360 Function fr_rolling_balance (p_assignment_id in number,
361 p_balance_name in varchar2,
362 p_balance_start_date in date,
363 p_balance_end_date in date) return number;
364 -----------------------------------------------------------------------
365 -- Function GET_BASE_NAME
366 ----------------------------------------------------------------------
367 function get_base_name (
368 p_business_group_id in number
369 ,p_group_code in varchar2)
370 return varchar2;
371 -----------------------------------------------------------------------
372 -- Function GET_BASE_NAME_CU
373 ----------------------------------------------------------------------
374 function get_base_name_CU (
375 p_business_group_id in number
376 ,p_cu_id in number)
377 return varchar2;
378 -----------------------------------------------------------------------
379 -- Function GET_GROUP_CODE
380 ----------------------------------------------------------------------
381 function get_group_code(
382 p_cu_id in number)
383 return varchar2;
384 -----------------------------------------------------------------------
385 -- Function GET_SUMMARY_DEDUCTION
386 ----------------------------------------------------------------------
387 FUNCTION get_summary_deduction
388 (
389 p_rate OUT NOCOPY NUMBER
390 , p_base OUT NOCOPY NUMBER
391 , p_contribution_code OUT NOCOPY VARCHAR2
392 , p_contribution_usage_id OUT NOCOPY NUMBER
393 , p_pay_value OUT NOCOPY NUMBER
394 ) return varchar2;
395
396 -----------------------------------------------------------------------
397 -- Function MAINTAIN_SUMMARY_DEDUCTION
398 ----------------------------------------------------------------------
399 PROCEDURE maintain_summary_deduction
400 (
401 p_rate IN NUMBER
402 , p_base_type IN VARCHAR2
403 , p_base IN NUMBER
404 , p_contribution_code IN VARCHAR2
405 , p_contribution_usage_id IN NUMBER
406 , p_rate_type IN VARCHAR2
407 , p_pay_value IN NUMBER
408 , p_rate_category IN VARCHAR2
409 , p_user_column_instance_id IN NUMBER
410 , p_code_rate_id IN NUMBER
411 , p_element_name IN VARCHAR2
412 ) ;
413
414 -----------------------------------------------------------------------
415 -- Function MAINTAIN_RATE_CACHE
416 ----------------------------------------------------------------------
417 PROCEDURE maintain_rate_cache
418 (
419 p_contribution_usage_id IN NUMBER
420 , p_tax_unit_id IN NUMBER
421 , p_contribution_code IN VARCHAR2
425 );
422 , p_rate_value IN NUMBER
423 , p_user_column_instance_id IN NUMBER
424 , p_risk_code IN VARCHAR2
426
427 -----------------------------------------------------------------------
428 -- Function GET_CACHED_RATE
429 ----------------------------------------------------------------------
430 FUNCTION GET_CACHED_RATE
431 (
432 p_assignment_id IN NUMBER
433 , p_contribution_usage_id IN NUMBER
434 , p_tax_unit_id IN NUMBER
435 , p_contribution_code IN OUT nocopy VARCHAR2
436 , p_user_column_instance_id IN OUT nocopy NUMBER
437 , p_risk_code IN OUT nocopy VARCHAR2
438 ) return number;
439
440 -----------------------------------------------------------------------
441 -- Function GET_TABLE_RATE
442 ----------------------------------------------------------------------
443 Function get_table_rate (p_bus_group_id in number,
444 p_table_name in varchar2,
445 p_row_value in varchar2,
446 p_user_row_id out NOCOPY number,
447 p_user_column_instance_id out NOCOPY number )
448 return number;
449
450 -----------------------------------------------------------------------
451 -- Function CHECK_SUMMARY_DEDUCTION_CLEAR
452 ----------------------------------------------------------------------
453 FUNCTION count_summary_deductions return number;
454 -----------------------------------------------------------------------
455 END PAY_FR_GENERAL;
[Home] [Help]