USP_DATAFORMTEMPLATE_VIEW_GROUPWEALTHCAPACITY

The load procedure used by the view dataform template "Group Wealth Capacity View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@ESTIMATEDWEALTH	uniqueidentifier	INOUT	Estimated wealth
@MAJORGIVINGCAPACITY	uniqueidentifier	INOUT	Major giving capacity
@MAJORGIVINGCAPACITYVALUE	money	INOUT	Major giving capacity value
@OVERALLRATINGCODEID	uniqueidentifier	INOUT	Overall rating
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ESTIMATEDWEALTHVALUE	money	INOUT	Estimated wealth value
@OVERALLRATING	nvarchar(100)	INOUT	Overall rating
@ESTIMATEDWEALTH_MIN	money	INOUT	Estimated wealth min
@ESTIMATEDWEALTH_MAX	money	INOUT	Estimated wealth max
@MAJORGIVINGCAPACITY_MIN	money	INOUT	Major giving capacity min
@MAJORGIVINGCAPACITY_MAX	money	INOUT	Major giving capacity max

Definition

Copy



                    CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_GROUPWEALTHCAPACITY] (
                        @ID uniqueidentifier,
                        @ESTIMATEDWEALTH uniqueidentifier = null output,
                        @MAJORGIVINGCAPACITY uniqueidentifier = null output,
                        @MAJORGIVINGCAPACITYVALUE money = null output,
                        @OVERALLRATINGCODEID uniqueidentifier = null output,
                        @DATALOADED bit = 0 output,
                        @ESTIMATEDWEALTHVALUE money = null output,
                        @OVERALLRATING nvarchar(100) = null output,

                        @ESTIMATEDWEALTH_MIN money = null output,
                        @ESTIMATEDWEALTH_MAX money = null output,
                        @MAJORGIVINGCAPACITY_MIN money = null output,
                        @MAJORGIVINGCAPACITY_MAX money = null output
                    ) as
                        set nocount on;
                        set @DATALOADED = 0;

                        select
                            @DATALOADED = 1,
                            @ESTIMATEDWEALTH = WC.ESTIMATEDWEALTHID,
                            @ESTIMATEDWEALTHVALUE = WC.ESTIMATEDWEALTHVALUE,
                            @MAJORGIVINGCAPACITY = WC.MAJORGIVINGCAPACITYID,
                            @MAJORGIVINGCAPACITYVALUE = WC.MAJORGIVINGCAPACITYVALUE,
                            @OVERALLRATINGCODEID = WC.OVERALLRATINGCODEID,
                            @OVERALLRATING = ORC.DESCRIPTION
                        from
                            dbo.WEALTHCAPACITY WC
                        left join dbo.OVERALLRATINGCODE ORC on
                            WC.OVERALLRATINGCODEID = ORC.ID
                        where
                            WC.ID = @ID;


                        if @DATALOADED = 0
                            select @DATALOADED = 1 from dbo.CONSTITUENT where ID = @ID;

                        -------------------------------------------------------------------------------------------------------------

                        /*
                        Get estimated wealth translation fields
                        */
                        -------------------------------------------------------------------------------------------------------------


                        if @ESTIMATEDWEALTH is not null
                        begin

                            SELECT @ESTIMATEDWEALTH_MIN = MINIMUMAMOUNT from dbo.ESTIMATEDWEALTH where ID=@ESTIMATEDWEALTH;

                            --max is calculated as ([next higher up min] -1  )

                            SELECT top 1 @ESTIMATEDWEALTH_MAX = (MINIMUMAMOUNT - 1) from dbo.ESTIMATEDWEALTH
                            WHERE MINIMUMAMOUNT > @ESTIMATEDWEALTH_MIN order by MINIMUMAMOUNT; 

                        end
                        -------------------------------------------------------------------------------------------------------------



                        -------------------------------------------------------------------------------------------------------------

                        /*
                        Get major giving translation fields
                        */
                        if @MAJORGIVINGCAPACITY is not null
                        begin
                        SELECT @MAJORGIVINGCAPACITY_MIN = MINIMUMAMOUNT from dbo.[MAJORGIVINGCAPACITY] where ID=@MAJORGIVINGCAPACITY;


                        SELECT top 1 @MAJORGIVINGCAPACITY_MAX = MINIMUMAMOUNT - 1 from dbo.[MAJORGIVINGCAPACITY]
                        WHERE MINIMUMAMOUNT > @MAJORGIVINGCAPACITY_MIN order by MINIMUMAMOUNT ;
                        ;
                        end
                        -------------------------------------------------------------------------------------------------------------


                        return 0;