SAP Business One Resources: Configure Your Plant Capacity and Audit the Load with SQL

A client showed me a closed production order with exactly the cost he expected and, two screens further on, a plant three weeks behind. Both were true at the same time. The SAP Business One Resources module existed in his system, it was activated, and it did not have a single record loaded: the bill of materials only contained components, so the cost added up because nobody had told the ERP that machines and people cost money too.

That job, loading resources, looks administrative. It is not. It is the difference between an ERP that tells you how much material you consumed and one that tells you whether your plant can handle the order you just accepted.

Why SAP Business One Resources changes the cost conversation

Up to version 9.0, SAP Business One handled production with items and nothing else. If you wanted to reflect machine hours, the trick was to create a non-inventory item called «LATHE HOUR» and put it in the bill of materials. It worked, more or less, but it dirtied the item master and did not let you talk about capacity. From 9.1 the Resource object appeared as an entity of its own, and in 9.2 SAP also exposed it to the DI API for integrations and mass loading (SAP documentation on Resource Master Data).

The practical difference is this. An item has stock; a resource has capacity, which is a quantity available per day and which runs out. So when you load resources properly, the system stops answering only «do I have material?» and starts answering «do I have hours?». What is more, the order costing incorporates the value of those hours, so the finished product's margin stops being an optimistic fiction.

For a manager, the translation is direct: without resources loaded, MRP plans purchases but does not plan the plant. It is a traffic light with only a green light.

Prerequisites before touching SAP Business One Resources

Before creating the first record, it pays to have the following settled:

• SAP Business One 9.1 or higher (SQL Server or SAP HANA).
• Production module active and at least one Production-type bill of materials working.
• User authorization for Production → Resource Master Data.
• Cost definition groups configured in Administration → Setup → Production → Resource Cost Definition Groups.
• A criterion agreed with Operations on what counts as a resource: by individual machine, by work cell or by cost center.
• Access to the Query Manager to run the audit queries at the end of this article.

That last point on the list is the one that saves the most arguments later. Defining the level of granularity before loading avoids having to delete resources that are already tied to bills of materials.

Creating a resource in SAP Business One Resources: step by step

The whole process takes less than ten minutes per resource once you have the criterion clear. Below, the exact sequence.

1. Open the resource master. Go Production → Resource Master Data and click the new record icon.
2. Define code and name. Use a naming convention that survives growth: TOR-01, TOR-02, ENV-LIN1. Avoid names like «Old lathe».
3. Select the resource type. The options are Machine, Labor and Other. The type does not change the calculation, but it does let you group and filter later in the reports.
4. Assign the unit of measure. For machines and people it is almost always the hour. Pick one and do not mix it across resources of the same type.
5. Configure the cost definition group. Here you set the rate per unit of time and the G/L account where consumption will be posted. Without this, the resource is consumed but does not cost anything.

Planning, capacity and a real test

So far you have correct master data. Next comes the part that actually models the plant.

6. Complete the planning data tab. This is the one most people skip. Up to three multiplier factors are loaded: hours per shift, shifts per day and number of units of the resource. A lathe running 8 hours, on two shifts, with three identical machines, is loaded as 8 × 2 × 3.
7. Mark which factors are single-execution. In the previous example, factors 1 and 2 are; factor 3 is not, because an order is produced on one machine at a time. This detail sets the ceiling of hours the system will accept for a single order.
8. Assign the warehouse or warehouses. A resource is linked to the physical location where it operates.
9. Generate the daily capacity. Go Production → Capacity Data, select the resource and the date range, and let the system propagate the calculated capacity. Planned downtime is loaded here too: maintenance, holidays, collective vacations.
10. Add the resource to the bill of materials. Open the production bill of materials, add a line, change the line type to Resource and define the number of hours per unit produced.
11. Test with a real production order. Create the order, release it and issue the consumption. Check that the journal entry hit the cost group's account.

Daily capacity in SAP Business One Resources: the step almost everyone skips

The planning data tab does not create capacity on its own. It only stores the factors that are later used as a proposal when you generate the actual capacity data. It is a distinction that bothers me about how SAP designed this screen, because the name invites you to think filling it in is enough, and it is not.

If you do not generate the capacity data, the resource is still consumed in the orders and the cost is still calculated. However, the system will never warn you that you are overloading the lathe, because it has nothing to compare against. This has cost me dearly more than once: implementation delivered, client satisfied, and six months later planning was still being done in a spreadsheet because the ERP never showed a capacity alert. In 9.3, if memory serves, capacity generation was still a manual process by date range; it is worth scheduling it as a quarterly task and not trusting that someone will remember.

SQL queries to audit SAP Business One Resources

A technical detail worth its weight in gold: internally SAP stores resource lines in the same tables as item lines. What tells them apart is the field ItemType, which is 4 for items and 290 for resources. So any production query that does not filter on that field will be mixing kilos with hours.

Query 1: inventory of SAP Business One Resources and their actual use

This query answers an uncomfortable question: how many of the resources you loaded are actually used? Resources with zeros in both columns are dead master records.

SQL Server

-- Resources loaded vs. resources actually used
SELECT
    T0.ResCode AS [Code],
    T0.ResName AS [Resource],
    CASE T0.ResType
        WHEN 'M' THEN 'Machine'
        WHEN 'L' THEN 'Labor'
        ELSE 'Other'
    END AS [Type],
    (SELECT COUNT(*) FROM ITT1 I
      WHERE I.Code = T0.ResCode AND I.ItemType = 290) AS [In bills of materials],
    (SELECT COUNT(*) FROM WOR1 W
      WHERE W.ItemCode = T0.ResCode AND W.ItemType = 290) AS [In production orders]
FROM ORSC T0
ORDER BY 5 DESC, 4 DESC, T0.ResCode;

SAP HANA

-- Resources set up vs. resources actually used
SELECT
    T0."ResCode" AS "Code",
    T0."ResName" AS "Resource",
    CASE T0."ResType"
        WHEN 'M' THEN 'Machine'
        WHEN 'L' THEN 'Labor'
        ELSE 'Other'
    END AS "Type",
    (SELECT COUNT(*) FROM ITT1 I
      WHERE I."Code" = T0."ResCode" AND I."ItemType" = 290) AS "In bills of materials",
    (SELECT COUNT(*) FROM WOR1 W
      WHERE W."ItemCode" = T0."ResCode" AND W."ItemType" = 290) AS "In production orders"
FROM ORSC T0
ORDER BY 5 DESC, 4 DESC, T0."ResCode";

Query 2: monthly load by resource

This is where the bottleneck shows up. Grouping hours by resource and month makes the resource that concentrates the load visible without needing a scheduling add-on.

SQL Server

-- Load by resource and month (last 12 months)
SELECT
    T2.ResName AS [Resource],
    YEAR(T0.DueDate)  AS [Year],
    MONTH(T0.DueDate) AS [Month],
    SUM(T1.PlannedQty) AS [Planned hours],
    SUM(T1.IssuedQty)  AS [Consumed hours],
    SUM(T1.PlannedQty) - SUM(T1.IssuedQty) AS [Pending]
FROM OWOR T0
INNER JOIN WOR1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN ORSC T2 ON T2.ResCode  = T1.ItemCode
WHERE T1.ItemType = 290
  AND T0.Status IN ('R','L')
  AND T0.DueDate >= DATEADD(MONTH, -12, GETDATE())
GROUP BY T2.ResName, YEAR(T0.DueDate), MONTH(T0.DueDate)
ORDER BY 2 DESC, 3 DESC, 4 DESC;

SAP HANA

-- Load by resource and month (last 12 months)
SELECT
    T2."ResName" AS "Resource",
    YEAR(T0."DueDate")  AS "Year",
    MONTH(T0."DueDate") AS "Month",
    SUM(T1."PlannedQty") AS "Planned hours",
    SUM(T1."IssuedQty")  AS "Consumed hours",
    SUM(T1."PlannedQty") - SUM(T1."IssuedQty") AS "Pending"
FROM OWOR T0
INNER JOIN WOR1 T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN ORSC T2 ON T2."ResCode"  = T1."ItemCode"
WHERE T1."ItemType" = 290
  AND T0."Status" IN ('R','L')
  AND T0."DueDate" >= ADD_MONTHS(CURRENT_DATE, -12)
GROUP BY T2."ResName", YEAR(T0."DueDate"), MONTH(T0."DueDate")
ORDER BY 2 DESC, 3 DESC, 4 DESC;

Query 3: variance between planned hours and actual hours

This is the query Management Control cares about. A resource with a systematic 30% variance does not have an operations problem, it has a badly loaded standard in the bill of materials.

SQL Server

-- Plan vs. actual variance by resource, orders closed in the last 6 months
SELECT
    T2.ResCode AS [Code],
    T2.ResName AS [Resource],
    COUNT(DISTINCT T0.DocEntry) AS [Closed orders],
    SUM(T1.PlannedQty) AS [Planned hours],
    SUM(T1.IssuedQty)  AS [Actual hours],
    CAST(100.0 * (SUM(T1.IssuedQty) - SUM(T1.PlannedQty))
         / NULLIF(SUM(T1.PlannedQty), 0) AS DECIMAL(10,1)) AS [Variance %]
FROM OWOR T0
INNER JOIN WOR1 T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN ORSC T2 ON T2.ResCode  = T1.ItemCode
WHERE T1.ItemType = 290
  AND T0.Status = 'L'
  AND T0.CloseDate >= DATEADD(MONTH, -6, GETDATE())
GROUP BY T2.ResCode, T2.ResName
HAVING SUM(T1.PlannedQty) > 0
ORDER BY 6 DESC;

SAP HANA

-- Plan vs. actual variance by resource, orders closed in the last 6 months
SELECT
    T2."ResCode" AS "Code",
    T2."ResName" AS "Resource",
    COUNT(DISTINCT T0."DocEntry") AS "Closed orders",
    SUM(T1."PlannedQty") AS "Planned hours",
    SUM(T1."IssuedQty")  AS "Actual hours",
    ROUND(100.0 * (SUM(T1."IssuedQty") - SUM(T1."PlannedQty"))
         / NULLIF(SUM(T1."PlannedQty"), 0), 1) AS "Variance %"
FROM OWOR T0
INNER JOIN WOR1 T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN ORSC T2 ON T2."ResCode"  = T1."ItemCode"
WHERE T1."ItemType" = 290
  AND T0."Status" = 'L'
  AND T0."CloseDate" >= ADD_MONTHS(CURRENT_DATE, -6)
GROUP BY T2."ResCode", T2."ResName"
HAVING SUM(T1."PlannedQty") > 0
ORDER BY 6 DESC;

Query 4: bills of materials that cost blind

Returns the products whose production bill of materials has no resource line at all. That is, products whose standard cost only accounts for material. In most of the companies I review, this list is long.

SQL Server

-- Production bills of materials with no resource lines
SELECT
    T0.Code     AS [Product],
    T1.ItemName AS [Description],
    T0.Qauntity AS [Base quantity],
    (SELECT COUNT(*) FROM ITT1 I WHERE I.Father = T0.Code) AS [Total lines]
FROM OITT T0
INNER JOIN OITM T1 ON T1.ItemCode = T0.Code
WHERE T0.TreeType = 'P'
  AND NOT EXISTS (
      SELECT 1 FROM ITT1 I
      WHERE I.Father = T0.Code AND I.ItemType = 290)
ORDER BY 4 DESC, T0.Code;

SAP HANA

-- Production bills of materials with no resource lines
SELECT
    T0."Code"     AS "Product",
    T1."ItemName" AS "Description",
    T0."Qauntity" AS "Base quantity",
    (SELECT COUNT(*) FROM ITT1 I WHERE I."Father" = T0."Code") AS "Total lines"
FROM OITT T0
INNER JOIN OITM T1 ON T1."ItemCode" = T0."Code"
WHERE T0."TreeType" = 'P'
  AND NOT EXISTS (
      SELECT 1 FROM ITT1 I
      WHERE I."Father" = T0."Code" AND I."ItemType" = 290)
ORDER BY 4 DESC, T0."Code";

A note on the field name: Qauntity in the OITT table is in fact misspelled in SAP’s schema. It is not a typo in this article; it is an SAP error that has been sitting there for over a decade and that you have to respect exactly as is when writing the query.

Variations and use cases

The four queries above are the foundation. Building on them, these variants cover most of the questions that come down from management:

• Effective rate per resource. Cross the hours consumed with the amount charged to the cost group account for the month. If the effective rate drifts far from the one loaded in the system, the standard is obsolete.
• Load by warehouse or by plant. Add the order warehouse to the GROUP BY in Query 2 and you get the load balance across branches.
• Overload alert. Turn Query 2 into an internal alert that fires when a resource’s planned hours exceed a monthly threshold. That way planning stops depending on someone remembering to check the report.
• Labor resources by shift. If you model crews as Labor-type resources, Query 3 becomes a productivity indicator per crew.
• Order acceptance simulation. Before committing to a date, compare the pending hours from Query 2 against the capacity generated for the month. It is a thirty-second answer to a question that today takes two days.

Best practices and common mistakes with SAP Business One Resources

The short list of things that break, in order of frequency. First, creating one resource per machine when the operation is planned by cell: it multiplies the maintenance without adding information. Second, forgetting to generate the capacity data; the system does not complain and the problem surfaces months later. Third, loading rates from three years ago and never reviewing them, which turns costing into a tidy fiction.

There is a subtler and more expensive mistake. When you set the individual execution factors wrong, the system happily assigns the capacity of three machines to a single order, so the plan is technically valid but physically impossible, and nobody catches it until the order has been open for two weeks and the plant manager explains, quite rightly, that those hours never existed.

As a practice, I suggest running Query 1 once a quarter. Orphan resources pile up fast, especially after a plant reorganization. It is also worth restricting resource creation to a single user profile.

Official SAP documentation

• SAP Learning: Working with Resources in SAP Business One
• SAP Help Portal: SAP Business One documentation
• SAP Blogs: Resource Master Data exposed in release 9.2

Conclusion: the capacity you do not measure is capacity you do not have

Configuring SAP Business One Resources is not a project. It is a few hours of master data work plus one upfront decision about granularity. The effect on the management conversation, however, is out of all proportion: product cost starts to include time, planning stops living in a parallel spreadsheet, and delivery dates promised to the customer stop being an act of faith.

Run Query 1 against your database today. If the last column is full of zeros, you already know where to start. And if it comes back empty, the conversation is a different one, and probably more urgent.

This tutorial is part of the practical library of Strategic Circle. If this kind of material saves you time, share the article with whoever runs production at your company.