"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 impliedCREATE TRIGGER pmEOMPlusNet ON dbo.PM20000 AFTER INSERTASBEGIN 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 TRYBEGIN 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 impliedCREATE TRIGGER rmEOMPlusNet ON dbo.RM20101 AFTER INSERTASBEGIN 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 TRYBEGIN 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/

Asset-based lending and receivables factoring

Factoring receivable invoices is the sale of an asset – your company’s invoices. The sale of your invoices to a third party – known as a Factor – eliminates the sale-to-collection business cycle of waiting for a customer payment. Usually, factoring companies will purchase your invoices for up to 90% of the total amount. You get your cash now and the factor takes on the risk of collecting the payments from your customers. The creditworthiness of your customers is very important if you want to get a good rate from a factor.

What are the specific advantages?

• Immediate cash with no waiting and without incurring new debt: You receive quick payment following invoicing.
• To factor, your business credit rating is not an issue because you are not borrowing money
• Efficient handling of all your invoicing and data entry
• Relief from the responsibility for collecting no-pay and slow-pay clients
• You have expanded growth capacity through increased production and total sales
• Ability to take advantage of vendor discounts

How to implement factoring in GP?

I have done a number of implementations where accounts receivable factoring was necessary, and one way — read, they may be other methods, but this is what worked for my clients — I have found is by using the National Accounts functionality in GP, where the factoring company becomes a national account and all customers are child records of the national account. Lets take a look:

1) Setup your factoring company as a customer tied to the checkbook ID where funds from the factoring company are deposited.

*Click on image to enlarge

2) Setup your factoring company as a national account and add your customers as child records to the national account.

*Click on image to enlarge

3) If you receive check or payments from your customers, just forward them to your factoring company. This is not a common occurrence in this scenario, but if you do receive checks from your customers, there is no need to record these in Dynamics GP.

4) When you receive the funds and payment report from the factoring company, enter a cash receipt for the amount reflected on the report. When you post the cash receipt, don’t forget to do a bank deposit. This will reflect the funds in your account.

* Click on image to enlarge

5) Apply the cash receipt from the factoring company to the invoices reflected on the report. Because of factoring, invoices will be short. Writeoff the difference to the factoring expense account you have created.

* Click on image to enlarge

* Click on image to enlarge

NOTE: If you have a high volume of transactions, you can request a file from the factoring company to be used with Integration Manager.

Overall, what I like about this approach is, your company still does not loose track of the invoces owed by your customers, you can still run receivable agings that will allow you to estimate how much your outstanding invoices are worth allowing greather visibility on cash flow — remember, your fees will increase depending on the age of the invoices held by the factoring company.

Until next post!

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

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 INSERTAS

 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/

How to create a Go To link to the Apply Sales Document window in SmartList Builder

A few days aback, my buddy Jim Harris at TurboChef was having an issue with creating a Go To in SLB that would link open credits and return documents displayed in SmartList to the Apply Sales Documents window in GP where he could effectively apply documents that had not been applied to invoices, etc.

I thought, how complicated could that be? You open SLB, you setup your SmartList fields then, setup your restrictions for RM Document Type-All equal or greater than 7, and add the Go To by matching the Customer Number field on the window to the Customer Number in the RM Open Table, the Document Type on the window with the RM Document Type-All field in the table, and the Document Number in the window withe the Document Number in the table, a few clicks to close and save, build and… not so quick!

At first the Apply Sales Document window filled in the Customer Number and Document Number, but screamed at the Document Type field. I figured, my restriction was wrong, so I went back in and check on the restrictions, reviewed the SQL check on my Go To fields to make sure I was matching the values and running the field scripts, a few clicks to close and save, build and… not so quick!

I got the same result as before and was starting to wonder now if I had had a long day and was not thinking straight. So I went to the kitchen, got some coffee and went back to work on the problem.

After a few hit-and-miss attempts the light bulb went off! Drop-down lists are enumerations of the items listed in them! In the case of the Apply Sales Document window, 1 – Credit Memo, 2 – Returns, 3 – Payments, which do not correspond to the actual document types in RM (7 — Credit Memo, 8 — Returns and 9 — Payments).

This meant that I would need to create a calculated field that would pickup the RM Document Type-All value from the RM Open table and translate it into the corresponding enumaration in the Drop-Down list in the Apply Sales Document window. The resulting calculation looked something like this:

case {RM Open File:RM Document Type-All} when 7 then 1 when 8 then 2 when 9 then 3 end

That’s correct! It looks and behaves just like the CASE…END statement in SQL Server!

This now meant that in the Go To window, I would have to reference my calculated field instead of the RM Document Type-All field when matching the Document Type value.

From this point on a few clicks to close and save, build and… Voila!

Until next post!

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

Resolving SQL Duplicate Key Error Message When Executing RM Paid Transaction Removal

This is one of those issues that I cannot fathom why the Microsoft Dynamics GP development team has not address in its RM Paid Transaction Removal procedure. It seems when the RM module gets “out of sync” — usually after a crash of some sort — it will cause transaction records to post between the RM Open table (RM20101) and the RM History table (RM30101). Unfortunately, for the end-user the problem is only evident when executing the Paid Transaction Removal operation, since they are likely to experience the following error message:

(Microsoft)(SQL Native Client)(SQL Server)Cannot insert duplicate key row in object ‘dbo.RM30101′ with unique index ‘AK3RM30101′.

In addition, rebuilding the RM Key table (RM00401) does not solve the problem as the system will not know what to do with the same record found on both open and history tables. Given this situation, I have written the following query to identify and help in resolving the issue — the query must be executed against the company database:

SELECT a.rmdtypal, a.docnumbr, b.rmdtypal, b.docnumbrFROM RM20101 a  INNER JOIN RM30101 b ON (a.rmdtypal = b.rmdtypal) AND (a.docnumbr = b.docnumbr)

Once it is determined which document(s) is causing the problem, additional research will need to be conducted to establish which of the two records is the valid one and remove from either table (RM20101 or RM30101) accordingly. Establish if the document is fully applied and also check the Document Status (DCSTATUS) field in the RM Key table (RM00401) — 0: Reserved, 1: Work, 2: Open, 3: History.

Until next post!

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

Removing Child Records from a National Account

This is an actual problem I faced at an actual customer of mine a few months aback and posted today on the Google’s Dynamics GP board. It seems that more often than one could assert, a customer’s payment records get assigned to the wrong national account (or parent account). This can cause problems down the road when incorrect statements or agings are submitted to the corporate customer.

In light of this issue, I developed a two fold solution:

1) The following query identifies the credit documents posted and applied by the parent customer on behalf of the child account.

declare @custnmbr char(21), @parent char(21)set @custnmbr = 'YOUR_CHILD_CUSTOMER'set @parent = 'YOUR_PARENT_CUSTOMER'

select distinct aptodcnm from( select aptodcnm from rm30201 where custnmbr = @custnmbr and apfrdcnm in  ( select docnumbr from rm20101 where custnmbr = @parent and RMDTYPAL = 9    union    select docnumbr from rm30101 where custnmbr = @parent and RMDTYPAL = 9  )  union  select aptodcnm from rm20201 where custnmbr = @custnmbr and apfrdcnm in  ( select docnumbr from rm20101 where custnmbr = @parent and RMDTYPAL = 9    union    select docnumbr from rm30101 where custnmbr = @parent and RMDTYPAL = 9  )) unapply

2) You will then need to use Dynamics GP’s Professional Services Tools Library (PSTL) to unapply the invoices associated to the child record, which in turn will unapply the credit documents posted under the national account. Make sure you print all reports generated by Professional Tools as you will need them to reapply the credit documents.

Well, hope this helps in solving a major headache faced by many AR departments around the globe.

Until next post!

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

UPDATES
05/28/2009 – Edited text and added link to article in Google’s Dynamics GP group. Included signature.