We are trying to force folks to complete jobs before they take the item to be shipped. It is tough though when the truck is at the dock and folks start to rush.
I have a query that I want to use in a BPM. I am trying to convert the query to use then the ttShipDtl.ReadyToInvoice field gets updated to be checked (true). This query check to make sure the QtyCompleted on the job is completed before the items can be shipped. Do I need to set the variables before I create the condition on the if query? I am not seeing how to do this in the designer.
Better yet, is there a setting in the config that will stop users from shipping without having to create the BPM.
select sd.EffectiveDate, sd.ChangeDate, sd.PackNum, sd.PackLine, sd.OrderNum, sd.OrderLine, sd.OrderRelNum, sd.LineType, sd.OurJobShipQty, sd.JobNum, sd.ReadyToInvoice,
jp.ProdQty, jp.ShippedQty, jp.ReceivedQty, jp.WIPQty,
jh.JobNum, jh.JobComplete, jh.QtyCompleted,
( jh.QtyCompleted - jp.ShippedQty ) as AvailToShip
from Erp.ShipDtl sd
inner join Erp.JobProd jp on sd.OrderNum = jp.OrderNum and sd.OrderLine = jp.OrderLine and sd.OrderRelNum = jp.OrderRelNum
inner join JobHead jh on jh.JobNum = jp.JobNum
where 1 = 1
and ((sd.OurJobShipQty) <= ( jh.QtyCompleted - jp.ShippedQty ))
--and sd.ReadyToInvoice = 1
Hey Ken, I’d love to help but I am not clear on exactly what you are doing here.
On one hand I see that you mention forcing folks to complete jobs before they take the item to be shipped, then you also mention ttShipDtl.ReadyToInvoice. Whats the relevance?
What relation is there between the order line and the job? Is it simply ensuring enough of a certain part is avialable in stock (or WIP if needed) to ship? Does it HAVE to be from a certain job, or is the proper part number sufficient?
See if you can make me understand ( you know I am special so use small words haha) and I’ll try to repay you for the epic BOM crawling BAQ you helped me with.
Ken,
You’ll have to write that as a LINQ query in Custom Code use a Custom Code Condition and then use the Field Changed from X to Y as another condition (ReadyToInvoice)
See below
Like this (note I attempted to translate your SQL query to LINQ not sure if I hit the mark or not but you’l get the idea.
bool bad=false;
foreach(var x in ttShipDtl)
{
var val = (from jp in Db.JobProd
join jh in Db.JobHead on new {jp.Company, jp.JobNum} equals new {jh.Company, jh.JobNum}
where jp.Company == x.Company && jp.OrderNum == x.OrderNum && jp.OrderLine == x.OrderLine
&& jp.OrderRelNum == x.OrderRelNum select new {jh,jp}).FirstOrDefault();
if(x.OurJobShipQty <= (val.jh.QtyCompleted - val.jp.ShippedQty))
{
bad=true;
break;
}
}
return bad;
Most of what we ship is made to order. So the unit being shipped will be coming from a job. We have had a few times where units were shipped, but the job activity wasn’t completed.
There might be a better way to do this.
The ReadyToInvoice is the trigger on the record to file the BPM.
