USP_DATALIST_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE

CREATE procedure dbo.[USP_DATALIST_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
(
  @ACKNOWLEDGEMENTMAILINGTEMPLATEID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  select
    [MR].[ID],
    [MR].[SEQUENCE],
    [MKTSEGMENT].[NAME] as [SEGMENT],
    [MKTPACKAGE].[NAME] as [PACKAGE],
    [MKTPACKAGE].[CHANNEL],
    (case when [LETTERCODE].[INCLUDESRECEIPT] = 1 then 'Yes' else 'No' end) as [INCLUDESRECEIPT],
    [MKTASKLADDER].[NAME] as [ASKLADDER],
    dbo.[UFN_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_BUILDSOURCECODE]([MR].[ID], [MKTSEGMENT].[QUERYVIEWCATALOGID], default) as [SOURCECODE],
    (case when [MR].[MAXDAYSCHECKED] = 0 then 'N/A' else cast([MR].[MAXDAYS] as nvarchar(10)) end) as [MAXDAYS],
    (case when [MR].[MINQUANTITYCHECKED] = 0 then 'N/A' else cast([MR].[MINQUANTITY] as nvarchar(10)) end) as [MINQUANTITY],
    [MR].[RESPONSERATE],
    [MR].[GIFTAMOUNT],
    [MKTSEGMENT].[ID] [SEGMENTID],
    [MR].[BASECURRENCYID],
    [MR].[EXCLUDE]
  from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] as [MR]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MR].[SEGMENTID]
  left join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MR].[PACKAGEID]
  left join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
  left join dbo.[MKTASKLADDER] on [MKTASKLADDER].[ID] = [MR].[ASKLADDERID]
  where (@ACKNOWLEDGEMENTMAILINGTEMPLATEID is null or [MR].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID)
  order by [MR].[SEQUENCE];

  return 0;