UFN_QUERY_REALESTATESBYCONSTITUENT

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


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

                        WPREALESTATE.REJECTED,
                        WPREALESTATE.REJECTEDBYAPPUSERID,    --Rejected By

                        WPREALESTATE.SOURCE,                      --Source

                        WPREALESTATE.MC,                  --Match code


                        WPREALESTATE.ASSESSORSPARCELNUMBER, --APN

                        WPREALESTATE.ASSESSEDTOTALVALUE,    --Assessed value

                        WPREALESTATE.ASSESSMENTYEAR,        --Assessed value year

                        WPREALESTATE.ASSESSEENAME,            --Assessee #1 name

                        WPREALESTATE.ASSESSEENAME2,            --Assessee #2 name

                        WPREALESTATE.ASSESSEEMAILINGADDRESS,--Assessee address

                        WPREALESTATE.BUYER,                    --Buyer #1 name

                        WPREALESTATE.BUYER2,                --Buyer #2 name

                        WPREALESTATE.BUYERMAILINGADDRESS,    --Buyer address

                        WPREALESTATE.BB_COUNTY,                --county

                        WPREALESTATE.LOANAMOUNT,            --Loan amount

                        WPREALESTATE.ORIGINALCONTRACTDATE,    --Loan date

                        WPREALESTATE.TOTALMARKETVALUE,        --Market value

                        WPREALESTATE.MARKETVALUEYEAR,        --Market value year

                        WPREALESTATE.OWNER,                    --Owner

                        WPREALESTATE.MAILINGADDRESS,        --Owner mailing address

                        dbo.UFN_BUILDFULLADDRESS_WPREALESTATE(WPREALESTATE.PROPADDRESS, WPREALESTATE.PROPCITY, WPREALESTATE.PROPSTATE, WPREALESTATE.PROPZIP) as PROPFULLADDRESS, --Property full address

                        WPREALESTATE.PROPADDRESS,            --Property address line 1

                        WPREALESTATE.PROPCITY,                --Property city

                        WPREALESTATE.PROPSTATE,                --Property state

                        WPREALESTATE.PROPZIP,                --Property ZIP

                        WPREALESTATE.ESTIMATEDVALUE,        --Property estimate

                        case when WPREALESTATE.CONFIDENCESCORE = -1 then null else WPREALESTATE.CONFIDENCESCORE end as CONFIDENCESCORE,        --Property estimate confidence

                        WPREALESTATE.ESTIMATEDVALUEDATE,    --Property estimate date

                        WPREALESTATE.PROPUSECODE,            --Property use

                        WPREALESTATE.SALEDATE,                --Sale date

                        WPREALESTATE.SALEPRICE,                --Sales price

                        WPREALESTATE.PROPERTYVALUATION,        --Property valuation


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

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

                        from
                        dbo.WPREALESTATE
                        left outer join
                            dbo.MATCHCODE on WPREALESTATE.MC = MATCHCODE.MATCHCODE
                        left outer join 
                            dbo.WEALTHSOURCE on WPREALESTATE.SOURCE = WEALTHSOURCE.SOURCE    
                        left outer join
                            dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID 
            where
              WPREALESTATE.WEALTHID=@ID
                    )
                select
                    WEALTH_CTE.ID,
                    WEALTH_CTE.CONFIRMED,
                    coalesce(nullif(CONFIRMEDAPPUSER.DISPLAYNAME, ''), CONFIRMEDAPPUSER.USERNAME) CONFIRMEDBYUSER,
                    WEALTH_CTE.REJECTED,
                    coalesce(nullif(REJECTEDAPPUSER.DISPLAYNAME, ''), REJECTEDAPPUSER.USERNAME) REJECTEDBYUSER,
                    WEALTH_CTE.SOURCE,
                    WEALTH_CTE.MC,
                    MATCHCODE.DESCRIPTION MCDESCRIPTION,

                  WEALTH_CTE.ASSESSORSPARCELNUMBER,    --APN

                    WEALTH_CTE.ASSESSEDTOTALVALUE,        --Assessed value

                    WEALTH_CTE.ASSESSMENTYEAR,            --Assessed value year

                    WEALTH_CTE.ASSESSEENAME,            --Assessee #1 name

                    WEALTH_CTE.ASSESSEENAME2,            --Assessee #2 name

                    WEALTH_CTE.ASSESSEEMAILINGADDRESS,    --Assessee address

                    WEALTH_CTE.BUYER,                    --Buyer #1 name

                    WEALTH_CTE.BUYER2,                    --Buyer #2 name

                    WEALTH_CTE.BUYERMAILINGADDRESS,        --Buyer address

                    WEALTH_CTE.BB_COUNTY,                --county

                    WEALTH_CTE.LOANAMOUNT,                --Loan amount

                    WEALTH_CTE.ORIGINALCONTRACTDATE,    --Loan date

                    WEALTH_CTE.TOTALMARKETVALUE,        --Market value

                    WEALTH_CTE.MARKETVALUEYEAR,            --Market value year

                    WEALTH_CTE.OWNER,                    --Owner

                    WEALTH_CTE.MAILINGADDRESS,            --Owner mailing address

                    WEALTH_CTE.PROPFULLADDRESS,            --Property full address

                    WEALTH_CTE.PROPADDRESS,                --Property address line 1

                    WEALTH_CTE.PROPCITY,                --Property city

                    WEALTH_CTE.PROPSTATE,                --Property state

                    WEALTH_CTE.PROPZIP,                    --Property ZIP

                    WEALTH_CTE.ESTIMATEDVALUE,            --Property estimate

                    WEALTH_CTE.CONFIDENCESCORE,            --Property estimate confidence

                    WEALTH_CTE.ESTIMATEDVALUEDATE,        --Property estimate date

                    WEALTH_CTE.PROPUSECODE,                --Property use

                    WEALTH_CTE.SALEDATE,                --Sale date

                    WEALTH_CTE.SALEPRICE,                --Sales price

                    WEALTH_CTE.PROPERTYVALUATION,        --Property valuation


                    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