Posted by: IS | November 30, 2010

Custom Stored Procedure to copy Dynamics GP business alerts from one company to another.


 Check out Fabien’s Linked in Profile here:

/*

Comments: This stored procedure will copy/update a Business Alert header and all settings from one company into another, or within a company

Run format: Exec usp_copy_bus_alert ‘DBFROM’, ‘DBTO’, ‘ALERTIDFROM’, ‘ALERTIDTO’

Author: Fabien Peyaud

Created on: 04/11/2010

Updated by: Fabien Peyaud

Updated on: 05/11/2010

*/

Create Procedure usp_copy_bus_alert

@DBFROM char(10),

@DBTO char(10),

@BUSALRTIDFROM char(15),

@BUSALRTIDTO char(15)

AS

If @DBFROM = ” Or @DBTO = ” or @BUSALRTIDFROM = ” or @BUSALRTIDTO = ”

PRINT ‘One or more argument(s) missing’

Else

BEGIN

Declare @BARULEIDFROM int

Declare @BARULEIDTO int

Set @BARULEIDFROM = (Select BARULEID from SY40500 where BUSALRTID = @BUSALRTIDFROM and DBNAME = @DBFROM)

Set @BARULEIDTO = (Select BARULEID from SY40500 where BUSALRTID = @BUSALRTIDTO and DBNAME = @DBTO)

If (@DBFROM = @DBTO and @BUSALRTIDTO = @BUSALRTIDFROM) OR (@BARULEIDFROM = @BARULEIDTO)

PRINT ‘Cannot copy to same Alert ID in same company – Change Destination alert ID or one of the companies’

Else

BEGIN

If @BARULEIDTO is null OR (@DBFROM = @DBTO)

BEGIN

Set @BARULEIDTO = (Select MAX(BARULEID) from SY40500)+1

PRINT ‘Creating new Rule ID ‘ + CONVERT(varchar(4), @BARULEIDTO) + ‘ in ‘ + RTRIM(@DBTO) + ‘: ‘ + @BUSALRTIDTO

PRINT ‘Rule will not work until enabled in Dynamics’

Insert into SY40500

(BARULEID, DSCRIPTN, BUSALRTID, EMAILMSG, LSTRWCHKD, INCLDRSLTS, DBNAME, CMPANYID, FREQTYPE, FREQINT, FRQSUBTYP, FRQSUBINT, FRQRELINT, FRQRCINT, STRTTIME, ENDTIME,

STRTDATE, ENDDATE, CREATDDT, CRUSRID, MODIFDT, MDFUSRID, ENABLED, KEEPHIST, NMBRTIME, NOTEINDX, SCHEDTXT)

Select @BARULEIDTO, DSCRIPTN, @BUSALRTIDTO, EMAILMSG, LSTRWCHKD, INCLDRSLTS, @DBTO, (Select CMPANYID from SY01500 where INTERID = @DBTO),

FREQTYPE, FREQINT, FRQSUBTYP, FRQSUBINT, FRQRELINT, FRQRCINT, STRTTIME, ENDTIME,

STRTDATE, ENDDATE, CREATDDT, CRUSRID, MODIFDT, MDFUSRID, 0, KEEPHIST, NMBRTIME, NOTEINDX, SCHEDTXT

from SY40500 where BARULEID=@BARULEIDFROM

END

ELSE

BEGIN

PRINT ‘Updating existing Rule ID ‘ + CONVERT(varchar(4), @BARULEIDTO) + ‘ in ‘ + RTRIM(@DBTO) + ‘: ‘ + @BUSALRTIDTO

UPDATE SY40500

SET DSCRIPTN=(SELECT DSCRIPTN FROM SY40500 WHERE BARULEID = @BARULEIDFROM), EMAILMSG =(SELECT EMAILMSG FROM SY40500 WHERE BARULEID = @BARULEIDFROM)

WHERE BARULEID = @BARULEIDTO

–delete all settings of existing destination rule (except rule header located in SY40500)

delete SY40502 where BARULEID = @BARULEIDTO

delete SY40503 where BARULEID = @BARULEIDTO

delete SY40504 where BARULEID = @BARULEIDTO

delete SY40505 where BARULEID = @BARULEIDTO

delete SY40506 where BARULEID = @BARULEIDTO

END

Insert into SY40502

(BARULEID, TBLPHYSNM, ALIASNAME)

Select @BARULEIDTO, TBLPHYSNM, ALIASNAME from SY40502 where BARULEID=@BARULEIDFROM

Insert into SY40503

(BARULEID, DISPSEQ, COLUMNAM, COLHEADR)

Select @BARULEIDTO, DISPSEQ, COLUMNAM, COLHEADR from SY40503 where BARULEID=@BARULEIDFROM

Insert into SY40504

(BARULEID, SQLCRTYP, SEQNUMBR, SQLCRTRIA)

Select @BARULEIDTO, SQLCRTYP, SEQNUMBR, SQLCRTRIA from SY40504 where BARULEID=@BARULEIDFROM

Insert into SY40505

(BARULEID, NOTIFTYPE, NOTFCTNID)

Select @BARULEIDTO, NOTIFTYPE, NOTFCTNID from SY40505 where BARULEID=@BARULEIDFROM

END

END

Advertisements

Responses

  1. […] Comments 0 Have you ever wanted to copy Business Alerts from one GP company to another? Well Fabian makes its easy with a Custom Stored Procedure to copy Dynamics GP business alerts from one company to another. […]

  2. […] Comments 0 Have you ever wanted to copy your business alerts from a company to another? Fabien Peyaud created stored procedures posted @ Dynamics GP.ie blog, checkout the article here. […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: