Thursday, November 15, 2018

.NET code to create a Dynamics GP batch with eConnect

By Steve Endow

I just posted a story about how I discovered that sometimes I need to explicitly create Dynamics GP batches using eConnect in order to control the batch posting date.

Here's the code that I used.

First, I check to see if the batch exists.


 public static bool BatchExists(string database, int series, string batchSource, string batchID)   
 {  
   try  
   {  
     string commandText = "SELECT COUNT(*) AS RecordCount FROM dbo.SY00500 WHERE SERIES = @SERIES AND BCHSOURC = @BCHSOURC AND BACHNUMB = @BACHNUMB"; // AND GLPOSTDT = @GLPOSTDT";  
     SqlParameter[] sqlParameters = new SqlParameter[3];  
     sqlParameters[0] = new SqlParameter("@SERIES", System.Data.SqlDbType.Int);  
     sqlParameters[0].Value = series;  
     sqlParameters[1] = new SqlParameter("@BCHSOURC", System.Data.SqlDbType.VarChar, 15);  
     sqlParameters[1].Value = batchSource.Trim();  
     sqlParameters[2] = new SqlParameter("@BACHNUMB", System.Data.SqlDbType.VarChar, 15);  
     sqlParameters[2].Value = batchID.Trim();  
     string result = DataAccess.ExecuteScalar(database, CommandType.Text, commandText, sqlParameters);  
     int recordCount = Convert.ToInt32(result);  
     if (recordCount > 0)  
     {  
       return true;  
     }  
     else  
     {  
       return false;  
     }  
   }  
   catch (Exception ex)  
   {  
     Log.Write("An unexpected error occurred in DataAccess.BatchExists: " + ex.Message, true);  
     return false;  
   }  
 }  


If the batch does exist, I make sure to update the posting date.




 public static bool SetBatchPostingDate(string database, int series, string batchSource, string batchID, string glPostDate)  
 {  
   try  
   {  
     string commandText = "UPDATE dbo.SY00500 SET GLPOSTDT = @GLPOSTDT WHERE SERIES = @SERIES AND BCHSOURC = @BCHSOURC AND BACHNUMB = @BACHNUMB AND GLPOSTDT <> @GLPOSTDT";  
     SqlParameter[] sqlParameters = new SqlParameter[4];  
     sqlParameters[0] = new SqlParameter("@SERIES", System.Data.SqlDbType.Int);  
     sqlParameters[0].Value = series;  
     sqlParameters[1] = new SqlParameter("@BCHSOURC", System.Data.SqlDbType.VarChar, 15);  
     sqlParameters[1].Value = batchSource.Trim();  
     sqlParameters[2] = new SqlParameter("@BACHNUMB", System.Data.SqlDbType.VarChar, 15);  
     sqlParameters[2].Value = batchID.Trim();  
     sqlParameters[3] = new SqlParameter("@GLPOSTDT", System.Data.SqlDbType.DateTime);  
     sqlParameters[3].Value = Convert.ToDateTime(glPostDate);  
     int recordCount = DataAccess.ExecuteNonQuery(database, CommandType.Text, commandText, sqlParameters);  
     if (recordCount > 0)  
     {  
       return true;  
     }  
     else  
     {  
       return false;  
     }  
   }  
   catch (Exception ex)  
   {  
     Log.Write("An unexpected error occurred in DataAccess.SetBatchPostingDate: " + ex.Message, true);  
     return false;  
   }  
 }  


And if the batch does not exist, I create it using taCreateUpdateBatchHeaderRcd.


 public bool CreateBatch(string batchID, string comment, int series, string postingDate, string batchSource, decimal batchTotal, int origin, int numOfTrx, int postToGL)  
 {  
   //Series:  
   //1 = All;  
   //2 = Financial;  
   //3 = Sales;  
   //4 = Purchasing;  
   //5 = Inventory;  
   //6 = Payroll;  
   //7 = Project  
   //Some batch source values are:  
   //PM_Trxent - Series 4   
   //Rcvg Trx Entry - Series 4  
   //Rcvg Trx Ivc - Series 4  
   //ASMENT  
   //GL_Clearing  
   //GL_Normal  
   //IV_Trans  
   //IV_Trxent  
   //PA_ML - Series 7  
   //PA_TS  
   //RM_Cash  
   //RM_Sales  
   //Sales Entry  
   //Origin:  
   //For GL:  
   //1= General entry  
   //For IV:   
   //1=Transaction entry;  
   //2=Transfer entry   
   //For PM:   
   //1=Transaction entry;   
   //2=Computer check;  
   //3=Manual payment   
   //For RM:   
   //1=Transaction entry;   
   //2=Cash receipts entry  
   //For SOP:  
   //1=Sales transaction entry  
   //Post to GL:  
   //Defaults to 0 if nothing is passed in.  
   //0 = False  
   //1 = True  
   taCreateUpdateBatchHeaderRcd batchHeader = new taCreateUpdateBatchHeaderRcd();  
   batchHeader.BACHNUMB = batchID.Trim();  
   batchHeader.BCHCOMNT = comment.Trim();  
   batchHeader.SERIES = series;  
   batchHeader.GLPOSTDT = postingDate;  
   batchHeader.BCHSOURC = batchSource;  
   batchHeader.ORIGIN = origin;  
   batchHeader.NUMOFTRX = numOfTrx;  
   SMTransactionBatchType batchType = new SMTransactionBatchType();  
   batchType.taCreateUpdateBatchHeaderRcd = batchHeader;  
   SMTransactionBatchType[] batchTypeArray = { batchType };  
   eConnectType eConnect = new eConnectType();  
   eConnect.SMTransactionBatchType = batchTypeArray;  
   MemoryStream memStream = new MemoryStream();  
   XmlSerializer serializer = new XmlSerializer(eConnect.GetType());  
   serializer.Serialize(memStream, eConnect);  
   memStream.Position = 0;  
   XmlDocument xmlDocument = new XmlDocument();  
   xmlDocument.Load(memStream);  
   memStream.Close();  
   eConn eConn = new eConn();  
   string response = string.Empty;  
   bool returnValue = eConn.InsertTransaction(ref response, xmlDocument.OuterXml, Controller.Instance.Model.GPDatabase);  
   if (returnValue == false)  
   {  
     Log.Write("\tFailed to create batch " + batchID, true);  
     Log.Write(response, true);  
     return false;  
   }  
   else  
   {  
     return true;  
   }  
 }  


And this is the InsertTransaction call in my "eConn" class, which calls eConnect:


public bool InsertTransaction(ref string response, string transactionXML)  //, string companyDB
{
    GP gp = new GP(gpUserID, gpPassword, gpDatabase, log);
    string connString = gp.ConnectionStringWindows();

    string returnValue = string.Empty;  //GP 2010
    //bool success = false;  //GP 10
    
    response = "";

    eConnectMethods eConnCall = new eConnectMethods();

    try
    {
        returnValue = eConnCall.CreateTransactionEntity(connString, transactionXML);
        return true;
    }
    catch (eConnectException ex)
    {
        response = "eConnectException: " + ex.Message;
        response += "\r\nInner Exception: " + ex.InnerException;
        response += "\r\n" + transactionXML;
        return false;
    }
    catch (SqlException ex)
    {
        response = "SqlException: " + ex.Message;
        response += "\r\nInner Exception: " + ex.InnerException;
        response += "\r\n" + transactionXML;
        return false;
    }
    catch (Exception ex)
    {
        response = "Exception: " + ex.Message;
        response += "\r\nInner Exception: " + ex.InnerException;
        response += "\r\n" + transactionXML;
        return false;
    }
}



The eConnect taCreateUpdateBatchHeaderRcd node is a bit odd.  Based on my limited testing, it will only update an existing batch in some cases.  For example, if a Payables Transaction Entry batch already exists, if you submit the batch request to eConnect with a different posting date, eConnect will not update the batch record.  I haven't bothered to dig through the stored procedure to decipher when it will actually perform an update.

So that is why I have my own routine to update the batch posting date if the batch already exists.



You can also find him on Twitter and YouTube







2 comments:

  1. I realize this posting is a year old, but it's code that would be very helpful to me if I could get it to work. There is a line in the last part that apparently needs a reference I don't have.

    eConn eConn = new eConn();

    I've used the serialize code to create customers and SOP transactions, but this is the first time I've tried creating a batch record only. I could do so via a SQL call but I prefer to use eConnect via .NET

    Thanks for your help.

    ReplyDelete
  2. I have updated the sample code to show the InsertTransaction method. It's just a call to the eConnect CreateTransactionEntity method.

    ReplyDelete

All comments must be reviewed and approved before being published. Your comment will not appear immediately.

Dynamics GP: Does GL batch size affect posting performance? Yes, it does.

By Steve Endow If you need to post 50,000 journal entries in Dynamics GP, should you create one batch with 50,000 JEs?  Or should you crea...