Blog related to Microsoft Dynamics AX
select CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS
from CUSTTABLE
left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY
left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.PRIMARYADDRESSLOCATION
left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID
Order by CUSTTABLE.ACCOUNTNUM
select CustTable.ACCOUNTNUM, SubscriberType.DISPLAYVALUE as SubscriberType
from CustTable
left outer join DIMENSIONATTRIBUTEVALUESETITEM AS SubscriberType ON SubscriberType.DIMENSIONATTRIBUTEVALUESET = CUSTTABLE.DEFAULTDIMENSION
left outer join DIMENSIONATTRIBUTEVALUE ON DIMENSIONATTRIBUTEVALUE.RECID = SubscriberType.DIMENSIONATTRIBUTEVALUE
left outer join DIMENSIONATTRIBUTE ON DIMENSIONATTRIBUTE.RECID = DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE and DIMENSIONATTRIBUTE.NAME = 'SubscriberType'
SELECT CUSTTABLE.ACCOUNTNUM AS CUSTID, DIRPARTYTABLE.NAME AS CUSTNAME,
CASE LOGISTICSELECTRONICADDRESS.TYPE WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' END AS CONTACTTYPE,
LOGISTICSELECTRONICADDRESS.DESCRIPTION AS CONTACTNAME, LOGISTICSELECTRONICADDRESS.LOCATOR AS CONTACTDETAILS
FROM DIRPARTYTABLE AS DIRPARTYTABLE INNER JOIN
CUSTTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY INNER JOIN
DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY INNER JOIN
LOGISTICSELECTRONICADDRESS ON DIRPARTYLOCATION.LOCATION = LOGISTICSELECTRONICADDRESS.LOCATION
ORDER BY DIRPARTYTABLE.NAME
static server str serverSendToEndPoint(str _endPoint, str _soapAction, str _serverThumbprint, str _clientThumbprint, str _xmlString)
{
str ret;
System.String clrEndPoint;
System.String clrSoapAction;
System.String clrServerThumbprint, clrClientThumbprint;
System.String clrXmlString;
System.String clrResponse;
System.Exception ex;
Namespace.WebService webService;
try
{
webService = new Namespace.WebService();
clrEndPoint = CLRInterop::getObjectForAnyType(_endPoint);
clrSoapAction = CLRInterop::getObjectForAnyType(_soapAction);
clrServerThumbprint = CLRInterop::getObjectForAnyType(_serverThumbprint);
clrClientThumbprint = CLRInterop::getObjectForAnyType(_clientThumbprint);
clrXmlString = CLRInterop::getObjectForAnyType(_xmlString);
if (VendParameters::find().AVAMutualAuthentication == NoYes::Yes)
{
clrResponse = webService.callWCFWebService(clrEndPoint, clrSoapAction, clrServerThumbprint, clrClientThumbprint, clrXmlString);
}
else
{
clrResponse = webService.callWCFWebService(clrEndPoint, clrSoapAction, clrServerThumbprint, clrXmlString);
}
return CLRInterop::getAnyTypeForObject(clrResponse);
}
catch (Exception::CLRError)
{
ex = CLRInterop::getLastException();
throw error(strFmt("The request failed with the following response %1", CLRInterop::getAnyTypeForObject(ex.ToString())));
}
}
namespace Namespace
{
public class WebService
{
public String callWCFWebService(string url = "", string soapAction = "", string thumbPrint = "", string thumbPrint2 = "", string xmlString = "")
{
ServicePointManager.ServerCertificateValidationCallback = delegate(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors) { return true; };
//Validate parameters
if (url == "")
{
throw new System.ArgumentException("Url missing");
}
if (soapAction == "")
{
throw new System.ArgumentException("Soap Action missing");
}
if (thumbPrint == "")
{
throw new System.ArgumentException("Server Thumbprint missing");
}
if (thumbPrint2 == "")
{
throw new System.ArgumentException("Client Thumbprint missing");
}
if (xmlString == "")
{
throw new System.ArgumentException("xmlString missing");
}
// Create a new HttpWebRequest object for the specified resource.
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
// Request mutual authentication.
request.AuthenticationLevel = AuthenticationLevel.MutualAuthRequired;
// Supply client credentials.
X509Store store = new X509Store(StoreName.My, StoreLocation.CurrentUser);
store.Open(OpenFlags.ReadOnly | OpenFlags.IncludeArchived);
// Find Server Certificate by thumbprint
X509Certificate2Collection col =
store.Certificates.Find(X509FindType.FindByThumbprint, thumbPrint.Replace(" ", ""), false);
X509Certificate2 cert = col.OfType<X509Certificate2>().FirstOrDefault();
if (cert == null)
{
throw new System.ArgumentException("Server Certificate not found in store ");
}
request.ClientCertificates.Add(cert);
// Find Client Certificate by thumbprint
col = store.Certificates.Find(X509FindType.FindByThumbprint, thumbPrint2.Replace(" ", ""), false);
cert = col.OfType<X509Certificate2>().FirstOrDefault();
store.Close();
if (cert == null)
{
throw new System.ArgumentException("Client Certificate not found in store");
}
request.ClientCertificates.Add(cert);
ASCIIEncoding encoding = new ASCIIEncoding();
byte[] bytesToWrite = encoding.GetBytes(xmlString);
request.Method = "POST";
request.ContentLength = bytesToWrite.Length;
request.Headers.Add("SOAPAction: \"" + soapAction + "\"");
request.ContentType = "text/xml; charset=utf-8";
request.KeepAlive = false;
request.ProtocolVersion = HttpVersion.Version10;
request.PreAuthenticate = true;
//Send Request
Stream dataStream = request.GetRequestStream();
dataStream.Write(bytesToWrite, 0, bytesToWrite.Length);
dataStream.Close();
//Get Response
string responseString = "";
try
{
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
// Read and display the response.
Stream streamResponse = response.GetResponseStream();
StreamReader streamRead = new StreamReader(streamResponse);
responseString = streamRead.ReadToEnd();
//Console.WriteLine(responseString);
// Close the stream objects.
streamResponse.Close();
streamRead.Close();
//Release the HttpWebResponse.
response.Close();
}
catch (Exception e)
{
if (e is WebException)
{
WebResponse errResp = ((WebException)e).Response;
if (errResp != null)
{
using (Stream streamResponse = errResp.GetResponseStream())
{
StreamReader streamRead = new StreamReader(streamResponse);
responseString = streamRead.ReadToEnd();
//Console.WriteLine(responseString);
// Close the stream objects.
streamResponse.Close();
streamRead.Close();
}
}
else
{
responseString = e.Message;
//Console.WriteLine(e.Message);
}
}
}
return responseString;
}
}
