1 PACKAGE PAY_US_W2C_IN_MMREF2_FORMAT AUTHID CURRENT_USER as
2 /* $Header: payusw2cinmmref2.pkh 120.4 2011/12/27 18:14:41 skchalla ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_w2c_in_mmref2_format
10
11 File
12 payusw2cinmmref2.pkh
13
14 Purpose
15 The purpose of this package is to support the generation of magnetic tape W-2c
16 reports in MMREF-2 format for US legilsative requirements.
17
18 Notes
19
20 History
21
22 Date User Id Version Description
23 24-OCT-03 ppanda 115.0 created
24 26-OCT-04 meshah 115.1 added action_information21 to TYPE
25 action_rcw_info_rec for ER Health
26 Savings account. Bug# 3650105.
27 10-Jan-07 sausingh 115.2 added action_information_24, action_information_25
28 and p_output_51 to p_output_54 for the bug 5358272
29 12-Nov-07 skchalla 115.6 modified the action_rco_info_rec for
30 adding a new balance Hire exempt wages and tips
31 27-Dec-11 skchalla 115.7 (13255427) Modified to report two new balances, W2 Health Coverage
32 and W2 Roth 457b
33 ============================================================================*/
34 -- Global Variable
35
36 g_number NUMBER;
37
38
39 FUNCTION format_w2c_record(
40 p_effective_date IN varchar2,
41 p_report_type IN varchar2,
42 p_format IN varchar2,
43 p_report_qualifier IN varchar2,
44 p_record_name IN varchar2,
45 p_input_1 IN varchar2,
46 p_input_2 IN varchar2,
47 p_input_3 IN varchar2,
48 p_input_4 IN varchar2,
49 p_input_5 IN varchar2,
50 p_input_6 IN varchar2,
51 p_input_7 IN varchar2,
52 p_input_8 IN varchar2,
53 p_input_9 IN varchar2,
54 p_input_10 IN varchar2,
55 p_input_11 IN varchar2,
56 p_input_12 IN varchar2,
57 p_input_13 IN varchar2,
58 p_input_14 IN varchar2,
59 p_input_15 IN varchar2,
60 p_input_16 IN varchar2,
61 p_input_17 IN varchar2,
62 p_input_18 IN varchar2,
63 p_input_19 IN varchar2,
64 p_input_20 IN varchar2,
65 p_input_21 IN varchar2,
66 p_input_22 IN varchar2,
67 p_input_23 IN varchar2,
68 p_input_24 IN varchar2,
69 p_input_25 IN varchar2,
70 p_input_26 IN varchar2,
71 p_input_27 IN varchar2,
72 p_input_28 IN varchar2,
73 p_input_29 IN varchar2,
74 p_input_30 IN varchar2,
75 p_input_31 IN varchar2,
76 p_input_32 IN varchar2,
77 p_input_33 IN varchar2,
78 p_input_34 IN varchar2,
79 p_input_35 IN varchar2,
80 p_input_36 IN varchar2,
81 p_input_37 IN varchar2,
82 p_input_38 IN varchar2,
83 p_input_39 IN varchar2,
84 p_input_40 IN varchar2,
85 p_validate IN varchar2,
86 p_exclude_from_output OUT nocopy varchar2,
87 sp_out_1 OUT nocopy varchar2,
88 sp_out_2 OUT nocopy varchar2,
89 sp_out_3 OUT nocopy varchar2,
90 sp_out_4 OUT nocopy varchar2,
91 sp_out_5 OUT nocopy varchar2,
92 ret_str_len OUT nocopy number
93 )
94
95 return varchar2;
96
97 /* -------------------------------------------------------------
98 Function Name : print_record_header
99 Purpose : Function will return the String for header
100 or title line for the Table or table heading
101 related to record for printing in audit files
102
103 Error checking
104
105 Special Note :
106
107 -------------------------------------------------------------- */
108
109 FUNCTION print_w2c_record_header(
110 p_effective_date IN varchar2,
111 p_report_type IN varchar2,
112 p_format IN varchar2,
113 p_report_qualifier IN varchar2,
114 p_record_name IN varchar2,
115 p_input_1 IN varchar2,
116 p_input_2 IN varchar2,
117 p_input_3 IN varchar2,
118 p_input_4 IN varchar2,
119 p_input_5 IN varchar2,
120 p_validate IN varchar2,
121 p_exclude_from_output OUT nocopy varchar2,
122 sp_out_1 OUT nocopy varchar2,
123 sp_out_2 OUT nocopy varchar2,
124 sp_out_3 OUT nocopy varchar2,
125 sp_out_4 OUT nocopy varchar2,
126 sp_out_5 OUT nocopy varchar2,
127 sp_out_6 OUT nocopy varchar2,
128 sp_out_7 OUT nocopy varchar2,
129 sp_out_8 OUT nocopy varchar2,
130 sp_out_9 OUT nocopy varchar2,
131 sp_out_10 OUT nocopy varchar2
132 ) RETURN VARCHAR2;
133
134 /* ******************************************************************
135 ** PL/SQL Record to store the archived values for RCW Record
136 ****************************************************************** */
137 TYPE action_rcw_info_rec IS RECORD
138 ( assignment_Action_id NUMBER
139 ,SSN varchar2(200)
140 ,first_name varchar2(200)
141 ,middle_name varchar2(200)
142 ,last_name varchar2(200)
143 ,action_information1 NUMBER(14,2) := 0 -- wages, tips and other compensation
144 ,action_information2 NUMBER(14,2) := 0 -- FIT withheld
145 ,action_information3 NUMBER(14,2) := 0 -- SS Wages
146 ,action_information4 NUMBER(14,2) := 0 -- SS Tax withheld
147 ,action_information5 NUMBER(14,2) := 0 -- Medicare Wages/Tips
148 ,action_information6 NUMBER(14,2) := 0 -- Medicare Tax withheld
149 ,action_information7 NUMBER(14,2) := 0 -- Social Security Tips
150 ,action_information8 NUMBER(14,2) := 0 -- Advanced EIC
151 ,action_information9 NUMBER(14,2) := 0 -- Dependent Care benefits
152 ,action_information10 NUMBER(14,2) := 0 -- deferred compensation contributions to section 401(K)
153 ,action_information11 NUMBER(14,2) := 0 -- deferred compensation contributions to section 403(b)
154 ,action_information12 NUMBER(14,2) := 0 -- deferred compensation contributions to section 408(K)(6)
155 ,action_information13 NUMBER(14,2) := 0 -- deferred compensation contributions to section 457(b)
156 ,action_information14 NUMBER(14,2) := 0 -- deferred compensation contributions to section 501(c)(18)(D)
157 ,action_information15 NUMBER(14,2) := 0 -- Deferred compensation contributions
158 ,action_information16 NUMBER(14,2) := 0 -- Military employees basic quarters, subsistence and combat pay
159 ,action_information17 NUMBER(14,2) := 0 -- nonqualified plan section 457 distributions or contributions
160 ,action_information18 NUMBER(14,2) := 0 -- nonqualified plan not section 457 distributions or contributions
161 ,action_information19 NUMBER(14,2) := 0 -- employer cost of premiums for GTL over $50000
162 ,action_information20 NUMBER(14,2) := 0 -- income from the exercise of nonstatutory stock options
163 ,action_information21 NUMBER(14,2) := 0 -- ER Health Savings Account
164 ,action_information22 NUMBER(14,2) := 0 -- Nontaxable Combat Pay
165 ,action_information23 NUMBER(14,2) := 0 -- 409A deferrals
166 ,action_information24 NUMBER(14,2) := 0 -- Designed ROTH contributio-- ns to a 401(k) plan /* 5358272 */
167 ,action_information25 NUMBER(14,2) := 0 -- Designed ROTH contributio-- ns to a 403(b) plan /* 5358272 */
168 ,action_information26 NUMBER(14,2) := 0 -- Cost of Employer-Sponsored Health Coverage, Bug 13255427
169 ,statutory_emp_indicator VARCHAR2(200) := ' '
170 ,retirement_plan_indicator VARCHAR2(200) := ' '
171 ,sick_pay_indicator VARCHAR2(200) := ' '
172 );
173 /*******************************************************************
174 ** PL/SQL table of record to store the archived values of RCW Record
175 *******************************************************************/
176 TYPE action_rcw_info_tab IS TABLE OF action_rcw_info_rec
177 INDEX BY BINARY_INTEGER;
178
179 ltr_rcw_info action_rcw_info_tab;
180
181
182 /*******************************************************************
183 ** PL/SQL Record to store the archived values for RCO Record
184 *******************************************************************/
185 TYPE action_rco_info_rec IS RECORD
186 ( action_information1 NUMBER(14,2) := 0 -- allocated tips
187 ,action_information2 NUMBER(14,2) := 0 -- uncollected employee tax on tips
188 ,action_information3 NUMBER(14,2) := 0 -- Medical Savings Account
189 ,action_information4 NUMBER(14,2) := 0 -- Simple Retirement Account
190 ,action_information5 NUMBER(14,2) := 0 -- Qualified adoption expenses
191 ,action_information6 NUMBER(14,2) := 0 -- uncollected social security or RRTA tax on GTL insurance over $50000
192 ,action_information7 NUMBER(14,2) := 0 -- uncollected medicare tax on GTL insurance over $50,000
193 ,action_information8 NUMBER(14,2) := 0 -- 409A income
194 ,action_information9 NUMBER(14,2) := 0 -- Hire Exempt Wages and Tips /*9783383*/
195 ,action_information10 NUMBER(14,2) := 0 -- Designated Roth Contributions Under 457b plan, Bug 13255427
196 );
197
198 /*******************************************************************
199 ** PL/SQL table of record to store the archived values of RCO Record
200 *******************************************************************/
201 TYPE action_rco_info_tab IS TABLE OF action_rco_info_rec
202 INDEX BY BINARY_INTEGER;
203
204 ltr_rco_info action_rco_info_tab;
205
206 /*******************************************************************
207 ** PL/SQL Record to store the archived values for RCT Record
208 *******************************************************************/
209 TYPE rct_info_rec IS RECORD
210 ( rct_wage_old NUMBER(14,2) := 0 -- Old RCW Wage Total
211 ,rct_wage_old_formated varchar2(15) := ' ' -- Old RCW Wage Total reported on RCT
212 ,rct_wage_new NUMBER(14,2) := 0 -- New RCW Wage Total reported on RCT
213 ,rct_wage_new_formated varchar2(15) := ' '
214 ,rct_identical_flag varchar2(10) := 'Y'
215 );
216
217 /*******************************************************************
218 ** PL/SQL table of record to store the archived values of RCT Record
219 *******************************************************************/
220 TYPE rct_info_tab IS TABLE OF rct_info_rec
221 INDEX BY BINARY_INTEGER;
222
223 ltr_rct_info rct_info_tab;
224
225 /*******************************************************************
226 ** PL/SQL Record to store the archived values for RCU Record
227 *******************************************************************/
228 TYPE rcu_info_rec IS RECORD
229 ( rcu_wage_old NUMBER(14,2) := 0 -- Old RCO Wage Total
230 ,rcu_wage_old_formated varchar2(15) := ' ' -- OLD RCO Wage Total reported on RCU
231 ,rcu_wage_new NUMBER(14,2) := 0 -- New RCO Wage Total
232 ,rcu_wage_new_formated varchar2(15) := ' ' -- New RCO Wage Total reported on RCU
233 ,rcu_identical_flag varchar2(10) := 'Y'
234 );
235
236 /*******************************************************************
237 ** PL/SQL table of record to store the archived values of RCO Record
238 *******************************************************************/
239 TYPE rcu_info_tab IS TABLE OF rcu_info_rec
240 INDEX BY BINARY_INTEGER;
241
242 ltr_rcu_info rcu_info_tab;
243
244 /*******************************************************************
245 ** PL/SQL Record to store the column of Formatted RCW record
246 *******************************************************************/
247
248 TYPE format_rcw_rec IS RECORD
249 (
250 ssn varchar2(100),
251 first_name varchar2(100),
252 middle_name varchar2(100),
253 last_name varchar2(100),
254 location_address varchar2(100),
255 delivery_address varchar2(100),
256 city varchar2(100),
257 state varchar2(100),
258 zip varchar2(100),
259 zip_extension varchar2(100),
260 foreign_state varchar2(100),
261 foreign_postal varchar2(100),
262 country_code varchar2(100),
263 wage_1 varchar2(100),
264 wage_2 varchar2(100),
265 wage_3 varchar2(100),
266 wage_4 varchar2(100),
267 wage_5 varchar2(100),
268 wage_6 varchar2(100),
269 wage_7 varchar2(100),
270 wage_8 varchar2(100),
271 wage_9 varchar2(100),
272 wage_10 varchar2(100),
273 wage_11 varchar2(100),
274 wage_12 varchar2(100),
275 wage_13 varchar2(100),
276 wage_14 varchar2(100),
277 wage_15 varchar2(100),
278 wage_16 varchar2(100),
279 wage_17 varchar2(100),
280 wage_18 varchar2(100),
281 wage_19 varchar2(100),
282 wage_20 varchar2(100),
283 stat_emp_indicator varchar2(100),
284 retire_plan_indicator varchar2(100),
285 sick_pay_indicator varchar2(100)
286 );
287
288 /*******************************************************************
289 ** PL/SQL table of record to store the archived values of RCT Record
290 *******************************************************************/
291
292 TYPE rcw_format_rec IS TABLE OF format_rcw_rec
293 INDEX BY BINARY_INTEGER;
294
295 rcw_record rcw_format_rec;
296
297 rcw_exclude_flag varchar2(10) := 'N';
298 rco_exclude_flag varchar2(10) := 'N';
299 rcw_number_of_correction number := 0;
300 rco_number_of_correction number := 0;
301
302
303 number_of_valid_rcw_rct number := 0; -- Number of Valid RCW Record to be reported in RCT
304 number_of_valid_rco_rcu number := 0; -- Number of Valid RCO Record to be reported in RCU
305
306 number_of_error_rcw_rct number := 0; -- Number of Error RCW Record to be reported in RCT
307 number_of_error_rco_rcu number := 0; -- Number of Error RCO Record to be reported in RCU
308
312 number_of_valid_rcw_rcf number := 0; -- Number of Valid RCW Record to be reported in RCF
309 number_of_error_rcw_rcf number := 0; -- Number of Error RCW Record to be reported in RCF
310 number_of_error_rco_rcf number := 0; -- Number of Error RCO Record to be reported in RCF
311
313 number_of_valid_rco_rcf number := 0; -- Number of Valid RCO Record to be reported in RCF
314
315 rcw_mf_record varchar2(32767) := '';
316 rcw_csv_record varchar2(32767) := '';
317 rcw_blank_csv_record varchar2(32767) := '';
318 rco_mf_record varchar2(32767) := '';
319 rco_csv_record varchar2(32767) := '';
320 rco_blank_csv_record varchar2(32767) := '';
321
322 TYPE wage_rec IS RECORD(identical_flag varchar2(3) := 'Y',
323 wage_old_value number := 0,
324 wage_old_value_formated varchar2(100) := '0',
325 wage_new_value number := 0,
326 wage_new_value_formated varchar2(100) := '0'
327 );
328 wage_record wage_rec;
329 TYPE table_wage_record IS TABLE OF wage_record%TYPE
330 INDEX BY BINARY_INTEGER;
331
332 PROCEDURE GET_ARCHIVED_VALUES ( p_action_type varchar2 -- O Originally Reported, C Corrected
333 ,p_record_type varchar2 -- RCW, RCO
334 ,p_assignment_action_id number
335 ,p_tax_unit_id number);
336
337 FUNCTION pay_us_w2c_RCW_record ( 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_tax_unit_id IN varchar2,
343 p_record_identifier IN varchar2,
344 p_ssn IN varchar2,
345 p_first_name IN varchar2,
346 p_middle_name IN varchar2,
347 p_last_name IN varchar2,
348 p_sufix IN varchar2,
349 p_location_address IN varchar2,
350 p_delivery_address IN varchar2,
351 p_city IN varchar2,
352 p_state IN varchar2,
353 p_zip IN varchar2,
354 p_zip_extension IN varchar2,
355 p_foreign_state IN varchar2,
356 p_foreign_postal_code IN varchar2,
357 p_country_code IN varchar2,
358 p_orig_assignment_actid IN varchar2,
359 p_correct_assignment_actid IN varchar2,
360 p_employee_number IN varchar2,
361 p_format_type IN varchar2,
362 p_validate IN varchar2,
363 p_exclude_from_output OUT nocopy varchar2,
364 sp_out_1 OUT nocopy varchar2,
365 sp_out_2 OUT nocopy varchar2,
366 sp_out_3 OUT nocopy varchar2,
367 sp_out_4 OUT nocopy varchar2,
368 sp_out_5 OUT nocopy varchar2,
369 ret_str_len OUT nocopy varchar2,
370 p_error OUT nocopy boolean
371 ) return varchar2;
372
373 Function Initialize_GRE_Level_Total return number;
374
375 FUNCTION format_w2c_total_record(
376 p_effective_date IN varchar2,
377 p_report_type IN varchar2,
378 p_format IN varchar2,
379 p_report_qualifier IN varchar2,
380 p_record_name IN varchar2,
381 p_input_1 IN varchar2,
382 p_input_2 IN varchar2,
383 p_input_3 IN varchar2,
384 p_input_4 IN varchar2,
385 p_input_5 IN varchar2,
386 p_output_1 OUT nocopy varchar2,
387 p_output_2 OUT nocopy varchar2,
388 p_output_3 OUT nocopy varchar2,
389 p_output_4 OUT nocopy varchar2,
390 p_output_5 OUT nocopy varchar2,
391 p_output_6 OUT nocopy varchar2,
392 p_output_7 OUT nocopy varchar2,
393 p_output_8 OUT nocopy varchar2,
394 p_output_9 OUT nocopy varchar2,
398 p_output_13 OUT nocopy varchar2,
395 p_output_10 OUT nocopy varchar2,
396 p_output_11 OUT nocopy varchar2,
397 p_output_12 OUT nocopy varchar2,
399 p_output_14 OUT nocopy varchar2,
400 p_output_15 OUT nocopy varchar2,
401 p_output_16 OUT nocopy varchar2,
402 p_output_17 OUT nocopy varchar2,
403 p_output_18 OUT nocopy varchar2,
404 p_output_19 OUT nocopy varchar2,
405 p_output_20 OUT nocopy varchar2,
406 p_output_21 OUT nocopy varchar2,
407 p_output_22 OUT nocopy varchar2,
408 p_output_23 OUT nocopy varchar2,
409 p_output_24 OUT nocopy varchar2,
410 p_output_25 OUT nocopy varchar2,
411 p_output_26 OUT nocopy varchar2,
412 p_output_27 OUT nocopy varchar2,
413 p_output_28 OUT nocopy varchar2,
414 p_output_29 OUT nocopy varchar2,
415 p_output_30 OUT nocopy varchar2,
416 p_output_31 OUT nocopy varchar2,
417 p_output_32 OUT nocopy varchar2,
418 p_output_33 OUT nocopy varchar2,
419 p_output_34 OUT nocopy varchar2,
420 p_output_35 OUT nocopy varchar2,
421 p_output_36 OUT nocopy varchar2,
422 p_output_37 OUT nocopy varchar2,
423 p_output_38 OUT nocopy varchar2,
424 p_output_39 OUT nocopy varchar2,
425 p_output_40 OUT nocopy varchar2,
426 p_output_41 OUT nocopy varchar2,
427 p_output_42 OUT nocopy varchar2,
428 p_output_43 OUT nocopy varchar2,
429 p_output_44 OUT nocopy varchar2,
430 p_output_45 OUT nocopy varchar2,
431 p_output_46 OUT nocopy varchar2,
432 p_output_51 OUT nocopy varchar2, /* 5358272 */
433 p_output_52 OUT nocopy varchar2, /* 5358272 */
434 p_output_53 OUT nocopy varchar2, /* 5358272 */
435 p_output_54 OUT nocopy varchar2, /* 5358272 */
436 p_validate IN varchar2,
437 p_exclude_from_output OUT nocopy varchar2,
438 sp_out_1 OUT nocopy varchar2,
439 sp_out_2 OUT nocopy varchar2,
440 sp_out_3 OUT nocopy varchar2,
441 sp_out_4 OUT nocopy varchar2,
442 sp_out_5 OUT nocopy varchar2,
443 ret_str_len OUT nocopy varchar2,
444 p_output_47 OUT nocopy varchar2,
445 p_output_48 OUT nocopy varchar2,
446 p_output_49 OUT nocopy varchar2,
447 p_output_50 OUT nocopy varchar2
448
449
450 ) RETURN VARCHAR2;
451
452
453 END pay_us_w2c_in_mmref2_format;
[Home] [Help]