How to setup "No payments, no interests until June 1, 2009" payment term in Microsoft Dynamics GP
Business Situation
The current economic climate is forcing businesses to be a bit more creative. Some of these companies are resorting to No interest, no payments terms that allow customers to walk out the stores with the merchandise and begin payments at a future date. But, how is a company using Microsoft Dynamics GP going to deal with this type of payment term? What if the promotion is only being ran for the month of December only? What if all invoice generated in December are all due simultaneously on June 1, 2009 regardless of the date the invoice was generated?
Solution Implementation
Since invoices from SOP and RM flow to the RM Open table (dbo.RM20101) after they are posted, it is safe to say we can add a SQL Server trigger to overcome the hurdle. However, we must setup a payment term ID that will serve as an identifier for our trigger to take action. Follow these steps to implement the solution to this problem:
1) Setup a new payment term called JUNE012009. Open the Payment Terms Setup window under Microsoft Dynamics GP > Tools > Setup > Company > Payment Terms. Fill in the window, as follows:
*Click on image to enlarge
2) With the payment term ready to go, we can now proceed to write our SQL Server trigger on the RM Open table (dbo.RM20101). Run the following T-SQL script against your company database.
trigger dbo.trPaymentTerm
A few things to highlight are, the UPDATE statement in the trigger uses the DEX_ROW_ID to match the records inserted against the records already committed in the table. This is a valid use of the DEX_ROW_ID column. For more information on the DEX_ROW_ID column please check my previous article on the subject.
It is also worth noting that the WHERE clause contains all the rules we originally set out to obey, that is, we would apply the payment term for any document that had the 'JUN012009' payment term assigned, but also, the documents must be created during the month of December. This is important, since we need to prevent our trigger from firing for months other than December.
An enhancement could be introduced in the trigger to raise an error on the Dynamics GP user interface (using the RAISERROR statement), for any invoice document that does not meet the criteria.
Hope you find this post useful and I would like to hear your comments.
Until next post!
MG.-
Mariano Gomez, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
The current economic climate is forcing businesses to be a bit more creative. Some of these companies are resorting to No interest, no payments terms that allow customers to walk out the stores with the merchandise and begin payments at a future date. But, how is a company using Microsoft Dynamics GP going to deal with this type of payment term? What if the promotion is only being ran for the month of December only? What if all invoice generated in December are all due simultaneously on June 1, 2009 regardless of the date the invoice was generated?
Solution Implementation
Since invoices from SOP and RM flow to the RM Open table (dbo.RM20101) after they are posted, it is safe to say we can add a SQL Server trigger to overcome the hurdle. However, we must setup a payment term ID that will serve as an identifier for our trigger to take action. Follow these steps to implement the solution to this problem:
1) Setup a new payment term called JUNE012009. Open the Payment Terms Setup window under Microsoft Dynamics GP > Tools > Setup > Company > Payment Terms. Fill in the window, as follows:
*Click on image to enlarge
2) With the payment term ready to go, we can now proceed to write our SQL Server trigger on the RM Open table (dbo.RM20101). Run the following T-SQL script against your company database.
trigger dbo.trPaymentTerm
/* Created by Mariano Gomez, MVP
Code is delivered "AS IS". No warranties expressed or implied
*/
CREATE TRIGGER dbo.trPaymentTerm ON dbo.RM20101 AFTER INSERT
AS
BEGIN TRAN
UPDATE A
WITH (ROWLOCK) SET A.DUEDATE = '2009-06-01'
FROM dbo.RM20101 A INNER JOIN INSERTED I ON (A.DEX_ROW_ID = I.DEX_ROW_ID)
WHERE (I.PYMTRMID = 'JUN012009') AND I.DOCDATE BETWEEN '2008-12-01' AND '2008-12-31'
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
GO
It is also worth noting that the WHERE clause contains all the rules we originally set out to obey, that is, we would apply the payment term for any document that had the 'JUN012009' payment term assigned, but also, the documents must be created during the month of December. This is important, since we need to prevent our trigger from firing for months other than December.
An enhancement could be introduced in the trigger to raise an error on the Dynamics GP user interface (using the RAISERROR statement), for any invoice document that does not meet the criteria.
Hope you find this post useful and I would like to hear your comments.
Until next post!
MG.-
Mariano Gomez, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments
Maybe I'm missing something.
Thanks,
Japheth
Thanks for pointing this out! I will correct this in my post -- fast typing.
Best regards,
MG.-
Victoria
Thanks for the great post. I DO appreciate all the helpful tips.
Japheth
I want to thank you for your posts as it has taught me a great deal. This post is great, but what if you wanted to make the net terms ID and due date change before the invoice is posted? Can you make this same change on the SOP10100 table and how would that look?
Thank you,
Greg
Thanks for your inquiry. I would believe that you could create an alternate table that will hold configuration information for "special" payment terms. Then you could setup all these "special" payment terms in GP as I indicated in the article, but change the trigger to join with the alternate table to retrieve the correct data.
For example:
/* Created by Mariano Gomez, MVP Code is delivered "AS IS". No warranties expressed or implied*/
IF OBJECT_ID('dbo.rmSpecialPayments') IS NOT NULL
DROP TABLE dbo.rmSpecialPayments
GO
CREATE TABLE dbo.rmSpecialPayments(
PaymentTerm varchar(20) PRIMARY KEY,
DueDate datetime NOT NULL,
ApplyFrom datetime NOT NULL,
ApplyTo datetime NOT NULL,
DEX_ROW_ID int IDENTITY(1,1) NOT NULL,
)
GO
ALTER TABLE dbo.rmSpecialPayments WITH CHECK ADD CHECK ((datepart(hour,[DueDate])=(0) AND datepart(minute,[DueDate])=(0) AND datepart(second,[DueDate])=(0) AND datepart(millisecond,[DueDate])=(0)))
GO
ALTER TABLE dbo.rmSpecialPayments WITH CHECK ADD CHECK ((datepart(hour,[ApplyFrom])=(0) AND datepart(minute,ApplyFrom)=(0) AND datepart(second,[ApplyFrom])=(0) AND datepart(millisecond,[ApplyFrom])=(0)))
GO
ALTER TABLE dbo.rmSpecialPayments WITH CHECK ADD CHECK ((datepart(hour,[ApplyTo])=(0) AND datepart(minute, ApplyTo)=(0) AND datepart(second,[ApplyTo])=(0) AND datepart(millisecond,[ApplyTo])=(0)))
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.rmSpecialPayments TO DYNGRP
GO
INSERT rmSpecialPayments(PaymentTerm, DueDate, ApplyFrom, ApplyTo)
VALUES ('JUN012009', '20090601', '20081201', '20081231')
GO
CREATE TRIGGER dbo.trPaymentTerm ON dbo.RM20101 AFTER INSERT
AS
BEGIN TRAN
UPDATE A WITH (ROWLOCK) SET A.DUEDATE = B.DUEDATE
FROM dbo.RM20101 A
INNER JOIN INSERTED I ON (A.DEX_ROW_ID = I.DEX_ROW_ID)
LEFT OUTER JOIN rmSpecialPayments B ON (I.YMTRMID = B.PaymentTerm)
WHERE I.DOCDATE BETWEEN B.ApplyFrom AND B.ApplyTo
IF @@ERROR = 0
COMMIT TRAN
ELSE ROLLBACK TRAN
GO
Best regards,
MG.-
Mariano Gomez, MVP