CREATE PROCEDURE [dbo].[Sp_AccountTransferGuy]
    @OldAccCode VARCHAR(20) ,
    @ParentCode VARCHAR(20) ,
    @PersonType SMALLINT ,
    @Result SMALLINT OUTPUT
AS
    --@Result =  0  Result is Successfully
    --@Result = -1  Insert AccCode Is Error
    --msg-num = -2  CheckValidation GuyCode Or Status inValid

    DECLARE @Name NVARCHAR(90)
    DECLARE @GuyCode VARCHAR(5)
    DECLARE @AutoId INT
    DECLARE @NewAccCode VARCHAR(20)
    
    SET @Result = 0
    SET @GuyCode = RIGHT(@OldAccCode, 5)
    SELECT @Name = Name FROM Guy WHERE Guy_Code = @GuyCode
    SET @NewAccCode = @ParentCode + @GuyCode
    Select @AutoId = Autoid FROM account WHERE Acc_Code = @ParentCode
    
    BEGIN Tran
    IF LEN(@GuyCode) = 5 AND (@AutoId IS NOT Null)
    BEGIN
      IF EXISTS ( SELECT  Acc_Code FROM Account WHERE Acc_Code = @NewAccCode )
      BEGIN
         IF NOT EXISTS ( SELECT  Acc_Code FROM  Acc_link WHERE   Acc_Code = @NewAccCode )
         BEGIN
           INSERT  INTO Acc_link (Class , Code , Acc_Code , Acc_Parent_Code )
           VALUES                (@PersonType ,@GuyCode ,@NewAccCode , @ParentCode)
           IF @@Error > 0
		   BEGIN
		 	 SET @Result = -1     
		 	 ROLLBACK TRAN
			 RETURN @Result
		   END
         END
      END
      ELSE
      BEGIN
         INSERT  INTO Account (Acc_Code , Acc_Name , Parent_Code , Acc_Level)
         VALUES               (@NewAccCode , @Name , @AutoId , 3 )
         IF @@Error > 0
         BEGIN
		   SET @Result = -1     
		   ROLLBACK TRAN
		   RETURN @Result
		 END
         INSERT  INTO Acc_link (Class , Code , Acc_Code , Acc_Parent_Code)
         VALUES                (@PersonType , @GuyCode ,@NewAccCode  , @ParentCode)
		 IF @@Error > 0
		 BEGIN
		   SET @Result = -1     
		   ROLLBACK TRAN
		   RETURN @Result
		 END
      END
      ---------------------------------------------------
      UPDATE  Sanad
      SET     Acc_Code = @NewAccCode
      WHERE   Acc_Code = @OldAccCode

      UPDATE  HeadC
      SET     AccCode = @NewAccCode
      WHERE   AccCode = @OldAccCode
      ---------------------------------------------------
    END
    ELSE
      SET @Result = -2
      
    COMMIT TRAN
    
    RETURN @Result