@AbapCatalog.sqlViewName: 'PCAPACITYDEF'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM: {
private:true,
viewType: #COMPOSITE
}
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.preserveKey:true
//@EndUserText.label: 'Work Center Capacity Definition'
define view P_CapacityDefinition
as select from P_CapacityHeader as Header
// interval exists
left outer to many join P_CapacityInterval as interval on Header.CapacityInternalID = interval.CapacityInternalID
and Header.CapacityActiveVersion = interval.AvailableCapacityType
// and Header.WorkCenterInternalID = interval.WorkCenterInternalID //condition added for pooled capacity scenario
// shift exists
left outer to many join I_AvailableCapacityShift as shift on shift.CapacityInternalID = interval.CapacityInternalID
and shift.ValidityEndDate = interval.ValidityEndDate
and shift.AvailableCapacityType = interval.AvailableCapacityType
and interval.StdAvailableCapacityIsValid = ''
// shift definition exists
left outer to many join P_WorkCenterShiftDefinition as shift_def on shift.ShiftDefinition = shift_def.ShiftDefinition
and Header.ShiftGroup = shift_def.ShiftGrouping
and interval.ValidityEndDate >= shift_def.ShiftStartDate // interval and shift definition must overlap
and interval.ValidityBeginDate <= shift_def.ShiftEndDate
{
// key Header.WorkCenterInternalID as WorkCenterInternalID,
// key Header.WorkCenterTypeCode as WorkCenterTypeCode,
// key Header.CapacityCategoryAllocation as CapacityCategoryAllocation,
key Header.CapacityInternalID as CapacityInternalID,
key Header.CapacityActiveVersion as CapacityActiveVersion,
// key case when (shift_def.ShiftDefinition is not null)
// then case when shift_def.ShiftEndDate < interval.ValidityEndDate
// then shift_def.ShiftEndDate
// else interval.ValidityEndDate
// end
// else coalesce( interval.ValidityEndDate, Header.ValidityEndDate )
// end as ValidityEndDate,
key case when (shift_def.ShiftDefinition is not null and shift_def.ShiftEndDate < interval.ValidityEndDate)
then shift_def.ShiftEndDate
when interval.ValidityEndDate is not null
then interval.ValidityEndDate
when Header.ValidityEndDate is not null
then Header.ValidityEndDate
end as ValidityEndDate,
// when (shift_def.ShiftDefinition is not null and shift_def.ShiftEndDate > interval.ValidityEndDate)
// then interval.ValidityEndDate
// when (shift_def.ShiftDefinition is null and interval.ValidityEndDate is not null)
// then interval.ValidityEndDate
// when (shift_def.ShiftDefinition is null and interval.ValidityEndDate is null)
// then Header.ValidityEndDate
// end as ValidityEndDate,
// key coalesce( shift.WeekDay, '1' ) as WeekDay,
key case when shift.WeekDay is not null
then shift.WeekDay
when shift.WeekDay is null
then '1'
end as WeekDay,
// key coalesce( shift.AvailableCapacityShift, '' ) as AvailableCapacityShift,
key case when shift.AvailableCapacityShift is not null
then shift.AvailableCapacityShift
when shift.AvailableCapacityShift is null
then ''
end as AvailableCapacityShift,
// Header.WorkCenter as WorkCenter,
Header.Plant as Plant,
Header.FactoryCalendar as FactoryCalendar,
// adjust validity dates if shift definition exists
// case when (shift_def.ShiftDefinition is not null)
// then case when shift_def.ShiftStartDate > interval.ValidityBeginDate
// then shift_def.ShiftStartDate
// else interval.ValidityBeginDate
// end
// else coalesce( interval.ValidityBeginDate, Header.ValidityBeginDate )
// end as ValidityBeginDate,
case when (shift_def.ShiftDefinition is not null and shift_def.ShiftStartDate > interval.ValidityBeginDate)
then shift_def.ShiftStartDate
when interval.ValidityBeginDate is not null
then interval.ValidityBeginDate
when Header.ValidityBeginDate is not null
then Header.ValidityBeginDate
end as ValidityBeginDate,
//coalesce(shift_def.ShiftDefinition,'') as ShiftName,
case when shift_def.ShiftDefinition is not null
then shift_def.ShiftDefinition
when shift_def.ShiftDefinition is null
then ''
end as ShiftName,
shift_def.ShiftDefinition,
Header.ShiftGroup as ShiftGrouping,
// case when (shift_def.ShiftDefinition is not null)
// then shift_def.ShiftStartDate
// else coalesce( interval.ValidityBeginDate, Header.ValidityBeginDate )
// end as ShiftStartDate,
case when (shift_def.ShiftDefinition is not null)
then shift_def.ShiftStartDate
when interval.ValidityBeginDate is not null
then interval.ValidityBeginDate
when Header.ValidityBeginDate is not null
then Header.ValidityBeginDate
end as ShiftStartDate,
// case when (shift_def.ShiftDefinition is not null)
// then shift_def.ShiftStartTime
// else
// cast ( substring( cast (
// tstmp_add_seconds(
// cast ('10010102000000' as abap.dec(15,0)),
// cast( coalesce( shift.CapacityStartTimeID, Header.CapacityStartTime) as abap.dec(15,0) ),
// 'FAIL') as abap.sstring(50)),
// 9,6) as abap.tims)
// end as ShiftStartTime,
case when (shift_def.ShiftDefinition is not null)
then shift_def.ShiftStartTime
when (shift_def.ShiftDefinition is null)
then
cast ( substring( cast (
tstmp_add_seconds(
cast ('10010102000000' as abap.dec(15,0)),
cast( case when shift.CapacityStartTimeID is not null
then shift.CapacityStartTimeID
when shift.CapacityStartTimeID is null
then Header.CapacityStartTime
end as abap.dec(15,0) ),
'FAIL') as abap.sstring(50)),
9,6) as abap.tims)
end as ShiftStartTime,
// case when (shift_def.ShiftDefinition is not null)
// then shift_def.ShiftEndDate
// else coalesce( interval.ValidityEndDate, Header.ValidityEndDate )
// end as ShiftEndDate,
case when (shift_def.ShiftDefinition is not null)
then shift_def.ShiftEndDate
when (interval.ValidityEndDate is not null)
then interval.ValidityEndDate
when (Header.ValidityEndDate is not null)
then Header.ValidityEndDate
end as ShiftEndDate,
// case when (shift_def.ShiftDefinition is not null)
// then shift_def.ShiftEndTime
// when shift.CapacityEndTimeID = 86400
// or (Header.CapacityEndTime = 86400 and shift.CapacityEndTimeID is null)
// then cast( '240000' as abap.tims )
// else
// cast ( substring( cast (
// tstmp_add_seconds(
// cast ('10010102000000' as abap.dec(15,0)),
// cast( coalesce( shift.CapacityEndTimeID, Header.CapacityEndTime) as abap.dec(15,0) ),
// 'FAIL') as abap.sstring(50)),
// 9,6) as abap.tims)
// end as ShiftEndTime,
case when (shift_def.ShiftDefinition is not null)
then shift_def.ShiftEndTime
when shift.CapacityEndTimeID = 86400
or (Header.CapacityEndTime = 86400 and shift.CapacityEndTimeID is null)
then cast( '240000' as abap.tims )
else
cast ( substring( cast (
tstmp_add_seconds(
cast ('10010102000000' as abap.dec(15,0)),
cast( case when shift.CapacityEndTimeID is not null
then shift.CapacityEndTimeID
when Header.CapacityEndTime is not null
then Header.CapacityEndTime
end as abap.dec(15,0) ),
'FAIL') as abap.sstring(50)),
9,6) as abap.tims)
end as ShiftEndTime,
// case when (shift_def.ShiftDefinition is not null)
// then shift_def.ShiftEndTime
// when shift.CapacityEndTimeID = 86400 or (Header.CapacityEndTime = 86400 and shift.CapacityEndTimeID is null)
// then cast( '240000' as abap.tims )
// when (shift_def.ShiftDefinition is null ) or
// (shift.CapacityEndTimeID <> 86400 or (Header.CapacityEndTime <> 86400 and shift.CapacityEndTimeID is not null))
// then cast ( substring( cast (
// tstmp_add_seconds(
// cast ('10010102000000' as abap.dec(15,0)),
// cast (case when shift.CapacityEndTimeID is not null
// then shift.CapacityEndTimeID
// when shift.CapacityEndTimeID is null
// then Header.CapacityEndTime
// end as abap.dec(15,0)), 'FAIL') as abap.sstring(50)), 9,6) as abap.tims)
// end as ShiftEndTime,
// Header.WorkCenterResponsible as WorkCenterResponsible,
// Header.WorkCenterDesc as WorkCenterDesc,
// Header.WorkCenterCategoryCode as WorkCenterCategoryCode,
Header.CapacityCategoryCode as CapacityCategoryCode,
Header.CapacityCategoryName as CapacityCategoryName,
Header.Capacity as Capacity,
// Header.CapacityText as CapacityText,
Header.CapacityResponsiblePlanner as CapacityResponsiblePlanner,
// coalesce( coalesce( shift_def.CapacityStartTimeID,
// shift.CapacityStartTimeID ),
// Header.CapacityStartTime ) as CapacityStartTime,
case when shift_def.CapacityStartTimeID is not null
then shift_def.CapacityStartTimeID
when shift.CapacityStartTimeID is not null // shift_def.CapacityStartTimeID is null
then shift.CapacityStartTimeID
when Header.CapacityStartTime is not null //shift.CapacityStartTimeID is null
then Header.CapacityStartTime
end as CapacityStartTime,
// coalesce( coalesce( shift_def.CapacityEndTimeID,
// shift.CapacityEndTimeID ),
// Header.CapacityEndTime) as CapacityEndTime,
case when shift_def.CapacityEndTimeID is not null
then shift_def.CapacityEndTimeID
when shift.CapacityEndTimeID is not null
then shift.CapacityEndTimeID
when Header.CapacityEndTime is not null
then Header.CapacityEndTime
end as CapacityEndTime,
// coalesce( interval.AvailableCapacityIntervalDurn, '01' ) as AvailableCapacityIntervalDurn,
case when interval.AvailableCapacityIntervalDurn is not null
then interval.AvailableCapacityIntervalDurn
when interval.AvailableCapacityIntervalDurn is null
then '01'
end as AvailableCapacityIntervalDurn,
// WorkDay rule ('WorkDay' indicator) will always come from the interval definitions
//coalesce( interval.WorkDayRule, '' ) as WorkDayRule,
case when interval.WorkDayRule is not null
then interval.WorkDayRule
when interval.WorkDayRule is null
then ''
end as 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.OverallCapacity )
// end as TotOperatingDurationInSeconds,
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)
when (shift.TotOperatingDurationInSeconds is not null)
then shift.TotOperatingDurationInSeconds
when (Header.OverallCapacity is not null)
then Header.OverallCapacity
end as TotOperatingDurationInSeconds,
// 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) /
// cast (3600 as abap.fltp)
// else coalesce( shift.TotOperatingDurationInSeconds, Header.OverallCapacity ) / cast (3600 as abap.fltp)
// end as TotOperatingDurationInHours,
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) /
cast (3600 as abap.fltp)
when (shift.TotOperatingDurationInSeconds is not null)
then cast(shift.TotOperatingDurationInSeconds as abap.fltp) / cast (3600 as abap.fltp)
when (Header.OverallCapacity is not null)
then Header.OverallCapacity / cast (3600 as abap.fltp)
end as TotOperatingDurationInHours,
// coalesce( coalesce( cast (shift_def.OperatingDurationInSeconds as abap.fltp),
// (cast(shift.OperatingDurationInSeconds as abap.fltp) * cast(100 as abap.fltp) / cast(shift.CapacityPlanUtilizationPercent as abap.fltp) ) ),
// cast(Header.SingleCapacity as abap.fltp) )
// as OperatingDurationInSeconds,
// case when (shift_def.ShiftDefinition is not null)
// then cast(shift_def.OperatingDurationInSeconds as abap.fltp)
// when shift.CapacityPlanUtilizationPercent is not null
// then case when shift.CapacityPlanUtilizationPercent > '000'
// then (cast(shift.OperatingDurationInSeconds as abap.fltp) * cast(100 as abap.fltp) / cast(shift.CapacityPlanUtilizationPercent as abap.fltp) )
// else 0
// end
// else cast(Header.SingleCapacity as abap.fltp)
// end as OperatingDurationInSeconds,
case when (shift_def.ShiftDefinition is not null)
then cast(shift_def.OperatingDurationInSeconds as abap.fltp)
when (shift.CapacityPlanUtilizationPercent is not null and shift.CapacityPlanUtilizationPercent > '000')
then (cast(shift.OperatingDurationInSeconds as abap.fltp) * cast(100 as abap.fltp) / cast(shift.CapacityPlanUtilizationPercent as abap.fltp) )
when (shift.CapacityPlanUtilizationPercent is not null and (shift.CapacityPlanUtilizationPercent = '000'))
then 0
when (Header.SingleCapacity is not null)
then cast(Header.SingleCapacity as abap.fltp)
end as OperatingDurationInSeconds,
// only consider shift definition break, if it is inside shift
// case when shift_def.ShiftDefinition is not null
// then case when ( shift_def.CapacityEndTimeID > shift_def.CapacityStartTimeID ) or ( shift_def.CapacityEndTimeID = 0 and shift_def.CapacityStartTimeID = 0 )
// then (shift_def.CapacityEndTimeID - shift_def.CapacityStartTimeID) - shift_def.OperatingDurationInSeconds
// when shift_def.CapacityStartTimeID = shift_def.CapacityEndTimeID
// then 86400 - shift_def.OperatingDurationInSeconds
// when shift_def.CapacityStartTimeID > shift_def.CapacityEndTimeID
// then (86400 - (shift_def.CapacityStartTimeID - shift_def.CapacityEndTimeID)) - shift_def.OperatingDurationInSeconds
// end
// else coalesce( shift.BreakDurationInSeconds, Header.CapacityBreakDuration )
// end as CapacityBreakDuration,
case when (shift_def.ShiftDefinition is not null and (( shift_def.CapacityEndTimeID > shift_def.CapacityStartTimeID ) or ( shift_def.CapacityEndTimeID = 0 and shift_def.CapacityStartTimeID = 0 )))
then (shift_def.CapacityEndTimeID - shift_def.CapacityStartTimeID) - shift_def.OperatingDurationInSeconds
when (shift_def.ShiftDefinition is not null and shift_def.CapacityStartTimeID = shift_def.CapacityEndTimeID)
then 86400 - shift_def.OperatingDurationInSeconds
when (shift_def.ShiftDefinition is not null and shift_def.CapacityStartTimeID > shift_def.CapacityEndTimeID)
then (86400 - (shift_def.CapacityStartTimeID - shift_def.CapacityEndTimeID)) - shift_def.OperatingDurationInSeconds
when (shift.BreakDurationInSeconds is not null)
then shift.BreakDurationInSeconds
when (Header.CapacityBreakDuration is not null)
then Header.CapacityBreakDuration
end as CapacityBreakDuration,
//Operating Capacity
// 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
// (case when (shift.OperatingDurationInSeconds is not null )
// then
// shift.OperatingDurationInSeconds
// else
// Header.CapacityOperatingDuration
// end )
// end as CapacityOperatingDuration,
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)
when (shift.OperatingDurationInSeconds is not null)
then shift.OperatingDurationInSeconds
when (Header.CapacityOperatingDuration is not null)
then Header.CapacityOperatingDuration
end as CapacityOperatingDuration,
// coalesce( shift.CapacityNumberOfCapacities, Header.CapacityNumberOfCapacities) as CapacityNumberOfCapacities,
case when shift.CapacityNumberOfCapacities is not null
then shift.CapacityNumberOfCapacities
when shift.CapacityNumberOfCapacities is null
then Header.CapacityNumberOfCapacities
end as CapacityNumberOfCapacities,
// coalesce( shift.CapacityPlanUtilizationPercent, Header.CapacityPlanUtilizationPercent) as CapacityPlanUtilizationPercent
case when shift.CapacityPlanUtilizationPercent is not null
then shift.CapacityPlanUtilizationPercent
when shift.CapacityPlanUtilizationPercent is null
then Header.CapacityPlanUtilizationPercent
end as CapacityPlanUtilizationPercent
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_AVAILABLECAPACITYSHIFT",
"P_CAPACITYHEADER",
"P_CAPACITYINTERVAL",
"P_WORKCENTERSHIFTDEFINITION"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/