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