I’ve been asked to create an email alert when a customer shipment over a certain value is made.
I’m a bit of a BPM novice. I’ve created a post-processing directive using Erp.CustShip.InvoiceShipment to send an email. But I’m completely stumped as to how to add the criteria.
I thought to use the “Number of rows in the xxx query is not less than 1” condition, using the ShipOrd.ShippedAmt as criteria, but it doesn’t seem to be available to me.
Equally, I’ll need to pull fields such as Order Num and Customer Name into my email body. How do I make these available?
We do this but when a high value order is taken. It’s a set of two data directives, one InTransaction to test for the conditions, and one Standard to actually do the sending. It tends to be easier to put your conditions in pre-processing. Our condition is just field value greater than, no need for a query.
Because we had people objecting to multiple sending whenever the order was adjusted we also added a UD field to flag when it had gone, but on shipments you can probably get away without that.
Turns out ShipOrd.ShippedAmt isn’t the criteria to use, as we don’t invoice until the following day and so far as I can see that’s when this table is populated.
So, I’ve spent the afternoon going round in circles to be honest.
I guess that I need to link ShipDtl back to OrderDtl to get the shipment value information. But it’s all a bit beyond me I’m afraid.
If you go to company config | Modules | Materials | Shipping Receiving and check the calculate prices box, then the unit price and qty shipped * unit price for each relevant pack slip line will be stored in the shipdtl table - they will also appear on the pack slip so you may want to edit the pack slip to hide these on the printed output.
If you turn this option on you should be able to reference them in a bpm - not quite sure how exactly you would go about calculating the sum
If this works for you then I would use it as it is using core system functionality to do the calculations.
If for whatever reason it isn’t an option, then you can create a BPM variable of decimal type, and populate it with a “Set Argument/Variable” widget, expression something like
(decimal?)(from drow in Db.ShipDtl
join hrow in (from row in Db.ShipHead where row.PackNum == ttShipHeadRow.PackNum select row) on drow.PackNum equals hrow.PackNum
join orow in Db.OrderDtl on new {drow.OrderNum, drow.OrderLine} equals new {orow.OrderNum, orow.OrderLine}
select ((orow.ExtPriceDtl / orow.OrderQty) * (drow.OurInventoryShipQty + drow.OurJobShipQty))).Sum() ?? 0.0m
You need a conditional before it to make sure the right things have changed in ttShipHead, for example that the shipment has been shipped, but then another condition after this one can check its value as needed. And obviously it isn’t using the full system logic to arrive at the value, either, but it should be possible to tweak it to get close enough for the purpose.
Definitely see if the configuration option will work first, though.
one way to calculate any sum is a foreach loop within the target table and this simple equation using relevant variable type
sum =sum + table.row value
Thanks for your help, all.
I only work part time (hence the delay in responding) and this isn’t my main priority right now - I’m kind of working on it as and when. You’ve given me great pointers though to be starting with. I’m sure I’ll be back when I still can’t figure it out.
Hi Daryl,
That is a lovely piece of code How would I modify it to calculate the values of all shipments for a customer that had been shipped but had not yet been invoiced?
Best regards
Adrian.
I have a Method Directive, pre processing on the CustomerShip.Update, which so far (with the help of your code) calculates the open invoice value plus the value of the current shipment to check that it does not exceed the customer’s credit limit. My problem is that our despatch department ship many orders before invoicing takes place, sometimes for the same customer. We do not want to include open orders in the credit calculation so we often get caught out when five or six orders have been shipped to the same customer and any one of them would have exceeded their credit limit.
The best solution is to include open orders and then release individual orders for shipment as required. If you use the readytoinvoice flag to calculate value, there is a chance that the packing slip is printed but not marked shipped. More than one person making packing slips for the same customer can cause an issue also.
That is what we do. Sometimes we have to release multiple orders on the same day for the same customer, but there is no chance of failure by using the system inherent capabilities.
I will give that a try and see what I get. Thanks Daryl. Do I need a For Each or similar to get them all or will the code return the sum of all pending shipments for invoicing?
We don’t include open orders in the credit calculation because it does not work for us. We have hundreds of open orders in our system at any one time, with many customers having multiple orders relating to various projects, some of which are not due for completion / delivery for months to come.
How would I modify it to calculate the values of all shipments for a customer that had been shipped but had not yet been invoiced?