USP_MKTACKNOWLEDGEMENTMAILINGPROCESS_GETREVIEWDATALIST

CREATE procedure dbo.[USP_MKTACKNOWLEDGEMENTMAILINGPROCESS_GETREVIEWDATALIST]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @NUMDAYS int = 7
)
as
  set nocount on;

  declare @SECURITYFEATUREID uniqueidentifier;
  declare @SECURITYFEATURETYPE tinyint;

  set @SECURITYFEATUREID = 'C29FD902-ACD9-40F8-A74E-2C0C61657488';
  set @SECURITYFEATURETYPE = 2;

  select
    [BUSINESSPROCESSSTATUS].[ID],
    [MKTACKNOWLEDGEMENTMAILINGPROCESS].[NAME],
    [BUSINESSPROCESSSTATUS].[STATUSCODE],
    [BUSINESSPROCESSSTATUS].[STATUS],
    [BUSINESSPROCESSSTATUS].[STARTEDON],
    [BUSINESSPROCESSSTATUS].[ENDEDON],
    [BUSINESSPROCESSSTATUS].[NUMBERPROCESSED],
    [BUSINESSPROCESSSTATUS].[NUMBEROFEXCEPTIONS],
    [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEDATE] as [ACKNOWLEDGERECEIPTDATE],
    [MKTSEGMENTATION].[NAME] as [MAILINGNAME],
    [MKTSEGMENTATION].[ID] as [MAILINGID],
    datediff(s, [BUSINESSPROCESSSTATUS].[STARTEDON], coalesce([BUSINESSPROCESSSTATUS].[ENDEDON], getdate())) as [DURATION],
    [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID] as "ACKNOWLEDGEMENTMAILINGPROCESSID"
  from dbo.[BUSINESSPROCESSSTATUS]
  inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID] = [BUSINESSPROCESSSTATUS].[ID]
  inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID] = [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID]
  left join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = [BUSINESSPROCESSSTATUS].[ID]
  left join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID]
  where dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID]) = 1
  --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

  --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

  and (isnull(@NUMDAYS, 0) <= 0 or dateadd(ms, -003, dateadd(d, 1, cast(cast([BUSINESSPROCESSSTATUS].[STARTEDON] as date) as datetime))) > cast(dateadd(dd, -@NUMDAYS, getdate()) as date))
  and 
    ( -- check site security

      select count(*) 
      from
        (select [SITEID]
         from dbo.[MKTSEGMENTATION] as [SEGMENTATIONSITE]
         where [SEGMENTATIONSITE].[ID] = [MKTSEGMENTATION].[ID]) 
      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
  order by [BUSINESSPROCESSSTATUS].[STARTEDON] desc;

  return 0;