I_HandlingUnitHeaderCombined

@AbapCatalog: {
    sqlViewName: 'IHANDLINGUNITHCO',
    compiler.compareFilter: true
}

@ClientHandling.algorithm: #SESSION_VARIABLE

@EndUserText.label: 'Combined Handling Units from ERP and EWM'

@Metadata.ignorePropagatedAnnotations: true

@AccessControl: {
    authorizationCheck:    #MANDATORY,
    personalData.blocking: #BLOCKED_DATA_EXCLUDED
}

@ObjectModel: {
    usageType: {
      dataClass:      #MIXED,
      serviceQuality: #C,
      sizeCategory:   #XL
    }
}

@VDM: {
    viewType: #COMPOSITE,
    lifecycle.contract.type: #SAP_INTERNAL_API
}

// This view is a union of two parts: the Handling Units managed by ERP and the Handling Units managed by CloudWM / EWM

// The Handling Units are distinguished by the key field 'Warehouse'

// For ERP Handling Units the Warehouse is always empty, for EWM Handling Units the Warehouse is always filled

define view I_HandlingUnitHeaderCombined

  // Part I: Handling Units managed by ERP

  as select from I_HndlgUntHdrERPAddlFieldsEWM

{
      // Key

  key HandlingUnitExternalID,
  @Environment.sql.passValue: true
  key EWMWarehouse                   as Warehouse,

      // Identifier fields

      HandlingUnitCharUUID,
      HandlingUnitInternalCharUUID,
      HandlingUnitExternalIdType,
      HandlingUnitInternalID,
      HandlingUnitHandle             as SourceHandlingUnitUUID,
      HandlingUnitSecondExternalId,

      // Identifier fields of parent

      HandlingUnitLowerLevelRefer,
      ParentHandlingUnitNumber,
      HighestLevelHandlingUnitNumber,

      // Organizational data

      Plant,
      StorageLocation,
      ShippingPoint,

      // Packaging data

      PackagingMaterial,
      PackagingMaterialType,
      PackingInstruction,

      // Weights

      @Semantics.quantity.unitOfMeasure: 'WeightUnit'
      GrossWeight,
      @Semantics.quantity.unitOfMeasure: 'WeightUnit'
      NetWeight,
      @Semantics.quantity.unitOfMeasure: 'WeightUnit'
      HandlingUnitMaxWeight,
      @Semantics.unitOfMeasure:true
      WeightUnit,
      @Semantics.quantity.unitOfMeasure: 'HandlingUnitTareWeightUnit'
      HandlingUnitTareWeight,
      @Semantics.unitOfMeasure:true
      HandlingUnitTareWeightUnit,

      // Volumes

      @Semantics.quantity.unitOfMeasure: 'VolumeUnit'
      GrossVolume,
      @Semantics.quantity.unitOfMeasure: 'VolumeUnit'
      HandlingUnitNetVolume,
      @Semantics.quantity.unitOfMeasure: 'VolumeUnit'
      HandlingUnitMaxVolume,
      @Semantics.unitOfMeasure:true
      VolumeUnit,
      @Semantics.quantity.unitOfMeasure: 'HandlingUnitTareVolumeUnit'
      HandlingUnitTareVolume,
      @Semantics.unitOfMeasure:true
      HandlingUnitTareVolumeUnit,

      // Dimensions

      @Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
      HandlingUnitLength,
      @Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
      HandlingUnitWidth,
      @Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
      HandlingUnitHeight,
      @Semantics.unitOfMeasure:true
      HandlingUnitDimensionUnit      as UnitOfMeasureDimension,

      // Reference object

      HandlingUnitPackingObjectType,
      HandlingUnitReferenceDocument,
      HandlingUnitHasCrossRefDoc,

      //Delivery

      ShipToParty,

      // Change log

      CreatedByUser,
      @Semantics.systemDateTime.createdAt: true
      HandlingUnitCreationDateTime   as CreationDateTime,
      LastChangedByUser,
      @Semantics.systemDateTime.lastChangedAt: true
      HandlingUnitLastChangeDateTime as LastChangeDateTime,

      // Status

      HandlingUnitInternalStatus,
      HandlingUnitProcessStatus,

      // EWM fields - Organizational data

      EWMStorageType                 as StorageType,
      StorageSection,
      EWMStorageBin                  as StorageBin,
      EWMResource,

      // EWM fields - Handling Unit specifics

      EWMConsolidationGroup,
      HandlingUnitIsClosed,
      HandlingUnitType,

      // EWM fields - Process storage control

      ExternalStorageProcessStep,
      EWMHUProcessStepIsCompleted,
      StorageProcess,

      // EWM fields - Handling Unit process status

      EWMHandlingUnitIsPlanned,
      EWMHandlingUnitIsInStock,
      EWMHandlingUnitIsUnloaded,
      EWMHandlingUnitIsLoaded,
      EWMTranspUnitIsAssigned,

      // EWM fields - Handling Unit blocked status

      EWMHUContentChangeIsBlocked,
      EWMHUMovementChangeIsBlocked,
      EWMHUAttributeChangeIsBlocked,
      EWMHUPostingChangeIsBlocked,
      EWMHUIsBlockedByCustoms,
      EWMHUIsPackedForShipping,

      // EWM fields - Other

      EntitledToDisposeParty,
      HandlingUnitLogicalPosition,
      HandlingUnitPackingGroup,
      HazardousSubstanceIndicator,
      WorkloadIdentification,
      @Semantics.quantity.unitOfMeasure: 'WeightUnit'
      HandlingUnitWeightTolerance,
      @Semantics.quantity.unitOfMeasure: 'VolumeUnit'
      HandlingUnitVolumeTolerance,

      // EWM fields - Maximum dimensions

      @Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
      HandlingUnitMaxLength,
      @Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
      HandlingUnitMaxWidth,
      @Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
      HandlingUnitMaxHeight,
      @Semantics.unitOfMeasure: true
      HandlingUnitMaxDimensionUnit,

      // EWM fields - Origin

      HandlingUnitOrigin,

      // Associations

      _WeightUnit,
      _WeightUnitTare,
      _VolumeUnit,
      _VolumeUnitTare,
      _UnitOfMeasureDimension
}

union all

  // Part II: Handling Units managed by EWM

  select from P_EWM_HandlingUnitHeader08
  
  association [0..1] to I_UnitOfMeasure as _WeightUnit             on _WeightUnit.UnitOfMeasure = P_EWM_HandlingUnitHeader08.WeightUnit
  association [0..1] to I_UnitOfMeasure as _WeightUnitTare         on _WeightUnitTare.UnitOfMeasure = P_EWM_HandlingUnitHeader08.HandlingUnitTareWeightUnit
  association [0..1] to I_UnitOfMeasure as _VolumeUnit             on _VolumeUnit.UnitOfMeasure = P_EWM_HandlingUnitHeader08.VolumeUnit
  association [0..1] to I_UnitOfMeasure as _VolumeUnitTare         on _VolumeUnitTare.UnitOfMeasure = P_EWM_HandlingUnitHeader08.HandlingUnitTareVolumeUnit
  association [0..1] to I_UnitOfMeasure as _UnitOfMeasureDimension on _UnitOfMeasureDimension.UnitOfMeasure = P_EWM_HandlingUnitHeader08.EWMDimensionUnit

{
      // Key

  key HandlingUnitNumber                                                                        as HandlingUnitExternalID,
  @Environment.sql.passValue: true
  key EWMWarehouse                                                                              as Warehouse,

      // Identifier fields

      bintohex(EWMHandlingUnitExternalUUID)                                                     as HandlingUnitCharUUID,
      bintohex(HandlingUnitUUID)                                                                as HandlingUnitInternalCharUUID,
      cast(' ' as exida preserving type)                                                        as HandlingUnitExternalIdType,
      cast('          ' as venum preserving type)                                               as HandlingUnitInternalID,
      cast('0000000000000000000000' as guid_vekp)                                               as SourceHandlingUnitUUID,
      cast('00000000000000000000' as exidv2 )                                                   as HandlingUnitSecondExternalId,

      // Identifier fields of parent

      cast('          ' as uevel preserving type)                                               as HandlingUnitLowerLevelRefer,
      ParentHandlingUnitNumber,
      EWMHghstLvlHandlingUnitNumber                                                             as HighestLevelHandlingUnitNumber,

      // Organizational data - for EWM HUs Plant and Storage location are not filled

      // An EWM HU can contain items from different Plants

      cast('    ' as hum_werks preserving type)                                                 as Plant,
      cast('    ' as hum_lgort preserving type)                                                 as StorageLocation,
      cast('    ' as vstel preserving type)                                                     as ShippingPoint,

      // Packaging data

      PackagingMaterial                                                                         as PackagingMaterial,
      PackagingMaterialType                                                                     as PackagingMaterialType,
      cast('                      ' as pl_packnr_conv preserving type)                          as PackingInstruction,

      // Weights

      GrossWeight,
      NetWeight,
      HandlingUnitMaxWeight,
      WeightUnit,
      HandlingUnitTareWeight,
      HandlingUnitTareWeightUnit,

      // Volumes

      GrossVolume,
      HandlingUnitNetVolume,
      HandlingUnitMaxVolume,
      VolumeUnit,
      HandlingUnitTareVolume,
      HandlingUnitTareVolumeUnit,

      // Dimensions

      HandlingUnitLength,
      HandlingUnitWidth,
      HandlingUnitHeight,
      EWMDimensionUnit                                                                          as UnitOfMeasureDimension,

      // Reference object

      case EWMDeliveryDocumentCategory
        when 'PDO' then '01'  // Outbound Delivery

        when 'PDI' then '03'  // Inbound Delivery

        else cast('  ' as vpobj preserving type)
      end                                                                                       as HandlingUnitPackingObjectType,
      ReferenceDocumentNumber                                                                   as HandlingUnitReferenceDocument,
      HandlingUnitHasCrossRefDoc,
      
      //Delivery

      ShipToParty,

      // Change log

      CreatedByUser                                                                             as CreatedByUser,
      CreationDateTime                                                                          as CreationDateTime,
      case when LastChangedByUser is initial then CreatedByUser else LastChangedByUser end      as LastChangedByUser,
      case when LastChangeDateTime is initial then CreationDateTime else LastChangeDateTime end as LastChangeDateTime,

      // Status

      cast('    ' as hu_status preserving type)                                                 as HandlingUnitInternalStatus,
      HandlingUnitStatus                                                                        as HandlingUnitProcessStatus,

      // EWM fields - Organizational data

      EWMStorageType                                                                            as StorageType,
      EWMStorageBinSection                                                                      as StorageSection,
      EWMStorageBin                                                                             as StorageBin,
      EWMResource,

      // EWM fields - Handling Unit specifics

      EWMConsolidationGroup,
      HandlingUnitIsClosed,
      HandlingUnitType,

      // EWM fields - Process storage control

      ExternalStorageProcessStep,
      EWMHUProcessStepIsCompleted,
      StorageProcess,

      // EWM fields - Handling Unit process status

      EWMHandlingUnitIsPlanned,
      EWMHandlingUnitIsInStock,
      EWMHandlingUnitIsUnloaded,
      EWMHandlingUnitIsLoaded,
      EWMTranspUnitIsAssigned,

      // EWM fields - Handling Unit blocked status

      EWMHUContentChangeIsBlocked,
      EWMHUMovementChangeIsBlocked,
      EWMHUAttributeChangeIsBlocked,
      EWMHUPostingChangeIsBlocked,
      EWMHUIsBlockedByCustoms,
      EWMHUIsPackedForShipping,

      // EWM fields - Other

      EntitledToDisposeParty,
      HandlingUnitLogicalPosition,
      HandlingUnitPackingGroup,
      HazardousSubstanceIndicator,
      WorkloadIdentification,
      HandlingUnitWeightTolerance,
      HandlingUnitVolumeTolerance,

      // EWM fields - Maximum dimensions

      HandlingUnitMaxLength,
      HandlingUnitMaxWidth,
      HandlingUnitMaxHeight,
      HandlingUnitMaxDimensionUnit,

      // EWM fields - Origin

      cast('EWM' as lo_hu_hu_origin preserving type)                                            as HandlingUnitOrigin,

      _WeightUnit,
      _WeightUnitTare,
      _VolumeUnit,
      _VolumeUnitTare,
      _UnitOfMeasureDimension
}
where
      HandlingUnitStatus != 'D'
  and EWMWarehouse       is not initial