Say you have a dilemma about the objects you get in a new model that you are going to apply on your instance, wondering if you will have conflicts (overshadowed code with the existing one); I am not going to talk about the standard ways of determining this; what I use instead is a t-sql script that gives me the objects nodes' that are in both models (this is taking into account the AX granularity of storing/handling the objects when):
-- Look at the models
-- select * from ModelManifest
DECLARE @ObjectElement Integer
DECLARE @EIDEModelLower Integer
DECLARE @EIDEModelHigher Integer
SET @EIDEModelLower = ??
SET @EIDEModelHigher = ??
drop table EIDEModelLower
drop table EIDEModelHigher
drop table EIDEModelMerged
drop table EIDEModelElementsMerged
CREATE TABLE [dbo].[EIDEModelElementsMerged](
[ElementHandle] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[ElementType] [int] NOT NULL,
[PName] [nvarchar](255) NOT NULL,
[PType] [int] NOT NULL
) ON [PRIMARY]
-- List of objects that are changed on a specified (input param) model
select parent.ElementHandle, parent.Name, parent.ElementType
into EIDEModelLower
from ModelElementData as data
join modelelement as prop
on data.ElementHandle = prop.ElementHandle
join ModelElement as parent
on parent.ElementHandle = prop.RootHandle
where
data.ModelId = @EIDEModelLower -- modelId param
group by
parent.ElementHandle, parent.Name, parent.ElementType
order by
parent.ElementType
-- List of objects that are changed on a specified (input param) model
select parent.ElementHandle, parent.Name, parent.ElementType
into EIDEModelHigher
from ModelElementData as data
join modelelement as prop
on data.ElementHandle = prop.ElementHandle
join ModelElement as parent
on parent.ElementHandle = prop.RootHandle
where
data.ModelId = @EIDEModelHigher -- modelId param
group by
parent.ElementHandle, parent.Name, parent.ElementType
order by
parent.ElementType
-- list elements existing in Higher model (output display)
-- select data.Name, etypes.ElementTypeName from EIDEModelHigher as data
-- join ElementTypes as etypes
-- on data.ElementType = etypes.ElementType
-- order by etypes.ElementType
-- view same objects
select mLower.*
into EIDEModelMerged
from EIDEModelLower as mLower
join EIDEModelHigher as mHigher
on mHigher.ElementHandle = mLower.ElementHandle
order by
mLower.ElementType, mLower.Name
-- list elements that belongs to both models (output display)
-- select data.Name, etypes.ElementTypeName
-- from EIDEModelMerged as data
-- join ElementTypes as etypes
-- on data.ElementType = etypes.ElementType
DECLARE elementsCursor CURSOR
FOR select ElementHandle from EIDEModelMerged
OPEN elementsCursor
FETCH NEXT FROM elementsCursor
INTO @ObjectElement
truncate table EIDEModelElementsMerged
WHILE @@FETCH_STATUS = 0
BEGIN
-- Look at the changed subnodes
-- List of elements in a selected object that are changed on both layers
-- This works when both elements are changed on the lower and upper layer and there can be conflicts
-- But this is not enough when a child element that is new belongs to a parent that is changed on both layers
insert into EIDEModelElementsMerged
select data.ElementHandle, prop.Name, prop.ElementType, parent.Name, parent.ElementType
from ModelElementData as data
join ModelElement as prop
on data.ElementHandle = prop.ElementHandle
join ModelElementData as data_up
on data_up.ElementHandle = data.ElementHandle
join ModelElement as parent
on parent.ElementHandle = prop.RootHandle
where
data.ModelId = @EIDEModelLower -- lower layer
and prop.RootHandle = @ObjectElement
and data_up.ModelId = @EIDEModelHigher -- upper layer
order by prop.ElementHandle
FETCH NEXT FROM elementsCursor
INTO @ObjectElement
END
-- display output conflicts
select data.Name, data.PName, etypes.ElementTypeName, etypesP.ElementTypeName
from EIDEModelElementsMerged as data
join ElementTypes as etypes
on data.ElementType = etypes.ElementType
join ElementTypes as etypesP
on data.PType = etypesP.ElementType
order by etypesP.ElementTypeName, data.PName, etypes.ElementTypeName
-- display output conflicting objects
select data.PName, etypesP.ElementTypeName
from EIDEModelElementsMerged as data
join ElementTypes as etypes
on data.ElementType = etypes.ElementType
join ElementTypes as etypesP
on data.PType = etypesP.ElementType
group by etypesP.ElementTypeName, data.PName
order by etypesP.ElementTypeName, data.PName
CLOSE elementsCursor
DEALLOCATE elementsCursor
No comments:
Post a Comment