BPM for UnitPrice change in Sales Order Entry Epicor10

Yahoo Archive
1
Yes I know.
Apparently I had to add to your last post the following to make it update:

foreach (var ttOrderDtl_iterator in (from ttOrderDtl_Row in ttOrderDtl where ( ttOrderDtl_Row.Added() || ttOrderDtl_Row.Updated() ) select ttOrderDtl_Row))
{
your post
}

Thanks a lot! I really appreciate your help!

2
I have created a BPM to send e-mails whenever the Unit Price from a Sales Order entry is changed. (SalesOrder.Update)

The e-mail includes information like:  SO#, Line#, PO#, Part#, new UnitPrice and so on.

The problem is that I also need to have in my e-mail the job number that is linked to that Sales Order.

Well, information about jobnum can be found
within JobHead table.

 

Is it possible to include information in my e-mail from other tables (which are not in the list), if yes how do I include other tables?
I only have the following: ttHedTaxSum, ttOHOrderMsc, ttOrderXXXX, ttSelectedSerialNumbers,  ttSerialNumberSearch, ttSNFormat, ttTaxConnectStatus
Is there a possibility to see the ttJobHead in there?

In the past we where using BAM(Vantage 8) but now it doesn't work so that's why I have tried to implement it with a BPM.
In the past with BAM, I had something like that in my .p file:

IF Mfgsys.JobHead.JobClosed<>true Then Do:
                        IF strJobs="" THEN DO:
                            strJob=Mfgsys.JobProd.JobNum.
                            strJobs=Mfgsys.JobProd.JobNum.
                        END.
                        IF strJobs<>"" THEN DO:
                            IF Mfgsys.JobProd.JobNum<>strJob THEN DO:
                                strJob=Mfgsys.JobProd.JobNum.
                                strJobs=strJob + ", " + strJobs.
                            END.
                        END.
                        IF strJob<>"" THEN DO:
                            IF intQty=0 and Mfgsys.JobHead.StartDate<>? then datStartDate=Mfgsys.JobHead.StartDate.
                            IF intQty=0 and Mfgsys.JobHead.StartDate=? and Mfgsys.JobHead.ReqDueDate<>? then datStartDate=Mfgsys.JobHead.ReqDueDate.
                            IF intQty=0 then intQty=Mfgsys.JobHead.ProdQty.

Thanks!


3
If you’re coming from the sales order, you can query the jobprod table using SO/Line/Rel. There are a couple ways you can go about it; execute custom code and write a linq query and store those values in your call context data, this would allow you to include extraneous info using the standard email wizard/template. The other would be to do all that via custom code and define and send the email using c#.

Rob Bucek
Production Control Manager
D&S Manufacturing
301 E. Main St. | PO Box 279
Black River Falls, WI 54615
715-284-5376 Ext. 311
Mobile: 715-896-3119
rbucek@...
Visit our newly redesigned Website at www.dsmfg.com<http://www.dsmfg.com/>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Friday, January 8, 2016 3:52 PM
To: vantage@yahoogroups.com
Subject: [Vantage] BPM for UnitPrice change in Sales Order Entry Epicor10


I have created a BPM to send e-mails whenever the Unit Price from a Sales Order entry is changed. (SalesOrder.Update)

The e-mail includes information like: SO#, Line#, PO#, Part#, new UnitPrice and so on.

The problem is that I also need to have in my e-mail the job number that is linked to that Sales Order.
Well, information about jobnum can be found within JobHead table.

Is it possible to include information in my e-mail from other tables (which are not in the list), if yes how do I include other tables?
I only have the following: ttHedTaxSum, ttOHOrderMsc, ttOrderXXXX, ttSelectedSerialNumbers, ttSerialNumberSearch, ttSNFormat, ttTaxConnectStatus
Is there a possibility to see the ttJobHead in there?

In the past we where using BAM(Vantage 8) but now it doesn't work so that's why I have tried to implement it with a BPM.
In the past with BAM, I had something like that in my .p file:

IF Mfgsys.JobHead.JobClosed<>true Then Do:
IF strJobs="" THEN DO:
strJob=Mfgsys.JobProd.JobNum.
strJobs=Mfgsys.JobProd.JobNum.
END.
IF strJobs<>"" THEN DO:
IF Mfgsys.JobProd.JobNum<>strJob THEN DO:
strJob=Mfgsys.JobProd.JobNum.
strJobs=strJob + ", " + strJobs.
END.
END.
IF strJob<>"" THEN DO:
IF intQty=0 and Mfgsys.JobHead.StartDate<>? then datStartDate=Mfgsys.JobHead.StartDate.
IF intQty=0 and Mfgsys.JobHead.StartDate=? and Mfgsys.JobHead.ReqDueDate<>? then datStartDate=Mfgsys.JobHead.ReqDueDate.
IF intQty=0 then intQty=Mfgsys.JobHead.ProdQty.

Thanks!





[Non-text portions of this message have been removed]
4

 Find First JobHead Where JobHead.Company = ttTable.Company and JobHead.JobNum = ttTable.JobNum  no-lock no-error.

 If avail JobHead Then Do:

 Define Variable varJobNum as integer no-undo.

                    assign varJobNum = JobHead.JobNum.

End.


Here's an example of how to pull data from one table by linking it to a tt table.  I think you'll be looking for the JobProd table through the ordernum first though... then the jobhead information.

5
Thank you for your advice, but it's still ambiguous for me, I dont have that much of experience with ERP
6
duckorz, 

Where would I be writing this query?create a baq or how? and after this is written the tt table with jobhead will be there?

thanks!
7
My bad i0nutzz, I posted ABL code.  For E10 here is an example.

Erp.Tables.JobHead JobHead;

var ttTable_xRow=(from ttTable_Row in ttTable where string.Compare(ttTable_Row.RowMod,"U",true)== 0 select ttTable_Row).FirstOrDefault();

JobHead =(from JobHead_Row in Db.JobHead where JobHead_Row.Company == Session.CompanyID && string.Compare(JobHead_Row.JobNum,ttTable_xRow.JobNum,true)== 0 select JobHead_Row).FirstOrDefault();

You'll want to create a Standard Data Directive and add into the design a condition, custom code and an email action.  You'll need to modify/replicate the example above in an execute custom code in the designer.
Once you find the data you want, pass it to the ttTable UD fields to pull into the email action.
8
duckorz,

for my already created BPM in pre-processing, insert an "execute custom code" caller with your code above after my condition?
I tried different ways but still do not see that temp table that I need.
Perhaps, I am missing something...

Thanks

9
Your temp tables are pre-determined by the directive you are using.  What you want is for ttorder to make its way to job.  ttOrderhed/ttOrderdtl are the pre-existing temp tables as well as a few others.  You need to write in actual tables to pull data which is what i showed you above.  So ttOrdertable(temp header or detail) has an OrderNum field.  You need to then look at JobProd(actual table) to find the row with the OrderNum of your temp table.  Once you find that you can link JobProd (actual table) to your JobHead (actual table) using the JobProd.JobNum as a link and pull that data into your temp table UD fields (ttorderwhatever) to use in your email.

Again, the previously posted is an example and will not do what you want without re-writing the C# code. Copying and pasting will not work. 
10
Thank you for you detailed explanation, I appreciate.
I have found what you have mentioned above, but where do I go to link those together?
Create a query with ttJobProd with ERP.JobHead and use Table Relations as JobNum?

Sorry for this, but I dont get it completely.
11
3 Queries using the C#/Linq example provided. Again temp tables are pre-determined so ttJobProd does not exist which is the whole reason you are writing the query to link the table JobProd and then to JobHead to get your information.  

So you go to the Execute custom code and use the script above to get your additional tables.  The query links are as follows.

ttOrderDtl--->OrderNum<---JobProd--->JobNum<---JobHead

You need to know C#/Linq to accomplish this.


12
Great! I appreciate your help, I hope I will fix my issues.
Thanks!
13
duckorz,

I skipped this issue until now, and got back to it.
In my pre-processing I have used: condition->execute custom code->enable post directive, then a post-processing with condition and send e-mail.

Inside the custom code here is what I have(I am using epicor 10):

Erp.Tables.JobHead JobHead;
Erp.Tables.JobProd JobProd;
Erp.Tables.OrderDtl OrderDtl;

foreach (var ttOrderDtl_iterator in (from ttOrderDtl_Row in ttOrderDtl
where (string.Equals(ttOrderDtl_Row.RowMod, IceRow.ROWSTATE_UPDATED,
StringComparison.OrdinalIgnoreCase))
select ttOrderDtl_Row))
{

var ttOrderDtlRow = ttOrderDtl_iterator.ToString();

var ttOrderDtl_xRow=(from ttOrderDtl_Row in ttOrderDtl where ttOrderDtl_Row.Company == Session.CompanyID && string.Compare(ttOrderDtl_Row.RowMod,ttOrderDtlRow,true)== 0 select ttOrderDtl_Row).FirstOrDefault();

OrderDtl = (from OrderDtl_Row in Db.OrderDtl where OrderDtl_Row.Company == Session.CompanyID && string.Compare(OrderDtl_Row.OrderNum.ToString(),ttOrderDtl_xRow.OrderNum.ToString(),true)== 0 select OrderDtl_Row).FirstOrDefault();

JobProd = (from JobProd_Row in Db.JobProd where JobProd_Row.Company == Session.CompanyID && string.Compare(JobProd_Row.OrderNum.ToString(),OrderDtl.OrderNum.ToString(),true)== 0 select JobProd_Row).FirstOrDefault();

JobHead = (from JobHead_Row in Db.JobHead where JobHead_Row.Company == Session.CompanyID && string.Compare(JobHead_Row.JobNum.ToString(),JobProd.JobNum.ToString(),true)== 0 select JobHead_Row).FirstOrDefault();
}

Is this what you have suggested?

The question is, if yes, how do I include the job number in my e-mail?

Thanks!


14
Thinking about this more I realized that there is no need for JobHead.  JobProd technically holds the jobnum field which is what I think you need.  Glancing over your queries they look correct.  Like I said you could probably remove the 3rd.  Last thing would be assigning the context table with your new found data.

if(JobProd != null){
callContextBpmData.Number01 = JobProd.JobNum; //(or character01 if you want to ToString it)
}

Last would just be adding the field from Context into the email.
15
duckorz,

Yes you were right, there is no need for the 3rd.
The thing is that it does display the job in my e-mail BUT with FirstOrDefault(), if I go to another Sales Order and try to update the Unit Price in my e-mail I will get the same JobNum as before(it is not updating the jobnum)
I have tried orderby param.jobnum descending select param.FirstOrDefault() to get the last value, but now it will display the last value.
Let's say if I want to go back to a previous Sales Order and update the Unit Price it will still show me the last jobnum value and not the correct one.

Any thoughts to fix this?


P.S. when writing the queries for some reason OrderDtl_Row.OrderNum.ToString() and the others had the following error:

Server Side Exception
LINQ to Entities does not recognize the method 'System.String ToString()' metho
d, and this method cannot be translated into a store expression.
Exception caught in: Epicor.ServiceModel

so they had to be changed like this:
->SqlFunctions.StringConvert((double)OrderDtl_Row.OrderNum).Trim() and add 
->using System.Data.Objects.SqlClient; in the BPM Designer>Usings tab.

Cheers!
16
Try this instead?

Erp.Tables.JobProd JobProd;

var ttOrderDtl_xRow=(from ttOrderDtl_Row in ttOrderDtl where string.Compare(ttOrderDtl_Row.RowMod ,"U" ,true)==0 select ttOrderDtl_Row).FirstOrDefault();

JobProd = (from JobProd_Row in Db.JobProd where string.Compare(SqlFunctions.StringConvert((double)JobProd_Row.OrderNum).Trim(), SqlFunctions.StringConvert((double)ttOrderDtl_xRow.OrderNum).Trim(),true)==0 select JobProd_Row).FirstOrDefault();

if(JobProd != null){
callContextBpmData.Character01 = JobProd.JobNum.ToString();
}


17
nope, I have tried it too.
Still does not work.
I think for Epicor 10 the update RowMod is a bit different:

In many BPMs a user wants to perform a certain action if the tt record is a new record or updated record. In
Epicor 9.05 the status of the RowMod = 'A' or 'U' was used in this case. The Epicor 10 equivalent is displayed
below:
foreach (var ttAPInvHed_iterator in (from ttAPInvHed_Row in ttAPInvHed
where (string.Equals(ttAPInvHed_Row.RowMod, IceRow.ROWSTATE_ADDED,
StringComparison.OrdinalIgnoreCase) ||
string.Equals(ttAPInvHed_Row.RowMod, IceRow.ROWSTATE_UPDATED,
StringComparison.OrdinalIgnoreCase))
select ttAPInvHed_Row))

I have tried the next thing:

Erp.Tables.JobProd JobProd;

foreach (var ttOrderDtl_iterator in (from ttOrderDtl_Row in ttOrderDtl where (string.Equals(ttOrderDtl_Row.RowMod, IceRow.ROWSTATE_UPDATED, StringComparison.OrdinalIgnoreCase)) select ttOrderDtl_Row))
{

var Update = ttOrderDtl_iterator.ToString();

var ttOrderDtl_xRow=(from ttOrderDtl_Row in ttOrderDtl where string.Compare(ttOrderDtl_Row.RowMod ,Update ,true)==0 select ttOrderDtl_Row).FirstOrDefault();

JobProd = (from JobProd_Row in Db.JobProd where string.Compare(SqlFunctions.StringConvert((double)JobProd_Row.OrderNum).Trim(), SqlFunctions.StringConvert((double)ttOrderDtl_xRow.OrderNum).Trim(),true)==0 select JobProd_Row).FirstOrDefault();

if(JobProd != null){
callContextBpmData.Character01 = JobProd.JobNum.ToString();
}
}

and I get the following error when I update the Unit Price, that the object is not instantiated

e.g. Object reference not set to an instance of an object.
 


18
I just copied and pasted my script into a standard data directive with a condition of unit price from any to another.  It worked just fine.
19
I know what you mean, the logic is good, the query as well, but on mine it just would not update.
Yeah, I have the same thing set in my BPM.

Are you at least using the same version of Epicor(10.0.700.0) or an older one?


20
I posted the entire thing.  You removed ALL of your old code correct?  The only thing in the BPM should be what I posted.  There shouldn't be a foreach or update.