USP_DIRECTMARKETINGEFFORTBATCH_VALIDATEROW

CREATE procedure dbo.[USP_DIRECTMARKETINGEFFORTBATCH_VALIDATEROW]
(
  @BATCHID uniqueidentifier,
  @VALIDATEFORCOMMIT bit = 0,
  @BATCHSEGMENTID uniqueidentifier = null output,
  @BATCHPACKAGEID uniqueidentifier = null output,
  @BATCHLISTID uniqueidentifier = null output,
  @BATCHTESTSEGMENTID uniqueidentifier = null output,
  @EXISTINGLIST bit = 0 output,
  @EXISTINGSEGMENT bit = 0 output,
  @EXISTINGTESTSEGMENT bit = 0 output,
  @EXISTINGPACKAGE bit = 0 output,
  @SEGMENTATIONSEGMENTID uniqueidentifier = null output,
  @SAVELISTRECORD bit = 1 output,
  @SAVETESTSEGMENTRECORD bit = 1 output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @SEGMENTATIONCODEVALUEID uniqueidentifier = null output,
  @SEGMENTATIONCODE nvarchar(10) = '' output,
  @SOURCECODE nvarchar(50) = '' output,
  @RECORDSOURCEID uniqueidentifier = null output,
  @LISTID uniqueidentifier = null output,
  @LISTNAME nvarchar(203) = '' output,
  @LISTDESCRIPTION nvarchar(255) = '' output,
  @LISTCODEVALUEID uniqueidentifier = null output,
  @LISTCODE nvarchar(10) = '' output,
  @LISTCATEGORYCODEID uniqueidentifier = null output,
  @LISTVENDORID uniqueidentifier = null output,
  @SEGMENTID uniqueidentifier = null output,
  @SEGMENTNAME nvarchar(203) = '' output,
  @SEGMENTDESCRIPTION nvarchar(255) = '' output,
  @SEGMENTCODEVALUEID uniqueidentifier = null output,
  @SEGMENTCODE nvarchar(10) = '' output,
  @SEGMENTCATEGORYCODEID uniqueidentifier = null output,
  @SEGMENTTYPECODE tinyint = 1 output,
  @SEGMENTEXPECTEDGIFTAMOUNT money = 0 output,
  @SEGMENTEXPECTEDRESPONSERATE decimal(5,2) = 0.00 output,
  @SEGMENTRENTALQUANTITY int = 0 output,
  @SEGMENTEXCHANGEQUANTITY int = 0 output,
  @SEGMENTRENTALCOST money = 0 output,
  @SEGMENTRENTALCOSTBASISCODE tinyint = 1 output,
  @SEGMENTEXCHANGECOST money = 0 output,
  @SEGMENTEXCHANGECOSTBASISCODE tinyint = 1 output,
  @SEGMENTUSAGECODE tinyint = 1 output,
  @TESTSEGMENTID uniqueidentifier = null output,
  @TESTSEGMENTNAME nvarchar(100) = '' output,
  @TESTSEGMENTDESCRIPTION nvarchar(255) = '' output,
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null output,
  @TESTSEGMENTCODE nvarchar(10) = '' output,
  @TESTSEGMENTEXPECTEDGIFTAMOUNT money = 0 output,
  @TESTSEGMENTEXPECTEDRESPONSERATE decimal(5,2) = 0.00 output,
  @TESTSEGMENTQUANTITY int = 0 output,
  @PACKAGEID uniqueidentifier = null output,
  @PACKAGENAME nvarchar(203) = '' output,
  @PACKAGEDESCRIPTION nvarchar(255) = '' output,
  @PACKAGECODEVALUEID uniqueidentifier = null output,
  @PACKAGECODE nvarchar(10) = '' output,
  @PACKAGECHANNELCODEVALUEID uniqueidentifier = null output,
  @PACKAGECHANNELCODE nvarchar(10) = '' output,
  @PACKAGECATEGORYCODEID uniqueidentifier = null output,
  @PACKAGETYPECODE tinyint = 0 output,
  @PACKAGECOST money = 0 output,
  @PACKAGECOSTDISTRIBUTIONMETHODCODE tinyint = 0 output,
  @BASECURRENCYID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier = null output,
  @LISTSITEID uniqueidentifier = null output,
  @PACKAGESITEID uniqueidentifier = null output,
  @SEGMENTSITEID uniqueidentifier = null output
)
as
  set nocount on;

  declare @LISTRECORDSOURCEID uniqueidentifier;
  declare @LISTBASECURRENCYID uniqueidentifier;
  declare @SEGMENTRECORDSOURCEID uniqueidentifier;
  declare @SEGMENTATIONBASECURRENCYID uniqueidentifier;
  declare @SEGMENTATIONSEGMENTLISTID uniqueidentifier;
  declare @SEGMENTATIONSEGMENTPACKAGEID uniqueidentifier;
  declare @SEGMENTATIONSEGMENTTESTCODE nvarchar(10);
  declare @SEGMENTATIONTESTSEGMENTSEGMENTID uniqueidentifier;
  declare @SEGMENTATIONTESTSEGMENTPACKAGEID uniqueidentifier;
  declare @PACKAGEBASECURRENCYID uniqueidentifier;
  declare @SOURCECODEID uniqueidentifier;
  declare @NAME nvarchar(203);
  declare @CODEVALUEID uniqueidentifier;
  declare @CODE nvarchar(10);
  declare @TYPECODE tinyint;
  declare @SITEID uniqueidentifier;
  declare @LISTTYPECODE tinyint;
  declare @COUNT int;
  declare @CURRENTLISTSITEID uniqueidentifier;
  declare @CURRENTPACKAGESITEID uniqueidentifier;
  declare @CURRENTSEGMENTSITEID uniqueidentifier;
  declare @ISSYSADMIN bit = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);

  begin try
    --Save start point as a fallback for add/edit form usage if errors are encountered

    save transaction rowvalidatestart;

    --These are strictly output params, so don't let the user pass in something that could affect us...

    set @BATCHSEGMENTID = null;
    set @BATCHPACKAGEID = null;
    set @BATCHLISTID = null;
    set @BATCHTESTSEGMENTID = null;
    set @EXISTINGLIST = 0;
    set @EXISTINGSEGMENT = 0;
    set @EXISTINGTESTSEGMENT = 0;
    set @EXISTINGPACKAGE = 0;
    set @SEGMENTATIONSEGMENTID = null;
    set @SAVELISTRECORD = 1;
    set @SAVETESTSEGMENTRECORD = 1;


    /**************************************************/
    /* Set default values for any non-nullable fields */
    /**************************************************/
    if @VALIDATEFORCOMMIT is null
      set @VALIDATEFORCOMMIT = 0;

    if @SEGMENTATIONCODE is null
      set @SEGMENTATIONCODE = '';
    if @SOURCECODE is null
      set @SOURCECODE = '';      

    if @LISTNAME is null
      set @LISTNAME = '';
    if @LISTDESCRIPTION is null
      set @LISTDESCRIPTION = '';
    if @LISTCODE is null
      set @LISTCODE = '';

    if @SEGMENTNAME is null
      set @SEGMENTNAME = '';
    if @SEGMENTDESCRIPTION is null
      set @SEGMENTDESCRIPTION = '';
    if @SEGMENTCODE is null
      set @SEGMENTCODE = '';
    if @SEGMENTTYPECODE is null
      set @SEGMENTTYPECODE = 1;
    if @SEGMENTEXPECTEDGIFTAMOUNT is null
      set @SEGMENTEXPECTEDGIFTAMOUNT = 0;
    if @SEGMENTEXPECTEDRESPONSERATE is null
      set @SEGMENTEXPECTEDRESPONSERATE = 0.00;
    if @SEGMENTRENTALQUANTITY is null
      set @SEGMENTRENTALQUANTITY = 0;
    if @SEGMENTEXCHANGEQUANTITY is null
      set @SEGMENTEXCHANGEQUANTITY = 0;
    if @SEGMENTRENTALCOST is null
      set @SEGMENTRENTALCOST = 0;
    if @SEGMENTRENTALCOSTBASISCODE is null
      set @SEGMENTRENTALCOSTBASISCODE = 1;
    if @SEGMENTEXCHANGECOST is null
      set @SEGMENTEXCHANGECOST = 0;
    if @SEGMENTEXCHANGECOSTBASISCODE is null
      set @SEGMENTEXCHANGECOSTBASISCODE = 1;
    if @SEGMENTUSAGECODE is null
      set @SEGMENTUSAGECODE = 1;

    if @TESTSEGMENTNAME is null
      set @TESTSEGMENTNAME = '';
    if @TESTSEGMENTDESCRIPTION is null
      set @TESTSEGMENTDESCRIPTION = '';
    if @TESTSEGMENTCODE is null
      set @TESTSEGMENTCODE = '';
    if @TESTSEGMENTEXPECTEDGIFTAMOUNT is null
      set @TESTSEGMENTEXPECTEDGIFTAMOUNT = 0;
    if @TESTSEGMENTEXPECTEDRESPONSERATE is null
      set @TESTSEGMENTEXPECTEDRESPONSERATE = 0.00;
    if @TESTSEGMENTQUANTITY is null
      set @TESTSEGMENTQUANTITY = 0;

    if @PACKAGENAME is null
      set @PACKAGENAME = '';
    if @PACKAGEDESCRIPTION is null
      set @PACKAGEDESCRIPTION = '';
    if @PACKAGECODE is null
      set @PACKAGECODE = '';
    if @PACKAGECHANNELCODE is null
      set @PACKAGECHANNELCODE = '';
    if @PACKAGETYPECODE is null
      set @PACKAGETYPECODE = 0;
    if @PACKAGECOST is null
      set @PACKAGECOST = 0;
    if @PACKAGECOSTDISTRIBUTIONMETHODCODE is null
      set @PACKAGECOSTDISTRIBUTIONMETHODCODE = 0;




    /*********************************/
    /* Validate the marketing effort */
    /*********************************/
    if @SEGMENTATIONID is null
      begin
        if @SEGMENTATIONCODE = ''
          begin
            if @SOURCECODE = ''
              raiserror('BBERR_SRC_NAME_OR_CODE_REQ: A full source code, marketing effort name, or marketing effort code is required.', 13, 1);
            else
              begin
                --Try to parse the sourcecode field and figure out which mailing it maps to.  We have to search all possible sourcecode layouts that

                --have a marketing effort part for valid mailings, and then we have to end up with a unique code match or we still have to throw an error.

                declare @SOURCECODEMATCHES table ([SOURCECODEID] uniqueidentifier, [SEGMENTATIONID] uniqueidentifier, [CODE] nvarchar(10));
                declare @MAILINGMATCHES table ([SOURCECODEID] uniqueidentifier, [SEGMENTATIONID] uniqueidentifier, [CODE] nvarchar(10));

                insert into @SOURCECODEMATCHES ([SOURCECODEID], [SEGMENTATIONID], [CODE])
                  select
                    [MKTSOURCECODE].[ID],
                    [MKTSEGMENTATION].[ID],
                    [MKTSEGMENTATION].[CODE]
                  from dbo.[MKTSEGMENTATION]
                  inner join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
                  inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODE].[ID]
                  inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
                  where [MKTSEGMENTATION].[ACTIVE] = 0
                  and [MKTSEGMENTATION].[MAILINGTYPECODE] = 0
                  and [MKTSOURCECODE].[ISACTIVE] = 1
                  and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0
                  order by [MKTSOURCECODE].[ISDEFAULT] desc, [MKTSEGMENTATION].[IDINTEGER];

                declare SOURCECODECURSOR cursor local fast_forward for
                  select distinct [SOURCECODEID]
                  from @SOURCECODEMATCHES;

                open SOURCECODECURSOR;
                fetch next from SOURCECODECURSOR into @SOURCECODEID;

                while (@@fetch_status = 0)
                begin
                  set @CODE = dbo.[UFN_MKTSOURCECODE_GETCODEFORPART](@SOURCECODEID, @SOURCECODE, 0);

                  if isnull(@CODE, '') <> ''
                    insert into @MAILINGMATCHES ([SOURCECODEID], [SEGMENTATIONID], [CODE])
                      select
                        [SOURCECODEID],
                        [SEGMENTATIONID],
                        [CODE]
                      from @SOURCECODEMATCHES
                      where [SOURCECODEID] = @SOURCECODEID
                      and [CODE] = @CODE;

                  fetch next from SOURCECODECURSOR into @SOURCECODEID;
                end

                close SOURCECODECURSOR;
                deallocate SOURCECODECURSOR;

                select
                  @COUNT = count(*)
                from @MAILINGMATCHES;

                if @COUNT > 1
                  raiserror('BBERR_MULTI_EFFORT_FOUND: Unable to determine the marketing effort from the source code because there are multiple marketing efforts in the system with the same code.  A marketing effort name or code is required.', 13, 1);
                else if @COUNT = 1
                  select
                    @SOURCECODEID = [SOURCECODEID],
                    @SEGMENTATIONID = [SEGMENTATIONID],
                    @SEGMENTATIONCODE = [CODE]
                  from @MAILINGMATCHES;
                else
                  raiserror('BBERR_NO_EFFORT_FOUND: Unable to determine the marketing effort from the source code because a marketing effort with a matching code could not be found.  A marketing effort name or code is required.', 13, 1);
              end
          end

        if @SEGMENTATIONID is null
          begin
            --Try to find a direct marketing effort in the system with the same code...

            select
              @COUNT = count(*)
            from dbo.[MKTSEGMENTATION]
            where [MAILINGTYPECODE] = 0
            and [ACTIVE] = 0
            and [CODE] = @SEGMENTATIONCODE;

            if @COUNT > 1
              raiserror('BBERR_MULTI_EFFORT_CODE: There are multiple direct marketing efforts in the system with the same marketing effort code.', 13, 1);
 else if @COUNT = 1
              select
                @SEGMENTATIONID = [ID]
              from dbo.[MKTSEGMENTATION]
              where [MAILINGTYPECODE] = 0
              and [ACTIVE] = 0
              and [CODE] = @SEGMENTATIONCODE;

            if @SEGMENTATIONID is null
              raiserror('BBERR_EFFORT_NAME_OR_CODE_REQ: A marketing effort name or code is required.', 13, 1);
          end
      end

    --Check if the mailing is currently activated or being activated and throws errors...

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    --Grab some info from the mailing...

    select
      @SOURCECODEID = [SOURCECODEID],
      @SEGMENTATIONBASECURRENCYID = [BASECURRENCYID]
    from dbo.[MKTSEGMENTATION]
    where [ID] = @SEGMENTATIONID;



    /*****************************************************/
    /* Check if the source code is valid for the mailing */
    /*****************************************************/
    if @VALIDATEFORCOMMIT = 1
      begin
        --Check each of the codes to see if they have the format characters (#, @, ?)...

        if patindex('%#%', @SEGMENTATIONCODE) > 0 or patindex('%@%', @SEGMENTATIONCODE) > 0 or patindex('%?%', @SEGMENTATIONCODE) > 0
          raiserror('BBERR_EFFORT_CODE_REQ: The marketing effort code is required.', 13, 1);

        if patindex('%#%', @LISTCODE) > 0 or patindex('%@%', @LISTCODE) > 0 or patindex('%?%', @LISTCODE) > 0
          raiserror('BBERR_LIST_CODE_REQ: The list code is required.', 13, 1);

        if patindex('%#%', @SEGMENTCODE) > 0 or patindex('%@%', @SEGMENTCODE) > 0 or patindex('%?%', @SEGMENTCODE) > 0
          raiserror('BBERR_SEG_CODE_REQ: The segment code is required.', 13, 1);

        if patindex('%#%', @TESTSEGMENTCODE) > 0 or patindex('%@%', @TESTSEGMENTCODE) > 0 or patindex('%?%', @TESTSEGMENTCODE) > 0
          raiserror('BBERR_TEST_SEG_CODE_REQ: The test segment code is required.', 13, 1);

        if patindex('%#%', @PACKAGECODE) > 0 or patindex('%@%', @PACKAGECODE) > 0 or patindex('%?%', @PACKAGECODE) > 0
          raiserror('BBERR_PKG_CODE_REQ: The package code is required.', 13, 1);

        if patindex('%#%', @PACKAGECHANNELCODE) > 0 or patindex('%@%', @PACKAGECHANNELCODE) > 0 or patindex('%?%', @PACKAGECHANNELCODE) > 0
          raiserror('BBERR_PKG_CH_CODE_REQ: The package channel code is required.', 13, 1);


        --Check if each code has a value specified if the description is specified...

        if @SEGMENTATIONCODEVALUEID is not null and @SEGMENTATIONCODE = ''
          raiserror('BBERR_EFFORT_CODE_WITH_DESC: The marketing effort code is required when specifying a marketing effort code description.', 13, 1);

        if @LISTCODEVALUEID is not null and @LISTCODE = ''
          raiserror('BBERR_LIST_CODE_WITH_DESC: The list code is required when specifying a list code description.', 13, 1);

        if @SEGMENTCODEVALUEID is not null and @SEGMENTCODE = ''
          raiserror('BBERR_SEG_CODE_WITH_DESC: The segment code is required when specifying a segment code description.', 13, 1);

        if @TESTSEGMENTCODEVALUEID is not null and @TESTSEGMENTCODE = ''
          raiserror('BBERR_TEST_SEG_CODE_WITH_DESC: The test segment code is required when specifying a test segment code description.', 13, 1);

        if @PACKAGECODEVALUEID is not null and @PACKAGECODE = ''
          raiserror('BBERR_PKG_CODE_WITH_DESC: The package code is required when specifying a package code description.', 13, 1);

        if @PACKAGECHANNELCODEVALUEID is not null and @PACKAGECHANNELCODE = ''
          raiserror('BBERR_PKG_CH_CODE_WITH_DESC: The package channel code is required when specifying a package channel code description.', 13, 1);
      end

    if @SOURCECODE <> '' and @SOURCECODEID is not null
      begin
        if @VALIDATEFORCOMMIT = 1 and dbo.[UFN_MKTSOURCECODE_SOURCECODEISVALID](@SOURCECODEID, @SOURCECODE) = 0
          raiserror('BBERR_INVALID_SRC_CODE: The source code is not valid for the specified marketing effort.', 13, 1);

        --Check if the marketing effort code matches the part in the full source code...

        set @CODE = dbo.[UFN_MKTSOURCECODE_GETCODEFORPART](@SOURCECODEID, @SOURCECODE, 0);
        if @VALIDATEFORCOMMIT = 1 and @SEGMENTATIONCODE <> '' and @SEGMENTATIONCODE <> @CODE
          raiserror('BBERR_EFFORT_CODE_SRC_MISS: The marketing effort code does not match the value in the full source code.', 13, 1);
        if isnull(@CODE, '') <> ''
          set @SEGMENTATIONCODE = @CODE;

        --Check if the list code matches the part in the full source code...

        set @CODE = dbo.[UFN_MKTSOURCECODE_GETCODEFORPART](@SOURCECODEID, @SOURCECODE, 6);
        if @VALIDATEFORCOMMIT = 1 and @LISTCODE <> '' and @LISTCODE <> @CODE
          raiserror('BBERR_LIST_CODE_SRC_MISS: The list code does not match the value in the full source code.', 13, 1);
        if isnull(@CODE, '') <> ''
          set @LISTCODE = @CODE;

        --Check if the segment code matches the part in the full source code...

        set @CODE = dbo.[UFN_MKTSOURCECODE_GETCODEFORPART](@SOURCECODEID, @SOURCECODE, 1);
        if @VALIDATEFORCOMMIT = 1 and @SEGMENTCODE <> '' and @SEGMENTCODE <> @CODE
          raiserror('BBERR_SEG_CODE_SRC_MISS: The segment code does not match the value in the full source code.', 13, 1);
        if isnull(@CODE, '') <> ''
          set @SEGMENTCODE = @CODE;

        --Check if the test segment code matches the part in the full source code...

        set @CODE = dbo.[UFN_MKTSOURCECODE_GETCODEFORPART](@SOURCECODEID, @SOURCECODE, 7);
        if @VALIDATEFORCOMMIT = 1 and @TESTSEGMENTCODE <> '' and @TESTSEGMENTCODE <> @CODE
          raiserror('BBERR_TEST_SEG_CODE_SRC_MISS: The test segment code does not match the value in the full source code.', 13, 1);
        if isnull(@CODE, '') <> ''
          set @TESTSEGMENTCODE = @CODE;

        --Check if the package code matches the part in the full source code...

        set @CODE = dbo.[UFN_MKTSOURCECODE_GETCODEFORPART](@SOURCECODEID, @SOURCECODE, 2);
        if @VALIDATEFORCOMMIT = 1 and @PACKAGECODE <> '' and @PACKAGECODE <> @CODE
          raiserror('BBERR_PKG_CODE_SRC_MISS: The package code does not match the value in the full source code.', 13, 1);
        if isnull(@CODE, '') <> ''
          set @PACKAGECODE = @CODE;

        --Check if the package channel code matches the part in the full source code...

        set @CODE = dbo.[UFN_MKTSOURCECODE_GETCODEFORPART](@SOURCECODEID, @SOURCECODE, 3);
        if @VALIDATEFORCOMMIT = 1 and @PACKAGECHANNELCODE <> '' and @PACKAGECHANNELCODE <> @CODE
          raiserror('BBERR_PKG_CH_CODE_SRC_MISS: The package channel code does not match the value in the full source code.', 13, 1);
        if isnull(@CODE, '') <> ''
          set @PACKAGECHANNELCODE = @CODE;
      end


    --Check if the marketing effort code is valid for the layout...

    if @SEGMENTATIONCODE <> ''
      begin
        set @CODEVALUEID = dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE](@SOURCECODEID, @SEGMENTATIONCODE, 0, default);
        if @VALIDATEFORCOMMIT = 1 and @CODEVALUEID is null
          begin
            if @SOURCECODEID is not null
              begin
                if exists(select 1 from dbo.[MKTSOURCECODEPARTDEFINITION] inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID] where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0)
                  raiserror('BBERR_SRC_CODE_INVALID_FOR_LAYOUT: The marketing effort code is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_SRC_CODE_INVALID_ALL_LAYOUTS: The marketing effort code is not valid for any source code layout.', 13, 1);
          end

       if @SEGMENTATIONCODEVALUEID is null
          set @SEGMENTATIONCODEVALUEID = @CODEVALUEID;
        else if @VALIDATEFORCOMMIT = 1 and dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_VALUEISVALID](@SOURCECODEID, @SEGMENTATIONCODEVALUEID, 0) = 0
          begin
            if @SOURCECODEID is not null
              begin
                if dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 0) = 1
                  raiserror('BBERR_SRC_DESC_INVALID_FOR_LAYOUT: The marketing effort code description is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_SRC_DESC_INVALID_ALL_LAYOUTS: The marketing effort code description is not valid for any source code layout.', 13, 1);
          end
      end


    --Check if the list code is valid for the layout...

    if @LISTCODE <> ''
      begin
        set @CODEVALUEID = dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE](@SOURCECODEID, @LISTCODE, 6, default);
        if @VALIDATEFORCOMMIT = 1 and @CODEVALUEID is null
          begin
            if @SOURCECODEID is not null
              begin
                if exists(select 1 from dbo.[MKTSOURCECODEPARTDEFINITION] inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID] where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 6)
                  raiserror('BBERR_LIST_CODE_INVALID_FOR_LAYOUT: The list code is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_LIST_CODE_INVALID_ALL_LAYOUTS: The list code is not valid for any source code layout.', 13, 1);
          end

        if @LISTCODEVALUEID is null
          set @LISTCODEVALUEID = @CODEVALUEID;
        else if @VALIDATEFORCOMMIT = 1 and dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_VALUEISVALID](@SOURCECODEID, @LISTCODEVALUEID, 6) = 0
          begin
            if @SOURCECODEID is not null
              begin
                if dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 6) = 1
                  raiserror('BBERR_LIST_DESC_INVALID_FOR_LAYOUT: The list code description is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_LIST_DESC_INVALID_ALL_LAYOUTS: The list code description is not valid for any source code layout.', 13, 1);
          end
      end


    --Check if the segment code is valid for the layout...

    if @SEGMENTCODE <> ''
      begin
        set @CODEVALUEID = dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE](@SOURCECODEID, @SEGMENTCODE, 1, default);
        if @VALIDATEFORCOMMIT = 1 and @CODEVALUEID is null
          begin
            if @SOURCECODEID is not null
              begin
                if exists(select 1 from dbo.[MKTSOURCECODEPARTDEFINITION] inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID] where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1)
                  raiserror('BBERR_SEG_CODE_INVALID_FOR_LAYOUT: The segment code is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_SEG_CODE_INVALID_ALL_LAYOUTS: The segment code is not valid for any source code layout.', 13, 1);
          end

        if @SEGMENTCODEVALUEID is null
          set @SEGMENTCODEVALUEID = @CODEVALUEID;
        else if @VALIDATEFORCOMMIT = 1 and dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_VALUEISVALID](@SOURCECODEID, @SEGMENTCODEVALUEID, 1) = 0
          begin
            if @SOURCECODEID is not null
              begin
                if dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 1) = 1
                  raiserror('BBERR_SEG_DESC_INVALID_FOR_LAYOUT: The segment code description is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_SEG_DESC_INVALID_ALL_LAYOUTS: The segment code description is not valid for any source code layout.', 13, 1);
          end
      end


    --Check if the test segment code is valid for the layout...

    if @TESTSEGMENTCODE <> ''
      begin
        set @CODEVALUEID = dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE](@SOURCECODEID, @TESTSEGMENTCODE, 7, default);
        if @VALIDATEFORCOMMIT = 1 and @CODEVALUEID is null
          begin
            if @SOURCECODEID is not null
              begin
                if exists(select 1 from dbo.[MKTSOURCECODEPARTDEFINITION] inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID] where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 7)
                  raiserror('BBERR_TEST_SEG_CODE_INVALID_FOR_LAYOUT: The test segment code is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_TEST_SEG_CODE_INVALID_ALL_LAYOUTS: The test segment code is not valid for any source code layout.', 13, 1);
          end

        if @TESTSEGMENTCODEVALUEID is null
          set @TESTSEGMENTCODEVALUEID = @CODEVALUEID;
        else if @VALIDATEFORCOMMIT = 1 and dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_VALUEISVALID](@SOURCECODEID, @TESTSEGMENTCODEVALUEID, 7) = 0
          begin
            if @SOURCECODEID is not null
              begin
                if dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 7) = 1
                  raiserror('BBERR_TEST_SEG_DESC_INVALID_FOR_LAYOUT: The test segment code description is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_TEST_SEG_DESC_INVALID_ALL_LAYOUTS: The test segment code description is not valid for any source code layout.', 13, 1);
          end
      end


    --Check if the package code is valid for the layout...

    if @PACKAGECODE <> ''
      begin
        set @CODEVALUEID = dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE](@SOURCECODEID, @PACKAGECODE, 2, default);
        if @VALIDATEFORCOMMIT = 1 and @CODEVALUEID is null
          begin
            if @SOURCECODEID is not null
              begin
                if exists(select 1 from dbo.[MKTSOURCECODEPARTDEFINITION] inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID] where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 2)
                  raiserror('BBERR_PKG_CODE_INVALID_FOR_LAYOUT: The package code is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_PKG_CODE_INVALID_ALL_LAYOUTS: The package code is not valid for any source code layout.', 13, 1);
          end

        if @PACKAGECODEVALUEID is null
          set @PACKAGECODEVALUEID = @CODEVALUEID;
        else if @VALIDATEFORCOMMIT = 1 and dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_VALUEISVALID](@SOURCECODEID, @PACKAGECODEVALUEID, 2) = 0
          begin
            if @SOURCECODEID is not null
              begin
                if dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 2) = 1
                  raiserror('BBERR_PKG_DESC_INVALID_FOR_LAYOUT: The package code description is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_PKG_DESC_INVALID_ALL_LAYOUTS: The package code description is not valid for any source code layout.', 13, 1);
          end
      end


    --Check if the package channel code is valid for the layout...

    if @PACKAGECHANNELCODE <> ''
      begin
        set @CODEVALUEID = dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE](@SOURCECODEID, @PACKAGECHANNELCODE, 3, default);
        if @VALIDATEFORCOMMIT = 1 and @CODEVALUEID is null
          begin
            if @SOURCECODEID is not null
              begin
                if exists(select 1 from dbo.[MKTSOURCECODEPARTDEFINITION] inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID] where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 3)
                  raiserror('BBERR_PKG_CH_CODE_INVALID_FOR_LAYOUT: The package channel code is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_PKG_CH_CODE_INVALID_ALL_LAYOUTS: The package channel code is not valid for any source code layout.', 13, 1);
          end

        if @PACKAGECHANNELCODEVALUEID is null
          set @PACKAGECHANNELCODEVALUEID = @CODEVALUEID;
        else if @VALIDATEFORCOMMIT = 1 and dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_VALUEISVALID](@SOURCECODEID, @PACKAGECHANNELCODEVALUEID, 3) = 0
          begin
            if @SOURCECODEID is not null
              begin
                if dbo.[UFN_MKTSOURCECODE_ITEMTYPEISVALID](@SOURCECODEID, 3) = 1
                  raiserror('BBERR_PKG_CH_DESC_INVALID_FOR_LAYOUT: The package channel code description is not valid for the marketing effort source code layout.', 13, 1);
              end
            else
              raiserror('BBERR_PKG_CH_DESC_INVALID_FOR_LAYOUTS: The package channel code description is not valid for any source code layout.', 13, 1);
          end
      end



    /*********************/
    /* Validate the list */
    /*********************/
    if @LISTID is null
      begin
        if @LISTNAME = ''
          begin
            if @LISTCODE = ''
              begin
                --We have no information to match a list by, but if they have other list fields with values,

                --then throw an error that the list name or code is required...

                if @LISTDESCRIPTION <> '' or @LISTCATEGORYCODEID is not null or @LISTVENDORID is not null
                  raiserror('BBERR_LIST_VALUES_REQ: A full source code, list name, or list code is required when specifying other list values.', 13, 1);

                set @SAVELISTRECORD = 0;
              end
            else
              begin
                if exists(select 1 from dbo.[MKTRECORDSOURCE] where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([ID]) = 1 and [CODE] = @LISTCODE)
                  --The list code matches the record source code, so don't save a list record...

                  set @SAVELISTRECORD = 0;
                else
                  begin
                    --Try to find a list in the system with the same code...

                    select
                      @COUNT = count(*)
                    from dbo.[MKTLIST]
                    where [CODE] = @LISTCODE;

                    if @COUNT > 1
                      raiserror('BBERR_MULTI_LIST_CODE_FOUND: There are multiple lists in the system with the same list code.', 13, 1);
                    else if @COUNT = 1
                      select
                        @LISTID = [ID],
                        @NAME = [NAME],
                        @CODE = [CODE],
                        @LISTRECORDSOURCEID = [RECORDSOURCEID],
                        @LISTBASECURRENCYID = [BASECURRENCYID],
                        @CURRENTLISTSITEID = [SITEID],
                        @EXISTINGLIST = 1
                      from dbo.[MKTLIST]
                      where [CODE] = @LISTCODE;
                    else
                      begin
                        -- Check to see if the list was added through batch.

                        select
                          @COUNT = count(*)
                        from dbo.[BATCHDIRECTMARKETINGEFFORT]
                        inner join dbo.[BATCHDIRECTMARKETINGEFFORTLIST] on [BATCHDIRECTMARKETINGEFFORTLIST].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTLISTID]
                        where 
                          [BATCHDIRECTMARKETINGEFFORTLIST].[CODE] = @LISTCODE
                          and [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID;

                        if @COUNT > 1
                          --If there are multiple lists added in batch with the same list code, then only raise an error if no list

                          --name was specified.

                          raiserror('BBERR_BATCH_MULTI_LIST_CODE: There were multiple lists being added through batch with the same list code.', 13, 1);
                        else if @COUNT = 1
                          select
                            @LISTNAME = [BATCHDIRECTMARKETINGEFFORTLIST].[NAME]
                        from dbo.[BATCHDIRECTMARKETINGEFFORT]
                        inner join dbo.[BATCHDIRECTMARKETINGEFFORTLIST] on [BATCHDIRECTMARKETINGEFFORTLIST].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTLISTID]
                        where 
                          [BATCHDIRECTMARKETINGEFFORTLIST].[CODE] = @LISTCODE
                          and [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID;
                        else
                          --They didn't specify a name for the new list, so just use the code as the name...

                          set @LISTNAME = @LISTCODE;
                      end

                    if @EXISTINGLIST = 1
                      begin
                        if @LISTNAME = ''
                          set @LISTNAME = @NAME;
                        else if @LISTNAME <> @NAME
                          raiserror('BBERR_LIST_CODE_NAME_CONFLICT: The list code matches an existing list, but the list name does not match that same list.', 13, 1);
                      end
                  end
              end
          end

        if @SAVELISTRECORD = 1
          begin
            --If we come out of the block above and still haven't found an existing list, the list name will now

            --be set to the list code, so try to find an existing list based on that.  If we didn't go through the

            --block above, then we will always enter this block.

            if @EXISTINGLIST = 0 and @LISTNAME <> ''
              begin
                --Try to find any list in the system with the same name.

                --List name is unique in the system, so we don't have to worry about duplicates here.

                select
                  @LISTID = [ID],
                  @CODE = [CODE],
                  @LISTRECORDSOURCEID = [RECORDSOURCEID],
                  @LISTBASECURRENCYID = [BASECURRENCYID],
                  @CURRENTLISTSITEID = [SITEID],
                  @EXISTINGLIST = 1
                from dbo.[MKTLIST]
                where [NAME] = @LISTNAME;
              end

            if @EXISTINGLIST = 1
              begin
                --If a list code was specified, make sure it is the same as the existing list...            

                if @LISTCODE <> '' and @LISTCODE <> @CODE
                  raiserror('BBERR_LIST_CODE_MISS: The list code does not match the code on the existing list record.', 13, 1);
              end
          end
      end
    else
      begin
        --We have a list ID, so check to make sure it is exists...

        select
          @LISTRECORDSOURCEID = [RECORDSOURCEID],
          @LISTBASECURRENCYID = [BASECURRENCYID],
          @CURRENTLISTSITEID = [SITEID],
          @EXISTINGLIST = 1
        from dbo.[MKTLIST]
        where [ID] = @LISTID;

        if @EXISTINGLIST = 0
          raiserror('BBERR_LIST_ID_NOT_FOUND: The list ID does not exist in the system.', 13, 1);
      end      

    if @SAVELISTRECORD = 1
      begin

        if @EXISTINGLIST = 1
          begin
            --Check that user has access to existing list site from the context of the list edit form.

            if (@ISSYSADMIN <> 1 and not exists (select * from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, '4DCC7F29-6003-42D6-93FE-24A3D86E2EEA', 1) where [SITEID] = @CURRENTLISTSITEID or ([SITEID] is null and @CURRENTLISTSITEID is null))) 
              raiserror('BBERR_LIST_NO_EDIT_RIGHTS: The current user does not have rights to edit the list in the context of this specific site.', 13, 1);

            --If editing the list and the sites do not match, we must check security on the new site being added.

            if @LISTSITEID <> @CURRENTLISTSITEID and not (@LISTSITEID is null and @CURRENTLISTSITEID is null)
              --Check that user has access to new list site from the context of the list edit form.

              if (@ISSYSADMIN <> 1 and not exists (select * from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, '4DCC7F29-6003-42D6-93FE-24A3D86E2EEA', 1) where [SITEID] = @LISTSITEID or ([SITEID] is null and @LISTSITEID is null))) 
                raiserror('BBERR_LIST_NO_EDIT_RIGHTS: The current user does not have rights to edit the list in the context of this specific site.', 13, 1);
          end
        else
          begin
            --Check that user has access to existing list site from the context of the list add form.

            if (@ISSYSADMIN <> 1 and not exists (select * from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, 'FF7F1D23-3238-45CB-BB42-9D47C3238A1D', 1) where [SITEID] = @LISTSITEID or ([SITEID] is null and @LISTSITEID is null))) 
              raiserror('BBERR_LIST_NO_ADD_RIGHTS: The current user does not have rights to add a list in the context of this specific site.', 13, 1);
          end

        if @SEGMENTTYPECODE <> 2
          begin
            if @VALIDATEFORCOMMIT = 1
              raiserror('BBERR_LIST_OR_SEG_TYPE_INCORRECT: Either the segment type is incorrect for the segment or a list should not be specified.', 13, 1);
            else
              --Since we are saving a list, we can quietly assume the segment type should also be set to "list", because the

              --validate code for segments below depends on the segment type being correct.

              set @SEGMENTTYPECODE = 2;
          end
      end

    /************************/
    /* Validate the package */
    /************************/
    if @PACKAGEID is null
      begin
        --No package ID, so we need to try to match on name or code...

        if @PACKAGENAME = '' or @PACKAGECODE <> ''
          begin
            --No package name or code, so throw error...

            if @PACKAGENAME = '' and @PACKAGECODE = ''
              raiserror('BBERR_PKG_VALUES_REQ: A full source code, package name, or package code is required.', 13, 1);

            --Try to find a package in the system with the same code...

            select
              @COUNT = count(*)
            from dbo.[MKTPACKAGE]
            where [CODE] = @PACKAGECODE;

            if @COUNT > 1
              raiserror('BBERR_MULTI_PKG_CODE_FOUND: There are multiple packages in the system with the same package code.', 13, 1);
            else if @COUNT = 1
              select
                @PACKAGEID = [ID],
                @PACKAGETYPECODE = [CHANNELCODE],
                @NAME = [NAME],
                @CODE = [CODE],
                @SITEID = [SITEID],
                @PACKAGEBASECURRENCYID = [BASECURRENCYID],
                @CURRENTPACKAGESITEID = [SITEID],
                @EXISTINGPACKAGE = 1
              from dbo.[MKTPACKAGE]
              where [CODE] = @PACKAGECODE;
            else if @PACKAGENAME = ''
              begin
                -- Check to see if the package was added through batch.

                select
                  @COUNT = count(*)
                from dbo.[BATCHDIRECTMARKETINGEFFORT]
                inner join dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] on [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTPACKAGEID]
                where 
                  [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODE] = @PACKAGECODE
                  and [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID;

                if @COUNT > 1 and @PACKAGENAME = ''
                  --If there are multiple packages added in batch with the same segment code, then only raise an error if no package

                  --name was specified.

                  raiserror('BBERR_BATCH_MULTI_PKG_CODE: There are multiple packages being added through batch with the same package code.', 13, 1);
                else if @COUNT = 1
                  select
                    @PACKAGENAME = [NAME]
                  from dbo.[BATCHDIRECTMARKETINGEFFORT]
                  inner join dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] on [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTPACKAGEID]
                  where 
                    [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODE] = @PACKAGECODE
                    and [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID;
                else
                  --They didn't specify a name for the new package, so just use the code as the name...

                  set @PACKAGENAME = @PACKAGECODE;
              end

            if @EXISTINGPACKAGE = 1
              begin
                if @PACKAGENAME = ''
                  set @PACKAGENAME = @NAME;
                else if @PACKAGENAME <> @NAME
                  raiserror('BBERR_PKG_CODE_NAME_CONFLICT: The package code matches an existing package, but the package name does not match that same package.', 13, 1);
              end
          end

        --If we come out of the block above and still haven't found an existing package, the package name will now

        --be set to the package code, so try to find an existing package based on that.  If we didn't go through the

        --block above, then we will always enter this block.

        if @EXISTINGPACKAGE = 0
          begin
            --Try to find any package in the system with the same name.

            --Package name is unique in the system, so we don't have to worry about duplicates here.

            select
              @PACKAGEID = [ID],
              @PACKAGETYPECODE = [CHANNELCODE],
              @CODE = [CODE],
              @SITEID = [SITEID],
              @PACKAGEBASECURRENCYID = [BASECURRENCYID],
              @CURRENTPACKAGESITEID = [SITEID],
              @EXISTINGPACKAGE = 1
            from dbo.[MKTPACKAGE]
            where [NAME] = @PACKAGENAME;
          end

        if @EXISTINGPACKAGE = 1
          begin
            --If a package code was specified, make sure it is the same as the existing package...

            if @PACKAGECODE <> '' and @PACKAGECODE <> @CODE
              raiserror('BBERR_PKG_CODE_MISS: The package code does not match the code on the existing package record.', 13, 1);
          end
      end
    else
      begin
        --We have a package ID, so check to make sure it is exists...

        select
          @PACKAGETYPECODE = [CHANNELCODE],
          @SITEID = [SITEID],
          @PACKAGEBASECURRENCYID = [BASECURRENCYID],
          @CURRENTPACKAGESITEID = [SITEID],
          @EXISTINGPACKAGE = 1
        from dbo.[MKTPACKAGE]
        where [ID] = @PACKAGEID;

        if @EXISTINGPACKAGE = 0
          raiserror('BBERR_PKG_ID_NOT_FOUND: The package ID does not exist in the system.', 13, 1);
      end

    if @VALIDATEFORCOMMIT = 1
      begin
if @EXISTINGPACKAGE = 1
          begin
            --Check that user has access to existing package site from the context of the package edit form.

            if (@ISSYSADMIN <> 1 and not exists (select * from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, 'D85DD414-BED5-4FBE-B5F9-0C8369D456F7', 1) where [SITEID] = @CURRENTPACKAGESITEID or ([SITEID] is null and @CURRENTPACKAGESITEID is null))) 
              raiserror('BBERR_PKG_NO_EDIT_RIGHTS: The current user does not have rights to edit the package in the context of this specific site.', 13, 1);

            --If editing the package and the sites do not match, we must check security on the new site being added.

            if @PACKAGESITEID <> @CURRENTPACKAGESITEID and not (@PACKAGESITEID is null and @CURRENTPACKAGESITEID is null)
              --Check that user has access to new package site from the context of the package edit form.

              if (@ISSYSADMIN <> 1 and not exists (select * from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, 'D85DD414-BED5-4FBE-B5F9-0C8369D456F7', 1) where [SITEID] = @PACKAGESITEID or ([SITEID] is null and @PACKAGESITEID is null))) 
                raiserror('BBERR_PKG_NO_EDIT_RIGHTS: The current user does not have rights to edit the package in the context of this specific site.', 13, 1);                 
          end
      end

    /************************/
    /* Validate the segment */
    /************************/
    if @SEGMENTID is null
      begin
        --No segment ID, so we need to try to match on name or code...

        if @SEGMENTNAME = '' or @SEGMENTCODE <> ''
          begin
            --No segment name or code, so throw error...

            if @SEGMENTNAME = '' and @SEGMENTCODE = ''
              raiserror('BBERR_SEG_VALUES_REQ: A full source code, segment name, or segment code is required.', 13, 1);

            --Try to find an existing segment with the same code in the mailing first...

            select
              @COUNT = count(*)
            from dbo.[MKTSEGMENTATIONSEGMENT]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
            where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
            and ((@SAVELISTRECORD = 0 and [MKTSEGMENT].[SEGMENTTYPECODE] in (1, 2)) or (@SAVELISTRECORD = 1 and [MKTSEGMENT].[SEGMENTTYPECODE] = 2))
            and [MKTSEGMENT].[CODE] = @SEGMENTCODE
            and ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @PACKAGEID);

            if @COUNT > 1 and @SEGMENTNAME = ''
              --If there are multiple segments in the marketing effort with the same segment code, then only raise an error if

              --no segment name was specified.  Otherwise, let it fall through and we will search for the segment by name below.

              raiserror('BBERR_MULTI_SEG_CODE_OR_PKG_FOUND: There are multiple segments in the marketing effort with the same segment code and/or package combination.', 13, 1);
            else if @COUNT = 1
              select
                @SEGMENTID = [MKTSEGMENT].[ID],
                @SEGMENTATIONSEGMENTID = case when @PACKAGEID is not null then [MKTSEGMENTATIONSEGMENT].[ID] end,
                @SEGMENTATIONSEGMENTLISTID = [MKTSEGMENTLIST].[LISTID],
                @SEGMENTATIONSEGMENTPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
                @SEGMENTATIONSEGMENTTESTCODE = [MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE],
                @NAME = [MKTSEGMENT].[NAME],
                @SEGMENTRECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
                @LISTTYPECODE = [MKTSEGMENTLIST].[TYPECODE],
                @CURRENTSEGMENTSITEID = [MKTSEGMENT].[SITEID],
                @EXISTINGSEGMENT = 1
              from dbo.[MKTSEGMENTATIONSEGMENT]
              inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
              left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
              and ((@SAVELISTRECORD = 0 and [MKTSEGMENT].[SEGMENTTYPECODE] in (1, 2)) or (@SAVELISTRECORD = 1 and [MKTSEGMENT].[SEGMENTTYPECODE] = 2))
              and [MKTSEGMENT].[CODE] = @SEGMENTCODE
              and ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @PACKAGEID);
            else
              begin
                --The segment is not in the mailing yet, so try to find any segment in the system with the same code...

                select
                  @COUNT = count(*)
                from dbo.[MKTSEGMENT]
                where ((@SAVELISTRECORD = 0 and [SEGMENTTYPECODE] in (1, 2)) or (@SAVELISTRECORD = 1 and [SEGMENTTYPECODE] = 2))
                and [CODE] = @SEGMENTCODE;

                if @COUNT > 1 and @SEGMENTNAME = ''
                  --If there are multiple segments in the system with the same segment code, then only raise an error if no segment

                  --name was specified.  Otherwise, let it fall through and we will search for the segment by name below.

                  raiserror('BBERR_MULTI_SEG_CODE_FOUND: There are multiple segments in the system with the same segment code.', 13, 1);
                else if @COUNT = 1
                  select
                    @SEGMENTID = [MKTSEGMENT].[ID],
                    @NAME = [MKTSEGMENT].[NAME],
                    @SEGMENTATIONSEGMENTLISTID = [MKTSEGMENTLIST].[LISTID],
                    @SEGMENTRECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
                    @LISTTYPECODE = [MKTSEGMENTLIST].[TYPECODE],
                    @CURRENTSEGMENTSITEID = [MKTSEGMENT].[SITEID],
                    @EXISTINGSEGMENT = 1
                  from dbo.[MKTSEGMENT]
                  left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                  where ((@SAVELISTRECORD = 0 and [MKTSEGMENT].[SEGMENTTYPECODE] in (1, 2)) or (@SAVELISTRECORD = 1 and [MKTSEGMENT].[SEGMENTTYPECODE] = 2))
                  and [MKTSEGMENT].[CODE] = @SEGMENTCODE;
                else if @COUNT = 0 and @SEGMENTNAME = ''
                  begin
                    -- Check to see if the segment was added through batch.

                    select
                      @COUNT = count(*)
                    from dbo.[BATCHDIRECTMARKETINGEFFORT]
                    inner join dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTSEGMENTID]
                    where 
                      ((@SAVELISTRECORD = 0 and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SEGMENTTYPECODE] in (1, 2)) 
                        or (@SAVELISTRECORD = 1 and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SEGMENTTYPECODE] = 2))
                      and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODE] = @SEGMENTCODE
                      and [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID;

                    if @COUNT > 1 and @SEGMENTNAME = ''
                      --If there are multiple segments in the system with the same segment code, then only raise an error if no segment

                      --name was specified.  Otherwise, let it fall through and we will search for the segment by name below.

                      raiserror('BBERR_BATCH_MULTI_SEG_CODE: There are multiple segments being added through batch with the same segment code.', 13, 1);
                    else if @COUNT = 1
                      select
                        @SEGMENTNAME = [BATCHDIRECTMARKETINGEFFORTSEGMENT].[NAME]
                      from dbo.[BATCHDIRECTMARKETINGEFFORT]
                      inner join dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTSEGMENTID]
                      where 
    ((@SAVELISTRECORD = 0 and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SEGMENTTYPECODE] in (1, 2)) 
                          or (@SAVELISTRECORD = 1 and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SEGMENTTYPECODE] = 2))
                        and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODE] = @SEGMENTCODE
                        and [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID;
                    else
                      --They didn't specify a name for the new segment, so just use the code as the name, and check for duplicates below...

                      set @SEGMENTNAME = @SEGMENTCODE;                    
                  end
              end

            if @EXISTINGSEGMENT = 1
              begin
                if @SEGMENTNAME = ''
                  set @SEGMENTNAME = @NAME;
                else if @SEGMENTNAME <> @NAME
                  raiserror('BBERR_SEG_CODE_NAME_CONFLICT: The segment code matches an existing segment, but the segment name does not match that same segment.', 13, 1);
              end
          end

        --If we come out of the block above and still haven't found an existing segment, the segment name will now

        --be set to the segment code, so try to find an existing segment based on that.  If we didn't go through the

        --block above, then we will always enter this block.

        if @EXISTINGSEGMENT = 0
          begin
            --Try to find an existing segment with the same name in the mailing first...

            select
              @COUNT = count(*)
            from dbo.[MKTSEGMENTATIONSEGMENT]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
            where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
            and [MKTSEGMENT].[NAME] = @SEGMENTNAME
            and ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @PACKAGEID);

            if @COUNT > 1
              raiserror('BBERR_MULTI_SEG_NAME_OR_PKG_FOUND: There are multiple segments in the marketing effort with the same name and/or package combination.', 13, 1);
            else if @COUNT = 1
              select
                @SEGMENTID = [MKTSEGMENT].[ID],
                @TYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
                @CODE = [MKTSEGMENT].[CODE],
                @SEGMENTATIONSEGMENTID = case when @PACKAGEID is not null then [MKTSEGMENTATIONSEGMENT].[ID] end,
                @SEGMENTATIONSEGMENTLISTID = [MKTSEGMENTLIST].[LISTID],
                @SEGMENTATIONSEGMENTPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
                @SEGMENTATIONSEGMENTTESTCODE = [MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE],
                @SEGMENTRECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
                @LISTTYPECODE = [MKTSEGMENTLIST].[TYPECODE],
                @CURRENTSEGMENTSITEID = [MKTSEGMENT].[SITEID],
                @EXISTINGSEGMENT = 1
              from dbo.[MKTSEGMENTATIONSEGMENT]
              inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
              left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
              where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
              and [MKTSEGMENT].[NAME] = @SEGMENTNAME
              and ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @PACKAGEID);
            else
              --The segment is not in the mailing yet, so try to find any segment in the system with the same name.

              --Segment name is unique in the system, so we don't have to worry about duplicates here.

              select
                @SEGMENTID = [MKTSEGMENT].[ID],
                @TYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
                @CODE = [MKTSEGMENT].[CODE],
                @SEGMENTATIONSEGMENTLISTID = [MKTSEGMENTLIST].[LISTID],
                @SEGMENTRECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
                @LISTTYPECODE = [MKTSEGMENTLIST].[TYPECODE],
                @CURRENTSEGMENTSITEID = [MKTSEGMENT].[SITEID],
                @EXISTINGSEGMENT = 1
              from dbo.[MKTSEGMENT]
              left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
              where [MKTSEGMENT].[NAME] = @SEGMENTNAME;

            if @EXISTINGSEGMENT = 1
              begin
                --If a segment code was specified, make sure it is the same as the existing segment...

                if @SEGMENTCODE <> '' and @SEGMENTCODE <> @CODE
                  raiserror('BBERR_SEG_CODE_MISS: The segment code does not match the code on the existing segment record.', 13, 1);

                --The segment type does not match the type of the existing segment record, but we don't want to throw an error

                --here because the user just may not have the segment type field mapped, so quietly just set the type correctly

                --since the user cannot change the segment type on existing segments anyhow.

                if @SEGMENTTYPECODE <> @TYPECODE
                  set @SEGMENTTYPECODE = @TYPECODE;
              end
            else
              begin
                --If the segment does not exist yet and we are saving a list record, then automatically set the

                --segment type to "List" instead of letting it default to a "Constituent" segment...

                if @SAVELISTRECORD = 1
                  set @SEGMENTTYPECODE = 2;
              end
          end
      end
    else
      begin
        --We have a segment ID, so check to make sure it exists...

        select
          @TYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
          @SEGMENTATIONSEGMENTID = case when @PACKAGEID is not null then [MKTSEGMENTATIONSEGMENT].[ID] end,
          @SEGMENTATIONSEGMENTLISTID = [MKTSEGMENTLIST].[LISTID],
          @SEGMENTATIONSEGMENTPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
          @SEGMENTATIONSEGMENTTESTCODE = [MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE],
          @SEGMENTRECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
          @LISTTYPECODE = [MKTSEGMENTLIST].[TYPECODE],
          @CURRENTSEGMENTSITEID = [MKTSEGMENT].[SITEID],
          @EXISTINGSEGMENT = 1
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
        left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
        where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
        and [MKTSEGMENT].[ID] = @SEGMENTID
        and ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = @PACKAGEID);

        if @EXISTINGSEGMENT = 0
          select
            @TYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
            @SEGMENTATIONSEGMENTLISTID = [MKTSEGMENTLIST].[LISTID],
            @SEGMENTRECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
            @LISTTYPECODE = [MKTSEGMENTLIST].[TYPECODE],
            @CURRENTSEGMENTSITEID = [MKTSEGMENT].[SITEID],
            @EXISTINGSEGMENT = 1
          from dbo.[MKTSEGMENT]
          left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
          where [MKTSEGMENT].[ID] = @SEGMENTID;

        if @EXISTINGSEGMENT = 0
          raiserror('BBERR_SEG_ID_NOT_FOUND: The segment ID does not exist in the system.', 13, 1);
        else if @SEGMENTTYPECODE <> @TYPECODE
          --The segment type does not match the type of the existing segment record, but we don't want to throw an error

          --here because the user just may not have the segment type field mapped, so quietly just set the type correctly

          --since the user cannot change the segment type on existing segments anyhow.

          set @SEGMENTTYPECODE = @TYPECODE;
      end

    if @EXISTINGSEGMENT = 1
      begin

        --Check that user has access to existing segment site from the context of the segment edit form.

        if (@ISSYSADMIN <> 1 and not exists (select * from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, 'AEA5E647-4ACB-40A7-868E-ACAF6EFAC17E', 1) where [SITEID] = @CURRENTSEGMENTSITEID or ([SITEID] is null and @CURRENTSEGMENTSITEID is null))) 
          raiserror('BBERR_SEG_NO_EDIT_RIGHTS: The current user does not have rights to edit the segment in the context of this specific site.', 13, 1);

        --If editing the segment and the sites do not match, we must check security on the new site being added.

        if @SEGMENTSITEID <> @CURRENTSEGMENTSITEID and not (@SEGMENTSITEID is null and @CURRENTSEGMENTSITEID is null)
          --Check that user has access to new segment site from the context of the segment edit form.

          if (@ISSYSADMIN <> 1 and not exists (select * from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, 'AEA5E647-4ACB-40A7-868E-ACAF6EFAC17E', 1) where [SITEID] = @SEGMENTSITEID or ([SITEID] is null and @SEGMENTSITEID is null))) 
            raiserror('BBERR_SEG_NO_EDIT_RIGHTS: The current user does not have rights to edit the segment in the context of this specific site.', 13, 1); 

        --Check if the segment is a consolidated segment...

        if dbo.[UFN_MKTSEGMENT_ISCONSOLIDATEDVIEWSEGMENT](@SEGMENTID) = 1
          raiserror('BBERR_SEG_LIST_RECORD_CONFLICT: The existing segment contains records from the consolidated list and cannot be used in batch.', 13, 1);

        --If we are not saving a list record, but we have a list segment, then grab some fields from the list...

        if @SAVELISTRECORD = 0 and @SEGMENTTYPECODE = 2 and @SEGMENTATIONSEGMENTLISTID is not null
          select
            @LISTID = [ID],
            @LISTRECORDSOURCEID = [RECORDSOURCEID],
            @LISTBASECURRENCYID = [BASECURRENCYID],
            @EXISTINGLIST = 1,
            @SAVELISTRECORD = 1
          from dbo.[MKTLIST]
          where [ID] = @SEGMENTATIONSEGMENTLISTID;

        if @VALIDATEFORCOMMIT = 1
          begin
            --Check if the list matches the segment...

            if @EXISTINGLIST = 1 and @LISTID <> @SEGMENTATIONSEGMENTLISTID
              raiserror('BBERR_SEG_LIST_MISS: The list does not match the list on the existing segment.', 13, 1);

            --For existing list segments, check that the list segment is currently set to vendor managed because that is all we can allow.

            --This would cause problems with having to recreate queryviews, etc. when we switch from imported to vendor managed.

            if @SEGMENTTYPECODE = 2 and @LISTTYPECODE <> 1
              raiserror('BBERR_LIST_SEG_IMPORT_CONFLICT: The existing list segment record must not have an import file associated with it.', 13, 1);
          end

      end



    /*****************************/
    /* Validate the test segment */
    /*****************************/
    if @TESTSEGMENTID is null
      begin
        --No test segment ID, so we need to try to match on name or code...

        if @TESTSEGMENTNAME = ''
          begin
            --We have no information to match a test segment by, but if they have other test segment fields with values,

            --then throw an error that the test segment name or code is required...   Bug512314 - Codes no longer have

            --to pair to specific segments, check for parent segment test code value and add an error condition for codes

            if @TESTSEGMENTDESCRIPTION <> '' or @TESTSEGMENTEXPECTEDGIFTAMOUNT <> 0 or @TESTSEGMENTEXPECTEDRESPONSERATE <> 0.00 or @TESTSEGMENTQUANTITY <> 0 or (@TESTSEGMENTCODE <> @SEGMENTATIONSEGMENTTESTCODE and @TESTSEGMENTCODE <> '')
                raiserror('BBERR_TEST_SEG_VALUES_REQ: A full source code, or test segment name is required when specifying other test segment values.', 13, 1);

            set @SAVETESTSEGMENTRECORD = 0;
          end

        -- We will always enter this block, assuming the parent segment exists.

        if @EXISTINGTESTSEGMENT = 0 and @EXISTINGSEGMENT = 1 and @SEGMENTATIONSEGMENTID is not null and @TESTSEGMENTNAME <> ''
          begin
            --Try to find any test segment with the same name under the parent segment.

            --Test segment name is unique for each parent segment, so we don't have to worry about duplicates here.

            select
              @TESTSEGMENTID = [ID],
              @CODE = [TESTSEGMENTCODE],
              @SEGMENTATIONTESTSEGMENTPACKAGEID = [PACKAGEID],
              @EXISTINGTESTSEGMENT = 1
            from dbo.[MKTSEGMENTATIONTESTSEGMENT]
            where [SEGMENTID] = @SEGMENTATIONSEGMENTID
            and dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([ID]) = @TESTSEGMENTNAME;
          end

        if @EXISTINGTESTSEGMENT = 0
          begin

            --The test segment is using a different package than parent segment, so look up the parent id using the segment.  But only if 

            --looking up the segmentation segment ID for a test segment.

            if @SEGMENTATIONSEGMENTID is null and (@TESTSEGMENTCODE <> '' or @TESTSEGMENTNAME <> '')
              begin
                select top 1
                  @SEGMENTATIONSEGMENTID = [MKTSEGMENTATIONSEGMENT].[ID]
                from dbo.[MKTSEGMENTATIONSEGMENT]
                where 
                  [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                  and [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID
                order by [SEQUENCE] desc;

                -- Check to see if test segment exists 

                if @EXISTINGTESTSEGMENT = 0 and @EXISTINGSEGMENT = 1 and @SEGMENTATIONSEGMENTID is not null and @TESTSEGMENTNAME <> ''
                  begin
                    --Try to find any test segment with the same name under the parent segment.

                    --Test segment name is unique for each parent segment, so we don't have to worry about duplicates here.

                    select
                      @TESTSEGMENTID = [ID],
                      @CODE = [TESTSEGMENTCODE],
                      @SEGMENTATIONTESTSEGMENTPACKAGEID = [PACKAGEID],
                      @EXISTINGTESTSEGMENT = 1
                    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
                    where [SEGMENTID] = @SEGMENTATIONSEGMENTID
                    and dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([ID]) = @TESTSEGMENTNAME;
                  end
              end

            --We have no information to match a test segment by, so if they specified a code and no other fields, then it must be the test

            --code on the segment record.  Ignore the test quantity field here because the user may have mapped the same field to both the

            --segment rental/exchange quantity and the test segment quantity.

            if @TESTSEGMENTCODE <> '' and @TESTSEGMENTNAME = '' and @TESTSEGMENTDESCRIPTION = '' and @TESTSEGMENTEXPECTEDGIFTAMOUNT = 0 and @TESTSEGMENTEXPECTEDRESPONSERATE = 0.00
              begin
                set @SAVETESTSEGMENTRECORD = 0;

                --If we get this far, then we don't have an existing test segment, and we don't have the values to create a new test segment.

                --So we can assume the test segment code was really meant to be the test/control code on the existing segment.  Check the test

                --segment code against the test code on the existing segment record to see if they match.

                if @SEGMENTATIONSEGMENTID is not null and @TESTSEGMENTCODE <> '' and @TESTSEGMENTCODE <> @SEGMENTATIONSEGMENTTESTCODE
                  raiserror('BBERR_TEST_SEG_CODE_MISS: The test segment code does not match the test code on the existing segment record.', 13, 1);
              end
          end
      end
    else
      begin
        --We have a test segment ID, so check to make sure it is exists...

        select
          @SEGMENTATIONTESTSEGMENTSEGMENTID = [SEGMENTID],
          @SEGMENTATIONTESTSEGMENTPACKAGEID = [PACKAGEID],
          @EXISTINGTESTSEGMENT = 1
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        where [ID] = @TESTSEGMENTID;

        if @EXISTINGTESTSEGMENT = 0
          raiserror('BBERR_TEST_SEG_ID_NOT_FOUND: The test segment ID does not exist in the system.', 13, 1);

        if @EXISTINGSEGMENT = 1 and @SEGMENTATIONSEGMENTID is not null and @SEGMENTATIONSEGMENTID <> @SEGMENTATIONTESTSEGMENTSEGMENTID
          raiserror('BBERR_TEST_SEG_PARENT_CONFLICT: The test segment exists in the system, but it is under a different parent segment.', 13, 1);
      end

    --package validation moved to happen before segment validation


    /******************************/
    /* Validate the record source */
    /******************************/
    if @RECORDSOURCEID is null
      begin
        --If record source is not specified, then get the record source, but only if there is just one...

        select
          @COUNT = count(*)
        from dbo.[MKTRECORDSOURCE]
        inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
        where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;

        if @COUNT > 1
          begin
            --Since we have multiple record sources, try to figure out the record source from the existing list or existing segment...

            if @EXISTINGLIST = 1 and @EXISTINGSEGMENT = 0 and @LISTRECORDSOURCEID is not null
              set @RECORDSOURCEID = @LISTRECORDSOURCEID;
            else if @EXISTINGLIST = 0 and @EXISTINGSEGMENT = 1 and @SEGMENTRECORDSOURCEID is not null
              set @RECORDSOURCEID = @SEGMENTRECORDSOURCEID;
            else if @EXISTINGLIST = 1 and @EXISTINGSEGMENT = 1 and @LISTRECORDSOURCEID is not null and @SEGMENTRECORDSOURCEID is not null and @LISTRECORDSOURCEID = @SEGMENTRECORDSOURCEID
              set @RECORDSOURCEID = @LISTRECORDSOURCEID;
            else if @VALIDATEFORCOMMIT = 1
              raiserror('BBERR_MULTI_RECORD_SRC_FOUND: There are multiple record sources available in the system.  Please specify a record source for this record.', 13, 1);
          end
        else if @COUNT = 1
          select
            @RECORDSOURCEID = [MKTRECORDSOURCE].[ID]
          from dbo.[MKTRECORDSOURCE]
          inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
          where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;
        else if @VALIDATEFORCOMMIT = 1
          raiserror('BBERR_RECORD_SRC_NOT_FOUND: A valid record source could not be found in the system.', 13, 1);
      end

    if @VALIDATEFORCOMMIT = 1
      begin
        --Check if the record source matches the existing list...

        if @EXISTINGLIST = 1 and @RECORDSOURCEID <> @LISTRECORDSOURCEID
          raiserror('BBERR_RECORD_SRC_LIST_CONFLICT: The record source does not match the record source on the existing list record.', 13, 1);

        --Check if the record source matches the existing segment...

        if @EXISTINGSEGMENT = 1 and @RECORDSOURCEID <> @SEGMENTRECORDSOURCEID
          raiserror('BBERR_RECORD_SRC_SEG_CONFLICT: The record source does not match the record source on the existing segment record.', 13, 1);
      end



    /******************************/
    /* Validate the base currency */
    /******************************/
    --Check if the user is allowed to change the base currency on the mailing...

    if @BASECURRENCYID is null
      set @BASECURRENCYID = @SEGMENTATIONBASECURRENCYID;
    else if @VALIDATEFORCOMMIT = 1 and @SEGMENTATIONBASECURRENCYID <> @BASECURRENCYID
      begin
        --Check if the mailing is associated with an appeal (only needed if it is a BBEC record source)...

        if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1 and exists(select * from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @SEGMENTATIONID and [RECORDSOURCEID] = @RECORDSOURCEID and [APPEALSYSTEMID] <> '')
          raiserror('BBERR_CURRENCY_APPEAL_CONFLICT: The marketing effort base currency cannot be changed because the marketing effort is associated with an appeal.', 13, 1);

        --Check if the mailing has existing segments...

        if exists(select * from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID)
          raiserror('BBERR_CURRENCY_SEG_CONFLICT: The marketing effort base currency cannot be changed because the marketing effort contains existing segments.', 13, 1);
      end

    if @VALIDATEFORCOMMIT = 1
      begin
        --Check if the existing list base currency matches the mailing's base currency...

        if @EXISTINGLIST = 1 and @BASECURRENCYID <> @LISTBASECURRENCYID
          raiserror('BBERR_EFFORT_LIST_CURRENCY_CONFLICT: The list base currency does not match the base currency on the marketing effort.', 13, 1);

        --Check if the existing package base currency matches the mailing's base currency...

        if @EXISTINGPACKAGE = 1 and @BASECURRENCYID <> @PACKAGEBASECURRENCYID
          raiserror('BBERR_EFFORT_PKG_CURRENCY_CONFLICT: The package base currency does not match the base currency on the marketing effort.', 13, 1);
      end



    /************************************************************************/
    /* Check if we have a matching record already saved for this batch, if  */
    /* so use its ID and overwrite any previous information for the record. */
    /************************************************************************/
    select
      @BATCHSEGMENTID = [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID]
    from dbo.[BATCHDIRECTMARKETINGEFFORT]
    inner join dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTSEGMENTID]
    where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
    and ((@EXISTINGSEGMENT = 1 and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[MKTSEGMENTID] = @SEGMENTID)
      or (@EXISTINGSEGMENT = 0 and ([BATCHDIRECTMARKETINGEFFORTSEGMENT].[NAME] = @SEGMENTNAME or (@SEGMENTCODE <> '' and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODE] = @SEGMENTCODE and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SEGMENTTYPECODE] = @SEGMENTTYPECODE))));

    select
      @BATCHPACKAGEID = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID]
    from dbo.[BATCHDIRECTMARKETINGEFFORT]
    inner join dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] on [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTPACKAGEID]
    where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
    and ((@EXISTINGPACKAGE = 1 and [BATCHDIRECTMARKETINGEFFORTPACKAGE].[MKTPACKAGEID] = @PACKAGEID)
      or (@EXISTINGPACKAGE = 0 and ([BATCHDIRECTMARKETINGEFFORTPACKAGE].[NAME] = @PACKAGENAME or (@PACKAGECODE <> '' and [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODE] = @PACKAGECODE))));

    if @SAVELISTRECORD = 1
      select
        @BATCHLISTID = [BATCHDIRECTMARKETINGEFFORTLIST].[ID]
      from dbo.[BATCHDIRECTMARKETINGEFFORT]
      inner join dbo.[BATCHDIRECTMARKETINGEFFORTLIST] on [BATCHDIRECTMARKETINGEFFORTLIST].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTLISTID]
      where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
      and ((@EXISTINGLIST = 1 and [BATCHDIRECTMARKETINGEFFORTLIST].[MKTLISTID] = @LISTID)
        or (@EXISTINGLIST = 0 and ([BATCHDIRECTMARKETINGEFFORTLIST].[NAME] = @LISTNAME or (@LISTCODE <> '' and [BATCHDIRECTMARKETINGEFFORTLIST].[CODE] = @LISTCODE))));
    else
      set @BATCHLISTID = null;

    if @SAVETESTSEGMENTRECORD = 1
      select
        @BATCHTESTSEGMENTID = [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[ID]
      from dbo.[BATCHDIRECTMARKETINGEFFORT]
      inner join dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTSEGMENTID]
      inner join dbo.[BATCHDIRECTMARKETINGEFFORTTESTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID]
      where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
      and ((@EXISTINGTESTSEGMENT = 1 and [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[MKTSEGMENTATIONTESTSEGMENTID] = @TESTSEGMENTID)
        or (@EXISTINGTESTSEGMENT = 0
              and ((@EXISTINGSEGMENT = 1 and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[MKTSEGMENTID] = @SEGMENTID)
                or (@EXISTINGSEGMENT = 0 and ([BATCHDIRECTMARKETINGEFFORTSEGMENT].[NAME] = @SEGMENTNAME or (@SEGMENTCODE <> '' and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[CODE] = @SEGMENTCODE and [BATCHDIRECTMARKETINGEFFORTSEGMENT].[SEGMENTTYPECODE] = @SEGMENTTYPECODE))))
              and ([BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[NAME] = @TESTSEGMENTNAME or (@TESTSEGMENTCODE <> '' and [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[CODE] = @TESTSEGMENTCODE))));
    else
      set @BATCHTESTSEGMENTID = null;


  end try

  begin catch
    -- Bug 512314: Reserve errors for commit to allow add/edit forms to successfully save data (especially in the case of an exception batch)

    if @VALIDATEFORCOMMIT = 0
      begin
        rollback transaction rowvalidatestart;
      end
    else
      begin
        exec dbo.[USP_RAISE_ERROR];
      end
    return 1;
  end catch

  return 0;