[Home] [Help]
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);
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 ,
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 )
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
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);
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:
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
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
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.*/
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,
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)
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:
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
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*/
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:
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 = '
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:
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);
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
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.*/
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,
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);
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);
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:
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,
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);
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: --
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
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: --
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
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
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
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:
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);
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:
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,
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,
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,
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
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.*/
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;
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';
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,
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');
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;
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:
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);
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:
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
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:
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));
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');
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));
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));
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:
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);
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;
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;
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:
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
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,
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;
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');
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.
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,
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;
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. */
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:
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);
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:
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);
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;
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;
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
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:
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.*/
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: --
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
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);
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:
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
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;
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);
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:
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,
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:
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 (
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));
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));
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));
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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:
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
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:
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) */
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;
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,
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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:
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);
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:
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;
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');
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
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:
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:
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 = '
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:
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);
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;
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:
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
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');
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);
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:
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,
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);
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));
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);
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);
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));
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:
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.
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 */
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:
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);
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:
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. */
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:
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,
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;
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));
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));
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);
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);
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;
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.
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:
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:
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:
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: */
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:
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));
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
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;
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:
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:
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));
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
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
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 ;
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
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);
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;
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;
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';
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);
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:
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;
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;
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;
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: );
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);
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,
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(
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,
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:
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(
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);
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:
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;
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;
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
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;
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
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:
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:
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:
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,
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:
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',
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:
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,
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,
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:
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: --
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);
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);
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:
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;
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:
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: --
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
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;
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
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;
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
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'
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
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;
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:
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;
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;
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;
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;
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;
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:
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,
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,
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:
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
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;