[Home] [Help]
337: select papf.person_id person_id, paaf.assignment_id,
338: papf.effective_start_date, null effective_end_date,
339: national_identifier, full_name, employee_number, normal_hours,
340: hourly_salaried_code, hsc.segment3 position_code, frequency
341: from per_all_people_f papf,
342: per_all_assignments_f paaf,
343: hr_soft_coding_keyflex hsc,
344: per_assignment_status_types past
345: where papf.person_id between p_start_person and p_end_person
414: select papf.person_id person_id, paaf.assignment_id,
415: papf.effective_start_date, paaf.effective_end_date,
416: national_identifier, full_name, employee_number, normal_hours,
417: hourly_salaried_code, hsc.segment3 position_code, frequency
418: from per_all_people_f papf,
419: per_all_assignments_f paaf,
420: hr_soft_coding_keyflex hsc,
421: per_assignment_status_types past
422: where paaf.person_id = papf.person_id
511: --
512: /* Cursor to get the Absence Start Date and End Date when employee is on
513: absence for more than 14 days
514: cursor csr_absence_days (
515: p_person_id per_all_people_f.person_id%type
516: ) is
517: select paa.date_start, paa.date_end
518: from per_absence_attendances paa, per_absence_attendance_types paat
519: where paat.absence_attendance_type_id =
527:
528: /* Cursor to get the Absence Start Date and End Date when employee is on
529: absence for more than 14 days */
530: cursor csr_absence_start_days (
531: p_person_id per_all_people_f.person_id%type
532: ) is
533: select paa.date_start, paa.date_end
534: from per_absence_attendances paa, per_absence_attendance_types paat
535: where paat.absence_attendance_type_id =
587:
588:
589:
590: cursor csr_absence_end_days (
591: p_person_id per_all_people_f.person_id%type
592: ,p_prev_last_date date
593: ) is
594: select paa.date_start, paa.date_end
595: from per_absence_attendances paa, per_absence_attendance_types paat
857: and screen_entry_value is not null;
858:
859: /* Cursor to get all the assignment for the person except the given assignment*/
860: cursor csr_get_all_assignments
861: (p_person_id per_all_people_f.person_id%type,
862: p_assignment_id per_all_assignments_f.assignment_id%type,
863: p_local_unit hr_all_organization_units.organization_id%type)
864: is
865: select assignment_id
905: l_legal_employer_id hr_organization_units.organization_id%type;
906: l_effective_date date;
907: l_emp_start_date date;
908: l_emp_end_date date;
909: l_person_id per_all_people_f.person_id%type;
910: l_event_group_id pay_event_groups.event_group_id%type;
911: l_detailed_output l_detailed_output_table_type; -- pay_interpreter_pkg.t_detailed_output_table_type;
912: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
913: l_detail_tab pay_interpreter_pkg.t_detailed_output_table_type;
963: l_sickness_unpaid_end date;
964: l_prev_hour_flag char (1);
965: /* Flag to set whether the hours have got changed during the period or not*/
966: l_hour_year_change_flag char (1);
967: l_national_identifier per_all_people_f.national_identifier%type;
968: l_detailed_output1 pay_interpreter_pkg.t_detailed_output_table_type;
969: l_detailed_output2 pay_interpreter_pkg.t_detailed_output_table_type;
970: l_detailed_output3 pay_interpreter_pkg.t_detailed_output_table_type;
971: l_empty_detailed_output l_detailed_output_table_type;
2689: end writetoclob;
2690:
2691: function get_assignment_all_hours (
2692: p_assignment_id in per_all_assignments_f.assignment_id%type,
2693: p_person_id in per_all_people_f.person_id%type,
2694: p_effective_date in date,
2695: p_primary_hour_value number,
2696: p_local_unit number
2697: )
2706: and p_effective_date between effective_start_date
2707: and effective_end_date;
2708:
2709: cursor csr_all_assignments_hours (
2710: p_person_id per_all_people_f.person_id%type,
2711: p_assignment_id per_all_assignments_f.assignment_id%type,
2712: p_effective_date date,
2713: p_local_unit number
2714: ) is
2773: function check_national_identifier (
2774: p_national_identifier varchar2
2775: )
2776: return varchar2 is
2777: l_return_value per_all_people_f.national_identifier%type;
2778: l_check_value number;
2779: d1 number;
2780: d2 number;
2781: m1 number;