350
HI All,
I am trying to understand the table structure in dynamics GP. Can anybody suggest the link between tables aag30000 and aag00401.
I tried AAG30003 where aatrxcodeid is common as aatrxdimcodeid in AAG00401 and aaGLHdrID is common in aag30000 and aag30003
but the problem with that is its giving me duplicate results as it has same aaGLHdrID for the transactions I am looking for.
Please let me know if there is any other table linked to that.
Rahul
*This post is locked for comments
It's been a while since I had to deal with any Analytical Accounting so I am way out of practice on these tables. I actually don't have access to a system where they have AA Transactions anymore, so I don't think I can help out here.
Hi, <Posted by K Day replied on 08-27-2010 4:18 PM >
Thanks for sharing your queries, they are pretty helpful.
I modified your query (see code below) because single link to aaTRxDimCodeId was duplicating data. In my scenario there are records with the same aaTRxDimCodeId, so I add a link to field aaTrxDimId together with aaTrxDimCodeID. so far is not showing duplicates.
I have a question, how come some aaTRxDimCodeId are null? why they exist in AAGxxx.. Tables
SELECT TOP (100) PERCENT g.aaTrxDim, h.aaTrxDimCode, a.aaGLHdrID, a.JRNENTRY, a.RCTRXSEQ, a.YEAR1, a.aaTRXType, a.aaGLTRXSource,
a.aaTRXSource, a.GLPOSTDT, a.DEX_ROW_ID, d.ACTDESCR, e.ACTNUMST, 'AAG30001 -->' AS Expr1, b.aaGLHdrID AS Expr2, b.aaGLDistID,
b.INTERID, b.CorrespondingUnit, b.ACTINDX, b.ACCTTYPE, b.aaBrowseType, b.DECPLACS, b.DEBITAMT, b.CRDTAMNT, b.ORDBTAMT, b.ORCRDAMT,
b.CURNCYID, b.CURRNIDX, b.RATETPID, b.EXGTBLID, b.XCHGRATE, b.EXCHDATE, b.TIME1, b.RTCLCMTD, b.DENXRATE, b.MCTRXSTT, b.SEQNUMBR,
b.aaCustID, b.aaVendID, b.aaSiteID, b.aaItemID, b.aaCopyStatus, b.aaChangeDate, b.aaChangeTime, b.DEX_ROW_ID AS Expr3,
'AAG30002 -->' AS Expr4, c.aaGLHdrID AS Expr5, c.aaGLDistID AS Expr6, c.aaGLAssignID, c.DEBITAMT AS Expr7, c.CRDTAMNT AS Expr8,
c.ORDBTAMT AS Expr9, c.ORCRDAMT AS Expr10, c.aaAssignedPercent, c.DistRef, c.NOTEINDX, c.DEX_ROW_ID AS Expr11, f.aaGLHdrID AS Expr12,
f.aaGLDistID AS Expr13, f.aaGLAssignID AS Expr14, f.aaTrxDimID, f.aaTrxCodeID, f.DEX_ROW_ID AS Expr15, e.ACTNUMBR_3,
(SELECT TOP (1) ORDOCNUM
FROM dbo.GL20000
WHERE (JRNENTRY = a.JRNENTRY) AND (TRXSORCE = a.aaGLTRXSource)) AS DocRef,
(SELECT TOP (1) ORMSTRNM
FROM dbo.GL20000 AS GL20000_1
WHERE (JRNENTRY = a.JRNENTRY) AND (TRXSORCE = a.aaGLTRXSource)) AS Customer
FROM dbo.AAG00401 AS h RIGHT OUTER JOIN
dbo.AAG30003 AS f ON h.aaTrxDimID = f.aaTrxDimID AND h.aaTrxDimCodeID = f.aaTrxCodeID RIGHT OUTER JOIN
dbo.AAG30000 AS a LEFT OUTER JOIN
dbo.AAG30001 AS b ON a.aaGLHdrID = b.aaGLHdrID LEFT OUTER JOIN
dbo.AAG30002 AS c ON b.aaGLHdrID = c.aaGLHdrID AND b.aaGLDistID = c.aaGLDistID LEFT OUTER JOIN
dbo.GL00100 AS d ON d.ACTINDX = b.ACTINDX LEFT OUTER JOIN
dbo.GL00105 AS e ON e.ACTINDX = b.ACTINDX ON f.aaGLHdrID = c.aaGLHdrID AND f.aaGLDistID = c.aaGLDistID AND
f.aaGLAssignID = c.aaGLAssignID LEFT OUTER JOIN
dbo.AAG00400 AS g ON g.aaTrxDimID = f.aaTrxDimID
ORDER BY a.JRNENTRY
Thanks K day,
I am new to SQL and GP table so thanks for helping out.
Cheers
Rahul
I was messing around with Analytical Accounting once upon a time and I just looked and still have my initial select scripts trying to make sense out of the tables. Here's what I came up with, and this I believe only pertains to the GLtransaction, which flow from the Subledger. Hopefully it can give you a start. It might not be perfect, but its a start.
--This looks like Open Year Transactions
Select g.aaTRXDim, h.AATrxDimCode, a.*, d.ACTDESCR, e.ACTNUMST, 'AAG30001 -->', b.*, 'AAG30002 -->', c.*, f.*
FROM AAG30000 a
LEFT Join AAG30001 b on (a.aaGLHdrID = b.aaGLHdrID)
LEFT Join AAG30002 c on (b.aaGLHdrID = c.aaGLHdrID and b.aaGLDistID = c.aaGLDistID)
LEFT JOIN GL00100 d ON d.ACTINDX = b.ACTINDX
LEFT JOIN GL00105 e ON e.ACTINDX = b.ACTINDX
LEFT JOIN AAG30003 f ON (f.aaGLHdrID = c.aaGLHdrID and f.aaGLDistID = c.aaGLDistID and f.aaGLAssignID = c.aaGLAssignID)
LEFT JOIN AAG00400 g ON g.aaTrxDimID = f.aaTrxDimID
LEFT JOIN AAG00401 h ON h.aaTrxDimCodeID = f.aaTrxCodeID
WHERE g.aaTRXDim is NOT NULL
ORDER BY a.JRNENTRY
--This looks like Historical Year Transactions
Select g.aaTRXDim, h.AATrxDimCode, a.*, d.ACTDESCR, e.ACTNUMST, 'AAG40001 -->', b.*, 'AAG40002 -->', c.*, f.*
FROM AAG40000 a
LEFT Join AAG40001 b on (a.aaGLHdrID = b.aaGLHdrID)
LEFT Join AAG40002 c on (b.aaGLHdrID = c.aaGLHdrID and b.aaGLDistID = c.aaGLDistID)
LEFT JOIN GL00100 d ON d.ACTINDX = b.ACTINDX
LEFT JOIN GL00105 e ON e.ACTINDX = b.ACTINDX
LEFT JOIN AAG40003 f ON (f.aaGLHdrID = c.aaGLHdrID and f.aaGLDistID = c.aaGLDistID and f.aaGLAssignID = c.aaGLAssignID)
LEFT JOIN AAG00400 g ON g.aaTrxDimID = f.aaTrxDimID
LEFT JOIN AAG00401 h ON h.aaTrxDimCodeID = f.aaTrxCodeID
WHERE g.aaTRXDim is NOT NULL
ORDER BY a.JRNENTRY
The table aag00401 contains the dimension codes, Aag3xxxx contain the transaction information
AAG30000 can be used to link to the GL transaction using JE Number
AAG30001 can be used to link to the AAG30000 based AAhdrID.
AAG30002 can be used to link to the AAG30001 based AAhdrID and aadistID
AAG30003 can be used to link to the AAG30001 or AAG30002 based AAhdrID and aadistID
The table AAG30003 contains multiple rows for each value of AAhdrID and aadistID. The multiple rows represent the various dimensions. Each row contains the dimension ID and dimension codes.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (