USP_MKTSEGMENTLIST_EDIT_SAVE_2

CREATE procedure dbo.[USP_MKTSEGMENTLIST_EDIT_SAVE_2]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,    
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255),
  @SEGMENTCATEGORYCODEID uniqueidentifier,
  @CODE nvarchar(10),
  @ALLOWCODEUPDATE bit,
  @ORDERDATE datetime,
  @EXPIRATIONDATE datetime,
  @NUMBEROFCONTACTS smallint,
  @LISTID uniqueidentifier,
  @LISTLAYOUTID uniqueidentifier,
  @FILENAME nvarchar(255),
  @LISTIMPORTFILECHANGED bit,
  @RENTALQUANTITY int,
  @RENTALCOSTADJUSTMENT money,
  @RENTALCOSTBASISCODE tinyint,
  @EXCHANGEQUANTITY int,
  @EXCHANGECOSTADJUSTMENT money,
  @EXCHANGECOSTBASISCODE tinyint,
  @GROUPS xml,
  @TYPECODE tinyint, --0 = Imported, 1 = Vendor-managed

  @CODEVALUEID uniqueidentifier,
  @SITEID uniqueidentifier
)
as
  set nocount on;

  declare @NEWSEGMENTLISTID uniqueidentifier;
  declare @OLDSEGMENTLISTID uniqueidentifier;
  declare @OLDTYPECODE tinyint;
  declare @OLDSTATUSCODE tinyint;
  declare @OLDIMPORTPROCESSID uniqueidentifier;
  declare @OLDIMPORTPROCESSSTATUSCODE tinyint;
  declare @OLDCONSOLIDATEDQUERYVIEWID uniqueidentifier;
  declare @OLDNAME nvarchar(100);
  declare @OLDRECORSOURCEID uniqueidentifier;
  declare @OLDLISTID uniqueidentifier;
  declare @IDSETRECORDTYPEID uniqueidentifier;
  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @STANDARDIDSETID uniqueidentifier;
  declare @DUPLICATEIDSETID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @STATUSCODE tinyint;
  declare @CURRENTDATE datetime;
  declare @BASECURRENCYID uniqueidentifier;
  declare @CURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONRENTALCOSTADJUSTMENT money;
  declare @ORGANIZATIONEXCHANGECOSTADJUSTMENT money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @DATEADDED datetime;

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

    set @CURRENTDATE = getdate();

    select 
      @OLDSEGMENTLISTID = [MKTSEGMENTLIST].[ID],
      @OLDTYPECODE = [MKTSEGMENTLIST].[TYPECODE],
      @OLDSTATUSCODE = [MKTSEGMENTLIST].[STATUSCODE],
      @OLDIMPORTPROCESSID = [MKTSEGMENTLISTIMPORTPROCESS].[ID],
      @OLDIMPORTPROCESSSTATUSCODE = [BUSINESSPROCESSSTATUS].[STATUSCODE],
      @OLDCONSOLIDATEDQUERYVIEWID = [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID],
      @OLDNAME = [MKTSEGMENT].[NAME],
      @OLDRECORSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
      @OLDLISTID = [MKTSEGMENTLIST].[LISTID],
      @IDSETRECORDTYPEID = [MKTSEGMENTLIST].[IDSETRECORDTYPEID],
      @QUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
      @STANDARDIDSETID = [MKTSEGMENTLIST].[STANDARDIDSETID],
      @DUPLICATEIDSETID = [MKTSEGMENTLIST].[DUPLICATEIDSETID],
      @BASECURRENCYID = [MKTSEGMENTLIST].[BASECURRENCYID]
    from dbo.[MKTSEGMENT]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    left join dbo.[MKTSEGMENTLISTIMPORTPROCESS] on [MKTSEGMENTLISTIMPORTPROCESS].[SEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
    left join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSCATALOGID] = 'CA0DD398-85C2-409B-A847-9F09A78A150F' and [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTLISTIMPORTPROCESS].[ID]
    where [MKTSEGMENT].[ID] = @ID;

    if @LISTIMPORTFILECHANGED = 1 and @OLDSTATUSCODE not in (0, 3) and not (@OLDSTATUSCODE = 1 and @OLDIMPORTPROCESSSTATUSCODE = 2)
      raiserror('The current segment must have a status of Pending Import or Active in order to continue, or the import must have previously failed.', 13, 1);

    set @STATUSCODE = (case @TYPECODE
                       when 0 then 0  --Imported, PendingImport

                       when 1 then 3  --VendorManged, Active

                       end);

    select
      @RECORDSOURCEID = [RECORDSOURCEID]
    from dbo.[MKTLIST]
    where [ID] = @LISTID;

    select
      @DATEADDED = [MKTSEGMENTLIST].[DATEADDED],
      @CURRENCYEXCHANGERATEID = [MKTSEGMENTLIST].[CURRENCYEXCHANGERATEID]
    from dbo.[MKTSEGMENTLIST]
    where [MKTSEGMENTLIST].[ID] = @OLDSEGMENTLISTID;

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

    if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
      begin
        set @ORGANIZATIONRENTALCOSTADJUSTMENT = @RENTALCOSTADJUSTMENT;
        set @ORGANIZATIONEXCHANGECOSTADJUSTMENT = @EXCHANGECOSTADJUSTMENT;
      end
    else
      begin
        if @CURRENCYEXCHANGERATEID is null
          set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);

        set @ORGANIZATIONRENTALCOSTADJUSTMENT = dbo.[UFN_CURRENCY_CONVERT](@RENTALCOSTADJUSTMENT, @CURRENCYEXCHANGERATEID);
        set @ORGANIZATIONEXCHANGECOSTADJUSTMENT = dbo.[UFN_CURRENCY_CONVERT](@EXCHANGECOSTADJUSTMENT, @CURRENCYEXCHANGERATEID);
      end

    --If the import file info changed and it is marked "Active", then we need to mark it as historical and create a new finder file record...

    if @LISTIMPORTFILECHANGED = 1 and @OLDSTATUSCODE = 3
      begin
        --Delete the old finder file's import query views, IDSets, etc. (but not the actual imported records, we still need those for matchback)...

        exec dbo.[USP_MKTSEGMENTLIST_DELETEIMPORTDATA] @ID, @CURRENTAPPUSERID, 0, @CHANGEAGENTID;

        --Mark the old finder file as historical...

        update dbo.[MKTSEGMENTLIST] set
          [STATUSCODE] = 4,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @OLDSEGMENTLISTID;

        --Add a historical record for this finder file (only if it is imported)...

        if @OLDTYPECODE = 0
          insert into dbo.[MKTSEGMENTLISTHISTORICAL] (
            [ID],
            [LISTNAME],
            [LISTDESCRIPTION],
            [LISTCODE],
            [LISTVENDORID],
            [LISTCATEGORYCODEID],
            [LISTBASERENTALCOST],
            [LISTBASERENTALCOSTBASISCODE],
            [LISTBASEEXCHANGECOST],
            [LISTBASEEXCHANGECOSTBASISCODE],
            [SEGMENTNAME],
            [SEGMENTDESCRIPTION],
            [SEGMENTCODE],
            [SEGMENTCATEGORYCODEID],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED],
            [BASECURRENCYID],
            [ORGANIZATIONLISTBASERENTALCOST],
            [ORGANIZATIONLISTBASEEXCHANGECOST],
            [CURRENCYEXCHANGERATEID]
          )
          select
            [MKTSEGMENTLIST].[ID],
            [MKTLIST].[NAME],
            [MKTLIST].[DESCRIPTION],
            [MKTLIST].[CODE],
            [MKTLIST].[VENDORID],
            [MKTLIST].[LISTCATEGORYCODEID],
            [MKTLIST].[BASERENTALCOST],
            [MKTLIST].[BASERENTALCOSTBASISCODE],
            [MKTLIST].[BASEEXCHANGECOST],
            [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
            [MKTSEGMENT].[NAME],
            [MKTSEGMENT].[DESCRIPTION],
            [MKTSEGMENT].[CODE],
            [MKTSEGMENT].[SEGMENTCATEGORYCODEID],
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
            [MKTLIST].[BASECURRENCYID],
            [MKTLIST].[ORGANIZATIONBASERENTALCOST],
            [MKTLIST].[ORGANIZATIONBASEEXCHANGECOST],
            [MKTLIST].[CURRENCYEXCHANGERATEID]
          from dbo.[MKTSEGMENTLIST]
          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTLIST].[SEGMENTID]
          inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
          where [MKTSEGMENTLIST].[ID] = @OLDSEGMENTLISTID;

        --Add a new finder file record for the segment...

        set @CURRENTDATE = getdate();
        set @NEWSEGMENTLISTID = newid();
        insert into dbo.[MKTSEGMENTLIST] (
          [ID],
          [SEGMENTID],
          [LISTID],
          [TYPECODE],
          [STATUSCODE],
          [ORDERDATE],
          [EXPIRATIONDATE],
          [NUMBEROFCONTACTS],
          [LISTLAYOUTID],
          [FILENAME],
          [RENTALQUANTITY],
          [RENTALCOSTADJUSTMENT],
          [RENTALCOSTBASISCODE],
          [EXCHANGEQUANTITY],
          [EXCHANGECOSTADJUSTMENT],
          [EXCHANGECOSTBASISCODE],
          [CONSOLIDATEDQUERYVIEWID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED],
          [BASECURRENCYID],
          [ORGANIZATIONRENTALCOSTADJUSTMENT],
          [ORGANIZATIONEXCHANGECOSTADJUSTMENT],
          [CURRENCYEXCHANGERATEID]
        ) values (
          @NEWSEGMENTLISTID,
          @ID,
          @LISTID,
          @TYPECODE,
          @STATUSCODE,
          @ORDERDATE,
          @EXPIRATIONDATE,
          @NUMBEROFCONTACTS,
          @LISTLAYOUTID,
          @FILENAME,
          @RENTALQUANTITY,
          @RENTALCOSTADJUSTMENT,
          @RENTALCOSTBASISCODE,
          @EXCHANGEQUANTITY,
          @EXCHANGECOSTADJUSTMENT,
          @EXCHANGECOSTBASISCODE,
          @OLDCONSOLIDATEDQUERYVIEWID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE,
          @BASECURRENCYID,
          @ORGANIZATIONRENTALCOSTADJUSTMENT,
          @ORGANIZATIONEXCHANGECOSTADJUSTMENT,
          @CURRENCYEXCHANGERATEID
        );
      end
    else
      begin
        set @NEWSEGMENTLISTID = @OLDSEGMENTLISTID;

        update dbo.[MKTSEGMENTLIST] set
          [LISTID] = @LISTID,
          [TYPECODE] = (case when @LISTIMPORTFILECHANGED = 1 then @TYPECODE else [TYPECODE] end),
          [STATUSCODE] = (case when @LISTIMPORTFILECHANGED = 1 then @STATUSCODE else [STATUSCODE] end),
          [ORDERDATE] = @ORDERDATE,
          [EXPIRATIONDATE] = @EXPIRATIONDATE,
          [NUMBEROFCONTACTS] = @NUMBEROFCONTACTS,
          [LISTLAYOUTID] = (case when @LISTIMPORTFILECHANGED = 1 then @LISTLAYOUTID else [LISTLAYOUTID] end),
          [FILENAME] = (case when @LISTIMPORTFILECHANGED = 1 then @FILENAME else [FILENAME] end),
          [RENTALQUANTITY] = @RENTALQUANTITY,
          [RENTALCOSTADJUSTMENT] = @RENTALCOSTADJUSTMENT,
          [RENTALCOSTBASISCODE] = @RENTALCOSTBASISCODE,
          [EXCHANGEQUANTITY] = @EXCHANGEQUANTITY,
          [EXCHANGECOSTADJUSTMENT] = @EXCHANGECOSTADJUSTMENT,
          [EXCHANGECOSTBASISCODE] = @EXCHANGECOSTBASISCODE,
          [CONSOLIDATEDQUERYVIEWID] = (case when @LISTIMPORTFILECHANGED = 1 and @TYPECODE = 1 then null else [CONSOLIDATEDQUERYVIEWID] end),
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE,
          [BASECURRENCYID] = @BASECURRENCYID,
          [ORGANIZATIONRENTALCOSTADJUSTMENT] = @ORGANIZATIONRENTALCOSTADJUSTMENT,
          [ORGANIZATIONEXCHANGECOSTADJUSTMENT] = @ORGANIZATIONEXCHANGECOSTADJUSTMENT,
          [CURRENCYEXCHANGERATEID] = @CURRENCYEXCHANGERATEID
        where [ID] = @OLDSEGMENTLISTID;
      end


    update dbo.[MKTSEGMENT] set
      [NAME] = @NAME,
      [DESCRIPTION] = @DESCRIPTION,
      [SEGMENTCATEGORYCODEID] = @SEGMENTCATEGORYCODEID,
      [CODE] = (case when @ALLOWCODEUPDATE = 1 then @CODE else [CODE] end),
      [PARTDEFINITIONVALUESID] = (case when @ALLOWCODEUPDATE = 1 then @CODEVALUEID else [PARTDEFINITIONVALUESID] end),
      [QUERYVIEWCATALOGID] = @RECORDSOURCEID,
      [CURRENTSEGMENTLISTID] = @NEWSEGMENTLISTID,
      [SITEID] = @SITEID,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    --Save all the groups...

    exec dbo.[USP_MKTSEGMENT_GETGROUPS_UPDATEFROMXML] @ID, @GROUPS, @CHANGEAGENTID, @CURRENTDATE;

    --If the segment name changed, then update the matching query view, id set, and record type names.

    --Only do this if this is an imported list and only if the import file has NOT changed, because otherwise

    --the import process will create and rename all this.

    if @OLDNAME <> @NAME and @LISTIMPORTFILECHANGED = 0 and @QUERYVIEWCATALOGID is not null and @IDSETRECORDTYPEID is not null and @STANDARDIDSETID is not null
      begin
        declare @NEWNAME nvarchar(100);
        declare @NEWDESCRIPTION nvarchar(255);
        declare @NEWRECORDTYPE nvarchar(255);
        declare @NEWQUERYVIEWSPEC xml;

        select 
          @NEWNAME = @NAME + substring([DISPLAYNAME], len(@OLDNAME) + 1, len([DISPLAYNAME]) - len(@OLDNAME)),
          @NEWDESCRIPTION = replace([DESCRIPTION], @OLDNAME, @NAME),
          @NEWQUERYVIEWSPEC = [QUERYVIEWSPEC]
        from dbo.[QUERYVIEWCATALOG]
        where [ID] = @QUERYVIEWCATALOGID;

        select
          @NEWRECORDTYPE = @NAME + substring([NAME], len([NAME]) - charindex('(', reverse([NAME])), charindex('(', reverse([NAME])) + 1)
        from dbo.[RECORDTYPE]
        where [ID] = @IDSETRECORDTYPEID;

        set @NEWQUERYVIEWSPEC.modify('declare namespace QV="bb_appfx_queryview"; replace value of (/QV:QueryViewSpec/@Name)[1] with sql:variable("@NEWNAME")');
        set @NEWQUERYVIEWSPEC.modify('declare namespace QV="bb_appfx_queryview"; replace value of (/QV:QueryViewSpec/@Description)[1] with sql:variable("@NEWDESCRIPTION")');
        set @NEWQUERYVIEWSPEC.modify('declare namespace QV="bb_appfx_queryview"; replace value of (/QV:QueryViewSpec/@RecordType)[1] with sql:variable("@NEWRECORDTYPE")');

        --Update the record type name...

        update dbo.[RECORDTYPE] set
          [NAME] = @NEWRECORDTYPE,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @IDSETRECORDTYPEID;

        --Update the query view name, description, and record type...

        update dbo.[QUERYVIEWCATALOG] set
          [DISPLAYNAME] = @NEWNAME,
          [DESCRIPTION] = @NEWDESCRIPTION,
          [QUERYVIEWSPEC] = @NEWQUERYVIEWSPEC,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @QUERYVIEWCATALOGID;

        --Update the IDSet names...

        update dbo.[IDSETREGISTER] set
          [NAME] = replace([NAME], @OLDNAME, @NAME),
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @STANDARDIDSETID
        or [ID] = @DUPLICATEIDSETID;
      end

    --Check if the segment has any child list segments based off of it...

    if exists(select top 1 1 from dbo.[MKTSEGMENTLIST] where [PARENTSEGMENTID] = @ID)
      begin
        --If the segment's record source changed, then also update any sub list segments that are based off this segment...

        if @OLDRECORSOURCEID <> @RECORDSOURCEID
          update dbo.[MKTSEGMENT] set
            [QUERYVIEWCATALOGID] = @RECORDSOURCEID,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          from dbo.[MKTSEGMENT] as [S]
          inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [S].[CURRENTSEGMENTLISTID]
          where [MKTSEGMENTLIST].[PARENTSEGMENTID] = @ID;

        --If the segment's parent list changed, then also update any sub list segments that are based off this segment...

        if @OLDLISTID <> @LISTID
          update dbo.[MKTSEGMENTLIST] set
            [LISTID] = @LISTID,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [PARENTSEGMENTID] = @ID;
      end

    --Add an import process for lists to be imported...

    if @LISTIMPORTFILECHANGED = 1 and @TYPECODE = 0
      begin
        --Delete the old import process and add a new one...

        if @OLDIMPORTPROCESSID is not null
          exec dbo.[USP_MKTSEGMENTLISTIMPORTPROCESS_DELETEBYID_WITHCHANGEAGENTID] @OLDIMPORTPROCESSID, @CHANGEAGENTID;

        --Add a new import business process parameter set...

        exec dbo.[USP_MKTSEGMENTLISTIMPORTPROCESS_CREATE] null, @NEWSEGMENTLISTID, @CHANGEAGENTID;
      end

    /* Update the segment with the selected code */
    if @ALLOWCODEUPDATE = 1
      exec dbo.[USP_MKTSEGMENT_UPDATECODE] @ID, @CODE, @CODEVALUEID, @CHANGEAGENTID, 1, @CURRENTAPPUSERID;
  end try

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

  return 0;