SAP Business One Vendor Lookup Query

Key Concepts

Vendors vs Customers in OCRD

OCRD stores all business partners — customers, vendors, and leads. Use CardType to filter: 'C' = Customer, 'S' = Supplier (vendor), 'L' = Lead. Same table structure, just different type.

Contact Information

Basic contact info lives on OCRD: Phone1, Phone2, Cellular, E_Mail. For multiple contacts (sales rep, accounts payable, etc.), join OCPR which has one row per contact person.

Active Vendors

validFor = 'Y' filters active vendors. frozenFor = 'N' means the vendor isn't frozen for transactions. Combine both for vendors you can currently do business with.

Vendor Code Patterns

Many companies prefix vendor codes with 'V' or 'S' (V10000, S-001). Customer codes often start with 'C'. This is just convention — SAP doesn't enforce it, but it makes CardType filtering less critical for searches.

Available Fields Reference

Find Vendor by Name

Search vendors using partial name match. We filter CardType = 'S' for suppliers only. Results include primary contact info from the vendor master.

SELECT 
    CardCode,
    CardName,
    Phone1,
    E_Mail,
    Address,
    City,
    Country
FROM OCRD
WHERE CardType = 'S'
  AND validFor = 'Y'
  AND (CardName LIKE '%supply%' OR CardCode LIKE '%supply%')
ORDER BY CardName

Find by Vendor Code

Exact match lookup with full contact details. Use this when you know the vendor code. Includes payment terms and currency.

SELECT 
    CardCode,
    CardName,
    Phone1,
    Phone2,
    Cellular,
    Fax,
    E_Mail,
    Address,
    City,
    ZipCode,
    Country,
    Currency
FROM OCRD
WHERE CardCode = 'V10000'

Vendor with All Contacts

Join OCPR to get all contact persons for a vendor. Each vendor can have multiple contacts (sales, accounts, shipping). Filter Active = 'Y' on OCPR for current contacts only.

SELECT 
    T0.CardCode,
    T0.CardName,
    T1.Name AS ContactName,
    T1.Position,
    T1.Tel1 AS Phone,
    T1.Cellolar AS Mobile,
    T1.E_MailL AS Email
FROM OCRD T0
LEFT JOIN OCPR T1 ON T0.CardCode = T1.CardCode AND T1.Active = 'Y'
WHERE T0.CardType = 'S'
  AND T0.CardCode = 'V10000'
ORDER BY T1.Name

Note: The column is spelled Cellolar and E_MailL (with extra L) in OCPR — these are SAP's actual field names.

Search All Active Vendors

List all active vendors with their primary contact. Uses LEFT JOIN so vendors without contacts still appear. Filter frozenFor = 'N' to exclude frozen vendors.

SELECT 
    T0.CardCode,
    T0.CardName,
    T0.Phone1,
    T0.E_Mail,
    T0.City,
    T0.Country,
    (SELECT TOP 1 Name FROM OCPR WHERE CardCode = T0.CardCode AND Active = 'Y') AS PrimaryContact
FROM OCRD T0
WHERE T0.CardType = 'S'
  AND T0.validFor = 'Y'
  AND T0.frozenFor = 'N'
ORDER BY T0.CardName