USP_MKTSEGMENTATIONACTIVATE_CACHELISTSEGMENTS

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHELISTSEGMENTS]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

  begin try
    -- only save this info if this is the first activation

    if (select [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) = 0
      begin
        if @CHANGEAGENTID is null
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        set @CURRENTDATE = getdate();

        --Cache all list segment info that was not specifically overridden...

        insert into dbo.[MKTSEGMENTATIONSEGMENTLIST] (
          [ID],
          [SEGMENTLISTID],
          [OVERRIDELISTCOSTS],
          [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
          [BASERENTALCOST],
          [BASERENTALCOSTBASISCODE],
          [RENTALQUANTITY],
          [RENTALCOSTADJUSTMENT],
          [RENTALCOSTADJUSTMENTBASISCODE],
          [BASEEXCHANGECOST],
          [BASEEXCHANGECOSTBASISCODE],
          [EXCHANGEQUANTITY],
          [EXCHANGECOSTADJUSTMENT],
          [EXCHANGECOSTADJUSTMENTBASISCODE],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED],
          [BASECURRENCYID],
          [ORGANIZATIONBASERENTALCOST],
          [ORGANIZATIONBASEEXCHANGECOST],
          [ORGANIZATIONRENTALCOSTADJUSTMENT],
          [ORGANIZATIONEXCHANGECOSTADJUSTMENT]
        )
        select
          [MKTSEGMENTATIONSEGMENT].[ID],
          [MKTSEGMENTLIST].[ID],
          0,
          0,
          [MKTLIST].[BASERENTALCOST],
          [MKTLIST].[BASERENTALCOSTBASISCODE],
          [MKTSEGMENTLIST].[RENTALQUANTITY],
          [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
          [MKTSEGMENTLIST].[RENTALCOSTBASISCODE],
          [MKTLIST].[BASEEXCHANGECOST],
          [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
          [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
          [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
          [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE],
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE,
          [MKTSEGMENTLIST].[BASECURRENCYID],
          [MKTLIST].[ORGANIZATIONBASERENTALCOST],
          [MKTSEGMENTLIST].[ORGANIZATIONRENTALCOSTADJUSTMENT],
          [MKTLIST].[ORGANIZATIONBASEEXCHANGECOST],
          [MKTSEGMENTLIST].[ORGANIZATIONEXCHANGECOSTADJUSTMENT]
        from dbo.[MKTSEGMENTATIONSEGMENT]
        left outer join dbo.[MKTSEGMENTATIONSEGMENTLIST] on [MKTSEGMENTATIONSEGMENTLIST].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
        inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
        inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
        where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
        and [MKTSEGMENTATIONSEGMENTLIST].[ID] is null;

        --Set the list segment ID for all list segments that were overridden...

        update dbo.[MKTSEGMENTATIONSEGMENTLIST] set
          [SEGMENTLISTID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID],
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        from [MKTSEGMENTATIONSEGMENTLIST] as [SSL]
        inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [SSL].[ID]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
        where [SSL].[SEGMENTLISTID] is null;
      end;
  end try

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

  return 0;