[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_MAIN_UPLOAD
Source
1 PACKAGE BODY hr_h2pi_main_upload AS
2 /* $Header: hrh2pimn.pkb 120.0 2005/05/31 00:39:58 appldev noship $ */
3
4 g_package VARCHAR2(33) := ' hr_h2pi_main_upload.';
5
6 PROCEDURE clear_staging_tables (p_from_client_id NUMBER) IS
7
8 BEGIN
9
10 DELETE FROM hr_h2pi_employees
11 WHERE client_id = p_from_client_id;
12
13 DELETE FROM hr_h2pi_addresses
14 WHERE client_id = p_from_client_id;
15
16 DELETE FROM hr_h2pi_assignments
17 WHERE client_id = p_from_client_id;
18
19 DELETE FROM hr_h2pi_periods_of_service
20 WHERE client_id = p_from_client_id;
21
22 DELETE FROM hr_h2pi_locations
23 WHERE client_id = p_from_client_id;
24
25 DELETE FROM hr_h2pi_pay_bases
26 WHERE client_id = p_from_client_id;
27
28 DELETE FROM hr_h2pi_hr_organizations
29 WHERE client_id = p_from_client_id;
30
31 DELETE FROM hr_h2pi_organization_class
32 WHERE client_id = p_from_client_id;
33
34 DELETE FROM hr_h2pi_organization_info
35 WHERE client_id = p_from_client_id;
36
37 DELETE FROM hr_h2pi_payrolls
38 WHERE client_id = p_from_client_id;
39
40 DELETE FROM hr_h2pi_element_types
41 WHERE client_id = p_from_client_id;
42
43 DELETE FROM hr_h2pi_input_values
44 WHERE client_id = p_from_client_id;
45
46 DELETE FROM hr_h2pi_element_links
47 WHERE client_id = p_from_client_id;
48
49 DELETE FROM hr_h2pi_bg_and_gre
50 WHERE client_id = p_from_client_id;
51
52 DELETE FROM hr_h2pi_org_payment_methods
53 WHERE client_id = p_from_client_id;
54
55 DELETE FROM hr_h2pi_patch_status
56 WHERE client_id = p_from_client_id;
57
58 DELETE FROM hr_h2pi_federal_tax_rules
59 WHERE client_id = p_from_client_id;
60
61 DELETE FROM hr_h2pi_state_tax_rules
62 WHERE client_id = p_from_client_id;
63
64 DELETE FROM hr_h2pi_county_tax_rules
65 WHERE client_id = p_from_client_id;
66
67 DELETE FROM hr_h2pi_city_tax_rules
68 WHERE client_id = p_from_client_id;
69
70 DELETE FROM hr_h2pi_salaries
71 WHERE client_id = p_from_client_id;
72
73 DELETE FROM hr_h2pi_cost_allocations
74 WHERE client_id = p_from_client_id;
75
76 DELETE FROM hr_h2pi_payment_methods
77 WHERE client_id = p_from_client_id;
78
79 DELETE FROM hr_h2pi_element_names
80 WHERE client_id = p_from_client_id;
81
82 DELETE FROM hr_h2pi_element_entries
83 WHERE client_id = p_from_client_id;
84
85 DELETE FROM hr_h2pi_element_entry_values
86 WHERE client_id = p_from_client_id;
87
88
89 DELETE FROM hr_h2pi_bg_and_gre
90 WHERE client_id = p_from_client_id;
91
92 DELETE FROM hr_h2pi_payrolls
93 WHERE client_id = p_from_client_id;
94
95 DELETE FROM hr_h2pi_pay_bases
96 WHERE client_id = p_from_client_id;
97
98 DELETE FROM hr_h2pi_org_payment_methods
99 WHERE client_id = p_from_client_id;
100
101 DELETE FROM hr_h2pi_element_types
102 WHERE client_id = p_from_client_id;
103
104 DELETE FROM hr_h2pi_input_values
105 WHERE client_id = p_from_client_id;
106
107 DELETE FROM hr_h2pi_element_links
108 WHERE client_id = p_from_client_id;
109
110 DELETE FROM hr_h2pi_us_city_names
111 WHERE client_id = p_from_client_id;
112
113 DELETE FROM hr_h2pi_us_modified_geocodes
114 WHERE client_id = p_from_client_id;
115
116 END;
117
118
119 FUNCTION check_incomplete_upload RETURN BOOLEAN IS
120
121 l_proc varchar2(72) := g_package|| 'check_incomplete_upload';
122 l_stage_rec_count number(10):= 0 ;
123 l_from_client_id number(15);
124
125 BEGIN
126 hr_utility.set_location('Entering:'|| l_proc, 10);
127 l_from_client_id := hr_h2pi_upload.get_from_client_id;
128
129 select l_stage_rec_count + count(*)
130 into l_stage_rec_count
131 from hr_h2pi_addresses
132 where client_id = l_from_client_id;
133 select l_stage_rec_count + count(*)
134
135 into l_stage_rec_count
136 from hr_h2pi_assignments
137 where client_id = l_from_client_id;
138
139 select l_stage_rec_count + count(*)
140 into l_stage_rec_count
141 from hr_h2pi_bg_and_gre
142 where client_id = l_from_client_id;
143
144 select l_stage_rec_count + count(*)
145 into l_stage_rec_count
146 from hr_h2pi_city_tax_rules
147 where client_id = l_from_client_id;
148
149 select l_stage_rec_count + count(*)
150 into l_stage_rec_count
151 from hr_h2pi_cost_allocations
152 where client_id = l_from_client_id;
153
154 select l_stage_rec_count + count(*)
155 into l_stage_rec_count
156 from hr_h2pi_county_tax_rules
157 where client_id = l_from_client_id;
158
159 select l_stage_rec_count + count(*)
160 into l_stage_rec_count
161 from hr_h2pi_element_entries
162 where client_id = l_from_client_id;
163
164 select l_stage_rec_count + count(*)
165 into l_stage_rec_count
166 from hr_h2pi_element_entry_values
167 where client_id = l_from_client_id;
168
169 select l_stage_rec_count + count(*)
170 into l_stage_rec_count
171 from hr_h2pi_element_links
172 where client_id = l_from_client_id;
173
174 select l_stage_rec_count + count(*)
175 into l_stage_rec_count
176 from hr_h2pi_element_names
177 where client_id = l_from_client_id;
178
179 select l_stage_rec_count + count(*)
180 into l_stage_rec_count
181 from hr_h2pi_element_types
182 where client_id = l_from_client_id;
183
184 select l_stage_rec_count + count(*)
185 into l_stage_rec_count
186 from hr_h2pi_employees
187 where client_id = l_from_client_id;
188
189 select l_stage_rec_count + count(*)
190 into l_stage_rec_count
191 from hr_h2pi_federal_tax_rules
192 where client_id = l_from_client_id;
193
194 select l_stage_rec_count + count(*)
195 into l_stage_rec_count
196 from hr_h2pi_hr_organizations
197 where client_id = l_from_client_id;
198
199 select l_stage_rec_count + count(*)
200 into l_stage_rec_count
201 from hr_h2pi_input_values
202 where client_id = l_from_client_id;
203
204 select l_stage_rec_count + count(*)
205 into l_stage_rec_count
206 from hr_h2pi_locations
207 where client_id = l_from_client_id;
208
209 select l_stage_rec_count + count(*)
210 into l_stage_rec_count
211 from hr_h2pi_organization_class
212 where client_id = l_from_client_id;
213
214 select l_stage_rec_count + count(*)
215 into l_stage_rec_count
216 from hr_h2pi_organization_info
217 where client_id = l_from_client_id;
218
219 select l_stage_rec_count + count(*)
220 into l_stage_rec_count
221 from hr_h2pi_org_payment_methods
222 where client_id = l_from_client_id;
223
224 select l_stage_rec_count + count(*)
225 into l_stage_rec_count
226 from hr_h2pi_patch_status
227 where client_id = l_from_client_id;
228
229 select l_stage_rec_count + count(*)
230 into l_stage_rec_count
231 from hr_h2pi_payment_methods
232 where client_id = l_from_client_id;
233
234 select l_stage_rec_count + count(*)
235 into l_stage_rec_count
236 from hr_h2pi_payrolls
237 where client_id = l_from_client_id;
238
239 select l_stage_rec_count + count(*)
240 into l_stage_rec_count
241 from hr_h2pi_pay_bases
242 where client_id = l_from_client_id;
243
244 select l_stage_rec_count + count(*)
245 into l_stage_rec_count
246 from hr_h2pi_periods_of_service
247 where client_id = l_from_client_id;
248
249 select l_stage_rec_count + count(*)
250 into l_stage_rec_count
251 from hr_h2pi_salaries
252 where client_id = l_from_client_id;
253
254 select l_stage_rec_count + count(*)
255 into l_stage_rec_count
256 from hr_h2pi_state_tax_rules
257 where client_id = l_from_client_id;
258
259 select l_stage_rec_count + count(*)
260 into l_stage_rec_count
261 from hr_h2pi_us_city_names
262 where client_id = l_from_client_id;
263
264 select l_stage_rec_count + count(*)
265 into l_stage_rec_count
266 from hr_h2pi_us_modified_geocodes
267 where client_id = l_from_client_id;
268
269 hr_utility.set_location('Leaving:'|| l_proc, 30);
270 if l_stage_rec_count > 0 then
271 return TRUE;
272 else
273 return FALSE;
274 end if;
275 END;
276
277 FUNCTION get_request_id (p_process VARCHAR2) RETURN NUMBER IS
278
279 l_call_status BOOLEAN;
280 l_request_id number(15);
281 l_rphase varchar2(80);
282 l_rstatus varchar2(80);
283 l_dphase varchar2(80);
284 l_dstatus varchar2(80);
285 l_message varchar2(80);
286 l_proc varchar2(72) := g_package || 'get_request_id';
287 BEGIN
288 hr_utility.set_location('Entering:' || l_proc,10);
289 l_call_status := fnd_concurrent.get_request_status
290 (l_request_id,
291 'PER',
292 p_process,
293 l_rphase,
294 l_rstatus,
295 l_dphase,
296 l_dstatus,
297 l_message);
298 hr_utility.set_location('Leaving:' || l_proc,20);
299 return l_request_id;
300 EXCEPTION
301 when others then
302 hr_utility.set_location(l_proc,30);
303 fnd_message.raise_error;
304 END get_request_id;
305
306
307 FUNCTION get_from_business_group_id RETURN NUMBER IS
308
309 l_from_business_group_id NUMBER(15);
310 l_proc VARCHAR2(72) := g_package||'get_from_business_group_id';
311
312 BEGIN
313 hr_utility.set_location('Entering:'|| l_proc, 10);
314 l_from_business_group_id := hr_h2pi_map.get_from_id
315 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
316 p_to_id => hr_h2pi_upload.g_to_business_group_id);
317 IF l_from_business_group_id = -1 THEN
318 hr_utility.set_location(l_proc, 20);
319 hr_h2pi_error.data_error
320 (p_from_id => hr_h2pi_upload.g_to_business_group_id,
321 p_table_name => 'HR_H2PI_BG_AND_GRE',
322 p_message_level => 'FATAL',
323 p_message_name => 'HR_289241_MAPPING_ID_MISSING');
324 END IF;
325
326 hr_utility.set_location('Leaving:'|| l_proc, 30);
327 RETURN l_from_business_group_id;
328 END;
329
330 PROCEDURE upload_core (p_from_client_id NUMBER) IS
331
332 l_proc VARCHAR2(72) := g_package||'upload_core';
333 l_message VARCHAR2(2000);
334
335 BEGIN
336 hr_utility.set_location('Entering:'|| l_proc, 10);
337
338 hr_h2pi_validate.validate_bg_and_gre(p_from_client_id);
339 hr_h2pi_validate.validate_pay_basis(p_from_client_id);
340 hr_h2pi_validate.validate_payroll(p_from_client_id);
341 hr_h2pi_validate.validate_org_payment_method(p_from_client_id);
342 hr_h2pi_validate.validate_element_type(p_from_client_id);
343 hr_h2pi_validate.validate_element_link(p_from_client_id);
344 hr_h2pi_validate.validate_geocode(p_from_client_id);
345 IF hr_h2pi_error.check_for_errors THEN
346 hr_utility.set_location(l_proc, 20);
347 hr_h2pi_error.generate_error_report;
348 clear_staging_tables(p_from_client_id);
349 return;
350 END IF;
351
352 hr_utility.set_location(l_proc, 30);
353 hr_h2pi_bg_upload.upload_location(p_from_client_id);
354 hr_h2pi_bg_upload.upload_hr_organization(p_from_client_id);
355 hr_h2pi_bg_upload.upload_element_type(p_from_client_id);
356 IF hr_h2pi_error.check_for_errors THEN
357 hr_utility.set_location(l_proc, 40);
358 hr_h2pi_error.generate_error_report;
359 return;
360 END IF;
361
362 hr_utility.set_location(l_proc, 50);
363 hr_h2pi_person_upload.upload_person_level(p_from_client_id);
364 IF hr_h2pi_error.check_for_errors THEN
365 hr_utility.set_location(l_proc, 60);
366 hr_h2pi_error.generate_error_report;
367 return;
368 ELSE
369 DELETE FROM hr_h2pi_message_lines
370 WHERE to_business_group_id = hr_h2pi_upload.g_to_business_group_id;
371 fnd_message.set_name('PER', 'HR_289295_UPLOAD_SUCCESSFULL');
372 l_message := fnd_message.get_string('PER','HR_289295_UPLOAD_SUCCESSFULL');
373 fnd_file.put_line(FND_FILE.LOG,l_message);
374 END IF;
375
376 hr_utility.set_location('Leaving:'|| l_proc, 70);
377
378 END;
379
380 PROCEDURE upload (p_errbuf OUT NOCOPY VARCHAR2,
381 p_retcode OUT NOCOPY NUMBER,
382 p_file_name VARCHAR2,
383 p_business_group_id NUMBER) IS
384
385
386 l_proc VARCHAR2(72) := g_package||'upload';
387
388 l_from_business_group_id hr_all_organization_units.organization_id%TYPE;
389 l_from_client_id hr_all_organization_units.organization_id%TYPE;
390
391 BEGIN
392 hr_utility.set_location('Entering:'|| l_proc, 10);
393 hr_h2pi_upload.g_request_id := get_request_id('H2PI_UPLOAD');
394 hr_h2pi_upload.g_to_business_group_id := p_business_group_id;
395 l_from_client_id := hr_h2pi_upload.get_from_client_id;
396
397 IF check_incomplete_upload THEN
398 upload_core(l_from_client_id);
399 hr_utility.set_location('Leaving:'|| l_proc, 20);
400 ELSE
401 hr_h2pi_upload.upload(p_errbuf => p_errbuf,
402 p_retcode => p_retcode,
403 p_file_name => p_file_name);
404
405 upload_core(l_from_client_id);
406 hr_utility.set_location('Leaving:'|| l_proc, 20);
407 END IF;
408 END;
409
410 -- Remove retry upload after removing concurrent program.
411 --
412 PROCEDURE retry_upload (p_errbuf OUT NOCOPY VARCHAR2,
413 p_retcode OUT NOCOPY NUMBER,
414 p_business_group_id NUMBER) IS
415
416 l_proc VARCHAR2(72) := g_package||'upload';
417
418 l_from_business_group_id hr_all_organization_units.organization_id%TYPE;
419 l_from_client_id hr_all_organization_units.organization_id%TYPE;
420
421 BEGIN
422 hr_utility.set_location('Entering:'|| l_proc, 10);
423
424 hr_h2pi_upload.g_request_id := get_request_id('H2PI_RETRY_UPLOAD');
425 hr_h2pi_upload.g_to_business_group_id := p_business_group_id;
426 l_from_client_id := hr_h2pi_upload.get_from_client_id;
427
428 --upload_core(l_from_client_id);
429 hr_utility.set_location('Leaving:'|| l_proc, 20);
430 END;
431 --
432 END hr_h2pi_main_upload;