[Home] [Help]
364: national_identifier, full_name, employee_number, normal_hours,
365: hourly_salaried_code, hsc.segment3 position_code, frequency
366: from per_all_people_f papf,
367: per_all_assignments_f paaf,
368: hr_soft_coding_keyflex hsc,
369: per_assignment_status_types past
370: where papf.person_id between p_start_person and p_end_person
371: and paaf.person_id = papf.person_id
372: and paaf.business_group_id = papf.business_group_id
376: and paaf.assignment_status_type_id =
377: past.assignment_status_type_id
378: and past.PER_SYSTEM_STATUS in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
379: and paaf.assignment_id = (select min(assignment_id)
380: from per_all_assignments_f asg,hr_soft_coding_keyflex hsck
381: where person_id = papf.person_id
382: and hsck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
383: and hsck.segment2 = to_char (p_local_unit))
384: and p_end_date between paaf.effective_start_date
437: national_identifier, full_name, employee_number, normal_hours,
438: hourly_salaried_code, hsc.segment3 position_code, frequency
439: from per_all_people_f papf,
440: per_all_assignments_f paaf,
441: hr_soft_coding_keyflex hsc,
442: per_assignment_status_types past
443: where paaf.person_id = papf.person_id
444: and papf.person_id between p_start_person and p_end_person
445: and paaf.business_group_id = papf.business_group_id
448: and hsc.segment2 = to_char (p_local_unit)
449: and paaf.assignment_status_type_id =
450: past.assignment_status_type_id
451: and paaf.assignment_id = (select min(assignment_id)
452: from per_all_assignments_f asg,hr_soft_coding_keyflex hsck
453: where person_id = papf.person_id
454: and hsck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
455: and hsck.segment2 = to_char (p_local_unit))
456: --and past.PER_SYSTEM_STATUS = 'TERM_ASSIGN'
655:
656: /* Cursor to get the Organization No for the Local Unit based on the soft
657: coding keyflex id */
658: cursor csr_get_org_no (
659: p_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type
660: ) is
661: select org_information1
662: from hr_organization_information hoi, hr_soft_coding_keyflex hsc
663: where org_information_context = 'NO_LOCAL_UNIT_DETAILS'
658: cursor csr_get_org_no (
659: p_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type
660: ) is
661: select org_information1
662: from hr_organization_information hoi, hr_soft_coding_keyflex hsc
663: where org_information_context = 'NO_LOCAL_UNIT_DETAILS'
664: and hsc.segment2 = organization_id
665: and soft_coding_keyflex_id = p_soft_coding_keyflex_id;
666:
673: p_effective_date date,
674: p_job_id number
675: ) is
676: select segment3
677: from hr_soft_coding_keyflex hsc, per_all_assignments_f paaf
678: where paaf.job_id = p_job_id
679: and assignment_id = p_assignment_id
680: and p_effective_date between paaf.effective_start_date
681: and paaf.effective_end_date
717: cursor csr_get_lu_scl (
718: p_soft_coding_keyflex_id number
719: ) is
720: select nvl (segment2, '0')
721: from hr_soft_coding_keyflex
722: where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
723:
724: --
725: --
727: cursor csr_get_pos_scl (
728: p_soft_coding_keyflex_id number
729: ) is
730: select nvl(segment3, 0)
731: from hr_soft_coding_keyflex
732: where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
733:
734: --
735: --
739: p_soft_coding_keyflex_id number
740: ) is
741: select fnd_date.canonical_to_date(segment24)
742: /* fnd_date.canonical_to_date(nvl(segment24,'0001/01/01'))*/
743: from hr_soft_coding_keyflex
744: where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
745:
746: l_new_latest_start_date date ;
747: l_old_latest_start_date date ;
874: p_assignment_id per_all_assignments_f.assignment_id%type,
875: p_local_unit hr_all_organization_units.organization_id%type)
876: is
877: select assignment_id
878: from per_all_assignments_f paaf ,hr_soft_coding_keyflex hsck
879: where person_id = p_person_id
880: and assignment_id <> p_assignment_id
881: and hsck.segment2 = to_char (p_local_unit)
882: and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
887: p_local_unit hr_all_organization_units.organization_id%type)
888: is
889: select normal_hours,
890: hourly_salaried_code, hsc.segment3 position_code, frequency
891: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
892: where paaf.assignment_id = p_assignment_id
893: and hsc.segment2 = to_char (p_local_unit)
894: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
895: and p_effective_date between paaf.effective_start_date and paaf.effective_End_date;*/
899: p_assignment_id per_all_assignments_f.assignment_id%type)
900: is
901: select normal_hours,
902: hourly_salaried_code, hsc.segment3 position_code, frequency
903: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
904: where paaf.assignment_id = p_assignment_id
905: --and hsc.segment2 = to_char (p_local_unit)
906: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
907: and p_effective_date between paaf.effective_start_date and paaf.effective_End_date;
919: cursor curr_lu_org_number (
920: p_assignment_id per_all_assignments_f.assignment_id%type,
921: p_effective_date date ) is
922: select org_information1
923: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc,hr_organization_information hoi
924: where paaf.assignment_id = p_assignment_id
925: and p_effective_date between paaf.effective_start_date and paaf.effective_end_date
926: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
927: and hoi.org_information_context = 'NO_LOCAL_UNIT_DETAILS'
930: cursor curr_position_code (
931: p_assignment_id per_all_assignments_f.assignment_id%type,
932: p_effective_date date ) is
933: select segment3
934: from hr_soft_coding_keyflex hsc, per_all_assignments_f paaf
935: where paaf.assignment_id = p_assignment_id
936: and p_effective_date between paaf.effective_start_date
937: and paaf.effective_end_date
938: and paaf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id;
947: hour_date_change date,
948: temination_date date,
949: lu_change_date date,
950: lu_value varchar2 (100),
951: job_id hr_soft_coding_keyflex.segment2%type,
952: job_change_date date,
953: status_type varchar2 (2),
954: old_lu_value varchar2 (100), /* 5519990 */
955: rev_temination varchar2(8)
1017: l_job_id number;
1018: l_empl_start_date date;
1019: l_old_scl varchar2 (30);
1020: l_new_scl varchar2 (30);
1021: l_new_lu hr_soft_coding_keyflex.segment3%type;
1022: l_old_lu hr_soft_coding_keyflex.segment3%type;
1023: l_sickness_unpaid_start date;
1024: l_sickness_unpaid_end date;
1025: l_prev_hour_flag char (1);
1018: l_empl_start_date date;
1019: l_old_scl varchar2 (30);
1020: l_new_scl varchar2 (30);
1021: l_new_lu hr_soft_coding_keyflex.segment3%type;
1022: l_old_lu hr_soft_coding_keyflex.segment3%type;
1023: l_sickness_unpaid_start date;
1024: l_sickness_unpaid_end date;
1025: l_prev_hour_flag char (1);
1026: l_hour_year_change_flag char (1);
3045: p_effective_date date,
3046: p_local_unit number
3047: ) is
3048: select normal_hours, frequency
3049: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
3050: where paaf.person_id = p_person_id
3051: and paaf.assignment_id <> p_assignment_id
3052: and paaf.normal_hours is not null
3053: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id