USP_MKTMEMBERSHIPMAILINGPROCESS_GETREVIEWDATALIST

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

  declare @SECURITYFEATUREID uniqueidentifier;
  declare @SECURITYFEATURETYPE tinyint;

  set @SECURITYFEATUREID = '856765D7-2E52-4722-84FA-D5DE2992E575';
  set @SECURITYFEATURETYPE = 2;

  select
    [BUSINESSPROCESSSTATUS].[ID],
    [MKTMEMBERSHIPMAILINGPROCESS].[NAME],
    [BUSINESSPROCESSSTATUS].[STATUSCODE],
    [BUSINESSPROCESSSTATUS].[STATUS],
    [BUSINESSPROCESSSTATUS].[STARTEDON],
    [BUSINESSPROCESSSTATUS].[ENDEDON],
    [BUSINESSPROCESSSTATUS].[NUMBERPROCESSED],
    [BUSINESSPROCESSSTATUS].[NUMBEROFEXCEPTIONS],
    [MKTSEGMENTATION].[NAME] as [MAILINGNAME],
    [MKTSEGMENTATION].[ID] as [MAILINGID],
    datediff(s, [BUSINESSPROCESSSTATUS].[STARTEDON], coalesce([BUSINESSPROCESSSTATUS].[ENDEDON], getdate())) as [DURATION],
    [MKTMEMBERSHIPMAILINGPROCESS].[ID] [MEMBERSHIPEFFORTPROCESSID]
  from dbo.[BUSINESSPROCESSSTATUS]
  inner join dbo.[MKTMEMBERSHIPMAILINGPROCESSSTATUS] on [MKTMEMBERSHIPMAILINGPROCESSSTATUS].[ID] = [BUSINESSPROCESSSTATUS].[ID]
  inner join dbo.[MKTMEMBERSHIPMAILINGPROCESS] on [MKTMEMBERSHIPMAILINGPROCESSSTATUS].[PARAMETERSETID] = [MKTMEMBERSHIPMAILINGPROCESS].[ID]
  left join dbo.[MKTMEMBERSHIPMAILINGPROCESSSEGMENTATION] on [MKTMEMBERSHIPMAILINGPROCESSSEGMENTATION].[MEMBERSHIPMAILINGPROCESSSTATUSID] = [BUSINESSPROCESSSTATUS].[ID]
  left join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTMEMBERSHIPMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID]
  where dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [MKTMEMBERSHIPMAILINGPROCESS].[ID]) = 1
  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
  --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))
  order by [BUSINESSPROCESSSTATUS].[STARTEDON] desc;

  return 0;