[Home] [Help]
PACKAGE: APPS.PAY_US_W2_INFO_PKG
Source
1 PACKAGE PAY_US_W2_INFO_PKG AUTHID CURRENT_USER as
2 /* $Header: pyusw2dt.pkh 120.8.12020000.4 2012/10/30 12:23:41 pkoduri ship $ */
3
4
5 -- FUNCTION get_w2_data(p_assignment_action_id NUMBER) RETURN l_w2_fields_rec;
6
7 TYPE l_w2_fields_rec IS RECORD(
8 control_number NUMBER,
9 federal_ein VARCHAR2(100),
10 employer_name VARCHAR2(240), -- Bug# 14286448 Gre length issue
11 employer_address VARCHAR2(500),
12 SSN VARCHAR2(12),
13 emp_name VARCHAR2(200),
14 last_name VARCHAR2(200),
15 emp_suffix VARCHAR2(200), -- Bug 4523389
16 employee_address VARCHAR2(500),
17 wages_tips_compensation NUMBER,
18 fit_withheld NUMBER,
19 ss_wages NUMBER,
20 ss_withheld NUMBER,
21 med_wages NUMBER,
22 med_withheld NUMBER,
23 ss_tips NUMBER,
24 allocated_tips NUMBER,
25 eic_payment NUMBER,
26 dependent_care NUMBER,
27 non_qual_plan NUMBER,
28 stat_employee VARCHAR2(1),
29 retirement_plan VARCHAR2(1),
30 sick_pay VARCHAR2(1),
31 amended VARCHAR2(20),
32 amended_date DATE);
33
34 PROCEDURE get_w2_data(p_asg_action_id NUMBER,
35 p_tax_unit_id NUMBER,
36 p_year NUMBER,
37 p_error_msg out nocopy VARCHAR2);
38
39 FUNCTION create_xml_string (l_w2_fields l_w2_fields_rec,
40 l_box14_codea VARCHAR2,l_box14_meaninga VARCHAR2,
41 l_box14_codeb VARCHAR2,l_box14_meaningb VARCHAR2,
42 l_box14_codec VARCHAR2,l_box14_meaningc VARCHAR2,
43 l_box12_codea VARCHAR2,l_box12_meaninga VARCHAR2,
44 l_box12_codeb VARCHAR2,l_box12_meaningb VARCHAR2,
45 l_box12_codec VARCHAR2,l_box12_meaningc VARCHAR2,
46 l_box12_coded VARCHAR2,l_box12_meaningd VARCHAR2,
47 l_state1_code VARCHAR2,l_state1_ein VARCHAR2,
48 l_state1_wages VARCHAR2,l_state1_tax VARCHAR2,
49 l_local1_wages VARCHAR2,l_local1_tax VARCHAR2,
50 l_locality1 VARCHAR2,
51 l_state2_code VARCHAR2,l_state2_ein VARCHAR2,
52 l_state2_wages VARCHAR2, l_state2_tax VARCHAR2,
53 l_local2_wages VARCHAR2,l_local2_tax VARCHAR2,
54 l_locality2 VARCHAR2,p_year VARCHAR2)
55 RETURN BLOB;
56
57 FUNCTION fetch_w2_xml(p_assignment_action_id Number,
58 p_tax_unit_id NUMBER,
59 p_year NUMBER,
60 p_error_msg out nocopy VARCHAR2,
61 p_is_SS boolean) RETURN BLOB;
62
63 FUNCTION get_final_xml (p_assignment_action_id Number,
64 p_tax_unit_id NUMBER,
65 p_year NUMBER,
66 p_w2_template_location VARCHAR2,
67 p_inst_template_location VARCHAR2,
68 p_output_location VARCHAR2,
69 p_error_msg out nocopy VARCHAR2)
70 RETURN BLOB;
71
72 TYPE l_w2_fields_tab IS TABLE OF
73 l_w2_fields_rec
74 INDEX BY BINARY_INTEGER;
75
76 TYPE l_state_local_rec IS RECORD(
77 state_code VARCHAR2(20),
78 state_ein VARCHAR2(200),
79 state_wages VARCHAR2(20),
80 state_tax VARCHAR2(20),
81 locality VARCHAR2(100),
82 locality_wages NUMBER,
83 locality_tax NUMBER);
84
85 TYPE l_state_local_table IS TABLE OF
86 l_state_local_rec
87 INDEX BY BINARY_INTEGER;
88
89 TYPE l_state_rec IS RECORD(
90 state_code VARCHAR2(20),
91 state_ein VARCHAR2(200),
92 state_wages VARCHAR2(20),
93 state_tax VARCHAR2(20));
94
95 TYPE l_state_table IS TABLE OF
96 l_state_rec
97 INDEX BY BINARY_INTEGER;
98
99 TYPE l_local_rec IS RECORD(
100 locality VARCHAR2(100),
101 locality_wages NUMBER,
102 locality_tax NUMBER,
103 jurisdiction VARCHAR2(16), --Bug 13724610
104 state_code VARCHAR2(10),
105 tax_type VARCHAR2(100));
106
107
108 TYPE l_local_table IS TABLE OF
109 l_local_rec
110 INDEX BY BINARY_INTEGER;
111
112
113 TYPE l_box12_rec IS RECORD(
114 box12_meaning VARCHAR2(100),
115 box12_code VARCHAR2(100));
116
117
118 TYPE l_box12_table IS TABLE OF
119 l_box12_rec
120 INDEX BY BINARY_INTEGER;
121
122
123 TYPE l_box14_rec IS RECORD(
124 box14_meaning VARCHAR2(100),
125 box14_code VARCHAR2(100));
126
127
128 TYPE l_box14_table IS TABLE OF
129 l_box14_rec
130 INDEX BY BINARY_INTEGER;
131
132
133 CURSOR main_block IS
134 SELECT 'Version_Number=X' ,'Version 1.1'
135 FROM sys.dual;
136
137 CURSOR Transfer_Block IS
138 --changes for Bug 8876216
139 /* SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
140 FROM pay_assignment_actions paa
141 WHERE paa.payroll_action_id =
142 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');*/
143
144 SELECT 'TRANSFER_ACT_ID=P', mt.assignment_action_id
145 from hr_organization_units hou
146 , hr_locations_all hl
147 , per_periods_of_service pps
148 , per_assignments_f paf
149 , pay_assignment_actions mt
150 , pay_payroll_actions ppa
151 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
152 and mt.payroll_action_id = ppa.payroll_action_id
153 and paf.assignment_id = mt.assignment_id
154 and paf.effective_start_date = (select /*+ push_subq no_unnest */ max(paf2.effective_start_date) from per_assignments_f paf2 --Added Hint for Bug 13506529
155 where paf2.assignment_id = paf.assignment_id
156 and paf2.effective_start_date <= to_date('31-DEC-'||hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),'DD/MM/YYYY'))
157 and paf.effective_end_date >= to_date('01-JAN-'||hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),'DD/MM/YYYY')
158 and paf.assignment_type = 'E'
159 and pps.period_of_service_id = paf.period_of_service_id
160 and pps.person_id = paf.person_id
161 and hl.location_id = paf.location_id
162 and hou.organization_id = paf.organization_id
163 and hou.business_group_id + 0 = ppa.business_group_id
164 order by decode(hr_us_w2_mt.get_parameter('S1',ppa.legislative_parameters), 'Employee_Name',
165 hr_us_w2_rep.get_w2_employee_name(paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'YYYY/MM/DD')),
166 'SSN',nvl(hr_us_w2_rep.get_per_item( to_number(mt.serial_number), 'A_PER_NATIONAL_IDENTIFIER'), 'Applied For'),
167 '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),'YYYY/MM/DD')),
168 'Organization',hou.name, 'Location',hl.location_code,
169 'Termination_Reason', decode(leaving_reason,null,'ZZ',hr_us_w2_rep.get_leav_reason(leaving_reason)),
170 hr_us_w2_rep.get_w2_employee_name(paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'YYYY/MM/DD'))),
171 decode(hr_us_w2_mt.get_parameter('S2',ppa.legislative_parameters), 'Employee_Name',
172 hr_us_w2_rep.get_w2_employee_name(paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'YYYY/MM/DD')),
173 'SSN',nvl(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), 'A_PER_NATIONAL_IDENTIFIER'), 'Applied For'),
174 '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),'YYYY/MM/DD')),
175 'Organization',hou.name, 'Location',hl.location_code,
176 'Termination_Reason',decode(leaving_reason,null,'ZZ',hr_us_w2_rep.get_leav_reason(leaving_reason)),
177 hr_us_w2_rep.get_w2_employee_name(paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'YYYY/MM/DD'))),
178 decode(hr_us_w2_mt.get_parameter('S3',ppa.legislative_parameters), 'Employee_Name',
179 hr_us_w2_rep.get_w2_employee_name(paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'YYYY/MM/DD')),
180 'SSN',nvl(hr_us_w2_rep.get_per_item( to_number(mt.serial_number), 'A_PER_NATIONAL_IDENTIFIER'), 'Applied For'),
181 '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),'YYYY/MM/DD')),
182 'Organization',hou.name, 'Location',hl.location_code, 'Termination_Reason',decode(leaving_reason,null,'ZZ',hr_us_w2_rep.get_leav_reason(leaving_reason)),
183 hr_us_w2_rep.get_w2_employee_name(paf.person_id,to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'YYYY/MM/DD')));
184
185 --changes for Bug 8876216
186
187 CURSOR c_get_asg_action IS
188 SELECT 'TRANSFER_ACT_ID=P',
189 pay_magtape_generic.get_parameter_value(
190 'TRANSFER_ACT_ID')
191 FROM DUAL;
192
193 PROCEDURE get_headers ;
194 PROCEDURE get_footers;
195 PROCEDURE fetch_w2_xm;
196 FUNCTION get_outfile return VARCHAR2;
197
198 level_cnt NUMBER :=0;
199 EOL VARCHAR2(20) := fnd_global.local_chr(13)||fnd_global.local_chr(10);
200 g_temp_dir VARCHAR2(512);
201 END pay_us_w2_info_pkg;