Using T-SQL and recursive CTE to find related sales documents

In Sales Order Processing, master numbers are used to track the flow of documents throughout the sales process. Documents with the same master number as the current document will be listed in the Sales Document Detail Entry window. A master number is a single number assigned to a series of documents that is used to track related documents. For example, when you issue a quote, then transfer it to an order, and then an invoice, each document will be assigned the same master number. Each document also maintains its own document number.

However, I have seen cases where the master number becomes damaged or out of sequence causing SOP documents to no longer follow a chain. The case was also recently reported in the Partner forum and the partner wanted to understand how they could find what SOP documents were related to each other. Fortunately enough, Microsoft Dynamics GP also tracks the original document number (ORIGNUMB) and document type (ORIGTYPE) from which a SOP document originated. These columns can be found on the Sales Transaction Work table (dbo.SOP10100) and Sales Transaction History (dbo.SOP30200) table.

Given this, we could create a hierarchical structure of documents were the previous document state -- say for example a quote -- becomes the parent of the following document state -- say for example the order that originated from the quote. Knowing this, we can then produce the following recursive Common Table Expression (CTE) -- see my previous article Using T-SQL and recursive CTE to generate a BOM tree for a definition of CTE and another practical example.

-- Mariano Gomez, MVP
-- This query is provided "AS IS". No warranties expressed or implied.
with sopHierarchy as (
 select
  CAST(RTRIM(ORIGNUMB) + '/' + RTRIM(SOPNUMBE) AS VARCHAR(MAX)) as sopPath, sopnumbe, soptype, orignumb, origtype, 0 as lvl from (
  select sopnumbe, soptype, orignumb, origtype from sop10100
  union all
  select sopnumbe, soptype, orignumb, origtype from sop30200
 ) anchlvl

 union all

 select CAST(RTRIM(h.sopPath) + '/' + RTRIM(sublvl.SOPNUMBE) AS VARCHAR(MAX)) as sopPath, sublvl.sopnumbe, sublvl.soptype, sublvl.ORIGNUMB, sublvl.ORIGTYPE, h.lvl + 1 from (
  select sopnumbe, soptype, orignumb, origtype from sop10100
  union all
  select sopnumbe, soptype, orignumb, origtype from sop30200
 ) sublvl inner join sopHierarchy h on (sublvl.ORIGTYPE = h.SOPTYPE) and (sublvl.ORIGNUMB = h.SOPNUMBE)
)
select distinct lvl, sopPath, soptype, sopnumbe, origtype, orignumb, SPACE(lvl * 4) + sopnumbe as hierarchy
from sopHierarchy  
order by sopPath

When this query is executed against the TWO database on SQL, the following result is produced:


Note the path and graphic hierarchical representation of the data on the query window. Also, the level of the relationship is detailed. In this case, this query proved extremely helpful to find related SOP documents in lieu of a master number.

In general, recursive CTEs can be a very powerful tool to uncover data relationships and aid in rebuilding missing or damaged information.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/