[Home] [Help]
542: ) is
543: --
544: -- Declare cursors and local variables
545: --
546: TYPE spt_list IS TABLE OF per_person_types.system_person_type%type
547: INDEX BY binary_integer;
548: system_type spt_list;
549: --
550: -- Returns person type usages records for the specified person and person type
571: p_person_type_id in number
572: ) is
573: select ppt.person_type_id
574: ,ppt.system_person_type
575: from per_person_types ppt
576: where ppt.person_type_id = p_person_type_id;
577: l_person_type csr_person_types%rowtype;
578: --
579: -- Returns person type usages records for the specified person and system
593: where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
594: and ptu.person_id = p_person_id
595: and ptu.person_type_id in
596: (select ppt.person_type_id
597: from per_person_types ppt
598: where ( ( p_system_person_type in ('EMP','EX_EMP')
599: and ppt.system_person_type in ('EMP','EX_EMP') )
600: or ( p_system_person_type in ('APL','EX_APL')
601: and ppt.system_person_type in ('APL','EX_APL') )
966: ,ptu.object_version_number
967: ,ppt.system_person_type
968: ,ptu.effective_start_date
969: ,ptu.effective_end_date
970: from per_person_types ppt
971: ,per_person_type_usages_f ptu
972: where ppt.person_type_id = ptu.person_type_id
973: and ptu.person_type_usage_id = c_person_type_usage_id
974: and ptu.person_id = p_person_id
987: ,p_effective_start_date DATE)
988: is
989: --cursor update for bug 5706213
990: select ptu.person_type_usage_id,ptu.effective_start_date,ptu.effective_end_date,object_version_number
991: from per_person_type_usages_f ptu ,per_person_types ppt
992: where ptu.person_type_usage_id <> p_person_type_usage_id
993: and ptu.person_id = p_person_id
994: and ppt.PERSON_TYPE_ID = ptu.PERSON_TYPE_ID
995: and ppt.system_person_type = 'APL'
1014: l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
1015: l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
1016: l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
1017: l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
1018: l_system_person_type per_person_types.system_person_type%TYPE;
1019: l_proc varchar2(72) := g_package||'cancel_person_type_usage';
1020: l_effective_start_date1 per_person_type_usages_f.effective_start_date%TYPE;
1021: l_effective_end_date1 per_person_type_usages_f.effective_end_date%TYPE;
1022: l_object_version_number1 per_person_type_usages_f.object_version_number%TYPE;
1044: end if;
1045: --
1046: begin
1047: select ptu.person_type_usage_id into c_person_type_usage_id
1048: from per_person_types ppt
1049: ,per_person_type_usages_f ptu
1050: where ppt.person_type_id = ptu.person_type_id
1051: and ppt.system_person_type = p_system_person_type
1052: and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
1286: ptu.object_version_number,
1287: effective_start_date,
1288: effective_end_date
1289: from per_person_type_usages_f ptu,
1290: per_person_types pt
1291: where ptu.person_id = c_person_id
1292: and (c_date between ptu.effective_start_date
1293: and ptu.effective_end_date
1294: or c_date+1 between ptu.effective_start_date
1315: ,ptu.object_version_number
1316: ,ptu.effective_start_date
1317: ,ptu.effective_end_date
1318: from per_person_type_usages_f ptu
1319: ,per_person_types ppt
1320: where ptu.effective_start_date = l_ptu_effective_start_date
1321: and ptu.person_id = p_person_id
1322: and ptu.person_type_id = ppt.person_type_id
1323: and ppt.system_person_type = 'EX_APL';
1327: ,ptu.object_version_number
1328: ,ptu.effective_start_date
1329: ,ptu.effective_end_date
1330: from per_person_type_usages_f ptu
1331: ,per_person_types ppt
1332: where ptu.effective_end_date = l_ptu_effective_start_date-1
1333: and ptu.person_id = p_person_id
1334: and ptu.person_type_id = ppt.person_type_id
1335: and ppt.system_person_type = 'APL';
1339: ,ptu.object_version_number
1340: ,ptu.effective_start_date
1341: ,ptu.effective_end_date
1342: from per_person_type_usages_f ptu
1343: ,per_person_types ppt
1344: where ptu.effective_end_date = l_ptu_effective_start_date-1
1345: and ptu.person_id = p_person_id
1346: and ptu.person_type_id = ppt.person_type_id
1347: and ppt.system_person_type = 'OTHER';