Dynamics GP SOP Orphaned Transactions and Allocated Items

Dynamics GP sales order processing (SOP) allows users to enter quotes, orders, invoices, returns, and backorders. These documents can fulfill 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. This article will address two of the most common causes for orphan records, header and detail line issues.

Table of Contents  

What Causes SOP Orphaned Transactions

To understand how to correct orphaned transactions 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 all other relevant information 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 by posting it or saving it to a batch. It is only at this step that the rest of the header is written to the SOP10100 table.

If everything works as 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 from 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.

To correct orphan records, you must remove the records for the header and the detail lines. Additionally, the items must be unallocated, which is done once the records are removed.

In this article, we will use SQL Server Manager and Dynamics GP to correct this issue.

How to Identify Orphan Records

If the Transaction is Still Open

A header is created when creating a new Sales Transaction Entry, by selecting the Document No., filling out the necessary fields, placing it in a batch, and entering a line item. In this example it is ORDST2241, and the item has been allocated.

Screenshot inside a Sales Transaction Entry highlighting the Document No. "ORDST2241".

Issues occur when header information is not saved completely. If GP were to close unexpectedly, the record would exist in the database but would not be accessible to the user. Therefore, these records would be caught as orphan records.

Fortunately, these unsaved records will be visible in the OrphanedSOPLine Items window in SmartList.

Remove an Open Orphaned Record

In SmartList, click into OrphanedSOPLine Items to view the record allocation and quantity.

All orphaned records will appear in this window, in this example there are two other SOP Numbers, both ORDST2239.

Screenshot in SmartList "Sales Line Items - OrphanedSOPLine Itmes".

To remove an open record from OrphanedSOPLine, navigate into your Sales Transaction Entry window in GP. Click to exit out of the window, and a pop-up will prompt you to either save or delete the entry.

Screenshot in "Sales Transaction Entry" window with a popup saying "Do you want to save or delete the document".

Clicking save will remove this record from the OrphanedSOPLine Items because it will save the full document and will no longer be an orphan record.

Clicking delete will delete and unallocate the record. The next time you refresh the OrphanedSOPLine Items, the record you chose to delete will no longer appear as orphaned.

If the Transaction Has Been Closed

As mentioned above, all orphaned records will appear in the OrphanedSOPLine Items window; in this example, ORDST2239 remains.

When searching for the item number (ORDST2239) in the Sales Transaction Entry window, an error message may appear, stating that the document has been posted. However, this document appears in the OrphanedSOPLine, indicating that it has not been posted.

To gather information about item allocation for this orphaned record, navigate to the Item Maintenance window, enter the item number you wish to see, click “Go To” and select “Quantities/Sites.”

Screenshot in "Item Maintenance" window highlighting "Go To" and "Quantities/Sites".

In the Item Qualities Maintenance window, note the allocated amount; in this example, it is 24. Click into “Allocated,” these are the orders that the system finds. Note that it is only 12.

Screenshot of the "Item Quantities Maintenance" window highlighting the allocated amount and of the "Item Allocation Inquiry" window highlighting items allocated.

If you check the quantity in SmartList, OrphanedSOPLine Items, you will notice that there are 12 items listed for that item number.

Screenshot in "OrphanedSOPLine Items" highlighting SOP Number "ORDST2239".

Now that I’ve shown you how to identify an orphan record, I will show you how to correct this issue and balance your item quantity and allocated items.

How to Correct Orphaned Records

Begin by navigating into SQL Server Studio Manager.

You will first want to execute the first select statement:

Screenshot in SQL Server Studio Manager highlighting the first piece of script and the results lower on the screen.

Notice that, in the select statement, I’ve added the SOP type, which tells you whether it is an invoice or a return, the SOP number, the customer number, the currency ID, the batch source, and the batch number.

Adding where customer = ‘ ‘ allows you to see all of the records that have been caught as orphaned records. In this example, we have SOP numbers ORDST2239 and ORDST2240.

Note that if you have an active SOP open for an item it will appear here.

There are two methods to solve orphan records:

Method 1

In SQL, run the following script against the company database. Before doing so, ensure that no one is working in GP to avoid deleting open records.

select soptype, sopnumbe, custnmbr, curncyid, bchsourc, bachnumb
from sop10100
where custnmbr = ' '

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

delete from sop10100 where custnmbr = ' '

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 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 still requires a check link, and the check links on the SOP file can take hours. Not only do you have to reconcile (the check links), but you must also reconcile the inventory to remove the allocation.

Method 2

Method 2 allows the administrator to correct the issue without limiting the user’s access. What we do is reactivate the documents so we can delete them 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. First, we run the following script:

select soptype, sopnumbe, custnmbr, curncyid, bchsourc, bachnumb
from sop10100
where custnmbr = ' '

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.

Screenshot inside SQL Server Studio Manager highlighting the first part of the script.

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 it using the functional currency (in my case, Z-US$). By running the following script, we can activate the documents:

update SOP10100
  BCHSOURC = 'Sales Entry',
where SOPNUMBE = 'xxxxxxx'

We substitute your document numbers for the XXXXXXX and your customer for the CUSTNMBR, in this example ORDST2239, and click Execute. The record you input will no longer appear and you can now look at the transaction for that item number you’ve entered. And make sure that the allocations are corrected.

Screenshot inside SQL highlighting the line "where SOPNUMBE = 'ORDST2239'". and the Executed results lower on the page.

This will leave the documents available to edit using Sales Transaction Entry.

Navigate back to your Sales Transaction Entry window, and when you enter your item number, you will now be able to open this record that was previously orphaned.

Screenshot inside "Sales Transaction Entry" highlighting the Document No. "ORDST2239"

The most important thing to note here is the item quantity that had been missing, 12 units. Now that this record has been opened, these units are now accessible.

Note that this is a non-recoverable state. The only thing we can do here is delete or void.

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.

Now when you go back into that item and click on “Go To” > Quantities and Sites, the allocated number has been automatically updated.

Screenshot in "Item Quantities Maintenance" window highlight the allocated amount and inside the "Item Allocation Inquiry" window highlighting the amount of items allocated.

This method requires more work from admin but can be a significant time saver for larger companies, because running check links and reconcile are very time consuming.

For a detailed walkthrough on the maintenance of SOP records, watch this recorded webinar:

If you require additional help with orphan records or have questions about your Dynamics GP environment, please contact us.

What Is New in Dynamics GP?

Get 8 premium pieces of content that will help you plan a Dynamics GP upgrade!

Get the Upgrade Guide

What Is New in Dynamics GP?

Get the Upgrade Guide