[Home] [Help]
402: AND pei.information_type = 'SA_PASSPORT' AND pei.pei_information_category = 'SA_PASSPORT'
403: AND p_date between trunc(fnd_date.canonical_to_date(pei.pei_information3),'MM') and fnd_date.canonical_to_date(pei.pei_information4);
404: --cursor to get the Civil ID
405: cursor get_civil_id (p_person_id number,p_date date) is
406: SELECT NATIONAL_IDENTIFIER from per_all_people_f pap WHERE pap.person_id = p_person_id
407: and trunc(p_date,'MM') between trunc(pap.effective_start_date,'MM') and pap.effective_end_date;
408: --cursor to get the joining date of the newly hired employees
409: cursor get_start_date(p_person_id number) is
410: select date_start from per_periods_of_service where person_id = p_person_id and trunc(date_start,'MM') = to_date(l_effective_date,'DD-MM-YYYY');
412: cursor get_term_date_new (p_person_id number) is
413: select actual_termination_date from per_periods_of_service where person_id = p_person_id;
414: --cursor to get the details of the newly hired employees
415: cursor get_details_new (p_person_id number,p_date date) is
416: select full_name,nationality,date_of_birth from per_all_people_f where person_id = p_person_id
417: and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
418: --cursor to get GOSI number of the employees
419: cursor get_GOSI_number_new (p_person_id number) is
420: select segment2
422: where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
423: and paf.person_id = p_person_id;
424: --cursor to get names of the terminated employees
425: cursor get_name_term (p_person_id number,p_date date) is
426: select full_name from per_all_people_f where person_id = p_person_id
427: and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
428: --cursor to get GOSI numbers of the terminated employees
429: cursor get_GOSI_number_term(p_person_id number) is
430: select segment2
2098: (pay_balance_pkg.get_value(l_emp_gosi_ann_asg_ptd_db,rec_leav_assact.assignment_action_id));
2099: end if;
2100: select count(*)
2101: into l_loc_nat
2102: from per_all_people_f
2103: where person_id = rec_leav_assact.person_id
2104: and upper(nationality) = FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY')
2105: and trunc(l_prev_mon_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
2106: if l_loc_nat > 0 then
2649: per_information1,
2650: per_information2,
2651: per_information10,
2652: last_name
2653: from per_all_people_f peo
2654: ,per_all_assignments_f paa
2655: where peo.person_id = paa.person_id
2656: and paa.assignment_id = l_assignment_id;
2657: --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2751: select peo.national_identifier
2752: ,peo.sex
2753: ,peo.marital_status
2754: ,peo.date_of_birth
2755: from per_all_people_f peo
2756: ,per_all_assignments_f paa
2757: where peo.person_id = paa.person_id
2758: and paa.assignment_id = l_assignment_id;
2759: --and l_effective_date between paa.effective_start_date and paa.effective_end_date
2779: and hlp.lookup_code = hl.TOWN_OR_CITY;
2780: /* Cursor to fetch nationality, employee_number , e-mail address*/
2781: CURSOR get_nationality (l_person_id NUMBER , l_date date) IS
2782: select nationality,employee_number,email_address
2783: from per_all_people_f ppf
2784: where ppf.person_id = l_person_id
2785: and l_date between ppf.effective_start_date and ppf.effective_end_date;
2786: --cursor to get the passport number and other details
2787: cursor get_passport_number (l_person_id number,l_date date) is
2891: /* Cursor to fetch phone numbers of employee */
2892: CURSOR get_phone_number (l_phone_type VARCHAR2, l_person_id NUMBER, l_effective_date DATE) IS
2893: SELECT phone_number
2894: FROM per_phones pp,
2895: per_all_people_f pap
2896: WHERE pp.parent_id = pap.person_id
2897: AND pp.phone_type = l_phone_type
2898: AND pap.person_id = l_person_id
2899: AND l_effective_date BETWEEN pp.date_from