[Home] [Help]
133: END get_working_time_ratio;
134:
135: -- -----------------------------------------------------------------------*
136: -- FUNCTION get_employee_type
137: -- This function returns the agent type (as held in PER_INFORMATION15 of PER_ALL_PEOPLE_F)
138: -- for the person
139: -- -----------------------------------------------------------------------*
140:
141: FUNCTION get_employee_type (p_person_id IN per_all_people_f.person_id%TYPE,
137: -- This function returns the agent type (as held in PER_INFORMATION15 of PER_ALL_PEOPLE_F)
138: -- for the person
139: -- -----------------------------------------------------------------------*
140:
141: FUNCTION get_employee_type (p_person_id IN per_all_people_f.person_id%TYPE,
142: p_determination_date IN DATE)
143: RETURN VARCHAR2 IS
144: l_emp_type per_assignments_f.employee_category%TYPE;
145: l_proc varchar2(60) := g_package||'get_emp_type';
145: l_proc varchar2(60) := g_package||'get_emp_type';
146: l_leg_code varchar2(30) := 'x';
147: CURSOR csr_emp_type IS
148: SELECT per.per_information15
149: FROM per_all_people_f per
150: WHERE per.person_id = p_person_id
151: AND p_determination_date between per.effective_start_date and per.effective_end_date;
152:
153: CURSOR csr_leg_code IS
151: AND p_determination_date between per.effective_start_date and per.effective_end_date;
152:
153: CURSOR csr_leg_code IS
154: SELECT hr_api.return_legislation_code(per.business_group_id)
155: FROM per_all_people_f per
156: WHERE per.person_id = p_person_id
157: AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
158:
159: CURSOR csr_emp_catg IS
201: -- -----------------------------------------------------------------------*
202:
203:
204: FUNCTION get_absent_period (p_bg_id IN per_all_organization_units.organization_id%TYPE,
205: p_person_id IN per_all_people_f.person_id%TYPE,
206: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
207: p_los_type IN hr_lookups.lookup_code%TYPE,
208: p_start_date IN DATE,
209: p_end_date IN DATE
398: -- This function returns the previous employment duration to be taken into
399: -- while calculating the LOS.
400: -- -----------------------------------------------------------------------*
401:
402: FUNCTION get_previous_employment(p_person_id IN per_all_people_f.person_id%TYPE,
403: p_assignment_id IN per_assignments_f.assignment_id%TYPE,
404: p_start_date IN DATE,
405: p_end_date IN DATE) RETURN NUMBER IS
406: l_prev_emp_period NUMBER(22,3) := 0;
420: -- This function returns the length of previous employment to be taken into
421: -- while calculating the LOS for French Public Sector.
422: -- -----------------------------------------------------------------------*
423:
424: FUNCTION get_length_previous_employment(p_person_id IN per_all_people_f.person_id%TYPE,
425: p_bg_id IN per_all_organization_units.organization_id%TYPE,
426: p_los_type IN hr_lookups.lookup_code%TYPE,
427: p_previous_job_id IN per_previous_jobs.previous_job_id%TYPE
428: ) RETURN NUMBER IS
471: select to_number(hsck.segment7) corps_id
472: from hr_soft_coding_keyflex hsck,
473: fnd_id_flex_structures fifs,
474: per_all_assignments_f paf,
475: per_all_people_f ppf
476: where hsck.id_flex_num = fifs.id_flex_num
477: and fifs.id_flex_structure_code = 'FR_STATUTORY_INFO.'
478: and fifs.application_id = 800
479: and fifs.id_flex_code = 'SCL'
585: -- in Person EIT FR_PQH_ADDL_SENIORITY_INFO
586: -- -----------------------------------------------------------------------*
587:
588:
589: FUNCTION get_correction_factor ( p_person_id IN per_all_people_f.person_id%TYPE,
590: p_los_type IN hr_lookups.lookup_code%TYPE,
591: p_effective_date IN DATE)
592: RETURN NUMBER IS
593:
620: -- This function returns corrected number of days in Military Service for the
621: -- person as held in FR_PQH_BONIFICATION_DETAILS
622: -- -----------------------------------------------------------------------*
623: FUNCTION get_military_service_period (p_bg_id IN hr_all_organization_units.organization_id%TYPE,
624: p_person_id IN per_all_people_f.person_id%TYPE,
625: p_assignment_id IN per_assignments_f.assignment_id%TYPE,
626: p_los_type IN hr_lookups.lookup_code%TYPE,
627: p_start_date IN DATE,
628: p_end_date IN DATE)
679: -- This function returns the general / public length of service for the employee
680: -- -----------------------------------------------------------------------*
681:
682: FUNCTION get_gen_pub_length_of_service( p_bg_id IN per_all_organization_units.organization_id%TYPE,
683: p_person_id IN per_all_people_f.person_id%TYPE,
684: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
685: p_los_type IN VARCHAR2,
686: p_determination_date IN DATE)
687: RETURN NUMBER IS
776: -- This function returns the length of service in the current grade for the employee
777: -- -----------------------------------------------------------------------*
778:
779: FUNCTION get_grade_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
780: p_person_id IN per_all_people_f.person_id%TYPE,
781: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
782: p_determination_date IN DATE)
783: RETURN NUMBER IS
784:
889: -- This function returns the length of service in the current position for the employee
890: -- -----------------------------------------------------------------------*
891:
892: FUNCTION get_position_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
893: p_person_id IN per_all_people_f.person_id%TYPE,
894: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
895: p_determination_date IN DATE)
896: RETURN NUMBER IS
897:
1001: -- This function returns the length of service in the current position for the employee
1002: -- -----------------------------------------------------------------------*
1003:
1004: FUNCTION get_corps_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
1005: p_person_id IN per_all_people_f.person_id%TYPE,
1006: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1007: p_determination_date IN DATE)
1008: RETURN NUMBER IS
1009:
1117: -- This function returns the length of service in the current grade step for the employee
1118: -- -----------------------------------------------------------------------*
1119:
1120: FUNCTION get_step_length_of_service (p_bg_id IN per_all_organization_units.organization_id%TYPE,
1121: p_person_id IN per_all_people_f.person_id%TYPE,
1122: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1123: p_determination_date IN DATE)
1124: RETURN NUMBER IS
1125: Cursor Csr_asg_step IS
1281: l_assignment_id PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE;
1282: l_los_days NUMBER(22,3) := 0;
1283: l_los_return NUMBER(22,3);
1284: l_emp_type PER_ALL_ASSIGNMENTS_F.employee_category%TYPE;
1285: l_person_id PER_ALL_PEOPLE_F.person_id%TYPE;
1286: l_bg_id per_all_organization_units.organization_id%TYPE := p_bg_id;
1287: l_adj_service_date DATE;
1288: l_proc varchar2(60) := g_package||'get_length_of_sevice';
1289: l_exists VARCHAR2(2) := '0';
1289: l_exists VARCHAR2(2) := '0';
1290:
1291: CURSOR CSR_validate_person IS
1292: SELECT '1'
1293: FROM per_all_people_f
1294: WHERE person_id = p_person_id
1295: AND l_determination_date BETWEEN effective_start_date AND effective_end_date;
1296:
1297: CURSOR CSR_validate_assignment IS
1464: --
1465: -- This Function returns the military service duration for an employee
1466: --
1467: FUNCTION get_de_military_service_period(p_bg_id IN hr_all_organization_units.organization_id%TYPE,
1468: p_person_id IN per_all_people_f.person_id%TYPE,
1469: p_los_type IN hr_lookups.lookup_code%TYPE,
1470: p_start_date IN DATE,
1471: p_end_date IN DATE)
1472: RETURN VARCHAR2 IS
1546: -- This function returns the length of service in Public Services for an employee
1547: -- -----------------------------------------------------------------------*
1548:
1549: FUNCTION get_de_pub_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
1550: p_person_id IN per_all_people_f.person_id%TYPE,
1551: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1552: p_los_type IN VARCHAR2,
1553: p_assg_start_date IN DATE ,
1554: p_assg_end_date IN DATE
1556: RETURN NUMBER IS
1557:
1558: CURSOR c_person_dob IS
1559: SELECT date_of_birth
1560: FROM per_all_people_f
1561: WHERE person_id = p_person_id
1562: AND effective_start_date <= p_assg_end_date
1563: AND effective_end_date >= p_assg_start_date;
1564:
1661: -- This function returns the length of service for Jubilee Money for an employee
1662: -- -----------------------------------------------------------------------*
1663:
1664: FUNCTION get_jub_mon_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
1665: p_person_id IN per_all_people_f.person_id%TYPE,
1666: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1667: p_los_type IN VARCHAR2,
1668: p_assg_start_date IN DATE ,
1669: p_assg_end_date IN DATE
1677: l_general_los NUMBER(22,3) := 0;
1678: l_military_duration NUMBER(22,3) := 0;
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:
1685:
1686:
1687: CURSOR c_person_dob IS
1688: SELECT date_of_birth, nvl(per_information10,'N')
1689: FROM per_all_people_f
1690: WHERE person_id = p_person_id
1691: AND effective_start_date < p_assg_end_date
1692: AND effective_end_date > p_assg_start_date;
1693:
1797: -- This function returns the length of service for BDA calculation of an employee
1798: -- -----------------------------------------------------------------------*
1799:
1800: FUNCTION get_bda_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
1801: p_person_id IN per_all_people_f.person_id%TYPE,
1802: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
1803: p_los_type IN VARCHAR2,
1804: p_assg_start_date IN DATE ,
1805: p_assg_end_date IN DATE
1815: AND effective_end_date = decode(p_assg_end_date,trunc(sysdate),effective_end_date,p_assg_end_date);
1816:
1817: CURSOR c_person_dob IS
1818: SELECT date_of_birth
1819: FROM per_all_people_f
1820: WHERE person_id = p_person_id
1821: AND effective_start_date <= p_assg_end_date
1822: AND effective_end_date >= p_assg_start_date;
1823:
1975:
1976: --
1977: -- Function to get the current grade for a person
1978: --
1979: FUNCTION get_current_grade(p_person_id IN per_all_people_f.person_id%TYPE)
1980: RETURN NUMBER IS
1981:
1982: CURSOR c_curr_grade IS
1983: SELECT grade_id
2003: -- This function return the Length of service in a Grade for an Employee - German PS
2004: --
2005:
2006: FUNCTION get_de_grade_length_of_service(p_bg_id IN per_all_organization_units.organization_id%TYPE,
2007: p_person_id IN per_all_people_f.person_id%TYPE,
2008: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2009: p_los_type IN VARCHAR2,
2010: p_assg_start_date IN DATE ,
2011: p_assg_end_date IN DATE
2110:
2111: --
2112: -- This Function returns the step in the current assignment of a person
2113: --
2114: FUNCTION get_current_step(p_person_id IN per_all_people_f.person_id%TYPE)
2115: RETURN NUMBER IS
2116:
2117: Cursor c_current_step IS
2118: SELECT spp.step_id
2136: -- This function returns the length of service in the current grade step for the employee
2137: -- -----------------------------------------------------------------------*
2138:
2139: FUNCTION get_de_step_length_of_service (p_bg_id IN per_all_organization_units.organization_id%TYPE,
2140: p_person_id IN per_all_people_f.person_id%TYPE,
2141: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2142: p_los_type IN VARCHAR2,
2143: p_assg_start_date IN DATE ,
2144: p_assg_end_date IN DATE
2266: -- This function returns the length of service in the current assignment for the employee
2267: -- -----------------------------------------------------------------------*
2268:
2269: FUNCTION get_de_assg_length_of_service( p_bg_id IN per_all_organization_units.organization_id%TYPE,
2270: p_person_id IN per_all_people_f.person_id%TYPE,
2271: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2272: p_los_type IN VARCHAR2,
2273: p_assg_start_date IN DATE ,
2274: p_assg_end_date IN DATE)
2339: -- This is called in the Report for Period of Employment - DEPS
2340: --
2341:
2342: FUNCTION get_length_of_service( p_bg_id IN per_all_organization_units.organization_id%TYPE,
2343: p_person_id IN per_all_people_f.person_id%TYPE,
2344: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE DEFAULT NULL,
2345: p_prev_job_id IN per_previous_jobs.previous_job_id%TYPE DEFAULT NULL,
2346: p_los_type IN VARCHAR2,
2347: p_assg_start_date IN DATE ,
2364: l_curr_step_id per_spinal_point_placements_f.step_id%TYPE;
2365: l_emp_type per_previous_jobs.employment_category%TYPE := 'x';
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:
2375: CURSOR CSR_validate_person IS
2376: SELECT '1'
2377: FROM per_all_people_f
2378: WHERE person_id = p_person_id
2379: AND (p_assg_start_date BETWEEN effective_start_date AND effective_end_date
2380: or l_end_date BETWEEN effective_start_date AND effective_end_date);
2381:
2399: WHERE previous_employer_id = l_prev_empl_id;
2400:
2401: CURSOR c_person_dob IS
2402: SELECT date_of_birth
2403: FROM per_all_people_f
2404: WHERE person_id = p_person_id
2405: AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;
2406:
2407: CURSOR c_empl_entitlements IS
2646: RETURN l_los_return;
2647:
2648: END get_length_of_service;
2649:
2650: FUNCTION get_de_correction_factor(p_person_id IN per_all_people_f.person_id%TYPE,
2651: p_los_type IN hr_lookups.lookup_code%TYPE,
2652: p_effective_date IN DATE)
2653: RETURN VARCHAR2 IS
2654: