thanks to Robert Tattersall to share the article about InventSite and LogisticsPostalAddress
I’d copied here incase this article moved/deleted
5 May 2016 9:27 PM
This document explains technical details for connecting Site with it’s address and contact information (e.g. phone, and email), and should help anyone who is attempting to customize existing code, or write new code, that tries to access address and contact information for a Site. It also includes some custom X++ code that could help in your efforts.
First, let’s look at a simplified layout of the database tables involved and their relation to each other – a basic understanding of X++ queries is helpful to understand it. In the diagram below the arrows indicate the direction tables are joined in, and there are fragments of X++ code on the arrow lines showing the table.fields joined on, sorting, and filters. Note that the X++ fragments use a shortened table names associated with the table name (in parentheses).
Before further explanation on the tables in the database diagram above please check out the screenshot below (the form shown is found at Warehouse management > Setup > Warehouse setup > Sites > Address tab > More options > Advanced). In the screenshot the colored boxes relate the fields to the table it comes from, just match the colors with the database diagram (e.g. Location ID is in a blue box which in the diagram above is the LogisticsLocation table).
Some important notes regarding the relationship, contents, and processing of data in the tables in the database diagram:
InventSite – Typically an InventSite record is selected using Site Id. Site Id can be retrieved from a number of different places (e.g. SalesTable.InventSiteId, or InventDim.InventSiteId through field InventDimId found in many inventory related tables).
InventSiteLogisticsLocation – There is one record in this table for each distinct named address (i.e. each record in the grid on the left of the screenshot above). As you can see from the database diagram this table is the hub for address and contact information. Filtering by Purpose (a.k.a. LocationRole) is common on this table, and if there is more than one record with the same Purpose it will choose the one marked as Primary if found, otherwise it will choose the first one.
LogisticsPostalAddress – This table contains the address related fields as seen in green boxes in the screenshot above. This table also contains Effective and Expiration dates (field names are ValidFrom and ValidTo), and only the active address as determined by these dates is shown. Please note that there is some complex logic in AX that manages what happens when making changes to a record, and what happen when using functions Remove and Deactivate. If a change is made to the address then the current record has its Expiration date set to the point in time the change was made (minus a couple of seconds), and a new record is created with Effective date set to the time the change was committed that reflects the changed address. If the Remove option in the form seen in the screenshot is chosen the Expiration date is set to the date the change was committed which causes it to disappear from the list. If the Delete option is chosen, and there are no references to the address then the LogisticsPostalAddress record, and related records in tables InventSiteLogisticsLocation, LogisticsLocation, InventSiteLogisticsLocationRole, and LogisticsElectronicAddress are removed.
Do not be confused by the Address tab in the Sites form having options Remove and Deactivate where the Manage Addresses form (and many other places that manage address, including Vendor and Customer) have options Delete and Remove. In the Sites form Remove does the same action as Delete, and Deactivate does the same action as Remove seen in other forms.
Also, note that there is a method InventSite.getLocationFromRole() that can be used to return Location value that in turn can be used to choose related LogisticsPostalAddress, and LogisticsElectronicAddress records. However, it is puzzling that this method does consider whether those records are, or were for some date, active even though it has a TransDateTime parameter (it just never uses that date). If you use method InventSite.getLocationFromRole() you run the risk of getting an inactive address – the method findContactBySiteRoleTypeDate provided with this article accepts a TransDateTime and does check it against the Effective and Expirations dates.
LogisticsElectronicAddress – This table contains the contact information related fields seen in the Manage addresses form in the screenshot above. There can be multiple records associated with the address, with each being given a Type along with whatever information you choose to add – the same type can be used on more than one record. Only one record can be marked as Primary. It is typical to filter on Type, and because there can be more than one preference is given to the one marked as Primary if one exists for the Type.
With exception of the InventSite table in the database diagram no tables are striped by DataAreaId (i.e. the records are seen and used across all companies).
Below is some X++ and SQL code that could be very helpful if you are attempting customize or create code involving Site and related address and contact information. Here is a brief explanation of what is included:
Job hDAddressHelperTest – This job can be used to test the X++ methods also included below.
Method findContactBySiteRoleTypeDate – This method accepts parameters: InventSiteId, LogisticsLocationRoleType, LogisticsElectronicAddressMethodType, and TransDateTime, and attempts to find an LogisticsElectronicAddress record for the parameters – the code follows what is seen in the database diagram (some code fragments came from here).
Method findContactByMethodType – This method accepts parameters LogisticsLocationRecId and LogisticsElectronicAddressMethodType and attempts to find an LogisticsElectronicAddress record for the parameters – the code follows what is seen in the database diagram (some code fragments came from here).
SQL get LogisticsPostalAddress records – For developers more comfortable with SQL statements the code shows the connection between a Site and LogisticsPostalAddress.
SQL get LogisticsElectronicAddress records – For developers more comfortable with SQL statements the following shows the connection between a Site and LogisticsElectronicAddress – you can change fields displayed and filters as desired.
Job hDAddressHelperTest – The following X++ job will test method findContactBySiteRoleTypeDate. It will prompt for values to be used as parameters in the call to the method and will return the value of the Locator field which is where phone, URL, email address, etc. are stored.
static void hDAddressHelperTest(Args _args)
{
Dialog dialog = new Dialog("Get Site and Role");
DialogField fieldSiteId = dialog.addField(extendedTypeStr(InventSiteId), "Enter Site Id");
DialogField fieldRoleType = dialog.addField(enumStr(LogisticsLocationRoleType), "Enter Role Type");
DialogField fieldTransDate = dialog.addField(extendedTypeStr(TransDate), "Active Date");
DialogField fieldMethType = dialog.addField(enumStr(LogisticsElectronicAddressMethodType),
"Enter Method Type");
LogisticsElectronicAddress logisticsElectronicAddress;
utcDateTime activeDate;
;
dialog.run();
if (dialog.closedOk() && fieldSiteId.value())
{
activeDate = DateTimeUtil::newDateTime(fieldTransDate.value(), 0,
DateTimeUtil::getCompanyTimeZone());
// Change class hdAddressHelper to whatever class you put the method in.
logisticsElectronicAddress = hDAddressHelper::findContactBySiteRoleTypeDate(fieldSiteId.value(),
fieldRoleType.value(),
fieldMethType.value(),
activeDate);
info(logisticsElectronicAddress.Locator);
}
}
Method findContactBySiteRoleTypeDate – This method accepts parameters: InventSiteId, LogisticsLocationRoleType, LogisticsElectronicAddressMethodType, and TransDateTime, and attempts to find an LogisticsElectronicAddress record for the parameters – the code follows what is seen in the database diagram (some code fragments came from here). This method calls the X++ function findContactByMethodType() that follows it. This is a static function that could be placed in its own class, but a more appropriate place would be to add it as a method in the LogisticsElectronicAddress table.
static LogisticsElectronicAddress findContactBySiteRoleTypeDate(
InventSiteId _siteId,
LogisticsLocationRoleType _roleType,
LogisticsElectronicAddressMethodType _methodType,
TransDateTime _transDateTime,
boolean _forUpdate = false)
{
LogisticsElectronicAddress electronicAddr;
RecId siteRecId = InventSite::find(_siteId).RecId;
InventSiteLogisticsLocation siteLocation;
InventSiteLogisticsLocationRole siteLocationRole;
LogisticsLocationRole locationRole;
LogisticsPostalAddress postalAddress;
LogisticsLocation logisticsLocation;
;
select firstonly Location from siteLocation
order by IsPrimary desc
where siteLocation.Site == siteRecId
exists join siteLocationRole
where siteLocationRole.SiteLogisticsLocation == siteLocation.RecId
exists join locationRole
where locationRole.Type == _roleType &&
locationRole.RecId == siteLocationRole.LocationRole
exists join postalAddress
where postalAddress.Location == siteLocation.Location &&
postalAddress.ValidFrom <= _transDateTime &&
postalAddress.ValidTo >= _transDateTime;
if (siteLocation)
{
electronicAddr = hDAddressHelper::findContactByMethodType(siteLocation.Location,
_methodType, _forUpdate);
}
return electronicAddr;
}Method findContactByMethodType – This method accepts parameters LogisticsLocationRecId and LogisticsElectronicAddressMethodType and attempts to find an LogisticsElectronicAddress record for the parameters – the code follows what is seen in the database diagram (some code fragments came from here).
static LogisticsElectronicAddress findContactByMethodType(
LogisticsLocationRecId _location,
LogisticsElectronicAddressMethodType _methodType,
boolean _forUpdate = false)
{
LogisticsElectronicAddress electronicAddr;
LogisticsLocation logisticsLocation;
;
electronicAddr.selectForUpdate(_forUpdate);
select firstonly logisticsLocation
where logisticsLocation.ParentLocation == _location
join electronicAddr
order by electronicAddr.IsPrimary desc
where electronicAddr.Type == _methodType &&
electronicAddr.Location == logisticsLocation.RecId;
return electronicAddr;
}SQL get LogisticsPostalAddress records – For developers more comfortable with SQL statements the code shows the connection between a Site and LogisticsPostalAddress.
select InventSite.SiteId, InventSite.Name, InventSite.RecId,
InventSiteLogisticsLocation.IsPrimary, InventSiteLogisticsLocation.RecId,
InventSiteLogisticsLocationRole.LocationRole,
LogisticsLocationRole.Name,
LogisticsLocation.[Description],
LogisticsPostalAddress.[Address], LogisticsPostalAddress.ValidFrom,
LogisticsPostalAddress.ValidTo
from InventSite
join InventSiteLogisticsLocation
on InventSiteLogisticsLocation.[Site] = InventSite.RecId
join InventSiteLogisticsLocationRole
on InventSiteLogisticsLocationRole.SiteLogisticsLocation =
InventSiteLogisticsLocation.RecId
join LogisticsLocationRole
on LogisticsLocationRole.RecId =
InventSiteLogisticsLocationRole.LocationRole
join LogisticsLocation
on LogisticsLocation.RecId = InventSiteLogisticsLocation.Location
join LogisticsPostalAddress
on LogisticsPostalAddress.Location = InventSiteLogisticsLocation.Location
where InventSite.DataAreaId = 'usmf' --Replace with your DataAreaId (i.e. Company)
and InventSite.SiteId = 1 -- Replace with Site Id you would like results forSQL get LogisticsElectronicAddress records – For developers more comfortable with SQL statements the following shows the connection between a Site and LogisticsElectronicAddress – you can change fields displayed and filters as desired.
select InventSite.SiteId, InventSite.Name, InventSite.RecId,
InventSiteLogisticsLocation.IsPrimary, InventSiteLogisticsLocation.RecId,
InventSiteLogisticsLocationRole.LocationRole,
LogisticsLocationRole.Name,
LL.[Description],
LogisticsElectronicAddress.[Type], LogisticsElectronicAddress.IsPrimary,
LogisticsElectronicAddress.[Description], LogisticsElectronicAddress.Locator
from InventSite
join InventSiteLogisticsLocation
on InventSiteLogisticsLocation.[Site] = InventSite.RecId
join InventSiteLogisticsLocationRole
on InventSiteLogisticsLocationRole.SiteLogisticsLocation =
InventSiteLogisticsLocation.RecId
join LogisticsLocationRole
on LogisticsLocationRole.RecId =
InventSiteLogisticsLocationRole.LocationRole
join LogisticsLocation as LL
on LL.RecId = InventSiteLogisticsLocation.Location
join LogisticsLocation
on LogisticsLocation.ParentLocation =
InventSiteLogisticsLocation.Location
join LogisticsElectronicAddress
on LogisticsElectronicAddress.Location = LogisticsLocation.RecId
where InventSite.DataAreaId = 'usmf' -- Replace with your DataAreaId (i.e. Company)
and InventSite.SiteId = 1 -- Replace with Site Id you would like results for