[Home] [Help]
PACKAGE: APPS.PAY_US_W2C_RPT
Source
1 PACKAGE pay_us_w2c_rpt AUTHID CURRENT_USER AS
2 /* $Header: pyusw2cr.pkh 120.0.12020000.6 2013/04/10 10:38:00 skchalla ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_w2c_rpt
21
22 Description : This procedure is used by W-2C Pre-Process
23 to archive data for W-2C Corrections Reporting.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ----- ------- -----------------------------------
29 25-JAN-2001 Asasthan 115.0 Created.
30 05-FEB-2013 Skchalla 115.3 13599887 Added procedures/fuctions required to
31 generated the XML for W2c
32 19-FEB-2013 skchalla 115.4 16398337 Modified the Transfer_Block cursor to sort the data
33 based on the sort options.
34 21-FEB-2013 skchalla 115.5 16398337 Modified the oreder by clause of Transfer_Block cursor.
35 08-APR-2013 skchalla 115.6 16571508 Added the global variables.
36 10-APR-2013 skchalla 115.7 16571508 Modified the varible datatypes to display '-0-' instead of null when
37 any monetary field being changed either from or to a zero.
38 */
39
40
41 PROCEDURE w2crpt_range_cursor(p_payroll_action_id in number
42 ,p_sqlstr out nocopy varchar2);
43
44 PROCEDURE w2crpt_action_creation(p_payroll_action_id in number
45 ,p_start_person_id in number
46 ,p_end_person_id in number
47 ,p_chunk in number);
48
49 PROCEDURE sort_action(p_payroll_action_id in varchar2
50 ,p_sql_string in out nocopy varchar2
51 ,p_sql_length out nocopy number);
52
53 TYPE l_w2c_fields_rec IS RECORD(
54 control_number NUMBER,
55 federal_ein VARCHAR2(100),
56 employer_name VARCHAR2(200),
57 employer_address VARCHAR2(500),
58 SSN VARCHAR2(12),
59 emp_name VARCHAR2(200),
60 last_name VARCHAR2(200),
61 emp_suffix VARCHAR2(200), -- Bug 4523389
62 employee_address VARCHAR2(500),
63 wages_tips_compensation VARCHAR2(25),
64 fit_withheld VARCHAR2(25),
65 ss_wages VARCHAR2(25),
66 ss_withheld VARCHAR2(25),
67 med_wages VARCHAR2(25),
68 med_withheld VARCHAR2(25),
69 ss_tips VARCHAR2(25),
70 allocated_tips VARCHAR2(25),
71 eic_payment VARCHAR2(25),
72 dependent_care VARCHAR2(25),
73 non_qual_plan VARCHAR2(25),
74 stat_employee VARCHAR2(1),
75 retirement_plan VARCHAR2(1),
76 sick_pay VARCHAR2(1),
77 amended VARCHAR2(20),
78 amended_date DATE);
79
80 PROCEDURE get_w2c_data(p_asg_action_id NUMBER,
81 p_prev_asg_action_id NUMBER,
82 p_tax_unit_id NUMBER,
83 p_year NUMBER,
84 p_error_msg out nocopy VARCHAR2);
85
86 FUNCTION create_xml_string (--l_w2c_fields_o l_w2c_fields_rec,
87 l_w2c_fields_c l_w2c_fields_rec,
88 l_box14_codea VARCHAR2,l_box14_meaninga_c VARCHAR2,l_box14_meaninga_o VARCHAR2,
89 l_box14_codeb VARCHAR2,l_box14_meaningb_c VARCHAR2,l_box14_meaningb_o VARCHAR2,
90 l_box14_codec VARCHAR2,l_box14_meaningc_c VARCHAR2,l_box14_meaningc_o VARCHAR2,
91 l_box12_codea VARCHAR2,l_box12_meaninga_c VARCHAR2,l_box12_meaninga_o VARCHAR2,
92 l_box12_codeb VARCHAR2,l_box12_meaningb_c VARCHAR2,l_box12_meaningb_o VARCHAR2,
93 l_box12_codec VARCHAR2,l_box12_meaningc_c VARCHAR2,l_box12_meaningc_o VARCHAR2,
94 l_box12_coded VARCHAR2,l_box12_meaningd_c VARCHAR2,l_box12_meaningd_o VARCHAR2,
95 l_state1_code VARCHAR2,
96 l_state1_ein_c VARCHAR2,l_state1_wages_c VARCHAR2,l_state1_tax_c VARCHAR2,
97 l_state1_ein_o VARCHAR2,l_state1_wages_o VARCHAR2,l_state1_tax_o VARCHAR2,
98 l_local1_wages_c VARCHAR2,l_local1_tax_c VARCHAR2,
99 l_local1_wages_o VARCHAR2,l_local1_tax_o VARCHAR2,
100 l_locality1 VARCHAR2,
101 l_state2_code VARCHAR2,
102 l_state2_ein_c VARCHAR2,l_state2_wages_c VARCHAR2, l_state2_tax_c VARCHAR2,
103 l_state2_ein_o VARCHAR2,l_state2_wages_o VARCHAR2, l_state2_tax_o VARCHAR2,
104 l_local2_wages_c VARCHAR2,l_local2_tax_c VARCHAR2,
105 l_local2_wages_o VARCHAR2,l_local2_tax_o VARCHAR2,
106 l_locality2 VARCHAR2,p_year VARCHAR2)
107 RETURN BLOB;
108
109 FUNCTION fetch_w2c_xml(p_assignment_action_id Number,
110 p_prev_assignment_action_id Number,
111 p_tax_unit_id NUMBER,
112 p_year NUMBER,
113 p_error_msg out nocopy VARCHAR2,
114 p_is_SS boolean) RETURN BLOB;
115
116 FUNCTION get_final_xml (p_assignment_action_id Number,
117 p_prev_assignment_action_id Number,
118 p_tax_unit_id NUMBER,
119 p_year NUMBER,
120 p_w2c_template_location VARCHAR2,
121 p_inst_template_location VARCHAR2,
122 p_output_location VARCHAR2,
123 p_error_msg out nocopy VARCHAR2)
124 RETURN BLOB;
125
126 TYPE l_w2c_fields_tab IS TABLE OF
127 l_w2c_fields_rec
128 INDEX BY BINARY_INTEGER;
129
130 TYPE l_state_local_rec IS RECORD(
131 state_code_c VARCHAR2(20),
132 state_ein_c VARCHAR2(200),
133 state_wages_c VARCHAR2(20),
134 state_tax_c VARCHAR2(20),
135 locality_c VARCHAR2(100),
136 locality_wages_c VARCHAR2(25),
137 locality_tax_c VARCHAR2(25),
138 state_ein_o VARCHAR2(200),
139 state_wages_o VARCHAR2(20),
140 state_tax_o VARCHAR2(20),
141 locality_tax_o VARCHAR2(25),
142 locality_wages_o VARCHAR2(25));
143
144 TYPE l_state_local_table IS TABLE OF
145 l_state_local_rec
146 INDEX BY BINARY_INTEGER;
147
148 TYPE l_state_rec IS RECORD(
149 state_code_c VARCHAR2(20),
150 state_ein_c VARCHAR2(200),
151 state_wages_c VARCHAR2(20),
152 state_tax_c VARCHAR2(20),
153 jurisdiction_c VARCHAR2(20),
154 state_ein_o VARCHAR2(200),
155 state_wages_o VARCHAR2(20),
156 state_tax_o VARCHAR2(20));
157
158 TYPE l_state_table IS TABLE OF
159 l_state_rec
160 INDEX BY BINARY_INTEGER;
161
162 TYPE l_local_rec IS RECORD(
163 locality_c VARCHAR2(100),
164 locality_wages_c VARCHAR2(25),
165 locality_tax_c VARCHAR2(25),
166 jurisdiction_c VARCHAR2(16), --Bug 13724610
167 state_code_c VARCHAR2(10),
168 tax_type_c VARCHAR2(100),
169 locality_wages_o VARCHAR2(25),
170 locality_tax_o VARCHAR2(25));
171
172
173 TYPE l_local_table IS TABLE OF
174 l_local_rec
175 INDEX BY BINARY_INTEGER;
176
177 TYPE l_box12_rec IS RECORD(
178 box12_meaning_c VARCHAR2(100),
179 box12_code_c VARCHAR2(100),
180 box12_meaning_o VARCHAR2(100));
181
182
183 TYPE l_box12_table IS TABLE OF
184 l_box12_rec
185 INDEX BY BINARY_INTEGER;
186
187
188 TYPE l_box14_rec IS RECORD(
189 box14_meaning_c VARCHAR2(100),
190 box14_code_c VARCHAR2(100),
191 box14_meaning_o VARCHAR2(100));
192
193
194 TYPE l_box14_table IS TABLE OF
195 l_box14_rec
196 INDEX BY BINARY_INTEGER;
197
198
199 CURSOR main_block IS
200 SELECT 'Version_Number=X' ,'Version 1.1'
201 FROM sys.dual;
202
203 CURSOR Transfer_Block IS
204 --changes for Bug 16398337
205 /* SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
206 FROM pay_assignment_actions paa
207 WHERE paa.payroll_action_id =
208 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
209 */
210 SELECT 'TRANSFER_ACT_ID=P', mt.assignment_action_id
211 from hr_organization_units hou
212 , hr_locations_all hl
213 , per_periods_of_service pps
214 , per_assignments_f paf
215 , pay_assignment_actions mt
216 , pay_payroll_actions ppa
217 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
218 and mt.payroll_action_id = ppa.payroll_action_id
219 and paf.assignment_id = mt.assignment_id
220 and paf.effective_start_date = (select /*+ push_subq no_unnest */ max(paf2.effective_start_date) from per_assignments_f paf2
221 where paf2.assignment_id = paf.assignment_id
222 and paf2.effective_start_date <= ppa.effective_date)
223 and paf.effective_end_date >= ppa.start_date
224 and paf.assignment_type = 'E'
225 and pps.period_of_service_id = paf.period_of_service_id
226 and pps.person_id = paf.person_id
227 and hl.location_id = paf.location_id
228 and hou.organization_id = paf.organization_id
229 and hou.business_group_id + 0 = ppa.business_group_id
230 order by decode(hr_us_w2_mt.get_parameter('S1',ppa.legislative_parameters), 'Employee_Name',
231 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_LAST_NAME')||' '
232 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_FIRST_NAME')||' '
233 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_MIDDLE_NAMES'),1,1),
234 'SSN',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), 'A_PER_NATIONAL_IDENTIFIER'),
235 'Applied For'),
236 'Zip_Code',hr_us_w2_rep.get_w2_postal_code( paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'DD-MM-YYYY')),
237 'Organization',hou.name, 'Location',hl.location_code,
238 'Termination_Reason',decode(leaving_reason,null,'ZZ',hr_us_w2_rep.get_leav_reason(leaving_reason)),
239 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_LAST_NAME')||' '
240 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_FIRST_NAME')||' '
241 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_MIDDLE_NAMES'),1,1)),
242 decode(hr_us_w2_mt.get_parameter('S2',ppa.legislative_parameters), 'Employee_Name',
243 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_LAST_NAME')||' '
244 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_FIRST_NAME')||' '
245 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_MIDDLE_NAMES'),1,1),
246 'SSN',nvl(hr_us_w2_rep.get_per_item(
247 to_number(substr(mt.serial_number,1,15)), 'A_PER_NATIONAL_IDENTIFIER'), 'Applied For'),
248 'Zip_Code',hr_us_w2_rep.get_w2_postal_code( paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'DD-MM-YYYY')),
249 'Organization',hou.name, 'Location',hl.location_code,
250 'Termination_Reason',decode(leaving_reason,null,'ZZ',hr_us_w2_rep.get_leav_reason(leaving_reason)),
251 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_LAST_NAME')||' '
252 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_FIRST_NAME')||' '
253 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_MIDDLE_NAMES'),1,1)),
254 decode(hr_us_w2_mt.get_parameter('S3',ppa.legislative_parameters), 'Employee_Name', hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)),
255 'A_PER_LAST_NAME')||' '
256 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_FIRST_NAME')||' '
257 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_MIDDLE_NAMES'),1,1),
258 'SSN',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), 'A_PER_NATIONAL_IDENTIFIER'),
259 'Applied For'), 'Zip_Code',hr_us_w2_rep.get_w2_postal_code( paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'DD-MM-YYYY')),
260 'Organization',hou.name, 'Location',hl.location_code, 'Termination_Reason',decode(leaving_reason,null,'ZZ',
261 hr_us_w2_rep.get_leav_reason(leaving_reason)),
262 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_LAST_NAME')||' '
263 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_FIRST_NAME')||' '
264 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), 'A_PER_MIDDLE_NAMES'),1,1));
265
266 CURSOR c_get_asg_action IS
267 SELECT 'TRANSFER_ACT_ID=P',
268 pay_magtape_generic.get_parameter_value(
269 'TRANSFER_ACT_ID')
270 FROM DUAL;
271
272 PROCEDURE get_headers ;
273 PROCEDURE get_footers;
274 PROCEDURE fetch_w2c_xm;
275 FUNCTION get_outfile return VARCHAR2;
276
277 level_cnt NUMBER :=0;
278 EOL VARCHAR2(20) := fnd_global.local_chr(13)||fnd_global.local_chr(10);
279 g_temp_dir VARCHAR2(512);
280
281 g_min_chunk number:= -1;
282 g_archive_flag varchar2(1) := 'N';
283 g_bal_act_id number:= -1;
284
285 name_ssn_corrected varchar2(1) := 'N';
286 g_no_corrections number :=0;
287 wages_tips_compensation_c varchar2(25);
288 wages_tips_compensation_o varchar2(25);
289 fit_withheld_c varchar2(25);
290 fit_withheld_o varchar2(25);
291 ss_wages_o varchar2(25);
292 ss_wages_c varchar2(25);
293 ssn_o varchar2(100);
294 ssn_c varchar2(100);
295 emp_name_o varchar2(1000);
296 emp_name_c varchar2(1000);
297 last_name_o varchar2(1000);
298 last_name_c varchar2(1000);
299 emp_suffix_o varchar2(1000);
300 emp_suffix_c varchar2(1000);
301 ss_withheld_c varchar2(25);
302 ss_withheld_o varchar2(25);
303 med_wages_o varchar2(25);
304 med_wages_c varchar2(25);
305 med_withheld_o varchar2(25);
306 med_withheld_c varchar2(25);
307 ss_tips_c varchar2(25);
308 ss_tips_o varchar2(25);
309 allocated_tips_c varchar2(25);
310 allocated_tips_o varchar2(25);
311 eic_payment_c varchar2(25);
312 eic_payment_o varchar2(25);
313 dependent_care_c varchar2(25);
314 dependent_care_o varchar2(25);
315 non_qual_plan_c varchar2(25);
316 non_qual_plan_o varchar2(25);
317 stat_employee_c varchar2(1);
318 retirement_plan_c varchar2(1);
319 sick_pay_c varchar2(1);
320 stat_employee_o varchar2(1);
321 retirement_plan_o varchar2(1);
322 sick_pay_o varchar2(1);
323
324 END pay_us_w2c_rpt;