[Home] [Help]
125: select asg.person_id,
126: asg.location_id
127: into v_person_id,
128: v_location_id
129: from per_all_people_f per,
130: per_assignments_f asg
131: where asg.assignment_id = p_asg_id
132: and asg.business_group_id = p_business_group_id
133: and p_effective_date between asg.effective_start_date and asg.effective_end_date
561:
562:
563: l_proc_name varchar2(100);
564: l_result Varchar2(1);
565: l_person_id per_all_people_f.person_id%type;
566: l_tsp_amount Number;
567: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type;
568: l_payroll_id pay_payrolls_f.payroll_id%type;
569: l_enrt_cvg_strt_dt Date;
665: Return Varchar2 is
666:
667: l_proc_name varchar2(100);
668: l_result Varchar2(1);
669: l_person_id per_all_people_f.person_id%type;
670: l_tsp_percentage Number;
671: l_enrt_cvg_strt_dt Date;
672: l_emp_csrs Varchar2(1);
673: Nothing_to_do Exception;
1118:
1119: l_proc_name varchar2(100);
1120: l_eligible varchar2(1);
1121: l_ee_50 varchar2(1);
1122: l_person_id per_all_people_f.person_id%type;
1123: l_payroll_id per_all_assignments_f.payroll_id%type;
1124: l_pgm_year_end_dt Date;
1125: --l_date_of_birth Date;
1126: l_tspc_rate_start_dt Date;
1160: /*
1161: -- get date of birth of an employee
1162: Cursor c_get_dob is
1163: Select date_of_birth
1164: from per_all_people_f
1165: where person_id = l_person_id
1166: and trunc(l_current_check_date) between effective_start_date and effective_end_date;
1167:
1168: Cursor c_get_pgm_yr is
1478: v_eligible VARCHAR2(1);
1479: v_ler_name ben_ler_f.name%type;
1480: v_opt_name ben_opt_f.name%type;
1481: v_pl_name ben_pl_f.name%type;
1482: v_person_id per_all_people_f.person_id%type;
1483: v_coe_date Date;
1484:
1485: Cursor c_get_person_id is
1486: Select person_id
1605: Return Date is
1606:
1607: l_proc_name VARCHAR2(100);
1608: v_agency_date Date;
1609: v_person_id per_all_people_f.person_id%type;
1610:
1611: Cursor c_get_person_id is
1612: Select person_id
1613: from per_all_assignments_f
1643: Return Date is
1644:
1645: l_proc_name VARCHAR2(100);
1646: v_emp_date Date;
1647: v_person_id per_all_people_f.person_id%type;
1648:
1649: Cursor c_get_person_id is
1650: Select person_id
1651: from per_all_assignments_f
1682: Return Date is
1683:
1684: l_proc_name VARCHAR2(100);
1685: v_coe_date Date;
1686: v_person_id per_all_people_f.person_id%type;
1687:
1688: Cursor c_get_person_id is
1689: Select person_id
1690: from per_all_assignments_f
1723: l_proc_name varchar2(100);
1724: v_latest_hire_date Date;
1725: v_cvg_rate_date Date;
1726: v_hire_date Date;
1727: v_person_id per_all_people_f.person_id%type;
1728: v_payroll_id per_all_assignments_f.payroll_id%type;
1729: v_noa_family_code ghr_pa_requests.noa_family_code%type;
1730: v_first_noa_code ghr_pa_requests.first_noa_code%type;
1731: v_rehire Varchar2(1);
1739:
1740: -- get hire date
1741: Cursor c_get_hire_date is
1742: select decode(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,null) hire_date
1743: from per_all_people_f per, per_periods_of_service pps
1744: where per.person_id = v_person_id
1745: and per.person_id = pps.person_id
1746: and PER.EMPLOYEE_NUMBER IS NOT NULL
1747: and PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
1854: RETURN varchar2 is
1855:
1856: l_proc_name varchar2(100);
1857: l_result Varchar2(1);
1858: l_person_id per_all_people_f.person_id%type;
1859: l_tsp_cu_amount Number;
1860: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type;
1861: l_payroll_id pay_payrolls_f.payroll_id%type;
1862: l_rt_strt_dt Date;
2003: order by start_date ;
2004:
2005: Cursor c_get_dob is
2006: Select date_of_birth
2007: from per_all_people_f
2008: where person_id = p_person_id
2009: and trunc(l_current_check_date) between effective_start_date and effective_end_date;
2010:
2011: Begin