/**
* This view gets vendors from XLedger.
* To get vendors from Microsoft GP, see the Vendor_Legacy view.
*/
create view [dbo].[Vendor_XLedger]
(
    [Name],
    VendorID,
    Contact,
    [Address1],
    [Address2],
    [Address3],
    City,
    [State],
    PostalCode,
    CountryName,
    [Status],
    Hold,
    VendorStatus,
    AccountingVendorId,
    AccountingVendorAddressId,
    VendorJoinField,
    Ten99Type,
    VendorClass
)
as
select
    Vendor.[Name],
    Vendor.VendorNumber as VendorID,
    Vendor.Contact,
    AccountingVendorAddress.Address1 as [Address1],
    AccountingVendorAddress.Address2 as [Address2],
    AccountingVendorAddress.Address3 as [Address3],
    AccountingVendorAddress.City,
    AccountingVendorAddress.[State],
    AccountingVendorAddress.PostalCode,
    CountryCodeStandard.CountryName,
    Vendor.[Status],
    convert( varchar(7), '' ) as Hold,
    convert( int, 1 ) as VendorStatus,
    Vendor.AccountingVendorId,
    AccountingVendorAddress.AccountingVendorAddressId,
    VendorJoinField = Vendor.AccountingVendorId,
    Vendor.Ten99Type,
    AccountingVendorClass.ClassCode as VendorClass
from AccountingVendor as Vendor
left outer join AccountingVendorAddressType on
    AccountingVendorAddressType.TypeName = dbo.GetDefaultAccountingVendorAddressTypeName()
left outer join AccountingVendorAddress on
    AccountingVendorAddress.AccountingVendorFid = Vendor.AccountingVendorId and
    AccountingVendorAddress.AccountingVendorAddressTypeFid = AccountingVendorAddressType.AccountingVendorAddressTypeId
left outer join AccountingVendorClass on
    AccountingVendorClass.AccountingVendorClassId = Vendor.AccountingVendorClassFid
left outer join CountryCodeStandard on
    CountryCodeStandard.CountryCodeStandardID = AccountingVendorAddress.CountryCodeStandardFID
GO
GRANT SELECT ON  [dbo].[Vendor_XLedger] TO [MssExec]
GRANT INSERT ON  [dbo].[Vendor_XLedger] TO [MssExec]
GRANT DELETE ON  [dbo].[Vendor_XLedger] TO [MssExec]
GRANT UPDATE ON  [dbo].[Vendor_XLedger] TO [MssExec]
GO