BPM for Warning Message on Duplicate PO

Epicor ERP 10
22

Would I be adding this as a second condition on the BPM? Or add it to the query that is already in use under the first condition?

23

I don’t see the join the the data base table in the screen shots above, but I’m assuming that you must have them both (tt and database) if you have it working. So you should be able to simply add the criteria on your database table for the date. (working a little blind here, so forgive the assumptions if they are not correct)

24

Yes, I do link the tt and the database tables, the screenshots are really old. Here is what I’m working off of now:

image
image.png660×533 22.6 KB

25

Right, so add a criteria like this to your OrderHed table.

image

26

I added that under what I already had on the OrderHed table and got a BPM runtime exception during testing. I added a second line on the condition but it is still firing on everything.

27

I’m working on setting up a test.

While I’m doing that, can you set up a message box to return the rows that are being passed? Do you know how to do that?

28

No, I don’t know how to do that. Is that in the trace?

29

You can grab a message box, then in the message body, right click, and you can do a field or table query. Try a table query, then click on some fields. Then when the message pops up, you can see what data is being passed. It can help troubleshoot tricky criteria.

image

image
image.png923×649 30 KB

30

We have a BPM on salesorder.masterupdate pre-process that executes the following custom code, works great for us.

var ttOrderHedRow = (from ttOrderHed_Row in ttOrderHed
    where (ttOrderHed_Row.Added() || ttOrderHed_Row.Updated())
    select ttOrderHed_Row).FirstOrDefault();
if (ttOrderHedRow != null)

    var OrderHedRow = (from OrderHed_Row in Db.OrderHed
        where (OrderHed_Row.Company == ttOrderHedRow.Company && OrderHed_Row.CustNum == ttOrderHedRow.CustNum && OrderHed_Row.PONum == ttOrderHedRow.PONum 
            && OrderHed_Row.OrderNum != ttOrderHedRow.OrderNum) 
        select OrderHed_Row).FirstOrDefault();
    if (OrderHedRow != null)
    {

        this.PublishInfoMessage("WARNING - This PO has already been used on Sales Order " + OrderHedRow.OrderNum, 
            Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "SalesOrder", "CloseOrderLine");    
    }
}
31

Thank you! I set that up so hopefully I can see what’s going on

32

Hey @timshuwy, since your the widget BPM guy., Do you know why adding a date comparison creates an exception when running the BPM? It looks like it’s a problem trying to compare Nullable and non-nullable date fields.

image
image.png718×651 27.7 KB

expand to see exception

Server Side Exception

BPM runtime caught an unexpected exception of ‘NotSupportedException’ type.
See more info in the Inner Exception section of Exception Details.

Exception caught in: Epicor.ServiceModel

Error Detail

Description: BPM runtime caught an unexpected exception of ‘NotSupportedException’ type.
See more info in the Inner Exception section of Exception Details.
Program: EntityFramework.SqlServer.dll
Method: DateAdd
Original Exception Type: NotSupportedException
Framework Method: <C001_QuerySizeCondition>b__3
Framework Line Number: 0
Framework Column Number: 0
Framework Source: <C001_QuerySizeCondition>b__3 at offset 230 in file:line:column :0:0

Server Trace Stack: at System.Data.Entity.SqlServer.SqlFunctions.DateAdd(String datePartArg, Nullable 1 number, Nullable 1 date)
at Epicor.Customization.Bpm.BO712CA4B426BC4295962284D1172CDE1F.UpdatePreProcessingDirective_datetest_36249CCD361A4F6DA32EFCB5327D227B.<>c__DisplayClass2_0.<C001_QuerySizeCondition>b__3(<>f__AnonymousType1 2 <>h__TransparentIdentifier0) at System.Linq.Enumerable.WhereSelectEnumerableIterator 2.MoveNext()
at System.Linq.Enumerable.d__25 1.MoveNext() at System.Linq.Enumerable.Count[TSource](IEnumerable 1 source)
at Epicor.Customization.Bpm.BO712CA4B426BC4295962284D1172CDE1F.UpdatePreProcessingDirective_datetest_36249CCD361A4F6DA32EFCB5327D227B.C001_QuerySizeCondition()
at Epicor.Customization.Bpm.BO712CA4B426BC4295962284D1172CDE1F.UpdatePreProcessingDirective_datetest_36249CCD361A4F6DA32EFCB5327D227B.ExecuteCore()
at Epicor.Customization.Bpm.DirectiveBase`3.Execute(TParam parameters) in C:_Releases\ICE\ICE3.2.200.13\Source\Server\Internal\Lib\Epicor.Customization.BPM\DirectiveBase.Generic.cs:line 147

1 Like
33

well, my first question would by why are you doing this query against the ERP.Orderhed, and not the ttOrderHed…
I am assuming that this is a “Fill data by query” widget, but I cannot tell from the image. I cannot quite tell what you are doing with this widget… but you should be able to do all that work with the tt record that is already in memory.

34

It’s a condition query, trying to check for duplicate PO numbers in the past 90 days.

35

So I think I have a workaround.

Go the variable tab and date a DateTime variable.

image
image.png889×205 4.53 KB

Then add a set arguement/variable widget and set the expression as shown. This will give you the date to compare it to.

image
image.png1301×736 81.5 KB

Then back in your query, you can compare it to the variable instead of trying to calculated it in the query. From my quick non-exhaustive test, it seems to work.

image

One more thing, You will need to add a criteria to your tt table. RowMod = “A” or RowMod = “U”. I think there are some “state” rows with no row mod that will mess up your criteria. In the BPM you have to put “” around your filter values. (you don’t have to do that in a BAQ.)

image

1 Like
36

Thank you! I will test that out today!

37

Thank you for all of your help! I was finally able to test it out and your workaround did the trick. I really appreciate it!

1 Like