Programming Microsoft Dynamics GP Great Plains

By: Andrew Karasev

We already covered topics on Dexterity, eConnect, GP SDK Visual Studio.net programming in earlier articles.? In this small article we would like to concentrate on SQL scripting

Item Allocation example.? For the reason of simplicity let’s assume that the only allocated quantity you have in Sales Order Processing orders and invoices and disregard Inventory adjustments, transfers and other modules work transactions, then the following script will give you discrepancy in allocation for the item master record and the summary from Sales Order Processing work transactions lines:

select

a.ATYALLOC, b.ATYALLOC, a.ITEMNMBR

from IV00102 a

select ITEMNMBR as ITEMNMBR, sum (ATYALLOC) as ATYALLOC from

select

ITEMNMBR as ITEMNMBR, sum(ATYALLOC) as ATYALLOC

from SOP10200 group by ITEMNMBR

) bb group by ITEMNMBR

) b on a.ITEMNMBR=b.ITEMNMBR

where a.LOCNCODE like ' %' and b.ATYALLOC != a.ATYALLOC

Selling at Zero Cost.? Next sample script will give you the idea on which items you sold at zero cost.? It may seems too trivial, however it is often used in business reporting:

select * from SOP30300 where UNITCOST=0

Selling at the cost, varying from current cost.? The following script will report you the sales items, sold with 3% variation of current cost for the same item:

SOP30300 a

join IV00101 b on a.ITEMNMBR=b.ITEMNMBR

where abs((a.UNITCOST-b.CURRCOST)/b.CURRCOST)0

How to build your own script.? The answer is simple – you need to do some homework and research GP tables structure, good place to start is opening GP workstation and look at tools->resource description->tables.? It is good idea to select only until you get reasonable comfort in understanding what update statement could do and potentially damage

If you need help with GP programming, feel free to call the author: 1-866-528-0577,

Microsoft
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 
 • 

» More on Microsoft