USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYINDIVIDUALRELATIONSHIPSTILE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYINDIVIDUALRELATIONSHIPSTILE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @SPOUSENAME nvarchar(255) = null output,
    @SPOUSEID uniqueidentifier = null output,
    @ISSPOUSEDECEASED bit = null output,
    @HOUSEHOLDNAME nvarchar(255) = null output,
    @HOUSEHOLDID uniqueidentifier = null output,
    @PRIMARYBUSINESSNAME nvarchar(255) = null output,
    @PRIMARYBUSINESSID uniqueidentifier = null output,
    @HASMARRIAGERULESET bit = null output
)
as
    set nocount on;

    -- Confirm the constituent exists.  We don't actually need anything from CONSTITUENT beyond that.

    select 
        @DATALOADED = 1
    from dbo.CONSTITUENT
    where CONSTITUENT.ID = @ID;

    select @HASMARRIAGERULESET = RULESSET from dbo.UFN_MARRIAGEOPTION_GETRULES();

    -- Spouse

    select
        @SPOUSENAME = NF.NAME,
        @SPOUSEID = RELATIONSHIP.RECIPROCALCONSTITUENTID,
        @ISSPOUSEDECEASED = case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end
    from
        dbo.RELATIONSHIP
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) NF
        left join dbo.DECEASEDCONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = DECEASEDCONSTITUENT.ID
    where 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
        and RELATIONSHIP.ISSPOUSE = 1;

    -- Household

    select top 1
        @HOUSEHOLDNAME = HOUSEHOLD.NAME,
        @HOUSEHOLDID = HOUSEHOLD.ID
    from dbo.GROUPMEMBER
    inner join dbo.CONSTITUENT as HOUSEHOLD on GROUPMEMBER.GROUPID = HOUSEHOLD.ID
    inner join dbo.GROUPDATA as GROUPDATA on GROUPDATA.ID = HOUSEHOLD.ID
    where
        GROUPMEMBER.MEMBERID = @ID
        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1
        and GROUPDATA.GROUPTYPECODE = 0;

    -- Business

    select
        @PRIMARYBUSINESSNAME = ORG.KEYNAME,
        @PRIMARYBUSINESSID = ORG.ID
    from
        dbo.RELATIONSHIP
        inner join dbo.CONSTITUENT as ORG on ORG.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
    where 
        RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
        and RELATIONSHIP.ISPRIMARYBUSINESS = 1;

    return 0;