My task right now is to create the Time Phased Mtl. Requirements report into a dashboard so that we can filter out different values we do not want to see. Its suprising to see that Time Phase data is not more accessible. I have used the PartDtl table to do most of the dashboard and have used several calculated fields to get the different fields that are not provided (Source, Exception, Balance, Suggested Order Date, etc.) thanks to many folks on this forum.
One of the challenges is that exceptions that occur for On-Hand Quantity do not show because PartDtl does not give information for all parts but only the pending receipts/requirements. What would be the best way to get to this? Do I have to check all parts in our system to see if the OnHand < Minimum qty?
Also, I dont think I am doing the Suggested Order Date correctly. Currently, I am calculating the ReOrderPoint (safety + minimum). If RunningSum (Balance) < ReOrderPoint then DATEADD(day, -PartPlant.LeadTime, PartDtl.DueDate). The problem with this is that LeadTime is business days. How do I subtract production business days. How do I subtract business days from a date?
Possibly,
I used an old V8 tip - 6095MP from EpicWeb to get an idea of what Time Phase was doing behind the scenes. I’m not sure there is an E10 equivalent. Should still be available if you have access to the site.
If you search this forum, I’m pretty sure there are a few examples.
For now… here is an example for comparing PO due/receipt “workdays” ( use at your own risk)
case
when RcvDtl.ReceiptDate > PORel.DueDate then
datediff(dd, PORel.DueDate, RcvDtl.ReceiptDate) - (datediff(wk, PORel.DueDate, RcvDtl.ReceiptDate) * 2) -
case when datepart(dw, PORel.DueDate) = 1 then 1 else 0 end +
case when datepart(dw, RcvDtl.ReceiptDate) = 1 then 1 else 0 end
else 0
end
Thanks for the reply. I do not have access to EpicWeb. Would you mind giving a brief explanation of how it checks OnHand qtys for parts if you have time?
I may be able to get this to account for Saturdays and Sundays, but what about Holidays or if they are currently using a 4/10 calendar? I have seen several posts that use C# code to loop through to a certain date, but how would I do this in a BAQ calculated field?
Also, is it possible to calculate the ‘Projected Balance’? In the report, it is simply the last balance of each PartNum. Is there a way I could do this in calculated field?"
Edit: I am getting an error when using LAST_VALUE in a window function because RunningSum (Balance) is also a window function. - cannot use window function in the context of another window function or aggregate. How do I work around this?
To handle this we added a stored procedure to the database (DBO schema) that calcs business days and we call that from the BAQ
ALTER FUNCTION [dbo].[GetBusinessDays]
(
@startDate DATE,
@endDate DATE
)
RETURNS INT
AS
BEGIN
DECLARE @days INT
SET @days = (SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1) -
(DATEDIFF(wk, @StartDate, @EndDate) * 2) -
(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -
(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
) - 1
RETURN @days
END
