UFN_QUERY_SECURITIESBYCONSTITUENT

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

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

                        WPSECURITIES.REJECTED,
                        WPSECURITIES.REJECTEDBYAPPUSERID,    --Rejected By

                        WPSECURITIES.SOURCE,                --Source

                        WPSECURITIES.MC,                    --Match code


                        WPSECURITIES.PRICEDIRECT,            --Direct price

                        WPSECURITIES.AMOUNTOWNEDDIRECT,        --Direct quantity held

                        WPSECURITIES.REPORTDATEDIRECT,        --Direct transaction date

                        WPSECURITIES.VALUEDIRECT,            --Direct value

                        WPSECURITIES.PRICEINDIRECT,            --Indirect price

                        WPSECURITIES.AMOUNTOWNEDINDIRECT,    --Indirect quantity held

                        WPSECURITIES.REPORTDATEINDIRECT,    --Indirect transaction date

                        WPSECURITIES.VALUEINDIRECT,            --Indirect value

                        WPSECURITIES.ISSUERNAME,            --Issuer name

                        WPSECURITIES.FULLNAME,                --Name

                        WPSECURITIES.RELATIONSHIP,            --Relationship

                        WPSECURITIES.TICKER,                --Symbol

                        (WPSECURITIES.VALUEDIRECT + WPSECURITIES.VALUEINDIRECT) as VALUE,    --Total value


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


                        from
                        dbo.WPSECURITIES
                        left outer join
                            dbo.MATCHCODE on WPSECURITIES.MC = MATCHCODE.MATCHCODE
                        left outer join 
                            dbo.WEALTHSOURCE on WPSECURITIES.SOURCE = WEALTHSOURCE.SOURCE    
                        left outer join
                            dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID 
            where
              WPSECURITIES.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.PRICEDIRECT,                --Direct price

                    WEALTH_CTE.AMOUNTOWNEDDIRECT,        --Direct quantity held

                    WEALTH_CTE.REPORTDATEDIRECT,        --Direct transaction date

                    WEALTH_CTE.VALUEDIRECT,                --Direct value

                    WEALTH_CTE.PRICEINDIRECT,            --Indirect price

                    WEALTH_CTE.AMOUNTOWNEDINDIRECT,        --Indirect quantity held

                    WEALTH_CTE.REPORTDATEINDIRECT,        --Indirect transaction date

                    WEALTH_CTE.VALUEINDIRECT,            --Indirect value

                    WEALTH_CTE.ISSUERNAME,                --Issuer name

                    WEALTH_CTE.FULLNAME,                --Name

                    WEALTH_CTE.RELATIONSHIP,            --Relationship

                    WEALTH_CTE.TICKER,                    --Symbol

                    WEALTH_CTE.VALUE,                    --Total value


                    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