[Home] [Help]
532: -- to get the active start date and end_date of the employee
533: -- changed the ORDER BY from desc to asc. Fix for bug # 3300436
534: CURSOR c_active_emp_dates (l_person_id IN NUMBER) IS
535: SELECT date_start, actual_termination_date
536: FROM per_periods_of_service
537: WHERE person_id = l_person_id
538: ORDER BY date_start asc;
539:
540: -- CURSOR c_active_npw_dates (l_person_id IN NUMBER) IS
1308: /*
1309: AND not exists (select ppl.person_id
1310: from per_all_people_f ppl,
1311: per_all_assignments_f asg,
1312: per_periods_of_service ser
1313: where ppl.person_id = rsc.source_id
1314: -- and rsc.category = 'EMPLOYEE'
1315: and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
1316: and ppl.employee_number is not null
1332:
1333: -- Added this cursor (and related code) for the Enh #2660474
1334: -- to get the actual termination date of an employee
1335: /* Added the last 2 lines in the cursor to fix bug # 3300436
1336: The first record from this query will give the correct record from per_periods_of_service
1337: if there are more records in this table. */
1338: CURSOR c_term_emp_date (l_person_id IN NUMBER) IS
1339: SELECT actual_termination_date
1340: FROM per_periods_of_service
1336: The first record from this query will give the correct record from per_periods_of_service
1337: if there are more records in this table. */
1338: CURSOR c_term_emp_date (l_person_id IN NUMBER) IS
1339: SELECT actual_termination_date
1340: FROM per_periods_of_service
1341: WHERE person_id = l_person_id
1342: AND trunc(date_start) <= trunc(sysdate)
1343: ORDER BY period_of_service_id desc;
1344:
1437:
1438: END IF;
1439:
1440: ------------------------------------------------------------------------------
1441: -- We assume that per_periods_of_service will always have a value
1442: -- for termination_date column, if an employee is terminated.
1443: -- But for any bad data (if there is no value), we initialize it to sysdate
1444:
1445: IF l_date IS NULL THEN