USP_DATALIST_CONSTITUENT_RECOGNITIONSUMMARY_2

        create procedure dbo.USP_DATALIST_CONSTITUENT_RECOGNITIONSUMMARY_2
            (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @SITEFILTERMODE tinyint = 0,
                /*DanielCo [12/19/08] Dashboards don't support parameters that cannot 
                    be cast as strings like this collection so an error will occur if
                    user selects more sites than fits here (~73)*/
                @SITESSELECTED nvarchar(max) = 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 nvarchar(max) = 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 nvarchar(24) = 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,

          --household specific fields

          @RECOGNITIONTOTALNUMBER_HH int = null,
                  @RECOGNITIONTOTALAMOUNT_HH money = null,
          @RECOGNITIONTOTALAMOUNT_HOUSEHOLD_HH money = null,
                  @RECOGNITIONTOTALYEARS_HH int = null,
                  @RECOGNITIONCONSECUTIVEYEARS_HH int = null,
                  @RECOGNITIONGIVENSINCEFISCALYEAR_HH nvarchar(24) = null,
          @TOTALRECOGNITIONWITHGIFTAID_HH money = null,

                  @RECOGNITIONFIRSTID_HH uniqueidentifier = null,
                  @RECOGNITIONFIRSTRECORDID_HH uniqueidentifier = null,
                  @RECOGNITIONFIRSTDATE_HH datetime = null,
                  @RECOGNITIONFIRSTTYPECODE_HH tinyint = null,
                  @RECOGNITIONFIRSTTYPE_HH nvarchar(50) = null,
                  @RECOGNITIONFIRSTAMOUNT_HH money = null,
                  @RECOGNITIONLATESTID_HH uniqueidentifier = null,
                  @RECOGNITIONLATESTRECORDID_HH uniqueidentifier = null,
                  @RECOGNITIONLATESTDATE_HH datetime = null,
                  @RECOGNITIONLATESTTYPECODE_HH tinyint = null,
                  @RECOGNITIONLATESTTYPE_HH nvarchar(50) = null,
                  @RECOGNITIONLATESTAMOUNT_HH money = null

          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_2
              @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

          if @HOUSEHOLDID is not null
            begin
              exec dbo.USP_CONSTITUENT_RECOGNITIONSUMMARYEXPANDED_2 
                @HOUSEHOLDID,
                0,
                null,
                @HOUSEHOLDRECOGNITIONFILTERID,
                @STARTDATE,
                @ENDDATE,
                @CURRENTAPPUSERID,
                @SITEFILTERMODE,
                @SITESSELECTED,
                @SECURITYFEATUREID,
                @SECURITYFEATURETYPE,
                @CURRENCYCODE,

                @RECOGNITIONTOTALNUMBER = @RECOGNITIONTOTALNUMBER_HH output,
                @RECOGNITIONTOTALAMOUNT = @RECOGNITIONTOTALAMOUNT_HH output,
                @RECOGNITIONTOTALYEARS = @RECOGNITIONTOTALYEARS_HH output,
                @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS_HH output,
                @RECOGNITIONGIVENSINCEFISCALYEAR = @RECOGNITIONGIVENSINCEFISCALYEAR_HH output,
                @TOTALRECOGNITIONWITHGIFTAID = @TOTALRECOGNITIONWITHGIFTAID_HH output,

                @RECOGNITIONFIRSTID = @RECOGNITIONFIRSTID_HH output,
                @RECOGNITIONFIRSTRECORDID = @RECOGNITIONFIRSTRECORDID_HH output,
                @RECOGNITIONFIRSTDATE = @RECOGNITIONFIRSTDATE_HH output,
                @RECOGNITIONFIRSTTYPECODE = @RECOGNITIONFIRSTTYPECODE_HH output,
                @RECOGNITIONFIRSTTYPE = @RECOGNITIONFIRSTTYPE_HH output,
                @RECOGNITIONFIRSTAMOUNT = @RECOGNITIONFIRSTAMOUNT_HH output,
                @RECOGNITIONLATESTID = @RECOGNITIONLATESTID_HH output,
                @RECOGNITIONLATESTRECORDID = @RECOGNITIONLATESTRECORDID_HH output,
                @RECOGNITIONLATESTDATE = @RECOGNITIONLATESTDATE_HH output,
                @RECOGNITIONLATESTTYPECODE = @RECOGNITIONLATESTTYPECODE_HH output,
                @RECOGNITIONLATESTTYPE = @RECOGNITIONLATESTTYPE_HH output,
                @RECOGNITIONLATESTAMOUNT = @RECOGNITIONLATESTAMOUNT_HH output,  

                @BREAKDOWNBY = @BREAKDOWNBY,
                @CAMPAIGNFILTERMODE = @CAMPAIGNFILTERMODE,
                @CAMPAIGNSSELECTED = @CAMPAIGNSSELECTED
             end     

          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,

              @CURRENCYISOCURRENCYCODE as CURRENCYISOCURRENCYCODE,
              @CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
              @CURRENCYSYMBOL as CURRENCYSYMBOL,
              @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,

              --household specific fields

              @RECOGNITIONTOTALNUMBER_HH as RECOGNITIONTOTALNUMBER_HH,
              @RECOGNITIONTOTALAMOUNT_HH as RECOGNITIONTOTALAMOUNT_HH,
              @RECOGNITIONTOTALYEARS_HH as RECOGNITIONTOTALYEARS_HH,
              @RECOGNITIONGIVENSINCEFISCALYEAR_HH as RECOGNITIONGIVENSINCEFISCALYEAR_HH,
              @RECOGNITIONCONSECUTIVEYEARS_HH as RECOGNITIONCONSECUTIVEYEARS_HH,

              @RECOGNITIONFIRSTID_HH as RECOGNITIONFIRSTID_HH,
              @RECOGNITIONFIRSTRECORDID_HH as RECOGNITIONFIRSTRECORDID_HH,
              @RECOGNITIONFIRSTDATE_HH as RECOGNITIONFIRSTDATE_HH,
              @RECOGNITIONFIRSTTYPECODE_HH as RECOGNITIONFIRSTTYPECODE_HH,
              @RECOGNITIONFIRSTTYPE_HH as RECOGNITIONFIRSTTYPE_HH, 
              @RECOGNITIONFIRSTAMOUNT_HH as RECOGNITIONFIRSTAMOUNT_HH,
              @RECOGNITIONLATESTID_HH as RECOGNITIONLATESTID_HH,
              @RECOGNITIONLATESTRECORDID_HH as RECOGNITIONLATESTRECORDID_HH,
              @RECOGNITIONLATESTDATE_HH as RECOGNITIONLATESTDATE_HH,
              @RECOGNITIONLATESTTYPECODE_HH as RECOGNITIONLATESTTYPECODE_HH,
              @RECOGNITIONLATESTTYPE_HH as RECOGNITIONLATESTTYPE_HH,
              @RECOGNITIONLATESTAMOUNT_HH as RECOGNITIONLATESTAMOUNT_HH,
              @TOTALRECOGNITIONWITHGIFTAID_HH as TOTALRECOGNITIONWITHGIFTAID_HH               

        return 0;