One of my ACC customer is facing an issue with SQL outer joins and wanted to check if this is an expected behaviour or is it a ACC bug.
As per customer there is nms:delivery data schema extended by gsk:deliveryAnalytics in their data model. Among others they have added there four brand related links to gsk:deliveryAnalyticsBrandMeta data schema.
Link names are: metaBrandLink01, metaBrandLink02, metaBrandLink03, metaBrandLink04. All are outer joins (externalJoin=“true”).
These links work pretty well when operator fill out @brand01-@brand04 attributes manually in one-time delivery. But the issue occurs when delivery is build based on a delivery template (for example in recurring delivery activity). In that case all @brand01-@brand04 fields become empty in newly executed delivery, although they were populated in delivery template. It is not an input form issue as per customer they have already checked that. They believe it is a SQL execution issue during loading delivery template and inserting its data in a new delivery. They have observed it in SQL code. If they change type of join to the externalJoin=“false”, then they can see that the code contains proper link references:
In SELECT part:
… D30.sBrand, D30.iDeliveryAnalyticsBrandMetaId, …
In FROM part of the code:
FROM NmsDelivery D0 …
JOIN GskDeliveryAnalyticsBrandMeta D30 ON (D30.sBrand = D0.sBrand01)
But if they are using externalJoin=“true”, then none of this is a part of SQL code generated by recurring delivery activity. As a result brand fields are not populated in newly created delivery.
Customer wants to know why SQL code doesn’t include outer join data when new delivery is created from the template. Is it a AC bug or is it expected AC behaviour.
As per our experts, the external join shouldn’t have any impact.
What you want to do in case of copy or recurring. Do you want to create a new record in your table extension, or just reference the same row?
In case of copy, maybe you can try to play with the attribute integrity.Try to useowncopy
In second case that should just be a copy of the external key, as we copy any attribute. Check the attribute defOnDuplicate for your external key.
Then try to create a copy of your delivery or delivery template from the UI and check if the attribute/external keys are copied. If it works recurring delivery should work too.