Dynamics GP next sales order number is corrupted as has become the same as the next quote number range

This problem happened today, first time for a few years, but it used to happen regularly (every few months). The issue is that the auto populated sales order number in sales transaction entry gets overwritten with the number from the quotation number range.

My guess for the circumstances of this issue, is that the SOP Entry window is open with a quote showing, but not actually started yet. Somehow the order type gets changed from quote to order in the forms internal variables (integration or misbehaving code or connection lost or something?).

The Sop Entry window is then closed, or the order type drop down is changed to order from quote, causing the GP window to attempt to return the unused quote number to the pool to be reused via the next sop number field in sop setup.

However as we are saying that the window variable that holds order type is incorrect and thinks this is a quotation, the window actually returns this quote number to the next order number field, when it should have returned it to the next quote number field. Hence the next sales order number becomes a quote number! eek!Surprised smile

Ok, to fix this is easy, right? -open SOP setup by navigating to Microsoft Dynamics GP>>Tools>>Setup>>Sales>>Sales Order Processing then click [Numbers] button.

Next SOP number in Dynamics GP

Correct the next document number to what it should be (see SQL later to find this), note that you need to close both windows to ensure it saves, and be quick if users are generating numbers you will get a optimistic concurrency violation on save. The problem you then encounter is that if you have distributed sales force, spread over the globe, they will have picked up numbers from the incorrect sop number range, and have them sitting like a time bomb on new sales orders in the sop entry window. Even when you change the number in the sop set up window, if any of those users choose to subsequently close the Sop Entry window, having not used the erroneous sop number, then that number will again get returned to the sop next number field in sop setup, undoing your good work! This is exactly what happens, the number keeps jumping back to the quote range of numbers and you fight a losing battle against the users.

One answer is to ask all users to exit sop entry, make the change then let them back in, with a distributed work force, that may not be actually easy to contact or worse, not in front of the machines when you need to make the change, this is not practical. In this case, I have used the script below to keep resetting the number so immediately the users corrupts the next sop number, it gets corrected again.

Leave the script below running in SSMS until you are confident all users are now on the correct range of numbers.

DECLARE @NextSopNumber CHAR(21)

SET @NextSopNumber = '01447267'

WHILE 1 = 1
BEGIN
IF EXISTS (
SELECT SOPNUMBE
FROM SOP40300
--Here SOPTYPE = 2 is an Order - could be changed to target another type
WHERE SOPTYPE = 2
AND SOPNUMBE NOT LIKE
-- Make a like stub from the next number to see that the number has not jumped
CONCAT (
SUBSTRING(@NextSopNumber, 1, LEN(@NextSopNumber) - 3)
,'%'
)
)
BEGIN
UPDATE SOP40300
SET SOPNUMBE = @NextSopNumber
WHERE DOCTYABR = 'ORD'
END

PRINT 'WAIT'
-- 5 second delay then check again
WAITFOR DELAY '00:00:05'
END

In the above script, set the next sop number in the above script from the number that looks reasonable as a last number from running, looking at the SOPTYPE for the correct document type in question:

SELECT TOP(40)* FROM SOP10100 order by DEX_ROW_ID DESC

So long as the next SOPNumber is within 1000 of the actual last number used, then the GP client will scroll up through, finding the actual next sop number that is available for allocation. The exact number is not critical, and it wont matter until 1000 orders have been entered. After 1000 you will get an error message saying a new sop number could not be found. 
 
Do let me know if this was helpful, in the comments, keeps me motivated to write more!

Dynamics GP - Manual Payment does not generate next cheque/check number or document number

Manual Payment empty document number, not defaulted

When making a manual payment normally, after selecting the payee, the cheque book attached to that payee is queried for its next cheque number, that is then auto populated into the “Document No” field shown.

Payables Manual Payment Entry

During month end we found all of a sudden this was not happening, instead the document number was being left blank. The previous payment only ten minutes earlier had been fine, correctly generating the number.

Cash receipts entry required field for Cheque/Card Number


Shortly after Sales ledger started complaining that cheque number field in cash receipts had suddenly become mandatory.

 Cash Receipts Entry

See in the above screen how the Cheque/Card Number is bold, indicating it is now mandatory, before this was not the case.

Remove Creditor Hold Password set to default

A third event also happened at the same time, which was that the password on payables for removing creditor holds, that previously was blank, suddenly had defaulted back to its default value of ACCESS.

Password defaulted

After some digging I figured out the cause of this was that the Payment Document Management module had been switched on somehow and had not been configured so was causing the blanks.

.

Payment Document Management 2150 PMNTDOC.DIC
2150FRM.DIC
2150RPT.DIC

Navigating to Dynamics GP>> Tools>> Setup>> Company>> Payment  Document Setup
Unchecking both check boxes turned off the module and behaviour resumed as per normal.

Company Payment Document Management

 

It is a mystery how this occurred but maybe someone else one day might find this post useful, if so do comment!

Correct reconcile Dynamics GP SOP batch totals and transaction counts without SQL

Sometimes batch totals and transaction counts get corrupted in GP, there can be many reasons behind this.

To correct them the check links maintenance routine can be executed for “Sales Work”. This will correct the batch totals but will take a very long time to run and is not the sort of thing to be doing during working hours.

Alternatively corrections can be made via SQL which is quick and can address multiple batches,

Reconciling SOP Batches - The Dynamics GP Blogstersome thought needs to be put into batch locking etc if this is to be used regularly as the script in its current form does not check if it is “allowed” to update the batch totals.

Similar post: Reconciling SOP Batches from WITHIN Dynamics GP

Today I found out another way that I thought worth recording for future reference, printing an edit list for the batch. Although this can only be used one batch at a time, it is quick and “built in” to GP.

Instructions

Go to the Sales Batch Entry Window…

Transactions>>Sales>>Sales Batches

2017-06-22_09-32-37

Select the SOP batch of interest and click the print icon.

2017-06-22_09-32-53

In the Form to Print combo box, select Edit List and then click the print button.

Select a destination (to screen) in the print destination window that follows. After printing, leaving the batch and then going back into the batch, the totals will be found to be corrected.

Thanks for the hint from Jorge Mejia on GP forums: https://community.dynamics.com/gp/f/32/p/241273/665571#665571

Dynamics GP - SQL script to revert multiple bins into single bins

So you want to turn off multiple binning in Dynamics GP and need to record the multiple bin where the stock is held back into the basic single bin field against each location?

This script I just used to do that, it will pick the multiple bin with the most stock as the one to use for the single bin field. I would take a snapshot of the multiple bins table into excel, just for the record if performing this change (IV00112) and for future reference.

WITH CTE
AS (
SELECT ITEMNMBR
,LOCNCODE
,BIN
,ROW_NUMBER() OVER (
PARTITION BY ITEMNMBR
,LOCNCODE ORDER BY QUANTITY DESC
) RowSort
FROM iv00112
WHERE qtytype = 1
)
UPDATE IV00102
SET BINNMBR=BIN

--SELECT CTE.ITEMNMBR
-- ,CTE.LOCNCODE
-- ,BIN
-- ,BINNMBR
FROM CTE
JOIN IV00102 ON CTE.ITEMNMBR=IV00102.ITEMNMBR AND iv00102.LOCNCODE=CTE.LOCNCODE
WHERE RowSort = 1
AND BIN !=BINNMBR
 
Then, turn off multiple bins in the inventory setup window, and reconcile the inventory after doing so.