Relation companyInfo with OMOperatingUnit

(0) Share

Report

Posted on by ALAhmed

916

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

I have the same question (0)

All responses (1)

Answers (0)

Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report
RE: Relation companyInfo with OMOperatingUnit

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';

Was this reply helpful? Yes No