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
/* 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
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/

Comments

Japheth Nolt said…
I am a little confused by your statement "since you cannot change the due date on a SOP order or invoice document". I realize that on an order, the Terms Discount Taken fields is greyed out. But on the invoice, you can click on the blue arrow beside the Terms Discount Taken field which takes you to the Sales Payment Terms Entry window. There you can change the Due Date for the invoice.

Maybe I'm missing something.

Thanks,
Japheth
Mariano Gomez said…
Japhet,

Thanks for pointing this out! I will correct this in my post -- fast typing.

Best regards,

MG.-
Anonymous said…
Mariano, this is awesome stuff. For anyone working on their SQL skills having these kind of short examples with detailed explanations is very valuable.

Victoria
Japheth Nolt said…
Mariano,
Thanks for the great post. I DO appreciate all the helpful tips.

Japheth
Greg Parker said…
Hello Mariano,
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
Mariano Gomez said…
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

Popular posts from this blog

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

Do I have to use those "Z-" currency IDs in GP?

Microsoft Dynamics GP Scrolling Windows and Line Sequence Numbers