Programming Microsoft Dynamics GP Great Plains overview for consultant
Microsoft Great Plains is currently available on Microsoft SQL Server database platform only, this makes SQL scripting to be simple enough and feasible to practice, when integrating, reporting or doing data fixing for Microsoft Great Plains.
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 lets 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
join
(
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:
select
a.*
from
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 , help@albaspectrum.com
ABOUT THE AUTHOR
Andrew Karasev, Alba Spectrum Group, http://www.albaspectrum.com, serving GP customers remotely in Montreal, Toronto, New York, Chicago, Houston, Los Angeles, San Francisco, San Diego, Phoenix, Miami, Orlando, Atlanta, Kansas City, Denver, Rhino, Las Vegas, Vancouver, Seattle. Local service is available in Texas: downtown Houston, Richmond, Rosenberg, Sugar Land, Galveston, Katy, Dallas, in Illinois: downtown Chicago and suburbs: Naperville, Aurora, Bolingbrook, Winchester, Morris, Minooka, Plainfield, Romeoville, Schaumburg, Elk Grove, Lombard, Downers Grove, Lisle, Ottawa, LaSalle, De Kalb, Plano, Crystal Lake, Montgomery, Oswego, Elgin. |
