USP_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_GETDATALIST

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_GETDATALIST]
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier
)
as
    set nocount on;

  declare @TABLENAME nvarchar(250);
  declare @SQL nvarchar(max);
  declare @EXCLUSIONSQL nvarchar(max);

  declare @BUSINESSPROCESSPARAMETERSETID uniqueidentifier;

  select
    @BUSINESSPROCESSPARAMETERSETID = [BUSINESSPROCESSPARAMETERSETID]
  from dbo.[BUSINESSPROCESSSTATUS]
  where [ID] = @BUSINESSPROCESSSTATUSID;

  select @TABLENAME = [TABLENAME] from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID and upper([TABLEKEY]) = upper(convert(nvarchar(36), @BUSINESSPROCESSSTATUSID));

  if exists (select top 1 1
             from sys.columns as c
             inner join sys.objects as o on o.object_id = c.object_id
             where o.name = @TABLENAME and o.type = 'U' and c.name = 'EXCLUSION')
    set @EXCLUSIONSQL = '  [BUSINESSPROCESSOUTPUTSUMMARY].[EXCLUSION]';
  else
    set @EXCLUSIONSQL = '  cast(0 as bit) as [EXCLUSION]';

  set @SQL = 
      'select' + char(13) +
      '  (select top 1 [ID] from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID and upper([TABLEKEY]) = upper([BUSINESSPROCESSOUTPUTSUMMARY].[TABLEKEY])) as [ID],' + char(13) +      
      '  @BUSINESSPROCESSPARAMETERSETID as [PARAMETERSETID],' + char(13) +
      '  [BUSINESSPROCESSOUTPUTSUMMARY].[TABLEKEY],' + char(13) +
      '  [BUSINESSPROCESSOUTPUTSUMMARY].[EXPORTDEFINITIONID],' + char(13) +
      '  [EXPORTDEFINITION].[NAME] as [EXPORTDEFINITION],' + char(13) +
      '  convert(integer, cast([BUSINESSPROCESSOUTPUTSUMMARY].[PACKAGES].query(''count(/PACKAGES/ITEM/ID)'') as nvarchar(10))) as [PACKAGECOUNT],' + char(13) +
      '  dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESS_GETPACKAGELISTFROMXML]([BUSINESSPROCESSOUTPUTSUMMARY].[PACKAGES]) as [PACKAGES],' + char(13) +
      '  [BUSINESSPROCESSOUTPUTSUMMARY].[QUANTITY],' + char(13) +
      '  dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_SUMMARYSTATUSCODE]([BUSINESSPROCESSOUTPUTSUMMARY].[STATUS]) as [STATUSCODE],' + char(13) +
      '  isnull((select max(T.c.value(''(STATUSDATE)[1]'', ''datetime'')) from [BUSINESSPROCESSOUTPUTSUMMARY].[STATUS].nodes(''/STATUS/ITEM'') T(c) where T.c.value(''(NETCOMMUNITYEMAILID)[1]'', ''integer'') > 0), [BUSINESSPROCESSOUTPUTSUMMARY].[STATUS].value(''(/STATUS/@StatusDate)[1]'', ''datetime'')) as [STATUSDATE],' + char(13) +
      '  convert(bit, case when exists (' + char(13) +
      '    select top 1 1' + char(13) +
      '    from [BUSINESSPROCESSOUTPUTSUMMARY].[PACKAGES].nodes(''/PACKAGES/ITEM'') T(c)' + char(13) +
      '    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = T.c.value(''(ID)[1]'', ''uniqueidentifier'')' + char(13) +
      '    inner join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]' + char(13) +
      '    where [LETTERCODE].[WORDFILE] is not null)' + char(13) +
      '  then 1 else 0 end) as [WORDMERGEENABLED],' + char(13) +
      '  convert(bit, case when exists (' + char(13) +
      '    select top 1 1' + char(13) +
      '    from [BUSINESSPROCESSOUTPUTSUMMARY].[PACKAGES].nodes(''/PACKAGES/ITEM'') T(c)' + char(13) +
      '    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = T.c.value(''(ID)[1]'', ''uniqueidentifier'')' + char(13) +
      '    where [MKTPACKAGE].[NETCOMMUNITYTEMPLATEID] > 0)' + char(13) +
      '  then 1 else 0 end) as [NETCOMMUNITYEMAILJOBENABLED],' + char(13) +
      '  convert(bit, ' + char(13) +
      '    case when exists (' + char(13) +
      '    select top 1 1' + char(13) +
      '    from [BUSINESSPROCESSOUTPUTSUMMARY].[STATUS].nodes(''/STATUS/ITEM'') T(c)' + char(13) +
      '    where T.c.value(''(NETCOMMUNITYEMAILID)[1]'', ''integer'') > 0)' + char(13) +
      '  then 1 else 0 end) as [NETCOMMUNITYEMAILJOBSTARTED],' + char(13) +
      '  convert(bit, case when [PRESORTREPORT] is not null then 1 else 0 end) as [PRESORTREPORTENABLED],' + char(13) +
      '  convert(bit, case when (select [LABELTEMPLATE] from dbo.[BUSINESSPROCESSSTATUS] where [ID] = @BUSINESSPROCESSSTATUSID) is not null then 1 else 0 end) as [LABELMERGEENABLED],' + char(13) +
      @EXCLUSIONSQL + char(13) +
      'from dbo.[' + @TABLENAME + '] as [BUSINESSPROCESSOUTPUTSUMMARY]' + char(13) +
      'inner join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [BUSINESSPROCESSOUTPUTSUMMARY].[EXPORTDEFINITIONID]' + char(13) +
      'order by [EXPORTDEFINITION].[NAME];';

  exec sp_executesql @SQL, N'@BUSINESSPROCESSPARAMETERSETID uniqueidentifier, @BUSINESSPROCESSSTATUSID uniqueidentifier', @BUSINESSPROCESSPARAMETERSETID=@BUSINESSPROCESSPARAMETERSETID, @BUSINESSPROCESSSTATUSID=@BUSINESSPROCESSSTATUSID; 

  -- use this to get the SPWrap Code Generator to update the wrapper code:

  -- 1. make the SPWRAP table's shape match that of the SQL above

  -- 2. uncomment the two lines below and load the spec

  -- 3. run the SPWrap Code Generator

  -- 4. comment the two lines below out again and load the spec

  -- make sure these lines are commented out before generating a revision for this spec!

  --declare @SPWRAP table ([ID] uniqueidentifier, [PARAMETERSETID] uniqueidentifier, [TABLEKEY] nvarchar(50), [EXPORTDEFINITIONID] uniqueidentifier, [EXPORTDEFINITION] nvarchar(255), [PACKAGECOUNT] integer, [PACKAGES] nvarchar(max), [QUANTITY] integer, [STATUSCODE] tinyint, [STATUSDATE] datetime, [WORDMERGEENABLED] bit, [NETCOMMUNITYEMAILJOBENABLED] bit, [NETCOMMUNITYEMAILJOBSTARTED] bit, [PRESORTREPORTENABLED] bit, [LABELMERGEENABLED] bit, [EXCLUSION] bit)

  --select * from @SPWRAP;


  return 0;