In this Blog I would like to show how to export Authorization from SAP B1 into microsoft excel using SAP B1 SDK. The final format will be: Users displayed in the columns, and Authorization will be displayed in the rows.

  

Background - DI API and SBOB Object

The SBObob object is raw data access object that enables you to retrieve information quickly and easily. The returned data is usually a Recordset object that enables data manipulation. The SBObob object has 2 methods related to permissions: GetSystemPermisson and SetSystempermission.

The GetSystemPermission method allows to read a specific permissions of a User. Returns a single record that contains the permission name of an item in the General Authorization Tree according to a specified permission ID and user code.

Public Function GetSystemPermission( _
 ByVal UserName As String, _
 ByVal PermissionID As String _
 ) As Recordset
Dim oSBObob As SAPbobsCOM.SBObob
Dim oRecordSet As SAPbobsCOM.Recordset
'// Get an initialized SBObob object
oSBObob = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge)
'// return the General permission (142)
oRecordSet = oSBObob.GetSystemPermission("manager", "142")
'//print the permission value (Read/Write=1 ,ReadOnly=2,NoAuthorization=3,
'//VariousAuthorization=4,NotDefined=6)
Debug.WriteLine(oRecordSet.Fields.Item(0).Value())

The SetSystemPermission method allows to set a specific permissions of a User.

Public Function SetSystemPermission( _
ByVal UserName As String, _
ByVal PermissionID As String _
ByVal Permission As Long)
Dim oSBObob As SAPbobsCOM.SBObobDim
oRecordSet As SAPbobsCOM.Recordset
'// Get an initialized SBObob object
oSBObob = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge)
'// Get an initialized Recordset object
oRecordSet = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
'//set the General permission to Read/Write(Read/Write=1 ,ReadOnly=2,NoAuthorization=3,
'//VariousAuthorization=4,NotDefined=6)
Call oSBObob.SetSystemPermission("manager", "142", 1)

The list of the users can be received using GetUserList method of SBObob object, which returns a recordset that contains a list of user codes defined in the SAP Business One company database.

Public Function GetUserList() As Recordset

 

The full list of Authorizations can be found inside the application, under Administration\System Initialization\Authorizations\General Authorizations. On the form the complete authorization tree can be accessed via UI API (form type = 951) and it is contains some hidden columns for the hierarchy like Permission ID, Level on the Matrix, Number of Sons in the matrix.

Exporting Users Permissions

In MS Excel, the VBA (Visual Basic for Applications) can be used for creating simple "Addons" like applications with SDK UI API and DI API. (simiral to excel macros). The langugae is similar to VB 6 version. To write the code, open a new workbook and launch VBA ( ALT+F11), and Add the SAP Business One UI API and SAP B1 DI API into the list of references from Tools\References menu.

Steps to be completed to receive the complete list of Authorizations for each user in VBA:

1. Connect to company (based on SDK help)

Dim SboGuiApi As New SAPbouiCOM.SboGuiApi
Dim SBO_App As SAPbouiCOM.Application
Dim oCompany As New SAPbobsCOM.Company
SboGuiApi.Connect
("0030002C0030002C00530041005000420044005F00440061007400
650076002C0050004C006F006D0056004900490056")
Set SBO_App = SboGuiApi.GetApplication()
Set oCompany = SBO_App.Company.GetDICompany()

2. Open the authorization form by ActivateMenuItem ("3332")command

SBO_App.ActivateMenuItem ("3332")
Dim oForm As SAPbouiCOM.FormSet oForm = SBO_App.Forms.ActiveForm

3. Expand the tree, and Download all tree elements into excel sheet.

' Headers in Excel
ActiveSheet.Cells(1, 1) = "ID"
ActiveSheet.Cells(1, 2) = "Name"
ActiveSheet.Cells(1, 3) = "Level"
ActiveSheet.Cells(1, 4) = "Number of Sons"
' Expand Autorization tree
oForm.Items.Item("13").Click (ct_Regular)
'Receive permission id and name from matrix object ItemUid = "6"row = 2
Dim oMatrix As SAPbouiCOM.Matrix
Set oMatrix = oForm.Items.Item("6").Specific
For i = 1 To oMatrix.VisualRowCount
  Dim oEditPermCode As SAPbouiCOM.EditText
  Dim oEditPermName As SAPbouiCOM.EditText
  Dim oEditPermLevel As SAPbouiCOM.EditText
  Dim oEditPermNumOfSons As SAPbouiCOM.EditText
  Set oEditPermCode = oMatrix.Columns.Item("0").Cells.Item(i).Specific
  et oEditPermName = oMatrix.Columns.Item("1").Cells.Item(i).Specific
  Set oEditPermLevel = oMatrix.Columns.Item("7").Cells.Item(i).Specific
  Set oEditPermNumOfSons = oMatrix.Columns.Item("6").Cells.Item(i).Specific
  ActiveSheet.Cells(row, 1) = oEditPermCode.String
  ActiveSheet.Cells(row, 2) = oEditPermName.String
  ActiveSheet.Cells(row, 3) = oEditPermLevel.String
  ActiveSheet.Cells(row, 4) = oEditPermNumOfSons.String
  If oEditPermCode.String = "UP_MAIN_HEADER" Then Exit For
  row = row + 1
Next

4. Receive the list of the users, and read the permissions.Here the permisson value has to be decoded by BoPermission Enumeration by the following:
- RW for Full Access
- RO for Read Only Access
- NO for No Authorization
- VA for Various Authorization
- ND for not defined value

Dim oRsSUers As SAPbobsCOM.Recordset
Dim oSBObob As SAPbobsCOM.SBObob
Set oSBObob = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge)
Set oRsSUers = oSBObob.GetUserList()

 
col = 5
Do While Not oRsSUers.EOF
ActiveSheet.Cells(1, col) = oRsSUers.Fields.Item(0).Value
row = 2
Do While Not (ActiveSheet.Cells(row, 1) = "UP_MAIN_HEADER")
If ActiveSheet.Cells(row, 4) = 0 Then
Dim oRsPerm As SAPbobsCOM.Recordset
Set oRsPerm = oSBObob.GetSystemPermission(oRsSUers.Fields.Item(0).Value,
ActiveSheet.Cells(row, 1))


Dim s As String
Select Case oRsPerm.Fields.Item(0).Value
Case 1
s = "RW"
Case 2
s = "RO"
Case 3
s = "NO"
Case 4
s = "VA"
Case 6
s = "ND"
Case Else
s = "n.a."
End Select
ActiveSheet.Cells(row, col) = s
endif
row = row + 1
Loop
col = col + 1
oRsSUers.MoveNext
Loop

5. Authorizations can be editied in lines where the Column  D "Number of Sons" are 0 (tree leafs).

Importing Permissions

To import edited permissions, scan through on the edited file, and read values by users, and encode them by BoPermission Enumeration. Please Note: SuperUser's authorization cannot be changed, so superusers can be deleted from excel sheet before you importing it.

' Connect to company
Dim oSBObob As SAPbobsCOM.SBObob

Set oSBObob = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge)
col = 5
Do While ActiveSheet.Cells(1, col) <> ""
row = 2
Do While ActiveSheet.Cells(row, 1) <> "UP_MAIN_HEADER"
' Where has no son, There can Authoritaion rewrited
If ActiveSheet.Cells(row, 4) = 0 Then
Select Case ActiveSheet.Cells(row, col)
Case "RW"
lPermission = 1
Case "RO"
lPermission = 2
Case "NO"
lPermission = 3
Case "VA"
lPermission = 4
Case "ND"
lPermission = 6
Case Else
lPermission = -1
End Select
' Set the permission by SBObob.SetSystemPermission function
If lPermission <> -1 Then
Call oSBObob.SetSystemPermission(ActiveSheet.Cells(1, col), _
ActiveSheet.Cells(row, 1), lPermission)
End If
End If
row = row + 1
Loop
col = col + 1
oRsSUers.MoveNext
Loop

Conclusion

Ms Excel and VBA is offers a good environment to write and small usefull applications related to SAP B1 using SDK components. By methods/features provided by SDK the Permission of the Authorization tree can manipulated.

Complete sourcecode can be downloaded from here

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.

Actions