So if you are using MS GP Dynamics by now you know that when you post a new Payable Document(s), it uses 'Remit To' AddressID from the VendorID to print on Check for the mailing address. And I am assuming you also know that when you go back to add new 'AddressID' and assign it to the Vendor 'Remit to' field that will not update all the open /pending documents addressID. And on top of all that, when you are in the middle of re-printing checks, if you go back and change the AddressID currently assigned to the Vendor that will not update any existing checks that are not posted and need to be re-printed. A long story short, Microsoft's solution is to void all your pending documents for that current vendor, then create a new AddressID, assign it to the vendor 'Remit to' and recreate the Documents. But if you have over 10,20,30+ documents good luck with that one!
Today I am going to show you how you can fix this issue by adding triggers from SQL Server Management Studio. From here on I am assuming you have (some) knowledge of MS SQL Server management Studio. You have SQL Server Management Studio installed on your computer and you have access to MS GP Dynamics (Company) Database.
First, I'll walk you through real life example using screen shots.
1st. we'll go ahead and post a payable document.
3rd. Now we'll go ahead and select the Posted document, and print a check.
5th. So we just received the new mailing address, and we go back to the Vendor card and create a new AddressID and enter the new address and save the vendor record and close the window.
6th. Now if we reprint the check again you'll see the new address has not taken affect.
7th. (See image below)
8th. To fix the issue above, all you need to do is add two triggers into two tables and it will solve your problems and it will save you tons of time and aggravation.
9th. (See image below) (Note: this also affects all pending documents, that have not been printed yet.)
Here is the script you'll need to run from SQL Manager against your GP Company DB.
Note, the script will create two new triggers in the tables (PM20000 and PM10300).
You'll only need to do this once.
------ copy from here ------------
Use TEST /*< *** CHANGE 'TEST' WITH YOUR GP COMPANY DATABASE NAME ***>*/Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists(select * from dbo.sysobjects where name='Custom_Update_PM20000_AddressID' and Objectproperty(id,'IsTrigger')=1)
Begin
Drop trigger [dbo].[Custom_Update_PM20000_AddressID]
End
Go
Create trigger [dbo].[Custom_Update_PM20000_AddressID] on [dbo].[PM00200] For update
As
-- Note: The trigger will execute only on specific Field. This way you'll have less performance hit on the server.
if Update(VADCDTRO)
Begin
-- Note: PM00200.VADCDTRO="REMIT TO"
-- Update Remit To Address (VADCDTRO) -- If you need to update other addressID types, add a seprate section below
update pm20000 set VADCDTRO=(select VADCDTRO from pm00200 where vendorID=pm20000.VendorID) where Vadcdtro<>'' -- Update Open Payable Documents (Not printed yet)
-- Update current check that were printed but not Posted, this is incase user needs to Reprint the Check without voiding the check
update PM10300 set
Address1=isnull((select rtrim(Address1) from pm00300 where (vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID))),''),
Address2=isnull((select rtrim(Address2) from pm00300 where (vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID))),''),
Address3=isnull((select rtrim(Address3) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
City=isnull((select rtrim(City) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
[State]=isnull((select rtrim([State]) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
ZipCode=isnull((select rtrim(ZipCode) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
Country=isnull((select rtrim(Country) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
VNDCHKNM=isnull((select rtrim(VNDCHKNM) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
CtySTZIP=isnull((select ltrim(rtrim(City)) + ' ' + ltrim(rtrim(STATE)) + ' ' + ltrim(rtrim(ZipCode)) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),'')
End
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists(select * from dbo.sysobjects where name='Custom_Update_PM10300_Address_Change' and Objectproperty(id,'IsTrigger')=1)
Begin
Drop trigger [dbo].[Custom_Update_PM10300_Address_Change]
End
Go
Create trigger [dbo].[Custom_Update_PM10300_Address_Change] on [dbo].[pm00300] For update
As
-- Note: The trigger will execute only when user changes the address of existing AddressID. This is seprate from the trigger in Table PM00200
-- Updates current check(s) that were printed but *not Posted, this is incase user needs to Reprint the Check without voiding the check with new addressID
update PM10300 set
Address1=isnull((select rtrim(Address1) from pm00300 where (vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID))),''),
Address2=isnull((select rtrim(Address2) from pm00300 where (vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID))),''),
Address3=isnull((select rtrim(Address3) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
City=isnull((select rtrim(City) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
[State]=isnull((select rtrim([State]) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
ZipCode=isnull((select rtrim(ZipCode) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
Country=isnull((select rtrim(Country) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
VNDCHKNM=isnull((select rtrim(VNDCHKNM) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),''),
CtySTZIP=isnull((select ltrim(rtrim(City)) + ' ' + ltrim(rtrim(STATE)) + ' ' + ltrim(rtrim(ZipCode)) from pm00300 where vendorID=PM10300.VendorID and pm00300.ADRSCODE=(select VADCDTRO from pm00200 where vendorID=PM10300.VendorID)),'')
------ End here ------------
How to run the script above -?
1. Launch MS SQL Server Management Studio,
2. Connect to your SQL Server,
3. Click on "New Query" button (usually located top right corner).
4. Copy and paste the text above in the new query windows.
5. ** Change the word 'TEST' from the first line of the script to your GP Company DB name. (Note: this is NOT 'Dynamics' this has to be your GP Company Name. For example Fabrikam is called 'TWO')
To find out the list of GP Company Name and physical DB Name you can run this query.
select CMPNYNAM,INTERID from Dynamics.dbo.sy01500
6. After step 5 Click Execute button to execute the query (OR press F5, to execute).
7. THAT'S IT! From now on all your future Vendor AddressID changes will also update any pending / open payable documents as well.
L8r
H
No comments:
Post a Comment