Summary:
This article troubleshoots an error that can occur when connecting to SAP B1 due to duplicate periodID using OSR Portal version 4x+.
Article Sections:
Symptoms:
When a user is connecting to SAP B1, they receive the following error message:
error neStop.Framework.Engine.Execution.PeriodTableInitializationException: Period ID values must be unique. --- System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
at System.Data.DataSet.EnableConstraints()
Causes:
This is caused by two or more transactions with duplicate periodID.
Resolution:
Run the following query in SQL server against the company database that is causing the error. Then scroll down to periodIDs and there should be duplicate periodIDs in that column. This column cannot have duplicate periodIDs.
Note: This should be done by the organization's SAP vendor.
Duplicate PeriodID Query:
SELECT [osr_a].[PeriodId] [Period.PeriodId],
[osr_a].[FinanceYear] [Period.FinanceYear],
[osr_a].[Name] [Period.Name],
[osr_a].[PeriodEnd] [Period.PeriodEnd],
[osr_a].[PeriodInFinanceYear] [Period.PeriodInFinanceYear],
[osr_a].[PeriodInYear] [Period.PeriodInYear],
[osr_a].[PeriodStart] [Period.PeriodStart],
[osr_a].[Year] [Period.Year]
FROM (
SELECT Datepart(yyyy,b.financyear)*100 + subnum AS periodid,
a.NAME,
a.f_refdate AS periodstart,
a.t_refdate AS periodend,
Datepart(yyyy,b.financyear) AS year,
Datepart(yyyy,b.financyear) AS financeyear,
Datepart(mm,a.f_refdate) AS periodinfinanceyear,
a.subnum AS periodinyear
FROM ofpr a
JOIN oacp b
ON b.periodcat = a.category
WHERE b.year IS NULL
OR b.year = 0
UNION
SELECT b.year*100 + subnum AS periodid,
a.NAME,
a.f_refdate AS periodstart,
a.t_refdate AS periodend,
b.year AS year,
Datepart(yyyy,b.financyear) AS financeyear,
Datepart(mm,a.f_refdate) AS periodinfinanceyear,
a.subnum AS periodinyear
FROM ofpr a
JOIN oacp b
ON b.periodcat = a.category
WHERE b.year IS NOT NULL
AND b.year
Properties:
Applies To:
OSR Portal 4.x+