INNER JOIN DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY INNER JOIN VENDTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY LOGISTICSELECTRONICADDRESS.LOCATOR AS CONTACTDETAILS LOGISTICSELECTRONICADDRESS.DESCRIPTION AS CONTACTNAME, Order by CUSTTABLE.DATAAREAID,CUSTTABLE.ACCOUNTNUMīelow SQL Query to extract a quick customer contact list from Dynamics AX 2012.ĬASE LOGISTICSELECTRONICADDRESS.TYPE WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' END AS CONTACTTYPE, WHERE CUSTTABLE.DATAAREAID IN ('CEU') -and CUSTTABLE.ACCOUNTNUM ='test033' left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = DIRPARTYTABLE.PRIMARYADDRESSLOCATION left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY from CUSTTABLE left outer JOIN VendBankAccount ON VendBankAccount.VENDACCOUNT = CUSTTABLE.ACCOUNTNUM -AND VendBankAccount.ACCOUNTID = CUSTTABLE.BANKACCOUNT VendBankAccount.RegistrationNum 'Routing Number',VendBankAccount.SWIFTNo,VendBankAccount.BankIBAN VendBankAccount.ACCOUNTID BankAccount ,VendBankAccount.NAME 'Bank Name' ,VendBankAccount.ACCOUNTNUM 'Bank account number', CUSTTABLE.PAYMTERMID,CUSTTABLE.TAXGROUP VATGROUP ,CUSTTABLE.CASHDISC , CUSTTABLE.CURRENCY ,CUSTTABLE.CUSTGROUP , SELECT distinct CUSTTABLE.PARTY ,CUSTTABLE.DATAAREAID ENTITY, CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME,Address.ADDRESS , select dirPart圜ontactInfoView.* from dirPart圜ontactInfoView JOIN CUSTTABLE ON dirPart圜ontactInfoView.PARTY =CUSTTABLE.PARTY JOIN LOGISTICSLOCATIONROLE ON DIRPARTYLOCATIONROLE.LOCATIONROLE =LOGISTICSLOCATIONROLE.RECID JOIN DIRPARTYLOCATIONROLE ON DIRPARTYLOCATIONROLE.PARTYLOCATION =DirPartyPostalAddressView.RECID SELECT LOGISTICSLOCATIONROLE.NAME,DirPartyPostalAddressView.PARTY,CUSTTABLE.PARTY FROM DirPartyPostalAddressView JOIN CUSTTABLE ON DirPartyPostalAddressView.PARTY =CUSTTABLE.PARTY SELECT DirPartyPostalAddressView.*,CUSTTABLE.PARTY FROM DirPartyPostalAddressView JOIN CUSTTABLE SELECT DIRPARTYTABLE.NAMEALIAS ,CUSTTABLE.* FROM CUSTTABLE JOIN DIRPARTYTABLE ON CUSTTABLE.PARTY =DIRPARTYTABLE.RECID Select * from dirPart圜ontactInfoView JOIN VENDTABLE ON dirPart圜ontactInfoView.PARTY =VENDTABLE.PARTY (Click Organization administration > Setup > Global address book > Address and contact information purpose.) SELECT LOGISTICSLOCATIONROLE.*,DirPartyPostalAddressView.*,VENDTABLE.* FROM DirPartyPostalAddressView JOIN VENDTABLE ON DirPartyPostalAddressView.PARTY =VENDTABLE.PARTY SELECT * FROM DirPartyPostalAddressView JOIN VENDTABLE ON DirPartyPostalAddressView.PARTY =VENDTABLE.PARTY SELECT * FROM VENDTABLE WHERE VENDTABLE.DATAAREAID='CEU' Change the code according to your requirement.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |