Change Issue Material From Whse/Bin Defaults To Use Backflush Hierarchy Logic

Recently with the help of Craig Moore at Viridian Systems Consulting we were able to construct a BPM that would change the default From Warehouse & Bin when using the Issue Material program in Epicor 10.  Instead of defaulting to the Part’s primary bin logic it made more sense to leverage the Epicor Backflush hierarchy to determine the default warehouse & bin when issuing materials.  I think this can give a greater sense of consistency for material handlers who in some cases were getting confused with where to transfer inventory since some materials were backflushed and others were not.

I think it is very important for BPM’s to be driven by dynamic data and not static values embedded in the BPM.  This gives end users more control over how a BPM behaves instead of fighting a static override.  By following the default Epicor backflush logic as well as providing a few other overrides options in Resource Group setup we were able to accomplish this dynamic and ‘smart’ behavior.  Here is how we did it.

Basic Requirements:

When the Issue Qty is updated in the Issue Material program, use the Epicor backflush logic, plus some Resource Group override logic to determine and set the From Warehouse & Bin.

Hierarchy Detail:

First Determine the Labor Reporting Resource.  This is the tricky step since an unscheduled job (which does happen) would not have a Resource defined.  This means we first need to check the Job Material’s  related Operation’s ‘Production Labor Reporting Resource.’  This ‘Resource’ (JobOprDtl) record has fields for both Resource Group and Resource, of which both are not required to be defined so we select whichever is more specific.  This means if the Resource is defined we use that, if not, we must use the Resource Group.  This means that depending on which we use the processed hierarchy will change. If the Resource Group is used then 1, 4, 6, 7, 8 is processed.  If the Resource is used then 2, 3, 5, 6, 7 ,8 is processed.

  1. Job Materials related Operation’s Resource Group Input Warehouse & Bin, if adequate inventory.
  2. Job Operation’s Resource Group Input Warehouse & Bin, no matter the inventory, IF Job Operation’s Resource Get Default Whse From Group (Resource.GetDefaultWhseFromGroup) = TRUE. (this field is hidden by default, but you can view it in the list view with a personalization). This is the override step so if we need the Resource Group settings to be used no matter which Resource is used we can, this is handy when multiple resources pull from the same whse/bin.
  3. Job Operation’s Resource Input Warehouse & Bin, if adequate inventory.
  4. Job Operation’s Resource Group Backflush Warehouse & Bin, if adequate inventory.
  5. Job Operation’s Resource Backflush Warehouse & Bin, if adequate inventory.
  6. Job Material’s Warehouse Primary Bin, if adequate inventory.
  7. Job Material’s Warehouse first Bin, with adequate inventory.
  8. Job Material’s Warehouse first bin no matter the inventory.

BPM Construction:

We identified the ‘IssueReturn.OnChangeTranQty’ as the method to be used and we created a Post-Processing directive.  Since this object is used for both issue and return transactions we first use a Query Condition widget to check the TranType to make sure it’s ‘ STK-MTL.’  If it is we call some C# code to determine the warehouse & bin:

bool FoundIt = false;
string FoundBin = string.Empty;
string FoundWhse = string.Empty;
string vResourceID = string.Empty;
string vResourceGroupID = string.Empty;
string vCompany = string.Empty;
Erp.Tables.JobMtl JobMtl;
Erp.Tables.JobOpDtl JobOpDtl;
Erp.Tables.ResourceGroup ResourceGroup;
Erp.Tables.Resource Resource;
Erp.Tables.Resource Resource0;
Erp.Tables.PartBin PartBin;
Erp.Tables.PartBin PartBin2;
Erp.Tables.PartBin PartBin3;
Erp.Tables.PartBin PartBin4;
Erp.Tables.PlantWhse PlantWhse;
Erp.Tables.PartBin PartBin5;
Erp.Tables.PartBin PartBin6;
Erp.Tables.PartBin PartBin7;
Erp.Tables.WhseBin WhseBin;
Erp.Tables.Warehse Warehse;
Erp.Tables.JobOper JobOper;
vCompany = Session.CompanyID;
foreach (var ttIssueReturn_xRow in ttIssueReturn)
{
var ttIssueReturnRow = ttIssueReturn_xRow;
foreach (var JobMtl_iterator in (from JobMtl_Row in Db.JobMtl
where JobMtl_Row.Company == ttIssueReturn_xRow.Company && JobMtl_Row.JobNum == ttIssueReturn_xRow.ToJobNum &&
JobMtl_Row.AssemblySeq == ttIssueReturn_xRow.ToAssemblySeq && JobMtl_Row.MtlSeq == ttIssueReturn_xRow.ToJobSeq
select JobMtl_Row))
{
JobMtl = JobMtl_iterator;
foreach (var JobOper_iterator in (from JobOper_Row in Db.JobOper
where string.Compare(JobOper_Row.Company, JobMtl.Company, true) == 0 &&
string.Compare(JobOper_Row.JobNum, JobMtl.JobNum, true) == 0 && JobOper_Row.AssemblySeq == JobMtl.AssemblySeq &&
JobOper_Row.OprSeq == JobMtl.RelatedOperation
select JobOper_Row))
{
//determine proper ResourceID and ResourceGroupID
JobOper = JobOper_iterator;
foreach (var JobOpDtl_iterator in (from JobOpDtl_Row in Db.JobOpDtl
where string.Compare(JobOpDtl_Row.Company, JobOper.Company, true) == 0 &&
string.Compare(JobOpDtl_Row.JobNum, JobOper.JobNum, true) == 0 && JobOpDtl_Row.AssemblySeq == JobOper.AssemblySeq && JobOpDtl_Row.OprSeq == JobOper.OprSeq &&
JobOpDtl_Row.OpDtlSeq == JobOper.PrimaryProdOpDtl
select JobOpDtl_Row))
{
JobOpDtl = JobOpDtl_iterator;
if (JobOpDtl.ResourceID == “”)
{
vResourceGroupID = JobOpDtl.ResourceGrpID;
}
if (JobOpDtl.ResourceID != “”)
{
vResourceID = JobOpDtl.ResourceID;
foreach (var Resource_iterator in (from Resource_Row in Db.Resource
where string.Compare(Resource_Row.Company, JobOpDtl.Company, true) == 0 && string.Compare(Resource_Row.ResourceID, JobOpDtl.ResourceID, true) == 0
&& Resource_Row.GetDefaultWhseFromGroup == true
select Resource_Row))
{
Resource0 = Resource_iterator;
vResourceGroupID = Resource0.ResourceGrpID;
}
}
} //JobOpDtl
//Checking Resource Group Input Bin
foreach (var ResourceGroup_iterator in (from ResourceGroup_Row in Db.ResourceGroup
where string.Compare(ResourceGroup_Row.Company, vCompany, true) == 0 && string.Compare(ResourceGroup_Row.ResourceGrpID, vResourceGroupID, true) == 0
select ResourceGroup_Row))
{
ResourceGroup = ResourceGroup_iterator;
foreach (var PartBin_iterator in (from PartBin_Row in Db.PartBin
where string.Compare(PartBin_Row.Company, ResourceGroup.Company, true) == 0 && string.Compare(PartBin_Row.PartNum, JobMtl.PartNum, true) == 0 &&
string.Compare(PartBin_Row.WarehouseCode, ResourceGroup.InputWhse, true) == 0 && string.Compare(PartBin_Row.BinNum, ResourceGroup.InputBinNum, true) == 0
select PartBin_Row))
{
PartBin = PartBin_iterator;
if (ttIssueReturn_xRow.TranQty <= PartBin.OnhandQty)
{
FoundIt = true;
FoundBin = PartBin.BinNum;
FoundWhse = PartBin.WarehouseCode;
}
}
// 1.5 per RB, use Resource Group input if Resource.GetDefaultWhseFromGroup = True, regardless of inventory
foreach (var Resource_iterator in (from Resource_Row in Db.Resource
where string.Compare(Resource_Row.Company, vCompany, true) == 0 && string.Compare(Resource_Row.ResourceID, vResourceID, true) == 0
&& Resource_Row.GetDefaultWhseFromGroup == true
select Resource_Row))
{
if (ResourceGroup.InputBinNum != “” && ResourceGroup.InputWhse != “”)
{
FoundIt = true;
FoundBin = ResourceGroup.InputBinNum;
FoundWhse = ResourceGroup.InputWhse;
}
} //Resource if GetDefault = true
} //Res Group Input
//Checking Resource Input Bin
if (FoundIt == false)
{
foreach (var Resource_iterator in (from Resource_Row in Db.Resource
where string.Compare(Resource_Row.Company, vCompany, true) == 0 && string.Compare(Resource_Row.ResourceID, vResourceID, true) == 0
select Resource_Row))
{
Resource = Resource_iterator;
foreach (var PartBin_iterator in (from PartBin_Row in Db.PartBin
where string.Compare(PartBin_Row.Company, Resource.Company, true) == 0 && string.Compare(PartBin_Row.PartNum, JobMtl.PartNum, true) == 0 &&
string.Compare(PartBin_Row.WarehouseCode, Resource.InputWhse, true) == 0 && string.Compare(PartBin_Row.BinNum, Resource.InputBinNum, true) == 0
select PartBin_Row))
{
PartBin2 = PartBin_iterator;
if (ttIssueReturn_xRow.TranQty <= PartBin2.OnhandQty)
{
FoundIt = true;
FoundBin = PartBin2.BinNum;
FoundWhse = PartBin2.WarehouseCode;
}
}
}
} //resource input
//Checking Resource Group Backflush Bin
if (FoundIt == false)
{
foreach (var ResourceGroup_iterator in (from ResourceGroup_Row in Db.ResourceGroup
where string.Compare(ResourceGroup_Row.Company, vCompany, true) == 0 && string.Compare(ResourceGroup_Row.ResourceGrpID, vResourceGroupID, true) == 0
select ResourceGroup_Row))
{
ResourceGroup = ResourceGroup_iterator;
foreach (var PartBin_iterator in (from PartBin_Row in Db.PartBin
where string.Compare(PartBin_Row.Company, ResourceGroup.Company, true) == 0 && string.Compare(PartBin_Row.PartNum, JobMtl.PartNum, true) == 0 &&
string.Compare(PartBin_Row.WarehouseCode, ResourceGroup.BackflushWhse, true) == 0 && string.Compare(PartBin_Row.BinNum, ResourceGroup.BackflushBinNum, true) == 0
select PartBin_Row))
{
PartBin3 = PartBin_iterator;
if (ttIssueReturn_xRow.TranQty <= PartBin3.OnhandQty)
{
FoundIt = true;
FoundBin = PartBin3.BinNum;
FoundWhse = PartBin3.WarehouseCode;
}
}
}
} //Res Group backflush
//Checking Resource Backflush Bin
if (FoundIt == false)
{
foreach (var Resource_iterator in (from Resource_Row in Db.Resource
where string.Compare(Resource_Row.Company, vCompany, true) == 0 && string.Compare(Resource_Row.ResourceID, vResourceID, true) == 0
select Resource_Row))
{
Resource = Resource_iterator;
foreach (var PartBin_iterator in (from PartBin_Row in Db.PartBin
where string.Compare(PartBin_Row.Company, Resource.Company, true) == 0 && string.Compare(PartBin_Row.PartNum, JobMtl.PartNum, true) == 0 &&
string.Compare(PartBin_Row.WarehouseCode, Resource.BackflushWhse, true) == 0 && string.Compare(PartBin_Row.BinNum, Resource.BackflushBinNum, true) == 0
select PartBin_Row))
{
PartBin4 = PartBin_iterator;
if (ttIssueReturn_xRow.TranQty <= PartBin4.OnhandQty)
{
FoundIt = true;
FoundBin = PartBin4.BinNum;
FoundWhse = PartBin4.WarehouseCode;
}
}
}
} //resource backflush
} //JobOper
//Checking Job Material’s Warehouse Primary Bin
if (FoundIt == false)
{
foreach (var PlantWhse_iterator in (from PlantWhse_Row in Db.PlantWhse
where string.Compare(PlantWhse_Row.Company, JobMtl.Company, true) == 0 && string.Compare(PlantWhse_Row.PartNum, JobMtl.PartNum, true) == 0 &&
string.Compare(PlantWhse_Row.Plant, JobMtl.Plant, true) == 0 && string.Compare(PlantWhse_Row.WarehouseCode, JobMtl.WarehouseCode, true) == 0
select PlantWhse_Row))
{
PlantWhse = PlantWhse_iterator;
foreach (var PartBin_iterator in (from PartBin_Row in Db.PartBin
where string.Compare(PartBin_Row.Company, PlantWhse.Company, true) == 0 && string.Compare(PartBin_Row.PartNum, PlantWhse.PartNum, true) == 0 &&
string.Compare(PartBin_Row.WarehouseCode, PlantWhse.WarehouseCode, true) == 0 && string.Compare(PartBin_Row.BinNum, PlantWhse.PrimBin, true) == 0
select PartBin_Row))
{
PartBin5 = PartBin_iterator;
if (ttIssueReturn_xRow.TranQty <= PartBin5.OnhandQty)
{
FoundIt = true;
FoundBin = PartBin5.BinNum;
FoundWhse = PartBin5.WarehouseCode;
}
}
}
} // Job Mtl Prim Bin
// Checking Job Mtl Whse Bin with enough
if (FoundIt == false)
{
foreach (var PartBin_iterator in (from PartBin_Row in Db.PartBin
where string.Compare(PartBin_Row.Company, JobMtl.Company, true) == 0 && string.Compare(PartBin_Row.PartNum, JobMtl.PartNum, true) == 0 &&
string.Compare(PartBin_Row.WarehouseCode, JobMtl.WarehouseCode, true) == 0
select PartBin_Row))
{
PartBin6 = PartBin_iterator;
if (ttIssueReturn_xRow.TranQty <= PartBin6.OnhandQty && FoundIt == false)  // looking for the first bin with enough inventory then ignoring the rest
{
FoundIt = true;
FoundBin = PartBin6.BinNum;
FoundWhse = PartBin6.WarehouseCode;
}
}
} //Job Mtl Whse Bin with enough
// Using First Job Mtl Whse Bin
if (FoundIt == false)
{
PartBin7 = (from PartBin_Row in Db.PartBin
where string.Compare(PartBin_Row.Company, JobMtl.Company, true) == 0 && string.Compare(PartBin_Row.PartNum, JobMtl.PartNum, true) == 0 &&
string.Compare(PartBin_Row.WarehouseCode, JobMtl.WarehouseCode, true) == 0
select PartBin_Row).FirstOrDefault();
if (PartBin7 != null) // no longer checking for sufficient inventory
{
FoundIt = true;
FoundBin = PartBin7.BinNum;
FoundWhse = PartBin7.WarehouseCode;
}
} //Job Mtl First Whse Bin
} //JobMtl
// Assigning found bin to transaction row
if (FoundIt == true)
{
WhseBin = (from WhseBin_Row in Db.WhseBin
where WhseBin_Row.Company == ttIssueReturnRow.Company && WhseBin_Row.WarehouseCode == FoundWhse && WhseBin_Row.BinNum == FoundBin
select WhseBin_Row).FirstOrDefault();
if (WhseBin != null)
{
ttIssueReturnRow.FromWarehouseCode = FoundWhse;
ttIssueReturnRow.FromBinNum = FoundBin;
ttIssueReturnRow.FromBinNumDescription = WhseBin.Description;
Warehse = (from Warehse_Row in Db.Warehse
where Warehse_Row.Company == ttIssueReturnRow.Company && Warehse_Row.WarehouseCode == FoundWhse
select Warehse_Row).FirstOrDefault();
if (Warehse != null)
{
ttIssueReturnRow.FromWarehouseCodeDescription = Warehse.Description;
}
}
}
} //ttIssueReturn

We really had to fiddle with the code due to scope creep, so it may not be as efficient if we weren’t dodging around learning how things work.

C# code is courtesy of Viridian Systems Consulting.

 

 

 

 

 

Leave a Reply

%d bloggers like this: