TABLE NAME FOR PAYABLES TRANSACTIONS

[villica]

Hi everyone


We have tons of payables invoices taht were created with a Net 60 payment terms. We want to run an update query that will change those invoices with Net 60 to Next 30 before a cheque run is done. What is the name of the table or tables?

thanks in advance

 

[VictoriaYudin]

First a warning, changing transactional data directly in the database is unsupported and could cause adverse results.

With that said - simply changing the Payment Terms ID on a posted transaction inside a table will not change the due date. Is your intention to go to Select Checks and pick the invoices to be paid by due date? If so, then terms don't matter, you want to update the DUEDATE field in PM20000. You can also change the PYMTRMID (payment terms ID) field if you'd like, but that won't help with picking checks unless you are doing something outside the typical GP check selection process that uses the payment terms ID.

Here is what the SQL code would look like to update all your due dates to the invoice date + 30 days for all invoices with the terms ID of 'Net 60':

UPDATE PM20000
set DUEDATE = DOCDATE + 30
WHERE PYMTRMID = 'Net 60'



Victoria Yudin
Dynamics GP MVP 2005 - 2008
Flexible Solutions - home of GP Reports

http://www.flex-solutions.com/gpreports.html

blog:

http://victoriayudin.com

 

[villica]

Hi Victoria,

Thank you for replying. We are finding that we are making late payments due to the way the invoices were set up at the stores(Net 60 instead of Net 30). I am not an GP expert at all. Please let me know if changing the payment terms id is not the right thing to do.

villica

 

[VictoriaYudin]

Villica,

The payment terms ID is simply used at the time of entering a transaction to have GP automatically calculate the due date. For example - if you've set up a vendor as Net 60, then enter an invoice for that vendor dated 8/1/08, GP will auto-calculate the due date as 9/30/08. After this the payment terms field is not used for anything and the due date is really what matters. That's why I was questioning whether it's the Payment Terms ID that you want to change.

I am assuming that you're either using the GP Select Checks functionality (Transactions > Purchasing > Select Checks) and selecting what to pay with a due date cutoff or you're printing a report with due dates and looking at it to determine what to pay. If that's not the case, please post back with more details.

Also, if you want to make a change after the transaction is posted, you can go to Transactions > Purchasing > Edit Transaction Information and change the due date there.

I am typically not a huge fan of changing transactional information directly in SQL, but for this specifically, I think you should be ok. Since GP does give us a way to do this directly in the interface.

Hope that helps. Please post back if you have more questions.

Victoria Yudin
Dynamics GP MVP 2005 - 2008
Flexible Solutions - home of GP Reports

http://www.flex-solutions.com/gpreports.html

blog:

http://victoriayudin.com