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