Copy Fiscal Calendar To All Companies

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPIn the last post I mentioned that I create scripts when implementing Microsoft Dynamics GP and then make them available to clients when they would be of benefit. The last script I posted, allowed calendars to ve copied from one database to another. Afetr writing that scirpt I then adapted it by wrapping a cursor around it which allowed a source database to be defined and the calendar copied to all other databases.

The original script is still useful as it allows a targeted copying of calendars from a source to a destination database, but the new allows calendars to be quickly replicated across all companies if they share the same financial year.

The script has two parameters (highlighted) at the top which need to be set:

  • Year
  • SourceDatabase
  • Continue reading → Copy Fiscal Calendar To All Companies

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

    Copy Fiscal Calendar To A New Company

    ● Ian Grieve ●  ● 5 Comments  ● 

    Microsoft Dynamics GPI have a bit of catching up to do on writing posts, but to start the year off I am back to some SQL scripts. As I have mentioned before I have a number of clients with multiple, and in some cases well over 100, companies in Microsoft Dynamics GP.

    I do try to make life easier for them when possible so have been sharing a number of scripts I have for implementing systems. One such script is one which will copy financial (fiscal) calendars from one database to another.

    There are three parameters at the top which need to be set before running the script:

    1. Year
    2. Source Company
    3. Destination Company

    These parameters are highlighted below:

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    DECLARE @Year VARCHAR(4) = '2014'
    DECLARE @SourceDatabase VARCHAR(5) = 'TWO'
    DECLARE @DestinationDatabase VARCHAR(5) = 'THREE'
    DECLARE @SQLStatement VARCHAR(1000)
    
    SET @SQLStatement =
    	'INSERT INTO ' + @DestinationDatabase + '..SY40101
    		(YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR)
    	--VALUES
    		(SELECT
    			YEAR1,FSTFSCDY,LSTFSCDY,NUMOFPER,HISTORYR
    		FROM
    			' + @SourceDatabase + '..SY40101
    		WHERE
    			(SELECT
    				COUNT(YEAR1)
    			FROM
    				' + @DestinationDatabase + '..SY40101
    			WHERE
    				YEAR1 = ' + @Year + ') = 0
    		AND
    			YEAR1 = ' + @Year + ')'
    EXEC (@SQLStatement)
    
    SET @SQLStatement =
    	'INSERT INTO ' + @DestinationDatabase + '..SY40100
    		(CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
    		,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
    		YEAR1,PERDENDT)
    	--VALUES
    		(SELECT
    			CLOSED,SERIES,ODESCTN,FORIGIN,PERIODID,PERIODDT,PERNAME
    			,PSERIES_1,PSERIES_2,PSERIES_3,PSERIES_4,PSERIES_5,PSERIES_6,
    			YEAR1,PERDENDT
    		FROM
    			' + @SourceDatabase + '..SY40100
    		WHERE
    			(SELECT
    				COUNT(YEAR1)
    			FROM
    				' + @DestinationDatabase + '..SY40100
    			WHERE
    				YEAR1 = ' + @Year + ') = 0
    			AND
    				YEAR1 = ' + @Year + ')'
    EXEC (@SQLStatement)
    GO

    The calendar will only be copied if the destination database does not contain a calendar with the same name.

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

    Changing The Logical File Names Of A SQL Database

    ● Ian Grieve ●  ● 3 Comments  ● 

    Microsoft Dynamics GPWhen a database is created, it has a logical name assigned to it which will match the physical name. However, when working with Microsoft Dynamics GP, we often create both a live and test database and then populate the settings in the live database and replicate over the top of the test one.

    Or on occasion we have created a template database which then gets copied when a new company is created (this method is often used for clients who have a substantial amount of setup in third party modules which the PSTL Company Copy doesn’t cater for.

    The problem with both of these is that when a database is restored elsewhere it brings it’s logical name with it; meaning a mismatch between the logical and physical names which causes problems when backing up and restoring databases.

    However, all is not lost; it is possible to change the logical name of a database using a simple SQL script. The script, below, has two ALTER DATABASE commands, one for the data file and the other for the log file.

    I am changing the logical name from GPST15R2 to GPSP15R2 on both files (see highlighted text):

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    ALTER DATABASE
    	P15R2
    MODIFY FILE 
    	(
    	NAME = [GPST15R2Dat.mdf]
    	,NEWNAME = [GPSP15R2Dat.mdf]
    	)
     GO
     ALTER DATABASE
    	P15R2
    MODIFY FILE 
    	(
    	NAME = [GPST15R2Log.ldf]
    	,NEWNAME = [GPSP15R2Log.ldf]
    	)
    GO

    As always when running a SQL script against a database, make sure you have a good backup and perform a test afterward to make sure there are no problems.

    ● Categories: Dynamics, GP, Microsoft, SQL Server ● Tags: , , , ,  ● Permalink ● Shortlink ●

    SQL Trigger To Delete EFT Bank Information When The Vendor Is Deleted

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPA while ago I came across a bug in the Creditor (or Vendor for my American readers) Maintenance window where when a creditor is deleted the EFT information is not deleted; this has caused problems for a number of clients and I finally decided I needed to do something about it.

    The result is a SQL trigger on the Creditor Master (PM00200) table; when a creditor is deleted the trigger runs and deletes all records in the Address Electronic Transfer Funds Master (SY06000) table are then deleted:

    CREATE TRIGGER dbo.utr_AZRCRV_DeleteSY06000 ON dbo.PM00200 AFTER Delete AS
    	/*
    	Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    	This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    	*/
    	DELETE
    		['Address Electronic Transfer Funds Master']
    	FROM
    		SY06000 AS ['Address Electronic Transfer Funds Master']
    	INNER JOIN
    		deleted
    			ON
    				deleted.VENDORID = ['Address Electronic Transfer Funds Master'].VENDORID
    GO

    This the avoids the possibility of a new creditor record being linked to a different creditors bank details. As always with a script (especially one which deletes information, make sure it is tested and you’re happy with how it works before releasing live.

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

    SQL View To Allow Customer/Item Link In SmartList Builder

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

    As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):

    
    CREATE VIEW uv_AZRCRV_CustomerItemLink AS
    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    	SELECT
    		SOP102.SOPNUMBE
    		,SOP102.SOPTYPE
    		,SOP102.LNITMSEQ
    		,SOP101.CUSTNMBR
    		,SOP102.ITEMNMBR
    	FROM
    		SOP10200 AS SOP102 WITH (NOLOCK)
    	INNER JOIN
    		SOP10100 AS SOP101 WITH (NOLOCK)
    			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
    	INNER JOIN
    		SOP60300 AS SOP603 WITH (NOLOCK)
    			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
    GO
    
    GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
    GO
    ● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

    SQL Error: “The query uses non-ANSI outer join operators”

    ● Ian Grieve ●  ● 3 Comments  ● 

    Microsoft Dynamics GPI’ve recently been working on a project upgrading a client from Microsoft Dynamics GP 9 SP3 to a later version and also from Microsoft SQL Server 2000 to SQL Server 2008 R2. Much of the upgrade has gone through without problems, but we’ve encountered one or two issues with customisations and custom reports.

    The following error message was produced during testing when generating one of the old Crystal Reports:

    Crystal Report Viewer - Failed to open rowset. Detsails: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]The query uses non-ANSI outer join operators("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backwa...

    Crystal Report Viewer

    Failed to open rowset.
    Details: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]The query uses non-ANSI outer join operators("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backwa...

    The compatibility level of a database for Microsoft Dynamics GP should NOT be changed back to 80 when this error is encountered. The solution to use is the recommended one from the error message: to rewrite the query.

    In this case the client had a set of Crystal Reports written against GP 9 which called a variety of stored procedures. I spent a few hours reviewing and rewriting stored procedures to remove the non-ANSI outer joins and replacing them with ANSI ones.

    ● Categories: Dynamics, GP, Microsoft, SQL Server, SQL Server 2000, SQL Server 2008 ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

    SQL Script To Copy Tax Setup To All Companies Using Cursor

    ● Ian Grieve ●  ● 2 Comments  ● 

    Microsoft Dynamics GPFollowing on from the creation of the script to copy tax schedules between companies, I was on a fairly long train journey and spent a small part of the time wrapping a cursor round the original script to allow tax schedules to be configured in one company and rolled through the others.

    There is one setting at the top of the script to set the SourceCompany.

    One item to note, is that the Account Index is set to 0 so all companies do need the tax nominal account entering once they have been copied.

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    DECLARE @SQLStatement AS VARCHAR(2000)
    DECLARE @SourceCompany VARCHAR(5)
    DECLARE @DestinationCompany VARCHAR(5)
    
    SET @SourceCompany = 'TWO'
    
    DECLARE
    	cursor_InterID CURSOR 
    FOR 
    	SELECT
    		INTERID
    	FROM
    		DYNAMICS..SY01500
    	INNER JOIN
    		master..sysdatabases
    	ON
    		name = INTERID
    	
    	OPEN cursor_InterID
    
    	FETCH NEXT FROM
    		cursor_InterID
    	INTO
    		@DestinationCompany
    	WHILE (@@FETCH_STATUS <> -1)
    		BEGIN
    		IF (@@FETCH_STATUS <> -2)
    
    			-- Sales/Purchases Tax Schedule Header Master
    			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00101
    				(TAXSCHID,TXSCHDSC)
    			--VALUES
    				(SELECT 
    					TAXSCHID,TXSCHDSC
    				FROM
    					' + @SourceCompany + '..TX00101 AS TX
    				WHERE
    					(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00101 WHERE TAXSCHID = TX.TAXSCHID) = 0)'
    			EXEC (@SQLStatement)
    
    			-- Sales/Purchases Tax Schedule Master
    			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00102
    				(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
    			--VALUES
    				(SELECT
    					TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate
    				FROM
    					' + @SourceCompany + '..TX00102 AS TX
    				WHERE
    					(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00102 WHERE TAXSCHID = TX.TAXSCHID AND TAXDTLID = TX.TAXDTLID) = 0)'
    			EXEC (@SQLStatement)
    
    			-- Sales Purchases Tax Master
    			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00201
    				(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
    				TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,
    				NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
    				TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
    			--VALUES
    				(SELECT
    					TAXDTLID,TXDTLDSC,TXDTLTYP,0,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
    					TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,0,
    					NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
    					TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT
    				FROM
    					' + @SourceCompany + '..TX00201 AS TX 
    				WHERE
    					(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00201 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
    			EXEC (@SQLStatement)
    
    			-- Sales/Purchases Tax Summary Master
    			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00202
    				(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
    			--VALUES
    				(SELECT
    					TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST
    				FROM
    					' + @SourceCompany + '..TX00202 AS TX
    				WHERE
    					(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00202 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
    			EXEC (@SQLStatement)
    
    			FETCH NEXT FROM
    				cursor_InterID
    			INTO
    				@DestinationCompany
    		END
    	CLOSE cursor_InterID
    DEALLOCATE cursor_InterID

    If you use this script, then please make sure you have a good backup before running it and also test afterwards.

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

    SQL Script To Copy Tax Setup To A New Company

    ● Ian Grieve ●  ● 4 Comments  ● 

    Microsoft Dynamics GPWhile helping a client configure some new companies recently, we needed to create the tax schedules in each of the companies; unfortunately, there were about 30 tax schedules and 4 companies which needed them. To do manually, or even with a macro, was going to take a while, so I took a few minutes out to see if it was going to be possible to run the update using a SQL script.

    The below is what I came up with and appeared to work fine for the companies we tested it in. One item to note, is that the Account Index is set to 0 so all companies do need the tax nominal account entering once they have been copied.

    There are two variables at the top which will need to be configured before running the script: @SourceCompany and @DestinationCompany.

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    DECLARE @Statement AS VARCHAR(2000)
    DECLARE @SourceCompany AS VARCHAR(5)
    DECLARE @DestinationCompany AS VARCHAR(2000)
    
    SET @SourceCompany = 'TWO'
    SET @DestinationCompany = 'PI'
    
    -- Sales/Purchases Tax Schedule Header Master
    SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00101
    	(TAXSCHID,TXSCHDSC)
    --VALUES
    	(SELECT 
    		TAXSCHID,TXSCHDSC
    	FROM
    		' + @SourceCompany + '..TX00101 AS TX
    	WHERE
    		(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00101 WHERE TAXSCHID = TX.TAXSCHID) = 0)'
    EXEC (@Statement)
    
    -- Sales/Purchases Tax Schedule Master
    SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00102
    	(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
    --VALUES
    	(SELECT
    		TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate
    	FROM
    		' + @SourceCompany + '..TX00102 AS TX
    	WHERE
    		(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00102 WHERE TAXSCHID = TX.TAXSCHID AND TAXDTLID = TX.TAXDTLID) = 0)'
    EXEC (@Statement)
    
    -- Sales/Purchases Tax Master
    SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00201
    	(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
    	TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,
    	NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
    	TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
    --VALUES
    	(SELECT
    		TAXDTLID,TXDTLDSC,TXDTLTYP,0,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
    		TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,0,
    		NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
    		TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT
    	FROM
    		' + @SourceCompany + '..TX00201 AS TX 
    	WHERE
    		(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00201 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
    EXEC (@Statement)
    
    -- Sales/Purchases Tax Summary Master
    SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00202
    	(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
    --VALUES
    	(SELECT
    		TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST
    	FROM
    		' + @SourceCompany + '..TX00202 AS TX
    	WHERE
    		(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00202 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
    EXEC (@Statement)

    If you do use this script, then please make sure you have a good backup before running it and also test afterwards.

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

    SQL Script To Fix A Corrupt VAT 100 Return

    ● Ian Grieve ●  ● 1 Comment  ● 

    Microsoft Dynamics GPThe process of creating a Tax Return in Microsoft Dynamics GP can sometimes take a while when there are a large number of transactions to process. While this usually isn’t a problem, on occasion a network connectivity issue (or a Citrix server cutting the user off after a certain length of time) can cut GP off from the database resulting in corrupt information in the Tax Return tables.

    The following script will remove the corrupt data from the Tax tables and reset the flag on the transactions which shows which tax Return they’re on.

    Change the highlighted section to the Tax Return ID to be reset:

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    DECLARE @TAX_RETURN_ID AS VARCHAR(100) = 'taxreturnid'
    
    DELETE FROM
    	TX00301
    WHERE
    	TAX_RETURN_ID = @TAX_RETURN_ID
    GO
    
    DELETE FROM
    	TX00303
    WHERE
    	TAX_RETURN_ID = @TAX_RETURN_ID
    GO
    
    DELETE FROM
    	TX00304
    WHERE
    	TAX_RETURN_ID = @TAX_RETURN_ID
    GO
    
    UPDATE
    	TX30000
    SET
    	Included_On_Return = 0
    	,Tax_Return_ID = ''
    WHERE
    	TAX_RETURN_ID = @TAX_RETURN_ID
    GO

    Before running the script make sure you have a good backup of the database and test afterwards to make sure everything is OK.

    ● Categories: Dynamics, GP, Microsoft, VAT Daybook ● Tags: , , , , ,  ● Permalink ● Shortlink ●

    SQL Trigger To Automatically Enable Email Documents In Purchasing – All

    ● Ian Grieve ●  ● 2 Comments  ● 

    Microsoft Dynamics GPThis post includes a script which amalgamates the previous two posted triggers to enable email documents (purchase orders and check remittances) into one.

    The below script creates a trigger on the SY04905 table to automatically flag the remittance and purchase orders to be emailed out in PDF format and using Message IDs of REMITTANCE and PURCHASEORDER respectively.

    /*
    Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
    This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
    */
    CREATE TRIGGER utr_AZRCRV_Update_SY04905_Activate_All ON dbo.SY04905 AFTER Insert AS
    	-- Enable Purchase Order
    	UPDATE
    		Email
    	SET
    		EmailDocumentEnabled = 1
    		,EmailMessageID = 'PURCHASEORDER'
    		,EmailDocumentFormat = 3
    	FROM
    		SY04905 AS Email
    	INNER JOIN
    		inserted
    			ON Email.EmailDictionaryID = 0
    				AND Email.EmailSeriesID = 4
    				AND Email.MODULE1 = 12
    				AND Email.EmailCardID = inserted.EmailCardID
    				AND Email.EmailDocumentID = 1
    	-- Enable Remittance
    	UPDATE
    		Email
    	SET
    		EmailDocumentEnabled = 1
    		,EmailMessageID = 'REMITTANCE'
    		,EmailDocumentFormat = 3
    	FROM
    		SY04905 AS Email
    	INNER JOIN
    		inserted
    			ON Email.EmailDictionaryID = 0
    				AND Email.EmailSeriesID = 4
    				AND Email.MODULE1 = 19
    				AND Email.EmailCardID = inserted.EmailCardID
    				AND Email.EmailDocumentID = 6
    GO
    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●