USP_DATAFORMTEMPLATE_VIEW_MKTCOMMUNICATIONEFFORTBASICINFORMATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MKTCOMMUNICATIONEFFORTBASICINFORMATION
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TEMPLATENAME nvarchar(100) = null output,
  @EFFORTTEMPLATETYPECODE tinyint = null output,
  @EFFORTNAME nvarchar(100) = null output,
  @NAMESCHEME nvarchar(100) = null output,
  @EFFORTDESCRIPTION nvarchar(255) = null output,
  @EFFORTDESCRIPTIONLOCKED bit = null output,
  @EFFORTAPPEAL nvarchar(255) = null output,
  @EFFORTAPPEALLOCKED bit = null output,
  @EFFORTCHANNELCODE tinyint = null output,
  @EFFORTCHANNELCODELOCKED bit = null output,
  @EFFORTSITE nvarchar(255) = null output,
  @EFFORTDUEDATE datetime = null output,
  @EFFORTLAUNCHDATE datetime = null output,
  @EFFORTALLOWRESERVINGFINDERNUMBERS bit = null output,
  @EFFORTALLOWRESERVINGFINDERNUMBERSLOCKED bit = null output,
  @EFFORTALLOWSPECIFYBUDGET bit = null output,
  @EFFORTALLOWSPECIFYBUDGETLOCKED bit = null output,
  @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS bit = null output,
  @EFFORTALLOWEXCLUDEPREVIOUSEFFORTSLOCKED bit = null output,
  @SHOWLOCKEDFIELDS bit = null output,
  @PLANPATH nvarchar(405) = null output
)
as
  set nocount on;

  -- be sure to set this, in case the select returns no rows

  set @DATALOADED = 0;

  declare @BBECAPPEALID nvarchar(36);
  declare @BBECAPPEALGUID uniqueidentifier;
  declare @BBECAPPEALDESCRIPTION nvarchar(100);

  --Make sure the appeal name and description are up-to-date in our table...

  exec dbo.[USP_MKTSEGMENTATIONACTIVATE_UPDATEAPPEALINFO] @ID;

  select 
    @BBECAPPEALID = [APPEALSYSTEMID]
  from dbo.[MKTSEGMENTATIONACTIVATE] 
  where [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @ID
  and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1;

  begin try
    set @BBECAPPEALGUID = convert(uniqueidentifier, @BBECAPPEALID);
  end try
  begin catch
    set @BBECAPPEALGUID = null;
  end catch

  if not @BBECAPPEALGUID is null
    select
      @BBECAPPEALDESCRIPTION = [NAME]
    from dbo.[APPEAL] 
    where [ID] = @BBECAPPEALGUID;

  select
    @DATALOADED = 1,
    @TEMPLATENAME = [MKTCOMMUNICATIONTEMPLATE].[NAME],
    --For old mailings without templates, translate the old mailing types into the new template types...

    @EFFORTTEMPLATETYPECODE = isnull([MKTCOMMUNICATIONTEMPLATE].[TEMPLATETYPECODE], (
                                case [MKTSEGMENTATION].[MAILINGTYPECODE]
                                  when 0 then 0  --Direct marketing effort = Constituent

                                  when 1 then null  --Marketing acknowledgement = ?

                                  when 2 then null  --Membership effort = ?

                                  when 3 then null  --Sponsorship effort = ?

                                  when 4 then null  --Public media effort = ?

                                  when 5 then null  --Communication revenue = ?

                                end
                              )),
    @EFFORTNAME = [MKTSEGMENTATION].[NAME],
    @NAMESCHEME = [MKTCOMMUNICATIONNAMESCHEME].[NAME],
    @EFFORTDESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
    @EFFORTDESCRIPTIONLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTIONLOCKED],
    @EFFORTAPPEAL = isnull(stuff(
                            (
                              select ', ' + case when [APPEALSYSTEMID] = @BBECAPPEALID then @BBECAPPEALDESCRIPTION else [APPEALDESCRIPTION] end 
                              from dbo.[MKTSEGMENTATIONACTIVATE] 
                              where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] 
                              for xml path(''), type
                            ).value('.', 'varchar(max)')
                            , 1, 2, ''
                          ), ''),
    @EFFORTAPPEALLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED],
    @EFFORTCHANNELCODE = [MKTSEGMENTATION].[CHANNELCODE],
    @EFFORTCHANNELCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODELOCKED],
    @EFFORTSITE = dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]),
    @EFFORTDUEDATE = [MKTSEGMENTATION].[DUEDATE],
    @EFFORTLAUNCHDATE = [MKTSEGMENTATION].[MAILDATE],
    @EFFORTALLOWRESERVINGFINDERNUMBERS = [MKTSEGMENTATION].[ALLOWRESERVINGFINDERNUMBERS],
    @EFFORTALLOWRESERVINGFINDERNUMBERSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERSLOCKED],
    @EFFORTALLOWSPECIFYBUDGET = [MKTSEGMENTATION].[ALLOWSPECIFYBUDGET],
    @EFFORTALLOWSPECIFYBUDGETLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGETLOCKED],
    @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS = [MKTSEGMENTATION].[ALLOWEXCLUDEPREVIOUSEFFORTS],
    @EFFORTALLOWEXCLUDEPREVIOUSEFFORTSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED],
    @PLANPATH = dbo.[UFN_MKTMARKETINGPLANITEM_PATH]([MKTSEGMENTATION].[MARKETINGPLANITEMID], 1)
  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATE].[ID] = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID]
  left join dbo.[MKTCOMMUNICATIONNAMESCHEME] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONNAMESCHEME].[MKTSEGMENTATIONID]
  where [MKTSEGMENTATION].[ID] = @ID;

  if @DATALOADED = 1
    select
      @SHOWLOCKEDFIELDS = [SHOWLOCKEDFIELDS]
    from dbo.[MKTCOMMUNICATIONEFFORTUSERSETTINGS]
    where [SEGMENTATIONID] = @ID
    and [APPUSERID] = @CURRENTAPPUSERID;

  return 0;