Hello,
MR is more restrictive than FRx and we expect this to change in the future. The one problem is MR looks for matches in the CURRENCYID between the GL tables. If you run the following scripts and get results with any of them there are mismatches that need to be updated.
/*
Finds records in the Open AA tables where the Currency ID does not match the Open GL tables.
*/
SELECT a.CURNCYID, *
FROM AAG30001 a
JOIN AAG30000 b
ON a.aaGLHdrID = b.aaGLHdrID
JOIN GL20000 c
ON b.JRNENTRY = c.JRNENTRY
AND b.GLPOSTDT = c.TRXDATE
AND b.aaGLTRXSource = c.TRXSORCE
AND a.ACTINDX = c.ACTINDX
AND a.CURNCYID <> c.CURNCYID
/*
Finds records in the Open GL tables where the Currency ID does not match the Open AA tables.
Data found should be the same as the script above.
*/
SELECT c.CURNCYID, *
FROM GL20000 c
JOIN AAG30000 b
ON b.JRNENTRY = c.JRNENTRY
AND b.GLPOSTDT = c.TRXDATE
AND b.aaGLTRXSource = c.TRXSORCE
JOIN AAG30001 a
ON a.aaGLHdrID = b.aaGLHdrID
AND a.ACTINDX = c.ACTINDX
AND a.CURNCYID <> c.CURNCYID
/*
Finds records in the History AA tables where the Currency ID does not match the History GL tables.
*/
SELECT a.CURNCYID, *
FROM AAG40001 a
JOIN AAG40000 b
ON a.aaGLHdrID = b.aaGLHdrID
JOIN GL30000 c
ON b.JRNENTRY = c.JRNENTRY
AND b.GLPOSTDT = c.TRXDATE
AND b.aaGLTRXSource = c.TRXSORCE
AND a.ACTINDX = c.ACTINDX
AND a.CURNCYID <> c.CURNCYID
/*
Finds records in the History GL tables where the Currency ID does not match the History AA tables.
Data found should be the same as the script above.
*/
SELECT c.CURNCYID, *
FROM GL30000 c
JOIN AAG40000 b
ON b.JRNENTRY = c.JRNENTRY
AND b.GLPOSTDT = c.TRXDATE
AND b.aaGLTRXSource = c.TRXSORCE
JOIN AAG40001 a
ON a.aaGLHdrID = b.aaGLHdrID
AND a.ACTINDX = c.ACTINDX
AND a.CURNCYID <> c.CURNCYID
If you see differences the following will update your AAG tables to match the GL tables.
/*
Updates records in the Open AA tables
Sets the Currency ID and Currency Index to match the GL Open tables
*/
UPDATE a
SET a.CURNCYID = c.CURNCYID,
a.CURRNIDX = c.CURRNIDX
FROM AAG30001 a
JOIN AAG30000 b
ON a.aaGLHdrID = b.aaGLHdrID
JOIN GL20000 c
ON b.JRNENTRY = c.JRNENTRY
AND b.GLPOSTDT = c.TRXDATE
AND b.aaGLTRXSource = c.TRXSORCE
/*
Updates records in the History AA tables
Sets the Currency ID and Currency Index to match the GL History tables
*/
UPDATE a
SET a.CURNCYID = c.CURNCYID,
a.CURRNIDX = c.CURRNIDX
FROM AAG40001 a
JOIN AAG40000 b
ON a.aaGLHdrID = b.aaGLHdrID
JOIN GL30000 c
ON b.JRNENTRY = c.JRNENTRY
AND b.GLPOSTDT = c.TRXDATE
AND b.aaGLTRXSource = c.TRXSORCE
