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

SQL Server: Using SqlCmd to export table (including header) to CSV

SQLCMD is a tool which can export dynamics AX 2012 table to CSV easily. Let's see.

sqlcmd  -E -Q "select * from AX2012_Train.dbo.BANKPARAMETERS" -o "e:\test\test.csv" -s","

Until the next post! :-)

SQL Server: Using BCP to export table (including header) to CSV

If we have to export a table to CSV file. There is a simple way to do this by BCP. 

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name 

from AX2012_Train.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='BANKPARAMETERS'; 

select @colnames;" queryout e:\test\HeadersOnly.csv -c -T 

BCP AX2012_Train.dbo.BANKPARAMETERS out e:\test\TableDataWithoutHeaders.csv -c -t, -T 

copy /b e:\test\HeadersOnly.csv+e:\test\TableDataWithoutHeaders.csv e:\test\TableData.csv

del e:\test\HeadersOnly.csv
del e:\test\TableDataWithoutHeaders.csv

However this way isn't a best solution to extract table in Dynamics AX 2012 because the column of AX table doesn't sort in the ascending order. (Some field such as RecId, DataAreaId will be put at the last)  But I'm sure this code will be useful in the general case.

SQL Server: Display table name

Hi, today I will show the simple query which run on Query editior on MS SQL Server Management studio. It displays the table names in Dynamics AX database.

select *
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like '%parameters%' and
    TABLE_NAME not like '%_BE' and
   TABLE_NAME not like '%_BR' and
   TABLE_NAME not like '%_CN' and
   TABLE_NAME not like '%_DE' and
   TABLE_NAME not like '%_NL' and
   TABLE_NAME not like '%_RU' and
   TABLE_NAME not like '%_UK';