Dynamics GP SOP Orphaned Transactions and Allocated Items

The Issue With SOP Orphaned Transactions and Allocated Items

Dynamics GP sales order processing (SOP) allows users to enter quotes, orders, invoices, returns and Back orders. These documents can fulfil and allocate product, reserving the items from other documents using the on-hand inventory. It operates most times with little intervention from support, however, it does sometimes leave orphaned records in the database that the firm needs to remove. To understand how to correct this requires the user to understand the reason these records exist at all.

The cause of this is the way that Dynamics GP assigns the next number in SOP.  When the screen is opened, and the document type is selected, Dynamics GP assigns the document number and writes a holding record in the SOP10100 table. This holding record has only the document type and the number, all other fields are empty or null at this stage. The user then adds the customer, the ship to, the date and the currency to the screen before moving to the scrolling window for the line items. This data is held on the screen but not written to the table. The user then enters the lines required by the customer one by one. Each time the line is changed the line item information is added to the SOP10200 table and any allocations/fulfilments are recorded in the inventory records. When the user is finished, the document is saved either by posting or saving to a batch. It is only at this step that the rest of the header is written to the SOP10100 table.

If everything works the way it should, the record is complete and ready for further processing

If something occurs prior to the save (power fluctuations, restart of GP, exit from remote, system hanging etc.) then the header remains in the system without the customer, or the currency. The items are still attached to this blank header so the allocations remain and can stop users form selling these products on other documents.

The Solution

So now that we understand what is happening, the next step is to find the underlying records so we can release the items for allocation to other orders.

What to Fix

We need to determine the offending records. Since new entries are stored in the SOP10100 without a customer until saved, all users need to get out of GP. By closing GP, we remove any blank records that the current users may have open.

Create a SmartList under Sales Transactions and add the following fields:

• SOP type
• SOP Number
• Customer Number
• Currency
• Batch Source
• Batch Number

Search for all the records where the Customer number is equal to ‘blank’. Save as a favorite: Orphaned SOP Headers. This shows the headers that are orphaned. Export to Excel for future reference.

To check if orders affect inventory, run a SmartList from Sales line items using the following fields:

• SOP type
• SOP Number
• Customer Number
• Item number
• Item description
• Qty
• Qty Allocated

Search for all records where the Customer number is equal to ‘blank’. Save as a favorite: Orphaned SOP Lines. This shows the items that are orphaned. Export to Excel for future reference.

How to Fix

There are two methods, both use Microsoft SQL Server Management Studio:

Method 1

When all users are out of GP run the following script against the company database:

This should give the same number of records by obtained running the Orphaned SOP Header SmartList.

The result will show a deletion of the same number of records.

Once the header records are deleted, we need to remove the attached records in the other files. From GP, run check links against the sales work. This will print a report showing the lines in the other tables as being removed (Tax, Distribution, Items, Commission, UDF etc.). The removed documents should match the Document numbers in the Orphaned SOP Header SmartList. This eliminates the orphan records in SOP but we must now run reconcile for the items listed on the Orphaned SOP lines (under Utilities in Inventory). We can run for the specific items or for all items. This will unallocate the items and free the inventory to be reassigned to new orders.

The challenge with this method is that it needs all users out of GP, and running Check links and Reconcile can take a long time.

Method 2

Method 2 allows the administrator to correct the issue without limiting the user’s access and without running utilities. What we do is reactivate the documents so we can delete using GP. Deleting using GP automatically removes all table references and updates the inventory for allocations and fulfillment. This does not make the record usable moving forward, only makes it available to DELETE.

The same SmartLists are required as they provide the information required for the next steps. Fist we run the following script:

Again, this should list the items on the SmartList. Since the SmartLists were exported with no users in GP the record count may now be higher, as users are entering documents in SOP. This is an expected result.

In GP we add a new Batch called RECOVERY for Sales Transactions. This will hold the activated transaction prior to deletion. We also need a customer and a currency to attach to the document.

We will activate using AARONFIT0001 as a customer. We will activate using the functional currency (in my case Z-US$). By running the following script, we can activate the documents:

We substitute your documents numbers in for the XXXXXXX and your customer to the CUSTNMBR.

This will leave the documents available to edit using sales transaction entry. We then open the document and using actions, delete the transaction (You may need to set the allow document deletion on the SOP setup of the document type). This removes the header, the lines, and all other table records as required and updates the inventory for the allocations. We then do this for all documents on the SmartList.

This completes the fix for the records, but we have a batch that cannot be deleted as it now has a negative number of transactions. We need to remove the recovery batch using the following script:

This completes Method 2, which is more work by the admin but can be a significant time saver for larger companies since Check Links and Reconcile are very time consuming.

As always, Encore is available to assist you with Dynamics GP, just contact us here.