USP_MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS_GETDATALIST

CREATE procedure dbo.[USP_MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS_GETDATALIST]
(
  @MKTACKNOWLEDGEMENTMAILINGPROCESSID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @STATUSCODE tinyint = null
)
as
  set nocount on;

  select
     [BUSINESSPROCESSSTATUS].[ID],
     [BUSINESSPROCESSCATALOG].[NAME] as [BUSINESSPROCESSCATALOGNAME],
     [BUSINESSPROCESSSTATUS].[STATUSCODE],
     [BUSINESSPROCESSSTATUS].[STATUS],
     [BUSINESSPROCESSSTATUS].[ERRORMESSAGE],
     isnull([APPUSER].[USERNAME],'') as [USERNAME],
     [BUSINESSPROCESSSTATUS].[STARTEDON],
     [BUSINESSPROCESSSTATUS].[ENDEDON],
     [BUSINESSPROCESSSTATUS].[NUMBERPROCESSED],
     [BUSINESSPROCESSSTATUS].[NUMBEROFEXCEPTIONS],
     [BUSINESSPROCESSSTATUS].[SERVERNAME],
     [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] as [MAILINGID],
     dbo.[UFN_MKTSEGMENTATION_GETNAME]([MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID]) as [MAILING],
     [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEDATE],
     datediff(s, [BUSINESSPROCESSSTATUS].[STARTEDON], coalesce([BUSINESSPROCESSSTATUS].[ENDEDON], getdate())) as [DURATION]
  from dbo.[BUSINESSPROCESSSTATUS]
  inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID] = [BUSINESSPROCESSSTATUS].[ID]
  inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID]
  left join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID]
  inner join dbo.[APPUSER] on [APPUSER].[ID] = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID]
  inner join dbo.[BUSINESSPROCESSCATALOG] on [BUSINESSPROCESSCATALOG].[ID] = [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSCATALOGID]
  where [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID] = @MKTACKNOWLEDGEMENTMAILINGPROCESSID
  and (dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID]) = 1)
  and (@STATUSCODE is null or [BUSINESSPROCESSSTATUS].[STATUSCODE] = @STATUSCODE)
  order by [BUSINESSPROCESSSTATUS].[STARTEDON] desc

  return 0;