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
|