[Home] [Help]
PACKAGE BODY: APPS.HR_HEAD_COUNT
Source
1 PACKAGE BODY HR_HEAD_COUNT AS
2 /* $Header: perhdcnt.pkb 120.1 2007/10/29 11:50:42 asgugupt noship $ */
3
4 --
5 function get_rev_start_val(p_org_structure_element_id NUMBER) return NUMBER is
6 begin
7 return(HQOrgData(p_org_structure_element_id).rev_start_val);
8 exception
9 when others then
10 return (0);
11 end;
12 --
13 function get_nonrev_start_val(p_org_structure_element_id NUMBER) return NUMBER is
14 begin
15 return(HQOrgData(p_org_structure_element_id).nonrev_start_val);
16 exception
17 when others then
18 return (0);
19 end;
20 --
21 function get_rev_perm(p_org_structure_element_id NUMBER) return NUMBER is
22 begin
23 return(HQOrgData(p_org_structure_element_id).rev_perm);
24 exception
25 when others then
26 return (0);
27 end;
28 --
29 function get_nonrev_perm(p_org_structure_element_id NUMBER) return NUMBER is
30 begin
31 return(HQOrgData(p_org_structure_element_id).nonrev_perm);
32 exception
33 when others then
34 return (0);
35 end;
36 --
37 function get_rev_cont(p_org_structure_element_id NUMBER) return NUMBER is
38 begin
39 return(HQOrgData(p_org_structure_element_id).rev_cont);
40 exception
41 when others then
42 return (0);
43 end;
44 --
45 function get_nonrev_cont(p_org_structure_element_id NUMBER) return NUMBER is
46 begin
47 return(HQOrgData(p_org_structure_element_id).nonrev_cont);
48 exception
49 when others then
50 return (0);
51 end;
52 --
53 function get_rev_temp(p_org_structure_element_id NUMBER) return NUMBER is
54 begin
55 return(HQOrgData(p_org_structure_element_id).rev_temp);
56 exception
57 when others then
58 return (0);
59 end;
60 --
61 function get_nonrev_temp(p_org_structure_element_id NUMBER) return NUMBER is
62 begin
63 return(HQOrgData(p_org_structure_element_id).nonrev_temp);
64 exception
65 when others then
66 return (0);
67 end;
68 --
69 function get_rev_cur_nh(p_org_structure_element_id NUMBER) return NUMBER is
70 begin
71 return(HQOrgData(p_org_structure_element_id).rev_cur_nh);
72 exception
73 when others then
74 return (0);
75 end;
76 --
77 function get_nonrev_cur_nh(p_org_structure_element_id NUMBER) return NUMBER is
78 begin
79 return(HQOrgData(p_org_structure_element_id).nonrev_cur_nh);
80 exception
81 when others then
82 return (0);
83 end;
84 --
85 function get_rev_nh(p_org_structure_element_id NUMBER) return NUMBER is
86 begin
87 return(HQOrgData(p_org_structure_element_id).rev_nh);
88 exception
89 when others then
90 return (0);
91 end;
92 --
93 function get_nonrev_nh(p_org_structure_element_id NUMBER) return NUMBER is
94 begin
95 return(HQOrgData(p_org_structure_element_id).nonrev_nh);
96 exception
97 when others then
98 return (0);
99 end;
100 --
101 function get_rev_transfer_in(p_org_structure_element_id NUMBER) return NUMBER is
102 begin
103 return(HQOrgData(p_org_structure_element_id).rev_transfer_in);
104 exception
105 when others then
106 return (0);
107 end;
108 --
109 function get_nonrev_transfer_in(p_org_structure_element_id NUMBER) return NUMBER is
110 begin
111 return(HQOrgData(p_org_structure_element_id).nonrev_transfer_in);
112 exception
113 when others then
114 return (0);
115 end;
116 --
117 function get_rev_transfer_out(p_org_structure_element_id NUMBER) return NUMBER is
118 begin
119 return(HQOrgData(p_org_structure_element_id).rev_transfer_out);
120 exception
121 when others then
122 return (0);
123 end;
124 --
125 function get_nonrev_transfer_out(p_org_structure_element_id NUMBER) return NUMBER is
126 begin
127 return(HQOrgData(p_org_structure_element_id).nonrev_transfer_out);
128 exception
129 when others then
130 return (0);
131 end;
132 --
133 function get_rev_open_offers(p_org_structure_element_id NUMBER) return NUMBER is
134 begin
135 return(HQOrgData(p_org_structure_element_id).rev_open_offers);
136 exception
137 when others then
138 return (0);
139 end;
140 --
141 function get_nonrev_open_offers(p_org_structure_element_id NUMBER) return NUMBER is
142 begin
143 return(HQOrgData(p_org_structure_element_id).nonrev_open_offers);
144 exception
145 when others then
146 return (0);
147 end;
148 --
149 function get_rev_accepted_offers(p_org_structure_element_id NUMBER) return NUMBER is
150 begin
151 return(HQOrgData(p_org_structure_element_id).rev_accepted_offers);
152 exception
153 when others then
154 return (0);
155 end;
156 --
157 function get_nonrev_accepted_offers(p_org_structure_element_id NUMBER) return NUMBER is
158 begin
159 return(HQOrgData(p_org_structure_element_id).nonrev_accepted_offers);
160 exception
161 when others then
162 return (0);
163 end;
164 --
165 function get_rev_vacant_FTE(p_org_structure_element_id NUMBER) return NUMBER is
166 begin
167 return(HQOrgData(p_org_structure_element_id).rev_vacant_FTE);
168 exception
169 when others then
170 return (0);
171 end;
172 --
173 function get_nonrev_vacant_FTE(p_org_structure_element_id NUMBER) return NUMBER is
174 begin
175 return(HQOrgData(p_org_structure_element_id).nonrev_vacant_FTE);
176 exception
177 when others then
178 return (0);
179 end;
180 --
181 function get_rev_vol_term(p_org_structure_element_id NUMBER) return NUMBER is
182 begin
183 return(HQOrgData(p_org_structure_element_id).rev_vol_term);
184 exception
185 when others then
186 return (0);
187 end;
188 --
189 function get_nonrev_vol_term(p_org_structure_element_id NUMBER) return NUMBER is
190 begin
191 return(HQOrgData(p_org_structure_element_id).nonrev_vol_term);
192 exception
193 when others then
194 return (0);
195 end;
196 --
197 function get_rev_invol_term(p_org_structure_element_id NUMBER) return NUMBER is
198 begin
199 return(HQOrgData(p_org_structure_element_id).rev_invol_term);
200 exception
201 when others then
202 return (0);
203 end;
204 --
205 function get_nonrev_invol_term(p_org_structure_element_id NUMBER) return NUMBER is
206 begin
207 return(HQOrgData(p_org_structure_element_id).nonrev_invol_term);
208 exception
209 when others then
210 return (0);
211 end;
212 --
213 function get_rev_cur_term(p_org_structure_element_id NUMBER) return NUMBER is
214 begin
215 return(HQOrgData(p_org_structure_element_id).rev_cur_term);
216 exception
217 when others then
218 return (0);
219 end;
220 --
221 function get_nonrev_cur_term(p_org_structure_element_id NUMBER) return NUMBER is
222 begin
223 return(HQOrgData(p_org_structure_element_id).nonrev_cur_term);
224 exception
225 when others then
226 return (0);
227 end;
228 --
229 function get_rev_change(p_org_structure_element_id NUMBER) return NUMBER is
230 begin
231 return(HQOrgData(p_org_structure_element_id).rev_end_val-HQOrgData(p_org_structure_element_id).rev_start_val);
232 exception
233 when others then
234 return (0);
235 end;
236 --
237 function get_nonrev_change(p_org_structure_element_id NUMBER) return NUMBER is
238 begin
239 return(HQOrgData(p_org_structure_element_id).nonrev_end_val-HQOrgData(p_org_structure_element_id).nonrev_start_val);
240 exception
241 when others then
242 return (0);
243 end;
244 --
245 function get_rev_end_val(p_org_structure_element_id NUMBER) return NUMBER is
246 begin
247 return(HQOrgData(p_org_structure_element_id).rev_end_val);
248 exception
249 when others then
250 return (0);
251 end;
252 --
253 function get_nonrev_end_val(p_org_structure_element_id NUMBER) return NUMBER is
254 begin
255 return(HQOrgData(p_org_structure_element_id).nonrev_end_val);
256 exception
257 when others then
258 return (0);
259 end;
260 --
261 function get_rev_pct_change(p_org_structure_element_id NUMBER) return NUMBER is
262 begin
263 --Changes for bug 6124652 starts here
264 if (HQOrgData(p_org_structure_element_id).rev_start_val > 0) then
265 --Changes for bug 6124652 ends here
266 return(100*(HQOrgData(p_org_structure_element_id).rev_end_val-HQOrgData(p_org_structure_element_id).rev_start_val)/ HQOrgData(p_org_structure_element_id).rev_start_val);
267 --Changes for bug 6124652 starts here
268 else
269 return(100*(HQOrgData(p_org_structure_element_id).rev_end_val-HQOrgData(p_org_structure_element_id).rev_start_val));
270 end if;
271 --Changes for bug 6124652 ends here
272 exception
273 when zero_divide then
274 return (0);
275 when others then
276 return (0);
277 end;
278 --
279 function get_nonrev_pct_change(p_org_structure_element_id NUMBER) return NUMBER is
280 begin
281 --Changes for bug 6124652 starts here
282 if (HQOrgData(p_org_structure_element_id).nonrev_start_val > 0) then
283 --Changes for bug 6124652 ends here
284 return(100*(HQOrgData(p_org_structure_element_id).nonrev_end_val-HQOrgData(p_org_structure_element_id).nonrev_start_val)/ HQOrgData(p_org_structure_element_id).nonrev_start_val);
285 else
286 --Changes for bug 6124652 starts here
287 return(100*(HQOrgData(p_org_structure_element_id).nonrev_end_val-HQOrgData(p_org_structure_element_id).nonrev_start_val));
288 end if;
289 --Changes for bug 6124652 ends here
290 exception
291 when zero_divide then
292 return (0);
293 when others then
294 return (0);
295 end;
296 --
297 --
298
299 procedure populate_headcount_table
300 ( P_BUSINESS_GROUP_ID IN NUMBER
301 , P_TOP_ORGANIZATION_ID IN NUMBER
302 , P_ORGANIZATION_STRUCTURE_ID IN NUMBER
303 , P_BUDGET IN VARCHAR2
304 , P_ROLL_UP IN VARCHAR2
305 , P_REPORT_DATE_FROM IN DATE
306 , P_REPORT_DATE_TO IN DATE
307 , P_REPORT_DATE IN DATE
308 , P_INCLUDE_ASG_TYPE IN VARCHAR2
309 , P_INCLUDE_TOP_ORG IN VARCHAR2
310 , P_WORKER_TYPE IN VARCHAR2
311 , P_DAYS_PRIOR_TO_END_DATE IN NUMBER
312 , P_JOB_CATEGORY IN VARCHAR2 default 'RG'
313 )
314 is
315
316 cursor get_org_structure_version
317 (P_ORGANIZATION_STRUCTURE_ID NUMBER
318 ,P_REPORT_DATE_FROM DATE
319 ,P_REPORT_DATE_TO DATE)
320 is
321 select posv.org_structure_version_id
322 ,posv.date_from date_from
323 ,nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY')) date_to
324 from per_org_structure_versions posv
325 where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
326 and (P_REPORT_DATE_FROM between posv.date_from and
327 nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY'))
328 or posv.date_from between P_REPORT_DATE_FROM and P_REPORT_DATE_TO)
329 order by posv.org_structure_version_id
330 ,posv.date_from
331 ,posv.date_to;
332
333 cursor get_org_structure_element
334 (P_ORG_STRUCTURE_VERSION_ID NUMBER
335 ,P_TOP_ORGANIZATION_ID NUMBER)
336 is
337 select ose.org_structure_element_id
338 ,ose.organization_id_child organization_id
339 from per_org_structure_elements ose
340 where ose.org_structure_version_id +0 = P_ORG_STRUCTURE_VERSION_ID
341 and ose.organization_id_parent = P_TOP_ORGANIZATION_ID
342 order by ose.organization_id_child;
343
344 cursor get_organizations
345 ( P_ORG_STRUCTURE_VERSION_ID NUMBER
346 , P_ORGANIZATION_ID NUMBER
347 , P_ROLL_UP VARCHAR2)
348 is
349 select ose.organization_id_child organization_id
350 from per_org_structure_elements ose
351 where ose.org_structure_version_id +0 = P_ORG_STRUCTURE_VERSION_ID
352 and P_ROLL_UP = 'Y'
353 connect by prior ose.organization_id_child = ose.organization_id_parent
354 and ose.org_structure_version_id = P_ORG_STRUCTURE_VERSION_ID
355 start with ose.organization_id_parent = P_ORGANIZATION_ID
356 and ose.org_structure_version_id = P_ORG_STRUCTURE_VERSION_ID
357 UNION
358 select P_ORGANIZATION_ID organization_id
359 from dual
360 where P_ROLL_UP = 'Y'
361 UNION
362 select P_ORGANIZATION_ID organization_id
363 from dual
364 where P_ROLL_UP = 'N';
365
366 --bug 6124652 starts here
367 cursor get_assignment_start_end_fte
368 (P_EFFECTIVE_DATE DATE
369 ,P_ORGANIZATION_ID_CHILD NUMBER
370 ,P_BUSINESS_GROUP_ID NUMBER
371 ,P_WORKER_TYPE VARCHAR2
372 ,P_INCLUDE_ASG_TYPE VARCHAR2
373 )
374 is
375 select paf.assignment_id
376 ,paf.job_id
377 ,paf.effective_start_date
378 ,paf.assignment_type
379 from per_all_assignments_f paf
380 ,per_assignment_status_types past
381 where paf.organization_id = P_ORGANIZATION_ID_CHILD
382 and P_EFFECTIVE_DATE between paf.effective_start_date
383 and paf.effective_end_date
384 and paf.assignment_status_type_id = past.assignment_status_type_id
385 and (
386 (P_WORKER_TYPE IN ('E','B')
387 and paf.assignment_type = 'E'
388 and (
389 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
390 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
391 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
392 'SUSP_ASSIGN'))
393 )
394 )
395 OR
396 (P_WORKER_TYPE IN ('C','B')
397 and paf.assignment_type = 'C'
398 and (
399 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
400 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
401 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
402 'SUSP_CWK_ASG'))
403 )
404 )
405 );
406 --bug 6124652 ends here
407 cursor get_assignment_start_end
408 (--bug 6124652 starts here
409 --P_EFFECTIVE_DATE DATE
410 P_START_DATE DATE
411 ,P_END_DATE DATE
412 --bug 6124652 ends here
413 ,P_ORGANIZATION_ID_CHILD NUMBER
414 ,P_BUSINESS_GROUP_ID NUMBER
415 ,P_WORKER_TYPE VARCHAR2
416 ,P_INCLUDE_ASG_TYPE VARCHAR2
417 )
418 is
419 select paf.assignment_id
420 ,paf.job_id
421 ,paf.effective_start_date
422 --bug 6124652 starts here
423 ,paf.effective_end_date
424 --bug 6124652 ends here
425 ,paf.assignment_type
426 from per_all_assignments_f paf
427 ,per_assignment_status_types past
428 where paf.organization_id = P_ORGANIZATION_ID_CHILD
429 --bug 6124652 starts here
430 -- and P_EFFECTIVE_DATE between paf.effective_start_date
431 -- and paf.effective_end_date
432 and ((P_START_DATE <= paf.effective_start_date
433 and paf.effective_end_date <=P_END_DATE)
434 or( P_START_DATE between paf.effective_start_date
435 and paf.effective_end_date)
436 or( P_END_DATE between paf.effective_start_date
437 and paf.effective_end_date)
438 )
439 --bug 6124652 ends here
440 and paf.assignment_status_type_id = past.assignment_status_type_id
441 and (
442 (P_WORKER_TYPE IN ('E','B')
443 and paf.assignment_type = 'E'
444 and (
445 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
446 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
447 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
448 'SUSP_ASSIGN'))
449 )
450 )
451 OR
452 (P_WORKER_TYPE IN ('C','B')
453 and paf.assignment_type = 'C'
454 and (
455 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
456 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
457 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
458 'SUSP_CWK_ASG'))
459 )
460 )
461 );
462
463
464 cursor get_assignment
465 ( P_DATE_FROM DATE
466 , P_DATE_TO DATE
467 , P_ORGANIZATION_ID_CHILD NUMBER
468 , P_BUSINESS_GROUP_ID NUMBER
469 , P_WORKER_TYPE VARCHAR2
470 , P_INCLUDE_ASG_TYPE VARCHAR2
471 )
472 is
473 select paf.assignment_id
474 ,paf.job_id
475 ,paf.effective_start_date
476 ,paf.assignment_type
477 from per_all_assignments_f paf
478 ,per_assignment_status_types past
479 where paf.organization_id = P_ORGANIZATION_ID_CHILD
480 --Bug 6124652 starts here
481 -- and paf.effective_start_date <= P_DATE_FROM
482 and P_DATE_FROM between paf.effective_start_date
483 and paf.effective_end_date
484 --Bug 6124652 ends here
485 and paf.assignment_status_type_id = past.assignment_status_type_id
486 and P_DATE_TO <= (select max(paf1.effective_end_date)
487 from per_all_assignments_f paf1
488 ,per_assignment_status_types past1
489 where paf.assignment_id = paf1.assignment_id
490 and paf1.assignment_status_type_id = past1.assignment_status_type_id
491 and past.per_system_status = past1.per_system_status)
492 and (
493 (P_WORKER_TYPE IN ('E','B')
494 and paf.assignment_type = 'E'
495 and (
496 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_ASSIGN')
497 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_ASSIGN')
498 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_ASSIGN',
499 'SUSP_ASSIGN'))
500 )
501 )
502 OR
503 (P_WORKER_TYPE IN ('C','B')
504 and paf.assignment_type = 'C'
505 and (
506 (P_INCLUDE_ASG_TYPE = 'A' and past.per_system_status = 'ACTIVE_CWK')
507 OR (P_INCLUDE_ASG_TYPE = 'S' and past.per_system_status = 'SUSP_CWK_ASG')
508 OR (P_INCLUDE_ASG_TYPE = 'B' and past.per_system_status IN ('ACTIVE_CWK',
509 'SUSP_CWK_ASG'))
510 )
511 )
512 );
513
514 cursor get_open_offers
515 ( P_DATE_FROM DATE
516 , P_DATE_TO DATE
517 , P_ORGANIZATION_ID_CHILD NUMBER
518 , P_BUSINESS_GROUP_ID NUMBER
519 )
520 is
521 select paf.assignment_id
522 ,paf.job_id
523 ,paf.effective_start_date
524 from per_all_assignments_f paf
525 ,per_assignment_status_types past
526 where paf.organization_id = p_organization_id_child
527 -- and paf.business_group_id = P_BUSINESS_GROUP_ID
528 and paf.assignment_type = 'A'
529 and paf.effective_start_date between P_DATE_FROM and P_DATE_TO
530 and paf.assignment_status_type_id = past.assignment_status_type_id
531 and past.per_system_status = 'OFFER';
532
533 cursor get_accepted_offers
534 ( P_DATE_FROM DATE
535 , P_DATE_TO DATE
536 , P_ORGANIZATION_ID_CHILD NUMBER
537 , P_BUSINESS_GROUP_ID NUMBER
538 )
539 is
540 select paf.assignment_id
541 ,paf.job_id
542 ,paf.effective_start_date
543 from per_all_assignments_f paf,
544 per_assignment_status_types past
545 where paf.organization_id = p_organization_id_child
546 -- and paf.business_group_id = P_BUSINESS_GROUP_ID
547 and paf.assignment_type = 'A'
548 and paf.effective_start_date between P_DATE_FROM and P_DATE_TO
549 and paf.assignment_status_type_id = past.assignment_status_type_id
550 and past.per_system_status = 'ACCEPTED';
551
552 cursor get_requisitions
553 (P_BUSINESS_GROUP_ID NUMBER,
554 P_ORGANIZATION_ID_CHILD NUMBER,
555 P_BUDGET VARCHAR2,
556 P_DATE_TO DATE)
557 is
558 select vac.job_id,
559 vac.BUDGET_MEASUREMENT_VALUE,
560 vac.NUMBER_OF_OPENINGS
561 from per_vacancies vac
562 ,per_requisitions req
563 where vac.organization_id = P_ORGANIZATION_ID_CHILD
564 --and vac.business_group_id = P_BUSINESS_GROUP_ID
565 and vac.BUDGET_MEASUREMENT_TYPE = P_BUDGET
566 and vac.REQUISITION_ID = req.REQUISITION_ID
567 and req.date_from < P_DATE_TO
568 and nvl(req.date_to,to_date('31/12/4712','DD/MM/YYYY'))
569 > P_DATE_TO;
570
571 cursor get_terminations
572 ( P_DATE_FROM DATE
573 , P_DATE_TO DATE
574 , P_ORGANIZATION_ID_CHILD NUMBER
575 , P_BUSINESS_GROUP_ID NUMBER
576 )
577 is
578 select pos.leaving_reason
579 ,pos.actual_termination_date
580 ,paf.job_id
581 ,paf.assignment_id
582 from per_periods_of_service pos
583 ,per_all_assignments_f paf
584 where pos.date_start <= P_DATE_FROM
585 and pos.actual_termination_date is not null
586 and pos.actual_termination_date between P_DATE_FROM
587 and P_DATE_TO
588 and pos.period_of_service_id = paf.period_of_service_id
589 and paf.effective_end_date = pos.actual_termination_date
590 and paf.organization_id = P_ORGANIZATION_ID_CHILD
591 order by paf.assignment_id ;
592
593
594 cursor get_cur_terminations
595 ( P_DATE_FROM DATE
596 , P_CUR_DATE_FROM DATE
597 , P_CUR_DATE_TO DATE
598 , P_ORGANIZATION_ID_CHILD NUMBER
599 , P_BUSINESS_GROUP_ID NUMBER
600 )
601 is
602 select pos.leaving_reason
603 ,pos.actual_termination_date
604 ,paf.job_id
605 ,paf.assignment_id
606 from per_periods_of_service pos
607 ,per_all_assignments_f paf
608 where pos.date_start <= P_DATE_FROM
609 and pos.actual_termination_date is not null
610 and pos.actual_termination_date between
611 P_CUR_DATE_FROM and P_CUR_DATE_TO
612 and pos.period_of_service_id = paf.period_of_service_id
613 and paf.effective_end_date = pos.actual_termination_date
614 and paf.organization_id = P_ORGANIZATION_ID_CHILD
615 order by paf.assignment_id ;
616
617 cursor get_terminations_cwk
618 ( P_DATE_FROM DATE
619 , P_DATE_TO DATE
620 , P_ORGANIZATION_ID_CHILD NUMBER
621 , P_BUSINESS_GROUP_ID NUMBER
622 )
623 is
624 select pop.termination_reason
625 ,pop.actual_termination_date
626 ,paf.job_id
627 ,paf.assignment_id
628 from per_periods_of_placement pop
629 ,per_all_assignments_f paf
630 where pop.date_start <= P_DATE_FROM
631 and pop.actual_termination_date is not null
632 and pop.actual_termination_date between P_DATE_FROM
633 and P_DATE_TO
634 and pop.date_start = paf.period_of_placement_date_start
635 and pop.person_id = paf.person_id
636 and paf.effective_end_date = pop.actual_termination_date
637 and paf.assignment_type = 'C'
638 and paf.organization_id = P_ORGANIZATION_ID_CHILD
639 order by paf.assignment_id;
640
641
642 cursor get_cur_terminations_cwk
643 ( P_DATE_FROM DATE
644 , P_CUR_DATE_FROM DATE
645 , P_CUR_DATE_TO DATE
646 , P_ORGANIZATION_ID_CHILD NUMBER
647 , P_BUSINESS_GROUP_ID NUMBER
648 )
649 is
650 select pop.termination_reason
651 ,pop.actual_termination_date
652 ,paf.job_id
653 ,paf.assignment_id
654 from per_periods_of_placement pop
655 ,per_all_assignments_f paf
656 where pop.date_start <= P_DATE_FROM
657 and pop.actual_termination_date is not null
658 and pop.actual_termination_date between P_CUR_DATE_FROM
659 and P_CUR_DATE_TO
660 and pop.date_start = paf.period_of_placement_date_start
661 and pop.person_id = paf.person_id
662 and paf.effective_end_date = pop.actual_termination_date
663 and paf.assignment_type = 'C'
664 and paf.organization_id = P_ORGANIZATION_ID_CHILD
665 order by paf.assignment_id;
666
667 /*
668 cursor c_get_PerType_formula
669 (p_business_group_id NUMBER )
670 is
671 select formula_id
672 from ff_formulas_f
673 where p_business_group_id = business_group_id+0
674 and trunc(sysdate) between effective_start_date and effective_end_date
675 and formula_name = 'HR_PERSON_TYPE'
676 and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
677
678 cursor c_get_tmplt_PerType_formula is
679 select formula_id
680 from ff_formulas_f
681 where business_group_id+0 is null
682 and trunc(sysdate) between effective_start_date and effective_end_date
683 and formula_name = 'HR_PERSON_TYPE_TEMPLATE'
684 and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
685
686 cursor c_get_term_formula
687 (p_business_group_id NUMBER )
688 is
689 select formula_id
690 from ff_formulas_f
691 where business_group_id+0 = p_business_group_id
692 and trunc(sysdate) between effective_start_date and effective_end_date
693 and formula_name = 'HR_MOVE_TYPE'
694 and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
695
696 cursor c_get_tmplt_term_formula is
697 select formula_id
698 from ff_formulas_f
699 where business_group_id+0 is null
700 and trunc(sysdate) between effective_start_date and effective_end_date
701 and formula_name = 'HR_MOVE_TYPE_TEMPLATE'
702 and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
703 */
704 cursor c_get_ABV_formula
705 ( p_business_group_id NUMBER )
706 is
707 select formula_id
708 from ff_formulas_f
709 where p_business_group_id = business_group_id+0
710 and trunc(sysdate) between effective_start_date and effective_end_date
711 and formula_name = 'BUDGET_'||p_budget
712 and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Quickpaint');
713
714
715 cursor c_get_tmplt_ABV_formula is
716 select formula_id
717 from ff_formulas_f
718 where business_group_id+0 is null
719 and trunc(sysdate) between effective_start_date and effective_end_date
720 and formula_name = 'TEMPLATE_'||p_budget
721 and formula_type_id = HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Quickpaint');
722
723 cursor c_get_all_orgs
724 (P_ORGANIZATION_STRUCTURE_ID NUMBER
725 ,P_TOP_ORGANIZATION_ID NUMBER
726 ,P_REPORT_DATE_FROM DATE
727 ,P_REPORT_DATE_TO DATE)
728 is
729 select
730 pose.org_structure_version_id
731 ,pose.org_structure_element_id
732 ,pose.organization_id_child
733 ,posv1.version_number
734 ,greatest(posv1.date_from,P_REPORT_DATE_FROM) date_from
735 ,least(nvl(posv1.date_to,to_date('31/12/4712','DD/MM/YYYY')),P_REPORT_DATE_TO) date_to
736 from per_org_structure_elements pose
737 ,per_org_structure_versions posv1
738 where pose.organization_id_parent = P_TOP_ORGANIZATION_ID
739 and pose.org_structure_version_id = posv1.org_structure_version_id
740 and pose.org_structure_version_id in (
741 select posv.org_structure_version_id
742 from per_org_structure_versions posv
743 where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
744 and (P_REPORT_DATE_FROM between posv.date_from and
745 nvl(posv.date_to, P_REPORT_DATE_TO)
746 or posv.date_from between P_REPORT_DATE_FROM and P_REPORT_DATE_TO))
747 order by pose.org_structure_version_id
748 ,pose.organization_id_child;
749
750 cursor c_get_top_orgs
751 (P_ORGANIZATION_STRUCTURE_ID NUMBER
752 ,P_TOP_ORGANIZATION_ID NUMBER
753 ,P_REPORT_DATE_FROM DATE
754 ,P_REPORT_DATE_TO DATE)
755 is
756 select posv.org_structure_version_id
757 ,1-(posv.org_structure_version_id+P_TOP_ORGANIZATION_ID)
758 org_structure_element_id
759 ,P_TOP_ORGANIZATION_ID organization_id_child
760 ,posv.version_number
761 ,greatest(posv.date_from,P_REPORT_DATE_FROM) date_from
762 ,least(nvl(posv.date_to,to_date('31/12/4712','DD/MM/YYYY')),P_REPORT_DATE_TO) date_to
763 from per_org_structure_versions posv
764 where posv.organization_structure_id = P_ORGANIZATION_STRUCTURE_ID
765 and (P_REPORT_DATE_FROM between posv.date_from and
766 nvl(posv.date_to, P_REPORT_DATE_TO)
767 or posv.date_from between P_REPORT_DATE_FROM and P_REPORT_DATE_TO)
768 order by posv.org_structure_version_id;
769
770 cursor c_cwk_profile IS
771 select PARAMETER_VALUE
772 from pay_action_parameters
773 where PARAMETER_NAME = 'HR_HEADCOUNT_FOR_CWK';
774
775 l_AsgWorkerType_formula_id number;
776 l_ABV_formula_id number;
777 l_term_formula_id number;
778 l_cwk_term_formula_id number;
779 l_ABV number;
780
781 l_cur_days number :=0;
782 l_rev_start_val number := 0;
783 l_rev_end_val number := 0;
784 l_nonrev_start_val number := 0;
785 l_nonrev_end_val number := 0;
786 l_rev_perm number := 0;
787 l_nonrev_perm number := 0;
788 l_rev_temp number := 0;
789 l_nonrev_temp number := 0;
790 l_rev_cont number := 0;
791 l_nonrev_cont number := 0;
792 l_rev_transfer_in number := 0;
793 l_nonrev_transfer_in number := 0;
794 l_rev_transfer_out number := 0;
795 l_nonrev_transfer_out number := 0;
796 l_rev_nh number := 0;
797 l_nonrev_nh number := 0;
798 l_rev_cur_nh number := 0;
799 l_nonrev_cur_nh number := 0;
800 l_rev_open_offers number := 0;
801 l_rev_accepted_offers number := 0;
802 l_nonrev_open_offers number := 0;
803 l_nonrev_accepted_offers number := 0;
804 l_rev_vol_term number := 0;
805 l_nonrev_vol_term number := 0;
806 l_rev_invol_term number := 0;
807 l_nonrev_invol_term number := 0;
808 l_rev_cur_term number := 0;
809 l_nonrev_cur_term number := 0;
810 l_vacant_FTE number := 0;
811 l_rev_vacant_FTE number := 0;
812 l_nonrev_vacant_FTE number := 0;
813
814 l_jobcatg varchar2(1);
815 l_cur_nh varchar2(1);
816 l_pertype varchar2(10);
817 l_termtype varchar2(1);
818 l_cwk_termtype varchar2(1);
819 l_budget varchar2(30);
820 l_movement_category varchar2(30);
821 l_cwk_profile varchar2(1);
822
823 P_DATE_FROM date;
824 P_DATE_TO date;
825 l_cur_date_from date;
826 l_cur_date_to date;
827
828
829 begin
830
831 l_cwk_profile := HR_PERSON_FLEX_LOGIC.HeadCountForCWK;
832
833 -- Look for User Defined HR_PERSON_TYPE Formula
834
835 l_AsgWorkerType_formula_id := HR_PERSON_FLEX_LOGIC.GetFormulaID
836 (p_business_group_id
837 ,'HR_PERSON_TYPE'
838 ,'Oracle Payroll');
839 /*
840
841 open c_get_PerType_formula(p_business_group_id);
842 fetch c_get_PerType_formula into l_AsgWorkerType_formula_id;
843
844 hr_utility.set_location('l_AsgWorkerType_formula_id = '|| l_AsgWorkerType_formula_id,10);
845
846 if (c_get_PerType_formula%notfound)
847 then
848 close c_get_PerType_formula;
849
850 hr_utility.set_location('Formula - HR_PERSON_TYPE does not exist.',15);
851
852 -- If User Defined HR_PERSON_TYPE formula does not exist,
853 -- look for seeded HR_PERSON_TYPE_TEMPLATE formula
854
855 open c_get_tmplt_PerType_formula;
856 fetch c_get_tmplt_PerType_formula into l_AsgWorkerType_formula_id;
857
858 hr_utility.set_location('Template_AsgWorkerType_formula_id = '|| l_AsgWorkerType_formula_id,20);
859
860 if (c_get_tmplt_PerType_formula%notfound)
861 then
862 close c_get_tmplt_PerType_formula;
863
864 -- Set to null so that we can calculate values differently later
865
866 l_AsgWorkerType_formula_id := null;
867
868 hr_utility.set_location('Formula - HR_PERSON_TYPE_TEMPLATE does not exist.',20);
869
870 else
871
872 close c_get_tmplt_PerType_formula;
873
874 end if;
875
876 else
877
878 close c_get_PerType_formula;
879
880 end if;
881
882 */
883
884 -- Look for user defined BUDGET_FTE/HEAD Formula
885
886 open c_get_ABV_formula(p_business_group_id);
887 fetch c_get_ABV_formula into l_ABV_formula_id;
888
889 hr_utility.set_location('ABV Formula ID = '||l_ABV_formula_id,30);
890
891 if (c_get_ABV_formula%notfound)
892 then
893 close c_get_ABV_formula;
894
895 hr_utility.set_location('User Defined Formula - BUDGET_FTE/HEAD does not exist.',35);
896
897 -- If User Defined BUDGET_FTE/HEAD formula does not exist,
898 -- look for seeded TEMPLATE_FTE/HEAD formula
899
900 open c_get_tmplt_ABV_formula;
901 fetch c_get_tmplt_ABV_formula into l_ABV_formula_id;
902
903 hr_utility.set_location('Template ABV Formula = '||l_ABV_formula_id,40);
904
905 if (c_get_tmplt_ABV_formula%notfound)
906 then
907 close c_get_tmplt_ABV_formula;
908
909 hr_utility.set_location('Seeded Formula - TEMPLATE_FTE/HEAD does not exist.',45);
910 -- Set to null so that we can calculate values differently later
911
912 l_ABV_formula_id := null;
913
914 else
915
916 close c_get_tmplt_ABV_formula;
917
918 end if;
919
920 else
921
922 close c_get_ABV_formula;
923
924 end if;
925
926
927 -- Look for User Defined formula HR_MOVE_TYPE
928
929 l_term_formula_id :=
930 HR_PERSON_FLEX_LOGIC.GetFormulaID
931 (p_business_group_id
932 ,'HR_MOVE_TYPE'
933 ,'Oracle Payroll');
934
935
936 l_cwk_term_formula_id :=
937 HR_PERSON_FLEX_LOGIC.GetFormulaID
938 (p_business_group_id
939 ,'HR_CWK_MOVE_TYPE'
940 ,'Oracle Payroll');
941
942 /*
943 open c_get_term_formula(p_business_group_id);
944 fetch c_get_term_formula into l_term_formula_id;
945
946 hr_utility.set_location('Term Formula ID = '|| l_term_formula_id,50);
947
948 if (c_get_term_formula%notfound)
949 then
950 close c_get_term_formula;
951
952 hr_utility.set_location('User Defined Formula - HR_MOVE_TYPE does not exist.',55);
953
954 -- If User Defined HR_MOVE_TYPE formula does not exist,
955 -- look for seeded HR_MOVE_TYPE_TEMPLATE formula
956
957 open c_get_tmplt_term_formula;
958 fetch c_get_tmplt_term_formula into l_term_formula_id;
959
960 hr_utility.set_location('Term Template Formula ID = '|| l_term_formula_id,60);
961
962 if (c_get_tmplt_term_formula%notfound)
963 then
964 close c_get_tmplt_term_formula;
965
966 hr_utility.set_location('User Defined Formula - HR_MOVE_TYPE_TEMPLATE does not exist.',65);
967
968 -- Set to null so that we can calculate values differently later
969 l_term_formula_id := null;
970 else
971
972 close c_get_tmplt_term_formula;
973
974 end if;
975
976 else
977 close c_get_term_formula;
978
979 end if;
980 */
981 hr_utility.set_location('P_REPORT_DATE_FROM='||P_REPORT_DATE_FROM,70);
982
983 for osv_rec in get_org_structure_version
984 (P_ORGANIZATION_STRUCTURE_ID
985 ,P_REPORT_DATE_FROM
986 ,P_REPORT_DATE_TO)
987
988 loop
989
990 hr_utility.set_location('osv_rec.date_from ='||osv_rec.date_from,90);
991 hr_utility.set_location('osv_rec.date_to ='||osv_rec.date_to,100);
992
993 if P_REPORT_DATE_FROM > osv_rec.date_from then
994 P_DATE_FROM := P_REPORT_DATE_FROM;
995 else
996 P_DATE_FROM :=osv_rec.date_from;
997 end if;
998
999 hr_utility.set_location('P_DATE_FROM='||P_DATE_FROM,110);
1000
1001 if P_REPORT_DATE_TO < osv_rec.date_to then
1002 P_DATE_TO := P_REPORT_DATE_TO;
1003 else
1004 P_DATE_TO := osv_rec.date_to;
1005 end if;
1006
1007 l_cur_date_to := P_DATE_TO;
1008
1009 l_cur_date_from := l_cur_date_to - P_DAYS_PRIOR_TO_END_DATE;
1010
1011 if l_cur_date_from < osv_rec.date_from then
1012 l_cur_date_from := osv_rec.date_from;
1013 end if;
1014
1015 hr_utility.set_location('l_cur_date_from ='||l_cur_date_from,118);
1016 hr_utility.set_location('l_cur_date_to ='||l_cur_date_to,119);
1017
1018 hr_utility.set_location('P_DATE_TO ='||P_DATE_TO,120);
1019
1020 for ose_rec in get_org_structure_element
1021 (osv_rec.org_structure_version_id
1022 ,P_TOP_ORGANIZATION_ID)
1023 loop
1024
1025 -- Initializing the columns values
1026
1027 HQOrgData(ose_rec.org_structure_element_id).rev_start_val := 0;
1028 HQOrgData(ose_rec.org_structure_element_id).rev_end_val := 0;
1029 HQOrgData(ose_rec.org_structure_element_id).nonrev_start_val := 0;
1030 HQOrgData(ose_rec.org_structure_element_id).nonrev_end_val := 0;
1031 HQOrgData(ose_rec.org_structure_element_id).rev_perm := 0;
1032 HQOrgData(ose_rec.org_structure_element_id).nonrev_perm := 0;
1033 HQOrgData(ose_rec.org_structure_element_id).rev_cont := 0;
1034 HQOrgData(ose_rec.org_structure_element_id).nonrev_cont := 0;
1035 HQOrgData(ose_rec.org_structure_element_id).rev_temp := 0;
1036 HQOrgData(ose_rec.org_structure_element_id).nonrev_temp := 0;
1037 HQOrgData(ose_rec.org_structure_element_id).rev_transfer_in := 0;
1038 HQOrgData(ose_rec.org_structure_element_id).nonrev_transfer_in := 0;
1039 HQOrgData(ose_rec.org_structure_element_id).rev_transfer_out := 0;
1040 HQOrgData(ose_rec.org_structure_element_id).nonrev_transfer_out := 0;
1041 HQOrgData(ose_rec.org_structure_element_id).rev_nh := 0;
1042 HQOrgData(ose_rec.org_structure_element_id).nonrev_nh := 0;
1043 HQOrgData(ose_rec.org_structure_element_id).rev_cur_nh := 0;
1044 HQOrgData(ose_rec.org_structure_element_id).nonrev_cur_nh := 0;
1045 HQOrgData(ose_rec.org_structure_element_id).rev_vacant_FTE := 0;
1046 HQOrgData(ose_rec.org_structure_element_id).nonrev_vacant_FTE := 0;
1047 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term := 0;
1048 HQOrgData(ose_rec.org_structure_element_id).nonrev_vol_term := 0;
1049 HQOrgData(ose_rec.org_structure_element_id).rev_invol_term := 0;
1050 HQOrgData(ose_rec.org_structure_element_id).nonrev_invol_term := 0;
1051 HQOrgData(ose_rec.org_structure_element_id).rev_cur_term := 0;
1052 HQOrgData(ose_rec.org_structure_element_id).nonrev_cur_term := 0;
1053
1054
1055 hr_utility.set_location('osv_rec.org_structure_version_id='||osv_rec.org_structure_version_id,130);
1056 hr_utility.set_location('ose_rec.organization_id ='||ose_rec.organization_id,140);
1057
1058 for org_rec in get_organizations
1059 (osv_rec.org_structure_version_id -- P_ORG_STRUCTURE_VERSION_ID
1060 ,ose_rec.organization_id -- P_ORGANIZATION_ID
1061 ,P_ROLL_UP)
1062 loop
1063
1064 l_rev_start_val :=0;
1065 l_rev_end_val :=0;
1066 l_nonrev_start_val :=0;
1067 l_nonrev_end_val :=0;
1068 l_rev_transfer_out :=0;
1069 l_nonrev_transfer_out:=0;
1070
1071 hr_utility.set_location('org_rec.organization_id='||org_rec.organization_id,150);
1072
1073 --changes for bug 6124652 starts here
1074 for asg_rec in get_assignment_start_end_fte
1075 (P_DATE_FROM
1076 ,org_rec.organization_id
1077 ,P_BUSINESS_GROUP_ID
1078 ,P_WORKER_TYPE
1079 ,P_INCLUDE_ASG_TYPE)
1080
1081 loop
1082
1083 l_jobcatg := NULL;
1084 l_abv := NULL;
1085
1086 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1087 ( p_ABV_formula_id => l_ABV_formula_id
1088 , p_ABV => p_budget
1089 , p_assignment_id => asg_rec.assignment_id
1090 , p_effective_date => asg_rec.effective_start_date
1091 , p_session_date => trunc(sysdate) );
1092
1093
1094 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1095 (asg_rec.job_id
1096 ,p_job_category);
1097
1098
1099 if l_jobcatg = 'Y'
1100 then
1101 l_rev_start_val := l_rev_start_val + l_abv;
1102 else
1103 l_nonrev_start_val := l_nonrev_start_val + l_abv;
1104 end if;
1105 end loop;
1106 --changes for bug 6124652 ends here
1107
1108 for asg_rec in get_assignment_start_end
1109 (P_DATE_FROM,
1110 --bug 6124652 starts here
1111 P_DATE_TO
1112 --bug 6124652 ends here
1113 ,org_rec.organization_id
1114 ,P_BUSINESS_GROUP_ID
1115 ,P_WORKER_TYPE
1116 ,P_INCLUDE_ASG_TYPE)
1117
1118 loop
1119
1120 l_jobcatg := NULL;
1121 l_abv := NULL;
1122
1123 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1124 ( p_ABV_formula_id => l_ABV_formula_id
1125 , p_ABV => p_budget
1126 , p_assignment_id => asg_rec.assignment_id
1127 , p_effective_date => asg_rec.effective_start_date
1128 , p_session_date => trunc(sysdate) );
1129
1130
1131 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1132 (asg_rec.job_id
1133 ,p_job_category);
1134
1135
1136 /* if l_jobcatg = 'Y'
1137 then
1138 l_rev_start_val := l_rev_start_val + l_abv;
1139 else
1140 l_nonrev_start_val := l_nonrev_start_val + l_abv;
1141 end if;*/
1142
1143 l_movement_category := NULL;
1144
1145 HR_PERSON_FLEX_LOGIC.GetMovementCategory(
1146 p_organization_id => org_rec.organization_id
1147 ,p_assignment_id => asg_rec.assignment_id
1148 ,p_period_start_date => asg_rec.effective_end_date
1149 ,p_period_end_date => P_DATE_TO
1150 ,p_movement_type => 'OUT'
1151 ,p_assignment_type => P_WORKER_TYPE
1152 ,p_movement_category => l_movement_category
1153 );
1154
1155 hr_utility.set_location('Movement Category 1 = '||l_movement_category,160);
1156
1157 if (l_movement_category = 'TRANSFER_OUT' or
1158 -- l_movement_category = 'SEPARATED' or
1159 l_movement_category = 'SUSPENDED' ) then
1160
1161 if l_jobcatg = 'Y'
1162 then
1163 l_rev_transfer_out := l_rev_transfer_out + l_abv;
1164 else
1165 l_nonrev_transfer_out := l_nonrev_transfer_out + l_abv;
1166 end if;
1167
1168 end if;
1169
1170
1171 end loop; -- get_assignment_start_end
1172
1173 HQOrgData(ose_rec.org_structure_element_id).rev_start_val :=
1174 HQOrgData(ose_rec.org_structure_element_id).rev_start_val + nvl(l_rev_start_val,0);
1175 HQOrgData(ose_rec.org_structure_element_id).nonrev_start_val :=
1176 HQOrgData(ose_rec.org_structure_element_id).nonrev_start_val + nvl(l_nonrev_start_val,0);
1177
1178 hr_utility.set_location('Beginning Head/FTE Count (R) = '||HQOrgData(ose_rec.org_structure_element_id).rev_start_val,170);
1179 hr_utility.set_location('Beginning Head/FTE Count (N) = '||HQOrgData(ose_rec.org_structure_element_id).nonrev_start_val,180);
1180
1181 HQOrgData(ose_rec.org_structure_element_id).rev_transfer_out :=
1182 HQOrgData(ose_rec.org_structure_element_id).rev_transfer_out + nvl(l_rev_transfer_out,0);
1183 HQOrgData(ose_rec.org_structure_element_id).nonrev_transfer_out :=
1184 HQOrgData(ose_rec.org_structure_element_id).nonrev_transfer_out + nvl(l_nonrev_transfer_out,0);
1185
1186 l_rev_transfer_out := 0;
1187 l_nonrev_transfer_out:= 0;
1188 l_rev_transfer_in := 0;
1189 l_nonrev_transfer_in := 0;
1190
1191 --changes for bug 6124652 starts here
1192 for asg_rec in get_assignment_start_end_fte
1193 (P_DATE_TO
1194 ,org_rec.organization_id
1195 ,P_BUSINESS_GROUP_ID
1196 ,P_WORKER_TYPE
1197 ,P_INCLUDE_ASG_TYPE)
1198
1199 loop -- get_assignment_start_end
1200
1201 l_jobcatg := NULL;
1202 l_abv := NULL;
1203
1204 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1205 ( p_ABV_formula_id => l_ABV_formula_id
1206 , p_ABV => p_budget
1207 , p_assignment_id => asg_rec.assignment_id
1208 , p_effective_date => asg_rec.effective_start_date
1209 , p_session_date => trunc(sysdate) );
1210
1211
1212 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1213 (asg_rec.job_id
1214 ,p_job_category);
1215
1216 if l_jobcatg = 'Y'
1217 then
1218 l_rev_end_val := l_rev_end_val + l_abv;
1219 else
1220 l_nonrev_end_val := l_nonrev_end_val + l_abv;
1221 end if;
1222 end loop;
1223 --changes for bug 6124652 ends here
1224 for asg_rec in get_assignment_start_end
1225 (
1226 --bug 6124652 starts here
1227 P_DATE_FROM,
1228 --bug 6124652 ends here
1229 P_DATE_TO
1230 ,org_rec.organization_id
1231 ,P_BUSINESS_GROUP_ID
1232 ,P_WORKER_TYPE
1233 ,P_INCLUDE_ASG_TYPE)
1234
1235 loop -- get_assignment_start_end
1236
1237 l_jobcatg := NULL;
1238 l_abv := NULL;
1239
1240 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1241 ( p_ABV_formula_id => l_ABV_formula_id
1242 , p_ABV => p_budget
1243 , p_assignment_id => asg_rec.assignment_id
1244 , p_effective_date => asg_rec.effective_start_date
1245 , p_session_date => trunc(sysdate) );
1246
1247
1248 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1249 (asg_rec.job_id
1250 ,p_job_category);
1251
1252 /* if l_jobcatg = 'Y'
1253 then
1254 l_rev_end_val := l_rev_end_val + l_abv;
1255 else
1256 l_nonrev_end_val := l_nonrev_end_val + l_abv;
1257 end if;
1258 */
1259 HR_PERSON_FLEX_LOGIC.GetMovementCategory(
1260 p_organization_id => org_rec.organization_id
1261 ,p_assignment_id => asg_rec.assignment_id
1262 ,p_period_start_date => P_DATE_FROM
1263 ,p_period_end_date => asg_rec.effective_end_date
1264 ,p_movement_type => 'IN'
1265 ,p_assignment_type => P_WORKER_TYPE
1266 ,p_movement_category => l_movement_category);
1267
1268 if (l_movement_category = 'TRANSFER_IN' or
1269 l_movement_category = 'REACTIVATED') then
1270
1271 hr_utility.set_location('Movement Category 2 = '||l_movement_category,190);
1272 hr_utility.set_location('Organization ID = '||org_rec.organization_id,200);
1273 hr_utility.set_location('Assignment ID = '||asg_rec.assignment_id,210);
1274 hr_utility.set_location('Start Date = '||to_char(P_DATE_FROM,'DD-MON-YYYY'),220);
1275 hr_utility.set_location('End Date = '||to_char(P_DATE_TO,'DD-MON-YYYY'),230);
1276
1277 if l_jobcatg = 'Y'
1278 then
1279 l_rev_transfer_in := l_rev_transfer_in + l_abv;
1280 else
1281 l_nonrev_transfer_in := l_nonrev_transfer_in + l_abv;
1282 end if;
1283
1284 elsif l_movement_category = 'NEW_HIRE' then
1285
1286 if l_jobcatg = 'Y'
1287 then
1288 l_rev_nh := l_rev_nh + l_abv;
1289 else
1290 l_nonrev_nh := l_nonrev_nh + l_abv;
1291 end if;
1292
1293 l_cur_nh := NULL;
1294
1295 l_cur_nh := HR_PERSON_FLEX_LOGIC.GetCurNHNew
1296 ( p_organization_id => org_rec.organization_id
1297 , p_assignment_id => asg_rec.assignment_id
1298 , p_assignment_type => P_WORKER_TYPE
1299 , p_cur_date_from => l_cur_date_from
1300 , p_cur_date_to => l_cur_date_to);
1301
1302 hr_utility.set_location('New Hire = '||l_cur_nh,240);
1303
1304 if l_cur_nh = 'Y' then
1305
1306 if l_jobcatg = 'Y'
1307 then
1308 l_rev_cur_nh := l_rev_cur_nh + l_abv;
1309 else
1310 l_nonrev_cur_nh := l_nonrev_cur_nh + l_abv;
1311 end if;
1312
1313 end if;
1314
1315 end if;
1316
1317 end loop; -- get_assignment_start_end
1318
1319 hr_utility.set_location('org_structure_element_id = '||ose_rec.org_structure_element_id,250);
1320 HQOrgData(ose_rec.org_structure_element_id).rev_end_val :=
1321 HQOrgData(ose_rec.org_structure_element_id).rev_end_val + nvl(l_rev_end_val,0);
1322 HQOrgData(ose_rec.org_structure_element_id).nonrev_end_val :=
1323 HQOrgData(ose_rec.org_structure_element_id).nonrev_end_val + nvl(l_nonrev_end_val,0);
1324
1325 hr_utility.set_location('Ending HeadCount (R) = '||HQOrgData(ose_rec.org_structure_element_id).rev_end_val,260);
1326 hr_utility.set_location('Ending HeadCount (N) = '||HQOrgData(ose_rec.org_structure_element_id).nonrev_end_val,270);
1327
1328 HQOrgData(ose_rec.org_structure_element_id).rev_transfer_in :=
1329 HQOrgData(ose_rec.org_structure_element_id).rev_transfer_in + nvl(l_rev_transfer_in,0);
1330
1331 HQOrgData(ose_rec.org_structure_element_id).nonrev_transfer_in :=
1332 HQOrgData(ose_rec.org_structure_element_id).nonrev_transfer_in + nvl(l_nonrev_transfer_in,0);
1333 hr_utility.set_location('Transfer In',2701);
1334
1335 HQOrgData(ose_rec.org_structure_element_id).rev_nh :=
1336 HQOrgData(ose_rec.org_structure_element_id).rev_nh + nvl(l_rev_nh,0);
1337 HQOrgData(ose_rec.org_structure_element_id).nonrev_nh :=
1338 HQOrgData(ose_rec.org_structure_element_id).nonrev_nh + nvl(l_nonrev_nh,0);
1339
1340 hr_utility.set_location('New Hire',2702);
1341
1342 HQOrgData(ose_rec.org_structure_element_id).rev_cur_nh :=
1343 HQOrgData(ose_rec.org_structure_element_id).rev_cur_nh + nvl(l_rev_cur_nh,0);
1344 HQOrgData(ose_rec.org_structure_element_id).nonrev_cur_nh :=
1345 HQOrgData(ose_rec.org_structure_element_id).nonrev_cur_nh + nvl(l_nonrev_cur_nh,0);
1346
1347 hr_utility.set_location('Current New Hire',2703);
1348
1349 l_rev_transfer_in := 0;
1350 l_nonrev_transfer_in := 0;
1351 l_rev_nh := 0;
1352 l_nonrev_nh := 0;
1353 l_rev_cur_nh := 0;
1354 l_nonrev_cur_nh := 0;
1355
1356 hr_utility.set_location('Current Organization',2704);
1357
1358 -- For the Current Organization
1359
1360 for assgt_rec in get_assignment
1361 ( P_DATE_FROM
1362 , P_DATE_TO
1363 , org_rec.organization_id
1364 , P_BUSINESS_GROUP_ID
1365 , P_WORKER_TYPE
1366 , P_INCLUDE_ASG_TYPE)
1367
1368 loop -- get_assignment
1369
1370 hr_utility.set_location('Current Organization',2705);
1371
1372 hr_utility.set_location('l_AsgWorkerType_formula_id = '||to_char(l_AsgWorkerType_formula_id),275);
1373 hr_utility.set_location('assgt_rec.assignment_id = '||to_char(assgt_rec.assignment_id),275);
1374 hr_utility.set_location('assgt_rec.effective_start_date = '||to_char(assgt_rec.effective_start_date,'DD/MM/YYYY'),275);
1375
1376 if assgt_rec.assignment_type <> 'C' then
1377
1378 l_pertype := NULL;
1379 l_pertype := HR_PERSON_FLEX_LOGIC.GetAsgWorkerType
1380 (p_AsgWorkerType_formula_id => l_AsgWorkerType_formula_id
1381 ,p_assignment_id => assgt_rec.assignment_id
1382 ,p_effective_date => assgt_rec.effective_start_date
1383 ,p_session_date => trunc(sysdate)
1384 );
1385
1386 end if;
1387
1388 hr_utility.set_location('AsgWorkerType = '||l_pertype,280);
1389
1390 l_jobcatg := NULL;
1391 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1392 (assgt_rec.job_id
1393 ,p_job_category);
1394 l_abv := 0;
1395 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1396 ( p_ABV_formula_id => l_ABV_formula_id
1397 , p_ABV => p_budget
1398 , p_assignment_id => assgt_rec.assignment_id
1399 , p_effective_date => assgt_rec.effective_start_date
1400 , p_session_date => trunc(sysdate) );
1401
1402 hr_utility.set_location('l_abv = '||l_abv,290);
1403 hr_utility.set_location('l_jobcatg = '||l_jobcatg,300);
1404 hr_utility.set_location('l_pertype = '||l_pertype,310);
1405
1406 if l_pertype = 'P' then
1407
1408 if l_jobcatg = 'Y'
1409 then
1410 l_rev_perm := l_rev_perm + l_abv;
1411 else
1412 l_nonrev_perm := l_nonrev_perm + l_abv;
1413 end if;
1414
1415 hr_utility.set_location('Rev Perm = '||to_char(l_rev_perm),320);
1416 hr_utility.set_location('NonRev Perm = '||to_char(l_nonrev_perm),330);
1417
1418 elsif l_pertype = 'T' then
1419
1420 if l_jobcatg = 'Y'
1421 then
1422 l_rev_temp := l_rev_temp + l_abv;
1423 else
1424 l_nonrev_temp := l_nonrev_temp + l_abv;
1425 end if;
1426
1427 elsif l_cwk_profile = 'N' then
1428
1429 if l_pertype = 'C' then
1430
1431 if l_jobcatg = 'Y'
1432 then
1433 l_rev_cont := l_rev_cont + l_abv;
1434 else
1435 l_nonrev_cont := l_nonrev_cont + l_abv;
1436 end if;
1437
1438 end if;
1439
1440 end if;
1441
1442 if (l_cwk_profile = 'Y' and
1443 assgt_rec.assignment_type = 'C') then
1444
1445 if l_jobcatg = 'Y'
1446 then
1447 l_rev_cont := l_rev_cont + l_abv;
1448 else
1449 l_nonrev_cont := l_nonrev_cont + l_abv;
1450 end if;
1451
1452 end if;
1453
1454
1455 end loop; -- get_assignment
1456
1457 HQOrgData(ose_rec.org_structure_element_id).rev_perm :=
1458 HQOrgData(ose_rec.org_structure_element_id).rev_perm + nvl(l_rev_perm,0);
1459 HQOrgData(ose_rec.org_structure_element_id).nonrev_perm :=
1460 HQOrgData(ose_rec.org_structure_element_id).nonrev_perm + nvl(l_nonrev_perm,0);
1461
1462 l_rev_perm := 0;
1463 l_nonrev_perm := 0;
1464
1465 hr_utility.set_location('Permanent (R) = '||HQOrgData(ose_rec.org_structure_element_id).rev_perm,340);
1466 hr_utility.set_location('Permanent (N) = '||HQOrgData(ose_rec.org_structure_element_id).nonrev_perm,350);
1467
1468 HQOrgData(ose_rec.org_structure_element_id).rev_temp :=
1469 HQOrgData(ose_rec.org_structure_element_id).rev_temp + nvl(l_rev_temp,0);
1470 HQOrgData(ose_rec.org_structure_element_id).nonrev_temp :=
1471 HQOrgData(ose_rec.org_structure_element_id).nonrev_temp + nvl(l_nonrev_temp,0);
1472
1473 l_rev_temp := 0;
1474 l_nonrev_temp := 0;
1475
1476 HQOrgData(ose_rec.org_structure_element_id).rev_cont :=
1477 HQOrgData(ose_rec.org_structure_element_id).rev_cont + nvl(l_rev_cont,0);
1478 HQOrgData(ose_rec.org_structure_element_id).nonrev_cont :=
1479 HQOrgData(ose_rec.org_structure_element_id).nonrev_cont + nvl(l_nonrev_cont,0);
1480
1481 l_rev_cont := 0;
1482 l_nonrev_cont := 0;
1483
1484
1485 HQOrgData(ose_rec.org_structure_element_id).rev_open_offers := 0;
1486 HQOrgData(ose_rec.org_structure_element_id).nonrev_open_offers := 0;
1487
1488 hr_utility.set_location('Permanent HeadCount (R) = '||HQOrgData(ose_rec.org_structure_element_id).rev_perm,360);
1489 hr_utility.set_location('Permanent HeadCount (N) = '||HQOrgData(ose_rec.org_structure_element_id).nonrev_perm,370);
1490
1491 l_rev_open_offers := 0;
1492 l_nonrev_open_offers := 0;
1493
1494 for appl_open_offers_rec in get_open_offers
1495 ( P_DATE_FROM
1496 , P_DATE_TO
1497 , org_rec.organization_id
1498 , P_BUSINESS_GROUP_ID)
1499
1500 loop -- get_open_offers
1501
1502 l_jobcatg := NULL;
1503 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1504 (appl_open_offers_rec.job_id
1505 ,p_job_category);
1506
1507 if l_jobcatg = 'Y'
1508 then
1509 l_rev_open_offers := l_rev_open_offers + 1;
1510 else
1511 l_nonrev_open_offers := l_nonrev_open_offers + 1;
1512 end if;
1513
1514 end loop; -- get_open_offers
1515
1516 HQOrgData(ose_rec.org_structure_element_id).rev_open_offers :=
1517 HQOrgData(ose_rec.org_structure_element_id).rev_open_offers + nvl(l_rev_open_offers,0);
1518 HQOrgData(ose_rec.org_structure_element_id).nonrev_open_offers :=
1519 HQOrgData(ose_rec.org_structure_element_id).nonrev_open_offers + nvl(l_nonrev_open_offers,0);
1520
1521 HQOrgData(ose_rec.org_structure_element_id).rev_accepted_offers := 0;
1522 HQOrgData(ose_rec.org_structure_element_id).nonrev_accepted_offers := 0;
1523
1524 l_rev_accepted_offers := 0;
1525 l_nonrev_accepted_offers := 0;
1526
1527 for appl_accepted_offers_rec in get_accepted_offers
1528 ( P_DATE_FROM
1529 , P_DATE_TO
1530 , org_rec.organization_id
1531 , P_BUSINESS_GROUP_ID)
1532
1533 loop -- get_accepted_offers
1534
1535 l_jobcatg := NULL;
1536 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1537 (appl_accepted_offers_rec.job_id
1538 ,p_job_category);
1539
1540 if l_jobcatg = 'Y'
1541 then
1542 l_rev_accepted_offers := l_rev_accepted_offers + 1;
1543 else
1544 l_nonrev_accepted_offers := l_nonrev_accepted_offers + 1;
1545 end if;
1546
1547 end loop; -- get_accepted_offers
1548
1549 HQOrgData(ose_rec.org_structure_element_id).rev_accepted_offers :=
1550 HQOrgData(ose_rec.org_structure_element_id).rev_accepted_offers + nvl(l_rev_accepted_offers,0);
1551 HQOrgData(ose_rec.org_structure_element_id).nonrev_accepted_offers :=
1552 HQOrgData(ose_rec.org_structure_element_id).nonrev_accepted_offers + nvl(l_nonrev_accepted_offers,0);
1553
1554 l_rev_vacant_FTE :=0;
1555 l_nonrev_vacant_FTE :=0;
1556
1557 for vac_rec in get_requisitions
1558 (P_BUSINESS_GROUP_ID
1559 ,org_rec.organization_id
1560 ,P_BUDGET
1561 ,P_DATE_TO )
1562
1563 loop -- get_requisitions
1564 l_jobcatg := NULL;
1565
1566 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1567 (vac_rec.job_id
1568 ,p_job_category);
1569
1570 if vac_rec.budget_measurement_value <> 0
1571 and vac_rec.number_of_openings <> 0
1572 then
1573 l_vacant_FTE := vac_rec.budget_measurement_value / vac_rec.number_of_openings;
1574 end if;
1575
1576 if l_jobcatg = 'Y'
1577 then
1578 l_rev_vacant_FTE := l_rev_vacant_FTE + l_vacant_FTE;
1579 else
1580 l_nonrev_vacant_FTE := l_nonrev_vacant_FTE + l_vacant_FTE;
1581 end if;
1582
1583 end loop; -- get_requisitions
1584
1585 HQOrgData(ose_rec.org_structure_element_id).rev_vacant_FTE :=
1586 HQOrgData(ose_rec.org_structure_element_id).rev_vacant_FTE + l_rev_vacant_FTE;
1587 HQOrgData(ose_rec.org_structure_element_id).nonrev_vacant_FTE :=
1588 HQOrgData(ose_rec.org_structure_element_id).nonrev_vacant_FTE + l_nonrev_vacant_FTE;
1589
1590 hr_utility.set_location('Organization ID............ '||org_rec.organization_id,400);
1591
1592 if P_WORKER_TYPE <> 'C' then
1593
1594 hr_utility.set_location('Start of Worker Type <> C Current Terminations.......',405);
1595
1596 l_rev_cur_term :=0;
1597 l_nonrev_cur_term :=0;
1598
1599 for cur_term_rec in get_cur_terminations
1600 ( P_DATE_FROM
1601 , l_cur_date_from
1602 , l_cur_date_to
1603 , org_rec.organization_id
1604 , P_BUSINESS_GROUP_ID)
1605
1606 loop -- get_cur_terminations
1607
1608 l_jobcatg := NULL;
1609 l_abv := NULL;
1610
1611 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1612 ( p_ABV_formula_id => l_ABV_formula_id
1613 , p_ABV => p_budget
1614 , p_assignment_id => cur_term_rec.assignment_id
1615 , p_effective_date => cur_term_rec.actual_termination_date
1616 , p_session_date => trunc(sysdate) );
1617
1618
1619 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1620 (cur_term_rec.job_id
1621 ,p_job_category);
1622
1623 if l_jobcatg = 'Y'
1624 then
1625 l_rev_cur_term := l_rev_cur_term + l_abv;
1626 else
1627 l_nonrev_cur_term := l_nonrev_cur_term + l_abv;
1628 end if;
1629
1630 end loop; -- get_cur_terminations
1631
1632 HQOrgData(ose_rec.org_structure_element_id).rev_cur_term :=
1633 HQOrgData(ose_rec.org_structure_element_id).rev_cur_term + nvl(l_rev_cur_term,0);
1634 HQOrgData(ose_rec.org_structure_element_id).nonrev_cur_term :=
1635 HQOrgData(ose_rec.org_structure_element_id).nonrev_cur_term + nvl(l_nonrev_cur_term,0);
1636
1637 hr_utility.set_location('End of Worker Type <> C Current Terminations.......',450);
1638
1639 l_rev_vol_term :=0;
1640 l_nonrev_vol_term :=0;
1641 l_rev_invol_term :=0;
1642 l_nonrev_invol_term :=0;
1643
1644 hr_utility.set_location('Start of Worker Type <> C Terminations.......',460);
1645 hr_utility.set_location('E Rev Vol T = '||l_rev_vol_term||'..........'||
1646 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term,480);
1647
1648 for term_rec in get_terminations
1649 ( P_DATE_FROM
1650 , P_DATE_TO
1651 , org_rec.organization_id
1652 , P_BUSINESS_GROUP_ID)
1653
1654 loop -- get_terminations
1655
1656 hr_utility.set_location('Leaving Reason = '||term_rec.leaving_reason,465);
1657
1658 l_termtype:= NULL;
1659 l_termtype:= HR_PERSON_FLEX_LOGIC.GetTermType
1660 (p_term_formula_id => l_term_formula_id
1661 ,p_leaving_reason => term_rec.leaving_reason
1662 ,p_session_date => trunc(sysdate));
1663
1664 hr_utility.set_location('Term Type = '||l_termtype,470);
1665
1666 l_jobcatg := NULL;
1667 l_abv := NULL;
1668
1669 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1670 ( p_ABV_formula_id => l_ABV_formula_id
1671 , p_ABV => p_budget
1672 , p_assignment_id => term_rec.assignment_id
1673 , p_effective_date => term_rec.actual_termination_date
1674 , p_session_date => trunc(sysdate) );
1675
1676 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1677 (term_rec.job_id
1678 ,p_job_category);
1679
1680 hr_utility.set_location('Job Category = '||l_jobcatg,475);
1681
1682 if l_termtype = 'V'
1683 then
1684 if l_jobcatg = 'Y'
1685 then
1686 l_rev_vol_term := l_rev_vol_term + l_abv;
1687 else
1688 l_nonrev_vol_term := l_nonrev_vol_term + l_abv;
1689 end if;
1690 elsif l_termtype = 'I'
1691 then
1692 if l_jobcatg = 'Y'
1693 then
1694 l_rev_invol_term := l_rev_invol_term + l_abv;
1695 else
1696 l_nonrev_invol_term := l_nonrev_invol_term + l_abv;
1697 end if;
1698 end if;
1699
1700 end loop; -- get_terminations
1701
1702 hr_utility.set_location('E Rev Vol T = '||l_rev_vol_term||'..........'||
1703 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term,480);
1704 hr_utility.set_location('E NonRev Vol T = '||l_nonrev_vol_term||'.......'||
1705 HQOrgData(ose_rec.org_structure_element_id).nonrev_vol_term,485);
1706
1707 hr_utility.set_location('E Rev InVol T = '||l_rev_invol_term||'........'||
1708 HQOrgData(ose_rec.org_structure_element_id).rev_invol_term,490);
1709 hr_utility.set_location('E NonRev InVol T = '||l_nonrev_invol_term||'.....'||
1710 HQOrgData(ose_rec.org_structure_element_id).nonrev_invol_term,495);
1711
1712 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term :=
1713 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term + nvl(l_rev_vol_term,0);
1714 HQOrgData(ose_rec.org_structure_element_id).rev_invol_term :=
1715 HQOrgData(ose_rec.org_structure_element_id).rev_invol_term + nvl(l_rev_invol_term,0);
1716
1717 HQOrgData(ose_rec.org_structure_element_id).nonrev_vol_term :=
1718 HQOrgData(ose_rec.org_structure_element_id).nonrev_vol_term + nvl(l_nonrev_vol_term,0);
1719 HQOrgData(ose_rec.org_structure_element_id).nonrev_invol_term :=
1720 HQOrgData(ose_rec.org_structure_element_id).nonrev_invol_term + nvl(l_nonrev_invol_term,0);
1721
1722 l_rev_vol_term :=0;
1723 l_nonrev_vol_term :=0;
1724 l_rev_invol_term :=0;
1725 l_nonrev_invol_term :=0;
1726
1727 hr_utility.set_location('End of Worker Type <> C Terminations.......',500);
1728
1729 end if;
1730
1731 if P_WORKER_TYPE <> 'E' then
1732
1733 hr_utility.set_location('Start of Worker Type <> E Current Terminations.......',505);
1734
1735 l_rev_cur_term :=0;
1736 l_nonrev_cur_term :=0;
1737
1738 for cur_term_rec in get_cur_terminations_cwk
1739 ( P_DATE_FROM
1740 , l_cur_date_from
1741 , l_cur_date_to
1742 , org_rec.organization_id
1743 , P_BUSINESS_GROUP_ID)
1744
1745 loop -- get_cur_terminations_cwk
1746
1747 l_jobcatg := NULL;
1748 l_abv := NULL;
1749
1750 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1751 ( p_ABV_formula_id => l_ABV_formula_id
1752 , p_ABV => p_budget
1753 , p_assignment_id => cur_term_rec.assignment_id
1754 , p_effective_date => cur_term_rec.actual_termination_date
1755 , p_session_date => trunc(sysdate) );
1756
1757
1758 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1759 (cur_term_rec.job_id
1760 ,p_job_category);
1761
1762 if l_jobcatg = 'Y'
1763 then
1764 l_rev_cur_term := l_rev_cur_term + l_abv;
1765 else
1766 l_nonrev_cur_term := l_nonrev_cur_term + l_abv;
1767 end if;
1768
1769 end loop; -- get_cur_terminations_cwk
1770
1771 HQOrgData(ose_rec.org_structure_element_id).rev_cur_term :=
1772 HQOrgData(ose_rec.org_structure_element_id).rev_cur_term + nvl(l_rev_cur_term,0);
1773 HQOrgData(ose_rec.org_structure_element_id).nonrev_cur_term :=
1774 HQOrgData(ose_rec.org_structure_element_id).nonrev_cur_term + nvl(l_nonrev_cur_term,0);
1775
1776 hr_utility.set_location('End of Worker Type <> E Current Terminations.......',550);
1777
1778 l_rev_vol_term :=0;
1779 l_nonrev_vol_term :=0;
1780 l_rev_invol_term :=0;
1781 l_nonrev_invol_term :=0;
1782
1783 hr_utility.set_location('Start of Worker Type <> E Terminations.......',560);
1784
1785 for term_rec in get_terminations_cwk
1786 ( P_DATE_FROM
1787 , P_DATE_TO
1788 , org_rec.organization_id
1789 , P_BUSINESS_GROUP_ID)
1790
1791 loop -- get_terminations_cwk
1792
1793 l_jobcatg := NULL;
1794 l_abv := NULL;
1795
1796 l_cwk_termtype := NULL;
1797 l_cwk_termtype := HR_PERSON_FLEX_LOGIC.GetTermType
1798 (p_term_formula_id => l_cwk_term_formula_id
1799 ,p_leaving_reason => term_rec.termination_reason
1800 ,p_session_date => trunc(sysdate));
1801
1802 hr_utility.set_location('Term Type = '||l_cwk_termtype,470);
1803
1804 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1805 ( p_ABV_formula_id => l_ABV_formula_id
1806 , p_ABV => p_budget
1807 , p_assignment_id => term_rec.assignment_id
1808 , p_effective_date => term_rec.actual_termination_date
1809 , p_session_date => trunc(sysdate) );
1810
1811 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1812 (term_rec.job_id
1813 ,p_job_category);
1814
1815 -- hr_utility.set_location('Job Categ = '||l_jobcatg,565);
1816
1817 if l_cwk_termtype = 'V'
1818 then
1819 if l_jobcatg = 'Y'
1820 then
1821 l_rev_vol_term := l_rev_vol_term + l_abv;
1822 else
1823 l_nonrev_vol_term := l_nonrev_vol_term + l_abv;
1824 end if;
1825 elsif l_cwk_termtype = 'I'
1826 then
1827 if l_jobcatg = 'Y'
1828 then
1829 l_rev_invol_term := l_rev_invol_term + l_abv;
1830 else
1831 l_nonrev_invol_term := l_nonrev_invol_term + l_abv;
1832 end if;
1833 end if;
1834
1835 end loop; -- get_terminations_cwk
1836
1837 hr_utility.set_location('C Rev Vol T = '||l_rev_vol_term,570);
1838 hr_utility.set_location('C NONRev Vol T = '||l_nonrev_vol_term,575);
1839
1840 hr_utility.set_location('C Rev InVol T = '||l_rev_vol_term,580);
1841 hr_utility.set_location('C NONRev InVol T = '||l_nonrev_vol_term,585);
1842
1843 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term :=
1844 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term + nvl(l_rev_vol_term,0);
1845 HQOrgData(ose_rec.org_structure_element_id).nonrev_vol_term :=
1846 HQOrgData(ose_rec.org_structure_element_id).nonrev_vol_term + nvl(l_nonrev_vol_term,0);
1847
1848 l_rev_vol_term :=0;
1849 l_nonrev_vol_term :=0;
1850 l_rev_invol_term :=0;
1851 l_nonrev_invol_term :=0;
1852
1853 hr_utility.set_location('End of Worker Type <> E Terminations.......',600);
1854
1855 end if;
1856
1857 if P_WORKER_TYPE = 'B' then
1858
1859 hr_utility.set_location('Worker Type = B Terminations.......',605);
1860
1861 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term :=
1862 HQOrgData(ose_rec.org_structure_element_id).rev_vol_term +
1863 HQOrgData(ose_rec.org_structure_element_id).rev_invol_term;
1864 HQOrgData(ose_rec.org_structure_element_id).nonrev_vol_term :=
1865 HQOrgData(ose_rec.org_structure_element_id).nonrev_vol_term +
1866 HQOrgData(ose_rec.org_structure_element_id).nonrev_invol_term ;
1867
1868 end if;
1869
1870 end loop; -- get_organizations
1871
1872 end loop; -- get_org_structure_element
1873
1874 -- This is the start of the TOP Organization story for a version
1875
1876 if P_INCLUDE_TOP_ORG = 'Y' then
1877
1878 hr_utility.set_location('P_INCLUDE_TOP_ORG = Y',800);
1879
1880 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_start_val := 0;
1881 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_end_val := 0;
1882 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_start_val := 0;
1883 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_end_val := 0;
1884 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_perm := 0;
1885 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_perm := 0;
1886 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cont := 0;
1887 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cont := 0;
1888 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_temp := 0;
1889 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_temp := 0;
1890 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_transfer_in := 0;
1891 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_transfer_in := 0;
1892 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_transfer_out := 0;
1893 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_transfer_out:= 0;
1894 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_nh := 0;
1895 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_nh := 0;
1896 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cur_nh := 0;
1897 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cur_nh := 0;
1898 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vacant_FTE := 0;
1899 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vacant_FTE := 0;
1900 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vol_term := 0;
1901 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vol_term := 0;
1902 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_invol_term := 0;
1903 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_invol_term := 0;
1904 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cur_term := 0;
1905 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cur_term := 0;
1906
1907
1908 hr_utility.set_location('Top Organization ='||(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)),810);
1909 hr_utility.set_location('osv_rec.org_structure_version_id = '||osv_rec.org_structure_version_id,820);
1910 hr_utility.set_location('P_TOP_ORGANIZATION_ID = '||P_TOP_ORGANIZATION_ID,830);
1911
1912 l_rev_start_val :=0;
1913 l_rev_end_val :=0;
1914 l_nonrev_start_val :=0;
1915 l_nonrev_end_val :=0;
1916 l_rev_transfer_out := 0;
1917 l_nonrev_transfer_out:= 0;
1918 --bug 6124652 starts here
1919 for asg_rec in get_assignment_start_end_fte
1920 (P_DATE_FROM
1921 ,P_TOP_ORGANIZATION_ID
1922 ,P_BUSINESS_GROUP_ID
1923 ,P_WORKER_TYPE
1924 ,P_INCLUDE_ASG_TYPE)
1925
1926 loop -- get_assignment_start_end
1927
1928 l_jobcatg := NULL;
1929 l_abv := NULL;
1930
1931 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1932 ( p_ABV_formula_id => l_ABV_formula_id
1933 , p_ABV => p_budget
1934 , p_assignment_id => asg_rec.assignment_id
1935 , p_effective_date => asg_rec.effective_start_date
1936 , p_session_date => trunc(sysdate) );
1937
1938
1939
1940
1941
1942
1943 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1944 (asg_rec.job_id
1945 ,p_job_category);
1946
1947 hr_utility.set_location('Top Org l_jobcatg = '||l_jobcatg,880);
1948
1949 if l_jobcatg = 'Y'
1950 then
1951 l_rev_start_val := l_rev_start_val + l_abv;
1952 else
1953 l_nonrev_start_val := l_nonrev_start_val + l_abv;
1954 end if;
1955 end loop;
1956 --bug 6124652 ends here
1957 for asg_rec in get_assignment_start_end
1958 (P_DATE_FROM,
1959 --bug 6124652 starts here
1960 P_DATE_TO
1961 --bug 6124652 ends here
1962 ,P_TOP_ORGANIZATION_ID
1963 ,P_BUSINESS_GROUP_ID
1964 ,P_WORKER_TYPE
1965 ,P_INCLUDE_ASG_TYPE)
1966
1967 loop -- get_assignment_start_end
1968
1969 l_jobcatg := NULL;
1970 l_abv := NULL;
1971
1972 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
1973 ( p_ABV_formula_id => l_ABV_formula_id
1974 , p_ABV => p_budget
1975 , p_assignment_id => asg_rec.assignment_id
1976 , p_effective_date => asg_rec.effective_start_date
1977 , p_session_date => trunc(sysdate) );
1978
1979 hr_utility.set_location('Top Org Assignment ID = '||asg_rec.assignment_id,840);
1980 hr_utility.set_location('Top Org Effective Start Date = '||to_char(asg_rec.effective_start_date,'DD/MM/YYYY'),850);
1981 hr_utility.set_location('Top Org Job ID = '||asg_rec.job_id,860);
1982 hr_utility.set_location('Top Org l_abv = '||l_abv,870);
1983
1984 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
1985 (asg_rec.job_id
1986 ,p_job_category);
1987
1988 hr_utility.set_location('Top Org l_jobcatg = '||l_jobcatg,880);
1989
1990 /* if l_jobcatg = 'Y'
1991 then
1992 l_rev_start_val := l_rev_start_val + l_abv;
1993 else
1994 l_nonrev_start_val := l_nonrev_start_val + l_abv;
1995 end if;
1996 */
1997 l_movement_category := NULL;
1998
1999 HR_PERSON_FLEX_LOGIC.GetMovementCategory(
2000 p_organization_id => P_TOP_ORGANIZATION_ID
2001 ,p_assignment_id => asg_rec.assignment_id
2002 ,p_period_start_date => asg_rec.effective_end_date
2003 ,p_period_end_date => P_DATE_TO
2004 ,p_movement_type => 'OUT'
2005 ,p_assignment_type => P_WORKER_TYPE
2006 ,p_movement_category => l_movement_category
2007 );
2008
2009 hr_utility.set_location('Top Org l_movement_category = '||l_movement_category,890);
2010
2011 if (l_movement_category = 'TRANSFER_OUT' or
2012 -- l_movement_category = 'SEPARATED' or
2013 l_movement_category = 'SUSPENDED' ) then
2014
2015 if l_jobcatg = 'Y'
2016 then
2017 l_rev_transfer_out := l_rev_transfer_out + l_abv;
2018 else
2019 l_nonrev_transfer_out := l_nonrev_transfer_out + l_abv;
2020 end if;
2021
2022 end if;
2023
2024
2025 end loop; -- get_assignment_start_end
2026
2027 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_start_val :=
2028 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_start_val + nvl(l_rev_start_val,0);
2029
2030 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_start_val :=
2031 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_start_val + nvl(l_nonrev_start_val,0);
2032
2033 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_transfer_out :=
2034 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_transfer_out + nvl(l_rev_transfer_out,0);
2035
2036 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_transfer_out :=
2037 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_transfer_out + nvl(l_nonrev_transfer_out,0);
2038
2039
2040 l_rev_transfer_out := 0;
2041 l_nonrev_transfer_out:= 0;
2042 l_rev_transfer_in := 0;
2043 l_nonrev_transfer_in := 0;
2044 --bug 6124652 starts here
2045 for asg_rec in get_assignment_start_end_fte
2046 (P_DATE_TO
2047 ,P_TOP_ORGANIZATION_ID
2048 ,P_BUSINESS_GROUP_ID
2049 ,P_WORKER_TYPE
2050 ,P_INCLUDE_ASG_TYPE)
2051
2052 loop -- get_assignment_start_end
2053
2054 l_jobcatg := NULL;
2055 l_abv := NULL;
2056
2057 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
2058 ( p_ABV_formula_id => l_ABV_formula_id
2059 , p_ABV => p_budget
2060 , p_assignment_id => asg_rec.assignment_id
2061 , p_effective_date => asg_rec.effective_start_date
2062 , p_session_date => trunc(sysdate) );
2063
2064
2065 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2066 (asg_rec.job_id
2067 ,p_job_category);
2068
2069 if l_jobcatg = 'Y'
2070 then
2071 l_rev_end_val := l_rev_end_val + l_abv;
2072 else
2073 l_nonrev_end_val := l_nonrev_end_val + l_abv;
2074 end if;
2075 end loop;
2076 --bug 6124652 ends here
2077 for asg_rec in get_assignment_start_end
2078 (
2079 --bug 6124652 starts here
2080 P_DATE_FROM,
2081 --bug 6124652 ends here
2082 P_DATE_TO
2083 ,P_TOP_ORGANIZATION_ID
2084 ,P_BUSINESS_GROUP_ID
2085 ,P_WORKER_TYPE
2086 ,P_INCLUDE_ASG_TYPE)
2087
2088 loop -- get_assignment_start_end
2089
2090 l_jobcatg := NULL;
2091 l_abv := NULL;
2092
2093 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
2094 ( p_ABV_formula_id => l_ABV_formula_id
2095 , p_ABV => p_budget
2096 , p_assignment_id => asg_rec.assignment_id
2097 , p_effective_date => asg_rec.effective_start_date
2098 , p_session_date => trunc(sysdate) );
2099
2100
2101 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2102 (asg_rec.job_id
2103 ,p_job_category);
2104 /*
2105 if l_jobcatg = 'Y'
2106 then
2107 l_rev_end_val := l_rev_end_val + l_abv;
2108 else
2109 l_nonrev_end_val := l_nonrev_end_val + l_abv;
2110 end if;
2111 */
2112 HR_PERSON_FLEX_LOGIC.GetMovementCategory(
2113 p_organization_id => P_TOP_ORGANIZATION_ID
2114 ,p_assignment_id => asg_rec.assignment_id
2115 ,p_period_start_date => P_DATE_FROM
2116 ,p_period_end_date => asg_rec.effective_end_date
2117 ,p_movement_type => 'IN'
2118 ,p_assignment_type => P_WORKER_TYPE
2119 ,p_movement_category => l_movement_category
2120 );
2121
2122 if (l_movement_category = 'TRANSFER_IN' or
2123 l_movement_category = 'REACTIVATED') then
2124
2125 hr_utility.set_location('Top Org l_movement_category2 = '||l_movement_category,900);
2126 hr_utility.set_location('Top Org Start Date = '||to_char(P_DATE_FROM,'DD-MON-YYYY'),910);
2127 hr_utility.set_location('Top Org End Date = '||to_char(P_DATE_TO,'DD-MON-YYYY'),920);
2128
2129 if l_jobcatg = 'Y'
2130 then
2131 l_rev_transfer_in := l_rev_transfer_in + l_abv;
2132 else
2133 l_nonrev_transfer_in := l_nonrev_transfer_in + l_abv;
2134 end if;
2135
2136 elsif l_movement_category = 'NEW_HIRE' then
2137
2138 if l_jobcatg = 'Y'
2139 then
2140 l_rev_nh := l_rev_nh + l_abv;
2141 else
2142 l_nonrev_nh := l_nonrev_nh + l_abv;
2143 end if;
2144
2145 l_cur_nh := NULL;
2146
2147 l_cur_nh := HR_PERSON_FLEX_LOGIC.GetCurNHNew
2148 ( p_organization_id => P_TOP_ORGANIZATION_ID
2149 , p_assignment_id => asg_rec.assignment_id
2150 , p_assignment_type => P_WORKER_TYPE
2151 , p_cur_date_from => l_cur_date_from
2152 , p_cur_date_to => l_cur_date_to);
2153
2154 hr_utility.set_location('Top Org New Hire = '||l_cur_nh,930);
2155
2156 if l_cur_nh = 'Y' then
2157
2158 if l_jobcatg = 'Y'
2159 then
2160 l_rev_cur_nh := l_rev_cur_nh + l_abv;
2161 else
2162 l_nonrev_cur_nh := l_nonrev_cur_nh + l_abv;
2163 end if;
2164
2165 end if;
2166
2167 end if;
2168
2169 end loop; -- get_assignment_start_end
2170
2171 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_end_val :=
2172 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_end_val + nvl(l_rev_end_val,0);
2173
2174 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_end_val :=
2175 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_end_val + nvl(l_nonrev_end_val,0);
2176
2177 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_transfer_in :=
2178 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_transfer_in + nvl(l_rev_transfer_in,0);
2179
2180 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_transfer_in :=
2181 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_transfer_in + nvl(l_nonrev_transfer_in,0);
2182
2183 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_nh :=
2184 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_nh + nvl(l_rev_nh,0);
2185
2186 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_nh :=
2187 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_nh + nvl(l_nonrev_nh,0);
2188
2189
2190 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cur_nh :=
2191 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cur_nh + nvl(l_rev_cur_nh,0);
2192
2193 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cur_nh :=
2194 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cur_nh + nvl(l_nonrev_cur_nh,0);
2195
2196
2197 l_rev_transfer_in := 0;
2198 l_nonrev_transfer_in := 0;
2199 l_rev_nh := 0;
2200 l_nonrev_nh := 0;
2201 l_rev_cur_nh := 0;
2202 l_nonrev_cur_nh := 0;
2203
2204 -- For the Current Organization
2205
2206
2207 for assgt_rec in get_assignment
2208 ( P_DATE_FROM
2209 , P_DATE_TO
2210 , P_TOP_ORGANIZATION_ID
2211 , P_BUSINESS_GROUP_ID
2212 , P_WORKER_TYPE
2213 , P_INCLUDE_ASG_TYPE)
2214
2215 loop -- get_assignment
2216
2217 hr_utility.set_location('Top Org New Hire = '||l_cur_nh,930);
2218 hr_utility.set_location('Top Org assgt_rec.assignment_id = '||to_char(assgt_rec.assignment_id),940);
2219 hr_utility.set_location('assgt_rec.effective_start_date = '||to_char(assgt_rec.effective_start_date,'DD/MM/YYYY'),950);
2220
2221 if assgt_rec.assignment_type <> 'C' then
2222 l_pertype := NULL;
2223 l_pertype := HR_PERSON_FLEX_LOGIC.GetAsgWorkerType
2224 (p_AsgWorkerType_formula_id => l_AsgWorkerType_formula_id
2225 ,p_assignment_id => assgt_rec.assignment_id
2226 ,p_effective_date => assgt_rec.effective_start_date
2227 ,p_session_date => trunc(sysdate)
2228 );
2229 end if;
2230
2231 hr_utility.set_location('Top Org AsgWorkerType = '||l_pertype,960);
2232
2233 l_jobcatg := NULL;
2234 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2235 (assgt_rec.job_id
2236 ,p_job_category);
2237 l_abv := 0;
2238 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
2239 ( p_ABV_formula_id => l_ABV_formula_id
2240 , p_ABV => p_budget
2241 , p_assignment_id => assgt_rec.assignment_id
2242 , p_effective_date => assgt_rec.effective_start_date
2243 , p_session_date => trunc(sysdate) );
2244
2245 hr_utility.set_location('Top Org l_abv = '||l_abv,970);
2246 hr_utility.set_location('Top Org l_jobcatg = '||l_jobcatg,980);
2247 hr_utility.set_location('Top Org l_pertype = '||l_pertype,990);
2248
2249 if l_pertype = 'P' then
2250 if l_jobcatg = 'Y'
2251 then
2252 l_rev_perm := l_rev_perm + l_abv;
2253 else
2254 l_nonrev_perm := l_nonrev_perm + l_abv;
2255 end if;
2256
2257 hr_utility.set_location('Top Org Rev Perm = '||to_char(l_rev_perm),1000);
2258 hr_utility.set_location('Top Org NonRev Perm = '||to_char(l_nonrev_perm),1010);
2259
2260 elsif l_pertype = 'T' then
2261 if l_jobcatg = 'Y'
2262 then
2263 l_rev_temp := l_rev_temp + l_abv;
2264 else
2265 l_nonrev_temp := l_nonrev_temp + l_abv;
2266 end if;
2267
2268 elsif l_cwk_profile = 'N' then
2269
2270 if l_pertype = 'C' then
2271
2272 if l_jobcatg = 'Y'
2273 then
2274 l_rev_cont := l_rev_cont + l_abv;
2275 else
2276 l_nonrev_cont := l_nonrev_cont + l_abv;
2277 end if;
2278
2279
2280 end if;
2281
2282 end if;
2283
2284 if (l_cwk_profile = 'Y' and
2285 assgt_rec.assignment_type = 'C') then
2286
2287 if l_jobcatg = 'Y'
2288 then
2289 l_rev_cont := l_rev_cont + l_abv;
2290 else
2291 l_nonrev_cont := l_nonrev_cont + l_abv;
2292 end if;
2293
2294 end if;
2295
2296
2297 end loop; -- get_assignment
2298
2299 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_perm :=
2300 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_perm + nvl(l_rev_perm,0);
2301
2302 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_perm :=
2303 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_perm + nvl(l_nonrev_perm,0);
2304
2305 l_rev_perm := 0;
2306 l_nonrev_perm := 0;
2307
2308 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_temp :=
2309 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_temp + nvl(l_rev_temp,0);
2310
2311 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_temp :=
2312 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_temp + nvl(l_nonrev_temp,0);
2313
2314
2315 l_rev_temp := 0;
2316 l_nonrev_temp := 0;
2317
2318 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cont :=
2319 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cont + nvl(l_rev_cont,0);
2320
2321 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cont :=
2322 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cont + nvl(l_nonrev_cont,0);
2323
2324
2325 l_rev_cont := 0;
2326 l_nonrev_cont := 0;
2327
2328 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_open_offers := 0;
2329 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_open_offers := 0;
2330
2331 l_rev_open_offers := 0;
2332 l_nonrev_open_offers := 0;
2333
2334 for appl_open_offers_rec in get_open_offers
2335 ( P_DATE_FROM
2336 , P_DATE_TO
2337 , P_TOP_ORGANIZATION_ID
2338 , P_BUSINESS_GROUP_ID)
2339
2340 loop -- get_open_offers
2341
2342 l_jobcatg := NULL;
2343 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2344 (appl_open_offers_rec.job_id
2345 ,p_job_category);
2346
2347 if l_jobcatg = 'Y'
2348 then
2349 l_rev_open_offers := l_rev_open_offers + 1;
2350 else
2351 l_nonrev_open_offers := l_nonrev_open_offers + 1;
2352 end if;
2353
2354 end loop; -- get_open_offers
2355
2356 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_open_offers :=
2357 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_open_offers + nvl(l_rev_open_offers,0);
2358
2359 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_open_offers :=
2360 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_open_offers + nvl(l_nonrev_open_offers,0);
2361
2362
2363 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_accepted_offers := 0;
2364 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_accepted_offers := 0;
2365
2366 l_rev_accepted_offers := 0;
2367 l_nonrev_accepted_offers := 0;
2368
2369 for appl_accepted_offers_rec in get_accepted_offers
2370 ( P_DATE_FROM
2371 , P_DATE_TO
2372 , P_TOP_ORGANIZATION_ID
2373 , P_BUSINESS_GROUP_ID)
2374
2375 loop -- get_accepted_offers
2376
2377 l_jobcatg := NULL;
2378 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2379 (appl_accepted_offers_rec.job_id
2380 ,p_job_category);
2381
2382 if l_jobcatg = 'Y'
2383 then
2384 l_rev_accepted_offers := l_rev_accepted_offers + 1;
2385 else
2386 l_nonrev_accepted_offers := l_nonrev_accepted_offers + 1;
2387 end if;
2388
2389 end loop; -- get_accepted_offers
2390
2391 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_accepted_offers :=
2392 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_accepted_offers + nvl(l_rev_accepted_offers,0);
2393
2394 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_accepted_offers :=
2395 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_accepted_offers + nvl(l_nonrev_accepted_offers,0);
2396
2397
2398 l_rev_vacant_FTE :=0;
2399 l_nonrev_vacant_FTE :=0;
2400
2401 for vac_rec in get_requisitions
2402 (P_BUSINESS_GROUP_ID
2403 ,P_TOP_ORGANIZATION_ID
2404 ,P_BUDGET
2405 ,P_DATE_TO )
2406 loop
2407 l_jobcatg := NULL;
2408
2409 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2410 (vac_rec.job_id
2411 ,p_job_category);
2412
2413 if vac_rec.budget_measurement_value <> 0
2414 and vac_rec.number_of_openings <> 0
2415 then
2416 l_vacant_FTE := vac_rec.budget_measurement_value / vac_rec.number_of_openings;
2417 end if;
2418
2419 if l_jobcatg = 'Y'
2420 then
2421 l_rev_vacant_FTE := l_rev_vacant_FTE + l_vacant_FTE;
2422 else
2423 l_nonrev_vacant_FTE := l_nonrev_vacant_FTE + l_vacant_FTE;
2424 end if;
2425
2426 end loop; -- get_requisitions
2427
2428 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vacant_FTE :=
2429 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vacant_FTE + nvl(l_rev_vacant_FTE,0);
2430
2431 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vacant_FTE :=
2432 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vacant_FTE + nvl(l_nonrev_vacant_FTE,0);
2433
2434 l_rev_cur_term :=0;
2435 l_nonrev_cur_term :=0;
2436
2437
2438 if P_WORKER_TYPE <> 'C' then
2439
2440 for cur_term_rec in get_cur_terminations
2441 ( P_DATE_FROM
2442 , l_cur_date_from
2443 , l_cur_date_to
2444 , P_TOP_ORGANIZATION_ID
2445 , P_BUSINESS_GROUP_ID)
2446
2447 loop -- get_cur_terminations
2448
2449 l_jobcatg := NULL;
2450 l_abv := NULL;
2451
2452 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
2453 ( p_ABV_formula_id => l_ABV_formula_id
2454 , p_ABV => p_budget
2455 , p_assignment_id => cur_term_rec.assignment_id
2456 , p_effective_date => cur_term_rec.actual_termination_date
2457 , p_session_date => trunc(sysdate) );
2458
2459
2460 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2461 (cur_term_rec.job_id
2462 ,p_job_category);
2463
2464 if l_jobcatg = 'Y'
2465 then
2466 l_rev_cur_term := l_rev_cur_term + l_abv;
2467 else
2468 l_nonrev_cur_term := l_nonrev_cur_term + l_abv;
2469 end if;
2470
2471 end loop; -- get_cur_terminations
2472
2473 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cur_term :=
2474 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cur_term + nvl(l_rev_cur_term,0);
2475
2476 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cur_term :=
2477 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cur_term + nvl(l_nonrev_cur_term,0);
2478
2479
2480 l_rev_vol_term :=0;
2481 l_nonrev_vol_term :=0;
2482 l_rev_invol_term :=0;
2483 l_nonrev_invol_term :=0;
2484
2485 for term_rec in get_terminations
2486 ( P_DATE_FROM
2487 , P_DATE_TO
2488 , P_TOP_ORGANIZATION_ID
2489 , P_BUSINESS_GROUP_ID)
2490
2491 loop -- get_terminations
2492
2493 l_termtype:= NULL;
2494 l_termtype:= HR_PERSON_FLEX_LOGIC.GetTermType
2495 (p_term_formula_id => l_term_formula_id
2496 ,p_leaving_reason => term_rec.leaving_reason
2497 ,p_session_date => trunc(sysdate));
2498
2499 hr_utility.set_location('Term Type = '||l_termtype,400);
2500
2501 l_jobcatg := NULL;
2502 l_abv := NULL;
2503
2504 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
2505 ( p_ABV_formula_id => l_ABV_formula_id
2506 , p_ABV => p_budget
2507 , p_assignment_id => term_rec.assignment_id
2508 , p_effective_date => term_rec.actual_termination_date
2509 , p_session_date => trunc(sysdate) );
2510
2511
2512 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2513 (term_rec.job_id
2514 ,p_job_category);
2515
2516 hr_utility.set_location('Job Categ = '||l_jobcatg,410);
2517
2518 if l_termtype = 'V'
2519 then
2520 if l_jobcatg = 'Y'
2521 then
2522 l_rev_vol_term := l_rev_vol_term + l_abv;
2523 else
2524 l_nonrev_vol_term := l_nonrev_vol_term + l_abv;
2525 end if;
2526 elsif l_termtype = 'I'
2527 then
2528 if l_jobcatg = 'Y'
2529 then
2530 l_rev_invol_term := l_rev_invol_term + l_abv;
2531 else
2532 l_nonrev_invol_term := l_nonrev_invol_term + l_abv;
2533 end if;
2534 end if;
2535
2536 end loop; -- get_terminations
2537
2538 hr_utility.set_location('Rev Vol T = '||l_rev_vol_term,420);
2539 hr_utility.set_location('NONRev Vol T = '||l_nonrev_vol_term,430);
2540
2541 end if;
2542
2543 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vol_term :=
2544 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vol_term +
2545 nvl(l_rev_vol_term,0);
2546
2547 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vol_term :=
2548 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vol_term +
2549 nvl(l_nonrev_vol_term,0);
2550
2551
2552 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_invol_term :=
2553 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_invol_term +
2554 nvl(l_rev_invol_term,0);
2555
2556 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_invol_term :=
2557 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_invol_term +
2558 nvl(l_nonrev_invol_term,0);
2559
2560 if P_WORKER_TYPE <> 'E' then
2561
2562 for cur_term_rec in get_cur_terminations_cwk
2563 ( P_DATE_FROM
2564 , l_cur_date_from
2565 , l_cur_date_to
2566 , P_TOP_ORGANIZATION_ID
2567 , P_BUSINESS_GROUP_ID)
2568
2569 loop -- get_cur_terminations_cwk
2570
2571 l_jobcatg := NULL;
2572 l_abv := NULL;
2573
2574 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
2575 ( p_ABV_formula_id => l_ABV_formula_id
2576 , p_ABV => p_budget
2577 , p_assignment_id => cur_term_rec.assignment_id
2578 , p_effective_date => cur_term_rec.actual_termination_date
2579 , p_session_date => trunc(sysdate) );
2580
2581
2582 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2583 (cur_term_rec.job_id
2584 ,p_job_category);
2585
2586 if l_jobcatg = 'Y'
2587 then
2588 l_rev_cur_term := l_rev_cur_term + l_abv;
2589 else
2590 l_nonrev_cur_term := l_nonrev_cur_term + l_abv;
2591 end if;
2592
2593 end loop; -- get_cur_terminations_cwk
2594
2595 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cur_term :=
2596 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_cur_term + nvl(l_rev_cur_term,0);
2597
2598 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cur_term :=
2599 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_cur_term + nvl(l_nonrev_cur_term,0);
2600
2601
2602 l_rev_vol_term :=0;
2603 l_nonrev_vol_term :=0;
2604 l_rev_invol_term :=0;
2605 l_nonrev_invol_term :=0;
2606
2607 for term_rec in get_terminations_cwk
2608 ( P_DATE_FROM
2609 , P_DATE_TO
2610 , P_TOP_ORGANIZATION_ID
2611 , P_BUSINESS_GROUP_ID)
2612
2613 loop -- get_terminations_cwk
2614
2615 l_jobcatg := NULL;
2616 l_abv := NULL;
2617
2618 l_cwk_termtype := NULL;
2619 l_cwk_termtype := HR_PERSON_FLEX_LOGIC.GetTermType
2620 (p_term_formula_id => l_cwk_term_formula_id
2621 ,p_leaving_reason => term_rec.termination_reason
2622 ,p_session_date => trunc(sysdate));
2623
2624 l_abv := HR_PERSON_FLEX_LOGIC.GetABV
2625 ( p_ABV_formula_id => l_ABV_formula_id
2626 , p_ABV => p_budget
2627 , p_assignment_id => term_rec.assignment_id
2628 , p_effective_date => term_rec.actual_termination_date
2629 , p_session_date => trunc(sysdate) );
2630
2631
2632 l_jobcatg := HR_PERSON_FLEX_LOGIC.GetJobCategory
2633 (term_rec.job_id
2634 ,p_job_category);
2635
2636 hr_utility.set_location('Job Categ = '||l_jobcatg,410);
2637
2638 if l_cwk_termtype = 'V'
2639 then
2640 if l_jobcatg = 'Y'
2641 then
2642 l_rev_vol_term := l_rev_vol_term + l_abv;
2643 else
2644 l_nonrev_vol_term := l_nonrev_vol_term + l_abv;
2645 end if;
2646 elsif l_cwk_termtype = 'I'
2647 then
2648 if l_jobcatg = 'Y'
2649 then
2650 l_rev_invol_term := l_rev_invol_term + l_abv;
2651 else
2652 l_nonrev_invol_term := l_nonrev_invol_term + l_abv;
2653 end if;
2654 end if;
2655
2656 end loop; -- get_terminations
2657
2658 hr_utility.set_location('Rev Vol T = '||l_rev_vol_term,420);
2659 hr_utility.set_location('NONRev Vol T = '||l_nonrev_vol_term,430);
2660
2661 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vol_term :=
2662 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vol_term + nvl(l_rev_vol_term,0);
2663
2664 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vol_term :=
2665 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vol_term + nvl(l_nonrev_vol_term,0);
2666
2667
2668 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_invol_term :=
2669 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_invol_term + nvl(l_rev_invol_term,0);
2670
2671 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_invol_term :=
2672 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_invol_term + nvl(l_nonrev_invol_term,0);
2673
2674 if P_WORKER_TYPE = 'B' then
2675
2676 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vol_term :=
2677 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_vol_term +
2678 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).rev_invol_term;
2679
2680 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vol_term :=
2681 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_vol_term +
2682 HQOrgData(1-(osv_rec.org_structure_version_id+P_TOP_ORGANIZATION_ID)).nonrev_invol_term;
2683
2684 end if;
2685
2686 end if;
2687
2688 hr_utility.set_location(' End of P_INCLUDE_TOP_ORG = Y',1000);
2689
2690 end if; -- if P_INCLUDE_TOP_ORG = 'Y'
2691
2692 hr_utility.set_location(' Out of P_INCLUDE_TOP_ORG = Y',1000);
2693
2694 end loop; -- get_org_structure_version
2695
2696 hr_utility.set_location(' Out of get_org_structure_version ',1000);
2697 hr_utility.set_location(' ',1000);
2698 hr_utility.set_location('*Organiz*STAR*PERM*CONT*TEMP*CuNH*NewH*TrIN*TrOt*VoTM*InTM*ENDV*',8000);
2699
2700 for all_org_rec in c_get_all_orgs
2701 (P_ORGANIZATION_STRUCTURE_ID
2702 ,P_TOP_ORGANIZATION_ID
2703 ,P_REPORT_DATE_FROM
2704 ,P_REPORT_DATE_TO)
2705
2706 loop
2707
2708 hr_utility.set_location(' ',8000);
2709 hr_utility.set_location('R'||lpad(to_char(all_org_rec.organization_id_child),7,'.')||
2710 '*'||
2711 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_start_val,4,'.')||
2712 '*'||
2713 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_perm,4,'.')||
2714 '*'||
2715 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_cont,4,'.')||
2716 '*'||
2717 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_temp,4,'.')||
2718 '*'||
2719 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_cur_nh,4,'.')||
2720 '*'||
2721 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_nh,4,'.')||
2722 '*'||
2723 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_transfer_in,4,'.')||
2724 '*'||
2725 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_transfer_out,4,'.')||
2726 '*'||
2727 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_vol_term,4,'.')||
2728 '*'||
2729 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_invol_term,4,'.')||
2730 '*'||
2731 lpad(HQOrgData(all_org_rec.org_structure_element_id).rev_end_val,4,'*'),8000);
2732
2733 hr_utility.set_location('N'||lpad(to_char(all_org_rec.organization_id_child),7,'.')||
2734 '*'||
2735 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_start_val,4,'.')||
2736 '*'||
2737 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_perm,4,'.')||
2738 '*'||
2739 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_cont,4,'.')||
2740 '*'||
2741 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_temp,4,'.')||
2742 '*'||
2743 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_cur_nh,4,'.')||
2744 '*'||
2745 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_nh,4,'.')||
2746 '*'||
2747 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_transfer_in,4,'.')||
2748 '*'||
2749 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_transfer_out,4,'.')||
2750 '*'||
2751 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_vol_term,4,'.')||
2752 '*'||
2753 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_invol_term,4,'.')||
2754 '*'||
2755 lpad(HQOrgData(all_org_rec.org_structure_element_id).nonrev_end_val,4,'*'),8000);
2756
2757 end loop;
2758
2759 if P_INCLUDE_TOP_ORG = 'Y' then
2760
2761 hr_utility.set_location(' ',10000);
2762 hr_utility.set_location('start of c_get_top_orgs',10000);
2763 hr_utility.set_location(' ',10000);
2764 hr_utility.set_location('*Organiz*STAR*PERM*CONT*TEMP*CuNH*NewH*TrIN*TrOt*VoTM*InTM*ENDV*',8000);
2765
2766 for all_top_org_rec in c_get_top_orgs
2767 (P_ORGANIZATION_STRUCTURE_ID
2768 ,P_TOP_ORGANIZATION_ID
2769 ,P_REPORT_DATE_FROM
2770 ,P_REPORT_DATE_TO)
2771
2772 loop
2773
2774 -- hr_utility.set_location('Top Org == '||all_top_org_rec.org_structure_element_id,10000);
2775 hr_utility.set_location('R'||lpad(to_char(all_top_org_rec.organization_id_child),7,'.')||
2776 '*'||
2777 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_start_val,4,'.')||
2778 '*'||
2779 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_perm,4,'.')||
2780 '*'||
2781 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_cont,4,'.')||
2782 '*'||
2783 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_temp,4,'.')||
2784 '*'||
2785 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_cur_nh,4,'.')||
2786 '*'||
2787 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_nh,4,'.')||
2788 '*'||
2789 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_transfer_in,4,'.')||
2790 '*'||
2791 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_transfer_out,4,'.')||
2792 '*'||
2793 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_vol_term,4,'.')||
2794 '*'||
2795 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_invol_term,4,'.')||
2796 '*'||
2797 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).rev_end_val,4,'.'),8000);
2798
2799 hr_utility.set_location('N'||lpad(to_char(all_top_org_rec.organization_id_child),7,'.')||
2800 '*'||
2801 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_start_val,4,'.')||
2802 '*'||
2803 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_perm,4,'.')||
2804 '*'||
2805 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_cont,4,'.')||
2806 '*'||
2807 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_temp,4,'.')||
2808 '*'||
2809 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_cur_nh,4,'.')||
2810 '*'||
2811 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_nh,4,'.')||
2812 '*'||
2813 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_transfer_in,4,'.')||
2814 '*'||
2815 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_transfer_out,4,'.')||
2816 '*'||
2817 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_vol_term,4,'.')||
2818 '*'||
2819 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_invol_term,4,'.')||
2820 '*'||
2821 lpad(HQOrgData(all_top_org_rec.org_structure_element_id).nonrev_end_val,4,'.'),8000);
2822 hr_utility.set_location(' ',10000);
2823
2824
2825 end loop; -- c_get_top_orgs
2826
2827 end if; -- if P_INCLUDE_TOP_ORG = 'Y'
2828
2829 hr_utility.set_location('end populate_headcount_table',9999);
2830
2831 end populate_headcount_table;
2832
2833 END HR_HEAD_COUNT;