USP_MKTEXPORTDEFINITION_SAVE

CREATE procedure dbo.[USP_MKTEXPORTDEFINITION_SAVE]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @MAILINGTYPECODE tinyint,
  @POSTALEXPORT bit,
  @POSTALTEMPLATEID uniqueidentifier = null,
  @PREVIEWDATALISTCATALOGID uniqueidentifier = null,
  @NETCOMMUNITYDATASOURCEID integer = null,
  @SEGMENTATIONID uniqueidentifier = null,
  @CHANGEDBYCULTURE nvarchar(30) = null,
  @ISSYSTEM bit = 0
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

  begin try
    if @CHANGEAGENTID is null exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    if @ID is null
        set @ID = newID();

    /* save the export definition */
    if not exists(select [ID] from dbo.[MKTEXPORTDEFINITION] where [ID] = @ID)
      begin
        insert into dbo.[MKTEXPORTDEFINITION] (
          [ID],
          [NAME],
          [MAILINGTYPECODE],
          [POSTALEXPORT],
          [POSTALTEMPLATEID],
          [PREVIEWDATALISTCATALOGID],
          [NETCOMMUNITYDATASOURCEID],
          [CHANGEDBYCULTURE],
          [ISSYSTEM],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @ID,
          @NAME,
          @MAILINGTYPECODE,
          @POSTALEXPORT,
          @POSTALTEMPLATEID,
          @PREVIEWDATALISTCATALOGID,
          @NETCOMMUNITYDATASOURCEID,
          @CHANGEDBYCULTURE,
          @ISSYSTEM,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end
    else
      begin
        /* update the export definition - note TYPECODE cannot be changed on an existing export definition */
        update
          dbo.[MKTEXPORTDEFINITION]
        set
          [NAME] = @NAME,
          [POSTALEXPORT] = @POSTALEXPORT,
          [POSTALTEMPLATEID] = @POSTALTEMPLATEID,
          [PREVIEWDATALISTCATALOGID] = @PREVIEWDATALISTCATALOGID,
          [NETCOMMUNITYDATASOURCEID] = @NETCOMMUNITYDATASOURCEID,
          [CHANGEDBYCULTURE] = @CHANGEDBYCULTURE,
          [ISSYSTEM] = @ISSYSTEM,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @ID;

        /* delete all the old output fields so we can save the updated ones after this */
        delete from dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD]
        where [EXPORTDEFINITIONID] = @ID
        and (@SEGMENTATIONID is null 
        or [QUERYVIEWCATALOGID] in (select [ID] from dbo.[UFN_MKTEXPORTDEFINITION_GETSPECIALQUERYVIEWIDS]())
        or [QUERYVIEWCATALOGID] in 
        (
          select
            case when @MAILINGTYPECODE = 0 -- Appeal

                 then isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [RS].[QUERYVIEWCATALOGID])
                 -- else is valid for Acknowledgement, Membership Mailings

                 else [RS].[QUERYVIEWCATALOGID] 
            end
          from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
          left outer join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [RS].[QUERYVIEWCATALOGID]
        )
        or (@MAILINGTYPECODE = 1 and [QUERYVIEWCATALOGID] in 
        (
          select 
            [GRS].[QUERYVIEWCATALOGID]
          from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
          inner join dbo.[MKTGIFTRECORDSOURCE] [GRS] on [GRS].[ID] = [RS].[QUERYVIEWCATALOGID]
        ))
        or (@MAILINGTYPECODE = 2 and [QUERYVIEWCATALOGID] in 
        (
          select 
            [MRS].[QUERYVIEWCATALOGID]
          from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
          inner join dbo.[MKTMEMBERSHIPRECORDSOURCE] [MRS] on [MRS].[ID] = [RS].[QUERYVIEWCATALOGID]
        ))
        or (@MAILINGTYPECODE = 3 and [QUERYVIEWCATALOGID] in 
        (
  select 
            [SRS].[QUERYVIEWCATALOGID]
          from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) [RS]
          inner join dbo.[MKTSPONSORSHIPRECORDSOURCE] [SRS] on [SRS].[ID] = [RS].[QUERYVIEWCATALOGID]
        )));
      end
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;