/**
*  
*  This function takes a phone number string from Great Plains and formats it to the (***) ***-**** x.**** format.
*  If the input string is not of length 7, 10, or 11 or more, then the source string is returned "as is", but trimmed.
*  
*  @param @inString The phone number character string that will be formated
*  @param @outString The formatted character string that will be returned
*/

create function dbo.FormatGreatPlainsPhoneNumberForARCC
(
    @inString varchar( 21 )
)
returns varchar( 25 )
as
begin
    declare @outString varchar( 25 ) = ''
    declare @theLength int

    set @inString = ltrim( rtrim( @inString ) )
    set @theLength = len( @inString )
    if( isnull( @inString, '' ) != '' and @inString != '00000000000000' )
    begin
        set @outString = case
            when @theLength = 7 then '000' + substring( @inString, 1, 7 )
            when @theLength >= 10 then substring( @inString, 1, 10 )
            else @inString
        end
    end
    return @outString

end
GO
GRANT EXECUTE ON  [dbo].[FormatGreatPlainsPhoneNumberForARCC] TO [MssExec]
GO