@AbapCatalog.sqlViewName: 'PWCCAPSHIFTOP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE
@VDM: {
private: true
}
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_WorkCenterCapShiftOp
with parameters
P_StartDate: datum,
P_EndDate: datum,
P_HorizonFlag: char1
as select from P_WrkCtrCapOpWithNights(P_StartDate : $parameters.P_StartDate,
P_EndDate : $parameters.P_EndDate) as OperationCapacityPerShift
inner join I_CalendarDate as date_series 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
)
)
cross join P_WorkCenterCapMaxMinDte(p_sapclient:$session.client,P_fromdate : $parameters.P_StartDate,
P_todate: $parameters.P_EndDate,P_HorizonFlag :$parameters.P_HorizonFlag) as OperationsTimeFrame
{
key OperationCapacityPerShift.WorkCenterInternalID as WorkCenterInternalID,
key OperationCapacityPerShift.WorkCenterTypeCode as WorkCenterTypeCode,
key OperationCapacityPerShift.CapacityCategoryAllocation as CapacityCategoryAllocation,
key OperationCapacityPerShift.CapacityInternalID as CapacityInternalID,
//key OperationCapacityPerShift.CapacityRequirement,
//key OperationCapacityPerShift.Material,
//key OperationCapacityPerShift.OrderID as OrderID,
//key OperationCapacityPerShift.Operation as Operation,
key case when OperationCapacityPerShift.NightSplitDate = 2
then DATS_ADD_DAYS( date_series.CalendarDate, -1, 'FAIL')
else
date_series.CalendarDate
end as CalendarDate,
key OperationCapacityPerShift.CapacityActiveVersion,
key OperationCapacityPerShift.AvailableCapacityShift,
key OperationCapacityPerShift.ValidityEndDate,
key OperationCapacityPerShift.WeekDay,
OperationCapacityPerShift.CapacityRequirement,
OperationCapacityPerShift.Material,
OperationCapacityPerShift.OrderID as OrderID,
OperationCapacityPerShift.Operation as Operation,
OperationCapacityPerShift.ShiftName,
OperationCapacityPerShift.Plant,
OperationCapacityPerShift.MRPController,
OperationCapacityPerShift.WorkCenter,
OperationCapacityPerShift.FactoryCalendar,
date_series.CalendarWeek as CalendarWeek,
date_series.CalendarMonth as CalendarMonth,
date_series.CalendarYear as CalendarYear,
OperationCapacityPerShift.WorkCenterResponsible,
// OperationCapacityPerShift.WorkCenterDesc,
OperationCapacityPerShift.WorkCenterCategoryCode,
date_series.CalendarDate as FactoryCalendarDate,
-- 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)
when (OperationCapacityPerShift.CapacityStartTime = OperationCapacityPerShift.CapacityEndTime)
then 0
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 Operation is starting and ending on the same Calendar 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)
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))
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))
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))
else 0
end
else
-- In case Capacity Calendar Date is same as Operation Start date.
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))
else -- in case Operation start time is before Capacity start time then no need to calculate
(OperationCapacityPerShift.TotOperatingDurationInSeconds)
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 OperationLatestEndTime
(cast ( OperationCapacityPerShift.OpLtstSchedldExecEndTme - OperationCapacityPerShift.CapacityStartTime as abap.fltp) /
cast ( OperationCapacityPerShift.CapacityEndTime - OperationCapacityPerShift.CapacityStartTime as abap.fltp) * cast(OperationCapacityPerShift.TotOperatingDurationInSeconds as abap.fltp))
else -- in case Operation end time is after Capacity end time then no need to calculate
(OperationCapacityPerShift.TotOperatingDurationInSeconds)
end
end
else 0
end
end
end
end) as CalCapacityInParallel,
OperationCapacityPerShift.CapacityStartTime,
OperationCapacityPerShift.CapacityEndTime,
case when OperationCapacityPerShift.ValidityBeginDate = date_series.CalendarDate
then 'X' end as isStartDay,
case when OperationCapacityPerShift.ValidityEndDate = date_series.CalendarDate
then 'X' end as isEndDay,
OperationCapacityPerShift.OperatingDurationInSeconds,
OperationCapacityPerShift.TotOperatingDurationInSeconds,
OperationCapacityPerShift.NightSplitDate,
OperationCapacityPerShift.AvailableCapacityIntervalDurn,
//OperationCapacityPerShift.WeekDay,
OperationCapacityPerShift.WorkDayRule,
OperationCapacityPerShift.CapacityRequirementOrigin,
OperationCapacityPerShift.CapacityRequirementUnit,
OperationCapacityPerShift.OrderInternalID,
OperationCapacityPerShift.OrderType,
OperationCapacityPerShift.OrderCategory,
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.CapacityRequirementsAreDtmnd as RequirementHours,
OperationCapacityPerShift.TotRequirementInSeconds,
// OperationCapacityPerShift.OrderStatusText,
OperationCapacityPerShift.OrderStatusCode,
OperationCapacityPerShift.OrderFirmingStatusCode
}
where
date_series.CalendarDate between OperationsTimeFrame.OperationMinimumDate and OperationsTimeFrame.OperationMaximumDate