eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HR_NL_LAW_SAMEN_REPORT

Source


1 PACKAGE BODY HR_NL_LAW_SAMEN_REPORT AS
2 /* $Header: pernllsr.pkb 115.9 2002/08/24 14:16:48 gpadmasa noship $ */
3 
4 --
5 function get_total(p_organization_id NUMBER) return NUMBER is
6 begin
7 return(HQOrgData(p_organization_id).total);
8 exception
9 when others then
10   return (0);
11 end;
12 --
13 function get_ls_total(p_organization_id NUMBER) return NUMBER is
14 begin
15 return(HQOrgData(p_organization_id).ls_total);
16 exception
17 when others then
18   return (0);
19 end;
20 --
21 function get_acht_total(p_organization_id NUMBER) return NUMBER is
22 begin
23 return(HQOrgData(p_organization_id).acht_total);
24 exception
25 when others then
26   return (0);
27 end;
28 --
29 function get_full_time(p_organization_id NUMBER) return NUMBER is
30 begin
31 return(HQOrgData(p_organization_id).full_time);
32 exception
33 when others then
34   return (0);
35 end;
36 --
37 function get_acht_full_time(p_organization_id NUMBER) return NUMBER is
38 begin
39 return(HQOrgData(p_organization_id).acht_full_time);
40 exception
41 when others then
42   return (0);
43 end;
44 --
45 function get_part_time(p_organization_id NUMBER) return NUMBER is
46 begin
47 return(HQOrgData(p_organization_id).part_time);
48 exception
49 when others then
50   return (0);
51 end;
52 
53 --
54 function get_acht_part_time(p_organization_id NUMBER) return NUMBER is
55 begin
56 return(HQOrgData(p_organization_id).acht_part_time);
57 exception
58 when others then
59   return (0);
60 end;
61 --
62 function get_total_hired(p_organization_id NUMBER) return NUMBER is
63 begin
64 return(HQOrgData(p_organization_id).total_hired);
65 exception
66 when others then
67   return (0);
68 end;
69 
70 --
71 function get_acht_hired(p_organization_id NUMBER) return NUMBER is
72 begin
73 return(HQOrgData(p_organization_id).acht_hired);
74 exception
75 when others then
76   return (0);
77 end;
78 --
79 function get_terminated(p_organization_id NUMBER) return NUMBER is
80 begin
81 return(HQOrgData(p_organization_id).terminated);
82 exception
83 when others then
84   return (0);
85 end;
86 --
87 function get_acht_terminated(p_organization_id NUMBER) return NUMBER is
88 begin
89 return(HQOrgData(p_organization_id).acht_terminated);
90 exception
91 when others then
92   return (0);
93 end;
94 --
95 function get_current_total(p_organization_id NUMBER) return NUMBER is
96 begin
97 return(HQOrgData(p_organization_id).current_total);
98 exception
99 when others then
100   return (0);
101 end;
102 --
103 function get_last_acht_total(p_organization_id NUMBER) return NUMBER is
104 begin
105 return(HQOrgData(p_organization_id).last_acht_total);
106 exception
107 when others then
108   return (0);
109 end;
110 
111 --
112 function get_last_perc_acht(p_organization_id NUMBER) return NUMBER is
113 begin
114 return(HQOrgData(p_organization_id).last_perc_acht);
115 exception
116 when others then
117   return (0);
118 end;
119 
120 --
121 function get_perc_acht(p_organization_id NUMBER) return NUMBER is
122 begin
123 return(HQOrgData(p_organization_id).perc_acht);
124 exception
125 when others then
126   return (0);
127 end;
128 --
129 function get_perc_full_time(p_organization_id NUMBER) return NUMBER is
130 begin
131 return(HQOrgData(p_organization_id).perc_full_time);
132 exception
133 when others then
134   return (0);
135 end;
136 
137 --
138 function get_perc_acht_ftime(p_organization_id NUMBER) return NUMBER is
139 begin
140 return(HQOrgData(p_organization_id).perc_acht_ftime);
141 exception
142 when others then
143   return (0);
144 end;
145 --
146 function get_perc_part_time(p_organization_id NUMBER) return NUMBER is
147 begin
148 return(HQOrgData(p_organization_id).perc_part_time);
149 exception
150 when others then
151   return (0);
152 end;
153 --
154 function get_perc_acht_ptime(p_organization_id NUMBER) return NUMBER is
155 begin
156 return(HQOrgData(p_organization_id).perc_acht_ptime);
157 exception
158 when others then
159   return (0);
160 end;
161 --
162 function get_perc_acht_hired(p_organization_id NUMBER) return NUMBER is
163 begin
164 return(HQOrgData(p_organization_id).perc_acht_hired);
165 exception
166 when others then
167   return (0);
168 end;
169 --
170 function get_perc_acht_term(p_organization_id NUMBER) return NUMBER is
171 begin
172 return(HQOrgData(p_organization_id).perc_acht_term);
173 exception
174 when others then
175   return (0);
176 end;
177 
178 
179 procedure populate_lawsamen_table
180 ( P_BUSINESS_GROUP_ID         IN NUMBER
181 , P_TOP_ORGANIZATION_ID       IN NUMBER
182 , P_ORGANIZATION_STRUCTURE_ID IN NUMBER
183 , P_ROLL_UP              	  IN VARCHAR2
184 , P_REPORT_YEAR               IN NUMBER
185 , P_REGION                    IN VARCHAR2)
186 
187 is
188 
189 l_roll_up				varchar2(2);
190 
191 cursor	get_org_structure_version
192 (P_ORGANIZATION_STRUCTURE_ID	NUMBER
193 ,P_REPORT_YEAR			NUMBER)
194 is
195 select max(posv.org_structure_version_id) org_structure_version_id
196 from per_org_structure_versions posv
197 where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
198 and     to_number(to_char(nvl(posv.date_from,sysdate),'YYYY')) <= P_REPORT_YEAR
199 and    	to_number(to_char(nvl(posv.date_to,sysdate),'YYYY')) >= P_REPORT_YEAR
200 order by   posv.org_structure_version_id;
201 
202 
203 cursor    get_org_structure_element
204 (P_ORG_STRUCTURE_VERSION_ID	NUMBER
205 ,P_TOP_ORGANIZATION_ID	NUMBER
206 ,P_ORG_STRUCTURE_ID NUMBER)
207 is
208 select    distinct ose.organization_id_child organization_id
209 from      per_org_structure_elements ose, per_org_structure_versions_v posv
210 where     ose.org_structure_version_id     = posv.ORG_STRUCTURE_VERSION_ID
211 and       ose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
212 and       posv.ORG_STRUCTURE_VERSION_ID =  P_ORG_STRUCTURE_VERSION_ID
213 and       posv.ORGANIZATION_STRUCTURE_ID = P_ORG_STRUCTURE_ID
214 and       ose.organization_id_parent         = P_TOP_ORGANIZATION_ID
215 
216 UNION
217 select    P_TOP_ORGANIZATION_ID organization_id
218 from      dual
219 order by  organization_id;
220 
221 cursor	get_organizations
222 ( P_ORG_STRUCTURE_VERSION_ID  NUMBER
223 , P_ORGANIZATION_ID  		NUMBER
224 , P_ROLL_UP 				VARCHAR2)
225 is
226 select 	distinct ose.organization_id_child organization_id
227 from   	per_org_structure_elements ose
228 where  	ose.org_structure_version_id +0  	= P_ORG_STRUCTURE_VERSION_ID
229 and	  	P_ROLL_UP						= 'Y'
230 connect by prior ose.organization_id_child 	= ose.organization_id_parent
231 and    	ose.org_structure_version_id  	= P_ORG_STRUCTURE_VERSION_ID
232 start with ose.organization_id_parent 		= P_ORGANIZATION_ID
233 and    	ose.org_structure_version_id  	= P_ORG_STRUCTURE_VERSION_ID
234 UNION
235 select 	P_ORGANIZATION_ID organization_id
236 from		dual
237 where	P_ROLL_UP                          = 'Y'
238 UNION
239 select 	P_ORGANIZATION_ID organization_id
240 from		dual
241 where	P_ROLL_UP                          = 'N';
242 
243 --Bug :2508617
244 --Ghanshyam
245 --Modified Cursor to remove the join to HR_ORGANIZATION_INFORMATION
246 --to return all Org from Org Hierarchy belonging to specified region
247 cursor get_region_organizations
248 ( P_BUSINESS_GROUP_ID NUMBER
249  ,P_REPORT_YEAR       NUMBER
250  ,P_REGION            VARCHAR2)
251 is
252 SELECT distinct POU.ORGANIZATION_ID organization_id
253 FROM PER_ORGANIZATION_UNITS POU
254 WHERE POU.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
255 AND POU.ORGANIZATION_ID = HR_NL_ORG_INFO.Check_Org_In_Region(POU.ORGANIZATION_ID,P_REGION)
256 AND TO_NUMBER(TO_CHAR(POU.DATE_FROM,'YYYY'))<= P_REPORT_YEAR
257 AND TO_NUMBER(TO_CHAR(NVL(POU.DATE_TO,SYSDATE),'YYYY')) >= P_REPORT_YEAR;
258 
259 
260 BEGIN
261 
262 IF P_REGION IS NULL THEN
263 
264 for org_structure_version_rec in get_org_structure_version
265 (P_ORGANIZATION_STRUCTURE_ID
266 ,P_REPORT_YEAR)
267 loop
268 
269 	for org_structure_element_rec in get_org_structure_element
270 		(org_structure_version_rec.org_structure_version_id
271 		,P_TOP_ORGANIZATION_ID
272 		,P_ORGANIZATION_STRUCTURE_ID)
273 	loop
274 
275 
276 HQOrgData(org_structure_element_rec.organization_id).total		        := 0;
277 HQOrgData(org_structure_element_rec.organization_id).ls_total 		    := 0;
278 HQOrgData(org_structure_element_rec.organization_id).acht_total	        := 0;
279 HQOrgData(org_structure_element_rec.organization_id).full_time 	        := 0;
280 HQOrgData(org_structure_element_rec.organization_id).acht_full_time  	:= 0;
281 HQOrgData(org_structure_element_rec.organization_id).part_time  		:= 0;
282 HQOrgData(org_structure_element_rec.organization_id).acht_part_time	    := 0;
283 HQOrgData(org_structure_element_rec.organization_id).total_hired		:= 0;
284 HQOrgData(org_structure_element_rec.organization_id).acht_hired  		:= 0;
285 HQOrgData(org_structure_element_rec.organization_id).terminated		    := 0;
286 HQOrgData(org_structure_element_rec.organization_id).acht_terminated	:= 0;
287 HQOrgData(org_structure_element_rec.organization_id).current_total		:= 0;
288 HQOrgData(org_structure_element_rec.organization_id).last_acht_total	:= 0;
289 HQOrgData(org_structure_element_rec.organization_id).last_perc_acht	    := 0;
290 HQOrgData(org_structure_element_rec.organization_id).perc_acht			:= 0;
291 HQOrgData(org_structure_element_rec.organization_id).perc_full_time	    := 0;
292 HQOrgData(org_structure_element_rec.organization_id).perc_acht_ftime	:= 0;
293 HQOrgData(org_structure_element_rec.organization_id).perc_part_time	    := 0;
294 HQOrgData(org_structure_element_rec.organization_id).perc_acht_ptime	:= 0;
295 HQOrgData(org_structure_element_rec.organization_id).perc_acht_hired	:= 0;
296 HQOrgData(org_structure_element_rec.organization_id).perc_acht_term	    := 0;
297 
298 if (org_structure_element_rec.organization_id = p_top_organization_id) then
299 l_roll_up := 'N';
300 else
301 l_roll_up := p_roll_up;
302 end if;
303 
304     for org_rec in get_organizations
305 	( org_structure_version_rec.org_structure_version_id -- P_ORG_STRUCTURE_VERSION_ID
306 	, org_structure_element_rec.organization_id -- P_ORGANIZATION_ID
307 	, l_roll_up)
308 	loop
309 
310 calculate_values(P_REPORT_YEAR
311 		, org_rec.organization_id
312 		, P_BUSINESS_GROUP_ID
313 		, org_structure_element_rec.organization_id);
314 
315 
316 end loop; -- get_organizations
317 
318 if(HQOrgData(org_structure_element_rec.organization_id).current_total > 0) then
319 
320 HQOrgData(org_structure_element_rec.organization_id).perc_full_time	    := 100 * HQOrgData(org_structure_element_rec.organization_id).full_time	/HQOrgData(org_structure_element_rec.organization_id).current_total;
321 HQOrgData(org_structure_element_rec.organization_id).perc_part_time	    := 100 * HQOrgData(org_structure_element_rec.organization_id).part_time	/HQOrgData(org_structure_element_rec.organization_id).current_total;
322 HQOrgData(org_structure_element_rec.organization_id).perc_acht	        := 100 * HQOrgData(org_structure_element_rec.organization_id).acht_total	/HQOrgData(org_structure_element_rec.organization_id).current_total;
323 end if;
324 if(HQOrgData(org_structure_element_rec.organization_id).part_time > 0) then
325 HQOrgData(org_structure_element_rec.organization_id).perc_acht_ptime	:= 100 * HQOrgData(org_structure_element_rec.organization_id).acht_part_time	/HQOrgData(org_structure_element_rec.organization_id).part_time;
326 end if;
327 if(HQOrgData(org_structure_element_rec.organization_id).full_time > 0) then
328 HQOrgData(org_structure_element_rec.organization_id).perc_acht_ftime	:= 100 * HQOrgData(org_structure_element_rec.organization_id).acht_full_time	/HQOrgData(org_structure_element_rec.organization_id).full_time;
329 end if;
330 if(HQOrgData(org_structure_element_rec.organization_id).total_hired > 0) then
331 HQOrgData(org_structure_element_rec.organization_id).perc_acht_hired	:= 100 * HQOrgData(org_structure_element_rec.organization_id).acht_hired	/HQOrgData(org_structure_element_rec.organization_id).total_hired;
332 end if;
333 if(HQOrgData(org_structure_element_rec.organization_id).terminated > 0) then
334 HQOrgData(org_structure_element_rec.organization_id).perc_acht_term	    := 100 * HQOrgData(org_structure_element_rec.organization_id).acht_terminated	/HQOrgData(org_structure_element_rec.organization_id).terminated;
335 end if;
336 
337 
338 end loop; -- get_org_structure_element
339 
340 
341 end loop;  -- get_org_structure_version
342 
343 ELSE
344 
345 for org_region_rec in get_region_organizations
346 		(P_BUSINESS_GROUP_ID
347 		,P_REPORT_YEAR
348 		,P_REGION)
349 loop
350 
351 HQOrgData(org_region_rec.organization_id).total		        := 0;
352 HQOrgData(org_region_rec.organization_id).ls_total 		    := 0;
353 HQOrgData(org_region_rec.organization_id).acht_total	        := 0;
354 HQOrgData(org_region_rec.organization_id).full_time 	        := 0;
355 HQOrgData(org_region_rec.organization_id).acht_full_time  	:= 0;
356 HQOrgData(org_region_rec.organization_id).part_time  		:= 0;
357 HQOrgData(org_region_rec.organization_id).acht_part_time	    := 0;
358 HQOrgData(org_region_rec.organization_id).total_hired		:= 0;
359 HQOrgData(org_region_rec.organization_id).acht_hired  		:= 0;
360 HQOrgData(org_region_rec.organization_id).terminated		    := 0;
361 HQOrgData(org_region_rec.organization_id).acht_terminated	:= 0;
362 HQOrgData(org_region_rec.organization_id).current_total		:= 0;
363 HQOrgData(org_region_rec.organization_id).last_acht_total	:= 0;
364 HQOrgData(org_region_rec.organization_id).last_perc_acht	    := 0;
365 HQOrgData(org_region_rec.organization_id).perc_acht			:= 0;
366 HQOrgData(org_region_rec.organization_id).perc_full_time	    := 0;
367 HQOrgData(org_region_rec.organization_id).perc_acht_ftime	:= 0;
368 HQOrgData(org_region_rec.organization_id).perc_part_time	    := 0;
369 HQOrgData(org_region_rec.organization_id).perc_acht_ptime	:= 0;
370 HQOrgData(org_region_rec.organization_id).perc_acht_hired	:= 0;
371 HQOrgData(org_region_rec.organization_id).perc_acht_term	    := 0;
372 
373 calculate_values
374 (   P_REPORT_YEAR
375   , org_region_rec.organization_id
376   , P_BUSINESS_GROUP_ID
377   , org_region_rec.organization_id
378 );
379 
380 
381 end loop;
382 end if;
383 
384 end populate_lawsamen_table;
385 
386 
387 
388 
389 
390 
391 procedure calculate_values
392 (   P_REPORT_YEAR                 IN NUMBER
393   , P_ORGANIZATION_ID             IN NUMBER
394   , P_BUSINESS_GROUP_ID           IN NUMBER
395   , P_TOPORG_ID                   IN NUMBER
396 )is
397 
398 
399 cursor	get_assignment
400 ( P_REPORT_YEAR				NUMBER
401 , P_ORGANIZATION_ID_CHILD	NUMBER
402 , P_BUSINESS_GROUP_ID		NUMBER
403 )
404 is
405 
406 select     distinct paf.assignment_id assignment_id
407               ,paf.person_id
408 from       per_all_assignments_f paf
409 
410 where      paf.organization_id     =     p_organization_id_child
411 and        paf.business_group_id   =     P_BUSINESS_GROUP_ID
412 and        paf.assignment_type     =       'E'
413 and        paf.primary_flag        =       'Y'
414 and        to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
415 and to_number(to_char(nvl(paf.effective_end_date,sysdate),'YYYY')) >= P_REPORT_YEAR;
416 
417 cursor	get_people
418 ( P_REPORT_YEAR				NUMBER
419 , P_BUSINESS_GROUP_ID		NUMBER
420 ,P_PERSON_ID				NUMBER
421 )
422 is
423 select   distinct hr_nl_calc_target_group.get_target_group(p_person_id,
424                    to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY'))
425                   allochth
426        ,ppf1.per_information7 objection
427 from     per_people_f ppf1
428 where      ppf1.business_group_id   =      P_BUSINESS_GROUP_ID
429 and        to_number(to_char(nvl(ppf1.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
430 and to_number(to_char(nvl(ppf1.effective_end_date,sysdate),'YYYY')) >= P_REPORT_YEAR
431 and ppf1.person_id  = P_PERSON_ID
432 and     to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf1.effective_start_date and ppf1.effective_end_date;
433 
434 
435 
436 
437 cursor	get_cur_assignment
438 ( P_REPORT_YEAR				NUMBER
439 , P_ORGANIZATION_ID_CHILD	NUMBER
440 , P_BUSINESS_GROUP_ID		NUMBER
441 )
442 is
443 select  distinct paf.assignment_id assignment_id
444             ,paf.effective_start_date start_date
445 	   ,paf.employment_category per_type
446 	   ,hr_nl_calc_target_group.get_target_group(ppf.person_id,
447            to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY')) allochth
448 	   ,ppf.per_information7 objection
449 
450 from 	per_all_assignments_f paf
451       , per_people_f ppf
452 
453 where 	paf.organization_id 	= 	p_organization_id_child
454 and	paf.business_group_id	=	P_BUSINESS_GROUP_ID
455 and	paf.assignment_type	= 	'E'
456 and	paf.primary_flag	=	'Y'
457 and	to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR
458 and nvl(paf.effective_end_date,sysdate) >= to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY')
459 and paf.person_id  = ppf.person_id
460 and ppf.BUSINESS_GROUP_ID = paf.business_group_id
461 and to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;
462 
463 
464 cursor	get_terminations
465 ( P_REPORT_YEAR				NUMBER
466 , P_ORGANIZATION_ID_CHILD	NUMBER
467 , P_BUSINESS_GROUP_ID		NUMBER
468 )
469 is
470 select  distinct  pos.leaving_reason leaving_reason
471        ,paf.job_id job
472 	   ,paf.person_id
473 
474 from    per_periods_of_service pos
475 	  , per_assignments_f 	 paf
476 
477 where   to_number(to_char(nvl(pos.date_start,sysdate),'YYYY'))                  <= P_REPORT_YEAR
478 and     to_number(to_char(nvl(pos.actual_termination_date,sysdate+50000),'YYYY')) = P_REPORT_YEAR
479 and     pos.period_of_service_id        = paf.period_of_service_id
480 and	   paf.business_group_id	= P_BUSINESS_GROUP_ID
481 and	   paf.assignment_type	= 'E'
482 and	   paf.primary_flag		= 'Y'
483 and    paf.organization_id  	= P_ORGANIZATION_ID_CHILD;
484 
485 
486 
487 
488 l_total 			    number := 0;
489 l_ls_total 	 			number := 0;
490 l_cur_total             number := 0;
491 l_acht_total 			number := 0;
492 l_last_acht_total       number := 0;
493 l_full_time  			number := 0;
494 l_acht_full_time  		number := 0;
495 l_part_time  			number := 0;
496 l_acht_part_time  		number := 0;
497 l_total_hired  			number := 0;
498 l_acht_hired  			number := 0;
499 l_terminated  			number := 0;
500 l_acht_terminated  		number := 0;
501 l_last_perc_acht   		number := 0;
502 l_perc_acht        		number := 0;
503 l_perc_full_time   		number := 0;
504 l_perc_acht_ftime  		number := 0;
505 l_perc_part_time   		number := 0;
506 l_perc_acht_ptime  		number := 0;
507 l_perc_acht_hired  		number := 0;
508 l_perc_acht_term   		number := 0;
509 
510 l_acht                  varchar2(1);
511 l_pertype     	 		varchar2(10);
512 l_movement_category		varchar2(30);
513 l_start_date            date;
514 l_end_date              date;
515 
516 
517 
518 begin
519 
520 for assgt_rec in get_assignment
521 		( P_REPORT_YEAR
522 		, P_ORGANIZATION_ID
523 		, P_BUSINESS_GROUP_ID)
524 
525 loop
526 for people_rec in get_people
527 		( P_REPORT_YEAR
528 		, P_BUSINESS_GROUP_ID
529 		, assgt_rec.person_id)
530 		loop
531 
532 
533 
534 IF 	(people_rec.allochth = 'Y')
535 then
536 IF (people_rec.objection <> 'Y')
537 then l_ls_total := l_ls_total +1;
538 end if;
539 end if;
540 
541 
542 l_movement_category := NULL;
543 l_start_date := '01-JAN-'||P_REPORT_YEAR;
544 l_end_date   := '31-DEC-'||P_REPORT_YEAR;
545 
546 HR_PERSON_FLEX_LOGIC.GetMovementCategory(
547 		 p_organization_id        =>   P_ORGANIZATION_ID
548 		,p_assignment_id          =>   assgt_rec.assignment_id
549 		,p_period_start_date      =>   l_start_date
550 		,p_period_end_date        =>   l_end_date
551 		,p_movement_type          =>   'IN'
552 		,p_movement_category 	 =>   l_movement_category
553 		);
554 
555 if l_movement_category = 'NEW_HIRE'
556 then	l_total_hired := l_total_hired + 1;
557 IF 	(people_rec.allochth = 'Y')
558 then l_acht_hired := l_acht_hired +1;
559 end if;
560 end if;
561 
562 end loop;
563 l_total := l_total + 1;
564 end loop;
565 
566 for assgt_cur_rec in get_cur_assignment
567 		( P_REPORT_YEAR
568 		, P_ORGANIZATION_ID
569 		, P_BUSINESS_GROUP_ID)
570 
571 loop
572 
573 l_cur_total := l_cur_total +1;
574 
575 IF 	(assgt_cur_rec.allochth = 'Y')
576 then
577 l_acht_total := l_acht_total + 1;
578 end if;
579 
580 IF 	(assgt_cur_rec.per_type = 'FR' or assgt_cur_rec.per_type = 'FT')
581 then l_full_time := l_full_time + 1;
582 IF 	(assgt_cur_rec.allochth = 'Y')
583 then l_acht_full_time := l_acht_full_time +1;
584 end if;
585 end if;
586 
587 
588 IF 	(assgt_cur_rec.per_type = 'PR' or assgt_cur_rec.per_type = 'PT')
589 then l_part_time := l_part_time + 1;
590 IF 	(assgt_cur_rec.allochth = 'Y')
591 then l_acht_part_time := l_acht_part_time +1;
592 end if;
593 end if;
594 
595 
596 end loop;
597 
598 select  count(1)
599 into    l_last_acht_total
600 from 	per_all_assignments_f paf
601       , per_people_f ppf
602 
603 where 	paf.organization_id 	= 	P_ORGANIZATION_ID
604 and	    paf.business_group_id	=	P_BUSINESS_GROUP_ID
605 and	    paf.assignment_type	= 	'E'
606 and	    paf.primary_flag	=	'Y'
607 and	    to_number(to_char(nvl(paf.effective_start_date,sysdate),'YYYY')) <= P_REPORT_YEAR - 1
608 and     nvl(paf.effective_end_date,sysdate) >= to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY')
609 and     paf.person_id  = ppf.person_id
610 and     ppf.BUSINESS_GROUP_ID = paf.business_group_id
611 and     hr_nl_calc_target_group.get_target_group(ppf.person_id,
612         to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY')) = 'Y'
613 and     to_date('31/12/' ||to_char(P_REPORT_YEAR - 1),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;
614 
615 
616 
617 if (l_last_acht_total > 0) then
618 l_last_perc_acht  := 100 * (l_acht_total - l_last_acht_total) / l_last_acht_total;
619 end if;
620 if (l_cur_total > 0) then
621 l_perc_acht       := 100 * (l_acht_total/l_cur_total) ;
622 l_perc_full_time  := 100 * (l_full_time/l_cur_total);
623 l_perc_part_time  := 100 * (l_part_time/l_cur_total);
624 end if;
625 if (l_part_time > 0) then
626 l_perc_acht_ptime := 100 * (l_acht_part_time/l_part_time);
627 end if;
628 if (l_full_time > 0) then
629 l_perc_acht_ftime := 100 * (l_acht_full_time/l_full_time);
630 end if;
631 if (l_total_hired > 0) then
632 l_perc_acht_hired := 100 * (l_acht_hired/l_total_hired);
633 end if;
634 
635 
636 
637 	HQOrgData(P_TOPORG_ID).full_time :=
638 	HQOrgData(P_TOPORG_ID).full_time + nvl(l_full_time,0);
639 	HQOrgData(P_TOPORG_ID).acht_full_time :=
640 	HQOrgData(P_TOPORG_ID).acht_full_time + nvl(l_acht_full_time,0);
641 	HQOrgData(P_TOPORG_ID).part_time :=
642 	HQOrgData(P_TOPORG_ID).part_time + nvl(l_part_time,0);
643 	HQOrgData(P_TOPORG_ID).acht_part_time :=
644 	HQOrgData(P_TOPORG_ID).acht_part_time + nvl(l_acht_part_time,0);
645 	HQOrgData(P_TOPORG_ID).ls_total :=
646 	HQOrgData(P_TOPORG_ID).ls_total + nvl(l_ls_total,0);
647 	HQOrgData(P_TOPORG_ID).acht_total :=
648 	HQOrgData(P_TOPORG_ID).acht_total + nvl(l_acht_total,0);
649 	HQOrgData(P_TOPORG_ID).total :=
650 	HQOrgData(P_TOPORG_ID).total + nvl(l_total,0);
651 	HQOrgData(P_TOPORG_ID).total_hired :=
652 	HQOrgData(P_TOPORG_ID).total_hired + nvl(l_total_hired,0);
653 	HQOrgData(P_TOPORG_ID).acht_hired :=
654 	HQOrgData(P_TOPORG_ID).acht_hired + nvl(l_acht_hired,0);
655     HQOrgData(P_TOPORG_ID).current_total :=
656 	HQOrgData(P_TOPORG_ID).current_total + nvl(l_cur_total,0);
657 	HQOrgData(P_TOPORG_ID).last_acht_total :=
658 	HQOrgData(P_TOPORG_ID).last_acht_total + nvl(l_last_acht_total,0);
659 	HQOrgData(P_TOPORG_ID).last_perc_acht :=
660 	HQOrgData(P_TOPORG_ID).last_perc_acht + nvl(l_last_perc_acht,0);
661 	HQOrgData(P_TOPORG_ID).perc_acht			:=
662 	HQOrgData(P_TOPORG_ID).perc_acht + nvl(l_perc_acht,0);
663 	HQOrgData(P_TOPORG_ID).perc_full_time	:=
664 	HQOrgData(P_TOPORG_ID).perc_full_time + nvl(l_perc_full_time,0);
665 	HQOrgData(P_TOPORG_ID).perc_acht_ftime	:=
666 	HQOrgData(P_TOPORG_ID).perc_acht_ftime + nvl(l_perc_acht_ftime,0);
667 	HQOrgData(P_TOPORG_ID).perc_part_time	:=
668 	HQOrgData(P_TOPORG_ID).perc_part_time + nvl(l_perc_part_time,0);
669 	HQOrgData(P_TOPORG_ID).perc_acht_ptime	:=
670 	HQOrgData(P_TOPORG_ID).perc_acht_ptime + nvl(l_perc_acht_ptime,0);
671 	HQOrgData(P_TOPORG_ID).perc_acht_hired	:=
672 	HQOrgData(P_TOPORG_ID).perc_acht_hired +nvl(l_perc_acht_hired,0);
673 
674 
675 
676 
677 
678 for term_rec in get_terminations
679 		( P_REPORT_YEAR
680 		, P_ORGANIZATION_ID
681 		, P_BUSINESS_GROUP_ID)
682 
683 loop -- get_terminations
684 
685 l_terminated := l_terminated + 1;
686 
687 select hr_nl_calc_target_group.get_target_group(ppf.person_id,
688        to_date('31/12/'||to_char(P_REPORT_YEAR),'DD/MM/YYYY')) allochth
689 into l_acht
690 from per_people_f ppf
691 where ppf.person_id = term_rec.person_id
692 and     to_date('31/12/' ||to_char(P_REPORT_YEAR),'DD/MM/YYYY') between ppf.effective_start_date and ppf.effective_end_date;
693 
694 
695 
696 IF 	(l_acht = 'Y')
697 then l_acht_terminated := l_acht_terminated +1;
698 end if;
699 
700 end loop;
701 
702 if (l_terminated > 0) then
703 l_perc_acht_term  := 100 * (l_acht_terminated/l_terminated);
704 end if;
705 
706 	HQOrgData(P_TOPORG_ID).terminated :=
707 	HQOrgData(P_TOPORG_ID).terminated + nvl(l_terminated,0);
708     HQOrgData(P_TOPORG_ID).acht_terminated :=
709 	HQOrgData(P_TOPORG_ID).acht_terminated + nvl(l_acht_terminated,0);
710 	HQOrgData(P_TOPORG_ID).perc_acht_term	:=
711 	HQOrgData(P_TOPORG_ID).perc_acht_term + nvl(l_perc_acht_term,0);
712 
713 
714 end; -- end calculate_values
715 
716 
717 END HR_NL_LAW_SAMEN_REPORT;