eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;