USP_DATAFORMTEMPLATE_EDIT_LEDGERACCOUNT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_LEDGERACCOUNT (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @DESCRIPTION nvarchar(400),
    @DATAELEMENTS xml,
  @PDACCOUNTSYSTEMID uniqueidentifier
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

  if @PDACCOUNTSYSTEMID is null
    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

    begin try

        /* Verify that the account is unique */
        if dbo.UFN_LEDGERACCOUNT_ACCOUNTISUNIQUE(@DATAELEMENTS,@ID) = 0
            raiserror('ERR_LEDGERACCOUNT_NONUNIQUEACCOUNTSTRING',13,1)

        begin tran;

    declare @Segments table (DATAELEMENTID uniqueidentifier, SEGMENTSEQUENCE tinyint, SEGMENTCOLUMN tinyint, SHORTDESCRIPTION nvarchar(100))

    insert into @Segments (DATAELEMENTID, SEGMENTSEQUENCE, SEGMENTCOLUMN, SHORTDESCRIPTION)
    select tf1.DATAELEMENTID, PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE, NUMBERS.NUM, PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION
    from dbo.UFN_LEDGERACCOUNT_GETACCOUNTSEGMENTS_FROMITEMLISTXML(@DATAELEMENTS) tf1
    inner join dbo.PDACCOUNTSEGMENTVALUE on tf1.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

    update dbo.GLACCOUNT
    set ACCOUNTDESCRIPTION = @DESCRIPTION
            , CHANGEDBYID = @CHANGEAGENTID
            , DATECHANGED = @CURRENTDATE
      , DATAELEMENT1ID = Col1
      , DATAELEMENT2ID = Col2
      , DATAELEMENT3ID = Col3
      , DATAELEMENT4ID = Col4
      , DATAELEMENT5ID = Col5
      , DATAELEMENT6ID = Col6
      , DATAELEMENT7ID = Col7
      , DATAELEMENT8ID = Col8
      , DATAELEMENT9ID = Col9
      , DATAELEMENT10ID = Col10
      , DATAELEMENT11ID = Col11
      , DATAELEMENT12ID = Col12
      , DATAELEMENT13ID = Col13
      , DATAELEMENT14ID = Col14
      , DATAELEMENT15ID = Col15
      , DATAELEMENT16ID = Col16
      , DATAELEMENT17ID = Col17
      , DATAELEMENT18ID = Col18
      , DATAELEMENT19ID = Col19
      , DATAELEMENT20ID = Col20
      , DATAELEMENT21ID = Col21
      , DATAELEMENT22ID = Col22
      , DATAELEMENT23ID = Col23
      , DATAELEMENT24ID = Col24
      , DATAELEMENT25ID = Col25
      , DATAELEMENT26ID = Col26
      , DATAELEMENT27ID = Col27
      , DATAELEMENT28ID = Col28
      , DATAELEMENT29ID = Col29
      , DATAELEMENT30ID = Col30
    from dbo.GLACCOUNT
    inner join
      (select @ID as ID,
    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, SEGMENTCOLUMN
      from @Segments) as S
      pivot
      (max(Element) for SEGMENTCOLUMN 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) v1
      on GLACCOUNT.ID = v1.ID

        commit;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;