USP_DATALIST_HOUSEHOLD_ADDITIONALRELATIONSHIPS

CREATE procedure dbo.USP_DATALIST_HOUSEHOLD_ADDITIONALRELATIONSHIPS
(
    @ADDITIONALCONSTITUENTID uniqueidentifier,
    @HOUSEHOLDCONSTITUENTID uniqueidentifier = null,
    @HOUSEHOLDID uniqueidentifier = null,
    @INCLUDEHOUSEHOLDCONSTITUENT bit = 0
)
as
set nocount on;

    if @HOUSEHOLDID is null
        select 
            @HOUSEHOLDID = GROUPDATA.ID 
        from 
            dbo.GROUPMEMBER
        inner join
            dbo.GROUPDATA on GROUPMEMBER.GROUPID = GROUPDATA.ID
        where
            GROUPDATA.GROUPTYPECODE = 0
            and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1
            and GROUPMEMBER.MEMBERID = @HOUSEHOLDCONSTITUENTID

    --Return all constituents in the same household and their (if they have one) relation to the household constituent 

    --that are not already related to the additional constituent

    select
        RELATIONSHIPCONSTITUENT.ID as RELATIONSHIPCONSTITUENTID,
        case when RELATIONSHIPCONSTITUENT.ISGROUP = 1 then 2
            when RELATIONSHIPCONSTITUENT.ISORGANIZATION = 1 then 1
            else 0 end as RELATIONSHIPCONSTITUENTTYPE,
        RELATIONSHIPCONSTITUENT_NF.NAME as RELATIONSHIPNAME,
        case when ORIGINALCONSTITUENT.FIRSTNAME = '' then ORIGINALCONSTITUENT.KEYNAME
            else ORIGINALCONSTITUENT.FIRSTNAME end as CONSTITUENTNAME,
        case when RECIPROCALCONSTITUENT.FIRSTNAME = '' then RECIPROCALCONSTITUENT.KEYNAME
            else RECIPROCALCONSTITUENT.FIRSTNAME end as RECIPROCALNAME,
        dbo.UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION(RELATIONS.RECIPROCALTYPECODEID) RECIPROCALTYPE,
        EXISTINGRELATIONSHIP.RECIPROCALTYPECODEID as EXISTINGRELATIONSHIPTYPEID,
        EXISTINGRELATIONSHIP.RELATIONSHIPTYPECODEID as EXISTINGRECIPROCALTYPEID,
        GROUPMEMBER.ISPRIMARY as PRIMARYGROUPMEMBER,
        case when (not SPOUSERELATIONSHIP.ID is null) then 1 else 0 end as HASSPOUSE,
        coalesce(SPOUSERELATIONSHIP.RECIPROCALCONSTITUENTID, '00000000-0000-0000-0000-000000000000') as SPOUSEID
    from 
        dbo.GROUPMEMBER
    inner join
        dbo.CONSTITUENT RELATIONSHIPCONSTITUENT on RELATIONSHIPCONSTITUENT.ID = GROUPMEMBER.MEMBERID
    left join
        dbo.CONSTITUENT ORIGINALCONSTITUENT on ORIGINALCONSTITUENT.ID = @HOUSEHOLDCONSTITUENTID
    inner join
        dbo.CONSTITUENT RECIPROCALCONSTITUENT on RECIPROCALCONSTITUENT.ID = @ADDITIONALCONSTITUENTID
    left join
        dbo.RELATIONSHIP RELATIONS on RELATIONS.RELATIONSHIPCONSTITUENTID = @HOUSEHOLDCONSTITUENTID and RELATIONS.RECIPROCALCONSTITUENTID = GROUPMEMBER.MEMBERID
    left join
        dbo.RELATIONSHIP EXISTINGRELATIONSHIP on EXISTINGRELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ADDITIONALCONSTITUENTID and EXISTINGRELATIONSHIP.RECIPROCALCONSTITUENTID = GROUPMEMBER.MEMBERID
    left join 
        dbo.RELATIONSHIP SPOUSERELATIONSHIP on (SPOUSERELATIONSHIP.ISSPOUSE = 1) and (SPOUSERELATIONSHIP.RELATIONSHIPCONSTITUENTID = RELATIONSHIPCONSTITUENT.ID)
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIPCONSTITUENT.ID) RELATIONSHIPCONSTITUENT_NF
    where 
        GROUPMEMBER.GROUPID = @HOUSEHOLDID
        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1
        and (@INCLUDEHOUSEHOLDCONSTITUENT = 1 or (@HOUSEHOLDCONSTITUENTID is null or GROUPMEMBER.MEMBERID <> @HOUSEHOLDCONSTITUENTID))
        and RELATIONSHIPCONSTITUENT.ID <> @ADDITIONALCONSTITUENTID
    order by
        PRIMARYGROUPMEMBER desc,
        case when EXISTINGRELATIONSHIP.RECIPROCALTYPECODEID is null or EXISTINGRELATIONSHIP.RELATIONSHIPTYPECODEID is null then 0 else 1 end,
        RELATIONSHIPNAME