.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.

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter and YouTube

http://www.precipioservices.com