@AbapCatalog.sqlViewName: 'PWRKCTRCAPEVAL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
//View with workdays and holidays ... Union holiday view
define view P_WORKCENTERCAPACITYEVAL
with parameters
P_StartDate : datum,
P_EndDate : datum
as select from I_CalendarDate as Date_series
left outer join P_WrkCtrShiftWithReq(P_StartDate:$parameters.P_StartDate,P_EndDate:$parameters.P_EndDate) as OperationCapacityPerShift
on Date_series.CalendarDate <= OperationCapacityPerShift.ValidityEndDate
and Date_series.CalendarDate >= OperationCapacityPerShift.ValidityBeginDate
and (
(
OperationCapacityPerShift.AvailableCapacityIntervalDurn = '01'
) // 1 day shift interval duration
or
(
OperationCapacityPerShift.AvailableCapacityIntervalDurn = '07'
and // 7 day shift interval duration
Date_series.WeekDay = OperationCapacityPerShift.WeekDay
)
)
left outer join P_WorkCenterOpDistrPct(P_StartDate :$parameters.P_StartDate, P_EndDate:$parameters.P_EndDate) as Distrperc
on OperationCapacityPerShift.WorkCenter = Distrperc.WorkCenter
and OperationCapacityPerShift.CapacityInternalID = Distrperc.CapacityInternalID
and OperationCapacityPerShift.CapacityRequirement = Distrperc.CapacityRequirement
and OperationCapacityPerShift.Operation = Distrperc.Operation
// as select from P_WorkCenterCapShiftOp(P_fromdate : $parameters.P_StartDate,
// P_todate : $parameters.P_EndDate) as OperationCapacityPerShift
//
// inner join P_WorkCenterOpDistrpercPct (P_StartDate : $parameters.P_StartDate,
// P_EndDate : $parameters.P_EndDate) as OperationDistrpercPct
// on OperationDistrpercPct.WorkCenter = OperationCapacityPerShift.WorkCenter
// and OperationDistrpercPct.CapacityInternalID = OperationCapacityPerShift.CapacityInternalID
// and OperationDistrpercPct.CapacityRequirement = OperationCapacityPerShift.CapacityRequirement
{
key OperationCapacityPerShift.WorkCenterInternalID,
key OperationCapacityPerShift.WorkCenterTypeCode,
key OperationCapacityPerShift.CapacityCategoryAllocation,
key OperationCapacityPerShift.CapacityInternalID as CapacityInternalID,
key OperationCapacityPerShift.ShiftName,
key case when OperationCapacityPerShift.NightSplitDate = 2
then DATS_ADD_DAYS( Date_series.CalendarDate, -1, 'FAIL')
else
Date_series.CalendarDate
end as CalendarDate,
OperationCapacityPerShift.AvailableCapacityShift,
Date_series.CalendarWeek as CalendarWeek,
Date_series.CalendarMonth as CalendarMonth,
Date_series.CalendarYear as CalendarYear,
OperationCapacityPerShift.Operation,
OperationCapacityPerShift.FactoryCalendar,
OperationCapacityPerShift.Plant,
OperationCapacityPerShift.MRPController,
OperationCapacityPerShift.WorkCenter,
OperationCapacityPerShift.WorkDayRule as WorkDayRule,
OperationCapacityPerShift.WorkCenterResponsible,
// OperationCapacityPerShift.WorkCenterDesc,
OperationCapacityPerShift.WorkCenterCategoryCode,
OperationCapacityPerShift.CapacityRequirement,
OperationCapacityPerShift.CapacityRequirementUnit,
OperationCapacityPerShift.CapacityEndTime,
OperationCapacityPerShift.CapacityRequirementOrigin,
OperationCapacityPerShift.OrderInternalID,
OperationCapacityPerShift.OrderType,
// OperationCapacityPerShift.OrderTypeName,
OperationCapacityPerShift.ProductionVersion,
OperationCapacityPerShift.OrderPlannedTotalQty,
OperationCapacityPerShift.BaseUnit,
// OperationCapacityPerShift.OperationPlanningStatusText,
OperationCapacityPerShift.OperationPlanningStatusCode,
OperationCapacityPerShift.OperationLatestStartDate,
OperationCapacityPerShift.OperationLatestStartTime,
OperationCapacityPerShift.OperationLatestEndDate,
OperationCapacityPerShift.OperationLatestEndTime,
OperationCapacityPerShift.OpLtstSchedldExecStrtTme,
OperationCapacityPerShift.OpLtstSchedldExecEndTme,
OperationCapacityPerShift.OpLtstSchedldProcgStrtDte,
OperationCapacityPerShift.OpLtstSchedldProcgStrtTme,
OperationCapacityPerShift.OpLtstSchedldTrdwnStrtDte,
OperationCapacityPerShift.OpLtstSchedldTrdwnStrtTme,
OperationCapacityPerShift.RemainingCapReqOpSegSetupDurn,
OperationCapacityPerShift.RemainingCapReqOpSegProcgDurn,
OperationCapacityPerShift.RemainingCapReqOpSegTrdwnDurn,
// OperationCapacityPerShift.OrderStatusText,
OperationCapacityPerShift.OrderStatusCode,
OperationCapacityPerShift.CapacityRequirementsAreDtmnd as RequirementHours,
OperationCapacityPerShift.CapacityStartTime as CapacityStartTime,
cast ('H' as meins) as WorkCenterCapacityUnit,
// 'Hours' as WorkCenterCapUnitText,
(OperationCapacityPerShift.TotOperatingDurationInSeconds) / cast(3600 as abap.fltp) as WorkCenterAvailableCapacity,
// (cast(OperationCapacityPerShift.CalCapacityInParallel as abap.fltp) / cast(3600 as abap.fltp))
// * OperationDistrpercPct.CapacityCapacityDistrPercent / cast(100 as abap.fltp) as WorkCenterCapacityRequirement
-- if the capacity date is not on the start date and not on the end date of the operation, it must be between these dates
-- on these days, the operation fully spans the shift and hence always consumes te entire shift capacity
(case
when OperationCapacityPerShift.OrderCategory = '30'
then 0
when ( Date_series.CalendarDate > OperationCapacityPerShift.OperationLatestStartDate and
Date_series.CalendarDate < OperationCapacityPerShift.OperationLatestEndDate ) -- capacity on fully covered days
then (OperationCapacityPerShift.TotOperatingDurationInSeconds / cast(3600 as abap.fltp))*Distrperc.CapacityDistrPercent / cast(100 as abap.fltp)
else
-- If the operation ends after the capacity starts, we calculate the available capacity
-- Example:
-- Capacity 09:00 to 17:00 with 1 hour break duration. Operation end 16:00, start on another day
-- OperatingDurationInHours is 17 - 9 - 1 = 7 hours (in seconds)
-- This calculation determines the capacity in this shift available for the operation by
-- ( 57.600 (OperationEndTime) - 32.400 (CapacityStartTime) ) / ( 61.200 (CapacityEndTime) - 32.400 (CapacityStartTime) ) * 25.200 (OperatingDurationInHours)
-- = 25.200 (Opening Hours in parallel to operation in this shift) / 28.800 (total opening hours of this shift) * 25.200 (Single resource capacity of the shift)
-- = 22.050 (Single resource capacity of the shift available to the operation)
-- In case Capacity Calendar Date is same as Operation Start date.
case
when (Date_series.CalendarDate = OperationCapacityPerShift.OperationLatestStartDate and Date_series.CalendarDate = OperationCapacityPerShift.OperationLatestEndDate)
then
case when OperationCapacityPerShift.OpLtstSchedldExecStrtTme = OperationCapacityPerShift.OpLtstSchedldExecEndTme or
(OperationCapacityPerShift.OpLtstSchedldExecStrtTme <= OperationCapacityPerShift.CapacityStartTime and OperationCapacityPerShift.OpLtstSchedldExecEndTme >= OperationCapacityPerShift.CapacityEndTime )
then (OperationCapacityPerShift.TotOperatingDurationInSeconds *Distrperc.CapacityDistrPercent / cast(100 *3600 as abap.fltp))
when ( OperationCapacityPerShift.OpLtstSchedldExecStrtTme >= OperationCapacityPerShift.CapacityStartTime and OperationCapacityPerShift.OpLtstSchedldExecEndTme <= OperationCapacityPerShift.CapacityEndTime )
then (cast( OperationCapacityPerShift.OpLtstSchedldExecEndTme - OperationCapacityPerShift.OpLtstSchedldExecStrtTme as abap.fltp) /
cast( OperationCapacityPerShift.CapacityEndTime - OperationCapacityPerShift.CapacityStartTime as abap.fltp) * cast(OperationCapacityPerShift.TotOperatingDurationInSeconds as abap.fltp))
*Distrperc.CapacityDistrPercent / cast(100 * 3600 as abap.fltp)
when ( OperationCapacityPerShift.OpLtstSchedldExecStrtTme > OperationCapacityPerShift.CapacityStartTime and OperationCapacityPerShift.OpLtstSchedldExecStrtTme < OperationCapacityPerShift.CapacityEndTime)
then (cast( OperationCapacityPerShift.CapacityEndTime - OperationCapacityPerShift.OpLtstSchedldExecStrtTme as abap.fltp) /
cast( OperationCapacityPerShift.CapacityEndTime - OperationCapacityPerShift.CapacityStartTime as abap.fltp) * cast(OperationCapacityPerShift.TotOperatingDurationInSeconds as abap.fltp))
*Distrperc.CapacityDistrPercent / cast(100 * 3600 as abap.fltp)
when ( OperationCapacityPerShift.OpLtstSchedldExecEndTme > OperationCapacityPerShift.CapacityStartTime and OperationCapacityPerShift.OpLtstSchedldExecEndTme < OperationCapacityPerShift.CapacityEndTime)
then (cast ( OperationCapacityPerShift.OpLtstSchedldExecEndTme - OperationCapacityPerShift.CapacityStartTime as abap.fltp) /
cast ( OperationCapacityPerShift.CapacityEndTime - OperationCapacityPerShift.CapacityStartTime as abap.fltp) * cast(OperationCapacityPerShift.TotOperatingDurationInSeconds as abap.fltp))
*Distrperc.CapacityDistrPercent / cast(100 * 3600 as abap.fltp)
else 0
end
else
case
when Date_series.CalendarDate = OperationCapacityPerShift.OperationLatestStartDate
then
case -- check if Capacity is already been ended before Operation start date then there is no capacity consider 0
when OperationCapacityPerShift.OpLtstSchedldExecStrtTme >= OperationCapacityPerShift.CapacityEndTime
then 0
else -- in case Capacity falls under Operation start date
case
when ( OperationCapacityPerShift.OpLtstSchedldExecStrtTme > OperationCapacityPerShift.CapacityStartTime )
then -- in case Capacity start time is before Operation planned start time, then calaculate the use subtract the OpLtstSchedldExecStrtTme
(cast( OperationCapacityPerShift.CapacityEndTime - OperationCapacityPerShift.OpLtstSchedldExecStrtTme as abap.fltp) /
cast( OperationCapacityPerShift.CapacityEndTime - OperationCapacityPerShift.CapacityStartTime as abap.fltp) * cast(OperationCapacityPerShift.TotOperatingDurationInSeconds as abap.fltp))
*Distrperc.CapacityDistrPercent / cast(100 * 3600 as abap.fltp)
else -- in case Operation start time is before Capacity start time then no need to calculate
(OperationCapacityPerShift.TotOperatingDurationInSeconds)*Distrperc.CapacityDistrPercent / cast(100 * 3600 as abap.fltp)
end
end
-- In case Capacity Calendar Date is same as Operation End date.
else
case
when Date_series.CalendarDate = OperationCapacityPerShift.OperationLatestEndDate
then
case
when (OperationCapacityPerShift.OpLtstSchedldExecEndTme <= OperationCapacityPerShift.CapacityStartTime)
then 0
else
case
when OperationCapacityPerShift.OpLtstSchedldExecEndTme < OperationCapacityPerShift.CapacityEndTime
then -- in case Capacity end time is after Operation planned end time, then subtract from PlannedEndTime
(cast ( OperationCapacityPerShift.OpLtstSchedldExecEndTme - OperationCapacityPerShift.CapacityStartTime as abap.fltp) /
cast ( OperationCapacityPerShift.CapacityEndTime - OperationCapacityPerShift.CapacityStartTime as abap.fltp) * cast(OperationCapacityPerShift.TotOperatingDurationInSeconds as abap.fltp))
*Distrperc.CapacityDistrPercent / cast(100 * 3600 as abap.fltp)
else -- in case Operation end time is after Capacity end time then no need to calculate
(OperationCapacityPerShift.TotOperatingDurationInSeconds)*Distrperc.CapacityDistrPercent / cast(100 * 3600 as abap.fltp)
end
end
else 0
end
end
end
end) as WorkCenterCapacityRequirement,
case
when OperationCapacityPerShift.OrderCategory = '30'
then case
when ( Date_series.CalendarDate > OperationCapacityPerShift.OperationLatestStartDate and
Date_series.CalendarDate < OperationCapacityPerShift.OperationLatestEndDate )
or
((Date_series.CalendarDate = OperationCapacityPerShift.OperationLatestStartDate and
Date_series.CalendarDate = OperationCapacityPerShift.OperationLatestEndDate) and
(OperationCapacityPerShift.OpLtstSchedldExecStrtTme < OperationCapacityPerShift.CapacityEndTime and
OperationCapacityPerShift.OpLtstSchedldExecEndTme > OperationCapacityPerShift.CapacityStartTime ))
or
( Date_series.CalendarDate = OperationCapacityPerShift.OperationLatestStartDate and
OperationCapacityPerShift.CapacityEndTime > OperationCapacityPerShift.OpLtstSchedldExecStrtTme )
or
( Date_series.CalendarDate = OperationCapacityPerShift.OperationLatestEndDate and
OperationCapacityPerShift.CapacityStartTime < OperationCapacityPerShift.OpLtstSchedldExecEndTme )
then 'X'
else ''
end
else ''
end as IsMaintained
}
where Date_series.CalendarDate between $parameters.P_StartDate and DATS_ADD_DAYS($parameters.P_EndDate, 1, 'FAIL')