Found a solution. The key referenced in the datamart.PeriodsByDate is the OrganizationID (sequence of company added to MR DataMart & not the same as CompanyId in GP) & the starting date of fiscal period.
You can see which GP company the organizationID pertains to from
Select * from Datamart.Organization
Now that we have the company identified, there are 2 issues that can likely lead to this error, both are within the SY40100 table. There can be 2 fiscal periods with the same starting fiscal date OR there can be fiscal periods with incorrect ending dates (only seen in SQL and not within GP).
To check duplicate starting fiscal dates.
Select '{Co}' as Company, PERIODID, YEAR1 from {Co}.dbo.SY40100
where FORIGIN in (0,1) and ODESCTN in ('', 'General Entry')
group by YEAR1, PERIODID
having COUNT(PERIODID)<>2
order by Company, YEAR1,PERIODID
To check for incorrect ending dates.
Select Perioddt as PDT, Perdendt as PEDT, DATEDIFF(day,Perioddt,Perdendt) as 'Days', year1, periodid, *
from {Co}.dbo.SY40100 where --YEAR1=2008 and
series=0 and pername <> 'Beginning Balance'
and DATEDIFF(day,Perioddt,Perdendt) > 31
order by perioddt
For either one, the solution to fix is to re-calculate the fiscal years from within GP & then run the Reconcile utility under Financial>>Utilities>>Reconcile.
From here, if the DataMart is still trying to sync the company & you're still receiving the original error, you can delete the incorrect periods from staging table that leads to the DataMart.periodsbydate table.
Here's the script to clear the instance where the original fiscal ending dates were incorrect.
select datediff(day,starts,ends) as 'days',* from datamart.periodstaging
where datediff(day,starts,ends) > 31
delete from datamart.periodstaging where datediff(day,starts,ends) > 31
Hope this helps!
