OPRJ table in SAP Business One (Project Codes)
OPRJ is the SAP Business One table of project codes: one row per project with its code, name, validity dates and active flag. Journal entry lines (JDT1.Project) and document lines (INV1.Project) store the project code, so OPRJ is the master behind profit and cost reports per project.
OPRJ key fields
| Field | What it holds |
|---|---|
PrjCode | Project code, the key. JDT1.Project, INV1.Project and OINV.Project point to it. |
PrjName | Project name. |
ValidFrom | Valid from date. |
ValidTo | Valid to date. |
Active | Y = active, N = inactive. |
Locked | Y = locked, N = not locked. |
UserSign | User who created the project. Joins to OUSR.USERID. |
UserSign2 / UpdateDate | User and date of the last update. |
DataSource | How the row was created, for example O = DI API, S = Service Layer. |
How OPRJ relates to other tables
OPRJ is master data with no line table. The project code is stored on journal entry lines (JDT1.Project), on document lines (INV1.Project and the other line tables), on document headers (OINV.Project) and as a default on the business partner (OCRD.ProjectCod).
Common joins
JDT1 T0 INNER JOIN OPRJ T1 ON T1.PrjCode = T0.Project (project of the journal line)JDT1 T0 INNER JOIN OACT T2 ON T2.AcctCode = T0.Account (G/L account)INV1 T3 INNER JOIN OPRJ T1 ON T1.PrjCode = T3.Project (project of the invoice line)
When you use the OPRJ table
Use OPRJ for profit and loss per project from JDT1, revenue per project from invoice lines, and to find postings on projects that are inactive or past their ValidTo date.
Example query: debit, credit and balance per project this year
SQL Server
SELECT T1.PrjCode, T1.PrjName, SUM(T0.Debit) AS Debit, SUM(T0.Credit) AS Credit, SUM(T0.Debit - T0.Credit) AS Balance FROM JDT1 T0 INNER JOIN OPRJ T1 ON T1.PrjCode = T0.Project WHERE YEAR(T0.RefDate) = YEAR(GETDATE()) GROUP BY T1.PrjCode, T1.PrjName ORDER BY T1.PrjCode
SAP HANA (column names in double quotes)
SELECT T1."PrjCode", T1."PrjName", SUM(T0."Debit") AS "Debit", SUM(T0."Credit") AS "Credit", SUM(T0."Debit" - T0."Credit") AS "Balance" FROM JDT1 T0 INNER JOIN OPRJ T1 ON T1."PrjCode" = T0."Project" WHERE YEAR(T0."RefDate") = YEAR(CURRENT_TIMESTAMP) GROUP BY T1."PrjCode", T1."PrjName" ORDER BY T1."PrjCode"
Paste either version in Tools → Queries → Query Generator. Need the other dialect for your own query? Use the SQL to HANA converter.
Common pitfall: Project on the header but not on the lines
The header project of a document and the project on each line can differ, and the journal entry takes the project from the lines. A report on OINV.Project can disagree with a report on JDT1.Project. For financial figures per project, report on JDT1. See G/L account transactions query.
Ready-made queries that use OPRJ
- G/L account transactions: Journal lines for one G/L account in a period, with debit, credit and reference.
- G/L account balances: Current balance of every postable G/L account, grouped by account level.
Questions about the OPRJ table
Where is the project code stored on a journal entry?
On each line, in JDT1.Project, which joins to OPRJ.PrjCode.
Can a project code expire?
Yes. OPRJ.ValidFrom and ValidTo set the validity period and Active marks it active or inactive.
Is there a default project per business partner?
Yes, OCRD.ProjectCod points to OPRJ.
Related tables
JDT1 (Journal Entry Lines) · OJDT (Journal Entry (header)) · INV1 (A/R Invoice (lines)) · OOCR (Distribution Rules (cost centers)) · search all SAP Business One tables
Sources: SAP Business One documentation and SDK (DI API database tables reference), SAP Help Portal.
Ready to get started?
Tell us what you need. You get a clear technical answer, a fixed scope and a price, usually within 24 hours.