Thursday, July 16, 2009

"End of Month + Net Days" payment terms due date calculation

Folks across the pond use payment terms and due dates that are not traditionally close the ones we are familiar with on this side of the hemisphere. "End of Month + Net Days" (EOM+ND) is a typical case.

In EOM+ND payment terms, an invoice becomes due a number of net days after the last day of the month for the invoice date. For example, if an invoice date is July 16 and we are on a payment term of EOM plus 45 net days, the invoice will not be due until September 14 -- or 45 days from July 31.

Of course, you cannot manage this type of payment term request in GP, not out-of-the-box anyways, which most of the time will require a customization to deal with the issue.

In this article, I will examine setting up two SQL Server triggers: one on the PM Transaction Open File (dbo.PM20000) and another on the RM Open File (dbo.RM20101) tables. The triggers will use the Net Days field in the Payment Terms Master (dbo.SY03300) table to calculate the net days after the end of month to assign the invoice due date.

So here are the triggers:

trigger pmEOMPlusNet

-- Created by Mariano Gomez, MVP
-- No warranties expressed or implied
CREATE TRIGGER pmEOMPlusNet ON dbo.PM20000 AFTER INSERT
AS
BEGIN TRANSACTION;

BEGIN TRY
UPDATE A SET A.DUEDATE =
DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))
FROM PM20000 A
INNER JOIN INSERTED I ON (A.VCHRNMBR = I.VCHRNMBR) AND (A.DOCTYPE = I.DOCTYPE)
LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)
WHERE (I.DOCTYPE = 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity
, ERROR_STATE() AS ErrorState
, ERROR_PROCEDURE() AS ErrorProcedure
, ERROR_LINE() AS ErrorLine
, ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO


trigger rmEOMPlusNet

-- Created by Mariano Gomez, MVP
-- No warranties expressed or implied
CREATE TRIGGER rmEOMPlusNet ON dbo.RM20101 AFTER INSERT
AS
BEGIN TRANSACTION;

BEGIN TRY
UPDATE A SET A.DUEDATE =
DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))
FROM RM20101 A
INNER JOIN INSERTED I ON (A.CUSTNMBR = I.CUSTNMBR) AND (A.DOCNUMBR = I.DOCNUMBR)
AND (A.RMDTYPAL = I.RMDTYPAL)
LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)
WHERE (I.RMDTYPAL= 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity
, ERROR_STATE() AS ErrorState
, ERROR_PROCEDURE() AS ErrorProcedure
, ERROR_LINE() AS ErrorLine
, ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO


Setting up the payment term in Dynamics GP

Open the Payment Terms Setup window (MSDGP > Tools > Setup > Company > Payment Terms) and configure the payment term as shown below:


NOTE: You can still setup discount and discount types for the payment term, but these will be calculated based on the document date. If you need these to apply based on EOM as well, you will need to change the above triggers to reflect the discount calculation based on EOM as well.

Finally, once setup, these payment terms can be used from SOP and POP. Just keep in mind that the due dates will not be calculated while the transactions are stored in a batch, but rather when posted.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

12 comments:

Janakiram M.P. said...

Cool!!!

jerry h said...

Nice work...My company deals with numerous EOM+NET invoices, though I used a combination of VBA and SQL to get these to work.

Triggers are probably more ideal though.

Robert O'Donnell said...

Ever done any like avg 30 on Friday? We have a customer which is basically giving 25 days plus # of days until the following friday. This is for cash flow purposes. They also do things like avg 10 on wednesday which is 5 days plus # of days until Wednesday.

timgduncan said...

My Company use 3 different payment terms and I am having an issue with 2 of them. The payment terms used are...
1 month (End of following month) - ive set this up using the Next Month option 31 days which is fine

2 month (End of 2nd month) - eg. invoice date 22nd july 2011 will be due last day of Sept 2011

3 month (End of 3rd month) - eg. invoice dated 22nd july 2011 will be due last day of Oct 2011.

I cant work out how to do the last 2 payment terms.

Any ideas?

Mariano Gomez said...

Tim,
Thanks for writing. I will show how to do these special payments with the Support Debugging Tool. Look for my article this week

MG.-

Mariano Gomez said...

Tim,
Thanks for writing. I will show how to do these special payment terms with the Support Debugging Tool. Look for my article this week.

MG.-

Geoff James said...

Hi Mariano,

I have just found this Payment terms method which I require. You mentioned to Tim to read your article. How do I access that article from your site.

Regards,

Geoff James

Mariano Gomez said...

Geoff,

I did not work on this article, but the payment terms should be fairly simple to achieve with a bit of T-SQL scripting - very similar to what has been posted in this article.

MG.-

Mariano Gomez said...

Geoff/Tim,

In further looking at your request, you should be able to setup those payment terms as EOM+ND just as I instructed in the article. In this case, your net days will be 60 (2 months) or 90 (3 months).

MG.-

CSMEUTAH said...

Mario, I had to update the pm00400 keys table on the payables side since it also keeps the duedate. We noticed this looking up the transaction in inquiry.

CSMEUTAH said...

We had to update the trigger to include the pm00400 table since it also holds the duedate column:

UPDATE A SET A.DUEDATE =
DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))
FROM pm00400 A
INNER JOIN INSERTED I ON (A.CNTRLNUM = I.VCHRNMBR) AND (A.DOCTYPE = I.DOCTYPE)
LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)
WHERE (I.DOCTYPE = 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')

Scott Steffen said...

I have a prospect that is using MAS90. They are very interested in moving to GP, however, I've run into an issue with terms.

This company has terms of: "$0.50/ton, 10th proximo net 30".

For example, all invoices in January will have a due date of Feb. 10th. If paid, they receive a discount of $0.50 per ton.

The second issue here is they want to limit the discount to receiving payment for ALL January invoices in order to give the discount. So, if they had 10 invoices in January, but only received payment on the 10th for 9 invoices, they would not get the discount.

Any thoughts? Does anyone know if MAS 90 even does this?

Thanks