USP_MKTSPONSORSHIPMAILINGPROCESSSTATUS_GETDATALIST

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

  return 0;