USP_DATAFORMTEMPLATE_WEALTHSUMMARY_AFFILIATIONS

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@NONPROFITAFFILIATIONS	int	INOUT
@C_NONPROFITAFFILIATIONS	int	INOUT
@FOUNDATIONAFFILIATIONS	int	INOUT
@C_FOUNDATIONAFFILIATIONS	int	INOUT
@NETWORKCONNECTIONS	int	INOUT
@C_NETWORKCONNECTIONS	int	INOUT
@CONSTITUENCIES	int	INOUT
@C_CONSTITUENCIES	int	INOUT
@ENTERPRISE_FLAG	bit	INOUT
@NEWNONPROFITAFFILIATIONS	bit	INOUT
@NEWPRIVATEFOUNDATIONS	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_WEALTHSUMMARY_AFFILIATIONS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,

  @CONSTITUENTID uniqueidentifier = null output,

  @NONPROFITAFFILIATIONS integer = null output,
    @C_NONPROFITAFFILIATIONS integer = null output,

  @FOUNDATIONAFFILIATIONS integer = null output,
    @C_FOUNDATIONAFFILIATIONS integer = null output,

  @NETWORKCONNECTIONS integer = null output,
    @C_NETWORKCONNECTIONS integer = null output,

  @CONSTITUENCIES integer = null output,
    @C_CONSTITUENCIES integer = null output,

    @ENTERPRISE_FLAG bit = null output,

    @NEWNONPROFITAFFILIATIONS    bit = null output,
    @NEWPRIVATEFOUNDATIONS       bit = null output

)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

  declare @CHANGEAGENTID uniqueidentifier;
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

    select
        @DATALOADED = 1,
    @CONSTITUENTID = ID
    from
        dbo.CONSTITUENT
    where
        CONSTITUENT.ID = @ID;

    set @NETWORKCONNECTIONS = (SELECT COUNT(ID) FROM dbo.UFN_WEALTHPOINT_EXTENDEDRELATIONSHIP(@CONSTITUENTID, null, null))
    set @C_NETWORKCONNECTIONS = (SELECT COUNT(ID) FROM dbo.UFN_WEALTHPOINT_EXTENDEDRELATIONSHIP(@CONSTITUENTID, null, null) WHERE LINKEDCONSTITUENTID IS NOT NULL)
    set @CONSTITUENCIES = (SELECT COUNT(*) FROM dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(@CONSTITUENTID))

    select
        @NONPROFITAFFILIATIONS = NONPROFITAFFILIATIONNUMBER,
        @C_NONPROFITAFFILIATIONS = NONPROFITAFFILIATIONNUMBERCONFIRMED,
        @FOUNDATIONAFFILIATIONS = PRIVATEFOUNDATIONNUMBER,
        @C_FOUNDATIONAFFILIATIONS = PRIVATEFOUNDATIONNUMBERCONFIRMED   
from 
        dbo.WEALTH
    where
        WEALTH.ID = @ID;

    set @C_CONSTITUENCIES = @CONSTITUENCIES;

    set @ENTERPRISE_FLAG = 0;
    Select @ENTERPRISE_FLAG = 1 from INSTALLEDPRODUCTLIST where ID='3117D2C8-7F46-42F2-ABEB-B654F2F63046'

    set @NEWNONPROFITAFFILIATIONS    = (select count(ID) from dbo.WPNONPROFITAFFILIATION    where WEALTHID=@ID and VIEWED = 0); 
  if @NEWNONPROFITAFFILIATIONS = 1
      update 
                  dbo.WPNONPROFITAFFILIATION
      set
                  VIEWED = 1,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
      where
                  VIEWED = 0 and
                  WEALTHID = @ID;
    set @NEWPRIVATEFOUNDATIONS       = (select count(ID) from dbo.WPPRIVATEFOUNDATION       where WEALTHID=@ID and VIEWED = 0); 
  if @NEWPRIVATEFOUNDATIONS = 1
      update 
                  dbo.WPPRIVATEFOUNDATION
      set
                  VIEWED = 1,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
      where
                  VIEWED = 0 and
                  WEALTHID = @ID;

    return 0;