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-0577, help@albaspectrum.com
Dexterity Customization for Dynamics GP Evaluation Level Paper
When you are developer it is always a good idea to read technical manuals. But if you was just assigned to the IT team to decide if Dexterity is the right tool to customize your ERP application then first you need something which is in style of ‘easy reading papers’ or FAQ
Planning Dynamics GP Customization in Large Corporation
If you are reading this page then chances are high that you were not able to find ISV add-on and need customization project. Let’s talk about planning, quality assurance and future event such as version updates.
Dynamics GP Invoice Logo Attributed to Specific Company or Crossing the Borders of Three SOP Forms
Initial Great Plains Dynamics architecture had three SOP Invoice forms: Long, Short and Blank. Modern GP is popular in scenarios where you have more than three companies under one business entity umbrella
