USP_MKTMEMBERSHIPMAILINGPROCESSSTATUS_GETDATALIST

CREATE procedure dbo.[USP_MKTMEMBERSHIPMAILINGPROCESSSTATUS_GETDATALIST]
(
  @MEMBERSHIPMAILINGPROCESSID 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],
     [MKTMEMBERSHIPMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] as [MAILINGID],
     dbo.[UFN_MKTSEGMENTATION_GETNAME]([MKTMEMBERSHIPMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID]) as [MAILING],
     datediff(s, [BUSINESSPROCESSSTATUS].[STARTEDON], coalesce([BUSINESSPROCESSSTATUS].[ENDEDON], getdate())) as [DURATION]
  from 
    dbo.[BUSINESSPROCESSSTATUS]
  inner join 
    dbo.[MKTMEMBERSHIPMAILINGPROCESSSTATUS] 
  on 
    [MKTMEMBERSHIPMAILINGPROCESSSTATUS].[ID] = [BUSINESSPROCESSSTATUS].[ID]
  inner join 
    dbo.[MKTMEMBERSHIPMAILINGPROCESS] 
  on 
    [MKTMEMBERSHIPMAILINGPROCESS].[ID] = [MKTMEMBERSHIPMAILINGPROCESSSTATUS].[PARAMETERSETID]
  left outer join 
    dbo.[MKTMEMBERSHIPMAILINGPROCESSSEGMENTATION] 
  on 
    [MKTMEMBERSHIPMAILINGPROCESSSEGMENTATION].[MEMBERSHIPMAILINGPROCESSSTATUSID] = [MKTMEMBERSHIPMAILINGPROCESSSTATUS].[ID]
  inner join 
    dbo.[APPUSER] 
  on 
    [APPUSER].[ID] = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID]
  inner join 
    dbo.[BUSINESSPROCESSCATALOG] 
  on 
    [BUSINESSPROCESSCATALOG].[ID] = [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSCATALOGID]
  where 
    [MKTMEMBERSHIPMAILINGPROCESSSTATUS].[PARAMETERSETID] = @MEMBERSHIPMAILINGPROCESSID
  and 
    (dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [MKTMEMBERSHIPMAILINGPROCESSSTATUS].[PARAMETERSETID]) = 1)
  and 
    (@STATUSCODE is null or [BUSINESSPROCESSSTATUS].[STATUSCODE] = @STATUSCODE)
  order by 
    [BUSINESSPROCESSSTATUS].[STARTEDON] desc

  return 0;