Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL – Follow up!

September 23, 2011

Ok, of course my dear friend Corey in Louisville, Ohio had to challenge my post from yesterday and ask a question that is, frankly, a logical and natural progression of things – see Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL.

In yesterday’s post I showed a query that could create a year-to-year Inventory Margin report using T-SQL’s PIVOT operator. The query, while very useful, hardcodes the years you want to display in the pivoted columns list. An excerpt of the PIVOT operator and the pivoted columns list as follows:

.
.
.
PIVOT (
SUM(MARGIN)
FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
) AS pvt

So naturally, Corey’s question was, “What if I want to add the pivoted columns list dynamically, instead of hardcoding them?“. Since all transactions have a date, it would make sense to add the list of years based on the document dates. The good news is this is possible. For this we would need to create a dynamic pivoted list using, well, dynamic SQL, as follows:

DECLARE @listCol NVARCHAR(MAX), @sqlstmt NVARCHAR(MAX);

SELECT @listCol = STUFF(
( SELECT DISTINCT '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
FROM SOP30200
ORDER BY '],[' + CONVERT(NVARCHAR(5), YEAR(DOCDATE))
FOR XML PATH('')
),
1,
2,
'') + ']'

SET @sqlstmt =
N';WITH CTE AS (
SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE)
WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0)
GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description],' + @listCol + N'
FROM (
SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN
FROM CTE
) p
PIVOT (
SUM(MARGIN)
FOR TRXYEAR IN (' + @listCol + N')
) AS pvt
ORDER BY ITEMNMBR;';

EXEC sp_executesql @sqlstmt;

Couple observations…

We use the FOR XML clause with the PATH mode to build a list of elements and attributes based on the distinct year values stored in the document date (DOCDATE) column in our SOP30200 (SOP History) table, which we store in the @listcol variable length Unicode character data type (NVARCHAR).

Our CTE is now embedded in an NVARCHAR variable, which we call @sqlstmt, with just the right breaks to concatenate our pivoted column list variable, @listcol as part of the overall SQL statement character string that will be executed.

We finally use the sp_executesql system stored procedure to run our dynamic SQL query and produce the results below:

Item Number Item Description 2014 2015 2016 2017 2018
100XLG Green Phone NULL NULL 307.05000 40.05000 NULL
3-B3813A Keyboard NULL NULL NULL 40.00000 NULL
3-C2924A SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL 148.50000 NULL
3-C2924A T0101 - SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL NULL 84.38000
3-C2924A T0102 - SCSI Cable, 2.5m. 68-pin HI-Density NULL NULL NULL NULL 67.50000

Finally, you will notice NULL values for some of the years where there was no sales activity for an item. You can take care of these and how they display, directly on your report or Excel spreadsheet.

Not bad at all!

For a primer on the Do’s and Don’ts of dynamic SQL, I invite you to read SQL Server MVP, Earland Sommarskog article, The Curse and Blessings of Dynamic SQL.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL

September 22, 2011

As of late I have been camping out at the SQL Developer Center’s  Transact-SQL Forum and I have to say, I have learned a great deal from my fellow SQL Server MVPs. Very alike the Microsoft Dynamics GP MVPs, these folks are willing to resolve pretty much any T-SQL request that comes across the forum.

So long you follow some simple posting guides, like including your table definitions, providing some sample data and detailing your expected results, there’s nothing these folks won’t do for you.

I decided to put some of what I have learned to the test by creating a T-SQL query that would produce a Year-to-year Inventory Margin Report (in currency) by using the T-SQL PIVOT operator. My fellow MVP Mark Polino is very well versed with Microsoft Excel and PowerPivot and I thought this would be a way to demonstrate that you can still use T-SQL to resolve some very complex issues which otherwise would require the use of front-end tool or some back-end cubes.

The following query can be run against the Fabrikam test company and adjusted to meet your specific needs:

;WITH CTE AS (
SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE)
WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0)
GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC
)
SELECT ITEMNMBR AS [Item Number], ITEMDESC AS [Item Description],
COALESCE([2014], 0) AS Y2014,
COALESCE([2015], 0) AS Y2015,
COALESCE([2016], 0) AS Y2016,
COALESCE([2017], 0) AS Y2017
FROM (
SELECT TRXYEAR, ITEMNMBR, ITEMDESC, MARGIN
FROM CTE
) p
PIVOT (
SUM(MARGIN)
FOR TRXYEAR IN ([2014], [2015], [2016], [2017])
) AS pvt
ORDER BY ITEMNMBR;

The script uses a query encapsulated in a Common Table Expression (CTE) to produce a temporary result for the sales data by item, grouped over each year. However, this result would produce rows of records for each year, as shown below:

SELECT YEAR(b.DOCDATE) AS TRXYEAR, a.ITEMNMBR, a.ITEMDESC, SUM(a.XTNDPRCE) AS XTNDPRCE, SUM(a.EXTDCOST) AS EXTDCOST, SUM(a.XTNDPRCE - a.EXTDCOST) AS MARGIN
FROM SOP30300 a LEFT OUTER JOIN SOP30200 b ON (a.SOPTYPE = b.SOPTYPE) AND (a.SOPNUMBE = b.SOPNUMBE)
WHERE (b.SOPTYPE = 3) AND (b.VOIDSTTS = 0)
GROUP BY YEAR(b.DOCDATE), a.ITEMNMBR, a.ITEMDESC

/* Results */

TRXYEAR ITEMNMBR ITEMDESC XTNDPRCE EXTDCOST MARGIN
2014 ACCS-CRD-12WH Phone Cord - 12' White 39.80000 13.16000 26.64000
2014 ACCS-RST-DXBK Shoulder Rest-Deluxe Black 29.85000 13.65000 16.20000
2014 ACCS-RST-DXWH Shoulder Rest - Deluxe White 39.80000 18.20000 21.60000
2014 ANSW-ATT-1000 Attractive Answering System 1000 119.95000 59.29000 60.66000
2014 ANSW-PAN-1450 Panache KX-T1450 answer 219.90000 100.50000 119.40000
2014 FAXX-CAN-9800 Cantata FaxPhone 9800 23999.50000 11970.00000 12029.50000
2014 FAXX-HLP-5433 Hewlett Packard FAX-310 854.50000 395.10000 459.40000
2014 FAXX-SLK-0172 Sleek UX-172 fax 2699.90000 1349.00000 1350.90000
2014 HDWR-PNL-0001 Control Panel 609.95000 303.85000 306.10000
2014 HDWR-PRO-4862 Processor 486/25MHz 5999.95000 2998.15000 3001.80000
2014 PHON-ATT-53BL Cordless-Attractive 5352-Blue 1139.70000 561.30000 578.40000
2014 PHON-ATT-53WH Cordless-Attractive 5352-White 189.95000 92.59000 97.36000
2014 PHON-BUS-1250 Handset,multi-line 359.95000 165.85000 194.10000
2014 PHON-PAN-2315 Panache KX-T231 wall 119.90000 59.50000 60.40000
2014 WIRE-SCD-0001 Single conductor wire 8.75000 4.00000 4.75000
2016 100XLG Green Phone 4136.55000 3829.50000 307.05000

But in order to produce the results we want, having a true year by year comparison, it is necessary to pivot the results of the above query. Here’s where the PIVOT operator comes into play, as shown in the initial query. This is the output produced:

Item Number       Item Description      Y2014  Y2015  Y2016  Y2017
100XLG Green Phone 0.00000 0.00000 307.05000 40.05000
3-B3813A Keyboard 0.00000 0.00000 0.00000 40.00000
3-C2924A SCSI Cable, 2.5m. 68-pin HI-Density 0.00000 0.00000 0.00000 148.50000
3-C2924A T0101 - SCSI Cable, 2.5m. 68-pin HI-Density 0.00000 0.00000 0.00000 0.00000
3-C2924A T0102 - SCSI Cable, 2.5m. 68-pin HI-Density 0.00000 0.00000 0.00000 0.00000
3-D2657A T0101 - DB 15 Male Adapter 0.00000 0.00000 0.00000 0.00000
3-D2657A T0102 - DB 15 Male Adapter 0.00000 0.00000 0.00000 0.00000
3-D2659A T0101 - DB 25 Female Adapter 0.00000 0.00000 0.00000 0.00000
3-D2659A T0102 - DB 25 Female Adapter 0.00000 0.00000 0.00000 0.00000
3-E4471A HP Extractor Fan, 200-240V 0.00000 0.00000 0.00000 134.00000
3-E4592A SurgeArrest Plus 0.00000 0.00000 0.00000 180.00000
3-E4592A T0101 - SurgeArrest Plus 0.00000 0.00000 0.00000 0.00000
3-E4592A T0106 - SurgeArrest Plus 0.00000 0.00000 0.00000 0.00000
3-J2094A HP-PB 16 Channel RS-232C Modem Conn MUX 0.00000 0.00000 0.00000 473.00000
4-A3666A 4.2GB LP Disk Drive 0.00000 0.00000 0.00000 350.00000
5-FEE Per Call Fee 0.00000 0.00000 0.00000 0.00000
5-FEE T0101 - Per Call Fee 0.00000 0.00000 0.00000 0.00000
5-FEE T0102 - Per Call Fee 0.00000 0.00000 0.00000 0.00000
5-OVTLABOR T0101 - Overtime service labor 0.00000 0.00000 0.00000 0.00000
5-OVTLABOR T0102 - Overtime service labor 0.00000 0.00000 0.00000 0.00000
5-STDLABOR T0101 - Standard service labor 0.00000 0.00000 0.00000 0.00000
5-STDLABOR T0102 - Standard service labor 0.00000 0.00000 0.00000 0.00000
5-STDLABOR T0106 - Standard service labor 0.00000 0.00000 0.00000 0.00000
5-TVLLABOR T0101 - Travel Labor 0.00000 0.00000 0.00000 0.00000
5-TVLLABOR T0102 - Travel Labor 0.00000 0.00000 0.00000 0.00000
5-TVLLABOR T0106 - Travel Labor 0.00000 0.00000 0.00000 0.00000
ACCS-CRD-12WH Phone Cord - 12' White 26.64000 0.00000 199.80000 226.44000
ACCS-CRD-25BK Phone Cord - 25' Black 0.00000 0.00000 69.85000 83.82000
ACCS-HDS-1EAR Headset-Single Ear 0.00000 0.00000 1034.00000 813.35000
ACCS-HDS-2EAR Headset - Dual Ear 0.00000 0.00000 0.00000 527.67000
ACCS-RST-DXBK Shoulder Rest-Deluxe Black 16.20000 0.00000 226.80000 226.80000
ACCS-RST-DXWH Shoulder Rest - Deluxe White 21.60000 0.00000 163.20000 205.20000
ANSW-ATT-1000 Attractive Answering System 1000 60.66000 0.00000 242.64000 303.30000
ANSW-PAN-1450 Panache KX-T1450 answer 119.40000 0.00000 1194.00000 1134.30000
ANSW-PAN-2460 Panache KX-T2460 answer 0.00000 0.00000 149.60000 169.60000
FAXX-CAN-9800 Cantata FaxPhone 9800 12029.50000 0.00000 66987.41000 51129.85000
FAXX-HLP-5433 Hewlett Packard FAX-310 459.40000 0.00000 0.00000 0.00000
FAXX-RIC-060E Richelieu Fax 60E 0.00000 0.00000 2404.50000 2404.50000
FAXX-SLK-0172 Sleek UX-172 fax 1350.90000 0.00000 1350.90000 675.45000

Happy pivoting.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


Cannot insert the value NULL into column ‘BASEUOFM’ error when clicking on Items List in Navigation Pane

August 3, 2011

Just recently a few partners began reporting getting the error Cannot insert the value NULL into column ‘BASEUOFM’ when clicking on the Items list under the Inventory Navigation Pane option.




Items list error

  The name of the global temp table – in this case, tempdb.dbo.##0251007 – varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2.

Upon further review, the issue is due to bad data in the Unit of Measure Schedule (UOMSCHDL) column in the Item Master table (IV00101). In summary, if you have an item record with a blank Unit of Measure Schedule or a Unit of Measure Schedule that does not exist in the Unit of Schedule Master table (IV40201), it will cause the Items list to fail with the error above.

The following query should help in identifying the offending record(s):

' Created by Mariano Gomez, MVP

' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.
SELECT * FROM IV00101 WHERE UOMSCHDL NOT IN (SELECT UOMSCHDL FROM IV40201);

Once you have identified the record(s) causing the failure, you can use the Item Maintenance window to correct the problem:

Item Maintenance window

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


>Customizations for the View Indented Bill (bmViewIndentedBillInquiry) window failing for GP 2010

March 29, 2011

>In the past days I had been helping a Microsoft partner and good friend of mine in migrating some Modifier with VBA customizations from Microsoft Dynamics GP version 9.0 to version 2010.  In doing so, we came across an issue with one of the modified objects that needed to be converted: the Inventory module’s View Indented Bill window.

View Indented Bill window – Original

The customization in question added a field to the View Indented Bill window to retrieve the quantity available for a given component item on a bill, as shown below:

View Indented Bill window – Modified

The rest of the customization included adding some VBA code to retrieve the quantity available for the component across all sites by using some simple ADO code. So far, good!

The error came into play when we attempted to print the Indented Bill Report, by clicking on the print button on the window.

Unhandled script exception error

After selecting the report type on the destination on the Report Destination window and clicking Ok to generate the actual report, Microsoft Dynamics GP was complaining about an Unhandled script exception an its inability to find script PrintTraverseCallback of form “[Not Found]”.

As every good developer would know, troubleshooting this issue meant:

a) Testing that the error did not happen with the original form. Checked!
b) Testing that modifying the form did not corrupt the forms dictionary file, FORMS.DIC. Not Checked!
c) Testing that adding VBA code did not introduce the error, Not Checked!

In cases b) and c) even after rebuilding the forms dictionary and removing the VBA code, the error still occurred, but for case a) the system printed the report without presenting the Unhandled script exception error.

So being the, ehem!, well connected guy I am, I decided to call my good friend [Name excluded to protect the innocent] over at Microsoft. A test was performed to modify the same window and grant security to the modified version of the window, then run the report. The results were the same – Unhandled script exception. Now I could confirm that this was in effect a bug.

The bug also causes the BM Bill of Materials report would also not print the indented bill as expected.

BM Bill of Materials printed from original View Indented Bill window

Now, here is the report printed from the modified window.

BM Bill of Materials printed from the modified View Indented Bill window

Consequently, a problem report has been written up for this to be addressed in the near future – hopefully quick enough for the affected client.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


>From the newsgroups: Changing item currency decimal places

February 3, 2011

>Every once in a while as consultants, we run into these requests that leave us scrambling for an answer. It is not at all strange to find companies that want to modify the currency decimal places supported only at the product level (item master), while wanting to maintain the currency decimals set from an accounting perpective (general ledger and financial reporting). For example, for product transaction purposes, some companies may require 3 or 4 decimal places, but for financial reporting and general ledger would still want to maintain 2 decimals. Here is what the consultant requested:

Is there a way to change “Currency Decimals” on the Item Maintenance window after an item is saved? If the field is not accessible which field in which SQL table has to be changed?

The answer comes courtesy of Microsoft’s Tirumal Boppana from the Dynamics GP Online Partner Technical community forum. Tirumal outlines two methods that allow you to change your currency decimals and explains the values stored in the tables representing the on-screen decimal places.


There are actually two ways in changing the currency decimal places of the item.

Note: Secure a current restorable backup of the company database. You also have the option to restore your backup to a test company database so you can go through these steps in the test environment. I have attached KB Article 871973How to set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, or SQL Server 2005.

Option 1: Change the currency decimal place using the Change Decimal Places Utility. (Recommended)

1. Delete all unposted transactions against the item number from all module such as IV, SOP, POP, Manufacturing, Bill of Materials and Field Service. These transactions could be allocation of the item, stock count and anything that updates Inventory tables.
2. Use the utility to change the decimal place.

a. Go to Change Decimal Places window (Microsoft Dynamics GP menu – Utilities – Inventory – Change Decimal Places).
b. Mark Change Currency Decimal Place option and then click Yes when you get the prompt saying that ‘Changing the currency decimals will round the amounts in the price list for each item included in the range. Do you want to continue?’
c. Select the decimal place you wish to change it to.
d. Select the appropriate Currency ID.
e. Click on Process.

Option 2: How to change currency decimals without having to remove items from Work transactions and without having to change price lists.

Here are the lists the steps that we can take to change currency decimals for items with a high turnover rate and cannot be removed from Work transactions. If the users would like to keep the price list of the item then this set of steps will also help.

Note: Do not use these steps if you are reducing decimal places and if the decimals you would like to lose have already been used. Doing this may cause data integrity issues for you on the costing of your items.

For example: Let’s say that some of my cost layers for my A item have a cost tag that takes all its currency decimal places (like $1.12345 for 5 decimals, $1.1234 for 4 decimals and $1.123 for 3 decimals). If I force a change of the currency decimals to 2 for my item, then these cost values will be truncated (to show $1.12) and may cause issues with the cost values being posted to the General Ledger. This goes with the item prices that you have setup as well.

1. Make a complete backup of the company database. You can restore this backup to a test database if you would like to test these steps in a test environment first.

2. In SQL Server Management Studio, run the script below:

-- This script is provided "AS IS"
SELECT * FROM sysobjects o, syscolumns c
WHERE o.id = c.id AND o.type = 'U' AND c.name = 'DECPLCUR'
ORDER BY o.name

** This script will search the tables that have the DECPLCUR as a column name.


3. Run the script below only if the tables that were listed by the script in step 1 has an ITEMNMBR field.

-- This script is provided "AS IS"
Update TABLENAME set DECPLCUR = '0' where ITEMNMBR = 'xxx'

Note #1 : Replace xxx with the item number of the item we need to change decimals for.


Note #2 : Replace Y with your preferred currency decimal place field. Below are the possible integer values of the Currency Decimal Place (DECPLCUR) field in SQL Query Analyzer:


1 – 0 decimal place
2 – 1 decimal place
3 – 2 decimal places
4 – 3 decimal places
5 – 4 decimal places
6 – 5 decimal places


Example: Since I wanted to have 5 Decimal Places, the Update statement is: Update IV10001 set DECPLCUR = ‘6’ where ITEMNMBR = ‘CURRENCY’


Note: This option would not affect posted transactions. This change would affect the transactions that are to be posted.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/


How to display an image at the User Interface level

December 13, 2010

One of the questions I often get is, can you dynamically display an image on a Microsoft Dynamics GP form or report. The answer is No, it’s not possible. The reason for this is, Microsoft Dexterity, the tool used to developed your beloved Microsoft Dynamics GP, is only capable of displaying images stored within the dictionary itself. Since the picture control in Dexterity is binded to these pre-stored images, this obviously would indicate that there is no room for dynamic images to be added. In addition, the Dexterity picture control cannot be referenced or manipulated programatically as Dexterity does not treat this object like it does with any other fields.

One *exception* may be the adding these pictures via Modifier, but even so, this is not a dynamic manipulation of a picture control. In summary, not possible!

Of course, you can always resort to VBA. The objective of this post is to see how you can use VBA to aid in displaying images where needed. One typical application is displaying product images or employee pictures. Today’s example will look at displaying product images. For this customization we will add a button to the Item Maintenance window, which in turn will call a VBA user form. Our user form contains a picture control which will display the product image once the window opens. We will use the image link on the Item Internet Information window to store the path to the actual image file.

NOTE: The Item Internet Information window can certainly display a picture, but it calls the default application associated with the extension of the file being loaded. Not practical if you want to avoid users damaging the actual image file.

1. Modify the Item Maintenance window to add a button control. To accomplish this, we will use Modifier to add the button control. Once the control has been added, we will return to Microsoft Dynamics GP to grant security to the modified window.



Item Maintenance window in Modifier
Note the button control added next to the other buttons (click image to enlarge)

Upon returning to Microsoft Dynamics GP, we grant security to the modified window and the result should be as follows:

Item Maintenance window

2. For the next step, we will add the window, the Item Number field, and the newly added Image button to Visual Basic for Applications. We can then proceed to create a simple user form with a picture control as shown below:

For this example, we have renamed the picture control object to ctrlImage and the user form to frmImage.

3. The following piece of code is added to the Image button’ BeforeUserChanged event on the ItemMaintenance object:

Image_BeforeUserChanged()

' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Private Sub Image_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
If ItemNumber.Empty = False Then
frmImage.Show
Else
MsgBox "Please select an Item Number to continue"
End If
End Sub

The above code opens the frmImage user form if the ItemNumber field is not empty.

4. We can now add the code to load the image based on the path stored in the Internet Addresses table (dbo.SY01200) in the company database. For this, we will use the UserInfoGet object to open an ADO connection to the company database and use a recordset to retrieve the path stored for the specific item. In addition, we will use the LoadPicture() function to read the file containing the actual image into the picture control.

UserForm_Activate()

' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Private Sub UserForm_Activate()
Dim oCn As New ADODB.Connection
Dim oCmd As New ADODB.Command
Dim oRst As New ADODB.Recordset, picRst As New ADODB.Recordset
Dim oStream As New ADODB.Stream

' Establish connection to company database
Set oCn = UserInfoGet.CreateADOConnection()
With oCn
.CursorLocation = adUseClient
.DefaultDatabase = UserInfoGet.IntercompanyID
End With

If oCn.State = adStateOpen Then
With oCmd
.ActiveConnection = oCn
.CommandType = adCmdText

' Command to retrieve image path
.CommandText = "SELECT INET4 FROM SY01200 WHERE Master_ID = '" & ItemMaintenance.ItemNumber & "' AND Master_Type = 'ITM'"

Set oRst = .Execute

If Not oRst.EOF Then
ctrlImage.Picture = LoadPicture(oRst!INET4)
ctrlImage.PictureSizeMode = fmPictureSizeModeStretch

Else
MsgBox "Could not find an image file for this item"
End If

oRst.Close
End With
Else
MsgBox "Could not connect to company database " & UserInfoGet.IntercompanyID, vbCritical, "Connection Error"
Set oCn = Nothing
End If
End Sub

5. Finally, some sample data… I will be using a picture of a processor, stored in the path indicated by the window field:

Internet Information window

Once we are done, we can test our customization by clicking on the Image button on the Item Maintenance window:

Item Maintenance window with Item Image user form window

It’s great to see how VBA can help with everyday tasks that may otherwise seem complex. Hope you enjoyed the techniques used in this customization and provide your feedback. There are a few enhancements I can think of, but this should get you started in the right direction.

Downloads:

Item Maintenance and User Form package and Image zip file – Click here

Notes:

a) To test, change the path of the image on the item Internet Information card.
b) You may require a reference to Microsoft ActiveX Data Objects under the Visual Basic Editor to have this code execute properly.
c) This customization is compatible with Microsoft Dynamics GP versions 10.0 and 2010

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


From the Newsgroups: What are those GL entries with reference SALESASMxxxx?

July 30, 2010

Welcome to another edition of From the Newsgroups.

The Microsoft Dynamics GP Online Partner Technical Community forum is one of those virtual places where you get to see and experience it all. One gets to become familiar with real life issues experienced by us partners keen enough to share our implementation and support issues. The following is a thread from the Online Partner Technical Community:

Good afternoon. Our customer keeps getting these “mystery” GL postings [SALESASMxxxx] and wants to know why they are being generated but I cannot find any article or search that shows this document prefix for GP. Does anyone know what type of doc this is and why it may be generated automatically into GL?

The answer comes courtesy of Tristan Thor Clores, a Microsoft Support Engineer.

For transactions that have the SALESASM prefix in their description: These may mean that a sales invoice with a shortage override of a top level BOM (also known as finished good) was posted at one cost. Then when the BOM that fulfilled the shortage was posted its unit cost was different from what the invoice held.

————————————————————————————
Example:
Let’s pretend that we have a $0.00 balance in the Inventory-Finished Goods account. An invoice was posted with a shortage override for 10 units of finished good item HARD DISK. Its unit cost is $10.00 and its unit price is $20. Its journal entry will look like the following:

Debit: Accounts Receivables $200.00
Debit: Cost of Sales $100.00

Credit: Sales $200.00
Credit: Inventory-Finished Goods $100.00

However, when a BOM assembly was posted for 10 units of HARD DISK, its unit cost was actually at $12.00 per base unit. Let’s pretend that it has the following account distributions:

Debit: Inventory-Finished Goods $120.00

Credit: Inventory-Assembly Component #1 $50.00
Credit: Inventory-Assembly Component #2 $70.00

This journal entry will result in you having 0 units On Hand for the item but with a debit balance of $20.00 in the General Ledger. So now, Microsoft Dynamics GP will create the following cost variance journal entry to remove this balance and help your GL recognize the true cost of the sale. This change though will never hit Sales Order Processing history and so the SOP Document Analysis report will not print this additional cost and will then print an incorrect profit margin afterwards:

Debit: Cost of Sales $20.00
Credit: Inventory $20.00
————————————————————————————

I would also like to share the following list with you. It is a list of the transaction reference prefixes that Microsoft Dynamics GP support engineers have encountered with cost variance journal entries so far:

1. BOM – assembly transaction
2. INV – invoice from the Invoicing module
3. IVT – inventory transfer
4. IVA – inventory adjustment
5. IVV – inventory variance
6. SALES – sales invoice from Sales Order Processing
7. PRTN – purchase return
8. MCTE – for a transaction that originated in Manufacturing Component Transaction Entry
9. MRCT – manufacturing receipt
10. MCLS – manufacturing close, including regular close and Quick MO
11. STCK – stock count variance
12. FSSC – field service Service Call
13. FSRMA – field service RMA
14. FSRTV – field service RTV
15. FSWO – field service Work Order
16. PA – project accounting
17. POP – close a PO line in Edit PO Status
18. RECON – created by Inventory Reconcile
19. CONV – created by an upgrade conversion
20. MCTERCT – If the items were issued in the Component Transaction Entry window and then the invoice is completed the adjustment will have a prefix of MCTERCT
21. MRCTRCV – If the items were issued in Mo Receipt Entry (they are “backflushed”) and then the invoice is complete the adjustment will have a prefix of MRCTRCV.
22. MCLSRCV – If the items were issued in the MO Close process (the MO was partially received and more “backflushed” items were issued during the close) and then the invoice is completed the adjustment will have a prefix of MCLSRCV.

I recommend going through KB Article 869470 – “Cost Variance for Inventory,” “Sales Order Processing,” and “Purchase Order Processing” for mor details regarding cost variance in the system.

Let me know how this goes.

I hope you find this article very interesting.

Related Articles

What do those strage reference codes in GL mean?

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/