@AbapCatalog.sqlViewName: 'PWCCAPSHFTCALC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
//@EndUserText.label: 'Work Center Capacity Shift Calculations'
define view P_WorkCenterCapacityShiftCalc
as select from I_WorkCenterCapacityInterval_2 as _Interval
inner join P_CapacityHeader as _Header on _Header.CapacityInternalID = _Interval.CapacityInternalID
// shift exists
left outer join P_WrkCtrAvailableCapShift as _Shift on _Shift.CapacityInternalID = _Interval.CapacityInternalID
and _Shift.ValidityEndDate = _Interval.IntervalEndDate
and _Shift.AvailableCapacityType = _Interval.CapacityActiveVersion
and _Interval.StdAvailableCapacityIsValid = ''
// shift definition exists
left outer join P_WorkCenterShiftDefinition as _ShiftDef on _Shift.ShiftDefinition = _ShiftDef.ShiftDefinition
and _Shift.ShiftGroup = _ShiftDef.ShiftGrouping
and _Interval.IntervalEndDate >= _ShiftDef.ShiftStartDate // interval and shift definition must overlap
and _Interval.IntervalEndDate <= _ShiftDef.ShiftEndDate
and _Interval.IntervalStartDate >= _ShiftDef.ShiftStartDate
and _Interval.IntervalStartDate <= _ShiftDef.ShiftEndDate
{
//interval
key _Interval.CapacityInternalID,
key _Interval.CapacityActiveVersion,
key case when (_ShiftDef.ShiftDefinition is not null)
then case when _ShiftDef.ShiftEndDate < _Interval.IntervalEndDate
then _ShiftDef.ShiftEndDate
else _Interval.IntervalEndDate
end
else coalesce( _Interval.IntervalEndDate, CapacityEndDate )
end as ValidityEndDate,
key coalesce(_Shift.WeekDay,'1') as WeekDay,
key _Shift.AvailableCapacityShift as AvailableCapacityShift,
// adjust validity dates if shift definition exists
case when (_ShiftDef.ShiftDefinition is not null)
then case when _ShiftDef.ShiftStartDate > _Interval.IntervalStartDate
then _ShiftDef.ShiftStartDate
else _Interval.IntervalStartDate
end
else coalesce( _Interval.IntervalStartDate, CapacityStartDate )
end as ValidityStartDate,
_Shift.WorkDayRule,
_Shift.ShiftDefinition,
_Header.ShiftGroup,
case when _Interval.StdAvailableCapacityIsValid = 'X'
then _Interval.CapacityNumberOfCapacities
else
_Shift.CapacityNumberOfCapacities end as CapacityNumberOfCapacities,
case when _Interval.StdAvailableCapacityIsValid = 'X'
then _Interval.CapacityPlanUtilizationPercent
else
_Shift.CapacityPlanUtilizationPercent end as CapacityPlanUtilizationPercent,
case when (_ShiftDef.ShiftDefinition is not null)
then _ShiftDef.ShiftStartTime
else
cast ( substring( cast (
tstmp_add_seconds(
cast ('10010102000000' as abap.dec(15,0)),
cast( coalesce( _Shift.CapacityStartTimeID, _Interval._Capacity.CapacityStartTime ) as abap.dec(15,0) ),
'FAIL') as abap.sstring(50)),
9,6) as abap.tims)
end as ShiftStartTime,
case when (_ShiftDef.ShiftDefinition is not null)
then _ShiftDef.ShiftEndTime
else
cast ( substring( cast (
tstmp_add_seconds(
cast ('10010102000000' as abap.dec(15,0)),
cast( coalesce( _Shift.CapacityEndTimeID, _Interval._Capacity.CapacityEndTime ) as abap.dec(15,0) ),
'FAIL') as abap.sstring(50)),
9,6) as abap.tims)
end as ShiftEndTime,
case when (_ShiftDef.ShiftDefinition is not null)
then _ShiftDef.ShiftStartDate
else coalesce( _Interval.IntervalStartDate,CapacityStartDate )
end as ShiftStartDate,
case when (_ShiftDef.ShiftDefinition is not null)
then _ShiftDef.ShiftEndDate
else coalesce( _Interval.IntervalEndDate, CapacityEndDate )
end as ShiftEndDate,
// coalesce(_ShiftDef.BreakDurationInSeconds,_Shift.BreakDurationInSeconds) as BreakDurationInSeconds,
case when (_ShiftDef.ShiftDefinition is not null and (( _ShiftDef.CapacityEndTimeID > _ShiftDef.CapacityStartTimeID ) or ( _ShiftDef.CapacityEndTimeID = 0 and _ShiftDef.CapacityStartTimeID = 0 )))
then (_ShiftDef.CapacityEndTimeID - _ShiftDef.CapacityStartTimeID) - _ShiftDef.OperatingDurationInSeconds
when (_ShiftDef.ShiftDefinition is not null and _ShiftDef.CapacityStartTimeID = _ShiftDef.CapacityEndTimeID)
then 86400 - _ShiftDef.OperatingDurationInSeconds
when (_ShiftDef.ShiftDefinition is not null and _ShiftDef.CapacityStartTimeID > _ShiftDef.CapacityEndTimeID)
then (86400 - (_ShiftDef.CapacityStartTimeID - _ShiftDef.CapacityEndTimeID)) - _ShiftDef.OperatingDurationInSeconds
when (_Shift.BreakDurationInSeconds is not null)
then _Shift.BreakDurationInSeconds
when _Interval.StdAvailableCapacityIsValid = 'X'
then _Interval._Capacity.CapacityBreakDuration
end as BreakDurationInSeconds,
case when (_ShiftDef.ShiftDefinition is not null)
then cast(_ShiftDef.OperatingDurationInSeconds as abap.fltp) *
cast(_Shift.CapacityPlanUtilizationPercent as abap.fltp) / cast(100 as abap.fltp)
when _Interval.StdAvailableCapacityIsValid = 'X'
then cast(_Header.CapacityOperatingDuration as abap.fltp)
else _Shift.OperatingDurationInSeconds
end as OperatingDurationInSeconds,
case when (_ShiftDef.ShiftDefinition is not null)
then cast(_ShiftDef.OperatingDurationInSeconds as abap.fltp) *
cast(_Shift.CapacityNumberOfCapacities as abap.fltp) *
cast(_Shift.CapacityPlanUtilizationPercent as abap.fltp) / cast(100 as abap.fltp)
when _Interval.StdAvailableCapacityIsValid = 'X'
then cast(_Header.OverallCapacity as abap.fltp)
else TotOperatingDurationInSeconds
end as TotOperatingDurationInSeconds
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CAPACITY",
"I_WORKCENTERCAPACITYINTERVAL_2",
"P_CAPACITYHEADER",
"P_WORKCENTERSHIFTDEFINITION",
"P_WRKCTRAVAILABLECAPSHIFT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/