Skip to main content

I am looking to build a customization to export shipment header and line detail information to an XML file.  I can do this easily within SQL using the XML writing shortcut, but I’m curious whether I can do something similar within Acumatica to generate the same file.

 

There are a few things I shortcut below, such as contact info is not in the header of the shipment, but this is close to the format I am looking to export.

SELECT ship.ShipmentNbr AS @ShipmentNbr]
, ship.Customer As @CustomerID]
, ship.CustomerName As @CustomerName]
, ship.ShipmentDate As @ShipDate]
, ship.CompanyName As @ShipToName]
, ship.Phone1 As @Phone1]
, ship.Fax As @Fax]
, ship.Email As @Email]
, ship.Location As @Location]
, ship.LocationName As @LocationName]
, ship.WarehouseID As @WarehouseID]
, ship.WarehouseName As @WarehouseName]
, ship.TotalShipmentQty As @TotalShipmentQty]
, ship.ShippedWeight As @ShippedWeight]
, ship.ShipVia As @ShipVia]
, ship.AddressLine1 As @AddressLine1]
, ship.AddressLine2 As @AddressLine2]
, ship.City As @City]
, ship.State As @State]
, ship.PostalCode As @PostalCode]
, ship.Country As @Country]
      ,(
        SELECT line.InventoryID AS e@InventoryID]
        ,line.ItemDescription AS t@ItemDescription]
        ,line.LineQty AS Â@ShippedQty]
        ,line.UOM AS @UOM]
        FROM dbo.SOShipLine AS line
        INNER JOIN dbo.SOShipment AS shipment ON line.ShipmentNbr=shipment.ShipmentNbr
        WHERE ship.ShipmentNbr=shipment.ShipmentNbr
        FOR XML PATH('InventoryID'),TYPE
      ) AS Items
FROM dbo.SOShipment AS ship
FOR XML PATH('ShipmentNbr'),ROOT('Shipment')

You could look into creating a custom data provider to generate the XML file. That allows you to use an Export scenario with parameters so you can control what shipment you want to generate the file for.

@Django  Have you been able to create a multi-level XML file using the dataprovider?  Do you have an example you could share?  I’ve tried to use the XML export before, but it seems I can only get a flat structure even though it is supposed to support multi-level.  If you have a sample, I’m sure I could swap out your fields with mine.

You only get a flat structure, unfortunately, with the XML provider.

This page shows the very basics on how to create your own provider.

https://asiablog.acumatica.com/2016/09/custom-integration-services-data.html

The blog post talks about creating a CSV file but you can create anything that .Net can create - you have 100% control over the generation of the file.

The good work happens in

protected override byte[] InternalExport(string objectName, PXSYTable table)

and, in my case, I build up the contents of my file in a List<string> object and then turn the contents of the list into the array of bytes:

data = FileLines.SelectMany(s => System.Text.Encoding.UTF8.GetBytes(s)).ToArray();

return data;

You can do the same to build the XML file however you want to build it and then turn it into an array of types at the end and you’ll have your XML file.

Let me know if you have further questions. I can share my code but it really is mostly a derivative of the blog post along with some help that I received from @Gabriel Michaud .

Reply


Terms & ConditionsCookie settings