Script to get dependents for Employee
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;
/
-
Comments