USP_MKTSEGMENTATION_GETACTIVEOFFERCOUNT

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETACTIVEOFFERCOUNT]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @OFFERS int;

  -- get the total offer count for the activated mailing...

  set @SQL = 'select @OFFERS = count([DONORID])' + char(13) +
             'from dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + ']' + char(13) +
             'where [DONORQUERYVIEWCATALOGID] is not null';

  exec sp_executesql @SQL, N'@OFFERS int output', @OFFERS = @OFFERS output;

  -- get the total offer count for any vendor managed list segments and add their offers to the total offer count...

  select
    @OFFERS = @OFFERS + isnull(sum(isnull([MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY], [MKTSEGMENTLIST].[RENTALQUANTITY])), 0) + isnull(sum(isnull([MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY], [MKTSEGMENTLIST].[EXCHANGEQUANTITY])), 0)
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
  and [MKTSEGMENTLIST].[TYPECODE] = 1;  --Only get the vendor managed segments


  select @OFFERS as [TOTALOFFERS];

  return 0;