UFN_QUERY_AFFLUENCEINDICATORSBYCONSTITUENT

CREATE function [dbo].[UFN_QUERY_AFFLUENCEINDICATORSBYCONSTITUENT]
(
  @ID uniqueidentifier
)
returns table
as
  return 

with WEALTH_CTE as(
                        select     
                        WPAFFLUENCEINDICATOR.ID,
                        WPAFFLUENCEINDICATOR.WEALTHID,
                        WPAFFLUENCEINDICATOR.CONFIRMED,
                        WPAFFLUENCEINDICATOR.CONFIRMEDBYAPPUSERID,    --Confirmed By

                        WPAFFLUENCEINDICATOR.REJECTED,
                        WPAFFLUENCEINDICATOR.REJECTEDBYAPPUSERID,    --Rejected By

                        WPAFFLUENCEINDICATOR.SOURCE,                --Source

                        WPAFFLUENCEINDICATOR.MC,                    --Match code


                        WPAFFLUENCEINDICATOR.CITY,                    --City

                        WPAFFLUENCEINDICATOR.TITLE,                    --Job title

                        WPAFFLUENCEINDICATOR.MULTIMILLN,            --Millionaire code

                        WPAFFLUENCEINDICATOR.FULLNAME,                --Name

                        WPAFFLUENCEINDICATOR.STATE,                    --State

                        WPAFFLUENCEINDICATOR.WEALTHIND,                --Wealth indicators


                        WPAFFLUENCEINDICATOR.RECURSIVEMATCH,
                            case
                                when WPAFFLUENCEINDICATOR.CONFIRMED = 1 then '5'
                                when WPAFFLUENCEINDICATOR.REJECTED =  1 then '0'
                                else
                                     isnull(coalesce(CONFIDENCERATING.CONFIDENCE,MATCHCODE.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                            end as CONFIDENCE                        --Confidence


                        from
                        dbo.WPAFFLUENCEINDICATOR
                        left outer join
                            dbo.MATCHCODE on WPAFFLUENCEINDICATOR.MC = MATCHCODE.MATCHCODE
                        left outer join 
                            dbo.WEALTHSOURCE on WPAFFLUENCEINDICATOR.SOURCE = WEALTHSOURCE.SOURCE    
                        left outer join
                            dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID 
            where
              WPAFFLUENCEINDICATOR.WEALTHID=@ID
                    )
                select
                    WEALTH_CTE.ID,
                    WEALTH_CTE.CONFIRMED,
                    coalesce(nullif(CONFIRMEDAPPUSER.DISPLAYNAME, ''), CONFIRMEDAPPUSER.USERNAME) CONFIRMEDBYUSER, --Confirmed by

                    WEALTH_CTE.REJECTED,
                    coalesce(nullif(REJECTEDAPPUSER.DISPLAYNAME, ''), REJECTEDAPPUSER.USERNAME) REJECTEDBYUSER, --Rejected by

                    WEALTH_CTE.SOURCE,                                                    --Source

                    WEALTH_CTE.MC,                                                        --Match code

                    MATCHCODE.DESCRIPTION MCDESCRIPTION,        --Match code description


                    WEALTH_CTE.CITY,                    --City

                    WEALTH_CTE.TITLE,                    --Job title

                    WEALTH_CTE.MULTIMILLN,                --Millionaire code

                    WEALTH_CTE.FULLNAME,                --Name

                    WEALTH_CTE.STATE,                    --State

                    WEALTH_CTE.WEALTHIND,                --Wealth indicators


                    WEALTH_CTE.CONFIDENCE,
                        case
                            when WEALTH_CTE.CONFIRMED = 1 and WEALTH_CTE.RECURSIVEMATCH = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_check_16.png'
                            when WEALTH_CTE.CONFIRMED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_check_16.png'
                            when WEALTH_CTE.REJECTED = 1 then  'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
                            else
                                case WEALTH_CTE.RECURSIVEMATCH
                                    when '1' then         
                                        case WEALTH_CTE.CONFIDENCE
                                            when '0' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
                                            when '1' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_1_star_16.png'
                                            when '2' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_2_star_16.png'    
                                            when '3' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_3_star_16.png'
                                            when '4' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_4_star_16.png'         
                                            when '5' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_16.png'
                                        end 
                                    when '0' then
                                        case WEALTH_CTE.CONFIDENCE
                                            when '0' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
                                            when '1' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_1_16.png'
                                            when '2' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_2_16.png'    
                                            when '3' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_3_16.png'
                                            when '4' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_4_16.png'         
                                            when '5' then
                                                'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_16.png'    
                                        end
                                end
                        end as STATUSICON

                    /*#EXTENSION*/ 
                from
                    WEALTH_CTE
                left join dbo.APPUSER CONFIRMEDAPPUSER on CONFIRMEDAPPUSER.ID = WEALTH_CTE.CONFIRMEDBYAPPUSERID
                left join dbo.APPUSER REJECTEDAPPUSER on REJECTEDAPPUSER.ID = WEALTH_CTE.REJECTEDBYAPPUSERID
                left join dbo.MATCHCODE on MATCHCODE.MATCHCODE = WEALTH_CTE.MC