USP_REPORT_DIRECTMARKETINGEFFORTBATCHVALIDATIONREPORT

create procedure dbo.[USP_REPORT_DIRECTMARKETINGEFFORTBATCHVALIDATIONREPORT]
(
  @BATCHID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  select
    [MKTSEGMENTATION].[NAME] as [MAILINGNAME],
    [BATCHDIRECTMARKETINGEFFORT].[SOURCECODE],
    (case when [MKTSEGMENT].[ID] is not null then [MKTSEGMENT].[NAME] else [BATCHDIRECTMARKETINGEFFORTSEGMENT].[NAME] end) as [SEGMENTNAME],
    [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODE] as [SEGMENTCODE],
    (case when [MKTSEGMENTATIONTESTSEGMENT].[ID] is not null then dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]) else [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[NAME] end) as [TESTSEGMENTNAME],
    (case when [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[ID] is not null then [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[CODE] else [BATCHDIRECTMARKETINGEFFORT].[SEGMENTTESTCODE] end) as [TESTSEGMENTCODE],
    (case when [MKTPACKAGE].[ID] is not null then [MKTPACKAGE].[NAME] else [BATCHDIRECTMARKETINGEFFORTPACKAGE].[NAME] end) as [PACKAGENAME],
    [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODE] as [PACKAGECODE],
    (case when [MKTLIST].[ID] is not null then [MKTLIST].[NAME] else [BATCHDIRECTMARKETINGEFFORTLIST].[NAME] end) as [LISTNAME],
    [BATCHDIRECTMARKETINGEFFORTLIST].[CODE] as [LISTCODE]
  from dbo.[BATCHDIRECTMARKETINGEFFORT]
  inner join dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTSEGMENTID]
  inner join dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] on [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTPACKAGEID]
  left join dbo.[BATCHDIRECTMARKETINGEFFORTLIST] on [BATCHDIRECTMARKETINGEFFORTLIST].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTLISTID]
  left join dbo.[BATCHDIRECTMARKETINGEFFORTTESTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [BATCHDIRECTMARKETINGEFFORT].[SEGMENTATIONID]
  left join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORTSEGMENT].[MKTSEGMENTID]
  left join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[MKTPACKAGEID]
  left join dbo.[MKTLIST] on [MKTLIST].[ID] = [BATCHDIRECTMARKETINGEFFORTLIST].[MKTLISTID]
  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[MKTSEGMENTATIONTESTSEGMENTID]
  where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
  order by [BATCHDIRECTMARKETINGEFFORT].[SEQUENCE];

  return 0;