USP_DATALIST_MKTSEGMENTATIONFINDERNUMBERVENDOR

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTATIONFINDERNUMBERVENDOR]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  with [QUANTITY] ([NAME], [VENDORID], [QUANTITY], [STATUS]) as
  (
    select 
      isnull([V].[KEYNAME], 'None') as [NAME],
      [L].[VENDORID],
      sum(isnull([SSL].[RENTALQUANTITY], [SL].[RENTALQUANTITY]) + isnull([SSL].[EXCHANGEQUANTITY], [SL].[EXCHANGEQUANTITY])) as [QUANTITY],
      case when [V].[KEYNAME] is null then 2 else 1 end as [STATUS]
    from dbo.[MKTSEGMENTATIONSEGMENT] as [SS] 
    left outer join dbo.[MKTSEGMENTATIONSEGMENTLIST] as [SSL] on [SSL].[ID] = [SS].[ID]    
    inner join dbo.[MKTSEGMENT] as [S] on [S].[ID] = [SS].[SEGMENTID]
    inner join dbo.[MKTSEGMENTLIST] as [SL] on [SL].[ID] = [S].[CURRENTSEGMENTLISTID] -- this data list is only used with inactive mailings, so this is the join to use

    inner join dbo.[MKTLIST] as [L] on [L].[ID] = [SL].[LISTID]
    left outer join dbo.[CONSTITUENT] as [V] on [V].[ID] = [L].[VENDORID]
    where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [SL].[TYPECODE] = 1
    group by [L].[VENDORID], [V].[KEYNAME]

    union all

    select 
      '<House file>' as [NAME],
      null as [VENDORID],
      sum(dbo.[UFN_MKTSEGMENTATIONSEGMENT_GETCACHEDOFFERCOUNT]([SS].[ID])) + dbo.[UFN_MKTSEGMENTATIONSEED_GETSEEDCOUNT](@SEGMENTATIONID) as [QUANTITY],
      min(convert(integer, dbo.[UFN_MKTSEGMENTATIONSEGMENT_ISRECORDCOUNTCACHECURRENT_2]([SS].[ID], 0, 0))) as [STATUS]
    from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
    inner join dbo.[MKTSEGMENT] as [S] on [S].[ID] = [SS].[SEGMENTID]    
    left outer join dbo.[MKTSEGMENTLIST] as [SL] on [SL].[ID] = [S].[CURRENTSEGMENTLISTID] -- this data list is only used with inactive mailings, so this is the join to use

    where [SS].[SEGMENTATIONID] = @SEGMENTATIONID and [SS].[EXCLUDE] = 0 and ([SL].[TYPECODE] is null or [SL].[TYPECODE] <> 1)
  ),
  [RESERVED] ([NAME], [VENDORID], [RESERVED]) as
  (
    select 
      isnull([V].[KEYNAME], '<House file>') as [NAME],
      [FN].[VENDORID],
      sum(isnull([FN].[MAX] - [FN].[MIN] + 1, 0)) as [RESERVED]
    from dbo.[MKTSEGMENTATIONFINDERNUMBER] as [FN]
    left join dbo.[CONSTITUENT] as [V] on [V].[ID] = [FN].[VENDORID]
    where [FN].[SEGMENTATIONID] = @SEGMENTATIONID
    group by [FN].[VENDORID], [V].[KEYNAME]
  )
  select 
    isnull([QUANTITY].[NAME], [RESERVED].[NAME]),
    case when [QUANTITY].[STATUS] = 0 then 0 else isnull([QUANTITY].[QUANTITY], 0) end [QUANTITY], 
    case when [QUANTITY].[STATUS] = 2 then 0 else isnull([RESERVED].[RESERVED], 0) end [RESERVED], 
    case 
      when [QUANTITY].[STATUS] = 0 then 'The quantity is 0 because the segments have not been recalculated.'
      when [QUANTITY].[STATUS] = 1 and [QUANTITY].[QUANTITY] - isnull([RESERVED].[RESERVED], 0) > 0 then 'There are not enough finder numbers reserved for this source.'
      when [QUANTITY].[STATUS] = 2 then 'The marketing effort uses an imported list that has not been imported, or a vendor managed list that does not have a vendor.'
      when [QUANTITY].[STATUS] is null then 'There are no longer any members from this source in the marketing effort.'
      else ''
    end [STATUS],
    case 
      when [QUANTITY].[STATUS] = 0 then 'RES:warning'
      when [QUANTITY].[STATUS] = 1 and [QUANTITY].[QUANTITY] - isnull([RESERVED].[RESERVED], 0) > 0 then 'RES:x_16'
      when [QUANTITY].[STATUS] = 2 then 'RES:x_16'
      when [QUANTITY].[STATUS] is null then 'RES:warning'
      else 'RES:check'
    end [STATUSIMAGE]
  from [RESERVED] 
  left join [QUANTITY] on [RESERVED].[VENDORID] = [QUANTITY].[VENDORID] or ([RESERVED].[VENDORID] is null and [QUANTITY].[VENDORID] is null);

  return 0;