USP_DATAFORMTEMPLATE_EDIT_GLACCOUNTSEQUENCE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_GLACCOUNTSEQUENCE (
    @CHANGEAGENTID uniqueidentifier = null,
    @ACCOUNTSEQUENCE xml

)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        -- handle updating the data

        exec dbo.USP_ACCOUNTSEQUENCE_UPDATEFROMXML @ACCOUNTSEQUENCE, @CHANGEAGENTID;

    -- update account strings to match new sequence

    declare @Segments table (LEDGERACCOUNTID uniqueidentifier, SEGMENTSEQUENCE tinyint, SHORTID nvarchar(100))
    insert into @Segments (LEDGERACCOUNTID, SEGMENTSEQUENCE, SHORTID)
    select unPvt.ID, PDACCOUNTSTRUCTURE.SEGMENTSEQUENCE, PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION
      from
    (select ID, 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
    from dbo.GLACCOUNT) p
    unpivot
    (DATAELEMENTID for dColumn in
        (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)) as unPvt
    inner join dbo.PDACCOUNTSEGMENTVALUE ON unPvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID    
    inner join dbo.PDACCOUNTSTRUCTURE ON PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID

    declare @SEPARATOR nvarchar(1);
    select top 1 @SEPARATOR = SEPARATOR from dbo.PDACCOUNTSTRUCTURE;
    update dbo.GLACCOUNT
    set ACCOUNTSTRING =
        substring((select isnull(SHORTID+@SEPARATOR,'')
      from @Segments S
      where S.LEDGERACCOUNTID = ID
      order by S.SEGMENTSEQUENCE
      for xml path(''), type).value('.','nvarchar(130)'), 1, len((select isnull(SHORTID+@SEPARATOR,'')
      from @Segments S
      where S.LEDGERACCOUNTID = ID
      order by S.SEGMENTSEQUENCE
      for xml path(''), type).value('.','nvarchar(130)'))-1)      
end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch
return 0;