Programming,Microsoft,Dynamics computer Programming Microsoft Dynamics GP Great Plains overview fo
----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc
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 scriptingItem 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.ITEMNMBRfrom IV00102 ajoin (select ITEMNMBR as ITEMNMBR, sum (ATYALLOC) as ATYALLOC from(select ITEMNMBR as ITEMNMBR, sum(ATYALLOC) as ATYALLOCfrom SOP10200 group by ITEMNMBR) bb group by ITEMNMBR) b on a.ITEMNMBR=b.ITEMNMBRwhere a.LOCNCODE like ' %' and b.ATYALLOC != a.ATYALLOCSelling 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=0Selling 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:selecta.*from SOP30300 ajoin IV00101 b on a.ITEMNMBR=b.ITEMNMBRwhere abs((a.UNITCOST-b.CURRCOST)/b.CURRCOST)0How 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 damageIf you need help with GP programming, feel free to call the author: 1-866-528-0577, [email protected]
Programming,Microsoft,Dynamics