[Home] [Help]
21:
22: -- Cursor to fetch distinct person records.
23: CURSOR c_per_records IS
24: SELECT distinct a.person_id person_id
25: FROM per_all_people_f a,hr_organization_information hoi
26: WHERE a.business_group_id = hoi.organization_id
27: AND hoi.org_information_context = 'GHR_US_ORG_INFORMATION';
28:
29: -- Cursor to find total number of distinct person records
28:
29: -- Cursor to find total number of distinct person records
30: CURSOR c_tot_per_records IS
31: SELECT COUNT(distinct a.person_id) person_count
32: FROM per_all_people_f a,hr_organization_information hoi
33: WHERE a.business_group_id = hoi.organization_id
34: AND hoi.org_information_context = 'GHR_US_ORG_INFORMATION';
35:
36: CURSOR c_completion_status(c_session_id NUMBER) IS
229: l_errbuf VARCHAR2(2000);
230: l_retcode NUMBER;
231:
232: l_per_err_cnt NUMBER(15);
233: l_ssn per_all_people_f.national_identifier%TYPE;
234: l_full_name per_all_people_f.full_name%TYPE;
235: l_dummy VARCHAR2(1);
236: l_sid NUMBER;
237:
230: l_retcode NUMBER;
231:
232: l_per_err_cnt NUMBER(15);
233: l_ssn per_all_people_f.national_identifier%TYPE;
234: l_full_name per_all_people_f.full_name%TYPE;
235: l_dummy VARCHAR2(1);
236: l_sid NUMBER;
237:
238: --Bug # 9329643
309: ) IS
310: CURSOR cur_per IS
311: SELECT per.full_name
312: ,per.national_identifier
313: FROM per_all_people_f per
314: WHERE per.person_id = p_person_id
315: AND NVL(p_effective_date,TRUNC(sysdate)) between per.effective_start_date
316: and per.effective_end_date;
317: BEGIN
2065: l_assignment_id pay_element_entries_f.assignment_id%type;
2066: l_position_id per_all_assignments_f.position_id%type;
2067: l_person_id per_all_assignments_f.person_id%type;
2068: l_agncy_contrib_elig_date per_people_extra_info.pei_information14%type;
2069: l_effective_start_date per_all_people_f.effective_start_date%type;
2070: l_effective_end_date per_all_people_f.effective_end_date%type;
2071: l_retirement_plan pay_element_entry_values_f.screen_entry_value%type;
2072: l_agency_effective_date date;
2073: l_calculated_date date;
2066: l_position_id per_all_assignments_f.position_id%type;
2067: l_person_id per_all_assignments_f.person_id%type;
2068: l_agncy_contrib_elig_date per_people_extra_info.pei_information14%type;
2069: l_effective_start_date per_all_people_f.effective_start_date%type;
2070: l_effective_end_date per_all_people_f.effective_end_date%type;
2071: l_retirement_plan pay_element_entry_values_f.screen_entry_value%type;
2072: l_agency_effective_date date;
2073: l_calculated_date date;
2074: l_req VARCHAR2 (25);
2097:
2098: l_exists boolean := false;
2099: l_check_date date;
2100: --Bug # 9329643 Modified SSN to Emp No
2101: l_employee_number per_all_people_f.employee_number%type;
2102: l_employee_name per_all_people_f.full_name%type;
2103:
2104: l_tsp_abv_agcy_rec_cnt number :=0;
2105: l_tsp_future_rec_cnt number :=0;
2098: l_exists boolean := false;
2099: l_check_date date;
2100: --Bug # 9329643 Modified SSN to Emp No
2101: l_employee_number per_all_people_f.employee_number%type;
2102: l_employee_name per_all_people_f.full_name%type;
2103:
2104: l_tsp_abv_agcy_rec_cnt number :=0;
2105: l_tsp_future_rec_cnt number :=0;
2106: l_process_log_upd_flag number :=0;
2108: CURSOR c_tsp_agncy_date IS
2109: select paf.position_id,ppf.person_id,paf.assignment_id,
2110: ppei.pei_information14 ,ppf.effective_start_date, ppf.effective_end_date,ppf.national_identifier,
2111: ppf.full_name,ppf.employee_number
2112: from per_all_people_f ppf, per_all_assignments_f paf, per_people_extra_info ppei
2113: where ppf.person_id=paf.person_id
2114: and ppf.person_id=ppei.person_id
2115: and ppei.information_type='GHR_US_PER_BENEFIT_INFO'
2116: and paf.primary_flag='Y'