/**
*
*    Returns the list of customer contacts for a specific customer number.  This is used by the
*    AR Credit and Collections module.
*
*    Do NOT call this SP directly.  Instead, call GetARCCustomerContacts_Synonym and then that
*    synonym knows to call this SP or another one based upon that system's active accounting
*    system type.
*/

create procedure [dbo].[GetARCCustomerContacts_Legacy]
    @inCustomerNumber varchar(15)
as
begin
    set nocount on

    select
        -- Contact fields
        ARCCustomerContactId = 2147483647,
        CustomerNumber = rtrim( RM00101.CUSTNMBR ),
        CustomerAddressCode = convert( varchar(15), '' ),
        Contact = rtrim( RM00101.CNTCPRSN ),
        Email = ltrim( rtrim( SY01200.INET1 ) ),

        -- Contact Address fields
        Address1 = rtrim( RM00101.Address1 ),
        Address2 = rtrim( RM00101.Address2 ),
        Address3 = rtrim( RM00101.Address3 ),
        City = case
            when len( rtrim( RM00101.City ) ) > 26 then substring( rtrim( RM00101.City ), 1, 26 )
            else rtrim( RM00101.City )
        end,
        [State] = case
            when len( rtrim( RM00101.[State] ) ) > 2 then substring( rtrim( RM00101.[State] ), 1, 2 )
            else rtrim( RM00101.[State] )
        end,
        Zip = case
            when len( rtrim( RM00101.ZIP ) ) > 10 then substring( rtrim( RM00101.ZIP ), 1, 10 )
            else rtrim( RM00101.ZIP )
        end,
        Country = CountryCodeStandard.CountryName,
        CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID,

        -- Phone1 Phone fields
        Phone1 = dbo.FormatGreatPlainsPhoneNumberForARCC( RM00101.PHONE1 ),
        Phone1CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID,
        Phone1Ext = dbo.ExtractGreatPlainsPhoneExtension( RM00101.PHONE1 ),

        -- Phone2 Phone fields
        Phone2 = dbo.FormatGreatPlainsPhoneNumberForARCC( RM00101.PHONE2 ),
        Phone2Ext = dbo.ExtractGreatPlainsPhoneExtension( RM00101.PHONE2 ),

        -- Phone3 Phone fields
        Phone3 = dbo.FormatGreatPlainsPhoneNumberForARCC( RM00101.PHONE3 ),
        Phone3Ext = dbo.ExtractGreatPlainsPhoneExtension( RM00101.PHONE3 ),

        -- Fax Phone fields
        Fax = dbo.FormatGreatPlainsPhoneNumberForARCC( RM00101.FAX )

    from RM00101_Synonym as RM00101
    left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = RM00101.COUNTRY
    left outer join SY01200_Synonym as SY01200 on SY01200.Master_ID = 'CUS' and SY01200.ADRSCODE = RM00101.ADRSCODE
    where RM00101.CUSTNMBR = @inCustomerNumber

    union all

    select
        -- Contact fields
        ARCCustomerContactId = RM00102.DEX_ROW_ID,
        CustomerNumber = rtrim( RM00102.CUSTNMBR ),
        CustomerAddressCode = rtrim( RM00102.ADRSCODE ),
        Contact = rtrim( RM00102.CNTCPRSN ),
        Email = ltrim( rtrim( SY01200.INET1 ) ),

        -- Contact Address fields
        Address1 = rtrim( RM00102.Address1 ),
        Address2 = rtrim( RM00102.Address2 ),
        Address3 = rtrim( RM00102.Address3 ),
        City = case
            when len( rtrim( RM00102.City ) ) > 26 then substring( rtrim( RM00102.City ), 1, 26 )
            else rtrim( RM00102.City )
        end,
        [State] = case
            when len( rtrim( RM00102.[State] ) ) > 2 then substring( rtrim( RM00102.[State] ), 1, 2 )
            else rtrim( RM00102.[State] )
        end,
        Zip = case
            when len( rtrim( RM00102.ZIP ) ) > 10 then substring( rtrim( RM00102.ZIP ), 1, 10 )
            else rtrim( RM00102.ZIP )
        end,
        Country = CountryCodeStandard.CountryName,
        CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID,

        -- Phone1 Phone fields
        Phone1 = dbo.FormatGreatPlainsPhoneNumberForARCC( RM00102.PHONE1 ),
        Phone1CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID,
        Phone1Ext = dbo.ExtractGreatPlainsPhoneExtension( RM00102.PHONE1 ),

        -- Phone2 Phone fields
        Phone2 = dbo.FormatGreatPlainsPhoneNumberForARCC( RM00102.PHONE2 ),
        Phone2Ext = dbo.ExtractGreatPlainsPhoneExtension( RM00102.PHONE2 ),

        -- Phone3 Phone fields
        Phone3 = dbo.FormatGreatPlainsPhoneNumberForARCC( RM00102.PHONE3 ),
        Phone3Ext = dbo.ExtractGreatPlainsPhoneExtension( RM00102.PHONE3 ),

        -- Fax Phone fields
        Fax = dbo.FormatGreatPlainsPhoneNumberForARCC( RM00102.FAX )

    from RM00102_Synonym as RM00102
    left outer join CountryCodeStandard on CountryCodeStandard.VanlineCountryCode = RM00102.COUNTRY
    left outer join SY01200_Synonym as SY01200 on SY01200.Master_ID = 'CUS' and SY01200.ADRSCODE = RM00102.ADRSCODE
    where RM00102.CUSTNMBR = @inCustomerNumber

    order by CustomerAddressCode
end
GO
GRANT EXECUTE ON  [dbo].[GetARCCustomerContacts_Legacy] TO [MssExec]
GO