SmartList Builder: Sales Summary By Quarter

Just recently I was approached by a partner wanting to leverage the info in the Receivables Summary table (RM00104) to build a smart list showing sales summary by quarter. The smart list needed to display the following information:

Customer Number	Year	Q1	Q2	Q3	Q4
AARONFIT0001	2013	0.00	4224.67	0.00	10277.37
AARONFIT0001	2014	21468.68	0.00	0.00	0.00
AARONFIT0001	2016	12164.15	0.00	0.00	0.00
AARONFIT0001	2017	4945.70	5809.40	0.00	0.00
AARONFIT0001	2018	0.00	0.00	877.50	0.00

As is customary with these types of request, the best bet is to create a SQL Server view that can then be leveraged from Smartlist Builder. By using a SQL Server view, we can leverage some cool T-SQL set-based data manipulation capabilities.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT custnmbr as [Customer Number], year1 as [Year], isnull([1], 0.00) as Q1, isnull([2], 0.00) as Q2, isnull([3], 0.00) as Q3, isnull([4], 0.00) as Q4
FROM (
  SELECT custnmbr, year1, datepart(qq, datefromparts(year1, periodid, 1)) as qtr, smrysals 
  FROM RM00104
  WHERE histtype = 1
) p
PIVOT 
( SUM (smrysals) FOR qtr IN ([1], [2], [3], [4])
) AS pvt
GO

The above query, produces the results required, but what makes it happen is the beauty of the PIVOT operator. In addition, if you are using SQL Server 2012, you can take advantage of the DATEFROMPARTS function to simplify the conversion of the date parts (year1 and periodid) to a full date to then calculate the quarter with the DATEPART function.

If you are using SQL Server 2008 or earlier, the following query should do:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT custnmbr as [Customer Number], year1 as [Year], isnull([1], 0.00) as Q1, isnull([2], 0.00) as Q2, isnull([3], 0.00) as Q3, isnull([4], 0.00) as Q4
FROM  (
  SELECT custnmbr, year1, datepart(qq, CAST(CAST(year1 AS varchar) + '-' + CAST(periodid AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME)) as qtr, smrysals 
  FROM RM00104
  WHERE histtype = 1
) p
PIVOT 
( SUM (smrysals) 
  FOR qtr IN ([1], [2], [3], [4])
) as pvt
GO

Note that the above version of the query uses the CAST function to determine the date.

Until next post!

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

Read More

Listing all eConnect Modified PRE and POST stored procedures

As of recent, I was ask of a simple method to determine which eConnect PRE and POST stored procedures had been modified. After all, with a concrete list, you are able to script just the affected ones prior to a major version upgrade or a feature pack installation.

A modified stored procedure could be simply qualified as one on which you have ran an ALTER PROCEDURE statement or have edited it using SQL Server Management Studio. With that said, the following query may not be failsafe, but provides a very accurate way of getting a list.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT name, create_date, modify_date 
FROM sys.objects 
WHERE (type = 'P') AND (name LIKE 'ta%Pre' or name LIKE 'ta%Post') AND (create_date <> modify_date)

Enjoy!

Until next post!

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

Read More

Extracting data from Microsoft Dynamics GP company databases using SQL Server FOR XML and XMLNAMESPACES

I truly love what I do. Really!

My job takes me just about everywhere around this great country of ours and beyond its borders in the quest of helping clients and partners get the best out of their Microsoft Dynamics GP application and data.

In reference to the latter - data - I had been asked recently by a client how they could produce XML formatted data from their Microsoft Dynamics GP databases to be consumed by some web services applications they had developed. Some conditions around this request:

• The client did not want to implement eConnect Requester, though I have to admit this would have been a slam dunk with MSMQ queues. 
• The XML documents needed to be rather available and changeable very quickly to serve other needs.
• No additional investments in third party products, middlewares or the likes could be suggested since budget was pretty tight.

In other words, no eConnect, no third party products, and lots of flexibility...

The answer could only be one: use the powerful XML capabilities of T-SQL to get data out in the format required by the client.

Some theory

There's a powerful option when querying data from SQL Server for use with third party applications and/or web services. You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions.

The FOR XML clause has some great benefits:

• It allows a SQL Server developer of Microsoft Dynamics GP consultant to write critical pieces of integration architecture without having to learn the destination system's schema.
• Additional table columns - pieces of data, if you will - can be added to the results with relative ease.
• It's an efficient way to process data and reduces the number of components that must be developed.
• It can be formatted to match target schemas in order to simplify mapping and/or middleware configuration.

There are a number of options related to using the FOR XML clause in SQL Server. The most appropriate way I have found - best practice, if you will - is to declare your own namespace using the WITH XMLNAMESPACES clause and to format the XML specifically as expected with the PATH mode.

Keep in mind that SQL Server has an AUTO mode which allows it to automatically format the XML document for you, relinquishing some control from you the developer or consultant.

For more information on SQL Server FOR XML and the WITH XMLNAMESPACES clauses, please take a look at SQL Server Books Online:

MSDN - FOR XML clause - http://msdn.microsoft.com/en-us/library/ms178107.aspx
MSDN - WITH XMLNAMESPACES clause - http://msdn.microsoft.com/en-us/library/ms177400.aspx

Now a practical application...

This is a simple example on how to implement all of it together. Let's take the case of a Customer with multiple addresses. The following query should produce XML data with our customer master (RM00101) and address master information (RM00102).

CustomerExtract.sql

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

WITH XMLNAMESPACES('http://sql.customer.extract' as "ce0")
SELECT NULL
    , (  SELECT RM00101.CUSTNMBR AS [ce0:CustomerNumber]
   ,RM00101.CUSTNAME AS [ce0:CustomerName]
   ,RM00101.CHEKBKID AS [ce0:CheckbookID]
   , ( SELECT RM00102.ADRSCODE AS [ce0:AddressCode]
    , RM00102.ADDRESS1 AS [ce0:Address1]
    , RM00102.ADDRESS2 AS [ce0:Address2]
    , RM00102.CITY AS [ce0:City]
    , RM00102.[STATE] AS [ce0:State]
    , RM00102.ZIP AS [ce0:Zipcode]
    FROM RM00102 
    WHERE RM00102.CUSTNMBR = RM00101.CUSTNMBR
    FOR XML PATH('ce0:Addresses'), TYPE)
   FROM RM00101
  FOR XML PATH('ce0:Customer'), TYPE)
 FOR XML PATH ('ce0:CustomerExtract'), TYPE

The results are pretty straight forward:

FOR XML output (formatted for display purposes only)

Nothing but XML greatness!

The above query could have been encapsulated in a stored procedure with a parameter for customer number, which could have driven the results displayed. As you can tell, getting the data you need for any destination, will depend on you specific requirements, but it's doable with the power of SQL Server and T-SQL.

Until next post!

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

Read More

SSRS: GL Trial Balance Summary report returns no data - Follow Up

Back in July of last year, I wrote an article on a reported issue with the SSRS GL Trial Balance reports were not returning any data with Microsoft Dynamics GP 2010 and 2010 R2 (SP2) - see SSRS: GL Trial Balance Summary report returns no data.

Since then, this problem has been documented in KB article 2588519 - GL Trial Balance SRS Reports return no data using Microsoft Dynamics GP and a workaround provided in the same article to fix the ailing stored procedure, dbo.seeglPrintSRSTrialBalance, causing data not to be returned.

The good news is, this problem is scheduled to be fixed in Service Pack 3.

Until next post!

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

Read More

1099 reports: the devil is in the details

It's certainly that time of the year and if you haven't sent out those 1099's you are running out of time. I was working on a case recently at one of my clients who was having all sort of issues printing 1099 reports. Apparently, reconciling their calendar year wasn't helping the issue either. It did not really matter what they tried - short of manually adjusting the 1099 amounts for each vendor - nothing seemed to make the correct numbers print on the report.

We reviewed all 10,000 vendor cards to make sure they had the proper 1099 type and box number selected - we caught a few mishaps here too.

After inspecting a few of the problem reports (and vendors), we narrowed down the issue to a number of payable invoices not having the proper 1099 amount and box type associated to the transactions. So to fix this issue, I wrote a few scripts which I hope you find useful.

Fix1099.sql

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

-- Update the 1099 amount, type, and box number on open payable records
UPDATE a SET a.TEN99AMNT = a.DOCAMNT
 , a.TEN99TYPE = b.TEN99TYPE
 , a.TEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM20000 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID) 
WHERE (b.TEN99TYPE <> 1) AND (a.DOCTYPE IN (1, 4, 5));

-- Update the 1099 amount, type, and box number on history payable records
UPDATE a SET a.TEN99AMNT = a.DOCAMNT
 , a.TEN99TYPE = b.TEN99TYPE
 , a.TEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM30200 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (b.TEN99TYPE <> 1) AND (a.DOCTYPE IN (1, 4, 5));

-- Update the 1099 amount, type, and box number on history apply records
UPDATE a SET a.TEN99AMNT = a.APPLDAMT
 , a.DEFTEN99TYPE = b.TEN99TYPE
 , a.DEFTEN99BOXNUMBER = b.TEN99BOXNUMBER
FROM PM30300 a INNER JOIN PM00200 b ON (a.VENDORID = b.VENDORID)
WHERE (b.TEN99TYPE <> 1) AND (a.APTODCTY IN (1, 4, 5));

Once the scripts are executed against the company database, you can then proceed to reconcile the 1099 amounts for the current calendar year - Microsoft Dynamics GP | Tools | Utilities | Purchasing | Reconcile. Note that the update is restricted to invoices (1), returns (4), and credit memos (5) document types.

Once we went through all of the above (including making sure the vendor cards were accurate), the reports printed fine, with the correct amounts, and in the correct boxes.

A few things to remember:

1. Execute all of the above procedures in a test environment, prior to executing in production. To create a test company from your production company follow the steps outlined in Microsoft Support KB article 871973.

2. The above steps and scripts specifically solved the issues with my client environment. You may have specifics within your environment that may prevent these scripts from being effective - I can't think of anything, but then again, test!

3. See the disclaimers on the right frame of this blog.

Until next post!

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

Read More

Granting Access and Binding Defaults when recreating SQL Tables: a follow up

In his most recent article, Granting Access and Binding Defaults when recreating SQL Tables, my good friend, David Musgrave, points out how to drop and re-create a Microsoft Dynamics GP object with the SQL Server Management Studio (SSMS) database object scripting capabilities.

His article overall addresses issues with objects not being properly upgraded from previous releases of Microsoft Dynamics GP to the version you are trying to upgrade to, and how to deal with these issue, so please read the articles as they contain valuable upgrade tips.

However, in his article, David points out that SSMS does not generate the object permissions and bindings necessary, hence the reason for the script he created and attached to his article. The bottom line is, these are configurable settings under SSMS's SQL Server Object Explorer options. To activate scripting of permissions and bindings in SSMS, follow these steps:

1. Open SSMS and go to the Tools | Options menu. This will open the Options window. Locate the SQL Server Object Explorer options and expand, then highlight Scripting to activate all scripting options.

SQL Server Management Studio Options window

2. Under Object Scripting Options, change Script Permissions to True.

Script permissions option

3. Under Table and view options, change Script bound defaults and rules to True.

Script bound defaults and rules

4. Click Ok to acknowledge all changes and exit the Options window.

Now let's test this... if we choose to script the SOP10110 table referenced in the sample script provided by David in his article, the following are the results:

USE [TWO]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK__SOP10110__ENDDAT__41320FDC]') AND parent_object_id = OBJECT_ID(N'[dbo].[SOP10110]'))
ALTER TABLE [dbo].[SOP10110] DROP CONSTRAINT [CK__SOP10110__ENDDAT__41320FDC]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK__SOP10110__STRTDA__42263415]') AND parent_object_id = OBJECT_ID(N'[dbo].[SOP10110]'))
ALTER TABLE [dbo].[SOP10110] DROP CONSTRAINT [CK__SOP10110__STRTDA__42263415]
GO

USE [TWO]
GO

/****** Object:  Table [dbo].[SOP10110]    Script Date: 12/05/2011 08:49:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SOP10110]') AND type in (N'U'))
DROP TABLE [dbo].[SOP10110]
GO

USE [TWO]
GO

/****** Object:  Table [dbo].[SOP10110]    Script Date: 12/05/2011 08:49:26 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[SOP10110](
 [PRCSHID] [char](15) NOT NULL,
 [DESCEXPR] [char](51) NOT NULL,
 [NTPRONLY] [tinyint] NOT NULL,
 [ACTIVE] [tinyint] NOT NULL,
 [STRTDATE] [datetime] NOT NULL,
 [ENDDATE] [datetime] NOT NULL,
 [NOTEINDX] [numeric](19, 5) NOT NULL,
 [PROMO] [tinyint] NOT NULL,
 [CURNCYID] [char](15) NOT NULL,
 [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKSOP10110] PRIMARY KEY NONCLUSTERED 
(
 [PRCSHID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[PRCSHID]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[DESCEXPR]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[NTPRONLY]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[ACTIVE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[SOP10110].[STRTDATE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[SOP10110].[ENDDATE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[SOP10110].[NOTEINDX]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[PROMO]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[CURNCYID]' , @futureonly='futureonly'
GO

GRANT DELETE ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT INSERT ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT SELECT ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT UPDATE ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

ALTER TABLE [dbo].[SOP10110]  WITH CHECK ADD CHECK  ((datepart(hour,[ENDDATE])=(0) AND datepart(minute,[ENDDATE])=(0) AND datepart(second,[ENDDATE])=(0) AND datepart(millisecond,[ENDDATE])=(0)))
GO

ALTER TABLE [dbo].[SOP10110]  WITH CHECK ADD CHECK  ((datepart(hour,[STRTDATE])=(0) AND datepart(minute,[STRTDATE])=(0) AND datepart(second,[STRTDATE])=(0) AND datepart(millisecond,[STRTDATE])=(0)))
GO

Note that the name of your constraints may be different, however note that all permissions are granted to the DYNGRP role and that all defaults are properly binded for each currency, date, char, and integer data types.

As usual and if nothing else, you now have 2 methods of dropping and creating SQL Server objects, granting permissions and binding defaults.

Until next post!

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

Read More

Copying Smartlists favorites from one user to another

I get this question asked very often and sometimes have to dig up my notes on the subject, so I figured I would just post the SQL script that I use for this.

Smartlist favorites are traditionally stored in the ASIEXP81 table on a user-company basis. It's important to keep this in mind, as you can tailor this script to copy favorites from one user to another in the same company or the same user across various companies. However, this script replicates all favorites, across all companies for one user to another user.

USE DYNAMICS;
GO
-- remove any records for the destination user prior to synchronizing
DELETE FROM ASIEXP81 WHERE USERID = '<DestinationUserID>';

-- execute synchronization
INSERT INTO ASIEXP81
           (ASI_Favorite_Dict_ID
           ,ASI_Favorite_Type
           ,ASI_Favorite_Save_Level
           ,CMPANYID
           ,USRCLASS
           ,USERID
           ,ASI_Favorite_Name
           ,ASI_Field_Dict_ID_1
           ,ASI_Field_Dict_ID_2
           ,ASI_Field_Dict_ID_3
           ,ASI_Field_Dict_ID_4
           ,ASI_Field_List_1
           ,ASI_Field_List_2
           ,ASI_Field_List_3
           ,ASI_Field_List_4
           ,ASI_Search_Type_1
           ,ASI_Search_Type_2
           ,ASI_Search_Type_3
           ,ASI_Search_Type_4
           ,ASI_Match_Case_1
           ,ASI_Match_Case_2
           ,ASI_Match_Case_3
           ,ASI_Match_Case_4
           ,ASI_Start_Account_Number_1_1
           ,ASI_Start_Account_Number_1_2
           ,ASI_Start_Account_Number_1_3
           ,ASI_Start_Account_Number_1_4
           ,ASI_Start_Account_Number_1_5
           ,ASI_Start_Account_Number_1_6
           ,ASI_Start_Account_Number_1_7
           ,ASI_Start_Account_Number_1_8
           ,ASI_Start_Account_Number_1_9
           ,ASI_Start_Account_Number_1_10
           ,ASI_Start_Account_Number_2_1
           ,ASI_Start_Account_Number_2_2
           ,ASI_Start_Account_Number_2_3
           ,ASI_Start_Account_Number_2_4
           ,ASI_Start_Account_Number_2_5
           ,ASI_Start_Account_Number_2_6
           ,ASI_Start_Account_Number_2_7
           ,ASI_Start_Account_Number_2_8
           ,ASI_Start_Account_Number_2_9
           ,ASI_Start_Account_Number_2_10
           ,ASI_Start_Account_Number_3_1
           ,ASI_Start_Account_Number_3_2
           ,ASI_Start_Account_Number_3_3
           ,ASI_Start_Account_Number_3_4
           ,ASI_Start_Account_Number_3_5
           ,ASI_Start_Account_Number_3_6
           ,ASI_Start_Account_Number_3_7
           ,ASI_Start_Account_Number_3_8
           ,ASI_Start_Account_Number_3_9
           ,ASI_Start_Account_Number_3_10
           ,ASI_Start_Account_Number_4_1
           ,ASI_Start_Account_Number_4_2
           ,ASI_Start_Account_Number_4_3
           ,ASI_Start_Account_Number_4_4
           ,ASI_Start_Account_Number_4_5
           ,ASI_Start_Account_Number_4_6
           ,ASI_Start_Account_Number_4_7
           ,ASI_Start_Account_Number_4_8
           ,ASI_Start_Account_Number_4_9
           ,ASI_Start_Account_Number_4_10
           ,ASI_Start_Date_Token_DDL_1
           ,ASI_Start_Date_Token_DDL_2
           ,ASI_Start_Date_Token_DDL_3
           ,ASI_Start_Date_Token_DDL_4
           ,ASI_Start_Date_1
           ,ASI_Start_Date_2
           ,ASI_Start_Date_3
           ,ASI_Start_Date_4
           ,ASI_Start_DDL_1
           ,ASI_Start_DDL_2
           ,ASI_Start_DDL_3
           ,ASI_Start_DDL_4
           ,ASI_String_Start_1
           ,ASI_String_Start_2
           ,ASI_String_Start_3
           ,ASI_String_Start_4
           ,ASI_End_Account_Number_1_1
           ,ASI_End_Account_Number_1_2
           ,ASI_End_Account_Number_1_3
           ,ASI_End_Account_Number_1_4
           ,ASI_End_Account_Number_1_5
           ,ASI_End_Account_Number_1_6
           ,ASI_End_Account_Number_1_7
           ,ASI_End_Account_Number_1_8
           ,ASI_End_Account_Number_1_9
           ,ASI_End_Account_Number_1_10
           ,ASI_End_Account_Number_2_1
           ,ASI_End_Account_Number_2_2
           ,ASI_End_Account_Number_2_3
           ,ASI_End_Account_Number_2_4
           ,ASI_End_Account_Number_2_5
           ,ASI_End_Account_Number_2_6
           ,ASI_End_Account_Number_2_7
           ,ASI_End_Account_Number_2_8
           ,ASI_End_Account_Number_2_9
           ,ASI_End_Account_Number_2_10
           ,ASI_End_Account_Number_3_1
           ,ASI_End_Account_Number_3_2
           ,ASI_End_Account_Number_3_3
           ,ASI_End_Account_Number_3_4
           ,ASI_End_Account_Number_3_5
           ,ASI_End_Account_Number_3_6
           ,ASI_End_Account_Number_3_7
           ,ASI_End_Account_Number_3_8
           ,ASI_End_Account_Number_3_9
           ,ASI_End_Account_Number_3_10
           ,ASI_End_Account_Number_4_1
           ,ASI_End_Account_Number_4_2
           ,ASI_End_Account_Number_4_3
           ,ASI_End_Account_Number_4_4
           ,ASI_End_Account_Number_4_5
           ,ASI_End_Account_Number_4_6
           ,ASI_End_Account_Number_4_7
           ,ASI_End_Account_Number_4_8
           ,ASI_End_Account_Number_4_9
           ,ASI_End_Account_Number_4_10
           ,ASI_End_Date_Token_DDL_1
           ,ASI_End_Date_Token_DDL_2
           ,ASI_End_Date_Token_DDL_3
           ,ASI_End_Date_Token_DDL_4
           ,ASI_End_Date_1
           ,ASI_End_Date_2
           ,ASI_End_Date_3
           ,ASI_End_Date_4
           ,ASI_End_DDL_1
           ,ASI_End_DDL_2
           ,ASI_End_DDL_3
           ,ASI_End_DDL_4
           ,ASI_String_End_1
           ,ASI_String_End_2
           ,ASI_String_End_3
           ,ASI_String_End_4
           ,ASI_Search_From_Str_1
           ,ASI_Search_From_Str_2
           ,ASI_Search_From_Str_3
           ,ASI_Search_From_Str_4
           ,ASI_Search_To_Str_1
           ,ASI_Search_To_Str_2
           ,ASI_Search_To_Str_3
           ,ASI_Search_To_Str_4
           ,ASI_Search_Logic_Type
           ,ASI_Max_Records
           ,ASI_Sort_Dict_ID
           ,ASI_Sort_Field
           ,ASI_Sort_Type
           ,ASI_Field_Comparison_1
           ,ASI_Field_Comparison_2
           ,ASI_Field_Comparison_3
           ,ASI_Field_Comparison_4
           ,ASI_Start_Comp_Field_ID_1
           ,ASI_Start_Comp_Field_ID_2
           ,ASI_Start_Comp_Field_ID_3
           ,ASI_Start_Comp_Field_ID_4
           ,ASI_Start_Comp_Field_Dic_1
           ,ASI_Start_Comp_Field_Dic_2
           ,ASI_Start_Comp_Field_Dic_3
           ,ASI_Start_Comp_Field_Dic_4
           ,ASI_End_Comp_Field_ID_1
           ,ASI_End_Comp_Field_ID_2
           ,ASI_End_Comp_Field_ID_3
           ,ASI_End_Comp_Field_ID_4
           ,ASI_End_Comp_Field_Dict_1
           ,ASI_End_Comp_Field_Dict_2
           ,ASI_End_Comp_Field_Dict_3
           ,ASI_End_Comp_Field_Dict_4)
SELECT ASI_Favorite_Dict_ID
      ,ASI_Favorite_Type
      ,ASI_Favorite_Save_Level
      ,CMPANYID
      ,USRCLASS
      ,'<DestinationUserID>'
      ,ASI_Favorite_Name
      ,ASI_Field_Dict_ID_1
      ,ASI_Field_Dict_ID_2
      ,ASI_Field_Dict_ID_3
      ,ASI_Field_Dict_ID_4
      ,ASI_Field_List_1
      ,ASI_Field_List_2
      ,ASI_Field_List_3
      ,ASI_Field_List_4
      ,ASI_Search_Type_1
      ,ASI_Search_Type_2
      ,ASI_Search_Type_3
      ,ASI_Search_Type_4
      ,ASI_Match_Case_1
      ,ASI_Match_Case_2
      ,ASI_Match_Case_3
      ,ASI_Match_Case_4
      ,ASI_Start_Account_Number_1_1
      ,ASI_Start_Account_Number_1_2
      ,ASI_Start_Account_Number_1_3
      ,ASI_Start_Account_Number_1_4
      ,ASI_Start_Account_Number_1_5
      ,ASI_Start_Account_Number_1_6
      ,ASI_Start_Account_Number_1_7
      ,ASI_Start_Account_Number_1_8
      ,ASI_Start_Account_Number_1_9
      ,ASI_Start_Account_Number_1_10
      ,ASI_Start_Account_Number_2_1
      ,ASI_Start_Account_Number_2_2
      ,ASI_Start_Account_Number_2_3
      ,ASI_Start_Account_Number_2_4
      ,ASI_Start_Account_Number_2_5
      ,ASI_Start_Account_Number_2_6
      ,ASI_Start_Account_Number_2_7
      ,ASI_Start_Account_Number_2_8
      ,ASI_Start_Account_Number_2_9
      ,ASI_Start_Account_Number_2_10
      ,ASI_Start_Account_Number_3_1
      ,ASI_Start_Account_Number_3_2
      ,ASI_Start_Account_Number_3_3
      ,ASI_Start_Account_Number_3_4
      ,ASI_Start_Account_Number_3_5
      ,ASI_Start_Account_Number_3_6
      ,ASI_Start_Account_Number_3_7
      ,ASI_Start_Account_Number_3_8
      ,ASI_Start_Account_Number_3_9
      ,ASI_Start_Account_Number_3_10
      ,ASI_Start_Account_Number_4_1
      ,ASI_Start_Account_Number_4_2
      ,ASI_Start_Account_Number_4_3
      ,ASI_Start_Account_Number_4_4
      ,ASI_Start_Account_Number_4_5
      ,ASI_Start_Account_Number_4_6
      ,ASI_Start_Account_Number_4_7
      ,ASI_Start_Account_Number_4_8
      ,ASI_Start_Account_Number_4_9
      ,ASI_Start_Account_Number_4_10
      ,ASI_Start_Date_Token_DDL_1
      ,ASI_Start_Date_Token_DDL_2
      ,ASI_Start_Date_Token_DDL_3
      ,ASI_Start_Date_Token_DDL_4
      ,ASI_Start_Date_1
      ,ASI_Start_Date_2
      ,ASI_Start_Date_3
      ,ASI_Start_Date_4
      ,ASI_Start_DDL_1
      ,ASI_Start_DDL_2
      ,ASI_Start_DDL_3
      ,ASI_Start_DDL_4
      ,ASI_String_Start_1
      ,ASI_String_Start_2
      ,ASI_String_Start_3
      ,ASI_String_Start_4
      ,ASI_End_Account_Number_1_1
      ,ASI_End_Account_Number_1_2
      ,ASI_End_Account_Number_1_3
      ,ASI_End_Account_Number_1_4
      ,ASI_End_Account_Number_1_5
      ,ASI_End_Account_Number_1_6
      ,ASI_End_Account_Number_1_7
      ,ASI_End_Account_Number_1_8
      ,ASI_End_Account_Number_1_9
      ,ASI_End_Account_Number_1_10
      ,ASI_End_Account_Number_2_1
      ,ASI_End_Account_Number_2_2
      ,ASI_End_Account_Number_2_3
      ,ASI_End_Account_Number_2_4
      ,ASI_End_Account_Number_2_5
      ,ASI_End_Account_Number_2_6
      ,ASI_End_Account_Number_2_7
      ,ASI_End_Account_Number_2_8
      ,ASI_End_Account_Number_2_9
      ,ASI_End_Account_Number_2_10
      ,ASI_End_Account_Number_3_1
      ,ASI_End_Account_Number_3_2
      ,ASI_End_Account_Number_3_3
      ,ASI_End_Account_Number_3_4
      ,ASI_End_Account_Number_3_5
      ,ASI_End_Account_Number_3_6
      ,ASI_End_Account_Number_3_7
      ,ASI_End_Account_Number_3_8
      ,ASI_End_Account_Number_3_9
      ,ASI_End_Account_Number_3_10
      ,ASI_End_Account_Number_4_1
      ,ASI_End_Account_Number_4_2
      ,ASI_End_Account_Number_4_3
      ,ASI_End_Account_Number_4_4
      ,ASI_End_Account_Number_4_5
      ,ASI_End_Account_Number_4_6
      ,ASI_End_Account_Number_4_7
      ,ASI_End_Account_Number_4_8
      ,ASI_End_Account_Number_4_9
      ,ASI_End_Account_Number_4_10
      ,ASI_End_Date_Token_DDL_1
      ,ASI_End_Date_Token_DDL_2
      ,ASI_End_Date_Token_DDL_3
      ,ASI_End_Date_Token_DDL_4
      ,ASI_End_Date_1
      ,ASI_End_Date_2
      ,ASI_End_Date_3
      ,ASI_End_Date_4
      ,ASI_End_DDL_1
      ,ASI_End_DDL_2
      ,ASI_End_DDL_3
      ,ASI_End_DDL_4
      ,ASI_String_End_1
      ,ASI_String_End_2
      ,ASI_String_End_3
      ,ASI_String_End_4
      ,ASI_Search_From_Str_1
      ,ASI_Search_From_Str_2
      ,ASI_Search_From_Str_3
      ,ASI_Search_From_Str_4
      ,ASI_Search_To_Str_1
      ,ASI_Search_To_Str_2
      ,ASI_Search_To_Str_3
      ,ASI_Search_To_Str_4
      ,ASI_Search_Logic_Type
      ,ASI_Max_Records
      ,ASI_Sort_Dict_ID
      ,ASI_Sort_Field
      ,ASI_Sort_Type
      ,ASI_Field_Comparison_1
      ,ASI_Field_Comparison_2
      ,ASI_Field_Comparison_3
      ,ASI_Field_Comparison_4
      ,ASI_Start_Comp_Field_ID_1
      ,ASI_Start_Comp_Field_ID_2
      ,ASI_Start_Comp_Field_ID_3
      ,ASI_Start_Comp_Field_ID_4
      ,ASI_Start_Comp_Field_Dic_1
      ,ASI_Start_Comp_Field_Dic_2
      ,ASI_Start_Comp_Field_Dic_3
      ,ASI_Start_Comp_Field_Dic_4
      ,ASI_End_Comp_Field_ID_1
      ,ASI_End_Comp_Field_ID_2
      ,ASI_End_Comp_Field_ID_3
      ,ASI_End_Comp_Field_ID_4
      ,ASI_End_Comp_Field_Dict_1
      ,ASI_End_Comp_Field_Dict_2
      ,ASI_End_Comp_Field_Dict_3
      ,ASI_End_Comp_Field_Dict_4
FROM ASIEXP81 WHERE USERID = '<SourceUserID>';

GO

In the above script <SourceUserID> and <DestinationUserID> are placeholders for the actual user IDs.

Until next post!

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

Read More

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

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/

Read More

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

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/

Read More

Running Fixed Assets Depreciation causes Microsoft Dynamics GP to "hang"

I just completed a full upgrade of Microsoft Dynamics GP from version 9 to version 2010 R2 for a client and they were going through their first month-end closing in the upgraded system. 3 weeks ago, after the upgrade, they reported experiencing an issue running Fixed Assets depreciation from two laptop computers, where apparently, when running depreciation the system would hang. The only option to recover would be to terminate the Dynamics.exe process from Task Manager. Nonetheless, we did not pay much attention to this at the time since the process was completed successfully from another machine, just in time to close the month of August - more on this later.

The client called back on Thursday morning, letting me know they were ready to run Fixed Assets depreciation again, and this time I offered to be onsite to see the problem first hand. So effectively this past Friday morning I drove to their location and stood behind the Sr. Accountant to see the process in action and spot any possible issues while there. The accountant proceeded to log into the company database for which he would run the depreciation, entered his September cutoff date and clicked on the Depreciate button... as luck would have it with some support cases, nothing happened and the process completed successfully. Well, after some chuckles and the typical apologies from the client, I was back in my car on the way home.

Fixed Assets - Depreciation Process Information


Halfway through, I received an email saying that as soon as I left, they logged into another company and were able to reproduce the hung up issue.

Now, I began playing all the typical troubleshooting plots in my head... the problem happens only in one company, the problem can be reproduced by all users, the problem can be reproduced on all machines. Typically, when an issue is constrained to one company, it's related to some problem with the data or the way that company is configured. Not a bad proposition since I was only dealing with some 300 assets... but I am in my car, remember? So I offered the client to look at the issue when I was back in front of my computer, since I had discarded a user or workstation being the culprits.

Back at home I VPN'd into their system, then RDP'd to the SQL Server. I had the Sr. Accountant log into GP and start the depreciation process again. In troubleshooting the issue, I could see that the depreciation process was being correctly added to the Process Monitor and that the process showed Active, but it did not seem to complete.

Process Monitor

I also ran a SQL Profiler and noticed that the same set of T-SQL instructions would appear to be processed over and over at the database level. This told me the depreciation process was in an endless loop of some kind and something was preventing it from finishing.

SQL Profiler Trace

I then offered to run the process from the server with the 'sa' user and noticed that the depreciation was stopping on a particular asset ID (by clicking on the Progress button). This was now promising, because I now had a piece of data to look at.

Fixed Assets Progress window

I queried the Asset Master table and noticed that this particular asset had an acquisition cost of zero. In looking at the Asset Book, I noticed that the Cost Basis was USD $.01 (1 penny). Not sure why this grabbed my attention, but I asked the Sr. Accountant why had they set this asset up this way and he replied that they did it only to record the asset and keep track of its location, but that it had been fully depreciated in the past.

Asset General Information

Asset Book

He also added that the process was working fine in GP 9.0

So I figured I would try something by changing the Depreciation Method to "No Depreciation". After all, if the asset had an acquisition cost of zero and a Cost Basis of 1 penny, what was there to depreciate? I ran the following statement to change the Depreciation Method to "No Depreciation":

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

-- Remove the lock for the book being depreciated
DELETE FROM FA40203;

-- Change the depreciation method
UPDATE FA00200 SET DEPRECIATIONMETHOD = 15 WHERE COSTBASIS <= .01;

I then asked the Sr. Accountant to re-run the process and this time it completed in less than 10 seconds and produced the reports he was expecting.

Since it was not enough to fix this issue, I went out to the Microsoft Dynamics GP Partner Online Technical forum and found a case where a partner reported having the same issue at her client's site. It seems Microsoft has identified and logged this as a problem report, but no concrete fix date has been given for it. So for now, the above query should do.

Also, you could end up with a cost basis of 1 penny at the end of the useful life of an asset, which would throw the system into an endless loop if you attempt to depreciate such assets once more. If you feel this is your case, the above script should also correct the problem.

Until next post!

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

Read More

Getting the Next Voucher Number for a Payables Transaction Integration

At the beginning of the year, I wrote a 2-part series on retrieving document numbers assigned by Microsoft Dynamics GP when a field rule is set to Use Default in Integration Manager, see:

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 1

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 2

Back then, I was addressing an issue faced by many working with integrations that require you to pass back some value to a source system and the complexities involved in retrieving those values, especially when the field rule is set to Use Default, this is, Microsoft Dynamics GP is assigning the field value, not the integration (via source file or otherwise).

Today, I am looking at a slightly different issue, nonetheless, related.

In this occasion, the client wanted to retrieve the next voucher number before hand for a payables transaction integration and assign it to the voucher number field, but did not want to have to write their own script. In essence, they wanted to leverage whatever mechanism built already in Microsoft Dynamics GP's business logic to get the next voucher number.


Payables Setup Options - Next Voucher Number field

 Indeed, writing their own code would involve retrieving the field value and incrementing the numeric part of the string. This sometimes can be a nightmare, especially when having to deal with record collisions and users accessing the system while the integration was running.

In doing some additional reading, I realized that eConnect already included this method, so all I had to do was find the SQL Server stored procedure to get the next voucher number. That stored procedure is conveniently named taGetPMNextVoucherNumber. One down, 2 more to go!

The second challenge with eConnect stored procedures is to determine the parameters that need to be passed in, but all eConnect stored procedures are created with encryption, so editing them was not an option. However, I remembered that in SQL Server Management Studio, you have the ability to execute a stored procedure from the Management Studio UI and that this would in effect display a window with the parameters, furthermore detailing data types and whether they are input or output type parameters.

Execute Stored Procedure option (Right-click)

Execute Procedure window

The good thing about this window is you can enter values for your input parameters and click on OK, and SQL Server will automatically generate a template for executing the stored procedure, with variable declarations, types, and all. The construct looks something like this:

USE [TWO]
GO

DECLARE @return_value int,
  @O_vCNTRLNUM varchar(21),
  @O_iErrorState int

EXEC @return_value = [dbo].[taGetPMNextVoucherNumber]
  @O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT,
  @I_sCNTRLTYP = 0,
  @O_iErrorState = @O_iErrorState OUTPUT

SELECT @O_vCNTRLNUM as N'@O_vCNTRLNUM',
  @O_iErrorState as N'@O_iErrorState'

SELECT 'Return Value' = @return_value
GO

This was fantastic, because now I did not have to struggle with understanding what needed to be passed in. It so happens that the control type parameter, @I_sCNTRLTYP, requires a zero to retrieve the next voucher number. In essence, I played with the parameter value and compared to what I was seeing in the GP interface (above), so here are the parameter values accepted for control type:

0 - Next Voucher Number
1 - Next Payment Number
2 - Next Alignment Number

Two down, 1 more to go.

Finally, the rest is putting the scripts together in Integration Manager to call the stored procedure.

As a best practice,  I tend to make the connections to the database persistent throughout the integration. This assures me that connections are only opened once, and closed at the end of the integration, improving the overall performance of the integration and reducing the points of failure. So, as you can imagine, a before document or a field script aren't the places to open and close connections, as these events occur over and over, based on the number of records being integrated.

I typically open the connection in the Before Integration event script, so this is what this script looks like:

' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.
' 
' Persisting ADO connection

Const adUseClient = 3
Dim oCn

Set oCn = CreateObject("ADODB.Connection")
With oCn
  .ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
  .CursorLocation = adUseClient
End With

GPConnection.Open(oCn)
SetVariable "gblConn", oCn

Note that the connection object is stored in a global variable, gblConn, using the SetVariable statement in Integration Manager.

Once we have the connection piece sorted out, now we can focus on adding code to the Voucher Number field script to call the eConnect stored procedure, as follows:

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Prepare the SQL statement and retrieve the next voucher number
Const adCmdStoredProc = 4
Const adVarchar       = 200
Const adInteger       = 3
Const adParamInput    = 1
Const adParamOutput   = 2
Const PMVoucher       = 0

Set oCmd = CreateObject("ADODB.Command")
With oCmd 
 .ActiveConnection = GetVariable("gblConn")
 .CommandType = adCmdStoredProc
 .CommandText = "taGetPMNextVoucherNumber" 'the eConnect stored proc
 
 .Parameters.Append .CreateParameter ("@O_vCNTRLNUM", adVarchar, adParamOutput, 25)
 .Parameters.Append .CreateParameter ("@I_sCNTRLTYP", adInteger, adParamInput)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)
 
 oCmd.Parameters("@I_sCNTRLTYP").Value = PMVoucher 
 .Execute 
 NextVoucher = oCmd.Parameters("@O_vCNTRLNUM").Value
 CurrentField.Value = NextVoucher
 'MsgBox NextVoucher
End With

Set oCmd = Nothing

Note how in this occasion, we are using the GetVariable function to retrieve a pointer to the connection object stored in the global variable. We then access the Parameters object to add the different parameters and set the value for the input parameter to the stored procedure.

When this script is executed within the context of the integration, it effectively returns the next voucher number for the transaction being integrated, from which you can proceed to update this information in your source system, if needed.

Note that by using standard Microsoft Dynamics GP business logic, your integration can now be supported if you need to open a support incident. Indeed another method for retrieving a document number for your transaction.

Until next post!

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

Read More

Cannot insert the value NULL into column 'CONTACT' error when clicking on Items List in Navigation Pane

Moving on from my previous article on a similar subject - see Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane, I recently came across this error, Cannot insert the value NULL into column 'CONTACT' when clicking on the All Purchasing Transactions list under the Purchasing Navigation Pane option, after performing an upgrade from Microsoft Dynamics GP 9.0 to Microsoft Dynamics GP 2010 R2.

All Purchasing Transactions list error - Purchasing Navigation List

The name of the global temp table - in this case, tempdb.dbo.##2093338- 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 Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that does not exist in the Vendor Master table (PM00200), 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 POP30300 WHERE VENDORID NOT IN (SELECT VENDORID FROM PM00200);

Once you have identified the record(s) causing the failure, you can use the Vendor Maintenance window to add the missing vendor or further study the issue to remove the offending receipts if necessary:

Vendor Maintenance window

Patrick Roth, Escalation Engineer with Microsoft and blogger at Developing for Dynamics GP, provides a full explanation of his troubleshooting method for this error at the Partner Online Technical Community forum:



GP2010 Purchasing List Error - Partner Online Technical Community forum


Until next post!

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

Read More

SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

As I begin to wrap up a Microsoft Dynamics GP 2010 R2 production upgrade from Microsoft Dynamics GP 9.0, I ran into a small issue at my client. After deploying the new SSRS reports, and as users were getting ready to try them out, we realized that some 15 logins needed to be assigned to a number of the 24 default database security roles created for the SSRS reports.

User Mappings (some information blurred to protect the client's identity)

This would be a bit cumbersome giving the share number of clicks required to accomplish this feat. In addition, we had just setup Microsoft Dynamics GP security, and given that the SSRS database roles were similar to those in GP, something needed to be done to automate the assignment of these roles based on Microsoft Dynamics GP security roles.

As a result, I created the following script:

-- Created by Mariano Gomez, MVP

--  This code is licensed under the Creative Commons 

--  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

use DYNAMICS;

go



DECLARE @userid varchar(50), @companyid varchar(5), @securityroleid varchar(200), @ssrsRole varchar(200);

DECLARE @sqlStmt varchar(255);



DECLARE c_reportsecurity CURSOR FOR 

	SELECT a.USERID, b.INTERID, a.SECURITYROLEID FROM SY10500 a

		LEFT OUTER JOIN SY01500 b ON (A.CMPANYID = b.CMPANYID)

	WHERE a.USERID not in ('sa', 'DYNSA', 'LESSONUSER1', 'LESSONUSER2') and a.SECURITYROLEID NOT LIKE ('MBS%')

	ORDER BY a.USERID;



OPEN c_reportsecurity;

FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;



WHILE @@FETCH_STATUS = 0

BEGIN

	SELECT @ssrsrole = 

		CASE 

			WHEN @securityroleid = 'ACCOUNTING MANAGER*      ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'AP CLERK*                ' THEN 'rpt_accounts payable coordinator'

			WHEN @securityroleid = 'AR CLERK*                ' THEN 'rpt_accounts receivable coordinator'

			WHEN @securityroleid = 'BOOKKEEPER*              ' THEN 'rpt_bookkeeper'

			WHEN @securityroleid = 'CA AGENT*                ' THEN ''

			WHEN @securityroleid = 'CA MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CA STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CERTIFIED ACCOUNTANT*    ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'CL AGENT*                ' THEN ''

			WHEN @securityroleid = 'CL DISPATCHER*           ' THEN 'rpt_dispatcher'

			WHEN @securityroleid = 'CL MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CL STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CUSTOMER SERVICE REP*    ' THEN 'rpt_customer service rep'

			WHEN @securityroleid = 'DP MANAGER*              ' THEN ''

			WHEN @securityroleid = 'DP STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'DP TECHNICIAN*           ' THEN ''

			WHEN @securityroleid = 'FA MANAGER*              ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'FA STAKEHOLDER*          ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'IT OPERATIONS MANAGER*   ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER ADMIN       ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER USER        ' THEN ''

			WHEN @securityroleid = 'OPERATIONS MANAGER*      ' THEN 'rpt_operations manager'

			WHEN @securityroleid = 'ORDER PROCESSOR*         ' THEN 'rpt_order processor'

			WHEN @securityroleid = 'PAYROLL CLERK*           ' THEN 'rpt_payroll'

			WHEN @securityroleid = 'PM AGENT*                ' THEN ''

			WHEN @securityroleid = 'PM MANAGER*              ' THEN ''

			WHEN @securityroleid = 'PM STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'POWERUSER                ' THEN 'rpt_power user'

			WHEN @securityroleid = 'PURCHASING AGENT*        ' THEN 'rpt_purchasing agent'

			WHEN @securityroleid = 'PURCHASING MANAGER*      ' THEN 'rpt_purchasing manager'

			WHEN @securityroleid = 'RT AGENT*                ' THEN ''

			WHEN @securityroleid = 'RT MANAGER*              ' THEN ''

			WHEN @securityroleid = 'RT STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'SHIPPING AND RECEIVING*  ' THEN 'rpt_shipping and receiving'

			WHEN @securityroleid = 'WAREHOUSE MANAGER*       ' THEN 'rpt_warehouse manager'

			WHEN @securityroleid = 'WENNSOFT SMS CONTRACTS*  ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS DISPATCHER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS POWER USER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS SETUP*      ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTANT*         ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTING MANAGER* ' THEN ''

			WHEN @securityroleid = 'WSJC ADMIN*              ' THEN ''

			WHEN @securityroleid = 'WSJC BILLING CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSJC POWERUSER*          ' THEN ''

			WHEN @securityroleid = 'WSJC PROJECT MANAGER*    ' THEN ''

			WHEN @securityroleid = 'WSTT PAYROLL CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSTT POWERUSER*          ' THEN ''

		END

	

	IF (@ssrsRole <> '') 

	BEGIN

		SET @sqlStmt = 'USE ' + rtrim(@companyid) + '; EXEC sp_addrolemember ' + QUOTENAME(@ssrsRole, '''') + ',' + QUOTENAME(rtrim(@userid), '''');

		EXEC(@sqlStmt);

	END

	FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;

END



CLOSE c_reportsecurity;

DEALLOCATE c_reportsecurity;

The script looks at the Security Assignment User Role table (SY10500) and retrieves the physical company database from the Company Master table (SY01500), then assign an SSRS database security role to each of the Microsoft Dynamics GP default roles. If a role does not exist, you can choose to leave the assignment blank.

The script then proceeds to evaluate the database security role obtained, then creates a SQL string that can be executed. The SQL string uses the sp_addrolemember system stored procedure to add the corresponding SQL login to the role. A cursor is used to loop through each user, company, and security role combination to obtain and assign the proper SSRS database role.

You can choose to add custom security roles or roles for third party applications that deploy their own SSRS reports to the above script.

This definitely helped saving some time... phew!

Until next post!

MG.-

Mariano Gomez, MVP

IntellPartners, LLC

http://www.IntellPartners.com/

Read More