[Home] [Help]
203: --
204: -- Declare cursors and local variables
205: --
206: Type PerDetailsType is record
207: (per_id per_contact_relationships.contact_person_id%TYPE
208: ,contype varchar2(100)
209: );
210: Type PerDetails is table of PerDetailsType index by binary_integer;
211: --
332: ,c_contact_type in varchar2
333: )
334: is
335: select ctr.contact_person_id, ctr.contact_type
336: from per_contact_relationships ctr
337: where ctr.person_id = c_person_id
338: and c_eff_date
339: between nvl(ctr.date_start,hr_api.g_sot) and nvl(ctr.date_end,hr_api.g_eot)
340: and ctr.contact_type = c_contact_type
345: ,c_eff_date in date
346: )
347: is
348: select distinct ctr.contact_person_id
349: from per_contact_relationships ctr
350: where exists
351: (select null
352: from per_person_type_usages_f ptu,
353: per_person_types pet
366: ,c_eff_date in date
367: )
368: is
369: select ctr.contact_person_id, ctr.contact_type
370: from per_contact_relationships ctr
371: where ctr.person_id = c_person_id
372: and ctr.contact_person_id = nvl(c_contact_person_id, ctr.contact_person_id)
373: and ctr.personal_flag = 'Y'
374: and c_eff_date
381: ,c_eff_date in date
382: )
383: is
384: select ctr.contact_person_id, ctr.contact_type
385: from per_contact_relationships ctr
386: where ctr.person_id = c_person_id
387: and ctr.contact_person_id = nvl(c_contact_person_id, ctr.contact_person_id)
388: and ctr.personal_flag = 'Y'
389: order by ctr.sequence_number;
583: Is
584: select ctr.rltd_per_rsds_w_dsgntr_flag,
585: ctr.contact_relationship_id,
586: ctr.object_version_number
587: from per_contact_relationships ctr
588: where ctr.person_id = c_per_id
589: and ctr.contact_person_id = c_conper_id
590: and p_benasg_effdate between ctr.date_start and nvl(ctr.date_end,p_benasg_effdate);
591: --