USP_REVENUEBATCH_VALIDATENEWCONSTITUENT

            CREATE procedure dbo.USP_REVENUEBATCH_VALIDATENEWCONSTITUENT
            (
                @REVENUEBATCHCONSTITUENTID uniqueidentifier,
                @ISDONOR bit,
                @BATCHROWID uniqueidentifier = null,
                @BATCHOWNERID uniqueidentifier = null
            )
            as
            set nocount on;

      -- BUG#584814: if the constituent is a newly created spouse added to a constituent in batch

      if exists(select ID from dbo.BATCHCONSTITUENTUPDATE where ID = @REVENUEBATCHCONSTITUENTID and SPOUSE_ID is null and len(coalesce(SPOUSE_LASTNAME, '')) > 0)
        return 0;

            --KevinKoe 6/6/11 - This SP is entered only if the constituent specified does not already exist in the system. If a constituent

            --    is deleted, it does not delete all batch revenue records tied to it, as it can cause merge issues. Due to this, it is

            --    necessary to check if a constituent that does not exist in the system was actually added through the batch. If it was not,

            --    then throw an error. If an error is not thrown here, then the batch commit will attempt to create the constituent, but will

            --    fail as there is no data with which to create the constituent.

            if not exists(select ID from dbo.BATCHREVENUECONSTITUENT where ID = @REVENUEBATCHCONSTITUENTID)
            begin
                raiserror('ERR_CONSTITUENT_DOESNOTEXIST', 13, 1)
                return 0
            end

            -- Determine the constituent to add's household

            declare @INDIVIDUALSHOUSEHOLDID uniqueidentifier;
            select @INDIVIDUALSHOUSEHOLDID = BRCGM.GROUPID
            from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BRCGM
            inner join dbo.BATCHREVENUECONSTITUENT HOUSEHOLD on BRCGM.GROUPID = HOUSEHOLD.ID and HOUSEHOLD.GROUPTYPECODE = 0
            where BRCGM.MEMBERID = @REVENUEBATCHCONSTITUENTID;

            if @BATCHOWNERID is null
            begin
                select
                    @BATCHOWNERID = APPUSERID
                from dbo.BATCH
                inner join dbo.BATCHREVENUE on BATCH.ID = BATCHREVENUE.BATCHID
                where BATCHREVENUE.ID = @BATCHROWID
            end

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

            declare @SPOUSEID uniqueidentifier
            select
                @SPOUSEID = BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID
            from dbo.BATCHREVENUECONSTITUENTRELATION
            inner join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTRELATION.RELATIONID = BATCHREVENUECONSTITUENT.ID
            where
                BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = @REVENUEBATCHCONSTITUENTID and
                BATCHREVENUECONSTITUENTRELATION.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 = BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID
            from dbo.BATCHREVENUECONSTITUENTRELATION
            inner join dbo.BATCHREVENUECONSTITUENT on BATCHREVENUECONSTITUENTRELATION.RELATIONID = BATCHREVENUECONSTITUENT.ID            
            where
                BATCHREVENUECONSTITUENTRELATION.CONSTITUENTID = @REVENUEBATCHCONSTITUENTID and
                BATCHREVENUECONSTITUENTRELATION.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.BATCHREVENUECONSTITUENT
            where
                ID = @REVENUEBATCHCONSTITUENTID 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.BATCHREVENUECONSTITUENT
                        inner join dbo.BATCHREVENUECONSTITUENTGROUPMEMBER on BATCHREVENUECONSTITUENT.ID = BATCHREVENUECONSTITUENTGROUPMEMBER.MEMBERID
                        where
                            BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID = @REVENUEBATCHCONSTITUENTID and
                            BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID is not null and
                            dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(BATCHREVENUECONSTITUENT.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.BATCHREVENUECONSTITUENT where ISGROUP = 1 and GROUPTYPECODE = 0 and ID = @REVENUEBATCHCONSTITUENTID)
            begin
                declare @HOUSEHOLDID uniqueidentifier
                if @INDIVIDUALSHOUSEHOLDID is not null
                    set @HOUSEHOLDID = @INDIVIDUALSHOUSEHOLDID
                else
                    set @HOUSEHOLDID = @REVENUEBATCHCONSTITUENTID

                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.BATCHREVENUECONSTITUENT BH -- Batch Household

                                inner join dbo.BATCHREVENUECONSTITUENTGROUPMEMBER BCGM on BH.ID = BCGM.GROUPID
                                inner join dbo.BATCHREVENUECONSTITUENT 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.BATCHREVENUECONSTITUENT where ID = @REVENUEBATCHCONSTITUENTID and ISGROUP = 1 and GROUPTYPECODE = 0)
            begin
                if exists    (
                                select 1
                                from dbo.BATCHREVENUECONSTITUENTRELATION BRCR
                                inner join dbo.BATCHREVENUECONSTITUENT CONSTITUENT on BRCR.CONSTITUENTID = CONSTITUENT.ID
                                inner join dbo.BATCHREVENUECONSTITUENT 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.BATCHREVENUECONSTITUENTGROUPMEMBER where GROUPID = @REVENUEBATCHCONSTITUENTID)
                            )
                    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.BATCHREVENUECONSTITUENT where ID = @REVENUEBATCHCONSTITUENTID 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.BATCHREVENUECONSTITUENT BRC inner join dbo.GROUPTYPE GT on BRC.GROUPTYPEID = GT.ID where BRC.ID = @REVENUEBATCHCONSTITUENTID and GT.CANBEDONOR = 0)
            begin
                raiserror('The selected group type cannot be a donor.', 13, 1);
            end