August 7, 2017
A few weeks ago I gave a presentation to a regional group of ERP users, some with an Oracle Database backend and some with a Microsoft SQL Server backend. The original intention for the presentation was to demonstrate an application package that I created the previous year which maintains and manages production equipment maintenance unplanned work orders as well as preventive maintenance work orders in the Visual Manufacturing ERP system, in addition to the production work orders that the Visual Manufacturing ERP system supports out of the box. My custom application package was created specifically to use an Oracle Database backend with ODP.Net data access using the Microsoft Visual Studio 2010 version of VB.Net (the VB.Net language is similar to C#, essentially just with slightly different grammar rules), and the application package could not be purchased by other companies.
A bit of a dilemma as I planned to show a computer package that was not only not available for commercial purchase, but also where the majority of the audience members were using a Microsoft SQL Server backend. The version of the Visual Manufacturing ERP package (8.0.0) that was released roughly two years ago introduced a “free” database platform independent API Toolkit that may be used with Visual Studio 2010 and later to not only retrieve information from the ERP database, but also create various types of document transactions in the ERP system. A “free” database platform independent API, ah… the perfect solution to the bit of a dilemma I had with the presentation. I told the organizer of the regional ERP group that I could probably create a version of the preventive and unplanned maintenance package that uses the “free” database platform independent API for 99+% of its tasks, and that reproduces the core functionality of the Oracle Database specific version that I wrote a year earlier, despite the fact that a year earlier the API Toolkit was incompatible with Oracle Database due to a design flaw (triggered whenever any column in a specific database table contained more than 64 characters), and the API Toolkit documentation ranges from OK but not very useful, to incomplete and inaccurate. My five slide presentation morphed to a lengthy presentation with nearly 90 slides, as the focus of the presentation shifted from demonstrating how I extended the capabilities of an ERP system to manage equipment maintenance tasks, to documenting how to beat persuade the “free” database platform independent API Toolkit into submission to accomplish various other tasks in addition to reproducing the core functionality of the application that I created a year ago. I believe that as a result of my presentation the API Toolkit is now much better documented, although I did leave a few stones unturned.
The presentation and associated files may be downloaded from this link. The Crystal Reports plugin that is used by the project is a bit difficult to find through internet searches on the manufacturer’s website, so I downloaded those files and made them available from this link. These files are located on Google Drive, click the down pointing arrow near the top-right of the Google Drive page to download the files (see the download picture in this article for assistance).
The following is a slightly edited version of the description of the presentation that I sent to the organizer of the regional ERP group roughly a month before the meeting.
Visual Manufacturing offers many useful built-in features for manufacturing environments. One task that either is not available or is poorly implemented/integrated into Visual Manufacturing is unplanned maintenance and preventive maintenance for equipment (resources) used in the manufacturing process. As customers demand that their suppliers demonstrate the existence of formal preventive maintenance programs, there is a growing need for a robust maintenance package – and it is especially helpful if that package directly integrates with Visual Manufacturing without any extra cost add-ons. The MFC Equipment Maintenance package, which was developed internally last year to help formalize and standardize the process of equipment maintenance in an environment with 25+ maintenance workers, will be demonstrated at the next meeting. The package essentially behaves as an extension to Visual Manufacturing’s Shop Resource Maintenance module, allowing maintenance staff to quickly generate, release, and close maintenance work orders in Visual Manufacturing to meet the requirements of both unplanned maintenance as well as preventive maintenance. As the maintenance work orders are maintained in the Visual Manufacturing database, all the native Visual modules for creating labor tickets, purchasing, issuing parts to work orders, and rolling up costs for accounting work just as they do with normal production work orders.
An equipment maintenance starter package, which primarily uses the free Visual 8/9 API Toolkit to duplicate the core functionality of the MFC Equipment Maintenance package, will also be demonstrated, with the VB.Net source code provided to attendees. Additional methods to exploit the Visual 8/9 API Toolkit will also be shown, including extended examples of a tool crib self-checkout application, a fast purchase order receiving application with barcode label printing, and a few other techniques for using the Visual 8/9 API Toolkit (including handling unexpected errors) will also be shown.
Needless to say, I ended up expanding the scope of the presentation a bit, as I found that the documentation of the API Toolkit left a lot of functionality undocumented. Demonstrating how Visual Manufacturing could be extended to manage unplanned maintenance and planned maintenance was the original intention of the presentation, but it is interesting to see how the scope expands once database independent approaches are implemented. Below is a screen capture of one of the main windows in the MFC Equipment Maintenance application, which is used to manage the maintenance of individual pieces of equipment used in the production of customer ordered parts. Oddly, some of the commercially available equipment maintenance packages charge unexpectedly high premiums for features such as maintaining more than two machine counters for a resource, storing basic warranty information, and integration with ERP/accounting programs.
The “starter” version of the program that I created for the presentation, which uses the API Toolkit to reproduce the core program functionality, is of course not as full featured, but allows maintaining additional information about resource IDs that are defined in Visual Manufacturing, creating and printing unplanned maintenance work orders, creating preventive maintenance templates (masters) and then applying those templates for resource IDs, and creating and printing preventive maintenance work orders. Below is a screen capture showing the main window for the “starter” version of the program that was included with the presentation.
The bottom of the window in both cases is a HTML web page that shows recent unplanned maintenance work order history for the selected resource ID, as well as repair parts that were issued through Visual Manufacturing to those work orders. The methods for retrieving that information from the database are a bit different when using ODP.Net compared to using the database independent methods that are exposed by the Visual API Toolkit. Before showing those differences, however, I thought that it would be best to first potentially confuse the issue by showing five slightly different VB.Net coding syntaxes to perform essentially the same set of operations using the API Toolkit (this helps to demonstrate different coding methods used by different programmers and a potential source for confusion while referring to the documentation for the API Toolkit). The task to be accomplished is to update an unplanned maintenance work order description, the associated maintenance operation’s estimated labor hours, and the associated description for the maintenance operation to repair the problem that is defined in the work order description.
Method 1:
objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
For i = 0 To objWO.Tables.Count - 1
If objWO.Tables(i).TableName = "WORKORDER_BINARY" Then
If objWO.Tables(i).Rows.Count > 0 Then
objWO.Tables(i).Rows(0).Item("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
objWO.Tables(i).Rows(0).Item("BITS_LENGTH") = Len(txtFailureNote.Text)
objWO.Tables(i).Rows(0).Item("TYPE") = "D"
End If
End If
If objWO.Tables(i).TableName = "OPERATION" Then
If objWO.Tables(i).Rows.Count > 0 Then
objWO.Tables(i).Rows(0).Item("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
objWO.Tables(i).Rows(0).Item("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
End If
End If
If objWO.Tables(i).TableName = "OPERATION_BINARY" Then
If objWO.Tables(i).Rows.Count > 0 Then
objWO.Tables(i).Rows(0).Item("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
objWO.Tables(i).Rows(0).Item("BITS_LENGTH") = Len(txtFailureRepair.Text)
objWO.Tables(i).Rows(0).Item("TYPE") = "D"
End If
End If
Next i
Method 2:
objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
For i = 0 To objWO.Tables.Count - 1
If objWO.Tables(i).TableName = "WORKORDER_BINARY" Then
If objWO.Tables(i).Rows.Count > 0 Then
objWO.Tables(i).Rows(0)("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
objWO.Tables(i).Rows(0)("BITS_LENGTH") = Len(txtFailureNote.Text)
objWO.Tables(i).Rows(0)("TYPE") = "D"
End If
End If
If objWO.Tables(i).TableName = "OPERATION" Then
If objWO.Tables(i).Rows.Count > 0 Then
objWO.Tables(i).Rows(0)("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
objWO.Tables(i).Rows(0)("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
End If
End If
If objWO.Tables(i).TableName = "OPERATION_BINARY" Then
If objWO.Tables(i).Rows.Count > 0 Then
objWO.Tables(i).Rows(0)("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
objWO.Tables(i).Rows(0)("BITS_LENGTH") = Len(txtFailureRepair.Text)
objWO.Tables(i).Rows(0)("TYPE") = "D"
End If
End If
Next i
Method 3:
objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
If objWO.Tables("WORKORDER_BINARY").Rows.Count > 0 Then
objWO.Tables("WORKORDER_BINARY").Rows(0).Item("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
objWO.Tables("WORKORDER_BINARY").Rows(0).Item("BITS_LENGTH") = Len(txtFailureNote.Text)
objWO.Tables("WORKORDER_BINARY").Rows(0).Item("TYPE") = "D"
End If
If objWO.Tables("OPERATION").Rows.Count > 0 Then
objWO.Tables("OPERATION").Rows(0).Item("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
objWO.Tables("OPERATION").Rows(0).Item("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
End If
If objWO.Tables("OPERATION_BINARY").Rows.Count > 0 Then
objWO.Tables("OPERATION_BINARY").Rows(0).Item("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
objWO.Tables("OPERATION_BINARY").Rows(0).Item("BITS_LENGTH") = Len(txtFailureRepair.Text)
objWO.Tables("OPERATION_BINARY").Rows(0).Item("TYPE") = "D"
End If
Method 4:
objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
If objWO.Tables("WORKORDER_BINARY").Rows.Count > 0 Then
objWO.Tables("WORKORDER_BINARY").Rows(0)("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
objWO.Tables("WORKORDER_BINARY").Rows(0)("BITS_LENGTH") = Len(txtFailureNote.Text)
objWO.Tables("WORKORDER_BINARY").Rows(0)("TYPE") = "D"
End If
If objWO.Tables("OPERATION").Rows.Count > 0 Then
objWO.Tables("OPERATION").Rows(0)("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
objWO.Tables("OPERATION").Rows(0)("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
End If
If objWO.Tables("OPERATION_BINARY").Rows.Count > 0 Then
objWO.Tables("OPERATION_BINARY").Rows(0)("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
objWO.Tables("OPERATION_BINARY").Rows(0)("BITS_LENGTH") = Len(txtFailureRepair.Text)
objWO.Tables("OPERATION_BINARY").Rows(0)("TYPE") = "D"
End If
Method 5:
Dim drRow As Lsa.Data.DataRow
objWO.Load("W", strWorkorderBaseID, strWorkorderLotID, "0")
If objWO.Tables("WORKORDER_BINARY").Rows.Count > 0 Then
drRow = objWO.Tables("WORKORDER_BINARY").Rows(0)
drRow("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureNote.Text)
drRow("BITS_LENGTH") = Len(txtFailureNote.Text)
drRow("TYPE") = "D"
End If
If objWO.Tables("OPERATION").Rows.Count > 0 Then
drRow = objWO.Tables("OPERATION").Rows(0)
drRow("RUN") = Convert.ToDouble(txtFailureEstimatedHours.Text)
drRow("RUN_HRS") = Convert.ToDouble(txtFailureEstimatedHours.Text)
End If
If objWO.Tables("OPERATION_BINARY").Rows.Count > 0 Then
drRow = objWO.Tables("OPERATION_BINARY").Rows(0)
drRow("BITS") = System.Text.Encoding.Default.GetBytes(txtFailureRepair.Text)
drRow("BITS_LENGTH") = Len(txtFailureRepair.Text)
drRow("TYPE") = "D"
End If
Method #5 above allows working with the API Toolkit using syntax that is somewhat similar to the syntax that is used with ODP.Net, where individual rows are processed one at a time with an OracleDataReader object, or bind variable values are specified for creating a single row with an OracleCommand object.
—
Let’s take a look at how the information at the bottom of the program’s main window is retrieved first in the application that I created last year, and then in the “starter” version that uses the API Toolkit. In programming, it is typically best from a performance point of view to perform tasks either once or not at all (don’t perform unnecessary tasks). As such, I tend to set up SQL statements that may be executed multiple times in various procedures in the form’s Load event. When processing data, I try to minimize the number of executions of SQL statements sent to the database instance by carefully crafting SQL statements and then using what used to be termed as Control Break processing to act on the returned data. This method improves performance over alternative methods, such as sending a unique SQL statement to the database instance to retrieve the list of issued part IDs for each work order row that is retrieved from the database. I also try to use bind variables where possible to reduce the number of hard parses, and minimize the performance impact of the necessary hard parses. At the top of the form code modules, I add the following Imports entries to make it easy to use ODP.Net for database access (note that a reference to Oracle.DataAccess.dll was also added):
Imports System.Data Imports Oracle.DataAccess.Client Imports Oracle.DataAccess.Types
The variable to store the reference to the SQL statement is declared in the form’s variables section near the top of the form code (immediately after the Public Class line):
Dim comRecentMaintenanceUM As OracleCommand
The connection to the Oracle Database is established in a login form, and that Oracle.DataAccess.Client.OracleConnection object (name may be shortened if the Imports entries are used) is given the name dbVMFG. In the form’s Load procedure the SQL statement associated with the OracleCommand variable is specfied.
strSQL = "SELECT /*+ LEADING(RO) */" & vbCrLf
strSQL = strSQL & " RO.ID," & vbCrLf
strSQL = strSQL & " RO.FAILURE_MODE," & vbCrLf
strSQL = strSQL & " RO.ENTRY_NOTE," & vbCrLf
strSQL = strSQL & " RO.REPAIR_NOTE," & vbCrLf
strSQL = strSQL & " RO.START_TIME," & vbCrLf
strSQL = strSQL & " RO.END_TIME," & vbCrLf
strSQL = strSQL & " RO.RESOURCE_IS_DOWN," & vbCrLf 'Added 6/20/2017 CH
strSQL = strSQL & " UW.WORKORDER_BASE_ID," & vbCrLf
strSQL = strSQL & " UW.WORKORDER_LOT_ID," & vbCrLf
strSQL = strSQL & " O.RUN_HRS," & vbCrLf
strSQL = strSQL & " O.ACT_RUN_HRS," & vbCrLf
strSQL = strSQL & " R.PART_ID," & vbCrLf
strSQL = strSQL & " P.DESCRIPTION," & vbCrLf
strSQL = strSQL & " R.CALC_QTY," & vbCrLf
strSQL = strSQL & " R.ISSUED_QTY" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " CUSTAPP.RESOURCE_MAINT_UM_WO UW," & vbCrLf
strSQL = strSQL & " CUSTAPP.RESOURCE_OUTAGE RO," & vbCrLf
strSQL = strSQL & " OPERATION O," & vbCrLf
strSQL = strSQL & " REQUIREMENT R," & vbCrLf
strSQL = strSQL & " PART P" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " RO.RESOURCE_ID= :1" & vbCrLf
strSQL = strSQL & " AND RO.OUTAGE_DELETED='N'" & vbCrLf
strSQL = strSQL & " AND RO.START_TIME>=TRUNC(SYSDATE-120)" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_TYPE=UW.WORKORDER_TYPE" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_BASE_ID=UW.WORKORDER_BASE_ID" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_LOT_ID=UW.WORKORDER_LOT_ID" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_SPLIT_ID=UW.WORKORDER_SPLIT_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_TYPE=O.WORKORDER_TYPE" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_BASE_ID=O.WORKORDER_BASE_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_LOT_ID=O.WORKORDER_LOT_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_SPLIT_ID=O.WORKORDER_SPLIT_ID" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID='0'" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=10" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_TYPE=R.WORKORDER_TYPE(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID=R.WORKORDER_SUB_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=R.OPERATION_SEQ_NO(+)" & vbCrLf
strSQL = strSQL & " AND R.PART_ID=P.ID(+)" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " RO.START_TIME DESC," & vbCrLf
strSQL = strSQL & " R.PART_ID"
comRecentMaintenanceUM = New OracleCommand(strSQL, dbVMFG)
comRecentMaintenanceUM.CommandType = CommandType.Text
comRecentMaintenanceUM.CommandTimeout = 30
comRecentMaintenanceUM.BindByName = False
comRecentMaintenanceUM.Parameters.Add("resource_id", OracleDbType.Varchar2, 15, "", ParameterDirection.Input)
As with the API Toolkit example code shown above, there is a slightly different syntax that may be used to set up the OracleCommand variable, a method that seems to be a little more clear because the SQL statement’s beginning and end is indented, but this formatting method is bit more difficult to adapt to C# as C# does not have an equivalent for the With syntax:
comRecentMaintenanceUM = New OracleCommand
With comRecentMaintenanceUM
strSQL = "SELECT /*+ LEADING(RO) */" & vbCrLf
strSQL = strSQL & " RO.ID," & vbCrLf
strSQL = strSQL & " RO.FAILURE_MODE," & vbCrLf
strSQL = strSQL & " RO.ENTRY_NOTE," & vbCrLf
strSQL = strSQL & " RO.REPAIR_NOTE," & vbCrLf
strSQL = strSQL & " RO.START_TIME," & vbCrLf
strSQL = strSQL & " RO.END_TIME," & vbCrLf
strSQL = strSQL & " RO.RESOURCE_IS_DOWN," & vbCrLf 'Added 6/20/2017 CH
strSQL = strSQL & " UW.WORKORDER_BASE_ID," & vbCrLf
strSQL = strSQL & " UW.WORKORDER_LOT_ID," & vbCrLf
strSQL = strSQL & " O.RUN_HRS," & vbCrLf
strSQL = strSQL & " O.ACT_RUN_HRS," & vbCrLf
strSQL = strSQL & " R.PART_ID," & vbCrLf
strSQL = strSQL & " P.DESCRIPTION," & vbCrLf
strSQL = strSQL & " R.CALC_QTY," & vbCrLf
strSQL = strSQL & " R.ISSUED_QTY" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " CUSTAPP.RESOURCE_MAINT_UM_WO UW," & vbCrLf
strSQL = strSQL & " CUSTAPP.RESOURCE_OUTAGE RO," & vbCrLf
strSQL = strSQL & " OPERATION O," & vbCrLf
strSQL = strSQL & " REQUIREMENT R," & vbCrLf
strSQL = strSQL & " PART P" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " RO.RESOURCE_ID= :1" & vbCrLf
strSQL = strSQL & " AND RO.OUTAGE_DELETED='N'" & vbCrLf
strSQL = strSQL & " AND RO.START_TIME>=TRUNC(SYSDATE-120)" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_TYPE=UW.WORKORDER_TYPE" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_BASE_ID=UW.WORKORDER_BASE_ID" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_LOT_ID=UW.WORKORDER_LOT_ID" & vbCrLf
strSQL = strSQL & " AND RO.REPAIR_WO_SPLIT_ID=UW.WORKORDER_SPLIT_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_TYPE=O.WORKORDER_TYPE" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_BASE_ID=O.WORKORDER_BASE_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_LOT_ID=O.WORKORDER_LOT_ID" & vbCrLf
strSQL = strSQL & " AND UW.WORKORDER_SPLIT_ID=O.WORKORDER_SPLIT_ID" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID='0'" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=10" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_TYPE=R.WORKORDER_TYPE(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID=R.WORKORDER_SUB_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=R.OPERATION_SEQ_NO(+)" & vbCrLf
strSQL = strSQL & " AND R.PART_ID=P.ID(+)" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " RO.START_TIME DESC," & vbCrLf
strSQL = strSQL & " R.PART_ID"
.CommandText = strSQL
.Connection = dbVMFG
.CommandType = CommandType.Text
.CommandTimeout = 30
.BindByName = False
.Parameters.Add("resource_id", OracleDbType.Varchar2, 15, "", ParameterDirection.Input)
End With
The procedure (actually a function) , using ODP.Net, that retrieves the recent unplanned maintenance history for a resource ID and the repair parts issued to those work orders is shown below:
Public Function RetrieveRecentMaintenance() As Boolean
Dim strColor(4) As String
Dim intToggle As Integer = 0
Dim intToggle2 As Integer = 0
Dim intColor As Integer = 0
Dim intLastID As Integer = 0
Dim intOffset As Integer = 0
Dim snpData As OracleDataReader
Dim sbHTML As StringBuilder = New StringBuilder()
comRecentMaintenanceUM.Parameters("resource_id").Value = cboResourceID.Text
strColor(0) = "bgcolor=#ffffff"
strColor(1) = "bgcolor=#eeeeee"
strColor(2) = "bgcolor=#ddddff"
strColor(3) = "bgcolor=#ededff"
sbHTML.Append("<HTML>" & vbCrLf)
sbHTML.Append("<head>" & vbCrLf)
sbHTML.Append("<title>Recent Maintenance for Resource</title>" & vbCrLf)
sbHTML.Append("<style>" & vbCrLf)
sbHTML.Append("<!--" & vbCrLf)
sbHTML.Append("table { font-size: 8pt; font-family: Arial }" & vbCrLf)
sbHTML.Append("-->" & vbCrLf)
sbHTML.Append("</style>" & vbCrLf)
sbHTML.Append("</head>" & vbCrLf)
sbHTML.Append("<body>" & vbCrLf)
sbHTML.Append("<table width=100% border=0 cellpadding=3 cellspacing=0>" & vbCrLf)
sbHTML.Append("<tr bgcolor=#8080FF>" & vbCrLf)
sbHTML.Append("<td align=""center"">Failure Mode</td>" & vbCrLf)
sbHTML.Append("<td>Start Time</td>" & vbCrLf)
sbHTML.Append("<td>End Time</td>" & vbCrLf)
sbHTML.Append("<td>Description of Problem</td>" & vbCrLf)
sbHTML.Append("<td>Repair Procedure</td>" & vbCrLf)
sbHTML.Append("<td>Est Hrs</td>" & vbCrLf)
sbHTML.Append("<td>Act Hrs</td>" & vbCrLf)
sbHTML.Append("<td>Repair WO</td>" & vbCrLf)
sbHTML.Append("</tr>" & vbCrLf)
Try
snpData = comRecentMaintenanceUM.ExecuteReader
If snpData.IsClosed = False Then
snpData.FetchSize = comRecentMaintenanceUM.RowSize * 1000
Do While (snpData.Read())
If intLastID <> snpData("id") Then
'New unplanned maintenance
intToggle = 1 - intToggle
intLastID = snpData("id")
'Output the row
sbHTML.Append("<tr " & strColor(intToggle) & ">" & vbCrLf)
sbHTML.Append("<td>" & snpData("failure_mode") & "</td>" & vbCrLf)
If Not (IsDBNull(snpData("start_time"))) Then
If Not (IsDBNull(snpData("end_time"))) Then
sbHTML.Append("<td>" & Convert.ToDateTime(snpData("start_time")).ToString("g") & "</td>" & vbCrLf)
sbHTML.Append("<td>" & Convert.ToDateTime(snpData("end_time")).ToString("g") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td bgcolor=#ff0000>" & Convert.ToDateTime(snpData("start_time")).ToString("g") & "</td>" & vbCrLf)
sbHTML.Append("<td bgcolor=#ff0000> </td>" & vbCrLf)
End If
Else
sbHTML.Append("<td bgcolor=#eeeeee> </td>" & vbCrLf)
sbHTML.Append("<td bgcolor=#eeeeee> </td>" & vbCrLf)
End If
If Not (IsDBNull(snpData("entry_note"))) Then
sbHTML.Append("<td>" & snpData("entry_note") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td> </td>" & vbCrLf)
End If
If Not (IsDBNull(snpData("repair_note"))) Then
sbHTML.Append("<td>" & snpData("repair_note") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td> </td>" & vbCrLf)
End If
If Not (IsDBNull(snpData("run_hrs"))) Then
sbHTML.Append("<td align=""right"">" & Format(snpData("run_hrs"), "#,##0.00") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td align=""right"">0.00</td>" & vbCrLf)
End If
If Not (IsDBNull(snpData("act_run_hrs"))) Then
sbHTML.Append("<td align=""right"">" & Format(snpData("act_run_hrs"), "#,##0.00") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td align=""right"">0.00</td>" & vbCrLf)
End If
If snpData("resource_is_down") = "Y" Then
sbHTML.Append("<td bgcolor=#bb1100>" & snpData("workorder_base_id") & "/" & snpData("workorder_lot_id") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td bgcolor=#dddd00>" & snpData("workorder_base_id") & "/" & snpData("workorder_lot_id") & "</td>" & vbCrLf)
End If
sbHTML.Append("</tr>" & vbCrLf)
End If
If Not (IsDBNull(snpData("calc_qty"))) Then
intToggle2 = 1 - intToggle2
'Output a part row
sbHTML.Append("<tr " & strColor(intToggle2 + 2) & ">" & vbCrLf)
sbHTML.Append("<td> </td>" & vbCrLf)
sbHTML.Append("<td> </td>" & vbCrLf)
sbHTML.Append("<td> </td>" & vbCrLf)
If Not (IsDBNull(snpData("part_id"))) Then
If Not (IsDBNull(snpData("description"))) Then
sbHTML.Append("<td>" & snpData("part_id") & " - " & snpData("description") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td>" & snpData("part_id") & " - </td>" & vbCrLf)
End If
Else
sbHTML.Append("<td> </td>" & vbCrLf)
End If
sbHTML.Append("<td align=""right"">Est Qty, Issued Qty:</td>" & vbCrLf)
If Not (IsDBNull(snpData("calc_qty"))) Then
sbHTML.Append("<td align=""right"">" & Format(snpData("calc_qty"), "#,##0.00") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td align=""right"">0.00</td>" & vbCrLf)
End If
If Not (IsDBNull(snpData("issued_qty"))) Then
sbHTML.Append("<td align=""right"">" & Format(snpData("issued_qty"), "#,##0.00") & "</td>" & vbCrLf)
Else
sbHTML.Append("<td align=""right"">0.00</td>" & vbCrLf)
End If
sbHTML.Append("<td> </td>" & vbCrLf)
sbHTML.Append("</tr>" & vbCrLf)
End If
Loop
sbHTML.Append("</table>" & vbCrLf)
snpData.Close()
End If
Catch ex As Exception
End Try
sbHTML.Append("</body></html>")
wbRecentMaintenanceHistory.DocumentText = sbHTML.ToString()
RetrieveRecentMaintenance = True
sbHTML = Nothing
End Function
Using the database platform independent API Toolkit, rather than ODP.Net, forces changes to the code as the GeneralQuery object is very picky about the contents and syntax of SQL statements that are submitted. Additionally, I tried to use fewer custom tables to store the information in the sample project that I included with the presentation than I did with the application that I created last year. Also, in this version of the procedure for the “starter” application, I did not use a StringBuilder variable, but instead a normal String variable, so the display speed related to building the HTML code will be a bit slower than it would be otherwise. To reduce potential confusion for people reviewing the code, I put the SQL statement directly into the function, rather than in the form’s Load procedure:
Public Function RetrieveRecentMaintenance() As Boolean
Dim i As Integer
Dim intToggle As Integer = 0
Dim intToggle2 As Integer = 0
Dim intColor As Integer = 0
Dim intOffset As Integer = 0
Dim strLastID As String = ""
Dim strSQL As String = ""
Dim strHTML As String = ""
Dim strColor(4) As String
Dim gqResource As Lsa.Shared.GeneralQuery
Dim drRow As Lsa.Data.DataRow
gqResource = New Lsa.Shared.GeneralQuery(strMDatabase)
strSQL = "SELECT" & vbCrLf
strSQL = strSQL & " WO.BASE_ID AS WORKORDER_BASE_ID," & vbCrLf
strSQL = strSQL & " WO.LOT_ID AS WORKORDER_LOT_ID," & vbCrLf
strSQL = strSQL & " WO.USER_6 AS START_TIME," & vbCrLf
strSQL = strSQL & " WO.USER_7 AS END_TIME," & vbCrLf
strSQL = strSQL & " WO.USER_8 AS FAILURE_MODE," & vbCrLf
strSQL = strSQL & " WO.USER_9 AS REPORTED_BY," & vbCrLf
strSQL = strSQL & " WO.USER_10 AS RESOURCE_ID," & vbCrLf
strSQL = strSQL & " WB.BITS AS ENTRY_NOTE," & vbCrLf
strSQL = strSQL & " OB.BITS AS REPAIR_NOTE," & vbCrLf
strSQL = strSQL & " O.RUN_HRS," & vbCrLf
strSQL = strSQL & " O.ACT_RUN_HRS," & vbCrLf
strSQL = strSQL & " R.PART_ID," & vbCrLf
strSQL = strSQL & " P.DESCRIPTION," & vbCrLf
strSQL = strSQL & " R.CALC_QTY," & vbCrLf
strSQL = strSQL & " R.ISSUED_QTY" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & " VMFG.WORK_ORDER WO," & vbCrLf
strSQL = strSQL & " VMFG.WORKORDER_BINARY WB," & vbCrLf
strSQL = strSQL & " VMFG.OPERATION O," & vbCrLf
strSQL = strSQL & " VMFG.OPERATION_BINARY OB," & vbCrLf
strSQL = strSQL & " VMFG.REQUIREMENT R," & vbCrLf
strSQL = strSQL & " VMFG.PART P" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & " WO.TYPE='W'" & vbCrLf
strSQL = strSQL & " AND WO.BASE_ID LIKE ?" & vbCrLf
strSQL = strSQL & " AND WO.SPLIT_ID='0'" & vbCrLf
strSQL = strSQL & " AND WO.SUB_ID='0'" & vbCrLf
strSQL = strSQL & " AND WO.STATUS IN ('U','F','R','C')" & vbCrLf
strSQL = strSQL & " AND WO.CREATE_DATE>= ?" & vbCrLf
strSQL = strSQL & " AND WO.TYPE=O.WORKORDER_TYPE" & vbCrLf
strSQL = strSQL & " AND WO.BASE_ID=O.WORKORDER_BASE_ID" & vbCrLf
strSQL = strSQL & " AND WO.LOT_ID=O.WORKORDER_LOT_ID" & vbCrLf
strSQL = strSQL & " AND WO.SPLIT_ID=O.WORKORDER_SPLIT_ID" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID='0'" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=10" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_TYPE=R.WORKORDER_TYPE(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_BASE_ID=R.WORKORDER_BASE_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_LOT_ID=R.WORKORDER_LOT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SPLIT_ID=R.WORKORDER_SPLIT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID=R.WORKORDER_SUB_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=R.OPERATION_SEQ_NO(+)" & vbCrLf
strSQL = strSQL & " AND R.PART_ID=P.ID(+)" & vbCrLf
strSQL = strSQL & " AND WB.TYPE(+) ='D'" & vbCrLf
strSQL = strSQL & " AND WO.TYPE=WB.WORKORDER_TYPE(+)" & vbCrLf
strSQL = strSQL & " AND WO.BASE_ID=WB.WORKORDER_BASE_ID(+)" & vbCrLf
strSQL = strSQL & " AND WO.LOT_ID=WB.WORKORDER_LOT_ID(+)" & vbCrLf
strSQL = strSQL & " AND WO.SPLIT_ID=WB.WORKORDER_SPLIT_ID(+)" & vbCrLf
strSQL = strSQL & " AND WO.SUB_ID=WB.WORKORDER_SUB_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_TYPE=OB.WORKORDER_TYPE(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_BASE_ID=OB.WORKORDER_BASE_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_LOT_ID=OB.WORKORDER_LOT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SPLIT_ID=OB.WORKORDER_SPLIT_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.WORKORDER_SUB_ID=OB.WORKORDER_SUB_ID(+)" & vbCrLf
strSQL = strSQL & " AND O.SEQUENCE_NO=OB.SEQUENCE_NO(+)" & vbCrLf
strSQL = strSQL & " AND OB.TYPE(+) ='D'"
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " WO.CREATE_DATE DESC," & vbCrLf
strSQL = strSQL & " WO.USER_6 DESC," & vbCrLf
strSQL = strSQL & " R.PART_ID"
gqResource.Prepare("-", strSQL)
'gqResource.Parameters(0) = "EMUM-" & cboResourceID.Text & "-____" 'Begins with EMUM- and ends with -YYYY
gqResource.Parameters(0) = "EMUM-" & cboResourceID.Text & "-%" 'Begins with EMUM- and ends with -YYYY or -YY
gqResource.Parameters(1) = DateAdd(DateInterval.Day, -120, Date.Today)
strColor(0) = "bgcolor=#ffffff"
strColor(1) = "bgcolor=#eeeeee"
strColor(2) = "bgcolor=#ddddff"
strColor(3) = "bgcolor=#ededff"
strHTML = "<HTML>" & vbCrLf
strHTML = strHTML & "<head>" & vbCrLf
strHTML = strHTML & "<title>Recent Maintenance for Resource</title>" & vbCrLf
strHTML = strHTML & "<style>" & vbCrLf
strHTML = strHTML & "<!--" & vbCrLf
strHTML = strHTML & "table { font-size: 8pt; font-family: Arial }" & vbCrLf
strHTML = strHTML & "-->" & vbCrLf
strHTML = strHTML & "</style>" & vbCrLf
strHTML = strHTML & "</head>" & vbCrLf
strHTML = strHTML & "<body>" & vbCrLf
strHTML = strHTML & "<table width=100% border=0 cellpadding=3 cellspacing=0>" & vbCrLf
strHTML = strHTML & "<tr bgcolor=#8080FF>" & vbCrLf
strHTML = strHTML & "<td align=""center"">Failure Mode</td>" & vbCrLf
strHTML = strHTML & "<td>Start Time</td>" & vbCrLf
strHTML = strHTML & "<td>End Time</td>" & vbCrLf
strHTML = strHTML & "<td>Description of Problem</td>" & vbCrLf
strHTML = strHTML & "<td>Repair Procedure</td>" & vbCrLf
strHTML = strHTML & "<td>Est Hrs</td>" & vbCrLf
strHTML = strHTML & "<td>Act Hrs</td>" & vbCrLf
strHTML = strHTML & "<td>Repair WO</td>" & vbCrLf
strHTML = strHTML & "</tr>" & vbCrLf
Try
gqResource.Execute()
If (gqResource.Tables(0).Rows.Count > 0) Then
For i = 0 To gqResource.Tables(0).Rows.Count - 1
drRow = gqResource.Tables(0).Rows(i)
If strLastID <> (drRow("workorder_base_id") & drRow("workorder_lot_id")) Then
'New unplanned maintenance
intToggle = 1 - intToggle
strLastID = drRow("workorder_base_id") & drRow("workorder_lot_id")
'Output the row
strHTML = strHTML & "<tr " & strColor(intToggle) & ">" & vbCrLf
strHTML = strHTML & "<td>" & drRow("failure_mode") & "</td>" & vbCrLf
If Not (IsDBNull(drRow("start_time"))) Then
If Not (IsDBNull(drRow("end_time"))) Then
strHTML = strHTML & "<td>" & drRow("start_time") & "</td>" & vbCrLf
strHTML = strHTML & "<td>" & drRow("end_time") & "</td>" & vbCrLf
Else
strHTML = strHTML & "<td bgcolor=#ff0000>" & drRow("start_time") & "</td>" & vbCrLf
strHTML = strHTML & "<td bgcolor=#ff0000> </td>" & vbCrLf
End If
Else
strHTML = strHTML & "<td bgcolor=#eeeeee> </td>" & vbCrLf
strHTML = strHTML & "<td bgcolor=#eeeeee> </td>" & vbCrLf
End If
If Not (IsDBNull(drRow("entry_note"))) Then
strHTML = strHTML & "<td>" & System.Text.Encoding.UTF8.GetString(drRow("entry_note")) & "</td>" & vbCrLf
Else
strHTML = strHTML & "<td> </td>" & vbCrLf
End If
If Not (IsDBNull(drRow("repair_note"))) Then
strHTML = strHTML & "<td>" & System.Text.Encoding.UTF8.GetString(drRow("repair_note")) & "</td>" & vbCrLf
Else
strHTML = strHTML & "<td> </td>" & vbCrLf
End If
If Not (IsDBNull(drRow("run_hrs"))) Then
strHTML = strHTML & "<td align=""right"">" & Format(drRow("run_hrs"), "#,##0.00") & "</td>" & vbCrLf
Else
strHTML = strHTML & "<td align=""right"">0.00</td>" & vbCrLf
End If
If Not (IsDBNull(drRow("act_run_hrs"))) Then
strHTML = strHTML & "<td align=""right"">" & Format(drRow("act_run_hrs"), "#,##0.00") & "</td>" & vbCrLf
Else
strHTML = strHTML & "<td align=""right"">0.00</td>" & vbCrLf
End If
strHTML = strHTML & "<td>" & drRow("workorder_base_id") & "/" & drRow("workorder_lot_id") & "</td>" & vbCrLf
strHTML = strHTML & "</tr>" & vbCrLf
End If
If Not (IsDBNull(drRow("calc_qty"))) Then
intToggle2 = 1 - intToggle2
'Output a part row
strHTML = strHTML & "<tr " & strColor(intToggle2 + 2) & ">" & vbCrLf
strHTML = strHTML & "<td> </td>" & vbCrLf
strHTML = strHTML & "<td> </td>" & vbCrLf
strHTML = strHTML & "<td> </td>" & vbCrLf
If Not (IsDBNull(drRow("part_id"))) Then
If Not (IsDBNull(drRow("description"))) Then
strHTML = strHTML & "<td>" & drRow("part_id") & " - " & drRow("description") & "</td>" & vbCrLf
Else
strHTML = strHTML & "<td>" & drRow("part_id") & " - </td>" & vbCrLf
End If
Else
strHTML = strHTML & "<td> </td>" & vbCrLf
End If
strHTML = strHTML & "<td align=""right"">Est Qty, Issued Qty:</td>" & vbCrLf
If Not (IsDBNull(drRow("calc_qty"))) Then
strHTML = strHTML & "<td align=""right"">" & Format(drRow("calc_qty"), "#,##0.00") & "</td>" & vbCrLf
Else
strHTML = strHTML & "<td align=""right"">0.00</td>" & vbCrLf
End If
If Not (IsDBNull(drRow("issued_qty"))) Then
strHTML = strHTML & "<td align=""right"">" & Format(drRow("issued_qty"), "#,##0.00") & "</td>" & vbCrLf
Else
strHTML = strHTML & "<td align=""right"">0.00</td>" & vbCrLf
End If
strHTML = strHTML & "<td> </td>" & vbCrLf
strHTML = strHTML & "</tr>" & vbCrLf
End If
Next i
End If 'If (gqResource.Tables(0).Rows.Count > 0)
Catch ex As Exception
End Try
strHTML = strHTML & "</table>" & vbCrLf
strHTML = strHTML & "</body></html>"
wbRecentMaintenanceHistory.DocumentText = strHTML
RetrieveRecentMaintenance = True
gqResource.Dispose()
End Function
The presentation slides and support documents, of course, dive into greater detail about how the sample application works.
The presentation topics extended beyond merely reproducing the core functionality of the equipment maintenance application that I created a year ago, taking a deep dive into the undocumented areas of the API Toolkit. Below are a few screen captures from the presentation, demonstrating various capabilities of the Visual API Toolkit (click the pictures for a larger view). The top section of the API Toolkit Demo window shows how to connect to the Visual Manufacturing database using three different methods, as well as how to retrieve basic information from the API Toolkit. The first tab (top-left in the below picture) shows how to create and copy Visual Manufacturing work orders using the API Toolkit for equipment maintenance type tasks (procedures may be generalized for working with normal production work orders). The second tab (top-right in the below picture) shows how to perform various transactions in Visual Manufacturing using the API Toolkit.
The Toolkit functionality is divided into seven categories. The first of those categories, Financials (bottom-left in the below picture), allows working with standard financials accounts payable and receivable type documents. Clicking one of the Load buttons generally retrieves a single document from the database, and displays the header information for that document in the table at the bottom-left of the application window. Documents tend to contain more than one result table (child tables), so the bottom-right of the API Toolkit Demo window contains a grid that shows all of the retrieved table names, column names, column data types, whether or not the column permits NULL values, and whether or not the column is provided a default value if one is not provided in your application code – the standard documentation for the most part excludes this type of helpful information. The various Browse buttons potentially retrieve all of the related document headers and the specified columns without retrieving any sub-tables. The second category, Inventory (bottom-right), allows working with part IDs and creating inventory transactions in Visual Manufacturing.
The third category, Purchasing (top-left in the below picture) allows working with purchase orders and receiving purchased items into inventory. The fourth category, Sales (top-left in the below picture) allows working with customer orders and related types of documents. The fifth category, Shared (bottom-left in the below picture) is more or less a catch-all for maintaining various types of lists in the database, such as employees, product codes, and commodity codes. The sixth category, ShopFloor (bottom-right in the below picture) is used to manage work orders, create labor tickets for those work orders, and manage the resource IDs.
The final category, Trace (not pictured), is rather limited, and is used to maintain or review part trace (such as mill heat code) information. Whenever a Browse button is clicked in the API Toolkit Demo main window, the demo application uses Reflection to obtain additional (otherwise unpublished) information about the selected API Toolkit item, retrieving all of the item’s methods (functions) and calling parameter types – this information is displayed in a secondary popup window. For those methods that return DataRow objects, the bottom section of the window shows the various columns found in the DataRow, as well as their data types and default value, and sample VB.Net code to set the value for the table column.
Scrolling the top half of the window down shows that the demo program also attempts to retrieve the actual parent, child, and grandchild database table names from the API Toolkit, along with the key columns that are used for joining those tables:
Information from the popup window may be sent into Microsoft Excel by clicking the Excel icon, with the program providing additional VB.Net compatible code usage examples, thus allowing rapid use of unfamiliar portions of the API Toolkit. The top half of the popup window is shown on the Calling Code tab in the Excel spreadsheet:
The bottom half of the window is transferred to the second tab, Method Columns, of the generated spreadsheet:
The below pictures show slides from the presentation that show the other example applications that were included in the API Toolkit Demo application. The first picture shows an attempt to adapt a barcode labor collection application to use the API Toolkit to create and edit labor tickets – the example was implemented with mixed results, as indicated on the slide:
The next two slides show the Simplified Receiving application in use, receiving purchase items into Visual’s inventory using the API Toolkit:
The last three slides show a tool crib self-checkout application that translates standardizes UPC barcode labels into their corresponding Visual Manufacturing part IDs, and if the employee using the application has an in-process labor ticket, that work order’s information is retrieved automatically when the employee’s ID number is entered:
While there is not much Oracle Database specific content in the presentation, it is somewhat interesting to see the lengths that I had to go through to use a database agnostic API to perform various tasks in the database. Some of those tasks would have been much easier and faster to implement using pure Oracle methods, but given that the majority of the audience used SQL Server and not Oracle Database and the fact that the API Toolkit was previously poorly documented (despite having hundreds of pages of documentation), the two month development time for the presentation was likely time well spent.
Charles Hooper's Oracle Notes 
Leave a comment