Free Articles, Free Web Content, Reprint Articles
Thursday, April 18, 2019
 
Free Articles, Free Web Content, Reprint ArticlesRegisterAll CategoriesTop AuthorsSubmit Article (Article Submission)ContactSubscribe Free Articles, Free Web Content, Reprint Articles
 

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 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

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-0577Health Fitness Articles, help@albaspectrum.com

Source: Free Articles from ArticlesFactory.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.



Health
Business
Finance
Travel
Technology
Home Repair
Computers
Marketing
Autos
Family
Entertainment
Law
Education
Communication
Other
Sports
ECommerce
Home Business
Self Help
Internet
Partners


Page loaded in 0.134 seconds