USP_DATAFORMTEMPLATE_VIEW_INVITATION

The load procedure used by the view dataform template "Invitation View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT	Name
@EVENTID	uniqueidentifier	INOUT	EVENTID
@EVENTNAME	nvarchar(100)	INOUT	Event
@BUSINESSPROCESSVIEWID	uniqueidentifier	INOUT	BUSINESSPROCESSVIEWID
@DESCRIPTION	nvarchar(255)	INOUT	Description
@BUSINESSPROCESSCATALOGID	uniqueidentifier	INOUT	BUSINESSPROCESSCATALOGID
@PARAMETERSID	uniqueidentifier	INOUT	PARAMETERSID
@ACTIVATEPROCESSID	uniqueidentifier	INOUT	ACTIVATEPROCESSID
@EXPORTQUANTITY	int	INOUT	EXPORTQUANTITY
@EXPORTSTATUSCODE	tinyint	INOUT	EXPORTSTATUSCODE
@NETCOMMUNITYEMAILJOBENABLED	bit	INOUT	NETCOMMUNITYEMAILJOBENABLED
@NETCOMMUNITYLINKESTABLISHED	bit	INOUT	NETCOMMUNITYLINKESTABLISHED
@NETCOMMUNITYEMAILJOBSTARTED	bit	INOUT	NETCOMMUNITYEMAILJOBSTARTED
@EXPORTPROCESSSTATUSID	uniqueidentifier	INOUT	EXPORTPROCESSSTATUSID

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_INVITATION
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(100) = null output,
  @EVENTID uniqueidentifier = null output,
  @EVENTNAME nvarchar(100) = null output,
  @BUSINESSPROCESSVIEWID uniqueidentifier = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @BUSINESSPROCESSCATALOGID uniqueidentifier = null output,
  @PARAMETERSID uniqueidentifier = null output,
  @ACTIVATEPROCESSID uniqueidentifier = null output,
  @EXPORTQUANTITY int = null output,
  @EXPORTSTATUSCODE tinyint = null output,
  @NETCOMMUNITYEMAILJOBENABLED bit = null output,
  @NETCOMMUNITYLINKESTABLISHED bit = null output,
  @NETCOMMUNITYEMAILJOBSTARTED bit = null output,
  @EXPORTPROCESSSTATUSID uniqueidentifier = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  declare @ACTIVATEPROCESSSTATUSID uniqueidentifier;

  select
    @DATALOADED = 1,
    @NAME = INVITATION.NAME,
    @EVENTID = INVITATION.EVENTID,
    @EVENTNAME = dbo.UFN_EVENT_GETNAME(INVITATION.EVENTID),
    @BUSINESSPROCESSVIEWID = coalesce(INVITATION.BUSINESSPROCESSVIEWID, '00000000-0000-0000-0000-000000000000'),
    @DESCRIPTION = INVITATION.DESCRIPTION,
    @BUSINESSPROCESSCATALOGID = '116332AF-BB79-4608-9709-4203BD2BA318',
    @PARAMETERSID = MKTSEGMENTATIONEXPORTPROCESS.ID,
    @ACTIVATEPROCESSID = MKTSEGMENTATIONACTIVATEPROCESS.ID
  from dbo.INVITATION
  left join dbo.MKTSEGMENTATIONACTIVATEPROCESS on INVITATION.ID = MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID
  left join dbo.MKTSEGMENTATIONEXPORTPROCESS on INVITATION.ID = MKTSEGMENTATIONEXPORTPROCESS.SEGMENTATIONID
  where INVITATION.ID = @ID;

  if @DATALOADED = 1
    begin
      -- Get the latest activate and export process runs

      select top(1)
        @ACTIVATEPROCESSSTATUSID = MKTSEGMENTATIONACTIVATEPROCESSSTATUS.ID,
        @EXPORTPROCESSSTATUSID = MKTSEGMENTATIONACTIVATEEXPORTLINK.EXPORTPROCESSSTATUSID
      from dbo.MKTSEGMENTATIONACTIVATEPROCESSSTATUS
      left join dbo.MKTSEGMENTATIONACTIVATEPROCESS 
        on MKTSEGMENTATIONACTIVATEPROCESS.ID = MKTSEGMENTATIONACTIVATEPROCESSSTATUS.PARAMETERSETID
      left join dbo.MKTSEGMENTATIONACTIVATEEXPORTLINK
        on MKTSEGMENTATIONACTIVATEPROCESSSTATUS.ID = MKTSEGMENTATIONACTIVATEEXPORTLINK.ID
      where MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = @ID
      order by MKTSEGMENTATIONACTIVATEPROCESSSTATUS.DATEADDED desc;

      if @ACTIVATEPROCESSSTATUSID is null
        set @ACTIVATEPROCESSSTATUSID = '00000000-0000-0000-0000-000000000000';
      if @EXPORTPROCESSSTATUSID is null
        set @EXPORTPROCESSSTATUSID = '00000000-0000-0000-0000-000000000000';

      -- Check to see if the export process has an email package to enable the NetCommunity jobs

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

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

      set @SQL = 
        'select top(1)
          @EXPORTQUANTITY = BUSINESSPROCESSOUTPUTSUMMARY.QUANTITY,
          @EXPORTSTATUSCODE = dbo.UFN_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_SUMMARYSTATUSCODE(BUSINESSPROCESSOUTPUTSUMMARY.STATUS),
          @NETCOMMUNITYEMAILJOBENABLED = case when exists (
            select top (1)
              1
            from
              BUSINESSPROCESSOUTPUTSUMMARY.PACKAGES.nodes(''/PACKAGES/ITEM'') T(c)
            inner join
              dbo.MKTPACKAGE on MKTPACKAGE.ID = T.c.value(''(ID)[1]'', ''uniqueidentifier'')
            where
              MKTPACKAGE.NETCOMMUNITYTEMPLATEID > 0)
            then 1 else 0 end,
          @NETCOMMUNITYEMAILJOBSTARTED = case when exists (
            select top(1)
              1
            from
              BUSINESSPROCESSOUTPUTSUMMARY.STATUS.nodes(''/STATUS/ITEM'') T(c)
            where
              T.c.value(''(NETCOMMUNITYEMAILID)[1]'', ''integer'') > 0)
            then 1 else 0 end,
          @NETCOMMUNITYLINKESTABLISHED = dbo.UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED()
        from
          dbo.' + @TABLENAME + ' as BUSINESSPROCESSOUTPUTSUMMARY;';

      exec sp_executesql @SQL, N'@EXPORTQUANTITY int output, @EXPORTSTATUSCODE tinyint output, @NETCOMMUNITYEMAILJOBENABLED bit output, @NETCOMMUNITYEMAILJOBSTARTED bit output, @NETCOMMUNITYLINKESTABLISHED bit output',
        @EXPORTQUANTITY = @EXPORTQUANTITY output,
        @EXPORTSTATUSCODE = @EXPORTSTATUSCODE output,
        @NETCOMMUNITYEMAILJOBENABLED = @NETCOMMUNITYEMAILJOBENABLED output,
        @NETCOMMUNITYEMAILJOBSTARTED = @NETCOMMUNITYEMAILJOBSTARTED output,
        @NETCOMMUNITYLINKESTABLISHED = @NETCOMMUNITYLINKESTABLISHED output;
    end

          return 0;