OUSR table in SAP Business One (Users)

OUSR key fields

How OUSR relates to other tables

OUSR is referenced from almost every table: UserSign (created by) and UserSign2 (updated by) on document headers, master data and history tables join to OUSR.USERID. OHEM.userId links an employee record to a user. The password columns in OUSR hold credentials, so leave them out of every report and export.

Common joins

• OINV T0 INNER JOIN OUSR T1 ON T1.USERID = T0.UserSign (user who created the invoice)
• OINV T0 LEFT JOIN OUSR T2 ON T2.USERID = T0.UserSign2 (user who last updated it)
• ADOC T3 INNER JOIN OUSR T1 ON T1.USERID = T3.UserSign2 (user per history version)
• OHEM T4 LEFT JOIN OUSR T1 ON T1.USERID = T4.userId (user of an employee)

When you use the OUSR table

Use OUSR to show who created or changed documents, to list active, locked and superuser accounts for an access review, and to count documents per user.

Example query: invoices created per user this month

SQL Server

SELECT T1.USER_CODE, T1.U_NAME, COUNT(*) AS Invoices, SUM(T0.DocTotal) AS Total
FROM OINV T0
INNER JOIN OUSR T1 ON T1.USERID = T0.UserSign
WHERE T0.DocDate >= DATEADD(day, 1 - DAY(GETDATE()), CAST(GETDATE() AS date))
GROUP BY T1.USER_CODE, T1.U_NAME
ORDER BY Invoices DESC

SAP HANA (column names in double quotes)

SELECT T1."USER_CODE", T1."U_NAME", COUNT(*) AS "Invoices", SUM(T0."DocTotal") AS "Total"
FROM OINV T0
INNER JOIN OUSR T1 ON T1."USERID" = T0."UserSign"
WHERE T0."DocDate" >= ADD_DAYS(CAST(CURRENT_TIMESTAMP AS date), 1 - DAYOFMONTH(CURRENT_TIMESTAMP))
GROUP BY T1."USER_CODE", T1."U_NAME"
ORDER BY "Invoices" DESC

Paste either version in Tools → Queries → Query Generator. Need the other dialect for your own query? Use the SQL to HANA converter.

Common pitfall: Joining UserSign to USER_CODE

UserSign and UserSign2 are numbers that match OUSR.USERID, not the login code in USER_CODE. A join on USER_CODE returns nothing or the wrong user. Also select named columns: SELECT * on OUSR pulls the password columns into your result. See Documents created by user query.

Ready-made queries that use OUSR

• Invoices created per user today: How many A/R invoices each SAP B1 user created today, with the total value.
• Documents waiting for approval: Approval requests still pending, with the document type, originator and age in days.

Questions about the OUSR table

Which OUSR column does UserSign point to?

OUSR.USERID, the internal user number. USER_CODE is the login code.

How do I find superusers in SAP Business One?

Select the users with OUSR.SUPERUSER = 'Y', and check Locked to see whether the account is still in use.

What does GROUPS = 99 mean in OUSR?

The SDK lists 99 as deleted and 0 as regular.

Related tables

ADOC (Document History (invoice history)) · OHEM (Employees (employee master data)) · OINV (A/R Invoice (header)) · search all SAP Business One tables

Sources: SAP Business One documentation and SDK (DI API database tables reference), SAP Help Portal.