USP_MKTSEGMENTATIONSEGMENTLIST_LOAD

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENTLIST_LOAD]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  set nocount on;

  declare @DATALOADED bit;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @MARKETINGPLANBRIEFID uniqueidentifier;
  declare @SEGMENTATIONACTIVE bit;
  declare @SEGMENTID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @ISVENDORMANAGED bit;
  declare @USAGECODE tinyint;
  declare @USAGE nvarchar(100);
  declare @CODE nvarchar(10);
  declare @CODEVALUEID uniqueidentifier;
  declare @LISTCODE nvarchar(10);
  declare @LISTCODEVALUEID uniqueidentifier;
  declare @TESTSEGMENTCODE nvarchar(10);
  declare @TESTSEGMENTCODEVALUEID uniqueidentifier;
  declare @OVERRIDEQUANTITIESANDORSEGMENTCOSTS bit;
  declare @OVERRIDELISTCOSTS bit;
  declare @PACKAGEID uniqueidentifier;
  declare @PACKAGECHANNELCODE tinyint;
  declare @PACKAGECODE nvarchar(10);
  declare @PACKAGECODEVALUEID uniqueidentifier;
  declare @RESPONSERATE decimal(5,2);
  declare @GIFTAMOUNT money;
  declare @SAMPLESIZE int;
  declare @SAMPLESIZETYPECODE tinyint;
  declare @SAMPLESIZEMETHODCODE tinyint;
  declare @SAMPLESIZEEXCLUDEREMAINDER bit;
  declare @SOURCECODEID uniqueidentifier;
  declare @ASKLADDERID uniqueidentifier;
  declare @SEQUENCE int;
  declare @SEGMENTATIONSITEID uniqueidentifier;
  declare @SEGMENTDATECHANGED datetime;
  declare @CHANNELSOURCECODE nvarchar(10);
  declare @CHANNELSOURCECODEVALUEID uniqueidentifier;
  declare @ISBBEC bit;
  declare @OVERRIDEBUSINESSUNITS bit;
  declare @BUSINESSUNITS xml;
  declare @EFFORTOVERRIDESBUSINESSUNITS bit;
  declare @ALLOWEFFORTBUSINESSUNITSOVERRIDE bit;
  declare @TSLONG bigint;
  declare @BASECURRENCYID uniqueidentifier;
  declare @PACKAGEADDDATAFORMCONTEXT nvarchar(128);
  declare @ITEMLIST xml;
  declare @ISHISTORICAL bit;
  declare @SOURCECODEISHISTORICAL bit;
  declare @SOURCECODE nvarchar(50);
  declare @CHANNELCODE tinyint;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  /* load the segment */
  declare @R int;
  exec @R = dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENT_4]
    @ID,
    @CURRENTAPPUSERID,
    @DATALOADED output,
    @SEGMENTATIONID output,
    @MARKETINGPLANBRIEFID output,
    @SEGMENTATIONACTIVE output,
    @SEGMENTID output,
    @CODE output,
    @TESTSEGMENTCODE output,
    @PACKAGEID output,
    @PACKAGECODE output,
    @RESPONSERATE output,
    @GIFTAMOUNT output,
    @SAMPLESIZE output,
    @SAMPLESIZETYPECODE output,
    @SAMPLESIZEMETHODCODE output,
    @SAMPLESIZEEXCLUDEREMAINDER output,
    @SOURCECODEID output,
    @ASKLADDERID output,
    @SEQUENCE output,
    @SEGMENTATIONSITEID output,
    @TSLONG output,
    null,
    null,
    @PACKAGECHANNELCODE output,
    null,
    null,
    @SEGMENTDATECHANGED output,
    0,
    0,
    0,
    null,
    null,
    @CODEVALUEID output,
    @PACKAGECODEVALUEID output,
    @TESTSEGMENTCODEVALUEID output,
    @ITEMLIST output,
    @CHANNELSOURCECODE output,
    @CHANNELSOURCECODEVALUEID output,
    null,
    @OVERRIDEBUSINESSUNITS output,
    @BUSINESSUNITS output,
    @BASECURRENCYID output,
    @PACKAGEADDDATAFORMCONTEXT output,
    null,
    null,
    @RECORDSOURCEID output,
    null,
    null,
    @EFFORTOVERRIDESBUSINESSUNITS output,
    @ISHISTORICAL output,
    null,
    @SOURCECODEISHISTORICAL output,
    @SOURCECODE output,
    @CHANNELCODE output,
    @ALLOWEFFORTBUSINESSUNITSOVERRIDE output;

  if @R <> 0 return 1;

  if @DATALOADED = 1
    begin
      -- checking to see if a finder file has been imported for this segment

      if exists (select 1 from dbo.[MKTFINDERFILEIMPORTPROCESS] where [SEGMENTATIONID] = @SEGMENTATIONID and [STATUSCODE] in (1, 2))
        begin
          declare @SQL nvarchar(max);
          declare @FINDERFILETABLE nvarchar(256);

          set @FINDERFILETABLE = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@SEGMENTATIONID);

          set @SQL = 'select @ISVENDORMANAGED = 1' + char(13) +
                     'from dbo.[' +@FINDERFILETABLE + '] [DATA]' + char(13) +
                     'left join dbo.[MKTSOURCECODEMAP] [SC] on [SC].[SEGMENTATIONID] = @SEGMENTATIONID and [SC].[SOURCECODE] = [DATA].[SOURCECODE]' + char(13) + 
                     'where [SC].[SEGMENTATIONSEGMENTID] = @ID;';

          exec sp_executesql @SQL,N'@ID uniqueidentifier, @SEGMENTATIONID uniqueidentifier, @ISVENDORMANAGED bit output', @ID = @ID, @SEGMENTATIONID = @SEGMENTATIONID, @ISVENDORMANAGED = @ISVENDORMANAGED output;  
        end

      select 
        @USAGECODE = [MKTSEGMENTATIONSEGMENT].[USAGECODE],
        @USAGE = [MKTSEGMENTATIONSEGMENT].[USAGE],
        @LISTCODEVALUEID = case when [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] is not null then [MKTLISTACTIVE].[PARTDEFINITIONVALUESID] else [MKTLIST].[PARTDEFINITIONVALUESID] end,
        @LISTCODE = case when [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID] is not null then [MKTLISTACTIVE].[CODE] else [MKTLIST].[CODE] end,
        @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDEQUANTITIESANDORSEGMENTCOSTS], 0) = 1 or @SEGMENTATIONACTIVE = 1 then 1 else 0 end),
        @OVERRIDELISTCOSTS = (case when isnull([MKTSEGMENTATIONSEGMENTLIST].[OVERRIDELISTCOSTS], 0) = 1 or @SEGMENTATIONACTIVE = 1 then 1 else 0 end),
        @ISVENDORMANAGED = case when @ISVENDORMANAGED = 1 then 0 else dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST](@SEGMENTID)end
      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]
      left outer join dbo.[MKTSEGMENTLIST] as [MKTSEGMENTLISTACTIVE] on [MKTSEGMENTLISTACTIVE].[ID] = [MKTSEGMENTATIONSEGMENTLIST].[SEGMENTLISTID]
      left outer join dbo.[MKTLIST] as [MKTLISTACTIVE] on [MKTLISTACTIVE].[ID] = [MKTSEGMENTLISTACTIVE].[LISTID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

      set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

      if @OVERRIDEQUANTITIESANDORSEGMENTCOSTS = 1 or @OVERRIDELISTCOSTS = 1 -- there's a row in MKTSEGMENTATIONSEGMENTLIST

        select
          @SEGMENTATIONID as [SEGMENTATIONID],
          @MARKETINGPLANBRIEFID as [MARKETINGPLANBRIEFID],
          @SEGMENTATIONACTIVE as [SEGMENTATIONACTIVE],
          @SEGMENTID as [SEGMENTID],
          @SEGMENTDATECHANGED as [SEGMENTDATECHANGED],
          @ISVENDORMANAGED as [ISVENDORMANAGED],
          @USAGECODE as [USAGECODE],
          @USAGE as [USAGE],
          @CODE as [CODE],
          @OVERRIDEQUANTITIESANDORSEGMENTCOSTS as [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
          @OVERRIDELISTCOSTS as [OVERRIDELISTCOSTS],
          [BASERENTALCOST],
          [BASERENTALCOSTBASISCODE],
          [RENTALQUANTITY],
          [RENTALCOSTADJUSTMENT],
          [RENTALCOSTADJUSTMENTBASISCODE],
          [RENTALCOSTADJUSTMENTBASIS],
          [BASEEXCHANGECOST],
          [BASEEXCHANGECOSTBASISCODE],
          [EXCHANGEQUANTITY],
          [EXCHANGECOSTADJUSTMENT],
          [EXCHANGECOSTADJUSTMENTBASISCODE],
          [EXCHANGECOSTADJUSTMENTBASIS],
          @PACKAGEID as [PACKAGEID],
          @PACKAGECODE as [PACKAGECODE],
          @RESPONSERATE as [RESPONSERATE],
          @GIFTAMOUNT as [GIFTAMOUNT],
          @SAMPLESIZE as [SAMPLESIZE],
          @SAMPLESIZETYPECODE as [SAMPLESIZETYPECODE],
          @SAMPLESIZEMETHODCODE as [SAMPLESIZEMETHODCODE],
          @SAMPLESIZEEXCLUDEREMAINDER as [SAMPLESIZEEXCLUDEREMAINDER],
          @SOURCECODEID as [SOURCECODEID],
          @ASKLADDERID as [ASKLADDERID],
          @SEQUENCE as [SEQUENCE],
          @SEGMENTATIONSITEID as [SEGMENTATIONSITEID],
          @TSLONG as [TSLONG],
          @TESTSEGMENTCODE as [TESTSEGMENTCODE],
          @CODEVALUEID as [CODEVALUEID],
          @TESTSEGMENTCODEVALUEID as [TESTSEGMENTCODEVALUEID],
          @PACKAGECODEVALUEID as [PACKAGECODEVALUEID],
          @CHANNELSOURCECODE as [CHANNELSOURCECODE],
          @CHANNELSOURCECODEVALUEID as [CHANNELSOURCECODEVALUEID],
          @ISBBEC as [ISBBEC],
          @OVERRIDEBUSINESSUNITS as [OVERRIDEBUSINESSUNITS],
          @BUSINESSUNITS as [BUSINESSUNITS],
          @BASECURRENCYID as [BASECURRENCYID],
          @PACKAGEADDDATAFORMCONTEXT as [PACKAGEADDDATAFORMCONTEXT],
          @RECORDSOURCEID as [RECORDSOURCEID],
          @LISTCODEVALUEID as [LISTCODEVALUEID],
          @LISTCODE as [LISTCODE],
          @ITEMLIST as [ITEMLIST],
          @PACKAGECHANNELCODE as [PACKAGECHANNELCODE],
          @EFFORTOVERRIDESBUSINESSUNITS as [EFFORTOVERRIDESBUSINESSUNITS],
          @ISHISTORICAL as [ISHISTORICAL],
          @SOURCECODEISHISTORICAL as [SOURCECODEISHISTORICAL],
          @SOURCECODE as [SOURCECODE],
          @CHANNELCODE as [CHANNELCODE],
          @ALLOWEFFORTBUSINESSUNITSOVERRIDE as [ALLOWEFFORTBUSINESSUNITSOVERRIDE]
        from dbo.[MKTSEGMENTATIONSEGMENTLIST]
        where [ID] = @ID;
      else
        select
          @SEGMENTATIONID as [SEGMENTATIONID],
          @MARKETINGPLANBRIEFID as [MARKETINGPLANBRIEFID],
          @SEGMENTATIONACTIVE as [SEGMENTATIONACTIVE],
          @SEGMENTID as [SEGMENTID],
          @SEGMENTDATECHANGED as [SEGMENTDATECHANGED],
          @ISVENDORMANAGED as [ISVENDORMANAGED],
          @USAGECODE as [USAGECODE],
          @USAGE as [USAGE],
          @CODE as [CODE],
          @OVERRIDEQUANTITIESANDORSEGMENTCOSTS as [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
          @OVERRIDELISTCOSTS as [OVERRIDELISTCOSTS],
          [MKTLIST].[BASERENTALCOST],
          [MKTLIST].[BASERENTALCOSTBASISCODE],
          [MKTSEGMENTLIST].[RENTALQUANTITY],
          [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
          [MKTSEGMENTLIST].[RENTALCOSTBASISCODE] as [RENTALCOSTADJUSTMENTBASISCODE],
          [MKTSEGMENTLIST].[RENTALCOSTBASIS] as [RENTALCOSTADJUSTMENTBASIS],
          [MKTLIST].[BASEEXCHANGECOST],
          [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
          [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
          [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
          [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE] as [EXCHANGECOSTADJUSTMENTBASISCODE],
          [MKTSEGMENTLIST].[EXCHANGECOSTBASIS] as [EXCHANGECOSTADJUSTMENTBASIS],
          @PACKAGEID as [PACKAGEID],
          @PACKAGECODE as [PACKAGECODE],
          @RESPONSERATE as [RESPONSERATE],
          @GIFTAMOUNT as [GIFTAMOUNT],
          @SAMPLESIZE as [SAMPLESIZE],
          @SAMPLESIZETYPECODE as [SAMPLESIZETYPECODE],
          @SAMPLESIZEMETHODCODE as [SAMPLESIZEMETHODCODE],
          @SAMPLESIZEEXCLUDEREMAINDER as [SAMPLESIZEEXCLUDEREMAINDER],
          @SOURCECODEID as [SOURCECODEID],
          @ASKLADDERID as [ASKLADDERID],
          @SEQUENCE as [SEQUENCE],
          @SEGMENTATIONSITEID as [SEGMENTATIONSITEID],
          @TSLONG as [TSLONG],
          @TESTSEGMENTCODE as [TESTSEGMENTCODE],
          @CODEVALUEID as [CODEVALUEID],
          @TESTSEGMENTCODEVALUEID as [TESTSEGMENTCODEVALUEID],
          @PACKAGECODEVALUEID as [PACKAGECODEVALUEID],
          @CHANNELSOURCECODE as [CHANNELSOURCECODE],
          @CHANNELSOURCECODEVALUEID as [CHANNELSOURCECODEVALUEID],
          @ISBBEC as [ISBBEC],
          @OVERRIDEBUSINESSUNITS as [OVERRIDEBUSINESSUNITS],
          @BUSINESSUNITS as [BUSINESSUNITS],
          @BASECURRENCYID as [BASECURRENCYID],
          @PACKAGEADDDATAFORMCONTEXT as [PACKAGEADDDATAFORMCONTEXT],
          @RECORDSOURCEID as [RECORDSOURCEID],
          @LISTCODEVALUEID as [LISTCODEVALUEID],
          @LISTCODE as [LISTCODE],
          @ITEMLIST as [ITEMLIST],
          @PACKAGECHANNELCODE as [PACKAGECHANNELCODE],
          @EFFORTOVERRIDESBUSINESSUNITS as [EFFORTOVERRIDESBUSINESSUNITS],
          @ISHISTORICAL as [ISHISTORICAL],
          @SOURCECODEISHISTORICAL as [SOURCECODEISHISTORICAL],
          @SOURCECODE as [SOURCECODE],
          @CHANNELCODE as [CHANNELCODE],
          @ALLOWEFFORTBUSINESSUNITSOVERRIDE as [ALLOWEFFORTBUSINESSUNITSOVERRIDE]
        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

  return 0;