We have SOOOOO many clients that get this error. As best I can tell, it happens under two circumstances.
The first is when an un-posted transaction exists in the system on one night, then gets posted the next night and shows in both tables when you try to load the warehouse – that’s a theory, mind you, that I have yet to fully test. However, in most circumstances, you can fix the problem with the below script:
Use DynamicsGPWarehouse
go
update
LastUpdated
set
[DateUpdated]=’01/01/1900′,LastRow=0,TempLastRow=0
where
TableName = ‘TotalRevenue’
go
truncate table TotalRevenue
go
If you want to limit this to only a single company in the data warehouse, use the following:
Use DynamicsGPWarehouse
go
update
LastUpdated
set
[DateUpdated]=’01/01/1900′,LastRow=0,TempLastRow=0
where
TableName = ‘TotalRevenue’ and CompanyID = ‘COMP’ –Replace COMP with your company ID
go
delete TotalRevenue where CompanyID = ‘COMP’ –Replace COMP with your company ID
go
However, there are some occasions when the above doesn’t work. That happens when you have a duplicate record in RM10101. This table is the Work and Open Distribution table for the RM records and, if something goes wrong in posting, you may end up with a two sets of records for the same document – one posted and one unposted. To diagnose, run this:
select RMDTYPAL, DOCNUMBR, SEQNUMBR from RM10101
group by RMDTYPAL, DOCNUMBR, SEQNUMBR
having count(*) > 1
The above will show you all documents where you have more than one distribution of the same type or status. Take a look at the PSTGSTUS field – this is posting status. Most likely, you’ll see identical records, one with a PSTGSTUS value of 1 (posted) and one with a value of 3 (unposted). If so, you must solve the issue that caused this problem before reloading the warehouse. Once you have solved it, use the first set of scripts to clean up TotalRevenue and then rerun the Agent job to load the warehouse.
Dwight
