1 PACKAGE BODY HR_NL_ORG_INFO AS
2 /* $Header: penlorgi.pkb 120.5 2007/03/12 15:19:30 summohan ship $ */
3 g_package varchar2(33) := ' HR_NL_ORG_INFO.';
4 --
5 --
6 --Cursor which fetches Organizations from the named hierarchy-bottom to top
7 --
8 CURSOR org_hierarchy(p_org_id NUMBER) IS
9 SELECT p_org_id organization_id_parent
10 ,0 lev
11 FROM dual
12 UNION
13 SELECT organization_id_parent
14 ,level
15 FROM
16 (SELECT organization_id_parent
17 ,organization_id_child
18 FROM per_org_structure_elements
19 WHERE org_structure_version_id = latest_named_hierarchy_vers(p_org_id)
20 )
21 START WITH organization_id_child = p_org_id
22 CONNECT BY PRIOR organization_id_parent = organization_id_child
23 ORDER BY lev;
24 --
25 --
26 -- Service function to return the current named hioerarchy.
27 --
28 FUNCTION named_hierarchy
29 (p_organization_id NUMBER) RETURN NUMBER IS
30 --
31 --
32 -- Cursor to return the current named hierarchy.
33 --
34 CURSOR c_hierarchy(vp_organization_id NUMBER) IS
35 SELECT TO_NUMBER(inf.org_information1) organization_structure_id
36 FROM hr_organization_information inf
37 ,hr_all_organization_units org
38 WHERE org.organization_id = vp_organization_id
39 AND inf.organization_id = org.business_group_id
40 AND inf.org_information_context = 'NL_BG_INFO'
41 AND inf.org_information1 IS NOT NULL;
42 --
43 --
44 -- Local Variables.
45 --
46 l_rec c_hierarchy%ROWTYPE;
47 l_proc varchar2(72) := g_package || '.named_hierarchy';
48 BEGIN
49 --
50 --
51 -- Find the current named organization hierarchy.
52 --
53 hr_utility.set_location('Entering ' || l_proc, 100);
54 OPEN c_hierarchy(vp_organization_id => p_organization_id);
55 FETCH c_hierarchy INTO l_rec;
56 CLOSE c_hierarchy;
57 hr_utility.set_location('Leaving ' || l_proc, 900);
58 --
59 --
60 -- Return ID.
61 --
62 RETURN l_rec.organization_structure_id;
63 EXCEPTION
64 when others then
65 hr_utility.set_location('Exception :' ||l_proc||SQLERRM(SQLCODE),999);
66 END named_hierarchy;
67 --
68 --
69 -- Service function to return the current version of the named hierarchy.
70 --
71 FUNCTION latest_named_hierarchy_vers
72 (p_organization_id NUMBER) RETURN NUMBER IS
73 --
74 --
75 -- Cursor to return the current named hierarchy version.
76 --
77 CURSOR c_hierarchy_version(vp_organization_id NUMBER) IS
78 SELECT sv.org_structure_version_id, sv.version_number
79 FROM per_org_structure_versions sv
80 ,fnd_sessions ses
81 WHERE sv.organization_structure_id = named_hierarchy(vp_organization_id)
82 AND ses.session_id = USERENV('sessionid')
83 AND ses.effective_date BETWEEN sv.date_from
84 AND NVL(sv.date_to, Hr_general.End_Of_time)
85 ORDER BY sv.version_number DESC;
86 --
87 --
88 -- Local Variables.
89 --
90 l_rec c_hierarchy_version%ROWTYPE;
91 l_proc varchar2(72) := g_package || '.latest_named_hierarchy_vers';
92 BEGIN
93 hr_utility.set_location('Entering ' || l_proc, 100);
94 --
95 --
96 -- Find the current primary organization hierarchy.
97 --
98 OPEN c_hierarchy_version(vp_organization_id => p_organization_id);
99 FETCH c_hierarchy_version INTO l_rec;
100 CLOSE c_hierarchy_version;
101 hr_utility.set_location('Leaving ' || l_proc, 900);
102 --
103 --
104 -- Return ID.
105 --
106 RETURN l_rec.org_structure_version_id;
107 EXCEPTION
108 when others then
109 hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
110
111 END latest_named_hierarchy_vers;
112 --
113 --
114 -- Service function to see if organization belongs to the
115 -- current named hierarchy.
116 --
117 FUNCTION org_exists_in_hierarchy
118 (p_organization_id NUMBER) RETURN VARCHAR2 IS
119 --
120 --
121 -- Cursor to see if the organization belongs to the current
122 -- named hierarchy.
123 --
124 CURSOR c_org_exists(vp_organization_id NUMBER) IS
125 SELECT se.organization_id_child
126 FROM per_org_structure_elements se
127 WHERE se.org_structure_version_id =
128 latest_named_hierarchy_vers(vp_organization_id)
129 AND (se.organization_id_parent = vp_organization_id OR
130 se.organization_id_child = vp_organization_id);
131 --
132 --
133 -- Local Variables.
134 --
135 l_rec c_org_exists%ROWTYPE;
136 l_proc varchar2(72) := g_package || '.org_exists_in_hierarchy';
137 BEGIN
138 hr_utility.set_location('Entering ' || l_proc, 100);
139 OPEN c_org_exists(vp_organization_id => p_organization_id);
140 FETCH c_org_exists INTO l_rec;
141 IF c_org_exists%FOUND THEN
142 CLOSE c_org_exists;
143 hr_utility.set_location('Leaving ' || l_proc, 900);
144 RETURN 'Y';
145 ELSE
146 CLOSE c_org_exists;
147 hr_utility.set_location('Leaving ' || l_proc, 910);
148 RETURN 'N';
149 END IF;
150 EXCEPTION
151 when others then
152 hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
153 END org_exists_in_hierarchy;
154
155 /* -----------------------------------------------------------------------
156 The procedure will return the value of the data item (Region and
157 Organization Number) required. The org_id taken as input is the org_id for
158 which the value is required.The procedure will navigate from the org_id
159 supplied up the hierarchy until it finds a value for the data item. |
160 The following data items are required ; |
161 Data Item Column Table Context |
162 1. Region org_information1 hr_organization_information NL_ORG_INFO |
163 2. Org. Number org_information1 hr_organization_information NL_ORG_INFO |
164 ---------------------------------------------------------------------------*/
165
166 PROCEDURE get_org_data_items(
167 p_org_id in number,
168 p_region out nocopy varchar2,
169 p_organization_number out nocopy varchar2) IS
170 l_proc varchar2(72) := g_package || '.get_org_data_items';
171 l_all_items_found boolean := FALSE;
172 l_level number;
173 l_organization_id hr_organization_units.organization_id%type;
174 l_region varchar2(255);
175 temp_region varchar2(255);
176 l_organization_number varchar2(255);
177 temp_organization_number varchar2(255);
178 l_org_id hr_organization_units.organization_id%type;
179 l_org_information_context
180 hr_organization_information.org_information_context%type;
181 /* Add a check to see if the data item has a value for the org_id supplied
182 - add as a union or separate query */
183 CURSOR org_data_items
184 (l_org_id in hr_organization_units.organization_id%type) IS
185 select
186 substr(org_information1, 1, 30),
187 substr(org_information2, 1, 30)
188 from
189 hr_organization_units d,
190 hr_organization_information e
191 where
192 d.organization_id = e.organization_id and
193 d.organization_id = l_org_id and
194 e.org_information_context in ('NL_ORG_INFORMATION');
195 BEGIN
196 hr_utility.set_location('Entering ' || l_proc, 100);
197 temp_region := null;
198 temp_organization_number := null;
199 open org_hierarchy(p_org_id);
200 LOOP
201 fetch org_hierarchy into l_organization_id,l_level;
202 exit when org_hierarchy%NOTFOUND or l_all_items_found =TRUE ;
203
204 --Fetch Organization Information
205 open org_data_items (l_organization_id);
206 fetch org_data_items into l_region,l_organization_number;
207 if org_data_items%found then
208 if l_region is not Null and temp_region is null then
209 temp_region := l_region;
210 end if;
211 if l_organization_number is not Null
212 and temp_organization_number is Null then
213 temp_organization_number := l_organization_number;
214 end if;
215 if temp_region is not null and
216 temp_organization_number is not null then
217 l_all_items_found :=TRUE;
218 else
219 l_all_items_found :=FALSE ;
220 end if;
221 end if;
222 close org_data_items;
223
224 END LOOP;
225 close org_hierarchy;
226 p_region := temp_region;
227 p_organization_number := temp_organization_number;
228 hr_utility.set_location('Leaving ' || l_proc, 900);
229
230
231 EXCEPTION
232 when others then
233 hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
234 p_region := null;
235 p_organization_number := null;
236 END get_org_data_items;
237 /*------------------------------------------------------------------------
238 The following procedure checks if the Organization passed in exists in the
239 Primary Hierarchy.
240 --------------------------------------------------------------------------*/
241 PROCEDURE chk_for_org_in_hierarchy
242 (p_org_id in hr_organization_units.organization_id%TYPE,
243 p_exists out nocopy varchar2) IS
244 l_organization_id hr_organization_units.organization_id%TYPE;
245 l_level number;
246 l_proc varchar2(72) := g_package || '.chk_for_org_in_hierarchy';
247 BEGIN
248 hr_utility.set_location('Entering ' || l_proc, 100);
249 p_exists := org_exists_in_hierarchy(p_org_id);
250 hr_utility.set_location('Leaving ' || l_proc, 100);
251 END chk_for_org_in_hierarchy;
252 --
253 --
254 -- Function checks to see if organization belongs to the same region from
255 -- the Org Hierarchy
256 --
257 FUNCTION Check_Org_In_Region
258 (p_org_id in hr_organization_units.organization_id%TYPE,
259 p_region in varchar2)
260 RETURN hr_organization_units.organization_id%TYPE IS
261 l_organization_id hr_organization_units.organization_id%TYPE;
262 l_level number;
263
264 CURSOR cur_Region
265 (vp_Organization_ID in hr_organization_units.organization_id%TYPE,
266 vp_region in varchar2) IS
267 SELECT Organization_id,
268 org_information1 Region
269 FROM Hr_Organization_information
270 WHERE Organization_ID = vp_Organization_ID
271 AND Org_Information_Context='NL_ORG_INFORMATION';
272 v_cur_Region cur_Region%ROWTYPE;
273 --
274 --
275 -- Cursor which fetches Organizations from the named hierarchy
276 --
277 CURSOR org_named_hierarchy(vp_Organization_ID NUMBER) IS
278 SELECT vp_Organization_ID Organization_id,
279 0 lev
280 FROM dual
281 UNION
282 SELECT organization_id_parent
283 ,level lev
284 FROM
285 (
286 SELECT organization_id_parent
287 ,organization_id_child
288 FROM per_org_structure_elements
289 WHERE org_structure_version_id =
290 hr_nl_org_info.latest_named_hierarchy_vers(vp_Organization_ID)
291 )
292 START WITH organization_id_child= vp_Organization_ID
293 CONNECT BY PRIOR organization_id_parent= organization_id_child
294 ORDER BY lev;
295 v_org_hierarchy org_named_hierarchy%ROWTYPE;
296 b_RegionInfoFound boolean := FALSE;
297 l_proc varchar2(72) := g_package || '.Check_Org_In_Region';
298 BEGIN
299 hr_utility.set_location('Entering ' || l_proc, 100);
300 IF p_region is NOT NULL THEN
301 OPEN org_named_hierarchy(p_org_id);
302 LOOP
303 hr_utility.set_location('Inside ' || l_proc, 105);
304 FETCH org_named_hierarchy INTO v_org_hierarchy;
305 EXIT WHEN org_named_hierarchy%NOTFOUND or b_RegionInfoFound=TRUE;
306 OPEN cur_Region(v_org_hierarchy.organization_id,p_region);
307 FETCH cur_Region INTO v_cur_Region;
308 hr_utility.set_location('Inside ' || l_proc, 110);
309 IF cur_Region%FOUND THEN
310 hr_utility.set_location('Inside ' || l_proc, 115);
311 IF v_cur_Region.Region IS NOT NULL AND
312 v_cur_Region.Region=p_region THEN
313 hr_utility.set_location('Inside ' || l_proc, 120);
314 l_organization_id := p_org_id;
315 b_RegionInfoFound := TRUE;
316 ELSIF v_cur_Region.Region IS NOT NULL AND
317 v_cur_Region.Region<>p_region THEN
318 hr_utility.set_location('Inside ' || l_proc, 125);
319 l_organization_id := null;
320 b_RegionInfoFound := TRUE;
321 END IF;
322 END IF;
323 CLOSE cur_Region;
324 END LOOP;
325 hr_utility.set_location('Inside ' || l_proc, 130);
326 CLOSE org_named_hierarchy;
327 END IF;
328 hr_utility.set_location('Leaving ' || l_proc, 900);
329 return l_organization_id;
330 EXCEPTION
331 when others then
332 hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
333 END Check_Org_In_Region;
334 -- Service function which returns the SI Provider information for the given organization.
335 -- It performs tree walk if SI information is not defined for the given organization.
336
337 FUNCTION Get_SI_Org_Id
338 (p_organization_id NUMBER,p_si_type VARCHAR2,p_assignment_id NUMBER) RETURN NUMBER IS
339 l_proc varchar2(72) := g_package || '.Get_SI_Org_Id';
340
341
342 --
343 -- Cursor which fetches Social Insurance Provider overridden at the Assignment Level
344 -- ordering the records by the si class order (A record for a Individual SI type would be
345 -- ordered higher than a AMI record).
346 CURSOR asg_provider
347 (l_org_id in hr_organization_units.organization_id%type,l_si_type varchar2,l_assgn_id NUMBER) IS
348 select pae.aei_information8 provider,
349 decode(pae.aei_information3,'AMI',0,1) si_class_order
350 from per_assignment_extra_info pae
351 ,fnd_sessions s
352 where assignment_id = l_assgn_id
353 and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
354 'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
355 pae.AEI_INFORMATION3 = DECODE(l_si_type,'WEWA','AMI','WEWE','AMI','WAOD','AMI','WAOB','AMI',
356 'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
357 'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
358 and s.effective_date between
359 fnd_date.canonical_to_date(pae.aei_information1)
360 and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
361 s.session_id=userenv('sessionid')
362 order by si_class_order desc;
363
364 --
365 -- Cursor which fetches Social Insurance Provider for the given Hr Organization
366 -- and which offers si class ordering the records first by the Primary provider Flag
367 -- and then by the si class order(A record for a Individual SI type would be
368 -- ordered higher than a AMI record).
369 CURSOR org_uwv_provider
370 (l_org_id in hr_organization_units.organization_id%type,
371 l_uwv_id in hr_organization_units.organization_id%type,
372 l_si_type varchar2) IS
373 select
374 e.org_information_id,
375 e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
376 decode(e.org_information3,'AMI',0,1) si_class_order
377 from
378 hr_organization_information e
379 ,fnd_sessions s
380 where
381 e.organization_id=l_org_id and
382 e.org_information_context = 'NL_SIP' and
383 (e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
384 'PRIVATE_HEALTH','ZFW',l_si_type) or
385 e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
386 'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
387 'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
388 e.org_information4 = NVL(l_uwv_id,e.org_information4)
389 and s.effective_date between
390 fnd_date.canonical_to_date(e.org_INFORMATION1)
391 and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
392 s.session_id=userenv('sessionid')
393 order by p_flag desc,si_class_order desc;
394
395 v_asg_provider asg_provider%ROWTYPE;
396 v_org_uwv_provider org_uwv_provider%ROWTYPE;
397 l_level number;
398 l_organization_id hr_organization_units.organization_id%TYPE;
399 l_org_found boolean := false;
400 l_uwv_org_id hr_organization_units.organization_id%TYPE;
401 l_org_info_id hr_organization_units.organization_id%TYPE;
402 BEGIN
403 /* Fetch Override Ins Provider at the Asg Level*/
404 OPEN asg_provider(p_organization_id,p_si_type,p_assignment_id);
405 FETCH asg_provider INTO v_asg_provider;
406 CLOSE asg_provider;
407
408
409 /* If Ins Provider at the Asg Level is specified*/
410 IF v_asg_provider.provider IS NOT NULL THEN
411 l_uwv_org_id := v_asg_provider.provider;
412 hr_utility.set_location('Asg Level UWV Prov l_uwv_org_id'||l_uwv_org_id,100);
413 END IF;
414
415 /* If Ins Provider at the Asg Level is not specified
416 tree walk to find the Primary Insurance Provider at the level */
417 --hr_utility.set_location('Calling Get_SI_Org_Id',200);
418
419 l_org_found := FALSE;
420 l_org_info_id := -1;
421 if org_hierarchy%ISOPEN then
422 CLOSE org_hierarchy;
423 END IF;
424 /*Start looking for the UWV Provider beginning from the HR Org
425 of Employee */
426
427 OPEN org_hierarchy(p_organization_id);
428 LOOP
429 FETCH org_hierarchy into l_organization_id,l_level;
430 exit when org_hierarchy%NOTFOUND or l_org_found =TRUE ;
431 --hr_utility.set_location(' l_organization_id'||l_organization_id||' level '||l_level,300);
432 --Fetch UWV Provider assigned to the HR Organization
433 open org_uwv_provider(l_organization_id,l_uwv_org_id,p_si_type);
434 FETCH org_uwv_provider into v_org_uwv_provider;
435 if org_uwv_provider%found then
436 --hr_utility.set_location(' l_organization_id'||l_organization_id||' p_organization_id '||p_organization_id,310);
437 if l_organization_id =p_organization_id then
438 /*Assign the UWV Provider defined at the HR Org
439 But continue further to see if any Primary
440 UWV exists up in the hierarchy*/
441 l_org_info_id := v_org_uwv_provider.org_information_id;
442 --hr_utility.set_location(' Assign -HR Org l_org_info_id'||l_org_info_id,320);
443 else
444 /*Assign the UWV Provider defined at the Parent HR Org if
445 not overridden at the HR Org Level*/
446 if l_org_info_id =-1 then
447 l_org_info_id := v_org_uwv_provider.org_information_id;
448 --hr_utility.set_location(' Parent HR Org l_org_info_id'||l_org_info_id,330);
449 end if;
450 end if;
451 /*Check If the UWV Provider assigned is also the Primary
452 Quit Searching the hierarchy*/
453 if v_org_uwv_provider.p_flag='Y' then
454 l_org_found:=TRUE;
455 l_org_info_id := v_org_uwv_provider.org_information_id;
456 --hr_utility.set_location(' Primary UWV l_org_info_id'||l_org_info_id,340);
457 end if;
458 end if;
459 close org_uwv_provider;
460 END LOOP;
461 close org_hierarchy;
462 --hr_utility.set_location('Org Info Id :'||l_org_info_id||' UWV From Hierarchy l_uwv_org_id'||l_uwv_org_id,350);
463 RETURN l_org_info_id;
464 EXCEPTION
465 when others then
466 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
467 IF org_hierarchy%ISOPEN THEN
468 CLOSE org_hierarchy;
469 END IF;
470 RETURN -1;
471 END Get_SI_Org_Id;
472
473
474 -- Service function which returns the SI Provider information for the given organization.
475 -- It performs tree walk if SI information is not defined for the given organization.
476 --
477
478 FUNCTION Get_SI_Provider_Info
479 (p_organization_id NUMBER,p_si_type VARCHAR2,p_assignment_id NUMBER) RETURN NUMBER IS
480
481 l_proc varchar2(72) := g_package || '.Get_SI_Provider_Info';
482 l_provider_info hr_organization_units.organization_id%type;
483 l_org_id hr_organization_units.organization_id%type;
484
485
486 --
487 -- Cursor which fetches Social Insurance Provider overridden at the Assignment Level
488 -- ordering the records by the si class order (A record for a Individual SI type would be
489 -- ordered higher than a AMI record).
490 CURSOR asg_provider
491 (l_org_id in hr_organization_units.organization_id%type,l_si_type varchar2,l_assgn_id NUMBER) IS
492 select pae.aei_information8 provider,
493 decode(pae.aei_information3,'AMI',0,1) si_class_order
494 from per_assignment_extra_info pae
495 ,fnd_sessions s
496 where assignment_id = l_assgn_id
497 and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
498 'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
499 pae.aei_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI',
500 'WAOB','AMI','WAOD','AMI',
501 'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
502 'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
503 and s.effective_date between
504 fnd_date.canonical_to_date(pae.aei_information1)
505 and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
506 s.session_id=userenv('sessionid')
507 order by si_class_order desc;
508
509 --
510 -- Cursor which fetches Social Insurance Provider for the given Hr Organization
511 -- and which offers SI type ordering the records first by the Primary provider Flag
512 -- and then by the si class order(A record for a Individual SI type would be
513 -- ordered higher than a AMI record).
514 CURSOR org_uwv_provider
515 (l_org_id in hr_organization_units.organization_id%type,l_si_type varchar2) IS
516 select
517 e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
518 decode(e.org_information3,'AMI',0,1) si_class_order
519 from
520 hr_organization_information e
521 ,fnd_sessions s
522 where
523 e.organization_id=l_org_id and
524 e.org_information_context = 'NL_SIP' and
525 (e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
526 'PRIVATE_HEALTH','ZFW',l_si_type) or
527 e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
528 'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
529 'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
530 s.effective_date between
531 fnd_date.canonical_to_date(e.org_INFORMATION1)
532 and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
533 s.session_id=userenv('sessionid')
534 order by p_flag desc,si_class_order desc;
535
536 v_asg_provider asg_provider%ROWTYPE;
537 v_org_uwv_provider org_uwv_provider%ROWTYPE;
538 l_level number;
539 l_organization_id hr_organization_units.organization_id%TYPE;
540 l_uwv_found boolean := false;
541 l_uwv_org_id hr_organization_units.organization_id%TYPE;
542 BEGIN
543 /* Fetch Override Ins Provider at the Asg Level*/
544 OPEN asg_provider(p_organization_id,p_si_type,p_assignment_id);
545 FETCH asg_provider INTO v_asg_provider;
546 CLOSE asg_provider;
547
548
549 /* If Ins Provider at the Asg Level is specified*/
550 IF v_asg_provider.provider IS NOT NULL THEN
551 l_uwv_org_id := v_asg_provider.provider;
552 --hr_utility.set_location('Asg Level UWV Prov l_uwv_org_id'||l_uwv_org_id,100);
553 ELSE
554 /* If Ins Provider at the Asg Level is not specified
555 tree walk to find the Primary Insurance Provider at the level */
556 --hr_utility.set_location('Calling Get_SI_Org_Id',200);
557
558 l_uwv_found := FALSE;
559 l_uwv_org_id := -1;
560 if org_hierarchy%ISOPEN then
561 CLOSE org_hierarchy;
562 END IF;
563 /*Start looking for the UWV Provider beginning from the HR Org
564 of Employee */
565
566 OPEN org_hierarchy(p_organization_id);
567 LOOP
568 FETCH org_hierarchy into l_organization_id,l_level;
569 exit when org_hierarchy%NOTFOUND or l_uwv_found =TRUE ;
570 --hr_utility.set_location(' l_organization_id'||l_organization_id||' level '||l_level,300);
571 --Fetch UWV Provider assigned to the HR Organization
572 open org_uwv_provider(l_organization_id,p_si_type);
573 FETCH org_uwv_provider into v_org_uwv_provider;
574 if org_uwv_provider%found then
575 --hr_utility.set_location(' l_organization_id'||l_organization_id||' p_organization_id '||p_organization_id,310);
576 if l_organization_id =p_organization_id then
577 /*Assign the UWV Provider defined at the HR Org
578 But continue further to see if any Primary
579 UWV exists up in the hierarchy*/
580 l_uwv_org_id := v_org_uwv_provider.provider;
581 --hr_utility.set_location(' Assign -HR Org l_uwv_org_id'||l_uwv_org_id,320);
582 else
583 /*Assign the UWV Provider defined at the Parent HR Org
584 if not overridden at the HR Org Level*/
585 if l_uwv_org_id =-1 then
586 l_uwv_org_id := v_org_uwv_provider.provider;
587 --hr_utility.set_location(' Parent HR Org l_uwv_org_id'||l_uwv_org_id,330);
588 end if;
589 end if;
590 /*Check If the UWV Provider assigned is also the Primary
591 Quit Searching the hierarchy*/
592 if v_org_uwv_provider.p_flag='Y' then
593 l_uwv_found:=TRUE;
594 l_uwv_org_id := v_org_uwv_provider.provider;
595 --hr_utility.set_location(' Primary UWV l_uwv_org_id'||l_uwv_org_id||' @ '||l_organization_id,340);
596 end if;
597
598 end if;
599 close org_uwv_provider;
600 END LOOP;
601 close org_hierarchy;
602 --hr_utility.set_location(' UWV From Hierarchy l_uwv_org_id'||l_uwv_org_id,350);
603
604 END IF;
605 --hr_utility.set_location(' UWV ID -> l_uwv_org_id'||l_uwv_org_id,360);
606 RETURN l_uwv_org_id;
607 EXCEPTION
608 when others then
609 hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
610 IF org_hierarchy%ISOPEN THEN
611 CLOSE org_hierarchy;
612 END IF;
613 RETURN -1;
614 END Get_SI_Provider_Info;
615
616 --
617 -- Service function which returns the SI Provider information for the given assignment.
618 -- Its an Over Loaded Function ,fetches the Org Id and then calls the
619 -- other Over Loaded Function to Tree Walk and Fetch the provider info.
620 FUNCTION Get_SI_Provider_Info
621 (p_assignment_id NUMBER,p_si_type VARCHAR2) RETURN NUMBER IS
622
623 --Determine the Organization Id of the Employees Assignment
624 CURSOR get_org_id(p_assignment_id number) is
625 select paa.organization_id
626 from per_all_assignments_f paa,fnd_sessions ses
627 where paa.assignment_id = p_assignment_id and
628 ses.effective_date between paa.effective_start_date and paa.effective_end_date and
629 session_id = userenv('sessionid');
630
631 l_org_id per_all_assignments_f.organization_id%TYPE;
632 l_provider_id hr_organization_units.organization_id%TYPE;
633
634 BEGIN
635
636 OPEN get_org_id(p_assignment_id);
637 FETCH get_org_id into l_org_id;
638 CLOSE get_org_id;
639
640 l_provider_id:=Get_SI_Provider_Info(l_org_id,p_si_type,p_assignment_id);
641
642 RETURN l_provider_id;
643
644 END Get_SI_Provider_Info;
645
646 --
647 --
648 -- Service function to see if uwv organization is assigned to
649 -- any hr organization in the hierarchy.
650 --
651 FUNCTION check_uwv_org_in_hierarchy
652 (p_uwv_org_id NUMBER,p_organization_id NUMBER) RETURN VARCHAR2 IS
653 --
654 --
655 -- Cursor to see if the organization belongs to the current
656 -- named hierarchy.
657 --
658 CURSOR org_uwv_provider
659 (l_uwv_org_id in hr_organization_units.organization_id%type,
660 l_org_id in hr_organization_units.organization_id%type) IS
661 select
662 e.org_information4 provider
663 from
664 hr_organization_information e
665 where
666 e.organization_id=l_org_id and
667 e.org_information_context = 'NL_SIP' and
668 e.org_information4 =l_uwv_org_id ;
669 --
670 --
671 -- Local Variables.
672 --
673 l_uwv_found varchar2(1) := 'N';
674 l_proc varchar2(72) := g_package || '.check_uwv_org_in_hierarchy';
675 l_level number;
676 l_organization_id hr_organization_units.organization_id%TYPE;
677 v_org_uwv_provider org_uwv_provider%ROWTYPE;
678 BEGIN
679 l_uwv_found := 'N';
680 IF org_hierarchy%ISOPEN THEN
681 CLOSE org_hierarchy;
682 END IF;
683 /*Start looking for the UWV Provider beginning from the HR Org
684 of Employee */
685
686 OPEN org_hierarchy(p_organization_id);
687 LOOP
688 FETCH org_hierarchy into l_organization_id,l_level;
689 exit when org_hierarchy%NOTFOUND or l_uwv_found ='Y' ;
690 --hr_utility.set_location(' l_organization_id'||l_organization_id||' level '||l_level,300);
691 --Fetch UWV Provider assigned to the HR Organization
692 open org_uwv_provider(p_uwv_org_id,l_organization_id);
693 FETCH org_uwv_provider into v_org_uwv_provider;
694 IF org_uwv_provider%FOUND THEN
695 l_uwv_found := 'Y';
696 END IF;
697 CLOSE org_uwv_provider;
698 END LOOP;
699 CLOSE org_hierarchy;
700 RETURN l_uwv_found;
701 EXCEPTION
702 when others then
703 hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
704 IF org_hierarchy%ISOPEN THEN
705 CLOSE org_hierarchy;
706 END IF;
707 RETURN l_uwv_found;
708 END check_uwv_org_in_hierarchy;
709 --
710 -- Service function to return the Info Id from the Assignment Extra Information
711 -- to support AMI Enhancement
712 -- Returns the ID for the Specified SI type defined,if not defined looks for a AMI
713 -- record and returns it.
714 FUNCTION Get_Asg_SII_Info_ID
715 (p_assignment_id NUMBER,p_si_type VARCHAR2) RETURN NUMBER IS
716 --
717 -- Cursor which fetches Social Insurance Provider overridden at the Assignment Level
718 -- ordering the records by the si class order (A record for a Individual SI type would be
719 -- ordered higher than a AMI record).
720 CURSOR asg_provider
721 (l_assgn_id NUMBER,l_si_type varchar2) IS
722 select pae.assignment_extra_info_id,
723 decode(pae.aei_information3,'AMI',0,1) si_class_order
724 from per_assignment_extra_info pae
725 ,fnd_sessions s
726 where assignment_id = l_assgn_id
727 and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
728 'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
729 pae.AEI_INFORMATION3 = DECODE(l_si_type,'WEWA','AMI','WEWE','AMI','WAOD','AMI','WAOB','AMI',
730 'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
731 'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
732 and s.effective_date between
733 fnd_date.canonical_to_date(pae.aei_information1)
734 and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date)
735 and session_id = userenv('sessionid')
736 order by si_class_order desc;
737 v_asg_provider asg_provider%ROWTYPE;
738 BEGIN
739 OPEN asg_provider(p_assignment_id,p_si_type);
740 FETCH asg_provider INTO v_asg_provider;
741 CLOSE asg_provider;
742
743 RETURN v_asg_provider.assignment_extra_info_id;
744 END Get_Asg_SII_Info_ID;
745
746 -- Function which returns average days per month for the given organization
747 -- If the value is not specified for the given organization it performs the tree walk.
748
749 FUNCTION Get_Avg_Days_Per_Month
750 (p_assignment_id NUMBER) RETURN NUMBER IS
751 --
752 --Determine the Organization Id of the Employees Assignment
753 --
754 CURSOR get_org_id(p_assignment_id number) is
755 select paa.organization_id
756 from per_all_assignments_f paa,fnd_sessions ses
757 where paa.assignment_id = p_assignment_id and
758 ses.effective_date between paa.effective_start_date and paa.effective_end_date and
759 session_id = userenv('sessionid');
760
761 --
762 --Cursor which fetches Tax Information for the given HR Organization
763 --
764 CURSOR Avg_Days_Per_Month
765 (l_org_id in hr_organization_units.organization_id%type) IS
766 select
767 e.org_information_id,
768 e.org_information5 Avg_days_Per_Month
769 from
770 hr_organization_information e
771 where
772 e.organization_id=l_org_id and
773 e.org_information_context= 'NL_ORG_INFORMATION'
774 and e.org_information5 IS NOT NULL;
775 --
776 --Local Variables
777 --
778 l_avg_days Number;
779 v_avg_days Avg_Days_Per_Month%ROWTYPE;
780 l_org_id per_all_assignments_f.organization_id%TYPE;
781 l_organization_id hr_organization_units.organization_id%TYPE;
782 l_level number;
783
784 BEGIN
785 --
786 --Determine the Organization Id of the Employees Assignment
787 --
788 OPEN get_org_id(p_assignment_id);
789 FETCH get_org_id into l_org_id;
790 CLOSE get_org_id;
791
792 l_avg_days := Null;
793 --
794 --Check whether the Average Days Per Month is specified for the Organization
795 --
796 OPEN Avg_Days_Per_Month(l_org_id);
797 Fetch Avg_Days_Per_Month into v_avg_days;
798 If Avg_Days_Per_Month%FOUND and v_avg_days.Avg_days_Per_Month is not null then
799 l_avg_days:= v_avg_days.Avg_days_Per_Month;
800 End if;
801 Close Avg_Days_Per_Month;
802
803 --If the Average days per month is not specified tree walk to find the organization with
804 --the same value defined.
805 --
806 IF l_avg_days IS NULL THEN
807
808 if org_hierarchy%ISOPEN then
809 CLOSE org_hierarchy;
810 END IF;
811
812 OPEN org_hierarchy(l_org_id);
813 LOOP
814 FETCH org_hierarchy into l_organization_id,l_level;
815 exit when org_hierarchy%NOTFOUND or l_avg_days IS NOT NULL;
816 --
817 --Fetch the avg days per month for the given organization
818 --
819 open Avg_Days_Per_Month(l_organization_id);
820 FETCH Avg_Days_Per_Month into v_avg_days;
821 if Avg_Days_Per_Month%found and v_avg_days.Avg_days_Per_Month is not null then
822 l_avg_days:= v_avg_days.Avg_days_Per_Month;
823 end if;
824 close Avg_Days_Per_Month;
825 END LOOP;
826 close org_hierarchy;
827
828 END IF;
829 --If the value for average days per month is not specified anywhere up in the hierarchy default it to 30
830 IF l_avg_days IS NULL THEN
831 l_avg_days:=30;
832 END IF;
833
834 RETURN l_avg_days;
835 EXCEPTION
836 when others then
837 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
838 IF org_hierarchy%ISOPEN THEN
839 CLOSE org_hierarchy;
840 END IF;
841 RETURN 0;
842 END Get_Avg_Days_Per_Month;
843 --
844 --Function which returns the tax organization for the given organization by traversing the org hierarchy
845 --
846 Function Get_Tax_Org_Id(p_org_structure_version_id NUMBER,p_organization_id NUMBER) RETURN NUMBER IS
847 --
848 -- Cursor which fetches Tax Information for the given HR Organization
849 --
850 CURSOR tax_organization
851 (l_org_id in hr_organization_units.organization_id%type) IS
852 select
853 e.org_information_id,
854 e.org_information4 tax_information
855 from
856 hr_organization_information e
857 where
858 e.organization_id=l_org_id and
859 e.org_information_context= 'NL_ORG_INFORMATION'
860 and e.org_information3 IS NOT NULL
861 and e.org_information4 IS NOT NULL;
862
863
864 --
865 -- Cursor which fetches Tax Organization list for the given HR Organization
866 --
867 CURSOR tax_org_hierarchy(l_org_struct_version_id in per_org_structure_versions.org_structure_version_id%type,
868 l_org_id in hr_organization_units.organization_id%type) IS
869 SELECT tax_org_id,lev from hr_organization_information e,(
870 SELECT l_org_id tax_org_id,0 lev from dual
871 UNION
872 SELECT distinct organization_id_parent
873 ,level
874 FROM (
875 SELECT distinct organization_id_parent, organization_id_child
876 FROM per_org_structure_elements pose
877 where pose.org_structure_version_id = l_org_struct_version_id)
878 START WITH organization_id_child = l_org_id
879 CONNECT BY PRIOR organization_id_parent = organization_id_child)
880 where
881 e.organization_id=tax_org_id and
882 e.org_information_context= 'NL_ORG_INFORMATION'
883 and e.org_information3 IS NOT NULL
884 and e.org_information4 IS NOT NULL
885 ORDER BY lev;
886
887 v_tax_org tax_organization%ROWTYPE;
888 l_level number;
889 l_tax_org_id hr_organization_units.organization_id%TYPE;
890
891 BEGIN
892 l_tax_org_id := NULL;
893
894 OPEN tax_organization(p_organization_id);
895 Fetch tax_organization into v_tax_org;
896 If tax_organization%FOUND and v_tax_org.tax_information is not null then
897 l_tax_org_id:= p_organization_id;
898 End if;
899 Close tax_organization;
900
901 if tax_org_hierarchy%ISOPEN then
902 CLOSE tax_org_hierarchy;
903 end if;
904
905 /*Fetch the tax organization list with tax information defined beginning from the HR Org */
906 if l_tax_org_id IS NULL then
907 OPEN tax_org_hierarchy(p_org_structure_version_id,p_organization_id);
908 FETCH tax_org_hierarchy into l_tax_org_id,l_level;
909 close tax_org_hierarchy;
910 end if;
911
912 RETURN l_tax_org_id;
913
914 EXCEPTION
915 when others then
916 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
917 IF tax_org_hierarchy%ISOPEN THEN
918 CLOSE tax_org_hierarchy;
919 END IF;
920 RETURN null;
921 END Get_Tax_Org_Id;
922
923
924 FUNCTION Get_Working_hours_Per_Week
925 (p_org_id NUMBER) RETURN NUMBER IS
926 --
927 --Determine the Organization Id of the Employees Assignment
928 --
929 CURSOR get_org_id(p_assignment_id number) is
930 select paa.organization_id
931 from per_all_assignments_f paa,fnd_sessions ses
932 where paa.assignment_id = p_assignment_id and
933 ses.effective_date between paa.effective_start_date and paa.effective_end_date and
934 session_id = userenv('sessionid');
935
936 --
937 --
938
939 CURSOR Working_hours_Per_Week
940 (l_org_id in hr_organization_units.organization_id%type) IS
941 select
942 e.org_information_id,
943 e.org_information7 working_hours
944 from
945 hr_organization_information e
946 where
947 e.organization_id=l_org_id and
948 e.org_information_context= 'NL_ORG_INFORMATION'
949 and e.org_information7 IS NOT NULL;
950 --
951 --Local Variables
952 --
953 l_working_hrs Number;
954 v_hrs_per_week Working_hours_Per_Week%ROWTYPE;
955 l_org_id per_all_assignments_f.organization_id%TYPE;
956 l_organization_id hr_organization_units.organization_id%TYPE;
957 l_level number;
958
959 BEGIN
960 --
961 --
962
963 l_working_hrs:= Null;
964 --
965 --Check whether the Working_hours_Per_Week is specified for the Organization
966 --
967 OPEN Working_hours_Per_Week(p_org_id);
968 Fetch Working_hours_Per_Week into v_hrs_per_week;
969 If Working_hours_Per_Week%FOUND and v_hrs_per_week.working_hours is not null then
970 l_working_hrs:= v_hrs_per_week.working_hours;
971 End if;
972 Close Working_hours_Per_Week;
973
974 hr_utility.trace('l_working_hours is : '||l_working_hrs);
975
976 --If the Working_hours_Per_Week is not specified tree walk to find the organization with
977 --the same value defined.
978 --
979 IF l_working_hrs IS NULL THEN
980
981 if org_hierarchy%ISOPEN then
982 CLOSE org_hierarchy;
983 END IF;
984
985 OPEN org_hierarchy(p_org_id);
986 LOOP
987 FETCH org_hierarchy into l_organization_id,l_level;
988 exit when org_hierarchy%NOTFOUND or l_working_hrs IS NOT NULL;
989 --
990 --
991 OPEN Working_hours_Per_Week(l_organization_id);
992 Fetch Working_hours_Per_Week into v_hrs_per_week;
993 If Working_hours_Per_Week%FOUND and v_hrs_per_week.working_hours is not null then
994 l_working_hrs:= v_hrs_per_week.working_hours;
995 hr_utility.trace('l_working_hours from hierarchy is : '||l_working_hrs);
996 end if;
997 Close Working_hours_Per_Week;
998 END LOOP;
999 close org_hierarchy;
1000
1001 END IF;
1002
1003
1004 RETURN l_working_hrs;
1005 EXCEPTION
1006 when others then
1007 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1008 IF org_hierarchy%ISOPEN THEN
1009 CLOSE org_hierarchy;
1010 END IF;
1011 RETURN 0;
1012 END Get_Working_hours_Per_Week;
1013
1014 -- Function which returns part time percentage method for the given organization
1015 -- If the value is not specified for the given organization it performs the tree walk.
1016
1017 FUNCTION Get_Part_Time_Perc_Method
1018 (p_assignment_id NUMBER) RETURN NUMBER IS
1019 --
1020 --Determine the Organization Id of the Employees Assignment
1021 --
1022 CURSOR get_org_id(p_assignment_id number) is
1023 select paa.organization_id
1024 from per_all_assignments_f paa,fnd_sessions ses
1025 where paa.assignment_id = p_assignment_id and
1026 ses.effective_date between paa.effective_start_date and paa.effective_end_date and
1027 session_id = userenv('sessionid');
1028
1029 --
1030 --Cursor which fetches Part Time Percetage Method for the given HR Organization
1031 --
1032 CURSOR Part_Time_Percentage_Method
1033 (l_org_id in hr_organization_units.organization_id%type) IS
1034 select
1035 e.org_information_id,
1036 e.org_information8 Part_Time_Percentage_Method
1037 from
1038 hr_organization_information e
1039 where
1040 e.organization_id=l_org_id and
1041 e.org_information_context= 'NL_ORG_INFORMATION'
1042 and e.org_information8 IS NOT NULL;
1043 --
1044 --Local Variables
1045 --
1046 l_part_time_per Number;
1047 v_part_time_per Part_Time_Percentage_Method%ROWTYPE;
1048 l_org_id per_all_assignments_f.organization_id%TYPE;
1049 l_organization_id hr_organization_units.organization_id%TYPE;
1050 l_level number;
1051
1052 BEGIN
1053 --
1054 --Determine the Organization Id of the Employees Assignment
1055 --
1056 OPEN get_org_id(p_assignment_id);
1057 FETCH get_org_id into l_org_id;
1058 CLOSE get_org_id;
1059
1060 l_part_time_per := Null;
1061 --
1062 --Check whether the Part Time Percetage Method is specified for the Organization
1063 --
1064 OPEN Part_Time_Percentage_Method(l_org_id);
1065 Fetch Part_Time_Percentage_Method into v_part_time_per;
1066 If Part_Time_Percentage_Method%FOUND and v_part_time_per.Part_Time_Percentage_Method is not null then
1067 l_part_time_per:= v_part_time_per.Part_Time_Percentage_Method;
1068 End if;
1069 Close Part_Time_Percentage_Method;
1070
1071 --If the Part Time Percetage Method is not specified tree walk to find the organization
1072 --with the same value defined.
1073 --
1074 IF l_part_time_per IS NULL THEN
1075
1076 if org_hierarchy%ISOPEN then
1077 CLOSE org_hierarchy;
1078 END IF;
1079
1080 OPEN org_hierarchy(l_org_id);
1081 LOOP
1082 FETCH org_hierarchy into l_organization_id,l_level;
1083 exit when org_hierarchy%NOTFOUND or l_part_time_per IS NOT NULL;
1084 --
1085 --Fetch the Part Time Percetage Method for the given organization
1086 --
1087 open Part_Time_Percentage_Method(l_organization_id);
1088 FETCH Part_Time_Percentage_Method into v_part_time_per;
1089 if Part_Time_Percentage_Method%found and v_part_time_per.Part_Time_Percentage_Method is not null then
1090 l_part_time_per:= v_part_time_per.Part_Time_Percentage_Method;
1091 end if;
1092 close Part_Time_Percentage_Method;
1093 END LOOP;
1094 close org_hierarchy;
1095
1096 END IF;
1097 IF l_part_time_per IS NULL THEN
1098 l_part_time_per:=1;
1099 END IF;
1100
1101 RETURN l_part_time_per;
1102 EXCEPTION
1103 when others then
1104 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1105 IF org_hierarchy%ISOPEN THEN
1106 CLOSE org_hierarchy;
1107 END IF;
1108 RETURN 1;
1109 END Get_Part_Time_Perc_Method;
1110
1111 -- Function which returns lunar 5-week month wage method for the given organization
1112 -- If the value is not specified for the given organization it performs the tree walk.
1113
1114 FUNCTION Get_Lunar_5_Week_Method
1115 (p_assignment_id NUMBER) RETURN NUMBER IS
1116 --
1117 --Determine the Organization Id of the Employees Assignment
1118 --
1119 CURSOR get_org_id(p_assignment_id number) is
1120 select paa.organization_id
1121 from per_all_assignments_f paa,fnd_sessions ses
1122 where paa.assignment_id = p_assignment_id and
1123 ses.effective_date between paa.effective_start_date and paa.effective_end_date and
1124 session_id = userenv('sessionid');
1125
1126 --
1127 --Cursor which fetches lunar 5 week month wage method for the given HR Organization
1128 --
1129 CURSOR Lunar_5_Week_Month_Wage_Method
1130 (l_org_id in hr_organization_units.organization_id%type) IS
1131 select
1132 e.org_information_id,
1133 e.org_information9 Lunar_5_Week_Month_Wage_Method
1134 from
1135 hr_organization_information e
1136 where
1137 e.organization_id=l_org_id and
1138 e.org_information_context= 'NL_ORG_INFORMATION'
1139 and e.org_information9 IS NOT NULL;
1140 --
1141 --Local Variables
1142 --
1143 l_lunar_method Number;
1144 v_lunar_method Lunar_5_Week_Month_Wage_Method%ROWTYPE;
1145 l_org_id per_all_assignments_f.organization_id%TYPE;
1146 l_organization_id hr_organization_units.organization_id%TYPE;
1147 l_level number;
1148
1149 BEGIN
1150 --
1151 --Determine the Organization Id of the Employees Assignment
1152 --
1153 OPEN get_org_id(p_assignment_id);
1154 FETCH get_org_id into l_org_id;
1155 CLOSE get_org_id;
1156
1157 l_lunar_method := Null;
1158 --
1159 --Check whether the lunar 5-week month wage method is specified for the Organization
1160 --
1161 OPEN Lunar_5_Week_Month_Wage_Method(l_org_id);
1162 Fetch Lunar_5_Week_Month_Wage_Method into v_lunar_method;
1163 If Lunar_5_Week_Month_Wage_Method%FOUND and v_lunar_method.Lunar_5_Week_Month_Wage_Method is not null then
1164 l_lunar_method:= v_lunar_method.Lunar_5_Week_Month_Wage_Method;
1165 End if;
1166 Close Lunar_5_Week_Month_Wage_Method;
1167
1168 --If the lunar 5 week month wage method is not specified tree walk to find the organization with
1169 --the same value defined.
1170 --
1171 IF l_lunar_method IS NULL THEN
1172
1173 if org_hierarchy%ISOPEN then
1174 CLOSE org_hierarchy;
1175 END IF;
1176
1177 OPEN org_hierarchy(l_org_id);
1178 LOOP
1179 FETCH org_hierarchy into l_organization_id,l_level;
1180 exit when org_hierarchy%NOTFOUND or l_lunar_method IS NOT NULL;
1181 --
1182 --Fetch the Part Time Percetage Method for the given organization
1183 --
1184 open Lunar_5_Week_Month_Wage_Method(l_organization_id);
1185 FETCH Lunar_5_Week_Month_Wage_Method into v_lunar_method;
1186 if Lunar_5_Week_Month_Wage_Method%found and v_lunar_method.Lunar_5_Week_Month_Wage_Method is not null then
1187 l_lunar_method:= v_lunar_method.Lunar_5_Week_Month_Wage_Method;
1188 end if;
1189 close Lunar_5_Week_Month_Wage_Method;
1190 END LOOP;
1191 close org_hierarchy;
1192
1193 END IF;
1194 IF l_lunar_method IS NULL THEN
1195 l_lunar_method:=0;
1196 END IF;
1197 RETURN l_lunar_method;
1198 EXCEPTION
1199 when others then
1200 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1201 IF org_hierarchy%ISOPEN THEN
1202 CLOSE org_hierarchy;
1203 END IF;
1204 RETURN 0;
1205 END Get_Lunar_5_Week_Method;
1206
1207 -- Start NL_Proration
1208
1209 FUNCTION Get_Proration_Tax_Table
1210 (p_assignment_id number) RETURN Varchar2 IS
1211 --
1212 --Determine the Organization Id of the Employees Assignment
1213 --
1214 CURSOR get_org_id(p_assignment_id number) is
1215 select paa.organization_id
1216 from per_all_assignments_f paa,fnd_sessions ses
1217 where paa.assignment_id = p_assignment_id and
1218 ses.effective_date between paa.effective_start_date
1219 and paa.effective_end_date and
1220 session_id = userenv('sessionid');
1221
1222 --
1223 --
1224
1225 CURSOR cur_Pro_Tax_Table
1226 (l_org_id in hr_organization_units.organization_id%type) IS
1227 select
1228 e.org_information_id,
1229 e.org_information10 Proration_Tax_Table
1230 from
1231 hr_organization_information e
1232 where
1233 e.organization_id=l_org_id and
1234 e.org_information_context= 'NL_ORG_INFORMATION'
1235 and e.org_information10 IS NOT NULL;
1236 --
1237 --Local Variables
1238 --
1239 l_Pro_Tax_Table hr_organization_information.ORG_INFORMATION10%TYPE;
1240 v_Pro_Tax_Table cur_Pro_Tax_Table%ROWTYPE;
1241 l_org_id per_all_assignments_f.organization_id%TYPE;
1242 l_organization_id hr_organization_units.organization_id%TYPE;
1243 l_level number;
1244
1245 BEGIN
1246 --
1247 --
1248 --
1249 --Determine the Organization Id of the Employees Assignment
1250 --
1251 OPEN get_org_id(p_assignment_id);
1252 FETCH get_org_id into l_org_id;
1253 CLOSE get_org_id;
1254
1255 l_Pro_Tax_Table:= Null;
1256 --
1257 --Check whether the Period_type is specified for the Organization
1258 --
1259 OPEN cur_Pro_Tax_Table (l_org_id);
1260 Fetch cur_Pro_Tax_Table into v_Pro_Tax_Table;
1261 If cur_Pro_Tax_Table%FOUND and
1262 v_Pro_Tax_Table.Proration_Tax_Table is not null then
1263 l_Pro_Tax_Table:= v_Pro_Tax_Table.Proration_Tax_Table;
1264 End if;
1265 Close cur_Pro_Tax_Table;
1266
1267 hr_utility.trace('l_Pro_Tax_Table is : '||l_Pro_Tax_Table);
1268
1269 --If the Working_hours_Per_Week is not specified tree walk
1270 --to find the organization with
1271 --the same value defined.
1272 --
1273 IF l_Pro_Tax_Table IS NULL THEN
1274
1275 if org_hierarchy%ISOPEN then
1276 CLOSE org_hierarchy;
1277 END IF;
1278
1279 OPEN org_hierarchy(l_org_id);
1280 LOOP
1281 FETCH org_hierarchy into l_organization_id,l_level;
1282 exit when org_hierarchy%NOTFOUND or l_Pro_Tax_Table IS NOT NULL;
1283 --
1284 --
1285 OPEN Cur_Pro_Tax_Table(l_organization_id);
1286 Fetch Cur_Pro_Tax_Table into v_Pro_Tax_Table;
1287 If Cur_Pro_Tax_Table%FOUND and
1288 v_Pro_Tax_Table.Proration_Tax_Table is not null then
1289 l_Pro_Tax_Table:= v_Pro_Tax_Table.Proration_Tax_Table;
1290 end if;
1291 Close Cur_Pro_Tax_Table;
1292 END LOOP;
1293 close org_hierarchy;
1294
1295 END IF;
1296 --If the value for Proration Tax Table is not specified anywhere
1297 --up in the hierarchy default it to 1
1298 IF l_Pro_Tax_Table IS NULL THEN
1299 l_Pro_Tax_Table :=1;
1300 END IF;
1301
1302 RETURN l_Pro_Tax_Table;
1303 EXCEPTION
1304 when others then
1305 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1306 IF org_hierarchy%ISOPEN THEN
1307 CLOSE org_hierarchy;
1308 END IF;
1309 RETURN '0';
1310 END Get_Proration_Tax_Table;
1311
1312
1313 -- End NL_Proration
1314
1315 -- Service function which returns the SI Provider information for the given organization.
1316 -- It performs tree walk if SI information is not defined for the given organization.
1317 --
1318
1319 FUNCTION Get_ER_SI_Prov_HR_Org_ID
1320 (p_organization_id NUMBER,p_si_type VARCHAR2,p_assignment_id NUMBER) RETURN NUMBER IS
1321
1322 l_proc varchar2(72) := g_package || '.Get_SI_Provider_Info';
1323 l_provider_info hr_organization_units.organization_id%type;
1324 l_org_id hr_organization_units.organization_id%type;
1325
1326
1327 --
1328 -- Cursor which fetches Social Insurance Provider overridden at the Assignment Level
1329 -- ordering the records by the si class order (A record for a Individual SI type would be
1330 -- ordered higher than a AMI record).
1331 CURSOR asg_provider
1332 (l_org_id in hr_organization_units.organization_id%type,l_si_type varchar2,l_assgn_id NUMBER) IS
1333 select pae.aei_information8 provider,
1334 decode(pae.aei_information3,'AMI',0,1) si_class_order
1335 from per_assignment_extra_info pae
1336 ,fnd_sessions s
1337 where assignment_id = l_assgn_id
1338 and (pae.aei_information3=decode (l_si_type,'WEWE','WW','WEWA','WW',
1339 'WAOB','WAO','WAOD','WAO','PRIVATE_HEALTH','ZFW',l_si_type) or
1340 pae.aei_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI',
1341 'WAOB','AMI','WAOD','AMI',
1342 'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
1343 'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type))
1344 and s.effective_date between
1345 fnd_date.canonical_to_date(pae.aei_information1)
1346 and nvl(fnd_date.canonical_to_date(pae.aei_information2),s.effective_date) AND
1347 s.session_id=userenv('sessionid')
1348 order by si_class_order desc;
1349
1350 --
1351 -- Cursor which fetches Social Insurance Provider for the given Hr Organization
1352 -- and which offers SI type ordering the records first by the Primary provider Flag
1353 -- and then by the si class order(A record for a Individual SI type would be
1354 -- ordered higher than a AMI record).
1355 CURSOR org_uwv_provider
1356 (l_org_id in hr_organization_units.organization_id%type,l_si_type varchar2) IS
1357 select
1358 e.org_information4 provider,nvl(e.org_information7,'N') p_flag,
1359 decode(e.org_information3,'AMI',0,1) si_class_order
1360 from
1361 hr_organization_information e
1362 ,fnd_sessions s
1363 where
1364 e.organization_id=l_org_id and
1365 e.org_information_context = 'NL_SIP' and
1366 (e.org_information3=DECODE(l_si_type,'WEWE','WW','WEWA','WW','WAOB','WAO','WAOD','WAO',
1367 'PRIVATE_HEALTH','ZFW',l_si_type) or
1368 e.org_information3 = DECODE(l_si_type,'WEWE','AMI','WEWA','AMI','WAOB','AMI','WAOD','AMI',
1369 'ZFW','AMI','PRIVATE_HEALTH','AMI','ZW','AMI',
1370 'ZVW','AMI','WGA','AMI','IVA','AMI','UFO','AMI',l_si_type)) and
1371 s.effective_date between
1372 fnd_date.canonical_to_date(e.org_INFORMATION1)
1373 and nvl(fnd_date.canonical_to_date(e.org_INFORMATION2),s.effective_date) AND
1374 s.session_id=userenv('sessionid')
1375 order by p_flag desc,si_class_order desc;
1376
1377 v_asg_provider asg_provider%ROWTYPE;
1378 v_org_uwv_provider org_uwv_provider%ROWTYPE;
1379 l_level number;
1380 l_organization_id hr_organization_units.organization_id%TYPE;
1381 l_uwv_found boolean := false;
1382 l_uwv_org_id hr_organization_units.organization_id%TYPE;
1383 l_er_org_id hr_organization_units.organization_id%TYPE;
1384 BEGIN
1385 /* Fetch Override Ins Provider at the Asg Level*/
1386 OPEN asg_provider(p_organization_id,p_si_type,p_assignment_id);
1387 FETCH asg_provider INTO v_asg_provider;
1388 CLOSE asg_provider;
1389
1390
1391 /* If Ins Provider at the Asg Level is specified*/
1392 IF v_asg_provider.provider IS NOT NULL THEN
1393 l_uwv_org_id := v_asg_provider.provider;
1394 l_er_org_id := p_organization_id;
1395 --hr_utility.set_location('Asg Level UWV Prov l_uwv_org_id'||l_uwv_org_id,100);
1396 ELSE
1397 /* If Ins Provider at the Asg Level is not specified
1398 tree walk to find the Primary Insurance Provider at the level */
1399 --hr_utility.set_location('Calling Get_SI_Org_Id',200);
1400
1401 l_uwv_found := FALSE;
1402 l_uwv_org_id := -1;
1403 l_er_org_id := -1;
1404 if org_hierarchy%ISOPEN then
1405 CLOSE org_hierarchy;
1406 END IF;
1407 /*Start looking for the UWV Provider beginning from the HR Org
1408 of Employee */
1409
1410 OPEN org_hierarchy(p_organization_id);
1411 LOOP
1412 FETCH org_hierarchy into l_organization_id,l_level;
1413 exit when org_hierarchy%NOTFOUND or l_uwv_found =TRUE ;
1414 --hr_utility.set_location(' l_organization_id'||l_organization_id||' level '||l_level,300);
1415 --Fetch UWV Provider assigned to the HR Organization
1416 open org_uwv_provider(l_organization_id,p_si_type);
1417 FETCH org_uwv_provider into v_org_uwv_provider;
1418 if org_uwv_provider%found then
1419 --hr_utility.set_location(' l_organization_id'||l_organization_id||' p_organization_id '||p_organization_id,310);
1420 if l_organization_id =p_organization_id then
1421 /*Assign the UWV Provider defined at the HR Org
1422 But continue further to see if any Primary
1423 UWV exists up in the hierarchy*/
1424 l_uwv_org_id := v_org_uwv_provider.provider;
1425 l_er_org_id := l_organization_id;
1426 --hr_utility.set_location(' Assign -HR Org l_uwv_org_id'||l_uwv_org_id,320);
1427 else
1428 /*Assign the UWV Provider defined at the Parent HR Org
1429 if not overridden at the HR Org Level*/
1430 if l_uwv_org_id =-1 then
1431 l_uwv_org_id := v_org_uwv_provider.provider;
1432 l_er_org_id := l_organization_id;
1433 --hr_utility.set_location(' Parent HR Org l_uwv_org_id'||l_uwv_org_id,330);
1434 end if;
1435 end if;
1436 /*Check If the UWV Provider assigned is also the Primary
1437 Quit Searching the hierarchy*/
1438 if v_org_uwv_provider.p_flag='Y' then
1439 l_uwv_found:=TRUE;
1440 l_uwv_org_id := v_org_uwv_provider.provider;
1441 l_er_org_id := l_organization_id;
1442 --hr_utility.set_location(' Primary UWV l_uwv_org_id'||l_uwv_org_id||' @ '||l_organization_id,340);
1443 end if;
1444
1445 end if;
1446 close org_uwv_provider;
1447 END LOOP;
1448 close org_hierarchy;
1449 --hr_utility.set_location(' UWV From Hierarchy l_uwv_org_id'||l_uwv_org_id,350);
1450
1451 END IF;
1452 hr_utility.set_location(' UWV ID -> l_uwv_org_id'||l_uwv_org_id,360);
1453 hr_utility.set_location('ER UWV ID -> l_er_org_id'||l_er_org_id,360);
1454 RETURN l_er_org_id;
1455 EXCEPTION
1456 when others then
1457 hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1458 IF org_hierarchy%ISOPEN THEN
1459 CLOSE org_hierarchy;
1460 END IF;
1461 RETURN -1;
1462 END Get_ER_SI_Prov_HR_Org_ID;
1463
1464
1465 -- To get all the employers for given Org Struct Version ID
1466 function Get_Employers_List(p_Org_Struct_Version_Id in number,
1467 p_top_org_id in number,
1468 p_sub_emp in varchar2)
1469 return varchar2 is
1470
1471 cursor c_all_emp is
1472 select pose.organization_id_child employer
1473 from per_org_structure_elements pose,hr_organization_information e
1474 where pose.org_structure_version_id = P_Org_Struct_Version_Id
1475 and e.organization_id=pose.organization_id_child
1476 and ((e.org_information_context= 'NL_ORG_INFORMATION'
1477 and e.org_information3 IS NOT NULL
1478 and e.org_information4 IS NOT NULL)
1479 or (e.org_information_context= 'NL_LE_TAX_DETAILS'
1480 and e.org_information1 IS NOT NULL
1481 and e.org_information2 IS NOT NULL))
1482 start with pose.organization_id_parent = p_top_org_id
1483 connect by prior pose.organization_id_child = pose.organization_id_parent
1484 union
1485 select to_number(p_top_org_id) employer from dual;
1486
1487 emp_list varchar2(1000);
1488
1489 begin
1490 if ((P_SUB_EMP='N') or (P_SUB_EMP is null)) then
1491 emp_list := p_top_org_id;
1492 else
1493 for i in c_all_emp
1494 loop
1495 if emp_list is not null then
1496 emp_list:=emp_list||','||i.employer;
1497 else
1498 emp_list:=i.employer;
1499 end if;
1500 end loop;
1501 END if;
1502 return '('||emp_list||')';
1503
1504 end Get_Employers_List;
1505
1506 -- Function which returns parental leave wage percentage for the given organization
1507 -- If the value is not specified for the given organization it performs the tree walk.
1508 FUNCTION Get_Parental_Leave_Wage_Perc(p_assignment_id NUMBER) RETURN NUMBER IS
1509 --
1510 --Determine the Organization Id of the Employees Assignment
1511 --
1512 CURSOR get_org_id(p_assignment_id number) is
1513 select paa.organization_id
1514 from per_all_assignments_f paa,fnd_sessions ses
1515 where paa.assignment_id = p_assignment_id and
1516 ses.effective_date between paa.effective_start_date and paa.effective_end_date and
1517 session_id = userenv('sessionid');
1518
1519 --
1520 --Cursor which fetches Tax Information for the given HR Organization
1521 --
1522 CURSOR csr_Parental_Leave_Perc
1523 (l_org_id in hr_organization_units.organization_id%type) IS
1524 select
1525 e.org_information_id,
1526 e.org_information11 Parental_Leave_Perc
1527 from
1528 hr_organization_information e
1529 where
1530 e.organization_id=l_org_id and
1531 e.org_information_context= 'NL_ORG_INFORMATION'
1532 and e.org_information11 IS NOT NULL;
1533 --
1534 --Local Variables
1535 --
1536 l_parental_leave_perc Number;
1537 v_csr_par_leave csr_Parental_Leave_Perc%ROWTYPE;
1538 l_org_id per_all_assignments_f.organization_id%TYPE;
1539 l_organization_id hr_organization_units.organization_id%TYPE;
1540 l_level number;
1541
1542 BEGIN
1543 --
1544 --Determine the Organization Id of the Employees Assignment
1545 --
1546 OPEN get_org_id(p_assignment_id);
1547 FETCH get_org_id into l_org_id;
1548 CLOSE get_org_id;
1549
1550 l_parental_leave_perc := Null;
1551 --
1552 --Check whether the Paid Parental Leave Percentage is specified for the Organization
1553 --
1554 OPEN csr_Parental_Leave_Perc(l_org_id);
1555 Fetch csr_Parental_Leave_Perc into v_csr_par_leave;
1556 If csr_Parental_Leave_Perc%FOUND and v_csr_par_leave.Parental_Leave_Perc is not null then
1557 l_parental_leave_perc:= v_csr_par_leave.Parental_Leave_Perc;
1558 End if;
1559 Close csr_Parental_Leave_Perc;
1560
1561 --If the Paid Parental Leave Percentage is not specified tree walk to find
1562 --the organization with the value defined.
1563 --
1564 IF l_parental_leave_perc IS NULL THEN
1565
1566 if org_hierarchy%ISOPEN then
1567 CLOSE org_hierarchy;
1568 END IF;
1569
1570 OPEN org_hierarchy(l_org_id);
1571 LOOP
1572 FETCH org_hierarchy into l_organization_id,l_level;
1573 exit when org_hierarchy%NOTFOUND or l_parental_leave_perc IS NOT NULL;
1574 --
1575 --Fetch the Parental Leave Percentage for the given organization
1576 --
1577 open csr_Parental_Leave_Perc(l_organization_id);
1578 FETCH csr_Parental_Leave_Perc into v_csr_par_leave;
1579 if csr_Parental_Leave_Perc%found and v_csr_par_leave.Parental_Leave_Perc is not null then
1580 l_parental_leave_perc:= v_csr_par_leave.Parental_Leave_Perc;
1581 end if;
1582 close csr_Parental_Leave_Perc;
1583 END LOOP;
1584 close org_hierarchy;
1585
1586 END IF;
1587 --If the value for Parental Leave Percentage is not specified anywhere up in the hierarchy default it to 0
1588 IF l_parental_leave_perc IS NULL THEN
1589 l_parental_leave_perc:=0;
1590 END IF;
1591
1592 RETURN l_parental_leave_perc;
1593 EXCEPTION
1594 when others then
1595 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1596 IF org_hierarchy%ISOPEN THEN
1597 CLOSE org_hierarchy;
1598 END IF;
1599 RETURN 0;
1600
1601 END Get_Parental_Leave_Wage_Perc;
1602
1603 -- Start CBS Reporting Frequency
1604 FUNCTION Get_Reporting_Frequency
1605 (p_org_id in hr_organization_units.organization_id%type) RETURN Varchar2 IS
1606 CURSOR csr_cbs_rep_freq
1607 (l_org_id in hr_organization_units.organization_id%type) IS
1608 select
1609 e.org_information_id,
1610 e.org_information15 cbs_reporting_frequency
1611 from
1612 hr_organization_information e
1613 where
1614 e.organization_id=l_org_id and
1615 e.org_information_context= 'NL_ORG_INFORMATION'
1616 and e.org_information15 IS NOT NULL;
1617 --
1618 --Local Variables
1619 --
1620 l_cbs_Rep_Freq hr_organization_information.ORG_INFORMATION15%TYPE;
1621 v_Rep_Freq_Table csr_cbs_rep_freq%ROWTYPE;
1622 l_org_id per_all_assignments_f.organization_id%TYPE;
1623 l_organization_id hr_organization_units.organization_id%TYPE;
1624 l_level number;
1625 BEGIN
1626 --
1627 --
1628 l_cbs_Rep_Freq := Null;
1629 --
1630 --Check whether the Reporting_Frequency is specified for the Organization
1631 --
1632 OPEN csr_cbs_rep_freq (p_org_id);
1633 Fetch csr_cbs_rep_freq into v_Rep_Freq_Table ;
1634 If csr_cbs_rep_freq %FOUND and
1635 v_Rep_Freq_Table.cbs_reporting_frequency is not null then
1636 l_cbs_Rep_Freq:= v_Rep_Freq_Table.cbs_reporting_frequency;
1637 End if;
1638 Close csr_cbs_rep_freq;
1639 hr_utility.trace('l_cbs_Rep_Freq is : '||l_cbs_Rep_Freq);
1640 --If the Working_hours_Per_Week is not specified tree walk
1641 --to find the organization with
1642 --the same value defined.
1643 --
1644 IF l_cbs_Rep_Freq IS NULL THEN
1645 if org_hierarchy%ISOPEN then
1646 CLOSE org_hierarchy;
1647 END IF;
1648 OPEN org_hierarchy(p_org_id);
1649 LOOP
1650 FETCH org_hierarchy into l_organization_id,l_level;
1651 exit when org_hierarchy%NOTFOUND or l_cbs_Rep_Freq IS NOT NULL;
1652 --
1653 --
1654 OPEN csr_cbs_rep_freq(l_organization_id);
1655 Fetch csr_cbs_rep_freq into v_Rep_Freq_Table;
1656 If csr_cbs_rep_freq%FOUND and
1657 v_Rep_Freq_Table.cbs_reporting_frequency is not null then
1658 l_cbs_Rep_Freq:= v_Rep_Freq_Table.cbs_reporting_frequency;
1659 end if;
1660 Close csr_cbs_rep_freq;
1661 END LOOP;
1662 close org_hierarchy;
1663 END IF;
1664 --If the value for Proration Tax Table is not specified anywhere
1665 --up in the hierarchy default it to 1
1666 IF l_cbs_Rep_Freq IS NULL THEN
1667 l_cbs_Rep_Freq :=1;
1668 END IF;
1669 RETURN l_cbs_Rep_Freq;
1670 EXCEPTION
1671 when others then
1672 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1673 IF org_hierarchy%ISOPEN THEN
1674 CLOSE org_hierarchy;
1675 END IF;
1676 RETURN '0';
1677 END Get_Reporting_Frequency;
1678 --
1679 --
1680 -- Start Customer Number
1681 --
1682 FUNCTION Get_customer_number
1683 (p_org_id in hr_organization_units.organization_id%type) RETURN Varchar2 IS
1684 CURSOR csr_customer_number
1685 (l_org_id in hr_organization_units.organization_id%type) IS
1686 select
1687 e.org_information_id,
1688 e.org_information16 cbs_cust_number
1689 from
1690 hr_organization_information e
1691 where
1692 e.organization_id=l_org_id and
1693 e.org_information_context= 'NL_ORG_INFORMATION'
1694 and e.org_information16 IS NOT NULL;
1695 --
1696 --Local Variables
1697 --
1698 l_cbs_cust_num hr_organization_information.ORG_INFORMATION16%TYPE;
1699 v_cust_num_table csr_customer_number%ROWTYPE;
1700 l_org_id per_all_assignments_f.organization_id%TYPE;
1701 l_organization_id hr_organization_units.organization_id%TYPE;
1702 l_level number;
1703 BEGIN
1704 --
1705 --
1706 l_cbs_cust_num := Null;
1707 --
1708 --Check whether the Reporting_Frequency is specified for the Organization
1709 --
1710 OPEN csr_customer_number (p_org_id);
1711 Fetch csr_customer_number into v_cust_num_table ;
1712 If csr_customer_number %FOUND and
1713 v_cust_num_table.cbs_cust_number is not null then
1714 l_cbs_cust_num:= v_cust_num_table.cbs_cust_number;
1715 End if;
1716 Close csr_customer_number;
1717 hr_utility.trace('l_cbs_cust_num is : '||l_cbs_cust_num);
1718 --If the Working_hours_Per_Week is not specified tree walk
1719 --to find the organization with
1720 --the same value defined.
1721 --
1722 IF l_cbs_cust_num IS NULL THEN
1723 if org_hierarchy%ISOPEN then
1724 CLOSE org_hierarchy;
1725 END IF;
1726 OPEN org_hierarchy(p_org_id);
1727 LOOP
1728 FETCH org_hierarchy into l_organization_id,l_level;
1729 exit when org_hierarchy%NOTFOUND or l_cbs_cust_num IS NOT NULL;
1730 --
1731 --
1732 OPEN csr_customer_number(l_organization_id);
1733 Fetch csr_customer_number into v_cust_num_table;
1734 If csr_customer_number%FOUND and
1735 v_cust_num_table.cbs_cust_number is not null then
1736 l_cbs_cust_num:= v_cust_num_table.cbs_cust_number;
1737 end if;
1738 Close csr_customer_number;
1739 END LOOP;
1740 close org_hierarchy;
1741 END IF;
1742 RETURN l_cbs_cust_num;
1743 EXCEPTION
1744 when others then
1745 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1746 IF org_hierarchy%ISOPEN THEN
1747 CLOSE org_hierarchy;
1748 END IF;
1749 RETURN 'Error';
1750 END Get_customer_number;
1751 --
1752 -- Start Company Unit
1753 --
1754 FUNCTION Get_company_unit
1755 (p_org_id in hr_organization_units.organization_id%type) RETURN Varchar2 IS
1756 CURSOR csr_company_unit
1757 (l_org_id in hr_organization_units.organization_id%type) IS
1758 select
1759 e.org_information_id,
1760 e.org_information18 cbs_company_unit
1761 from
1762 hr_organization_information e
1763 where
1764 e.organization_id=l_org_id and
1765 e.org_information_context= 'NL_ORG_INFORMATION'
1766 and e.org_information18 IS NOT NULL;
1767 --
1768 --Local Variables
1769 --
1770 l_cbs_company_unit hr_organization_information.ORG_INFORMATION18%TYPE;
1771 v_company_unit_table csr_company_unit%ROWTYPE;
1772 l_org_id per_all_assignments_f.organization_id%TYPE;
1773 l_organization_id hr_organization_units.organization_id%TYPE;
1774 l_level number;
1775 BEGIN
1776 --
1777 --
1778 l_cbs_company_unit := Null;
1779 --
1780 --Check whether the Reporting_Frequency is specified for the Organization
1781 --
1782 OPEN csr_company_unit (p_org_id);
1783 Fetch csr_company_unit into v_company_unit_table ;
1784 If csr_company_unit %FOUND and
1785 v_company_unit_table.cbs_company_unit is not null then
1786 l_cbs_company_unit:= v_company_unit_table.cbs_company_unit;
1787 End if;
1788 Close csr_company_unit;
1789 hr_utility.trace('l_cbs_company_unit is : '||l_cbs_company_unit);
1790 --If the Working_hours_Per_Week is not specified tree walk
1791 --to find the organization with
1792 --the same value defined.
1793 --
1794 IF l_cbs_company_unit IS NULL THEN
1795 if org_hierarchy%ISOPEN then
1796 CLOSE org_hierarchy;
1797 END IF;
1798 OPEN org_hierarchy(p_org_id);
1799 LOOP
1800 FETCH org_hierarchy into l_organization_id,l_level;
1801 exit when org_hierarchy%NOTFOUND or l_cbs_company_unit IS NOT NULL;
1802 --
1803 --
1804 OPEN csr_company_unit(l_organization_id);
1805 Fetch csr_company_unit into v_company_unit_table;
1806 If csr_company_unit%FOUND and
1807 v_company_unit_table.cbs_company_unit is not null then
1808 l_cbs_company_unit:= v_company_unit_table.cbs_company_unit;
1809 end if;
1810 Close csr_company_unit;
1811 END LOOP;
1812 close org_hierarchy;
1813 END IF;
1814 --If the value for Company Unit is not specified anywhere
1815 --up in the hierarchy default it to 0
1816 IF l_cbs_company_unit IS NULL THEN
1817 l_cbs_company_unit := 0;
1818 END IF;
1819 RETURN l_cbs_company_unit;
1820 EXCEPTION
1821 when others then
1822 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1823 IF org_hierarchy%ISOPEN THEN
1824 CLOSE org_hierarchy;
1825 END IF;
1826 RETURN '0';
1827 END Get_company_unit;
1828 --
1829 -- Start Get_Public_Sector_Org
1830 FUNCTION Get_Public_Sector_Org
1831 (p_org_id in hr_organization_units.organization_id%type) RETURN Varchar2 IS
1832 CURSOR csr_public_sector_info
1833 (l_org_id in hr_organization_units.organization_id%type) IS
1834 select
1835 e.org_information_id,
1836 e.org_information17 cbs_public_sector_org
1837 from
1838 hr_organization_information e
1839 where
1840 e.organization_id=l_org_id and
1841 e.org_information_context= 'NL_ORG_INFORMATION'
1842 and e.org_information17 IS NOT NULL;
1843 --
1844 --Local Variables
1845 --
1846 l_cbs_public_sector hr_organization_information.ORG_INFORMATION17%TYPE;
1847 v_public_sector_Table csr_public_sector_info%ROWTYPE;
1848 l_org_id per_all_assignments_f.organization_id%TYPE;
1849 l_organization_id hr_organization_units.organization_id%TYPE;
1850 l_level number;
1851 BEGIN
1852 --
1853 --
1854 l_cbs_public_sector := Null;
1855 --
1856 --Check whether the Public_Sector Information is specified for the Organization
1857 --
1858 OPEN csr_public_sector_info (p_org_id);
1859 Fetch csr_public_sector_info into v_public_sector_Table ;
1860 If csr_public_sector_info %FOUND and
1861 v_public_sector_Table.cbs_public_sector_org is not null then
1862 l_cbs_public_sector:= v_public_sector_Table.cbs_public_sector_org;
1863 End if;
1864 Close csr_public_sector_info;
1865 hr_utility.trace('l_cbs_Rep_Freq is : '||l_cbs_public_sector);
1866
1867 --
1868 IF l_cbs_public_sector IS NULL THEN
1869 if org_hierarchy%ISOPEN then
1870 CLOSE org_hierarchy;
1871 END IF;
1872 OPEN org_hierarchy(p_org_id);
1873 LOOP
1874 FETCH org_hierarchy into l_organization_id,l_level;
1875 exit when org_hierarchy%NOTFOUND or l_cbs_public_sector IS NOT NULL;
1876 --
1877 --
1878 OPEN csr_public_sector_info(l_organization_id);
1879 Fetch csr_public_sector_info into v_public_sector_Table;
1880 If csr_public_sector_info%FOUND and
1881 v_public_sector_Table.cbs_public_sector_org is not null then
1882 l_cbs_public_sector:= v_public_sector_Table.cbs_public_sector_org;
1883 end if;
1884 Close csr_public_sector_info;
1885 END LOOP;
1886 close org_hierarchy;
1887 END IF;
1888 --If the value for Proration Tax Table is not specified anywhere
1889 --up in the hierarchy default it to 1
1890 IF l_cbs_public_sector IS NULL THEN
1891 l_cbs_public_sector :=1;
1892 END IF;
1893 RETURN l_cbs_public_sector;
1894 EXCEPTION
1895 when others then
1896 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1897 IF org_hierarchy%ISOPEN THEN
1898 CLOSE org_hierarchy;
1899 END IF;
1900 RETURN '0';
1901 END Get_Public_Sector_Org;
1902
1903 -- Function which returns Full Sickness Wage Paid Indicator for the given organization
1904 -- If the value is not specified for the given organization it performs the tree walk.
1905 FUNCTION Get_Full_Sickness_Wage_Paid
1906 (p_org_id in hr_organization_units.organization_id%type) RETURN Varchar2 IS
1907 CURSOR csr_full_wage_paid
1908 (l_org_id in hr_organization_units.organization_id%type) IS
1909 select
1910 e.org_information_id,
1911 e.org_information19 full_sick_wage_paid
1912 from
1913 hr_organization_information e
1914 where
1915 e.organization_id=l_org_id and
1916 e.org_information_context= 'NL_ORG_INFORMATION'
1917 and e.org_information19 IS NOT NULL;
1918 --
1919 --Local Variables
1920 --
1921 l_full_wage_paid hr_organization_information.ORG_INFORMATION17%TYPE;
1922 v_full_sick_wage csr_full_wage_paid%ROWTYPE;
1923 l_org_id per_all_assignments_f.organization_id%TYPE;
1924 l_organization_id hr_organization_units.organization_id%TYPE;
1925 l_level number;
1926 BEGIN
1927 --
1928 --
1929 l_full_wage_paid := Null;
1930 --
1931 --Check whether the Full Sickness Wage Paid Indicator is specified for the Organization
1932 --
1933 OPEN csr_full_wage_paid (p_org_id);
1934 Fetch csr_full_wage_paid into v_full_sick_wage ;
1935 If csr_full_wage_paid %FOUND and
1936 v_full_sick_wage.full_sick_wage_paid is not null then
1937 l_full_wage_paid:= v_full_sick_wage.full_sick_wage_paid;
1938 End if;
1939 Close csr_full_wage_paid;
1940 hr_utility.trace('l_cbs_Rep_Freq is : '||l_full_wage_paid);
1941
1942 --
1943 IF l_full_wage_paid IS NULL THEN
1944 if org_hierarchy%ISOPEN then
1945 CLOSE org_hierarchy;
1946 END IF;
1947 OPEN org_hierarchy(p_org_id);
1948 LOOP
1949 FETCH org_hierarchy into l_organization_id,l_level;
1950 exit when org_hierarchy%NOTFOUND or l_full_wage_paid IS NOT NULL;
1951 --
1952 --
1953 OPEN csr_full_wage_paid(l_organization_id);
1954 Fetch csr_full_wage_paid into v_full_sick_wage;
1955 If csr_full_wage_paid%FOUND and
1956 v_full_sick_wage.full_sick_wage_paid is not null then
1957 l_full_wage_paid:= v_full_sick_wage.full_sick_wage_paid;
1958 end if;
1959 Close csr_full_wage_paid;
1960 END LOOP;
1961 close org_hierarchy;
1962 END IF;
1963
1964 RETURN l_full_wage_paid;
1965 EXCEPTION
1966 when others then
1967 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
1968 IF org_hierarchy%ISOPEN THEN
1969 CLOSE org_hierarchy;
1970 END IF;
1971 RETURN '0';
1972 END Get_Full_Sickness_Wage_Paid;
1973
1974 --
1975 FUNCTION Get_IZA_Weekly_Full_Hours
1976 (p_assignment_id in NUMBER ) RETURN Varchar2 IS
1977 -- Determine the Organization Id of the Employees Assignment
1978 --
1979 CURSOR get_org_id(p_assignment_id number) is
1980 select paa.organization_id
1981 from per_all_assignments_f paa,fnd_sessions ses
1982 where paa.assignment_id = p_assignment_id and
1983 ses.effective_date between paa.effective_start_date and paa.effective_end_date and
1984 session_id = userenv('sessionid');
1985 CURSOR csr_IZA_Weekly_Hours
1986 (l_org_id in hr_organization_units.organization_id%type) IS
1987 select
1988 e.org_information_id,
1989 e.org_information20 IZA_Weekly_Hours
1990 from
1991 hr_organization_information e
1992 where
1993 e.organization_id=l_org_id and
1994 e.org_information_context= 'NL_ORG_INFORMATION'
1995 and e.org_information20 IS NOT NULL;
1996 --
1997 --Local Variables
1998 --
1999 l_IZA_Weekly_Hours hr_organization_information.ORG_INFORMATION20%TYPE;
2000 v_IZA_Weekly_Hours_table csr_IZA_Weekly_Hours%ROWTYPE;
2001 l_org_id per_all_assignments_f.organization_id%TYPE;
2002 l_organization_id hr_organization_units.organization_id%TYPE;
2003 l_level number;
2004 BEGIN
2005 --
2006 --Determine the Organization Id of the Employees Assignment
2007 --
2008 OPEN get_org_id(p_assignment_id);
2009 FETCH get_org_id into l_org_id;
2010 CLOSE get_org_id;
2011 --
2012 l_IZA_Weekly_Hours := Null;
2013 --
2014 --Check whether the IZA_Weekly_Hours is specified for the Organization
2015 --
2016 OPEN csr_IZA_Weekly_Hours (l_org_id);
2017 Fetch csr_IZA_Weekly_Hours into v_IZA_Weekly_Hours_table ;
2018 If csr_IZA_Weekly_Hours %FOUND and
2019 v_IZA_Weekly_Hours_table.IZA_Weekly_Hours is not null then
2020 l_IZA_Weekly_Hours:= v_IZA_Weekly_Hours_table.IZA_Weekly_Hours;
2021 End if;
2022 Close csr_IZA_Weekly_Hours;
2023 hr_utility.trace('l_IZA_Weekly_Hours is : '||l_IZA_Weekly_Hours);
2024 --If the IZA_Weekly_Hours is not specified tree walk
2025 --to find the organization with
2026 --the same value defined.
2027 --
2028 IF l_IZA_Weekly_Hours IS NULL THEN
2029 if org_hierarchy%ISOPEN then
2030 CLOSE org_hierarchy;
2031 END IF;
2032 OPEN org_hierarchy(l_org_id);
2033 LOOP
2034 FETCH org_hierarchy into l_organization_id,l_level;
2035 exit when org_hierarchy%NOTFOUND or l_IZA_Weekly_Hours IS NOT NULL;
2036 --
2037 --
2038 OPEN csr_IZA_Weekly_Hours(l_organization_id);
2039 Fetch csr_IZA_Weekly_Hours into v_IZA_Weekly_Hours_table;
2040 If csr_IZA_Weekly_Hours %FOUND and
2041 v_IZA_Weekly_Hours_table.IZA_Weekly_Hours is not null then
2042 l_IZA_Weekly_Hours:= v_IZA_Weekly_Hours_table.IZA_Weekly_Hours;
2043 end if;
2044 Close csr_IZA_Weekly_Hours;
2045 END LOOP;
2046 close org_hierarchy;
2047 END IF;
2048 -- If the value for IZA_Weely_Hours is not specified anywhere
2049 -- up in the hierarchy default it to 36
2050 IF l_IZA_Weekly_Hours IS NULL THEN
2051 l_IZA_Weekly_Hours := 36;
2052 END IF;
2053 RETURN l_IZA_Weekly_Hours;
2054 EXCEPTION
2055 when others then
2056 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
2057 IF org_hierarchy%ISOPEN THEN
2058 CLOSE org_hierarchy;
2059 END IF;
2060 RETURN '0';
2061 END Get_IZA_Weekly_Full_Hours;
2062
2063 -- Start Monthly Full time Hours
2064 FUNCTION Get_IZA_Monthly_Full_Hours
2065 (p_assignment_id in NUMBER ) RETURN Varchar2 IS
2066 -- Determine the Organization Id of the Employees Assignment
2067 --
2068 CURSOR get_org_id(p_assignment_id number) is
2069 select paa.organization_id
2070 from per_all_assignments_f paa,fnd_sessions ses
2071 where paa.assignment_id = p_assignment_id and
2072 ses.effective_date between paa.effective_start_date and paa.effective_end_date and
2073 session_id = userenv('sessionid');
2074 --
2075 CURSOR csr_IZA_Monthly_Hours
2076 (l_org_id in hr_organization_units.organization_id%type) IS
2077 select
2078 e.org_information_id,
2079 e.org_information12 IZA_Monthly_Hours
2080 from
2081 hr_organization_information e
2082 where
2083 e.organization_id=l_org_id and
2084 e.org_information_context= 'NL_ORG_INFORMATION'
2085 and e.org_information12 IS NOT NULL;
2086 --
2087 --Local Variables
2088 --
2089 l_IZA_Monthly_Hours hr_organization_information.ORG_INFORMATION12%TYPE;
2090 v_IZA_Monthly_Hours_table csr_IZA_Monthly_Hours%ROWTYPE;
2091 l_org_id per_all_assignments_f.organization_id%TYPE;
2092 l_organization_id hr_organization_units.organization_id%TYPE;
2093 l_level number;
2094 BEGIN
2095 --
2096 --Determine the Organization Id of the Employees Assignment
2097 --
2098 OPEN get_org_id(p_assignment_id);
2099 FETCH get_org_id into l_org_id;
2100 CLOSE get_org_id;
2101 --
2102 l_IZA_Monthly_Hours := Null;
2103 --
2104 --Check whether the IZA_Monthly_Hours is specified for the Organization
2105 --
2106 OPEN csr_IZA_Monthly_Hours (l_org_id);
2107 Fetch csr_IZA_Monthly_Hours into v_IZA_Monthly_Hours_table ;
2108 If csr_IZA_Monthly_Hours %FOUND and
2109 v_IZA_Monthly_Hours_table.IZA_Monthly_Hours is not null then
2110 l_IZA_Monthly_Hours:= v_IZA_Monthly_Hours_table.IZA_Monthly_Hours;
2111 End if;
2112 Close csr_IZA_Monthly_Hours;
2113 hr_utility.trace('l_IZA_Monthly_Hours is : '||l_IZA_Monthly_Hours);
2114 --If the IZA_Monthly_Hours is not specified tree walk
2115 --to find the organization with
2116 --the same value defined.
2117 --
2118 IF l_IZA_Monthly_Hours IS NULL THEN
2119 if org_hierarchy%ISOPEN then
2120 CLOSE org_hierarchy;
2121 END IF;
2122 OPEN org_hierarchy(l_org_id);
2123 LOOP
2124 FETCH org_hierarchy into l_organization_id,l_level;
2125 exit when org_hierarchy%NOTFOUND or l_IZA_Monthly_Hours IS NOT NULL;
2126 --
2127 --
2128 OPEN csr_IZA_Monthly_Hours(l_organization_id);
2129 Fetch csr_IZA_Monthly_Hours into v_IZA_Monthly_Hours_table;
2130 If csr_IZA_Monthly_Hours %FOUND and
2131 v_IZA_Monthly_Hours_table.IZA_Monthly_Hours is not null then
2132 l_IZA_Monthly_Hours:= v_IZA_Monthly_Hours_table.IZA_Monthly_Hours;
2133 end if;
2134 Close csr_IZA_Monthly_Hours;
2135 END LOOP;
2136 close org_hierarchy;
2137 END IF;
2138 -- If the value for IZA_Weely_Hours is not specified anywhere
2139 -- up in the hierarchy default it to 36
2140 IF l_IZA_Monthly_Hours IS NULL THEN
2141 l_IZA_Monthly_Hours := 156;
2142 END IF;
2143 RETURN l_IZA_Monthly_Hours;
2144 EXCEPTION
2145 when others then
2146 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
2147 IF org_hierarchy%ISOPEN THEN
2148 CLOSE org_hierarchy;
2149 END IF;
2150 RETURN '0';
2151 END Get_IZA_Monthly_Full_Hours;
2152
2153
2154 Function Get_IZA_Org_Id(p_org_structure_version_id NUMBER,p_organization_id NUMBER) RETURN NUMBER IS
2155 --
2156 -- Cursor which fetches IZA Information for the given HR Organization
2157 --
2158 CURSOR iza_organization
2159 (l_org_id in hr_organization_units.organization_id%type) IS
2160 select
2161 e.org_information_id,
2162 e.org_information1 iza_information
2163 from
2164 hr_organization_information e
2165 where
2166 e.organization_id=l_org_id and
2167 e.org_information_context= 'NL_IZA_REPO_INFO'
2168 and e.org_information1 IS NOT NULL
2169 and e.org_information2 IS NOT NULL;
2170
2171
2172 --
2173 -- Cursor which fetches IZA Organization list for the given HR Organization
2174 --
2175 CURSOR iza_org_hierarchy(l_org_struct_version_id in per_org_structure_versions.org_structure_version_id%type,
2176 l_org_id in hr_organization_units.organization_id%type) IS
2177 SELECT iza_org_id,lev from hr_organization_information e,(
2178 SELECT l_org_id iza_org_id,0 lev from dual
2179 UNION
2180 SELECT distinct organization_id_parent
2181 ,level
2182 FROM per_org_structure_elements pose
2183 where pose.org_structure_version_id = l_org_struct_version_id
2184 START WITH organization_id_child = l_org_id
2185 CONNECT BY PRIOR organization_id_parent = organization_id_child)
2186 where
2187 e.organization_id=iza_org_id and
2188 e.org_information_context= 'NL_IZA_REPO_INFO'
2189 and e.org_information1 IS NOT NULL
2190 and e.org_information2 IS NOT NULL
2191 ORDER BY lev;
2192
2193 v_iza_org iza_organization%ROWTYPE;
2194 l_level number;
2195 l_iza_org_id hr_organization_units.organization_id%TYPE;
2196
2197 BEGIN
2198 l_iza_org_id := NULL;
2199
2200 OPEN iza_organization(p_organization_id);
2201 Fetch iza_organization into v_iza_org;
2202 If iza_organization%FOUND and v_iza_org.iza_information is not null then
2203 l_iza_org_id:= p_organization_id;
2204 End if;
2205 Close iza_organization;
2206
2207 if iza_org_hierarchy%ISOPEN then
2208 CLOSE iza_org_hierarchy;
2209 end if;
2210
2211 /*Fetch the iza organization list with iza information defined beginning from the HR Org */
2212 if l_iza_org_id IS NULL then
2213 OPEN iza_org_hierarchy(p_org_structure_version_id,p_organization_id);
2214 FETCH iza_org_hierarchy into l_iza_org_id,l_level;
2215 close iza_org_hierarchy;
2216 end if;
2217
2218 RETURN l_iza_org_id;
2219
2220 EXCEPTION
2221 when others then
2222 --hr_utility.set_location('Exception :' || l_proc||SQLERRM(SQLCODE),999);
2223 IF iza_org_hierarchy%ISOPEN THEN
2224 CLOSE iza_org_hierarchy;
2225 END IF;
2226 RETURN null;
2227 END Get_IZA_Org_Id;
2228
2229
2230
2231
2232 END HR_NL_ORG_INFO;
[Home] [Help]