[Home] [Help]
474: -- to get the active start date and end_date of the employee
475: -- changed the ORDER BY from desc to asc. Fix for bug # 3300436
476: CURSOR c_active_emp_dates (l_person_id IN NUMBER) IS
477: SELECT date_start, actual_termination_date
478: FROM per_periods_of_service
479: WHERE person_id = l_person_id
480: ORDER BY date_start asc;
481:
482: -- CURSOR c_active_npw_dates (l_person_id IN NUMBER) IS
1250: /*
1251: AND not exists (select ppl.person_id
1252: from per_all_people_f ppl,
1253: per_all_assignments_f asg,
1254: per_periods_of_service ser
1255: where ppl.person_id = rsc.source_id
1256: -- and rsc.category = 'EMPLOYEE'
1257: and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
1258: and ppl.employee_number is not null
1274:
1275: -- Added this cursor (and related code) for the Enh #2660474
1276: -- to get the actual termination date of an employee
1277: /* Added the last 2 lines in the cursor to fix bug # 3300436
1278: The first record from this query will give the correct record from per_periods_of_service
1279: if there are more records in this table. */
1280: CURSOR c_term_emp_date (l_person_id IN NUMBER) IS
1281: SELECT actual_termination_date
1282: FROM per_periods_of_service
1278: The first record from this query will give the correct record from per_periods_of_service
1279: if there are more records in this table. */
1280: CURSOR c_term_emp_date (l_person_id IN NUMBER) IS
1281: SELECT actual_termination_date
1282: FROM per_periods_of_service
1283: WHERE person_id = l_person_id
1284: AND trunc(date_start) <= trunc(sysdate)
1285: ORDER BY period_of_service_id desc;
1286:
1379:
1380: END IF;
1381:
1382: ------------------------------------------------------------------------------
1383: -- We assume that per_periods_of_service will always have a value
1384: -- for termination_date column, if an employee is terminated.
1385: -- But for any bad data (if there is no value), we initialize it to sysdate
1386:
1387: IF l_date IS NULL THEN