USP_DATAFORMTEMPLATE_EDIT_MKTASKLADDER_2

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTASKLADDER_2]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @CURRENCYSYMBOL nvarchar(3),
  @RECORDSOURCEID uniqueidentifier,
  @OBJECTKEY nvarchar(400),
  @ITEMLIST xml,
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEID uniqueidentifier
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @INUSE bit;

  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

  declare @ITEMLISTTABLE table (
   [ID] uniqueidentifier,
   [BASECURRENCYID] uniqueidentifier,
   [ITEMVALUE1] decimal(20, 4),
   [ITEMVALUE2] decimal(20, 4),
   [ITEMVALUE3] decimal(20, 4),
   [ITEMVALUE4] decimal(20, 4),
   [ITEMVALUE5] decimal(20, 4),
   [MINIMUMENTRYAMOUNT] decimal(20, 4),
   [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
   [ORGANIZATIONITEMVALUE1] decimal(20, 4),
   [ORGANIZATIONITEMVALUE2] decimal(20, 4),
   [ORGANIZATIONITEMVALUE3] decimal(20, 4),
   [ORGANIZATIONITEMVALUE4] decimal(20, 4),
   [ORGANIZATIONITEMVALUE5] decimal(20, 4),
   [ORGANIZATIONMINIMUMENTRYAMOUNT] decimal(20, 4),
   [ROUNDTOAMOUNT] decimal(20, 4),
   [TYPECODE] tinyint,
   [WRITEINTEXT] nvarchar(100)
  );

  begin try
    set @INUSE = dbo.[UFN_MKTASKLADDER_ISINUSE](@ID);

    -- If any ask amount 1 with a value of 0 in the ITEMLIST collection is of type ("fixed" or "multiply"), don't save.

    if @ITEMLIST.exist('/ITEMLIST/ITEM[TYPECODE[(text()[1]) != 0] and ITEMVALUE1[(text()[1]) = 0]] ') = 1
      raiserror('ERR_ITEMVALUE1ISZERO', 16, 10);

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

    set @CURRENTDATE = getdate();

    select @BASECURRENCYID = [BASECURRENCYID] from dbo.[MKTASKLADDER] where [ID] = @ID;

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

    if dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('97d98e59-5b0b-446f-bf48-de8709f50afe') = 1 -- multicurrency

      -- overwrite the currency symbol passed in with the symbol for the base currency

      select @CURRENCYSYMBOL = [CURRENCYSYMBOL] from dbo.[CURRENCY] where [ID] = @BASECURRENCYID;

    update dbo.[MKTASKLADDER] set 
      [NAME] = @NAME,
      [CURRENCYSYMBOL] = @CURRENCYSYMBOL,
      [RECORDSOURCEID] = case when @INUSE = 1 then [RECORDSOURCEID] else @RECORDSOURCEID end,
      [OBJECTKEY] = case when @INUSE = 1 then [OBJECTKEY] else @OBJECTKEY end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE,
      [SITEID] = @SITEID
    where [ID] = @ID;

    if @ITEMLIST is not null and @INUSE = 0
      begin
        insert into @ITEMLISTTABLE
        select
          [ID],
          [BASECURRENCYID],
          [ITEMVALUE1],
          isnull([ITEMVALUE2], 0),
          isnull([ITEMVALUE3], 0),
          isnull([ITEMVALUE4], 0),
          isnull([ITEMVALUE5], 0),
          isnull([MINIMUMENTRYAMOUNT], 0),
          [ORGANIZATIONEXCHANGERATEID],
          [ORGANIZATIONITEMVALUE1],
          isnull([ORGANIZATIONITEMVALUE2], 0),
          isnull([ORGANIZATIONITEMVALUE3], 0),
          isnull([ORGANIZATIONITEMVALUE4], 0),
          isnull([ORGANIZATIONITEMVALUE5], 0),
          isnull([ORGANIZATIONMINIMUMENTRYAMOUNT], 0),
          isnull([ROUNDTOAMOUNT], 0),
          [TYPECODE],
          [WRITEINTEXT] 
        from dbo.[UFN_MKTASKLADDER_GETITEMLIST_FROMITEMLISTXML](@ITEMLIST);

        if @BASECURRENCYID <> @ORGANIZATIONCURRENCYID
          begin
            set @ORGANIZATIONEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, default, default, default);

            update @ITEMLISTTABLE set
              [BASECURRENCYID] = @BASECURRENCYID,
              [ORGANIZATIONEXCHANGERATEID] = @ORGANIZATIONEXCHANGERATEID,
             [ORGANIZATIONMINIMUMENTRYAMOUNT] = dbo.[UFN_CURRENCY_CONVERT]([MINIMUMENTRYAMOUNT], @ORGANIZATIONEXCHANGERATEID),
              [ORGANIZATIONITEMVALUE1] = dbo.[UFN_CURRENCY_CONVERT]([ITEMVALUE1], @ORGANIZATIONEXCHANGERATEID),
              [ORGANIZATIONITEMVALUE2] = dbo.[UFN_CURRENCY_CONVERT]([ITEMVALUE2], @ORGANIZATIONEXCHANGERATEID),
              [ORGANIZATIONITEMVALUE3] = dbo.[UFN_CURRENCY_CONVERT]([ITEMVALUE3], @ORGANIZATIONEXCHANGERATEID),
              [ORGANIZATIONITEMVALUE4] = dbo.[UFN_CURRENCY_CONVERT]([ITEMVALUE4], @ORGANIZATIONEXCHANGERATEID),
              [ORGANIZATIONITEMVALUE5] = dbo.[UFN_CURRENCY_CONVERT]([ITEMVALUE5], @ORGANIZATIONEXCHANGERATEID)
          end
        else
          update @ITEMLISTTABLE set
            [BASECURRENCYID] = @BASECURRENCYID,
            [ORGANIZATIONEXCHANGERATEID] = null,
            [ORGANIZATIONMINIMUMENTRYAMOUNT] = [MINIMUMENTRYAMOUNT],
            [ORGANIZATIONITEMVALUE1] = [ITEMVALUE1],
            [ORGANIZATIONITEMVALUE2] = [ITEMVALUE2],
            [ORGANIZATIONITEMVALUE3] = [ITEMVALUE3],
            [ORGANIZATIONITEMVALUE4] = [ITEMVALUE4],
            [ORGANIZATIONITEMVALUE5] = [ITEMVALUE5];

        set @ITEMLIST = (
          select 
            [ID],
            [BASECURRENCYID],
            [ITEMVALUE1],
            [ITEMVALUE2],
            [ITEMVALUE3],
            [ITEMVALUE4],
            [ITEMVALUE5],
            [MINIMUMENTRYAMOUNT],
            [ORGANIZATIONEXCHANGERATEID],
            [ORGANIZATIONITEMVALUE1],
            [ORGANIZATIONITEMVALUE2],
            [ORGANIZATIONITEMVALUE3],
            [ORGANIZATIONITEMVALUE4],
            [ORGANIZATIONITEMVALUE5],
            [ORGANIZATIONMINIMUMENTRYAMOUNT],
            [ROUNDTOAMOUNT],
            [TYPECODE],
            [WRITEINTEXT]
          from @ITEMLISTTABLE
          order by [MINIMUMENTRYAMOUNT]
          for xml raw('ITEM'), type, elements, root('ITEMLIST'), binary base64);

        exec dbo.[USP_MKTASKLADDER_GETITEMLIST_UPDATEFROMXML] @ID, @ITEMLIST, @CHANGEAGENTID, @CURRENTDATE
      end
  end try

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

  return 0;