Using SQL UNPIVOT operator to reconcile Dynamics GP inventory items

Working with the Dynamics GP inventory tables you will encounter the field named QTYTYPE a lot. This is usually seen with its partner LOCNCODE.

Inventory in GP can reside in different locations (for example depots New York, London, Sidney). That location then is broken down further into five item types. Item types can be though of condition/state of the item where the item states are an indexed as followed:

1 = On Hand
2 = Returned
3 = In Use
4 = In Service
5 = Damaged

This is a way we can categorise where and what state/status the inventory is in.

The item stock levels for each item is stored in the table IV00102, keyed by ITEMNMBR and LOCNCODE. To avoid creating four times more rows, the table architect decided to pivot the table, giving each quantity type its own field column in the table. The fields names are as follows:

ITEMTYPE Field Name Description
1 QTYONHND On Hand
2 QTYRTRND Returned
3 QTYINUSE In Use
4 QTYINSVC In Service
5 QTYDMGED Damaged

 

This presents a problem as the other inventory tables have a QTYTYPE field and have separate rows for different quantity types. If we need to join to the IV00102 table this becomes troublesome for us. One solution (I know there are others) is to use the UNPIVOT operator in TSQL to unpivot the IV00102 table, causing the columns to present themselves as extra rows.

SELECT ITEMNMBR
,LOCNCODE
,CASE valuename
WHEN 'QTYONHND'
THEN 1
WHEN 'QTYRTRND'
THEN 2
WHEN 'QTYINUSE'
THEN 3
WHEN 'QTYINSVC'
THEN 4
WHEN 'QTYDMGED'
THEN 5
END AS QTYTYPE
,QtyValue
FROM iv00102
UNPIVOT(QtyValue FOR valuename IN (
QTYONHND
,QTYRTRND
,QTYINUSE
,QTYINSVC
,QTYDMGED
)) UnPiv
WHERE ITEMNMBR = '100XLG'

This gives rise to the following result set.

SQL results

See how each quantity type now has its own row rather  than being named columns? We also used a CASE statement to alias the names of the columns back to index numbers.

Example, reconciling inventory values using SQL

In the blog post by Mahmood M. Alsaadi  Reconciling Quantity on Hand – SQL Script, he shows a script to reconcile inventory, so let us not reinvent the wheel and start with that script. The example provided in his post, at the time of writing did not take into account the quantity types. Unfortunately I ended up debugging the SQL to work this out, only to then return to the original post comments to find someone else had also done the same and pointed this out. However the solution they proposed was to tie the QTYTYPE=1 so that only on hand quantities are reconciled.  I felt I could do better than!

I build on the original script adding in the UNPIVOT introduced above:

SELECT TRX_BALANCE.ITEMNMBR AS ItemNumber
,TRXLOCTN AS Location
,Master_Balance.QTYTYPE AS QTYTYPE
,BALANCE AS TRX_BALNACE
,QtyValue AS Master_Balance
,ATYALLOC AS Master_AllocatedQuantity
,QtyAvailable
,BALANCE - QtyValue AS Variance
FROM (
SELECT ITEMNMBR
,TRXLOCTN
,QTYTYPE
,SUM(QTYRECVD) - SUM(QTYSOLD) AS BALANCE
FROM dbo.IV10200
--WHERE IV10200.ITEMNMBR='40-322'
GROUP BY ITEMNMBR
,TRXLOCTN
,QTYTYPE
) AS TRX_BALANCE
LEFT OUTER JOIN (
SELECT ITEMNMBR
,LOCNCODE
,CASE valuename
WHEN 'QTYONHND'
THEN 1
WHEN 'QTYRTRND'
THEN 2
WHEN 'QTYINUSE'
THEN 3
WHEN 'QTYINSVC'
THEN 4
WHEN 'QTYDMGED'
THEN 5
END AS QTYTYPE
,QtyValue
,CASE valuename
WHEN 'QTYONHND'
THEN ATYALLOC
ELSE 0
END AS ATYALLOC
,CASE valuename
WHEN 'QTYONHND'
THEN QtyValue - ATYALLOC
ELSE 0
END AS QtyAvailable
FROM IV00102
UNPIVOT(QtyValue FOR valuename IN (
QTYONHND
,QTYRTRND
,QTYINUSE
,QTYINSVC
,QTYDMGED
)) IV00102Pivot
) AS Master_Balance ON TRX_BALANCE.ITEMNMBR = Master_Balance.ITEMNMBR
AND TRX_BALANCE.TRXLOCTN = Master_Balance.LOCNCODE
AND TRX_BALANCE.QTYTYPE = Master_Balance.QTYTYPE
WHERE BALANCE - QtyValue <> 0

This will output the item, location and quantity type of the items that have an incorrect inventory level in the IV00102 table (this is the table viewed when looking at the Dynamics GP item enquiry form).

This can be a useful  script to set as a scheduled SQL Job to notify your GP admin that inventory needs reconciling, even listing the items that need reconciling. This could also lead to generation of a macro to do the reconcile… one for another day…..

Document attach feature Dynamics GP database and BusObjKey formats

I’ve been working on an archive project recently where I’ve been archiving records from GP into another archive database. This meant looking at how attachments are handled in Dynamics GP, in order to take any attachments related to records being moved into the archive.
 
The Table CO00102 is the table that relates the attachment to the record it is attached to and contains the BusObjKey column, that is implemented as a resource path back to the database record that the attachment is related to. Using a resource path makes it flexible, allowing the widest variety of GP object to be compatible with the attachment system.
 
CO00102

The resource path is implemented in a inconstant manner, varying due to the different nature of the records involved and also through misuse by developers too (I think). 

An example of the format pattern is:

0\PM\Purchase Order History\290941

Where the elements are as follows:

0 Dynamics GP Product Dictionary ID (see hyperlink)
PM Table Series ID
Purchase Order History Table “Display Name”
(see Microsoft Dynamics GP>>Resource Descriptions>> Tables)
290941 Record ID
Could be customer id or purchase order number or other identifier for record

However for attachments to notes windows, the Record ID is the NOTEINDX (ID of the record) but encoded into a hexadecimal version! Some developer was obviously thinking we didn’t have enough hoops to jump through when they imagined that one up and fancied burning some more CPU on the SQL server! 

Update 2017-01-12: If you've used the OLE notes migration tool, you will notice that the OLE notes look like they used this way of encoding the record ID too, this might be where this scheme came from.

The article Document Attach vs. Notes and how to locate whether a master record has a document attached explains how to get from the note index against an attachment record to the hex version of that note index for participating in the BusObjKey column.

CONVERT(VARCHAR(MAX), CONVERT(binary(4),cast(NOTEINDX as integer)), 2)
 
In the screen shot above, the note ending in number 000001CF2 is actually note index ID 7410.
 
Now examining the final format format in the list, this is for the Advanced Credit Control Module (module 1577). For this module they decided that they would shorted the format of the path. Note how the format is one “folder” shorter in depth than the other paths, it misses out the “Table Series”, then jumps right to the table display name. Doing this screws up some SQL scripts that you might encounter out there on the internet that are used for reporting on attachments. They assume the same number of path separators when deconstructing the path but this has one short–oh dear! So what I’m saying is that you must program/script defensively when working with these BusObjKey fields as you are at the mercy of developers, also expect to use a lot of case statements or to create a SQL scalar function to convert your BusObjKey values!

I would be really interested to expand this article if you have any other formats that you have encountered, please drop me a comment against this post!

SQL server FILESTREAM for Dynamics GP file attachments in coattachitems -is it possible?

docattachwindow

In previous post (Managing document attachments  in Dynamics GP) I looked at the attachments feature in Dynamics GP 2013-R2 and above. I talked about how files attached to GP in this manner are stored in the database, specifically in the coAttachmentItems table. This table can get to be a huge size as a consequence of file attachments file sizes accumulating, then dwarfing the other GP tables by size and having knock on issues like size of and time taken for SQL back up and restores.

The table looks like this:

/****** Object:  Table [dbo].[coAttachmentItems]    Script Date: 17/12/2016 23:32:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[coAttachmentItems](
[Attachment_ID] [char](37) NOT NULL,
[BinaryBlob] [varbinary](max) NOT NULL,
[fileName] [char](255) NOT NULL,
CONSTRAINT [PK_coAttachmentItems] PRIMARY KEY CLUSTERED
(
[Attachment_ID] 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] TEXTIMAGE_ON [PRIMARY]


The files are stored in the BinaryBlob and filename stored for reference in the filename field. GP does this in two steps, creating the database row with empty blob (0x0), then populating the blob data.

INSERT INTO coAttachmentItems (
Attachment_ID
,fileName
,BinaryBlob
)
VALUES (
@Attachment_ID
,@fileName
,0x0
)
 
Now populate the blob
 
UPDATE coAttachmentItems
SET BinaryBlob.Write(@Bytes, @Offset, @Length)
WHERE Attachment_ID = @Attachment_ID
 
The Dex Script log shows this too with two stages or CreateAttachment then InsertAttachment methods.
22:27:30      'CreateAttachment() of form coAttachManagement', "", "0\RM\Customer Maintenance\TEST", "C:\Users\user\Documents\itemdesc.txt", "itemdesc.txt", 0, 1
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 'InsertAttachment() of form coAttachManagement', "4bb1e4e4-fe24-41ad-b102-a1f51ba76b34", "C:\Users\user\Documents\itemdesc.txt", "itemdesc.txt"


In the previous post I talked about how it would be helpful if the attach feature supported SQL Server FILESTREAM. This would allow the attachment files to be stored in file storage, which is a different tier of storage, cheaper for us to manage on the file system rather than in the database. I understand there are advantages to keeping the data together in the database but this does not scale well for us.
 
Today I wondered if it would be possible to just implement FILESTREAM myself for attachments in this table. Although this would be naughty and definitely would not be supported, it was an interesting experiment to try. 
To use file stream FILESTREAM columns must be accompanied by a corresponding uniqueidentifierROWGUID column and the column must be varbinary(MAX). These kinds of tables must also be accompanied by a unique index too.

Looking at the table, the data is being stored in a varbinary(MAX) however although the “Attachment_ID” field is a GUID in its content, sadly the content is being stored in a char(37) database data type. It needs to be a uniqueIdentifierROWGUID column to allow FILESTREAM. I suspect that GPs software language, Dexterity does not support a GUID data type, hence why its been put in a char(37). Annoyingly this means I can’t change the BinaryBlob to have a FILESTREAM attribute.

coattachitems

Thus it looks like we have hit a brick wall with trying to implement FILESTREAM this way.

Thoughts for next attempts:

  • Perhaps we can intercept the InsertAttachment method using a GP Visual Studio addin in the GP client, so as to redirect to a custom table that is backed with FILESTREAM? The problem here would be pulling the data back into the form again.
  • Perhaps we could create a new table that uses FILESTREAM and remove the existing table, replacing it with a view that converts the GUID back to char(37), so that GP is updating the view.  However this is going too far in changing the native product tables, I would not be comfortable with this nature of “table hacking”, even if it technically worked I’d be afraid of it breaking the GP install in the future.

Let me know what your thoughts are with a comment against this post.

Trying to update locked view

Trying to update a view with ALTER VIEW and it does not happen immediately, then there is most likely a contention for the table with another process (user).

To find out what is blocking, start the alter statement again, then execute the following SQL, for SQL server,

sp_who2 active
 
spwho2

look at the Blk By column (blocked by) against the row that represents your login and check that the “Command” column has the ALTER command in it. There should be a number of the SPID that is blocking the ALTER command shown where the dashes are shown above.

Use that ID to kill the process (if appropriate!). Although the screen shot shows no blockers, if it had 206 as the blocker, then issuing “kill 206” would allow the ALTER to run.

Note you should understand what you are killing and apologise to the user, don’t kill a process that is going to cause data integrity issues later!

kill 206