USP_DATAFORMTEMPLATE_ADD_DIRECTMARKETINGEFFORTBATCHROW

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_DIRECTMARKETINGEFFORTBATCHROW]
(
  @ID uniqueidentifier = null output,
  @BATCHID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @SEQUENCE int,
  @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 @CURRENTDATE datetime;
  declare @BATCHSEGMENTID uniqueidentifier;
  declare @BATCHPACKAGEID uniqueidentifier;
  declare @BATCHLISTID uniqueidentifier;
  declare @BATCHTESTSEGMENTID uniqueidentifier;
  declare @EXISTINGLIST bit = 0;
  declare @EXISTINGSEGMENT bit = 0;
  declare @EXISTINGTESTSEGMENT bit = 0;
  declare @EXISTINGPACKAGE bit = 0;
  declare @SAVELISTRECORD bit = 1;
  declare @SAVETESTSEGMENTRECORD bit = 1;
  declare @SEGMENTATIONSEGMENTID uniqueidentifier;

  begin try
    --This logic is only here so that we can successfully save exception batches during commit for new records, otherwise we'll hit errors
    --because the IDs won't exist.  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.  And since we're trying to save an exception batch, we don't know what the original batch number was, so we
    --have to check the entire table to see if the primary key exists or not.
    if exists(select 1 from dbo.[BATCHDIRECTMARKETINGEFFORTLIST] where [ID] = @LISTID)
      set @LISTID = null;
    if exists(select 1 from dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] where [ID] = @SEGMENTID)
      set @SEGMENTID = null;
    if exists(select 1 from dbo.[BATCHDIRECTMARKETINGEFFORTTESTSEGMENT] where [ID] = @TESTSEGMENTID)
      set @TESTSEGMENTID = null;
    if exists(select 1 from dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] where [ID] = @PACKAGEID)
      set @PACKAGEID = null;



    /***************************************/
    /* Validate the data before we save it */
  /***************************************/
    exec dbo.[USP_DIRECTMARKETINGEFFORTBATCH_VALIDATEROW]
      @BATCHID = @BATCHID,
      @VALIDATEFORCOMMIT = 0,
      @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;

    /**************************************************/
    /* Save the batch row into the appropriate tables */
    /**************************************************/
    if @ID is null
      set @ID = newid();

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

    set @CURRENTDATE = getdate();


    --Save the list record (only if we need to)...
    if @SAVELISTRECORD = 1
      begin
        if @EXISTINGLIST = 1
          --For any values that were not specified pull the values from the existing record...
          select
            @LISTNAME = (case when @LISTNAME = '' then [NAME] else @LISTNAME end),
            @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)
          from dbo.[MKTLIST]
          where [ID] = @LISTID;

        if @BATCHLISTID is null
          begin
            set @BATCHLISTID = newid();

            --Insert a new list record for the batch...
            insert into dbo.[BATCHDIRECTMARKETINGEFFORTLIST] (
              [ID],
              [MKTLISTID],
              [NAME],
              [DESCRIPTION],
              [CODE],
              [CODEPARTDEFINITIONVALUESID],
              [MKTLISTCATEGORYCODEID],
              [VENDORID],
              [SITEID],
              [LISTSITEISNULL],
              [ADDEDBYID],
              [CHANGEDBYID],
              [DATEADDED],
              [DATECHANGED]
            ) values (
              @BATCHLISTID,
              @LISTID,
              @LISTNAME,
              @LISTDESCRIPTION,
              @LISTCODE,
              @LISTCODEVALUEID,
              @LISTCATEGORYCODEID,
              @LISTVENDORID,
              @LISTSITEID,
              @LISTSITEISNULL,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
            );
          end
        else
          --The list record already exists in this table, so just update the existing row.
          --Because of how batch works, the last row to update a list record wins.
          update dbo.[BATCHDIRECTMARKETINGEFFORTLIST] set
            [MKTLISTID] = @LISTID,
            [NAME] = @LISTNAME,
            [DESCRIPTION] = @LISTDESCRIPTION,
            [CODE] = @LISTCODE,
            [CODEPARTDEFINITIONVALUESID] = @LISTCODEVALUEID,
            [MKTLISTCATEGORYCODEID] = @LISTCATEGORYCODEID,
            [VENDORID] = @LISTVENDORID,
            [SITEID] = @LISTSITEID,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @BATCHLISTID;
      end



    if @EXISTINGSEGMENT = 1
      --For any values that were not specified pull the values from the existing record...
      select
        @SEGMENTNAME = (case when @SEGMENTNAME = '' then [NAME] else @SEGMENTNAME end),
        @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]
      where [ID] = @SEGMENTID;

    --Save the segment record...
    if @BATCHSEGMENTID is null
      begin
        set @BATCHSEGMENTID = newid();

        --Insert a new segment record for the batch...
        insert into dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] (
          [ID],
          [MKTSEGMENTID],
          [NAME],
          [DESCRIPTION],
          [CODE],
          [CODEPARTDEFINITIONVALUESID],
          [MKTSEGMENTCATEGORYCODEID],
          [SEGMENTTYPECODE],
          [SITEID],
          [SEGMENTSITEISNULL],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @BATCHSEGMENTID,
          @SEGMENTID,
          @SEGMENTNAME,
          @SEGMENTDESCRIPTION,
          @SEGMENTCODE,
          @SEGMENTCODEVALUEID,
          @SEGMENTCATEGORYCODEID,
          @SEGMENTTYPECODE,
          @SEGMENTSITEID,
          @SEGMENTSITEISNULL,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end
    else
      --The segment record already exists in this table, so just update the existing row.
      --Because of how batch works, the last row to update a segment record wins.
      update dbo.[BATCHDIRECTMARKETINGEFFORTSEGMENT] set
        [MKTSEGMENTID] = @SEGMENTID,
        [NAME] = @SEGMENTNAME,
        [DESCRIPTION] = @SEGMENTDESCRIPTION,
        [CODE] = @SEGMENTCODE,
        [CODEPARTDEFINITIONVALUESID] = @SEGMENTCODEVALUEID,
        [MKTSEGMENTCATEGORYCODEID] = @SEGMENTCATEGORYCODEID,
        [SEGMENTTYPECODE] = @SEGMENTTYPECODE,
        [SITEID] = @SEGMENTSITEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @BATCHSEGMENTID;



    --Save the test segment record (only if we need to)...
    if @SAVETESTSEGMENTRECORD = 1
      begin
        if @EXISTINGTESTSEGMENT = 1
          --For any values that were not specified pull the values from the existing record...
          select
            @TESTSEGMENTNAME = (case when @TESTSEGMENTNAME = '' then [NAME] else @TESTSEGMENTNAME end),
            @TESTSEGMENTDESCRIPTION = (case when @TESTSEGMENTDESCRIPTION = '' then [DESCRIPTION] else @TESTSEGMENTDESCRIPTION end),
            @TESTSEGMENTCODE = (case when @TESTSEGMENTCODE = '' then [TESTSEGMENTCODE] else @TESTSEGMENTCODE end),
            @TESTSEGMENTCODEVALUEID = (case when @TESTSEGMENTCODEVALUEID is null then [TESTPARTDEFINITIONVALUESID] else @TESTSEGMENTCODEVALUEID end),
            @TESTSEGMENTEXPECTEDGIFTAMOUNT = (case when @TESTSEGMENTEXPECTEDGIFTAMOUNT = 0 then [GIFTAMOUNT] else @TESTSEGMENTEXPECTEDGIFTAMOUNT end),
            @TESTSEGMENTEXPECTEDRESPONSERATE = (case when @TESTSEGMENTEXPECTEDRESPONSERATE = 0.00 then [RESPONSERATE] else @TESTSEGMENTEXPECTEDRESPONSERATE end),
            @TESTSEGMENTQUANTITY = (case when @TESTSEGMENTQUANTITY = 0 and [SAMPLESIZETYPECODE] = 1 then [SAMPLESIZE] else @TESTSEGMENTQUANTITY end)
          from dbo.[MKTSEGMENTATIONTESTSEGMENT]
          where [ID] = @TESTSEGMENTID;

        --Because you cannot reuse the same test segment on multiple segments, we always insert a new row
        --into the batch temp tables here.  We do not need an update statement for this reason.
        set @BATCHTESTSEGMENTID = newid();

        --Insert a new test segment record for the batch...
        insert into dbo.[BATCHDIRECTMARKETINGEFFORTTESTSEGMENT] (
          [ID],
          [MKTSEGMENTATIONTESTSEGMENTID],
          [NAME],
          [DESCRIPTION],
          [CODE],
          [CODEPARTDEFINITIONVALUESID],
          [EXPECTEDGIFTAMOUNT],
          [EXPECTEDRESPONSERATE],
          [QUANTITY],
          [BASECURRENCYID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @BATCHTESTSEGMENTID,
          @TESTSEGMENTID,
          @TESTSEGMENTNAME,
          @TESTSEGMENTDESCRIPTION,
          @TESTSEGMENTCODE,
          @TESTSEGMENTCODEVALUEID,
          @TESTSEGMENTEXPECTEDGIFTAMOUNT,
          @TESTSEGMENTEXPECTEDRESPONSERATE,
          @TESTSEGMENTQUANTITY,
          @BASECURRENCYID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end



    if @EXISTINGPACKAGE = 1
      --For any values that were not specified pull the values from the existing record...
      select
        @PACKAGENAME = (case when @PACKAGENAME = '' then [NAME] else @PACKAGENAME end),
        @PACKAGEDESCRIPTION = (case when @PACKAGEDESCRIPTION = '' then [DESCRIPTION] else @PACKAGEDESCRIPTION end),
        @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),
        @PACKAGESITEID = (case when @PACKAGESITEID = [SITEID] or (@PACKAGESITEISNULL = 0 and @PACKAGESITEID is null) then [SITEID] else @PACKAGESITEID end)
      from dbo.[MKTPACKAGE]
      where [ID] = @PACKAGEID;

    --Save the package record...
    if @BATCHPACKAGEID is null
      begin
        set @BATCHPACKAGEID = newid();

        --Insert a new package record for the batch...
        insert into dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] (
          [ID],
          [MKTPACKAGEID],
          [NAME],
          [DESCRIPTION],
          [CODE],
          [CODEPARTDEFINITIONVALUESID],
          [CHANNELCODE],
          [CHANNELCODEPARTDEFINITIONVALUESID],
          [MKTPACKAGECATEGORYCODEID],
          [PACKAGETYPECODE],
          [COST],
          [COSTDISTRIBUTIONMETHODCODE],
          [BASECURRENCYID],
          [SITEID],
          [PACKAGESITEISNULL],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          @BATCHPACKAGEID,
          @PACKAGEID,
          @PACKAGENAME,
          @PACKAGEDESCRIPTION,
          @PACKAGECODE,
          @PACKAGECODEVALUEID,
          @PACKAGECHANNELCODE,
          @PACKAGECHANNELCODEVALUEID,
          @PACKAGECATEGORYCODEID,
          @PACKAGETYPECODE,
          @PACKAGECOST,
          @PACKAGECOSTDISTRIBUTIONMETHODCODE,
          @BASECURRENCYID,
          @PACKAGESITEID,
          @PACKAGESITEISNULL,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
      end
    else
      --The package record already exists in this table, so just update the existing row.
      --Because of how batch works, the last row to update a package record wins.
      update dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] set
        [MKTPACKAGEID] = @PACKAGEID,
        [NAME] = @PACKAGENAME,
        [DESCRIPTION] = @PACKAGEDESCRIPTION,
        [CODE] = @PACKAGECODE,
        [CODEPARTDEFINITIONVALUESID] = @PACKAGECODEVALUEID,
        [CHANNELCODE] = @PACKAGECHANNELCODE,
        [CHANNELCODEPARTDEFINITIONVALUESID] = @PACKAGECHANNELCODEVALUEID,
        [MKTPACKAGECATEGORYCODEID] = @PACKAGECATEGORYCODEID,
        [PACKAGETYPECODE] = @PACKAGETYPECODE,
        [COST] = @PACKAGECOST,
        [COSTDISTRIBUTIONMETHODCODE] = @PACKAGECOSTDISTRIBUTIONMETHODCODE,
        [BASECURRENCYID] = @BASECURRENCYID,
        [SITEID] = @PACKAGESITEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @BATCHPACKAGEID;



    if @EXISTINGSEGMENT = 1 and @SEGMENTATIONSEGMENTID is not null
      --For any values that were not specified pull the values from the existing record...
      select
        @TESTSEGMENTCODE = (case when @TESTSEGMENTCODE = '' then [MKTSEGMENTATIONSEGMENT].[TESTSEGMENTCODE] else @TESTSEGMENTCODE end),
  @TESTSEGMENTCODEVALUEID = (case when @TESTSEGMENTCODEVALUEID is null then [MKTSEGMENTATIONSEGMENT].[TESTPARTDEFINITIONVALUESID] else @TESTSEGMENTCODEVALUEID end),
        @SEGMENTEXPECTEDGIFTAMOUNT = (case when @SEGMENTEXPECTEDGIFTAMOUNT = 0 then [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] else @SEGMENTEXPECTEDGIFTAMOUNT end),
        @SEGMENTEXPECTEDRESPONSERATE = (case when @SEGMENTEXPECTEDRESPONSERATE = 0.00 then [MKTSEGMENTATIONSEGMENT].[RESPONSERATE] else @SEGMENTEXPECTEDRESPONSERATE end),
        @SEGMENTRENTALQUANTITY = (case when [MKTSEGMENTATIONSEGMENTLIST].[ID] is not null and @SEGMENTRENTALQUANTITY = 0 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALQUANTITY] else @SEGMENTRENTALQUANTITY end),
        @SEGMENTEXCHANGEQUANTITY = (case when [MKTSEGMENTATIONSEGMENTLIST].[ID] is not null and @SEGMENTEXCHANGEQUANTITY = 0 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGEQUANTITY] else @SEGMENTEXCHANGEQUANTITY end),
        @SEGMENTRENTALCOST = (case when [MKTSEGMENTATIONSEGMENTLIST].[ID] is not null and @SEGMENTRENTALCOST = 0 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENT] else @SEGMENTRENTALCOST end),
        @SEGMENTRENTALCOSTBASISCODE = (case when [MKTSEGMENTATIONSEGMENTLIST].[ID] is not null and @SEGMENTRENTALCOSTBASISCODE = 1 then [MKTSEGMENTATIONSEGMENTLIST].[RENTALCOSTADJUSTMENTBASISCODE] else @SEGMENTRENTALCOSTBASISCODE end),
        @SEGMENTEXCHANGECOST = (case when [MKTSEGMENTATIONSEGMENTLIST].[ID] is not null and @SEGMENTEXCHANGECOST = 0 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENT] else @SEGMENTEXCHANGECOST end),
        @SEGMENTEXCHANGECOSTBASISCODE = (case when [MKTSEGMENTATIONSEGMENTLIST].[ID] is not null and @SEGMENTEXCHANGECOSTBASISCODE = 1 then [MKTSEGMENTATIONSEGMENTLIST].[EXCHANGECOSTADJUSTMENTBASISCODE] else @SEGMENTEXCHANGECOSTBASISCODE end),
        @SEGMENTUSAGECODE = (case when [MKTSEGMENTATIONSEGMENTLIST].[ID] is not null and @SEGMENTUSAGECODE = 1 then [MKTSEGMENTATIONSEGMENT].[USAGECODE] else @SEGMENTUSAGECODE end)
      from dbo.[MKTSEGMENTATIONSEGMENT]
      left join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID;

    --For any values that were not specified pull the values from the existing record...
    select
      @SEGMENTATIONCODE = (case when @SEGMENTATIONCODE = '' then [CODE] else @SEGMENTATIONCODE end),
      @SEGMENTATIONCODEVALUEID = (case when @SEGMENTATIONCODEVALUEID is null then [PARTDEFINITIONVALUESID] else @SEGMENTATIONCODEVALUEID end)
    from dbo.[MKTSEGMENTATION]
    where [ID] = @SEGMENTATIONID;

    --Save the main batch row...
    insert into dbo.[BATCHDIRECTMARKETINGEFFORT] (
      [ID],
      [BATCHID],
      [SEQUENCE],
      [SEGMENTATIONID],
      [SEGMENTATIONCODE],
      [SEGMENTATIONCODEPARTDEFINITIONVALUESID],
      [SOURCECODE],
      [RECORDSOURCEID],
      [BATCHDIRECTMARKETINGEFFORTSEGMENTID],
      [BATCHDIRECTMARKETINGEFFORTPACKAGEID],
      [BATCHDIRECTMARKETINGEFFORTLISTID],
      [BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID],
      [SEGMENTTESTCODE],
      [SEGMENTTESTCODEPARTDEFINITIONVALUESID],
      [SEGMENTEXPECTEDGIFTAMOUNT],
      [SEGMENTEXPECTEDRESPONSERATE],
      [SEGMENTRENTALQUANTITY],
      [SEGMENTEXCHANGEQUANTITY],
      [SEGMENTRENTALCOST],
      [SEGMENTRENTALCOSTBASISCODE],
      [SEGMENTEXCHANGECOST],
      [SEGMENTEXCHANGECOSTBASISCODE],
      [SEGMENTUSAGECODE],
      [BASECURRENCYID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
      @BATCHID,
      @SEQUENCE,
      @SEGMENTATIONID,
      @SEGMENTATIONCODE,
      @SEGMENTATIONCODEVALUEID,
      @SOURCECODE,
      @RECORDSOURCEID,
      @BATCHSEGMENTID,
      @BATCHPACKAGEID,
      @BATCHLISTID,
      @BATCHTESTSEGMENTID,
      (case when @SAVETESTSEGMENTRECORD = 1 then '' else @TESTSEGMENTCODE end),
      (case when @SAVETESTSEGMENTRECORD = 1 then null else @TESTSEGMENTCODEVALUEID end),
      @SEGMENTEXPECTEDGIFTAMOUNT,
      @SEGMENTEXPECTEDRESPONSERATE,
      @SEGMENTRENTALQUANTITY,
      @SEGMENTEXCHANGEQUANTITY,
      @SEGMENTRENTALCOST,
      @SEGMENTRENTALCOSTBASISCODE,
      @SEGMENTEXCHANGECOST,
      @SEGMENTEXCHANGECOSTBASISCODE,
      @SEGMENTUSAGECODE,
      @BASECURRENCYID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );
  end try

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

  return 0;