[Home] [Help]
24: p_date_end DATE) return NUMBER IS
25:
26: CURSOR c_get_ass_type IS
27: SELECT a.assignment_status_type_id
28: FROM per_assignments_f a
29: WHERE a.person_id = p_person_id
30: AND A.business_group_id + 0 = p_business_group_id
31: AND A.APPLICATION_ID = p_application_id
32: AND p_date_end between A.EFFECTIVE_START_DATE
388: AND l.business_group_id + 0 = p_business_group_id
389: AND l.assignment_status_type_id = P_dummy_asg_stat_id
390: AND EXISTS
391: (SELECT NULL
392: FROM per_assignments_f A
393: WHERE a.business_group_id + 0 = p_business_group_id
394: AND a.person_id = p_person_id
395: AND a.assignment_status_type_id = P_dummy_asg_stat_id
396: AND a.assignment_type = 'A'
521: = 'AUTO');
522:
523: CURSOR csr_assign IS
524: SELECT ASSIGNMENT_ID
525: FROM PER_ASSIGNMENTS_f
526: WHERE business_group_id + 0 = p_business_group_id
527: AND PERSON_ID = p_person_id
528: AND APPLICATION_ID = p_application_id
529: AND ASSIGNMENT_TYPE = 'A'
547: v_dummy_asg_id NUMBER(1);
548: v_letter_request_id NUMBER(15);
549: v_test_new_req NUMBER(1);
550: v_letter_type NUMBER(15);
551: v_assignment_id per_assignments_f.assignment_id%TYPE;
552: l_dummy_number number; -- Added for bug 3680947.
553:
554: --
555:
758:
759: CURSOR c_chk_lines IS
760: SELECT distinct(1)
761: FROM per_letter_request_lines L
762: , per_assignments_f a
763: WHERE l.person_id = P_person_id
764: AND l.business_group_id + 0 = p_business_group_id
765: AND l.assignment_id = a.assignment_id
766: AND a.person_id = P_person_id
831: AND lrl.person_id = P_person_id
832: AND lrl.person_id = P_person_id
833: AND EXISTS
834: (SELECT NULL
835: FROM per_assignments_f a
836: WHERE a.assignment_id = lrl.assignment_id
837: AND a.person_id = P_person_id
838: AND a.application_id = p_application_id
839: AND a.business_group_id + 0 = P_business_group_id);
945:
946: CURSOR c_chk_prv_status IS
947: select 1
948: from per_assignment_status_types t
949: , per_assignments_f asg
950: where asg.person_id = p_person_id
951: and nvl(t.business_group_id,
952: p_business_group_id) = p_business_group_id
953: and t.PER_SYSTEM_STATUS = 'TERM_APL'
1297: and s.end_date = p_end_date
1298: and sa.assignment_id = s.assignment_id
1299: and exists
1300: (select null
1301: from per_assignments_f paf
1302: where paf.person_id = p_person_id
1303: and paf.application_id = p_application_id
1304: and paf.assignment_type = 'A'
1305: and paf.effective_end_date = p_end_date
1349: -- This was achieved by removing the sub-query
1350: cursor chk_sec_stat is
1351: select 1
1352: from per_secondary_ass_statuses s
1353: ,per_assignments_f a
1354: where s.business_group_id + 0 = p_business_group_id
1355: and s.start_date is not null
1356: and a.business_group_id + 0 = p_business_group_id
1357: and a.person_id = p_person_id
1367: -- This was achieved by removing the sub-query
1368: cursor c_sec_stat is
1369: select sa.assignment_id
1370: from per_secondary_ass_statuses sa
1371: ,per_assignments_f paf
1372: where sa.business_group_id + 0 = p_business_group_id
1373: and sa.start_date <= p_end_date
1374: and (sa.end_date is null
1375: or sa.end_date > p_end_date)
1396: where s.business_group_id + 0 = p_business_group_id
1397: and trunc(s.start_date) > p_end_date
1398: and s.assignment_id in
1399: (select a.assignment_id
1400: from per_assignments_f a
1401: where a.business_group_id + 0 = p_business_group_id
1402: and a.person_id = p_person_id
1403: and a.application_id = p_application_id
1404: and a.assignment_type = 'A'
1440: P_application_id NUMBER) IS
1441: CURSOR chk_events IS
1442: SELECT E.EVENT_ID
1443: FROM PER_EVENTS E
1444: , PER_ASSIGNMENTS_F A
1445: WHERE A.PERSON_ID = P_person_id
1446: AND E.business_group_id + 0 = p_business_group_id
1447: AND A.business_group_id + 0 = p_business_group_id
1448: AND A.APPLICATION_ID = P_application_id
1454: CURSOR chk_bookings IS
1455: SELECT distinct(1)
1456: FROM PER_BOOKINGS B
1457: , PER_EVENTS E
1458: , PER_ASSIGNMENTS_F A
1459: WHERE A.PERSON_ID = P_person_id
1460: AND A.APPLICATION_ID = P_application_id
1461: AND B.EVENT_ID = E.EVENT_ID
1462: AND E.DATE_START >= P_date_end