UFN_QUERY_PHILANTHROPICGIFTSBYCONSTITUENT

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

                    with GIFTS_CTE as(
                        select     
                        WPPHILANTHROPICGIFT.ID,
                        WPPHILANTHROPICGIFT.WEALTHID,
                        WPPHILANTHROPICGIFT.CONFIRMED,
                        WPPHILANTHROPICGIFT.CONFIRMEDBYAPPUSERID,
                        WPPHILANTHROPICGIFT.REJECTED,
                        WPPHILANTHROPICGIFT.REJECTEDBYAPPUSERID,
                        WPPHILANTHROPICGIFT.SOURCE,
                        WPPHILANTHROPICGIFT.MC,
                        WPPHILANTHROPICGIFT.ORGANIZATION,
                        WPPHILANTHROPICGIFT.LOCATION,
                        WPPHILANTHROPICGIFT.GIFTYEAR,
                        WPPHILANTHROPICGIFT.TYPE,
                        WPPHILANTHROPICGIFT.CATEGORY,
                        WPPHILANTHROPICGIFT.LO,
                        WPPHILANTHROPICGIFT.HI,
                        WPPHILANTHROPICGIFT.NAME,
                        WPPHILANTHROPICGIFT.ORGANIZATIONWEBADDRESS,
                        WPPHILANTHROPICGIFT.SOURCEMATERIAL,
                        WPPHILANTHROPICGIFT.RECURSIVEMATCH,
                            case
                                when WPPHILANTHROPICGIFT.CONFIRMED = 1 then '5'
                                when WPPHILANTHROPICGIFT.REJECTED =  1 then '0'
                                else
                                     isnull(coalesce(CONFIDENCERATING.CONFIDENCE,MATCHCODE.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                            end as CONFIDENCE

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

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

                    GIFTS_CTE.SOURCE,
                    GIFTS_CTE.MC,
                    MATCHCODE.DESCRIPTION MCDESCRIPTION,
                    GIFTS_CTE.ORGANIZATION,
                    GIFTS_CTE.LOCATION,
                    GIFTS_CTE.GIFTYEAR,
                    GIFTS_CTE.TYPE,
                    GIFTS_CTE.CATEGORY,
                    GIFTS_CTE.LO,
                    GIFTS_CTE.HI,
                    GIFTS_CTE.NAME,
                    GIFTS_CTE.ORGANIZATIONWEBADDRESS,
                    GIFTS_CTE.SOURCEMATERIAL,
          GIFTS_CTE.CONFIDENCE,
                        case
                            when GIFTS_CTE.CONFIRMED = 1 and GIFTS_CTE.RECURSIVEMATCH = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_check_16.png'
                            when GIFTS_CTE.CONFIRMED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_check_16.png'
                            when GIFTS_CTE.REJECTED = 1 then  'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
                            else
                                case GIFTS_CTE.RECURSIVEMATCH
                                    when '1' then         
                                        case GIFTS_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 GIFTS_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
                    GIFTS_CTE
                left join dbo.APPUSER CONFIRMEDAPPUSER on CONFIRMEDAPPUSER.ID = GIFTS_CTE.CONFIRMEDBYAPPUSERID
                left join dbo.APPUSER REJECTEDAPPUSER on REJECTEDAPPUSER.ID = GIFTS_CTE.REJECTEDBYAPPUSERID
                left join dbo.MATCHCODE on MATCHCODE.MATCHCODE = GIFTS_CTE.MC