USP_DATAFORMTEMPLATE_VIEW_WEALTHCAPACITY

The load procedure used by the view dataform template "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
@WEALTHCAPACITYFORMULA	nvarchar(100)	INOUT	Wealth capacity formula
@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_WEALTHCAPACITY

    @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,
    @WEALTHCAPACITYFORMULA nvarchar(100) = 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,
    @WEALTHCAPACITYFORMULA = WCF.NAME,
    @OVERALLRATING = ORC.DESCRIPTION
    from

    dbo.WEALTHCAPACITY WC    

    left join dbo.OVERALLRATINGCODE ORC on
    WC.OVERALLRATINGCODEID = ORC.ID
    left join    dbo.WEALTHCAPACITYFORMULA WCF    on
    WCF.ID = WC.WEALTHCAPACITYFORMULAID
    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;