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;  
   }  
 }  


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







No comments:

Post a Comment

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