Sales Order Processing in Dynamics GP

In this blog we go over information pertaining to Sales Order Processing in Dynamics GP. We look at how it happens, where does the information go once it’s been processed, and how dates are affected by the various actions in the Sales Order Process. First off, we will give a little back information of SOP and what it can be used for.

Before we get too in depth in the Sales Order Processing process, I will list which the SOPTYPES in SQL and their value. Knowing which type the SOP is can be useful when looking up records.

SOPTYPE:

1 – QUOTE
2 – ORDER
3 – INVOICE
4 – RETURN
5 – BACK ORDER
6 – FULFILLMENT

You can use Sales Order Processing to enter and print quotes, orders, invoices, back orders and returns. You can transfer sales documents from one type to another. There are other functions available in the SOP module, but these are ones we will focus on in this article.

SOP

Once the transaction is saved it can be found in the SOP10100 (unposted/work transaction with headers) table in SQl by running this script:

select SOPTYPE,SOPNUMBE,ORIGTYPE,DOCDATE,GLPOSTDT from sop10100 where sopnumbe=’ORDST2240′

The transaction can also be found in the SOP10200 (unposted/work transaction with line details) table by running this script:

select SOPTYPE,SOPNUMBE,LNITMSEQ,ITEMNMBR, UNITCOST, UNITPRCE, DROPSHIP from SOP10200 where SOPNUMBE=’ORDST2240′

Below is how the data appears in SQL. It should be noted that the fields populating the data are adjustable. I’ve included in the SOP10200 table a few more details on the item involved in the transaction.

SOP10100
SOPTYPE SOPNUMBE ORIGTYPE DOCDATE GLPOSTDT RETUDATE INVODATE QUOTEDAT
2 ORDST2240 0 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0
SOP10200
SOPTYPE SOPNUMBE LNITMSEQ ITEMNMBR UNITCOST UNITPRCE DROPSHIP
2 ORDST2240 16384 ACCS-CRD-12WH 3.29 9.95 0

As it goes, sometimes orders may need to be adjusted. In this instance I’ll show what happens when you transfer an order to an invoice.

SOP

After pressing the ‘transfer’ button on the previous window, the order will now become an invoice. In this instance it is now ‘STDINV2265’.

To confirm the transfer was successful, we will check in SQL with the query:

select SOPTYPE,SOPNUMBE,ORIGTYPE,DOCDATE,GLPOSTDT from sop10100 where sopnumbe=’ORDST2240′

The following query will not generate any results as the order was moved from SOP10100 to SOP30200.

You can confirm this by completing this SQL query:

select SOPTYPE,SOPNUMBE,ORIGTYPE,DOCDATE,GLPOSTDT, RETUDATE, INVODATE, QUOTEDAT from sop30200 where sopnumbe=’ORDST2240′

SOPTYPE SOPNUMBE ORIGTYPE DOCDATE GLPOSTDT RETUDATE INVODATE QUOTEDAT
2 ORDST2240 0 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0

To view the data of the transferred invoice ‘STDINV2265’ in SOP10100, you can do so by running this script:

select SOPTYPE,SOPNUMBE,ORIGTYPE,DOCDATE,GLPOSTDT, RETUDATE, INVODATE, QUOTEDAT from sop10100 where sopnumbe=’STDINV2265′

SOPTYPE SOPNUMBE ORIGTYPE DOCDATE GLPOSTDT RETUDATE INVODATE QUOTEDAT
3 STDINV2265 2 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0

The next logical move in this process is to post the invoice. When I created the transaction, I included it in a batch, so the entire batch will need to be posted.

You can view the data in SQL with this script:

select SOPTYPE,SOPNUMBE,ORIGTYPE,DOCDATE,GLPOSTDT, RETUDATE,INVODATE, QUOTEDAT from SOP10100 where SOPNUMBE=’STDINV2265′

select SOPTYPE,SOPNUMBE,ORIGTYPE,DOCDATE,GLPOSTDT, RETUDATE, INVODATE, QUOTEDAT from SOP30200 where SOPNUMBE=’STDINV2265′

The results after posting from SOP10100

SOPTYPE SOPNUMBE ORIGTYPE DOCDATE GLPOSTDT RETUDATE INVODATE QUOTEDAT
3 STDINV2265 2 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0

SOP30200

SOPTYPE SOPNUMBE ORIGTYPE DOCDATE GLPOSTDT RETUDATE INVODATE QUOTEDAT
3 STDINV2265 2 00:00.0 00:00.0 00:00.0 00:00.0 00:00.0

Since the batch is now posted, it’s common to want to view the batch in the General Ledger. I’ve found this query to be helpful when trying to find a specific transaction:

select DTAControlNum,TRXDATE,JRNENTRY, PSTGSTUS, TRXSORCE, TRXTYPE, REFRENCE

from GL10000 where DTAControlNum=’STDINV2265′

Here are the results:

DTAControlNum TRXDATE JRNENTRY PSTGSTUS TRXSORCE TRXTYPE REFRENCE
STDINV2265 00:00.0 3485 1 0 Sales Transaction Entry

Until you post the batch though to the General Ledger, you will only be able to view the documents in the GL10000 table.

Once posting through to the General Ledger, I was able to find the batch information by running this script:

SELECT * FROM GL20000 WHERE JRNENTRY= ‘3485’

From GL20000 after posting to GL
OPENYEAR JRNENTRY RCTRXSEQ SOURCDOC REFRENCE DSCRIPTN TRXDATE TRXSORCE
2027 3485 0 SJ Sales Transaction Entry Accounts Receivable 00:00.0 GLTRX00000666
2027 3485 0 SJ Sales Transaction Entry Inventory 00:00.0 GLTRX00000666
2027 3485 0 SJ Sales Transaction Entry Commissions Payable 00:00.0 GLTRX00000666
2027 3485 0 SJ Sales Transaction Entry Tax 00:00.0 GLTRX00000666
2027 3485 0 SJ Sales Transaction Entry Tax 00:00.0 GLTRX00000666
2027 3485 0 SJ Sales Transaction Entry Sales 00:00.0 GLTRX00000666
2027 3485 0 SJ Sales Transaction Entry Cost of Goods Sold 00:00.0 GLTRX00000666
2027 3485 0 SJ Sales Transaction Entry Commissions Expense 00:00.0 GLTRX00000666

Typically, in GP, your Sales Order Processing won’t be as simple as the one I’ve just detailed above. I’ll provide examples and reference tables below which will show what goes where, and where you can access it.

I have come across a couple questions regarding date and how it affects the document lifecycle, as well as which dates are related to which fields.

The easiest answer to this question is, dates flow along with the records that they exist on. For example, when an invoice moves from unposted to posted (SOP10100 to SOP30300), the transaction dates will reflect which day the change was made.

How is date affected when a SOP transaction is posted to the General Ledger?

This is dependant on how you have your posting settings set up. You’re able to view this by following this path: Microsoft Dynamics GP > Tools > Setup > Posting > Posting > Series = Sales > Origin = Sales Transaction Entry.

If you’re set up to post based on the transaction date, the posting date in the sales date entry window will be the same as the Journal Entry posting date in the General Ledger. The GLPOSTDT field in SOP10100 and SOP30200 will be the same as the TRXDATE in the GL20000 and GL30000 tables.

If you are set up to post by the batch date, the date used in the Sales Batch Entry window will be the date listed in GLPOSTDT field in the SY00500 table (postings master) will be what’s listed in the corresponding General Ledger tables.

What about advanced distribution? How does the date work with the pick pack and ship process? How is it tracked to the SOP10112 table?

With the SOP10112 table, GP uses the ‘system date’, which is the date/time on your workstation, not the date/time within GP itself. This date is recorded under the ‘Effective_Date’ field in the SOP10112 table. Each time the status of a record changes, GP inserts a new record into the SOP10112 table. For example, once an order is confirmed picked, a ‘3’ will be inserted as the status in the SOP10112 table at the time of confirmation.

You can access Advanced Distribution, first by setting it up with in GP, and then by selecting the All Sales Transaction option, which I have highlighted below.

I previously set up a fulfillment order ‘FULORD1009’, and am going through the pick, pack and ship process. As you can see below, prior to confirming it was picked, the SOP status is 6 (ready to print/post).

Before:

SOPNUMBE SOPTYPE ORD Effective_Date TIME1 SOPSTATUS USERID DEX_ROW_ID
FULORD1009 6 16384 00:00.0 40:59.0 2 CMorrison 31

After

SOPNUMBE SOPTYPE ORD Effective_Date TIME1 SOPSTATUS USERID DEX_ROW_ID
FULORD1009 3 16384 00:00.0 41:03.0 2 CMorrison 31

Below, I’ll list some other miscellaneous SOP questions that I’ve come across.

When entries are in the SOP10100 table, why do they have established invoice dates?

Each document will have a type (order, return, invoice, etc.) and whichever the user enters in the ‘Sales Transaction Window’ will determine if there is an invoice date and how that date will be reflected.

If you’d like to ensure that the invoice date will be updated automatically, there is a setting in the Sales Fulfillment Order/Invoice Setup window (Microsoft Dynamics GP > Tools > Setup > Sales > Sales Order Processing > Sales Document Setup > Fulfillment Order/Invoice) which will make this possible.

In this example, we will use a fulfillment order. The date the user enters in the transaction entry will be the date used.

The way my Fabrikam is setup, the default system date is 04/12/2027, which is how it will show in the screenshot below.

The invoice now has an established date of 04/12/2027.

What is ‘ACTLSHIP’ date? How is it different from a requested ship date?

Driven by how the document is set up and what is entered in the Sales Transaction Entry window, the Actual Ship Date will reflect the date on which the order was confirmed if the Update Actual Ship Date During Confirm Ship Option has been selected.

If not marked, the Actual Ship Date will be assigned from GP using the line item from SOP10200 (unposted work line detail) or SOP30300 (historical transaction line detail).

If a transaction has not been posted, how can it have an invoice date?

Most document types will have an invoice date once they are saved and the date listed once it’s been posted will be dependent on how you have your SOP transactions set up. (covered above).

Is the date of a transaction while in the SOP10100 table meaningful?

In short, it can be for a fulfillment order/invoice. Until the SOPTYPE switches from 6 to 3 (order to invoice) you likely don’t need to monitor the date too closely. Once it is posted, the date of posting could prove to be very helpful.

 

SOP SOP

If you have any questions about sales order processing in Dynamics GP, please feel free to reach out to 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