[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ONLINE_W4_XML_PKG
Source
1 PACKAGE BODY pay_us_online_w4_xml_pkg
2 /* $Header: pyw4xmlp.pkb 120.4 2012/01/19 11:31:23 rpahune ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2005 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 *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
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, *
17 * *
18 ******************************************************************
19
20 Name : pay_us_online_w4_xml_pkg
21
22 Description : This package contains the procedure generate_xml to
23 generate the XML extract for Online W4 PDF
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- -------------------------
29 31-MAY-2005 rsethupa 115.0 Created
30 14-JUN-2005 rsethupa 115.1 Comma printed between name
31 and address lines only if
32 required
33 31-JAN-2006 jgoswami 115.2 5012064 Modified c_get_personal_details
34 added date filters
35 21-MAY-2007 vaprakas 115.3 6029939 Modified code to fix a display issue.
36 27-JUN-2011 sgotlasw 115.4 12651843 Modified code to resolve
37 SAX Parser errors due to the
38 presence of special characters
39 in the address field.
40
41 *****************************************************************
42
43 ****************************************************************************
44 Procedure Name: generate_xml
45 Description: Returns the XML extract for Online W4 PDF
46 ***************************************************************************/
47 AS
48 PROCEDURE generate_xml (
49 p_person_id IN per_people_f.person_id%TYPE,
50 p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%TYPE,
51 p_temp_dir IN VARCHAR2,
52 p_appl_short_name IN VARCHAR2,
53 p_template_code IN VARCHAR2,
54 p_default_language IN VARCHAR2,
55 p_default_territory IN VARCHAR2,
56 p_xml_string OUT NOCOPY VARCHAR2
57 )
58 IS
59 CURSOR c_get_updated_values (cp_transaction_step_id IN NUMBER)
60 IS
61 SELECT fs.varchar2_value fs_value, allo.number_value allo_value,
62 aa.number_value aa_value, ex.varchar2_value ex_value,
63 lnd.varchar2_value lnd_value
64 FROM hr_api_transaction_values fs,
65 hr_api_transaction_values allo,
66 hr_api_transaction_values aa,
67 hr_api_transaction_values ex,
68 hr_api_transaction_values lnd
69 WHERE fs.transaction_step_id = cp_transaction_step_id
70 AND fs.NAME = 'P_FILING_STATUS'
71 AND allo.transaction_step_id = fs.transaction_step_id
72 AND allo.NAME = 'P_ALLOWANCES'
73 AND aa.transaction_step_id = fs.transaction_step_id
74 AND aa.NAME = 'P_ADDITIONAL_TAX'
75 AND ex.transaction_step_id = fs.transaction_step_id
76 AND ex.NAME = 'P_EXEMPT'
77 AND lnd.transaction_step_id = fs.transaction_step_id
78 AND lnd.NAME = 'P_LAST_NAME_DIFF';
79
80 CURSOR c_get_personal_details (cp_person_id IN NUMBER)
81 IS
82 SELECT ppf.first_name,
83 SUBSTR (ppf.middle_names, 1, 1) middle_initial,
84 ppf.last_name,
85 ppf.national_identifier,
86 pad.address_line1,
87 pad.address_line2,
88 pad.address_line3,
89 pad.town_or_city || ', ' || pad.region_2 || ', ' || pad.postal_code city_town_zip
90 FROM per_people_f ppf, per_addresses pad
91 WHERE ppf.person_id = cp_person_id
92 AND ppf.person_id = pad.person_id
93 AND trunc(sysdate) between ppf.effective_start_date
94 and ppf.effective_end_date
95 AND pad.primary_flag = 'Y'
96 AND trunc(sysdate) between pad.date_from
97 and nvl(pad.date_to, trunc(sysdate));
98
99 CURSOR c_get_lookup_codes (cp_meaning IN VARCHAR2)
100 IS
101 SELECT lookup_code
102 FROM fnd_common_lookups fcl
103 WHERE lookup_type = 'US_FIT_FILING_STATUS'
104 AND application_id = 800
105 AND meaning = cp_meaning;
106
107 l_filing_status VARCHAR2 (100);
108 l_allowances NUMBER;
109 l_additional_amount NUMBER;
110 l_exempt VARCHAR2 (100);
111 l_last_name_diff VARCHAR2 (2);
112 l_first_name VARCHAR2 (100);
113 l_middle_initial VARCHAR2 (100);
114 l_last_name VARCHAR2 (100);
115 l_national_identifier VARCHAR2 (100);
116 l_home_address VARCHAR2 (255);
117 l_address_line1 VARCHAR2 (255);
118 l_address_line2 VARCHAR2 (255);
119 l_address_line3 VARCHAR2 (255);
120 l_city_state_zip VARCHAR2 (100);
121 l_count NUMBER (15);
122 l_year VARCHAR2 (4);
123 l_filing_status_code VARCHAR2 (2);
124 l_fs_married VARCHAR2 (2);
125 l_fs_single VARCHAR2 (2);
126 l_fs_married_withhold VARCHAR2 (2);
127 l_xml_string VARCHAR2 (32767) DEFAULT NULL;
128 l_last_name_flag VARCHAR2 (1);
129 l_date DATE;
130 l_signature VARCHAR2 (100);
131 BEGIN
132 --hr_utility.trace_on (NULL, 'pyw4xmlp');
133 hr_utility.set_location ('pay_us_online_w4_xml_pkg.generate_xml', 10);
134 hr_utility.TRACE ('p_person_id: ' || p_person_id);
135 hr_utility.TRACE ('p_transaction_step_id: ' || p_transaction_step_id);
136 hr_utility.TRACE ('p_temp_dir: ' || p_temp_dir);
137 hr_utility.TRACE ('p_appl_short_name: ' || p_appl_short_name);
138 hr_utility.TRACE ('p_template_code: ' || p_template_code);
139 hr_utility.TRACE ('p_default_language: ' || p_default_language);
140 hr_utility.TRACE ('p_default_territory: ' || p_default_territory);
141 l_count := 0;
142
143 OPEN c_get_personal_details (p_person_id);
144
145 FETCH c_get_personal_details
146 INTO l_first_name, l_middle_initial, l_last_name,
147 l_national_identifier, l_address_line1, l_address_line2,
148 l_address_line3, l_city_state_zip;
149
150 CLOSE c_get_personal_details;
151
152 hr_utility.set_location ('pay_us_online_w4_xml_pkg.generate_xml', 20);
153
154 OPEN c_get_updated_values (p_transaction_step_id);
155
156 FETCH c_get_updated_values
157 INTO l_filing_status, l_allowances, l_additional_amount, l_exempt,
158 l_last_name_diff;
159
160 CLOSE c_get_updated_values;
161
162 hr_utility.set_location ('pay_us_online_w4_xml_pkg.generate_xml', 30);
163
164 OPEN c_get_lookup_codes (l_filing_status);
165
166 FETCH c_get_lookup_codes
167 INTO l_filing_status_code;
168
169 CLOSE c_get_lookup_codes;
170
171 hr_utility.set_location ('pay_us_online_w4_xml_pkg.generate_xml', 40);
172
173 IF l_filing_status_code = '01'
174 THEN
175 l_fs_single := 'Y';
176 ELSIF l_filing_status_code = '02'
177 THEN
178 l_fs_married := 'Y';
179 ELSE
180 l_fs_married_withhold := 'Y';
181 END IF;
182
183 hr_utility.set_location ('pay_us_online_w4_xml_pkg.generate_xml', 50);
184
185 SELECT SYSDATE, TO_CHAR (SYSDATE, 'YYYY')
186 INTO l_date, l_year
187 FROM DUAL;
188
189 l_xml_data_table (l_count).xml_tag := '<YEAR>';
190 l_xml_data_table (l_count).xml_data := l_year;
191
192 l_count := l_count + 1;
193 l_xml_data_table (l_count).xml_tag := '<FIRST_NAME>';
194 IF l_middle_initial IS NOT NULL
195 THEN
196 l_xml_data_table (l_count).xml_data :=
197 l_first_name || ', ' || l_middle_initial;
198 ELSE
199 l_xml_data_table (l_count).xml_data := l_first_name;
200 END IF;
201
202 l_count := l_count + 1;
203 l_xml_data_table (l_count).xml_tag := '<LAST_NAME>';
204 l_xml_data_table (l_count).xml_data := l_last_name;
205
206 l_count := l_count + 1;
207 l_xml_data_table (l_count).xml_tag := '<NATIONAL_IDENTIFIER>';
208 l_xml_data_table (l_count).xml_data := l_national_identifier;
209
210 l_count := l_count + 1;
211 l_xml_data_table (l_count).xml_tag := '<HOME_ADDRESS>';
212 l_home_address := l_address_line1;
213 IF l_address_line2 IS NOT NULL
214 THEN
215 l_home_address := l_home_address || ', ' || l_address_line2;
216 END IF;
217 IF l_address_line3 IS NOT NULL
218 THEN
219 l_home_address := l_home_address || ', ' || l_address_line3;
220 END IF;
221
222 -- replace statements added as part of bug 12651843
223 l_home_address := replace(l_home_address,'&','&');
224 l_home_address := replace(l_home_address,'<','<');
225
226 l_xml_data_table (l_count).xml_data := l_home_address;
227
228 l_count := l_count + 1;
229 l_xml_data_table (l_count).xml_tag := '<CITY_STATE_ZIP>';
230 l_xml_data_table (l_count).xml_data := l_city_state_zip;
231
232 l_count := l_count + 1;
233 l_xml_data_table (l_count).xml_tag := '<FILING_STATUS_SINGLE>';
234 l_xml_data_table (l_count).xml_data := l_fs_single;
235
236 l_count := l_count + 1;
237 l_xml_data_table (l_count).xml_tag := '<FILING_STATUS_MARRIED>';
238 l_xml_data_table (l_count).xml_data := l_fs_married;
239
240 l_count := l_count + 1;
241 l_xml_data_table (l_count).xml_tag := '<FILING_STATUS_MARRIED_WITHHOLD>';
242 l_xml_data_table (l_count).xml_data := l_fs_married_withhold;
243
244 l_count := l_count + 1;
245 l_xml_data_table (l_count).xml_tag := '<LAST_NAME_DIFF>';
246 l_xml_data_table (l_count).xml_data := l_last_name_diff;
247
248 l_count := l_count + 1;
249 l_xml_data_table (l_count).xml_tag := '<TOTAL_ALLOWANCES>';
250 l_xml_data_table (l_count).xml_data := l_allowances;
251
252 l_count := l_count + 1;
253 l_xml_data_table (l_count).xml_tag := '<ADDITIONAL_AMOUNT>';
254 IF l_additional_amount = 0
255 THEN
256 l_xml_data_table (l_count).xml_data := l_additional_amount;
257 ELSE
258 l_xml_data_table (l_count).xml_data := ltrim(to_char(round(l_additional_amount,2),'9999999999999.00'));
259 END IF;
260
261 l_count := l_count + 1;
262 l_xml_data_table (l_count).xml_tag := '<EXEMPT>';
263
264 IF l_exempt = 'Yes'
265 THEN
266 l_xml_data_table (l_count).xml_data := 'Exempt';
267 ELSE
268 l_xml_data_table (l_count).xml_data := NULL;
269 END IF;
270
271 l_count := l_count + 1;
272 IF l_middle_initial IS NOT NULL
273 THEN
274 l_signature := l_first_name || ' ' || l_middle_initial || '. ' || l_last_name;
275 ELSE
276 l_signature := l_first_name || ' ' || l_last_name;
277 END IF;
278
279 l_xml_data_table (l_count).xml_tag := '<SIGNATURE>';
280 l_xml_data_table (l_count).xml_data := l_signature;
281
282 l_count := l_count + 1;
283 l_xml_data_table (l_count).xml_tag := '<DATE>';
284 l_xml_data_table (l_count).xml_data := l_date;
285
286 l_count := l_count + 1;
287 l_xml_data_table (l_count).xml_tag := '<FORMW4_YEAR>';
288 l_xml_data_table (l_count).xml_data := l_year;
289
290 l_xml_string := '<?xml version="1.0" encoding="UTF-8"?>';
291 l_xml_string :=
292 l_xml_string
293 || '<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">';
294 l_xml_string := l_xml_string || '<xapi:request>';
295 l_xml_string := l_xml_string || '<xapi:delivery>';
296 l_xml_string :=
297 l_xml_string
298 || '<xapi:filesystem output="'
299 || p_temp_dir
300 || 'onlinew4_'
301 || p_person_id
302 || '.pdf">';
303 l_xml_string := l_xml_string || '</xapi:filesystem>';
304 l_xml_string := l_xml_string || '</xapi:delivery>';
305 l_xml_string := l_xml_string || '<xapi:document output-type="pdf">';
306 l_xml_string :=
307 l_xml_string
308 || '<xapi:template type="xsl-fo" location="xdo://'
309 || p_appl_short_name
310 || '.'
311 || p_template_code
312 || '.'
313 || p_default_language
314 || '.'
315 || p_default_territory
316 || '">';
317 l_xml_string := l_xml_string || '<xapi:data>';
318 l_xml_string := l_xml_string || '<START>';
319 hr_utility.set_location ('pay_us_online_w4_xml_pkg.generate_xml', 60);
320
321 FOR counter IN l_xml_data_table.FIRST .. l_xml_data_table.LAST
322 LOOP
323 l_xml_string :=
324 l_xml_string
325 || l_xml_data_table (counter).xml_tag
326 || l_xml_data_table (counter).xml_data
327 || '</'
328 || SUBSTR (l_xml_data_table (counter).xml_tag, 2);
329 END LOOP;
330
331 l_xml_string :=
332 l_xml_string
333 || '</START></xapi:data></xapi:template></xapi:document></xapi:request></xapi:requestset>';
334 p_xml_string := l_xml_string;
335 hr_utility.set_location ('pay_us_online_w4_xml_pkg.generate_xml', 70);
336 END generate_xml;
337 END pay_us_online_w4_xml_pkg;