CREATE Procedure [dbo].[Sp_EditLevelAccCode] as
----------------------(Build Parent_Code)-------------------
Declare @AccCode varchar(20)
Declare @Len Smallint
Declare @ParentLen Smallint

Declare Cursor_Update Cursor For
Select Len , parent_Len From Acc_Level
Open Cursor_Update
Fetch Next From Cursor_Update into @len, @ParentLen 
While @@Fetch_Status = 0 
Begin
    IF (@Len =1)
   	  Update Account Set Parent_Code = Null 
	  From Account Inner Join Acc_level 
	  on Acc_level.Len = len(Acc_Code)
	  Where Acc_level.Acc_level = 1
	  And Parent_Code is not Null

	Update Account Set Parent_Code = T.Autoid From Account 
	Inner Join (
	Select  Acc.Autoid, Acc1.Acc_Code From Account Acc
	Left Outer Join Account Acc1
	on Acc.Acc_Code = left(Acc1.Acc_code,@ParentLen) 
	Where Len(Acc1.Acc_Code) = @Len 
	) AS T on T.Acc_Code = Account.Acc_Code   
	Where Parent_Code <> T.Autoid

    Fetch Next From Cursor_Update into @len, @ParentLen 
End
Close Cursor_Update
Deallocate Cursor_Update

----------------------(Build Acc_Level)-------------------
Update Account Set Acc_Level = T.Acc_Level From Account
Inner Join 
(
	Select Acc_Code, Acc_Level.Acc_Level From Acc_Level
	Inner Join Account 
	on Acc_Level.Len = Len(Account.Acc_Code) 
) As T on T.Acc_Code = Account.Acc_Code
Where Account.Acc_Level <> T.Acc_Level


---------------------(Build Has_Child)--------------
UPDATE Account SET Has_Child = 1 FROM Account INNER JOIN
dbo.Account AS T1 ON T1.Parent_Code = dbo.Account.AutoId
WHERE Account.Has_Child = 0
;
DECLARE @Autoid BIGINT
DECLARE @AccCodeCase VARCHAR(10)
DECLARE Cursor_Update CURSOR FOR
    SELECT  AutoId ,
            Acc_Code
    FROM    dbo.Account
    WHERE   Has_Child = 1
 OPEN Cursor_Update
 FETCH NEXT FROM Cursor_Update INTO @Autoid, @AccCode
 WHILE @@FETCH_STATUS = 0
 BEGIN
    SET @AccCodeCase = @AccCode + '%'
    IF NOT EXISTS (SELECT AutoId FROM  Account
                    WHERE  Acc_Code LIKE @AccCodeCase
                    AND Acc_Code <> @AccCode
				   )
        UPDATE  dbo.Account
        SET     Has_Child = 0
        WHERE   AutoId = @Autoid
    FETCH NEXT FROM Cursor_Update INTO @Autoid, @AccCode
 END
 CLOSE Cursor_Update
 DEALLOCATE Cursor_Update