Hello ,
I'm looking how to use DirPartyTable to get the relation between companyInfo and OMOperatingUnit
I'm trying the bellow code but it return 0 record
SELECT * FROM DIRPARTYTABLE Party INNER JOIN MicrosoftDynamicsAx.dbo.DIRPARTYTABLE Common ON Common.RECID = Party.RECID AND Common.INSTANCERELATIONTYPE = 2376 LEFT OUTER JOIN MicrosoftDynamicsAx.dbo.DIRPARTYTABLE OMOPerating ON OMOPerating.RECID = Common.RECID AND OMOPerating.INSTANCERELATIONTYPE = 2377 AND OMOPerating.RECID = Party.RECID LEFT OUTER JOIN MicrosoftDynamicsAx.dbo.DIRPARTYTABLE CompanyInfo ON CompanyInfo.RECID = Common.RECID AND CompanyInfo.INSTANCERELATIONTYPE = 41 AND CompanyInfo.RECID = Party.RECID AND CompanyInfo.DATAAREA = 'USMF'
*This post is locked for comments
For AX 2012 R2/R3, inherited tables have been collapsed into their base table, so an OMOperatingUnit record is actually just a DirPartyTable record with an InstanceRelationType of 2377.
The reason your query returns no results is because of the INNER JOIN to DirPartyTable with an InstanceRelationType of 2376, which is the table number for OMInternalOrganization. There will never be any such records because that table is further inherited by OMOperatingUnit, etc.
If you want DirPartyTable records that are actually OMOperatingUnit records, you simply have to use the following SQL.
SELECT * FROM DIRPARTYTABLE OMOPerating WHERE OMOPerating.INSTANCERELATIONTYPE = 2377;
If you want DirPartyTable records that are actually CompanyInfo records, then use the following SQL.
SELECT * FROM DIRPARTYTABLE CompanyInfo WHERE CompanyInfo.INSTANCERELATIONTYPE = 41;
So in your example, the one record you appear to want can be fetched as follows.
SELECT * FROM DIRPARTYTABLE CompanyInfo WHERE CompanyInfo.INSTANCERELATIONTYPE = 41 AND CompanyInfo.DATAAREA = N'USMF';
André Arnaud de Cal...
291,969
Super User 2025 Season 1
Martin Dráb
230,842
Most Valuable Professional
nmaenpaa
101,156