USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD

            CREATE PROCEDURE dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD

            @APPUSERID uniqueidentifier,
            @DATAFORMTEMPLATEID  uniqueidentifier,
            @CONSTITUENTID  uniqueidentifier,
            @DATEADDEDTOUSE datetime=null,
            @CHANGEAGENTID uniqueidentifier=null

            AS

            /*            
            Used by Add dataforms that add new Constituents to the system to assign the appropriate sites to the new record.

            Here is the algorithm used to assign default sites for a new record:

            ?when a user adds an individual the individual will get all of the sites that are associated with any roles that the user has permission to use the add form. 
            Unless the user is granted permission to add individuals in a role with no site associated.
            In that case, no site will be assigned.?

            So basically the granting of the add form to a role and the sites associated with that role is what controls the sites assigned.


            */

            set nocount on;

            declare @SITEIDS table (id uniqueidentifier null);

            insert into @SITEIDS
                select distinct
                    case V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM.SITESECURITYMODE
                        when 2 then
                            SYSTEMROLEAPPUSERSITE.SITEID
                        when 3 then
                            SYSTEMROLEAPPUSER.BRANCHSITEID
                    end
                from 
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM
                inner join 
                    DATAFORMINSTANCECATALOG DFIC
                on 
                    DFIC.ID = DATAFORMINSTANCECATALOGID
                inner join 
                    SYSTEMROLEAPPUSER 
                on 
                    (SYSTEMROLEAPPUSER.APPUSERID = V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM.APPUSERID 
                    and 
                    SYSTEMROLEAPPUSER.SYSTEMROLEID = V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM.SYSTEMROLEID)
                left join 
                    SYSTEMROLEAPPUSERSITE
                on 
                    SYSTEMROLEAPPUSER.ID = SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID

                where 
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM.APPUSERID = @APPUSERID
                and 
                    DFIC.DATAFORMTEMPLATECATALOGID = @DATAFORMTEMPLATEID
                and
                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@APPUSERID,DFIC.ID)=1
                and 
                    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM.GRANTORDENY = 1


            --If in a non-security site role don't add any sites.

            if exists (SELECT ID FROM @SITEIDS WHERE ID IS NULL)
                    return 0;

            --otherwise add all these sites 

            --to the constit.

            if @DATEADDEDTOUSE is null set @DATEADDEDTOUSE = getdate();

            if @CHANGEAGENTID is null
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            insert into dbo.CONSTITUENTSITE
            (
                DATEADDED,
                DATECHANGED,
                ADDEDBYID,
                CHANGEDBYID,
                CONSTITUENTID,
                SITEID
            )
            select 
                @DATEADDEDTOUSE,
                @DATEADDEDTOUSE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CONSTITUENTID,
                ID
            from 
                @SITEIDS;

            return 0;