/**
*  
*  This function takes the MoversSuite phone parts (area code, local number and extension) and formats it
*  into the zero-filled varchar(21) Great Plains 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 @inAreaCode The phone number character string that will be formated
*  @param @outString The formatted 21 character string that will be returned for saving to the GP or the samed named
*  tables in the MoversSuite database (for like QuickBooks).
*/

CREATE FUNCTION dbo.FormatGreatPlainsPhoneNumberFromParts
(
    @inAreaCode dbo.PhoneAreaCode,
    @inLocalNumber dbo.PhoneLocalNumber,
    @inExtension dbo.PhoneExtension
)
returns varchar(21)
as
begin

    declare @outString varchar(21) = '00000000000000'
    if( isnull( @inLocalNumber, '' ) != '' )
    begin
        select
            @inAreaCode = rtrim( ltrim( @inAreaCode ) ),
            @inLocalNumber = rtrim( ltrim( @inLocalNumber ) ),
            @inExtension = rtrim( ltrim( @inExtension ) )

        select
            @inAreaCode = case len( @inAreaCode )
                when 3 then @inAreaCode
                else '000'
            end,
            @inLocalNumber = case
                when @inLocalNumber != '' then replace( replace( replace( @inLocalNumber, '-', '' ), ' ', '' ), '.', '' )
                else ''
            end

        select
            @inLocalNumber = case len( @inLocalNumber )
                when 7 then @inLocalNumber
                else '0000000'
            end,
            @inExtension = case len( @inExtension )
                when 4 then @inExtension
                when 3 then concat( '0', @inExtension )
                when 2 then concat( '00', @inExtension )
                when 1 then concat( '000', @inExtension )
                else '0000'
            end

        set @outString = concat( @inAreaCode, @inLocalNumber, @inExtension )
    end

    return @outString

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