/**
*
*    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_XLedger]
    @inCustomerNumber varchar(15)
as
begin
    set nocount on

    select
        -- Contact fields
        ARCCustomerContactId = ARCCustomerContact.ARCCustomerContactId,
        CustomerNumber = AccountingCustomer.CustomerNumber,
        CustomerAddressCode = convert( varchar(15), null ),
        Contact = ARCCustomerContact.ContactPerson,
        Email = ARCCustomerContact.EmailAddress,

        -- Contact Address fields
        Address1 = ARCCustomerContactAddress.Address1,
        Address2 = ARCCustomerContactAddress.Address2,
        Address3 = ARCCustomerContactAddress.Address3,
        City = ARCCustomerContactAddress.City,
        [State] = ARCCustomerContactAddress.[State],
        Zip = ARCCustomerContactAddress.PostalCode,
        Country = CountryCodeStandard.CountryName,
        CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID,

        -- Phone1 Phone fields
        Phone1 = dbo.FullPhoneNumberDigits( Phone1Phone.AreaCode, Phone1Phone.LocalNumber ),
        Phone1CountryCodeStandardFID = Phone1Phone.CountryCodeStandardFID,
        Phone1Ext = Phone1Phone.Extension,

        -- Phone2 Phone fields
        Phone2 = dbo.FullPhoneNumberDigits( Phone2Phone.AreaCode, Phone2Phone.LocalNumber ),
        Phone2Ext = Phone2Phone.Extension,

        -- Phone3 Phone fields
        Phone3 = dbo.FullPhoneNumberDigits( Phone3Phone.AreaCode, Phone3Phone.LocalNumber ),
        Phone3Ext = Phone3Phone.Extension,

        -- Fax Phone fields
        Fax = dbo.FullPhoneNumberDigits( FaxPhone.AreaCode, FaxPhone.LocalNumber )

    from AccountingCustomer
    inner join ARCCustomerContact on ARCCustomerContact.AccountingCustomerFid = AccountingCustomer.AccountingCustomerId
    inner join AddressType on ( AddressType.TypeName = 'Main' )
    inner join ARCCustomerContactPhoneType as Phone1PhoneType on ( Phone1PhoneType.TypeName = 'Phone1' )
    inner join ARCCustomerContactPhoneType as Phone2PhoneType on ( Phone2PhoneType.TypeName = 'Phone2' )
    inner join ARCCustomerContactPhoneType as Phone3PhoneType on ( Phone3PhoneType.TypeName = 'Phone3' )
    inner join ARCCustomerContactPhoneType as FaxPhoneType on ( FaxPhoneType.TypeName = 'Fax' )
    left outer join ARCCustomerContactPhoneNumber as Phone1Phone on
    (
        Phone1Phone.ARCCustomerContactFid = ARCCustomerContact.ARCCustomerContactId and
        Phone1Phone.ARCCustomerContactPhoneTypeFid = Phone1PhoneType.ARCCustomerContactPhoneTypeId
    )
    left outer join ARCCustomerContactPhoneNumber as Phone2Phone on
    (
        Phone2Phone.ARCCustomerContactFid = ARCCustomerContact.ARCCustomerContactId and
        Phone2Phone.ARCCustomerContactPhoneTypeFid = Phone2PhoneType.ARCCustomerContactPhoneTypeId
    )
    left outer join ARCCustomerContactPhoneNumber as Phone3Phone on
    (
        Phone3Phone.ARCCustomerContactFid = ARCCustomerContact.ARCCustomerContactId and
        Phone3Phone.ARCCustomerContactPhoneTypeFid = Phone3PhoneType.ARCCustomerContactPhoneTypeId
    )
    left outer join ARCCustomerContactPhoneNumber as FaxPhone on
    (
        FaxPhone.ARCCustomerContactFid = ARCCustomerContact.ARCCustomerContactId and
        FaxPhone.ARCCustomerContactPhoneTypeFid = FaxPhoneType.ARCCustomerContactPhoneTypeId
    )
    left outer join ARCCustomerContactAddress on
    (
        ARCCustomerContactAddress.ARCCustomerContactFid = ARCCustomerContact.ARCCustomerContactId and
        ARCCustomerContactAddress.AddressTypeFid = AddressType.AddressTypeID
    )
    left outer join CountryCodeStandard on ( CountryCodeStandard.CountryCodeStandardID = ARCCustomerContactAddress.CountryCodeStandardFid )
    where AccountingCustomer.CustomerNumber = @inCustomerNumber
    order by ARCCustomerContact.AccountingCustomerFid,
        ARCCustomerContact.ARCCustomerContactId
end
GO
GRANT EXECUTE ON  [dbo].[GetARCCustomerContacts_XLedger] TO [MssExec]
GO