[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_DOWNLOAD
Source
1 PACKAGE BODY hr_h2pi_download AS
2 /* $Header: hrh2pidl.pkb 120.0 2005/05/31 00:38:49 appldev noship $ */
3
4 g_package VARCHAR2(33) := ' hr_h2pi_download.';
5 --
6 -- --------------------------------------------------------------------------------
7 -- Description: Local procedure to insert record into hr_h2pi_data_feed_hist table
8 -- once download a xml file is created and download is completed.
9 -- This table keeps records of all the downloads.
10 --
11 -- --------------------------------------------------------------------------------
12 --
13 procedure record_extract_history (p_bg_id in number ,
14 p_client_id in number,
15 p_request_id in number,
16 p_start_date in date,
17 p_end_date in date) is
18
19 l_business_group_name hr_all_organization_units.name%TYPE;
20 l_proc varchar2(72) := g_package || 'record_extract_history' ;
21 begin
22 hr_utility.set_location('Entering:' || l_proc,10);
23 select name
24 into l_business_group_name
25 from hr_all_organization_units
26 where organization_id = p_bg_id;
27 insert into hr_h2pi_data_feed_hist
28 (start_date,
29 end_date,
30 sequence_number,
31 business_group_id,
32 client_id,
33 business_group_name,
34 object_version_number,
35 request_id,
36 program_application_id,
37 program_id,
38 program_update_date)
39 values
40 (p_start_date,
41 p_end_date,
42 hr_h2pi_data_feed_hist_s.nextval,
43 p_bg_id,
44 p_client_id,
45 l_business_group_name,
46 1,
47 p_request_id,
48 null,
49 null,
50 sysdate);
51 hr_utility.set_location('Leaving:' || l_proc,20);
52 exception
53 when no_data_found then
54 hr_utility.set_location(l_proc || l_proc,30);
55 fnd_message.set_name('PER','HR_6673_PO_EMP_NO_BG');
56 fnd_message.raise_error;
57 END record_extract_history;
58 --
59 --
60 -- --------------------------------------------------------------------------------
61 -- Description: Local function to get the values corresponding to ids for the DFF
62 --
63 -- --------------------------------------------------------------------------------
64 --
65
66 FUNCTION get_value_from_id(p_org_information_id in number,
67 p_org_info_number in number) return varchar2 is
68
69 CURSOR csr_org_info IS
70 SELECT ogi.org_information_context context
71 ,ogi.org_information1 ogi1
72 ,ogi.org_information2 ogi2
73 ,ogi.org_information3 ogi3
74 ,ogi.org_information4 ogi4
75 ,ogi.org_information5 ogi5
76 ,ogi.org_information6 ogi6
77 ,ogi.org_information7 ogi7
78 ,ogi.org_information8 ogi8
79 ,ogi.org_information9 ogi9
80 ,ogi.org_information10 ogi10
81 ,ogi.org_information11 ogi11
82 ,ogi.org_information12 ogi12
83 ,ogi.org_information13 ogi13
84 ,ogi.org_information14 ogi14
85 ,ogi.org_information15 ogi15
86 ,ogi.org_information16 ogi16
87 ,ogi.org_information17 ogi17
88 ,ogi.org_information18 ogi18
89 ,ogi.org_information19 ogi19
90 ,ogi.org_information20 ogi20
91 from hr_organization_units org,
92 hr_organization_information ogi,
93 hr_organization_information ogi2,
94 hr_org_info_types_by_class oitbc,
95 hr_org_information_types oit
96 where org.organization_id = ogi.organization_id
97 and ogi.organization_id = ogi2.organization_id
98 and ogi2.org_information_context = 'CLASS'
99 and ogi2.org_information1 IN ('HR_ORG', 'HR_LEGAL', 'HR_BG', 'HR_PAYEE', 'US_CARRIER', 'US_WC_CARRIER ')
100 and ogi.org_information_context = oit.org_information_type
101 and ogi.org_information_context IN ('Work Day Information',
102 '1099R Magnetic Report Rules',
103 'EEO-1 Filing',
104 'Employer Identification',
105 'Federal Tax Rules',
106 'NACHA Rules',
107 'SQWL Employer Rules 1',
108 'SQWL Employer Rules 2',
109 'SQWL GN Transmitter Rules',
110 'SQWL SS Transmitter Rules',
111 'PAY_US_STATE_WAGE_PLAN_INFO',
112 'Costing Information',
113 'Organization Name Alias',
114 'Work Day Information',
115 'Legal Entity Accounting',
116 'Multiple Worksite Reporting',
117 'TIAA-CREF Setup Codes',
118 'VETS-100 Filing',
119 'W2 Reporting Rules',
120 'State Tax Rules',
121 'Local Tax Rules')
122 and oitbc.org_classification = ogi2.org_information1
123 and oitbc.org_information_type = oit.org_information_type
124 and (oit.legislation_code is NULL or oit.legislation_code = 'US')
125 and ogi.org_information_id = p_org_information_id;
126
127 l_seg_id VARCHAR2(100);
128 l_seg_value VARCHAR2(100);
129 l_seg_desc VARCHAR2(100);
130 l_return_status BOOLEAN;
131
132 TYPE t_org_info IS RECORD
133 (column_seq_num number
134 ,column_name varchar2(30)
135 );
136
137 TYPE tab_org_info IS TABLE OF t_org_info
138 INDEX BY BINARY_INTEGER;
139
140 CURSOR csr_flex_cols(p_context VARCHAR2) IS
141 SELECT column_seq_num,
142 application_column_name col_name
143 FROM fnd_descr_flex_column_usages
144 WHERE application_id = 800
145 AND descriptive_flex_context_code = p_context
146 ORDER BY column_seq_num;
147
148 idx NUMBER;
149 i NUMBER;
150 l_proc varchar2(72) := g_package || '.get_value_from_id' ;
151
152 BEGIN
153 hr_utility.set_location('Entering:' || l_proc,10);
154 FOR v_rec IN csr_org_info LOOP
155 fnd_flex_descval.set_column_value('ORG_INFORMATION_CONTEXT', v_rec.context);
156 fnd_flex_descval.set_column_value('ORG_INFORMATION1', v_rec.ogi1 );
157 fnd_flex_descval.set_column_value('ORG_INFORMATION2', v_rec.ogi2 );
158 fnd_flex_descval.set_column_value('ORG_INFORMATION3', v_rec.ogi3 );
159 fnd_flex_descval.set_column_value('ORG_INFORMATION4', v_rec.ogi4 );
160 fnd_flex_descval.set_column_value('ORG_INFORMATION5', v_rec.ogi5 );
161 fnd_flex_descval.set_column_value('ORG_INFORMATION6', v_rec.ogi6 );
162 fnd_flex_descval.set_column_value('ORG_INFORMATION7', v_rec.ogi7 );
163 fnd_flex_descval.set_column_value('ORG_INFORMATION8', v_rec.ogi8 );
164 fnd_flex_descval.set_column_value('ORG_INFORMATION9', v_rec.ogi9 );
165 fnd_flex_descval.set_column_value('ORG_INFORMATION10', v_rec.ogi10);
166 fnd_flex_descval.set_column_value('ORG_INFORMATION11', v_rec.ogi11);
167 fnd_flex_descval.set_column_value('ORG_INFORMATION12', v_rec.ogi12);
168 fnd_flex_descval.set_column_value('ORG_INFORMATION13', v_rec.ogi13);
169 fnd_flex_descval.set_column_value('ORG_INFORMATION14', v_rec.ogi14);
170 fnd_flex_descval.set_column_value('ORG_INFORMATION15', v_rec.ogi15);
171 fnd_flex_descval.set_column_value('ORG_INFORMATION16', v_rec.ogi16);
172 fnd_flex_descval.set_column_value('ORG_INFORMATION17', v_rec.ogi17);
173 fnd_flex_descval.set_column_value('ORG_INFORMATION18', v_rec.ogi18);
174 fnd_flex_descval.set_column_value('ORG_INFORMATION19', v_rec.ogi19);
175 fnd_flex_descval.set_column_value('ORG_INFORMATION20', v_rec.ogi20);
176
177 l_return_status := fnd_flex_descval.VALIDATE_DESCCOLS
178 (
179 appl_short_name => 'PER',
180 desc_flex_name => 'Org Developer DF',
181 values_or_ids => 'I'
182 );
183
184 i:=1;
185
186 FOR v_org_info IN csr_flex_cols(v_rec.context) LOOP
187 IF v_org_info.col_name = 'ORG_INFORMATION'||TO_CHAR(p_org_info_number) THEN
188 idx := i;
189 hr_utility.trace('Column Name : ' || v_org_info.col_name);
190 END IF;
191 i:=i+1;
192 END LOOP;
193
194 select fnd_flex_descval.segment_id(idx+1),
195 fnd_flex_descval.segment_value(idx+1),
196 fnd_flex_descval.segment_description(idx+1)
197 into l_seg_id,
198 l_seg_value,
199 l_seg_desc
200 from dual;
201
202 return(l_seg_value);
203
204 END LOOP;
205 hr_utility.set_location('Leaving:' || l_proc,90);
206 END;
207 --
208 --
209 -- --------------------------------------------------------------------------------
210 -- Description: Local procedure to get the concurrent request id by calling
211 -- fnd_concurrent.get_request_status function.
212 --
213 -- --------------------------------------------------------------------------------
214 --
215 function get_request_id return number is
216
217 l_call_status BOOLEAN;
218 l_request_id number(15);
219 l_rphase varchar2(80);
220 l_rstatus varchar2(80);
221 l_dphase varchar2(80);
222 l_dstatus varchar2(80);
223 l_message varchar2(80);
224 l_proc varchar2(72) := g_package || 'get_request_id';
225 BEGIN
226 hr_utility.set_location('Entering:' || l_proc,10);
227 l_call_status := fnd_concurrent.get_request_status
228 (l_request_id,
229 'PER',
230 'H2PI_DOWNLOAD',
231 l_rphase,
232 l_rstatus,
233 l_dphase,
234 l_dstatus,
235 l_message);
236 hr_utility.set_location('Leaving:' || l_proc,20);
237 return l_request_id;
238 EXCEPTION
239 when others then
240 hr_utility.set_location(l_proc,30);
241 fnd_message.raise_error;
242 END get_request_id;
243 --
244 -- --------------------------------------------------------------------------------
245 --
246
247 --
248 -- --------------------------------------------------------------------------------
249 -- Description: Procedure to write data into a file, in XML format as provided
250 -- by XML to SQL Utility (XSU).
251 -- --------------------------------------------------------------------------------
252 --
253 procedure write ( p_errbuf out nocopy varchar2,
254 p_retcode out nocopy number,
255 p_clob_to_write clob ) is
256
257 l_cloblength number(30);
258 l_counter number(20);
259 l_offset number(20);
260 l_lengthtoread constant number:= 32767;
261 l_string varchar2(32767);
262
263 l_length_done number(30);
264 l_new_position number(10);
265
266 l_proc varchar2(72) := g_package || 'write' ;
267
268 begin
269 hr_utility.set_location('Entering:' || l_proc,10);
270 l_cloblength := dbms_lob.getlength(p_clob_to_write);
271 l_offset := 0;
272 l_new_position := 0;
273 loop
274 l_string := dbms_lob.substr(p_clob_to_write,l_lengthtoread, l_offset + 1);
275 if length(l_string) > 32766 then
276 l_new_position := instr(l_string,'</ROW>',-1)+ 5;
277 l_string := substr(l_string,1,l_new_position);
278 else
279 l_new_position := 32767;
280 end if;
281 fnd_file.put_line(fnd_file.output,l_string);
282 l_offset := l_offset + l_new_position + 1;
283 l_string := null;
284 if ( (l_offset) >= l_cloblength ) then
285 exit;
286 end if;
287 end loop;
288 hr_utility.set_location('Leaving:' || l_proc,20);
289 end write;
290 --
291 -- --------------------------------------------------------------------------------
292 -- Description: Procedure to download data from the H2PI views into a XML file
293 --
294 -- --------------------------------------------------------------------------------
295 --
296 procedure download ( p_errbuf OUT NOCOPY VARCHAR2,
297 p_retcode OUT NOCOPY NUMBER,
298 p_business_group_id IN NUMBER,
299 p_transfer_start_date IN VARCHAR2,
300 p_transfer_end_date IN VARCHAR2,
301 p_client_id IN NUMBER) IS
302
303 queryCtx DBMS_XMLQuery.ctxType;
304 xmlString1 CLOB := NULL;
305
306 xmlString2 CLOB := NULL;
307 dtdString CLOB := NULL;
308 --
309 x varchar2(10000);
310 y number(20);
311 --
312 l_start_date CONSTANT DATE := fnd_date.canonical_to_date(p_transfer_start_date);
313 l_end_date CONSTANT DATE := fnd_date.canonical_to_date(p_transfer_end_date);
314 l_request_id NUMBER(15);
315 lengthtoread NUMBER(10);
316 cloblength NUMBER(20);
317 l_query_string varchar2(10000);
318 l_proc varchar2(72) := g_package || 'download' ;
319
320 BEGIN
321 hr_utility.set_location('Entering:' || l_proc,10);
322
323 l_request_id := get_request_id;
324
325 --
326 -- For HR_H2PI_EMPLOYEES_V VIEW
327 --
328 hr_utility.set_location(l_proc,20);
329 l_query_string := 'select emp.*, :q_client_id client_id from hr_h2pi_employees_v emp where business_group_id = :q_bg_id and last_upd_date between ' ||
330 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
331 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
332 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
333 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
334 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_EMPLOYEES');
335 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
336 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
337 write(x,y,xmlstring1);
338 DBMS_XMLQuery.closeContext(queryCtx);
339 xmlString1 := null;
340 hr_utility.set_location(l_proc,30);
341 --
342
343 --
344 -- For HR_H2PI_ADDRESSES_V VIEW
345 --
346 hr_utility.set_location(l_proc,40);
347 l_query_string := 'select adr.last_upd_date, GREATEST(per.per_date, adr.date_from) date_from,adr.business_group_id, adr.address_id, adr.person_id, adr.style, ' ||
348 ' adr.date_to, adr.address_type, adr.address_line1, adr.address_line2, adr.address_line3, adr.town_or_city, adr.region_1, adr.region_2, ' ||
349 ' adr.region_3, adr.postal_code, adr.country, adr.telephone_number_1, adr.telephone_number_2, adr.telephone_number_3, adr.add_information13, ' ||
350 ' adr.add_information14, adr.add_information15, adr.add_information16, adr.add_information17, adr.add_information18, adr.add_information19, ' ||
351 ' adr.add_information20, adr.addr_attribute_category, adr.addr_attribute1, adr.addr_attribute2, adr.addr_attribute3, adr.addr_attribute4, ' ||
352 ' adr.addr_attribute5, adr.addr_attribute6, adr.addr_attribute7, adr.addr_attribute8, adr.addr_attribute9, adr.addr_attribute10, ' ||
353 ' adr.addr_attribute11, adr.addr_attribute12, adr.addr_attribute13, adr.addr_attribute14, adr.addr_attribute15, adr.addr_attribute16, ' ||
354 ' adr.addr_attribute17, adr.addr_attribute18, adr.addr_attribute19, adr.addr_attribute20, :q_client_id client_id ' ||
355 ' FROM hr_h2pi_addresses_v adr, ' ||
356 ' (select min(effective_start_date) per_date, person_id from per_all_people_f a, per_person_types b where b.system_person_type IN (''EMP'', ''EMP_APL'') ' ||
357 ' and a.person_type_id = b.person_type_id group by person_id) per, per_all_people_f per2 ' ||
358 ' WHERE adr.person_id = per.person_id AND per.person_id = per2.person_id ' ||
359 ' AND per.per_date = per2.effective_start_date and adr.business_group_id = :q_bg_id and ( per2.last_update_date between ' ||
360 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
361 ' OR per.person_id IN (select adr1.person_id from hr_h2pi_addresses_v adr1 where adr1.business_group_id = :q_bg_id1 and adr1.last_upd_date between ' ||
362 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
363 ' group by adr1.person_id having count(*)> 0 ) )' ;
364
365 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
366 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
367 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
368 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id1',p_business_group_id);
369 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ADDRESSES');
370 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
371 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
372 write(x,y,xmlstring1);
373 DBMS_XMLQuery.closeContext(queryCtx);
374 xmlString1 := null;
375 hr_utility.set_location(l_proc,50);
376 --
377
378 --
379 -- For HR_H2PI_LOCATIONS_V VIEW
380 --
381 hr_utility.set_location(l_proc,60);
382 l_query_string := 'select ' ||
383 ' last_upd_date,' ||
384 ' nvl(business_group_id,:q_bg_id1 ) business_group_id, ' ||
385 ' location_id, ' ||
386 ' location_code, ' ||
387 ' description, ' ||
388 ' address_line_1,' ||
389 ' address_line_2, ' ||
390 ' address_line_3, ' ||
391 ' town_or_city, ' ||
392 ' country, ' ||
393 ' postal_code, ' ||
394 ' region_1, ' ||
395 ' region_2, ' ||
396 ' region_3, ' ||
397 ' style, ' ||
398 ' inactive_date, ' ||
399 ' telephone_number_1, ' ||
400 ' telephone_number_2, ' ||
401 ' telephone_number_3, ' ||
402 ' loc_information13, ' ||
403 ' loc_information14, ' ||
404 ' loc_information15, ' ||
405 ' loc_information16, ' ||
406 ' loc_information17, ' ||
407 ' loc_information18, ' ||
408 ' loc_information19, ' ||
409 ' loc_information20, ' ||
410 ' attribute_category, ' ||
411 ' attribute1, ' ||
412 ' attribute2, ' ||
413 ' attribute3, ' ||
414 ' attribute4, ' ||
415 ' attribute5, ' ||
416 ' attribute6, ' ||
417 ' attribute7, ' ||
418 ' attribute8, ' ||
419 ' attribute9, ' ||
420 ' attribute10, ' ||
421 ' attribute11, ' ||
422 ' attribute12, ' ||
423 ' attribute13, ' ||
424 ' attribute14, ' ||
425 ' attribute15, ' ||
426 ' attribute16, ' ||
427 ' attribute17, ' ||
428 ' attribute18, ' ||
429 ' attribute19, ' ||
430 ' attribute20, ' ||
431 ' :q_client_id client_id ' ||
432 ' from hr_h2pi_locations_v where ( business_group_id = :q_bg_id OR business_group_id is null ) ' ||
433 ' and last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
434 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
435 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id1',p_business_group_id);
436 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
437 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
438 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_LOCATIONS');
439 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
440 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
441 write(x,y,xmlstring1);
442 DBMS_XMLQuery.closeContext(queryCtx);
443 xmlString1 := null;
444 hr_utility.set_location(l_proc,70);
445 --
446
447 --
448 -- For HR_H2PI_ASSIGNMENTS_V VIEW
449 --
450 hr_utility.set_location(l_proc,80);
451 l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_assignments_v p1 where business_group_id = :q_bg_id and effective_start_date >= ' ||
452 ' ((select min(effective_start_date) from hr_h2pi_assignments_v p2 where p2.last_upd_date between ' ||
453 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
454 ' and p2.assignment_id = p1.assignment_id ))';
455 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
456 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
457 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
458 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ASSIGNMENTS');
459 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
460 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
461 write(x,y,xmlstring1);
462 DBMS_XMLQuery.closeContext(queryCtx);
463 xmlString1 := null;
464 hr_utility.set_location(l_proc,90);
465 --
466
467 -- Following is for Baseline data
468
469 --
470 -- For HR_H2PI_PAY_BASES_V VIEW
471 --
472 hr_utility.set_location(l_proc,100);
473 queryCtx := DBMS_XMLQuery.newContext('select pay.*,:q_client_id client_id from hr_h2pi_pay_bases_v pay where business_group_id = :q_bg_id');
474 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_PAY_BASES');
475 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
476 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
477 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
478 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
479 write(x,y,xmlstring1);
480 DBMS_XMLQuery.closeContext(queryCtx);
481 xmlString1 := null;
482 hr_utility.set_location(l_proc,110);
483 --
484
485 --
486 -- For HR_H2PI_HR_ORGANIZATIONS_V VIEW
487 --
488 hr_utility.set_location(l_proc,120);
489 queryCtx := DBMS_XMLQuery.newContext('select org.*,:q_client_id client_id from hr_h2pi_hr_organizations_v org where business_group_id = :q_bg_id and ' ||
490 ' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ');
491 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_HR_ORGANIZATIONS');
492 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
493 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
494 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
495 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
496 write(x,y,xmlstring1);
497 DBMS_XMLQuery.closeContext(queryCtx);
498 xmlString1 := null;
499 hr_utility.set_location(l_proc,130);
500 --
501
502 --
503 -- For HR_H2PI_PAYROLLS_V VIEW
504 --
505 hr_utility.set_location(l_proc,140);
506 queryCtx := DBMS_XMLQuery.newContext('select pay.*,:q_client_id client_id from hr_h2pi_payrolls_v pay where business_group_id = :q_bg_id');
507 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_PAYROLLS');
508 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
509 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
510 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
511 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
512 write(x,y,xmlstring1);
513 DBMS_XMLQuery.closeContext(queryCtx);
514 xmlString1 := null;
515 hr_utility.set_location(l_proc,150);
516 --
517
518 --
519 -- For HR_H2PI_ELEMENT_TYPES_V VIEW
520 --
521 hr_utility.set_location(l_proc,160);
522 queryCtx := DBMS_XMLQuery.newContext('select et.*,:q_client_id client_id from hr_h2pi_element_types_v et where business_group_id = :q_bg_id');
523 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ELEMENT_TYPES');
524 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
525 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
526 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
527 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
528 write(x,y,xmlstring1);
529 DBMS_XMLQuery.closeContext(queryCtx);
530 xmlString1 := null;
531 hr_utility.set_location(l_proc,170);
532 --
533
534 --
535 -- For HR_H2PI_INPUT_VALUES_V VIEW
536 --
537 hr_utility.set_location(l_proc,180);
538 queryCtx := DBMS_XMLQuery.newContext('select iv.*,:q_client_id client_id from hr_h2pi_input_values_v iv where business_group_id = :q_bg_id');
539 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_INPUT_VALUES');
540 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
541 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
542 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
543 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
544 write(x,y,xmlstring1);
545 DBMS_XMLQuery.closeContext(queryCtx);
546 xmlString1 := null;
547 hr_utility.set_location(l_proc,190);
548 --
549
550 --
551 -- For HR_H2PI_ELEMENT_LINKS_V VIEW
552 --
553 hr_utility.set_location(l_proc,200);
554 queryCtx := DBMS_XMLQuery.newContext('select el.*,:q_client_id client_id from hr_h2pi_element_links_v el where business_group_id = :q_bg_id');
555 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ELEMENT_LINKS');
556 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
557 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
558 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
559 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
560 write(x,y,xmlstring1);
561 DBMS_XMLQuery.closeContext(queryCtx);
562 xmlString1 := null;
563 hr_utility.set_location(l_proc,210);
564 --
565
566 --
567 -- For HR_H2PI_BG_AND_GRE_V VIEW
568 --
569 hr_utility.set_location(l_proc,220);
570 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');
571 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_BG_AND_GRE');
572 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
573 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
574 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
575 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
576 write(x,y,xmlstring1);
577 DBMS_XMLQuery.closeContext(queryCtx);
578 xmlString1 := null;
579 hr_utility.set_location(l_proc,230);
580 --
581
582 --
583 -- For HR_H2PI_ORG_PAYMENT_METHODS_V VIEW
584 --
585 hr_utility.set_location(l_proc,240);
586 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');
587 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
588 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
589 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ORG_PAYMENT_METHODS');
590 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
591 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
592 write(x,y,xmlstring1);
593 DBMS_XMLQuery.closeContext(queryCtx);
594 xmlString1 := null;
595 hr_utility.set_location(l_proc,260);
596 --
597
598 --
599 -- For HR_H2PI_PATCH_STATUS_V VIEW
600 --
601 hr_utility.set_location(l_proc,270);
602 queryCtx := DBMS_XMLQuery.newContext('select pat.*,:q_bg_id business_group_id,:q_client_id client_id from hr_h2pi_patch_status_v pat');
603 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_PATCH_STATUS');
604 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
605 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
606 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
607 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
608 write(x,y,xmlstring1);
609 DBMS_XMLQuery.closeContext(queryCtx);
610 xmlString1 := null;
611 hr_utility.set_location(l_proc,280);
612 --
613
614 --
615 -- For HR_H2PI_FEDERAL_TAX_RULES_V VIEW
616 --
617 hr_utility.set_location(l_proc,290);
618 l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_federal_tax_rules_v p1 where business_group_id = :q_bg_id ' ||
619 ' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_federal_tax_rules_v p2 where p2.last_upd_date between ' ||
620 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
621 ' and p2.emp_fed_tax_rule_id = p1.emp_fed_tax_rule_id ))' ;
622 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
623 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_FEDERAL_TAX_RULES');
624 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
625 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
626 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
627 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
628 write(x,y,xmlstring1);
629 DBMS_XMLQuery.closeContext(queryCtx);
630 xmlString1 := null;
631 hr_utility.set_location(l_proc,300);
632 --
633
634 --
635 -- For HR_H2PI_STATE_TAX_RULES_V VIEW
636 --
637 hr_utility.set_location(l_proc,310);
638 l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_state_tax_rules_v p1 where business_group_id = :q_bg_id' ||
639 ' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_state_tax_rules_v p2 where p2.last_upd_date between ' ||
640 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
641 ' and p2.emp_state_tax_rule_id = p1.emp_state_tax_rule_id ))';
642 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
643 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_STATE_TAX_RULES');
644 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
645 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
646 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
647 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
648 write(x,y,xmlstring1);
649 DBMS_XMLQuery.closeContext(queryCtx);
650 xmlString1 := null;
651 hr_utility.set_location(l_proc,320);
652 --
653
654
655 --
656 -- For HR_H2PI_COUNTY_TAX_RULES_V VIEW
657 --
658 hr_utility.set_location(l_proc,330);
659 l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_county_tax_rules_v p1 where business_group_id = :q_bg_id' ||
660 ' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_county_tax_rules_v p2 where p2.last_upd_date between ' ||
661 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
662 ' and p2.emp_county_tax_rule_id = p1.emp_county_tax_rule_id ))';
663 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
664 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_COUNTY_TAX_RULES');
665 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
666 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
667 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
668 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
669 write(x,y,xmlstring1);
670 DBMS_XMLQuery.closeContext(queryCtx);
671 xmlString1 := null;
672 hr_utility.set_location(l_proc,340);
673 --
674
675 --
676 -- For HR_H2PI_CITY_TAX_RULES_V VIEW
677 --
678 hr_utility.set_location(l_proc,350);
679 l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_city_tax_rules_v p1 where business_group_id = :q_bg_id' ||
680 ' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_city_tax_rules_v p2 where p2.last_upd_date between ' ||
681 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
682 ' and p2.emp_city_tax_rule_id = p1.emp_city_tax_rule_id ))';
683 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
684 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_CITY_TAX_RULES');
685 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
686 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
687 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
688 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
689 write(x,y,xmlstring1);
690 DBMS_XMLQuery.closeContext(queryCtx);
691 xmlString1 := null;
692 hr_utility.set_location(l_proc,360);
693 --
694
695 --
696 -- For HR_H2PI_ORGANIZATION_CLASS_V VIEW
697 --
698 hr_utility.set_location(l_proc,370);
699 l_query_string := 'select org.*,:q_client_id client_id from hr_h2pi_organization_class_v org where business_group_id = :q_bg_id and ' ||
700 ' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
701 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
702 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
703 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
704 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ORGANIZATION_CLASS');
705 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
706 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
707 write(x,y,xmlstring1);
708 DBMS_XMLQuery.closeContext(queryCtx);
709 xmlString1 := null;
710 hr_utility.set_location(l_proc,380);
711 --
712
713 --
714 -- For HR_H2PI_PERIODS_OF_SERVICE_V VIEW
715 --
716
717 hr_utility.set_location(l_proc,380);
718 l_query_string := 'select pos.*,:q_client_id client_id from hr_h2pi_periods_of_service_v pos where business_group_id = :q_bg_id and ' ||
719 ' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
720 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
721 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
722 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
723 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_PERIODS_OF_SERVICE');
724 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
725 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
726 write(x,y,xmlstring1);
727 DBMS_XMLQuery.closeContext(queryCtx);
728 xmlString1 := null;
729 hr_utility.set_location(l_proc,390);
730 --
731
732 --
733 -- For HR_H2PI_SALARIES_V VIEW
734 --
735 hr_utility.set_location(l_proc,400);
736 l_query_string := 'select sal.*,:q_client_id client_id from hr_h2pi_salaries_v sal where business_group_id = :q_bg_id and ' ||
737 ' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
738 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
739 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
740 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
741 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_SALARIES');
742 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
743 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
744 write(x,y,xmlstring1);
745 DBMS_XMLQuery.closeContext(queryCtx);
746 xmlString1 := null;
747 hr_utility.set_location(l_proc,410);
748 --
749
750 --
751 -- For HR_H2PI_ORGANIZATION_INFO_V VIEW
752 --
753 hr_utility.set_location(l_proc,420);
754 l_query_string := 'select org.*,:q_client_id client_id from hr_h2pi_organization_info_v org where business_group_id = :q_bg_id ' ||
755 ' and last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
756 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
757 hr_utility.trace(l_query_string);
758 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
759 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
760 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ORGANIZATION_INFO');
761 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
762 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
763 write(x,y,xmlstring1);
764 DBMS_XMLQuery.closeContext(queryCtx);
765 xmlString1 := null;
766 hr_utility.set_location(l_proc,430);
767 --
768
769 --
770 -- For HR_H2PI_COST_ALLOCATIONS_V VIEW
771 --
772 hr_utility.set_location(l_proc,440);
773 l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_cost_allocations_v p1 where business_group_id = :q_bg_id ' ||
774 ' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_cost_allocations_v p2 where p2.last_upd_date between ' ||
775 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
776 ' and p2.cost_allocation_id = p1.cost_allocation_id ))';
777 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
778 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
779 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
780 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_COST_ALLOCATIONS');
781 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
782 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
783 write(x,y,xmlstring1);
784 DBMS_XMLQuery.closeContext(queryCtx);
785 xmlString1 := null;
786 hr_utility.set_location(l_proc,450);
787 --
788
789
790 --
791 -- For HR_H2PI_PAYMENT_METHODS_V VIEW
792 --
793 hr_utility.set_location(l_proc,460);
794 l_query_string := 'select p1.*,:q_client_id client_id from hr_h2pi_payment_methods_v p1 where business_group_id = :q_bg_id ' ||
795 ' and ( (p1.payee_type <> ''P'') OR (p1.payee_type IS NULL) )' ||
796 ' and effective_start_date >= ((select min(effective_start_date) from hr_h2pi_payment_methods_v p2 where p2.last_upd_date between ' ||
797 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
798 ' and p2.personal_payment_method_id = p1.personal_payment_method_id ))';
799 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
800 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
801 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
802 --DBMS_XMLQuery.setBindValue(queryCtx,'q_payee_type','P');
803 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_PAYMENT_METHODS');
804 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
805 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
806 write(x,y,xmlstring1);
807 DBMS_XMLQuery.closeContext(queryCtx);
808 xmlString1 := null;
809 hr_utility.set_location(l_proc,470);
810 --
811
812 --
813 -- For HR_H2PI_ELEMENT_NAMES_V VIEW
814 --
815 hr_utility.set_location(l_proc,480);
816 l_query_string := 'select en.*,:q_client_id client_id from hr_h2pi_element_names_v en where business_group_id = :q_bg_id and ' ||
817 ' last_upd_date between to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ';
818 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
819 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
820 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
821 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ELEMENT_NAMES');
822 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
823 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
824 write(x,y,xmlstring1);
825 DBMS_XMLQuery.closeContext(queryCtx);
826 xmlString1 := null;
827 hr_utility.set_location(l_proc,490);
828 --
829
830 --
831 -- For HR_H2PI_ELEMENT_ENTRIES_V VIEW
832 --
833 hr_utility.set_location(l_proc,500);
834 l_query_string := 'select p1.*, :q_client_id client_id from hr_h2pi_element_entries_v p1 where business_group_id = :q_bg_id and ' ||
835 ' effective_start_date >= ((select min(effective_start_date) from hr_h2pi_element_entries_v p2 where p2.last_upd_date between ' ||
836 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
837 ' and p2.element_entry_id = p1.element_entry_id ))';
838 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
839 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
840 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
841 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ELEMENT_ENTRIES');
842 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
843 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
844 write(x,y,xmlstring1);
845 DBMS_XMLQuery.closeContext(queryCtx);
846 xmlString1 := null;
847 hr_utility.set_location(l_proc,510);
848 --
849
850 --
851 -- For HR_H2PI_ELEMENT_ENTRY_VALUES_V VIEW
852 --
853 --
854 hr_utility.set_location(l_proc,520);
855 l_query_string := 'select p1.*, :q_client_id client_id from hr_h2pi_element_entry_values_v p1 where business_group_id = :q_bg_id and ' ||
856 ' effective_start_date >= ((select min(effective_start_date) from hr_h2pi_element_entry_values_v p2 where p2.last_upd_date between ' ||
857 ' to_date(''' || to_char(l_start_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') and to_date(''' || to_char(l_end_date, 'yyyy/mm/dd:hh24:mi:ss') || ''', ''yyyy/mm/dd:hh24:mi:ss'') ' ||
858 ' and p2.element_entry_id = p1.element_entry_id ))';
859 queryCtx := DBMS_XMLQuery.newContext(l_query_string);
860 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
861 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
862 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_ELEMENT_ENTRY_VALUES');
863 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
864 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
865 write(x,y,xmlstring1);
866 DBMS_XMLQuery.closeContext(queryCtx);
867 xmlString1 := null;
868 hr_utility.set_location(l_proc,530);
869 --
870
871 --
872 -- For HR_H2PI_US_MODIFIED_GEOCODES_V VIEW
873 --
874 hr_utility.set_location(l_proc,540);
875 queryCtx := DBMS_XMLQuery.newContext('select geo.*, :q_bg_id business_group_id, :q_client_id client_id from hr_h2pi_us_modified_geocodes_v geo where patch_name = (select NVL(MAX(patch_name),''GEOCODE_1900_Q1'') FROM hr_h2pi_patch_status_v) ');
876 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
877 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
878 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_US_MODIFIED_GEOCODES');
879 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
880 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
881 write(x,y,xmlstring1);
882 DBMS_XMLQuery.closeContext(queryCtx);
883 xmlString1 := null;
884 hr_utility.set_location(l_proc,550);
885 --
886
887 --
888 -- For HR_H2PI_US_CITIES_V VIEW
889 --
890 hr_utility.set_location(l_proc,560);
891 queryCtx := DBMS_XMLQuery.newContext('select cit.*, :q_bg_id business_group_id, :q_client_id client_id from hr_h2pi_us_city_names_v cit');
892 DBMS_XMLQuery.setBindValue(queryCtx,'q_bg_id',p_business_group_id);
893 DBMS_XMLQuery.setRowsetTag(queryCtx,'HR_H2PI_US_CITY_NAMES');
894 DBMS_XMLQuery.setBindValue(queryCtx,'q_client_id',p_client_id);
895 DBMS_XMLQuery.setMaxRows(queryCtx,99999999);
896 xmlString1 := DBMS_XMLQuery.getXML(queryCtx);
897 write(x,y,xmlstring1);
898 DBMS_XMLQuery.closeContext(queryCtx);
899 xmlString1 := null;
900 hr_utility.set_location(l_proc,570);
901 --
902
903 record_extract_history(p_business_group_id,p_client_id,l_request_id,l_start_date, l_end_date);
904
905 commit;
906 hr_utility.set_location('Leaving:' || l_proc,580);
907 END download ;
908
909 END hr_h2pi_download ;