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