Aaron Berquist Rotating Header Image

July, 2011:

Populate Rounding Account On Multicurrency Payables Transactions Created Via eConnect

Jul 15th, 2011
by Aaron Berquist.
4 comments

If you create multicurrency payables (PM) transactions using eConnect, and you are passing in the distributions manually (by setting CREATEDIST to 0), you may have encountered the same issue I encountered today.

When the transaction was created, eConnect also (correctly) added in a ROUND distribution, to account for 0.01 cents worth of FX related rounding on the transaction. Unfortunately, eConnect did not populate the distribution with an account. This struck me as strange – if you manually create a voucher in Dynamics GP and rounding is encountered, a ROUND distribution is created, and the account is populated (with the Rounding Difference Account, which is set up under Tools: Setup: Posting: Posting Accounts).

I should also mention – there is a specific order that should be followed when creating a multicurrency payables document – create the distributions first, then create the header record. Details are in VSTools thread here.

To resolve this issue, I entered some code into the taPMTransactionInsertPost procedure to retrieve the account and populate the distribution. The code snippet is below, or you can download it here.

/****** Object:  StoredProcedure [dbo].[taPMTransactionInsertPost]    Script Date: 07/15/2011 09:48:57 ******/
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[taPMTransactionInsertPost]')
                    AND type IN ( N'P', N'PC' ) )
    DROP PROCEDURE [dbo].[taPMTransactionInsertPost]
GO
 
/****** Object:  StoredProcedure [dbo].[taPMTransactionInsertPost]    Script Date: 07/15/2011 09:48:57 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
CREATE PROCEDURE [dbo].[taPMTransactionInsertPost]
    @I_vBACHNUMB CHAR(15) ,
    @I_vVCHNUMWK CHAR(17) ,
    @I_vVENDORID CHAR(15) ,
    @I_vDOCNUMBR CHAR(20) ,
    @I_vDOCTYPE SMALLINT ,
    @I_vDOCAMNT NUMERIC(19, 5) ,
    @I_vDOCDATE DATETIME ,
    @I_vPSTGDATE DATETIME ,
    @I_vVADCDTRO CHAR(15) ,
    @I_vVADDCDPR CHAR(15) ,
    @I_vPYMTRMID CHAR(20) ,
    @I_vTAXSCHID CHAR(15) ,
    @I_vDUEDATE DATETIME ,
    @I_vDSCDLRAM NUMERIC(19, 5) ,
    @I_vDISCDATE DATETIME ,
    @I_vPRCHAMNT NUMERIC(19, 5) ,
    @I_vCHRGAMNT NUMERIC(19, 5) ,
    @I_vCASHAMNT NUMERIC(19, 5) ,
    @I_vCAMCBKID CHAR(15) ,
    @I_vCDOCNMBR CHAR(20) ,
    @I_vCAMTDATE DATETIME ,
    @I_vCAMPMTNM CHAR(20) ,
    @I_vCHEKAMNT NUMERIC(19, 5) ,
    @I_vCHAMCBID CHAR(15) ,
    @I_vCHEKDATE DATETIME ,
    @I_vCAMPYNBR CHAR(20) ,
    @I_vCRCRDAMT NUMERIC(19, 5) ,
    @I_vCCAMPYNM CHAR(20) ,
    @I_vCHEKNMBR CHAR(20) ,
    @I_vCARDNAME CHAR(15) ,
    @I_vCCRCTNUM CHAR(20) ,
    @I_vCRCARDDT DATETIME ,
    @I_vCHEKBKID CHAR(15) ,
    @I_vTRXDSCRN CHAR(30) ,
    @I_vTRDISAMT NUMERIC(19, 5) ,
    @I_vTAXAMNT NUMERIC(19, 5) ,
    @I_vFRTAMNT NUMERIC(19, 5) ,
    @I_vTEN99AMNT NUMERIC(19, 5) ,
    @I_vMSCCHAMT NUMERIC(19, 5) ,
    @I_vPORDNMBR CHAR(20) ,
    @I_vSHIPMTHD CHAR(15) ,
    @I_vDISAMTAV NUMERIC(19, 5) ,
    @I_vDISTKNAM NUMERIC(19, 5) ,
    @I_vAPDSTKAM NUMERIC(19, 5) ,
    @I_vMDFUSRID CHAR(15) ,
    @I_vPOSTEDDT DATETIME ,
    @I_vPTDUSRID CHAR(15) ,
    @I_vPCHSCHID CHAR(15) ,
    @I_vFRTSCHID CHAR(15) ,
    @I_vMSCSCHID CHAR(15) ,
    @I_vPRCTDISC NUMERIC(19, 2) ,
    @I_vTax_Date DATETIME ,
    @I_vCURNCYID CHAR(15) ,
    @I_vXCHGRATE NUMERIC(19, 7) ,
    @I_vRATETPID CHAR(15) ,
    @I_vEXPNDATE DATETIME ,
    @I_vEXCHDATE DATETIME ,
    @I_vEXGTBDSC CHAR(30) ,
    @I_vEXTBLSRC CHAR(50) ,
    @I_vRATEEXPR SMALLINT ,
    @I_vDYSTINCR SMALLINT ,
    @I_vRATEVARC NUMERIC(19, 7) ,
    @I_vTRXDTDEF SMALLINT ,
    @I_vRTCLCMTD SMALLINT ,
    @I_vPRVDSLMT SMALLINT ,
    @I_vDATELMTS SMALLINT ,
    @I_vTIME1 DATETIME ,
    @I_vBatchCHEKBKID CHAR(15) ,
    @I_vCREATEDIST SMALLINT ,
    @I_vRequesterTrx SMALLINT ,
    @I_vUSRDEFND1 CHAR(50) ,
    @I_vUSRDEFND2 CHAR(50) ,
    @I_vUSRDEFND3 CHAR(50) ,
    @I_vUSRDEFND4 VARCHAR(8000) ,
    @I_vUSRDEFND5 VARCHAR(8000) ,
    @O_iErrorState INT OUTPUT ,
    @oErrString VARCHAR(255) OUTPUT
AS 
 
	/*
	AUTHOR: AARON BERQUIST
	DATE: 07/15/2011
	PURPOSE: POPULATE THE "ROUND" DISTRIBUTION AUTOMATICALLY CREATED WHEN
	A MULTICURRENCY PM TRANSACTION IS CREATED USING eCONNECT.
 
	BACKGROUND: WHEN CREATING A MULTICURRENCY PM TRANSACTION USING eCONNECT,
	IF THE "CREATEDIST" FLAG IS SET TO 0 AND DISTRIBUTIONS ARE BEING PASSED IN MANUALLY,
	THE SYSTEM WILL CORRECTLY CREATE A "ROUND" DISTRIBUTION (DISTTYPE = 16) IN THE PM10100 TABLE.
	UNFORTUNATELY, IT APPEARS THAT eCONNECT WILL NOT POPULATE THE DISTRIBUTION WITH THE ACCOUNT
	FROM THE ROUNDING DIFFERENCE ACCOUNT SETUP. IF YOU ENTER THE SAME TRANSACTION MANUALLY
	THROUGH THE GP APPLICATION, THE ACCOUNT IS AUTOMATICALLY POPULATED.
 
	TESTED ON DYNAMICS GP 10.00.1257.	
 
	*/
    SET nocount ON
    SELECT  @O_iErrorState = 0
 
    DECLARE @ROUNDINDEX INT
 
    --CHECK TO SEE IF THE INTEGRATION CREATED A ROUND ENTRY
    --IF SO, eCONNECT DOES NOT PICK THE DEFAULT ROUNDING ACCOUNT IF YOU CHOOSE TO MANUALLY CREATE DISTRIBUTIONS
    --SO THE POST PROCEDURE WILL READ THE ACCOUNT FROM POSTING ACCOUNT SETUP (TOOLS: SETUP: POSTING: POSTING ACCOUNTS: ROUNDING DIFFERENCE ACCOUNT)
    --AND POPULATE THE DISTRIBUTION
    IF EXISTS (SELECT 1 FROM PM10100 WHERE VCHRNMBR = @I_vVCHNUMWK AND DISTTYPE = 16 AND DSTINDX = 0)
    BEGIN
 
		--ROUNDING DIFFERENCE ACCOUNT IS SERIES 2, SEQNUMBR 900
		SELECT @ROUNDINDEX = ACTINDX FROM SY01100 WHERE SERIES = 2 AND SEQNUMBR = 900
		UPDATE PM10100 SET DSTINDX = ISNULL(@ROUNDINDEX,0) WHERE VCHRNMBR = @I_vVCHNUMWK AND DISTTYPE = 16 AND DSTINDX = 0
 
    END
 
    RETURN (@O_iErrorState)
GO
 
GRANT EXECUTE ON [dbo].[taPMTransactionInsertPost] TO [DYNGRP] AS [dbo]
GO

Post to Twitter

Use Dynamic SQL To Generate a Dynamics GP Login Macro

Jul 7th, 2011
by Aaron Berquist.
7 comments

If you run Dynamics GP and have more than a handful of companies, I am sure you have felt the pain of installing or upgrading a 3rd party product, and then having to log in to each company as “sa” to complete the install process. I had to do this recently for a new 3rd party we are using (SmartView from eOne Solutions). Once the install is complete, a log in to each database as “sa” is required to create the necessary tables to support the tool.

Wanting to avoid the aggravation (and tedium) of repeating this process for the 150+ company databases we have, I wrote the SQL script below to generate a Dynamics GP macro which can be played which will log in to each database in sequence without needing any intervention.

It took me a while to figure out how the get the right “Item” number into the macro. I first thought it was the CMPANYID value from the SY01500 table, but that wasn’t right. Next I tried the DEX_ROW_ID value, but that wasn’t it either. Some trial an error finally yielded the right formula. The Item number is the index position in the dropdown list, ordered by CMPANYID! Despite the fact that the dropdown list displays items ordered by CMPNYNAM (in other words, alphabetically), it appears to first populate the list with the items ordered by CMPANYID and assigns them the item number based on that sequencing, then reorders the list to display by CMPANYNM.

/*
AUTHOR: AARON BERQUIST
DATE: 07/07/2011
PURPOSE: THE SCRIPT BELOW WILL GENERATE A DYNAMICS GP MACRO WHICH WILL LOG IN TO EACH COMPANY IN SEQUENCE.
USEFUL WHEN YOU HAVE INSTALLED A NEW 3RD PARTY PRODUCT, OR A SERVICE PACK,
AND EACH DATABASE NEEDS TO BE INITIALIZED BY LOGGING IN AS "SA".
 
TESTED WITH DYNAMICS GP 10, BUILD 10.00.1257
 
OVERVIEW:
 
THE SCRIPT BELOW BUILDS A MACRO WHICH CAN BE RUN IN DYNAMICS GP.
DEPENDING ON THE NUMBER OF COMPANIES YOU HAVE, THE OUTPUT OF THE DYNAMIC SQL BELOW WILL BE TOO LARGE TO BE
FULLY DISPLAYED IN THE RESULTS PANE OF MANAGEMENT STUDIO.
 
THEREFORE, THE DYNAMIC SQL CODE IS INSERTED INTO THE TABLE __MACRO.
 
USE MANAGEMENT STUDIO TO EXPORT THE CONTENTS OF THE TABLE, WITHOUT THE HEADER ROW, INTO A FLAT FILE WITH AN EXTENTION OF .MAC
THE FILE CAN THEN BE PLAYED INSIDE DYNAMICS GP.
 
USAGE:
 
1. EXECUTE THE SCRIPT BELOW - IT USES DYNAMIC SQL TO BUILD A LOGIN MACRO FOR ALL DYNAMICS GP DATABASES.
2. THE CONTENTS OF THE SCRIPT ARE INSERTED INTO THE TABLE __MACRO.
3. EXPORT THE CONTENTS OF THE TABLE TO A FLAT FILE, WITH A .MAC EXTENSION.
4. LOG IN TO ANY DATABASE AS "SA" AND RUN THE MACRO. THE MACRO WILL LOG IN TO EACH DATABASE IN ALPHABETICAL SEQUENCE.
 
POTENTIAL MODIFICATIONS:
 
WHEN INSTALLING SOME 3RD PARTY PRODUCTS, YOU MAY NEED TO CLICK "OK" ON A WINDOW AT LOGIN TO INSTALL, OR PERFORM SOME REPETITIVE TASKS.
THE MACRO CODE BELOW CAN BE MODIFIED TO SUIT YOUR NEEDS. THE "SPECIAL SAUCE" IS THE ROW_NUMBER() OVER CMPANYID.
 
*/
USE DYNAMICS
GO
DECLARE @ssQL VARCHAR(MAX) = ''
 
SELECT @sSQL = @sSQL+'  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company''
NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company''
  ClickHit field ''(L) Company Names'' item '+CONVERT(VARCHAR(3),ROW_NUMBER() OVER (order BY CMPANYID))+'  # '+LTRIM(RTRIM(CMPNYNAM))+'
  MoveTo field ''OK Button''
  ClickHit field ''OK Button''
NewActiveWin dictionary ''default''  form sheLL window sheLL
NewActiveWin dictionary ''default''  form sheLL window sheLL
ActivateWindow dictionary ''default''  form sheLL window sheLL '+CHAR(13)
FROM DYNAMICS..SY01500
ORDER BY CMPNYNAM
 
PRINT (@sSQL)
 
/****** Object:  Table [dbo].[__Macro]    Script Date: 07/07/2011 21:41:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[__Macro]') AND type in (N'U'))
DROP TABLE [dbo].[__Macro]
GO
 
/****** Object:  Table [dbo].[__Macro]    Script Date: 07/07/2011 21:41:48 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[__Macro](
	[ssql] [varchar](max) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
INSERT __MAcro SELECT @ssQL
 
--NOW EXPORT THE CONTENTS OF THE TABLE TO A FLAT FILE AND RUN IN GP.
--YOU CAN THEN SAFELY DROP THE TABLE __MACRO

You can either copy and paste the code above, or download a script file here.

Post to Twitter

Using SmartList Builder With A Linked Server – It Can Be Done!

Jul 4th, 2011
by Aaron Berquist.
18 comments

We are looking to deploy SmartList Builder + SmartView as an enhanced reporting solution for our internal customers. One of the things I have seen floating around the net is that you can’t use SmartList Builder with a SQL Linked Server. In fact, a number of Dynamics MVP’s alluded to this in a support thread at http://www.ms-news.net/f1610/smartlist-builder-odbc-error-8692687.html. The actual error message will look something like this:

GPS Error 58

SQL Error 7405 ODBC SQL Server Driver

Heterogeneous queries require the ANSI_NULL and ANSI_Warnings options to be set for the  connection.
This ensures consistent query semantics. Enable these options and
then reissue your query.

ODBC error 37000

I’ll show a little further into the article that yes, you can work around this, and pretty easily too – it just takes a little sleight-of-hand with your SmartLists.

A little background – we have 150+ Dynamics GP companies, all on one database server. We also frequently have requests to produce reports which consolidate data from all of these entities onto one SmartList so that, in one report, users can quickly see data from every entity. Up until now, we have been using the technique I outlined here to create reports which use a linked server to execute a stored procedure inside a view using OPENQUERY. In order to make this work, I had to come up with a workaround to the very ANSI_NULLs issue other people have reported as stopping them in their tracks.

The secret boils down to this – you have to trick AnyView, or SmartList Builder into accepting the view the first time you add it. Once you do that, and build your SmartList, you can change what the view is actually doing after the fact. I know that it works with AnyView, and have done a test with SmartList builder and it works there as well.

For the steps below, assume the final query you want to use looks something like the code below. Column1 is varchar(5) and Column2 is TINYINT:

select Column1, Column2 from LINKEDSERVER.SAMPLEDB.dbo.SAMPLETABLE

Here are the steps you need to follow:

Create a SQL view with the columns and field lengths that match the “final” report you want to produce:

CREATE VIEW SampleView
 
AS
 
SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2
 
GO
 
GRANT SELECT ON SampleView TO DYNGRP

Add that view to SmartList Builder / AnyView and build your SmartList off that view.

Once you have successfully built the object, ALTER the view to call the code from the linked server:

ALTER VIEW SampleView
 
AS
 
--SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2
select Column1, Column2 from LINKEDSERVER.SAMPLEDB.dbo.SAMPLETABLE

 

Voila! Your SmartList will run the linked server code, with no ANSI_NULL warnings!

I have used this approach for most of the SmartLists we have in Production today, with great success. Of course, using a linked server is not, in the end, an ideal solution, but for cases when you have to, the method above will allow you to use SmartLists with SQL Linked Servers successfully.

Note: I have updated the sample code above to include the “GRANT SELECT” permissions to “DYNGRP” on the view. If you don’t do this, SmartView will spin forever on the “Loading…” screen when you try to run a SmartList.

Post to Twitter