USP_CONSTITUENT_DECEASEFROMRULES

            CREATE procedure dbo.USP_CONSTITUENT_DECEASEFROMRULES
            (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
            set nocount on;

            declare @CURRENTDATE datetime;

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

            set @CURRENTDATE = getdate();

            declare @ADDSOLICITCODE bit;
            declare @SOLICITCODEID uniqueidentifier;
            declare @SOLICITCODESTARTDATE datetime;
            declare @SETEFTSTATUS bit;
            declare @EFTSTATUSCODE tinyint;
            declare @DISABLEWEATHRATINGS bit;
            declare @DISABLESPOUSERECOGNITIONCREDIT bit;
            declare @SETMARITALSTATUS bit;
            declare @MARITALSTATUSCODEID uniqueidentifier;
            declare @MARKINACTIVE bit;
            declare @SPOUSEID uniqueidentifier;
            declare @RECOGNITIONDEFAULTID uniqueidentifier;

            select
                @ADDSOLICITCODE = ADDSOLICITCODE,
                @SOLICITCODEID = SOLICITCODEID,
                @SOLICITCODESTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE),
                @SETEFTSTATUS  = SETEFTSTATUS,
                @EFTSTATUSCODE  = EFTSTATUSCODE,
                @DISABLEWEATHRATINGS  = DISABLEWEATHRATINGS,
                @DISABLESPOUSERECOGNITIONCREDIT  = DISABLESPOUSERECOGNITIONCREDIT,
                @SETMARITALSTATUS  = SETMARITALSTATUS,
                @MARITALSTATUSCODEID  = MARITALSTATUSCODEID,
                @MARKINACTIVE  = MARKINACTIVE
            from
                dbo.UFN_DECEASINGRULE_GETRULES();

            select
                @SPOUSEID = RECIPROCALCONSTITUENTID
            from
                dbo.RELATIONSHIP
            where
                RELATIONSHIPCONSTITUENTID = @ID
            and
                ISSPOUSE = 1;

            begin try
                --Add solicit code

                if @ADDSOLICITCODE = 1
                    begin
                    --Delete all solicit codes of this type that start today or in the future

                        /* cache current context information */
                        declare @CONTEXTCACHE varbinary(128);
                        set @CONTEXTCACHE = CONTEXT_INFO();

                        /* set CONTEXT_INFO to @CHANGEAGENTID */
                        if not @CHANGEAGENTID is null
                            set CONTEXT_INFO @CHANGEAGENTID

                        delete from
                            dbo.CONSTITUENTSOLICITCODE
                        where
                            CONSTITUENTID = @ID
                        and
                            SOLICITCODEID = @SOLICITCODEID
                        and
                            STARTDATE is not null
                        and
                            STARTDATE >= @SOLICITCODESTARTDATE;

                        /* reset CONTEXT_INFO to previous value */
                        if not @CONTEXTCACHE is null
                            set CONTEXT_INFO @CONTEXTCACHE                            

                    --If solicit code of this type started before today but has not ended , mark end date as yesterday

                        update 
                            dbo.CONSTITUENTSOLICITCODE
                        set
                            ENDDATE = (@SOLICITCODESTARTDATE - 1),
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where
                            CONSTITUENTID = @ID
                        and
                            SOLICITCODEID = @SOLICITCODEID
                        and
                            (STARTDATE is null
                            or
                            STARTDATE < @SOLICITCODESTARTDATE)
                        and
                            (ENDDATE is null
                            or
                            ENDDATE >= @SOLICITCODESTARTDATE);

                        declare @SEQUENCE int;
                        select @SEQUENCE = coalesce(max(SEQUENCE),0)+1 from dbo.CONSTITUENTSOLICITCODE where CONSTITUENTID = @ID;

                        insert into dbo.CONSTITUENTSOLICITCODE
                        (
                            ID,
                            SOLICITCODEID,
                            CONSTITUENTID,
                            STARTDATE,
                            SEQUENCE,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        )
                        values
                        (
                            newid(),
                            @SOLICITCODEID,
                            @ID,
                            @SOLICITCODESTARTDATE,
                            @SEQUENCE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        )
                    end

                --Change EFT status on financial accounts

                if @SETEFTSTATUS = 1
                    update
                        dbo.CONSTITUENTACCOUNT
                    set
                        EFTSTATUSCODE = @EFTSTATUSCODE,
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    where
                        CONSTITUENTID = @ID;

                --Disable wealth ratings updates

                if @DISABLEWEATHRATINGS = 1
                    --taken from disablewealthupdates record operation

                    if not exists(select 1 from dbo.DISABLEDWEALTHUPDATES where ID=@ID)
                        insert into dbo.DISABLEDWEALTHUPDATES
                        (
                            [ID],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @ID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );

                --Remove recognition credit from spouse to deceased, change marital status of spouse

                if @SPOUSEID is not null
                    begin
                        if @DISABLESPOUSERECOGNITIONCREDIT = 1
                            begin
                                select
                                    @RECOGNITIONDEFAULTID = ID
                                from
                                    dbo.REVENUERECOGNITIONDEFAULT
                                where
                                    SOURCECONSTITUENTID = @SPOUSEID
                                and
                                    RECIPIENTCONSTITUENTID = @ID;

                                if @RECOGNITIONDEFAULTID is not null
                                    exec dbo.USP_REVENUERECOGNITIONDEFAULT_DELETEBYID_WITHCHANGEAGENTID @RECOGNITIONDEFAULTID, @CHANGEAGENTID;
                            end

                        if @SETMARITALSTATUS = 1
                            begin
                                update
                                    dbo.CONSTITUENT
                                set
                                    MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
                                    DATECHANGED = @CURRENTDATE,
                                    CHANGEDBYID = @CHANGEAGENTID
                                where
                                    ID = @SPOUSEID;
                            end
                    end

                --Mark constituent inactive

                if @MARKINACTIVE = 1
                    update
                        dbo.CONSTITUENT
                    set
                        ISINACTIVE = @MARKINACTIVE,
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    where
                        ID = @ID;

                -- Bug 441716 (ADC 12/12/14) - Always update CONSTITUENT table when changing deceased status to show that the constituent's data changed.

                declare @TABLEDATECHANGED datetime;
                select
                    @TABLEDATECHANGED = DATECHANGED
                from
                    dbo.CONSTITUENT
                where
                    ID = @ID;

                if @TABLEDATECHANGED <> @CURRENTDATE
                    begin
                        update
                            dbo.CONSTITUENT
                        set
                            DATECHANGED = @CURRENTDATE,
                            CHANGEDBYID = @CHANGEAGENTID
                        where
                            ID = @ID;
                    end                
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;