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.

eConnect Performance: Using GetNextDocNumbers vs taGetNext stored procedures

By Steve Endow This one is definitely an obscure topic that nobody is asking about . But hey, I was curious. I was researching whether...