Adding Ship To Address to Invoice Data
Need to add the Ship To address to Invoice data in a Generic Inquiry in Acumatica?
Let's say you've already added the ARTran and ARInvoice tables with a join from ARTran to ARInvoice on ARTran.TranType=ARInvoice.DocType and ARTran.RefNbr=ARInvoice.RefNbr.
In Acumatica 2018 R2 and prior, Acumatica doesn't store the ship address on the invoice record. So you have to go get it from the shipment.
You can join from ARTran to SOShipment on ARTran.SOShipmentNbr=SOShipment.ShipmentNbr.
Then you can join from SOShipment to SOShipmentAddress on SOShipment.ShipAddressID=SOShipmentAddress.AddressID.
The SOShipmentAddress table will give you the address fields you need: AddressLine1, AddressLine2, City, etc.
Note: I'd recommend using Left joins if you want to still see Invoices that have no associated Shipment.
In Acumatica 2019 R1, Acumatica is now storing the Ship To address on the Invoice record itself. I suspect this was driven by the Construction world because one Project could perform work in multiple tax jurisdictions and there is no Shipment so the Ship To address needs to be stored on the Invoice.
You can read about this new feature in the Finance: Ability to Override Shipping Address in Documents section of the Acumatica 2019 R1 Release Notes (click here).
So, in Acumatica 2019 R1, getting the Ship To address for an Invoice is a lot easier.
Just Left join from ARInvoice to ARAddress on ARInvoice.ShipAddressID=ARAddress.AddressID. Much easier right?
Interested in joining a Local Acumatica User Group? Click here for more info