I_HandlingUnitItemCombined

@AbapCatalog.sqlViewName: 'IHANDLINGUNITICO'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true

@ClientHandling.algorithm: #SESSION_VARIABLE

@Metadata.ignorePropagatedAnnotations: true

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

@ObjectModel: {
    usageType: {
      dataClass:       #MIXED,
      serviceQuality:  #C,
      sizeCategory:    #XL
    }
}
@VDM: {
    viewType: #COMPOSITE,
    lifecycle.contract.type: #SAP_INTERNAL_API
}

@EndUserText.label: 'Handling Unit Items data from LO and EWM'

define view I_HandlingUnitItemCombined
  as select from I_HandlingUnitItem

  association [1..1] to I_HandlingUnitHeaderCombined as _HandlingUnitHeader           on  $projection.HandlingUnitInternalID = _HandlingUnitHeader.HandlingUnitInternalID
  association [1..1] to P_HandlingUnitItemAsGuid     as _HandlingUnitItemGuid         on  $projection.HandlingUnitInternalID = _HandlingUnitItemGuid.HandlingUnitInternalID
                                                                                      and $projection.HandlingUnitItem       = _HandlingUnitItemGuid.HandlingUnitItem

  association [0..1] to I_UnitOfMeasure              as _HandlingUnitQuantityUnit     on  $projection.HandlingUnitQuantityUnit = _HandlingUnitQuantityUnit.UnitOfMeasure
  association [0..1] to I_UnitOfMeasure              as _HandlingUnitAltUnitOfMeasure on  $projection.HandlingUnitAltUnitOfMeasure = _HandlingUnitAltUnitOfMeasure.UnitOfMeasure

  association [0..*] to I_HndlgUnitItemSrlNoCombined as _HandlingUnitSerialNumber     on  $projection.handlingunitexternalid = _HandlingUnitSerialNumber.HandlingUnitExternalID
                                                                                      and $projection.HandlingUnitItem       = _HandlingUnitSerialNumber.HandlingUnitItem
                                                                                      and $projection.warehouse              = _HandlingUnitSerialNumber.Warehouse
                                                                                      and $projection.StockItemUUID          = _HandlingUnitSerialNumber.StockItemUUID

{
  key _HandlingUnitHeader.HandlingUnitExternalID,
  key _HandlingUnitHeader.Warehouse,
  key cast(hextobin(_HandlingUnitItemGuid.HandlingUnitItemUUID) as /lime/guid_stock preserving type) as StockItemUUID,

      HandlingUnitItem,
      HandlingUnitInternalID,

      HandlingUnitTypeOfContent,

      HandlingUnitNestedInternalID,

      HandlingUnitReferenceDocument,
      case
      when HandlingUnitRefDocumentItem is not initial
      //Casting to minimum type numc10 given by WarehouseManagement

      then cast(concat('0000', HandlingUnitRefDocumentItem) as numc10)
      else '' end                                                                                    as HandlingUnitRefDocumentItem,

      @Semantics.quantity.unitOfMeasure: 'HandlingUnitQuantityUnit'
      HandlingUnitQuantity,
      @Semantics.unitOfMeasure:true
      HandlingUnitQuantityUnit,
      @Semantics.unitOfMeasure:true
      HandlingUnitAltUnitOfMeasure,

      Material,
      _Material._Text[1: Language=$session.system_language].MaterialName,

      Batch,
      Plant,
      StorageLocation,

      ShelfLifeExpirationDate,
      HandlingUnitGoodsReceiptDate,
      cast('   ' as /scwm/de_coo preserving type)                                                    as CountryOfOrigin,

      HandlingUnitNrOfAuxPackgMat,

      HandlingUnitNumberOfSerialNumb,
      SerialNumberProfile,

      //Associations

      _HandlingUnitAltUnitOfMeasure,
      _HandlingUnitQuantityUnit,
      _HandlingUnitSerialNumber

}
where
      HandlingUnitTypeOfContent                  = '1' // we delivery only product items, since EWM does only this

  and _HandlingUnitHeader.HandlingUnitExternalID is not null

union all select from P_EWM_HandlingUnitItem05 as huitm
association [0..1] to I_UnitOfMeasure              as _HandlingUnitQuantityUnit     on  $projection.HandlingUnitQuantityUnit = _HandlingUnitQuantityUnit.UnitOfMeasure
association [0..1] to I_UnitOfMeasure              as _HandlingUnitAltUnitOfMeasure on  $projection.HandlingUnitAltUnitOfMeasure = _HandlingUnitAltUnitOfMeasure.UnitOfMeasure

association [0..*] to I_HndlgUnitItemSrlNoCombined as _HandlingUnitSerialNumber     on  $projection.HandlingUnitExternalID = _HandlingUnitSerialNumber.HandlingUnitExternalID
                                                                                    and $projection.HandlingUnitItem       = _HandlingUnitSerialNumber.HandlingUnitItem
                                                                                    and $projection.Warehouse              = _HandlingUnitSerialNumber.Warehouse
                                                                                    and $projection.StockItemUUID          = _HandlingUnitSerialNumber.StockItemUUID

{
  key huitm.HandlingUnitNumber                                                                                                as HandlingUnitExternalID,
  key huitm.EWMWarehouse                                                                                                      as Warehouse,
  key huitm.StockItemUUID                                                                                                     as StockItemUUID,

      cast('000000' as vepos preserving type)                                                                                 as HandlingUnitItem,
      cast('          ' as venum preserving type)                                                                             as HandlingUnitInternalID,
      
      // at the moment only 1=Material is supported

      cast('1' as velin)                                                                                                      as HandlingUnitTypeOfContent,

      cast('           ' as unvel )                                                                                           as HandlingUnitNestedInternalID,

      cast(huitm.EWMRefDeliveryDocumentNumber as vbeln)                                                                       as HandlingUnitReferenceDocument,
      cast(huitm.EWMRefDeliveryDocumentItem as numc10)                                                                        as HandlingUnitRefDocumentItem,

      @Semantics.quantity.unitOfMeasure: 'HandlingUnitBaseUnitOfMeasure'
      huitm.HandlingUnitQuantity                                                                                              as HandlingUnitQuantity,
      @Semantics.unitOfMeasure: true
      huitm.HandlingUnitBaseUnitOfMeasure                                                                                     as HandlingUnitQuantityUnit,
      @Semantics.unitOfMeasure: true
      huitm.HandlingUnitAltUnitOfMeasure                                                                                      as HandlingUnitAltUnitOfMeasure,

      // TBD EWM to provide

      huitm.Product                                                                                                           as Material,
      huitm.ProductName                                                                                                       as MaterialName,

      huitm.Batch                                                                                                             as Batch,

      huitm.Plant                                                                                                             as Plant,
      huitm.StorageLocation                                                                                                   as StorageLocation,

      huitm.ShelfLifeExpirationDate                                                                                           as ShelfLifeExpirationDate,

      tstmp_to_dats( huitm.GoodsReceiptUTCDateTime, abap_system_timezone( $session.client,'NULL' ),  $session.client,'NULL' ) as HandlingUnitGoodsReceiptDate,

      huitm.CountryOfOrigin                                                                                                   as CountryOfOrigin,

      cast('0' as veanz )                                                                                                     as HandlingUnitNrOfAuxPackgMat,

      cast( HandlingUnitNumberOfSerialNumb as anzsn )                                                                         as HandlingUnitNumberOfSerialNumb,
      cast( SerialNumberProfile as serail preserving type)                                                                    as SerialNumberProfile,

      // Associations

      _HandlingUnitAltUnitOfMeasure,
      _HandlingUnitQuantityUnit,
      _HandlingUnitSerialNumber

}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_HANDLINGUNITHEADERCOMBINED",
"I_HANDLINGUNITITEM",
"I_MATERIAL",
"I_MATERIALTEXT",
"P_EWM_HANDLINGUNITITEM05",
"P_HANDLINGUNITITEMASGUID"
],
"ASSOCIATED":
[
"I_HANDLINGUNITHEADERCOMBINED",
"I_HNDLGUNITITEMSRLNOCOMBINED",
"I_UNITOFMEASURE",
"P_HANDLINGUNITITEMASGUID"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/