USP_DATAFORMTEMPLATE_VIEW_WEALTHCAPACITY2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_WEALTHCAPACITY2

    @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,
    @MAJORGIVINGCAPACITYBASISVALUE nvarchar(250) = 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,
    @MAJORGIVINGCAPACITYBASISVALUE = WC.MAJORGIVINGCAPACITYBASISVALUE

    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;