|
|
|
|
|
We will check the design and function of BomProcessor.
BomProcessor contains following class, module and forms.
Class
BomSheet
CsapMatBom
Module
Main
Forms
BomHelp
|
|
|
|
16-Nov-2005
|
|
|
|
|
Class: BomSheet contains 1 property with 9 methods. The BomSheet class is for manipulating the worksheet that users are going to work on. Like creating new set of worksheet with template or transfer the worksheet data to the function module structure.
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "BomSheet"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Public Ss As Worksheet
Function ReadHeadText(Optional NameList As Object) As Object
Dim varRow As Variant
Set NameList = New Collection
Set ReadHeadText = New Collection
For Each varRow In ThisWorkbook.Sheets("Head").Rows
If varRow.Row > 1 Then
If varRow.Columns(1) = "" Then Exit For
ReadHeadText.Add varRow.Row - 1, varRow.Columns(1).Value
NameList.Add varRow.Columns(1).Value
End If
DoEvents
Next
End Function
Sub CreateNew()
Dim objDd03p As Object
Dim lngIdx As Long
Dim varRow As Variant
Const lngStartColumn = 1
If ActiveWorkbook Is Nothing Then Workbooks.Add
Set Ss = ActiveWorkbook.Sheets.Add
For Each varRow In ThisWorkbook.Sheets("Head").Rows
If varRow.Row > 1 Then
If varRow.Columns(1) = "" Then Exit For
With Ss.Cells(1, lngStartColumn + lngIdx)
.Value = varRow.Columns(1)
.ColumnWidth = varRow.Columns(2)
.Interior.ColorIndex = varRow.Columns(1).Interior.ColorIndex
.Font.Color = varRow.Columns(1).Font.Color
.Orientation = 90
.HorizontalAlignment = xlCenter
End With
lngIdx = lngIdx + 1
End If
DoEvents
Next
End Sub
Sub DownLoad(Conn As Object)
Dim rngParent As Range
Dim colParents As New Collection
Dim varDummy As Variant
Dim varStpoLine As Variant
Dim varDepData As Variant
Dim objCsapMatBom As CsapMatBom
Dim objStpo As Object
Dim objStko As Object
Dim objDepOrder As Object
Dim lngRow As Long
Dim lngIdx1 As Long
Dim strDep As String
Dim strPlant As String
Dim objHead As Object
Dim varField As Variant
Dim strUsage As String
lngRow = 2
On Error Resume Next
Set rngParent = Application.InputBox(prompt:="BOM Parent", Type:=8)
For Each varDummy In rngParent
colParents.Add varDummy.Value, varDummy.Value
Next
If colParents.Count = 0 Then Exit Sub
On Error GoTo 0
Set objHead = ReadHeadText
strPlant = CommandBars("Sapass").Controls("Sapass").Controls("Bom").Controls("Plant").Text
strUsage = CommandBars("Sapass").Controls("Sapass").Controls("Bom").Controls("Usage").Text
For Each varDummy In colParents
Set objCsapMatBom = New CsapMatBom
With objCsapMatBom
Set .Conn = Conn
.Material = varDummy
.Plant = strPlant
.BomUsage = strUsage
End With
If objCsapMatBom.Read(EStpo:=objStpo, EStko:=objStko, EDepOrder:=objDepOrder) = vbOK Then
For Each varStpoLine In objStpo.Rows
Ss.Cells(lngRow, objHead("MATERIAL")) = UCase(varDummy)
Ss.Cells(lngRow, objHead("VALID_FROM")) = ""
On Error Resume Next
For Each varField In objStpo.Columns
Debug.Print varField.Name
Ss.Cells(lngRow, objHead(varField.Name)) = varStpoLine(varField.Name)
DoEvents
Next
On Error GoTo 0
strDep = ""
For Each varDepData In objDepOrder.Rows
If varDepData("ITEM_NODE") = varStpoLine("ITEM_NODE") Then
strDep = strDep & varDepData("DEP_INTERN") & "=" & varDepData("DEP_LINENO") & ";"
End If
DoEvents
Next
Ss.Cells(lngRow, objHead("DEPDATA")) = strDep
lngRow = lngRow + 1
DoEvents
Next
Else
Ss.Cells(lngRow, objHead("MESSAGE")) = "BOM cannot be found"
End If
DoEvents
Next
End Sub
Sub ExecuteUpdate(Conn As Object)
Dim rngParent As Range
Dim colRows As New Collection
Dim varRow As Variant
Dim varDummy As Variant
Dim objCsapMatBom As CsapMatBom
Dim varStpoLine As Variant
Dim varDepLine As Variant
Dim varDepSplit As Variant
Dim lngIdx1 As Long
Dim objStpo As Object
Dim objStko As Object
Dim objDepOrder As Object
Dim objDepData As Object
Dim bolStpoFound As Boolean
Dim bolDepFound As Boolean
Dim lngResult As Long
Dim strBomNo As String
Dim objHead As Object
Dim strPlant As String
Dim strChangeNo As String
Dim colProcess As Object
Dim strUsage As String
On Error Resume Next
Set rngParent = Application.InputBox(prompt:="BOM Parent", Type:=8)
If rngParent Is Nothing Then Exit Sub
For Each varDummy In rngParent
colRows.Add varDummy.Row, CStr(varDummy.Row)
Next
'And add one dummy object for process everything completely.
colRows.Add 65535, "65535"
On Error GoTo 0
Set objCsapMatBom = New CsapMatBom
Set objCsapMatBom.Conn = Conn
Set objHead = ReadHeadText
With CommandBars("Sapass").Controls("Sapass").Controls("Bom")
strPlant = .Controls("Plant").Text
strChangeNo = .Controls("ChgNo").Text
strUsage = .Controls("Usage").Text
End With
If strChangeNo = "" Then MsgBox "Specify Change Number": Exit Sub
For Each varRow In colRows
'Check if the current line is same as previous
With objCsapMatBom
If .Material = UCase(Ss.Cells(varRow, 1).Value) And _
.Plant = strPlant And _
.BomUsage = strUsage Then
Else
'Check if this is first loop, if not execute change
If .Material <> "" Then
If .Maintain(IStpo:=objStpo, IStko:=objStko, IDepData:=objDepData, IDepOrder:=objDepOrder) <> vbOK Then Exit Sub
End If
Set colProcess = New Collection
Set objCsapMatBom = New CsapMatBom
Set objCsapMatBom.Conn = Conn
objCsapMatBom.Material = Ss.Cells(varRow, objHead("MATERIAL")).Value
objCsapMatBom.Plant = strPlant
objCsapMatBom.BomUsage = strUsage
objCsapMatBom.ChangeNo = strChangeNo
'Read BOM if it exists
If .Read(EStpo:=objStpo, EStko:=objStko, EDepData:=objDepData, EDepOrder:=objDepOrder) = vbOK Then
Else
'Create Header Record in order to create BOM.
lngResult = AddStko(objStko)
End If
.ChangeNo = strChangeNo
'Exit if reaches to the last one
If varRow = 65535 Then Exit For
End If
End With
'
bolStpoFound = False
For Each varStpoLine In objStpo.Rows
strBomNo = varStpoLine("BOM_NO")
If Ss.Cells(varRow, objHead("ITEM_NO")).Value <> "" And CStr(Ss.Cells(varRow, objHead("ITEM_NO")).Value) <> varStpoLine("ITEM_NO") Then
GoTo NEXTLOOP:
Else
If Ss.Cells(varRow, objHead("COMPONENT")).Value <> "" And CStr(Ss.Cells(varRow, objHead("COMPONENT")).Value) <> varStpoLine("COMPONENT") Then
GoTo NEXTLOOP:
ElseIf Ss.Cells(varRow, objHead("COMPONENT")).Value = "" And CStr(Ss.Cells(varRow, objHead("ITEM_TEXT1")).Value) <> varStpoLine("ITEM_TEXT1") Then
GoTo NEXTLOOP:
End If
End If
'Now identified the entry
bolStpoFound = True
varStpoLine("IDENTIFIER") = varRow
lngResult = ModifyStpo(varStpoLine, CLng(varRow))
'Then Dependency
If Ss.Cells(varRow, objHead("DEPDATA")).Value = "" Then GoTo NEXTLOOP
varDepSplit = Split(Ss.Cells(varRow, objHead("DEPDATA")), ";")
For lngIdx1 = 0 To UBound(varDepSplit)
bolDepFound = False
For Each varDepLine In objDepData.Rows
If varDepSplit(lngIdx1) Like varDepLine("DEP_INTERN") & "=*" And _
varStpoLine("BOM_NO") = varDepLine("BOM_NO") And _
varStpoLine("ITEM_NODE") = varDepLine("ITEM_NODE") Then
bolDepFound = True
lngResult = ModifyDependency(varStpoLine, CStr(varDepSplit(lngIdx1)), varDepLine, objDepOrder.Rows(varDepLine.Index), CLng(varRow))
Exit For
End If
DoEvents
Next
If bolDepFound = False Then
lngResult = AddDependency(varStpoLine, CStr(varDepSplit(lngIdx1)), objDepData, objDepOrder, CLng(varRow))
End If
DoEvents
Next
NEXTLOOP:
DoEvents
Next
'If the item is new
If bolStpoFound = False Then
'Add STPO line
lngResult = AddStpo(objStpo, CLng(varRow), strBomNo)
Set varStpoLine = objStpo.Rows(objStpo.RowCount)
'Add Dependency and its order line
varDepSplit = Split(Ss.Cells(varRow, objHead("DEPDATA")), ";")
For lngIdx1 = 0 To UBound(varDepSplit)
lngResult = AddDependency(varStpoLine, CStr(varDepSplit(lngIdx1)), objDepData, objDepOrder, CLng(varRow))
Next
End If
DoEvents
Next
End Sub
Function AddStko(Stko As Object) As Long
With Stko
.FreeTable
.Rows.Add
Stko(.RowCount, "BASE_QUAN") = 1
Stko(.RowCount, "BASE_UNIT") = "EA"
Stko(.RowCount, "BOM_STATUS") = CommandBars("Sapass").Controls("Sapass").Controls("Bom").Controls("Usage").Text
End With
End Function
Function AddStpo(Stpo As Object, CurrentRow As Long, BomNo As String) As Long
Dim varStpoLine As Variant
Dim lngResult As Long
Stpo.Rows.Add
Set varStpoLine = Stpo.Rows(Stpo.RowCount)
varStpoLine("BOM_NO") = BomNo
lngResult = ModifyStpo(varStpoLine, CurrentRow)
If varStpoLine("ITEM_NO") = "" Then
'
End If
End Function
Function ModifyStpo(StpoLine As Variant, CurrentRow As Long) As Long
Dim lngIdx1 As Long
Dim lngMaxColumn As Long
Dim bolNoChange As Boolean
Dim objStpoLine As Object
Dim strField As String
Dim strValue As String
bolNoChange = True
lngMaxColumn = Ss.Cells(1, 1).End(xlToRight).Column - 1
Set objStpoLine = StpoLine
For lngIdx1 = 3 To lngMaxColumn
strField = Ss.Cells(1, lngIdx1).Value
strValue = Ss.Cells(CurrentRow, lngIdx1).Value
If strValue <> "" Then
If strValue = "!" Then
StpoLine(strField) = ""
bolNoChange = False
Else
If IsNumeric(strValue) Then
If Val(StpoLine(strField)) <> Val(strValue) Then
StpoLine(strField) = strValue
bolNoChange = False
End If
ElseIf Trim(StpoLine(strField)) <> Trim(CStr(strValue)) Then
StpoLine(strField) = UCase(strValue)
bolNoChange = False
End If
End If
End If
DoEvents
Next
If bolNoChange = True Then Set StpoLine = objStpoLine: Exit Function 'No Change
StpoLine("IDENTIFIER") = CurrentRow
StpoLine("CHANGE_NO") = CommandBars("Sapass").Controls("Sapass").Controls("Bom").Controls("ChgNo").Text
End Function
Function AddDependency(StpoLine As Variant, DepString As String, DepData As Object, DepOrder As Object, CurrentRow As Long) As Long
Dim varDepDataLine As Variant
Dim varDepOrderLine As Variant
Dim lngResult As Long
If DepString = "" Then Exit Function
'Dependency Data
DepData.Rows.Add
Set varDepDataLine = DepData.Rows(DepData.RowCount)
'Dependency Order
DepOrder.Rows.Add
Set varDepOrderLine = DepOrder.Rows(DepData.RowCount)
'Put fields contents
lngResult = ModifyDependency(StpoLine, DepString, varDepDataLine, varDepOrderLine, CurrentRow)
End Function
Function ModifyDependency(StpoLine As Variant, DepString As String, DepDataLine As Variant, DepOrderLine As Variant, CurrentRow As Long) As Long
Dim varDepItem As Variant
Dim bolNoChange As Boolean
Dim objDepData As Object
Dim objDepOrder As Object
bolNoChange = True
Set objDepData = DepDataLine
Set objDepOrder = DepOrderLine
'Dependency Data
If InStr(DepString, "=") = 0 Then DepString = DepString & "=0000"
varDepItem = Split(DepString, "=")
DepDataLine("OBJECT_ID") = "2"
If DepDataLine("DEP_INTERN") <> varDepItem(0) Then
DepDataLine("DEP_INTERN") = varDepItem(0)
bolNoChange = False
End If
DepDataLine("STATUS") = "1"
DepDataLine("BOM_NO") = StpoLine("BOM_NO")
DepDataLine("ITEM_NODE") = StpoLine("ITEM_NODE")
DepDataLine("ITEM_COUNT") = StpoLine("ITEM_COUNT")
If varDepItem(1) = "!" Then
DepDataLine("FLDELETE") = "X"
End If
'Dependency Order
DepOrderLine("OBJECT_ID") = "2"
If DepOrderLine("DEP_INTERN") <> CStr(varDepItem(0)) Then
DepOrderLine("DEP_INTERN") = varDepItem(0)
bolNoChange = False
End If
If varDepItem(1) = "!" Then
bolNoChange = False
ElseIf Val(DepOrderLine("DEP_LINENO")) <> Val(varDepItem(1)) Then
DepOrderLine("DEP_LINENO") = varDepItem(1)
bolNoChange = False
End If
DepOrderLine("BOM_NO") = StpoLine("BOM_NO")
DepOrderLine("ITEM_NODE") = StpoLine("ITEM_NODE")
DepOrderLine("ITEM_COUNT") = StpoLine("ITEM_COUNT")
If bolNoChange = True Then Set DepDataLine = objDepData: Set DepOrderLine = objDepOrder: Exit Function
StpoLine("CHANGE_NO") = CommandBars("Sapass").Controls("Sapass").Controls("Bom").Controls("ChgNo").Text
StpoLine("IDENTIFIER") = CurrentRow
DepDataLine("IDENTIFIER") = CurrentRow
DepOrderLine("IDENTIFIER") = CurrentRow
End Function
|
|
|
|
|
16-Nov-2005
|
|
|
|
|
Propeties
The class contains 1 property.
Ss
Ss is an input parameter for the class. This will get the worksheet object which sheet to process for BOM download/upload.
|
|
|
|
08-Dec-2005
|
|
|
|
|
Methods
The class contains 9 methods.
ReadHeadText
This BomSheet function requires spread sheet to work on and this function does preparation for creation of the worksheet. It reads Header data information from woksheet "Head" which is hidden in the VBA .xla file and copy returns the field description as collection. Also it returns internal field name as NameList collection object. This function is to be called by Download and Execute update function in this class.
CreateNew
This is the core logic for adding new spread sheet to work on. Refer to the hidden "Head" sheet of the .xla file, create the header row in the created spread sheet. Thus, some of the field needs to be hidden or highlighted with some colors, you can configure them in Head sheet.
Download
This method requires one import parameter for SAP connection and download the BOM from SAP by calling CSAP_MAT_BOM_READ RFC function module. As there is another version of same function in latest version of SAP, it may be better to use the function (CSEP_MAT_BOM_READ) instead of this. This first gets the target ranges for BOM parent through inputbox (it's not inputbox function, but inputbox method. These two are different thing). For the plant and usage parameters, it takes values from the menu bar. Then creates CsapMatBom object and calls Read method of the class. The BOM item information will be returned to the object after the call, loop the BOM item and copy the values to the spread sheet.
ExecuteUpdate
Basically, this takes same steps as Download method does. First of all, this takes target range from the inputbox method. And loop the ranges that user specified, creates the CsapMatBom. In order not to remove/overwrite the fields that users not specified or intended, before it calls BOM change function module, it calls Read method of the class. Based on the loaded BOM, it modifies the necessary entries. These modified entries are passed to SAP through Maintain method which actually calls CSAP_MAT_BOM_MAINTAIN. For performance, if the changes for same BOM parent items will be grouped and does one call.
AddStko
This function is called by ExecuteUpdate procedure. This creates the STKO object (BOM header object) which is required for doing BOM update.
AddStpo
This prepares the parameter STPO (BOM item record) for the CSAP_MAT_BOM_MAINTAIN function. This receives the pointer for the line of the spread sheet currently working on and modify the STPO table entries with the values specified in the line.
ModifyStpo
This function is called in AddStpo function. This updates the STPO records with the specified values.
AddDependency
This function is called in the ExecuteUpdate function. For the configurable BOM, we can allocate dependency rule to the BOM item. This function helps to do for CSAP_MAT_BOM_MAINTAIN.
ModifyDependency
Same as AddStpo and ModifyStpo relation, this method correponsds to AddDependency and updates the entry of dependency rules for CSAP_MAT_BOM_MAINTAIN.
|
|
|
|
08-Dec-2005
|
|
|
|
|
Class: CsapMatBom class contains 8 property with 2 methods. The CsapMatBom class actually does RFC communication between Excel and SAP. CSAP_MAT_BOM_READ function module is called from Read method and CSAP_MAT_BOM_MAINTAin function module is called from Maintain method.
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "CsapMatBom"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Public Conn As Object
Public Material As String
Public Plant As String
Public BomUsage As String
Public Alternative As String
Public ValidFrom As String
Public ValidTo As String
Public ChangeNo As String
Function Read(Optional EStpo As Object, Optional EStko As Object, Optional EDepData As Object, Optional EDepOrder As Object) As Long
Dim objRfc As Object
Dim objMaterial As Object
Dim objPlant As Object
Dim objBomUsage As Object
Dim objAlternative As Object
Dim objValidFrom As Object
Dim objValidTo As Object
Dim objChangeNo As Object
Read = vbCancel
Set objRfc = Conn.Add("CSAP_MAT_BOM_READ")
Set objMaterial = objRfc.exports("MATERIAL")
Set objPlant = objRfc.exports("PLANT")
Set objBomUsage = objRfc.exports("BOM_USAGE")
Set objAlternative = objRfc.exports("ALTERNATIVE")
Set objValidFrom = objRfc.exports("VALID_FROM")
Set objValidTo = objRfc.exports("VALID_TO")
Set objChangeNo = objRfc.exports("CHANGE_NO")
objMaterial.Value = UCase(Material)
objPlant.Value = UCase(Plant)
objBomUsage.Value = UCase(BomUsage)
objAlternative.Value = Alternative
objValidFrom.Value = ValidFrom
objValidTo.Value = ValidTo
objChangeNo.Value = ChangeNo
Set EStpo = objRfc.tables("T_STPO")
Set EStko = objRfc.tables("T_STKO")
Set EDepData = objRfc.tables("T_DEP_DATA")
Set EDepOrder = objRfc.tables("T_DEP_ORDER")
EStpo.FreeTable
EStko.FreeTable
EDepData.FreeTable
EDepOrder.FreeTable
If objRfc.Call = False Then Exit Function
Read = vbOK
End Function
Function Maintain(IStpo As Object, IStko As Object, IDepData As Object, IDepOrder As Object) As Long
Dim objRfc As Object
Dim objMaterial As Object
Dim objPlant As Object
Dim objBomUsage As Object
Dim objAlternative As Object
Dim objValidFrom As Object
Dim objChangeNo As Object
Dim objStko As Object
Dim objFlNewItem As Object
Dim objFlBomCreate As Object
Dim Stko As Object
Dim Stpo As Object
Dim DepData As Object
Dim DepOrder As Object
Dim lngCount As Long
Dim varRow As Variant
'Dim ColumnName
Maintain = vbCancel
Set objRfc = Conn.Add("CSAP_MAT_BOM_MAINTAIN")
Set objMaterial = objRfc.exports("MATERIAL")
Set objPlant = objRfc.exports("PLANT")
Set objBomUsage = objRfc.exports("BOM_USAGE")
Set objAlternative = objRfc.exports("ALTERNATIVE")
Set objValidFrom = objRfc.exports("VALID_FROM")
Set objChangeNo = objRfc.exports("CHANGE_NO")
Set objFlNewItem = objRfc.exports("FL_NEW_ITEM")
Set objFlBomCreate = objRfc.exports("FL_BOM_CREATE")
objMaterial.Value = UCase(Material)
objPlant.Value = UCase(Plant)
objBomUsage.Value = UCase(BomUsage)
objAlternative.Value = Alternative
objValidFrom.Value = ValidFrom
objChangeNo.Value = ChangeNo
objFlNewItem.Value = "X"
objFlBomCreate.Value = "X"
Set Stko = objRfc.exports("I_STKO")
Set Stpo = objRfc.tables("T_STPO")
Set DepData = objRfc.tables("T_DEP_DATA")
Set DepOrder = objRfc.tables("T_DEP_ORDER")
'STKO
On Error Resume Next
For lngCount = 1 To Stko.ColumnCount
Stko(Stko.ColumnName(lngCount)) = IStko(1, Stko.ColumnName(lngCount))
DoEvents
Next
On Error GoTo 0
'STPO
For Each varRow In IStpo.Rows
Stpo.Rows.Add
Stpo(Stpo.RowCount, "ID_ITM_CTG") = varRow("ITEM_CATEG")
Stpo(Stpo.RowCount, "ID_ITEM_NO") = varRow("ITEM_NO")
Stpo(Stpo.RowCount, "ID_COMP") = varRow("COMPONENT")
Stpo(Stpo.RowCount, "ID_CLASS") = varRow("CLASS")
Stpo(Stpo.RowCount, "ID_CL_TYPE") = varRow("CLASS_TYPE")
Stpo(Stpo.RowCount, "ID_SORT") = varRow("SORTSTRING")
Stpo(Stpo.RowCount, "ID_GUID") = varRow("ITEM_GUID")
For lngCount = 1 To IStpo.Columns.Count
Stpo(Stpo.RowCount, IStpo.ColumnName(lngCount)) = varRow(IStpo.ColumnName(lngCount))
DoEvents
Next
DoEvents
Next
'DEP_DATA
For Each varRow In IDepData.Rows
DepData.Rows.Add
For lngCount = 1 To IDepData.Columns.Count
DepData(DepData.RowCount, IDepData.ColumnName(lngCount)) = varRow(IDepData.ColumnName(lngCount))
DoEvents
Next
DepOrder.Rows.Add
For lngCount = 1 To IDepOrder.Columns.Count
DepOrder(DepOrder.RowCount, IDepOrder.ColumnName(lngCount)) = IDepOrder(varRow.Index, IDepOrder.ColumnName(lngCount))
DoEvents
Next
DoEvents
Next
If objRfc.Call = False Then
MsgBox "NG"
Else
Maintain = vbOK
End If
Stpo.FreeTable
Stko.Clear
DepData.FreeTable
DepOrder.FreeTable
End Function
|
|
|
|
|
09-Dec-2005
|
|
|
|
|
Propeties
The class contains 8 properties.
Conn
Conn is an input parameter for the class. This will have the SAP RFC connection object. The class will work with the SAP that the connection specifies.
Material
Material is for the Parent material number for the BOM.
Plant
Plant is a plant that parent material is on. If the material is not on specific plant, this can be left as blank.
BomUsage
BomUsage is for the BOM usage of the BOM for read/update.
Alternative
Alternative is for the Alternative of the BOM for read/update.
ValidFrom
This gets the Valid from date for the BOM that you read/update.
ValidTo
This gets the Valid to date. This works only when you read BOM from SAP.
ChangeNo
ChangeNo is a field for the Change number that you can specify when you read/update the BOM. The valid from date for the specified change number will be retrieved and specified when read/update BOM.
|
|
|
|
28-Dec-2005
|
|
|
|
|