P_WorkCenterCapacityDefinition

@AbapCatalog.sqlViewName: 'PWORKCTRCAPDEF'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM: {
  private:true,
  viewType: #COMPOSITE
}
@ClientHandling.algorithm: #SESSION_VARIABLE
//@EndUserText.label: 'Work Center Capacity Definition'


define view P_WorkCenterCapacityDefinition
  as select from    P_WorkCenterHeaderCapacity   as Header
  // interval exists

    left outer join I_WorkCenterCapacityInterval 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 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 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 interval.AvailableCapacityType                                                                        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 coalesce( right( shift.WeekDay, 1),               '1' )                                               as  WeekDay,
  key coalesce( shift.AvailableCapacityShift, '' )                                                          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,


      coalesce(shift_def.ShiftDefinition,'')                                                                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.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.ShiftEndDate
         else coalesce( interval.ValidityEndDate, 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,

      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,


      coalesce( coalesce( shift_def.CapacityEndTimeID,
                           shift.CapacityEndTimeID ),
                           Header.CapacityEndTime)                                                          as  CapacityEndTime,


      coalesce( interval.AvailableCapacityIntervalDurn, '01' )                                              as  AvailableCapacityIntervalDurn,

      // WorkDay rule ('WorkDay' indicator) will always come from the interval definitions

      coalesce( interval.WorkDayRule, '' )                                                                  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) /
                cast (3600 as abap.fltp)
           else coalesce( shift.TotOperatingDurationInSeconds, 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,


      // 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,

     //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,


      coalesce( shift.CapacityNumberOfCapacities,     Header.CapacityNumberOfCapacities)                    as  CapacityNumberOfCapacities,
      coalesce( shift.CapacityPlanUtilizationPercent, Header.CapacityPlanUtilizationPercent)                as  CapacityPlanUtilizationPercent
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_AVAILABLECAPACITYSHIFT",
"I_WORKCENTERCAPACITYINTERVAL",
"P_WORKCENTERHEADERCAPACITY",
"P_WORKCENTERSHIFTDEFINITION"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/