/*
*    Creates a synonym for getting the customer phone information by customer number and address code
*    broken down into area code, local number and optional extension information.
*    The GetCustomerAddressInformation_Synonym will be set to use the appropriate function for the current
*    system, based upon if they have XLedger, GP, QuickBooks or no accounting system.
*
*/
CREATE PROCEDURE [dbo].[GetCustomerPhone1AreaCodeLocalNbrExt_Legacy]
    @inCustomerNumber varchar(15),
    @inCustomerAddressCode varchar(15)
as
set nocount on

declare @theCustomerPhone1 char(21)

if( isnull( @inCustomerAddressCode, '' ) = '' )
begin
    -- When AddressCode is '', phones come from RM00101
    select
        @theCustomerPhone1 = RM00101.Phone1
    from RM00101_Synonym as RM00101
    where RM00101.CUSTNMBR = convert( varchar(15), @inCustomerNumber )
end
else
begin
    -- When AddressCode is something, phones come from RM00102
    select
        @theCustomerPhone1 = RM00102.Phone1
    from RM00102_Synonym as RM00102
    where RM00102.CUSTNMBR = convert( varchar(15), @inCustomerNumber ) and
        RM00102.ADRSCODE = convert( varchar(15), @inCustomerAddressCode )
end

-- Return the specified Phone1 in its parts (as best we can)
select
    AreaCode = case
        when rtrim( @theCustomerPhone1 ) = '' then null
        when substring( @theCustomerPhone1, 1, 3 ) = '000' then null
        else substring( @theCustomerPhone1, 1, 3 )
    end,
    LocalNumber = case
        when rtrim( @theCustomerPhone1 ) = '' then null
        when substring( @theCustomerPhone1, 1, 3 ) = '000' then null
        else substring( @theCustomerPhone1, 4, 3 ) + '-' + substring( @theCustomerPhone1, 7, 4 )   
    end,
    Extension = case
        when rtrim( @theCustomerPhone1 ) = '' then null
        when substring( @theCustomerPhone1, 1, 3 ) = '000' then null
        when rtrim( substring( @theCustomerPhone1, 11, 10 ) ) in ( '0', '00', '000', '0000', '00000', '000000', '0000000', '00000000', '000000000', '0000000000' )  then null
        else rtrim( substring( @theCustomerPhone1, 11, 10 ) )
    end
GO
GRANT EXECUTE ON  [dbo].[GetCustomerPhone1AreaCodeLocalNbrExt_Legacy] TO [MssExec]
GO