USP_DATALIST_WEALTHPOINTDATAREFRESHNOTIFICATION_CONSTITUENT

                CREATE procedure dbo.USP_DATALIST_WEALTHPOINTDATAREFRESHNOTIFICATION_CONSTITUENT (
                    @WEALTHID uniqueidentifier = null,
                    @SOURCE nvarchar(150) = 'Refinitiv (Securities)',
                    @DATERANGECODE smallint = 1
                ) as
                    set nocount on;

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

                    declare @STARTDATE datetime;
                    select @STARTDATE = 
                        case @DATERANGECODE                        
                            when '1' then --Today 

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,0,@CURRENTDATE))
                            when '2' then --Last 7 days

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-6,@CURRENTDATE))
                            when '3' then --Last 30 days

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE))
                            when '4' then --Last 90 days

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE))
                            when '5' then --Last 6 months

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-5,@CURRENTDATE))
                            when '6' then --Last 12 months

                                dbo.UFN_DATE_GETEARLIESTTIME(dateadd(month,-11,@CURRENTDATE))
                        end

                    declare @ENDDATE datetime;
                    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);

                    with
                    ALLREFRESHCHANGES_CTE as (
                        select
                            dateadd(dd,0,datediff(dd,0,WPSECURITIESREFRESHCHANGES.[DATE])) DATE,
                            WPSECURITIESREFRESHCHANGES.[SOURCE] SOURCE,
                            WPSECURITIESREFRESHCHANGES.[KEYNAME] WEALTHDETAILNAME,
                            WPSECURITIESREFRESHCHANGES.[FIELDTRANSLATION] FIELD,
                            WPSECURITIESREFRESHCHANGES.[OLDVALUE] OLDVALUE,
                            WPSECURITIESREFRESHCHANGES.[NEWVALUE] NEWVALUE
                        from
                            dbo.UFN_WPSECURITIES_DATAREFRESHCHANGEHISTORY(@WEALTHID, @STARTDATE, @ENDDATE) WPSECURITIESREFRESHCHANGES
                        where
                            @SOURCE is null or WPSECURITIESREFRESHCHANGES.[SOURCE] = @SOURCE
                        union all
                        select
                            dateadd(dd,0,datediff(dd,0,WPBUSINESSOWNERSHIPREFRESHCHANGES.[DATE])),
                            WPBUSINESSOWNERSHIPREFRESHCHANGES.[SOURCE],    
                            WPBUSINESSOWNERSHIPREFRESHCHANGES.[KEYNAME],
                            WPBUSINESSOWNERSHIPREFRESHCHANGES.[FIELDTRANSLATION],
                            WPBUSINESSOWNERSHIPREFRESHCHANGES.[OLDVALUE],
                            WPBUSINESSOWNERSHIPREFRESHCHANGES.[NEWVALUE]
                        from
                            dbo.UFN_WPBUSINESSOWNERSHIP_DATAREFRESHCHANGEHISTORY(@WEALTHID, @STARTDATE, @ENDDATE) WPBUSINESSOWNERSHIPREFRESHCHANGES
                        where
                            @SOURCE is null or WPBUSINESSOWNERSHIPREFRESHCHANGES.[SOURCE] = @SOURCE
                        union all
                        select
                            dateadd(dd,0,datediff(dd,0,WPBIOGRAPHICALREFRESHCHANGES.[DATE])),
                            WPBIOGRAPHICALREFRESHCHANGES.[SOURCE],    
                            WPBIOGRAPHICALREFRESHCHANGES.[KEYNAME],
                            WPBIOGRAPHICALREFRESHCHANGES.[FIELDTRANSLATION],
                            WPBIOGRAPHICALREFRESHCHANGES.[OLDVALUE],
                            WPBIOGRAPHICALREFRESHCHANGES.[NEWVALUE]
                        from
                            dbo.UFN_WPBIOGRAPHICAL_DATAREFRESHCHANGEHISTORY(@WEALTHID, @STARTDATE, @ENDDATE) WPBIOGRAPHICALREFRESHCHANGES
                        where
                            @SOURCE is null or WPBIOGRAPHICALREFRESHCHANGES.[SOURCE] = @SOURCE
                        union all
                        select
                            dateadd(dd,0,datediff(dd,0,WPNONPROFITAFFILIATIONREFRESHCHANGES.[DATE])),
                            case WPNONPROFITAFFILIATIONREFRESHCHANGES.[SOURCE] 
                                when 'GuideStar' then
                                    'GuideStar (Nonprofit Affiliations)'
                                else
                                    WPNONPROFITAFFILIATIONREFRESHCHANGES.[SOURCE] 
                            end,
                            WPNONPROFITAFFILIATIONREFRESHCHANGES.[KEYNAME],
                            WPNONPROFITAFFILIATIONREFRESHCHANGES.[FIELDTRANSLATION],
                            WPNONPROFITAFFILIATIONREFRESHCHANGES.[OLDVALUE],
                            WPNONPROFITAFFILIATIONREFRESHCHANGES.[NEWVALUE]
                        from
                            dbo.UFN_WPNONPROFITAFFILIATION_DATAREFRESHCHANGEHISTORY(@WEALTHID, @STARTDATE, @ENDDATE) WPNONPROFITAFFILIATIONREFRESHCHANGES
                        where
                            @SOURCE is null or
                            case WPNONPROFITAFFILIATIONREFRESHCHANGES.[SOURCE] 
                                when 'GuideStar' then
                                    'GuideStar (Nonprofit Affiliations)'
                                else
                                    WPNONPROFITAFFILIATIONREFRESHCHANGES.[SOURCE] 
                            end = @SOURCE
                        union all
                        select
                            dateadd(dd,0,datediff(dd,0,WPPRIVATEFOUNDATIONREFRESHCHANGES.[DATE])),
                            case WPPRIVATEFOUNDATIONREFRESHCHANGES.[SOURCE] 
                                when 'GuideStar' then
                                    'GuideStar (Foundation Affiliations)'
                                else
                                    WPPRIVATEFOUNDATIONREFRESHCHANGES.[SOURCE] 
                            end,    
                            WPPRIVATEFOUNDATIONREFRESHCHANGES.[KEYNAME],
                            WPPRIVATEFOUNDATIONREFRESHCHANGES.[FIELDTRANSLATION],
                            WPPRIVATEFOUNDATIONREFRESHCHANGES.[OLDVALUE],
                            WPPRIVATEFOUNDATIONREFRESHCHANGES.[NEWVALUE]
                        from
                            dbo.UFN_WPPRIVATEFOUNDATION_DATAREFRESHCHANGEHISTORY(@WEALTHID, @STARTDATE, @ENDDATE) WPPRIVATEFOUNDATIONREFRESHCHANGES
                        where
                            @SOURCE is null or
                            case WPPRIVATEFOUNDATIONREFRESHCHANGES.[SOURCE] 
                                when 'GuideStar' then
                                    'GuideStar (Foundation Affiliations)'
                                else
                                    WPPRIVATEFOUNDATIONREFRESHCHANGES.[SOURCE] 
                            end = @SOURCE
                    )
                    select distinct
                        cast(ALLREFRESHCHANGES_CTE.[DATE] as nvarchar(100)) + cast(ALLREFRESHCHANGES_CTE.[SOURCE] as nvarchar(50)) ID,
                        null [PARENTID],
                        ALLREFRESHCHANGES_CTE.[DATE],
                        ALLREFRESHCHANGES_CTE.[SOURCE] [SOURCE],
                        null [WEALTHDETAILNAME],
                        null [FIELD],
                        null [OLDVALUE],
                        null [NEWVALUE]
                    from
                        ALLREFRESHCHANGES_CTE
                    union all
                    select
                        null,
                        cast(ALLREFRESHCHANGES_CTE.[DATE] as nvarchar(36)) + cast(ALLREFRESHCHANGES_CTE.[SOURCE] as nvarchar(50)),
                        null,
                        null,
                        ALLREFRESHCHANGES_CTE.[WEALTHDETAILNAME],
                        ALLREFRESHCHANGES_CTE.[FIELD],
                        ALLREFRESHCHANGES_CTE.[OLDVALUE],
                        case when ALLREFRESHCHANGES_CTE.[NEWVALUE] in('0','00000000','0.00','0.0000','') then ALLREFRESHCHANGES_CTE.[NEWVALUE]+ ' (Previous value retained)'
                             else ALLREFRESHCHANGES_CTE.[NEWVALUE] 
                        end
                    from
                        ALLREFRESHCHANGES_CTE
                    order by
                        [DATE],[SOURCE],[WEALTHDETAILNAME],[FIELD];