USP_GETCONDITIONALCONTENTJOBRECIPIENTS

CREATE procedure dbo.USP_GETCONDITIONALCONTENTJOBRECIPIENTS
(
@EMAILJOBID int,
@JOBKEY nvarchar(1000)
)
as
begin

  select BOSP.BACKOFFICERECORDID as RECIPIENTID, C.EMAILLISTID, CAST(0 as int) as SUBTYPE
  from CONDITIONALCONTENTRECIPIENT C
  inner join dbo.EMAILLIST EL on EL.ID = C.EMAILLISTID
  left outer join dbo.BACKOFFICESYSTEMPEOPLE BOSP on BOSP.ID = C.BOSPID
  where C.EMAILJOBID = @EMAILJOBID
  and C.JOBKEY = @JOBKEY
  and EL.ORIGIN = 2

  union all 

  select C.EMAILLISTUPLOADMASTERLISTID as RECIPIENTID, C.EMAILLISTID, CAST(0 as int) as SUBTYPE
  from CONDITIONALCONTENTRECIPIENT C
  inner join dbo.EMAILLIST EL on EL.ID = C.EMAILLISTID
  where C.EMAILJOBID = @EMAILJOBID
  and C.JOBKEY = @JOBKEY
  and EL.ORIGIN = 1

  union all

  select C.EMAILLIST_USERDEFINEDID as RECIPIENTID, C.EMAILLISTID, CAST(0 as int) as SUBTYPE
  from CONDITIONALCONTENTRECIPIENT C
  inner join dbo.EMAILLIST EL on EL.ID = C.EMAILLISTID
  where C.EMAILJOBID = @EMAILJOBID
  and C.JOBKEY = @JOBKEY
  and EL.ORIGIN = 3


  union all

  select C.APPEALEMAILLISTMEMBERID as RECIPIENTID, C.EMAILLISTID, CAST(0 as int) as SUBTYPE
  from CONDITIONALCONTENTRECIPIENT C
  inner join dbo.EMAILLIST EL on EL.ID = C.EMAILLISTID
  where C.EMAILJOBID = @EMAILJOBID
  and C.JOBKEY = @JOBKEY
  and EL.ORIGIN = 5

  union all

  select C.USERID as RECIPIENTID, C.EMAILLISTID, CAST(0 as int) as SUBTYPE
  from CONDITIONALCONTENTRECIPIENT C
  inner join dbo.EMAILLIST EL on EL.ID = C.EMAILLISTID
  where C.EMAILJOBID = @EMAILJOBID
  and C.JOBKEY = @JOBKEY
  and EL.ORIGIN = 0
  and EL.DATASOURCEID = 101

  union all

  --anonymous subscribers

  select C.EMAILLIST_SUBSCRIPTIONID as RECIPIENTID, C.EMAILLISTID, CAST(1 as int) as SUBTYPE
  from CONDITIONALCONTENTRECIPIENT C
  inner join dbo.EMAILLIST EL on EL.ID = C.EMAILLISTID
  inner join dbo.EmailList_Subscription ELS on ELS.ID = C.EMAILLIST_SUBSCRIPTIONID
  where C.EMAILJOBID = @EMAILJOBID
  and ELS.USERID is null
  and C.JOBKEY = @JOBKEY
  and EL.ORIGIN = 0
  and EL.DATASOURCEID = 100

  union all

  --non-linked user subscribers

  select C.USERID as RECIPIENTID, C.EMAILLISTID, CAST(2 as int) as SUBTYPE
  from CONDITIONALCONTENTRECIPIENT C
  inner join dbo.EMAILLIST EL on EL.ID = C.EMAILLISTID
  inner join dbo.EmailList_Subscription ELS on ELS.ID = C.EMAILLIST_SUBSCRIPTIONID
  inner join dbo.ClientUsers CU on CU.ID = C.USERID
  where C.EMAILJOBID = @EMAILJOBID
  and C.BOSPID is null
  and C.JOBKEY = @JOBKEY
  and EL.ORIGIN = 0
  and EL.DATASOURCEID = 100

  union all

  --linked user subscribers

  select BOSP.BACKOFFICERECORDID as RECIPIENTID, C.EMAILLISTID, CAST(0 as int) as SUBTYPE
  from CONDITIONALCONTENTRECIPIENT C
  inner join dbo.EMAILLIST EL on EL.ID = C.EMAILLISTID
  inner join dbo.BACKOFFICESYSTEMPEOPLE BOSP on BOSP.ID = C.BOSPID
  where C.EMAILJOBID = @EMAILJOBID
  and C.JOBKEY = @JOBKEY
  and EL.ORIGIN = 0
  and EL.DATASOURCEID = 100

end