BPM To Look At All OrderRel

Epicor ERP 10
, ,
1

I am trying to create a BPM that compares the OrderHed.NeedByDate with all the OrderRel rows NeedByDate. If the OrderHed.NeedByDAte is larger, then change the dates.

I keep getting an error: The Table SOTSet.OrderRel has more than one record.

Business Layer Exception
The table SOTSet.OrderRel has more than one record
Exception caught in: Epicor.ServiceModel
Error Detail
============
Correlation ID: 8580dff9-d7fd-4597-90e8-e6e35079d951
Description: The table SOTSet.OrderRel has more than one record
Program: Epicor.Customization.dll
Method: GetSingleRow
Line Number: 73
Column Number: 17
Table: SOTSet.OrderRel
Client 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, S
tring& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel, String& cAgingMessage)
at Erp.UI.App.SalesOrderEntry.Transaction.Update()

Here is how I have it set up.

  1. Data Directive: If OrderHed.NeedByDate has been changed from any to other, invoke Function.
  2. Function has access to ORderHed, OrderDtl, and OrderRel tables and BO.SalesOrder
  3. Function: GetByID–>Condition: OrderHed.NeedByDate of the changed row is more than OrderRel.NeedByDate–>True: update OrderRel table

image
image891Ă—473 18.1 KB

How do I get this to look at the multiple lines in the OrderRel table?

2

  1. Why the data directive vs SalesOrder.Update preprocessing method BPM? Data directives are kind of a “last resort” or “big hammer” approach since they’re triggered more often.

  2. Why the GetByID method call if you already have the OrderRel in your dataset? The “Set Field” widget seems like what you’re looking for here.

  3. You can probably skip the conditional and, using the set field widget, set the OrderRel field with a ternary e.g.

ttOrderHedRow.NeedByDate > ttOrderRelRow.NeedByDate ? ttOrderHedRow.NeedByDate : ttOrderRelRow.NeedByDate
1 Like
3

One side note … The OrderRel.NeedBy date can be null.

1 Like
4

Good point. In that case, the setter should be this:

(ttOrderRelRow.NeedByDate == null && ttOrderHedRow.NeedByDate != null)
  || ttOrderHedRow.NeedByDate > ttOrderRelRow.NeedByDate
  ? ttOrderHedRow.NeedByDate
  : ttOrderRelRow.NeedByDate
6

I must be missing something. This doesn’t seem to work either. If I do not use a condition, I keep t getting an error message when just changing the date and not hitting save saying that there isn’t any data in OrderHed. If I use the condition, the dates for the appropriate rows change, and the ones that do not need to change, don’t. However, if I do it again, for a date that should change all rows, only the ones that changed the first time get changed. So if line 1 and 3 were changed, but 2 and 4 were not, if do another date that would make all rows change, only rows 1 and three will change. the others do not change. Sometimes they do, sometimes they don’t. Which I find very confusing.

image
image1418Ă—966 64.7 KB

image
image1415Ă—1017 64 KB

7

I ran a couple permutations of this (on SalesOrder.Update and MasterUpdate) on my test environment and, yes, the system just doesn’t reliably bring the header along with the details and releases. Which is both annoying and inconsistent with how most other applications work in the system. Your condition is suppressing the error because it simply isn’t firing when the Header isn’t present.

So, there’s a couple ways to handle this.

  1. Use a condition to see if there’s a header row present. If there is, use the same setter I posted above. If there isn’t, use an Invoke BO Method to get the matching header and assign it to a variable. Then use a second setter block that compares the variable NeedByDate to ttOrderRel. This is a bit of work, and frankly kind of gross, but it is what it is.

  2. Try a data directive. Probably less work for you, but again, DD’s should be an absolute last resort.

8

Tried Data directive before posting this and get, OrderRel ahs more than one line error. I’ve never used a condition to check for header row, so going to figure that out and try your first method.

9

I just remembered you could also use a set by query instead of what I described above. Join ttOrderRel to Erp.OrderHed. That should be simple enough. Not sure why I didn’t think of that the first time…head in the wrong space, I guess.

11

When you change the NeedByDate on the Order Header and then save, Epicor prompts the user asking if they want to propagate the header dates down to lines/releases… Could this BPM be avoided by training users to click YES to that prompt…? Or perhaps your BPM could even attack the UI prompt by passing in true (or whatever method parameters mimic the user clicking “YES”)… You’d have to trace it all out to see if it’s possible (it might not be), but it’s worth considering.

Either way, I would urge caution with any code that silently updates something like a date field… I think it’s usually better to throw an error or warning message to the user and then let them make the changes themselves. Especially in a situation where an OrderHed change needs to trigger an OrderRel update – that’s a precarious BPM no matter how you do it, very easy for it to go south.

1 Like
12

I joined Orderhed, OrderDtl, and ttOrderRel in the set query. I have a message box popup to tell me what the dates are for the lines, and that was correct. However, in the Order screen, no such change.

UPDATE:

Figured it out! I need to sue the MasterUpdate instead of update. The last question on this is, what would be the best way to update OrderDtl NeedByDate? Is it simply doing a set field for that and base it off of the OrderRel date? Or is there a better option?

13

@ckrusen @jtownsend

So, the field gets set in the order, the message box shows it was changed as a well, but when you refresh the order, it reverts back to the original date. What am I missing?

14

I’ve found message boxes to not be 100% reliable when used for debugging.

Also when dubigging, I’ve found “doing the same exact thing” isn’t always the same. Particular what happens when you save a newly created sales order, compared to making changes to an existing one.

And to make sure the form is showing real data from the DB, I’ll not use refresh, but rather load a different order then go back to my test order.

Edit

One more thing that might need investigating is that Epicor doest consider the order of the release the way that you’d think. The release with RelNum 1 is intended to be the last one received. Not the first. I don’t know the all details about this, but others have had issues when RelNum 1 is competed before other releases for the line. Maybe a stretch, but since you’re messing with the dates on the releases, there might be some behind the curtain logic Epicor is injecting.

15

I wonder if your directive is only updating the “tt” table and the change is not committed yet? If you click “save” before your “refresh” - does your date “stick” then?

FWIW…
Managing SO dates (and PO dates) with BPM’s ( as I remember ) usually ended up being a little more complicated/involved than expected.

1 Like
16

@ckrusen
Thank you for the info! I took the order number that I changed the dates, went into another order, and back in, and saw the updated dates were not saved to the DB. As for Epicor logic behind the scenes and OrderRel, not surprised but didn’t know that. Thank you for that info.

@bordway
I thought the same thing. But, I can’t figure out HOW to get the BPM to commit the changes. From my understanding, how I am doing the BPM, it should commit the changes. Thoughts on this?

17

Sorry… it’s been a while so at this point I only remember using a combination of tracing and BPM messageboxes to get a handle on the base mechanics for dates in SO’s and PO’s. Due to logic related to dates in headers, lines and releases - believe there was “stuff” in the background I had to work around.
Also, in one case I (think I) ended up using more methods… besides just Update.

18

@ckrusen @jtownsend

Possibly a dumb question, BUT, on a Set By Query, does the RowMod automatically get triggered? If not, how can I trigger the RowMod? After tracing and going over the details again, I noticed two areas that RowMod was updated, on the OrderRel and on the OrderSched when updating the OrderRel date.

19

RowMod is a field on the row. You can set it like any other property.

20

I think, no. However, as @Will79 said, you can just set the row mod right in the setbyquery.

21

Just to tag onto Bruce’s comment - be cautious when changing dates. For example, if you change a date on an order release via a BPM, there’s an associated PartDtl record that contains all the supply and demand records by part that might not get changed. The PartDtl table is the basis for Time Phase and you can end up with Time Phase showing you different requirements than you’re getting on other reports. Not that I learned this the hard way or anything.

I’ve found dates and quantity fields are the two big ones to watch for. You’re usually safe if you catch them in a pre-processing directive, but even that can bite you. Stay away from data directive and post-processing updates of these fields. In your case, the data you want is not there in the pre-processing directive (you’re changing OrderHed, not OrderRel), so the “safest” way to do this is by using Epicor business object calls to have Epicor change the dates.

Good luck - this is not for the weak at heart. I’ve spent many an hour battling these issues - they’re never fun.

1 Like