USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD

            CREATE PROCEDURE dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_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 security groups to the new record.

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

                ?when a user adds an individual the individual will get all of the security groups 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 security group associated.
                In that case, no security group will be assigned.?

                So basically the granting of the add form to a role and the security groups associated with that role is what controls the groups assigned.
                */

                set nocount on;

                declare @ATTRIBUTEIDS table (ID uniqueidentifier null);

                insert into @ATTRIBUTEIDS(ID)
                    select distinct
                        CONSTITUENTSECURITYATTRIBUTEID
                    from
                        dbo.DATAFORMINSTANCECATALOG
                        inner join dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM as FORMPERMISSIONS ON FORMPERMISSIONS.DATAFORMINSTANCECATALOGID = DATAFORMINSTANCECATALOG.ID
                        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = FORMPERMISSIONS.SYSTEMROLEID
                        inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = SYSTEMROLEAPPUSER.ID 
                    where 
                        DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = @DATAFORMTEMPLATEID and
                        FORMPERMISSIONS.GRANTORDENY = 1 and
                        FORMPERMISSIONS.APPUSERID = @APPUSERID and
                        SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID and
                        SYSTEMROLEAPPUSER.CONSTITUENTSECURITYMODECODE = 2 and
                        dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@APPUSERID, DATAFORMINSTANCECATALOG.ID) = 1;

                --If in a non-security group role don't add any groups.

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

                --otherwise add all these securitygroups 

                --to the constit.

                IF @DATEADDEDTOUSE IS NULL 
                    SET @DATEADDEDTOUSE = GETDATE();

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

                insert into dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT(DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID, CONSTITUENTID, CONSTIT_SECURITY_ATTRIBUTEID)
                    select 
                        @DATEADDEDTOUSE,
                        @DATEADDEDTOUSE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CONSTITUENTID,
                        ID
                    from 
                        @ATTRIBUTEIDS;

                RETURN 0;