USP_GLACCOUNT_REFRESH2

    CREATE proc dbo.USP_GLACCOUNT_REFRESH2(@SysID uniqueidentifier, @ChangeAgentID uniqueidentifier = null)
    as
    begin

    set nocount on

    declare @StringTable table (ID int IDENTITY(1,1) PRIMARY KEY, GLACCOUNTID uniqueidentifier, SHORTDESCRIPTION nvarchar(100), SEPARATOR nvarchar(1), PDACCOUNTNUMBER nvarchar(100))
    declare @String nvarchar(100)

      insert into @StringTable (GLACCOUNTID, SHORTDESCRIPTION, SEPARATOR)
    select V1.GLACCOUNTID, isnull(t4.SHORTDESCRIPTION,replicate('0',V1.LENGTH)), case V1.SEPARATORCODE when 6 then '' else left(V1.SEPARATOR,1) end
      from
      (select t1.id as PDACCOUNTSTRUCTUREID, t2.id as GLACCOUNTID, t1.SEPARATOR, t1.SEQUENCE, t1.LENGTH, t1.SEPARATORCODE
      from dbo.PDACCOUNTSTRUCTURE t1 cross join dbo.GLACCOUNT t2 where t1.PDACCOUNTSYSTEMID=@SysID and t2.PDACCOUNTSYSTEMID=@SysID) V1
      left join dbo.PDACCOUNTSEGMENT t3 on V1.PDACCOUNTSTRUCTUREID = t3.PDACCOUNTSTRUCTUREID and V1.GLACCOUNTID = t3.GLACCOUNTID
      left join dbo.PDACCOUNTSEGMENTVALUE t4 on t3.PDACCOUNTSEGMENTVALUEID = t4.ID
      order by V1.GLACCOUNTID, V1.SEQUENCE

    update A1
    set @String = PDACCOUNTNUMBER = case when V1.GLACCOUNTID = V1.GLACCOUNTID2 then @String+V1.SEPARATOR+ShortDescription2 else ShortDescription2 end
    from
    (select t1.GLACCOUNTID, t1.SHORTDESCRIPTION, t1.SEPARATOR, t2.GLACCOUNTID as GLACCOUNTID2, t2.SHORTDESCRIPTION as ShortDescription2, t2.ID
    from @StringTable t1 RIGHT JOIN @StringTable t2 on t1.GLACCOUNTID = t2.GLACCOUNTID and t1.ID+1 = t2.ID) V1
    inner join @StringTable A1 on V1.ID = A1.ID

    update dbo.GLACCOUNT
    set ACCOUNTNUMBER = t1.PDACCOUNTNUMBER,
    CHANGEDBYID = @ChangeAgentID,
    DATECHANGED = getdate()
    from dbo.GLACCOUNT inner join @StringTable t1 on dbo.GLACCOUNT.ID = t1.GLACCOUNTID
    inner join (select max(ID) as ID, GLACCOUNTID from @StringTable group by GLACCOUNTID) V1 on t1.ID = V1.ID

    end