@AbapCatalog.sqlViewName: 'PRSHWCCAPDEF'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Work Center Capacity Definition'
/*
CONTRACT**********************************************************************************************************************
Name: Work Center Capacity Definition
Specification: This will consider the effective capacity interval details from all the different kinds of sources:
Header Capacity, Capacity Interval and Shift, Shift Definition (Grouping)
Examples:
-- 0) Header Capacity without any interval
-- 1) Interval with one or multiple shift(s)
-- 2) Intervals with different cycles (daily '01' or weekly '07')
-- 3) Interval where Standard Available Capacity/Header Capacity is relevant (Days where no Shifts/Intervals are present)
-- 4) Interval where shift definitions from shift groupings are present
Requires: NA
Ensures: This view lists the entire capacity definition from all the assigned work centers.
It ensures that
- only intervals of active capacity version (header.CapacityActiveVersion) are considered
- intervals do not overlap -> begin date is increased by 1.
- shift definition validity is considered and intervals are cut accordingly.
Owners: CK
Contributors: AN,PRP
Unit Test required Y/N: Yes
END OF CONTRACT***************************************************************************************************************
*/
define view P_RSHWCCapacityDefinition
as select from P_RshWCCapacityIntervalDetails as header_and_interval
// shift exists
left outer join I_AvailableCapacityShift as shift on shift.CapacityInternalID = header_and_interval.CapacityInternalID
and shift.ValidityEndDate = header_and_interval.ValidityEndDate
and shift.AvailableCapacityType = header_and_interval.AvailableCapacityType
and header_and_interval.StdAvailableCapacityIsValid = ''
// shift definition exists
left outer join P_RSHShiftDefinition as shift_def on shift.ShiftDefinition = shift_def.ShiftDefinition
and header_and_interval.ShiftGroup = shift_def.ShiftGrouping
and header_and_interval.ValidityEndDate >= shift_def.ShiftStartDate // interval and shift definition must overlap
and header_and_interval.ValidityBeginDate < shift_def.ShiftEndDate // < and not <= as we add day in that view
{
key header_and_interval.CapacityInternalID as CapacityInternalID,
// adjust validity dates if shift definition exists
key case when (shift_def.ShiftDefinition is not null and
shift_def.ShiftEndDate < header_and_interval.ValidityEndDate )
then shift_def.ShiftEndDate
else header_and_interval.ValidityEndDate
end as ValidityEndDate,
key coalesce( right( shift.WeekDay, 1), '1' ) as WeekDay,
key coalesce( shift.AvailableCapacityShift, '' ) as AvailableCapacityShift,
key coalesce( coalesce( shift_def.CapacityStartTimeID,
shift.CapacityStartTimeID ),
header_and_interval.CapacityStartTime ) as CapacityStartTime,
coalesce( coalesce( shift_def.CapacityEndTimeID,
shift.CapacityEndTimeID ),
header_and_interval.CapacityEndTime) as CapacityEndTime,
// adjust validity dates if shift definition exists
case when (shift_def.ShiftDefinition is not null and
shift_def.ShiftStartDate > header_and_interval.ValidityBeginDate )
then shift_def.ShiftStartDate
else dats_add_days(header_and_interval.ValidityBeginDate,1,'FAIL')
end as ValidityBeginDate,
header_and_interval.AvailableCapacityIntervalDurn,
// WorkDay rule ('WorkDay' indicator) will always come from the interval definitions
header_and_interval.WorkDayRule,
//calculate total duration if there is a shift definition
case when (shift_def.ShiftDefinition is not null)
then cast(shift_def.OperatingDurationInSeconds as abap.fltp) *
cast(shift.CapacityNumberOfCapacities as abap.fltp) *
cast(shift.CapacityPlanUtilizationPercent as abap.fltp) / cast(100 as abap.fltp)
else coalesce( shift.TotOperatingDurationInSeconds, header_and_interval.TotOperatingDurationInSeconds )
end as TotOperatingDurationInSeconds,
//Calculate operating duration if there is a shift definition. Operating duration also has
//to take in to account of the utilization percentage defined
case when (shift_def.ShiftDefinition is not null)
then cast(shift_def.OperatingDurationInSeconds as abap.fltp) *
cast(shift.CapacityPlanUtilizationPercent as abap.fltp) / cast(100 as abap.fltp)
else coalesce( shift.OperatingDurationInSeconds, header_and_interval.OperatingDurationInSeconds )
end as OperatingDurationInSeconds,
coalesce( coalesce( shift_def.BreakDuration,
shift.BreakDurationInSeconds ),
header_and_interval.CapacityBreakDuration ) as CapacityBreakDuration,
coalesce( shift.CapacityNumberOfCapacities, header_and_interval.CapacityNumberOfCapacities) as CapacityNumberOfCapacities,
coalesce( shift.CapacityPlanUtilizationPercent, header_and_interval.CapacityPlanUtilizationPercent) as CapacityPlanUtilizationPercent,
header_and_interval.WorkCenterInternalID as WorkCenterInternalID,
header_and_interval.WorkCenter as WorkCenter,
header_and_interval.WorkCenterCategoryCode as WorkCenterCategoryCode,
header_and_interval.WorkCenterTypeCode as WorkCenterTypeCode,
header_and_interval.Plant as Plant,
header_and_interval.FactoryCalendar,
header_and_interval.CapacityActiveVersion,
header_and_interval.IntervalValidityEndDate,
shift.ValidityEndDate as ShiftValidityEndDate,
shift_def.ShiftDefinition as ShiftDefinition,
shift_def.ShiftDefinitionName as ShiftDefinitionName
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_AVAILABLECAPACITYSHIFT",
"P_RSHSHIFTDEFINITION",
"P_RSHWCCAPACITYINTERVALDETAILS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/