USP_DATALIST_MKTASKLADDERRESPONSEROUNDINGUSED

create procedure dbo.USP_DATALIST_MKTASKLADDERRESPONSEROUNDINGUSED
(
  @SELECTIONID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier,
  @EFFORTID uniqueidentifier = null,
  @FILTERTYPECODE int = 0,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @SEGMENTATIONID uniqueidentifier;
  declare @MAILINGDATATABLENAME nvarchar(128);
  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  declare @ASKLADDER table (
    [ASKLADDERID] uniqueidentifier
  );

  --Build Segmentation cursor

  declare @SEGMENTATIONCURSOR cursor;                    

  if @FILTERTYPECODE = 0 begin
  set @SQL = 'set @SEGMENTATIONCURSOR = cursor local fast_forward for' + char(13) + 
         '  select [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) + 
         '  from dbo.[MKTSEGMENTATIONACTIVATE]' + char(13) +                                  
         '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) +
         '  where dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTSEGMENTATION].[SITEID]) = 1' + char(13) +
         '  and [MKTSEGMENTATION].[ACTIVE] = 1 and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4;' + char(13) +
         'open @SEGMENTATIONCURSOR;';
  end

  if @FILTERTYPECODE = 1 begin
    set @SQL = 'set @SEGMENTATIONCURSOR = cursor local fast_forward for' + char(13) + 
         '  select [IDSET].[ID]' + char(13) + 
         '  from dbo.' + dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME](@SELECTIONID) + ' as [IDSET]' + char(13) + 
         '  inner join dbo.[MKTSEGMENTATIONACTIVATE] on  [IDSET].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) +
         '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [IDSET].[ID]' + char(13) +
         '  where dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTSEGMENTATION].[SITEID]) = 1' + char(13) +
         '  and [MKTSEGMENTATION].[ACTIVE] = 1 and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4;' + char(13) +
         'open @SEGMENTATIONCURSOR;';
  end;

  if @FILTERTYPECODE = 2 begin
    set @SQL = 'set @SEGMENTATIONCURSOR = cursor local fast_forward for' + char(13) + 
         '  select [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) + 
         '  from dbo.[MKTSEGMENTATIONACTIVATE]' + char(13) +                                  
         '  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]' + char(13) +
         '  where dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTSEGMENTATION].[SITEID]) = 1' + char(13) +
         '  and [MKTSEGMENTATION].[ACTIVE] = 1 and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4 and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @EFFORTID;' + char(13) +
         'open @SEGMENTATIONCURSOR;';
  end

  if @FILTERTYPECODE = 2
    exec sp_executesql @SQL, N'@SEGMENTATIONCURSOR cursor output, @CURRENTAPPUSERID uniqueidentifier, @EFFORTID uniqueidentifier', @SEGMENTATIONCURSOR = @SEGMENTATIONCURSOR output, @CURRENTAPPUSERID = @CURRENTAPPUSERID, @EFFORTID = @EFFORTID;
  else
    exec sp_executesql @SQL, N'@SEGMENTATIONCURSOR cursor output, @CURRENTAPPUSERID uniqueidentifier', @SEGMENTATIONCURSOR = @SEGMENTATIONCURSOR output, @CURRENTAPPUSERID = @CURRENTAPPUSERID;

  --Loop through each Mailing

  fetch next from @SEGMENTATIONCURSOR into @SEGMENTATIONID;

  while (@@FETCH_STATUS = 0)
  begin
    if ( -- check site security

       select count(1) 
       from (select [SITEID]
             from dbo.[MKTSEGMENTATION]
             where [ID] = @SEGMENTATIONID) 
       as [SEGMENTATIONSITE]
       where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SEGMENTATIONSITE].[SITEID] or (SITEID is null and [SEGMENTATIONSITE].[SITEID] is null)))
       ) > 0
    begin
      set @MAILINGDATATABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

      --Get all the ask ladder items from the segmentation data table

      set @SQL = 'select distinct [MAILINGDATA].[ASKLADDERID]' + char(13) +
            'from dbo.[' + @MAILINGDATATABLENAME + '] as [MAILINGDATA]' + char(13);
      insert into @ASKLADDER
      exec (@SQL);
    end
  fetch next from @SEGMENTATIONCURSOR into @SEGMENTATIONID;
  end;

  close @SEGMENTATIONCURSOR;
  deallocate @SEGMENTATIONCURSOR;

  if exists (select [ASKLADDERID] from @ASKLADDER)

    select top 1
      cast(case when [MKTASKLADDER].[BASECURRENCYID] <> @ORGANIZATIONCURRENCYID then 1 else 0 end as bit) as [ROUNDINGUSED] 
    from @ASKLADDER as [ASKLADDER]
    inner join dbo.[MKTASKLADDER] on [MKTASKLADDER].[ID] = [ASKLADDER].[ASKLADDERID]
    order by [ROUNDINGUSED] desc;

  else
    select 
      cast(0 as bit) as [ROUNDINGUSED]
    order by [ROUNDINGUSED];

  return 0;