USP_DATALIST_WP_GETASSETTOTALS

CREATE procedure dbo.USP_DATALIST_WP_GETASSETTOTALS(
    @CONSTITUENTID uniqueidentifier = null,
    @CONFIDENCE integer = 0
    )
as
    set nocount on;

    with ALLASSETS_CTE as (
        select '1' as [ASSETTYPE],
                    null as [MC], 
                    null as [SOURCE], 
                    1 as [CONFIRMED], 
                    0 as [REJECTED], 
                    0 as [VALUE]
        UNION ALL
    select '2', null, null, 1, 0, 0
        UNION ALL
    select '3', null, null, 1, 0, 0
        UNION ALL
    select '4', null, null, 1, 0, 0
        UNION ALL
    select '5', null, null, 1, 0, 0
        UNION ALL
    select '1' as [ASSETTYPE], WPREALESTATE.MC, WPREALESTATE.SOURCE, WPREALESTATE.CONFIRMED, WPREALESTATE.REJECTED, WPREALESTATE.PROPERTYVALUATION
    from dbo.WPREALESTATE where WPREALESTATE.WEALTHID = @CONSTITUENTID and WPREALESTATE.HISTORICCODE <> 1
        UNION ALL
    select '2', WPBUSINESSOWNERSHIP.MC, WPBUSINESSOWNERSHIP.SOURCE, WPBUSINESSOWNERSHIP.CONFIRMED, WPBUSINESSOWNERSHIP.REJECTED, WPBUSINESSOWNERSHIP.OWNERSHIPVALUE
    from dbo.WPBUSINESSOWNERSHIP where WPBUSINESSOWNERSHIP.WEALTHID = @CONSTITUENTID
        UNION ALL
    select '3', WPSECURITIES.MC, WPSECURITIES.SOURCE, WPSECURITIES.CONFIRMED, WPSECURITIES.REJECTED, WPSECURITIES.VALUEDIRECT
    from dbo.WPSECURITIES where WPSECURITIES.WEALTHID=@CONSTITUENTID
        UNION ALL
    select '4', WPINCOMECOMPENSATION.MC, WPINCOMECOMPENSATION.SOURCE,WPINCOMECOMPENSATION.CONFIRMED, WPINCOMECOMPENSATION.REJECTED, WPINCOMECOMPENSATION.TOTALCOMPENSATION
    from dbo.WPINCOMECOMPENSATION where WPINCOMECOMPENSATION.WEALTHID=@CONSTITUENTID and WPINCOMECOMPENSATION.HISTORICCODE <> 1
        UNION ALL
    select     '5', null, WPOTHERASSET.SOURCE, WPOTHERASSET.CONFIRMED, WPOTHERASSET.REJECTED, WPOTHERASSET.VALUE
    from dbo.WPOTHERASSET where WPOTHERASSET.WEALTHID=@CONSTITUENTID
    )


    select ASSETTYPE, sum(VALUE) as [VALUE]
    from ALLASSETS_CTE
    left outer join dbo.MATCHCODE on ALLASSETS_CTE.MC = MATCHCODE.MATCHCODE
    left outer join dbo.WEALTHSOURCE on ALLASSETS_CTE.SOURCE = WEALTHSOURCE.SOURCE
    left outer join dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID 
                                            and CONFIDENCERATING.WEALTHSOURCEID = WEALTHSOURCE.ID
    where (select
            case
                when ALLASSETS_CTE.CONFIRMED = 1 then '6'
                when ALLASSETS_CTE.REJECTED = 1 then '0'
                else
                    ISNULL(coalesce(CONFIDENCERATING.CONFIDENCE, MATCHCODE.DEFAULTCONFIDENCE),
                            (select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
                end
        ) >= @CONFIDENCE
    group by ASSETTYPE
    Order by ASSETTYPE