I was looking into a problem reported by a client recently and needed to check the assignment of Analytical Accounting Transaction Dimension Codes to the AA GL transactions and so wrote the below SQL which I am posting to keep it easily accessible should I need it again.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
*
FROM
AAG30000 AS aaGLHdr
INNER JOIN
AAG30001 AS aaGLDist
ON
aaGLDist.aaGLHdrID = aaGLHdr.aaGLHdrID
INNER JOIN
AAG30002 AS GLAssign
ON
GLAssign.aaGLHdrID = aaGLDist.aaGLHdrID
AND
GLAssign.aaGLDistID = aaGLDist.aaGLDistID
INNER JOIN
AAG30003 AS aaGLCode
ON
aaGLCode.aaGLHdrID = GL Assign.aaGLHdrID
AND
aaGLCode.aaGLDistID = GLAssign.aaGLDistID
AND
aaGLCode.aaGLAssignID = GLAssign.aaGLAssignID
INNER JOIN
AAG00401 AS aaTrxDimCodeSetp
ON
aaTrxDimCodeSetp.aaTrxDimID = aaGLCode.aaTrxDimID
AND
aaTrxDimCodeSetp.aaTrxDimCodeID = aaGLCode.aaTrxCodeID