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.
