[Home] [Help]
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
429: l_prev_emp_period NUMBER(22,3) := 0;
430: l_temp_duration NUMBER(22,3) := 0;
431: l_entitlement NUMBER(22,3) := 0;
456: nvl(pjo.start_date, trunc(sysdate)) pjo_start_date,
457: nvl(pjo.end_date, trunc(sysdate)) pjo_end_date,
458: nvl(pju.start_date, trunc(sysdate)) pju_start_date,
459: nvl(pju.end_date, trunc(sysdate)) pju_end_date
460: from per_previous_employers pem, per_previous_jobs pjo, per_previous_job_usages pju
461: where
462: pem.previous_employer_id = pjo.previous_employer_id(+)
463: and
464: pjo.previous_job_id = pju.previous_job_id(+)
2245: -- This function checks whether the Employee's previous job length of service has
2246: -- to be considered or not.
2247: --
2248:
2249: FUNCTION check_prev_job_info (p_prev_job_id per_previous_jobs.previous_job_id%TYPE)
2250: RETURN VARCHAR2 IS
2251: l_los_flag varchar2(150) := 'N';
2252: CURSOR c_los_flag IS
2253: SELECT nvl(pjo_information4,'N')
2250: RETURN VARCHAR2 IS
2251: l_los_flag varchar2(150) := 'N';
2252: CURSOR c_los_flag IS
2253: SELECT nvl(pjo_information4,'N')
2254: FROM per_previous_jobs
2255: WHERE previous_job_id = p_prev_job_id;
2256: BEGIN
2257: OPEN c_los_flag;
2258: FETCH c_los_flag INTO l_los_flag;
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 ,
2348: p_assg_end_date IN DATE
2349: ) RETURN VARCHAR2 is
2357: l_date_of_birth DATE;
2358: l_start_date DATE;
2359: l_exists VARCHAR2(2) := '0';
2360: l_correction_factor NUMBER(22,3) := 0;
2361: l_prev_grade_id per_previous_jobs.pjo_information2%TYPE;
2362: l_prev_step_id per_previous_jobs.pjo_information3%TYPE;
2363: l_curr_grade_id per_assignments_f.grade_id%TYPE;
2364: l_curr_step_id per_spinal_point_placements_f.step_id%TYPE;
2365: l_emp_type per_previous_jobs.employment_category%TYPE := 'x';
2358: l_start_date DATE;
2359: l_exists VARCHAR2(2) := '0';
2360: l_correction_factor NUMBER(22,3) := 0;
2361: l_prev_grade_id per_previous_jobs.pjo_information2%TYPE;
2362: l_prev_step_id per_previous_jobs.pjo_information3%TYPE;
2363: l_curr_grade_id per_assignments_f.grade_id%TYPE;
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';
2361: l_prev_grade_id per_previous_jobs.pjo_information2%TYPE;
2362: l_prev_step_id per_previous_jobs.pjo_information3%TYPE;
2363: l_curr_grade_id per_assignments_f.grade_id%TYPE;
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;
2387: or l_end_date BETWEEN effective_start_date AND effective_end_date);
2388:
2389: CURSOR c_prev_job_info IS
2390: SELECT (end_date - start_date) los_days, previous_employer_id, pjo_information2, pjo_information3
2391: FROM per_previous_jobs
2392: WHERE previous_job_id = p_prev_job_id
2393: AND start_date = p_assg_start_date
2394: AND end_date = l_end_date;
2395:
2417: AND entitlement_flag = 'Y';
2418:
2419: CURSOR c_emp_catg IS
2420: SELECT nvl(employment_category,'x')
2421: FROM per_previous_jobs
2422: WHERE previous_job_id = p_prev_job_id;
2423:
2424: BEGIN
2425: