Dynamic Code Blocks

Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

Hacking Dynamics GP Perpetual Inventory

Background

It is a requirement that all inventory should be checked annually at a minimum. There is a further desire to ensure that inventory on the shelf reflects accurately that shown in Dyanamics GP inventory. Various factors can cause a mismatch so since inventory management was invented, stock checks are used to verify the quantities by physically counting the items in situe.

By stock counting items more frequently we are more likely to keep the levels in sync between the shelf and in the inventory module of GP.

GP Stock Take

GP has a stock count feature were it is possible to assign a stock count frequency (how many times a year we seek to count an item). The dates when the last and next stock counts were performed and are due is stored against each item, for each location.

A stock take in GP is simply a list of all the items we want to stock check. The user can use various criteria to select the attributes of items they want to put into this list. For the purposes of this discussion I am concentrating on using next count date as a basic for including items in the stock count. The user can select all items who next stock count due date is today or in the past (i.e. overdue).

Counting

When a stock take is started all the current inventory levels are snapshotted from inventory and updated against the items in the stock take list. The user then counts all the items putting next to the expected value from inventory, the actual quantity found by counting the item on the shelf.

An inventory transaction can then be built to correct the stock levels to those entered from the stock take.

The challenge

Setting the stock count intervals can become a challenge if you find a number of this sort of scale

image

Promoting a hands-off approach to system, what is needed is to default the information for stock takes.

SQL Help

It makes sense that the fastest turnover items should be counted more often as they are more likely to drift from the values held in GP. Most companies will classify items, there is an item class in GP that could be used to classify the different types of items or one of the user defined fields that are provided in GP. 

A classic A,B,C,D code can be used for the frequency of item movements, stored in the user defined field.

A table can then be created that has the classification against the frequency that it should be checked. Lets call this table IV_STK_COUNT_INTERVAL.COUNTINTERVAL.

Classification
(REORDERCAT)
Stock Check Frequency in working days
(COUNTINTERVAL)
A 30
B 60
C 90
D 120

The stock count facility in GP has a calendar where working days can be defined, so all stock count activities are bound by working days.

For each item in the inventory sites table IV00102, there are three key fields of interest held against each item in each site;

NXTCNTDT Next count date
NXTCNTTM Next count time
LSTCNTDT Last count date
LSTCNTTM Last count time
STCKCNTINTRVL Stock count interval

For the purposes of this example, ignore the time fields as that level of accuracy is not required.

Hence it can be seen that by updating a join between the classification stock check interval table created earlier and the inventory table, the stock take interval can be tied on a nightly basis to the classification of the product, so automating the maintenance of this field.

Discontinued items and items that have never had inventory

Discontinued items with no stock left can have stock take intervals set to zero, so they don’t get counted. The next stock count date should also be “zeroed” to 1900-01-01. The same applies to items that have never had stock.

UPDATE  IV00102
SET STCKCNTINTRVL = 0,
NXTCNTDT = '1900-01-01',
NXTCNTTM = '00:00:00'
FROM iv00101
INNER JOIN
IV_STK_COUNT_INTERVAL
ON USCATVLS_6 = REORDERCAT
LEFT OUTER JOIN
IV00102
ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
WHERE IV00102.LOCNCODE = 'MainWarehouse'
AND STCKCNTINTRVL != 0
AND IV00102.RCRDTYPE = 2
AND
(IV00101.ITEMTYPE = 2 -- discontinued
OR NOT (IV00102.BGNGQTY>0 OR IV00102.LRCPTQTY>0))
-- if has been no activity on this item ever
;
-- Next if the stock count interval is zero , set next stock
-- check to be 1/1/1900 so it don't come out on reports
UPDATE IV00102
SET NXTCNTDT = '1900-01-01',
NXTCNTTM = '00:00:00'
WHERE IV00102.LOCNCODE = 'MainWarehouse'
AND STCKCNTINTRVL = 0
AND NXTCNTDT != '1900-01-01'
AND IV00102.RCRDTYPE = 2;

Next Count Date

The next count date can be set by adding the stock take interval to the current date (taking into account non-working days). This is ok unless the creation of new products is lumpy in nature, in that case it is better to put some randomness into the interval to ensure the spread of products is better. Preventing no items to stock take one day and thousands the next!

In the script below, for the purposes of this article it has been simplified to not use a calendar table, instead it assumes a five day working week. This is part of a stored procedure called by SQL agent job nightly.

UPDATE  IV00102

SET STCKCNTINTRVL =CASE WHEN iv00102.QTYONHND=0 THEN 0 ELSE IV_STK_COUNT_INTERVAL.COUNTINTERVAL END,
NXTCNTDT =
--stock interval for us does not include the weekends so add them on for this period for the date addidtion
dateadd(day, IV_STK_COUNT_INTERVAL.COUNTINTERVAL +
ROUND(((IV_STK_COUNT_INTERVAL.COUNTINTERVAL / 5) * 2), 0),
CASE WHEN LSTCNTDT='1900-01-01'THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) ELSE LSTCNTDT END )

FROM iv00101
INNER JOIN
IV_STK_COUNT_INTERVAL
ON USCATVLS_6 = REORDERCAT
JOIN IV00102
ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
WHERE IV00102.LOCNCODE = 'MainWarehouse'
AND CASE WHEN iv00102.QTYONHND=0 THEN 0 ELSE IV_STK_COUNT_INTERVAL.COUNTINTERVAL END != STCKCNTINTRVL
AND IV00102.RCRDTYPE = 2
AND IV00101.ITEMTYPE = 1
AND (IV00102.BGNGQTY>0 OR IV00102.LRCPTQTY>0)

Stock take

When in GP select the items to add to the stock take by choosing next stock take date as your criteria, selecting your location and choosing 2nd Jan 1900 to today’s date as the date range. This will capture all over due items and todays count items. You may find it useful to set up a reporting services report to show how many items are overdue, emailed daily as part of your KPI setup. This can be done by simply count records where nxtcntdt is not 1900-01-01 and less than today by site.

After Stock Take

When an item is processed as part of the stock take, the next date updated to the interval plus the current date. The last checked date is updated to that of the stock take. This does cause a problem with uneven distributions over time, hot dates can be created when too many items end up with similar dates to be checked. For this reason it is wise to create a report showing the distribution of items by date over a year, to anticipate and take action when peaks are identified. Run the report regularly as the distribution is in constant flux as the different intervals are applied and thrust forward the next check dates.

Set up

Evenly distribute the dates over the year, for the above reason, something relevant when seeding the dates for the first time too.

One more note is that a stock check should be performed by location on a regular basis too, in order to find items placed in wrong bins or items that are showing no stock where stock exists.

Summary

Reporting services scheduled reports

  • Show number of items to count by date as distribution graph over year
  • Show number of items overdue count for KPI purposes

Counts

  • Count all the items due or over due a count
  • Check all locations regularly for items misplaced

SQL

  • Create regular job to align the stock take dates and frequency with the item class
  • Use a levelling script to smooth out the dates should the distribution get too lumpy
--Over due stock checks
select NXTCNTDT as DueDate,count(*) as OutstandingPIChecks
FROM iv00102
where NXTCNTDT >'1900-01-01'
AND NXTCNTDT<getdate()
AND STCKCNTINTRVL>0
AND LOCNCODE='1'
GROUP BY NXTCNTDT
order by 1

--Stock Calendar Exception Days
select * FROM iv41001
-- Stock Calendar Setup
SELECT * FROM IV41000

--Show counts by week

--Uses helper table
--CREATE TABLE [CompanyCalendar](
-- [DateID] [datetime] NOT NULL,
-- [Day of Year] [smallint] NOT NULL,
-- [Day] [varchar](10) NOT NULL,
-- [Day of Week] [tinyint] NOT NULL,
-- [Day of Month] [tinyint] NOT NULL,
-- [Week] [tinyint] NOT NULL,
-- [Month] [tinyint] NOT NULL,
-- [Quarter] [tinyint] NOT NULL,
-- [Year] [smallint] NOT NULL,
-- [WeekDay] [bit] NOT NULL,
-- [Business_Day_Flag] [bit] NOT NULL,
-- CONSTRAINT [PK_CompanyCalendar] PRIMARY KEY CLUSTERED
--(
-- [DateID] ASC
--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
--) ON [PRIMARY]
--GO

select [Week] CalendarWeek,count(*) as QtyPIChecks from iv00102
JOIN CompanyCalendar
ON NXTCNTDT=DateID
where NXTCNTDT >'1900-01-01'
AND STCKCNTINTRVL>0
AND LOCNCODE='1'
GROUP BY [Week]
order by 1

Dynamics GP Visual Studio Addin - Open Form Parameters

Opening a native GP form from VS addin

This post covers a self help pattern, to assist developers wishing to open a native GP form from .NET using Visual Studio Tools For GP addin.
An example is needed to work with, say it was required to open the Manufacturer’s Item Number Maintenance form from our .NET code:

image 

This form is normally only available from the Item Purchasing Options Maintenance Form but in this example it is required to launch it from our .NET code.

Identify the form name

Using the title caption from the form, go into Tools>Customise>Modifier to find the name of the form, or use the resource tool found under Tools>Resource Descriptions>Windows

IF using the resource tool, the product should be selected to which the form belongs, in this example Microsoft Dynamics GP, set Series Inventory, set View by display name.

image

Here we can see its called IV_MFG_Item_Nmbr_Mnt internally. Forms have display and internal names.

By typing in Visual Studio the window can be located by following intellisense, starting at the dictionary it belongs to (MicrosoftDynamicsGP) and taking out the underscores from the Dex name found above as it is typed.

Once the form is located, put a period after the form name to see what procedures it supports, typing “open” flushes out the procedures of interest.

image

It can be seen that to open the form, this form uses: OpenIvMfgItemNmbrMntProcedure

Intellisense also guides as to how to call it using  _Instance.Invoke
image

However, a familiar issue is hit at this point. The open procedure requires two parameters passing. What are the two parameters that intellisense is asking for? Luckily help is at hand, from the tooltip (see image above),

Invokes form procedure “Open_IV_MFG_Item_Mnt” of form “IV_MFG_Nmbr_Mnt”

Thus recalling that all that is happening here is a call down to procedures in the underlying dexterity code, we can find what we need to call this by checking the Software Developer Kit (SDK).

You of course have the GP SDK installed? – if not get it now…

Using the SDK to find parameters

Searching on the SDK folder, C:\program Files\Microsoft Dynamics\GP11.0 SDK\Content   for the start of the procedure name “Open_IV_MFG” a hit is returned in a file called “CoreForms_1100.txt”

Opening CoreForms_1100.txt, and search for the same term. The following text is found in the document …

------------------------------------------------------------------------
INVENTORY FORM PROCEDURE:  Open_IV_MFG_Item_Nmbr_Mnt
    of form IV_MFG_Item_Nmbr_Mnt
------------------------------------------------------------------------
in                       'Item Number'            l_item;
in                       'Item Description'       l_Description;

So the two parameters needed are Item Number and Item Description.

So now we call it like this:

MicrosoftDynamicsGpDictionary.IvMfgItemNmbrMntForm.OpenIvMfgItemNmbrMntProcedure._Instance.Invoke
("testpart", "testpart description")

Compile, deploy and you should find the form opens with the item selected!

 

Fix for Dynamics GP Add-In Initalization Error launching GP 2010,sp3

Problem

Building a new Dynamics GP 2010 client I experienced the following error on putting our customisations into the GP 2010 Addin directory. When launching the GP application, the application crashed out.

Microsoft.Dynamics.GP.BusinessIntelligence.BIReportDeploymentGP.dll: Method not found: ‘ClearCompanyListProcedure FormProcedures.get_ClearCompanyList()’.

 

image

Clue to my issue was in the forum posing here:
Login Problem in Microsoft Dynamics GP 2010- where MUFADDAL SHAKIR suggested that

removing Application.Dynamics.dll from the Addins folder resolved the issue

Ok it solved the issue but will mean that dll is not available to use which is no good for me, however it immediately occurred to me what had happened. In my GP application directory there is a copy of the current Application.Dynamics.dll, Checking its version it was 11.00.0363.000,

imageimage

then checking the one I was referencing in Visual Studio, that due to the deployment was also  the one held in the addins folder, I found it was version 11.00.0218.000. Classic file version control issues had struck! Somehow, I’m not certain how, an old version of the dll was still in source control and had been pulled into my machine’s addin folder in development.

Solution

Hence when the old dll was replaced by the new, everything worked as it should again. I guess this is a new method added into dynamics dic during one of the releases what could not be found.

Architecture

Just to remind you & me of the key dlls involved in visual studio dynamics gp Add-ins:

Microsoft.Dexterity.Shell.dll Used by visual studio do draw the right looking UI to the canvas
Microsoft.Dexterity.Bridge.dll Events and resources bridge to application dictionaries
Application.Dynamics.dll (and others) Dynamics.Dic default assembly, representing the actual methods and events in that GP application dictionary.
A long list of which can be found in your application directory, see below screenshot, you reference what you use:image

Use the DAG.EXE to generate your own for modified forms…

All this is covered in: Visual Studio® Tools for Microsoft Dynamics® GP 2010 Programmer’s Guide

Also online: Architecture from msdn

Fix: Dynamics GP 2010 modify word template, File Not Found

 

Template maintenance in Dynamics GP2010 fails

When in REPORTS>>Template Maintenance, after creating a new report from an original report, clicking on “Modify” caused a file not found exception on our install of GP.

"File Not Found: C:\DOCUME~1\USERXX\LOCAL~1\TEMP\file you were savingcreating.docx"

Solutions on the web GP community forums

  • Reinstalling Dexterity Shared Components
  • Upgrading to GP2010 SP2 or above

These solutions were not working for us. Between us we spent too much time running filemon and looking at debugger tool traces trying to track the problem down. The traces showed the template being created in the user profile temp directory with no error, but in reality it never got there.

Breakthrough

The server copy did work correctly. We spent some time between us trying to work out what the issue was and finally a colleague said something that made sense to me, so I checked out the theory.

I did a WinMerge check between the Add-in Directories (Addins subdirectory of GP application directory) on the working and failing machine and found the following missing files:

image

 

Copy the missing DLLs into the Adds folder

After copying all but the BusinessIntelligence dlls to the add-in folder of the previously broken install, the feature started working correctly again.

My guess is the managed storage dll is involved with the creating of the temp file… possibly.

Hence it would seem we’ve dropped these files as we have upgraded through the versions. We have our own Visual Studio Tools Addins in the add-ins folder. These are passed from version to version as we upgrade GP. My guess is these extra missing dlls got lost at some point causing the word template editing functionality to fail.

I hope this post has helped you get to the solution more quickly that we did.

Here is the working template in word

clip_image002