USP_DATAFORMTEMPLATE_ADD_PRELOAD_CONSTITUENCYREFRESHPROCESS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PRELOAD_CONSTITUENCYREFRESHPROCESS
(
    @RECORDTYPEID uniqueidentifier = null output,
    @CONSTITUENCIES xml = null output
)
as
begin
    set nocount on;

    select
        @RECORDTYPEID = ID
    from
        dbo.RECORDTYPE
    where
        upper(BASETABLENAME) = 'CONSTITUENT';

    -- Populate initial list of constituencies (all defaulted to unchecked).
    set @CONSTITUENCIES = (

        select 
            CONSTITUENCYDEFINITION.ID as [@CONSTITUENCYDEFINITIONID],
            1 as [@SELECTED],
            CONSTITUENCYDEFINITION.DESCRIPTION as [@DESCRIPTION]
        from
            dbo.CONSTITUENCYDEFINITION
            inner join dbo.V_CONSTITUENCYDISPLAYORDER as DISPLAYORDER on DISPLAYORDER.ID = CONSTITUENCYDEFINITION.ID
        where
            CONSTITUENCYDEFINITION.ISACTIVE = 1
            and CONSTITUENCYDEFINITION.ISSYSTEM = 1
            -- Hide the Advocate constituency
            -- Please don't follow this pattern for other constituencies: advocacy is a special case which requires partial support
            and CONSTITUENCYDEFINITION.ID <> 'F828E957-5F5E-479A-8F23-2FFD6C7C6899'
        order by CONSTITUENCYDEFINITION.DESCRIPTION asc

        for xml path('ITEM'),type,elements,root('CONSTITUENCIES'),BINARY BASE64
    );

    return 0;
end