AX 2012 | get CompanyInfo and Vendor data through T-SQL

This post describes the table relation of Company and Vendor data written in T-SQL. In AX 2012, the information of Company and Vendor do not kept only in a single table, so this might be useful to understand their data model and relation.

Company Info

 select  
    -- DPT.RecId as DPT_RecId  
       --,DPL.RECID as DPL_RecId  
       --,LPA.RecId as LPA_RecId  
     DPT.DATAAREA  
    ,DPT.NAME  
    ,DPT.NAMEALIAS  
    ,DPT.LANGUAGEID  
       ,LPA.Street  
       ,LPA.City  
       ,LPA.ZipCode  
       ,LPA.CountryRegionId  
    --,DPT.PARTYNUMBER  
    --,DPT.INSTANCERELATIONTYPE  
    --,DPT.KNOWNAS  
    --,DPT.PRIMARYADDRESSLOCATION  
    --,DPT.PRIMARYCONTACTEMAIL  
    --,DPT.PRIMARYCONTACTFAX  
    --,DPT.PRIMARYCONTACTPHONE  
    --,DPT.PRIMARYCONTACTTELEX  
    --,DPT.PRIMARYCONTACTURL  
    ,DPT.MODIFIEDDATETIME  
    ,DPT.MODIFIEDBY  
    ,DPT.CREATEDDATETIME  
    ,DPT.CREATEDBY  
    --,DPT.RECVERSION  
    --,DPT.RELATIONTYPE  
    --,DPT.PARTITION  
    --,DPT.EDI_GLN  
 from [dbo].[DIRPARTYTABLE] as DPT  
 left outer join [dbo].[DIRPARTYLOCATION] as DPL   
      on     DPT.RecId = DPL.Party  
      and DPT.PRIMARYADDRESSLOCATION = DPL.Location  
      and DPL.IsPrimary = 1  
 left outer join [dbo].[LOGISTICSPOSTALADDRESS] as LPA   
      on     DPL.Location = LPA.Location  
      and LPA.ValidFrom <= SYSDATETIME()  
      and LPA.ValidTo >= SYSDATETIME()  
 where DPT.INSTANCERELATIONTYPE = 41  

Vendors

 select  
    -- VNT.RecId as VNT_RecId  
       --,DPT.RECID as DPT_RecId  
       --,DPL.RECID as DPL_RecId  
       --,LPA.RecId as LPA_RecId  
       --,TRE.RecId as TRE_RecId  
       --,VBA.RecId as VBA_RecId  
        VNT.DATAAREAID  
    ,VNT.ACCOUNTNUM  
       ,DPT.Name  
       ,LPA.Street  
       ,LPA.City  
       ,LPA.ZipCode  
       ,LPA.CountryRegionId  
       ,TRE.RegistrationNumber          as Tax_regist_num  
       ,VBA.AccountId                    as Bank_ID  
       ,VBA.Name                              as Bank_Name  
       ,VBA.AccountNum                    as Bank_account_num  
       ,VBA.SwiftNo                         as Bank_Swift_code  
       ,VBA.BankIBAN                         as Bank_IBAN  
 from [dbo].[VENDTABLE] as VNT  
 left outer join [dbo].[DIRPARTYTABLE] as DPT   
      on     VNT.Party = DPT.RecId  
 left outer join [dbo].[DIRPARTYLOCATION] as DPL   
      on     DPT.RecId = DPL.Party  
      and DPT.PRIMARYADDRESSLOCATION = DPL.Location  
      and DPL.IsPrimary = 1  
 left outer join [dbo].[LOGISTICSPOSTALADDRESS] as LPA   
      on     DPL.Location = LPA.Location  
      and LPA.ValidFrom <= SYSDATETIME()  
      and LPA.ValidTo >= SYSDATETIME()  
 left outer join [dbo].[TAXREGISTRATION] as TRE   
      on     DPL.RecId = TRE.DirPartyLocation  
      and TRE.ValidFrom <= SYSDATETIME()  
      and TRE.ValidTo >= SYSDATETIME()  
 left outer join [dbo].[VENDBANKACCOUNT] as VBA   
      on     VNT.AccountNum = VBA.VendAccount  

Thanks for reading! Until the next post!


References
https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/296010/companyinfo-table-in-ax-2012
https://community.dynamics.com/365/financeandoperations/b/goshoom/posts/queries-to-tables-with-inheritance