ttShipTo is a virtual table that exists only during the transaction (tt = Temporary Table)
Okay that is what I was thinking based on what I know of how transactions work in Epicor. So I canât technically use this tool to test the custom code in its current form it looks like.
I disabled the code in maintenance and am able to bring up the customers in seconds as opposed to minutes.
I am going to have to do some more digging and testing as this code is only going to cause the system to run slower as the number of ShipTos increase for customers as time goes on.
I just tried going through the code and Iâm having a really hard time following what the intent is. This may be a case of re-write is best than a fix. Figure out what the end goal was and re-write it.
The code is looping through ttShiptos (there should be only one), then looping through Db.ShipTo that matches said ttShipTo (there should also be only one)
Do you know what the end goal is, seems messy at best?
This is where I am at with the code honestly. It might be best to take this back to the department and start from scratch to better define it. It was written by a third party who we ended have having to cut ties with due to issues with their development.
The end goal, to my knowledge, is to get the current amount that needs to be billed to the customer per shipto. When going into a customer as seen below, depending on the amount of shiptos it can take as quick as 2 seconds for new customers with little to no information but in the case of customers that we do many jobs for it can take up to a few minutes at times.
Once the customer is pulled up you can see their total bill to date. This code is supposed to grab that information and display it here. It is mainly used by our credit department to be able to have easy access to this information when looking at customers the only issue is that most of the users in our company use the customer maintenance page and it has been getting slower as time goes on due to their naturally being more and more jobs for each customer
And this is the small snippet of code that they are having me look at. Thereâs another one causing issues, also created for our credit department, that is over 1000 lines so that is going to be fun.
I tried⌠to make heads or tails of it⌠I THINK the following will do the same thing threw it together in notepad at the hotel lobby so apologize for the typos and misunderstandings.
foreach(var stt in ttShipTo)
{
decimal TotalBillToDate=0; TotalOrderToDate=0;
int custNum = stt.CustNum;
string shipToNum = stt.ShipToNum;
var shipToRecord = Db.ShipTo.Where(st=>st.CustNum == custNum && st.ShipToNum == shipToNum&& st.Company == callContextClient.CurrentCompany).FirstOrDefault();
TotalBillToDate = Db.InvcHead.Where(ih=>ih.Company == callContextClient.CurrentCompany && ih.CustNum == custNum && ih.ShipToNum == shipToNum).Select(id=>ih.DocInvoiceAmt).Sum();
TotalOrderToDate = Db.OrderHed.Where(oh => oh.Company == callContextClient.CurrentCompany && oh.ShipToCustNum == custNum && oh.ShipToNum ==shipToNum).Select(oh=>oh.DocTotalCharges+oh.DocTotalTax+oh.DocTotalMisc).Sum();
shipToRecord["TotalBillToDate_c"] = TotalBillToDate;
shipToRecord["TotalOrderedToDate_c"] = TotalOrderToDate - TotalBillToDate;
stt["TotalBillToDate_c"] =TotalBillToDate;
shipToRecord["TotalOrderedToDate_c"] =TotalOrderToDate - TotalBillToDate;
Db.Validate();
}
Ok I misunderstood when this was being executed. You will need to loop through the ttShipTo as before since its doing it for all ShipTosâŚ
However this seems hiligh in-efficienct doing this every time you pull up a customer. Why not make a simple BAQ /Dashboard that shows this and bring that in using a BAQDataView or an Embeded Dashboard.
Iâve corrected the code above to use a loop⌠Still think you should go with a BAQview or Embeded Dashboard. though
I believe there is a dashboard that displays this data as well as other job information already created for the department but they are adamant that the information is available on this tab as well.
We were discussing what you propose internally as it causes so many issues and I suggested we should not be having to run this every single time someone goes into a customer as most donât even need that information in the first place.
I am going to dive a little further into the dashboard they have already created and see what they have done to it.
You can use a BAQDataView with Publish and Subscribe to bring this data into that âTabâ as a read only field that is populated by the data view.
2 Likes
That may be the best solution. I will bring it to my team on Monday and discuss further.
Thanks again for the help.
@IanCastellanos Regards to the LINQPad for Prototyping⌠there is obviously no ttâs but I always pretend I have one which I replace in the BPM with the actual tt when I am ready.
Also you cant do some other things like Db.Table.With(LockHint.UpdLock) - you can add LockHintâs like NoLock and UpdLock in the BPM later, just not while in LINQPad.
1 Like
@hkeric.wci Good to know for future use when we start developing in house.
In my case this particular bit of custom code is going through every single shipto for a customer and in the customer I am testing they have over 500 shiptos as of right now and the code seems to work okay when customers have a small amount of shiptos in the system. I will use your example above and just run it against a single ship to and see what the results are as they could give me some better insight to move forward.
So I took your code and modified it a little as I was getting errors thrown and it still took quite a while to bring up customers. When I disable to BPM customers only take a few seconds to bring up at most.
At this point I am just thinking it is best to do what you mentioned before with a BAQDataView to at least get the system working faster and maybe just using that as our solution.
1 Like
By the way what Method or Data Directive is your BPM Code running on ?
Method is GetByCustId
Try changing selection criteria of OrderHed from irow.ShipToCustNum == tt.CustNum to irow.CustNum = tt.CustNum as CustNum and ShipToCustNum will be same . Index is based on CustNum.
@josecgomez What would you suggest that I use as Publish and Subscribe? I have created the BAQ and I am not sure the best way to attach it.
Would I use the customer name? If so would I have to create an ipublisher or should the publisher already be inside of Epicor?
CustID or CustNum is your best bet.
Okay I thought so. I will use those and see what works best.
@josecgomez I had to end up using ShipToNum to get it working as I wanted but am definitely making progress and I think I have it essentially figured out. Unless there is a way to pass both the CustID and ShipToNum to the publisher that I am not doing correctly.
I will have to do some more testing and make some more changes to make sure ShipToNums are not accidentally doubled up as we ran into some weird records with same ShipTos for different customers which shouldnât be happening with our CSRs.
Thank you everyone in here for you input and suggestions.
Yes just do publish subscribe for each of the fields. What does your publish / subscribe code look like?