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