1 PACKAGE PAY_US_W2_GENERIC_EXTRACT as
2 /* $Header: payusw2genxtract.pkh 120.0.12010000.1 2008/07/27 21:57:10 appldev ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 PAY_US_W2_GENERIC_EXTRACT
10
11 File
12 payusw2genxtract.pkh
13
14 Purpose
15 The purpose of this package is to support the YearEnd Generic Interface Extract Process
16 This package to include generic dtabase package components may be used for various
17 YearEnd process for Extractiing archived Data, Validating and constructing XML element
18
19 Notes
20
21 History
22
23 Date User Id Version Description
24 ============================================================================
25 08-Nov-06 ppanda 115.0 Initial Version Created
26
27
28 ============================================================================*/
29 -- Global Variable
30
31 g_number NUMBER;
32
33 /*******************************************************************
34 ** PL/SQL Record to store the archived values for Employee
35 *******************************************************************/
36 TYPE fed_employee_record IS RECORD
37 ( ASSIGNMENT_ACTION_ID NUMBER
38 ,EE_TAX_YEAR NUMBER
39 ,EE_TAX_UNIT_ID NUMBER
40 ,EE_ASSIGNMENT_ID NUMBER
41 ,EE_ASSIGNMENT_NUMBER VARCHAR2(200)
42 ,EE_EMPLOYEE_NUMBER VARCHAR2(200)
43 ,EE_SSN VARCHAR2(200)
44 ,EE_FIRST_NAME VARCHAR2(200)
45 ,EE_MIDDLE_INITIAL VARCHAR2(200)
46 ,EE_LAST_NAME VARCHAR2(200)
47 ,EE_SUFFIX VARCHAR2(200)
48 ,EE_LOCATION_ADDRESS VARCHAR2(200)
49 ,EE_DELIVERY_ADDRESS VARCHAR2(200)
50 ,EE_CITY VARCHAR2(200)
51 ,EE_STATE_ABBREVIATION VARCHAR2(200)
52 ,EE_ZIP_CODE VARCHAR2(200)
53 ,EE_ZIP_CODE_EXTENSION VARCHAR2(200)
54 ,EE_FOREIGN_STATE_PROVINCE VARCHAR2(200)
55 ,EE_FOREIGN_POSTAL_CODE VARCHAR2(200)
56 ,EE_COUNTRY_CODE VARCHAR2(200)
57 ,FIT_GROSS_WAGES VARCHAR2(200) -- wages, tips and other compensation
58 ,FIT_WITHHELD VARCHAR2(200) -- FIT withheld
59 ,SS_WAGES VARCHAR2(200) -- SS Wages
60 ,SS_TAX_WITHHELD VARCHAR2(200) -- SS Tax withheld
61 ,MEDICARE_WAGES_TIPS VARCHAR2(200) -- Medicare Wages/Tips
62 ,MEDICARE_TAX_WITHHELD VARCHAR2(200) -- Medicare Tax withheld
63 ,SS_TIPS VARCHAR2(200) -- Social Security Tips
64 ,EIC_ADVANCE VARCHAR2(200) -- Advanced EIC
65 ,W2_DEPENDENT_CARE VARCHAR2(200) -- Dependent Care benefits
66 ,W2_401K VARCHAR2(200) -- deferred compensation contributions to section 401(K)
67 ,W2_403B VARCHAR2(200) -- deferred compensation contributions to section 403(b)
68 ,W2_408K VARCHAR2(200) -- deferred compensation contributions to section 408(K)(6)
69 ,W2_457 VARCHAR2(200) -- deferred compensation contributions to section 457(b)
70 ,W2_501C VARCHAR2(200) -- deferred compensation contributions to section 501(c)(18)(D)
71 ,W2_MILITARY_HOUSING VARCHAR2(200) -- Military employees basic quarters, subsistence and combat pay
72 ,W2_NONQUAL_457 VARCHAR2(200) -- nonqualified plan section 457 distributions or contributions
73 ,W2_HSA VARCHAR2(200) -- nonqualified plan not section 457 distributions or contributions
74 ,NON_QUAL_NOT_457 VARCHAR2(200) -- employer cost of premiums for GTL over $50000
75 ,W2_NONTAX_COMBAT VARCHAR2(200) -- income from the exercise of nonstatutory stock options
76 ,W2_GROUP_TERM_LIFE VARCHAR2(200) -- ER Health Savings Account
77 ,W2_NONQUAL_STOCK VARCHAR2(200) -- Nontaxable Combat Pay
78 ,W2_NONQUAL_DEF_COMP VARCHAR2(200) -- Deferred compensation contributions
79 ,W2_ROTH_401K VARCHAR2(200) -- Designated Roth Contributions to a section 401(k) plan
80 ,W2_ROTH_403B VARCHAR2(200) -- Designated Roth Contributions Under a section 403(b) Salary Reduction Agreement
81 ,W2_ASG_STATUTORY_EMPLOYEE VARCHAR2(200)
82 ,RETIREMENT_PLAN_INDICATOR VARCHAR2(200)
83 ,W2_TP_SICK_PAY_IND VARCHAR2(200)
84 --
85 -- Puertorico based Data
86 --
87 ,RO_RECORD_IDENTIFIER VARCHAR2(200)
88 ,RO_W2_BOX_8 VARCHAR2(200) -- allocated tips
89 ,RO_UNCOLLECTED_TAX_ON_TIPS VARCHAR2(200) -- uncollected employee tax on tips
90 ,RO_W2_MSA VARCHAR2(200) -- Medical Savings Account
91 ,RO_W2_408P VARCHAR2(200) -- Simple Retirement Account
92 ,RO_W2_ADOPTION VARCHAR2(200) -- Qualified adoption expenses
93 ,RO_W2_UNCOLL_SS_GTL VARCHAR2(200) -- uncollected social security or RRTA tax on GTL insurance over $50000
94 ,RO_W2_UNCOLL_MED_GTL VARCHAR2(200) -- uncollected medicare tax on GTL insurance over $50,000
95 ,RO_W2_409A_NONQUAL_INCOM VARCHAR2(200) -- 409A income
96 ,RO_CIVIL_STATUS VARCHAR2(200)
97 ,RO_SPOUSE_SSN VARCHAR2(200)
98 ,RO_WAGES_SUBJ_PR_TAX VARCHAR2(200)
99 ,RO_COMM_SUBJ_PR_TAX VARCHAR2(200)
100 ,RO_ALLOWANCE_SUBJ_PR_TAX VARCHAR2(200)
101 ,RO_TIPS_SUBJ_PR_TAX VARCHAR2(200)
102 ,RO_W2_STATE_WAGES VARCHAR2(200)
103 ,RO_PR_TAX_WITHHELD VARCHAR2(200)
104 ,RO_RETIREMENT_CONTRIB VARCHAR2(200)
105 --
106 -- RS Record Data
107 --
108 ,RS_TAXING_ENTITY_CODE VARCHAR2(200)
109 ,RS_OPTIONAL_CODE VARCHAR2(200)
110 ,RS_REPORTING_PERIOD VARCHAR2(200)
111 ,RS_SQWL_UNEMP_INS_WAGES VARCHAR2(200)
112 ,RS_SQWL_UNEMP_TXBL_WAGES VARCHAR2(200)
113 ,RS_WEEKS_WORKED VARCHAR2(200)
114 ,RS_DATE_FIRST_EMPLOYED VARCHAR2(200)
115 ,RS_DATE_OF_SEPARATION VARCHAR2(200)
116 ,RS_STATE_ER_ACCT_NUM VARCHAR2(200)
117 ,RS_STATE_CODE VARCHAR2(200)
118 ,RS_STATE_WAGES VARCHAR2(200)
119 ,RS_SIT_WITHHELD VARCHAR2(200)
120 ,RS_OTHER_STATE_DATA VARCHAR2(200)
121 ,RS_STATE_EIN VARCHAR2(200)
122 ,RS_TAX_TYPE_CODE VARCHAR2(200)
123 ,RS_STATE_CONTROL_NUMBER VARCHAR2(200)
124 ,RS_SUPPLEMENTAL_DATA_1 VARCHAR2(200)
125 ,RS_SUPPLEMENTAL_DATA_2 VARCHAR2(200)
126 );
127
128 /*******************************************************************
129 ** PL/SQL table of record to store the archived values of RCW Record
130 *******************************************************************/
131 TYPE fed_ee_record_tab IS TABLE OF fed_employee_record
132 INDEX BY BINARY_INTEGER;
133 --
134 -- Table of Records with Data Type fed_ee_record_tab
135 --
136 ltr_fed_ee_record fed_ee_record_tab;
137
138 TYPE validate_data_rec IS RECORD
139 (assingment_action_id NUMBER
140 ,record_name VARCHAR2(200)
141 ,field_name VARCHAR2(200)
142 ,data_type VARCHAR2(200)
143 ,dbi_Name VARCHAR2(200)
144 ,data_value VARCHAR2(200)
145 ,derived_live_data VARCHAR2(200) -- D :- Devied L :- Live
146 ,mandatory_flag VARCHAR2(200)
147 ,no_of_validation NUMBER -- Range between 1 and 3
148 ,validation_type_1 VARCHAR2(200)
149 ,validation_type_2 VARCHAR2(200)
150 ,validation_type_3 VARCHAR2(200)
151 ,validation_status VARCHAR2(200)
152 ,xml_string VARCHAR2(32767)
153 );
154 TYPE validate_data_record_tab IS TABLE OF validate_data_rec
155 INDEX BY BINARY_INTEGER;
156 validate_data_record validate_data_record_tab;
157
158 --
159 -- This procedure would be used to populate the Tag used for RA Data Record
160 --
161 PROCEDURE populate_ra_data_tag;
162
163 PROCEDURE populate_arch_transmitter (
164 p_payroll_action_id IN NUMBER
165 ,p_tax_unit_id IN NUMBER
166 ,p_date_earned IN DATE
167 ,p_reporting_year IN VARCHAR2
168 ,p_jurisdiction_code IN VARCHAR2
169 ,p_state_code IN NUMBER
170 ,p_state_abbreviation IN VARCHAR2
171 ,p_locality_code IN VARCHAR2
172 ,status IN VARCHAR2
173 ,p_final_string OUT NOCOPY VARCHAR2
174 );
175 --
176 -- This procedure would be used to populate the Tag used for RE Record
177 --
178 PROCEDURE populate_re_data_tag;
179 --
180 -- This procedure would be used to populate the PL/Table used for
181 -- storing RE or Employer record Data
182
183 PROCEDURE populate_arch_employer (
184 p_payroll_action_id IN NUMBER
185 ,p_tax_unit_id IN NUMBER
186 ,p_date_earned IN DATE
187 ,p_reporting_year IN VARCHAR2
188 ,p_jurisdiction_code IN VARCHAR2
189 ,p_state_code IN NUMBER
190 ,p_state_abbreviation IN VARCHAR2
191 ,p_locality_code IN VARCHAR2
192 ,status IN VARCHAR2
193 ,p_final_string OUT NOCOPY VARCHAR2
194 );
195
196 --
197 -- This procedure would be used to populate the Tag used for RW, RO and RS Data Item Tags
198 --
199 PROCEDURE populate_ee_data_tag;
200
201 PROCEDURE populate_arch_employee(
202 p_payroll_action_id NUMBER
203 ,p_ye_assignment_action_id NUMBER
204 ,p_tax_unit_id NUMBER
205 ,p_assignment_id NUMBER
206 ,p_date_earned DATE
207 ,p_reporting_year VARCHAR2
208 ,p_jurisdiction_code VARCHAR2
209 ,p_state_code NUMBER
210 ,p_state_abbreviation VARCHAR2
211 ,p_locality_code VARCHAR2
212 ,status VARCHAR2
213 ,p_final_string OUT NOCOPY VARCHAR2
214 );
215
216
217 FUNCTION convert_special_char( p_data varchar2)
218 RETURN varchar2;
219
220
221 TYPE submitter_rec IS RECORD
222 ( submitter_tag varchar2(200),
223 submitter_data varchar2(200));
224
225 /*******************************************************************
226 ** PL/SQL table of record to store the archived values of RA Record DataItems
227 *******************************************************************/
228 TYPE submitter_record IS TABLE OF submitter_rec
229 INDEX BY BINARY_INTEGER;
230 --
231 -- Table of Records with Data Type submitter_record
232 --
233 g_ra_record submitter_record;
234
235 --
236 --PL Table used for storing the RA record Data Item Tags
237 --
238 TYPE ra_dataitem_tag IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
239 ltr_ra_data_tag ra_dataitem_tag;
240
241 --
242 -- Nof of Tags used RA Records Data Items
243 --
244 g_ra_no_of_tag number := 33;
245 --
246 -- This is to store Submitter Employer Identification Number
247 --
248 g_submitter_ein varchar2(200);
249
250 /* ========================================================
251 ** PL/SQL table of record to store the archived values of RE Record DataItems
252 ========================================================= */
253 TYPE employer_rec IS RECORD
254 ( employer_tag varchar2(200),
255 employer_data varchar2(200));
256
257 TYPE employer_record IS TABLE OF employer_rec
258 INDEX BY BINARY_INTEGER;
259 --
260 -- Table of Records with Data Type employer_record
261 --
262 g_re_record employer_record;
263
264 --
265 --PL Table used for storing the RE record Data Item Tags
266 --
267 TYPE re_dataitem_tag IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
268 ltr_re_data_tag re_dataitem_tag;
269
270 --
271 -- Nof of Tags used RA Records Data Items
272 --
273 g_re_no_of_tag number := 38;
274
275
276 /* ==================================================================
277 ** PL/SQL table of record to store the archived values of RW, RO and RS Record DataItems
278 ================================================================== */
279 TYPE employee_rec IS RECORD
280 ( employee_tag varchar2(200),
281 employee_data varchar2(200));
282
283 TYPE employee_record IS TABLE OF employee_rec
284 INDEX BY BINARY_INTEGER;
285 --
286 -- Table of Records with Data Type employee_record
287 --
288 g_ee_record employee_record;
289
290 --
291 --PL Table used for storing the RE record Data Item Tags
292 --
293 TYPE ee_dataitem_tag IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
294 ltr_ee_data_tag ee_dataitem_tag;
295
296 --
297 -- Nof of Tags used RW, RO and RS Records Data Items
298 --
299 g_ee_no_of_tag number := 78;
300
301 --
302 -- This table structure is used to store locality level data
303 --
304 TYPE l_local_rec IS RECORD(
305 jurisdiction VARCHAR2(15),
306 city_name VARCHAR2(100),
307 county_name VARCHAR2(100),
308 tax_type VARCHAR2(100),
309 locality_code VARCHAR2(100),
310 locality_wages VARCHAR2(100),
311 locality_tax VARCHAR2(100));
312
313 TYPE l_local_table IS TABLE OF l_local_rec
314 INDEX BY BINARY_INTEGER;
315 ltr_local_record l_local_table;
316
317 TYPE city_rec IS RECORD
318 ( city_tag varchar2(200),
319 city_data varchar2(200));
320
321 TYPE ee_city_record IS TABLE OF city_rec
322 INDEX BY BINARY_INTEGER;
323 --
324 -- Table of Records with Data Type ee_city_record
325 --
326 g_city_record ee_city_record;
327
328 --
329 --PL Table used for storing the Employee level locality Data Item Tags
330 --
331 TYPE ee_locality_tag IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
332 ltr_ee_locality_tag ee_locality_tag;
333
334 --
335 -- Nof of Tags used RW, RO and RS Records Data Items
336 --
337 g_no_of_locality_tag number := 7;
338
339
340
341 END pay_us_w2_generic_extract;
[Home] [Help]