[Home] [Help]
PACKAGE BODY: APPS.HR_DE_ORGANIZATION_UPLOAD
Source
4 --
1 PACKAGE BODY hr_de_organization_upload AS
2 /* $Header: pedeoupl.pkb 115.3 2003/05/21 16:56:26 pkakar noship $ */
3 --
5 -- Cursor to return tax office definitions.
6 --
7 CURSOR c_tax_office_defs
8 (p_bundesland VARCHAR2) IS
9 SELECT hr_general.decode_lookup('DE_FED_STATE', bundesland) bundesland_description
10 ,bundesland
11 ,tax_office_no
12 ,tax_office_name
13 FROM hr_de_tax_office_definition_v
14 WHERE bundesland = NVL(p_bundesland, bundesland)
15 ORDER BY hr_general.decode_lookup('DE_FED_STATE', bundesland) ASC;
16 --
17 --
18 -- Cursor to return social insurance provider definitions.
19 --
20 CURSOR c_soc_ins_prov_defs
21 (p_provider_type VARCHAR2) IS
22 SELECT provider_name
23 ,hr_general.decode_lookup('DE_ORG_UPLOAD_PROV_TYPES', provider_type) provider_type_description
24 ,provider_type
25 ,health_insurance_type
26 ,LPAD(east_betriebesnumber, 8, '0') east_betriebesnumber
27 ,LPAD(west_betriebesnumber, 8, '0') west_betriebesnumber
28 ,pension_insurance_type
29 FROM hr_de_socins_prov_definition_v
30 WHERE provider_type = NVL(p_provider_type, provider_type)
31 ORDER BY hr_general.decode_lookup('DE_ORG_UPLOAD_PROV_TYPES', provider_type);
32 --
33 --
34 -- Cursor to return organization information.
35 --
36 CURSOR c_org_info
37 (p_business_group_id NUMBER
38 ,p_organization_name VARCHAR2) IS
39 SELECT organization_id
40 ,object_version_number
41 FROM hr_all_organization_units
42 WHERE business_group_id = p_business_group_id
43 AND name = p_organization_name;
44 --
45 --
46 -- Cursor to return organization classifications.
47 --
48 CURSOR c_org_class
49 (p_organization_id NUMBER) IS
50 SELECT org_information2 || ':' || org_information1 value
51 FROM hr_organization_information
52 WHERE organization_id = p_organization_id;
53 --
54 --
55 -- Cursor to return tax office information.
56 --
57 CURSOR c_tax_office_info
58 (p_business_group_id NUMBER
59 ,p_tax_office_no VARCHAR2) IS
60 SELECT o.organization_id
61 ,o.name
62 FROM hr_all_organization_units o
63 ,hr_organization_information i
64 WHERE o.business_group_id = p_business_group_id
65 AND i.organization_id = o.organization_id
66 AND i.org_information_context = 'DE_TAX_OFFICE_INFO'
67 AND i.org_information1 = p_tax_office_no;
68 --
69 --
70 -- Cursor to return mandatory health provider information.
71 --
72 CURSOR c_mand_health_prov_info
73 (p_business_group_id NUMBER
74 ,p_east_betriebsnummer VARCHAR2
75 ,p_west_betriebsnummer VARCHAR2) IS
76 SELECT o.organization_id
77 ,o.name
78 FROM hr_all_organization_units o
79 ,hr_organization_information i
80 WHERE o.business_group_id = p_business_group_id
81 AND i.organization_id = o.organization_id
82 AND i.org_information_context = 'DE_SOCIAL_INSURANCE_INFO'
86 -- Cursor to return business group information.
83 AND (i.org_information1 = p_west_betriebsnummer OR i.org_information2 = p_east_betriebsnummer);
84 --
85 --
87 --
88 CURSOR c_bg_info
89 (p_business_group_id NUMBER) IS
90 SELECT *
91 FROM per_business_groups
92 WHERE business_group_id = p_business_group_id;
93 --
94 --
95 -- Table Definitions.
96 --
97 TYPE t_tax_office_list IS TABLE OF c_tax_office_info%ROWTYPE INDEX BY BINARY_INTEGER;
98 TYPE t_mand_health_prov_list IS TABLE OF c_mand_health_prov_info%ROWTYPE INDEX BY BINARY_INTEGER;
99 --
100 --
101 -- --------------------------------------------------------------------------
102 -- Service function to decode avalue i.e. based on the expression return one
103 -- of two possible values.
104 -- --------------------------------------------------------------------------
105 --
106 FUNCTION local_decode
107 (p_expr BOOLEAN
108 ,p_true_value VARCHAR2
109 ,p_false_value VARCHAR2) RETURN VARCHAR2 IS
110 BEGIN
111 IF p_expr THEN
112 RETURN p_true_value;
113 ELSE
114 RETURN p_false_value;
115 END IF;
116 END local_decode;
117 --
118 --
119 -- --------------------------------------------------------------------------
120 -- Service function to return information for an organization.
121 --
122 -- This returns the following ...
123 --
124 -- 1. organization_id
125 -- 2. list of organization classifications for the organization
126 -- --------------------------------------------------------------------------
127 --
128 PROCEDURE org_info
129 (p_business_group_id NUMBER
130 ,p_organization_name VARCHAR2
131 ,o_organization_id OUT NOCOPY NUMBER
132 ,o_classification_list OUT NOCOPY VARCHAR2) IS
133 --
134 --
135 -- Local variables.
136 --
137 l_classification_list VARCHAR2(2000) := '';
138 l_org_rec c_org_info%ROWTYPE;
139 BEGIN
140 --
141 --
142 -- See if the organization exists.
143 --
144 OPEN c_org_info(p_business_group_id => p_business_group_id, p_organization_name => p_organization_name);
145 FETCH c_org_info INTO l_org_rec;
146 CLOSE c_org_info;
147 --
148 --
149 -- Organization exsists so build up a list of its classifications.
150 --
151 IF l_org_rec.organization_id IS NOT NULL THEN
152 FOR l_class_rec IN c_org_class(p_organization_id => l_org_rec.organization_id) LOOP
153 l_classification_list := l_classification_list || l_class_rec.value || ':';
154 END LOOP;
155 END IF;
156 --
157 --
158 -- Pass back the information.
159 --
160 o_organization_id := l_org_rec.organization_id;
161 o_classification_list := l_classification_list;
162 END org_info;
163 --
164 --
165 -- --------------------------------------------------------------------------
166 -- Service function to return information for a tax office.
167 --
168 -- This returns a list of organization_id / organization name pairs in a table.
169 -- --------------------------------------------------------------------------
170 --
171 PROCEDURE tax_office_info
172 (p_business_group_id NUMBER
173 ,p_tax_office_no VARCHAR2
174 ,o_tax_office_list OUT NOCOPY t_tax_office_list) IS
175 --
176 --
177 -- Local variables.
178 --
179 l_tax_office_list t_tax_office_list;
180 l_index NUMBER := 1;
181 l_tax_office_rec c_tax_office_info%ROWTYPE;
185 -- Find first matching tax office.
182 BEGIN
183 --
184 --
186 --
187 OPEN c_tax_office_info
188 (p_business_group_id => p_business_group_id
189 ,p_tax_office_no => p_tax_office_no);
190 FETCH c_tax_office_info INTO l_tax_office_rec;
191 --
192 --
193 -- Loop for all matching tax offices.
194 --
195 WHILE c_tax_office_info%FOUND LOOP
196 l_tax_office_list(l_index) := l_tax_office_rec;
197 FETCH c_tax_office_info INTO l_tax_office_rec;
198 l_index := l_index + 1;
199 END LOOP;
200 --
201 --
202 -- All matching tax offices have been found so close the cursor.
203 --
204 CLOSE c_tax_office_info;
205 --
206 --
207 -- Return matching list of tax offices.
208 --
209 o_tax_office_list := l_tax_office_list;
210 END tax_office_info;
211 --
212 --
213 -- --------------------------------------------------------------------------
214 -- Service function to return information for a mandatory health provider.
215 --
216 -- This returns a list of organization_id / organization name pairs in a table.
217 -- --------------------------------------------------------------------------
218 --
219 PROCEDURE mand_health_info
220 (p_business_group_id NUMBER
221 ,p_east_betriebsnummer VARCHAR2
222 ,p_west_betriebsnummer VARCHAR2
223 ,o_mand_health_prov_list OUT NOCOPY t_mand_health_prov_list) IS
224 --
225 --
226 -- Local variables.
227 --
228 l_mand_health_prov_list t_mand_health_prov_list;
229 l_index NUMBER := 1;
230 l_mand_health_prov_rec c_mand_health_prov_info%ROWTYPE;
231 BEGIN
232 --
233 --
234 -- Find first matching mandatory health provider.
235 --
236 OPEN c_mand_health_prov_info
237 (p_business_group_id => p_business_group_id
238 ,p_east_betriebsnummer => p_east_betriebsnummer
239 ,p_west_betriebsnummer => p_west_betriebsnummer);
240 FETCH c_mand_health_prov_info INTO l_mand_health_prov_rec;
241 --
242 --
243 -- Loop for all matching mandatory health providers.
244 --
245 WHILE c_mand_health_prov_info%FOUND LOOP
246 l_mand_health_prov_list(l_index) := l_mand_health_prov_rec;
247 FETCH c_mand_health_prov_info INTO l_mand_health_prov_rec;
248 l_index := l_index + 1;
249 END LOOP;
250 --
251 --
252 -- All matching mandatory health providers have been found so close the cursor.
253 --
254 CLOSE c_mand_health_prov_info;
255 --
256 --
257 -- Return matching list of mandatory health providers.
258 --
259 o_mand_health_prov_list := l_mand_health_prov_list;
260 END mand_health_info;
261 --
262 --
263 --
264 -- --------------------------------------------------------------------------
265 -- Service function to create a new tax office.
266 --
267 -- This involves the following sequence ...
268 --
269 -- 1. Create an organization.
270 -- 2. Classify it as a tax office.
271 -- 3. Add tax office specifc information e.g. tax office number.
272 -- --------------------------------------------------------------------------
273 --
274 PROCEDURE create_tax_office
275 (p_effective_date DATE
276 ,p_business_group_id NUMBER
277 ,p_date_from DATE
278 ,p_bundesland VARCHAR2
279 ,p_tax_office_no VARCHAR2
280 ,p_tax_office_name VARCHAR2) IS
281 --
282 --
283 -- Local Variables.
284 --
285 l_organization_id NUMBER;
286 l_org_information_id NUMBER;
287 l_object_version_number NUMBER;
288 BEGIN
289 --
290 --
291 -- Create basic organization definition.
292 --
293 hr_organization_api.create_organization
294 (p_validate => false
295 ,p_effective_date => p_effective_date
296 ,p_language_code => 'US'
297 ,p_date_from => p_date_from
298 ,p_business_group_id => p_business_group_id
299 ,p_name => p_tax_office_name
300 ,p_organization_id => l_organization_id
301 ,p_object_version_number => l_object_version_number);
302 --
303 --
304 -- Classify the organization as a tax office.
305 --
306 hr_organization_api.create_org_class_internal
307 (p_validate => false
308 ,p_effective_date => p_effective_date
309 ,p_organization_id => l_organization_id
310 ,p_org_classif_code => 'DE_TAX_OFFICE'
311 ,p_org_information_id => l_org_information_id
312 ,p_object_version_number => l_object_version_number
313 );
314 --
315 --
316 -- Add extra organization information.
317 --
318 hr_organization_api.create_org_information
319 (p_validate => false
320 ,p_effective_date => p_effective_date
321 ,p_organization_id => l_organization_id
322 ,p_org_info_type_code => 'DE_TAX_OFFICE_INFO'
323 ,p_org_information1 => p_tax_office_no
324 ,p_org_information2 => p_bundesland
325 ,p_org_information_id => l_org_information_id
326 ,p_object_version_number => l_object_version_number);
327 END create_tax_office;
328 --
329 --
330 -- --------------------------------------------------------------------------
331 -- Service function to create a new social insurance provider.
332 --
333 -- This involves the following sequence ...
334 --
338 -- betriebesnumber.
335 -- 1. Create an organization.
336 -- 2. Classify it as tthe correct social insurance provider type.
337 -- 3. Add social insurance provider specifc information e.g. east
339 -- --------------------------------------------------------------------------
340 --
341 PROCEDURE create_soc_ins_provider
342 (p_effective_date DATE
343 ,p_business_group_id NUMBER
344 ,p_date_from DATE
345 ,p_provider_name VARCHAR2
346 ,p_provider_type VARCHAR2
347 ,p_health_insurance_type VARCHAR2
348 ,p_east_betriebesnumber VARCHAR2
349 ,p_west_betriebesnumber VARCHAR2
350 ,p_pension_insurance_type VARCHAR2) IS
351 --
352 --
353 -- Local Variables.
354 --
355 l_organization_id NUMBER;
356 l_org_information_id NUMBER;
357 l_object_version_number NUMBER;
358 BEGIN
359 --
360 --
361 -- Create basic organization definition.
362 --
363 hr_organization_api.create_organization
364 (p_validate => false
365 ,p_effective_date => p_effective_date
366 ,p_language_code => 'US'
367 ,p_business_group_id => p_business_group_id
368 ,p_date_from => p_date_from
369 ,p_name => p_provider_name
370 ,p_organization_id => l_organization_id
371 ,p_object_version_number => l_object_version_number);
372 --
373 --
374 -- Classify the organization as a social insurance
375 -- provider of type p_provider_type.
376 --
377 hr_organization_api.create_org_classification
378 (p_validate => false
379 ,p_effective_date => p_effective_date
380 ,p_organization_id => l_organization_id
381 ,p_org_classif_code => p_provider_type
382 ,p_org_information_id => l_org_information_id
383 ,p_object_version_number => l_object_version_number);
384 --
385 --
386 -- Add extra organization information.
387 --
388 hr_organization_api.create_org_information
389 (p_validate => false
390 ,p_effective_date => p_effective_date
391 ,p_organization_id => l_organization_id
392 ,p_org_info_type_code => 'DE_SOCIAL_INSURANCE_INFO'
393 ,p_org_information1 => LOCAL_DECODE(p_provider_type = 'DE_MAN_HEALTH_PROV'
394 ,p_west_betriebesnumber, NULL)
395 ,p_org_information2 => LOCAL_DECODE(p_provider_type = 'DE_MAN_HEALTH_PROV'
396 ,p_east_betriebesnumber, NULL)
397 ,p_org_information3 => LOCAL_DECODE(p_provider_type = 'DE_MAN_HEALTH_PROV'
398 ,p_health_insurance_type, NULL)
399 ,p_org_information4 => LOCAL_DECODE(p_provider_type = 'DE_MAN_PENSION_PROV'
400 ,p_pension_insurance_type, NULL)
401 ,p_org_information_id => l_org_information_id
402 ,p_object_version_number => l_object_version_number);
403 END create_soc_ins_provider;
404 --
405 --
406 -- --------------------------------------------------------------------------
407 -- Service function to process a tax office.
408 -- --------------------------------------------------------------------------
409 --
410 PROCEDURE process_tax_office
411 (p_upload_mode VARCHAR2
412 ,p_effective_date DATE
413 ,p_bg_rec c_bg_info%ROWTYPE
414 ,p_tax_office_rec c_tax_office_defs%ROWTYPE) IS
415 --
416 --
417 -- Local Variables.
418 --
419 l_org_id NUMBER;
420 l_org_name VARCHAR2(60);
421 l_org_class_list VARCHAR2(2000);
422 l_tax_office_list t_tax_office_list;
423 BEGIN
424 --
425 --
426 -- Write out header line for the tax office e.g.
427 --
428 -- Bundesland : <>
429 -- Tax Office Name : <>
430 -- Tax Office Code : <>
431 --
432 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'BUNDESLAND'), 30, ' ')
433 || ': ' || p_tax_office_rec.bundesland_description);
434 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'TAX_OFFICE_NAME'), 30, ' ')
435 || ': ' || p_tax_office_rec.tax_office_name);
436 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'TAX_OFFICE_CODE'), 30, ' ')
437 || ': ' || NVL(p_tax_office_rec.tax_office_no, ' '));
438 fnd_file.put_line(fnd_file.output, ' ');
439 --
440 --
441 -- See if the tax office already exists (by searching by tax office no).
442 --
443 tax_office_info
444 (p_business_group_id => p_bg_rec.business_group_id
445 ,p_tax_office_no => p_tax_office_rec.tax_office_no
446 ,o_tax_office_list => l_tax_office_list);
447 --
448 --
449 -- At least one tax office exists based on tax office code.
450 --
451 IF l_tax_office_list.COUNT > 0 THEN
452 --
453 --
454 -- Loop for all matching tax offices.
455 --
456 FOR l_loop_index IN l_tax_office_list.FIRST..l_tax_office_list.LAST LOOP
457 --
458 --
459 -- Names matches.
460 --
461 IF l_tax_office_list(l_loop_index).name = p_tax_office_rec.tax_office_name THEN
462 --
463 --
464 -- Write out status line e.g. Tax office already exists.
465 --
466 fnd_message.set_name('PER', 'HR_DE_TAX_OFFICE_EXISTS');
467 fnd_file.put_line(fnd_file.output, fnd_message.get);
468 fnd_file.put_line(fnd_file.output, '');
469 --
470 --
474 --
471 -- Name does not match.
472 --
473 ELSE
475 --
476 -- Write out status line e.g.
477 --
478 -- The tax office <> already exists with this tax office code.
479 --
480 fnd_message.set_name('PER', 'HR_DE_TAX_OFFICE_CODE_MATCHES');
481 fnd_message.set_token('NAME', l_tax_office_list(l_loop_index).name);
482 fnd_file.put_line(fnd_file.output, fnd_message.get);
483 fnd_file.put_line(fnd_file.output, '');
484 END IF;
485 END LOOP;
486 END IF;
487 --
488 --
489 -- No matching organization has been found yet.
490 --
491 IF l_tax_office_list.COUNT = 0 THEN
492 --
493 --
494 -- See if an organization with the same name already exists.
495 --
496 org_info
497 (p_business_group_id => p_bg_rec.business_group_id
498 ,p_organization_name => p_tax_office_rec.tax_office_name
499 ,o_organization_id => l_org_id
500 ,o_classification_list => l_org_class_list);
501 --
502 --
503 -- Organization exists based on the tax office name.
504 --
505 IF l_org_id IS NOT NULL THEN
506 --
507 --
508 -- Organization is a tax office.
509 --
510 IF INSTR(l_org_class_list, 'DE_TAX_OFFICE') > 0 THEN
511 --
512 --
513 -- Write out status line e.g.
514 --
515 -- A tax office with this name already exists but the tax office
516 -- code does not match.
517 --
518 fnd_message.set_name('PER', 'HR_DE_TAX_OFFICE_NAME_MATCHES');
519 fnd_file.put_line(fnd_file.output, fnd_message.get);
520 fnd_file.put_line(fnd_file.output, '');
521 --
522 --
523 -- Organization is not a tax office.
524 --
525 ELSE
526 --
527 --
528 -- Write out status line e.g.
529 --
530 -- An organization with this name already exists but it is not a tax office
531 --
532 fnd_message.set_name('PER', 'HR_DE_NOT_A_TAX_OFFICE');
533 fnd_file.put_line(fnd_file.output, fnd_message.get);
534 fnd_file.put_line(fnd_file.output, '');
535 END IF;
536 END IF;
537 END IF;
538 --
539 --
540 -- No matching organization has been found yet.
541 --
542 IF l_tax_office_list.COUNT = 0 AND l_org_id IS NULL THEN
543 --
544 --
545 -- Running in merge mode so create new tax office.
546 --
547 IF p_upload_mode = 'MERGE' THEN
548 --
549 --
550 -- Tax office does not exist so create it.
551 --
552 create_tax_office
553 (p_effective_date => p_effective_date
554 ,p_business_group_id => p_bg_rec.business_group_id
555 ,p_date_from => p_bg_rec.date_from
556 ,p_bundesland => p_tax_office_rec.bundesland
557 ,p_tax_office_no => p_tax_office_rec.tax_office_no
558 ,p_tax_office_name => p_tax_office_rec.tax_office_name);
559 --
560 --
561 -- Write out status line e.g. Tax office has been created.
562 --
563 fnd_message.set_name('PER', 'HR_DE_TAX_OFFICE_CREATED');
564 fnd_file.put_line(fnd_file.output, fnd_message.get);
565 fnd_file.put_line(fnd_file.output, '');
566 --
567 --
568 -- Running in analyse mode so only report the fact that the
569 -- tax office will be created.
570 --
571 ELSE
572 --
573 --
574 -- Write out status line e.g. Tax office can been created.
575 --
576 fnd_message.set_name('PER', 'HR_DE_TAX_OFFICE_POSSIBLE');
577 fnd_file.put_line(fnd_file.output, fnd_message.get);
578 fnd_file.put_line(fnd_file.output, '');
579 END IF;
580 END IF;
581 END process_tax_office;
582 --
583 --
584 -- --------------------------------------------------------------------------
585 -- Service function to process a social insurance provider.
586 -- --------------------------------------------------------------------------
587 --
588 PROCEDURE process_soc_ins_provider
589 (p_upload_mode VARCHAR2
590 ,p_effective_date DATE
591 ,p_bg_rec c_bg_info%ROWTYPE
592 ,p_soc_ins_prov_rec c_soc_ins_prov_defs%ROWTYPE) IS
593 --
594 --
595 -- Local Variables.
596 --
597 l_org_id NUMBER;
598 l_org_name VARCHAR2(60);
599 l_org_class_list VARCHAR2(2000);
600 BEGIN
601 --
602 --
603 -- Write out header line for the social insurance provider e.g.
604 --
605 -- Provider Type : <>
606 -- Provider Name : <>
607 --
608 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_TYPE'), 30, ' ')
609 || ': ' || p_soc_ins_prov_rec.provider_type_description);
610 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_NAME'), 30, ' ')
611 || ': ' || p_soc_ins_prov_rec.provider_name);
612 fnd_file.put_line(fnd_file.output, ' ');
613 --
614 --
615 -- See if an organization with the same name already exists.
616 --
617 org_info
618 (p_business_group_id => p_bg_rec.business_group_id
619 ,p_organization_name => p_soc_ins_prov_rec.provider_name
620 ,o_organization_id => l_org_id
621 ,o_classification_list => l_org_class_list);
622 --
623 --
627 --
624 -- Organization exists based on the social insurance provider name.
625 --
626 IF l_org_id IS NOT NULL THEN
628 --
629 -- Organization is a social insurance provider of the correct type.
630 --
631 IF INSTR(l_org_class_list, p_soc_ins_prov_rec.provider_type) > 0 THEN
632 --
633 --
634 -- Write out status line e.g. The social insurance provider already exists.
635 --
636 fnd_message.set_name('PER', 'HR_DE_SOCINS_PROV_EXISTS');
637 fnd_file.put_line(fnd_file.output, fnd_message.get);
638 fnd_file.put_line(fnd_file.output, '');
639 --
640 --
641 -- Organization is not a social insurance provider of this type.
642 --
643 ELSE
644 --
645 --
646 -- Write out status line e.g. An organization with this name already exists but it is not a <>.
647 --
648 fnd_message.set_name('PER', 'HR_DE_NOT_A_SOCINS_PROV');
649 fnd_message.set_token('TYPE', p_soc_ins_prov_rec.provider_type_description);
650 fnd_file.put_line(fnd_file.output, fnd_message.get);
651 fnd_file.put_line(fnd_file.output, '');
652 END IF;
653 END IF;
654 --
655 --
656 -- No matching organization has been found yet.
657 --
658 IF l_org_id IS NULL THEN
659 --
660 --
661 -- Running in merge mode so create new social insurance provider.
662 --
663 IF p_upload_mode = 'MERGE' THEN
664 --
665 --
666 -- Social insurance provider does not exist so create it.
667 --
668 create_soc_ins_provider
669 (p_effective_date => p_effective_date
670 ,p_business_group_id => p_bg_rec.business_group_id
671 ,p_date_from => p_bg_rec.date_from
672 ,p_provider_name => p_soc_ins_prov_rec.provider_name
673 ,p_provider_type => p_soc_ins_prov_rec.provider_type
674 ,p_health_insurance_type => p_soc_ins_prov_rec.health_insurance_type
675 ,p_east_betriebesnumber => p_soc_ins_prov_rec.east_betriebesnumber
676 ,p_west_betriebesnumber => p_soc_ins_prov_rec.west_betriebesnumber
677 ,p_pension_insurance_type => p_soc_ins_prov_rec.pension_insurance_type);
678 --
679 --
680 -- Write out status line e.g. Social insurance provider has been created.
681 --
682 fnd_message.set_name('PER', 'HR_DE_SOCINS_PROV_CREATED');
683 fnd_file.put_line(fnd_file.output, fnd_message.get);
684 fnd_file.put_line(fnd_file.output, '');
685 --
686 --
687 -- Running in analyse mode so only report the fact that the
688 -- social insurance provider will be created.
689 --
690 ELSE
691 --
692 --
693 -- Write out status line e.g. Social insurance provider can been created.
694 --
695 fnd_message.set_name('PER', 'HR_DE_SOCINS_PROV_POSSIBLE');
696 fnd_file.put_line(fnd_file.output, fnd_message.get);
697 fnd_file.put_line(fnd_file.output, '');
698 END IF;
699 END IF;
700 END process_soc_ins_provider;
701 --
702 --
703 -- --------------------------------------------------------------------------
704 -- Service function to process a mandatoru health provider.
705 -- --------------------------------------------------------------------------
706 --
707 PROCEDURE process_mand_health_provider
708 (p_upload_mode VARCHAR2
709 ,p_effective_date DATE
710 ,p_bg_rec c_bg_info%ROWTYPE
711 ,p_soc_ins_prov_rec c_soc_ins_prov_defs%ROWTYPE) IS
712 --
713 --
714 -- Local Variables.
715 --
716 l_org_id NUMBER;
717 l_org_name VARCHAR2(60);
718 l_org_class_list VARCHAR2(2000);
719 l_mand_health_prov_list t_mand_health_prov_list;
720 BEGIN
721 --
722 --
723 -- Write out header line for the mandatory health provider e.g.
724 --
725 -- Provider Type : <>
726 -- Provider Name : <>
727 -- East Betriebsnummer : <>
728 -- West Betriebsnummer : <>
729 --
730 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_TYPE'), 30, ' ')
731 || ': ' || p_soc_ins_prov_rec.provider_type_description);
732 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_NAME'), 30, ' ')
733 || ': ' || p_soc_ins_prov_rec.provider_name);
734 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'EAST_BETRIEBS'), 30, ' ')
735 || ': ' || NVL(p_soc_ins_prov_rec.east_betriebesnumber, ' '));
736 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'WEST_BETRIEBS'), 30, ' ')
737 || ': ' || NVL(p_soc_ins_prov_rec.west_betriebesnumber, ' '));
738 fnd_file.put_line(fnd_file.output, ' ');
739 --
740 --
741 -- See if the mandatory health provider already exists (by searching by east
742 -- and west betriebsnummer).
743 --
744 mand_health_info
745 (p_business_group_id => p_bg_rec.business_group_id
746 ,p_east_betriebsnummer => p_soc_ins_prov_rec.east_betriebesnumber
747 ,p_west_betriebsnummer => p_soc_ins_prov_rec.west_betriebesnumber
748 ,o_mand_health_prov_list => l_mand_health_prov_list);
749 --
750 --
751 -- At least one manadatory health provider exists (searching by betriebsnummers).
752 --
753 IF l_mand_health_prov_list.COUNT > 0 THEN
754 --
755 --
756 -- Loop for all mandatory health providers.
757 --
761 -- Names matches.
758 FOR l_loop_index IN l_mand_health_prov_list.FIRST..l_mand_health_prov_list.LAST LOOP
759 --
760 --
762 --
763 IF l_mand_health_prov_list(l_loop_index).name = p_soc_ins_prov_rec.provider_name THEN
764 --
765 --
766 -- Write out status line e.g. Social insurance provider already exists.
767 --
768 fnd_message.set_name('PER', 'HR_DE_SOCINS_PROV_EXISTS');
769 fnd_file.put_line(fnd_file.output, fnd_message.get);
770 fnd_file.put_line(fnd_file.output, '');
771 --
772 --
773 -- Name does not match.
774 --
775 ELSE
776 --
777 --
778 -- Write out status line e.g.
779 --
780 -- The social insurance provider <> already exists with these betriebsnummers.
781 --
782 fnd_message.set_name('PER', 'HR_DE_SOCINS_BETRIEBS_MATCHES');
783 fnd_message.set_token('NAME', l_mand_health_prov_list(l_loop_index).name);
784 fnd_file.put_line(fnd_file.output, fnd_message.get);
785 fnd_file.put_line(fnd_file.output, '');
786 END IF;
787 END LOOP;
788 END IF;
789 --
790 --
791 -- No matching organization has been found yet.
792 --
793 IF l_mand_health_prov_list.COUNT = 0 THEN
794 --
795 --
796 -- See if an organization with the same name already exists.
797 --
798 org_info
799 (p_business_group_id => p_bg_rec.business_group_id
800 ,p_organization_name => p_soc_ins_prov_rec.provider_name
801 ,o_organization_id => l_org_id
802 ,o_classification_list => l_org_class_list);
803 --
804 --
805 -- Organization exists based on the mandatory health provider name.
806 --
807 IF l_org_id IS NOT NULL THEN
808 --
809 --
810 -- Organization is a mandatory health provider.
811 --
812 IF INSTR(l_org_class_list, 'DE_MAN_HEALTH_PROV') > 0 THEN
813 --
814 --
815 -- Write out status line e.g.
816 --
817 -- A social insurance provider with this name already exists but the east
818 -- and west betriebsnummers do not match.
819 --
820 fnd_message.set_name('PER', 'HR_DE_SOCINS_NAME_MATCHES');
821 fnd_file.put_line(fnd_file.output, fnd_message.get);
822 fnd_file.put_line(fnd_file.output, '');
823 --
824 --
825 -- Organization is not a mandatory health provider.
826 --
827 ELSE
828 --
829 --
830 -- Write out status line e.g. An organization with this name already exists but it is not a <>.
831 --
832 fnd_message.set_name('PER', 'HR_DE_NOT_A_SOCINS_PROV');
833 fnd_message.set_token('TYPE', p_soc_ins_prov_rec.provider_type_description);
834 fnd_file.put_line(fnd_file.output, fnd_message.get);
835 fnd_file.put_line(fnd_file.output, '');
836 END IF;
837 END IF;
838 END IF;
839 --
840 --
841 -- No matching organization has been found yet.
842 --
843 IF l_mand_health_prov_list.COUNT = 0 AND l_org_id IS NULL THEN
844 --
845 --
846 -- Running in merge mode so create new mandatory health provider.
847 --
848 IF p_upload_mode = 'MERGE' THEN
849 --
850 --
851 -- Mandatory health provider does not exist so create it.
852 --
853 create_soc_ins_provider
854 (p_effective_date => p_effective_date
855 ,p_business_group_id => p_bg_rec.business_group_id
856 ,p_date_from => p_bg_rec.date_from
857 ,p_provider_name => p_soc_ins_prov_rec.provider_name
858 ,p_provider_type => p_soc_ins_prov_rec.provider_type
859 ,p_health_insurance_type => p_soc_ins_prov_rec.health_insurance_type
860 ,p_east_betriebesnumber => p_soc_ins_prov_rec.east_betriebesnumber
861 ,p_west_betriebesnumber => p_soc_ins_prov_rec.west_betriebesnumber
862 ,p_pension_insurance_type => p_soc_ins_prov_rec.pension_insurance_type);
863 --
864 --
865 -- Write out status line e.g. Social insurance provider has been created.
866 --
867 fnd_message.set_name('PER', 'HR_DE_SOCINS_PROV_CREATED');
868 fnd_file.put_line(fnd_file.output, fnd_message.get);
869 fnd_file.put_line(fnd_file.output, '');
870 --
871 --
872 -- Running in analyse mode so only report the fact that the
873 -- social insurance provider will be created.
874 --
875 ELSE
876 --
877 --
878 -- Write out status line e.g. Social insurance provider can been created.
879 --
880 fnd_message.set_name('PER', 'HR_DE_SOCINS_PROV_POSSIBLE');
881 fnd_file.put_line(fnd_file.output, fnd_message.get);
882 fnd_file.put_line(fnd_file.output, '');
883 END IF;
884 END IF;
885 END process_mand_health_provider;
886 --
887 --
888 -- --------------------------------------------------------------------------
889 -- This uploads the definitions for tax offices as organizations in the HRMS
890 -- system.
891 --
892 -- The definitions for the tax offices are held in a user table named
893 -- HR_DE_TAX_OFFICE_DEFINITION which can be seen by using a view named
894 -- HR_DE_TAX_DEFINITION_V.
895 --
896 -- The parameters are defined as follows...
897 --
901 -- details).
898 -- p_business_group_id: the business group for which this upload is being run.
899 -- p_effective_date : the date on which the changes are made.
900 -- p_upload_mode : the mode is either 'Merge' or 'Analyse' (see below for
902 -- p_bundesland : can be used to identify a subset of the tax offices
903 -- NB. leaving this blank means load all tax offices.
904 --
905 -- The mode of 'Merge' only adds tax offices that do not already exist, while
906 -- 'Analyse' produces a summary of what would happen if 'Merge' was used.
907 -- --------------------------------------------------------------------------
908 --
909 PROCEDURE upload_tax_offices
910 (errbuf OUT NOCOPY VARCHAR2
911 ,retcode OUT NOCOPY NUMBER
912 ,p_business_group_id NUMBER
913 ,p_effective_date VARCHAR2
914 ,p_upload_mode VARCHAR2
915 ,p_bundesland VARCHAR2) IS
916 --
917 --
918 -- Local variables.
919 --
920 l_bg_rec c_bg_info%ROWTYPE;
921 l_tax_office_rec c_tax_office_defs%ROWTYPE;
922 l_effective_date DATE := fnd_date.canonical_to_date(p_effective_date);
923 BEGIN
924 --
925 --
926 -- Setup up session date.
927 --
928 INSERT INTO fnd_sessions
929 (session_id
930 ,effective_date)
931 VALUES
932 (userenv('sessionid')
933 ,l_effective_date);
934 --
935 --
936 -- Get information for the business group.
937 --
938 OPEN c_bg_info(p_business_group_id => p_business_group_id);
939 FETCH c_bg_info INTO l_bg_rec;
940 CLOSE c_bg_info;
941 --
942 --
943 -- Write heading for output file.
944 --
945 fnd_file.put_line(fnd_file.output, ' ');
946 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'TAX_OFFICE_UPLOAD'), 41, ' '));
947 fnd_file.put_line(fnd_file.output, ' ');
948 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'UPLOAD_MODE'), 29, ' ') || ' : '
949 || hr_general.decode_lookup('DE_ORG_UPLOAD_MODE', p_upload_mode));
950 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'BUNDESLAND'), 29, ' ') || ' : '
951 || LOCAL_DECODE(p_bundesland IS NOT NULL
952 ,hr_general.decode_lookup('DE_FED_STATE', p_bundesland)
953 ,hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'ALL')));
954 fnd_file.put_line(fnd_file.output, ' ');
955 fnd_file.put_line(fnd_file.output, ' ');
956 --
957 --
958 -- Fetch the first tax office definition.
959 --
960 OPEN c_tax_office_defs(p_bundesland => p_bundesland);
961 FETCH c_tax_office_defs INTO l_tax_office_rec;
962 --
963 --
964 -- Loop for all tax office definitions.
965 --
966 WHILE c_tax_office_defs%FOUND LOOP
967 --
968 --
969 -- Process the tax office.
970 --
971 process_tax_office
972 (p_upload_mode => p_upload_mode
973 ,p_effective_date => l_effective_date
974 ,p_bg_rec => l_bg_rec
975 ,p_tax_office_rec => l_tax_office_rec);
976 --
977 --
978 -- Fetch the next tax office definition.
979 --
980 FETCH c_tax_office_defs INTO l_tax_office_rec;
981 END LOOP;
982 --
983 --
984 -- All tax office definitions have been found.
985 --
986 CLOSE c_tax_office_defs;
987 --
988 --
989 -- Make the changes permanent.
990 --
991 COMMIT;
992 --
993 --
994 -- Return success.
995 --
996 errbuf := NULL;
997 retcode := 0;
998 EXCEPTION
999 WHEN OTHERS THEN
1000 --
1001 --
1002 -- Write out header line for the tax office e.g.
1003 --
1004 -- Bundesland : <>
1005 -- Tax Office Name : <>
1006 -- Tax Office Code : <>
1007 --
1008 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'BUNDESLAND'), 30, ' ')
1009 || ': ' || l_tax_office_rec.bundesland_description);
1010 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'TAX_OFFICE_NAME'), 30, ' ')
1011 || ': ' || l_tax_office_rec.tax_office_name);
1012 fnd_file.put_line(fnd_file.output, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'TAX_OFFICE_CODE'), 30, ' ')
1013 || ': ' || NVL(l_tax_office_rec.tax_office_no, ' '));
1014 fnd_file.put_line(fnd_file.output, ' ');
1015 --
1016 --
1017 -- Output the error that was raised.
1018 --
1019 fnd_file.put_line(fnd_file.log, SQLERRM);
1020 fnd_file.put_line(fnd_file.log, '');
1021 --
1022 --
1023 -- Close the cursor.
1024 --
1025 CLOSE c_tax_office_defs;
1026 --
1027 --
1028 -- Undo the changes.
1029 --
1030 ROLLBACK;
1031 --
1032 --
1033 -- Return the unexpected error.
1034 --
1035 errbuf := NULL;
1036 retcode := 2;
1037 END upload_tax_offices;
1038 --
1039 --
1040 -- --------------------------------------------------------------------------
1041 -- This uploads the definitions for social insurance providers as organizations
1042 -- in the HRMS system.
1043 --
1044 -- The definitions for the social insurance providers are held in a user table
1048 -- The parameters are defined as follows...
1045 -- named HR_DE_SOC_INS_PROV_DEFINITION which can be seen by using a view named
1046 -- HR_DE_SOCINS_PROV_DEFINITION_V.
1047 --
1049 --
1050 -- p_business_group_id: the business group for which this upload is being run.
1051 -- p_effective_date : the date on which the changes are made.
1052 -- p_upload_mode : the mode is either 'Merge' or 'Analyse' (see below for
1053 -- details).
1054 -- p_provider_type : can be used to identify a subset of the social insurance
1055 -- providers e.g. mandatory health providers, mandatory
1056 -- pension providers, etc.
1057 --
1058 -- The mode of 'Merge' only adds social insurance providers that do not already
1059 -- exist, while 'Analyse' produces a summary of what would happen if 'Merge'
1060 -- was used.
1061 -- --------------------------------------------------------------------------
1062 --
1063 PROCEDURE upload_soc_ins_providers
1064 (errbuf OUT NOCOPY VARCHAR2
1065 ,retcode OUT NOCOPY NUMBER
1066 ,p_business_group_id NUMBER
1067 ,p_effective_date VARCHAR2
1068 ,p_upload_mode VARCHAR2
1069 ,p_provider_type VARCHAR2) IS
1070 --
1071 --
1072 -- Local variables.
1073 --
1074 l_bg_rec c_bg_info%ROWTYPE;
1075 l_soc_ins_prov_rec c_soc_ins_prov_defs%ROWTYPE;
1076 l_effective_date DATE := fnd_date.canonical_to_date(p_effective_date);
1077 BEGIN
1078 --
1079 --
1080 -- Setup up session date.
1081 --
1082 INSERT INTO fnd_sessions
1083 (session_id
1084 ,effective_date)
1085 VALUES
1086 (userenv('sessionid')
1087 ,l_effective_date);
1088 --
1089 --
1090 -- Get information for the business group.
1091 --
1092 OPEN c_bg_info(p_business_group_id => p_business_group_id);
1093 FETCH c_bg_info INTO l_bg_rec;
1094 CLOSE c_bg_info;
1095 --
1096 --
1097 -- Write heading for output file.
1098 --
1099 fnd_file.put_line(fnd_file.output, ' ');
1100 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'SOCINS_PROV_UPLOAD'), 47, ' '));
1101 fnd_file.put_line(fnd_file.output, ' ');
1102 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'UPLOAD_MODE'), 29, ' ') || ' : '
1103 || hr_general.decode_lookup('DE_ORG_UPLOAD_MODE', p_upload_mode));
1104 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_TYPE'), 29, ' ') || ' : '
1105 || LOCAL_DECODE(p_provider_type IS NOT NULL
1106 ,hr_general.decode_lookup('DE_ORG_UPLOAD_PROV_TYPES', p_provider_type)
1107 ,hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'ALL')));
1108 fnd_file.put_line(fnd_file.output, ' ');
1109 fnd_file.put_line(fnd_file.output, ' ');
1110 --
1111 --
1112 -- Fetch the first social insurance provider definition.
1113 --
1114 OPEN c_soc_ins_prov_defs(p_provider_type => p_provider_type);
1115 FETCH c_soc_ins_prov_defs INTO l_soc_ins_prov_rec;
1116 --
1117 --
1118 -- Loop for all social insrance provider definitions.
1119 --
1120 WHILE c_soc_ins_prov_defs%FOUND LOOP
1121 --
1122 --
1123 -- Process the social insurance provider NB. the processing is split
1124 -- into two with one focussed on mandatory health providers as their
1125 -- processing is more complex while all others can be processed in
1126 -- the same way.
1127 --
1128 IF l_soc_ins_prov_rec.provider_type = 'DE_MAN_HEALTH_PROV' THEN
1129 process_mand_health_provider
1130 (p_upload_mode => p_upload_mode
1131 ,p_effective_date => l_effective_date
1132 ,p_bg_rec => l_bg_rec
1133 ,p_soc_ins_prov_rec => l_soc_ins_prov_rec);
1134 ELSE
1135 process_soc_ins_provider
1136 (p_upload_mode => p_upload_mode
1137 ,p_effective_date => l_effective_date
1138 ,p_bg_rec => l_bg_rec
1139 ,p_soc_ins_prov_rec => l_soc_ins_prov_rec);
1140 END IF;
1141 --
1142 --
1143 -- Fetch the next social insurance provider definition.
1144 --
1145 FETCH c_soc_ins_prov_defs INTO l_soc_ins_prov_rec;
1146 END LOOP;
1147 --
1148 --
1149 -- All social insrance provider definitions have been found.
1150 --
1151 CLOSE c_soc_ins_prov_defs;
1152 --
1153 --
1154 -- Make the changes permanent.
1155 --
1156 COMMIT;
1157 --
1158 --
1159 -- Return success.
1160 --
1161 errbuf := NULL;
1162 retcode := 0;
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 --
1166 --
1167 -- Output details of the current record that is being processed to aid debugging.
1168 --
1169 IF l_soc_ins_prov_rec.provider_type = 'DE_MAN_HEALTH_PROV' THEN
1170 --
1171 --
1172 -- Write out header line for the mandatory health provider e.g.
1173 --
1174 -- Provider Type : <>
1175 -- Provider Name : <>
1176 -- East Betriebsnummer : <>
1177 -- West Betriebsnummer : <>
1178 --
1179 fnd_file.put_line(fnd_file.log, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_TYPE'), 30, ' ')
1180 || ': ' || l_soc_ins_prov_rec.provider_type_description);
1181 fnd_file.put_line(fnd_file.log, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_NAME'), 30, ' ')
1182 || ': ' || l_soc_ins_prov_rec.provider_name);
1183 fnd_file.put_line(fnd_file.log, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'EAST_BETRIEBS'), 30, ' ')
1184 || ': ' || NVL(l_soc_ins_prov_rec.east_betriebesnumber, ' '));
1185 fnd_file.put_line(fnd_file.log, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'WEST_BETRIEBS'), 30, ' ')
1186 || ': ' || NVL(l_soc_ins_prov_rec.west_betriebesnumber, ' '));
1187 fnd_file.put_line(fnd_file.log, ' ');
1188 ELSE
1189 --
1190 --
1191 -- Write out header line for the social insurance provider e.g.
1192 --
1193 -- Provider Type : <>
1194 -- Provider Name : <>
1195 --
1196 fnd_file.put_line(fnd_file.log, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_TYPE'), 30, ' ')
1197 || ': ' || l_soc_ins_prov_rec.provider_type_description);
1198 fnd_file.put_line(fnd_file.log, RPAD(hr_general.decode_lookup('DE_ORG_UPLOAD_LABELS', 'PROV_NAME'), 30, ' ')
1199 || ': ' || l_soc_ins_prov_rec.provider_name);
1200 fnd_file.put_line(fnd_file.output, ' ');
1201 END IF;
1202 --
1203 --
1204 -- Output the error that was raised.
1205 --
1206 fnd_file.put_line(fnd_file.log, SQLERRM);
1207 fnd_file.put_line(fnd_file.log, '');
1208 --
1209 --
1210 -- Close the cursor.
1211 --
1212 CLOSE c_soc_ins_prov_defs;
1213 --
1214 --
1215 -- Undo the changes.
1216 --
1217 ROLLBACK;
1218 --
1219 --
1220 -- Return the unexpected error.
1221 --
1222 errbuf := NULL;
1223 retcode := 2;
1224 END upload_soc_ins_providers;
1225 END hr_de_organization_upload;