1 PACKAGE BODY pay_eosurvey_pkg as
2 /* $Header: pyuseosy.pkb 120.2.12000000.2 2007/07/16 17:29:57 rpasumar noship $ */
3 /*Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
4
5 Name :This package defines the cursors needed for EO Survey report.
6
7 REM Change List:
8 REM ------------
9 REM
10 REM Name Date Version Bug Text
11 REM -------------- ---------- ------- ------- ------------------------------
12 REM fusman 02-APR-01 115.0 Created
13 REM fusman 13-may-01 115.1 Added Change list and mesgs.
14 REM fusman 15-may-01 115.2 Changed the comments.
15 REM fusman 17-may-01 115.3 Added the check condition for the dates.
16 REM fusman 29-jun-01 115.4 Changed the date setting.
17 REM vbanner 28-jun-04 115.6 GSCC changes (dbdrv etc).
18 REM vbanner 28-jun-04 115.7 Further GSCC changes (to_date etc).
19 REM ynegoro 19-JUL-04 115.8 3730282 Added substr(p_location_name,1,80)
20 REM to insert a record into
21 REM pay_us_rpt_totals
22 REM Changed promotion procedure
23 REM ynegoro 28-SEP-04 115.9 3894120 Changed the length of fein
24 REM in TYPE establishment
25 REM ynegoro 05-OCT-04 115.10 3886008 Added l_hours_worked is NULL
26 REM in hire_of_fte procedure
27 REM ynegoro 14-OCT-04 115.12 3940867 Changed parameters to open
28 REM assignment_details cursor in
29 REM find_persons procuedure
30 REM ynegoro 14-OCT-04 115.13 3940867 Changed c_person_infm cursor
31 REM comment out max(ppf1.effective_start_date)
32 REM ynegoro 15-OCT-04 115.14 Added new parameters to
33 REM hire_or_fte procedure
34 REM 3941606 Added minority procedure call
35 REM when applicant's racecode is NULL
36 REM 3954458 Added c_check_future_termination
37 REM cursor for FTE count
38 REM ynegoro 18-OCT-04 115.15 Changed assignment_details
39 REM ynegoro 19-OCT-04 115.16 3941606 Added c_get_updated_racecode cursor
40 REM ynegoro 19-OCT-04 115.17 3954458 Changed paremeter from p_period_start
41 REM to p_eff_start_date to open
42 REM c_check_future_termination cursor
43 REM 3878442 Added 'EMP_APL' to include
44 REM APPLICANT cont in app_fire_count
45 REM ynegoro 20-OCT-04 115.18 3954458 Changed c_app_term_assignment
46 REM to pick up terminated employees
47 REM who are rehired.
48 REM ynegoro 21-OCT-04 115.19 3963090 Changed promotion procedure to
49 REM pick up multiple promotions
50 REM ynegoro 22-OCT-04 115.20 3878442 Defined the following variables
51 REM as local variables
52 REM m_app_count
53 REM f_app_count
54 REM m_terminate_count
55 REM f_terminate_count
56 REM m_hire_count
57 REM f_hire_count
58 REM m_fte_count
59 REM f_fte_count
60 REM m_promotion_count
61 REM f_promotion_count
62 REM ynegoro 03-NOV-04 115.21 3993335 Added p_eff_start_date and
63 REM p_eff_end_date parameters to
64 REM promotion procedure
65 REM ynegoro 15-JUN-05 115.22 4434130 Updated c_app_term_assignments,
66 REM c_persons,c_person_infm cursors
67 REM to pick up rehired employees in
68 REM different job group
69 REM ynegoro 20-JUN-05 115.23 4445250 Updated c_persons cursor to
70 REM pick up correct terminated
71 REM employees
72 REM rpasumar 15-JUL-2007 115.24 5982927 Modified the report so that
73 REM it won't consider the changes to eeo1 job categories
74 REM and US ethnic group lookup changes.
75 REM ========================================================================
76
77
78
79 --------------------Global variables-------------------------------------------
80 To store the establishment information and fein number this table is
81 declared globally. */
82
83 TYPE establishment IS RECORD(
84 entity_id per_gen_hierarchy_nodes.entity_id%TYPE,
85 hierarchy_node_id per_gen_hierarchy_nodes.hierarchy_node_id%TYPE,
86 fein varchar2(100), -- BUG3894120
87 location_name varchar2(1000),
88 est_flag varchar2(1));
89 est_rec establishment;
90 TYPE est IS TABLE OF est_rec%TYPE
91 INDEX BY BINARY_INTEGER;
92 est_infm est;
93
94 minority_code number;
95 ethnic_group_code varchar2(2);
96 monetary_comp number :=null;
97 tenure_years number :=0;
98 tenure_months number :=0;
99 l_est_flag varchar2(1);
100 l_est_name varchar2(100);
101 l_est_fein varchar2(100);
102 l_est_id number;
103 fte_flag varchar2(1);
104
105 p_fein varchar2(100);
106 p_location_name varchar2(100);
107 l_seq_num number;
108
109 PROCEDURE app_fire_count (p_est_entity_id in number,
110 p_hierarchy_version_id in number,
111 p_period_start in date,
112 p_period_end in date,
113 p_seq_num in number)
114 IS
115
116 /* This cursor picks up all the establishments and locations for a given
117 hierarchy version id. If an establishment is given then it picks up the
118 locations under that establishment and including the entity_id of that est.*/
119
120 CURSOR c_est_loc
121 (l_est_id per_gen_hierarchy_nodes.entity_id%type,
122 l_hierarchy_version_id per_gen_hierarchy_versions.hierarchy_version_id%type)
123 IS
124 SELECT entity_id,parent_hierarchy_node_id
125 FROM per_gen_hierarchy_nodes
126 WHERE hierarchy_version_id = l_hierarchy_version_id
127 AND (
128 (
129 entity_id = nvl(l_est_id,entity_id)
130 AND node_type ='EST'
131 )
132 OR
133 (
134 parent_hierarchy_node_id in(select hierarchy_node_id from per_gen_hierarchy_nodes
135 where hierarchy_version_id =l_hierarchy_version_id
136 and entity_id = nvl(l_est_id,entity_id)
137 and node_type = 'EST')
138 AND node_type = 'LOC'
139 )
140 );
141
142
143 /* This cursor picks up the applicants and the terminated employees
144 from the given location.*/
145
146 CURSOR c_app_term_assignments
147 ( l_location_id per_gen_hierarchy_nodes.entity_id%type,
148 l_period_end date,
149 l_period_start per_assignments_f.effective_start_date%type
150 )
151 IS
152 /* Retrieve applicants only */
153 SELECT paf.assignment_id,
154 paf.person_id,
155 pj.job_information1 job_category,
156 paf.assignment_status_type_id
157 ,'APPLICANT'
158 FROM per_assignments_f paf,
159 per_jobs pj,
160 per_assignment_status_types past,
161 fnd_common_lookups fcl
162 WHERE paf.assignment_status_type_id = past.assignment_status_type_id
163 AND --((
164 paf.assignment_type = 'A'
165 and paf.effective_end_date >= l_period_start
166 and paf.effective_start_date <= l_period_end
167 --)
168 -- or (paf.assignment_type = 'E'
169 -- and paf.primary_flag = 'Y'
170 -- and paf.effective_start_date between
171 -- l_period_start and l_period_end)
172 -- )
173 AND paf.effective_start_Date = (select max(paf1.effective_Start_date)
174 from per_assignments_f paf1
175 where paf1.assignment_id = paf.assignment_id
176 and paf1.effective_start_Date <=l_period_end
177 and paf1.assignment_status_type_id =
178 paf.assignment_Status_type_id)
179 --AND past.per_system_status in ('ACTIVE_APL','ACCEPTED','TERM_ASSIGN','SUSP_ASSIGN')
180 AND past.per_system_status in ('ACTIVE_APL','ACCEPTED','SUSP_ASSIGN')
181 AND pj.job_id = paf.job_id
182 AND pj.job_information1 = fcl.lookup_code
183 AND fcl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
184 AND fcl.lookup_code <> '10'
185 AND paf.location_id = l_location_id
186 UNION
187 /* Retrieve terminated employees only */
188 SELECT paf.assignment_id
189 ,paf.person_id
190 ,pj.job_information1 job_category
191 ,paf.assignment_status_type_id
192 ,ppt.system_person_type
193 FROM per_people_f ppf
194 ,per_assignments_f paf
195 ,per_periods_of_service pps
196 ,per_person_types ppt
197 ,per_jobs pj
198 ,fnd_common_lookups fcl
199 WHERE pps.person_id = paf.person_id
200 and pps.actual_termination_date is not null
201 and pps.actual_termination_date
202 between l_period_start and l_period_end
203 /* BUG4434130
204 and ppf.effective_start_date =
205 (select max(ppf2.effective_start_date)
206 from per_people_f ppf2
207 where ppf2.person_id = ppf.person_id
208 and ppf2.current_employee_flag is null
209 )
210 */
211 and pps.date_start = ppf.effective_start_date
212 and pps.actual_termination_date between
213 ppf.effective_start_date and ppf.effective_end_date
214 -- End of BUG4434130
215 and ppf.person_id = paf.person_id
216 and pps.actual_termination_date between
217 paf.effective_start_date and paf.effective_end_date
218 and ppf.person_type_id = ppt.person_type_id
219 And paf.assignment_type = 'E'
220 And paf.primary_flag = 'Y'
221 AND pj.job_id = paf.job_id
222 AND pj.job_information1 = fcl.lookup_code
223 AND fcl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
224 AND fcl.lookup_code <> '10'
225 AND paf.location_id = l_location_id
226 order by 2;
227
228
229 /* This cursor picks up the person's information.*/
230
231 CURSOR c_persons
232 (l_person_id per_people_f.person_id%type,
233 l_period_start date,
234 l_period_end date)
235 is
236 select ppf.per_information1 race_code
237 ,ppf.sex sex
238 ,ppt.system_person_type person_type
239 ,pj.job_information1 job_category -- BUG4434130
240 from per_people_f ppf
241 ,per_person_types ppt
242 ,per_assignments_f paf -- BUG4434130
243 ,per_jobs pj -- BUG4434130
244 where ppf.effective_start_date <= l_period_end
245 and ppf.effective_end_date >= l_period_start
246 and ppf.person_type_id = ppt.person_type_id
247 -- and ppt.system_person_type not in ( 'EMP_APL','EMP')
248 and ppt.system_person_type <> 'EMP' -- BUG3878442
249 and ppf.effective_start_Date
250 = (select max(effective_Start_date)
251 from per_people_f ppf1
252 where ppf1.person_type_id = ppf.person_type_id
253 and ppf1.effective_start_Date <=l_period_end
254 and ppf1.person_id =ppf.person_id
255 )
256 and ppt.system_person_type in ('APL','APL_EX_APL','EX_APL','EX_EMP_APL','EMP_APL') -- BUG3878442
257 and ppf.person_id = l_person_id
258 -- BUG4434130
259 and paf.person_id = ppf.person_id
260 and paf.effective_start_date = ppf.effective_start_date
261 and paf.job_id = pj.job_id
262 -- End of BUG4434130
263 -- BUG4434130
264 /* Retrieve terminated employees */
265 UNION
266 select ppf.per_information1 race_code
267 ,ppf.sex sex
268 ,'EX_EMP' person_type
269 ,pj.job_information1 job_category
270 from per_people_f ppf
271 ,per_periods_of_service pps
272 ,per_assignments_f paf
273 ,per_jobs pj
274 where ppf.person_id = l_person_id
275 and pps.person_id = ppf.person_id
276 and pps.actual_termination_date is not null
277 and pps.actual_termination_date between
278 l_period_start and l_period_end
279 and paf.person_id = ppf.person_id
280 and pps.date_start = ppf.effective_start_date -- BUG4445250
281 and paf.effective_start_date = ppf.effective_start_date
282 and paf.job_id = pj.job_id;
283 -- End of BUG4434130
284
285
286 /* An assignment_type of 'A' is checked if its an ACCEPTED assignment.*/
287
288 CURSOR applicant_accepted(l_asgn_status_id
289 per_assignment_status_types.assignment_status_type_id%type)
290 is
291 select 'x'
292 from per_assignment_status_types
293 where per_system_status = 'ACCEPTED'
294 and assignment_status_type_id = l_asgn_status_id;
295
296 /* This cursor checks if applicant was also an ACTIVE_APL in the same period.
297 If he had both the status in the same period then he should be counted just once.*/
298
299 CURSOR applicant_active(l_asgn_id per_assignments_f.assignment_id%type,
300 l_period_start date,
301 l_period_end date)
302 is
303 select 'x'
304 from per_assignments_f paf,
305 per_assignment_Status_types past
306 where paf.assignment_id = l_asgn_id
307 and paf.assignment_type = 'A'
308 and paf.assignment_status_type_id = past.assignment_status_type_id
309 and past.per_system_status = 'ACTIVE_APL'
310 and paf.effective_start_date >= l_period_start
311 and paf.effective_end_date <= l_period_end;
312
313
314 CURSOR c_race_code
315 (l_person_id per_people_f.person_id%type,
316 l_period_start date,
317 l_period_end date)
318 is
319 select ppf.per_information1 race_code,
320 ppt.system_person_type person_type
321 from per_people_f ppf
322 ,per_person_types ppt
323 ,per_periods_of_service pps
324 where ppf.effective_start_date <= l_period_end
325 and ppf.effective_end_date >= l_period_start
326 and ppf.per_information1 is not NULL
327 and ppf.person_type_id = ppt.person_type_id
328 and ppt.system_person_type = 'EMP'
329 and pps.person_id = ppf.person_id
330 and ppf.effective_start_date = pps.date_start
331 and ppf.person_id = l_person_id;
332
333 fein varchar2(30);
334 location_name varchar2(100);
335 ethnic_group_code varchar2(2);
336 l_app_count varchar2(1) := null;
337 l_entity_id number;
338 l_version_id number;
339 l_person_id number;
340 l_accepted_flag varchar2(1);
341 l_active_flag varchar2(1);
342 l_race_code varchar2(40); -- BUG3941606
343 l_person_type varchar2(40); -- BUG3941606
344 l_package varchar2(70);
345
346
347 -- Defined local variables BUG3878442
348 m_app_count number:=0;
349 f_app_count number:=0;
350 m_terminate_count number :=0;
351 f_terminate_count number :=0;
352 m_hire_count number :=0;
353 f_hire_count number :=0;
354 m_fte_count number :=0;
355 f_fte_count number :=0;
356 m_promotion_count number :=0;
357 f_promotion_count number :=0;
358
359 begin
360 --hr_utility.trace_on(null,'ORACLE');
361 l_package := 'pay_eosurvey_pkg.app_fire_count';
362
363 hr_utility.trace('==============================app_fire_count==================================');
364 hr_utility.set_location('Entering.. ' || l_package,10);
365 fte_flag:='N';
366
367 FOR est in c_est_loc(p_est_entity_id ,
368 p_hierarchy_version_id )
369 LOOP
370
371 hr_utility.trace('Inside Loop1.location_id = ' || est.entity_id);
372
373 /* For the selected location assignments are picked up.*/
374
375 FOR app_term in c_app_term_assignments( est.entity_id
376 ,p_period_end,p_period_start)
377 LOOP
378 hr_utility.trace('Inside Loop2.assignment id = '|| to_char(app_term.assignment_id));
379
380 hr_utility.trace('l_person_id = '||to_char(l_person_id));
381 hr_utility.trace('app_term.person_id = '||to_char(app_term.person_id));
382 hr_utility.set_location(l_package||':person_id='||app_term.person_id,20);
383 hr_utility.trace('assignment_id = '||to_char(app_term.assignment_id));
384
385 /*An assignment will be picked up twice if it has two different status
386 in the same period for the same person.To avoid running the person
387 loop twice checking is done here.*/
388
389 IF (l_person_id IS NULL OR l_person_id <> app_term.person_id) THEN
390
391 hr_utility.set_location(l_package||':person_id='||app_term.person_id,30);
392 hr_utility.trace('assignment_id = '||app_term.assignment_id);
393
394 FOR per in c_persons(app_term.person_id,
395 p_period_start,
396 p_period_end)
397 LOOP
398
399 hr_utility.set_location(l_package||':person_id='||app_term.person_id,40);
400 hr_utility.trace('Inside Loop3.Person_id = '||app_term.person_id);
401 hr_utility.trace('Person_type = '||per.person_type);
402 hr_utility.trace('Job_category= '||per.job_category);
403
404 /*If the assignment is an Applicant then he is checked for ACCEPTED
405 and checked for ACTIVE_APL also*/
406
407 -- IF per.person_type in ('APL','APL_EX_APL','EX_EMP_APL') then
408 IF per.person_type in ('APL','APL_EX_APL','EX_EMP_APL','EMP_APL') then -- BUG3878442
409 hr_utility.set_location(l_package||':person_id='||app_term.person_id,50);
410
411 OPEN applicant_accepted(app_term.assignment_status_type_id);
412 FETCH applicant_accepted into l_accepted_flag;
413 hr_utility.trace('After applicant_accepted. l_accepted_flag = '
414 ||l_accepted_flag);
415
416 IF applicant_accepted%found and l_accepted_flag IS NOT NULL THEN
417
418 CLOSE applicant_accepted;
419 hr_utility.set_location(l_package||':person_id='||app_term.person_id,60);
420
421 /* The applicant has a status ACCEPTED.
422 Check if he is also with the status ACTIVE_APL in the same period.*/
423
424 OPEN applicant_active(app_term.assignment_id,
425 p_period_Start,
426 p_period_end);
427 FETCH applicant_active into l_active_flag;
428 hr_utility.trace('After applicant_active. l_active_flag = '
429 ||l_active_flag);
430
431 /*If not then count him as an applicant.Which means that this
432 applicant was with the status just ACCEPTED.*/
433
434 IF applicant_active%notfound and l_active_flag IS NULL THEN
435
436 CLOSE applicant_active;
437 hr_utility.set_location(l_package||':person_id='||app_term.person_id,70);
438 hr_utility.trace('After applicant_active%notfound ');
439 male_female_count(per.sex,
440 m_app_count,
441 f_app_count);
442 ELSE
443
444 CLOSE applicant_active;
445 hr_utility.set_location(l_package||':person_id='||app_term.person_id,80);
446
447 END IF;
448
449 ELSE /* The applicant is with status ACTIVE_APL. So count him.*/
450
451 hr_utility.set_location(l_package||':person_id='||app_term.person_id,90);
452 hr_utility.trace('The applicant is with status ACTIVE_APL. So count him.');
453 CLOSE applicant_accepted;
454 male_female_count(per.sex,
455 m_app_count,
456 f_app_count);
457 END IF;
458
459 ELSIF per.person_type = 'EX_EMP' then -- BUG4434130
460 hr_utility.set_location(l_package||':person_id='||app_term.person_id,100);
461 hr_utility.set_location(l_package||':asg_id ='||app_term.assignment_id,101);
462 hr_utility.set_location(l_package||':job_category='||per.job_category,102);
463
464 hr_utility.trace('Person is TERMINATED = '||app_term.person_id);
465
466 male_female_count(per.sex,
467 m_terminate_count,
468 f_terminate_count);
469
470 END IF;
471 hr_utility.set_location(l_package||':person_id='||app_term.person_id,110);
472
473
474 /* To categorise on what ethnic group they are belonging this procedure
475 is called.*/
476
477 IF (per.person_type in ('APL','APL_EX_APL','EX_APL','EX_EMP_APL')
478 AND per.race_code is NULL) THEN
479
480 hr_utility.set_location(l_package||':person_id='||app_term.person_id,120);
481 hr_utility.trace('Race code is null.so setting the value to 0');
482 hr_utility.trace('For person '||to_char(app_term.person_id));
483
484 --
485 -- The following statements are added by BUG3491606
486 --
487 open c_race_code(app_term.person_id
488 ,p_period_start
489 ,p_period_end);
490 fetch c_race_code into l_race_code, l_person_type;
491 if c_race_code%FOUND then
492 close c_race_code;
493 hr_utility.set_location(l_package||':person_id='||app_term.person_id,130);
494 hr_utility.trace('l_race_code = ' || l_race_code);
495 hr_utility.trace('l_person_type = ' || l_person_type);
496
497 minority(per.sex
498 ,l_race_code
499 ,minority_code
500 ,ethnic_group_code);
501 else
502 close c_race_code;
503 hr_utility.set_location(l_package||':person_id='||app_term.person_id,140);
504 ethnic_group_code:= 0;
505 minority_code:=null;
506 end if;
507
508
509 ELSE
510
511 hr_utility.set_location(l_package||':person_id='||app_term.person_id,150);
512 hr_utility.trace('Race code is not null.so calling the pkg minority');
513 hr_utility.trace('person_id = '||app_term.person_id);
514 hr_utility.trace('per.person_type = '||per.person_type);
515 hr_utility.trace('per_information1 = '||per.race_code);
516
517 minority(per.sex,
518 per.race_code,
519 minority_code,
520 ethnic_group_code);
521 END IF;
522
523 hr_utility.set_location(l_package||':person_id='||app_term.person_id,160);
524 hr_utility.trace('After calling minority before inserting');
525
526 /* The location_id is compared with the entity_id of the establishment.
527 It is also compared with the parent_hierarchy_node_id.If it is equal
528 then the fein and location_name is passed.*/
529
530 For i in 1..est_infm.count LOOP
531
532 IF est_infm(i).entity_id = est.entity_id THEN
533
534 hr_utility.trace('entity_id = '|| est.entity_id);
535
536 l_est_name:=est_infm(i).location_name;
537 l_est_fein:=est_infm(i).fein;
538 l_est_flag:=est_infm(i).est_flag;
539 l_est_id:=est_infm(i).entity_id;
540
541 EXIT ;
542 ELSIF est_infm(i).hierarchy_node_id=est.parent_hierarchy_node_id THEN
543
544 hr_utility.trace('location LOC = '|| est.entity_id);
545
546 l_est_name:=est_infm(i).location_name;
547 l_est_fein:=est_infm(i).fein;
548 l_est_flag:='N';
549 l_est_id:=est_infm(i).entity_id;
550 EXIT ;
551
552 END IF;
553
554 END LOOP;
555
556 hr_utility.set_location(l_package||':person_id='||app_term.person_id,170);
557 p_insert(
558 l_est_id,
559 p_seq_num,
560 est.entity_id,
561 l_est_name,
562 l_est_fein,
563 app_term.assignment_id,
564 app_term.person_id,
565 per.job_category, -- app_term.job_category, BUG4434130
566 per.race_code,
567 per.person_type,
568 m_app_count,
569 f_app_count,
570 m_hire_count,
571 f_hire_count,
572 m_terminate_count,
573 f_terminate_count,
574 m_promotion_count,
575 f_promotion_count,
576 m_fte_count,
577 f_fte_count,
578 monetary_comp,
579 tenure_years,
580 tenure_months,
581 minority_code,
582 ethnic_group_code,
583 l_est_flag,
584 fte_flag);
585
586 -- Initialize local variables
587 m_app_count:=0;
588 f_app_count:=0;
589 m_hire_count:=0;
590 f_hire_count:=0;
591 m_terminate_count:=0;
592 f_terminate_count:=0;
593 m_promotion_count:=0;
594 f_promotion_count:=0;
595 m_fte_count:=0;
596 f_fte_count:=0;
597
598 end loop;
599
600 l_person_id:=app_term.person_id;
601
602 END IF;
603 hr_utility.set_location(l_package||':person_id='||app_term.person_id,180);
604 hr_utility.trace('After fifth loop');
605 end loop;
606 hr_utility.set_location(l_package,190);
607 hr_utility.trace('Afterfourth loop');
608 end loop;
609 hr_utility.trace('==============================end app_fire_count==================================');
610
611 hr_utility.set_location('Leaving.. ' || l_package,200);
612 end app_fire_count;
613
614
615 procedure find_persons(p_pactid in pay_assignment_actions.payroll_action_id%type
616 ,p_thread in number)
617
618 is
619
620 /* This cursor picks up the legislative_paramters and the end date for the
621 the given pactid . */
622
623 CURSOR c_leg_param(l_pact_id pay_assignment_actions.payroll_action_id%type)
624 IS
625 SELECT ppa.legislative_parameters,ppa.start_date,ppa.effective_date
626 FROM pay_payroll_actions ppa
627 WHERE ppa.payroll_action_id =l_pact_id;
628
629 /* This cursor picks up the defined balance_id for the new balance
630 EO Regular Salary YTD */
631
632 CURSOR c_defined_balance_id
633 IS
634 SELECT pdb.defined_balance_id
635 FROM pay_defined_balances pdb,
636 pay_balance_dimensions pbd,
637 pay_balance_types pbt
638 WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
639 AND pbd.database_item_suffix = '_ASG_YTD'
640 AND pbd.legislation_code = 'US'
641 AND pdb.balance_type_id = pbt.balance_type_id
642 AND pbt.balance_name = 'EO Regular Salary Year to Date'
643 AND pbt.legislation_code = 'US'
644 AND pdb.legislation_code = 'US';
645
646 /* This cursor picks up all the establishments for a given hierarchy_version_id.
647 If an establishment is specified then it picks up the infm for that est alone. */
648
649 CURSOR c_est_id(l_version_id per_gen_hierarchy_nodes.hierarchy_version_id%type,
650 l_entity_id per_gen_hierarchy_nodes.entity_id%type)
651 IS
652 SELECT entity_id,hierarchy_node_id
653 FROM per_gen_hierarchy_nodes
654 WHERE node_type = 'EST'
655 AND entity_id =nvl(l_entity_id,entity_id)
656 AND hierarchy_version_id = l_version_id;
657
658 /* This cursor picks up all the assignment actions that has been
659 inserted in pay_assignment_Actions for a specific chunk and pactid.
660 It also picks up the person_id stored in serial_number. */
661
662 CURSOR c_fte_asgn(l_pactid pay_payroll_actions.payroll_action_id%type,
663 l_chunk pay_assignment_actions.chunk_number%type)
664 IS
665 SELECT assignment_action_id,
666 assignment_id,
667 serial_number,
668 source_action_id location_id
669 FROM pay_assignment_actions
670 WHERE payroll_action_id = l_pactid
671 AND chunk_number = l_chunk;
672
673 /* This cursor picks up the max of asact_id which has been locked in pay_action_interlocks.
674 this was selected during action creation. */
675
676 CURSOR c_max_asact_id(l_locking_asact_id pay_assignment_actions.assignment_action_id%type)
677 IS
678 SELECT locked_action_id
679 FROM pay_action_interlocks
680 WHERE locking_action_id = l_locking_asact_id;
681
682 /* This cursor selects the person information like race, job_category only for employees
683 and EMP_APL. */
684
685 CURSOR c_person_infm( l_person_id per_assignments_f.person_id%type,
686 l_period_start date,
687 l_period_end date)
688
689 IS
690
691 SELECT ppf.sex,
692 ppf.person_id,
693 ppt.system_person_type person_type,
694 ppf.effective_start_Date eff_Start,
695 ppf.effective_end_date eff_end,
696 ppf.per_information1 race,
697 ppf.person_type_id,pps.date_start service_start
698 FROM per_people_f ppf,
699 per_person_types ppt,
700 per_periods_of_service pps
701 WHERE ( ( ppt.system_person_type = 'EMP'
702 /* BUG4434130
703 and ppf.effective_start_date
704 = (select max(ppf1.effective_start_date)
705 from per_people_f ppf1
706 where ppf1.person_type_id = ppf.person_type_id
707 and ppf1.person_id = ppf.person_id
708 and ppf1.effective_start_date<=l_period_end
709 )
710 */
711 and ppf.effective_start_Date <=l_period_end
712 and ppf.effective_end_date >= l_period_start
713 and pps.date_start = ppf.effective_start_date -- BUG4434130
714 )
715 OR
716 ( ppt.system_person_type = 'EMP_APL'
717 and ppf.effective_start_date
718 = (select max(ppf2.effective_Start_date)
719 from per_people_f ppf2
720 where ppf2.person_id = ppf.person_id
721 and ppf2.person_type_id = ppf.person_type_id
722 and ppf2.effective_start_Date <=l_period_end
723 and ppf2.effective_end_date >= l_period_start
724 )
725 )
726 )
727 and ppf.person_id =l_person_id
728 and ppt.person_type_id = ppf.person_type_id
729 and pps.person_id = ppf.person_id;
730
731 CURSOR assignment_details(l_person_id per_people_f.person_id%TYPE,
732 l_location_id per_assignments_f.location_id%TYPE,
733 l_person_type per_person_types.system_person_type%TYPE,
734 l_period_start date,
735 l_period_end date)
736 IS
737 SELECT distinct paf.person_id,
738 pj.job_information1 job,
739 paf.assignment_type
740 ,paf.assignment_id
741 ,paf.effective_start_date
742 ,paf.effective_end_date
743 FROM per_assignments_f paf,
744 per_jobs pj,
745 fnd_common_lookups fcl
746 WHERE paf.person_id =l_person_id
747 AND pj.job_id = paf.job_id
748 AND ( ( paf.assignment_type = 'A'
749 and l_person_type = 'EMP_APL'
750 )
751 or (paf.assignment_type = 'E'
752 and l_person_type = 'EMP'
753 )
754 )
755 AND paf.effective_start_Date <= l_period_end
756 AND paf.effective_end_Date >= l_period_start
757 AND paf.location_id = l_location_id
758 AND pj.job_information1=fcl.lookup_code
759 AND fcl.lookup_code <> '10'
760 AND fcl.lookup_type = 'US_EEO1_JOB_CATEGORIES';
761
762
763 l_est_entity_id number;
764 l_hierarchy_version_id number;
765 l_min_hours number;
766 l_defined_balance_id number;
767 l_leg_param pay_payroll_actions.legislative_parameters%type;
768 l_period_end date;
769 l_period_start date;
770 l_est_count number;
771 l_max_asact_id pay_assignment_Actions.assignment_action_id%type;
772 l_location_id per_assignments_f.location_id%TYPE;
773 l_person_id per_people_f.person_id%type;
774 l_job varchar2(10);
775 l_assignment_type varchar2(10);
776 l_package varchar2(70);
777 l_effective_start_date date; -- BUG3940867
778 l_effective_end_date date; -- BUG3940867
779 l_assignment_id number; -- 18-OCT-04
780 l_asg_eff_start_date date; -- BUG3958260
781 l_asg_eff_end_date date; -- BUG3958260
782
783 begin
784 --hr_utility.trace_on(null,'ORACLE');
785 l_package := 'pay_eosurvey_pkg.find_persons';
786
787 hr_utility.trace('=============================== find_persons==================================');
788
789 hr_utility.set_location('Entering.. ' || l_package,10);
790 hr_utility.trace('The p_pactid = '||to_char(p_pactid));
791 hr_utility.trace('The thread = '||to_char(p_thread));
792
793 hr_utility.trace('The parameters');
794 hr_utility.trace('l_hierarchy_vsn_id = '||to_char(l_hierarchy_version_id));
795 hr_utility.trace('l_est_entity_id = '||to_char(l_est_entity_id));
796 hr_utility.trace('l_seq_num = '||to_char(l_seq_num));
797
798 OPEN c_leg_param(p_pactid);
799 FETCH c_leg_param INTO l_leg_param,l_period_end,l_period_start;
800 CLOSE c_leg_param;
801
802 hr_utility.trace('l_period_start = '||to_char(l_period_start));
803 hr_utility.trace('l_period_end = '||to_char(l_period_end));
804
805 /* All the parameters are picked up from the legislative parameters.*/
806
807 l_hierarchy_version_id := pay_eosy_ac_pkg.get_parameter
808 ('HI_VER_ID',l_leg_param);
809 l_est_entity_id := pay_eosy_ac_pkg.get_parameter
810 ('EST_ID',l_leg_param);
811 l_min_hours := pay_eosy_ac_pkg.get_parameter
812 ('MIN_HRS',l_leg_param);
813 l_seq_num:=pay_eosy_ac_pkg.get_parameter
814 ('S_N',l_leg_param);
815
816 /* The establishment information is calculated using
817 a procedure gre_name and the values are stored in the table.*/
818
819
820 l_est_count:=1;
821
822 FOR est_id in c_est_id(l_hierarchy_version_id,l_est_entity_id)
823 LOOP
824
825 hr_utility.trace('Before calling gre');
826
827 IF p_thread = 1 THEN
828
829 job_race_insert(est_id.entity_id,
830 l_seq_num);
831 END IF;
832
833 gre_name(est_id.entity_id,
834 l_hierarchy_version_id,
835 p_fein,
836 p_location_name);
837
838 hr_utility.trace('After calling gre');
839
840 est_infm(l_est_count).entity_id:=est_id.entity_id;
841 est_infm(l_est_count).hierarchy_node_id:=est_id.hierarchy_node_id;
842 est_infm(l_est_count).fein:=p_fein;
843 est_infm(l_est_count).location_name:=p_location_name;
844 est_infm(l_est_count).est_flag:='Y';
845
846 -- Bug# 5982927
847 update pay_us_rpt_totals
848 set gre_name = p_fein,
849 location_name = p_location_name
850 where session_id = est_id.entity_id
851 and business_group_id = l_seq_num;
852
853 commit;
854
855 l_est_count :=l_est_count+1;
856
857 hr_utility.trace('After inserting the values.entity_id = '|| est_id.entity_id);
858
859 END LOOP;
860
861 hr_utility.trace('After est_id loop before c_defined_balance_id loop');
862
863 IF p_thread = 1 THEN
864
865 /* This procedure is called to find out the applicants and the terminations.
866 Just the version_id is passed from which the locations should be calculated
867 within the procedure. */
868
869 hr_utility.trace('before calling app_fire_count');
870
871 app_fire_count(l_est_entity_id,
872 l_hierarchy_version_id,
873 l_period_start,
874 l_period_end,
875 l_seq_num);
876
877 hr_utility.trace('after calling app_fire_count');
878
879 END IF;
880
881 OPEN c_defined_balance_id;
882 FETCH c_defined_balance_id INTO l_defined_balance_id;
883 CLOSE c_defined_balance_id;
884
885 hr_utility.trace('l_defined_balance_id = '||to_char(l_defined_balance_id));
886 hr_utility.trace('full time assignments are picked up');
887
888 /* All the assignments are picked up and their corresponding
889 person_id and location_id also. */
890
891 hr_utility.set_location(l_package,20);
892 --FOR fte_asgn in c_fte_asgn(p_pactid,p_thread)
893 FOR fte_asgn in c_fte_asgn(p_pactid,p_thread)
894 LOOP
895 hr_utility.set_location(l_package,30);
896 hr_utility.trace('fte_asgn.assignment_action_id = ' || fte_asgn.assignment_action_id);
897 hr_utility.trace('fte_asgn.person_id = '||fte_asgn.serial_number);
898 hr_utility.trace('fte_asgn.assignment_id = ' || fte_asgn.assignment_id);
899
900 OPEN c_max_asact_id(fte_asgn.assignment_action_id);
901 FETCH c_max_asact_id INTO l_max_asact_id;
902 CLOSE c_max_asact_id;
903 hr_utility.trace('l_max_asact_id = ' || l_max_asact_id);
904
905 FOR per in c_person_infm(fte_asgn.serial_number,l_period_start,l_period_end)
906 LOOP
907 /*This cursor picks up the person records for each person_type EMP and EMP_APL
908 if exists.*/
909
910 hr_utility.set_location(l_package,40);
911 hr_utility.trace('per.person_type = '||per.person_type);
912 hr_utility.trace('per.eff_start = '||per.eff_start);
913 hr_utility.trace('per.eff_end = '||per.eff_end);
914
915 /*This cursor gets the location and job information.*/
916 --
917 -- the following if statements are added by BUG3940867
918 --
919 if (per.eff_start < l_period_start) then
920 l_effective_start_date := l_period_start;
921 else
922 l_effective_start_date := per.eff_start;
923 end if;
924
925 if (per.eff_end > l_period_end) then
926 l_effective_end_date := l_period_end;
927 else
928 l_effective_end_date := per.eff_end;
929 end if;
930 OPEN assignment_details(fte_asgn.serial_number
931 ,fte_asgn.location_id
932 ,per.person_type
933 ,l_effective_start_date -- l_period_start
934 ,l_effective_end_date -- l_period_end
935 );
936 FETCH assignment_details INTO l_person_id,l_job,l_assignment_type
937 ,l_assignment_id -- 18-OCT-04
938 ,l_asg_eff_start_date
939 ,l_asg_eff_end_date;
940
941 hr_utility.trace('preson_id.l_job = '||fte_asgn.serial_number|| '.' ||l_job);
942 hr_utility.trace('l_assignment_type = '||l_assignment_type);
943 hr_utility.trace('l_assignment_id = '||l_assignment_id);
944 hr_utility.trace('l_asg_eff_start_date= '||l_asg_eff_start_date);
945 hr_utility.trace('l_asg_eff_end_date = '||l_asg_eff_end_date);
946
947 if (l_effective_start_date < l_asg_eff_start_date) then
948 l_effective_start_date := l_asg_eff_start_date;
949 hr_utility.set_location(l_package,45);
950 end if;
951
952 IF assignment_details%FOUND THEN
953
954
955 /*This procedure calculates the tenure,new hire,fte and monetary infm.*/
956
957 hr_utility.set_location(l_package,50);
958 hire_or_fte(l_assignment_id, -- fte_asgn.assignment_id, 18-OCT-04
959 fte_asgn.serial_number,
960 l_period_start,
961 l_period_end,
962 per.eff_start,
963 per.eff_end,
964 per.service_start,
965 l_assignment_type,
966 per.sex,
967 l_job,
968 per.race,
969 per.person_type,
970 fte_asgn.location_id,
971 l_hierarchy_version_id,
972 l_min_hours,
973 l_defined_balance_id,
974 l_max_asact_id,
975 l_seq_num
976 ,l_effective_start_date -- BUG3940867
977 ,l_effective_end_date -- BUG3940867
978 );
979 END IF;
980 CLOSE assignment_details;
981
982 END LOOP;
983 END LOOP;
984 hr_utility.trace('=============================== END find_persons==================================');
985 hr_utility.set_location('Leaving.... ' || l_package,100);
986 END find_persons;
987
988
989 PROCEDURE p_insert (
990 p_entity_id in number,
991 p_seq_num in number,
992 p_location_id in number,
993 p_location_name in varchar2,
994 fein in varchar2 ,
995 p_assignment_id in number ,
996 p_person_id in number ,
997 p_job_category in varchar2,
998 p_race_code in varchar2 ,
999 p_person_type in varchar2,
1000 p_m_app_count in number ,
1001 p_f_app_count in number ,
1002 p_m_hire_count in number ,
1003 p_f_hire_count in number ,
1004 p_m_terminate_count in number ,
1005 p_f_terminate_count in number ,
1006 p_m_promotion_count in number ,
1007 p_f_promotion_count in number ,
1008 p_m_fte_count in number ,
1009 p_f_fte_count in number ,
1010 p_monetary_comp in number ,
1011 p_tenure_years in number ,
1012 p_tenure_months in number ,
1013 p_minority_code in varchar2,
1014 p_ethnic_group_code in varchar2,
1015 p_est_flag in varchar2,
1016 p_fte_flag in varchar2)
1017 IS
1018 n number;
1019 l_ethnic_group_code number;
1020
1021 BEGIN
1022 hr_utility.trace('=============================== p_insert==================================');
1023 hr_utility.trace('-------------------');
1024 hr_utility.trace('p_entity_id = ' || to_char(p_entity_id));
1025 hr_utility.trace('business_group_id = ' || to_char(p_seq_num));
1026 hr_utility.trace('p_person_id = ' || to_char(p_person_id));
1027 hr_utility.trace('p_assignment_id = ' || to_char(p_assignment_id));
1028 hr_utility.trace('location_id = ' || to_char(p_entity_id));
1029 hr_utility.trace('p_job_category = ' || p_job_category);
1030 hr_utility.trace('p_m_hire_count = ' || p_m_hire_count);
1031 hr_utility.trace('p_f_hire_count = ' || p_f_hire_count);
1032 hr_utility.trace('p_m_app_count = ' || p_m_app_count);
1033 hr_utility.trace('p_f_app_count = ' || p_f_app_count);
1034 hr_utility.trace('p_m_terminate_cnt = ' || p_m_terminate_count);
1035 hr_utility.trace('p_f_terminate_cnt = ' || p_f_terminate_count);
1036 hr_utility.trace('p_m_promotion_cnt = ' || p_m_promotion_count);
1037 hr_utility.trace('p_f_promotion_cnt = ' || p_f_promotion_count);
1038 hr_utility.trace('p_m_fte_count = ' || p_m_fte_count);
1039 hr_utility.trace('p_f_fte_count = ' || p_f_fte_count);
1040 hr_utility.trace('p_minority_code = ' || p_minority_code);
1041 hr_utility.trace('p_tenure_years = ' || p_tenure_years);
1042 hr_utility.trace('p_tenure_months = ' || p_tenure_months);
1043 hr_utility.trace('gre_name = ' || fein);
1044 hr_utility.trace('p_ethnic_grp_code = ' || p_ethnic_group_code);
1045
1046 /*Inserting the records twice if the person is in
1047 either once of the following race:
1048 Hispanic or Latino (White race only) Hispanic or Latino (all other races) */
1049
1050 IF p_ethnic_group_code in ('7','8') THEN
1051
1052 n:=2;
1053 l_ethnic_group_code := p_ethnic_group_code;
1054
1055 ELSIF p_ethnic_group_code IS NULL THEN
1056
1057 l_ethnic_group_code:=null;
1058 n:=0;
1059
1060 ELSE
1061
1062 n:=1;
1063 l_ethnic_group_code := p_ethnic_group_code;
1064
1065 END IF;
1066
1067 FOR i in 1 .. n LOOP
1068
1069 IF i = 2 then
1070 l_ethnic_group_code:=6;
1071 END If;
1072
1073 INSERT INTO pay_us_rpt_totals
1074 ( session_id,
1075 business_group_id,
1076 location_id,
1077 location_name,
1078 gre_name,
1079 tax_unit_id,
1080 organization_id,
1081 attribute1,
1082 attribute2,
1083 attribute3,
1084 value1,
1085 value2,
1086 value3,
1087 value4,
1088 value5,
1089 value6,
1090 value7,
1091 value8,
1092 value9,
1093 value10,
1094 value11,
1095 value12,
1096 value13,
1097 value14,
1098 attribute4,
1099 attribute5,
1100 attribute6)
1101
1102 Values
1103 (p_entity_id,
1104 p_seq_num,
1105 p_location_id ,
1106 substr(p_location_name,1,80) ,
1107 fein ,
1108 p_assignment_id ,
1109 p_person_id ,
1110 p_job_category ,
1111 p_race_code ,
1112 p_person_type ,
1113 nvl(p_m_app_count,0) ,
1114 nvl(p_f_app_count,0) ,
1115 nvl(p_m_hire_count,0) ,
1116 nvl(p_f_hire_count,0) ,
1117 nvl(p_m_terminate_count,0) ,
1118 nvl(p_f_terminate_count,0) ,
1119 nvl(p_m_promotion_count,0) ,
1120 nvl(p_f_promotion_count,0) ,
1121 nvl(p_m_fte_count,0) ,
1122 nvl(p_f_fte_count,0) ,
1123 nvl(p_monetary_comp,0) ,
1124 p_tenure_years ,
1125 p_tenure_months ,
1126 p_minority_code ,
1127 l_ethnic_group_code ,
1128 p_est_flag,
1129 p_fte_flag
1130 );
1131
1132 END LOOP;
1133 hr_utility.trace('After Inserting. Resetting the counts. ');
1134 /* BUG3878442
1135 m_app_count:=0;
1136 f_app_count:=0;
1137 m_hire_count:=0;
1138 f_hire_count:=0;
1139 m_terminate_count:=0;
1140 f_terminate_count:=0;
1141 m_promotion_count:=0;
1142 f_promotion_count:=0;
1143 m_fte_count:=0;
1144 f_fte_count:=0;
1145 */
1146 monetary_comp:=0;
1147 tenure_years:=0;
1148 tenure_months:=0;
1149 minority_code:=0;
1150 ethnic_group_code:=null;
1151 l_est_id:=0;
1152 l_est_flag:=null;
1153 l_est_name:=null;
1154 fte_flag :=null;
1155 hr_utility.trace('=============================== end p_insert==================================');
1156 end p_insert;
1157
1158 procedure hire_or_fte (p_assignment_id in number,
1159 p_person_id in number,
1160 p_period_start in date,
1161 p_period_end in date,
1162 p_eff_start_date in date,
1163 p_eff_end_date in date,
1164 p_per_actual_start_date in date,
1165 p_assignment_type in varchar2,
1166 p_sex in varchar2,
1167 p_job in varchar2,
1168 p_race in varchar2,
1169 p_person_type in varchar2,
1170 p_location_id in number,
1171 p_hierarchy_version_id in number,
1172 p_min_hours in number,
1173 p_defined_balance_id in number,
1174 p_max_asact_id in number,
1175 p_seq_num in number
1176 ,p_effective_start_date in date -- BUG3940867
1177 ,p_effective_end_date in date -- BUG3940867
1178 )
1179 is
1180
1181 /*This cursor selects the parent_hierarchy_node_id for the selected
1182 entity_id. */
1183
1184 CURSOR c_loc_hierarchy_id (l_location_id per_gen_hierarchy_nodes.entity_id%type,
1185 l_hierarchy_version_id per_gen_hierarchy_nodes.hierarchy_version_id%type)
1186 IS
1187 SELECT parent_hierarchy_node_id
1188 FROM per_gen_hierarchy_nodes
1189 WHERE entity_id = l_location_id
1190 AND hierarchy_version_id = l_hierarchy_version_id;
1191
1192 /*If the report is not run for the recently concluded calendar year
1193 then the asact_id should be calculated for the recently concluded
1194 calendar year which is selected here.*/
1195
1196 CURSOR asact_id(c_assignment_id per_assignments_f.assignment_id%type,
1197 c_period_end date)
1198 IS
1199 SELECT to_number(substr(max(lpad(paa.action_sequence,15,'0')||
1200 paa.assignment_action_id),16))
1201 FROM pay_assignment_actions paa,
1202 pay_payroll_actions ppa
1203 WHERE paa.assignment_id = c_assignment_id
1204 AND ppa.payroll_action_id = paa.payroll_action_id
1205 AND ppa.effective_date <= c_period_end
1206 AND ppa.action_type in ('R', 'Q', 'I');
1207
1208 -- This cursor checks an employee will terminate in future date
1209 -- BUG3954458
1210 cursor c_check_future_termination(p_person_id in number
1211 ,p_start_date in date
1212 ,p_end_date in date)
1213 is
1214 select 1
1215 from per_people_f ppf
1216 ,per_periods_of_service pps
1217 where ppf.person_id = p_person_id
1218 and ppf.effective_start_date < p_end_date
1219 and ppf.effective_end_date > p_start_date
1220 and pps.person_id = ppf.person_id
1221 and pps.actual_termination_date between
1222 p_start_date and p_end_date
1223 and ppf.effective_start_date =
1224 (select max(ppf2.effective_start_date)
1225 from per_people_f ppf2
1226 where ppf2.person_id = ppf.person_id
1227 and ppf2.effective_start_date < p_end_date
1228 and ppf2.effective_end_date > p_start_date
1229 );
1230
1231 CURSOR c_get_updated_racecode( l_person_id per_assignments_f.person_id%type,
1232 l_period_start date,
1233 l_period_end date)
1234
1235 IS
1236
1237 SELECT ppt.system_person_type person_type,
1238 ppf.effective_start_Date eff_Start,
1239 ppf.effective_end_date eff_end,
1240 ppf.per_information1 race
1241 FROM per_people_f ppf,
1242 per_person_types ppt,
1243 per_periods_of_service pps
1244 WHERE ( ( ppt.system_person_type = 'EMP'
1245 and ppf.effective_start_date
1246 = (select max(effective_start_date)
1247 from per_people_f
1248 where person_type_id = ppf.person_type_id
1249 and person_id = ppf.person_id
1250 and effective_start_date<=l_period_end
1251 )
1252 )
1253 OR
1254 ( ppt.system_person_type = 'EMP_APL'
1255 and ppf.effective_start_date = (select max(effective_Start_date)
1256 from per_people_f
1257 where person_id = ppf.person_id
1258 and person_type_id = ppf.person_type_id
1259 and effective_start_Date <=l_period_end
1260 and effective_end_date >= l_period_start
1261 )
1262 )
1263 )
1264 and ppf.person_id =l_person_id
1265 and ppt.person_type_id = ppf.person_type_id
1266 and pps.person_id = ppf.person_id;
1267
1268 l_hours_worked per_assignments_f.normal_hours%type;
1269 l_asact_id pay_assignment_actions.assignment_action_id%type;
1270 l_year varchar2(4);
1271 calendar_period_start date;
1272 calendar_period_end date;
1273 calendar_next_period_start date;
1274 p_cal_period_start date;
1275 l_parent_hierarchy_node_id per_gen_hierarchy_nodes.parent_hierarchy_node_id%type;
1276 cal_monetary_comp number:=0;
1277 l_package varchar2(70);
1278 l_exists varchar2(1);
1279 l_race varchar2(20);
1280 l_effective_start_date date;
1281 l_effective_end_date date;
1282 l_person_type varchar2(20);
1283
1284 -- Defined local variables BUG3878442
1285 m_app_count number:=0;
1286 f_app_count number:=0;
1287 m_terminate_count number :=0;
1288 f_terminate_count number :=0;
1289 m_hire_count number :=0;
1290 f_hire_count number :=0;
1291 m_fte_count number :=0;
1292 f_fte_count number :=0;
1293 m_promotion_count number :=0;
1294 f_promotion_count number :=0;
1295
1296
1297 BEGIN
1298
1299 l_package := 'pay_eosurvey.hire_or_fte';
1300
1301 /*The year from the start date is calculated. */
1302
1303 hr_utility.trace('=============================hire_or_fte==================================');
1304
1305 hr_utility.set_location('Entering... ' || l_package||':p_person_id = '||p_person_id,10);
1306 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
1307 hr_utility.trace('p_person_id = ' || p_person_id);
1308 hr_utility.trace('p_period_start = ' || p_period_start);
1309 hr_utility.trace('p_period_end = ' || p_period_end);
1310 hr_utility.trace('p_eff_start_date = ' || p_eff_start_date);
1311 hr_utility.trace('p_eff_end_date = ' || p_eff_end_date);
1312 hr_utility.trace('p_per_actl_strt_date= ' || p_per_actual_start_date);
1313 hr_utility.trace('p_assignment_type= ' || p_assignment_type);
1314 hr_utility.trace('p_sex = ' || p_sex);
1315 hr_utility.trace('p_job = ' || p_job);
1316 hr_utility.trace('p_race = ' || p_race);
1317 hr_utility.trace('p_person_type = ' || p_person_type);
1318 hr_utility.trace('p_location_id = ' || p_location_id);
1319 hr_utility.trace('p_hirrchy_vsn_id = ' || p_hierarchy_version_id);
1320 hr_utility.trace('p_min_hours = ' || p_min_hours);
1321 hr_utility.trace('p_defin_balace_id= ' || p_defined_balance_id);
1322 hr_utility.trace('p_max_asact_id = ' || p_max_asact_id);
1323 hr_utility.trace('p_seq_num = ' || p_seq_num);
1324 hr_utility.trace('p_effective_start_date= ' || p_effective_start_date);
1325 hr_utility.trace('p_effective_end_date = ' || p_effective_end_date);
1326
1327 hr_utility.set_location(l_package||':p_person_id = '||p_person_id,20);
1328
1329 monetary_comp:=null;
1330
1331 l_year:=to_char(p_period_start,'yyyy');
1332
1333 hr_utility.trace('Calculated year = '||l_year);
1334
1335 /*It is checked if the period beginning is January. */
1336
1337 IF p_period_start <> to_date('01-01-'||l_year,'dd-mm-yyyy') THEN
1338 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,30);
1339
1340 /*If not then the period January 1 and December 31 of the most
1341 recently concluded year is calculated. */
1342
1343 hr_utility.trace('Period begin is not January.');
1344
1345 calendar_period_start:=to_date('01-01-'||l_year,'dd-mm-yyyy');
1346 calendar_period_end:=to_date('31-12-'||l_year,'dd-mm-yyyy');
1347
1348 /*The next period beginning is also calcualted as this will
1349 used to check for the new employees joined after the
1350 beginning of this year. */
1351
1352 l_year:=l_year+1;
1353 calendar_next_period_start:=to_date('01-01-'||l_year,'dd-mm-yyyy');
1354
1355 hr_utility.trace('Calculated calendar_period_start =
1356 '||to_char(calendar_period_start));
1357 hr_utility.trace('Calculated calendar_period_end = '
1358 ||to_char(calendar_period_end));
1359 hr_utility.trace('Calculated calendar_next_period_start = '
1360 ||to_char(calendar_next_period_start));
1361
1362 ELSE /*The starting period itself is 1st Jan. So setting the variables to null. */
1363 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,40);
1364
1365 hr_utility.trace('Period beginJanuary.So setting the calendar start and end null');
1366
1367 calendar_period_start:=null;
1368 calendar_period_end:=null;
1369
1370 END IF;
1371
1372 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,50);
1373 /* check if the employee is an applicant. */
1374 hr_utility.trace('Begin hire_or_fte');
1375
1376 if p_person_type = 'EMP_APL' and p_assignment_type = 'A' then
1377 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,60);
1378 hr_utility.trace('Check for EMP_APL');
1379 male_female_count(p_sex,
1380 m_app_count,
1381 f_app_count);
1382 end if;
1383
1384 /* Check if the employee is a hire hired between the report period dates. */
1385
1386 if p_per_actual_start_date between p_period_start and p_period_end then
1387 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,70);
1388
1389 hr_utility.trace('Check for new hire');
1390 hr_utility.trace('p_sex = '||p_sex);
1391
1392 male_female_count(p_sex,
1393 m_hire_count,
1394 f_hire_count);
1395 end if;
1396
1397 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,80);
1398 hr_utility.trace('p_eff_start_Date = '||to_char(p_eff_start_Date));
1399 hr_utility.trace('p_eff_end_date = '||to_char(p_eff_end_date));
1400 hr_utility.trace('p_period_start = '||to_char(p_period_start));
1401 hr_utility.trace('p_period_end = '||to_char(p_period_end));
1402 hr_utility.trace('p_assignment_type= '||p_assignment_type);
1403 hr_utility.trace('p_person_type = '||p_person_type);
1404 hr_utility.trace('p_per_actual_start_date = '||to_char(p_per_actual_start_date));
1405
1406 /* The employee will be shown twice if he is an EMP_APL in a year.
1407 Checking is made */
1408
1409
1410 if (p_eff_start_Date < p_period_start and
1411 p_eff_end_date > p_period_end and
1412 p_person_type = 'EMP_APL') or
1413 (p_person_type = 'EMP'
1414 and p_assignment_type = 'E' ) then
1415
1416 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,90);
1417 hr_utility.trace('Emp is a fte and non-terminated person.calculate tenure and salary');
1418
1419 l_hours_worked:= pay_us_employee_payslip_web.
1420 get_asgn_annual_hours(p_assignment_id,p_period_end);
1421
1422 hr_utility.trace('hours worked = '||to_char(l_hours_worked));
1423 hr_utility.trace('p_min_hours = '||to_char(p_min_hours));
1424
1425 /*An employee is considered full time only if his working hours
1426 meet the companys hours specified in the report parameter. */
1427
1428 if l_hours_worked >= p_min_hours
1429 or l_hours_worked is NULL then -- BUG3886008
1430 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,95);
1431 -- If an employee will terminate in the report period,
1432 -- the employee should NOT be report under full time employees
1433 -- BUG3954458
1434 open c_check_future_termination(p_person_id
1435 ,p_eff_start_date -- p_period_start
1436 ,p_period_end);
1437 fetch c_check_future_termination into l_exists;
1438 if c_check_future_termination%NOTFOUND then
1439 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,97);
1440 fte_flag:='Y';
1441 hr_utility.trace('setting fte count');
1442 male_female_count(p_sex,
1443 m_fte_count,
1444 f_fte_count);
1445 end if;
1446 close c_check_future_termination;
1447
1448 /* Calculate the tenure.
1449 If the report is run for the recent concluded
1450 year then the tenure is calculated as a differnce between the persons
1451 start date and period_end_Date which will be 31-Dec-yy. */
1452
1453 /*If the report is run for the AAP year then
1454 the tenure is calculated for the employees based on the recently concluded
1455 year. This infm is stored in the local variables:
1456 calendar_period_start, calendar_period_end */
1457
1458 /* For the new employees tenure is based on the report end period. */
1459
1460 IF calendar_period_start IS NOT NULL then
1461
1462 /*It means the period_Start date is not 01-Jan-yy */
1463
1464 /* new hires joined after the concluded calendar year. */
1465
1466 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,99);
1467 IF( p_per_actual_start_date > calendar_next_period_start) then
1468 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,100);
1469
1470 tenure_months:=round(mod(months_between(p_period_end,p_per_actual_start_date),12));
1471
1472 hr_utility.trace('tenure_months = '||to_char(tenure_months));
1473
1474
1475 monetary_comp:= pay_balance_pkg.get_value(p_defined_balance_id,
1476 p_max_asact_id);
1477 hr_utility.trace('monetary_comp for new emp = '||to_char(monetary_comp));
1478
1479 ELSE /*for the employees who have joined before 31-dec-yy
1480 calculated calendar_period_end is used to calculate the tenure. */
1481 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,110);
1482
1483 OPEN asact_id(p_assignment_id,calendar_period_end);
1484 FETCH asact_id INTO l_asact_id;
1485 CLOSE asact_id;
1486
1487 monetary_comp:= pay_balance_pkg.get_value(p_defined_balance_id,
1488 l_asact_id);
1489
1490 tenure_years:= trunc(months_between(calendar_period_end,p_per_actual_start_date)/12);
1491 tenure_months:=round(mod(months_between(calendar_period_end,p_per_actual_start_date),12));
1492
1493 hr_utility.trace('tenure calculation for new emp');
1494
1495 END IF;
1496
1497 ELSE /* The dates are recently concluded year.
1498 So the period_end_date is used to calculate the tenure. */
1499
1500 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,120);
1501
1502 tenure_years:= trunc(months_between(p_period_end,p_per_actual_start_date)/12);
1503 tenure_months:=round(mod(months_between(p_period_end,p_per_actual_start_date),12));
1504
1505 /*Asact id calculated in the action_Creation is used here to calcualte the comp. */
1506
1507 monetary_comp:= pay_balance_pkg.get_value(p_defined_balance_id,
1508 p_max_asact_id);
1509
1510 END IF;
1511
1512
1513 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,130);
1514 hr_utility.trace('tenure_months = '||to_char(tenure_months));
1515 hr_utility.trace('tenure_years = '||to_char(tenure_years));
1516 hr_utility.trace('monetary_comp = '||to_char(monetary_comp));
1517
1518
1519 /*If the employee is a new hire then his YTD is caculated as follows: */
1520
1521 IF p_per_actual_start_date between p_period_start and p_period_end then
1522
1523 hr_utility.trace('salary calculation for new employees');
1524 hr_utility.trace('p_per_actual_start_Date '||to_char(p_per_actual_start_Date));
1525
1526 cal_monetary_comp:=round(monetary_comp/(p_period_end-p_per_actual_start_Date)*365/1000,0);
1527 hr_utility.trace('New employee comp is = '||to_char(cal_monetary_comp));
1528 ELSE
1529 cal_monetary_comp:=round(monetary_comp/1000,0);
1530
1531 END IF;
1532
1533
1534
1535 monetary_comp:=cal_monetary_comp;
1536
1537 IF tenure_months = 12 THEN
1538
1539 hr_utility.trace('Tenure months is 12.So setting the month to 0.');
1540
1541 tenure_years:=tenure_years+1;
1542 tenure_months:=0;
1543
1544 hr_utility.trace('Recalculated tenure months = '||to_char(tenure_months));
1545 hr_utility.trace('Recalculated tenure years = '||to_char(tenure_years));
1546
1547 END If;
1548
1549 hr_utility.trace('monetary_comp = '||to_char(cal_monetary_comp));
1550 hr_utility.trace('After salary and tenure before calling Promotion');
1551
1552 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,135);
1553 promotion(p_assignment_id
1554 ,p_sex
1555 ,p_period_start
1556 ,p_period_end
1557 ,p_effective_start_date
1558 ,p_effective_end_date
1559 ,m_promotion_count
1560 ,f_promotion_count);
1561 hr_utility.trace('After calling Promotion');
1562
1563 OPEN c_loc_hierarchy_id(p_location_id,p_hierarchy_version_id) ;
1564 FETCH c_loc_hierarchy_id INTO l_parent_hierarchy_node_id;
1565 CLOSE c_loc_hierarchy_id ;
1566 hr_utility.trace('LOCATION_ID ='||p_location_id);
1567 hr_utility.trace('l_parent_hierarchy_node_id = '||l_parent_hierarchy_node_id);
1568
1569 For i in 1..est_infm.count LOOP
1570 IF (est_infm(i).entity_id = p_location_id) THEN
1571 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,140);
1572 hr_utility.trace('This entity is an establishment.');
1573 hr_utility.trace('ENTITY_ID ='||to_char(p_location_id));
1574 l_est_id:=est_infm(i).entity_id;
1575 l_est_name:=est_infm(i).location_name;
1576 l_est_fein:=est_infm(i).fein;
1577 l_est_flag:='Y';
1578 hr_utility.trace('So setting the flag to Y.l_est_flag = '
1579 ||l_est_flag);
1580 EXIT ;
1581 ELSIF est_infm(i).hierarchy_node_id=l_parent_hierarchy_node_id THEN
1582 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,150);
1583
1584 hr_utility.trace('This entity is an LOCATION');
1585 hr_utility.trace('LOCATION LOC ='||to_char(p_location_id));
1586
1587 l_est_id:=est_infm(i).entity_id;
1588 l_est_name:=est_infm(i).location_name;
1589 l_est_fein:=est_infm(i).fein;
1590 l_est_flag:='N';
1591 hr_utility.trace('So setting the flag to N.l_est_flag = '
1592 ||l_est_flag);
1593 EXIT ;
1594 END IF;
1595 END LOOP;
1596
1597 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,160);
1598 open c_get_updated_racecode(p_person_id -- BUG3941606
1599 ,p_period_start
1600 ,p_period_end
1601 );
1602 fetch c_get_updated_racecode into l_person_type
1603 ,l_effective_start_date
1604 ,l_effective_end_date
1605 ,l_race;
1606
1607 if c_get_updated_racecode%NOTFOUND then
1608 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,165);
1609 l_race := p_race;
1610 end if;
1611 close c_get_updated_racecode;
1612 hr_utility.trace('l_race = ' || l_race);
1613
1614 minority(p_sex,
1615 l_race, -- p_race, 18-OCT-04
1616 minority_code,
1617 ethnic_group_code);
1618
1619 hr_utility.trace('hire_or_fte. before calling p_insert procedure');
1620
1621 hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,170);
1622
1623 p_insert(
1624 l_est_id,
1625 p_seq_num,
1626 p_location_id,
1627 l_est_name,
1628 l_est_fein,
1629 p_assignment_id,
1630 p_person_id,
1631 p_job,
1632 p_race,
1633 p_person_type,
1634 m_app_count,
1635 f_app_count,
1636 m_hire_count,
1637 f_hire_count,
1638 m_terminate_count,
1639 f_terminate_count,
1640 m_promotion_count,
1641 f_promotion_count,
1642 m_fte_count,
1643 f_fte_count,
1644 monetary_comp,
1645 tenure_years,
1646 tenure_months,
1647 minority_code,
1648 ethnic_group_code,
1649 l_est_flag,
1650 fte_flag);
1651
1652 -- Initialize local variables BUG3878442
1653 m_app_count:=0;
1654 f_app_count:=0;
1655 m_hire_count:=0;
1656 f_hire_count:=0;
1657 m_terminate_count:=0;
1658 f_terminate_count:=0;
1659 m_promotion_count:=0;
1660 f_promotion_count:=0;
1661 m_fte_count:=0;
1662 f_fte_count:=0;
1663
1664 end if; /* hours checking if */
1665
1666 END IF; /* EMP,EMP_APL checking */
1667
1668 hr_utility.trace('=============================End hire_or_fte==================================');
1669
1670 hr_utility.set_location('Leaving.. ' ||l_package||':p_assignment_id = '||p_assignment_id,200);
1671 end hire_or_fte;
1672
1673
1674 procedure gre_name(
1675 p_entity_id in number,
1676 p_version_id in number,
1677 p_fein out nocopy varchar2,
1678 p_location_name out nocopy varchar2)
1679 is
1680
1681 /*This cursor finds the fein which is stored in lei_information6
1682 and reporting name which is stored in lei_information1.
1683 If the reporting name is not found for the establishment then
1684 Reporting name not specified is printed. */
1685
1686
1687 cursor fein_est (c_entity_id per_gen_hierarchy_nodes.entity_id%type)
1688 is
1689 select hlei.lei_information6 fein
1690 from hr_location_extra_info hlei
1691 where hlei.location_id = c_entity_id
1692 and hlei.information_type = 'Establishment Information';
1693
1694 cursor est_rpt_name(c_entity_id per_gen_hierarchy_nodes.entity_id%type)
1695 is
1696 SELECT lei_information1 rpt_name
1697 from hr_location_extra_info
1698 where location_id = c_entity_id
1699 and information_type = 'EEO-1 Specific Information';
1700
1701
1702
1703 /*This cursor finds the fein infm of the parent. */
1704
1705 cursor fein_par(c_version_id per_gen_hierarchy_nodes.hierarchy_version_id%type)
1706 is
1707 select hoi.org_information3 fein
1708 from hr_organization_information hoi,
1709 per_gen_hierarchy_nodes pghn
1710 where hoi.organization_id = pghn.entity_id
1711 and pghn.node_type = 'PAR'
1712 and pghn.hierarchy_version_id = c_version_id
1713 and hoi.org_information_context = 'VETS_EEO_Dup';
1714
1715
1716 cursor est_name_address (c_entity_id per_gen_hierarchy_nodes.entity_id%type)
1717 is
1718 select rtrim(address_line_1)||' '||
1719 rtrim(address_line_2)||' '||
1720 rtrim(address_line_3)||' '||
1721 town_or_city||','||
1722 country||'-'||
1723 postal_code
1724 from hr_locations
1725 where location_id = c_entity_id;
1726
1727 l_address varchar2(1000);
1728 l_est_gre hr_location_extra_info.lei_information1%type;
1729 l_est_name hr_location_extra_info.lei_information1%type;
1730 l_par_gre hr_organization_information.org_information3%type;
1731
1732 begin
1733 hr_utility.trace('=============================== gre_name==================================');
1734 OPEN fein_est(p_entity_id);
1735 FETCH fein_est INTO l_est_gre;
1736
1737 hr_utility.trace('est fein = '||l_est_gre);
1738
1739 /*If the fein is not found for establishment level
1740 it is taken from the parent. */
1741
1742 IF l_est_gre IS NULL OR fein_est%NOTFOUND THEN
1743
1744 CLOSE fein_est;
1745 OPEN fein_par(p_version_id);
1746 FETCH fein_par INTO l_par_gre;
1747
1748 hr_utility.trace('Est gre not found.Fetching parent GRE');
1749
1750 IF l_par_gre IS NULL OR fein_par%NOTFOUND THEN
1751
1752 CLOSE fein_par;
1753 p_fein:='GRE information not found in both the establishment and parent level';
1754 hr_utility.trace('Est and parent gre not found.');
1755
1756 ELSE
1757
1758 CLOSE fein_par;
1759 p_fein:=l_par_gre;
1760 hr_utility.trace('parent gre found. l_par_gre:='||l_par_gre);
1761
1762 END IF;
1763
1764 ELSE
1765
1766 hr_utility.trace('Est gre found.l_est_gre:='||l_est_gre);
1767 p_fein:=l_est_gre;
1768 CLOSE fein_est;
1769
1770 END IF;
1771
1772 OPEN est_rpt_name(p_entity_id);
1773 fetch est_rpt_name into l_est_name;
1774
1775 IF l_est_name is null or est_rpt_name%NOTFOUND THEN
1776
1777 hr_utility.trace('Reporting name not specified');
1778 p_location_name:='Reporting name not specified';
1779
1780 CLOSE est_rpt_name;
1781 ELSE
1782
1783 CLOSE est_rpt_name;
1784
1785 open est_name_address(p_entity_id);
1786 fetch est_name_address into l_address;
1787 close est_name_address;
1788
1789 p_location_name:=l_est_name||' '||l_address;
1790
1791 hr_utility.trace('Reporting name specified. '||l_est_name);
1792
1793 END IF;
1794
1795
1796 hr_utility.trace('location name = '||p_location_name);
1797 hr_utility.trace('p_fein = '||p_fein);
1798 hr_utility.trace('===============================END gre_name==================================');
1799 end gre_name;
1800
1801
1802 procedure minority(p_sex in varchar2,
1803 p_race_code in varchar2,
1804 minority_code out nocopy number,
1805 ethnic_group_code out nocopy varchar2)
1806 is
1807
1808
1809 cursor ethnic_race(c_race_code varchar2)
1810 is
1811 select decode(lookup_code,'6','American Indian or Alaskan Native',
1812 '4','Asian',
1813 '5','Native Hawaiian or Other Pacific Islander',
1814 '2','Black or African American',
1815 '8','Black or African American',
1816 '1','White',
1817 '9','Hispanic or Latino (White race only)',
1818 '3','Hispanic or Latino (all other races)',
1819 '10','Hispanic or Latino (all other races)',null)
1820 from fnd_common_lookups
1821 where lookup_code = c_race_code
1822 and lookup_type = 'US_ETHNIC_GROUP';
1823
1824 l_ethnic_category fnd_common_lookups.meaning%type;
1825
1826
1827 begin
1828 hr_utility.trace('=============================== minority==================================');
1829 open ethnic_race(p_race_code);
1830 fetch ethnic_race into l_ethnic_category;
1831 close ethnic_race;
1832
1833 if l_ethnic_category = 'White' or
1834 l_ethnic_category = 'Caucasian' then
1835
1836 if p_sex = 'M' then
1837 minority_code := 4;
1838 elsif p_sex = 'F' then
1839 minority_code := 2;
1840 end if;
1841
1842 elsif l_ethnic_category is not null then
1843
1844 if p_sex = 'M' then
1845 minority_code := 3;
1846 elsif p_sex = 'F' then
1847 minority_code := 1;
1848 end if;
1849
1850 end if;
1851 hr_utility.trace('l_ethnic_category = '||l_ethnic_category);
1852
1853 select decode(l_ethnic_category,'American Indian or Alaskan Native','1',
1854 'Asian','2',
1855 'Native Hawaiian or Other Pacific Islander','3',
1856 'Black or African American','4',
1857 'White','5',
1858 'Hispanic or Latino (White race only)','7',
1859 'Hispanic or Latino (all other races)','8',
1860 null,'0')
1861 into ethnic_group_code
1862 from dual ;
1863 hr_utility.trace('ethnic_group_code = '||ethnic_group_code);
1864 hr_utility.trace('===============================END minority==================================');
1865 end minority;
1866
1867
1868 procedure promotion(p_assignment_id in number,
1869 p_sex in varchar2,
1870 p_period_start in date,
1871 p_period_end in date,
1872 p_eff_start_date in date, --BUG
1873 p_eff_end_date in date, --BUG
1874 m_promotion_count out nocopy number,
1875 f_promotion_count out nocopy number) is
1876
1877 /*-----------------------------------
1878 cursor promotion_check(l_assignment_id per_assignments_f.assignment_id%type) is
1879 select 'Y'
1880 from per_assignment_extra_info
1881 where aei_information_category = 'Promotion'
1882 and to_date(aei_information1,'dd-mm-yyyy') between
1883 p_period_start and p_period_end
1884 and assignment_id = l_assignment_id;
1885
1886
1887 begin
1888 hr_utility.trace('=============================== promotion==================================');
1889 open promotion_check(p_assignment_id);
1890 If promotion_check%FOUND then
1891 male_female_count(p_sex,
1892 m_promotion_count,
1893 f_promotion_count);
1894 else
1895 hr_utility.trace('No promotion');
1896 end if;
1897 hr_utility.trace('===============================END promotion==================================');
1898
1899
1900 ----------*/
1901 --
1902 -- Replaced promotion prcedure to call fastformula BUG#3730282
1903 --
1904 cursor csr_get_person_info is
1905 select business_group_id
1906 ,person_id
1907 ,effective_start_date -- BUG3963090
1908 ,effective_end_date -- BUG3963090
1909 from per_all_assignments_f
1910 where assignment_id = p_assignment_id
1911 and effective_start_date <= p_eff_end_date
1912 and effective_end_date >= p_eff_start_date;
1913
1914
1915
1916 l_count number;
1917 l_total_count number; -- BUG3963090
1918 l_period_start date;
1919 l_period_end date;
1920
1921 begin
1922
1923 hr_utility.trace('=============== promotion =================');
1924 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
1925 hr_utility.trace('p_period_start = ' || p_period_start);
1926 hr_utility.trace('p_period_end = ' || p_period_end);
1927 hr_utility.trace('p_eff_start_date = ' || p_eff_start_date);
1928 hr_utility.trace('p_eff_end_date = ' || p_eff_end_date);
1929
1930 l_total_count := 0;
1931
1932 --
1933 -- Added 'for loop' to pick up multiple promotions by BUG3963090
1934 --
1935 for prom_asgn in csr_get_person_info loop
1936 hr_utility.trace('business_group_id = ' || prom_asgn.business_group_id);
1937 hr_utility.trace('person_id = ' || prom_asgn.person_id);
1938 hr_utility.trace('effective_start_dat= ' || prom_asgn.effective_start_date);
1939 hr_utility.trace('effective_end_date = ' || prom_asgn.effective_end_date);
1940 if prom_asgn.effective_start_date < p_period_start then
1941 l_period_start := p_period_start;
1942 else
1943 l_period_start := prom_asgn.effective_start_date;
1944 end if;
1945 if prom_asgn.effective_end_date > p_period_end then
1946 l_period_end := p_period_end;
1947 else
1948 l_period_end := prom_asgn.effective_end_date;
1949 end if;
1950
1951 hr_utility.trace('l_period_start = ' || l_period_start);
1952 hr_utility.trace('l_period_end = ' || l_period_end);
1953
1954 hr_utility.trace('============ call per_fastformula_event ===========');
1955 l_count := per_fastformula_events_utility.per_fastformula_event
1956 ( 'PROMOTION'
1957 ,'Promotion'
1958 ,prom_asgn.business_group_id -- l_business_group_id
1959 ,prom_asgn.person_id -- l_person_id
1960 ,l_period_start
1961 ,l_period_end
1962 );
1963
1964 hr_utility.trace('========== return from per_fastformula_event =======');
1965 hr_utility.trace('l_count = ' || l_count);
1966 l_total_count := l_total_count + l_count;
1967 hr_utility.trace('l_total_count = ' || l_total_count);
1968 end loop;
1969
1970 if p_sex = 'M' then
1971 m_promotion_count := l_total_count;
1972 hr_utility.trace('m_promotion_cout = ' || m_promotion_count);
1973 elsif p_sex = 'F' then
1974 f_promotion_count := l_total_count;
1975 hr_utility.trace('f_promotion_cout = ' || f_promotion_count);
1976 end if;
1977
1978 hr_utility.trace('================ End promotion =================');
1979 end promotion;
1980
1981 procedure male_female_count(p_sex in varchar2,
1982 p_male_count out nocopy number,
1983 p_female_count out nocopy number)
1984 is
1985
1986 begin
1987 hr_utility.trace('=============================== male_female_count==================================');
1988 IF p_sex = 'M' THEN
1989 p_male_count:=1;
1990 ELSIF p_sex = 'F' THEN
1991 p_female_count:=1;
1992 END IF;
1993 hr_utility.trace('p_male_count = '||to_char(p_male_count));
1994 hr_utility.trace('p_female_count = '||to_char(p_female_count));
1995 hr_utility.trace('===============================END male_female_count==================================');
1996
1997 end male_female_count;
1998
1999 procedure job_race_insert(p_entity_id in number,
2000 p_seq_num in number)
2001 is
2002
2003
2004 CURSOR eeo1_job_code
2005 IS
2006 SELECT lookup_code
2007 FROM fnd_common_lookups
2008 WHERE lookup_type = 'US_EEO1_JOB_CATEGORIES'
2009 AND lookup_code <> '10';
2010
2011
2012 BEGIN
2013
2014 hr_utility.trace('=============================== job_race_insert==================================');
2015 FOR job_code in eeo1_job_code LOOP
2016 FOR i in 0 .. 8 LOOP
2017 INSERT INTO pay_us_rpt_totals
2018 (session_id,
2019 business_group_id,
2020 attribute1,
2021 attribute4,
2022 attribute6)
2023 Values
2024 (p_entity_id,
2025 p_seq_num,
2026 job_code.lookup_code,
2027 i,
2028 'Y');
2029 hr_utility.trace('job_code = '||job_code.lookup_code);
2030 hr_utility.trace('ethnic code = '||to_char(i));
2031 END LOOP;
2032 END LOOP;
2033
2034 FOR i in 1 ..4 LOOP
2035 FOR job_code in eeo1_job_code LOOP
2036 INSERT INTO pay_us_rpt_totals
2037 (session_id,
2038 business_group_id,
2039 attribute1,
2040 value14,
2041 attribute6)
2042 Values
2043 (p_entity_id,
2044 p_seq_num,
2045 job_code.lookup_code,
2046 i,
2047 'Y');
2048 END LOOP;
2049 END LOOP;
2050 hr_utility.trace('===============================END job_race_insert==================================');
2051
2052 END job_race_insert;
2053
2054 end pay_eosurvey_pkg;
[Home] [Help]