USP_REPORT_CANCELLATIONS

            CREATE procedure dbo.USP_REPORT_CANCELLATIONS (
                @DIMENSION1 as nvarchar(100) = null,
                @DIMENSION1VALUE as nvarchar(255) = null,
                @DIMENSION1VALUE3 as nvarchar(255) = null,
                @DIMENSION2 as nvarchar(100) = null,
                @DIMENSION2VALUE as nvarchar(255) = null,
                @DIMENSION2VALUE3 as nvarchar(255) = null,
                @DIMENSION3 as nvarchar(100) = null,
                @DIMENSION3VALUE as nvarchar(255) = null,
                @DIMENSION3VALUE3 as nvarchar(255) = null,
                @STARTDATE datetime = null, 
                @ENDDATE datetime = null,
                @INCLUDECANCELLATIONS bit= null,
                 @INCLUDETERMINATIONS bit= null,
                @DATETYPE nvarchar(15) = null,
                @DATERANGEDISPLAY nvarchar(100) = null
                )
            with execute as owner
            as
            set nocount on;

            declare @SQLTOEXEC nvarchar(max);
            declare @FROMCLAUSE nvarchar(max);
            declare @COLUMNSELECT nvarchar(max);
            declare @WHERECLAUSE nvarchar(max);
            declare @REVENUEJOIN nvarchar(max);
            declare @GROUPBYCLAUSE nvarchar(max);
            declare @SELECT nvarchar(max);
            declare @FROM nvarchar(max);
            declare @COLUMN nvarchar(max);
            declare @WHERE nvarchar(max);
            declare @LOCATIONCOUNT tinyint = 0;
            declare @LOCATIONTYPEENUM tinyint = 2;
            declare @DATECOLTEMP nvarchar(800);
            declare @DIMENSIONCOLUMNS nvarchar(100) = 'DIMENSION1';
            declare @RESULTSELECTION nvarchar(2000);

      declare @INCLUDECANCELORTERMINATE nvarchar(255);

      if @INCLUDETERMINATIONS = 1 and @INCLUDECANCELLATIONS = 1
        set @INCLUDECANCELORTERMINATE= ' in (2,3) '
      else  
      begin
        if @INCLUDETERMINATIONS =1 and @INCLUDECANCELLATIONS =0  
          set @INCLUDECANCELORTERMINATE= '= 3 '
        else
          set @INCLUDECANCELORTERMINATE= '= 2 '
      end

            set @FROMCLAUSE = 'from dbo.SPONSORSHIP SP inner join SPONSORSHIPTRANSACTION ST on ST.CONTEXTSPONSORSHIPID=SP.ID';
            set @WHERECLAUSE =  ' and ST.ACTIONCODE '+ @INCLUDECANCELORTERMINATE +
                                        ' and (@STARTDATE is null or ST.TRANSACTIONDATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) ' +
                                      ' and (@ENDDATE is null or ST.TRANSACTIONDATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) ';


            if @DIMENSION1 in (1,3,4,5) OR @DIMENSION2 in (1,3,4,5) OR @DIMENSION3 in (1,3,4,5)
            begin
                set @REVENUEJOIN = ' inner join dbo.REVENUESPLIT SPLT on SPLT.ID = SP.REVENUESPLITID ' +
                                   ' inner join dbo.REVENUE RV on RV.ID = SPLT.REVENUEID ';
            end

            if @REVENUEJOIN is not null
            begin
                 set @FROMCLAUSE =  @FROMCLAUSE + @REVENUEJOIN;
            end

            set @COLUMNSELECT = 'select dbo.UFN_REPORT_PROCESSDATE_PARAM('+ @DATETYPE + ',ST.TRANSACTIONDATE) DATECOL, datename(year, ST.TRANSACTIONDATE) YEARCOL';

            -- count how many dimension parameters have location type specified

            if @DIMENSION1 = @LOCATIONTYPEENUM
            begin
                set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
            end

            if @DIMENSION2 is not null and @DIMENSION2 = @LOCATIONTYPEENUM
            begin
                set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
            end

            if @DIMENSION3 is not null and @DIMENSION3 = @LOCATIONTYPEENUM
            begin
                set @LOCATIONCOUNT = @LOCATIONCOUNT + 1;
            end

            -- only process dimension if not null and not Location Type

            if @DIMENSION1 is not null and @DIMENSION1 <> @LOCATIONTYPEENUM
            begin
                exec dbo.USP_REPORT_PROCESSDIMENSION 
                    @DIMENSION1, 
                    '@DIMENSION1VALUE', 
                    @FROM output, 
                    @COLUMN output, 
                    @WHERE output, 
                    'DIMENSION1'

                set @FROMCLAUSE = @FROMCLAUSE + @FROM;
                set @COLUMNSELECT = @COLUMNSELECT + @COLUMN;
                set @WHERECLAUSE += @WHERE + ' and @DIMENSION1VALUE3 is null';
                set @GROUPBYCLAUSE = 'DIMENSION1';

                --

                -- set these to null for the next row

                --

                set @FROM = '';
                set @COLUMN = '';
                set @WHERE = '';
            end

            -- only process dimension if it's not null and is not Location Type

            if @DIMENSION2 is not null
            begin 
                set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION2';
                if @DIMENSION2 <> @LOCATIONTYPEENUM
                begin
                    exec dbo.USP_REPORT_PROCESSDIMENSION
                        @DIMENSION2, 
                        '@DIMENSION2VALUE', 
                        @FROM output, 
                        @COLUMN output, 
                        @WHERE output, 
                        'DIMENSION2'

                    -- for channel and appeal from clause is not used.

                    if @FROM is not null
                    begin
                        set @FROMCLAUSE = @FROMCLAUSE + @FROM;
                    end
                    set @COLUMNSELECT =  @COLUMNSELECT + @COLUMN;
                    set @WHERECLAUSE =  @WHERECLAUSE + @WHERE + ' and @DIMENSION2VALUE3 is null';
                    if @GROUPBYCLAUSE is not null
            set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION2';
          else
            set @GROUPBYCLAUSE = 'DIMENSION2';
                    --

                    -- set these to null for the next row

                    --

                    set @FROM = '';
                    set @COLUMN = '';
                    set @WHERE = '';
                end
            end
            else
            begin
                set @WHERECLAUSE =  @WHERECLAUSE + ' and @DIMENSION2VALUE is null and @DIMENSION2VALUE3 is null';
            end
            --

            if @DIMENSION3 is not null 
            begin
                set @DIMENSIONCOLUMNS = @DIMENSIONCOLUMNS + ', DIMENSION3';
                if @DIMENSION3 <> @LOCATIONTYPEENUM
                begin
                    exec dbo.USP_REPORT_PROCESSDIMENSION
                        @DIMENSION3,
                        '@DIMENSION3VALUE', 
                        @FROM output, 
                        @COLUMN output, 
                        @WHERE output, 
                        'DIMENSION3'

                    -- for channel and appeal from clause is not used.

                    if @FROM is not null
                    begin
                        set @FROMCLAUSE = @FROMCLAUSE + @FROM;
                    end

                    set @COLUMNSELECT =  @COLUMNSELECT + @COLUMN;
                    set @WHERECLAUSE =  @WHERECLAUSE + @WHERE + ' and @DIMENSION3VALUE3 is null';
                    if @GROUPBYCLAUSE is not null
            set @GROUPBYCLAUSE = @GROUPBYCLAUSE + ', DIMENSION3';
          else
            set @GROUPBYCLAUSE = 'DIMENSION3';
                end
            end
            else
            begin
                set @WHERECLAUSE =  @WHERECLAUSE + ' and @DIMENSION3VALUE is null and @DIMENSION3VALUE3 is null';
            end

            -- handle processing of location types

            If @LOCATIONCOUNT > 0
            begin
                exec dbo.USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION
                @LOCATIONCOUNT, 
                @DIMENSION1, 
                @DIMENSION2,
                @DIMENSION3,
                @COLUMNSELECT,
                @FROMCLAUSE,
                @WHERECLAUSE,
                @GROUPBYCLAUSE,
                @SQLTOEXEC output
            end
            else
            begin

                set @SELECT = 'select count(*) CNT, DATECOL, YEARCOL, '+ @GROUPBYCLAUSE;

                -- construct the sql query


                set @SQLTOEXEC = @SELECT + ' from (' + @COLUMNSELECT + ' ' + @FROMCLAUSE + ' ' + @WHERECLAUSE + ') as QRY group by DATECOL, YEARCOL, ' + @GROUPBYCLAUSE;

            end

            set @DATECOLTEMP = dbo.UFN_REPORT_BUILD_DATECOL_TABLE_STRING(@DATETYPE)

            -- append to the query the code that handles  blanc column values

            set @RESULTSELECTION =' delete from #DATECOL where DATECOL+YEARCOL in (select DATECOL+YEARCOL from #RESULT) ' +
                             ' insert into #RESULT (DATECOL, YEARCOL, CNT, ' + @DIMENSIONCOLUMNS + ') ' +
                             ' select distinct DATEC , YEARC , 0, ' + @DIMENSIONCOLUMNS +
                             ' from (select #DATECOL.DATECOL as DATEC, #DATECOL.YEARCOL as YEARC, #RESULT.* from #DATECOL cross join #RESULT) as CROSSJOIN ' +
                             ' select CNT, DATECOL, YEARCOL, ' + @DIMENSIONCOLUMNS + ' from #RESULT '  

            set @SQLTOEXEC = @DATECOLTEMP + ' select * into #RESULT from (' +@SQLTOEXEC + ') as RES ' + @RESULTSELECTION

         -- execute the query

            exec sp_executesql @SQLTOEXEC,
                               N'@DIMENSION1VALUE nvarchar(255), @DIMENSION2VALUE nvarchar(255), @DIMENSION3VALUE nvarchar(255), @STARTDATE datetime, @ENDDATE datetime, @DIMENSION1VALUE3 nvarchar(255), @DIMENSION2VALUE3 nvarchar(255), @DIMENSION3VALUE3 nvarchar(255)',
                               @DIMENSION1VALUE=@DIMENSION1VALUE, @DIMENSION2VALUE=@DIMENSION2VALUE, @DIMENSION3VALUE=@DIMENSION3VALUE, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @DIMENSION1VALUE3=@DIMENSION1VALUE3, @DIMENSION2VALUE3=@DIMENSION2VALUE3, @DIMENSION3VALUE3=@DIMENSION3VALUE3 ;