USP_DATALIST_WPBUSINESSOWNERSHIP_ORG

Displays a list of WealthPoint Business Ownership data for an organization.
Parameters

Parameter	Parameter Type	Mode	Description
@WEALTHID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@SOURCE	nvarchar(100)	IN	Source
@CONFIDENCE	int	IN	Confidence
Definition

Copy


CREATE procedure dbo.USP_DATALIST_WPBUSINESSOWNERSHIP_ORG
(
    @WEALTHID       uniqueidentifier = null,
    @SOURCE         nvarchar(100) = null,
    @CONFIDENCE        int = 0
)
as         
begin
    with RECORDCONFIDENCE as(
        select     
            WP.ID,
            case
                when WP.CONFIRMED = 1 then '5'
                when WP.REJECTED =  1 then '0'
                else
                    isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
            end as CONFIDENCE

        from
        dbo.WPBUSINESSOWNERSHIP WP
        left outer join
            dbo.MATCHCODE MC on WP.MC = MC.MATCHCODE
        left outer join 
            dbo.WEALTHSOURCE WS on WP.SOURCE = WS.SOURCE    
        left outer join
            dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID 
    )

    select

        --Begin Generic Fields 

        WP.ID,    
        case
            when WP.CONFIRMED = 1 and WP.RECURSIVEMATCH = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_check_16.png'
            when WP.CONFIRMED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_check_16.png'
            when WP.REJECTED = 1 then  'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
            else
                case WP.RECURSIVEMATCH
                    when '1' then         
                        case RC.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 RC.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,
        RC.CONFIDENCE,                                                          
        WP.DATECONFIRMED as DATECONFIRMED,
        coalesce(C.NAME,U.USERNAME) as CONFIRMEDBY,              
        WP.SOURCE,
        --End Generic Fields  


        WP.COMPANY,
        WP.LINE1,
        WP.CITY,
        WP.STATE,
        WP.OWNERSHIPVALUE,
        WP.CONFIRMED,
        WP.REJECTED
    from
        dbo.WPBUSINESSOWNERSHIP WP

    left outer join
        dbo.APPUSER U on U.ID=WP.CONFIRMEDBYAPPUSERID
    left outer join
        dbo.CONSTITUENT C on C.ID=U.CONSTITUENTID
    left outer join
        RECORDCONFIDENCE RC on RC.ID = WP.ID 

    where
        WP.WEALTHID = @WEALTHID and

        (nullif(@SOURCE, '') is null or WP.SOURCE = @SOURCE) and

        (((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
         ((@CONFIDENCE = 99)                     and WP.CONFIRMED = 1))

    order by
        WP.COMPANY,
        WP.SOURCE;
end