USP_DATAFORMTEMPLATE_ADD_DIRECTMARKETINGEFFORTBATCHCOMMIT

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_DIRECTMARKETINGEFFORTBATCHCOMMIT]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @BATCHROWID uniqueidentifier = null,
  @VALIDATEONLY bit = 0,
  @SEGMENTATIONID uniqueidentifier = null,
  @SEGMENTATIONCODEVALUEID uniqueidentifier = null,
  @SEGMENTATIONCODE nvarchar(10) = '',
  @SOURCECODE nvarchar(50) = '',
  @RECORDSOURCEID uniqueidentifier = null,
  @LISTID uniqueidentifier = null,
  @LISTNAME nvarchar(100) = '',
  @LISTDESCRIPTION nvarchar(255) = '',
  @LISTCODEVALUEID uniqueidentifier = null,
  @LISTCODE nvarchar(10) = '',
  @LISTCATEGORYCODEID uniqueidentifier = null,
  @LISTVENDORID uniqueidentifier = null,
  @SEGMENTID uniqueidentifier = null,
  @SEGMENTNAME nvarchar(100) = '',
  @SEGMENTDESCRIPTION nvarchar(255) = '',
  @SEGMENTCODEVALUEID uniqueidentifier = null,
  @SEGMENTCODE nvarchar(10) = '',
  @SEGMENTCATEGORYCODEID uniqueidentifier = null,
  @SEGMENTTYPECODE tinyint = 1,
  @SEGMENTEXPECTEDGIFTAMOUNT money = 0,
  @SEGMENTEXPECTEDRESPONSERATE decimal(5,2) = 0.00,
  @SEGMENTRENTALQUANTITY int = 0,
  @SEGMENTEXCHANGEQUANTITY int = 0,
  @SEGMENTRENTALCOST money = 0,
  @SEGMENTRENTALCOSTBASISCODE tinyint = 1,
  @SEGMENTEXCHANGECOST money = 0,
  @SEGMENTEXCHANGECOSTBASISCODE tinyint = 1,
  @SEGMENTUSAGECODE tinyint = 1,
  @TESTSEGMENTID uniqueidentifier = null,
  @TESTSEGMENTNAME nvarchar(100) = '',
  @TESTSEGMENTDESCRIPTION nvarchar(255) = '',
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null,
  @TESTSEGMENTCODE nvarchar(10) = '',
  @TESTSEGMENTEXPECTEDGIFTAMOUNT money = 0,
  @TESTSEGMENTEXPECTEDRESPONSERATE decimal(5,2) = 0.00,
  @TESTSEGMENTQUANTITY int = 0,
  @PACKAGEID uniqueidentifier = null,
  @PACKAGENAME nvarchar(100) = '',
  @PACKAGEDESCRIPTION nvarchar(255) = '',
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @PACKAGECODE nvarchar(10) = '',
  @PACKAGECHANNELCODEVALUEID uniqueidentifier = null,
  @PACKAGECHANNELCODE nvarchar(10) = '',
  @PACKAGECATEGORYCODEID uniqueidentifier = null,
  @PACKAGETYPECODE tinyint = 0,
  @PACKAGECOST money = 0,
  @PACKAGECOSTDISTRIBUTIONMETHODCODE tinyint = 0,
  @BASECURRENCYID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @LISTSITEID uniqueidentifier = null,
  @PACKAGESITEID uniqueidentifier = null,
  @SEGMENTSITEID uniqueidentifier = null,
  @LISTSITEISNULL bit = 0,
  @PACKAGESITEISNULL bit = 0,
  @SEGMENTSITEISNULL bit = 0
)
as
  set nocount on;

  declare @BATCHID uniqueidentifier;
  declare @BATCHSEGMENTID uniqueidentifier;
  declare @BATCHPACKAGEID uniqueidentifier;
  declare @BATCHLISTID uniqueidentifier;
  declare @BATCHTESTSEGMENTID uniqueidentifier;
  declare @OLDBATCHSEGMENTID uniqueidentifier;
  declare @OLDBATCHPACKAGEID uniqueidentifier;
  declare @OLDBATCHLISTID uniqueidentifier;
  declare @OLDBATCHTESTSEGMENTID uniqueidentifier;
  declare @EXISTINGLIST bit = 0;
  declare @EXISTINGSEGMENT bit = 0;
  declare @EXISTINGTESTSEGMENT bit = 0;
  declare @EXISTINGPACKAGE bit = 0;
  declare @SEGMENTATIONSEGMENTID uniqueidentifier;
  declare @SAVELISTRECORD bit = 1;
  declare @SAVETESTSEGMENTRECORD bit = 1;
  declare @CURRENTDATE datetime;

  declare @SOURCECODEID uniqueidentifier;
  declare @OLDCODE nvarchar(10);
  declare @OLDLISTID uniqueidentifier;
  declare @OLDSEGMENTID uniqueidentifier;
  declare @OLDPACKAGEID uniqueidentifier;
  declare @OLDPACKAGETYPECODE tinyint;
  declare @USEADDRESSPROCESSING bit;
  declare @SEQUENCE int;

  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @CURRENCYEXCHANGERATEID uniqueidentifier;
  declare @CURRENCYEXCHANGERATEIDCURRENT uniqueidentifier;
  declare @DATEADDED datetime;
  declare @LISTORGANIZATIONRENTALCOST money;
  declare @LISTORGANIZATIONEXCHANGECOST money;
  declare @PACKAGEORGANIZATIONCOST money;
  declare @SEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT money;
  declare @SEGMENTORGANIZATIONRENTALCOST money;
  declare @SEGMENTORGANIZATIONEXCHANGECOST money;
  declare @TESTSEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT money;


  begin try
    if @VALIDATEONLY is null
      set @VALIDATEONLY = 0;

    select
      @BATCHID = [BATCHID],
      @OLDBATCHSEGMENTID = [BATCHDIRECTMARKETINGEFFORTSEGMENTID],
      @OLDBATCHPACKAGEID = [BATCHDIRECTMARKETINGEFFORTPACKAGEID],
      @OLDBATCHLISTID = [BATCHDIRECTMARKETINGEFFORTLISTID],
      @OLDBATCHTESTSEGMENTID = [BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID]
    from dbo.[BATCHDIRECTMARKETINGEFFORT]
    where [ID] = @BATCHROWID;

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


    --Since the ID fields play double duty in the UI, we need to make sure we reset the fields correctly if they are new records...
    if @OLDBATCHLISTID is not null and @LISTID = @OLDBATCHLISTID
      set @LISTID = null;
    if @OLDBATCHSEGMENTID is not null and @SEGMENTID = @OLDBATCHSEGMENTID
      set @SEGMENTID = null;
    if @OLDBATCHTESTSEGMENTID is not null and @TESTSEGMENTID = @OLDBATCHTESTSEGMENTID
      set @TESTSEGMENTID = null;
    if @OLDBATCHPACKAGEID is not null and @PACKAGEID = @OLDBATCHPACKAGEID
      set @PACKAGEID = null;



    /*********************/
    /* Validate the data */
    /*********************/
    exec dbo.[USP_DIRECTMARKETINGEFFORTBATCH_VALIDATEROW]
      @BATCHID = @BATCHID,
      @VALIDATEFORCOMMIT = 1,
      @BATCHSEGMENTID = @BATCHSEGMENTID output,
      @BATCHPACKAGEID = @BATCHPACKAGEID output,
      @BATCHLISTID = @BATCHLISTID output,
      @BATCHTESTSEGMENTID = @BATCHTESTSEGMENTID output,
      @EXISTINGLIST = @EXISTINGLIST output,
      @EXISTINGSEGMENT = @EXISTINGSEGMENT output,
      @EXISTINGTESTSEGMENT = @EXISTINGTESTSEGMENT output,
      @EXISTINGPACKAGE = @EXISTINGPACKAGE output,
      @SEGMENTATIONSEGMENTID = @SEGMENTATIONSEGMENTID output,
      @SAVELISTRECORD = @SAVELISTRECORD output,
      @SAVETESTSEGMENTRECORD = @SAVETESTSEGMENTRECORD output,
      @SEGMENTATIONID = @SEGMENTATIONID output,
      @SEGMENTATIONCODEVALUEID = @SEGMENTATIONCODEVALUEID output,
      @SEGMENTATIONCODE = @SEGMENTATIONCODE output,
      @SOURCECODE = @SOURCECODE output,
      @RECORDSOURCEID = @RECORDSOURCEID output,
      @LISTID = @LISTID output,
      @LISTNAME = @LISTNAME output,
      @LISTDESCRIPTION = @LISTDESCRIPTION output,
      @LISTCODEVALUEID = @LISTCODEVALUEID output,
      @LISTCODE = @LISTCODE output,
      @LISTCATEGORYCODEID = @LISTCATEGORYCODEID output,
      @LISTVENDORID = @LISTVENDORID output,
      @SEGMENTID = @SEGMENTID output,
      @SEGMENTNAME = @SEGMENTNAME output,
      @SEGMENTDESCRIPTION = @SEGMENTDESCRIPTION output,
      @SEGMENTCODEVALUEID = @SEGMENTCODEVALUEID output,
      @SEGMENTCODE = @SEGMENTCODE output,
      @SEGMENTCATEGORYCODEID = @SEGMENTCATEGORYCODEID output,
      @SEGMENTTYPECODE = @SEGMENTTYPECODE output,
      @SEGMENTEXPECTEDGIFTAMOUNT = @SEGMENTEXPECTEDGIFTAMOUNT output,
      @SEGMENTEXPECTEDRESPONSERATE = @SEGMENTEXPECTEDRESPONSERATE output,
      @SEGMENTRENTALQUANTITY = @SEGMENTRENTALQUANTITY output,
      @SEGMENTEXCHANGEQUANTITY = @SEGMENTEXCHANGEQUANTITY output,
      @SEGMENTRENTALCOST = @SEGMENTRENTALCOST output,
      @SEGMENTRENTALCOSTBASISCODE = @SEGMENTRENTALCOSTBASISCODE output,
      @SEGMENTEXCHANGECOST = @SEGMENTEXCHANGECOST output,
      @SEGMENTEXCHANGECOSTBASISCODE = @SEGMENTEXCHANGECOSTBASISCODE output,
      @SEGMENTUSAGECODE = @SEGMENTUSAGECODE output,
      @TESTSEGMENTID = @TESTSEGMENTID output,
      @TESTSEGMENTNAME = @TESTSEGMENTNAME output,
      @TESTSEGMENTDESCRIPTION = @TESTSEGMENTDESCRIPTION output,
      @TESTSEGMENTCODEVALUEID = @TESTSEGMENTCODEVALUEID output,
      @TESTSEGMENTCODE = @TESTSEGMENTCODE output,
      @TESTSEGMENTEXPECTEDGIFTAMOUNT = @TESTSEGMENTEXPECTEDGIFTAMOUNT output,
      @TESTSEGMENTEXPECTEDRESPONSERATE = @TESTSEGMENTEXPECTEDRESPONSERATE output,
      @TESTSEGMENTQUANTITY = @TESTSEGMENTQUANTITY output,
      @PACKAGEID = @PACKAGEID output,
      @PACKAGENAME = @PACKAGENAME output,
      @PACKAGEDESCRIPTION = @PACKAGEDESCRIPTION output,
      @PACKAGECODEVALUEID = @PACKAGECODEVALUEID output,
      @PACKAGECODE = @PACKAGECODE output,
      @PACKAGECHANNELCODEVALUEID = @PACKAGECHANNELCODEVALUEID output,
      @PACKAGECHANNELCODE = @PACKAGECHANNELCODE output,
      @PACKAGECATEGORYCODEID = @PACKAGECATEGORYCODEID output,
      @PACKAGETYPECODE = @PACKAGETYPECODE output,
      @PACKAGECOST = @PACKAGECOST output,
      @PACKAGECOSTDISTRIBUTIONMETHODCODE = @PACKAGECOSTDISTRIBUTIONMETHODCODE output,
      @BASECURRENCYID = @BASECURRENCYID output,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @LISTSITEID = @LISTSITEID output,
      @PACKAGESITEID = @PACKAGESITEID output,
      @SEGMENTSITEID = @SEGMENTSITEID output;


    if @VALIDATEONLY = 0
      begin
        if @CHANGEAGENTID is null  
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        set @CURRENTDATE = getdate();
        set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

        if @ORGANIZATIONCURRENCYID <> @BASECURRENCYID
          set @CURRENCYEXCHANGERATEIDCURRENT = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);



        /*******************************/
        /* Update the marketing effort */
        /*******************************/
        update dbo.[MKTSEGMENTATION] set
          [PARTDEFINITIONVALUESID] = @SEGMENTATIONCODEVALUEID,
          [CODE] = @SEGMENTATIONCODE,
          [BASECURRENCYID] = @BASECURRENCYID,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @SEGMENTATIONID;




        /************************/
        /* Save the list record */
        /************************/
        if @SAVELISTRECORD = 1
          begin
            if @EXISTINGLIST = 1
              begin
                --This batch does not currently save the cost fields on the list record, but we still need to make sure
                --we set the exchange rate and organization amounts correct.
                declare @LISTRENTALCOST money;
                declare @LISTEXCHANGECOST money;

                select
                  @LISTRENTALCOST = [MKTLIST].[BASERENTALCOST],
                  @LISTEXCHANGECOST = [MKTLIST].[BASEEXCHANGECOST],
                  @CURRENCYEXCHANGERATEID = [CURRENCYEXCHANGERATEID],
                  @LISTDESCRIPTION = (case when @LISTDESCRIPTION = '' then [DESCRIPTION] else @LISTDESCRIPTION end),
                  @LISTCODE = (case when @LISTCODE = '' then [CODE] else @LISTCODE end),
                  @LISTCODEVALUEID = (case when @LISTCODEVALUEID is null then [PARTDEFINITIONVALUESID] else @LISTCODEVALUEID end),
                  @LISTCATEGORYCODEID = (case when @LISTCATEGORYCODEID is null then [LISTCATEGORYCODEID] else @LISTCATEGORYCODEID end),
                  @LISTVENDORID = (case when @LISTVENDORID is null then [VENDORID] else @LISTVENDORID end),
                  @LISTSITEID = (case when @LISTSITEID = [SITEID] or (@LISTSITEISNULL = 0 and @LISTSITEID is null) then [SITEID] else @LISTSITEID end),
                  @DATEADDED = [DATEADDED]
                from dbo.[MKTLIST]
                where [ID] = @LISTID;

                if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                  begin
                    set @LISTORGANIZATIONRENTALCOST = @LISTRENTALCOST;
                    set @LISTORGANIZATIONEXCHANGECOST = @LISTEXCHANGECOST;
                  end
                else
                  begin
                    if @CURRENCYEXCHANGERATEID is null
                      set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

                    set @LISTORGANIZATIONRENTALCOST = dbo.[UFN_CURRENCY_CONVERT](@LISTRENTALCOST, @CURRENCYEXCHANGERATEID);
                    set @LISTORGANIZATIONEXCHANGECOST = dbo.[UFN_CURRENCY_CONVERT](@LISTEXCHANGECOST, @CURRENCYEXCHANGERATEID);
                  end

       update dbo.[MKTLIST] set
                  [NAME] = @LISTNAME,
                  [DESCRIPTION] = @LISTDESCRIPTION,
                  [PARTDEFINITIONVALUESID] = @LISTCODEVALUEID,
                  [CODE] = @LISTCODE,
                  [LISTCATEGORYCODEID] = @LISTCATEGORYCODEID,
                  [VENDORID] = @LISTVENDORID,
                  [ORGANIZATIONBASERENTALCOST] = @LISTORGANIZATIONRENTALCOST,
                  [ORGANIZATIONBASEEXCHANGECOST] = @LISTORGANIZATIONEXCHANGECOST,
                  [CURRENCYEXCHANGERATEID] = @CURRENCYEXCHANGERATEID,
                  [SITEID] = @LISTSITEID,
                  [CHANGEDBYID] = @CHANGEAGENTID,
                  [DATECHANGED] = @CURRENTDATE
                where [ID] = @LISTID;
              end
            else
              begin
                if @LISTID is null
                  set @LISTID = newid();

                --This batch does not currently save the cost fields on the list record, but we still need to make sure
                --we set the exchange rate and organization amounts correct.
                if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                  begin
                    set @CURRENCYEXCHANGERATEID = null;
                    set @LISTORGANIZATIONRENTALCOST = 0;
                    set @LISTORGANIZATIONEXCHANGECOST = 0;
                  end
                else
                  begin
                    set @CURRENCYEXCHANGERATEID = @CURRENCYEXCHANGERATEIDCURRENT;
                    set @LISTORGANIZATIONRENTALCOST = 0;
                    set @LISTORGANIZATIONEXCHANGECOST = 0;
                  end

                insert into dbo.[MKTLIST] (
                  [ID],
                  [NAME],
                  [DESCRIPTION],
                  [PARTDEFINITIONVALUESID],
                  [CODE],
                  [LISTCATEGORYCODEID],
                  [VENDORID],
                  [RECORDSOURCEID],
                  [BASECURRENCYID],
                  [ORGANIZATIONBASERENTALCOST],
                  [ORGANIZATIONBASEEXCHANGECOST],
                  [CURRENCYEXCHANGERATEID],
                  [SITEID],
                  [ADDEDBYID],
                  [CHANGEDBYID],
                  [DATEADDED],
                  [DATECHANGED]
                ) values (
                  @LISTID,
                  @LISTNAME,
                  @LISTDESCRIPTION,
                  @LISTCODEVALUEID,
                  @LISTCODE,
                  @LISTCATEGORYCODEID,
                  @LISTVENDORID,
                  @RECORDSOURCEID,
                  @BASECURRENCYID,
                  @LISTORGANIZATIONRENTALCOST,
                  @LISTORGANIZATIONEXCHANGECOST,
                  @CURRENCYEXCHANGERATEID,
                  @LISTSITEID,
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CURRENTDATE,
                  @CURRENTDATE
                );
              end
          end




        /********************/
        /* Save the package */
        /********************/
        if @EXISTINGPACKAGE = 1
          begin
            select
              @CURRENCYEXCHANGERATEID = [CURRENCYEXCHANGERATEID],
              @PACKAGECODE = (case when @PACKAGECODE = '' then [CODE] else @PACKAGECODE end),
              @PACKAGECODEVALUEID = (case when @PACKAGECODEVALUEID is null then [PARTDEFINITIONVALUESID] else @PACKAGECODEVALUEID end),
              @PACKAGECHANNELCODE = (case when @PACKAGECHANNELCODE = '' then [CHANNELSOURCECODE] else @PACKAGECHANNELCODE end),
              @PACKAGECHANNELCODEVALUEID = (case when @PACKAGECHANNELCODEVALUEID is null then [CHANNELPARTDEFINITIONVALUESID] else @PACKAGECHANNELCODEVALUEID end),
              @PACKAGECATEGORYCODEID = (case when @PACKAGECATEGORYCODEID is null then [PACKAGECATEGORYCODEID] else @PACKAGECATEGORYCODEID end),
              @PACKAGECOST = (case when @PACKAGECOST = 0 then [UNITCOST] else @PACKAGECOST end),
              @PACKAGECOSTDISTRIBUTIONMETHODCODE = (case when @PACKAGECOSTDISTRIBUTIONMETHODCODE = 0 then [COSTDISTRIBUTIONMETHODCODE] else @PACKAGECOSTDISTRIBUTIONMETHODCODE end),
              @PACKAGEDESCRIPTION = (case when @PACKAGEDESCRIPTION = '' then [DESCRIPTION] else @PACKAGEDESCRIPTION end),
              @PACKAGESITEID = (case when @PACKAGESITEID = [SITEID] or (@PACKAGESITEISNULL = 0  and @PACKAGESITEID is null) then [SITEID] else @PACKAGESITEID end),
              @DATEADDED = [DATEADDED]
            from dbo.[MKTPACKAGE]
            where [ID] = @PACKAGEID;

            if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
              set @PACKAGEORGANIZATIONCOST = @PACKAGECOST;
            else
              begin
                if @CURRENCYEXCHANGERATEID is null
                  set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

                set @PACKAGEORGANIZATIONCOST = dbo.[UFN_CURRENCY_CONVERT](@PACKAGECOST, @CURRENCYEXCHANGERATEID);
              end

            update dbo.[MKTPACKAGE] set
              [NAME] = @PACKAGENAME,
              [DESCRIPTION] = @PACKAGEDESCRIPTION,
              [PARTDEFINITIONVALUESID] = @PACKAGECODEVALUEID,
              [CODE] = @PACKAGECODE,
              [CHANNELPARTDEFINITIONVALUESID] = @PACKAGECHANNELCODEVALUEID,
              [CHANNELSOURCECODE] = @PACKAGECHANNELCODE,
              [PACKAGECATEGORYCODEID] = @PACKAGECATEGORYCODEID,
              [UNITCOST] = @PACKAGECOST,
              [COSTDISTRIBUTIONMETHODCODE] = @PACKAGECOSTDISTRIBUTIONMETHODCODE,
              [ORGANIZATIONUNITCOST] = @PACKAGEORGANIZATIONCOST,
              [CURRENCYEXCHANGERATEID] = @CURRENCYEXCHANGERATEID,
              [SITEID] = @PACKAGESITEID,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            where [ID] = @PACKAGEID;
          end
        else
          begin
            if @PACKAGEID is null
              set @PACKAGEID = newid();

            if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
              begin
                set @CURRENCYEXCHANGERATEID = null;
                set @PACKAGEORGANIZATIONCOST = @PACKAGECOST;
              end
            else
              begin
                set @CURRENCYEXCHANGERATEID = @CURRENCYEXCHANGERATEIDCURRENT;
                set @PACKAGEORGANIZATIONCOST = dbo.[UFN_CURRENCY_CONVERT](@PACKAGECOST, @CURRENCYEXCHANGERATEID);
              end

            insert into dbo.[MKTPACKAGE] (
              [ID],
              [NAME],
              [DESCRIPTION],
              [PARTDEFINITIONVALUESID],
              [CODE],
              [CHANNELPARTDEFINITIONVALUESID],
              [CHANNELSOURCECODE],
              [PACKAGECATEGORYCODEID],
              [CHANNELCODE],
              [UNITCOST],
              [COSTDISTRIBUTIONMETHODCODE],
              [BASECURRENCYID],
              [ORGANIZATIONUNITCOST],
              [CURRENCYEXCHANGERATEID],
              [SITEID],
              [ADDEDBYID],
              [CHANGEDBYID],
              [DATEADDED],
              [DATECHANGED]
            ) values (
              @PACKAGEID,
              @PACKAGENAME,
              @PACKAGEDESCRIPTION,
              @PACKAGECODEVALUEID,
              @PACKAGECODE,
              @PACKAGECHANNELCODEVALUEID,
              @PACKAGECHANNELCODE,
              @PACKAGECATEGORYCODEID,
              @PACKAGETYPECODE,
              @PACKAGECOST,
              @PACKAGECOSTDISTRIBUTIONMETHODCODE,
              @BASECURRENCYID,
              @PACKAGEORGANIZATIONCOST,
              @CURRENCYEXCHANGERATEID,
              @PACKAGESITEID,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            );
          end





        /********************************/
        /* Save the base segment record */
        /********************************/
        if @EXISTINGSEGMENT = 1
          begin
            select
              @OLDCODE = [MKTSEGMENT].[CODE],
              @OLDLISTID = [MKTSEGMENTLIST].[LISTID],
              @SEGMENTDESCRIPTION = (case when @SEGMENTDESCRIPTION = '' then [DESCRIPTION] else @SEGMENTDESCRIPTION end),
              @SEGMENTCODE = (case when @SEGMENTCODE = '' then [CODE] else @SEGMENTCODE end),
              @SEGMENTCODEVALUEID = (case when @SEGMENTCODEVALUEID is null then [PARTDEFINITIONVALUESID] else @SEGMENTCODEVALUEID end),
              @SEGMENTCATEGORYCODEID = (case when @SEGMENTCATEGORYCODEID is null then [SEGMENTCATEGORYCODEID] else @SEGMENTCATEGORYCODEID end),
              @SEGMENTSITEID = (case when @SEGMENTSITEID = [SITEID] or (@SEGMENTSITEISNULL = 0 and @SEGMENTSITEID is null) then [SITEID] else @SEGMENTSITEID end)
            from dbo.[MKTSEGMENT]
            left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
            where [MKTSEGMENT].[ID] = @SEGMENTID;

            update dbo.[MKTSEGMENT] set
              [NAME] = @SEGMENTNAME,
              [DESCRIPTION] = @SEGMENTDESCRIPTION,
              [PARTDEFINITIONVALUESID] = @SEGMENTCODEVALUEID,
              [CODE] = @SEGMENTCODE,
              [SEGMENTCATEGORYCODEID] = @SEGMENTCATEGORYCODEID,
              [SITEID] = @SEGMENTSITEID,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            where [ID] = @SEGMENTID;

            if @SEGMENTTYPECODE = 2 and @LISTID <> @OLDLISTID
              --Update the list ID, but only if they changed list ID...
              update dbo.[MKTSEGMENTLIST] set
                [LISTID] = @LISTID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              from dbo.[MKTSEGMENTLIST] as [SL]
              inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[CURRENTSEGMENTLISTID] = [SL].[ID]
              where [MKTSEGMENT].[ID] = @SEGMENTID;

            if @SEGMENTCODE <> @OLDCODE
              --Update the code on all the non-activated mailing segments that use the same base segment, but only if they changed the code...
              update dbo.[MKTSEGMENTATIONSEGMENT] set
                [CODE] = @SEGMENTCODE,
                [PARTDEFINITIONVALUESID] = @SEGMENTCODEVALUEID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
              inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [SS].[SEGMENTATIONID]
              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 [SS].[SEGMENTID] = @SEGMENTID
              and [MKTSEGMENTATION].[ACTIVE] = 0  --Not activated
              and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1;  --Segment sourcecode part
          end
        else
          begin
            if @SEGMENTID is null
              set @SEGMENTID = newid();

            insert into dbo.[MKTSEGMENT] (
              [ID],
              [NAME],
              [DESCRIPTION],
              [PARTDEFINITIONVALUESID],
              [CODE],
              [SEGMENTCATEGORYCODEID],
              [SEGMENTTYPECODE],
              [QUERYVIEWCATALOGID],
              [SITEID],
              [ADDEDBYID],
              [CHANGEDBYID],
              [DATEADDED],
              [DATECHANGED]
            ) values (
              @SEGMENTID,
              @SEGMENTNAME,
              @SEGMENTDESCRIPTION,
              @SEGMENTCODEVALUEID,
              @SEGMENTCODE,
              @SEGMENTCATEGORYCODEID,
              @SEGMENTTYPECODE,
              @RECORDSOURCEID,
              @SEGMENTSITEID,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            );

 if @SEGMENTTYPECODE = 1 --Constituent
              begin
                --Insert a parameter set for the refresh process for this segment...
                insert into dbo.[MKTSEGMENTREFRESHPROCESS] (
                  [ID],
                  [SEGMENTID],
                  [ADDEDBYID],
                  [CHANGEDBYID],
                  [DATEADDED],
                  [DATECHANGED]
                ) values (
                  newid(),
                  @SEGMENTID,
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CURRENTDATE,
                  @CURRENTDATE
                );

                --Create the base segment view and add it to the IDSETREGISTER table...
                exec dbo.[USP_MKTSEGMENT_CREATEORUPDATEVIEW] @SEGMENTID, @CHANGEAGENTID;
              end
            else if @SEGMENTTYPECODE = 2 --List
              begin
                --Save the list segment specific info...
                declare @MKTSEGMENTLISTID uniqueidentifier = newid();

                if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                  begin
                    set @CURRENCYEXCHANGERATEID = null;
                    set @SEGMENTORGANIZATIONRENTALCOST = @SEGMENTRENTALCOST;
                    set @SEGMENTORGANIZATIONEXCHANGECOST = @SEGMENTEXCHANGECOST;
                  end
                else
                  begin
                    set @CURRENCYEXCHANGERATEID = @CURRENCYEXCHANGERATEIDCURRENT;
                    set @SEGMENTORGANIZATIONRENTALCOST = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTRENTALCOST, @CURRENCYEXCHANGERATEID);
                    set @SEGMENTORGANIZATIONEXCHANGECOST = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTEXCHANGECOST, @CURRENCYEXCHANGERATEID);
                  end

                insert into dbo.[MKTSEGMENTLIST] (
                  [ID],
                  [SEGMENTID],
                  [LISTID],
                  [TYPECODE],
                  [STATUSCODE],
                  [RENTALQUANTITY],
                  [RENTALCOSTADJUSTMENT],
                  [RENTALCOSTBASISCODE],
                  [EXCHANGEQUANTITY],
                  [EXCHANGECOSTADJUSTMENT],
                  [EXCHANGECOSTBASISCODE],
                  [BASECURRENCYID],
                  [ORGANIZATIONRENTALCOSTADJUSTMENT],
                  [ORGANIZATIONEXCHANGECOSTADJUSTMENT],
                  [CURRENCYEXCHANGERATEID],
                  [ADDEDBYID],
                  [CHANGEDBYID],
                  [DATEADDED],
                  [DATECHANGED]
                ) values (
                  @MKTSEGMENTLISTID,
                  @SEGMENTID,
                  @LISTID,
                  1, --Vendor managed
                  3, --Active
                  @SEGMENTRENTALQUANTITY,
                  @SEGMENTRENTALCOST,
                  @SEGMENTRENTALCOSTBASISCODE,
                  @SEGMENTEXCHANGEQUANTITY,
                  @SEGMENTEXCHANGECOST,
                  @SEGMENTEXCHANGECOSTBASISCODE,
                  @BASECURRENCYID,
                  @SEGMENTORGANIZATIONRENTALCOST,
                  @SEGMENTORGANIZATIONEXCHANGECOST,
                  @CURRENCYEXCHANGERATEID,
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CURRENTDATE,
                  @CURRENTDATE
                );

                --Update the segment to hold the current list segment record ID...
                --Need to set the CODE field again here so the check constraint gets validated now that we have set the CURRENTSEGMENTLISTID field.
                update dbo.[MKTSEGMENT] set
                  [CURRENTSEGMENTLISTID] = @MKTSEGMENTLISTID,
                  [CODE] = @SEGMENTCODE,
                  [BASECURRENCYID] = @BASECURRENCYID,
                  [CHANGEDBYID] = @CHANGEAGENTID,
                  [DATECHANGED] = @CURRENTDATE
                where [ID] = @SEGMENTID;
              end
          end




        /***********************************/
        /* Save the mailing segment record */
        /***********************************/
        --Only if this row doesn't have test segment info...
        if @SAVETESTSEGMENTRECORD = 0
          begin
            if @EXISTINGSEGMENT = 1 and @SEGMENTATIONSEGMENTID is not null
              begin
                select
                  @OLDSEGMENTID = [MKTSEGMENTATIONSEGMENT].[SEGMENTID],
                  @OLDPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
                  @OLDPACKAGETYPECODE = [MKTPACKAGE].[CHANNELCODE],
                  @USEADDRESSPROCESSING = (case when [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] = 1 and [MKTSEGMENTATIONSEGMENT].[ADDRESSPROCESSINGOPTIONID] is not null then 1 else 0 end),
                  @SEQUENCE = [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
                  @CURRENCYEXCHANGERATEID = [MKTSEGMENTATIONSEGMENT].[CURRENCYEXCHANGERATEID],
                  @DATEADDED = [MKTSEGMENTATIONSEGMENT].[DATEADDED]
                from dbo.[MKTSEGMENTATIONSEGMENT]
                inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
                where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;

                if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                  set @SEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT = @SEGMENTEXPECTEDGIFTAMOUNT;
                else
                  begin
                    if @CURRENCYEXCHANGERATEID is null
                      set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

                    set @SEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTEXPECTEDGIFTAMOUNT, @CURRENCYEXCHANGERATEID);
                  end

                update dbo.[MKTSEGMENTATIONSEGMENT] set 
                  [SEGMENTID] = @SEGMENTID,
                  [PARTDEFINITIONVALUESID] = @SEGMENTCODEVALUEID,
                  [CODE] = @SEGMENTCODE,
                  [TESTPARTDEFINITIONVALUESID] = @TESTSEGMENTCODEVALUEID,
                  [TESTSEGMENTCODE] = @TESTSEGMENTCODE,
                  [PACKAGEID] = @PACKAGEID,
                  [RESPONSERATE] = @SEGMENTEXPECTEDRESPONSERATE,
                  [GIFTAMOUNT] = @SEGMENTEXPECTEDGIFTAMOUNT,
                  [USAGECODE] = @SEGMENTUSAGECODE,
                  [ORGANIZATIONGIFTAMOUNT] = @SEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT,
                  [CURRENCYEXCHANGERATEID] = @CURRENCYEXCHANGERATEID,
                  [CHANGEDBYID] = @CHANGEAGENTID,
                  [DATECHANGED] = @CURRENTDATE
                where [ID] = @SEGMENTATIONSEGMENTID;

                --Clear the segment cache if the segment changed or if address processing is being used and the new package has a different
                --channel than the old one, since the package's channel can affect record counts when address processing is used.
                if (@OLDSEGMENTID <> @SEGMENTID) or (@OLDPACKAGETYPECODE <> @PACKAGETYPECODE and @USEADDRESSPROCESSING = 1)
                  begin
                    --Clear the cache for this segment and all segments after it with the same record type...
                    exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @SEGMENTATIONSEGMENTID, 1, 1;
                  end

                --Parse the full sourcecode and save any user defined parts that override the marketing effort defaults...
                exec dbo.[USP_MKTSOURCECODEPART_SAVEUSERDEFINEDPARTSFROMCODE] @SOURCECODE, @SEGMENTATIONSEGMENTID, null, @CHANGEAGENTID, @CURRENTDATE;

                if @SEGMENTTYPECODE = 2
                  begin
                    --Set the list quantities and cost overrides (we always set the quantities as overrides here)...
                    if not exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENTLIST] where [ID] = @SEGMENTATIONSEGMENTID)
                      begin
                        if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                          begin
                            set @CURRENCYEXCHANGERATEID = null;

                            select
                              @LISTORGANIZATIONRENTALCOST = [MKTLIST].[BASERENTALCOST],
                              @LISTORGANIZATIONEXCHANGECOST = [MKTLIST].[BASEEXCHANGECOST]
                            from dbo.[MKTSEGMENT]
                            inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                            inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
                            where [MKTSEGMENT].[ID] = @SEGMENTID;

                            set @SEGMENTORGANIZATIONRENTALCOST = @SEGMENTRENTALCOST;
                            set @SEGMENTORGANIZATIONEXCHANGECOST = @SEGMENTEXCHANGECOST;
                          end
                        else
                          begin
                            set @CURRENCYEXCHANGERATEID = @CURRENCYEXCHANGERATEIDCURRENT;

                            select
                              @LISTORGANIZATIONRENTALCOST = dbo.[UFN_CURRENCY_CONVERT]([MKTLIST].[BASERENTALCOST], @CURRENCYEXCHANGERATEID),
                              @LISTORGANIZATIONEXCHANGECOST = dbo.[UFN_CURRENCY_CONVERT]([MKTLIST].[BASEEXCHANGECOST], @CURRENCYEXCHANGERATEID)
                            from dbo.[MKTSEGMENT]
                            inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                            inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
                            where [MKTSEGMENT].[ID] = @SEGMENTID;

                            set @SEGMENTORGANIZATIONRENTALCOST = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTRENTALCOST, @CURRENCYEXCHANGERATEID);
                            set @SEGMENTORGANIZATIONEXCHANGECOST = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTEXCHANGECOST, @CURRENCYEXCHANGERATEID);
                          end

                        insert into dbo.[MKTSEGMENTATIONSEGMENTLIST] (
                          [ID],
                          [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
                          [BASERENTALCOST],
                          [BASERENTALCOSTBASISCODE],
                          [BASEEXCHANGECOST],
                          [BASEEXCHANGECOSTBASISCODE],
                          [RENTALQUANTITY],
                          [RENTALCOSTADJUSTMENT],
                          [RENTALCOSTADJUSTMENTBASISCODE],
                          [EXCHANGEQUANTITY],
                          [EXCHANGECOSTADJUSTMENT],
                          [EXCHANGECOSTADJUSTMENTBASISCODE],
                          [BASECURRENCYID],
                          [ORGANIZATIONBASERENTALCOST],
                          [ORGANIZATIONBASEEXCHANGECOST],
                          [ORGANIZATIONRENTALCOSTADJUSTMENT],
                          [ORGANIZATIONEXCHANGECOSTADJUSTMENT],
                          [ORGANIZATIONCURRENCYEXCHANGERATEID],
                          [ADDEDBYID],
                          [CHANGEDBYID],
                          [DATEADDED],
                          [DATECHANGED]
                        )
                        select 
                          @SEGMENTATIONSEGMENTID,
                          1,
                          [MKTLIST].[BASERENTALCOST],
                          [MKTLIST].[BASERENTALCOSTBASISCODE],
                          [MKTLIST].[BASEEXCHANGECOST],
                          [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
                          @SEGMENTRENTALQUANTITY,
                          @SEGMENTRENTALCOST,
                          @SEGMENTRENTALCOSTBASISCODE,
                          @SEGMENTEXCHANGEQUANTITY,
                          @SEGMENTEXCHANGECOST,
                          @SEGMENTEXCHANGECOSTBASISCODE,
                          @BASECURRENCYID,
                          @LISTORGANIZATIONRENTALCOST,
                          @LISTORGANIZATIONEXCHANGECOST,
                          @SEGMENTORGANIZATIONRENTALCOST,
           @SEGMENTORGANIZATIONEXCHANGECOST,
                          @CURRENCYEXCHANGERATEID,
                          @CHANGEAGENTID,
                          @CHANGEAGENTID,
                          @CURRENTDATE,
                          @CURRENTDATE
                        from dbo.[MKTSEGMENT]
                        inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                        inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
                        where [MKTSEGMENT].[ID] = @SEGMENTID;
                      end
                    else
                      begin
                        select
                          @CURRENCYEXCHANGERATEID = [ORGANIZATIONCURRENCYEXCHANGERATEID],
                          @DATEADDED = [DATEADDED]
                        from dbo.[MKTSEGMENTATIONSEGMENTLIST]
                        where [ID] = @SEGMENTATIONSEGMENTID;

                        if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                          begin
                            set @SEGMENTORGANIZATIONRENTALCOST = @SEGMENTRENTALCOST;
                            set @SEGMENTORGANIZATIONEXCHANGECOST = @SEGMENTEXCHANGECOST;
                          end
                        else
                          begin
                            if @CURRENCYEXCHANGERATEID is null
                              set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

                            set @SEGMENTORGANIZATIONRENTALCOST = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTRENTALCOST, @CURRENCYEXCHANGERATEID);
                            set @SEGMENTORGANIZATIONEXCHANGECOST = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTEXCHANGECOST, @CURRENCYEXCHANGERATEID);
                          end

                        update dbo.[MKTSEGMENTATIONSEGMENTLIST] set 
                          [OVERRIDEQUANTITIESANDORSEGMENTCOSTS] = 1,
                          [RENTALQUANTITY] = @SEGMENTRENTALQUANTITY,
                          [RENTALCOSTADJUSTMENT] = @SEGMENTRENTALCOST,
                          [RENTALCOSTADJUSTMENTBASISCODE] = @SEGMENTRENTALCOSTBASISCODE,
                          [EXCHANGEQUANTITY] = @SEGMENTEXCHANGEQUANTITY,
                          [EXCHANGECOSTADJUSTMENT] = @SEGMENTEXCHANGECOST,
                          [EXCHANGECOSTADJUSTMENTBASISCODE] = @SEGMENTEXCHANGECOSTBASISCODE,
                          [ORGANIZATIONRENTALCOSTADJUSTMENT] = @SEGMENTORGANIZATIONRENTALCOST,
                          [ORGANIZATIONEXCHANGECOSTADJUSTMENT] = @SEGMENTORGANIZATIONEXCHANGECOST,
                          [ORGANIZATIONCURRENCYEXCHANGERATEID] = @CURRENCYEXCHANGERATEID,
                          [CHANGEDBYID] = @CHANGEAGENTID,
                          [DATECHANGED] = @CURRENTDATE
                        where [ID] = @SEGMENTATIONSEGMENTID;
                      end
                  end
              end
            else
              begin
                set @SEGMENTATIONSEGMENTID = newid();

                --Get the next segment sequence...
                select
                  @SEQUENCE = isnull(max([SEQUENCE]), 0) + 1
                from dbo.[MKTSEGMENTATIONSEGMENT]
                where [SEGMENTATIONID] = @SEGMENTATIONID;

                if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                  begin
                    set @CURRENCYEXCHANGERATEID = null;
                    set @SEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT = @SEGMENTEXPECTEDGIFTAMOUNT;

                    if @SEGMENTTYPECODE = 2
                      begin
                        set @SEGMENTORGANIZATIONRENTALCOST = @SEGMENTRENTALCOST;
                        set @SEGMENTORGANIZATIONEXCHANGECOST = @SEGMENTEXCHANGECOST;

                        select
                          @LISTORGANIZATIONRENTALCOST = [MKTLIST].[BASERENTALCOST],
                          @LISTORGANIZATIONEXCHANGECOST = [MKTLIST].[BASEEXCHANGECOST]
                        from dbo.[MKTSEGMENT]
                        inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                        inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
                        where [MKTSEGMENT].[ID] = @SEGMENTID;
                      end
                  end
                else
                  begin
                    set @CURRENCYEXCHANGERATEID = @CURRENCYEXCHANGERATEIDCURRENT;
                    set @SEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTEXPECTEDGIFTAMOUNT, @CURRENCYEXCHANGERATEID);

                    if @SEGMENTTYPECODE = 2
                      begin
                        set @SEGMENTORGANIZATIONRENTALCOST = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTRENTALCOST, @CURRENCYEXCHANGERATEID);
                        set @SEGMENTORGANIZATIONEXCHANGECOST = dbo.[UFN_CURRENCY_CONVERT](@SEGMENTEXCHANGECOST, @CURRENCYEXCHANGERATEID);

                        select
                          @LISTORGANIZATIONRENTALCOST = dbo.[UFN_CURRENCY_CONVERT]([MKTLIST].[BASERENTALCOST], @CURRENCYEXCHANGERATEID),
                          @LISTORGANIZATIONEXCHANGECOST = dbo.[UFN_CURRENCY_CONVERT]([MKTLIST].[BASEEXCHANGECOST], @CURRENCYEXCHANGERATEID)
                        from dbo.[MKTSEGMENT]
                        inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                        inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
                        where [MKTSEGMENT].[ID] = @SEGMENTID;
                      end
                  end

                insert into dbo.[MKTSEGMENTATIONSEGMENT] (
                  [ID],
                  [SEGMENTATIONID],
                  [SEGMENTID],
                  [PARTDEFINITIONVALUESID],
                  [CODE],
                  [TESTSEGMENTCODE],
                  [TESTPARTDEFINITIONVALUESID],
                  [PACKAGEID],
                  [RESPONSERATE],
                  [GIFTAMOUNT],
                  [USAGECODE],
                  [SEQUENCE],
                  [NEXTBRIEFSEQUENCE],
                  [BASECURRENCYID],
                  [ORGANIZATIONGIFTAMOUNT],
                  [CURRENCYEXCHANGERATEID],
                  [ADDEDBYID],
                  [CHANGEDBYID],
                  [DATEADDED],
                  [DATECHANGED]
                ) values (
                  @SEGMENTATIONSEGMENTID,
                  @SEGMENTATIONID,
                  @SEGMENTID,
                  @SEGMENTCODEVALUEID,
                  @SEGMENTCODE,
                  @TESTSEGMENTCODE,
                  @TESTSEGMENTCODEVALUEID,
                  @PACKAGEID,
                  @SEGMENTEXPECTEDRESPONSERATE,
                  @SEGMENTEXPECTEDGIFTAMOUNT,
                  @SEGMENTUSAGECODE,
                  @SEQUENCE,
                  1,
                  @BASECURRENCYID,
                  @SEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT,
                  @CURRENCYEXCHANGERATEID,
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CURRENTDATE,
                  @CURRENTDATE
                );

                --Save and add the package to the mailing, only if it doesn't already exist...
                if not exists(select [ID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
                  exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPACKAGE] null, @CHANGEAGENTID, @SEGMENTATIONID, @PACKAGEID;

                --Parse the full sourcecode and save any user defined parts that override the marketing effort defaults...
                exec dbo.[USP_MKTSOURCECODEPART_SAVEUSERDEFINEDPARTSFROMCODE] @SOURCECODE, @SEGMENTATIONSEGMENTID, null, @CHANGEAGENTID, @CURRENTDATE;

                if @SEGMENTTYPECODE = 2
                  begin
   --Set the list quantities and cost overrides (we always set the quantities as overrides here)...
                    insert into dbo.[MKTSEGMENTATIONSEGMENTLIST] (
                      [ID],
                      [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
                      [BASERENTALCOST],
                      [BASERENTALCOSTBASISCODE],
                      [BASEEXCHANGECOST],
                      [BASEEXCHANGECOSTBASISCODE],
                      [RENTALQUANTITY],
                      [RENTALCOSTADJUSTMENT],
                      [RENTALCOSTADJUSTMENTBASISCODE],
                      [EXCHANGEQUANTITY],
                      [EXCHANGECOSTADJUSTMENT],
                      [EXCHANGECOSTADJUSTMENTBASISCODE],
                      [BASECURRENCYID],
                      [ORGANIZATIONBASERENTALCOST],
                      [ORGANIZATIONBASEEXCHANGECOST],
                      [ORGANIZATIONRENTALCOSTADJUSTMENT],
                      [ORGANIZATIONEXCHANGECOSTADJUSTMENT],
                      [ORGANIZATIONCURRENCYEXCHANGERATEID],
                      [ADDEDBYID],
                      [CHANGEDBYID],
                      [DATEADDED],
                      [DATECHANGED]
                    )
                    select 
                      @SEGMENTATIONSEGMENTID,
                      1,
                      [MKTLIST].[BASERENTALCOST],
                      [MKTLIST].[BASERENTALCOSTBASISCODE],
                      [MKTLIST].[BASEEXCHANGECOST],
                      [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
                      @SEGMENTRENTALQUANTITY,
                      @SEGMENTRENTALCOST,
                      @SEGMENTRENTALCOSTBASISCODE,
                      @SEGMENTEXCHANGEQUANTITY,
                      @SEGMENTEXCHANGECOST,
                      @SEGMENTEXCHANGECOSTBASISCODE,
                      @BASECURRENCYID,
                      @LISTORGANIZATIONRENTALCOST,
                      @LISTORGANIZATIONEXCHANGECOST,
                      @SEGMENTORGANIZATIONRENTALCOST,
                      @SEGMENTORGANIZATIONEXCHANGECOST,
                      @CURRENCYEXCHANGERATEID,
                      @CHANGEAGENTID,
                      @CHANGEAGENTID,
                      @CURRENTDATE,
                      @CURRENTDATE
                    from dbo.[MKTSEGMENT]
                    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                    inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
                    where [MKTSEGMENT].[ID] = @SEGMENTID;
                  end
              end
          end




        /*************************/
        /* Save the test segment */
        /*************************/
        if @SAVETESTSEGMENTRECORD = 1
          begin
            if @EXISTINGTESTSEGMENT = 1
              begin
                select
                  @OLDPACKAGEID = [PACKAGEID],
                  @CURRENCYEXCHANGERATEID = [ORGANIZATIONCURRENCYEXCHANGERATEID],
                  @DATEADDED = [DATEADDED]
                from dbo.[MKTSEGMENTATIONTESTSEGMENT]
                where [ID] = @TESTSEGMENTID;

                if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                  set @TESTSEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT = @TESTSEGMENTEXPECTEDGIFTAMOUNT;
                else
                  begin
                    if @CURRENCYEXCHANGERATEID is null
                      set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

                    set @TESTSEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@TESTSEGMENTEXPECTEDGIFTAMOUNT, @CURRENCYEXCHANGERATEID);
                  end

                update dbo.[MKTSEGMENTATIONTESTSEGMENT] set
                  [PREFIXCODE] = 0,
                  [NAME] = @TESTSEGMENTNAME,
                  [DESCRIPTION] = @TESTSEGMENTDESCRIPTION,
                  [PARTDEFINITIONVALUESID] = @SEGMENTCODEVALUEID,
                  [CODE] = @SEGMENTCODE,
                  [TESTPARTDEFINITIONVALUESID] = @TESTSEGMENTCODEVALUEID,
                  [TESTSEGMENTCODE] = @TESTSEGMENTCODE,
                  [PACKAGEID] = @PACKAGEID,
                  [GIFTAMOUNT] = @TESTSEGMENTEXPECTEDGIFTAMOUNT,
                  [RESPONSERATE] = @TESTSEGMENTEXPECTEDRESPONSERATE,
                  [SAMPLESIZE] = @TESTSEGMENTQUANTITY,
                  [SAMPLESIZETYPECODE] = 1,
                  [ORGANIZATIONGIFTAMOUNT] = @TESTSEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT,
                  [ORGANIZATIONCURRENCYEXCHANGERATEID] = @CURRENCYEXCHANGERATEID,
                  [CHANGEDBYID] = @CHANGEAGENTID,
                  [DATECHANGED] = @CURRENTDATE
                where [ID] = @TESTSEGMENTID;
              end
            else
              begin
                if @TESTSEGMENTID is null
                  set @TESTSEGMENTID = newid();

                --Get the next test segment sequence...
                select
                  @SEQUENCE = isnull(max([SEQUENCE]), 0) + 1
                from dbo.[MKTSEGMENTATIONTESTSEGMENT]
                where [SEGMENTID] = @SEGMENTATIONSEGMENTID;

                if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                  begin
                    set @CURRENCYEXCHANGERATEID = null;
                    set @TESTSEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT = @TESTSEGMENTEXPECTEDGIFTAMOUNT;
                  end
                else
                  begin
                    set @CURRENCYEXCHANGERATEID = @CURRENCYEXCHANGERATEIDCURRENT;
                    set @TESTSEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@TESTSEGMENTEXPECTEDGIFTAMOUNT, @CURRENCYEXCHANGERATEID);
                  end

                insert into dbo.[MKTSEGMENTATIONTESTSEGMENT] (
                  [ID],
                  [SEGMENTID],
                  [NAME],
                  [DESCRIPTION],
                  [PARTDEFINITIONVALUESID],
                  [CODE],
                  [TESTPARTDEFINITIONVALUESID],
                  [TESTSEGMENTCODE],
                  [PACKAGEID],
                  [GIFTAMOUNT],
                  [RESPONSERATE],
                  [SAMPLESIZE],
                  [SAMPLESIZETYPECODE],
                  [SEQUENCE],
                  [BASECURRENCYID],
                  [ORGANIZATIONGIFTAMOUNT],
                  [ORGANIZATIONCURRENCYEXCHANGERATEID],
                  [ADDEDBYID],
                  [CHANGEDBYID],
                  [DATEADDED],
                  [DATECHANGED]
                ) values (
                  @TESTSEGMENTID,
                  @SEGMENTATIONSEGMENTID,
                  @TESTSEGMENTNAME,
                  @TESTSEGMENTDESCRIPTION,
                  @SEGMENTCODEVALUEID,
                  @SEGMENTCODE,
                  @TESTSEGMENTCODEVALUEID,
                  @TESTSEGMENTCODE,
                  @PACKAGEID,
                  @TESTSEGMENTEXPECTEDGIFTAMOUNT,
                  @TESTSEGMENTEXPECTEDRESPONSERATE,
                  @TESTSEGMENTQUANTITY,
                  1,
                  @SEQUENCE,
                  @BASECURRENCYID,
                  @TESTSEGMENTORGANIZATIONEXPECTEDGIFTAMOUNT,
                  @CURRENCYEXCHANGERATEID,
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CURRENTDATE,
                  @CURRENTDATE
                );
              end

            --Parse the full sourcecode and save any user defined parts that override the marketing effort defaults...
            exec dbo.[USP_MKTSOURCECODEPART_SAVEUSERDEFINEDPARTSFROMCODE] @SOURCECODE, null, @TESTSEGMENTID, @CHANGEAGENTID, @CURRENTDATE;
          end




        /***********************************************************/
        /* Update the package information for the marketing effort */
        /***********************************************************/
        if ((@EXISTINGSEGMENT = 1 and @SEGMENTATIONSEGMENTID is not null) or @EXISTINGTESTSEGMENT = 1) and @OLDPACKAGEID <> @PACKAGEID
          begin
            --Remove the old package, only if is not being used by any other segments and test segments...
            if not exists(select 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @OLDPACKAGEID) and
               not exists(select 1 from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @OLDPACKAGEID)
              begin
                declare @OLDSEGMENTATIONPACKAGEID uniqueidentifier;

                select
                  @OLDSEGMENTATIONPACKAGEID = [ID]
                from dbo.[MKTSEGMENTATIONPACKAGE]
                where [SEGMENTATIONID] = @SEGMENTATIONID
                and [PACKAGEID] = @OLDPACKAGEID;

                exec dbo.[USP_MKTSEGMENTATIONPACKAGE_DELETE] @OLDSEGMENTATIONPACKAGEID, @CHANGEAGENTID;
              end
          end

        --Add the new package, only if it doesn't exist already...
        if not exists(select 1 from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
          exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPACKAGE] null, @CHANGEAGENTID, @SEGMENTATIONID, @PACKAGEID;




        /*****************************************************************************/
        /* Return an meaningful ID that can be used to identify the committed record */
        /*****************************************************************************/
        if @SAVETESTSEGMENTRECORD = 1
          set @ID = @TESTSEGMENTID;
        else
          set @ID = @SEGMENTATIONSEGMENTID;
      end
  end try

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

  return 0;