[Home] [Help]
34: Is
35: select ctr.rltd_per_rsds_w_dsgntr_flag,
36: ctr.contact_relationship_id,
37: ctr.object_version_number
38: from per_contact_relationships ctr
39: where ctr.person_id = c_per_id
40: and ctr.contact_person_id = c_conper_id
41: and p_benasg_effdate between ctr.date_start and nvl(ctr.date_end,p_benasg_effdate);
42: --
409: --
410: -- Declare cursors and local variables
411: --
412: Type PerDetailsType is record
413: (per_id per_contact_relationships.contact_person_id%TYPE
414: ,contype varchar2(100)
415: );
416: Type PerDetails is table of PerDetailsType index by binary_integer;
417: --
538: ,c_contact_type in varchar2
539: )
540: is
541: select ctr.contact_person_id, ctr.contact_type
542: from per_contact_relationships ctr
543: where ctr.person_id = c_person_id
544: and c_eff_date
545: between nvl(ctr.date_start,hr_api.g_sot) and nvl(ctr.date_end,hr_api.g_eot)
546: and ctr.contact_type = c_contact_type
551: ,c_eff_date in date
552: )
553: is
554: select distinct ctr.contact_person_id
555: from per_contact_relationships ctr
556: where exists
557: (select null
558: from per_person_type_usages_f ptu,
559: per_person_types pet
572: ,c_eff_date in date
573: )
574: is
575: select ctr.contact_person_id, ctr.contact_type
576: from per_contact_relationships ctr
577: where ctr.person_id = c_person_id
578: and ctr.contact_person_id = nvl(c_contact_person_id, ctr.contact_person_id)
579: and ctr.personal_flag = 'Y'
580: and c_eff_date
587: ,c_eff_date in date
588: )
589: is
590: select ctr.contact_person_id, ctr.contact_type
591: from per_contact_relationships ctr
592: where ctr.person_id = c_person_id
593: and ctr.contact_person_id = nvl(c_contact_person_id, ctr.contact_person_id)
594: and ctr.personal_flag = 'Y'
595: order by ctr.sequence_number;