USP_DATAFORMTEMPLATE_VIEW_WEALTHSUMMARY_ASSETS

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	INOUT
@REALESTATEASSETS	money	INOUT
@BUSINESSASSETS	money	INOUT
@SECURITIESASSETS	money	INOUT
@AFFLUENCEINDICATORSASSETS	money	INOUT
@INCOME	money	INOUT
@OTHERASSETS	money	INOUT
@C_REALESTATEASSETS	money	INOUT
@C_BUSINESSASSETS	money	INOUT
@C_SECURITIESASSETS	money	INOUT
@C_AFFLUENCEINDICATORSASSETS	money	INOUT
@C_INCOME	money	INOUT
@C_OTHERASSETS	money	INOUT
@NREALESTATE	int	INOUT
@NBUSINESS	int	INOUT
@NSECURITIES	int	INOUT
@NAFFLUENCEINDICATORS	int	INOUT
@NINCOME	int	INOUT
@NOTHERASSETS	int	INOUT
@C_NREALESTATE	int	INOUT
@C_NBUSINESS	int	INOUT
@C_NSECURITIES	int	INOUT
@C_NAFFLUENCEINDICATORS	int	INOUT
@C_NINCOME	int	INOUT
@C_NOTHERASSETS	int	INOUT
@NEWREALESTATE	bit	INOUT
@NEWBUSINESSOWNERSHIP	bit	INOUT
@NEWSECURITIES	bit	INOUT
@NEWAFFLUENCEINDICATORS	bit	INOUT
@NEWINCOMECOMPENSATION	bit	INOUT
@NEWOTHERASSETS	bit	INOUT
Definition

Copy

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

          @CONSTITUENTID uniqueidentifier = null output,

                    @REALESTATEASSETS money = null output,
                    @BUSINESSASSETS money = null output,
                    @SECURITIESASSETS money = null output,
                    @AFFLUENCEINDICATORSASSETS money = null output,
                    @INCOME money = null output,
                    @OTHERASSETS money = null output,

                    @C_REALESTATEASSETS money = null output,
                    @C_BUSINESSASSETS money = null output,
                    @C_SECURITIESASSETS money = null output,
                    @C_AFFLUENCEINDICATORSASSETS money = null output,
                    @C_INCOME money = null output,
                    @C_OTHERASSETS money = null output,

                    @NREALESTATE int = null output,
                    @NBUSINESS int = null output,
                    @NSECURITIES int = null output,
                    @NAFFLUENCEINDICATORS int = null output,
                    @NINCOME int = null output,
                    @NOTHERASSETS int = null output,

                    @C_NREALESTATE int = null output,
                    @C_NBUSINESS int = null output,
                    @C_NSECURITIES int = null output,
                    @C_NAFFLUENCEINDICATORS int = null output,
                    @C_NINCOME int = null output,
                    @C_NOTHERASSETS int = null output,

                    @NEWREALESTATE               bit = null output,
                    @NEWBUSINESSOWNERSHIP        bit = null output,
                    @NEWSECURITIES               bit = null output,
                    @NEWAFFLUENCEINDICATORS      bit = null output,
                    @NEWINCOMECOMPENSATION       bit = null output,
                    @NEWOTHERASSETS              bit = null output
                ) as
                    set NOCOUNT on;
                    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;

                    select 
                        @NINCOME = 0,
                        @NAFFLUENCEINDICATORS = 0,
                        @NOTHERASSETS = 0,
                        @NBUSINESS = 0,
                        @NREALESTATE = 0,
                        @NSECURITIES = 0,
                        @C_NINCOME = 0,
                        @C_NAFFLUENCEINDICATORS = 0,
                        @C_NOTHERASSETS = 0,
                        @C_NBUSINESS = 0,
                        @C_NREALESTATE = 0,
                        @C_NSECURITIES = 0

                    select
                        @REALESTATEASSETS = REALESTATEASSETS,
                        @C_REALESTATEASSETS = REALESTATEASSETSCONFIRMED,
                        @NREALESTATE = REALESTATENUMBER,
                        @C_NREALESTATE = REALESTATENUMBERCONFIRMED,
                        @BUSINESSASSETS = BUSINESSOWNERSHIPASSETS,
                        @C_BUSINESSASSETS = BUSINESSOWNERSHIPASSETSCONFIRMED,
                        @NBUSINESS = BUSINESSOWNERSHIPNUMBER,
                        @C_NBUSINESS = BUSINESSOWNERSHIPNUMBERCONFIRMED,
                        @SECURITIESASSETS = SECURITIESASSETS,
                        @C_SECURITIESASSETS = SECURITIESASSETSCONFIRMED,
                        @NSECURITIES = SECURITIESNUMBER,
                        @C_NSECURITIES = SECURITIESNUMBERCONFIRMED,
                        @AFFLUENCEINDICATORSASSETS = AFFLUENCEINDICATORASSETS,
                        @C_AFFLUENCEINDICATORSASSETS = AFFLUENCEINDICATORASSETSCONFIRMED,
                        @NAFFLUENCEINDICATORS = AFFLUENCEINDICATORNUMBER,
                        @C_NAFFLUENCEINDICATORS = AFFLUENCEINDICATORNUMBERCONFIRMED,
                        @INCOME = INCOMECOMPENSATIONASSETS,
                        @C_INCOME = INCOMECOMPENSATIONASSETSCONFIRMED,
                        @NINCOME = INCOMECOMPENSATIONNUMBER,
                        @C_NINCOME = INCOMECOMPENSATIONNUMBERCONFIRMED,
                        @OTHERASSETS = OTHERASSETS,
                        @C_OTHERASSETS = OTHERASSETSCONFIRMED,
                        @NOTHERASSETS = OTHERASSETSNUMBER,
                        @C_NOTHERASSETS = OTHERASSETSNUMBERCONFIRMED
                    from
                        dbo.WEALTH
                    where
                        WEALTH.ID = @ID;


                    if @NREALESTATE = 0
                       set @REALESTATEASSETS = null

                    if @C_NREALESTATE = 0
                       set @C_REALESTATEASSETS = null

                    if @NBUSINESS = 0
                       set @BUSINESSASSETS = null

                    if @C_NBUSINESS = 0
                       set @C_BUSINESSASSETS = null

                    if @NSECURITIES = 0
                       set @SECURITIESASSETS = null

                    if @C_NSECURITIES = 0
                       set @C_SECURITIESASSETS = null

                    if @NAFFLUENCEINDICATORS = 0
                       set @AFFLUENCEINDICATORSASSETS = null

                    if @C_NAFFLUENCEINDICATORS = 0
                       set @C_AFFLUENCEINDICATORSASSETS = null

                    if @NINCOME = 0
                       set @INCOME = null

                    if @C_NINCOME = 0
                       set @C_INCOME = null

                    if @NOTHERASSETS = 0
                       set @OTHERASSETS = null

                    if @C_NOTHERASSETS = 0
                       set @C_OTHERASSETS = null

                    set @NEWREALESTATE               = (select count(ID) from dbo.WPREALESTATE              where WEALTHID=@ID and VIEWED = 0);
          if @NEWREALESTATE = 1
                        update 
                               dbo.WPREALESTATE
                        set
                               VIEWED = 1,
                               CHANGEDBYID = @CHANGEAGENTID,
                               DATECHANGED = @CURRENTDATE
                        where
                               VIEWED = 0 and
                               WEALTHID = @ID;
                    set @NEWBUSINESSOWNERSHIP        = (select count(ID) from dbo.WPBUSINESSOWNERSHIP       where WEALTHID=@ID and VIEWED = 0); 
          if @NEWBUSINESSOWNERSHIP = 1
                        update 
                               dbo.WPBUSINESSOWNERSHIP
                        set
                               VIEWED = 1,
                               CHANGEDBYID = @CHANGEAGENTID,
                               DATECHANGED = @CURRENTDATE
                        where
                               VIEWED = 0 and
                               WEALTHID = @ID;
          set @NEWSECURITIES               = (select count(ID) from dbo.WPSECURITIES                where WEALTHID=@ID and VIEWED = 0); 
          if @NEWSECURITIES = 1
                        update 
                               dbo.WPSECURITIES
                        set
                               VIEWED = 1,
                               CHANGEDBYID = @CHANGEAGENTID,
                               DATECHANGED = @CURRENTDATE
                        where
                               VIEWED = 0 and
                               WEALTHID = @ID;
                    set @NEWAFFLUENCEINDICATORS      = (select count(ID) from dbo.WPAFFLUENCEINDICATOR      where WEALTHID=@ID and VIEWED = 0); 
          if @NEWAFFLUENCEINDICATORS = 1
                        update 
                               dbo.WPAFFLUENCEINDICATOR
                        set
                               VIEWED = 1,
                               CHANGEDBYID = @CHANGEAGENTID,
                               DATECHANGED = @CURRENTDATE
                        where
                               VIEWED = 0 and
                               WEALTHID = @ID;
                    set @NEWINCOMECOMPENSATION       = (select count(ID) from dbo.WPINCOMECOMPENSATION      where WEALTHID=@ID and VIEWED = 0);
          if @NEWINCOMECOMPENSATION = 1
                        update 
                               dbo.WPINCOMECOMPENSATION
                        set
                               VIEWED = 1,
                               CHANGEDBYID = @CHANGEAGENTID,
                               DATECHANGED = @CURRENTDATE
                        where
                               VIEWED = 0 and
                               WEALTHID = @ID;
                    set @NEWOTHERASSETS              = (select count(ID) from dbo.WPOTHERASSET              where WEALTHID=@ID and VIEWED = 0); 
          if @NEWOTHERASSETS = 1
                        update 
                               dbo.WPOTHERASSET
                        set
                               VIEWED = 1,
                               CHANGEDBYID = @CHANGEAGENTID,
                               DATECHANGED = @CURRENTDATE
                        where
                               VIEWED = 0 and
                               WEALTHID = @ID;
                    return 0;