119: /* Retrieves all the person types associated with the person for the date */
120: CURSOR type_usages_cur IS
121: /* Person type usages includes basic person type */
122: SELECT ppt.system_person_type, ppt.user_person_type
123: FROM per_person_type_usages_f ptu
124: ,per_person_types ppt
125: WHERE ptu.person_id = p_person_id
126: AND TRUNC(p_effective_date)
127: BETWEEN ptu.effective_start_date AND ptu.effective_end_date
222: FROM per_all_people_f
223: UNION
224: SELECT person_id
225: ,effective_start_date
226: FROM per_person_type_usages_f
227: UNION
228: SELECT person_id
229: ,effective_end_date + 1
230: FROM per_person_type_usages_f );
226: FROM per_person_type_usages_f
227: UNION
228: SELECT person_id
229: ,effective_end_date + 1
230: FROM per_person_type_usages_f );
231:
232: /* Cursor to return a row if the given person type combination already */
233: /* exists in the person type combinations table */
234: CURSOR row_exists_cur