UFN_QUERY_INCOMECOMPENSATIONSBYCONSTITUENT

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

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

                        WPINCOMECOMPENSATION.REJECTED,
                        WPINCOMECOMPENSATION.REJECTEDBYAPPUSERID,    --Rejected By

                        WPINCOMECOMPENSATION.SOURCE,                --Source

                        WPINCOMECOMPENSATION.MC,                    --Match code


                        nullif(WPINCOMECOMPENSATION.AGE, 0) AGE,        --Age

                        WPINCOMECOMPENSATION.COMPANY,                --Company

                        WPINCOMECOMPENSATION.LATESTCOMPENSATIONYEAR,--Compensation year

                        WPINCOMECOMPENSATION.LATESTFISCALYEARSALARY,--Salary

                        WPINCOMECOMPENSATION.LATESTFISCALYEARBONUS,    --Bonus

                        (WPINCOMECOMPENSATION.LATESTFISCALYEARBONUS + WPINCOMECOMPENSATION.LATESTFISCALYEARSALARY) as INCOME,    --Income

                        WPINCOMECOMPENSATION.LONGTITLE,                --Job title

                        WPINCOMECOMPENSATION.BB_FULLNAME,            --Name


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

                        WPINCOMECOMPENSATION.HISTORIC,  --Record state

            case WPINCOMECOMPENSATION.HISTORICCODE when 1 then 1 else 0 end as ISHISTORIC

                        from
                        dbo.WPINCOMECOMPENSATION
                        left outer join
                            dbo.MATCHCODE on WPINCOMECOMPENSATION.MC = MATCHCODE.MATCHCODE
                        left outer join 
                            dbo.WEALTHSOURCE on WPINCOMECOMPENSATION.SOURCE = WEALTHSOURCE.SOURCE    
                        left outer join
                            dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID 
            where
              WPINCOMECOMPENSATION.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.AGE,                        --Age

                    WEALTH_CTE.COMPANY,                    --Company

                    WEALTH_CTE.LATESTCOMPENSATIONYEAR,    --Compensation year

                    WEALTH_CTE.LATESTFISCALYEARSALARY,    --Salary

                    WEALTH_CTE.LATESTFISCALYEARBONUS,    --Bonus

                    WEALTH_CTE.INCOME,                    --Income

                    WEALTH_CTE.LONGTITLE,                --Job title

                    WEALTH_CTE.BB_FULLNAME,                --Name


                    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,
            WEALTH_CTE.HISTORIC,
            WEALTH_CTE.ISHISTORIC

                    /*#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