USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYCONTACTINFOTILE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSUMMARYCONTACTINFOTILE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PHONENUMBERID uniqueidentifier = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @PHONETYPE nvarchar(100) = null output,
    @EMAILADDRESSID uniqueidentifier = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @EMAILADDRESSTYPE nvarchar(100) = null output,
    @SOCIALMEDIAACCOUNTS xml = null output,
    @DONOTCALL bit = null output,
    @ISCONFIDENTIAL bit = null output,
    @DONOTEMAIL bit = null output,
    @EMAILISCONFIDENTIAL bit = null output,
    @DONOTTEXT bit = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    --Primary phone number
    select 
        @PHONENUMBERID = PHONE.ID,
        @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
        @PHONETYPE = PHONETYPECODE.DESCRIPTION,
        @DONOTCALL = PHONE.DONOTCALL,
        @ISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
        @DONOTTEXT = PHONE.DONOTTEXT
    from 
        dbo.PHONE
        left join dbo.PHONETYPECODE on PHONE.PHONETYPECODEID = PHONETYPECODE.ID
    where 
        PHONE.CONSTITUENTID = @ID
        and PHONE.ISPRIMARY = 1;

    --Primary email address
    select 
        @EMAILADDRESSID = EMAILADDRESS.ID,
        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
        @EMAILADDRESSTYPE = EMAILADDRESSTYPECODE.DESCRIPTION,
        @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
        @EMAILISCONFIDENTIAL = EMAILADDRESS.ISCONFIDENTIAL
    from 
        dbo.EMAILADDRESS
        left join dbo.EMAILADDRESSTYPECODE on EMAILADDRESS.EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODE.ID
    where 
        EMAILADDRESS.CONSTITUENTID = @ID and
        EMAILADDRESS.ISPRIMARY = 1;

    --Social media accounts
    select @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@ID);

    if exists(select 1 from dbo.CONSTITUENT where ID = @ID)
        set @DATALOADED = 1;

    return 0;