USP_DATAFORMTEMPLATE_ADD_WEALTHCAPACITYFORMULAAPPLY

The save procedure used by the add dataform template "Apply Wealth Capacity Formula Add Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@WEALTHCAPACITYFORMULAID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@APPLYTOCODE	tinyint	IN	Apply to
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@RESEARCHGROUPID	uniqueidentifier	IN	Research group
@IDSETREGISTERID	uniqueidentifier	IN	Selection
Definition

Copy

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_WEALTHCAPACITYFORMULAAPPLY (
                        @ID uniqueidentifier output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @WEALTHCAPACITYFORMULAID uniqueidentifier,
                        @APPLYTOCODE tinyint = 0,
                        @CONSTITUENTID uniqueidentifier = null,
                        @RESEARCHGROUPID uniqueidentifier = null,
                        @IDSETREGISTERID uniqueidentifier = null
                    ) with execute as owner as
                        set nocount on;

                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

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

                        begin try

                        if @APPLYTOCODE = 0 and @CONSTITUENTID is not null begin
                            update
                                dbo.WEALTHCAPACITY
                            set
                                WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            from
                                dbo.WEALTHCAPACITY
                            where
                                WEALTHCAPACITY.ID = @CONSTITUENTID and
                                WEALTHCAPACITY.CONFIRMED = 0;

                            insert into dbo.WEALTHCAPACITY (
                                ID,
                                WEALTHCAPACITYFORMULAID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            select
                                C.ID,
                                @WEALTHCAPACITYFORMULAID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            from
                                dbo.CONSTITUENT C
                            left join
                                dbo.WEALTHCAPACITY WC
                            on
                                WC.ID = C.ID
                            where
                                C.ID = @CONSTITUENTID and
                                WC.ID is null;
                        end
                        else if @APPLYTOCODE = 1 and @RESEARCHGROUPID is not null begin
                            update
                                dbo.WEALTHCAPACITY
                            set
                                WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            from
                                dbo.WEALTHCAPACITY
                            left join
                                dbo.RESEARCHGROUPMEMBER RGM
                            on
                                RGM.CONSTITUENTID = WEALTHCAPACITY.ID
                            where
                                RGM.RESEARCHGROUPID = @RESEARCHGROUPID and
                                WEALTHCAPACITY.CONFIRMED = 0;

                            insert into dbo.WEALTHCAPACITY (
                                ID,
                                WEALTHCAPACITYFORMULAID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            select
                                RGM.CONSTITUENTID,
                                @WEALTHCAPACITYFORMULAID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            from
                                dbo.RESEARCHGROUPMEMBER RGM
                            left join
                                dbo.WEALTHCAPACITY WC
                            on
                                WC.ID = RGM.CONSTITUENTID
                            where
                                RGM.RESEARCHGROUPID = @RESEARCHGROUPID and
                                WC.ID is null;
                        end
                        else if @APPLYTOCODE = 2 and @IDSETREGISTERID is not null begin
                            declare @DBOBJECTNAME nvarchar(128);
                            declare @DBOBJECTTYPE smallint;

                            select
                                @DBOBJECTNAME = IDSETREGISTER.DBOBJECTNAME,
                                @DBOBJECTTYPE = IDSETREGISTER.OBJECTTYPE
                            from
                                dbo.IDSETREGISTER
                            where
                                IDSETREGISTER.ID = @IDSETREGISTERID;

                            if not @DBOBJECTNAME is null and @DBOBJECTNAME <> '' begin
                                if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                                else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @ID) + ''')';

                                declare @SQLTOEXEC nvarchar(max);

                                set @SQLTOEXEC = N'set nocount on;

                                update
                                    dbo.WEALTHCAPACITY
                                set
                                    WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                from
                                    dbo.WEALTHCAPACITY
                                inner join
                                    ' + nchar(13);
                                set @SQLTOEXEC = @SQLTOEXEC + @DBOBJECTNAME + N' as SELECTION
                                on
                                    WEALTHCAPACITY.[ID] = SELECTION.[ID]
                                where
                                    WEALTHCAPACITY.CONFIRMED = 0;

                                insert into dbo.WEALTHCAPACITY (
                                    ID,
                                    WEALTHCAPACITYFORMULAID,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED
                                )
                                select
                                    SELECTION.ID,
                                    @WEALTHCAPACITYFORMULAID,
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                from
                                    ' + nchar(13);
                                set @SQLTOEXEC = @SQLTOEXEC + @DBOBJECTNAME + N' as SELECTION
                                left join
                                    dbo.WEALTHCAPACITY WC
                                on
                                    WC.ID = SELECTION.ID
                                where
                                    WC.ID is null;' + nchar(13);

                                exec sp_executesql @SQLTOEXEC,
                                    N'@WEALTHCAPACITYFORMULAID uniqueidentifier,
                                    @CHANGEAGENTID uniqueidentifier,
                                    @CURRENTDATE datetime',
                                    @WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID,
                                    @CHANGEAGENTID=@CHANGEAGENTID,
                                    @CURRENTDATE=@CURRENTDATE;
                            end
                        end
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;