USP_DATALIST_CONSTITUENT_RECOGNITIONSUMMARYEXPANDED

CREATE procedure dbo.USP_DATALIST_CONSTITUENT_RECOGNITIONSUMMARYEXPANDED
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @CURRENCYCODE tinyint = 1,
    @DATEFILTER tinyint,
    @RECOGNITIONFILTERID uniqueidentifier = null,
    @BREAKDOWNBY tinyint = 0,
    @HOUSEHOLDRECOGNITIONFILTERID uniqueidentifier = null,
    @CAMPAIGNFILTERMODE tinyint = 0,
    @CAMPAIGNSSELECTED xml = null
)
as
    set nocount on;

    declare
        @STARTDATE datetime,
        @ENDDATE datetime,
        @HOUSEHOLDID uniqueidentifier = null,
        @ISGROUP bit = 0,
        @RECOGNITIONTOTALNUMBER int = null,
        @RECOGNITIONTOTALAMOUNT money = null,
        @RECOGNITIONTOTALAMOUNT_HOUSEHOLD money = null,
        @RECOGNITIONTOTALYEARS int = null,
        @RECOGNITIONCONSECUTIVEYEARS int = null,
        @RECOGNITIONGIVENSINCEFISCALYEAR datetime = null,
        @TOTALRECOGNITIONWITHGIFTAID money = null,

        @RECOGNITIONFIRSTID uniqueidentifier = null,
        @RECOGNITIONFIRSTRECORDID uniqueidentifier = null,
        @RECOGNITIONFIRSTDATE datetime = null,
        @RECOGNITIONFIRSTTYPECODE tinyint = null,
        @RECOGNITIONFIRSTTYPE nvarchar(50) = null,
        @RECOGNITIONFIRSTAMOUNT money = null,
        @RECOGNITIONLATESTID uniqueidentifier = null,
        @RECOGNITIONLATESTRECORDID uniqueidentifier = null,
        @RECOGNITIONLATESTDATE datetime = null,
        @RECOGNITIONLATESTTYPECODE tinyint = null,
        @RECOGNITIONLATESTTYPE nvarchar(50) = null,
        @RECOGNITIONLATESTAMOUNT money = null,         

        @CURRENCYISOCURRENCYCODE nvarchar(3) = null,
        @CURRENCYDECIMALDIGITS tinyint = 0,
        @CURRENCYSYMBOL nvarchar(5) = null,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0;

    exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;

    /*Get Household ID*/
    select 
        top(1) @HOUSEHOLDID = GM.GROUPID
    from 
        dbo.GROUPMEMBER as GM
        left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
    where 
        GM.MEMBERID = @CONSTITUENTID and
        GD.GROUPTYPECODE = 0 and
        dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1;

    /* see if this is a household */
    if @HOUSEHOLDID is null
        select
            @ISGROUP = 1
        from 
            dbo.GROUPDATA 
        where 
            GROUPDATA.ID = @CONSTITUENTID;

    exec dbo.USP_CONSTITUENT_RECOGNITIONSUMMARYEXPANDED
        @CONSTITUENTID,
        @ISGROUP,
        @HOUSEHOLDID,
        @RECOGNITIONFILTERID,
        @STARTDATE,
        @ENDDATE,
        @CURRENTAPPUSERID,
        @SITEFILTERMODE,
        @SITESSELECTED,
        @SECURITYFEATUREID,
        @SECURITYFEATURETYPE,
        @CURRENCYCODE,

        @RECOGNITIONTOTALNUMBER output,
        @RECOGNITIONTOTALAMOUNT output,
        @RECOGNITIONTOTALAMOUNT_HOUSEHOLD output,
        @RECOGNITIONTOTALYEARS output,
        @RECOGNITIONCONSECUTIVEYEARS output,
        @RECOGNITIONGIVENSINCEFISCALYEAR output,
        @TOTALRECOGNITIONWITHGIFTAID output,

        @RECOGNITIONFIRSTID output,
        @RECOGNITIONFIRSTRECORDID output,
        @RECOGNITIONFIRSTDATE output,
        @RECOGNITIONFIRSTTYPECODE output,
        @RECOGNITIONFIRSTTYPE output,
        @RECOGNITIONFIRSTAMOUNT output,
        @RECOGNITIONLATESTID output,
        @RECOGNITIONLATESTRECORDID output,
        @RECOGNITIONLATESTDATE output,
        @RECOGNITIONLATESTTYPECODE output,
        @RECOGNITIONLATESTTYPE output,
        @RECOGNITIONLATESTAMOUNT output,
        @CURRENCYISOCURRENCYCODE output,
        @CURRENCYDECIMALDIGITS output,
        @CURRENCYSYMBOL output,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE output,
        @BREAKDOWNBY,
        @CAMPAIGNFILTERMODE,
        @CAMPAIGNSSELECTED

    select
        @RECOGNITIONTOTALNUMBER as RECOGNITIONTOTALNUMBER,
        @RECOGNITIONTOTALAMOUNT as RECOGNITIONTOTALAMOUNT,
        @RECOGNITIONTOTALAMOUNT_HOUSEHOLD as RECOGNITIONTOTALAMOUNT_HOUSEHOLD,
        @RECOGNITIONTOTALYEARS as RECOGNITIONTOTALYEARS,
        @RECOGNITIONGIVENSINCEFISCALYEAR as RECOGNITIONGIVENSINCEFISCALYEAR,
        @RECOGNITIONCONSECUTIVEYEARS as RECOGNITIONCONSECUTIVEYEARS,

        @RECOGNITIONFIRSTID as RECOGNITIONFIRSTID,
        @RECOGNITIONFIRSTRECORDID as RECOGNITIONFIRSTRECORDID,
        @RECOGNITIONFIRSTDATE as RECOGNITIONFIRSTDATE,
        @RECOGNITIONFIRSTTYPECODE as RECOGNITIONFIRSTTYPECODE,
        @RECOGNITIONFIRSTTYPE as RECOGNITIONFIRSTTYPE,
        @RECOGNITIONFIRSTAMOUNT as RECOGNITIONFIRSTAMOUNT,
        @RECOGNITIONLATESTID as RECOGNITIONLATESTID,
        @RECOGNITIONLATESTRECORDID as RECOGNITIONLATESTRECORDID,
        @RECOGNITIONLATESTDATE as RECOGNITIONLATESTDATE,
        @RECOGNITIONLATESTTYPECODE as RECOGNITIONLATESTTYPECODE,
        @RECOGNITIONLATESTTYPE as RECOGNITIONLATESTTYPE,
        @RECOGNITIONLATESTAMOUNT as RECOGNITIONLATESTAMOUNT,
        @TOTALRECOGNITIONWITHGIFTAID as TOTALRECOGNITIONWITHGIFTAID