USP_DATALIST_STUDENTEMERGENCYCONTACT

CREATE procedure dbo.USP_DATALIST_STUDENTEMERGENCYCONTACT
(
    @CONTEXTID uniqueidentifier
)
as
    set nocount on;

    declare @CURRENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    select RELATIONSHIP.ID,
           RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
           (select RECIPROCAL.DESCRIPTION 
           from dbo.RELATIONSHIP RECIPROCAL 
           where RECIPROCAL.RELATIONSHIPCONSTITUENTID = @CONTEXTID and 
           RECIPROCAL.RECIPROCALCONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and
           RECIPROCAL.RELATIONSHIPTYPECODEID = RELATIONSHIP.RECIPROCALTYPECODEID and
           RECIPROCAL.RECIPROCALTYPECODEID = RELATIONSHIP.RELATIONSHIPTYPECODEID),
           dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PHONE.PHONETYPECODEID) + case when ORGANIZATION.ISORGANIZATION = 1 then ' (' + dbo.UFN_CONSTITUENT_BUILDNAME(ORGANIZATION.ID) + ')' else '' end as TYPE,
           dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER) as CONTACTINFO,
           case when dbo.UFN_CONSTITUENT_ISFACULTY(RELATIONSHIP.RELATIONSHIPCONSTITUENTID) = 1 then 5   --Faculty

           else 0                                                                                       --Individual

           end as CONSTITUENTTYPE,
           case when dbo.UFN_CONSTITUENT_ISFACULTY(RELATIONSHIP.RELATIONSHIPCONSTITUENTID) = 1 then 'faculty'
           else 'individual'
           end as CONSTITUENTTYPELABEL
    from dbo.RELATIONSHIP
    inner join dbo.PHONE on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = PHONE.CONSTITUENTID
    left join dbo.RELATIONSHIP ORG_RELATIONSHIP on ORG_RELATIONSHIP.ID = PHONE.RELATIONSHIPID
    left join dbo.CONSTITUENT ORGANIZATION on ORG_RELATIONSHIP.RELATIONSHIPCONSTITUENTID = ORGANIZATION.ID
    where RELATIONSHIP.RECIPROCALCONSTITUENTID = @CONTEXTID and 
        RELATIONSHIP.ISEMERGENCYCONTACT = 1 and
        ((PHONE.ENDDATE is null) or (PHONE.ENDDATE > @CURRENTDATE))