janos.nagy

    January 2010 Previous month Next month

    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.