USP_CONTROLACCOUNT_SAVE

CREATE procedure dbo.USP_CONTROLACCOUNT_SAVE
  @ID uniqueidentifier,
  @CONTROLACCOUNTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CHANGEDATE datetime,
  @DATAELEMENTS xml,
  @ALLOWEDIT bit,
  @ALLOWOTHERSUBSIDIARY bit,
  @ALLOWJOURNALENTRY bit,
  @ACCOUNTTYPECODE tinyint

as
merge dbo.CONTROLACCOUNT as Target
using (select @CONTROLACCOUNTID as ControlAccountID, cast([1] as uniqueidentifier) as Col1, cast([2] as uniqueidentifier) as Col2, cast([3] as uniqueidentifier) as Col3, cast([4] as uniqueidentifier) as Col4, cast([5] as uniqueidentifier) as Col5, cast([6] as uniqueidentifier) as Col6, cast([7] as uniqueidentifier) as Col7, cast([8] as uniqueidentifier) as Col8, cast([9] as uniqueidentifier) as Col9, cast([10] as uniqueidentifier) as Col10, 
    cast([11] as uniqueidentifier) as Col11, cast([12] as uniqueidentifier) as Col12, cast([13] as uniqueidentifier) as Col13, cast([14] as uniqueidentifier) as Col14, cast([15] as uniqueidentifier) as Col15, cast([16] as uniqueidentifier) as Col16, cast([17] as uniqueidentifier) as Col17, cast([18] as uniqueidentifier) as Col18, cast([19] as uniqueidentifier) as Col19, cast([20] as uniqueidentifier) as Col20, 
    cast([21] as uniqueidentifier) as Col21, cast([22] as uniqueidentifier) as Col22, cast([23] as uniqueidentifier) as Col23, cast([24] as uniqueidentifier) as Col24, cast([25] as uniqueidentifier) as Col25, cast([26] as uniqueidentifier) as Col26, cast([27] as uniqueidentifier) as Col27, cast([28] as uniqueidentifier) as Col28, cast([29] as uniqueidentifier) as Col29, cast([30] as uniqueidentifier) as Col30 
    from (
      select cast(DATAELEMENTID as nvarchar(36)) as Element, NUM
      from dbo.UFN_CONTROLACCOUNT_GETDATAELEMENTS_FROMITEMLISTXML(@DATAELEMENTS) f1
      inner join dbo.PDACCOUNTSEGMENTVALUE on f1.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
      inner join dbo.PDACCOUNTSTRUCTURE ON PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
      right join dbo.NUMBERS on PDACCOUNTSTRUCTURE.SEGMENTCOLUMN = NUMBERS.NUM
      where NUMBERS.NUM between 1 and 30) as S
      pivot
      (max(Element) for NUM in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])) as P
 ) as Source
on Target.ID = Source.ControlAccountID
when matched then
  update set Target.DATAELEMENT1ID = Source.Col1,
  Target.DATAELEMENT2ID = Source.Col2,
  Target.DATAELEMENT3ID = Source.Col3,
  Target.DATAELEMENT4ID = Source.Col4,
  Target.DATAELEMENT5ID = Source.Col5,
  Target.DATAELEMENT6ID = Source.Col6,
  Target.DATAELEMENT7ID = Source.Col7,
  Target.DATAELEMENT8ID = Source.Col8,
  Target.DATAELEMENT9ID = Source.Col9,
  Target.DATAELEMENT10ID = Source.Col10,
  Target.DATAELEMENT11ID = Source.Col11,
  Target.DATAELEMENT12ID = Source.Col12,
  Target.DATAELEMENT13ID = Source.Col13,
  Target.DATAELEMENT14ID = Source.Col14,
  Target.DATAELEMENT15ID = Source.Col15,
  Target.DATAELEMENT16ID = Source.Col16,
  Target.DATAELEMENT17ID = Source.Col17,
  Target.DATAELEMENT18ID = Source.Col18,
  Target.DATAELEMENT19ID = Source.Col19,
  Target.DATAELEMENT20ID = Source.Col20,
  Target.DATAELEMENT21ID = Source.Col21,
  Target.DATAELEMENT22ID = Source.Col22,
  Target.DATAELEMENT23ID = Source.Col23,
  Target.DATAELEMENT24ID = Source.Col24,
  Target.DATAELEMENT25ID = Source.Col25,
  Target.DATAELEMENT26ID = Source.Col26,
  Target.DATAELEMENT27ID = Source.Col27,
  Target.DATAELEMENT28ID = Source.Col28,
  Target.DATAELEMENT29ID = Source.Col29,
  Target.DATAELEMENT30ID = Source.Col30,
  Target.CHANGEDBYID = @CHANGEAGENTID,
  Target.DATECHANGED = @CHANGEDATE,
  Target.ALLOWEDIT = @ALLOWEDIT,
  Target.ALLOWOTHERSUBSIDIARY = @ALLOWOTHERSUBSIDIARY,
  Target.ALLOWJOURNALENTRY = @ALLOWJOURNALENTRY,
  Target.ACCOUNTTYPECODE = @ACCOUNTTYPECODE,
  Target.SYSTEMID = @ID
when not matched by Target then
  insert (ID, SYSTEMID, ALLOWEDIT, ALLOWOTHERSUBSIDIARY, ALLOWJOURNALENTRY, ACCOUNTTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
          DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
          DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
          DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)
  values (isnull(@CONTROLACCOUNTID,newid()), @ID, @ALLOWEDIT, @ALLOWOTHERSUBSIDIARY, @ALLOWJOURNALENTRY, @ACCOUNTTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE,
          Source.Col1, Source.Col2, Source.Col3, Source.Col4, Source.Col5, Source.Col6, Source.Col7, Source.Col8, Source.Col9, Source.Col10, 
          Source.Col11, Source.Col12, Source.Col13, Source.Col14, Source.Col15, Source.Col16, Source.Col17, Source.Col18, Source.Col19, Source.Col20, 
          Source.Col21, Source.Col22, Source.Col23, Source.Col24, Source.Col25, Source.Col26, Source.Col27, Source.Col28, Source.Col29, Source.Col30);