Epicor suggesting / allowing duplicate POs

[TerryR (Terry Raymond)]

Hey fellas. I’m sort of sounding an alarm here… we recently ran into a bug where items will show up again in PO suggestions, leading to ordering the same thing twice. Epicor confirmed it today.
$100,000++ problem.

One source of the duplicates is a PO line being arrived, staying arrived overnight when PO suggestions is run. But I think there are more sources of duplicates. We’re looking into it now.

Here is query to see duplicate POs. Would be nice if some of yall ran this, let me know if you’re also seeing a problem…

select t1.jobnum
, t1.AssemblySeq
, t1.JobSeq
, t1.JobSeqType
, max(t2.RequiredQty) as DemandQty
,sum(t1.XRelQty) as PurchasedQty
, count(t1.ponum) as [NumPOs]
, min(t2.partnum) as [Job PartNum]
, min(t3.partnum) as [PO PartNum]
, min(t2.Description) as [Descr]
--, avg(t3.unitcost) as [Avg UnitCost]  --can't use avg() because some releases have more qty
, sum(
t3.unitcost*t1.RelQty /	(case CostPerCode when 'E' then 1 when 'C' then 100	when 'M' then 1000 end)
)/sum(t1.RelQty)*max(t1.RelQty/t1.XRelQty) as [Avg Our UnitCost]  --convert PO unit cost (supplier qty) to cost per our qty
, sum(t1.relqty*t3.unitcost /	(case CostPerCode when 'E' then 1 when 'C' then 100	when 'M' then 1000 end))
	- max(t2.RequiredQty)*max(t1.RelQty/t1.XRelQty)*sum(t3.unitcost / (case CostPerCode when 'E' then 1 when 'C' then 100 when 'M' then 1000 end) * t1.RelQty)/sum(t1.RelQty) as [Lost money???]  
from PORel t1 
join jobmtl t2 on t2.company = t1.Company and t2.JobNum = t1.JobNum and t2.AssemblySeq = t1.AssemblySeq and t2.MtlSeq = t1.JobSeq
join ERP.PODetail t3 on t3.company = t1.Company and t3.PONum = t1.PONum and t3.POLine = t1.POLine
left outer join erp.Part t4 on t4.Company = t1.company and t4.partnum = t2.PartNum
--left outer join @JobQtys t6 on t6.JobNum = t2.JobNum and t6.partnum = t2.PartNum
where 
t1.jobnum <> ''
and t1.JobSeqType = 'M'
and t2.RequiredQty > 0
and t1.VoidRelease = 0
and isnull(t4.QtyBearing,1) = 1
group by t1.jobnum, t1.AssemblySeq, t1.JobSeq, t1.JobSeqType
having count(t1.ponum) > 1
and sum(t1.XRelQty) >= 1.5 * max(t2.RequiredQty)
and sum(t1.XRelQty) < 10 * max(t2.RequiredQty)
order by jobnum desc

(edited 17may 12pm cdt better query)

[jkane (John Kane)]

I always recommend setting the release being closed on Arrival so that does not happen. It is much easier to handle the issues of creating a new PO than double ordering.

If you want to change your setting, it is on the Site.

[aosemwengie1 (Alisa Osemwengie)]

@TerryR

Epicor confirmed it today.

Do you have a PRB number?

[TerryR (Terry Raymond)]

CS0004331521
No PRB yet

[jkane (John Kane)]

Wait, sorry, said something incorrect.

You can change the setting to close the release to “Received”, that way the supply of the release will still show because “Arrived” does not go to inventory.

[TerryR (Terry Raymond)]

Yes this is how we have it set, on Received. This is the better setting for doing RMAs. I also only see option in company config, not site.

[hackaphreaka (Garret Travers)]

Did not even know about that setting. Ours was set at Arrival, which seems bad. Is that the default? Thankfully we do not utilize the arrived status and everything goes right from open to received. Otherwise this seems like it would cause a lot of headaches if you had any significant amount of time between arrived and received.

[jkane (John Kane)]

Sorry, got it confused with the Material Dates field on the site that sets when dates are generated for PO Suggestions.

Yikes, that does sound like a bug. Hopefully they will address it soon.

That is a very interesting situation though. What does time phase show for something that has arrived? I would expect to still see the PO release supply meeting whatever demand there was.

[hackaphreaka (Garret Travers)]

@jkane

What does time phase show for something that has arrived?

I was curious myself so I did a quick test. It shows up as supply in Time Phase even though it isn’t received and the PO gets auto-closed.

[jkane (John Kane)]

So, they put the Arrived status as an exception. Very interesting

[hackaphreaka (Garret Travers)]

Arrived seems to be this weird pseudo-inventory status kind of like Inspection. Although with arrived I’m not even sure where you would go in Epicor to see what inventory location it was in.

[jkane (John Kane)]

Arrived is not inventory. The arrived status can be used two different ways. First, you can mark something as being “in your building” by creating a receipt and not marking it received. This puts it in the arrived state so you have a record of it being there without putting it in your inventory. The second way it is used is for inspection. Technically, when something goes to inspection, it is not received and part of inventory. It is in arrived state until it is approved by QC, then it goes to inventory.

If you query the receipt detail table, you will see all of these fields. It just sort of happens in the background without most people knowing.

[TerryR (Terry Raymond)]

Anyone able to run that query? Are yall seeing many duplicate POs for job materials?

[TerryR (Terry Raymond)]

So no takers on running that query to see how many duplicate material orders yall have made? We had about 300 since inception.

I made a data directive BPM to stop many of the duplicate orders, including the arrived bug.
It warns on duplicate operation orders and non-qty-bearing parts, but doesn’t prevent. I don’t like it but thats how we intentionally order quite a few things…

My code. Has Db reads, no writes. Feedback appreciated.

var ttPORelrow = ttPORel.FirstOrDefault();
gThisPONum = ttPORelrow.PONum;

//#### Get demand ####
if (ttPORelrow.JobSeqType == "M") {
  var JobMtlResult = (from t1 in Db.JobMtl
    from t2 in Db.Part.Where(t2r => t2r.PartNum == t1.PartNum).DefaultIfEmpty()
    where t1.JobNum.Equals(ttPORelrow.JobNum)
    && t1.AssemblySeq == ttPORelrow.AssemblySeq
    && t1.MtlSeq == ttPORelrow.JobSeq
    select new{t1.RequiredQty, t2.QtyBearing}).FirstOrDefault();

  if (JobMtlResult != null) {
    if (JobMtlResult.QtyBearing == true) {
      gDemandQty = JobMtlResult.RequiredQty;
    } else {
      gDemandQty = -1; //not qty bearing, don't check
    }
  }    
}
else if (ttPORelrow.JobSeqType == "S") {
  var JobOprRelQty = (from t1 in Db.JobOper
    where t1.JobNum.Equals(ttPORelrow.JobNum)
    && t1.AssemblySeq == ttPORelrow.AssemblySeq
    && t1.OprSeq == ttPORelrow.JobSeq
    select t1.RunQty).FirstOrDefault();
    
  if (JobOprRelQty != null) {
    gDemandQty = JobOprRelQty;
  }    
}

//#### Get Already Ordered Qty ####
var TotalOtherPORelQtys = (from t1 in Db.PORel
  where t1.JobNum.Equals(ttPORelrow.JobNum)
  && t1.AssemblySeq == ttPORelrow.AssemblySeq
  && t1.JobSeq == ttPORelrow.JobSeq
  && t1.JobSeqType == ttPORelrow.JobSeqType
  && t1.VoidRelease == false
  && (t1.PONum != ttPORelrow.PONum //only other POs
  || (t1.PONum == ttPORelrow.PONum && t1.POLine != ttPORelrow.POLine)) //or this PO but other lines
  select t1.RelQty);
  
if (TotalOtherPORelQtys.Count() > 0) {  //Will be null if no more rows
  gOtherPOorderQty = TotalOtherPORelQtys.Sum();
  
  //get other PO info.. lazy code, nearly same query as above.
  var OtherPOInfo = (from t1 in Db.PORel
    where t1.JobNum.Equals(ttPORelrow.JobNum)
    && t1.AssemblySeq == ttPORelrow.AssemblySeq
    && t1.JobSeq == ttPORelrow.JobSeq
    && t1.JobSeqType == ttPORelrow.JobSeqType
    && t1.VoidRelease == false
    && (t1.PONum != ttPORelrow.PONum //only other POs
    || (t1.PONum == ttPORelrow.PONum && t1.POLine != ttPORelrow.POLine)) //or this PO but other lines
    select new{t1.PONum, t1.POLine}).FirstOrDefault();
    
  gOtherPONum = OtherPOInfo.PONum;
  gOtherPOLine = OtherPOInfo.POLine;
} 

[TerryR (Terry Raymond)]

Epicor PRB 0282630