USP_AUCTIONITEMBATCH_VALIDATENEWCONSTITUENT

            create procedure dbo.USP_AUCTIONITEMBATCH_VALIDATENEWCONSTITUENT
            (
                @BATCHCONSTITUENTID uniqueidentifier,
                @ISDONOR bit,
                @BATCHROWID uniqueidentifier = null
            )
            as
            set nocount on;

            -- Determine the constituent to add's household

            declare @INDIVIDUALSHOUSEHOLDID uniqueidentifier;
            select @INDIVIDUALSHOUSEHOLDID = BRCGM.GROUPID
            from dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER BRCGM
            inner join dbo.BATCHAUCTIONITEMCONSTITUENT HOUSEHOLD on BRCGM.GROUPID = HOUSEHOLD.ID and HOUSEHOLD.GROUPTYPECODE = 0
            where BRCGM.MEMBERID = @BATCHCONSTITUENTID;

            declare @BATCHOWNERID uniqueidentifier
            select
                @BATCHOWNERID = APPUSERID
            from dbo.BATCH
            inner join dbo.BATCHAUCTIONITEM on BATCH.ID = BATCHAUCTIONITEM.BATCHID
            where BATCHAUCTIONITEM.ID = @BATCHROWID

            -- Validate the user has permission to modify the referenced constituents

            declare @SPOUSEID uniqueidentifier
            select
                @SPOUSEID = BATCHAUCTIONITEMCONSTITUENT.EXISTINGCONSTITUENTID
            from dbo.BATCHAUCTIONITEMCONSTITUENTRELATION
            inner join dbo.BATCHAUCTIONITEMCONSTITUENT on BATCHAUCTIONITEMCONSTITUENTRELATION.RELATIONID = BATCHAUCTIONITEMCONSTITUENT.ID
            where
                BATCHAUCTIONITEMCONSTITUENTRELATION.CONSTITUENTID = @BATCHCONSTITUENTID and
                BATCHAUCTIONITEMCONSTITUENTRELATION.ISSPOUSE = 1

            -- Validate the user has permission for the spouse, primary business and parent corp

            if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@SPOUSEID, @BATCHOWNERID) = 0
            begin
                raiserror('ERR_SPOUSE_NOACCESS', 13, 1)
                return 0
            end

            declare @PRIMARYBUSINESSID uniqueidentifier
            select 
                @PRIMARYBUSINESSID = BATCHAUCTIONITEMCONSTITUENT.EXISTINGCONSTITUENTID
            from dbo.BATCHAUCTIONITEMCONSTITUENTRELATION
            inner join dbo.BATCHAUCTIONITEMCONSTITUENT on BATCHAUCTIONITEMCONSTITUENTRELATION.RELATIONID = BATCHAUCTIONITEMCONSTITUENT.ID            
            where
                BATCHAUCTIONITEMCONSTITUENTRELATION.CONSTITUENTID = @BATCHCONSTITUENTID and
                BATCHAUCTIONITEMCONSTITUENTRELATION.ISPRIMARYBUSINESS = 1

            if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@PRIMARYBUSINESSID, @BATCHOWNERID) = 0
            begin
                raiserror('ERR_PRIMARYBUSINESS_NOACCESS', 13, 1)
                return 0
            end

            declare @PARENTCORPID uniqueidentifier
            select
                @PARENTCORPID = PARENTCORPID
            from dbo.BATCHAUCTIONITEMCONSTITUENT
            where
                ID = @BATCHCONSTITUENTID and
                ISORGANIZATION = 1

            if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@PARENTCORPID, @BATCHOWNERID) = 0
            begin
                raiserror('ERR_PARENTCORP_NOACCESS', 13, 1)
                return 0
            end

            if exists (    select 1
                        from dbo.BATCHAUCTIONITEMCONSTITUENT
                        inner join dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER on BATCHAUCTIONITEMCONSTITUENT.ID = BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER.MEMBERID
                        where
                            BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER.GROUPID = @BATCHCONSTITUENTID and
                            BATCHAUCTIONITEMCONSTITUENT.EXISTINGCONSTITUENTID is not null and
                            dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(BATCHAUCTIONITEMCONSTITUENT.EXISTINGCONSTITUENTID, @BATCHOWNERID) = 0)
            begin
                raiserror('ERR_MEMBERS_NOACCESS', 13, 1)
                return 0
            end

            -- Validate constraints that only apply if the constituent is a household or if the constituent is a household member

            if @INDIVIDUALSHOUSEHOLDID is not null or exists (select ID from dbo.BATCHAUCTIONITEMCONSTITUENT where ISGROUP = 1 and GROUPTYPECODE = 0 and ID = @BATCHCONSTITUENTID)
            begin
                declare @HOUSEHOLDID uniqueidentifier
                if @INDIVIDUALSHOUSEHOLDID is not null
                    set @HOUSEHOLDID = @INDIVIDUALSHOUSEHOLDID
                else
                    set @HOUSEHOLDID = @BATCHCONSTITUENTID

                declare @CURRENTDATEEARLIESTTIME datetime;
                set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());                                            

                -- Verify that no constituents being added to a new household already belong to one.

                if exists    (    select 1 
                                from dbo.BATCHAUCTIONITEMCONSTITUENT BH -- Batch Household

                                inner join dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER BCGM on BH.ID = BCGM.GROUPID
                                inner join dbo.BATCHAUCTIONITEMCONSTITUENT BM on BCGM.MEMBERID = BM.ID -- Batch member 

                                inner join dbo.GROUPMEMBER GM on BM.EXISTINGCONSTITUENTID = GM.MEMBERID -- Batch member's possible current group memberships

                                inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID -- Possible household

                                left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                                where
                                    BH.ID = @HOUSEHOLDID and
                                    GD.GROUPTYPECODE = 0 and -- Is household

                                    BM.EXISTINGCONSTITUENTID is not null
                                    -- Make sure their membership is current

                                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                                or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                            )
                raiserror('Constituent is already a member of a household.', 13, 1);
            end

            -- Validate that the commit won't create duplicate relationships.  The only way that two existing

            -- constituents can be added to a relationship is by adding a household so this check only applies to 

            -- that constituent type.

            if exists (select ID from dbo.BATCHAUCTIONITEMCONSTITUENT where ID = @BATCHCONSTITUENTID and ISGROUP = 1 and GROUPTYPECODE = 0)
            begin
                if exists    (
                                select 1
                                from dbo.BATCHAUCTIONITEMCONSTITUENTRELATION BRCR
                                inner join dbo.BATCHAUCTIONITEMCONSTITUENT CONSTITUENT on BRCR.CONSTITUENTID = CONSTITUENT.ID
                                inner join dbo.BATCHAUCTIONITEMCONSTITUENT RELATION on BRCR.RELATIONID = RELATION.ID
                                inner join dbo.RELATIONSHIP R on
                                R.RELATIONSHIPCONSTITUENTID = CONSTITUENT.EXISTINGCONSTITUENTID and
                                R.RECIPROCALCONSTITUENTID = RELATION.EXISTINGCONSTITUENTID and
                                R.RELATIONSHIPTYPECODEID = BRCR.RELATIONSHIPTYPECODEID and
                                R.RECIPROCALTYPECODEID = BRCR.RECIPROCALTYPECODEID and
                                -- Only run the relationship check for the household's members

                                BRCR.CONSTITUENTID in (select MEMBERID from dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER where GROUPID = @BATCHCONSTITUENTID)
                            )
                    raiserror('The relationship already exists.', 13, 1);
            end

            -- If the constituent is a group, verify the start date isn't in the future

            if exists (select ID from dbo.BATCHAUCTIONITEMCONSTITUENT where ID = @BATCHCONSTITUENTID and ISGROUP = 1 and dbo.UFN_DATE_GETEARLIESTTIME(GROUPSTARTDATE) > dbo.UFN_DATE_GETEARLIESTTIME(getdate()))
            begin
                raiserror('Group start date cannot be in the future.', 13, 1);
            end

            -- If the constituent is a group, make sure the associated group type can be a donor

            if @ISDONOR = 1 and exists(select BRC.ID from dbo.BATCHAUCTIONITEMCONSTITUENT BRC inner join dbo.GROUPTYPE GT on BRC.GROUPTYPEID = GT.ID where BRC.ID = @BATCHCONSTITUENTID and GT.CANBEDONOR = 0)
            begin
                raiserror('The selected group type cannot be a donor.', 13, 1);
            end