USP_RECORDOPERATION_WEALTHCAPACITYFORMULARECALCULATERESEARCHGROUP

Executes the "Wealth Capacity Formula: Recalculate Research Group" record operation.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	nchar	IN	Input parameter indicating the ID of the record being updated.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the update.
Definition

Copy


                    CREATE procedure dbo.USP_RECORDOPERATION_WEALTHCAPACITYFORMULARECALCULATERESEARCHGROUP (
                        @ID nchar(72),
                        @CHANGEAGENTID uniqueidentifier
                    ) as
                        set nocount on;

                        declare @APPLYTOID uniqueidentifier;
                        declare @WEALTHCAPACITYFORMULAID uniqueidentifier;
                        declare @RESEARCHGROUPID uniqueidentifier;
                        declare @VALIDIDS bit;

                        begin try
                            set @WEALTHCAPACITYFORMULAID = convert(uniqueidentifier,substring(@ID,0,37));
                            set @RESEARCHGROUPID = convert(uniqueidentifier,substring(@ID,37,37));
                            set @VALIDIDS = 1;
                        end try
                        begin catch
                            set @VALIDIDS = 0;
                        end catch

                        if @VALIDIDS = 1 begin
                            declare APPLYTOCURSOR cursor local fast_forward for
                            select
                                WC.ID
                            from
                                dbo.RESEARCHGROUPMEMBER RGM
                            left join
                                dbo.WEALTHCAPACITY WC
                            on
                                WC.ID = RGM.CONSTITUENTID
                            where
                                WC.WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID and
                                WC.CONFIRMED = 0 and
                                RGM.RESEARCHGROUPID = @RESEARCHGROUPID;

                            open APPLYTOCURSOR;
                            fetch next from APPLYTOCURSOR into @APPLYTOID;
                            while @@fetch_status = 0 begin
                                exec dbo.USP_WEALTHCAPACITY_UPDATE @APPLYTOID, @CHANGEAGENTID;
                                fetch next from APPLYTOCURSOR into @APPLYTOID;
                            end
                            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                            close APPLYTOCURSOR;
                            deallocate APPLYTOCURSOR;
                        end

                        return 0;