USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENCYREFRESHPREPROCESS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENCYREFRESHPREPROCESS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @NAME nvarchar(100) = null output,
    @RECORDTYPEID uniqueidentifier = null output,
    @CONSTITUENCIES xml = null output,
    @INCLUDEALLUSERDEFINEDCONSTITUENCIES bit = null output,
    @IDSETREGISTERID uniqueidentifier = null output
)
as

    set nocount on;

    set @DATALOADED = 0;
    set @TSLONG = 0;

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

    select
        @DATALOADED = 1,
        @TSLONG = TSLONG,
        @NAME = NAME,
        @INCLUDEALLUSERDEFINEDCONSTITUENCIES = INCLUDEALLUSERDEFINEDCONSTITUENCIES,
        @IDSETREGISTERID = IDSETREGISTERID
    from 
        dbo.CONSTITUENCYREFRESHPROCESS
    where 
        ID = @ID;

    set @CONSTITUENCIES = (

        select 
            CONSTITUENCYDEFINITION.ID as [@CONSTITUENCYDEFINITIONID],
            case when SELECTEDCONSTITUENCIES.ID is null then 0 else 1 end as [@SELECTED],
            CONSTITUENCYDEFINITION.DESCRIPTION as [@DESCRIPTION]
        from 
            dbo.CONSTITUENCYDEFINITION
            left join dbo.UFN_CONSTITUENCYREFRESHPROCESS_GETCONSTITUENCIES(@ID) as SELECTEDCONSTITUENCIES on SELECTEDCONSTITUENCIES.CONSTITUENCYDEFINITIONID = CONSTITUENCYDEFINITION.ID
            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;