In a GI, I need to query the WarehouseDetails settings for Material items included in MFG BOMs.
Normally I would join AMBomMatl to INItemSite ON AMBomMatl.InventoryID = INItemSite.InventoryID AND AMBomMatl.SiteID = INItemSite.SiteID.
In this case however, the client has not populated AMBomMatl.SiteID, so I thought I could add AMBomItem to the joins and use AMBomItem.SiteID = INItemSite.SiteID instead.
In SQL, the query would look like this:
SELECT * from AMBomItem
ON AMBomItem.BOMID = AMBomOper.BOMID and AMBomItem.RevisionID = AMBomOper.RevisionID
ON AMBomMatl.BOMID = AMBomOper.BOMID AND AMBomMatl.RevisionID = AMBomOper.RevisionID
AND AMBomMatl.OperationID = AMBomOper.OperationID
LEFT JOIN INItemSite
ON AMBomMatl.InventoryID = INItemSite.InventoryID
AND AMBomItem.SiteID = INItemSite.SiteID
Prior to 21R1, the GI Relations allow me to join any field from any of the tables included in the GI, but in 21.106.0024 , I cannot join fields from AMBomItem to INItemSite.
I attached the GI XML.
Could this be related to pre-defined settings in the DAC Relations?
Thanks in advance for taking a look.
PS: I can populate AMBOMMatl.SiteID in order to solve the data problem, but I really would like to know the reason why a given DAC is not available for joins in a GI.