1 PACKAGE BODY HR_DE_ORG_INFO AS
2 /* $Header: pedeorgi.pkb 115.20 2003/01/24 12:11:00 vgunasek noship $ */
3
4 g_chamber_contribution varchar2(1) := null;
5 g_employer_Betriebsnummer varchar2(8) := null;
6 g_payroll_Betriebsnummer varchar2(8) := null;
7 g_package varchar2(33) := ' HR_DE_ORG_INFO.';
8 g_liab_prov varchar2(277);
9 g_assg_id number(15);
10 g_loc varchar2(861);
11 g_super_off varchar2(256);
12 --
13 --
14 -- Cursor which fetches Organizations from the named hierarchy - bottom to top
15 --
16 CURSOR organization_hierarchy(p_org_id NUMBER, p_org_structure_version_id NUMBER, p_org_exists_in_hierarchy VARCHAR2) IS
17 SELECT p_org_id organization_id_parent
18 ,0 lev
19 FROM dual
20 WHERE p_org_exists_in_hierarchy = 'Y'
21 UNION
22 SELECT organization_id_parent
23 ,level lev
24 FROM per_org_structure_elements
25 WHERE org_structure_version_id = p_org_structure_version_id
26 START WITH organization_id_child = p_org_id
27 CONNECT BY PRIOR organization_id_parent = organization_id_child
28 AND org_structure_version_id = p_org_structure_version_id
29 ORDER BY lev;
30 --
31 CURSOR org_hierarchy(p_org_id NUMBER, p_org_structure_version_id NUMBER, p_org_exists_in_hierarchy VARCHAR2) IS
32 SELECT p_org_id organization_id_parent
33 ,0 lev
34 FROM dual
35 WHERE p_org_exists_in_hierarchy = 'Y'
36 UNION
37 SELECT organization_id_parent
38 ,level lev
39 FROM per_org_structure_elements
40 WHERE org_structure_version_id = p_org_structure_version_id
41 START WITH organization_id_child = p_org_id
42 CONNECT BY PRIOR organization_id_parent = organization_id_child
43 AND org_structure_version_id = p_org_structure_version_id
44 ORDER BY lev;
45 --
46 --
47 -- Service function to return the current named hioerarchy.
48 --
49 FUNCTION named_hierarchy
50 (p_organization_id NUMBER) RETURN NUMBER IS
51 --
52 --
53 -- Cursor to return the current named hierarchy.
54 --
55 CURSOR c_hierarchy(p_organization_id NUMBER) IS
56 SELECT TO_NUMBER(inf.org_information1) organization_structure_id
57 FROM hr_organization_information inf
58 ,hr_all_organization_units org
59 WHERE org.organization_id = p_organization_id
60 AND inf.organization_id = org.business_group_id
61 AND inf.org_information_context = 'DE_BG_INFO'
62 AND inf.org_information1 IS NOT NULL;
63 --
64 --
65 -- Local Variables.
66 --
67 l_rec c_hierarchy%ROWTYPE;
68 BEGIN
69 --
70 --
71 -- Find the current named organization hierarchy.
72 --
73 OPEN c_hierarchy(p_organization_id => p_organization_id);
74 FETCH c_hierarchy INTO l_rec;
75 CLOSE c_hierarchy;
76 --
77 --
78 -- Return ID.
79 --
80 RETURN l_rec.organization_structure_id;
81 END named_hierarchy;
82 --
83 --
84 -- Service function to return the current version of the named hioerarchy.
85 --
86 FUNCTION latest_named_hierarchy_vers
87 (p_organization_id NUMBER) RETURN NUMBER IS
88 --
89 --
90 -- Cursor to return the current named hierarchy version.
91 --
92 CURSOR c_hierarchy_version(p_organization_id NUMBER, p_organization_structure_id NUMBER) IS
93 SELECT sv.org_structure_version_id, sv.version_number
94 FROM per_org_structure_versions sv
95 ,fnd_sessions ses
96 WHERE sv.organization_structure_id = p_organization_structure_id
97 AND ses.session_id = USERENV('sessionid')
98 AND ses.effective_date BETWEEN sv.date_from AND NVL(sv.date_to, TO_DATE('31/12/4712','DD/MM/YYYY'))
99 ORDER BY sv.version_number DESC;
100 --
101 --
102 -- Local Variables.
103 --
104 l_rec c_hierarchy_version%ROWTYPE;
105 BEGIN
106 --
107 --
108 -- Find the current primary organization hierarchy.
109 --
110 OPEN c_hierarchy_version(p_organization_id => p_organization_id
111 ,p_organization_structure_id => named_hierarchy(p_organization_id));
112 FETCH c_hierarchy_version INTO l_rec;
113 CLOSE c_hierarchy_version;
114 --
115 --
116 -- Return ID.
117 --
118 RETURN l_rec.org_structure_version_id;
119 END latest_named_hierarchy_vers;
120 --
121 --
122 -- Service function to see if organization belongs to the current named hioerarchy.
123 --
124 FUNCTION org_exists_in_hierarchy
125 (p_organization_id NUMBER) RETURN VARCHAR2 IS
126 --
127 --
128 -- Cursor to see if the organization belongs to the current named hierarchy.
129 --
130 CURSOR c_org_exists(p_organization_id NUMBER, p_org_structure_version_id NUMBER) IS
131 SELECT se.organization_id_child
132 FROM per_org_structure_elements se
133 WHERE se.org_structure_version_id = p_org_structure_version_id
134 AND (se.organization_id_parent = p_organization_id OR
135 se.organization_id_child = p_organization_id);
136 --
137 --
138 -- Local Variables.
139 --
140 l_rec c_org_exists%ROWTYPE;
141 BEGIN
142 OPEN c_org_exists(p_organization_id => p_organization_id
143 ,p_org_structure_version_id => latest_named_hierarchy_vers(p_organization_id));
144 FETCH c_org_exists INTO l_rec;
145 IF c_org_exists%FOUND THEN
146 CLOSE c_org_exists;
147 RETURN 'Y';
148 ELSE
149 CLOSE c_org_exists;
150 RETURN 'N';
151 END IF;
152 END org_exists_in_hierarchy;
153
154 /* -- ************************************************************************************* --
155 The procedure will return the value of the data item (legal information for a particular employee |
156 see document DE_FUN_INTERNAL_ORGANIZATIONS.doc) required. The org_id taken as input is the |
157 org_id for which the value is required. The procedure will navigate from the org_id supplied up |
158 the hierarchy until it finds a value for the data item. |
159 |
160 The following data items are required ; |
161 Data Item Column Table Context |
162 1. Chamber_contribution org_information1 hr_organization_information DE_CHAMBER_CONTRIBUTION |
163 2. employer_Betriebsnummer org_information1 hr_organization_information DE_HR_ORG_INFO |
164 3. payroll_Betriebsnummer org_information2 hr_organization_information DE_HR_ORG_INFO |
165 -------------------------------------------------------------------------------------------------*/
166
167 PROCEDURE get_org_data_items
168 (p_chamber_contribution_out OUT NOCOPY VARCHAR2
169 ,p_employer_Betriebsnummer OUT NOCOPY VARCHAR2
170 ,p_payroll_Betriebsnummer OUT NOCOPY VARCHAR2
171 ,p_org_id IN NUMBER) IS
172 --
173 --
174 -- Cursor to return organization information relating to chamber contributions and
175 -- general HR information.
176 --
177 CURSOR org_data_items(p_org_id IN NUMBER) IS
178 SELECT SUBSTR(org_information1, 1, 30) cc
179 ,SUBSTR(org_information1, 1, 30) eb
180 ,SUBSTR(org_information2, 1, 30) pb
181 ,org_information_context ctx
182 FROM hr_organization_units d
183 ,hr_organization_information e
184 WHERE d.organization_id = e.organization_id
185 AND d.organization_id = p_org_id
186 AND e.org_information_context IN ('DE_CHAMBER_CONTRIBUTION','DE_HR_ORG_INFO');
187 --
188 --
189 -- Local variables
190 --
191 l_org_rec org_data_items%ROWTYPE;
192 l_all_items_found BOOLEAN := FALSE;
193 l_level NUMBER;
194 l_organization_id NUMBER;
195 l_cc_save VARCHAR2(30);
196 l_eb_save VARCHAR2(30);
197 l_pb_save VARCHAR2(30);
198 BEGIN
199 --
200 --
201 -- Start walking up the organization hierarchy.
202 --
203 OPEN org_hierarchy(p_org_id, latest_named_hierarchy_vers(p_org_id), org_exists_in_hierarchy(p_org_id));
204 FETCH org_hierarchy INTO l_organization_id,l_level;
205 --
206 --
207 -- Walk up the organization hierarchy until all the organizations have been processed or
208 -- all the required information has been found.
209 --
210 WHILE l_all_items_found = FALSE AND org_hierarchy%found LOOP
211 --
212 --
213 -- Get the organization information for the current organization.
214 --
215 OPEN org_data_items(l_organization_id);
216 FETCH org_data_items into l_org_rec;
217 --
218 --
219 -- Loop through all the organization information for the current organization until all the information
220 -- has been processed or all the required information has been found.
221 --
222 WHILE l_all_items_found = FALSE AND org_data_items%found LOOP
223 --
224 --
225 -- See if the organization has the required information and if it does then save it NB. the first
226 -- piece of information in each category is the one that is returned.
227 --
228 --
229 IF l_cc_save IS NULL AND l_org_rec.ctx = 'DE_CHAMBER_CONTRIBUTION' AND l_org_rec.cc IS NOT NULL THEN
230 l_cc_save := RPAD(l_org_rec.cc, 30, ' ');
231 END IF;
232 --
233 IF l_eb_save IS NULL AND l_org_rec.ctx = 'DE_HR_ORG_INFO' AND l_org_rec.eb IS NOT NULL THEN
234 l_eb_save := RPAD(l_org_rec.eb, 30, ' ');
235 END IF;
236 --
237 IF l_pb_save IS NULL AND l_org_rec.ctx = 'DE_HR_ORG_INFO' AND l_org_rec.pb IS NOT NULL THEN
238 l_pb_save := RPAD(l_org_rec.pb, 30, ' ');
239 END IF;
240 --
241 --
242 -- Check to see if all the required information has been found.
243 --
244 l_all_items_found := (l_cc_save IS NOT NULL AND l_eb_save IS NOT NULL AND l_pb_save IS NOT NULL);
245 --
246 --
247 -- Get the next set of organization information for the current organization.
248 --
249 FETCH org_data_items into l_org_rec;
250 END LOOP;
251 --
252 CLOSE org_data_items;
253 --
254 --
255 -- Get the next organization in the hierarchy.
256 --
257 FETCH org_hierarchy INTO l_organization_id, l_level;
258 END lOOP;
259 --
260 CLOSE org_hierarchy;
261 --
262 --
263 -- Set the OUT parameters.
264 --
265 p_chamber_contribution_out := l_cc_save;
266 p_employer_Betriebsnummer := l_eb_save;
267 p_payroll_Betriebsnummer := l_pb_save;
268 END get_org_data_items;
269
270 /*---------------------------------------------------------------------------------------------
271 The table HR_DE_ORGANIZATION_LINKS holds relationships between an internal org and |
272 external orgs (insurance providers in this case). One or more insurance companies may provide |
273 insurance for the orgainization. Get the details and put them in a table that can be returned |
274 to the caller |
275 ---------------------------------------------------------------------------------------------*/
276 -- nocopy not used as the table should not be very large.
277 -- Since nocopy change is made mandatory previous comment is ignored.
278 PROCEDURE get_insurance_providers (p_org_id in hr_organization_units.organization_id%TYPE
279 ,p_Insurance_providers_Table out nocopy Insurance_providers_table) IS
280
281 l_organization_id hr_organization_units.organization_id%type;
282 l_record_count integer := 0;
283 --l_record_count2 integer := 0;
284 l_duplicate varchar2(1) := 'N';
285 l_default varchar2(1) := 'N';
286 l_Insurance_providers_Table Insurance_providers_table;
287 l_proc varchar2(72) := g_package || 'get_insurance_providers';
288
289 cursor c_insurance_providers (c_org_id hr_organization_units.organization_id%type) is
290 select HL.ORG_LINK_INFORMATION1 status,
291 HL.CHILD_ORGANIZATION_ID child_org_id,
292 HL.ORG_LINK_INFORMATION2 Class_Of_Risk,
293 HL.ORG_LINK_INFORMATION3 Membership_Number,
294 HU.NAME Name
295 from
296 HR_DE_ORGANIZATION_LINKS HL,
297 HR_ORGANIZATION_UNITS HU
298 where
299 Parent_Organization_id = c_org_id -- the internal org id
300 and Org_link_information_category = 'DE_LIABILITY_INSURANCE'
301 and HL.ORG_LINK_INFORMATION1 <> 'DE_INACTIVE'
302 and HL.CHILD_ORGANIZATION_ID = HU.ORGANIZATION_ID;
303
304
305 BEGIN
306 --
307 --
308 -- Only need to attempt to build a list of insurance providers if the organization belongs
309 -- to the named hierarchy.
310 --
311 if org_exists_in_hierarchy(p_org_id) = 'Y' then
312 FOR v_organization in organization_hierarchy(p_org_id
313 ,latest_named_hierarchy_vers(p_org_id)
314 ,org_exists_in_hierarchy(p_org_id)) LOOP
315 FOR v_provider in c_insurance_providers (v_organization.organization_id_parent) loop
316 l_duplicate := 'N';
317 FOR l_record_count2 in 1 .. l_record_count LOOP
318 IF v_provider.child_org_id=l_Insurance_providers_Table(l_record_count2).child_org_id THEN
319 l_duplicate := 'Y';
320 EXIT;
321 END IF;
322 END LOOP;
323 IF l_duplicate = 'N' THEN
324 l_record_count := l_record_count + 1;
325 l_Insurance_providers_Table(l_record_count).child_org_id := v_provider.child_org_id;
326 l_Insurance_providers_Table(l_record_count).name := v_provider.name;
327 l_Insurance_providers_Table(l_record_count).membership_Number := v_provider.membership_Number;
328 l_Insurance_providers_Table(l_record_count).Class_Of_Risk := v_provider.Class_Of_Risk;
329 IF l_default = 'N' AND v_provider.status = 'DE_DEFAULT' THEN
330 l_Insurance_providers_Table(l_record_count).status := 'Y';
331 l_default := 'Y';
332 END IF;
333 END IF;
334 END LOOP;
335 END LOOP;
336 end if;
337
338 p_Insurance_providers_Table := l_Insurance_providers_Table;
339 END get_insurance_providers;
340
341 /*------------------------------------------------------------------------------
342 The following procedure checks if the Organization passed in exists in the Prima
343 ry Hierarchy. Called in HREMEA.pll to validate the Organization Name on the Assi
344 gnment form
345 --------------------------------------------------------------------------------*/
346 PROCEDURE chk_for_org_in_hierarchy(p_org_id in hr_organization_units.organization_id%TYPE,
347 p_exists out nocopy varchar2) IS
351 p_exists := org_exists_in_hierarchy(p_org_id);
348 l_organization_id hr_organization_units.organization_id%TYPE;
349 l_level number;
350 BEGIN
352 END chk_for_org_in_hierarchy;
353
354 /*----------------------------------------------------------------------------------
355 The following package is for complex funtions required for the Work Incidents Report
356 Called in view HR_DE_WORK_INCIDENTS_REPORT.
357 1) get_liab_prov_details(Assignment_id of employee whose work incident report is to be run)
358 Parameters :
359 IN : Assignment_id of employee whose work incident report is to be run
360 OUT : Default Workers Liab Prov id(15 chars)
361 Global: Default Workers Liab Prov id(15 chars)||'@'||Name(240 chars)||'@'||Membership number with int org(20 chars)
362
363 CALLED IN : SQL for view HR_DE_WORK_INCIDENTS_REPORT.
364 -----------------------------------------------------------------------------------*/
365
366 FUNCTION get_liab_prov_details (p_assignment_id in per_assignments_f.assignment_id%TYPE,
367 p_incident_date in date)
368 RETURN VARCHAR2 IS
369 l_exempt varchar2(5);
370 l_liab_prov hr_organization_units.organization_id%TYPE;
371 l_org_id hr_organization_units.organization_id%TYPE;
372 l_membership_no varchar2(50);
373 l_duplicate varchar2(1);
374 l_record_count integer := 0;
375 l_default varchar2(1) := 'N';
376 l_name hr_organization_units.name%TYPE;
377 l_inc_date date;
378
379 cursor c_ins_providers (c_org_id hr_organization_units.organization_id%type) is
380 select HL.ORG_LINK_INFORMATION1 status,
381 Rpad(HL.CHILD_ORGANIZATION_ID, 15, ' ') child_org_id,
382 HL.ORG_LINK_INFORMATION2 Class_Of_Risk,
383 HL.ORG_LINK_INFORMATION3 Membership_Number,
384 rpad(HU.NAME, 240, ' ') Name
385 from
386 HR_DE_ORGANIZATION_LINKS HL,
387 HR_ORGANIZATION_UNITS HU
388 where
389 Parent_Organization_id = c_org_id -- the internal org id
390 and Org_link_information_category = 'DE_LIABILITY_INSURANCE'
391 and HL.CHILD_ORGANIZATION_ID = HU.ORGANIZATION_ID;
392
393
394 BEGIN
395 -- Querying organization recorded on the assignment
396 -- dbms_output.put_line('In Function get_liab_prov_details for assg id '||p_assignment_id);
397 l_inc_date := p_incident_date;
398 SELECT unique(organization_id) INTO l_org_id
399 FROM per_assignments_f
400 WHERE assignment_id = p_assignment_id
401 AND primary_flag = 'Y'
402 AND p_incident_date BETWEEN effective_start_date and effective_end_date;
403 -- Querying employee has Workers Liability details recorded against the assignment SCL
404 SELECT substr(segment2,1,5), substr(segment3,1,15) INTO l_exempt, l_liab_prov FROM Hr_soft_coding_keyflex
405 WHERE soft_coding_keyflex_id =
406 (SELECT unique(soft_coding_keyflex_id) FROM per_assignments_f WHERE assignment_id = p_assignment_id and p_incident_date BETWEEN effective_start_date AND effective_end_date);
407 IF SQL%FOUND THEN
408 IF l_exempt = 'N' THEN
409 SELECT org.name, NVL(orl.org_link_information3, 'NULL') INTO l_name, l_membership_no
410 FROM hr_de_organization_links orl, hr_organization_units org
411 WHERE orl.parent_organization_id = l_org_id
412 AND orl.child_organization_id = l_liab_prov
413 AND org.organization_id = l_liab_prov;
414 -- Copy to Global package variables
415 g_assg_id := p_assignment_id;
416 g_liab_prov := rpad(l_liab_prov,15,' ')||'@'||rpad(l_name,240,' ')||'@'||l_membership_no;
417 --
418 RETURN l_liab_prov;
419 END IF;
420 END IF;
421 RETURN NULL;
422 EXCEPTION
423 -- -----------------------------------------------------------------------------------
424 WHEN NO_DATA_FOUND THEN -- No data in the SCL
425 -- dbms_output.put_line(' In exception block for NO_DATA_FOUND on SCL');
426 --
427 -- As employee assignment doesn't have Workers Liability details recorded,
428 -- Starting with Organization recorded on the employee assignment, Query WLI details
429 -- and walk up the primary org hierarchy to find the 1st default provider
430 --
431 IF l_default = 'N' THEN
432 -- dbms_output.put_line( 'Walking up Primary Org hierarchy as no WLI details on assgt org' );
433 FOR v_organization in organization_hierarchy(l_org_id
434 ,latest_named_hierarchy_vers(l_org_id)
435 ,org_exists_in_hierarchy(l_org_id)) LOOP
436 /* dbms_output.put_line(' For org '||v_organization.organization_id_parent
437 ||' at level '||v_organization.lev);
438 */
439 FOR v_provider in c_ins_providers (v_organization.organization_id_parent) LOOP
440 IF l_default = 'N' AND v_provider.status = 'DE_DEFAULT' THEN
441 -- Copy to Global package variables
442 g_assg_id := p_assignment_id;
443 g_liab_prov := v_provider.child_org_id||'@'||v_provider.name||'@'||v_provider.Membership_number;
444 --
445 RETURN v_provider.child_org_id;
446 l_default := 'Y';
447 END IF;
448 END LOOP;
449 IF c_ins_providers%ISOPEN THEN
450 CLOSE c_ins_providers;
451 END IF;
452 END LOOP;
453 IF organization_hierarchy%ISOPEN THEN
454 CLOSE organization_hierarchy;
455 END IF;
456 END IF;
457 RETURN NULL;
461 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
458 -- ------------------------------------------------------------
459 END get_liab_prov_details;
460
462 /* Wrapper Functions returning details
463 1)Name of Provider
464 2)Membership no from string returned above */
465 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
466 FUNCTION get_liab_prov_name(p_assignment_id in per_assignments_f.assignment_id%TYPE)
467 RETURN VARCHAR2 IS
468 org_name hr_organization_units.name%TYPE;
469 BEGIN
470 IF g_assg_id = p_assignment_id THEN
471 org_name := SUBSTR(g_liab_prov, 17, 240);
472 RETURN org_name;
473 ELSE
474 RETURN NULL;
475 END IF;
476 END get_liab_prov_name;
477 ---
478 FUNCTION get_liab_prov_membership_no(p_assignment_id in per_assignments_f.assignment_id%TYPE)
479 RETURN VARCHAR2 IS
480 mem_no varchar2(50);
481 BEGIN
482 IF g_assg_id = p_assignment_id THEN
483 mem_no := SUBSTR(g_liab_prov, 258, 20);
484 -- g_liab_prov := NULL;
485 RETURN mem_no;
486 ELSE
487 RETURN NULL;
488 END IF;
489 END get_liab_prov_membership_no;
490
491
492 /* -------------------------------------------------------------------------------------
493 Get Location for organization id returned by above function
494 ------------------------------------------------------------------------------------*/
495
496 FUNCTION get_location(p_assignment_id in per_assignments_f.assignment_id%TYPE)
497 RETURN VARCHAR2 IS
498 l_loc_id hr_locations.location_id%TYPE;
499 l_add_1 varchar2(240); --hr_locations.address_line_1%TYPE;
500 l_add_2 varchar2(240); --hr_locations.address_line_2%TYPE;
501 l_add_3 varchar2(240); --hr_locations.address_line_3%TYPE;
502 l_town hr_locations.town_or_city%TYPE;
503 l_country hr_locations.country%TYPE;
504 l_post hr_locations.postal_code%TYPE;
505 l_loc varchar2(861);
506 l_org_id hr_organization_units.organization_id%type;
507 l_sqlcode number;
508 l_mssg varchar2(100);
509
510 CURSOR C_location (c_org_id hr_organization_units.organization_id%type) IS
511 SELECT rpad(nvl(LOC.location_id, 999999), 15, ' ') location_id,
512 rpad(nvl(LOC.address_line_1, 'XXXXXX'), 240, ' ') address_line_1,
513 rpad(nvl(LOC.address_line_2, 'XXXXXX'), 240, ' ') address_line_2,
514 rpad(nvl(LOC.address_line_3, 'XXXXXX'), 240, ' ') address_line_3,
515 rpad(nvl(LOC.town_or_city, 'XXXXXX'), 30, ' ') town_or_city,
516 rpad(nvl(LOC.country, 'XXXXXX'), 60, ' ') country,
517 rpad(nvl(LOC.postal_code, 'XXXXXX'), 30, ' ') postal_code
518 -- INTO l_loc_id, l_add_1, l_add_2, l_add_3, l_town , l_country, l_post
519 -- INTO location_table
520 FROM hr_locations LOC WHERE LOC.location_id =
521 (select ORG.location_id
522 FROM hr_organization_units ORG WHERE ORG.organization_id = c_org_id);
523
524
525 BEGIN
526 -- dbms_output.put_line('In Function get_location for assg id '||p_assignment_id);
527 IF g_assg_id = p_assignment_id THEN
528 -- dbms_output.put_line('Org Value exists globally for assgt id '||g_assg_id);
529 -- dbms_output.put_line('Org Value existing globally '||g_liab_prov);
530 l_org_id := substr(g_liab_prov, 1, 15);
531 -- dbms_output.put_line('Org Value extrcted '||l_org_id);
532 IF l_org_id IS NOT NULL THEN
533 OPEN C_location(l_org_id);
534 FETCH C_location
535 INTO l_loc_id, l_add_1, l_add_2, l_add_3, l_town , l_country, l_post;
536 -- dbms_output.put_line('Org Valueis extracted '||l_org_id);
537 CLOSE C_location;
538 /* dbms_output.put_line('Loc id is '||l_loc_id);dbms_output.put_line('Loc id is '||l_add_1);
539 dbms_output.put_line('Loc id is '||l_add_2);dbms_output.put_line('Loc id is '||l_add_3);
540 dbms_output.put_line('Loc id is '||l_town);dbms_output.put_line('Loc id is '||l_country);
541 dbms_output.put_line('Loc id is '||l_post);
542 */
543 l_loc := rpad(to_char(l_loc_id), 15,' ')||'@'||l_add_1||'@'||l_add_2||'@'||l_add_3||'@'||l_town||'@'||l_country||'@'||l_post;
544 g_loc := rpad(to_char(l_loc_id), 15,' ')||'@'||l_add_1||'@'||l_add_2||'@'||l_add_3||'@'||l_town||'@'||l_country||'@'||l_post;
545 /* dbms_output.put_line('Location is '||substr(l_loc,1,200));
546 dbms_output.put_line('Location is '||substr(l_loc,200,200));
547 */
548 RETURN l_loc;
549 END IF;
550 END IF;
551 RETURN (l_loc);
552 EXCEPTION
553 -- ------------------------------------------------------------
554 WHEN NO_DATA_FOUND THEN
555 -- dbms_output.put_line('NO_DATAFOUND');
556 RETURN NULL;
557 -- ------------------------------------------------------------
558 END get_location;
559
560 /* Wrapper Functions returning individual components of Location Address */
561 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
562 FUNCTION get_addr_line1 (p_assignment_id in per_assignments_f.assignment_id%TYPE)
563 RETURN VARCHAR2 IS
564 l_line1 varchar2(240);
565
566 BEGIN
567 -- dbms_output.put_line('In Function get_addr_line1 for assg id '||p_assignment_id);
568 IF g_assg_id = p_assignment_id THEN
569 IF g_loc IS NOT NULL THEN
570 l_line1 := substr(g_loc, 17, 240);
571 RETURN(l_line1);
575 END IF;
572 ELSE
573 RETURN NULL;
574 END IF;
576 RETURN NULL;
577 END get_addr_line1;
578 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
579 FUNCTION get_addr_line2 (p_assignment_id in per_assignments_f.assignment_id%TYPE)
580 RETURN VARCHAR2 IS
581 l_line2 varchar2(240);
582
583 BEGIN
584 -- dbms_output.put_line('In Function get_addr_line2 for assg id '||p_assignment_id);
585 IF g_assg_id = p_assignment_id THEN
586 IF g_loc IS NOT NULL THEN
587 l_line2 := substr(g_loc, 258, 240);
588 RETURN(l_line2);
589 ELSE
590 RETURN NULL;
591 END IF;
592 END IF;
593 RETURN NULL;
594 END get_addr_line2;
595 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
596 FUNCTION get_addr_line3 (p_assignment_id in per_assignments_f.assignment_id%TYPE)
597 RETURN VARCHAR2 IS
598 l_line3 varchar2(240);
599
600 BEGIN
601 -- dbms_output.put_line('In Function get_addr_line3 for assg id '||p_assignment_id);
602 IF g_assg_id = p_assignment_id THEN
603 IF g_loc IS NOT NULL THEN
604 l_line3 := substr(g_loc, 499, 240);
605 RETURN(l_line3);
606 ELSE
607 RETURN NULL;
608 END IF;
609 END IF;
610 RETURN NULL;
611 END get_addr_line3;
612 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
613 FUNCTION get_town (p_assignment_id in per_assignments_f.assignment_id%TYPE)
614 RETURN VARCHAR2 IS
615 l_town varchar2(30);
616
617 BEGIN
618 -- dbms_output.put_line('In Function get_town for assg id '||p_assignment_id);
619 IF g_assg_id = p_assignment_id THEN
620 IF g_loc IS NOT NULL THEN
621 l_town := substr(g_loc, 740, 30);
622 RETURN(l_town);
623 ELSE
624 RETURN NULL;
625 END IF;
626 END IF;
627 RETURN NULL;
628 END get_town;
629 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
630 FUNCTION get_country (p_assignment_id in per_assignments_f.assignment_id%TYPE)
631 RETURN VARCHAR2 IS
632 l_country varchar2(60);
633
634 BEGIN
635 -- dbms_output.put_line('In Function get_country for assg id '||p_assignment_id);
636 IF g_assg_id = p_assignment_id THEN
637 IF g_loc IS NOT NULL THEN
638 l_country := substr(g_loc, 771, 60);
639 RETURN(l_country);
640 ELSE
641 RETURN NULL;
642 END IF;
643 END IF;
644 RETURN NULL;
645 END get_country;
646 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
647 FUNCTION get_postal_code(p_assignment_id in per_assignments_f.assignment_id%TYPE)
648 RETURN VARCHAR2 IS
649 l_postal_code varchar2(30);
650
651 BEGIN
652 -- dbms_output.put_line('In Function get_postal_code for assg id '||p_assignment_id);
653 IF g_assg_id = p_assignment_id THEN
654 IF g_loc IS NOT NULL THEN
655 l_postal_code := substr(g_loc, 832, 30);
656 RETURN(l_postal_code);
657 ELSE
658 RETURN NULL;
659 END IF;
660 END IF;
661 RETURN NULL;
662 END get_postal_code;
663 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
664
665
666 /*----------------------------------------------------------------------------------
667
668 The following function returns the values required for the Work Incidents Report
669 It basically concatenates the output of above proc get_org_data_items to be used in the SQL
670 for the view HR_DE_WORK_INCIDENTS_REPORT.
671 1) get_liab_prov_details(Assignment_id of employee whose work incident report is to be run)
672 Parameters :
673 IN : Assignment_id of employee whose work incident report is to be run
674 OUT : Chamber_Contribution(30 chars)||Employer_betriebsnummer(30 chars)||Payroll_betriebsnummer(30 chars)
675
676 CALLED IN : SQL for view HR_DE_WORK_INCIDENTS_REPORT.
677
678 -----------------------------------------------------------------------------------*/
679
680 FUNCTION get_liab_prov_details2(p_assignment_id in per_assignments_f.assignment_id%TYPE,
681 p_incident_date in date)
682 RETURN VARCHAR2 IS
683 l_cc varchar2(30);
684 l_e_bet varchar2(30);
685 l_p_bet varchar2(30);
686 l_org_id hr_organization_units.organization_id%TYPE;
687 l_all varchar2(100);
688 l_inc_date date;
689
690 BEGIN
691 SELECT unique(organization_id) INTO l_org_id
692 FROM per_assignments_f
693 WHERE assignment_id = p_assignment_id
694 AND primary_flag = 'Y'
695 AND p_incident_date BETWEEN effective_start_date and effective_end_date;
696 -- dbms_output.put_line(' Function details2 calling get_org_data_items for '||l_org_id);
697 -- Calling Procedure defined above as it serves the purpose completely
698 --
699 hr_de_org_info.get_org_data_items(p_chamber_contribution_out => l_cc,
700 p_employer_Betriebsnummer => l_e_bet,
701 p_payroll_Betriebsnummer => l_p_bet,
702 p_org_id => l_org_id);
703 --dbms_output.put_line(' Cover Func: CC '||l_cc||' EB '||l_e_bet||' PB '||l_p_bet);
704 -- To handle NULLS
705 SELECT nvl(l_cc,' ')
706 ,nvl(l_e_bet,' ')
707 ,nvl(l_p_bet,' ')
708 INTO l_cc, l_e_bet, l_p_bet
709 FROM dual;
710
711
712
713 l_all := l_cc || l_e_bet|| l_p_bet;
717 IF org_hierarchy%ISOPEN THEN
714 -- dbms_output.put_line('ALL '||l_all);
715 RETURN l_all;
716
718 CLOSE org_hierarchy;
719 END IF;
720 END get_liab_prov_details2;
721
722
723
724 /*----------------------------------------------------------------------------------
725 The following function is required for the Work Incidents Report
726 Called in view HR_DE_WORK_INCIDENTS_REPORT.
727 Parameters :
728 IN : Assignment_id of employee whose work incident report is to be run
729 OUT : Supervising Office Org id(15 chars)
730 Global : Supervising Office Org id(15 chars)||'@'||Supervising Off org name(240 chars)
731
732 CALLED IN : SQL for view HR_DE_WORK_INCIDENTS_REPORT.
733 -----------------------------------------------------------------------------------*/
734
735 FUNCTION get_supervising_off (p_assignment_id in per_assignments_f.assignment_id%TYPE,
736 p_incident_date in date)
737 RETURN VARCHAR2 IS
738 l_sup_off hr_organization_units.organization_id%TYPE;
739 l_org_id hr_organization_units.organization_id%TYPE;
740 l_inc_date date;
741 l_record_count integer := 0;
742 l_name hr_organization_units.name%TYPE;
743 l_sqlcode number;
744 l_mssg varchar2(100);
745
746 cursor c_sup_off (c_org_id hr_organization_units.organization_id%type) IS
747 SELECT Rpad(HL.CHILD_ORGANIZATION_ID, 15, ' ') child_org_id,
748 Rpad(HU.NAME, 240, ' ') Name
749 FROM
750 HR_DE_ORGANIZATION_LINKS HL,
751 HR_ORGANIZATION_UNITS HU
752 WHERE
753 Parent_Organization_id = c_org_id -- the internal org id
754 AND Org_link_type = 'DE_WRK_INC_SUP_OFF'
755 AND HL.CHILD_ORGANIZATION_ID = HU.ORGANIZATION_ID;
756
757 BEGIN
758 -- Querying organization recorded on the assignment
759 -- dbms_output.put_line('In Function get_supervising_off for assg id '||p_assignment_id||' on '||p_incident_date);
760 SELECT unique(organization_id) INTO l_org_id
761 FROM per_assignments_f
762 WHERE assignment_id = p_assignment_id
763 AND primary_flag = 'Y'
764 AND p_incident_date BETWEEN effective_start_date and effective_end_date;
765
766 -- Starting with Organization recorded on the employee assignment, Query Supervising Off details
767 -- and if not found then walk UP the primary org hierarchy to find the 1st Supervising Off
768 --
769 -- dbms_output.put_line(' Getting Supervising Offices ');
770 IF Organization_hierarchy%ISOPEN THEN
771 CLOSE Organization_hierarchy;
772 END IF;
773
774 FOR v_organization in Organization_hierarchy(l_org_id
775 ,latest_named_hierarchy_vers(l_org_id)
776 ,org_exists_in_hierarchy(l_org_id)) LOOP
777 /* dbms_output.put_line(' For org '||v_organization.organization_id_parent
778 ||' at level '||v_organization.lev);
779 */
780 null;
781 IF c_sup_off%ISOPEN THEN
782 CLOSE c_sup_off;
783 END IF;
784 FOR v_sup_off in c_sup_off (v_organization.organization_id_parent) LOOP
785 -- dbms_output.put_line(' Supervising Off Org is '||v_sup_off.Name||'@'||v_sup_off.child_org_id);
786 g_assg_id := p_assignment_id;
787 g_super_off := v_sup_off.child_org_id||'@'||v_sup_off.name;
788 RETURN v_sup_off.child_org_id;
789 END LOOP;
790 IF c_sup_off%ISOPEN THEN
791 CLOSE c_sup_off;
792 END IF;
793 END LOOP;
794 IF Organization_hierarchy%ISOPEN THEN
795 CLOSE Organization_hierarchy;
796 END IF;
797 RETURN NULL;
798 EXCEPTION
799 -- ------------------------------------------------------------
800 WHEN NO_DATA_FOUND THEN
801 RETURN NULL;
802 END get_supervising_off;
803
804 /* Wrapper Functions returning Supervising Off Org Name */
805 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --
806 FUNCTION get_supervising_off_name(p_assignment_id in per_assignments_f.assignment_id%TYPE)
807 RETURN VARCHAR2 IS
808 org_name hr_organization_units.name%TYPE;
809 BEGIN
810 IF g_assg_id = p_assignment_id THEN
811 org_name := SUBSTR(g_super_off, 17, 240);
812 RETURN org_name;
813 ELSE
814 RETURN NULL;
815 END IF;
816 END get_supervising_off_name;
817 -- ************************************************************************************* --
818 END HR_DE_ORG_INFO;
819
820
[Home] [Help]