I_MfgOrderOperationDurnCube

@AbapCatalog.sqlViewName: 'IPPMFGORDEROPDC'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #MANDATORY
@AccessControl.personalData.blocking: #NOT_REQUIRED
@AccessControl.privilegedAssociations: ['_ProductionSupervisor']
@Analytics.dataCategory: #CUBE
@ClientHandling.algorithm: #SESSION_VARIABLE
@ClientHandling.type: #CLIENT_DEPENDENT
@Metadata.allowExtensions: true
@ObjectModel.modelingPattern: #ANALYTICAL_CUBE
@ObjectModel.supportedCapabilities: [#ANALYTICAL_PROVIDER]
@ObjectModel.usageType: {serviceQuality: #D, sizeCategory: #XL, dataClass: #MIXED}
@VDM.viewType: #COMPOSITE
@EndUserText.label: 'Manufacturing Order Operation Duration - Cube'
// Operation cube view with additional duration calculations


define view I_MfgOrderOperationDurnCube
  as select from I_MfgOrderOperationCube as oper                                       
    inner join   I_OrderItem             as item on  item.OrderID   = oper.ManufacturingOrder
                                                 and item.OrderItem = '0001'
    inner join   P_PPH_ReportingDate3    as date on  oper.OpActualExecutionEndDate = date.ReportingDate

    association [1..1] to I_CalendarDate    as _EndDate               on $projection.ActualExecutionEndDate = _EndDate.CalendarDate
    association [0..1] to I_WeekDay         as _EndDateWeekDay        on $projection.EndDateWeekDay         = _EndDateWeekDay.WeekDay
    association [0..1] to I_CalendarMonth   as _EndDateMonth          on $projection.EndDateMonth           = _EndDateMonth.CalendarMonth
    association [0..1] to I_CalendarYear    as _EndDateYear           on $projection.EndDateYear            = _EndDateYear.CalendarYear   
    association [0..1] to I_ReportingPeriod as _ReportingPeriod       on $projection.ReportingPeriod        = _ReportingPeriod.ReportingPeriod
    association [0..1] to I_UnitOfMeasure   as _DurationUnit          on $projection.DurationUnit           = _DurationUnit.UnitOfMeasure
    association [0..1] to I_UnitOfMeasure   as _ActualDurationUnit    on $projection.ActualDurationUnit     = _ActualDurationUnit.UnitOfMeasure
    association [0..1] to I_UnitOfMeasure   as _PlannedDurationUnit   on $projection.PlannedDurationUnit    = _PlannedDurationUnit.UnitOfMeasure
    association [0..1] to I_UnitOfMeasure   as _DeviationDurationUnit on $projection.DeviationDurationUnit  = _DeviationDurationUnit.UnitOfMeasure
{
      // Technical Key

      @ObjectModel.foreignKey.association: '_OrderInternalID'
  key oper.OrderInternalBillOfOperations,
  key oper.OrderIntBillOfOperationsItem,

      // Semantical Key

      @ObjectModel.foreignKey.association: '_MfgOrder'
      oper.ManufacturingOrder,
      @ObjectModel.foreignKey.association: '_MfgOrderSequence'
      oper.ManufacturingOrderSequence,
      @ObjectModel.text.element: ['MfgOrderOperationText']
      oper.ManufacturingOrderOperation,
      @ObjectModel.text.element: ['MfgOrderOperationText']
      oper.ManufacturingOrderSubOperation,

      // Order header data

      @ObjectModel.foreignKey.association: '_MfgOrderCategory'
      oper.ManufacturingOrderCategory,
      @ObjectModel.foreignKey.association: '_MfgOrderType'
      oper.ManufacturingOrderType,
      @ObjectModel.foreignKey.association: '_ProductionSupervisor'
      oper.ProductionSupervisor,
      @ObjectModel.foreignKey.association: '_MRPPlant'
      item.MRPPlant,
      @ObjectModel.foreignKey.association: '_Material'
      item.Material,

      // Text

      @Semantics.text: true
      oper.MfgOrderOperationText,
      @ObjectModel.foreignKey.association: '_Language'
      oper.Language,

      // Attributes

      oper.MfgOrderOperationIsPhase,
      oper.MfgOrderPhaseSuperiorOperation,
      oper.NumberOfCapacities,
      oper.NumberOfConfirmationSlips,
      oper.NumberOfOperationConfirmations,
      oper.OperationImportance,

      // Status

      oper.OperationIsCreated,
      oper.OperationIsReleased,
      oper.OperationIsPrinted,
      oper.OperationIsConfirmed,
      oper.OperationIsPartiallyConfirmed,
      oper.OperationIsTechlyCompleted,
      oper.OperationIsClosed,
      oper.OperationIsScheduled,
      oper.OperationIsPartiallyDelivered,
      oper.OperationIsDelivered,

      // Assignments

      @ObjectModel.foreignKey.association: '_ProductionPlant'
      oper.ProductionPlant,
      @ObjectModel.foreignKey.association: '_WorkCenter'
      oper.WorkCenterInternalID,
      @ObjectModel.foreignKey.association: '_WorkCenterType'
      oper.WorkCenterTypeCode,
      @ObjectModel.foreignKey.association: '_WorkCenterBySemanticKey'
      oper.WorkCenter,
      @ObjectModel.foreignKey.association: '_OperationControlProfile'
      oper.OperationControlProfile,
      @ObjectModel.foreignKey.association: '_BillOfOperationsType'
      oper.BillOfOperationsType,
      @ObjectModel.foreignKey.association: '_OperationConfirmation'
      oper.OperationConfirmation,
      @ObjectModel.foreignKey.association: '_CapacityRequirement'
      oper.CapacityRequirement,
      @ObjectModel.foreignKey.association: '_CapacityRequirementItem'
      oper.CapacityRequirementItem,

      // Assignments FI/CO

      @ObjectModel.foreignKey.association: '_CompanyCode'
      oper.CompanyCode,
      @ObjectModel.foreignKey.association: '_ControllingArea'
      oper.ControllingArea,

      // Dates and Times

      @Semantics.calendarItem.dtStart: true
      oper.OpErlstSchedldExecStrtDte,
      oper.OpErlstSchedldExecStrtTme,
      @Semantics.calendarItem.dtStart: true
      oper.OpErlstSchedldProcgStrtDte,
      oper.OpErlstSchedldProcgStrtTme,
      @Semantics.calendarItem.dtStart: true
      oper.OpErlstSchedldTrdwnStrtDte,
      oper.OpErlstSchedldTrdwnStrtTme,
      @Semantics.calendarItem.dtEnd: true
      oper.OpErlstSchedldExecEndDte,
      oper.OpErlstSchedldExecEndTme,
      @Semantics.calendarItem.dtStart: true
      oper.OpLtstSchedldExecStrtDte,
      oper.OpLtstSchedldExecStrtTme,
      @Semantics.calendarItem.dtStart: true
      oper.OpLtstSchedldProcgStrtDte,
      oper.OpLtstSchedldProcgStrtTme,
      @Semantics.calendarItem.dtStart: true
      oper.OpLtstSchedldTrdwnStrtDte,
      oper.OpLtstSchedldTrdwnStrtTme,
      @Semantics.calendarItem.dtEnd: true
      oper.OpLtstSchedldExecEndDte,
      oper.OpLtstSchedldExecEndTme,
      @Semantics.calendarItem.dtStart: true
      oper.OperationConfirmedStartDate,
      oper.OperationConfirmedEndDate,
      @Semantics.calendarItem.dtStart: true
      oper.OpActualExecutionStartDate,
      oper.OpActualExecutionStartTime,
      @Semantics.calendarItem.dtEnd: true
      oper.OpActualSetupEndDate,
      oper.OpActualSetupEndTime,
      @Semantics.calendarItem.dtStart: true
      oper.OpActualProcessingStartDate,
      oper.OpActualProcessingStartTime,
      @Semantics.calendarItem.dtEnd: true
      oper.OpActualProcessingEndDate,
      oper.OpActualProcessingEndTime,
      @Semantics.calendarItem.dtStart: true
      oper.OpActualTeardownStartDate,
      oper.OpActualTeardownStartTme,
      @Semantics.calendarItem.dtEnd: true
      oper.OpActualExecutionEndDate,
      oper.OpActualExecutionEndTime,

      @ObjectModel.foreignKey.association: '_ReportingPeriod'
      cast(date.ReportingPeriod as pph_reportingperiod preserving type) as ReportingPeriod,
      @Semantics.calendarItem.dtEnd: true
      oper.OpActualExecutionEndDate as ActualExecutionEndDate,
      oper.OpActualExecutionEndTime as ActualExecutionEndTime,
      cast(left(oper.OpActualExecutionEndTime,2) as enddatehour)       as EndDateHour,
      @ObjectModel.foreignKey.association: '_EndDateWeekDay'
      date.ReportingDateWeekDay as EndDateWeekDay,
      @Semantics.calendar.week: true
      date.ReportingDateWeek    as EndDateWeek,
      @ObjectModel.foreignKey.association: '_EndDateMonth'
      date.ReportingDateMonth   as EndDateMonth,
      @Semantics.calendar.year: true
      date.ReportingDateYear    as EndDateYear,
      cast(concat( date.ReportingDateYear, date.ReportingDateMonth ) as vdm_yearmonth) as EndDateYearMonth,

      // Durations

      @Semantics.calendarItem.duration: true
      oper.PlannedActualStartDateDvtnDurn,
      @Semantics.calendarItem.duration: true
      oper.PlannedActualEndDateDvtnDurn,
      @Semantics.calendarItem.duration: true
      oper.PlannedCurrentStrtDateDvtnDurn,
      @Semantics.calendarItem.duration: true
      oper.PlannedCurrentEndDateDvtnDurn,

      @Aggregation.default: #SUM
      @Semantics.calendarItem.duration: true
      cast(oper.ActualWorkdayDurnInSeconds as actualdurationinsec preserving type)       as ActualWorkdayDurnInSeconds,
      @Aggregation.default: #SUM
      @Semantics.calendarItem.duration: true
      cast(oper.PlannedWorkdayDurnInSeconds as planneddurationinsec preserving type)     as PlannedWorkdayDurnInSeconds,
      @Aggregation.default: #SUM
      @Semantics.calendarItem.duration: true
      cast(oper.DeviationWorkdayDurnInSeconds as deviationdurationinsec preserving type) as DeviationWorkdayDurnInSeconds,

      // Unit calculation and duration determination

      @Semantics.unitOfMeasure: true
      cast(oper.ActualDurationUnit    as actualdurationunit preserving type)    as ActualDurationUnit,
      @Semantics.unitOfMeasure: true
      cast(oper.PlannedDurationUnit   as planneddurationunit preserving type)   as PlannedDurationUnit,
      @Semantics.unitOfMeasure: true
      cast(oper.DeviationDurationUnit as deviationdurationunit preserving type) as DeviationDurationUnit,
      
      @Semantics.unitOfMeasure: true
      cast(oper.DurationUnit as durationunit preserving type)                   as DurationUnit,     //always in seconds


      @Semantics.quantity.unitOfMeasure: 'ActualDurationUnit'
      @Aggregation.default: #SUM
      cast(unit_conversion( quantity => oper.ActualWorkdayDurnInSeconds,
                       source_unit => oper.DurationUnit,
                       target_unit => oper.ActualDurationUnit,
                       error_handling => 'FAIL_ON_ERROR') as actualduration)    as ActualDuration,
      @Semantics.quantity.unitOfMeasure: 'PlannedDurationUnit'                     
      @Aggregation.default: #SUM
      cast(unit_conversion( quantity => oper.PlannedWorkdayDurnInSeconds,
                       source_unit => oper.DurationUnit,
                       target_unit => oper.PlannedDurationUnit,
                       error_handling => 'FAIL_ON_ERROR') as plannedduration)   as PlannedDuration,
      @Semantics.quantity.unitOfMeasure: 'DeviationDurationUnit'                     
      @Aggregation.default: #SUM                  
      cast(unit_conversion( quantity => oper.DeviationWorkdayDurnInSeconds,
                       source_unit => oper.DurationUnit,
                       target_unit => oper.DeviationDurationUnit,
                       error_handling => 'FAIL_ON_ERROR') as deviationduration) as DeviationDuration,

      // Associations

      oper._ManufacturingOrder,
      @Consumption.hidden: true
      oper._OrderInternalID,
      @Consumption.hidden: true
      @VDM.lifecycle.status: #DEPRECATED
      @VDM.lifecycle.successor: '_OrderInternalID'
      oper._OrdInternalBillOfOperations,
      @VDM.lifecycle.status: #DEPRECATED
      @VDM.lifecycle.successor: '_ManufacturingOrder'
      oper._MfgOrder,
      oper._MfgOrderSequence,
      oper._MfgOrderCategory,
      oper._MfgOrderType,
      oper._ProductionPlant,
      oper._ProductionSupervisor,
      oper._OperationControlProfile,
      oper._BillOfOperationsType,
      oper._WorkCenter,
      oper._WorkCenterType,
      oper._WorkCenterBySemanticKey,
      item._Material,
      item._MRPPlant,
      oper._CapacityRequirement,
      oper._CapacityRequirementItem,
      oper._OperationConfirmation,
      oper._CompanyCode,
      oper._ControllingArea,
      oper._Language,
      _ReportingPeriod,
      _EndDate,
      _EndDateWeekDay,
      _EndDateMonth,
      _EndDateYear,
      _DurationUnit,
      _ActualDurationUnit,
      _PlannedDurationUnit,
      _DeviationDurationUnit
}
where oper.OperationIsConfirmed = 'X';
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MFGORDEROPERATIONCUBE",
"I_ORDERITEM",
"P_PPH_REPORTINGDATE3"
],
"ASSOCIATED":
[
"I_BILLOFOPERATIONSTYPE",
"I_CALENDARDATE",
"I_CALENDARMONTH",
"I_CALENDARYEAR",
"I_CAPACITYREQUIREMENT",
"I_CAPACITYREQUIREMENTITEM",
"I_COMPANYCODE",
"I_CONFIRMATIONGROUP",
"I_CONTROLLINGAREA",
"I_LANGUAGE",
"I_MANUFACTURINGORDER",
"I_MATERIAL",
"I_MFGORDER",
"I_MFGORDERCATEGORY",
"I_MFGORDERSEQUENCE",
"I_MFGORDERTYPE",
"I_OPERATIONCONTROLPROFILE",
"I_ORDERINTERNALID",
"I_ORDINTERNALBILLOFOPERATIONS",
"I_PLANT",
"I_PRODUCTIONSUPERVISOR",
"I_REPORTINGPERIOD",
"I_UNITOFMEASURE",
"I_WEEKDAY",
"I_WORKCENTER",
"I_WORKCENTERBYSEMANTICKEY",
"I_WORKCENTERTYPE"
],
"BASE":
[
"I_MFGORDEROPERATIONCUBE",
"I_ORDERITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/