Usage spike baq

Epicor ERP 10
1

I’m looking for elegant ways to be able to look for usage spikes with a BAQ/Dashboard. We are going to be moving hardware to non-quantity bearing as they are already vendor managed anyways, however, I want to be able to look ahead and try and find when we could have an unusually high usage where we may be caught emptying out a bin before the vendor comes back to fill in the bin.

First thoughts are to simply some usage by a specific date range, now to 1 week, 1 week to 2 weeks, etc. That would work ok, but it’s going to end up being a bunch of columns with mostly 0’s.

What I would ideally like to be able to is to be able to get a list of dates where the usage is going to be above a bin quantity with a certain number of days in a period, say 3 days. They just display the part numbers that hit those thresholds.

I’m wondering if anyone has something snazzy they have done in a similar situation.

2

Interesting question. Alas I have nothing snazzy or at hand. Wouldn’t you want to drive some of this information with data from Part/Site/Detail?

image
image.png1010×757 32.1 KB

It would be a good one for DataDiscovery would it not?

3

Yes, I plan on doing that. I will be getting a quantity on how many fit in a bin and populating that somewhere, whether I hijack a field like max on hand that is no longer useful with non-quantity bearing, or create/convert a new field and throw it on the attributes tab.

In theory, yes it would, or more accurately, will be. I’m excited about the potential for Data Discovery, unfortunately the active home page has too many problems to be adoptable wide scale at this time. I would love it if we could use Data discovery independent of the active home page (@bconner, anything in the works on getting that to happen). I think adoption of the tool would go up ten fold.

For now I have to stick with a regular BAQ and dashboard. I’m hoping to figure out some windowing functions to get some better tools to look for these usage spikes. I think I may have found some things that could work when I was googling around at the end of the day, but I didn’t have time to fully dig into it.

4

Hey @knash or @Rick_Bird

I’m looking at this run a sum based on subsequent rows.

Specifically this part

image
image807×224 5.99 KB

It looks like you can specify the number of rows that you want to use. Instead of using the number of rows, is it possible to use a difference to determine which rows to include? I want to include x number of days from the PartDtl table, so that whenever a usage pops up, it looks ahead from the date on that row, and includes a cumulative sum within X number of days. Then I’m going to filter rows based on that sum being over a threshold number.

If I were to use this, it’s only going to grab x number or rows, which may be very concentrated or spread out date wise, so not the most useful. I would really rather not have to populate a whole sub query with empty dates just to make this work, but if that’s what I have to do, I guess I just have to do it. I’m just wondering if there is a way to make it work.

5

So I figured this out like this.

The first query is everything in the PartDtl table, only displaying the dates, then group by date, and culculated Row_number field. This gives me a sequential number to use for date ranges that dynamically follows the calendar. It’s possible that nothing is due on a working day, but highly unlikely in our business case.

The next query in the partdtl table joined to my row number table by due date. I sum everything by day on this table. I also add a calculated field for the vendor supply days. In my example, I just used 3, but I will be adding a field in the part master for vendor managed days of supply so that each part will have it’s own.

The top level, I bring in the 2nd query twice, and join it with a range. This gives me the duplicate rows that I need to sum up everything within the range. So I sum the quantity from the 2nd table and group by the information on the first table. This effectively gives me a 3 day usage sum for any single day.

In addition to the vendor days of supply I will also be adding a bin qty so I have a threshold to know when to display this row and when to hide it.

select 
	[DailySum].[PartDtl1_PartNum] as [PartDtl1_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[DailySum].[Calculated_RowNum] as [Calculated_RowNum],
	[DailySum].[PartDtl1_DueDate] as [PartDtl1_DueDate],
	(sum(DailySum1.Calculated_DailySum)) as [Calculated_threedaysum],
	[DailySum].[Calculated_DailySum] as [Calculated_DailySum]
from  (select 
	[PartDtl1].[Company] as [PartDtl1_Company],
	[PartDtl1].[PartNum] as [PartDtl1_PartNum],
	[PartDtl1].[DueDate] as [PartDtl1_DueDate],
	[DateTable].[Calculated_DateRange] as [Calculated_DateRange],
	[DateTable].[Calculated_RowNum] as [Calculated_RowNum],
	(sum(PartDtl1.Quantity )) as [Calculated_DailySum],
	(DateTable.Calculated_RowNum + 3) as [Calculated_DaysOfSupply]
from Erp.PartDtl as PartDtl1
inner join  (select 
	[PartDtl].[Company] as [PartDtl_Company],
	[PartDtl].[DueDate] as [PartDtl_DueDate],
	(Row_Number () over (order by PartDtl.DueDate)) as [Calculated_RowNum],
	(RowNum + 3) as [Calculated_DateRange]
from Erp.PartDtl as PartDtl
group by [PartDtl].[Company],
	[PartDtl].[DueDate])  as DateTable on 
	PartDtl1.Company = DateTable.PartDtl_Company
	and PartDtl1.DueDate = DateTable.PartDtl_DueDate
group by [PartDtl1].[Company],
	[PartDtl1].[PartNum],
	[PartDtl1].[DueDate],
	[DateTable].[Calculated_DateRange],
	[DateTable].[Calculated_RowNum])  as DailySum
inner join  (select 
	[PartDtl1].[Company] as [PartDtl1_Company],
	[PartDtl1].[PartNum] as [PartDtl1_PartNum],
	[PartDtl1].[DueDate] as [PartDtl1_DueDate],
	[DateTable].[Calculated_DateRange] as [Calculated_DateRange],
	[DateTable].[Calculated_RowNum] as [Calculated_RowNum],
	(sum(PartDtl1.Quantity )) as [Calculated_DailySum],
	(DateTable.Calculated_RowNum + 3) as [Calculated_DaysOfSupply]
from Erp.PartDtl as PartDtl1
inner join  (select 
	[PartDtl].[Company] as [PartDtl_Company],
	[PartDtl].[DueDate] as [PartDtl_DueDate],
	(Row_Number () over (order by PartDtl.DueDate)) as [Calculated_RowNum],
	(RowNum + 3) as [Calculated_DateRange]
from Erp.PartDtl as PartDtl
group by [PartDtl].[Company],
	[PartDtl].[DueDate])  as DateTable on 
	PartDtl1.Company = DateTable.PartDtl_Company
	and PartDtl1.DueDate = DateTable.PartDtl_DueDate
group by [PartDtl1].[Company],
	[PartDtl1].[PartNum],
	[PartDtl1].[DueDate],
	[DateTable].[Calculated_DateRange],
	[DateTable].[Calculated_RowNum])  as DailySum1 on 
	DailySum.PartDtl1_PartNum = DailySum1.PartDtl1_PartNum
	and DailySum.Calculated_RowNum <= DailySum1.Calculated_RowNum
	and DailySum.Calculated_DateRange >= DailySum1.Calculated_RowNum
inner join Erp.Part as Part on 
	DailySum.PartDtl1_Company = Part.Company
	and DailySum.PartDtl1_PartNum = Part.PartNum
	and ( Part.TypeCode = 'P'  )

group by [DailySum].[PartDtl1_PartNum],
	[Part].[PartDescription],
	[DailySum].[Calculated_RowNum],
	[DailySum].[PartDtl1_DueDate],
	[DailySum].[Calculated_DailySum]
3 Likes
6

Wow, this is a very impressive query.

2 Likes
7

How are you going to track usage if its non-quantity bearing ?

8

non-quanity bearing still shows up in the time-phase, so there is a requirement for that part number. They also still get issued, so you have a usage. Occasional review of PO receipts to usage on jobs should be done to make sure things aren’t way out of whack, but converting the parts to non-quantity bearing allows for a certain amount of leeway with these low value part numbers. I’m not using the inventory values, because the cost (work) to keep the numbers accurate isn’t worth the benefit.

1 Like
9

so you are keeping them on the BOM or as sales order lines?

10

They are on the BOM. Our jobs are large industrial conveyor systems. They get partially assembled in our factory then trucked to the customers and installed/assembled on site. The whole conveyor and all of the parts/hardware is on one job.

11

What I’ve done in the past is treated them as expense items, not tracked at all. buried the cost in the burden rate and everyone was happy.
They werent bought with a PO. just a payment entry to an expense account.

Ops didnt have to manage them (vendor managed)
accounting got to capture cost.

12

I’m basically a half a step to what you did with expense items. However, we have requirements for packing lists and standards that still require the parts to be on the jobs. In my opinion the way burden is calculated isn’t right and I want the cost of the parts to go to the job. A lot of that depends on your business model though. For us, I think this is a good mix of both worlds.

Also, we need to be able to know if we are going to run out in extreme situations. Hence this dashboard.

13

That’s very impressive query there

2 Likes
14

Nice, and great contribution. @Banderson

15

Here is a query I sent to @Banderson to help him with this issue. He didn’t like it so he created his own. :wink:

This figures out the day of the week and then add three working days. It assumes that there are no holidays, and sat and sun are never working days. Not sexy but gets the job done.

select FinalQuery.PartDtl1_Company, 
FinalQuery.PartDtl1_PartNum, 
FinalQuery.PartDtl1_DueDate, 
sum(FinalQuery.PartDtl1_Quantity) as [One Day Part Sum], 
AVG( FinalQuery.[Three Day Part Sum]) as [Three Day Part Sum]
from (select 
  [pdMain].[Company] as [PartDtl1_Company],
  [pdMain].[PartNum] as [PartDtl1_PartNum],
  [pdMain].[DueDate] as [PartDtl1_DueDate],
  [pdMain].[Quantity] as [PartDtl1_Quantity],
  (select sum(PartDtl1.Quantity) 
    from  Erp.PartDtl as PartDtl1
    Where  [PartDtl1].[DueDate] Between [pdMain].[DueDate] and  
            (case datename(dw, [pdMain].[DueDate])
              when 'Wednesday' then DateAdd(d, 5, [pdMain].[DueDate])
              when 'Thursday' then DateAdd(d, 5, [pdMain].[DueDate])
              when 'Friday' then DateAdd(d, 5, [pdMain].[DueDate])
              Else DateAdd(d, 3, [pdMain].[DueDate])
            end)
            and  [PartDtl1].[PartNum] = [pdMain].[PartNum]
            and  [PartDtl1].[Company] = [pdMain].[Company]            
    group by [PartDtl1].[Company], [PartDtl1].[PartNum] 
  ) as [Three Day Part Sum]
from Erp.PartDtl as pdMain) as FinalQuery
group by FinalQuery.PartDtl1_Company, PartDtl1_PartNum, PartDtl1_DueDate
2 Likes
16

yeah, that definitely got me on the right track to get what I got to.