USP_RECORDOPERATION_CONSTITUENTSUMMARYUPDATESELECTEDTILES

CREATE procedure dbo.USP_RECORDOPERATION_CONSTITUENTSUMMARYUPDATESELECTEDTILES
(
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @CONSTITUENTTYPECODE tinyint,
    @SELECTEDTILES xml = null,
    @USERSELECTEDNOTILES bit = 0
)
as begin

    declare @CURRENTDATE datetime = getdate();
    declare @SUMMARYTILECONTEXTID uniqueidentifier;
    declare @APPUSERSUMMARYTILECONTEXTID uniqueidentifier;

    select @SUMMARYTILECONTEXTID =
        dbo.UFN_CONSTITUENTSUMMARY_GETSUMMARYTILECONTEXTFORCONSTITUENTTYPECODE(@CONSTITUENTTYPECODE);

    -- Ensure a row exists in APPUSERSUMMARYTILECONTEXT for this user and constituent type.

    select @APPUSERSUMMARYTILECONTEXTID = ID
    from dbo.APPUSERSUMMARYTILECONTEXT
    where SUMMARYTILECONTEXTID = @SUMMARYTILECONTEXTID
    and APPUSERID = @CURRENTAPPUSERID

    if @APPUSERSUMMARYTILECONTEXTID is null
    begin
        set @APPUSERSUMMARYTILECONTEXTID = newid();
        insert into dbo.APPUSERSUMMARYTILECONTEXT
        (
            ID,
            APPUSERID,
            SUMMARYTILECONTEXTID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values (
            @APPUSERSUMMARYTILECONTEXTID,
            @CURRENTAPPUSERID,
            @SUMMARYTILECONTEXTID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        )
    end

    -- Rename the columns in the @SELECTEDTILES XML.


    declare @RENAMEDSELECTEDTILES xml;

    set @RENAMEDSELECTEDTILES = (
        select
            T.c.value('(TILEID)[1]','uniqueidentifier') as DATAFORMINSTANCECATALOGID,
            T.c.value('(SEQUENCE)[1]','int') as SEQUENCE,
            T.c.value('(CONSTITUENTSUMMARYAVAILABLETILEID)[1]','uniqueidentifier') as AVAILABLESUMMARYTILEID
        from @SELECTEDTILES.nodes('/SELECTEDTILES/ITEM') T(c)
        order by T.c.value('(SEQUENCE)[1]','int')
        for xml raw('ITEM'),type,elements,root('SELECTEDTILES'),BINARY BASE64
    );

    exec dbo.USP_APPUSERSUMMARYTILECONTEXT_GETSELECTEDTILES_UPDATEFROMXML @APPUSERSUMMARYTILECONTEXTID, @RENAMEDSELECTEDTILES, @CHANGEAGENTID, @CURRENTDATE;
    return 0;

end