1 package pay_us_reporting_utils_pkg as
2 /* $Header: pyusmref.pkh 120.6.12010000.1 2008/07/27 23:53:36 appldev ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_reporting_utils_pkg
10
11 Purpose
12 The purpose of this package is to support the generation of magnetic tape W2
13 reports for US legilsative requirements incorporating magtape resilience
14 and the new end-of-year design. New Functions will support the Year end
15 reporting in MMREF format initially and will be extended to have more
16 format.
17
18 Notes
19
20 History
21 24-JUL-01 fusman 40.0 created
22 02-Nov-01 fusman 40.1 Added two new functions
23 Character_check and Formula_check.
24 26-Nov-01 fusman 115.3 Added dbdrv command.
25 10-feb-02 djoshi 115.4 changed dbdrv command
26 03-Sep-02 ppanda 115.6 function named get_file_name added to
27 fix Bug # 2397313
28 19-Jan-03 ppanda 115.7 A new function added to get live balance
29 This function currently used for MA W2 Mag
30 Function name is get_live_ee_contrib
31 10-Nov-04 meshah 115.8 added function get_ff_archive_value
32 07-Nov-05 sudedas 115.11 Added 2 optional input parameters to
33 format_record function.
34 17-Feb-06 sudedas 115.12 Added Functions Get_Employee_Count and Get_Total_Wages
35 30-May-06 sackumar 115.113 Added Functions Get_Employee_Count_Monthwise and Get_Wages.
36 Bug 5089997.
37 17-Aug-06 sudedas 115.14 Added 2 Optional Input Parameters to
38 Format_Record : p_input_43, p_input_44
39 (Bug# 5256745)
40 22-Nov-06 sudedas 115.15 Added 2 Optional Input Parameters to
41 Get_Total_Wages (p_report_type, p_balance_name)
42 to use this Function in W2 as well (Bug# 5640748)
43 ============================================================================*/
44 -- Global Variable
45
46 g_number NUMBER;
47
48 -- Used by Magnetic W2 (MMREF format).
49 /* ============================================================================ */
50 /* Function Name : calculate_balance
51 Purpose : Purpose of this function is is to provide calculation
52 of Derived balnces that are used in the formula
53 Error checking
54
55 Special Note :
56
57
58 */
59
60 FUNCTION calculate_balance(
61 p_effective_date IN varchar2,
62 p_balance_name IN varchar2,
63 p_report_type IN varchar2,
64 p_format IN varchar2,
65 p_report_qualifier IN varchar2,
66 p_record_name IN varchar2,
67 p_input_1 IN varchar2,
68 p_input_2 IN varchar2,
69 p_input_3 IN varchar2,
70 p_input_4 IN varchar2,
71 p_input_5 IN varchar2,
72 p_input_6 IN varchar2,
73 p_input_7 IN varchar2,
74 p_input_8 IN varchar2,
75 p_input_9 IN varchar2,
76 p_input_10 IN varchar2,
77 p_input_11 IN varchar2,
78 p_input_12 IN varchar2,
79 p_input_13 IN varchar2,
80 p_input_14 IN varchar2,
81 p_input_15 IN varchar2,
82 p_validate IN varchar2,
83 p_exclude_from_output out nocopy varchar2,
84 sp_out_1 out nocopy varchar2,
85 sp_out_2 out nocopy varchar2,
86 sp_out_3 out nocopy varchar2,
87 sp_out_4 out nocopy varchar2,
88 sp_out_5 out nocopy varchar2)
89 RETURN number;
90
91 -- Used by Magnetic W2 (MMREF format).
92 /* ============================================================================ */
93 /* Function Name : calculate_wages
94 Purpose : Purpose of this function is is to provide calculation
95 of wages that are used in the formula
96 Error checking
97
98 Special Note :
99
100
101 */
102
103
104 FUNCTION calculate_wages(
105 p_effective_date IN varchar2,
106 p_wage_name IN varchar2,
107 p_report_type IN varchar2,
108 p_format IN varchar2,
109 p_report_qualifier IN varchar2,
110 p_record_name IN varchar2,
111 p_input_1 IN varchar2,
112 p_gross IN varchar2,
113 p_subject IN varchar2,
114 p_subject_nw IN varchar2,
115 p_pretax_redns IN varchar2,
116 p_taxable IN varchar2,
117 p_validate IN varchar2,
118 p_exclude_from_output out nocopy varchar2,
119 sp_exempt out nocopy varchar2,
120 sp_reduced_sub out nocopy varchar2,
121 sp_excess out nocopy varchar2,
122 sp_reduced_sub_wh out nocopy varchar2,
123 sp_out_1 out nocopy varchar2)
124 RETURN number;
125
126
127
128
129 /*
130 Name : get_item_data
131
132 Purpors : Purpose of this function is to get live
133 data from the System.This can be replace
134 Call to live database items where error
135 chekcing is required
136 Error checking
137
138 Special Note :
139
140
141 */
142
143 FUNCTION get_item_data(
144 p_assignment_id number, -- context
145 p_date_earned date, -- context
146 p_tax_unit_id number,-- context
147 p_effective_date IN varchar2,
148 p_item_name IN varchar2,
149 p_report_type IN varchar2,
150 p_format IN varchar2,
151 p_report_qualifier IN varchar2,
152 p_record_name IN varchar2,
153 p_input_1 IN varchar2,
154 p_input_2 IN varchar2,
155 p_input_3 IN varchar2,
156 p_input_4 IN varchar2,
157 p_input_5 IN varchar2,
158 p_validate IN varchar2,
159 p_exclude_from_output OUT nocopy varchar2,
160 sp_out_1 OUT nocopy varchar2,
161 sp_out_2 OUT nocopy varchar2,
162 sp_out_3 OUT nocopy varchar2,
163 sp_out_4 OUT nocopy varchar2,
164 sp_out_5 OUT nocopy varchar2,
165 sp_out_6 OUT nocopy varchar2,
166 sp_out_7 OUT nocopy varchar2,
167 sp_out_8 OUT nocopy varchar2,
168 sp_out_9 OUT nocopy varchar2,
169 sp_out_10 OUT nocopy varchar2
170 )
171 RETURN varchar2;
172
173 /* Function Name : print_record_header
174 Purpose : Function will return the String for header
175 or title line for the Table or table heading
176 related to record for printing in audit files
177
178 Error checking
179
180 Special Note :
181
182
183 */
184
185 FUNCTION print_record_header(
186 p_effective_date IN varchar2,
187 p_report_type IN varchar2,
188 p_format IN varchar2,
189 p_report_qualifier IN varchar2,
190 p_record_name IN varchar2,
191 p_input_1 IN varchar2,
192 p_input_2 IN varchar2,
193 p_input_3 IN varchar2,
194 p_input_4 IN varchar2,
195 p_input_5 IN varchar2,
196 p_validate IN varchar2,
197 p_exclude_from_output OUT nocopy varchar2,
198 sp_out_1 OUT nocopy varchar2,
199 sp_out_2 OUT nocopy varchar2,
200 sp_out_3 OUT nocopy varchar2,
201 sp_out_4 OUT nocopy varchar2,
202 sp_out_5 OUT nocopy varchar2,
203 sp_out_6 OUT nocopy varchar2,
204 sp_out_7 OUT nocopy varchar2,
205 sp_out_8 OUT nocopy varchar2,
206 sp_out_9 OUT nocopy varchar2,
207 sp_out_10 OUT nocopy varchar2
208 )
209 RETURN varchar2;
210
211
212
213 /*
214 Name : data_validation
215 : Function will validate data for any
216 database items or can change the data
217 based on the parameters. It should
218 be capable of having special data
219 validation and change function.
220
221 Error checking
222
223 Special Note :
224
225 */
226
227 FUNCTION data_validation(
228 p_effective_date IN varchar2,
229 p_report_type IN varchar2,
230 p_format IN varchar2,
231 p_report_qualifier IN varchar2,
232 p_record_name IN varchar2,
233 p_input_1 IN varchar2,
234 p_input_2 IN varchar2,
235 p_input_3 IN varchar2,
236 p_input_4 IN varchar2,
237 p_input_5 IN varchar2,
238 p_validate IN varchar2,
239 p_exclude_from_output OUT nocopy varchar2,
240 sp_out_1 OUT nocopy varchar2,
241 sp_out_2 OUT nocopy varchar2
242 )
243 return varchar2;
244
245
246
247 /* Function Name : format_record
248 Purpose : Function will return formating of the record
249 there will be one function per record
250 Error checking
251
252 Special Note :
253
254
255 */
256
257
258 FUNCTION format_record(
259 p_effective_date IN varchar2,
260 p_report_type IN varchar2,
261 p_format IN varchar2,
262 p_report_qualifier IN varchar2,
263 p_record_name IN varchar2,
264 p_input_1 IN varchar2,
265 p_input_2 IN varchar2,
266 p_input_3 IN varchar2,
267 p_input_4 IN varchar2,
268 p_input_5 IN varchar2,
269 p_input_6 IN varchar2,
270 p_input_7 IN varchar2,
271 p_input_8 IN varchar2,
272 p_input_9 IN varchar2,
273 p_input_10 IN varchar2,
274 p_input_11 IN varchar2,
275 p_input_12 IN varchar2,
276 p_input_13 IN varchar2,
277 p_input_14 IN varchar2,
278 p_input_15 IN varchar2,
279 p_input_16 IN varchar2,
280 p_input_17 IN varchar2,
281 p_input_18 IN varchar2,
282 p_input_19 IN varchar2,
283 p_input_20 IN varchar2,
284 p_input_21 IN varchar2,
285 p_input_22 IN varchar2,
286 p_input_23 IN varchar2,
287 p_input_24 IN varchar2,
288 p_input_25 IN varchar2,
289 p_input_26 IN varchar2,
290 p_input_27 IN varchar2,
291 p_input_28 IN varchar2,
292 p_input_29 IN varchar2,
293 p_input_30 IN varchar2,
294 p_input_31 IN varchar2,
295 p_input_32 IN varchar2,
296 p_input_33 IN varchar2,
297 p_input_34 IN varchar2,
298 p_input_35 IN varchar2,
299 p_input_36 IN varchar2,
300 p_input_37 IN varchar2,
301 p_input_38 IN varchar2,
302 p_input_39 IN varchar2,
303 p_input_40 IN varchar2,
304 p_validate IN varchar2,
305 p_exclude_from_output OUT nocopy varchar2,
306 sp_out_1 OUT nocopy varchar2,
307 sp_out_2 OUT nocopy varchar2,
308 sp_out_3 OUT nocopy varchar2,
309 sp_out_4 OUT nocopy varchar2,
310 sp_out_5 OUT nocopy varchar2,
311 ret_str_len OUT nocopy number,
312 p_input_41 IN varchar2 default null,
313 p_input_42 IN varchar2 default null,
314 p_input_43 IN varchar2 default null,
315 p_input_44 IN varchar2 default null
316 )
317
318 return varchar2;
319 /* End of Function format_record */
320
321
322
323 /* Function Name : Get_Territory_Values
324 Purpose : Purpose of this function is to fetch the balances as well
325 as the data related to territory.
326 Error checking
327
328 Special Note :
329
330
331 */
332
333
334 FUNCTION Get_Territory_Values(
335 p_assignment_action_id number, -- context
336 p_tax_unit_id number,-- context
337 p_effective_date IN varchar2,
338 p_report_type IN varchar2,
339 p_format IN varchar2,
340 p_report_qualifier IN varchar2,
341 p_record_name IN varchar2,
342 p_input_1 IN varchar2,
346 p_input_5 IN varchar2,
343 p_input_2 IN varchar2,
344 p_input_3 IN varchar2,
345 p_input_4 IN varchar2,
347 p_validate IN varchar2,
348 p_exclude_from_output OUT nocopy varchar2,
349 sp_out_1 OUT nocopy varchar2,
350 sp_out_2 OUT nocopy varchar2,
351 sp_out_3 OUT nocopy varchar2,
352 sp_out_4 OUT nocopy varchar2,
353 sp_out_5 OUT nocopy varchar2,
354 sp_out_6 OUT nocopy varchar2,
355 sp_out_7 OUT nocopy varchar2,
356 sp_out_8 OUT nocopy varchar2,
357 sp_out_9 OUT nocopy varchar2,
358 sp_out_10 OUT nocopy varchar2)
359 return varchar2;
360
361 FUNCTION CHARACTER_CHECK(p_value IN varchar2)
362 return varchar2;
363
364 FUNCTION Formula_Check(p_report_format IN VARCHAR2,
365 p_formula_name IN VARCHAR2)
366 return varchar2;
367 --
368 -- Purpose: This function used to derive the Mag file Name for following Magnetic media report
369 -- processes.
370 -- 1. Federal W-2 Magnetic Media
371 -- 2. State W-2 Magnetic Media
372 -- 3. State Quarterly Wage Listing
373 --
374 FUNCTION get_file_name
375 ( p_bus_group_id IN Number, -- Business Group Id
376 p_report_type IN Varchar2, -- W2, SQWL
377 p_state_code IN Varchar2, -- FED or State Code
378 p_mag_effective_date IN Varchar2, -- This would be used to derive period
379 p_format_type IN Varchar2 -- I=ICESA, M=MMREF, T=TIB4, S=State
380 ) RETURN varchar2;
381 --PRAGMA RESTRICT_REFERENCES(get_file_name, WNDS,WNPS);
382
383 FUNCTION get_live_ee_contrib( p_assignment_action_id number, --context
384 p_tax_unit_id number --context
385 ) RETURN VARCHAR2;
386
387 FUNCTION get_ff_archive_value (
388 p_action_id NUMBER, -- context
389 p_jurisdiction_code VARCHAR2, -- context
390 p_tax_unit_id NUMBER, -- context
391 p_data_type VARCHAR2
392 )
393 RETURN NUMBER;
394
395 /* Function Name : Get_Employee_Count
396 Purpose : Purpose of this function is to get the Number of Employees
397 for each Employer (Tax Unit ID). Created for getting data in SQWL Output.
398
399 Special Note :
400
401 */
402
403 FUNCTION get_employee_count( p_payroll_action_id number, --context
404 p_tax_unit_id number, --context
405 p_state varchar2 default null
406 ) RETURN number ;
407
408 /* Function Name : Get_Total_Wages
409 Purpose : Purpose of this function is to get the Total Wages (State Specific)
410 to be reported in SQWL Magtape.
411
412 Special Note :
413
414 */
415
416 FUNCTION get_total_wages( p_payroll_action_id number, --context
417 p_tax_unit_id number, --context
418 p_state varchar2,
419 -- Following Parameters have been Added later to use it in SQWL as well as W2
420 p_report_type varchar2 default 'SQWL',
421 p_balance_name varchar2 default null
422 ) RETURN number ;
423
424 /* Function Name : get_wages
425 Purpose : Purpose of this function is to get the Excess Wages (State Specific),
426 total withholding, total workers compensation.
427
428 Special Note :
429
430 */
431
432 function get_wages(p_payroll_action_id number, --context
433 p_tax_unit_id number, --context
434 p_state varchar2,
435 p_excess_wages out nocopy number,
436 p_withholding out nocopy number,
437 p_workerscomp out nocopy number
438 )return number;
439
440 /* Function Name : get_sui_wages
441 Purpose : this will return the SUI wages
442 */
443
444 function get_sui_wages(p_payroll_action_id number, --context
445 p_tax_unit_id number, --context
446 p_state varchar2,
447 p_sui_gross out nocopy number,
448 p_sui_subj out nocopy number,
449 p_sui_pre_tax out nocopy number,
450 p_sui_taxable out nocopy number
451 )return number;
452
453 /* Function Name : Get_Employee_Count_Monthwise
454 Purpose : Purpose of this function is to get the Number of Employees
455 for each Employer (Tax Unit ID). Created for getting data in SQWL Output.
456
457 Special Note :
458
459 */
460 FUNCTION get_employee_count_monthwise( p_payroll_action_id number, --context
461 p_tax_unit_id number, --context
462 p_database_item_name varchar2
463 ) RETURN number ;
464
465 END pay_us_reporting_utils_pkg;
[Home] [Help]