SAP BOM Upload File

To load a BOM into SAP we needed to create a XML file with a hierarchy of the BOM.

To start this project in Access you will need to set a reference to ChilkatXML and have a recordset for the BOM items.

Function Make_SAP_XML()

Dim xml As New ChilkatXml
Dim MT_TPC_BOM As ChilkatXml
Dim ProductHeader As ChilkatXml
Dim Systems As ChilkatXml
Dim System As ChilkatXml
Dim SystemItems As ChilkatXml
Dim SystemItem As ChilkatXml
Dim ATOComponents As ChilkatXml
Dim ATOComponent As ChilkatXml

Dim rst As Recordset
Dim tmpSystemLine As Integer
Dim tmpSystem As String, tmpOrder
Dim tmpItemNo As Integer, tmpOrderLine As Integer

xml.Tag = "MT_TPC_BOM"
xml.AddAttribute "xmlns:nr1", "http://siebel.com/contract_to_order"

Set ProductHeader = xml.NewChild("ProductHeader", "")

'set the header values
ProductHeader.NewChild2 "ConfigSource", "TPC"
ProductHeader.NewChild2 "TPCVersion", "7.3k"
ProductHeader.NewChild2 "TPCInternalDate", Format(Date, "mm/dd/yyyy")
ProductHeader.NewChild2 "TPCEngInfoDate", Format(Date, "mm/dd/yyyy")
ProductHeader.NewChild2 "TPCEngInfoFileVersion", "1.0"
ProductHeader.NewChild2 "CommerciallyComplete", "Y"
ProductHeader.NewChild2 "CurrencyCode", "EUR"

Set Systems = ProductHeader.NewChild("Systems", "")

Set rst = CurrentDb.OpenRecordset("tblBOM_Records")
If rst.RecordCount = 0 Then
    MsgBox "Nothing to Process"
    GoTo Exit_Func
    Exit Function
Else
    rst.MoveFirst
End If

tmpSystemLine = 0
tmpSystem = ""
Do While Not rst.EOF

    If tmpOrder <> rst![Order Number] Then  ' new section
        tmpOrderLine = 1

        tmpItemNo = 1
        Set System = Systems.NewChild("System", "")
        System.NewChild2 "SystemNumber", tmpSystemLine
        System.NewChild2 "SystemName", "SYSTEM_ASSY"
        System.NewChild2 "SystemIDNumber", rst![Order Number] & "." & tmpSystemLine
        System.NewChild2 "Quantity", "1"
            Set SystemItems = System.NewChild("SystemItems", "")

    End If

    If tmpSystem <> rst![Lot_code_Sap] Then
        tmpSystemLine = tmpSystemLine + 1
        Set SystemItem = SystemItems.NewChild("SystemItem", "")

        SystemItem.NewChild2 "OriginalItemNumber", tmpOrderLine & "." & tmpSystemLine
        SystemItem.NewChild2 "Product", rst![Lot_code_Sap]
        SystemItem.NewChild2 "Description", rst![Order Number]
        SystemItem.NewChild2 "Quantity", 1

        Set ATOComponents = SystemItem.NewChild("ATOComponents", "")
        tmpItemNo = 1

    End If

                    Set ATOComponent = ATOComponents.NewChild("ATOComponent", "")

                    ATOComponent.NewChild2 "ATOItemNumber", tmpOrderLine & "." & tmpSystemLine & "." & tmpItemNo
                    ATOComponent.NewChild2 "Product", rst!Product
                    ATOComponent.NewChild2 "Description", rst![Product type]
                    ATOComponent.NewChild2 "Quantity", rst!Qty
                    ATOComponent.NewChild2 "UnitCost", "0"

        tmpOrder = rst![Order Number]
        tmpSystem = rst![Lot_code_Sap]

        tmpItemNo = tmpItemNo + 1

rst.MoveNext ' goto the next line

Loop

'  Save the XML:
Dim success As Long
success = xml.SaveXml("c:SAP_XML_Test.xml")
If (success <> 1) Then
    MsgBox xml.LastErrorText
End If

Exit_Func:
Set rst = Nothing
Set ProductHeader = Nothing
Set Systems = Nothing
Set System = Nothing
Set SystemItems = Nothing
Set ATOComponents = Nothing
Set ATOComponent = Nothing

End Function