A follow up to an earlier article: Microsoft Dynamics AX 2012 Chart of Accounts Validation Rules SQL.
The Advanced Rules can be found in AX under: General Ledger > Setup > Chart of accounts > Configure account structures > select account structure > Advanced rule.
Here’s a small sample of our advanced rules from AX:
Here’s the same rule (results row 9), as produced by the below SQL:
Full SQL:
select dr.name AdvancedRule , dr.description Name , a.Name AccountStructure , a.Description ASDescription , case when dr.IsDraft =1 then 'Draft' else 'Active' end [Status] --seems to just duplicate the IsDraft info , dr.IsDraft Draft , drc.Filter , dh.Name ARSAdvancedRuleStructure , dh.DESCRIPTION ARSName from DimensionRule dr inner join DimensionHierarchy a on a.recid = dr.AccountStructure and a.Partition = dr.Partition inner join ( select drcO.DimensionRule , drcO.Partition , stuff ( ( select ' And ' + da.Name + ' ' + case when coalesce(drcI.WildCardString,'') > '' then case when drcI.WildCardString like '\%%\%' escape '\' then 'contains ' when drcI.WildCardString like '%\%' escape '\' then 'begins with ' when drcI.WildCardString like '\%%' escape '\' then 'ends with ' else 'equals ' end + quotename(replace(drcI.WildCardString,'%',''),'''') when drcI.IsFromOpen = drcI.IsToOpen then --if both are 0 or both are 1 (second scenario seems weird, but that's how the app behaves) case when drcI.RangeFrom = drcI.RangeTo then 'Is ' + quotename(drcI.RangeFrom,'''') else 'Between ' + quotename(drcI.RangeFrom,'''') + ' through ' + quotename(drcI.RangeTo,'''') end --in the below statements IsFromOpen and IsToOpen seem to behave backwards to what you'd expect; but again that's what the app does when drcI.IsFromOpen=1 and drcI.IsToOpen=0 then 'Greater than or equal to ' + quotename(drcI.RangeFrom,'''') when drcI.IsFromOpen=0 and drcI.IsToOpen=1 then 'Less than or equal to ' + quotename(drcI.RangeTo,'''') else '-UNEXPECTED SCENARIO; SPEAK TO JB-' --this should nevere happen end FROM DimensionRuleCriteria drcI inner join DimensionAttribute da on da.RecId = drcI.DimensionAttribute and da.Partition = drcI.Partition where drcI.DimensionRule = drcO.DimensionRule and drcI.Partition = drcO.Partition order by da.Name --drcI.RecId for xml path(''), type ).value('.','nvarchar(max)') ,1,4,'Where') Filter FROM DimensionRuleCriteria drcO group by drcO.Partition , drcO.DimensionRule ) drc on drc.DimensionRule = dr.RecId and drc.Partition = dr.Partition inner join DimensionRuleAppliedHierarchy drah on drah.DimensionRule = dr.RecId and drah.Partition = dr.Partition inner join DimensionHierarchy dh on dh.recid = drah.DimensionHierarchy and dh.Partition = drah.Partition order by a.Name, dr.name, dh.Name, dr.IsDraft
NB: One anomaly you may notice is some rules appear twice; once as Draft and once as Active. This is where an existing rule is being edited; the existing rule remains active whilst its replacement is created in draft status. When you browse the rules you’ll see the draft rule; however the active rule is the one being applied in any validation.