Landed Cost: Microsoft Great Plains Dynamics GP – Alba Spectrum Chicago
Alba Spectrum Group, http://www.albaspectrum.com help@albaspectrum.com 1-866-528-0577
This article is not intended to be distributed in open press, we at Alba Spectrum have copyrights and do not allow anyone to republish it outside of www.albaspectrum.com
Microsoft Great Plains tracks landed cost together with POP transactions: purchase receipts. The table name is this: POP30700 and if you need to link it to your purchase receipt lines use this link:
select
b.DEX_ROW_ID, b.ITEMNMBR, b.DATERECD, b.RCPTNMBR,
b.QTYONHND, b.ADJUNITCOST, b.QTYRECVD as NEWQTYRECVD, d.UNITCOST as NEWUNITCOST
from POP30310 a
join POP30300 c on a.POPRCTNM=c.POPRCTNM
join IV10200 b
on
a.ITEMNMBR=b.ITEMNMBR and a.POPRCTNM=b.RCPTNMBR and a.LOCNCODE=b.TRXLOCTN and c.RECEIPTDATE=b.DATERECD
join
(
select
a.POPRCTNM,
a.ITEMNMBR,
a.RCPTLNNM,
(a.SERLTQTY*a.UNITCOST+case when b.Total_Landed_Cost_Amount is null then 0 else b.Total_Landed_Cost_Amount end)/a.SERLTQTY as UNITCOST
from POP30330 a
left join POP30700 b on a.POPRCTNM=b.POPRCTNM and a.RCPTLNNM=b.RCPTLNNM --and b.LCLINENUMBER=16384
where a.SERLTQTY>0
You should be familiar with Microsoft Dynamics GP tables structure: Tools->Resource Description->Tables, then select series and sort either by physical table name or by technical table group. In our example inventory items are tracked by lot number. If your items are not tracked by lots, please review table POP30310 – the challenge there is the lack of quantity field – so this is moderate excersize for you
If you need SQL scripting where you import landed cost (good example would be integration with Microsoft RMS (Microsoft Retail Management System))
Alba Spectrum Group:
http://www.albaspectrum.com
help@albaspectrum.com 1-866-528-0577,
1-630-961-5918, serving Microsoft Dynamics GP Great Plains customers in
USA/Canada nationwide. Local service is available in Dallas and Houston, TX:
Richmond, Sugar Land, Katy, Rosenberg, Missouri City, Pearland, Friendswood,
Meadows, Mission Bend, Jersey Village; Chicago, IL: Naperville, Aurora,
Bolingbrook, Romeoville, Joliet, Wheaton, Lisle, Downers Grove, Schaumburg, Elk
Grove, Lombard, Woodridge, Darien, Westmont, Batavia, St. Charles, Elgin,
Crystal Lake.
International: Brazil-English Brasil-Portuguese
We serve large scale Microsoft Dynamics GP Great Plains Customization, Data Conversion/Massage/Migration, version upgrade, custom software development and integration to GP, heterogeneous reporting for GP: SQL Server Reporting Services, Crystal Reports; Visual Studio.Net programming for GP, including eConnect, ADO to GP database; complex GP data repair (when somebody damaged GP data via inaccurate UPDATE SQL statement)