Have you had any luck pushing data from a table to a UD table using these widgets?
I don’t think I ever tried doing that. And since I no longer have access to an E10 system (or any version of Epicor), I can’t try it out for you.
Pretty much all my future posts will be strictly from memory. A memory that doesn’t advance past 10.2.300.
I didn’t know you were out of Epicor! Thanks for responding though!
Instead of UpdateTable by Query use the Fill Table by Query. I have used that to write to UD tables. Use the Row Mod as “A” since you are adding data. Update is used if you are going to modify an existing dataset created earlier using Fill Table.
Ill give that a shot. Current use case is I have to be able to blow up the UD01 table and fill it any time OrderRel.UpdateExt is called via rest. Will using that be able to handle multiple rows simultaneously?
Ended up C# coding as found in the forum below, credit to them:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var UD01 in (from row in Db.UD01 select row))
{
Db.UD01.Delete(UD01);
}
Db.Validate();
txScope.Complete();
}
/* Find current record in dataset */
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach (var recOrderHed in (from row in Db.OrderHed where row.OpenOrder == true select row))
{
/* Find all lines for Order */
foreach (var recOrdDetail in (from row in Db.OrderDtl where row.Company == Session.CompanyID && row.OrderNum == recOrderHed.OrderNum && row.OpenLine == true select row))
{
foreach (var recOrdRel in (from row in Db.OrderRel where row.Company == Session.CompanyID && row.OrderNum == recOrdDetail.OrderNum && row.OrderLine == recOrdDetail.OrderLine && row.OpenRelease == true select row))
{
/* Update UD Record */
UD01 newRow = new UD01();
Db.UD01.Insert(newRow);
newRow.Company = Session.CompanyID;
newRow.Key1 = recOrdDetail.OrderNum.ToString();
newRow.Key2 = recOrdDetail.OrderLine.ToString();
newRow.Key3 = Guid.NewGuid().ToString();
newRow.ShortChar01 = "Example";
newRow.Number01 = recOrdRel.OrderNum;
newRow.Number02 = recOrdRel.OrderLine;
newRow.Number03 = recOrdRel.OrderRelNum;
newRow.Date01 = recOrdRel.ReqDate;
}
}
}
Db.Validate();
txScope.Complete();
}
New Goals:
I need to be able to detect when a user invokes the UpdateEXT with a row mod of “D” for the first time each day. This should then toggle the bpm to run deleting the old data out of the UD01 table and writing the new. Additionally, I would like a toggle that triggers four hours after that would engage that toggle again. I figure this can be done using a function and would like someones help standing one up.
Ive been working on this code a second and I believe I have it working the way I want it to. The code goes through an iterative process to get the order in which the added/updated release was in related to order#, Line, release order. It then iterates through the UD01 Table, that I have set up to grab and hold prior data, and finds its related field to pull a date out of.
The issue is the code used to write the old data to the UD01 table is doubling entries and putting them in out of order. See below pic and code for what I’m working with:
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var UD01 in (from row in Db.UD01 select row))
{
Db.UD01.Delete(UD01);
}
Db.Validate();
txScope.Complete();
}
/* Find current record in dataset */
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach (var recOrderHed in (from row in Db.OrderHed where row.OpenOrder == true select row))
{
/* Find all lines for Order */
foreach (var recOrdDetail in (from row in Db.OrderDtl where row.Company == Session.CompanyID && row.OrderNum == recOrderHed.OrderNum && row.OpenLine == true select row))
{
foreach (var recOrdRel in (from row in Db.OrderRel where row.Company == Session.CompanyID && row.OrderNum == recOrdDetail.OrderNum && row.OrderLine == recOrdDetail.OrderLine && row.OpenRelease == true select row))
{
/* Update UD Record */
UD01 newRow = new UD01();
Db.UD01.Insert(newRow);
newRow.Company = Session.CompanyID;
newRow.Key1 = recOrdDetail.OrderNum.ToString();
newRow.Key2 = recOrdDetail.OrderLine.ToString();
newRow.Key3 = Guid.NewGuid().ToString();
newRow.ShortChar01 = "Example";
newRow.Number01 = recOrdRel.OrderNum;
newRow.Number02 = recOrdRel.OrderLine;
newRow.Number03 = recOrdRel.OrderRelNum;
newRow.Date01 = recOrdRel.ReqDate;
}
}
}
Db.Validate();
txScope.Complete();
}
I believe the issue stems from the chunk of code that creates a new guid. I cannot however get that chunk of code to run without erroring out if I do not keep the guid included. Is there a better way of coding this?
Thanks a ton!!
Just did a hot-swap on the code to use some of Joses old recommendations on the same post. I get the following error:
Server Side Exception
BPM runtime caught an unexpected exception of ‘EntityException’ type.
See more info in the Inner Exception section of Exception Details.Exception caught in: Epicor.ServiceModel
Error Detail
Correlation ID: 752e7d57-0fd4-4758-b5a6-733e6c159a3e
Description: BPM runtime caught an unexpected exception of ‘EntityException’ type.
See more info in the Inner Exception section of Exception Details.
Inner Exception: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
Program: EntityFramework.dll
Method: EnlistTransaction
Original Exception Type: EntityException
Framework Method: SaveChanges
Framework Line Number: 352
Framework Column Number: 17
Framework Source: SaveChanges at offset 404 in file:line:column C:_Releases\ICE\ICE4.1.100.0\Source\Server\Framework\Epicor.System\Data\IceDataContext.cs:352:17Client Stack Trace
at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet dataSets)
at Erp.Proxy.BO.SalesOrderImpl.MasterUpdate(Boolean lCheckForOrderChangedMsg, Boolean lcheckForResponse, String cTableName, Int32 iCustNum, Int32 iOrderNum, Boolean lweLicensed, Boolean& lContinue, String& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel, String& cAgingMessage, SalesOrderDataSet ds)
at Erp.Adapters.SalesOrderAdapter.MasterUpdate(Boolean lCheckForOrderChangedMsg, Boolean lcheckForResponse, String cTableName, Int32 iCustNum, Int32 iOrderNum, Boolean lweLicensed, Boolean& lContinue, String& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel, String& cAgingMessage)
at Erp.UI.App.SalesOrderEntry.Transaction.Update()Inner Exception
The underlying provider failed on EnlistTransaction.
Inner Exception
The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
Inner Exception
The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var UD01 in (from row in Db.UD01 select row))
{
Db.UD01.Delete(UD01);
}
Db.Validate();
txScope.Complete();
}
/* Find current record in dataset */
using(var UD01svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD01SvcContract>(Db))
{
foreach (var recOrderHed in (from row in Db.OrderHed where row.OpenOrder == true select row))
{
/* Find all lines for Order */
foreach (var recOrdDetail in (from row in Db.OrderDtl where row.Company == Session.CompanyID && row.OrderNum == recOrderHed.OrderNum && row.OpenLine == true select row))
{
foreach (var recOrdRel in (from row in Db.OrderRel where row.Company == Session.CompanyID && row.OrderNum == recOrdDetail.OrderNum && row.OrderLine == recOrdDetail.OrderLine && row.OpenRelease == true select row))
{
/* Update UD Record */
UD01Tableset ds = new UD01Tableset();
UD01svc.GetaNewUD01(ref ds);
ds.UD01[0].Company = Session.CompanyID;
ds.UD01[0].Number01 = recOrdRel.OrderNum;
ds.UD01[0].Number02 = recOrdRel.OrderLine;
ds.UD01[0].Number03 = recOrdRel.OrderRelNum;
ds.UD01[0].Date01 = recOrdRel.ReqDate;
UD01svc.Update(ref ds);
}
}
}
}
Does anyone have an idea what could be causing this?
Dunno if this is the answer but, on the last bit of code you posted you are not setting the Key fields on UD01
Do I have to set those key fields when writing to UD01?
Normally when creating a new record you need at least one key field filled in, something that makes the record unique
So if I set Key 1 = order number, key2 to order line, and key 3 to order release will that work?
Thats what I do in similar situations… and thats what you where doing in your earlier code
I went ahead and added the keys and made a change where the orderhed table would use sessionID company as part of the where statement, I get same message.
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var UD01 in (from row in Db.UD01 select row))
{
Db.UD01.Delete(UD01);
}
Db.Validate();
txScope.Complete();
}
/* Find current record in dataset */
using(var UD01svc = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD01SvcContract>(Db))
{
foreach (var recOrderHed in (from row in Db.OrderHed where row.Company == Session.CompanyID && row.OpenOrder == true select row))
{
/* Find all lines for Order */
foreach (var recOrdDetail in (from row in Db.OrderDtl where row.Company == Session.CompanyID && row.OrderNum == recOrderHed.OrderNum && row.OpenLine == true select row))
{
foreach (var recOrdRel in (from row in Db.OrderRel where row.Company == Session.CompanyID && row.OrderNum == recOrdDetail.OrderNum && row.OrderLine == recOrdDetail.OrderLine && row.OpenRelease == true select row))
{
/* Update UD Record */
UD01Tableset ds = new UD01Tableset();
UD01svc.GetaNewUD01(ref ds);
ds.UD01[0].Key1 = recOrdRel.OrderNum.ToString();
ds.UD01[0].Key2 = recOrdRel.OrderLine.ToString();
ds.UD01[0].Key3 = recOrdRel.OrderRelNum.ToString();
ds.UD01[0].Company = Session.CompanyID;
ds.UD01[0].Number01 = recOrdRel.OrderNum;
ds.UD01[0].Number02 = recOrdRel.OrderLine;
ds.UD01[0].Number03 = recOrdRel.OrderRelNum;
ds.UD01[0].Date01 = recOrdRel.ReqDate;
UD01svc.Update(ref ds);
}
}
}
}
Server Side Exception
BPM runtime caught an unexpected exception of ‘EntityException’ type.
See more info in the Inner Exception section of Exception Details.Exception caught in: Epicor.ServiceModel
Error Detail
Correlation ID: 46b45444-c73f-4a7c-9669-8dafcde49c22
Description: BPM runtime caught an unexpected exception of ‘EntityException’ type.
See more info in the Inner Exception section of Exception Details.
Inner Exception: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
Program: EntityFramework.dll
Method: EnlistTransaction
Original Exception Type: EntityException
Framework Method: SaveChanges
Framework Line Number: 352
Framework Column Number: 17
Framework Source: SaveChanges at offset 404 in file:line:column C:_Releases\ICE\ICE4.1.100.0\Source\Server\Framework\Epicor.System\Data\IceDataContext.cs:352:17Client Stack Trace
at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet dataSets)
at Erp.Proxy.BO.SalesOrderImpl.MasterUpdate(Boolean lCheckForOrderChangedMsg, Boolean lcheckForResponse, String cTableName, Int32 iCustNum, Int32 iOrderNum, Boolean lweLicensed, Boolean& lContinue, String& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel, String& cAgingMessage, SalesOrderDataSet ds)
at Erp.Adapters.SalesOrderAdapter.MasterUpdate(Boolean lCheckForOrderChangedMsg, Boolean lcheckForResponse, String cTableName, Int32 iCustNum, Int32 iOrderNum, Boolean lweLicensed, Boolean& lContinue, String& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel, String& cAgingMessage)
at Erp.UI.App.SalesOrderEntry.Transaction.Update()Inner Exception
The underlying provider failed on EnlistTransaction.
Inner Exception
The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
Inner Exception
The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
Sorry I’m not much help, I just googled the text of the last Inner Exception, there is a lot of results…
Any and all help is accepted here, man! Thanks for taking the time to poke around and offer some advice!
Went back to the original code and made a few slight modifications. Now I get the exact results I was after.
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach(var UD01 in (from row in Db.UD01 select row))
{
Db.UD01.Delete(UD01);
}
Db.Validate();
txScope.Complete();
}
/* Find current record in dataset */
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach (var recOrderHed in (from row in Db.OrderHed where row.Company == Session.CompanyID && row.OpenOrder == true select row))
{
/* Find all lines for Order */
foreach (var recOrdDetail in (from row in Db.OrderDtl where row.Company == Session.CompanyID && row.OrderNum == recOrderHed.OrderNum && row.OpenLine == true select row))
{
foreach (var recOrdRel in (from row in Db.OrderRel where row.Company == Session.CompanyID && row.OrderNum == recOrdDetail.OrderNum && row.OrderLine == recOrdDetail.OrderLine && row.OpenRelease == true select row))
{
/* Update UD Record */
UD01 newRow = new UD01();
Db.UD01.Insert(newRow);
newRow.Company = Session.CompanyID;
newRow.Key1 = recOrdRel.OrderNum.ToString();
newRow.Key2 = recOrdRel.OrderLine.ToString();
newRow.Key3 = recOrdRel.OrderRelNum.ToString();
newRow.Number01 = recOrdRel.OrderNum;
newRow.Number02 = recOrdRel.OrderLine;
newRow.Number03 = recOrdRel.OrderRelNum;
newRow.Date01 = recOrdRel.ReqDate;
}
}
}
Db.Validate();
txScope.Complete();
}Very good, I see you are now setting Key3 to the order rel, probably why you were getting duplicate entries on your earlier code
1 Like
Now for the fun part of the project, functions. The way that the integrator decided to delete out our sales order releases was to do it one line at a time. This means that there will be multiple UpdateExt Calls by their user account with RowMods = “D”. I want to push the data from OrderRel to UD01 only once when they initialize the first Delete call, not ever one they make. The way I though this could be done would be to make a scheduled program that toggles a little before their program is scheduled to run. This can then be called on a condition statement allowing for the data push to be run. After the data push I want that toggle to be set to false and only tick back to true three to four hours later. This will ensure that their program is done running its delete calls before toggling back. Does anyone have any expertise on this?
Thanks!
Delete all SO Releases? Seems awfully aggressive, and they might run into an issue with that since I didn’t think Epicor would allow all Releases to be deleted. Anyways, sounds like a ‘nuclear option.’
A few thoughts:
- Demand Management instead, this would mean the PO data would feed into Epicor through Demand Management and then someone can mass manage the updates to the Sales Orders… might not be as ‘integrated’ but it might be safer and more elegant.
- You could manage the whole thing with PowerShell and DMT. Before the Update (or bomb and rebuild) comes through, your can have a PowerShell run that will call a BAQ for all the current state Releases with the relevant data (OrderNum, LineNum, Rel, Promise Date, etc). That will put the BAQ results in a CSV. You can then use PowerShell to manipulate the CSV if needed. Then after the Rest Update, have the PowerShell run a SalesOrder Release Update with the CSV to update the Promise Dates back. Might be easier than trying to pick up on the right trigger in a BPM.
- As you stated REST goes both ways, I would push a solution that will just Update the data that needs to be updated somehow.
- Might be better to ‘capture’ the Load into a CSV and then use PowerShell DMT to to just update what is needed.
2 Likes