[Home] [Help]
333: select papf.person_id person_id, paaf.assignment_id,
334: papf.effective_start_date, null effective_end_date,
335: national_identifier, full_name, employee_number, normal_hours,
336: hourly_salaried_code, hsc.segment3 position_code, frequency
337: from per_all_people_f papf,
338: per_all_assignments_f paaf,
339: hr_soft_coding_keyflex hsc,
340: per_assignment_status_types past
341: where papf.person_id between p_start_person and p_end_person
371: select papf.person_id person_id, paaf.assignment_id,
372: papf.effective_start_date, paaf.effective_end_date,
373: national_identifier, full_name, employee_number, normal_hours,
374: hourly_salaried_code, hsc.segment3 position_code, frequency
375: from per_all_people_f papf,
376: per_all_assignments_f paaf,
377: hr_soft_coding_keyflex hsc,
378: per_assignment_status_types past
379: where paaf.person_id = papf.person_id
428: --
429: /* Cursor to get the Absence Start Date and End Date when employee is on
430: absence for more than 14 days
431: cursor csr_absence_days (
432: p_person_id per_all_people_f.person_id%type
433: ) is
434: select paa.date_start, paa.date_end
435: from per_absence_attendances paa, per_absence_attendance_types paat
436: where paat.absence_attendance_type_id =
444:
445: /* Cursor to get the Absence Start Date and End Date when employee is on
446: absence for more than 14 days */
447: cursor csr_absence_start_days (
448: p_person_id per_all_people_f.person_id%type
449: ) is
450: select paa.date_start, paa.date_end
451: from per_absence_attendances paa, per_absence_attendance_types paat
452: where paat.absence_attendance_type_id =
457: and paat.absence_category not in
458: ('S', 'PTM', 'PTS', 'PTP', 'PTA', 'VAC', 'MRE'); /* 5520062 */
459:
460: cursor csr_absence_end_days (
461: p_person_id per_all_people_f.person_id%type
462: ,p_prev_last_date date
463: ) is
464: select paa.date_start, paa.date_end
465: from per_absence_attendances paa, per_absence_attendance_types paat
681: and screen_entry_value is not null;
682:
683: /* Cursor to get all the assignment for the person except the given assignment*/
684: cursor csr_get_all_assignments
685: (p_person_id per_all_people_f.person_id%type,
686: p_assignment_id per_all_assignments_f.assignment_id%type,
687: p_local_unit hr_all_organization_units.organization_id%type)
688: is
689: select assignment_id
729: l_legal_employer_id hr_organization_units.organization_id%type;
730: l_effective_date date;
731: l_emp_start_date date;
732: l_emp_end_date date;
733: l_person_id per_all_people_f.person_id%type;
734: l_event_group_id pay_event_groups.event_group_id%type;
735: l_detailed_output l_detailed_output_table_type; -- pay_interpreter_pkg.t_detailed_output_table_type;
736: l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
737: l_detail_tab pay_interpreter_pkg.t_detailed_output_table_type;
787: l_sickness_unpaid_end date;
788: l_prev_hour_flag char (1);
789: /* Flag to set whether the hours have got changed during the period or not*/
790: l_hour_year_change_flag char (1);
791: l_national_identifier per_all_people_f.national_identifier%type;
792: l_detailed_output1 pay_interpreter_pkg.t_detailed_output_table_type;
793: l_detailed_output2 pay_interpreter_pkg.t_detailed_output_table_type;
794: l_detailed_output3 pay_interpreter_pkg.t_detailed_output_table_type;
795: l_empty_detailed_output l_detailed_output_table_type;
2511: end writetoclob;
2512:
2513: function get_assignment_all_hours (
2514: p_assignment_id in per_all_assignments_f.assignment_id%type,
2515: p_person_id in per_all_people_f.person_id%type,
2516: p_effective_date in date,
2517: p_primary_hour_value number,
2518: p_local_unit number
2519: )
2528: and p_effective_date between effective_start_date
2529: and effective_end_date;
2530:
2531: cursor csr_all_assignments_hours (
2532: p_person_id per_all_people_f.person_id%type,
2533: p_assignment_id per_all_assignments_f.assignment_id%type,
2534: p_effective_date date,
2535: p_local_unit number
2536: ) is
2595: function check_national_identifier (
2596: p_national_identifier varchar2
2597: )
2598: return varchar2 is
2599: l_return_value per_all_people_f.national_identifier%type;
2600: l_check_value number;
2601: d1 number;
2602: d2 number;
2603: m1 number;