We’re trying to build a new way to forecast, as we sell manufactured parts assembled from purchased parts and we want to stop forecasting the manufactured items as they clog up the system with 1000s of unfirm jobs.
I built a solution that works perfectly in DEV:
1 - A stored procedure runs every day and analyzes the BOMs of everything we sold, returning a table in a separate DB containing adjustments needed to our forecast;
2 - An external BAQ grabs the data from that table;
3 - A system agent schedule triggers a data directive which contains the following:
Ice.Contracts.DynamicQuerySvcContract tQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>();
Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("PROD_Update");
Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);
DataSet results = tQuery.Execute(dsQuery, dsBAQ);
and then calls the forecast BO, makes some comparisons and updates the forecast. The whole thing works really well in DEV and solved the whole mess of issues we’ve been having with forecasting since we went live.
In PROD, it fails. The only difference is that DEV’s database lives on the same machine as the AppServer, while PROD’s is on a separate VM.
I spent pretty much my whole youth trying to figure it out, or would have if I had any youth left. I’ve got it to write all kinds of error messages and have narrowed it down a bit.
Basically, the first three expressions above work fine. However, when I try to run Execute or ExecuteByID the transaction aborts.
Strangely, if I try to parse out the results DataSet, for example using results.Tables.Count.ToString(); I can see that the expected 3 tables are there; but if I run results.Tables[0].Rows.Count(); the result is zero.
Also strangely, by the way, the Datasource and external BAQ work perfectly, including if I use the BL Tester to run the DynamicQuery methods GetByID and Execute.
By calling the inner exception in a catch statement I was able to get this message from the server:
Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.
I’ve wrangled the IPSEC people into meeting me Sunday to make this change because we want to do changes without anyone in the system. Unfortunately, I don’t think it will work.
In order to test it I have gone the other way around, accessing a database on DEV where I can change those settings. Before making changes I got the same error, and then enabled network access for MSDTC and added a firewall rule. The only comprehensive info I could find was here:
Unfortunately, all it did was stop producing the inner exception and detailed message. I still get the transaction aborted.
The full code for my BPM is here. It obviously has a few other functions but I’ve stripped them all out as above for testing.
//SF_ForecastUpdate 0.09
using (CallContext.Current.TemporarySessionCreator.SetCompanyID("RDCAN").Create() )
{
using (var txScope = IceContext.CreateDefaultTransactionScope() )
{
try
{
//Create a BAQ object
Ice.Contracts.DynamicQuerySvcContract tQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);
if (tQuery != null)
{
//Load the BAQ
Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("Updated_Forecast");
//Get BAQ execution parameters (there are non in this case)
Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery);
//Execute the BAQ
DataSet results = tQuery.Execute(dsQuery, dsBAQ);
//loop through the latest forecast and update the original
var lastUpdateRow = results.Tables[0].Rows[0];
if (Convert.ToDateTime(lastUpdateRow[12]) >= DateTime.Now.AddHours(-20)) //this will only run if the update calculator has run within the last day
{
for (int r = 0; r < results.Tables[0].Rows.Count ; r++)
{
if ((r%50) == 0)
{
var mrp = (from mp in Db.SysTask where mp.Company == this.Session.CompanyID select mp).FirstOrDefault();
if (mrp.TaskDescription == "ProcessMRP" && mrp.TaskStatus == "Active")
{
string vFrom = "epicor.globalalert@rangerdesign.com";
string emailTo = "steve.fossey@rangerdesign.com" ;
string vSubject = "Forecast Updater Stopped for MRP";
var mailer = this.GetMailer(async: true);
var message = new Ice.Mail.SmtpMail();
message.SetFrom(vFrom);
message.SetTo(emailTo);
message.SetSubject(vSubject);
mailer.Send(message);
throw new System.ArgumentException("MRP started before forecast updater finished", mrp.TaskDescription);
} //end if (mrp.TaskDescription == "ProcessMRP" && mrp.TaskStatus == "Active")
} //end if ((r%50) == 0)
//get the record to change based on part, plant and forecast date
DataRow row = results.Tables[0].Rows[r];
string partNum = row[1].ToString();
string plant = row[2].ToString();
DateTime foreDate = Convert.ToDateTime(row[3]);
int custNum = 0;
string parentPartNum = "";
int pageSize = 0;
int absolutePage = 0;
bool morePages = false;
string test = r.ToString() + " BAQ: " + partNum + " Qty: " + row[10].ToString() + Environment.NewLine;
//get the existing forecast dataset for this part. plant and forecast date
var bo = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.ForecastSvcContract>(Db);
var ds = bo.GetByID(partNum, plant, pageSize, foreDate, parentPartNum);
test += "Forecast: " + ds.Forecast[0].PartNum.ToString() + " Qty: " + ds.Forecast[0].ForeQty.ToString() + Environment.NewLine;
if (Convert.ToBoolean(row[11]) || Convert.ToDouble(row[10]) == 0.0 ) //If BAQ NewInactive == true then just update inactive
{
//create Before image for rollback on error
var origRow = ds.Forecast.NewRow();
BufferCopy.Copy(ds.Forecast[0], origRow);
ds.Forecast.Add(origRow);
//inactivate the forecast
ds.Forecast[0].Inactive = true;
ds.Forecast[0].RowMod = "U";
bo.Update(ref ds);
} //end if inactive flag is true
else
{
//create Before image for rollback on error
var origRow = ds.Forecast.NewRow();
BufferCopy.Copy(ds.Forecast[0], origRow);
ds.Forecast.Add(origRow);
//update forecast dataset
ds.Forecast[0].ForeQty = Convert.ToDecimal(row[10]);
ds.Forecast[0].ForeQtyUOM = row[6].ToString();
ds.Forecast[0].ConsumedQty = Convert.ToDecimal(row[7]);
ds.Forecast[0].Inactive = Convert.ToBoolean(row[11]);
ds.Forecast[0].RowMod = "U";
bo.Update(ref ds);
} //end else (IE BAQ NewInactive == false)
test += "Update: " + ds.Forecast[0].PartNum.ToString() + " Qty: " + ds.Forecast[0].ForeQty.ToString();
Ice.Diagnostics.Log.WriteEntry(test);
} //end for r in results
} //end if (Convert.ToDateTime(lastUpdateRow[12]) <= DateTime.Now.AddHours(-1))
else
{
//if Updated_Forecast not done, email alert
string vFrom = "epicor.globalalert@rangerdesign.com";
string emailTo = "steve.fossey@rangerdesign.com" ;
string vSubject = "Forecast Updater Could Not Run";
var mailer = this.GetMailer(async: true);
var message = new Ice.Mail.SmtpMail();
message.SetFrom(vFrom);
message.SetTo(emailTo);
message.SetSubject(vSubject);
mailer.Send(message);
}
} //end if tQuery != null
} catch (Exception e) //end try
{
Ice.Diagnostics.Log.WriteEntry("forecast BPM error " + e);
} // end catch
txScope.Complete();
} //End TXScope
}//End company ID session
If anyone has any ideas I’ll be very grateful, and will share the business case, process improvement and project once it’s working
