SQL Script To Return Functional Currencies For All Companies

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPI have done a fair bit of work recently for clients with the Web Services for Microsoft Dynamics GP. One of the checks the configuration tool does is to check if all of the companies have a functional currency defined.

However, if it returns a fail flag for this check, it doesn’t actually tell you which company (or companies) has failed the check. And when your client has well over a hundred companies you need an alternative method to going through each one manually.

That alternative method is the script below; it uses a cursor to look through the company databases and returns a list of all companies and their functional currency. To only see those companies without a functional currency, there is a where clause at the bottom which is currently commented out.

CREATE TABLE #FUNLCURR
	(
	INTERID VARCHAR(5)
	,FUNLCURR VARCHAR(15)
	)

DECLARE @SQL_Statement VARCHAR(1000)

DECLARE
	cursor_InterID CURSOR 
FOR
	SELECT INTERID FROM SY01500
	
	OPEN cursor_InterID

	DECLARE
		@INTERID VARCHAR(100)

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@INTERID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
				SET @SQL_Statement = 'INSERT INTO #FUNLCURR (INTERID,FUNLCURR) VALUES (''' + @INTERID + ''','''')'
				exec (@SQL_Statement)
				SET @SQL_Statement = 'UPDATE #FUNLCURR SET FUNLCURR = (SELECT FUNLCURR FROM ' + RTRIM(@INTERID) + '.dbo.MC40000 MC) WHERE INTERID = ''' + @INTERID + ''''
				exec (@SQL_Statement)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@INTERID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

SELECT
	#FUNLCURR.INTERID
	,SY01500.CMPNYNAM
	,#FUNLCURR.FUNLCURR
FROM
	#FUNLCURR
INNER JOIN
	SY01500
		ON SY01500.INTERID = #FUNLCURR.INTERID
/*WHERE
	LEN(FUNLCURR) = ''*/
ORDER BY
	#FUNLCURR.INTERID

DROP TABLE #FUNLCURR
● Categories: Dynamics, GP, Microsoft, Multicurrency, Web Services ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 R2: Web Services Verification

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 was released at the end of May. As I mentioned at the time, I was away from home for work and rather busy otherwise so this series of posts has been somewhat delayed.

However, I am now getting my hands on GP 2015 R2 and thought I would do my usual set of posts on the installation of the various components and also the usage of the new functionality. This is going to be quite a long series of posts, so keep checking back regularly.

Once the Web Services have been installed and configured, it is recommend that they be verified to ensure everything is working as it should. There are four items which need to be checked.

Firstly, open the Services Control Panel applet (easiest to do by hitting Win+R and typing services.msc; scroll down and find Microsoft Dynamics GP Service Host. Ensure it is running and set to Automatic:

Services

Continue reading → Hands On With Microsoft Dynamics GP 2015 R2: Web Services Verification

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 R2: Web Services Configuration

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 was released at the end of May. As I mentioned at the time, I was away from home for work and rather busy otherwise so this series of posts has been somewhat delayed.

However, I am now getting my hands on GP 2015 R2 and thought I would do my usual set of posts on the installation of the various components and also the usage of the new functionality. This is going to be quite a long series of posts, so keep checking back regularly.

With the web services installed, the next step is to configure them for the companies being used. Do this by launching the Web Services Configuration Wizard from the Start screen.

On the Welcome screen, click Next:

Web Services for Microsoft Dynamics GP Configuration Wizard: Welcome

Continue reading → Hands On With Microsoft Dynamics GP 2015 R2: Web Services Configuration

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 R2: Install Web Services

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 was released at the end of May. As I mentioned at the time, I was away from home for work and rather busy otherwise so this series of posts has been somewhat delayed.

However, I am now getting my hands on GP 2015 R2 and thought I would do my usual set of posts on the installation of the various components and also the usage of the new functionality. This is going to be quite a long series of posts, so keep checking back regularly.

In the past, we used the Web Services for Microsoft Dynamics GP mainly for integration, but since Microsoft Dynamics GP 2013 R2 introduced Workflow 2.0 we have been installing it for more and more clients.

In this, the fifteenth post of the Hands on with Microsoft Dynamics GP 2015 R2 series, I’m going to step through the installation process and following it up in the next couple of posts with the configuration and verification of the installation.

To install the Web Services for Microsoft Dynamics GP launch the setup.exe from the downloaded installation media and, under Additional Products click Web Services Runtime:

Microsoft Dynamics GP setup utility

Continue reading → Hands On With Microsoft Dynamics GP 2015 R2: Install Web Services

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Management Tools Installation

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

In the last few posts, I’ve covered the installation and configuration of the web services and rounded off with verifying that they are working correctly. The final item I’m going to cover is the optional installation of the Web Services Management Tools; which means the installing the Dynamics Security Console on another machine. Doing this is best practice as it means you don’t have to give access to the server running the web services themselves to all users who might maintain security.

To install the Web Services Management Tools, open the setup.exe from the installation media and, under Additional Products, select Web Services Management Tools:

Microsoft Dynamics GP 2015

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Management Tools Installation

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Security Verification

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

In the last post, we verified that the web services service was running and that the web services were working; in this post we’re going to verify the security was correctly deployed.

To do this open the Dynamics Security Console:

Windows Start screen

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Security Verification

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Service Verification

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

After installing and configuring the web services, I would always recommend verifying the install to ensure they are working correctly.

The first step, is to check the Windows Service is running; to do this open the Services control panel applet (which can also be accessed by using Win+R and typing services.msc).

Scroll down the list and find Microsoft Dynamics GP Service Host and make sure it is Running and set to Automatic:

Services

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Service Verification

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Configuration

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

With the web services installed, the next step is to configure them using the Web Services for Microsoft Dynamics GP Configuration Wizard. Launch the wizard and click Next on the welcome screen:

Web Services for Microsoft Dynamics GP Configuration Wizard - Welcome to the Web Services for Microsoft Dynamics GP Configuration Wizard

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Configuration

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Installation

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

With the web client, installed and tested it’s time to move onto the next of the additional tools that come with Dynamics GP 2015. As I intend to make use of the Workflow 2.0 I need to have the web services installed to be able to make use of the email actions.

So the next five posts will look at the installation and configuration of the web services. To begin the installation of the web services, open the setup utility and select :

Micvrosoft Dynamics GP 2015

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Installation

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 Feature of the Day: Vendor Approval

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

The fourth Feature of the Day is Vendor Approval. This feature is an enhancement to the new Workflow 2.0 module introduced in Microsoft Dynamics GP 2013 R2 which allows users to control the setup and maintenance of vendors.

Vendor Approval allows users to create mandatory workflow processes around the setup and maintenance of vendors. These mandatory processes need to be completed before transactions can be posted. The workflow processes are defined in Workflow Maintenance (Administration area page ¯ Setup ¯ Company ¯ Workflow ¯ Workflow Maintenance):

Workflow Maintenance

The workflow status appears on the window and actions can be taken directly from those windows, from an email or from a Navigation List.

I want to see more details about this feature as I am very much hoping that the workflow has been extended to include the EFT Bank details.

The new Workflow 2.0 is far, far simpler and easier to implement and use.

The roles targeted by this feature are:

  • All

Click to show/hide MDGP 2015 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●