[Home] [Help]
239:
240: -- entitlement for the employee category, for the given LOS type, for the give absence type
241: CURSOR Csr_absence_entitlements (p_abs_catg VARCHAR2, p_emp_type VARCHAR2) IS
242: SELECT NVL(entitlement_value,0)
243: FROM pqh_situations
244: WHERE business_group_id = p_bg_id
245: AND situation_type = 'ABSENCE'
246: AND length_of_service = p_los_type
247: AND situation = p_abs_catg
321: -- entitlement for the employee type, for the given LOS type, for the given Employment Category
322: CURSOR Csr_parttime_entitlements IS
323: SELECT NVL(worktime_proportional,'N'),
324: NVL(entitlement_value,0)
325: FROM pqh_situations
326: WHERE business_group_id = p_bg_id
327: AND situation_type = 'PARTTIME'
328: AND length_of_service = p_los_type
329: -- AND NVL(situation,'PT') = l_emp_catg
441: l_position_id number(10);
442:
443: CURSOR csr_prevemp_entitlements IS
444: SELECT situation, NVL(entitlement_value, 0)entitlement_value
445: FROM pqh_situations
446: WHERE business_group_id = p_bg_id
447: AND situation_type = 'EMPLOYMENT'
448: AND length_of_service = p_los_type
449: AND employee_type = l_emp_type
516: l_all_assignments := prev_job.all_assignments;
517: l_entitlement := 0;
518: l_temp_duration := 0;
519: for entitlement in csr_prevemp_entitlements loop
520: -- Look for the entitlement for this employee type and los type in pqh_situations
521: l_entitlement := entitlement.entitlement_value;
522: end loop;
523: if (l_entitlement <> 0 and l_emp_type <> '02') then
524: if p_los_type = '10' then -- General Length of Service
634: l_military_duration NUMBER(22,3) :=0;
635:
636: CURSOR csr_military_entitlement IS
637: SELECT NVL(entitlement_value,0)
638: FROM pqh_situations
639: WHERE business_group_id = p_bg_id
640: AND situation_type = 'MILITARY'
641: AND length_of_service = p_los_type
642: AND employee_type = l_emp_type
1481: l_adj_service_date DATE;
1482:
1483: CURSOR csr_military_entitlement IS
1484: SELECT '1'
1485: FROM pqh_situations
1486: WHERE business_group_id = p_bg_id
1487: AND situation_type = 'MILITARY'
1488: AND length_of_service = p_los_type
1489: AND employee_type = l_emp_type
1679: l_proc VARCHAR2(60) := g_package||'get_jub_mon_los';
1680: l_date_of_birth DATE;
1681: l_per_los_flag per_all_people_f.per_information10%TYPE;
1682: l_assg_los_type varchar2(1);
1683: l_entitlement pqh_situations.entitlement_value%TYPE :=0;
1684: l_emp_type per_all_assignments_f.employee_category%TYPE;
1685:
1686:
1687: CURSOR c_person_dob IS
1699: AND information_type = 'DE_PQH_ASSG_LOS_INFO';
1700:
1701: CURSOR c_18yrs_entitlements IS
1702: SELECT nvl(entitlement_value,0)
1703: FROM pqh_situations
1704: WHERE business_group_id = p_bg_id
1705: AND situation_type = 'PERSON'
1706: AND length_of_service = p_los_type
1707: AND situation = 'BEFORE_18'
2366: l_employer_type per_previous_employers.employer_type%TYPE := 'x';
2367: l_prev_empl_id per_previous_employers.previous_employer_id%TYPE;
2368: l_person_id per_all_people_f.person_id%TYPE;
2369: l_bg_id per_all_organization_units.organization_id%TYPE := p_bg_id;
2370: l_entitlement pqh_situations.entitlement_value%TYPE :=0;
2371: l_assignment_id per_all_assignments_f.assignment_id%TYPE := p_assignment_id;
2372: l_proc VARCHAR2(60) := g_package||'get_length_of_service';
2373: l_primary_flag per_all_assignments_f.primary_flag%TYPE;
2374:
2405: AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;
2406:
2407: CURSOR c_empl_entitlements IS
2408: SELECT NVL(entitlement_value,0)
2409: FROM pqh_situations
2410: WHERE business_group_id = p_bg_id
2411: AND situation_type = 'EMPLOYMENT'
2412: AND length_of_service = p_los_type
2413: AND situation = decode(l_employer_type,'CM','C',l_employer_type)