@AbapCatalog.sqlViewName: 'PWCSHIFTLOADDIST'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE
@VDM: {
private: true
}
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_WrkCtrOpShiftLoadDistr with parameters
P_StartDate : datum,
P_EndDate : datum
as select from P_WrkCtrCapOpWrkDays(P_StartDate : $parameters.P_StartDate,
P_EndDate : $parameters.P_EndDate,
P_HorizonFlag : 'Y') as OperationCapacityPerShift
inner join P_WorkCenterOpDistrPct (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
and OperationDistrpercPct.Operation = OperationCapacityPerShift.Operation
{
key OperationCapacityPerShift.WorkCenterInternalID as WorkCenterInternalID,
key OperationCapacityPerShift.WorkCenterTypeCode,
key OperationCapacityPerShift.CapacityCategoryAllocation,
key OperationCapacityPerShift.CapacityInternalID as CapacityInternalID,
/*key OperationCapacityPerShift.Material,
key OperationCapacityPerShift.OrderID,
key OperationCapacityPerShift.Operation,*/
key OperationCapacityPerShift.CapacityActiveVersion,
key OperationCapacityPerShift.AvailableCapacityShift,
key OperationCapacityPerShift.WeekDay,
key OperationCapacityPerShift.ValidityEndDate,
key OperationCapacityPerShift.CalendarDate as CalendarDate,
//key OperationCapacityPerShift.ShiftName,
OperationCapacityPerShift.Material,
OperationCapacityPerShift.OrderID,
OperationCapacityPerShift.Operation,
OperationCapacityPerShift.ShiftName,
OperationCapacityPerShift.Plant,
OperationCapacityPerShift.MRPController,
OperationCapacityPerShift.WorkCenter,
OperationCapacityPerShift.FactoryCalendar,
OperationCapacityPerShift.CalendarWeek as CalendarWeek,
OperationCapacityPerShift.CalendarMonth as CalendarMonth,
OperationCapacityPerShift.CalendarYear as CalendarYear,
OperationCapacityPerShift.FactoryCalendarDate,
OperationCapacityPerShift.CapacityRequirement, //key
// fltp_to_dec(OperationCapacityPerShift.OrderExecutionShiftStartTime as abap.dec(10,2)) as OrderExecutionShiftStartTime,
// fltp_to_dec(OperationCapacityPerShift.OrderExecutionShiftEndTime as abap.dec(10,2)) as OrderExecutionShiftEndTime,
fltp_to_dec(OperationCapacityPerShift.CapacityStartTime as abap.dec(10,2))as CapacityStartTime,
fltp_to_dec(OperationCapacityPerShift.CapacityEndTime as abap.dec(10,2)) as CapacityEndTime,
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.RequirementHours,
// OperationCapacityPerShift.OrderStatusText ,
OperationCapacityPerShift.OrderStatusCode,
OperationCapacityPerShift.OrderFirmingStatusCode,
cast ('H' as meins) as WorkCenterCapacityUnit,
// 'Hours' as WorkCenterCapUnitText,
OperationCapacityPerShift.WorkCenterResponsible,
// OperationCapacityPerShift.WorkCenterDesc,
OperationCapacityPerShift.WorkCenterCategoryCode,
(OperationCapacityPerShift.TotOperatingDurationInSeconds) / cast(3600 as abap.fltp) as WorkCenterAvailableCapacity,
(cast(OperationCapacityPerShift.CalCapacityInParallel as abap.fltp) / cast(3600 as abap.fltp))
* OperationDistrpercPct.CapacityDistrPercent / cast(100 as abap.fltp) as WorkCenterCapacityRequirement
}
where OperationCapacityPerShift.CalendarDate between $parameters.P_StartDate and $parameters.P_EndDate
// as select from P_WrkCtrCapOpWithNights(P_StartDate : $parameters.P_StartDate,
// P_EndDate : $parameters.P_EndDate,
// P_Horizon_Dates:'N')
// as OperationCapacityPerShift
// left outer join P_WrkCtrAllWrkDayTableFunction(p_sapclient : $session.client,
// P_fromdate : $parameters.P_StartDate,
// P_todate : $parameters.P_EndDate,
// P_HorizonDates : 'Y') as WorkDays
// on WorkDays.CapacityInternalID = OperationCapacityPerShift.CapacityInternalID
// and WorkDays.CalendarDate <= OperationCapacityPerShift.ValidityEndDate
// and WorkDays.CalendarDate >= OperationCapacityPerShift.ValidityBeginDate
// and (
// (
// OperationCapacityPerShift.WorkDayRule = ''
// and // we are only interested in workdays
// WorkDays.WorkDays = 1
// )
// or
// (
// OperationCapacityPerShift.WorkDayRule = '1'
// )
// )
// and (
// (
// OperationCapacityPerShift.AvailableCapacityIntervalDurn = '01'
// ) // 1 day shift interval duration
// or(
// OperationCapacityPerShift.AvailableCapacityIntervalDurn = '07'
// and // 7 day shift interval duration
// WorkDays.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
// case
// when WorkDays.WorkDays = 0 and OperationCapacityPerShift.WorkDayRule = '' or
// OperationCapacityPerShift.WorkDayRule = '0' or
// OperationCapacityPerShift.CapacityEndTime = OperationCapacityPerShift.CapacityStartTime or
// OperationCapacityPerShift.OperatingDurationInSeconds = 0
// then 0
// else
// -- 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 ( WorkDays.CalendarDate > OperationCapacityPerShift.OperationLatestStartDate and
// WorkDays.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 (WorkDays.CalendarDate = OperationCapacityPerShift.OperationLatestStartDate and WorkDays.CalendarDate = OperationCapacityPerShift.OperationLatestEndDate)
// then
// case when (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 WorkDays.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 WorkDays.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)
// end as WorkCenterCapacityRequirement
//}
//where
// CalendarDate between $parameters.P_StartDate and $parameters.P_EndDate