[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_BASELINE_DATA_DOWNLOAD
Source
1 PACKAGE BODY hr_h2pi_baseline_data_download AS
2 /* $Header: hrh2pipd.pkb 120.0 2005/05/31 00:40:58 appldev noship $ */
3
4 g_package VARCHAR2(80) := ' hr_h2pi_baseline_data_download.';
5 --
6 -- --------------------------------------------------------------------------------
7 -- Description: Procedure to download data from the H2PI views into a XML file
8 --
9 -- --------------------------------------------------------------------------------
10 --
11 procedure download ( p_errbuf OUT NOCOPY VARCHAR2,
12 p_retcode OUT NOCOPY NUMBER,
13 p_business_group_id IN NUMBER,
14 p_client_id IN NUMBER) IS
15
16 queryCtx DBMS_XMLQuery.ctxType;
17 xmlString1 CLOB := NULL;
18
19 xmlString2 CLOB := NULL;
20 dtdString CLOB := NULL;
21 --
22 x varchar2(10000);
23 y number(20);
24 --
25 l_request_id NUMBER(15);
26 lengthtoread NUMBER(10);
27 cloblength NUMBER(20);
28 l_query_string varchar2(10000);
29 l_proc varchar2(72) := g_package || 'download' ;
30 l_xml_header varchar2(80) := '<?xml version = ''1.0''?>';
31
32 BEGIN
33 hr_utility.set_location('Entering:' || l_proc,10);
34
35 l_request_id := hr_h2pi_download.get_request_id;
36
37 fnd_file.put_line(fnd_file.output,l_xml_header);
38 fnd_file.put_line(fnd_file.output,'<HR_H2PI_EMPLOYEES/>');
39 fnd_file.put_line(fnd_file.output,'');
40 fnd_file.put_line(fnd_file.output,l_xml_header);
41 fnd_file.put_line(fnd_file.output,'<HR_H2PI_ADDRESSES/>');
42 fnd_file.put_line(fnd_file.output,'');
43 --
44 -- For HR_H2PI_LOCATIONS_V VIEW
45 --
46 hr_utility.set_location(l_proc,60);
47 l_query_string := 'select ' ||
48 ' last_upd_date,' ||
49 TO_CHAR(p_business_group_id)||' business_group_id,'||
50 ' location_id, ' ||
51 ' location_code, ' ||
52 ' description, ' ||
53 ' address_line_1,' ||
54 ' address_line_2, ' ||
55 ' address_line_3, ' ||
56 ' town_or_city, ' ||
57 ' country, ' ||
58 ' postal_code, ' ||
59 ' region_1, ' ||
60 ' region_2, ' ||
61 ' region_3, ' ||
62 ' style, ' ||
63 ' inactive_date, ' ||
64 ' telephone_number_1, ' ||
65 ' telephone_number_2, ' ||
66 ' telephone_number_3, ' ||
67 ' loc_information13, ' ||
68 ' loc_information14, ' ||
69 ' loc_information15, ' ||
70 ' loc_information16, ' ||
71 ' loc_information17, ' ||
72 ' loc_information18, ' ||
73 ' loc_information19, ' ||
74 ' loc_information20, ' ||
75 ' attribute_category, ' ||
76 ' attribute1, ' ||
77 ' attribute2, ' ||
78 ' attribute3, ' ||
79 ' attribute4, ' ||
80 ' attribute5, ' ||
81 ' attribute6, ' ||
82 ' attribute7, ' ||
83 ' attribute8, ' ||
84 ' attribute9, ' ||
85 ' attribute10, ' ||
86 ' attribute11, ' ||
87 ' attribute12, ' ||
88 ' attribute13, ' ||
89 ' attribute14, ' ||
90 ' attribute15, ' ||
91 ' attribute16, ' ||
92 ' attribute17, ' ||
93 ' attribute18, ' ||
94 ' attribute19, ' ||
95 ' attribute20, ' ||
96 ' :q_client_id client_id ' ||
97 ' from hr_h2pi_locations_v ' ||
98 ' where ( business_group_id = :q_bg_id OR business_group_id is null)';
99 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
100 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
101 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
102 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_LOCATIONS');
103 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
104 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
105 hr_h2pi_download.write(x,y,xmlstring1);
106 DBMS_XMLQuery.closeContext(queryCtx);
107 xmlString1 := null;
108 hr_utility.set_location(l_proc,70);
109 --
110
111 --
112 fnd_file.put_line(fnd_file.output,l_xml_header);
113 fnd_file.put_line(fnd_file.output,'<HR_H2PI_ASSIGNMENTS/>');
114 fnd_file.put_line(fnd_file.output,'');
115
116 -- Following is for Baseline data
117
118 --
119 -- For HR_H2PI_PAY_BASES_V VIEW
120 --
121 hr_utility.set_location(l_proc,100);
122 queryCtx := DBMS_XMLQuery.newContext('select pay.*,:q_client_id client_id ' ||
123 ' from hr_h2pi_pay_bases_v pay ' ||
124 ' where business_group_id = :q_bg_id ' );
125 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_PAY_BASES');
126 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
127 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
128 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
129 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
130 hr_h2pi_download.write(x,y,xmlstring1);
131 DBMS_XMLQuery.closeContext(queryCtx);
132 xmlString1 := null;
133 hr_utility.set_location(l_proc,110);
134 --
135
136 fnd_file.put_line(fnd_file.output,l_xml_header);
137 fnd_file.put_line(fnd_file.output,'<HR_H2PI_HR_ORGANIZATIONS/>');
138 fnd_file.put_line(fnd_file.output,'');
139 --
140 -- For HR_H2PI_PAYROLLS_V VIEW
141 --
142 hr_utility.set_location(l_proc,140);
143 queryCtx := DBMS_XMLQuery.newContext('select pay.*,:q_client_id client_id from hr_h2pi_payrolls_v pay where business_group_id = :q_bg_id');
144 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_PAYROLLS');
145 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
146 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
147 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
148 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
149 hr_h2pi_download.write(x,y,xmlstring1);
150 DBMS_XMLQuery.closeContext(queryCtx);
151 xmlString1 := null;
152 hr_utility.set_location(l_proc,150);
153 --
154
155 --
156 -- For HR_H2PI_ELEMENT_TYPES_V VIEW
157 --
158 hr_utility.set_location(l_proc,160);
159 queryCtx := DBMS_XMLQuery.newContext(
160 'select last_upd_date, element_type_id, ' ||
161 ' business_group_id, ' ||
162 ' element_name, processing_type, ' ||
163 ' effective_start_date, effective_end_date, ' ||
164 ' :q_client_id client_id, ' ||
165 ' legislation_code ' ||
166 ' from hr_h2pi_element_types_v ' ||
167 ' where business_group_id = :q_bg_id ' ||
168 ' union select et1.last_update_date, et1.element_type_id, ' ||
169 TO_CHAR(p_business_group_id) ||
170 ', et1.element_name, et1.processing_type, ' ||
171 ' et1.effective_start_date, et1.effective_end_date, ' ||
172 ' :q_client_id1 client_id, ' ||
173 ' et1.legislation_code ' ||
174 ' from pay_element_types_f et1 ' ||
175 ' where et1.business_group_id is null ' ||
176 ' and et1.legislation_code = ''US'' ' );
177 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ELEMENT_TYPES');
178 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
179 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
180 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id1',p_client_id);
181 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
182 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
183 hr_h2pi_download.write(x,y,xmlstring1);
184 DBMS_XMLQuery.closeContext(queryCtx);
185 xmlString1 := null;
186 hr_utility.set_location(l_proc,170);
187 --
188
189 --
190 -- For HR_H2PI_INPUT_VALUES_V VIEW
191 --
192 hr_utility.set_location(l_proc,180);
193 queryCtx := DBMS_XMLQuery.newContext(
194 'select iv.last_upd_date, ' ||
195 ' iv.business_group_id, ' ||
196 ' iv.element_type_id, iv.effective_start_date, ' ||
197 ' iv.effective_end_date, iv.input_value_id, ' ||
198 ' iv.name, iv.uom, ' ||
199 ' iv.mandatory_flag, iv.default_value, iv.lookup_type, ' ||
200 ' :q_client_id client_id, ' ||
201 ' iv.legislation_code ' ||
202 ' from hr_h2pi_input_values_v iv ' ||
203 ' where business_group_id = :q_bg_id ' ||
204 ' union select et.last_update_date, ' ||
205 TO_CHAR(p_business_group_id) ||
206 ', et.element_type_id, iv.effective_start_date, ' ||
207 ' iv.effective_end_date, iv.input_value_id, ' ||
208 ' iv.name, iv.uom, ' ||
209 ' iv.mandatory_flag, iv.default_value, iv.lookup_type, ' ||
210 ' :q_client_id1 client_id, ' ||
211 ' iv.legislation_code ' ||
212 ' from pay_input_values_f iv, pay_element_types_f et ' ||
213 ' where et.element_type_id = iv.element_type_id ' ||
214 ' and iv.business_group_id is null ' ||
215 ' and iv.legislation_code = ''US'' ' ||
216 ' and iv.effective_start_date between ' ||
217 ' et.effective_start_date and et.effective_end_date ' );
218 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_INPUT_VALUES');
219 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
220 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
221 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id1',p_client_id);
222 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
223 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
224 hr_h2pi_download.write(x,y,xmlstring1);
225 DBMS_XMLQuery.closeContext(queryCtx);
226 xmlString1 := null;
227 hr_utility.set_location(l_proc,190);
228 --
229
230 --
231 -- For HR_H2PI_ELEMENT_LINKS_V VIEW
232 --
233 hr_utility.set_location(l_proc,200);
234 queryCtx := DBMS_XMLQuery.newContext(
235 'select el.last_update_date last_upd_date, ' ||
236 ' el.element_link_id, ' ||
237 ' el.business_group_id, el.effective_start_date, ' ||
238 ' el.effective_end_date, el.payroll_id, ' ||
239 ' el.cost_allocation_keyflex_id, el.element_type_id, ' ||
240 ' el.organization_id, el.location_id, el.pay_basis_id, ' ||
241 ' el.link_to_all_payrolls_flag, :q_client_id client_id ' ||
242 ' from pay_element_links_f el, ' ||
243 ' pay_element_types_f et ' ||
244 ' where el.business_group_id = :q_bg_id ' ||
245 ' and et.element_type_id = el.element_type_id ' ||
246 ' and (el.attribute2 = ''Y'' OR et.business_group_id IS NULL)');
247 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ELEMENT_LINKS');
248 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
249 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
250 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
251 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
252 hr_h2pi_download.write(x,y,xmlstring1);
253 DBMS_XMLQuery.closeContext(queryCtx);
254 xmlString1 := null;
255 hr_utility.set_location(l_proc,210);
256 --
257
258 --
259 -- For HR_H2PI_BG_AND_GRE_V VIEW
260 --
261 hr_utility.set_location(l_proc,220);
262 queryCtx := DBMS_XMLQuery.newContext('select bg.*,:q_client_id client_id from hr_h2pi_bg_and_gre_v bg where business_group_id = :q_bg_id');
263 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_BG_AND_GRE');
264 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
265 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
266 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
267 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
268 hr_h2pi_download.write(x,y,xmlstring1);
269 DBMS_XMLQuery.closeContext(queryCtx);
270 xmlString1 := null;
271 hr_utility.set_location(l_proc,230);
272 --
273
274 --
275 -- For HR_H2PI_ORG_PAYMENT_METHODS_V VIEW
276 --
277 hr_utility.set_location(l_proc,240);
278 queryCtx := DBMS_XMLQuery.newContext('select pmt.*,:q_client_id client_id from hr_h2pi_org_payment_methods_v pmt where business_group_id = :q_bg_id');
279 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
280 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
281 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ORG_PAYMENT_METHODS');
282 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
283 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
284 hr_h2pi_download.write(x,y,xmlstring1);
285 DBMS_XMLQuery.closeContext(queryCtx);
286 xmlString1 := null;
287 hr_utility.set_location(l_proc,260);
288 --
289 fnd_file.put_line(fnd_file.output,l_xml_header);
290 fnd_file.put_line(fnd_file.output,'<HR_H2PI_PATCH_STATUS/>');
291 fnd_file.put_line(fnd_file.output,'');
292 fnd_file.put_line(fnd_file.output,l_xml_header);
293 fnd_file.put_line(fnd_file.output,'<HR_H2PI_FEDERAL_TAX_RULES/>');
294 fnd_file.put_line(fnd_file.output,'');
295 fnd_file.put_line(fnd_file.output,l_xml_header);
296 fnd_file.put_line(fnd_file.output,'<HR_H2PI_STATE_TAX_RULES/>');
297 fnd_file.put_line(fnd_file.output,'');
298 fnd_file.put_line(fnd_file.output,l_xml_header);
299 fnd_file.put_line(fnd_file.output,'<HR_H2PI_COUNTY_TAX_RULES/>');
300 fnd_file.put_line(fnd_file.output,'');
301 fnd_file.put_line(fnd_file.output,l_xml_header);
302 fnd_file.put_line(fnd_file.output,'<HR_H2PI_CITY_TAX_RULES/>');
303 fnd_file.put_line(fnd_file.output,'');
304 fnd_file.put_line(fnd_file.output,l_xml_header);
308 fnd_file.put_line(fnd_file.output,'<HR_H2PI_PERIODS_OF_SERVICE/>');
305 fnd_file.put_line(fnd_file.output,'<HR_H2PI_ORGANIZATION_CLASS/>');
306 fnd_file.put_line(fnd_file.output,'');
307 fnd_file.put_line(fnd_file.output,l_xml_header);
309 fnd_file.put_line(fnd_file.output,'');
310 fnd_file.put_line(fnd_file.output,l_xml_header);
311 fnd_file.put_line(fnd_file.output,'<HR_H2PI_SALARIES/>');
312 fnd_file.put_line(fnd_file.output,'');
313
314 --
315 -- For HR_H2PI_ORGANIZATION_INFO_V VIEW
316 --
317 hr_utility.set_location(l_proc,420);
318 l_query_string := 'SELECT ogi.last_update_date last_upd_date, '||
319 'org.business_group_id, ogi.org_information_id, '||
320 'ogi.organization_id, ogi.org_information_context, '||
321 'ogi.org_information1, ogi.org_information2, '||
322 'ogi.org_information3, ogi.org_information4, '||
323 'ogi.org_information5, ogi.org_information6, '||
324 'ogi.org_information7, ogi.org_information8, '||
325 'ogi.org_information9, ogi.org_information10, '||
326 'ogi.org_information11, ogi.org_information12, '||
327 'ogi.org_information13, ogi.org_information14, '||
328 'ogi.org_information15, ogi.org_information16, '||
329 'ogi.org_information17, ogi.org_information18, '||
330 'ogi.org_information19, ogi.org_information20, '||
331 ':q_client_id client_id '||
332 'FROM hr_organization_units org, '||
333 ' hr_organization_information ogi, '||
334 ' hr_organization_information ogi2, '||
335 ' hr_org_info_types_by_class oitbc, '||
336 ' hr_org_information_types oit '||
337 'WHERE org.organization_id = ogi.organization_id '||
338 ' and ogi.organization_id = ogi2.organization_id '||
339 ' and ogi2.org_information_context = ''CLASS'' '||
340 ' and ogi2.org_information1 IN (''HR_BG'', ''HR_LEGAL'') '||
341 ' and ogi.org_information_context=oit.org_information_type '||
342 ' and ogi.org_information_context IN '||
343 '(''Work Day Information'', ''1099R Magnetic Report Rules'', '||
344 ' ''EEO-1 Filing'', ''Employer Identification'', '||
345 ' ''Federal Tax Rules'', ''NACHA Rules'', '||
346 ' ''SQWL Employer Rules 1'', ''SQWL Employer Rules 2'', '||
347 ' ''SQWL GN Transmitter Rules'', ''SQWL SS Transmitter Rules'', '||
348 ' ''PAY_US_STATE_WAGE_PLAN_INFO'', ''Costing Information'', '||
349 ' ''Organization Name Alias'', ''Work Day Information'', '||
350 ' ''Legal Entity Accounting'', ''Multiple Worksite Reporting'', '||
351 ' ''TIAA-CREF Setup Codes'', ''VETS-100 Filing'', '||
352 ' ''W2 Reporting Rules'') '||
353 ' and oitbc.org_classification=ogi2.org_information1 '||
354 ' and oitbc.org_information_type=oit.org_information_type '||
355 ' and (oit.legislation_code is NULL or oit.legislation_code = ''US'')'||
356 ' and oit.navigation_method = ''GS'' '||
357 ' and org.business_group_id = :q_bg_id ';
358 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
359 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
360 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
361 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ORGANIZATION_INFO');
362 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
363 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
364 hr_h2pi_download.write(x,y,xmlstring1);
365 DBMS_XMLQuery.closeContext(queryCtx);
366 xmlString1 := null;
367 hr_utility.set_location(l_proc,430);
368
369 fnd_file.put_line(fnd_file.output,l_xml_header);
370 fnd_file.put_line(fnd_file.output,'<HR_H2PI_COST_ALLOCATIONS/>');
371 fnd_file.put_line(fnd_file.output,'');
372 fnd_file.put_line(fnd_file.output,l_xml_header);
373 fnd_file.put_line(fnd_file.output,'<HR_H2PI_PAYMENT_METHODS/>');
374 fnd_file.put_line(fnd_file.output,'');
375 fnd_file.put_line(fnd_file.output,l_xml_header);
376 fnd_file.put_line(fnd_file.output,'<HR_H2PI_ELEMENT_NAMES/>');
377 fnd_file.put_line(fnd_file.output,'');
378 fnd_file.put_line(fnd_file.output,l_xml_header);
379 fnd_file.put_line(fnd_file.output,'<HR_H2PI_ELEMENT_ENTRIES/>');
380 fnd_file.put_line(fnd_file.output,'');
381 fnd_file.put_line(fnd_file.output,l_xml_header);
382 fnd_file.put_line(fnd_file.output,'<HR_H2PI_ELEMENT_ENTRY_VALUES/>');
383 fnd_file.put_line(fnd_file.output,'');
384 fnd_file.put_line(fnd_file.output,l_xml_header);
385 fnd_file.put_line(fnd_file.output,'<HR_H2PI_US_MODIFIED_GEOCODES/>');
386 fnd_file.put_line(fnd_file.output,'');
387 fnd_file.put_line(fnd_file.output,l_xml_header);
388 fnd_file.put_line(fnd_file.output,'<HR_H2PI_US_CITY_NAMES/>');
389 fnd_file.put_line(fnd_file.output,'');
390
391 COMMIT;
392 hr_utility.set_location('Leaving:' || l_proc,580);
393 END download ;
394
395 END hr_h2pi_baseline_data_download ;