CREATE OR REPLACE FUNCTION APPS.HR_GET_DEPENDENT(p_element_entry_id IN NUMBER,
    p_contact_number IN NUMBER,
p_data_field IN VARCHAR2)

RETURN VARCHAR2 IS
l_contact_name VARCHAR2(300);

CURSOR C1 IS
SELECT DEP.Full_Name Contact_Full_Name,
       DEP.National_Identifier,
       PCP.Name    PCP,
       LOOK.Meaning,
       DPNT.Cvg_Strt_Dt Effective_Start_Date,
       DPNT.Cvg_Thru_Dt Effective_End_Date
  FROM PER_PEOPLE_X              PER,
       PER_ASSIGNMENTS_X         ASG,
       PAY_ELEMENT_ENTRIES_X     ENTRY,
       BEN_PRTT_ENRT_RSLT_X      PEN,
       BEN_ELIG_CVRD_DPNT_X      DPNT,
       BEN_PRMRY_CARE_PRVDR_X    PCP,
       PER_PEOPLE_X              DEP,
       PER_CONTACT_RELATIONSHIPS REL,
       HR_LOOKUPS                LOOK
 WHERE ENTRY.Element_Entry_Id = P_Element_Entry_Id
   AND ENTRY.Assignment_Id = ASG.Assignment_Id
   AND ASG.Person_Id = PER.Person_Id
   AND PER.Person_Id = PEN.Person_Id
   AND PEN.Prtt_Enrt_Rslt_Id  = DPNT.Prtt_Enrt_Rslt_Id
   AND DPNT.Elig_Cvrd_Dpnt_Id = PCP.Elig_Cvrd_Dpnt_Id(+)
   AND DPNT.Dpnt_Person_Id = DEP.Person_Id
   AND REL.Person_Id = PER.Person_Id
   AND REL.Contact_Person_Id = DEP.Person_Id
   AND LOOK.Lookup_Type = ‘CONTACT’
   AND REL.Contact_Type = LOOK.Lookup_Code;

x number := 0;

BEGIN

FOR dep_rec IN C1 LOOP
x := x + 1;

IF x = p_contact_number THEN
IF p_data_field = ‘NAME’ THEN
   l_contact_name := dep_rec.contact_full_name;
ELSIF p_data_field = ‘SSN’ THEN
   l_contact_name := dep_rec.national_identifier;
ELSIF p_data_field = ‘PCP’ THEN
   l_contact_name := dep_rec.pcp;
ELSIF p_data_field = ‘TYPE’ THEN –relationship_type
   l_contact_name := dep_rec.meaning;
ELSIF p_data_field = ‘START’ THEN — start
   l_contact_name := dep_rec.effective_start_date;
ELSIF p_data_field = ‘END’ THEN — end
   l_contact_name := dep_rec.effective_end_date;
END IF;

EXIT;

END IF;

END LOOP;

       RETURN l_contact_name;

EXCEPTION WHEN OTHERS THEN
     RETURN null;
END HR_GET_DEPENDENT;
/