I have a task that involves obtaining freight information from Dynamics GP tables directly. I’m having some issues finding documentation on what I need… Below I have a few joins with the GL and POP tables. The first report is “Freight overview by type”, I’m having an issue finding how to distinguish between Intercompany, Purchases, returns and sales (must be a bit column). Below is what the report should look like (excuse the bad formatting), and my SQL so far… What am I missing in my join or what fields should I be referencing?
FREIGHT OVERVIEW BY TYPE
Sum of AMOUNT MONTH
SEGMENT DESC 2013_JAN 2013_FEB 2013_MAR 2013_APR Grand Total
_________________________________________________________________
FREIGHT-INTERCOMPANY 32,406 23,394 23,550 24,200 103,550
FREIGHT-PURCHASES 110,492 108,009 101,082 93,796 413,379
FREIGHT-RETURNS 304 1,415 6,675 1,248 9,642
FREIGHT-SALES 71,870 86,254 71,763 71,624 301,511
________________________________________________________________
Grand Total 215,072 219,072 203,069 190,867 828,081
My SQL:
SELECT distinct
glam.ACTDESCR
FROM
GL00105 aim--act indx mastr
INNER JOIN
GL00100 glam --gl acct mstr
ON glam.ACTINDX = aim.ACTINDX
INNER JOIN
PM30600 glDist --DIST HIST
ON glDist.DSTINDX = glam.ACTINDX
INNER JOIN
PM30200 pth --PAID TRAN HIST
ON pth.VCHRNMBR = glDist.VCHRNMBR
INNER JOIN
POP30100 poh --po hist
ON poh.PONUMBER = pth.PORDNMBR
INNER JOIN
POP30310 rlh --Receipt Line Hist
ON rlh.PONUMBER = poh.PONUMBER
WHERE
glam.ACTDESCR like '%freight%'
i was looking in GL00100 .ACTDESCR for the freight type... but it doesn't look like this method will work
