USP_APPEALMAILING_EDIT_LOAD

CREATE procedure dbo.[USP_APPEALMAILING_EDIT_LOAD]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  set nocount on;

  declare @ISSYSADMIN bit = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);

  declare @KPITABLE as table
  (
    [KPICATALOGID] uniqueidentifier,
    [SELECTED] bit,
    [NAME] nvarchar(255),
    [GOALTYPECODE] tinyint,
    [DEFAULT] bit,
    [LOCKED] bit,
    [TEMPLATETYPECODE] tinyint
  );

  declare @DESIGNATIONS xml;
  declare @REVENUETYPECODES xml;

  insert into @KPITABLE
    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_GETFIELDS] @ID, 1, @CURRENTAPPUSERID;

  select
    [APPEALMAILING].[TSLONG],
    [MKTSEGMENTATION].[NAME],
    [MKTSEGMENTATION].[DESCRIPTION],
    [MKTSEGMENTATION].[MAILDATE],
    [MKTSEGMENTATION].[SITEID],
    [MKTSEGMENTATIONBUDGET].[FIXEDCOST],
    [MKTSEGMENTATIONSEGMENT].[PACKAGEID] as [MKTPACKAGEID],
    (select [ID] from dbo.[RECORDTYPE] where upper([NAME]) = 'CONSTITUENT') as [CONSTITUENTRECORDTYPEID],
    [MKTSEGMENTSELECTION].[SELECTIONID] as [IDSETREGISTERID],
    [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
    cast((select (case when sum(case when isnull([MKTRECORDSOURCEFIELDMAPPINGS].[HOUSEHOLDIDFIELD],'') = '' then 0 else 1 end) = 0 then 0 else 1 end)
          from dbo.[MKTRECORDSOURCE]
          left join dbo.[MKTRECORDSOURCEFIELDMAPPINGS] on [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = [MKTRECORDSOURCE].[ID]
          where dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTRECORDSOURCE].[ID]) = 1) as bit) as [ENABLEHOUSEHOLDING],
    [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
    [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
    [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
    [BUSINESSPROCESSCOMMPREF].[DATETYPECODE] as [EXCLUSIONDATETYPECODE],
    [BUSINESSPROCESSCOMMPREF].[ASOFDATE] as [EXCLUSIONASOFDATE],
    [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
    [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
    (case when @ISSYSADMIN = 1 then @ISSYSADMIN else dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, '2EEC593D-06B1-49E0-9031-A4076B07081C') end) as [CANUPDATEEXCLUDEDECEASED],
    (case when @ISSYSADMIN = 1 then @ISSYSADMIN else dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'C0E02A6F-DF46-460C-ACA2-CC31C9C11BFC') end) as [CANUPDATEEXCLUDEINACTIVE],
    [MKTSEGMENTATION].[USEADDRESSPROCESSING],
    [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
    [MKTSEGMENTATION].[NAMEFORMATPARAMETERID],
    [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
    [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
    [APPEALMAILING].[LABELFILENAME],
    [MKTSEGMENTATION].[CREATEOUTPUTIDSET],
    [MKTSEGMENTATION].[OUTPUTIDSETNAME],
    [MKTSEGMENTATION].[OVERWRITEOUTPUTIDSET],
    [MKTSEGMENTATION].[ACTIVE],
    dbo.[UFN_BUSINESSPROCESSINSTANCE_GETOWNER]('22C3D75C-A956-4BFC-A5FD-4B866BAEF509', [MKTSEGMENTATIONACTIVATEPROCESS].[ID]) as [OWNERID],
    [MKTSEGMENTATIONACTIVATEPROCESS].[ID] as [MKTSEGMENTATIONACTIVATEPROCESSID],
    (select 
       [KPICATALOGID],
       [SELECTED],
       [NAME],
       [GOALTYPECODE],
       [DEFAULT]
     from @KPITABLE 
     for xml raw('ITEM'), type, elements, root('ACTIVATIONKPIS'), binary base64) as [ACTIVATIONKPIS],
    [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT],
    [MKTSEGMENTATION].[BASECURRENCYID],
    '0|' + convert(nvarchar(36), [MKTSEGMENTATION].[BASECURRENCYID]) + '|' + isnull(convert(nvarchar(36), [MKTSEGMENTATION].[SITEID]), '00000000-0000-0000-0000-000000000000') as [PACKAGEADDDATAFORMCONTEXT],
    [APPEALMAILING].[APPEALID]
  from dbo.[APPEALMAILING]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [APPEALMAILING].[ID]
  inner join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
  left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
  left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  where [APPEALMAILING].[ID] = @ID

  return 0;