Skip to Content

In last few months I have found several questions on the SAP Business One forums – SQL Queries about  Bill of Materails. In this blog i would like to demonstrate a features of MS SQL 2005 Common Table Expressions used as recursive queries.

Common use of recursive queries: Returning hierarchical data like
– displaying data in a bill of materials (BOM) in Production or Sales module
– displaying sales document flow from Sales Order to Credit Memo

Recursive CTE

Recursive CTE structure must contain one Ancor and Recusrive Member.The anchor member(s) creating the first invocation or base result set (T0), then the recursive member(s) using the base or previous result set (Ti) as input to gerenare the next result set (Ti+1) as output. The process gies until an empty result set. The results joined by UNION ALL operators. The depth of the recursion can be managed by OPTION (MAXRECURSION [i])

Syntax:

WITH cte_name ( column_name [,...n] )
AS
(
  CTE_query_definition –- Anchor member is defined.
 UNION ALL
  CTE_query_definition –- Recursive member is defined referencing cte_name.
)

-- Statement using the CTE
SELECT * FROM cte_name

Expanding a Product Tree

Product Tree defintion is stored at OITT and ITT1 tables. The following Query will expand the product tree:

WITH BOM (Code, Level, TreeType) AS
(
 SELECT
    T0.Code, 0 as Level, T2.TreeType
  FROM
    dbo.OITT T0 inner join OITM T2 on T0.Code = T2.ItemCode
  WHERE    
    T0.Code =  'PRODUCT_CODE'
UNION ALL
  SELECT    
    T1.Code , Level +1 , T2.TreeType
  FROM        
    dbo.ITT1 AS T1 inner join OITM T2 on T1.Code = T2.ItemCode
    JOIN BOM ON T1.Father = BOM.Code
)

SELECT * FROM BOM OPTION (MAXRECURSION 99)

List of Transaction related to a Product Tree

List of Transactions of one Product can be determined by easily with recursively. The Issue for Production and Receipt from Production documents are linked to warehouse journal (table OINM) by the fields ApplObj, AppObjType, AppObjAbs.

The list the transactions of a production order  related to one product from Warehouse Journal can be received by the following query (continue example of expanding product tree):

SELECT
  T0.TransType,T1.DocNum, T0.ItemCode, T0.InQty, T0.OutQty,
  T0.TransValue, T0.AppObjType,T2.Level
FROM
  OINM T0 INNER JOIN OWOR T1 ON T0.AppObjAbs = T1.DocEntry
  INNER JOIN BOM T2 ON T0.ItemCode = T2.Code
WHERE
  T0.ApplObj = '202'
     

COGS of Sales BOM

Cost price of multi level Sales BOM also easy to determine based on product tree query:

SELECT
  T0.TransType, T0.Base_Ref,  sum( T0.TransValue)
FROM
     oinm T0 INNER JOIN BOM T1 ON T0.ItemCode = T1.Code
GROUP BY
     T0.TransType, T0.Base_Ref
          

Sales Document Flow

In SAP B1 the sales documents can be linked together by Copy To – Copy From function on the forms. In the document tables the BaseLine, BaseType, BaseEntry fields of the target document define these relations to LineNum, ObjType and DocEntry fields of the source document. To handle a view – wich contains there (and necessary) informations – is easier than use tables indivudailly to make analisys on the sales process.

CREATE VIEW CTE_sales
AS
SELECT
  '1.ORDER' [Level],T0.DocEntry,T0.DocNum,T0.DocDate,T0.ObjType,
  T0.CardCode,T1.LineNum,T1.ItemCode,T1.Quantity,T1.BaseType,
  T1.BaseRef,T1.BaseEntry,T1.BaseLine
FROM
  ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
UNION ALL
  SELECT
  '2.DELIVERY' [Level],T0.DocEntry,T0.DocNum,T0.DocDate,T0.ObjType,
  T0.CardCode,T1.LineNum,T1.ItemCode,T1.Quantity,T1.BaseType,
  T1.BaseRef,T1.BaseEntry,T1.BaseLine
FROM
  ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
UNION ALL
SELECT
  '3.RETURN' [Level],T0.DocEntry,T0.DocNum,T0.DocDate,T0.ObjType,
  T0.CardCode,T1.LineNum,T1.ItemCode,T1.Quantity,T1.BaseType,
  T1.BaseRef,T1.BaseEntry,T1.BaseLine
FROM
  ORDN T0 INNER JOIN RDN1 T1 ON T0.DocEntry = T1.DocEntry
UNION ALL
SELECT
  '4.INVOICE' [Level],T0.DocEntry,T0.DocNum,T0.DocDate,T0.ObjType,
  T0.CardCode,T1.LineNum,T1.ItemCode,T1.Quantity, T1.BaseType,
  T1.BaseRef,T1.BaseEntry,T1.BaseLine
FROM
  OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
UNION ALL
SELECT
  '5.CREDIT MEMO' [Level],T0.DocEntry,T0.DocNum,T0.DocDate,T0.ObjType,
  T0.CardCode,T1.LineNum,T1.ItemCode,T1.Quantity, T1.BaseType,
  T1.BaseRef,T1.BaseEntry,T1.BaseLine
FROM
  ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
          

Use the following recursive query based on CTE_sales view to track a sales order:

WITH sales( [Level],CardCode,DocEntry,DocNum,DocDate,ObjType,LineNum,
  ItemCode,Quantity, BaseEntry, BaseLine, BaseType) AS
(
  SELECT
    T0.[Level],T0.CardCode,T0.DocEntry, T0.DocNum,T0.DocDate,T0.ObjType,
    T0.LineNum,T0.ItemCode,T0.Quantity,T0.BaseEntry,T0.BaseLine,T0.BaseType
  FROM
    CTE_sales T0
  WHERE
    T0.ObjType = '17' AND T0.DocNum = 'SALES_ORDER_NUMBER'
UNION ALL
  SELECT
    T0.[Level], T0.CardCode,T0.DocEntry,T0.DocNum,T0.DocDate,T0.ObjType,
    T0.LineNum,T0.ItemCode,T0.Quantity,T0.BaseEntry,T0.BaseLine,T0.BaseType
  FROM
    CTE_sales T0 INNER JOIN sales T1 ON T1.DocEntry= T0.BASeEntry
    AND T1.ObjType = T0.BaseType AND T1.LineNum = T0.BaseLine
)

SELECT * FROM sales ORDER BY [Level]

Of course there examples can be migrated to a stored procedures or can be run from SAP B1 application with paramters.

Conclusion

A recursive CTE can greatly simplify, easy to use to display hierarchical data production or sales analisys.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply