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 [@InventoryID]
,line.ItemDescription AS [@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')
Best answer by Django
View original