BizOneness: Vendor price lists in SAP Business one

Due to their structure, price lists can be tricky to extract from SAP Business One. One price list you might want to look at is the vendor price list.

Price lists are stored in the ITM1 table in SAP Business One. This subtable of the master inventory table OITM links back to OITM through ItemCode, so we'll join them like that.

I'll make a simple report listing the ItemCode, Name, PriceList and Price.

SELECT
   T0.ItemCode,
   T0.ItemName, 
   T1.PriceList, 
   T0.Price
FROM 
   OITM T0
   INNER JOIN ITM1 T1 ON T0.Itemcode = T1.ItemCode 
        

This gets you a lot of repeats and irrelevant subassemblies, fees, and finished goods. You are looking at all the price lists. You only want the ones for purchased goods and the price of the purchased goods.

For purchased goods, I'll use the OITM.PrchseItem flag to select them. You have two options: I may only want active goods with the isValid flag.

I'll add the WHERE clause

WHERE 
   T0.PrchseItem = 'Y'
   AND T0.isValid = 'Y'
        

For filtering to the vendor price only, most often, you'll filter for that price list. Commonly, you'll find a name like Item Cost or BasePrice. In SAP B1, go to Inventory>Price List>Price List in SAP to list all your price lists and identify the correct one. In my case, I have Base Price as a price list for vendors as price list 1. I'll use 1, and thus add to my WHERE clause

AND t1.PriceList = 1
        

This report lists all purchases. It might be useful to identify types of purchases such as raw materials, supplies, fees, labor, and purchased finished goods. The priceList column's brother flags invntItem and sellItem can help. I'll explain this in a future newsletter, but for now, here's a CASE expression to handle our situation.

     CASE 
      WHEN T0.PrchseItem = 'Y' AND T0.Sellitem = 'Y' AND T0.InvntItem = 'Y' THEN 'Purchased Finished Good'
      WHEN T0.PrchseItem = 'Y' AND T0.Sellitem = 'N' AND T0.InvntItem = 'Y' THEN 'Raw Material'
      WHEN T0.PrchseItem = 'Y' AND T0.Sellitem = 'Y' AND T0.InvntItem = 'N' THEN 'Fees Labor And Tasks'
      WHEN T0.PrchseItem = 'Y' AND T0.Sellitem = 'N' AND T0.InvntItem = 'N' THEN 'Supplies'
   END AS "Item Type",
        

I may want one more thing on this list -- who the preferred vendor is. For that, I added T0.CardCode to the report. For clarity, I'll add the vendor's name by joining OCRD and including OCRD.CardName. The full query would look like this:

SELECT
   T0.ItemCode,
   CASE 
      WHEN T0.PrchseItem = 'Y' AND T0.Sellitem = 'Y' AND T0.InvntItem = 'Y' THEN 'Purchased Finished Good'
      WHEN T0.PrchseItem = 'Y' AND T0.Sellitem = 'N' AND T0.InvntItem = 'Y' THEN 'Raw Material'
      WHEN T0.PrchseItem = 'Y' AND T0.Sellitem = 'Y' AND T0.InvntItem = 'N' THEN 'Fees Labor And Tasks'
      WHEN T0.PrchseItem = 'Y' AND T0.Sellitem = 'N' AND T0.InvntItem = 'N' THEN 'Supplies'
   END AS "Item Type",
   T0.CardCode,
   T2.CardName,
   T0.ItemName, 
   T1.PriceList, 
   T1.Price,
   T0.SellItem,
   T0.InvntItem
FROM 
   OITM T0
   INNER JOIN ITM1 T1 ON T0.Itemcode = T1.ItemCode 
   INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
WHERE 
   T0.PrchseItem = 'Y'
   AND T0.ValidFor = 'Y'
   AND T1.PriceList = 1
        

This query will get you all the information from inventory and a standard price list. There's a caution here: This way, we only get one vendor, the preferred vendor in SAP B1. We cannot track multiple vendors with these tables or be sure that the Item cost is what we paid the last time we bought it. There are some alternatives to this, which we'll discuss in upcoming newsletters.