Start a project

Batches expiring in the next 60 days

Batch numbers with stock that expire within 60 days, per warehouse.

Tables: OBTN, OBTQUse: Food, pharma and chemical stock control.
SELECT T0.ItemCode, T0.DistNumber AS BatchNumber, T0.ExpDate, T1.WhsCode, T1.Quantity,
  DATEDIFF(day, GETDATE(), T0.ExpDate) AS DaysLeft
FROM OBTN T0
INNER JOIN OBTQ T1 ON T0.AbsEntry = T1.MdAbsEntry
WHERE T1.Quantity > 0 AND T0.ExpDate IS NOT NULL
  AND T0.ExpDate <= DATEADD(day, 60, GETDATE())
ORDER BY T0.ExpDate
Need your UDFs in this report, or a dashboard? We extend it for your company.Customize it →
Inventory

More inventory queries

Stock by warehouseOn hand, committed, ordered and available quantity per item and warehouse.Items below minimum stockInventory items whose on-hand quantity is below the minimum level set on the item.Inventory value by warehouseStock quantity multiplied by the item average cost, totalled per warehouse.Slow-moving items (no sales in 90 days)Items with stock on hand that have not been invoiced in the last 90 days.
How it works

Three steps, no signup

1

Copy the right version

Use the SQL Server tab for MS SQL databases and the HANA tab for SAP B1 on HANA.

2

Open Query Generator

In SAP Business One go to Tools → Queries → Query Generator, paste and click Execute.

3

Save and share

Click Save, pick a category and give the query a name. Your team can run it from Query Manager.

FAQ

Questions about this tool

How do I run the batches expiring in the next 60 days query in SAP Business One?

Copy the version that matches your database, open Tools → Queries → Query Generator, paste it in the SQL box and click Execute. Save it to reuse it later.

Which tables does this query use?

It reads OBTN, OBTQ from the standard SAP Business One schema. No user-defined fields are needed.

Can this be turned into a dashboard or alert?

Yes. We turn queries into Crystal reports, Excel reports that refresh themselves, SAP B1 alerts or WhatsApp messages for managers.

More free tools

Built for SAP Business One teams

SQL to HANA query converterPaste a SQL Server query and get the SAP HANA version, with every change explained.SAP B1 table finderSearch any table: what it stores, its line tables and its key fields.ZATCA QR code decoderRead a Saudi e-invoice QR (TLV Base64) or generate a Phase 1 QR from invoice values.SRI clave de accesoValidate or generate the 49-digit access key of Ecuador electronic invoices.

Need this inside SAP Business One?

We build add-ons, reports and integrations for SAP B1 on SQL Server and HANA. Tell us what you need and get a fixed price, usually within 24 hours.

Start a project Chat on WhatsApp