Siebel EAI >  VB Script to Update views and responsibilities

VB Script to Update views and responsibilities

Here is an example of using VB Script to connect to Siebel and update or delete Views and Responsibility combinations in Siebel.

Each view and responsibility is linked in the screen Administration - Application -> Responsibilites screen

We can add new items or delete old items with this code.

You need to create an excel sheet, press Alt+ f11.

Add the code in this panel.

In the input screen you need to add 4 fields for input. For example

 

Responsibility (Required) View(Required) Required (Y or N) Required (ADD or DEL)
Administrator 1  Account Business Admin View N ADD
Administrator 1 Account IT Admin View N ADD
Administrator 1 Activity Business Admin View N ADD
Administrator 1  Activity IT Admin View N DEL

 

You also need to set the connetion Parameters, either by hard coding them or by creating a second tab in excel as shown below

 

 

Function ConnectToSiebel(ByRef oApp As SiebelDataControl) As Integer
' Set the connection params
Dim SiebelServer As String
SiebelServer = ConfigSheet.Cells(2, 2)    'or give the value of the server here

' Siebel Enterprise Name
Dim SiebelEnterprise As String
SiebelEnterprise = ConfigSheet.Cells(3, 2)   'or give the value of the enterprise here

' Siebel Login Name
Dim SiebelLogin As String
SiebelLogin = ConfigSheet.Cells(4, 2)      'or give the siebel administrator login here

' Siebel Login Password (Case Sensitive)
Dim SiebelPass As String
SiebelPass = ConfigSheet.Cells(5, 2)       'or give the Siebel Password here

 

Here is the whole code:

 

 

Option Explicit

'Siebel Objects
Dim SiebelApp As SiebelDataControl
Dim boResp As SiebelBusObject
Dim bcResp As SiebelBusComp
Dim bcFeature As SiebelBusComp
Dim bcAssoc As SiebelBusComp

'Workbooks to use
Dim ConfigSheet As Worksheet
Dim DataSheet As Worksheet

'Constants
Const RED = 30
Const YELLOW = 45
Const GREEN = 50

' *****************************************************************************************
' Create and Verify created records
' *****************************************************************************************
Sub RespViews()
'Set the workbooks to work with
Set ConfigSheet = Worksheets("CFG")
Set DataSheet = Worksheets("RESP_VIEWS")

'Connect to Siebel
If ConnectToSiebel(SiebelApp) <> 0 Then
MsgBox SiebelApp.GetLastErrText(), vbCritical, "RespViews Association"
Exit Sub
End If

'Setup siebel objects
Set boResp = SiebelApp.GetBusObject("Responsibility")
Set bcResp = boResp.GetBusComp("Responsibility")
Set bcFeature = boResp.GetBusComp("Feature Access")

'Activate Fields
bcResp.ActivateField "Name"
bcFeature.ActivateField "Name"
bcFeature.ActivateField "Read Only View"

'Check Opp Mode
If ConfigSheet.Cells(7, 2) = "" Then
MsgBox "Fill up the cell 'Verify Data' in the 'CFG' Spreadsheet", vbCritical, "RespViews Association"
Call CleanUp
Exit Sub
End If

'Set Opp Mod
Select Case Trim(ConfigSheet.Cells(7, 2))
Case "Deploy"
Call RunRespViews("DEPLOY")
Case "Check"
Call RunRespViews("CHECK")
Case "Deploy and Check"
Call RunRespViews("DEPLOY")
Call RunRespViews("CHECK")
Case Else
MsgBox "Fill up the cell 'Verify Data' in the 'CFG' Spreadsheet", vbCritical, "RespViews Association"
Call CleanUp
Exit Sub
End Select

'Cleanup objects
Call CleanUp
MsgBox "Loading complete. Check the log", vbInformation, "RespViews Association"
End Sub

' *****************************************************************************************
' Create or update existing resp x views associations
' *****************************************************************************************
Private Sub RunRespViews(sMode As String)
Dim iRowNum As Integer
Dim iErrCode As Integer
Dim sViewName As String
Dim sRespName As String
Dim sReadOnly As String
Dim sOpp As String

iRowNum = 1
Do While DataSheet.Cells(iRowNum + 2, 1) <> ""
iErrCode = 0
iRowNum = iRowNum + 1
sRespName = Trim(DataSheet.Cells(iRowNum + 1, 1))
sViewName = Trim(DataSheet.Cells(iRowNum + 1, 2))

' Siebel default is N
sReadOnly = Trim(DataSheet.Cells(iRowNum + 1, 3))
If sReadOnly <> "Y" And sReadOnly <> "N" Then
sReadOnly = "N"
End If

' Default operation is ADD
sOpp = Trim(DataSheet.Cells(iRowNum + 1, 4))
If sOpp <> "ADD" And sOpp <> "DEL" Then
sOpp = "ADD"
End If

' Find out the responsability
With bcResp
.ClearToQuery
.SetViewMode (3)
.SetSearchSpec "Name", "'" & sRespName & "'"
.ExecuteQuery (0)
End With

' Find out the view is already Associated
With bcFeature
If sMode = "DEPLOY" Then
If bcResp.FirstRecord() = 0 Then
WriteCell iRowNum + 1, 5, RED, "Error: Responsability not found"
Else
.ClearToQuery
.SetViewMode (3)
.SetSearchSpec "Name", "'" & sViewName & "'"
.ExecuteQuery (0)

'View Not Associated yet
If .FirstRecord() = 0 Then
If sOpp = "DEL" Then
WriteCell iRowNum + 1, 5, GREEN, "Deassociated"
ElseIf sOpp = "ADD" Then
Set bcAssoc = bcFeature.GetAssocBusComp()
With bcAssoc
.ActivateField "Name"
.ClearToQuery
.SetViewMode (3)
.SetSearchSpec "Name", "'" & sViewName & "'"
.ExecuteQuery (0)

If .FirstRecord() = 0 Then
iErrCode = iRowNum + 1
WriteCell iRowNum + 1, 5, RED, "Error: View not found"
Else
.Associate (1)
If .GetLastErrCode() > 0 Then
iErrCode = iRowNum + 1
WriteCell iRowNum + 1, 5, RED, "Error " & .GetLastErrCode() & ": " & .GetLastErrText()
Else
bcFeature.WriteRecord
If bcFeature.GetLastErrCode() > 0 Then
iErrCode = iRowNum + 1
WriteCell iRowNum + 1, 5, RED, "Error " & bcFeature.GetLastErrCode() & ": " & bcFeature.GetLastErrText()
End If
End If
End If
End With
Set bcAssoc = Nothing

If iErrCode = 0 Then
.SetFieldValue "Read Only View", sReadOnly
If .GetLastErrCode() > 0 Then
iErrCode = iRowNum + 1
WriteCell iRowNum + 1, 5, RED, "Error " & .GetLastErrCode() & ": " & .GetLastErrText()
Else
.WriteRecord
If .GetLastErrCode() > 0 Then
iErrCode = iRowNum + 1
WriteCell iRowNum + 1, 5, RED, "Error " & .GetLastErrCode() & ": " & .GetLastErrText()
Else
WriteCell iRowNum + 1, 5, GREEN, "Associated"
End If
End If
End If
End If
' View associated but we can have updates to perform
Else 'FirstRecord > 0
If sOpp = "DEL" Then
.DeleteRecord
If .GetLastErrCode() > 0 Then
WriteCell iRowNum + 1, 5, RED, "Error " & .GetLastErrCode() & ": " & .GetLastErrText()
Else
.WriteRecord
If .GetLastErrCode() > 0 Then
WriteCell iRowNum + 1, 5, RED, "Error " & .GetLastErrCode() & ": " & .GetLastErrText()
Else
WriteCell iRowNum + 1, 5, GREEN, "Deassociated"
End If
End If
Else
If sReadOnly = .GetFieldValue("Read Only View") Then
WriteCell iRowNum + 1, 5, GREEN, "Associated"
Else
.SetFieldValue "Read Only View", sReadOnly
If .GetLastErrCode() > 0 Then
WriteCell iRowNum + 1, 5, RED, "Error " & .GetLastErrCode() & ": " & .GetLastErrText()
Else
.WriteRecord
If .GetLastErrCode() > 0 Then
WriteCell iRowNum + 1, 5, RED, "Error " & .GetLastErrCode() & ": " & .GetLastErrText()
Else
WriteCell iRowNum + 1, 5, GREEN, "Updated"
End If
End If
End If
End If
End If
End If
ElseIf sMode = "CHECK" Then
If bcResp.FirstRecord() = 0 Then
WriteCell iRowNum + 1, 6, RED, "Error: Responsability not found"
Else
.ClearToQuery
.SetViewMode (3)
.SetSearchSpec "Name", "'" & sViewName & "'"
.ExecuteQuery (0)

If .FirstRecord() = 0 Then
If sOpp = "DEL" Then
WriteCell iRowNum + 1, 6, GREEN, "Record OK"
Else
WriteCell iRowNum + 1, 6, RED, "Error: View not found"
End If
Else
If sOpp = "DEL" Then
WriteCell iRowNum + 1, 6, RED, "Error: View still associated"
Else
If sReadOnly <> .GetFieldValue("Read Only View") Then
WriteCell iRowNum + 1, 6, YELLOW, "Warning: Mismatch in 'Read Only View' field"
Else
WriteCell iRowNum + 1, 6, GREEN, "Record OK"
End If
End If
End If
End If
End If
End With
Loop
End Sub

' *****************************************************************************************
' Write to a cell
' *****************************************************************************************
Function WriteCell(iRow As Integer, iCol As Integer, iColor As Integer, sMessage As String)
DataSheet.Cells(iRow, iCol).Font.ColorIndex = iColor
DataSheet.Cells(iRow, iCol).Value = sMessage
End Function

' *****************************************************************************************
' Connect to Siebel
' *****************************************************************************************
Function ConnectToSiebel(ByRef oApp As SiebelDataControl) As Integer
' Set the connection params
Dim SiebelServer As String
SiebelServer = ConfigSheet.Cells(2, 2)

' Siebel Enterprise Name
Dim SiebelEnterprise As String
SiebelEnterprise = ConfigSheet.Cells(3, 2)

' Siebel Login Name
Dim SiebelLogin As String
SiebelLogin = ConfigSheet.Cells(4, 2)

' Siebel Login Password (Case Sensitive)
Dim SiebelPass As String
SiebelPass = ConfigSheet.Cells(5, 2)

' SWSE URL to the eCommunications Object Manager Component
Dim SiebelURL As String
SiebelURL = "host=""siebel://" + SiebelServer + "/" + SiebelEnterprise + "/eCommunicationsObjMgr_enu"""

' Create a Siebel Object
Set oApp = CreateObject("SiebelDataControl.SiebelDataControl.1")
' Try to login to Siebel
oApp.Login SiebelURL, SiebelLogin, SiebelPass
' Returns 0 = Success, <> 0 = Error
ConnectToSiebel = oApp.GetLastErrCode()

End Function

' *****************************************************************************************
' Release the objects
' *****************************************************************************************
Function CleanUp()
Set bcFeature = Nothing
Set bcResp = Nothing
Set boResp = Nothing
Set SiebelApp = Nothing
End Function