USP_DATAFORMTEMPLATE_VIEW_MKTCOMMUNICATIONEFFORTADDRESSPROCESSING

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTCOMMUNICATIONEFFORTADDRESSPROCESSING]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ADDRESSPROCESSINGLOCKED bit = null output,
  @USEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTION nvarchar(100) = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null output,
  @NAMEFORMATPARAMETER nvarchar(100) = null output,
  @SHOWLOCKEDFIELDS bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,

    @USEADDRESSPROCESSING = [MKTSEGMENTATION].[USEADDRESSPROCESSING],
    @ADDRESSPROCESSINGOPTION = (select [ADDRESSPROCESSINGOPTION].[NAME] from dbo.[ADDRESSPROCESSINGOPTION] where [ADDRESSPROCESSINGOPTION].[ID] = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID]),
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
    @NAMEFORMATPARAMETER = (select [NAMEFORMATPARAMETER].[NAME] from dbo.[NAMEFORMATPARAMETER] where [NAMEFORMATPARAMETER].[ID] = [MKTSEGMENTATION].[NAMEFORMATPARAMETERID]),
    @SHOWLOCKEDFIELDS = isnull([MKTCOMMUNICATIONEFFORTUSERSETTINGS].[SHOWLOCKEDFIELDS], 0)
  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTCOMMUNICATIONEFFORTUSERSETTINGS] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONEFFORTUSERSETTINGS].[SEGMENTATIONID] and [MKTCOMMUNICATIONEFFORTUSERSETTINGS].[APPUSERID] = @CURRENTAPPUSERID
  where [MKTSEGMENTATION].[ID] = @ID;

  if @DATALOADED = 1
    begin

      /* Get template locked settings */  
      if exists(select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @ID)
        begin

          select
            @ADDRESSPROCESSINGLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ADDRESSPROCESSINGLOCKED]
          from dbo.[MKTCOMMUNICATIONTEMPLATE]
          inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
          where [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @ID;

        end

    end

  return 0;