Freight

(0) Share

Report

Posted on by Donald Leary

580

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

*This post is locked for comments

All responses (22)

Answers (6)

Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Freight

Don,

EXT00181 is an Extender table. Without looking at your Extender windows and data, there is no way to help with this, as the Extender setup will be specific to your implementation of GP.
Donald Leary 580 on at

Like (0)

Report

RE: Freight

Thanks again Victoria.

My boss had suggested using EXT00181... although i don't see any direct correlation between the GL or PM tables that relate to this. I notice the EXT00181.PT_UD_Key somewhat resembles the GL ORDOCNUM, but a close similarity is where it ends, never an exact match. I'll try what you suggested and let you know how i make out.

Thank you
Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Freight

Hi Don,

PO's are not actually financial transactions and never get 'posted', so you will not see them referenced in the GL by the PO number and there is really no definitive way to link the POP30100 table (or POP10100) to the GL without a number of steps in-between.

POP30310 is the receipt line detail table. There is not necessarily a direct link from one line in this table to a line in the GL. I believe you can link on GL20000.ORCTRNUM = POP30310.POPRCTNM, but you will have multiple results in both tables, ie, you could have 5 lines in the GL20000 resulting in 7 lines in the POP30310 table, so that might get a little messy. It might work though, depending on what you need.

Another option is to first link to the POP30300 table (receipt header) on:

GL20000.ORTRXTYP = POP30300.POPTYPE and GL20000.ORCTRNUM = POP30300.POPRCTNM

Then you can link from that to the POP30310 table (on POPRCTNM) to get the PO numbers.

Unless your company ALWAYS enters one receipt and invoice for a single PO, there may be multiple PO numbers for one GL entry. And unless you never have partial shipments, you might see multiple GL lines for the same PO.

Hope that helps.
Donald Leary 580 on at

Like (0)

Report

RE: Freight

Hello again Victoria,

I now need to join to the POP header POP30100. The problem is i'm not sure what to join on since the po number in the PM tables don't match the po numbers in the POP tables... Should i be joining POP30100 to the GL? I saw your "SQL view for Payables invoices originating from POP in Dynamics GP" but i'm not using any of those pop tables, just POP30100 and POP30310.

here is my query so far:

SELECT

LTRIM(RTRIM(gl.JRNENTRY)) 'JournalEntry',

CASE LTRIM(RTRIM(gl.SERIES))

WHEN 2 THEN 'Financial'

WHEN 3 THEN 'Sales'

WHEN 4 THEN 'Purchasing'

END SERIES,

gl.TRXDATE,

LTRIM(RTRIM(a.ACTNUMBR_1)) + '-' +

LTRIM(RTRIM(a.ACTNUMBR_2)) + '-' +

LTRIM(RTRIM(a.ACTNUMBR_3)) + '-' +

LTRIM(RTRIM(a.ACTNUMBR_4)) + '-' +

LTRIM(RTRIM(a.ACTNUMBR_5)) 'AccountNumber',

a.ACTDESCR 'AccountDescription',

gl.DEBITAMT,

gl.CRDTAMNT,

gl.REFRENCE,

gl.DSCRIPTN,

a.ACTNUMBR_2 'MainAccountSegment',

gl.ORMSTRID,

gl.ORMSTRNM,

gl.ORCTRNUM,

gl.DEBITAMT - gl.CRDTAMNT 'Amount',

MONTH(gl.JRNENTRY) 'Month',

CASE LTRIM(RTRIM(a.ACTNUMBR_2))

WHEN 5280 THEN 'FREIGHT-RETURNS'

WHEN 5225 THEN 'FREIGHT-INTERCOMPANY'

WHEN 5260 THEN 'FREIGHT-PURCHASES'

WHEN 5300 THEN 'FREIGHT-SALES'

END SegmentDesc,

LTRIM(RTRIM(a.ACTNUMBR_1)) 'Location',

LTRIM(RTRIM(a.ACTNUMBR_2)) 'MainSegment',

LTRIM(RTRIM(a.ACTNUMBR_3)) 'Department',

LTRIM(RTRIM(a.ACTNUMBR_4)) 'Sub',

LTRIM(RTRIM(a.ACTNUMBR_3)) + '-' +

LTRIM(RTRIM(a.ACTNUMBR_4)) + '-' +

LTRIM(RTRIM(a.ACTNUMBR_5)) 'Data1',

LTRIM(RTRIM(a.ACTNUMBR_4)) + '-' +

LTRIM(RTRIM(a.ACTNUMBR_5)) 'Data2',

LTRIM(RTRIM(a.ACTNUMBR_5)) 'Data3',

(

SELECT

ACCATDSC

FROM

RASGP.GPRAS.dbo.GL00102

WHERE

ACCATNUM = a.ACCATNUM

) 'P&L_Category', --may not need

a.ACTINDX,

hpt.DOCNUMBR 'APInvoice',

hpt.DOCAMNT 'InvoiceAmt',

hpt.PORDNMBR 'PONumber',

gl.ORMSTRNM 'Customer_Vendor',

gl.ORMSTRID 'VendorId'

FROM GL20000 gl --open yr posted transactions

INNER JOIN

GL00100 a --acct master

ON LTRIM(RTRIM(a.ACTINDX)) = LTRIM(RTRIM(gl.ACTINDX)) AND

gl.SERIES IN (2, 3, 4) AND-- finan, sales, purch

LTRIM(RTRIM(a.ACTNUMBR_2)) IN ('5260','5300','5280','5225') --show all freight accounts

INNER JOIN

(

SELECT

VENDORID,

VCHRNMBR,

DOCTYPE,

DOCDATE,

PSTGDATE,

DUEDATE,

DOCNUMBR,

DOCAMNT,

PORDNMBR,

CURTRXAM,

TRXDSCRN,

VOIDED

FROM

PM20000

UNION ALL

SELECT

VENDORID,

VCHRNMBR,

DOCTYPE,

DOCDATE,

PSTGDATE,

DUEDATE,

DOCNUMBR,

DOCAMNT,

PORDNMBR,

CURTRXAM,

TRXDSCRN,

VOIDED

FROM

PM30200) hpt --Historical/Paid Transactions

ON LTRIM(RTRIM(hpt.VCHRNMBR)) = LTRIM(RTRIM(gl.ORCTRNUM)) AND --from linda

gl.ORTRXTYP = hpt.DOCTYPE AND

hpt.DOCTYPE IN (1, 5) AND --invoice, credit memo

hpt.VOIDED = 0 --not voided
Donald Leary 580 on at

Like (0)

Report

RE: Freight

Thanks so much for the assistance setting up these tables and joins, it's greatly appreciated.

Don
Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Freight

Don,

No, this will not work if you have the same invoice number for 2 vendors, which can easily happen.

Link on GL20000.ORCTRNUM = PM30200.VCHRNMBR and GL20000.ORTRXTYP = PM30200.DOCTYPE
Donald Leary 580 on at

Like (0)

Report

RE: Freight

Thanks again Victoria.

So to join GL to PM i should join on GL20000.ORDOCNUM = PM30200.DOCNUMBR?
Victoria Yudin 22,768 on at

Like (0)

Report

RE: Freight

Don,

You can't join PM20000 and PM30200, you need to union them. Like in this example:

victoriayudin.com/.../sql-view-for-all-posted-payables-transactions-in-dynamics-gp

For the PO number - sometimes you have to research how the data is actually entered into GP to be able to add it to a report. There are many ways to accomplish similar things, so a lot of it may be specific to your company. People requesting a report may think it should be easy because they are working with these transaction every day, but if you're not familiar with their workflow, it can get very challenging to create reports.
Donald Leary 580 on at

Like (0)

Report

RE: Freight

Great,

Thank you for all your help. You've verified that i had the correct info. I'm assuming pieces and a few other fields are coming from an outside table that i can link on once i get the PO number. I've tried using PM30200.PORDNMBR for the Purchase Order Number (it's even documented as the PO) but there are only a handful of records that actually have a PO in that field (weather it's correct hasn't been verified yet). Is there a field i can join PM20000 and PM30200 on? After i obtain the correct PO, the skeleton query will be done and i can start the reports.

Thanks for all your great help!

Don
Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Freight

Donald,

I just noticed you had one more question - you would need to look at PM20000 and PM30200 (union the info from them).