[Home] [Help]
PACKAGE BODY: APPS.HR_HEAD_COUNT_SUMMARY
Source
1 PACKAGE BODY HR_HEAD_COUNT_SUMMARY AS
2 /* $Header: perhdsum.pkb 115.11 2003/07/07 18:22:12 asahay noship $ */
3
4 --
5 function get_rev_start_val(p_organization_id NUMBER) return NUMBER is
6 begin
7 return(HQOrgData(p_organization_id).rev_start_val);
8 exception
9 when others then
10 return (0);
11 end;
12 --
13 function get_nonrev_start_val(p_organization_id NUMBER) return NUMBER is
14 begin
15 return(HQOrgData(p_organization_id).nonrev_start_val);
16 exception
17 when others then
18 return (0);
19 end;
20 --
21 function get_rev_end_val(p_organization_id NUMBER) return NUMBER is
22 begin
23 return(HQOrgData(p_organization_id).rev_end_val);
24 exception
25 when others then
26 return (0);
27 end;
28 --
29 function get_nonrev_end_val(p_organization_id NUMBER) return NUMBER is
30 begin
31 return(HQOrgData(p_organization_id).nonrev_end_val);
32 exception
33 when others then
34 return (0);
35 end;
36 --
37 function get_rev_net_change(p_organization_id NUMBER) return NUMBER is
38 begin
39 return(HQOrgData(p_organization_id).rev_end_val-HQOrgData(p_organization_id).rev_start_val);
40 exception
41 when others then
42 return (0);
43 end;
44 --
45 function get_nonrev_net_change(p_organization_id NUMBER) return NUMBER is
46 begin
47 return(HQOrgData(p_organization_id).nonrev_end_val-HQOrgData(p_organization_id).nonrev_start_val);
48 exception
49 when others then
50 return (0);
51 end;
52 --
53 function get_rev_nh(p_organization_id NUMBER) return NUMBER is
54 begin
55 return(HQOrgData(p_organization_id).rev_nh);
56 exception
57 when others then
58 return (0);
59 end;
60 --
61 function get_nonrev_nh(p_organization_id NUMBER) return NUMBER is
62 begin
63 return(HQOrgData(p_organization_id).nonrev_nh);
64 exception
65 when others then
66 return (0);
67 end;
68 --
69 function get_rev_term(p_organization_id NUMBER) return NUMBER is
70 begin
71 return(HQOrgData(p_organization_id).rev_term);
72 exception
73 when others then
74 return (0);
75 end;
76 --
77 function get_nonrev_term(p_organization_id NUMBER) return NUMBER is
78 begin
79 return(HQOrgData(p_organization_id).nonrev_term);
80 exception
81 when others then
82 return (0);
83 end;
84 --
85 --
86 function get_rev_other_net(p_organization_id NUMBER) return NUMBER is
87 begin
88 return(HQOrgData(p_organization_id).rev_end_val-HQOrgData(p_organization_id).rev_start_val-HQOrgData(p_organization_id).rev_nh+HQOrgData(p_organization_id).rev_term);
89 exception
90 when others then
91 return (0);
92 end;
93 --
94 function get_nonrev_other_net(p_organization_id NUMBER) return NUMBER is
95 begin
96 return(HQOrgData(p_organization_id).nonrev_end_val-HQOrgData(p_organization_id).nonrev_start_val-HQOrgData(p_organization_id).nonrev_nh+HQOrgData(p_organization_id).nonrev_term);
97 exception
98 when others then
99 return (0);
100 end;
101 --
102
103 procedure populate_summary_table
104 ( P_BUSINESS_GROUP_ID IN NUMBER
105 , P_TOP_ORGANIZATION_ID IN NUMBER
106 , P_ORGANIZATION_STRUCTURE_ID IN NUMBER
107 , P_BUDGET IN VARCHAR2
108 , P_ROLL_UP IN VARCHAR2
109 , P_INCLUDE_TOP_ORG IN VARCHAR2
110 , P_REPORT_DATE_FROM IN DATE
111 , P_REPORT_DATE_TO IN DATE
112 , P_REPORT_DATE IN DATE
113 , P_INCLUDE_ASG_TYPE IN VARCHAR2
114 , P_JOB_CATEGORY IN VARCHAR2 default 'RG'
115 )
116 is
117 cursor get_all_organizations
118 (P_ORGANIZATION_STRUCTURE_ID NUMBER
119 ,P_TOP_ORGANIZATION_ID NUMBER
120 ,P_REPORT_DATE_FROM DATE
121 ,P_REPORT_DATE_TO DATE)
122 is
123 select distinct pose.ORGANIZATION_ID_CHILD organization_id
124 from per_org_structure_elements pose
125 where exists (select 1
126 from per_org_structure_versions posv
127 where posv.org_structure_version_id = pose.org_structure_version_id
128 and posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
129 and (posv.date_from <= P_REPORT_DATE_FROM
130 or nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'))
131 >= P_REPORT_DATE_TO ))
132 and pose.organization_id_parent = P_TOP_ORGANIZATION_ID
133 union
134 select P_TOP_ORGANIZATION_ID
135 from sys.dual
136 where P_INCLUDE_TOP_ORG = 'Y';
137
138 cursor get_org_structure_version
139 (P_ORGANIZATION_STRUCTURE_ID NUMBER
140 ,P_REPORT_DATE_FROM DATE
141 ,P_REPORT_DATE_TO DATE)
142 is
143 select posv.org_structure_version_id
144 ,posv.date_from date_from
145 ,nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY')) date_to
146 from per_org_structure_versions posv
147 where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
148 and (P_REPORT_DATE_FROM between posv.date_from and
149 nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'))
150 or posv.date_from between P_REPORT_DATE_FROM and P_REPORT_DATE_TO)
151 order by posv.org_structure_version_id
152 ,posv.date_from
153 ,posv.date_to;
154
155 cursor get_org_structure_element
156 (P_ORG_STRUCTURE_VERSION_ID NUMBER
157 ,P_TOP_ORGANIZATION_ID NUMBER)
158 is
159 select ose.org_structure_element_id,
160 ose.organization_id_child organization_id1
161 from per_org_structure_elements ose
162 where ose.org_structure_version_id +0 = P_ORG_STRUCTURE_VERSION_ID
163 and ose.organization_id_parent = P_TOP_ORGANIZATION_ID
164 order by ose.organization_id_child;
165
166 cursor get_organizations
167 ( P_ORG_STRUCTURE_VERSION_ID NUMBER
168 , P_ORGANIZATION_ID NUMBER
169 , P_ROLL_UP VARCHAR2)
170 is
171 select ose.organization_id_child organization_id2
172 from per_org_structure_elements ose
173 where ose.org_structure_version_id +0 = P_ORG_STRUCTURE_VERSION_ID
174 and P_ROLL_UP = 'Y'
175 connect by prior ose.organization_id_child = ose.organization_id_parent
176 and ose.org_structure_version_id = P_ORG_STRUCTURE_VERSION_ID
177 start with ose.organization_id_parent = P_ORGANIZATION_ID
178 and ose.org_structure_version_id = P_ORG_STRUCTURE_VERSION_ID
179 UNION
180 select P_ORGANIZATION_ID organization_id
181 from dual
182 where P_ROLL_UP = 'Y'
183 UNION
184 select P_ORGANIZATION_ID organization_id
185 from dual
186 where P_ROLL_UP = 'N';
187
188 cursor get_assignment_start_end
189 ( P_EFFECTIVE_DATE DATE
190 , P_ORGANIZATION_ID_CHILD NUMBER
191 , P_BUSINESS_GROUP_ID NUMBER
192 )
193 is
194 select paf.assignment_id
195 ,paf.job_id
196 ,paf.effective_start_date
197 ,paf.assignment_type
198 from per_all_assignments_f paf,
199 per_assignment_status_types past
200 where paf.organization_id = P_ORGANIZATION_ID_CHILD
201 and P_EFFECTIVE_DATE between paf.effective_start_date
202 and paf.effective_end_date
203 and paf.assignment_status_type_id = past.assignment_status_type_id
204 and (
205 (paf.assignment_type = 'E'
206 and (
207 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
208 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
209 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
210 'SUSP_ASSIGN'))
211 )
212 )
213 OR
214 (paf.assignment_type = 'C'
215 and (
216 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
217 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
218 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
219 'SUSP_CWK_ASG'))
220 )
221 )
222 );
223
224
225 cursor get_assignment
226 ( P_DATE_FROM DATE
227 , P_DATE_TO DATE
228 , P_ORGANIZATION_ID_CHILD NUMBER
229 , P_BUSINESS_GROUP_ID NUMBER
230 )
231 is
232 select paf.assignment_id
233 ,paf.job_id
234 ,paf.effective_start_date
235 ,paf.assignment_type
236 from per_all_assignments_f paf,
237 per_assignment_status_types past
238 where paf.organization_id = p_organization_id_child
239 and paf.effective_start_date <= P_DATE_FROM
240 and paf.assignment_status_type_id = past.assignment_status_type_id
241 and P_DATE_TO <= (select max(paf1.effective_end_date)
242 from per_all_assignments_f paf1
243 ,per_assignment_status_types past1
244 where paf.assignment_id = paf1.assignment_id
245 and paf1.assignment_status_type_id = past1.assignment_status_type_id
246 and past.per_system_status = past1.per_system_status)
247 and (
248 (paf.assignment_type = 'E'
249 and (
250 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
251 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
252 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
253 'SUSP_ASSIGN'))
254 )
255 )
256 OR
257 (paf.assignment_type = 'C'
258 and (
259 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
260 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
261 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
262 'SUSP_CWK_ASG'))
263 )
264 )
265 );
266
267
268 cursor get_terminations
269 ( P_DATE_FROM DATE
270 , P_DATE_TO DATE
271 , P_ORGANIZATION_ID_CHILD NUMBER
272 , P_BUSINESS_GROUP_ID NUMBER
273 )
274 is
275 select paf.job_id
276 from per_periods_of_service pos
277 ,per_all_assignments_f paf
278 where pos.date_start <= P_DATE_FROM
279 and pos.actual_termination_date is not null
280 and pos.actual_termination_date between P_DATE_FROM and P_DATE_TO
281 and pos.period_of_service_id = paf.period_of_service_id
282 and paf.assignment_type = 'E'
283 and paf.primary_flag = 'Y'
284 and paf.organization_id = P_ORGANIZATION_ID_CHILD
285 and paf.effective_start_date in
286 (select max(paf1.effective_start_date)
287 from per_all_assignments_f paf1
288 where paf1.assignment_id = paf.assignment_id
289 and paf1.effective_end_date
290 between P_DATE_FROM
291 and P_DATE_TO)
292 UNION
293 select paf.job_id
294 from per_periods_of_placement pop
295 ,per_all_assignments_f paf
296 where pop.date_start <= P_DATE_FROM
297 and pop.actual_termination_date is not null
298 and pop.actual_termination_date between P_DATE_FROM
299 and P_DATE_TO
300 and pop.date_start = paf.period_of_placement_date_start
301 and paf.effective_end_date = pop.actual_termination_date
302 and paf.assignment_type = 'C'
303 and paf.primary_flag = 'Y'
304 and paf.organization_id = P_ORGANIZATION_ID_CHILD
305 and paf.effective_start_date in
306 (select max(paf1.effective_start_date)
307 from per_all_assignments_f paf1
308 where paf1.assignment_id = paf.assignment_id
309 and paf1.effective_end_date
310 between P_DATE_FROM
311 and P_DATE_TO);
312
313 cursor c_get_ABV_formula
314 ( p_business_group_id NUMBER )
315 is
316 select formula_id
317 from ff_formulas_f
318 where p_business_group_id = business_group_id+0
319 and trunc(sysdate) between effective_start_date and effective_end_date
320 and formula_name = 'BUDGET_'||p_budget
321 and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Quickpaint');
322
323
324 cursor c_get_tmplt_ABV_formula is
325 select formula_id
326 from ff_formulas_f
327 where business_group_id+0 is null
328 and trunc(sysdate) between effective_start_date and effective_end_date
329 and formula_name = 'TEMPLATE_'||p_budget
330 and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Quickpaint');
331
332 l_ABV_formula_id number;
333 l_ABV number;
334 l_jobcatg varchar2(1);
335 l_budget varchar2(30);
336 l_rev_start_val number := 0;
337 l_rev_end_val number := 0;
338 l_nonrev_start_val number := 0;
339 l_nonrev_end_val number := 0;
340 l_rev_nh number := 0;
341 l_nonrev_nh number := 0;
342 l_rev_term number := 0;
343 l_nonrev_term number := 0;
344 l_movement_category varchar2(30);
345 P_DATE_FROM date;
346 P_DATE_TO date;
347 l_min_org_structure_version_id number;
348 l_max_org_structure_version_id number;
349 l_min_date_from date;
350 l_min_date_to date;
351 l_max_date_from date;
352 l_max_date_to date;
353
354 begin
355
356 hr_utility.set_location('Look for ABV Formula - 1',10);
357
358 /* Look for ABV Formula */
359
360 open c_get_ABV_formula(p_business_group_id);
361
362 fetch c_get_ABV_formula into l_ABV_formula_id;
363
364 hr_utility.set_location('ABV Formula ID = '||l_ABV_formula_id,20);
365
366 if (c_get_ABV_formula%notfound)
367
368 then
369
370 close c_get_ABV_formula;
371
372 hr_utility.set_location('c_get_ABV_formula Not Found - 2',30);
373
374 /* If the ABV formula does not exist, look for the template formula */
375
376 open c_get_tmplt_ABV_formula;
377
378 fetch c_get_tmplt_ABV_formula into l_ABV_formula_id;
379
380 hr_utility.set_location('Template ABV Formula = '||l_ABV_formula_id,40);
381
382 if (c_get_tmplt_ABV_formula%notfound)
383
384 then
385
386 close c_get_tmplt_ABV_formula;
387
388 hr_utility.set_location('c_get_tmpt_ABV_formula Not Found - 3',50);
389
390 -- Set to null so that we can calculate values differently later
391
392 l_ABV_formula_id := null;
393
394 else
395
396 close c_get_tmplt_ABV_formula;
397
398 hr_utility.set_location('close c_get_tmpt_ABV_formula - 4',60);
399
400 end if;
401
402 else
403
404 close c_get_ABV_formula;
405
406 hr_utility.set_location('close c_get_ABV_formula - 5',70);
407
408 end if;
409
410
411
412 hr_utility.set_location('P_BUSINESS_GROUP_ID='||to_char(P_BUSINESS_GROUP_ID),80);
413 hr_utility.set_location('P_TOP_ORGANIZATION_ID='||to_char(P_TOP_ORGANIZATION_ID),90);
414 hr_utility.set_location('P_ORGANIZATION_STRUCTURE_ID='||to_char(P_ORGANIZATION_STRUCTURE_ID),100);
415 hr_utility.set_location('P_REPORT_DATE_FROM='||to_char(P_REPORT_DATE_FROM,'DD-MON-YYYY'),110);
416 hr_utility.set_location('P_REPORT_DATE_TO='||to_char(P_REPORT_DATE_TO,'DD-MON-YYYY'),120);
417 hr_utility.set_location('P_REPORT_DATE='||to_char(P_REPORT_DATE,'DD-MON-YYYY'),130);
418 hr_utility.set_location('P_JOB_CATEGORY='||P_JOB_CATEGORY,140);
419 hr_utility.set_location('P_INCLUDE_TOP_ORG='||P_INCLUDE_TOP_ORG,150);
420
421 begin
422
423 select min(posv.org_structure_version_id)
424 into l_min_org_structure_version_id
425 from per_org_structure_versions posv
426 where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
427 and P_REPORT_DATE_FROM between posv.date_from
428 and nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'));
429
430 hr_utility.set_location('min_org_structure_version_id='||l_min_org_structure_version_id,160);
431
432 select greatest(posv.date_from,P_REPORT_DATE_FROM),
433 least(nvl(posv.date_to,P_REPORT_DATE_TO),P_REPORT_DATE_TO)
434 into l_min_date_from,l_min_date_to
435 from per_org_structure_versions posv
436 where posv.org_structure_version_id = l_min_org_structure_version_id;
437
438 hr_utility.set_location('l_min_date_from='||l_min_date_from,170);
439 hr_utility.set_location('l_min_date_to='||l_min_date_to,180);
440
441 select max(posv.org_structure_version_id)
442 into l_max_org_structure_version_id
443 from per_org_structure_versions posv
444 where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
445 and P_REPORT_DATE_TO between posv.date_from
446 and nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'));
447
448 hr_utility.set_location('max_org_structure_version_id='||l_max_org_structure_version_id,190);
449
450 select least(nvl(posv.date_to,P_REPORT_DATE_TO),P_REPORT_DATE_TO),
451 greatest(posv.date_from,P_REPORT_DATE_FROM)
452 into l_max_date_to,l_max_date_from
453 from per_org_structure_versions posv
454 where posv.org_structure_version_id = l_max_org_structure_version_id;
455
456 exception
457 when NO_DATA_FOUND then
458 raise;
459 end;
460
461 hr_utility.set_location('l_max_date_from='||l_max_date_from,200);
462 hr_utility.set_location('l_max_date_to='||l_max_date_to,210);
463
464 for all_organizations_rec in get_all_organizations
465 (P_ORGANIZATION_STRUCTURE_ID
466 ,P_TOP_ORGANIZATION_ID
467 ,P_REPORT_DATE_FROM
468 ,P_REPORT_DATE_TO)
469 loop
470
471 HQOrgData(all_organizations_rec.organization_id).rev_start_val := 0;
472 HQOrgData(all_organizations_rec.organization_id).rev_end_val := 0;
473 HQOrgData(all_organizations_rec.organization_id).nonrev_start_val := 0;
474 HQOrgData(all_organizations_rec.organization_id).nonrev_end_val := 0;
475 HQOrgData(all_organizations_rec.organization_id).rev_nh := 0;
476 HQOrgData(all_organizations_rec.organization_id).nonrev_nh := 0;
477 HQOrgData(all_organizations_rec.organization_id).rev_term := 0;
478 HQOrgData(all_organizations_rec.organization_id).nonrev_term := 0;
479
480 hr_utility.set_location('all_organizations_rec.organization_id='||all_organizations_rec.organization_id,220);
481 hr_utility.set_location('rev_start_val='||HQOrgData(all_organizations_rec.organization_id).rev_start_val,230);
482 hr_utility.set_location('rev_end_val='||HQOrgData(all_organizations_rec.organization_id).rev_end_val,240);
483 hr_utility.set_location('nonrev_start_val='||HQOrgData(all_organizations_rec.organization_id).nonrev_start_val,250);
484 hr_utility.set_location('nonrev_end_val='||HQOrgData(all_organizations_rec.organization_id).nonrev_end_val,260);
485
486 end loop;
487
488
489 for org_str_ele in get_org_structure_element
490 (l_min_org_structure_version_id
491 ,P_TOP_ORGANIZATION_ID
492 )
493
494 loop -- get_org_structure_element
495
496 hr_utility.set_location('org_str_ele.organization_id1='||org_str_ele.organization_id1,270);
497
498 l_rev_start_val :=0;
499 l_nonrev_start_val :=0;
500
501 for start_orgs_rec in get_organizations
502 (l_min_org_structure_version_id
503 ,org_str_ele.organization_id1
504 ,P_ROLL_UP)
505
506 loop -- get_organizations
507
508 hr_utility.set_location('start_orgs_rec.organization_id2='||start_orgs_rec.organization_id2,280);
509
510 for asg_start_rec in get_assignment_start_end
511 (l_min_date_from
512 ,start_orgs_rec.organization_id2
513 ,P_BUSINESS_GROUP_ID
514 )
515 loop -- get_assignment_start_end
516
517 hr_utility.set_location('asg_start_rec.assignment_id='||asg_start_rec.assignment_id,290);
518
519
520 l_jobcatg := NULL;
521 l_abv := NULL;
522
523 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
524 ( p_ABV_formula_id => l_ABV_formula_id
525 , p_ABV => p_budget
526 , p_assignment_id => asg_start_rec.assignment_id
527 , p_effective_date => asg_start_rec.effective_start_date
528 , p_session_date => trunc(sysdate) );
529
530 hr_utility.set_location('l_abv='||l_abv,300);
531
532
533 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
534 (asg_start_rec.job_id
535 ,p_job_category);
536
537 if l_jobcatg = 'Y'
538 then
539
540 l_rev_start_val := l_rev_start_val + l_abv;
541 else
542
543 l_nonrev_start_val := l_nonrev_start_val + l_abv;
544 end if;
545
546 end loop; -- get_assignment_start_end
547
548 end loop; -- get_organizations
549
550
551 HQOrgData(org_str_ele.organization_id1).rev_start_val :=
552 HQOrgData(org_str_ele.organization_id1).rev_start_val+ l_rev_start_val;
553 HQOrgData(org_str_ele.organization_id1).nonrev_start_val :=
554 HQOrgData(org_str_ele.organization_id1).nonrev_start_val+l_nonrev_start_val;
555
556 hr_utility.set_location('org_str_ele.organization_id1='||org_str_ele.organization_id1,310);
557 hr_utility.set_location('rev_start_val='||HQOrgData(org_str_ele.organization_id1).rev_start_val,320);
558 hr_utility.set_location('nonrev_start_val='||HQOrgData(org_str_ele.organization_id1).nonrev_start_val,330);
559
560 end loop; -- get_org_structure_element
561
562 hr_utility.set_location('100',340);
563
564 for org_str_ele in get_org_structure_element
565 (l_max_org_structure_version_id
566 ,P_TOP_ORGANIZATION_ID
567 )
568
569 loop -- get_org_structure_element
570
571 hr_utility.set_location('end.org_str_ele.organization_id1='||org_str_ele.organization_id1,350);
572
573 l_rev_end_val :=0;
574 l_nonrev_end_val :=0;
575
576 for end_orgs_rec in get_organizations
577 (l_max_org_structure_version_id
578 ,org_str_ele.organization_id1
579 ,P_ROLL_UP)
580
581 loop -- get_organizations
582
583 hr_utility.set_location('end_orgs_rec.organization_id2='||end_orgs_rec.organization_id2,360);
584
585 for asg_end_rec in get_assignment_start_end
586 (l_max_date_to
587 ,end_orgs_rec.organization_id2
588 ,P_BUSINESS_GROUP_ID
589 )
590 loop -- get_assignment_start_end
591
592 hr_utility.set_location('asg_end_rec.assignment_id='||asg_end_rec.assignment_id,370);
593
594
595 l_jobcatg := NULL;
596 l_abv := NULL;
597
598 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
599 ( p_ABV_formula_id => l_ABV_formula_id
600 , p_ABV => p_budget
601 , p_assignment_id => asg_end_rec.assignment_id
602 , p_effective_date => asg_end_rec.effective_start_date
603 , p_session_date => trunc(sysdate) );
604
605
606 hr_utility.set_location('l_abv='||l_abv,380);
607
608 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
609 (asg_end_rec.job_id
610 ,p_job_category);
611
612 if l_jobcatg = 'Y'
613 then
614
615 l_rev_end_val := l_rev_end_val + l_abv;
616 else
617
618 l_nonrev_end_val := l_nonrev_end_val + l_abv;
619 end if;
620
621 end loop; -- get_assignment_start_end
622
623 end loop; -- get_organizations
624
625 HQOrgData(org_str_ele.organization_id1).rev_end_val :=
626 HQOrgData(org_str_ele.organization_id1).rev_end_val+ l_rev_end_val;
627 HQOrgData(org_str_ele.organization_id1).nonrev_end_val :=
628 HQOrgData(org_str_ele.organization_id1).nonrev_end_val+l_nonrev_end_val;
629
630 hr_utility.set_location('org_str_ele.organization_id1='||org_str_ele.organization_id1,390);
631 hr_utility.set_location('rev_end_val='||HQOrgData(org_str_ele.organization_id1).rev_end_val,400);
632 hr_utility.set_location('nonrev_end_val='||HQOrgData(org_str_ele.organization_id1).nonrev_end_val,410);
633
634 end loop; -- get_org_structure_element
635
636 -- Start of New Hires and Terminations Count
637
638 for org_structure_version_rec in get_org_structure_version
639 (P_ORGANIZATION_STRUCTURE_ID
640 ,P_REPORT_DATE_FROM
641 ,P_REPORT_DATE_TO)
642 loop
643
644 hr_utility.set_location('org_structure_version_rec.date_from ='||org_structure_version_rec.date_from,420);
645 hr_utility.set_location('org_structure_version_rec.date_to ='||org_structure_version_rec.date_to,430);
646 if P_REPORT_DATE_FROM > org_structure_version_rec.date_from then
647 P_DATE_FROM := P_REPORT_DATE_FROM;
648 else
649 P_DATE_FROM :=org_structure_version_rec.date_from;
650 end if;
651
652 hr_utility.set_location('P_DATE_FROM='||P_DATE_FROM,440);
653
654 if P_REPORT_DATE_TO < org_structure_version_rec.date_to then
655 P_DATE_TO := P_REPORT_DATE_TO;
656 else
657 P_DATE_TO := org_structure_version_rec.date_to;
658 end if;
659
660 hr_utility.set_location('P_DATE_TO='||P_DATE_TO,450);
661
662 for org_structure_element_rec in get_org_structure_element
663 (org_structure_version_rec.org_structure_version_id
664 ,P_TOP_ORGANIZATION_ID)
665 loop
666
667
668 hr_utility.set_location('org_structure_version_rec.org_structure_version_id='||org_structure_version_rec.org_structure_version_id,460);
669 hr_utility.set_location('org_structure_element_rec.organization_id1='||org_structure_element_rec.organization_id1,470);
670
671
672 for org_rec in get_organizations
673 ( org_structure_version_rec.org_structure_version_id
674 -- P_ORG_STRUCTURE_VERSION_ID
675 , org_structure_element_rec.organization_id1
676 -- P_ORGANIZATION_ID
677 , P_ROLL_UP)
678 loop
679
680 -- Start of New Hires Story
681
682 hr_utility.set_location('org_rec.organization_id2='||org_rec.organization_id2,480);
683
684 for asg_rec in get_assignment_start_end
685 ( P_DATE_TO
686 , org_rec.organization_id2
687 , P_BUSINESS_GROUP_ID)
688
689 loop -- get_assignment_start_end
690
691 l_jobcatg := NULL;
692 l_abv := NULL;
693
694 hr_utility.set_location('asg_rec.assignment_id='||asg_rec.assignment_id,490);
695 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
696 ( p_ABV_formula_id => l_ABV_formula_id
697 , p_ABV => p_budget
698 , p_assignment_id => asg_rec.assignment_id
699 , p_effective_date => asg_rec.effective_start_date
700 , p_session_date => trunc(sysdate) );
701
702
703 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
704 (asg_rec.job_id
705 ,p_job_category);
706
707 HR_PERSON_FLEX_LOGIC.GetMovementCategory(
708 p_organization_id => org_rec.organization_id2
709 ,p_assignment_id => asg_rec.assignment_id
710 ,p_period_start_date => P_DATE_FROM
711 ,p_period_end_date => P_DATE_TO
712 ,p_movement_type => 'IN'
713 ,p_assignment_type => asg_rec.assignment_type
714 ,p_movement_category => l_movement_category
715 );
716
717 hr_utility.set_location('Movement Category 2 = '||l_movement_category,500);
718
719 if l_movement_category = 'NEW_HIRE'
720 then
721 if l_jobcatg = 'Y'
722 then
723 l_rev_nh := l_rev_nh + 1;
724 else
725 l_nonrev_nh := l_nonrev_nh + 1;
726 end if;
727 end if;
728
729 end loop; -- get_assignment_start_end
730
731 hr_utility.set_location('organization_id1 = '||org_structure_element_rec.organization_id1,510);
732
733 HQOrgData(org_structure_element_rec.organization_id1).rev_nh :=
734 HQOrgData(org_structure_element_rec.organization_id1).rev_nh + nvl(l_rev_nh,0);
735 HQOrgData(org_structure_element_rec.organization_id1).nonrev_nh :=
736 HQOrgData(org_structure_element_rec.organization_id1).nonrev_nh + nvl(l_nonrev_nh,0);
737
738 l_rev_nh := 0;
739 l_nonrev_nh := 0;
740
741 -- End of New Hires Story
742
743
744 l_rev_term :=0;
745 l_nonrev_term :=0;
746
747
748 hr_utility.set_location('org_rec.organization_id2='||org_rec.organization_id2,520);
749
750 for term_rec in get_terminations
751 ( P_DATE_FROM
752 , P_DATE_TO
753 , org_rec.organization_id2
754 , P_BUSINESS_GROUP_ID)
755
756 loop -- get_terminations
757
758 hr_utility.set_location('Organization ID2 = '||org_rec.organization_id2,530);
759
760 l_jobcatg := NULL;
761 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
762 (term_rec.job_id
763 ,p_job_category);
764
765 hr_utility.set_location('Job Categ = '||l_jobcatg,540);
766
767 if l_jobcatg = 'Y'
768 then
769 l_rev_term := l_rev_term + 1;
770
771 hr_utility.set_location('Rev Terms = '||l_rev_term,550);
772
773 else
774 l_nonrev_term := l_nonrev_term + 1;
775 hr_utility.set_location('NONRev Terms = '||l_nonrev_term,560);
776 end if;
777
778 end loop; -- get_terminations
779
780 hr_utility.set_location('Rev Terms1 = '||l_rev_term,570);
781 hr_utility.set_location('NONRev Terms1 = '||l_nonrev_term,580);
782
783 HQOrgData(org_structure_element_rec.organization_id1).rev_term :=
784 HQOrgData(org_structure_element_rec.organization_id1).rev_term + nvl(l_rev_term,0);
785 HQOrgData(org_structure_element_rec.organization_id1).nonrev_term :=
786 HQOrgData(org_structure_element_rec.organization_id1).nonrev_term + nvl(l_nonrev_term,0);
787
788 end loop; -- get_organizations
789
790 end loop; -- get_org_structure_element
791
792 end loop; -- get_org_structure_version
793
794 -- End of New Hires and Terminations Count
795
796 -- Start of Top Organization Values
797
798 l_rev_start_val :=0;
799 l_nonrev_start_val :=0;
800
801 if P_INCLUDE_TOP_ORG = 'Y' THEN
802
803 for asg_start_rec in get_assignment_start_end
804 (P_REPORT_DATE_FROM
805 ,P_TOP_ORGANIZATION_ID
806 ,P_BUSINESS_GROUP_ID
807 )
808 loop -- get_assignment_start_end
809
810 hr_utility.set_location('asg_start_rec.assignment_id='||asg_start_rec.assignment_id,590);
811
812 l_jobcatg := NULL;
813 l_abv := NULL;
814
815 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
816 ( p_ABV_formula_id => l_ABV_formula_id
817 , p_ABV => p_budget
818 , p_assignment_id => asg_start_rec.assignment_id
819 , p_effective_date => asg_start_rec.effective_start_date
820 , p_session_date => trunc(sysdate) );
821
822 hr_utility.set_location('l_abv='||l_abv,600);
823
824
825 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
826 (asg_start_rec.job_id
827 ,p_job_category);
828
829 if l_jobcatg = 'Y'
830 then
831
832 l_rev_start_val := l_rev_start_val + l_abv;
833 else
834
835 l_nonrev_start_val := l_nonrev_start_val + l_abv;
836 end if;
837
838 end loop; -- get_assignment_start_end
839
840 HQOrgData(P_TOP_ORGANIZATION_ID).rev_start_val :=
841 HQOrgData(P_TOP_ORGANIZATION_ID).rev_start_val+ l_rev_start_val;
842 HQOrgData(P_TOP_ORGANIZATION_ID).nonrev_start_val :=
843 HQOrgData(P_TOP_ORGANIZATION_ID).nonrev_start_val+l_nonrev_start_val;
844
845
846 l_rev_end_val :=0;
847 l_nonrev_end_val :=0;
848
849 for asg_end_rec in get_assignment_start_end
850 (P_REPORT_DATE_TO
851 ,P_TOP_ORGANIZATION_ID
852 ,P_BUSINESS_GROUP_ID
853 )
854 loop -- get_assignment_start_end
855
856 hr_utility.set_location('asg_end_rec.assignment_id='||asg_end_rec.assignment_id,610);
857
858 l_jobcatg := NULL;
859 l_abv := NULL;
860
861 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
862 ( p_ABV_formula_id => l_ABV_formula_id
863 , p_ABV => p_budget
864 , p_assignment_id => asg_end_rec.assignment_id
865 , p_effective_date => asg_end_rec.effective_start_date
866 , p_session_date => trunc(sysdate) );
867
868 hr_utility.set_location('l_abv='||l_abv,620);
869
870
871 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
872 (asg_end_rec.job_id
873 ,p_job_category);
874
875 if l_jobcatg = 'Y'
876 then
877
878 l_rev_end_val := l_rev_end_val + l_abv;
879 else
880
881 l_nonrev_end_val := l_nonrev_end_val + l_abv;
882 end if;
883
884 end loop; -- get_assignment_start_end
885
886 HQOrgData(P_TOP_ORGANIZATION_ID).rev_end_val :=
887 HQOrgData(P_TOP_ORGANIZATION_ID).rev_end_val+ l_rev_end_val;
888 HQOrgData(P_TOP_ORGANIZATION_ID).nonrev_end_val :=
889 HQOrgData(P_TOP_ORGANIZATION_ID).nonrev_end_val+l_nonrev_end_val;
890
891 -- Start of New Hires Story for Top Organization
892
893 for asg_rec in get_assignment_start_end
894 ( P_REPORT_DATE_TO
895 , P_TOP_ORGANIZATION_ID
896 , P_BUSINESS_GROUP_ID)
897
898 loop -- get_assignment_start_end
899
900 l_jobcatg := NULL;
901 l_abv := NULL;
902
903 hr_utility.set_location('asg_rec.assignment_id='||asg_rec.assignment_id,630);
904
905 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
906 ( p_ABV_formula_id => l_ABV_formula_id
907 , p_ABV => p_budget
908 , p_assignment_id => asg_rec.assignment_id
909 , p_effective_date => asg_rec.effective_start_date
910 , p_session_date => trunc(sysdate) );
911
912
913 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
914 (asg_rec.job_id
915 ,p_job_category);
916
917 HR_PERSON_FLEX_LOGIC.GetMovementCategory(
918 p_organization_id => P_TOP_ORGANIZATION_ID
919 ,p_assignment_id => asg_rec.assignment_id
920 ,p_period_start_date => P_REPORT_DATE_FROM
921 ,p_period_end_date => P_REPORT_DATE_TO
922 ,p_movement_type => 'IN'
923 ,p_assignment_type => asg_rec.assignment_type
924 ,p_movement_category => l_movement_category
925 );
926
927 hr_utility.set_location('Movement Category 2 = '||l_movement_category,640);
928
929 if l_movement_category = 'NEW_HIRE'
930 then
931 if l_jobcatg = 'Y'
932 then
933 l_rev_nh := l_rev_nh + 1;
934 else
935 l_nonrev_nh := l_nonrev_nh + 1;
936 end if;
937 end if;
938
939 end loop; -- get_assignment_start_end
940
941
942 HQOrgData(P_TOP_ORGANIZATION_ID).rev_nh :=
943 HQOrgData(P_TOP_ORGANIZATION_ID).rev_nh + nvl(l_rev_nh,0);
944 HQOrgData(P_TOP_ORGANIZATION_ID).nonrev_nh :=
945 HQOrgData(P_TOP_ORGANIZATION_ID).nonrev_nh + nvl(l_nonrev_nh,0);
946
947 -- Start of Terminations Story of Top Organization
948
949 l_rev_term :=0;
950 l_nonrev_term :=0;
951
952 for term_rec in get_terminations
953 ( P_REPORT_DATE_FROM
954 , P_REPORT_DATE_TO
955 , P_TOP_ORGANIZATION_ID
956 , P_BUSINESS_GROUP_ID)
957
958 loop -- get_terminations
959
960 l_jobcatg := NULL;
961 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
962 (term_rec.job_id
963 ,p_job_category);
964
965 hr_utility.set_location('Job Categ = '||l_jobcatg,650);
966
967 if l_jobcatg = 'Y'
968 then
969 l_rev_term := l_rev_term + 1;
970
971 hr_utility.set_location('Rev Terms = '||l_rev_term,660);
972
973 else
974 l_nonrev_term := l_nonrev_term + 1;
975 hr_utility.set_location('NONRev Terms = '||l_nonrev_term,670);
976 end if;
977
978 end loop; -- get_terminations
979
980 hr_utility.set_location('Rev Terms1 = '||l_rev_term,680);
981 hr_utility.set_location('NONRev Terms1 = '||l_nonrev_term,690);
982
983 HQOrgData(P_TOP_ORGANIZATION_ID).rev_term :=
984 HQOrgData(P_TOP_ORGANIZATION_ID).rev_term + nvl(l_rev_term,0);
985 HQOrgData(P_TOP_ORGANIZATION_ID).nonrev_term :=
986 HQOrgData(P_TOP_ORGANIZATION_ID).nonrev_term + nvl(l_nonrev_term,0);
987
988 end if;
989
990 -- End of Top Organization Values
991 -- Start of Totals
992
993 l_rev_start_val := 0;
994 l_rev_end_val := 0;
995 l_nonrev_start_val := 0;
996 l_nonrev_end_val := 0;
997 l_rev_nh := 0;
998 l_nonrev_nh := 0;
999 l_rev_term := 0;
1000 l_nonrev_term := 0;
1001
1002 for all_organizations_rec in get_all_organizations
1003 (P_ORGANIZATION_STRUCTURE_ID
1004 ,P_TOP_ORGANIZATION_ID
1005 ,P_REPORT_DATE_FROM
1006 ,P_REPORT_DATE_TO)
1007 loop
1008
1009 l_rev_start_val := l_rev_start_val
1010 + HQOrgData(all_organizations_rec.organization_id).rev_start_val;
1011 l_rev_end_val := l_rev_end_val
1012 + HQOrgData(all_organizations_rec.organization_id).rev_end_val;
1013 l_rev_nh := l_rev_nh
1014 + HQOrgData(all_organizations_rec.organization_id).rev_nh;
1015 l_rev_term := l_rev_term
1016 + HQOrgData(all_organizations_rec.organization_id).rev_term;
1017 l_nonrev_start_val := l_nonrev_start_val
1018 + HQOrgData(all_organizations_rec.organization_id).nonrev_start_val;
1019 l_nonrev_end_val := l_nonrev_end_val
1020 + HQOrgData(all_organizations_rec.organization_id).nonrev_end_val;
1021 l_nonrev_nh := l_nonrev_nh
1022 + HQOrgData(all_organizations_rec.organization_id).nonrev_nh;
1023 l_nonrev_term := l_nonrev_term
1024 + HQOrgData(all_organizations_rec.organization_id).nonrev_term;
1025
1026 hr_utility.set_location('Org='||all_organizations_rec.organization_id||
1027 '*RS='||HQOrgData(all_organizations_rec.organization_id).rev_start_val||
1028 '*RE='||HQOrgData(all_organizations_rec.organization_id).rev_end_val||
1029 '*RNC='||to_char(HQOrgData(all_organizations_rec.organization_id).rev_end_val
1030 -HQOrgData(all_organizations_rec.organization_id).rev_start_val)||
1031 '*RH='||HQOrgData(all_organizations_rec.organization_id).rev_nh||
1032 '*RT='||HQOrgData(all_organizations_rec.organization_id).rev_term||
1033 '*RON='||to_char(HQOrgData(all_organizations_rec.organization_id).rev_end_val
1034 -HQOrgData(all_organizations_rec.organization_id).rev_start_val
1035 -HQOrgData(all_organizations_rec.organization_id).rev_nh
1036 +HQOrgData(all_organizations_rec.organization_id).rev_term),700);
1037
1038 hr_utility.set_location('--------'||
1039 '*NS='||HQOrgData(all_organizations_rec.organization_id).nonrev_start_val||
1040 '*NE='||HQOrgData(all_organizations_rec.organization_id).nonrev_end_val||
1041 '*NNC='||(HQOrgData(all_organizations_rec.organization_id).nonrev_end_val
1042 -HQOrgData(all_organizations_rec.organization_id).nonrev_start_val)||
1043 '*NH='||HQOrgData(all_organizations_rec.organization_id).nonrev_nh||
1044 '*NT='||HQOrgData(all_organizations_rec.organization_id).nonrev_term||
1045 '*NON='||to_char(HQOrgData(all_organizations_rec.organization_id).nonrev_end_val
1046 -HQOrgData(all_organizations_rec.organization_id).nonrev_start_val
1047 -HQOrgData(all_organizations_rec.organization_id).nonrev_nh
1048 +HQOrgData(all_organizations_rec.organization_id).nonrev_term),800);
1049
1050 end loop;
1051
1052 HQOrgData(-1).rev_start_val := l_rev_start_val;
1053 HQOrgData(-1).rev_end_val := l_rev_end_val;
1054 HQOrgData(-1).rev_nh := l_rev_nh;
1055 HQOrgData(-1).rev_term := l_rev_term;
1056 HQOrgData(-1).nonrev_start_val := l_nonrev_start_val;
1057 HQOrgData(-1).nonrev_end_val := l_nonrev_end_val;
1058 HQOrgData(-1).nonrev_nh := l_nonrev_nh;
1059 HQOrgData(-1).nonrev_term := l_nonrev_term;
1060
1061 end populate_summary_table;
1062
1063 END HR_HEAD_COUNT_SUMMARY;