C_RSHWrkCtrUtilizationKPI

@AbapCatalog.sqlViewName: 'CRSHWCUTILKPI'

@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'Work Center Utilization Overview'
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #CONSUMPTION
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XL

/*
CONTRACT**********************************************************************************************************************

Name:                       Work Center Utilization for current and next week
Specification:              This is used to determine utilization in get_entityset of C_RSHOverloadedWC (CL_RSH_WC_UTILIZATION_DB)
Requires:                   Work Center Utilization for 2 weeks starting from current date
Ensures:                    Work Center Utilization for current and next week
Owners:                     AN
Contributors:               PRP
Unit Test required Y/N:     Yes
Additional comments         None

END OF CONTRACT***************************************************************************************************************
*/

define view C_RSHWrkCtrUtilizationKPI
  with parameters
    P_StartDate : datum,
    P_EndDate   : datum

  as select from P_RSHWCUtilizationPerWeek( P_StartDate : $parameters.P_StartDate, P_EndDate : $parameters.P_EndDate) as Utilization
  -- get one day of this week and one day of next week
    inner join   P_RSHThisWeekAndNextWeek                                                                             as ThisCwNextCw on ThisCwNextCw.DateOfCurrentWeek = $session.user_date
  -- Join Calendar Date twice to retrieve Calendar Week of current and next week
    inner join   I_CalendarDate                                                                                       as ThisWeek     on ThisWeek.CalendarDate = ThisCwNextCw.DateOfCurrentWeek
    inner join   I_CalendarDate                                                                                       as NextWeek     on NextWeek.CalendarDate = ThisCwNextCw.DateOfNextWeek
    inner join   I_CalendarDate                                                                                       as ThirdWeek    on ThirdWeek.CalendarDate = ThisCwNextCw.DateofThirdWeek
    inner join   I_CalendarDate                                                                                       as FourthWeek   on FourthWeek.CalendarDate = ThisCwNextCw.DateofFourthWeek

{

  key Utilization.WorkCenter             as WorkCenter,
      Utilization.WorkCenterCategoryCode as WorkCenterCategoryCode,
      Utilization.Plant                  as Plant,

      -- get work center utilization of current year week
      -- if the utilization value is greater than 9999%, default it to 9999%
      max( case
          when Utilization.YearWeek = ThisWeek.YearWeek
          then
              case
                 when WorkCenterUtilization > 9999
                 then
                     '9999%'
                 else
                     concat(cast ( cast( round ( WorkCenterUtilization, 0)as abap.dec( 4, 0 ) ) as val_text ), '%')
              end
        end )                            as WorkCenterUtilznIn1stWeekText,

      -- get work center utilization of next year week
      -- if the utilization value is greater than 9999%, default it to 9999%
      max( case
           when Utilization.YearWeek = NextWeek.YearWeek
           then
               case
                   when WorkCenterUtilization > 9999
                   then
                       '9999%'
                   else
                       concat(cast ( cast ( round ( WorkCenterUtilization, 0 )as abap.dec( 4, 0 ) ) as val_text ), '%')
               end
         end )                           as WorkCenterUtilznIn2ndWeekText,

      'FUTURE'                           as PeriodType,

      max( case
          when Utilization.YearWeek = ThirdWeek.YearWeek
          then
              case
                  when WorkCenterUtilization > 9999
                  then
                      '9999%'
                  else
                      concat(cast ( cast ( round ( WorkCenterUtilization, 0 )as abap.dec( 4, 0 ) ) as val_text ), '%')
              end
        end )                            as WorkCenterUtilznIn3rdWeekText,

      max( case
       when Utilization.YearWeek = FourthWeek.YearWeek
       then
           case
               when WorkCenterUtilization > 9999
               then
                   '9999%'
               else
                   concat(cast ( cast ( round ( WorkCenterUtilization, 0 )as abap.dec( 4, 0 ) ) as val_text ), '%')
           end
      end )                              as WorkCenterUtilznIn4thWeekText

      //      Utilization.WorkCenterInternalID,

      //      Utilization.WorkCenterTypeCode

}
group by
  Utilization.WorkCenter,
  Utilization.WorkCenterCategoryCode,
  Utilization.Plant
//  Utilization.WorkCenterInternalID,

//  Utilization.WorkCenterTypeCode