1. Delete un-matched location
while select forupdate inventLocationLogisticsLocation
where inventLocationLogisticsLocation.InventLocation == sInventLocation.RecId
notexists join dirPartyLocation
where dirPartyLocation.Location == inventLocationLogisticsLocation.Location
&& dirPartyLocation.Party == this.OMOperatingUnitID
{
deletePostalAddress = LogisticsPostalAddress::findByLocation(inventLocationLogisticsLocation.Location, true);
if (deletePostalAddress.RecId)
if (deletePostalAddress.aosValidateDelete())
deletePostalAddress.delete();
inventLocationLogisticsLocation.delete();
}
2. Update matched location
while select forUpdate inventLocationLogisticsLocation
where inventLocationLogisticsLocation.InventLocation == sInventLocation.RecId
join dirPartyLocation
where dirPartyLocation.Location == inventLocationLogisticsLocation.Location
&& dirPartyLocation.Party == this.OMOperatingUnitID
join logisticsPostalAddress
where logisticsPostalAddress.Location == dirPartyLocation.Location
{
rolesCon = _rolesMap.lookup(dirPartyLocation.Location);
inventLocationLogisticsLocation.InventLocation = sInventLocation.RecId;
inventLocationLogisticsLocation.Location = dirPartyLocation.Location;
inventLocationLogisticsLocation.IsPrimary = dirPartyLocation.IsPrimary;
inventLocationLogisticsLocation.IsPrivate = logisticsPostalAddress.IsPrivate;
inventLocationLogisticsLocation.addEntityLocation(rolesCon, true);
inventLocationLogisticsLocation.update();
}
3. Insert new location
while select dirPartyLocation
where dirPartyLocation.Party == this.OMOperatingUnitID
notExists join inventLocationLogisticsLocation
where inventLocationLogisticsLocation.InventLocation == sInventLocation.RecId
&& inventLocationLogisticsLocation.Location == dirPartyLocation.Location
notexists join logisticsPostalAddress
where logisticsPostalAddress.Location == dirPartyLocation.Location
{
rolesCon = _rolesMap.lookup(dirPartyLocation.Location);
//Create new location
newLogisticsLocation = LogisticsLocation::create(LogisticsLocation::find(dirPartyLocation.Location).Description);
//Create address
newPostalAddress.clear();
newPostalAddress.data(logisticsPostalAddress::findByLocation(dirPartyLocation.Location));
newPostalAddress.Location = newLogisticsLocation.RecId;
newPostalAddress.insert();
//Create mapping
newInventLocationLogisticsLocation.clear();
newInventLocationLogisticsLocation.InventLocation = sInventLocation.RecId;
newInventLocationLogisticsLocation.Location = newLogisticsLocation.RecId;
newInventLocationLogisticsLocation.IsPrimary = dirPartyLocation.IsPrimary;
newInventLocationLogisticsLocation.IsPrivate = logisticsPostalAddress.IsPrivate;
newInventLocationLogisticsLocation.insert();
newInventLocationLogisticsLocation.addEntityLocation(rolesCon, true);
}
ttsCommit;
I am using the joins above for inserting ,deleting and updating the address in retain channel and in the warehouse.
It is working fine for me in DEV and in QA but when I ran the same piece code in PROD it is killing the performance and it is taking almost 6-7 mins to update/insert/delete.
