Part demand issues

Epicor ERP 10
, , , , ,
1

Hi,
I have created a dashboard that gives end users a job summary. I have created two calculated fields in my baq to add up the total quantity on hand and the total demand for the parts listed on the job material. I am seeing an issue with the quantity on hand and the total demand doubling up, but it is not doing so for all the parts. See screenshots below:

Dashboard
image
Part Tracker
image

I tried running the part allocation and demand refresh but it didn’t help. Has anyone seen something like this before? Any help on how I can fix this would be appreciated.

2

There could be something in your query causing duplicate rows that go in to your calculations

2 Likes
3

Like @Asz0ka said, there’s probably some join that is causing a duplicate value in your query, which is doubling your aggregate SUM function. Could you share a screencap of the BAQ, and maybe the SQL code on the front page of the query?

2 Likes
4

This is from the Phrase Build

image
image1025×467 28.6 KB

The SQL query

select
[JobHead].[ProjectID] as [JobHead_ProjectID],
[JobProd].[OrderNum] as [JobProd_OrderNum],
[JobHead].[JobNum] as [JobHead_JobNum],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
[JobMtl].[MtlSeq] as [JobMtl_MtlSeq],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[Description] as [JobMtl_Description],
[Part].[PUM] as [Part_PUM],
[JobMtl].[RequiredQty] as [JobMtl_RequiredQty],
[JobMtl].[IssuedQty] as [JobMtl_IssuedQty],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHand],
(sum( PartWhse.DemandQty )) as [Calculated_Demand],
[Part].[TypeCode] as [Part_TypeCode],
(Min(PODetail.PONUM)) as [Calculated_PONum],
(Min(PORel.RelQty)) as [Calculated_POQty],
(Min(PORel.Duedate)) as [Calculated_PODate],
(PartCost.StdBurdenCost + PartCost.StdLaborCost + PartCost.StdMaterialCost + PartCost.StdMtlBurCost + PartCost.StdSubContCost) as [Calculated_StdCost],
[PartCost].[StdBurdenCost] as [PartCost_StdBurdenCost],
[PartCost].[StdLaborCost] as [PartCost_StdLaborCost],
[PartCost].[StdMaterialCost] as [PartCost_StdMaterialCost],
[PartCost].[StdMtlBurCost] as [PartCost_StdMtlBurCost],
[PartCost].[StdSubContCost] as [PartCost_StdSubContCost],
[PurAgent].[Name] as [PurAgent_Name]
from Erp.Company as Company
inner join Erp.JobHead as JobHead on
Company.Company = JobHead.Company
and ( JobHead.Plant = ‘’ )

inner join Erp.JobMtl as JobMtl on
JobHead.Company = JobMtl.Company
and JobHead.JobNum = JobMtl.JobNum
inner join Erp.Part as Part on
JobMtl.Company = Part.Company
and JobMtl.PartNum = Part.PartNum
left outer join Erp.PartWhse as PartWhse on
Part.Company = PartWhse.Company
and Part.PartNum = PartWhse.PartNum
inner join Erp.Warehse as Warehse on
PartWhse.Company = Warehse.Company
and PartWhse.WarehouseCode = Warehse.WarehouseCode
left outer join Erp.PODetail as PODetail on
Part.Company = PODetail.Company
and Part.PartNum = PODetail.PartNum
and ( PODetail.OpenLine = True )

left outer join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
and ( PORel.OpenRelease = True and PORel.Plant = ‘’ )

inner join Erp.PartPlant as PartPlant on
Part.Company = PartPlant.Company
and Part.PartNum = PartPlant.PartNum
and ( PartPlant.Plant = ‘’ )

inner join Erp.PartCost as PartCost on
Part.Company = PartCost.Company
and Part.PartNum = PartCost.PartNum
inner join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
and Part.ClassID = PartClass.ClassID
inner join Erp.PurAgent as PurAgent on
PartClass.Company = PurAgent.Company
and PartClass.BuyerID = PurAgent.BuyerID
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
group by [JobHead].[ProjectID],
[JobProd].[OrderNum],
[JobHead].[JobNum],
[JobMtl].[AssemblySeq],
[JobMtl].[MtlSeq],
[JobHead].[PartNum],
[JobMtl].[PartNum],
[JobMtl].[Description],
[Part].[PUM],
[JobMtl].[RequiredQty],
[JobMtl].[IssuedQty],
[Part].[TypeCode],
[PartCost].[StdBurdenCost],
[PartCost].[StdLaborCost],
[PartCost].[StdMaterialCost],
[PartCost].[StdMtlBurCost],
[PartCost].[StdSubContCost],
[PurAgent].[Name]

5

If something in the query is causing duplication, can you think of a reason why it won’t affect all the parts alike?

1 Like
6

Could be a number of things, multiple cost groups, part wharehouses, etc.

You could just try setting that base query to “distinct” and see if that fixes it

image

2 Likes
7

It could be that whatever join is causing the duplication has only one row for some parts. For example, you have Part and PartCost joined on the PartNum. If some parts have mutliple CostIDs, then those would duplicate (and double) but Parts with only 1 costID would not.

1 Like
8

I would say your best bet for troubleshooting would be to make a copy of your query, and delete some tables that might have a One-To-Many relationship one at a time, with testing in between. I’m not sure about the relationship between PartClass and PurAgent, but I’d probably try that one, PartWhse & Warehse, and PartCost to see if removing any of those eliminate the issue.

3 Likes
9

Thank you. I will try doing that.

2 Likes
10

So, I tried multiple things with my BAQ removing and adding some of the tables you mentioned. It did not work. Then I rebuilt the exact same query in the Test environment and the doubling up issue is gone. I have the same query in the Live environment but the numbers double up. I can’t figure out why though? I checked the cost ID, there is only one. And the strange thing is that the parts that are showing double the demand and quantity are only set up in one warehouse. There are other parts that are set up and have demand + qty spread across multiple warehouses, but they add up just fine.