Question

Cross Company Stock On Hand Report

  • 20 June 2023
  • 4 replies
  • 93 views
M

Hi, Has anyone here developed a BAQ dashboard that shows On Hand Qty of all parts or even a searched part number across different companies with Epicor.

Our ERP setup has 3 companies, (USA, UK, Canada) our users want to be able to look at a dashbaord and filter or search for a part to see what the other 2 companies have on hand of that part.

This is for our aftermarket, if a customer needs a part and the USA team have 0 on hand the can easily look at this report to see if Canada or UK have the part on Hand.

Thanks in advance
Mal

4 replies

T
Userlevel 3

Hi Mal

Yes I have developed something very similar in the past. The primary purpose was for the predictive search in Quote Entry and SO Entry but it was also used to power a dashboard.

Not only could it look cross-company and provide on-hand stock levels, it also used the various XRef data within the system to allow the user to search on the part refernce they have been provided with (e.g. System/Internal PartNum, Manfacturer Part Num, Vendor Part Num or Customer XRef).

I don’t have a copy of it any longer but the principal of the BAQ driving the feature was a series of UNIONs on different sub-queries pulling all the relevant data together. 

There were 3 fields on the BAQ:

  • Searchable Part Num
    • E.g. Part.PartNum, VendorXRef PartNum, Manf PartNum
  • Returned Part Num
    • Your actual internal Part Num
  • Description
    • This was concatenated information pulled in from various fields and displayed to the User e.g. OnHand Qty, Plant/Warehouse, Description

If you weren’t using the BAQ for predictive searching then the description example above could be split out into multiple fields.

The full query would take a while to run (still less than 5 seconds) but the predictive search was almost instant as it was applying the text filter based on the first few characters entered in the search field.

If you want a working example I can probably pull this together but it won’t be until next week.

Tim

M

Hi, Tim thanks for this info.

I would appreciate a working example if possible. No rush next week or the week after is fine

T
Userlevel 3

Hi Mal

I’ve attached the BAQ for you and a code except below. This BAQ was originally produced to act as a predictive search on the PartNum field on QuoteDtl, OrderDtl and PODetail. 

I have modified it slightly however, to give you an example of adding in the PartBin info for the relevant PartNum. If you want to use it for the predictive part search you’ll need to remove the additional PartBin columns. If this info is required for the predictive search (to make it easier to see what’s in stock) these fields will need concatenating into the ‘SearchPart’ field. Remember that if you add integers or boolean fields to the string you’ll need to convert them first (e.g. Convert(varchar, PartBin.OnHandQty).

PartBin is used as there is ‘normally’ only a record in the table for a PartNum, Warehouse, Bin combination when the OnHand Qty is not 0.

Screenshot of the BAQ Query Results

The principles of the BAQ can be used for other use-cases.

  1. Unions are used to bring multiple sets of results together
    • The number of fields must be consistent across the union queries
  2. Each Table that is being searched has two queries in the dataset
    • The first removes any non-alphanumeric characters from the PartNum field
    • The second ‘Clean’ search searches the original PartNum without any modifications
    • This allows users to search for ‘001MPCons’ or ‘001_MPCons’ and get the same data returned
  3. The field that is being searched by the Predictive Search is the ‘SearchPart’ Field
  4. The data that is returned to the Entry Screen is the ‘Part’ Field which is always the Key PartNum field of the relevant query
  5. Additional data can be concatenated into the ‘SearchPart’ field to display additional information to the User

I hope this helps. If you have any specific queries let me know and I’ll do my best to help out.

Tim

 

BAQ SQL Query:

select 
	[Part].[PartNum] as [Part_PartNum],
	(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Part.PartNum ,',',''),'.',''),'/',''),'\',''),'_',''),'-',''),'+',''),' ',''),')',''),'(','') + ' - ' + Part.Company + ' - ' + Part.PartDescription) as [Calculated_SearchPart],
	[PartBin].[Company] as [PartBin_Company],
	[PartBin].[PartNum] as [PartBin_PartNum],
	[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
	[PartBin].[BinNum] as [PartBin_BinNum],
	[PartBin].[OnhandQty] as [PartBin_OnhandQty]
from Erp.Part as Part
inner join Erp.PartBin as PartBin on 
	Part.Company = PartBin.Company
	and Part.PartNum = PartBin.PartNum
union
select 
	[Clean_Part].[PartNum] as [Clean_Part_PartNum],
	(Clean_Part.PartNum + ' - ' + Clean_Part.Company + ' - ' + Clean_Part.PartDescription) as [Calculated_SearchPart],
	[Clean_PartBin].[Company] as [Clean_PartBin_Company],
	[Clean_PartBin].[PartNum] as [Clean_PartBin_PartNum],
	[Clean_PartBin].[WarehouseCode] as [Clean_PartBin_WarehouseCode],
	[Clean_PartBin].[BinNum] as [Clean_PartBin_BinNum],
	[Clean_PartBin].[OnhandQty] as [Clean_PartBin_OnhandQty]
from Erp.Part as Clean_Part
inner join Erp.PartBin as Clean_PartBin on 
	Clean_Part.Company = Clean_PartBin.Company
	and Clean_Part.PartNum = Clean_PartBin.PartNum
union
select 
	[PartXRefMfg].[PartNum] as [PartXRefMfg_PartNum],
	(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(PartXRefMfg.MfgPartNum ,',',''),'.',''),'/',''),'\',''),'_',''),'-',''),'+',''),' ',''),')',''),'(','') + ' - ' + Part_PartXRefMfg.Company + ' - ' + Part_PartXRefMfg.PartDescription) as [Calculated_SearchPart],
	[PartXRefMfg_PartBin].[Company] as [PartXRefMfg_PartBin_Company],
	[PartXRefMfg_PartBin].[PartNum] as [PartXRefMfg_PartBin_PartNum],
	[PartXRefMfg_PartBin].[WarehouseCode] as [PartXRefMfg_PartBin_WarehouseCode],
	[PartXRefMfg_PartBin].[BinNum] as [PartXRefMfg_PartBin_BinNum],
	[PartXRefMfg_PartBin].[OnhandQty] as [PartXRefMfg_PartBin_OnhandQty]
from Erp.PartXRefMfg as PartXRefMfg
inner join Erp.Part as Part_PartXRefMfg on 
	PartXRefMfg.Company = Part_PartXRefMfg.Company
	and PartXRefMfg.PartNum = Part_PartXRefMfg.PartNum
inner join Erp.PartBin as PartXRefMfg_PartBin on 
	Part_PartXRefMfg.Company = PartXRefMfg_PartBin.Company
	and Part_PartXRefMfg.PartNum = PartXRefMfg_PartBin.PartNum
union
select 
	[Clean_PartXRefMfg].[PartNum] as [Clean_PartXRefMfg_PartNum],
	(Clean_PartXRefMfg.MfgPartNum  + ' - ' + Clean_Part_XRefMfg.Company + ' - ' + Clean_Part_XRefMfg.PartDescription) as [Calculated_Clean_SearchPart],
	[Clean_Part_XRefMfg_PartBin].[Company] as [Clean_Part_XRefMfg_PartBin_Company],
	[Clean_Part_XRefMfg_PartBin].[PartNum] as [Clean_Part_XRefMfg_PartBin_PartNum],
	[Clean_Part_XRefMfg_PartBin].[WarehouseCode] as [Clean_Part_XRefMfg_PartBin_WarehouseCode],
	[Clean_Part_XRefMfg_PartBin].[BinNum] as [Clean_Part_XRefMfg_PartBin_BinNum],
	[Clean_Part_XRefMfg_PartBin].[OnhandQty] as [Clean_Part_XRefMfg_PartBin_OnhandQty]
from Erp.PartXRefMfg as Clean_PartXRefMfg
inner join Erp.Part as Clean_Part_XRefMfg on 
	Clean_PartXRefMfg.Company = Clean_Part_XRefMfg.Company
	and Clean_PartXRefMfg.PartNum = Clean_Part_XRefMfg.PartNum
inner join Erp.PartBin as Clean_Part_XRefMfg_PartBin on 
	Clean_Part_XRefMfg.Company = Clean_Part_XRefMfg_PartBin.Company
	and Clean_Part_XRefMfg.PartNum = Clean_Part_XRefMfg_PartBin.PartNum
union
select 
	[PartXRefVend].[PartNum] as [PartXRefVend_PartNum],
	(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(PartXRefVend.VendPartNum,',',''),'.',''),'/',''),'\',''),'_',''),'-',''),'+',''),' ',''),')',''),'(','') + ' - ' + Part_XRefVend.Company + ' - ' + Part_XRefVend.PartDescription) as [Calculated_SearchPart],
	[Part_XRefVend_PartBin].[Company] as [Part_XRefVend_PartBin_Company],
	[Part_XRefVend_PartBin].[PartNum] as [Part_XRefVend_PartBin_PartNum],
	[Part_XRefVend_PartBin].[WarehouseCode] as [Part_XRefVend_PartBin_WarehouseCode],
	[Part_XRefVend_PartBin].[BinNum] as [Part_XRefVend_PartBin_BinNum],
	[Part_XRefVend_PartBin].[OnhandQty] as [Part_XRefVend_PartBin_OnhandQty]
from Erp.PartXRefVend as PartXRefVend
inner join Erp.Part as Part_XRefVend on 
	PartXRefVend.Company = Part_XRefVend.Company
	and PartXRefVend.PartNum = Part_XRefVend.PartNum
inner join Erp.PartBin as Part_XRefVend_PartBin on 
	Part_XRefVend.Company = Part_XRefVend_PartBin.Company
	and Part_XRefVend.PartNum = Part_XRefVend_PartBin.PartNum
union
select 
	[Clean_PartXRefVend].[PartNum] as [Clean_PartXRefVend_PartNum],
	(Clean_PartXRefVend.VendPartNum + ' - ' + Clean_Part_XRefVend.Company + ' - ' + convert(varchar,Clean_Part_XRefVend.PartDescription)) as [Calculated_Search_Part],
	[Clean_Part_XRefVend_PartBin].[Company] as [Clean_Part_XRefVend_PartBin_Company],
	[Clean_Part_XRefVend_PartBin].[PartNum] as [Clean_Part_XRefVend_PartBin_PartNum],
	[Clean_Part_XRefVend_PartBin].[WarehouseCode] as [Clean_Part_XRefVend_PartBin_WarehouseCode],
	[Clean_Part_XRefVend_PartBin].[BinNum] as [Clean_Part_XRefVend_PartBin_BinNum],
	[Clean_Part_XRefVend_PartBin].[OnhandQty] as [Clean_Part_XRefVend_PartBin_OnhandQty]
from Erp.PartXRefVend as Clean_PartXRefVend
inner join Erp.Part as Clean_Part_XRefVend on 
	Clean_PartXRefVend.Company = Clean_Part_XRefVend.Company
	and Clean_PartXRefVend.PartNum = Clean_Part_XRefVend.PartNum
inner join Erp.PartBin as Clean_Part_XRefVend_PartBin on 
	Clean_Part_XRefVend.Company = Clean_Part_XRefVend_PartBin.Company
	and Clean_Part_XRefVend.PartNum = Clean_Part_XRefVend_PartBin.PartNum

 

 

This BAQ was created in 10.2.700.1. It should import into later versions without issue, however, it may not import into earlier versions.

1 Attachment
M

Thanks Tim, this is great

Reply


Terms & Conditions