USP_MKTSEGMENTATIONEXPORTPROCESSSTATUSOUTPUT_GETDATALIST

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

  declare @TABLENAME nvarchar(250);
  declare @SQL 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));

  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) +
      '  [MKTEXPORTDEFINITION].[CHANNELCODE] as [CHANNELCODE],' + char(13) +
      '  [MKTEXPORTDEFINITION].[NAME] as [EXPORTDEFINITION],' + char(13) +
      '  [MKTEXPORTDEFINITION].[CHANNEL] as [CHANNEL],' + 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) +
      '  [BUSINESSPROCESSOUTPUTSUMMARY].[STATUSCODE],' + char(13) +
      '  [BUSINESSPROCESSOUTPUTSUMMARY].[STATUSDATE],' + char(13) +
      '  [BUSINESSPROCESSOUTPUTSUMMARY].[STATUSMESSAGE],' + 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) +
      '  then 1 else 0 end) as [WORDMERGEENABLED],' + char(13) +
      '  dbo.[UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED]() as [NETCOMMUNITYENABLED],' + char(13) +
      '  [MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID],' + char(13) +
      '  [BUSINESSPROCESSOUTPUTSUMMARY].[NETCOMMUNITYEMAILJOBID]' + char(13) +
      'from ' + char(13) +
      '  dbo.[' + @TABLENAME + '] as [BUSINESSPROCESSOUTPUTSUMMARY]' + char(13) +
      '  inner join dbo.[MKTEXPORTDEFINITION] on [MKTEXPORTDEFINITION].[ID] = [BUSINESSPROCESSOUTPUTSUMMARY].[EXPORTDEFINITIONID]' + char(13) +
      ' order by ' + char(13) +
      '  [MKTEXPORTDEFINITION].[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, [CHANNELCODE] tinyint, [EXPORTDEFINITION] nvarchar(255), [CHANNEL] nvarchar(100), [PACKAGECOUNT] integer, [PACKAGES] nvarchar(max), [QUANTITY] integer, [STATUSCODE] tinyint, [STATUSDATE] datetime, [STATUSMESSAGE] nvarchar(255), [WORDMERGEENABLED] bit, [NETCOMMUNITYENABLED] bit, [NETCOMMUNITYDATASOURCEID] integer, [NETCOMMUNITYEMAILJOBID] integer)

  --select * from @SPWRAP;


  return 0;