Description:
Script to get dependents for employee describes dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links.
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;
/
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
