[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
399: national_identifier, full_name, employee_number, normal_hours,
400: hourly_salaried_code, hsc.segment3 position_code, frequency
401: from per_all_people_f papf,
402: per_all_assignments_f paaf,
403: hr_soft_coding_keyflex hsc,
404: per_assignment_status_types past
405: where paaf.person_id = papf.person_id
406: and papf.person_id between p_start_person and p_end_person
407: and paaf.business_group_id = papf.business_group_id
410: and hsc.segment2 = to_char (p_local_unit)
411: and paaf.assignment_status_type_id =
412: past.assignment_status_type_id
413: and paaf.assignment_id = (select min(assignment_id)
414: from per_all_assignments_f asg,hr_soft_coding_keyflex hsck
415: where person_id = papf.person_id
416: and hsck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
417: and hsck.segment2 = to_char (p_local_unit))
418: --and past.PER_SYSTEM_STATUS = 'TERM_ASSIGN'
489:
490: /* Cursor to get the Organization No for the Local Unit based on the soft
491: coding keyflex id */
492: cursor csr_get_org_no (
493: p_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type
494: ) is
495: select org_information1
496: from hr_organization_information hoi, hr_soft_coding_keyflex hsc
497: where org_information_context = 'NO_LOCAL_UNIT_DETAILS'
492: cursor csr_get_org_no (
493: p_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type
494: ) is
495: select org_information1
496: from hr_organization_information hoi, hr_soft_coding_keyflex hsc
497: where org_information_context = 'NO_LOCAL_UNIT_DETAILS'
498: and hsc.segment2 = organization_id
499: and soft_coding_keyflex_id = p_soft_coding_keyflex_id;
500:
507: p_effective_date date,
508: p_job_id number
509: ) is
510: select segment3
511: from hr_soft_coding_keyflex hsc, per_all_assignments_f paaf
512: where paaf.job_id = p_job_id
513: and assignment_id = p_assignment_id
514: and p_effective_date between paaf.effective_start_date
515: and paaf.effective_end_date
551: cursor csr_get_lu_scl (
552: p_soft_coding_keyflex_id number
553: ) is
554: select nvl (segment2, '0')
555: from hr_soft_coding_keyflex
556: where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
557:
558: --
559: --
561: cursor csr_get_pos_scl (
562: p_soft_coding_keyflex_id number
563: ) is
564: select nvl(segment3, 0)
565: from hr_soft_coding_keyflex
566: where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
567:
568: --
569: --
573: p_soft_coding_keyflex_id number
574: ) is
575: select fnd_date.canonical_to_date(segment24)
576: /* fnd_date.canonical_to_date(nvl(segment24,'0001/01/01'))*/
577: from hr_soft_coding_keyflex
578: where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
579:
580: l_new_latest_start_date date ;
581: l_old_latest_start_date date ;
708: p_assignment_id per_all_assignments_f.assignment_id%type,
709: p_local_unit hr_all_organization_units.organization_id%type)
710: is
711: select assignment_id
712: from per_all_assignments_f paaf ,hr_soft_coding_keyflex hsck
713: where person_id = p_person_id
714: and assignment_id <> p_assignment_id
715: and hsck.segment2 = to_char (p_local_unit)
716: and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
721: p_local_unit hr_all_organization_units.organization_id%type)
722: is
723: select normal_hours,
724: hourly_salaried_code, hsc.segment3 position_code, frequency
725: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
726: where paaf.assignment_id = p_assignment_id
727: and hsc.segment2 = to_char (p_local_unit)
728: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
729: and p_effective_date between paaf.effective_start_date and paaf.effective_End_date;*/
733: p_assignment_id per_all_assignments_f.assignment_id%type)
734: is
735: select normal_hours,
736: hourly_salaried_code, hsc.segment3 position_code, frequency
737: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
738: where paaf.assignment_id = p_assignment_id
739: --and hsc.segment2 = to_char (p_local_unit)
740: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
741: and p_effective_date between paaf.effective_start_date and paaf.effective_End_date;
753: cursor curr_lu_org_number (
754: p_assignment_id per_all_assignments_f.assignment_id%type,
755: p_effective_date date ) is
756: select org_information1
757: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc,hr_organization_information hoi
758: where paaf.assignment_id = p_assignment_id
759: and p_effective_date between paaf.effective_start_date and paaf.effective_end_date
760: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
761: and hoi.org_information_context = 'NO_LOCAL_UNIT_DETAILS'
764: cursor curr_position_code (
765: p_assignment_id per_all_assignments_f.assignment_id%type,
766: p_effective_date date ) is
767: select segment3
768: from hr_soft_coding_keyflex hsc, per_all_assignments_f paaf
769: where paaf.assignment_id = p_assignment_id
770: and p_effective_date between paaf.effective_start_date
771: and paaf.effective_end_date
772: and paaf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id;
781: hour_date_change date,
782: temination_date date,
783: lu_change_date date,
784: lu_value varchar2 (100),
785: job_id hr_soft_coding_keyflex.segment2%type,
786: job_change_date date,
787: status_type varchar2 (2),
788: old_lu_value varchar2 (100), /* 5519990 */
789: rev_temination varchar2(8)
851: l_job_id number;
852: l_empl_start_date date;
853: l_old_scl varchar2 (30);
854: l_new_scl varchar2 (30);
855: l_new_lu hr_soft_coding_keyflex.segment3%type;
856: l_old_lu hr_soft_coding_keyflex.segment3%type;
857: l_sickness_unpaid_start date;
858: l_sickness_unpaid_end date;
859: l_prev_hour_flag char (1);
852: l_empl_start_date date;
853: l_old_scl varchar2 (30);
854: l_new_scl varchar2 (30);
855: l_new_lu hr_soft_coding_keyflex.segment3%type;
856: l_old_lu hr_soft_coding_keyflex.segment3%type;
857: l_sickness_unpaid_start date;
858: l_sickness_unpaid_end date;
859: l_prev_hour_flag char (1);
860: l_hour_year_change_flag char (1);
2858: p_effective_date date,
2859: p_local_unit number
2860: ) is
2861: select normal_hours, frequency
2862: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
2863: where paaf.person_id = p_person_id
2864: and paaf.assignment_id <> p_assignment_id
2865: and paaf.normal_hours is not null
2866: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id