@AbapCatalog: {
sqlViewName: 'IHANDLINGUNITHCO',
compiler.compareFilter: 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 Units - combined data from LO and EWM storages'
@AbapCatalog.preserveKey: true
// 1). select LO HUs for non-EWM and non-WM storage locations
define view I_HandlingUnitHeaderCombined
as select from I_HandlingUnitHeader as vekp
association [0..1] to I_UnitOfMeasure as _WeightUnit on _WeightUnit.UnitOfMeasure = vekp.WeightUnit
association [0..1] to I_UnitOfMeasure as _WeightUnitTare on _WeightUnitTare.UnitOfMeasure = vekp.HandlingUnitTareWeightUnit
association [0..1] to I_UnitOfMeasure as _VolumeUnit on _VolumeUnit.UnitOfMeasure = vekp.VolumeUnit
association [0..1] to I_UnitOfMeasure as _VolumeUnitTare on _VolumeUnitTare.UnitOfMeasure = vekp.HandlingUnitTareVolumeUnit
association [0..1] to I_UnitOfMeasure as _UnitOfMeasureDimension on _UnitOfMeasureDimension.UnitOfMeasure = vekp.UnitOfMeasureDimension
association [0..1] to I_HandlingUnitHeader as _HandlingUnitParentHdr on _HandlingUnitParentHdr.HandlingUnitInternalID = vekp.HandlingUnitLowerLevelRefer
{
key HandlingUnitExternalID,
key cast(' ' as /scwm/lgnum preserving type) as Warehouse,
HandlingUnitCharUUID,
HandlingUnitCharUUID as HandlingUnitInternalCharUUID,
HandlingUnitExternalIdType,
Plant,
StorageLocation,
ShippingPoint,
HandlingUnitInternalID,
HandlingUnitLowerLevelRefer,
_HandlingUnitParentHdr.HandlingUnitExternalID as ParentHandlingUnitNumber,
cast( ' ' as exidv preserving type ) as HighestLevelHandlingUnitNumber,
PackagingMaterial,
PackagingMaterialType,
PackingInstruction,
@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,
@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,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitLength,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitWidth,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitHeight,
@Semantics.unitOfMeasure:true
UnitOfMeasureDimension,
HandlingUnitPackingObjectType,
HandlingUnitPackingObjectKey as HandlingUnitReferenceDocument,
// EWM specific field for Cross HUs
cast( '' as abap_boolean preserving type ) as HandlingUnitHasCrossRefDoc,
CreatedByUser,
@Semantics.systemDateTime.createdAt: true
dats_tims_to_tstmp( CreationDate,
CreationTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' ) as CreationDateTime,
// always fill LastChanged timestamp/user, in particular to use single timestamp field as etag value
case
when LastChangedByUser is initial then
CreatedByUser
else
LastChangedByUser end as LastChangedByUser,
@Semantics.systemDateTime.lastChangedAt: true
case
when LastChangeDate is initial then
dats_tims_to_tstmp( CreationDate,
CreationTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' )
else
dats_tims_to_tstmp( LastChangeDate,
LastChangeTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' )
end as LastChangeDateTime,
HandlingUnitInternalStatus,
case HandlingUnitInternalStatus
when '0001' then 'A' // planned
when '0010' then 'A' // implemented - stock not posted as goods receipt
when '0015' then 'B' // visually checked
when '0020' then 'B' // stock at nonHU SLoc
when '0030' then 'B' // stock at HU SLoc
when '0031' then 'B' // Physical inventory
when '0040' then 'T' // in transit
when '0050' then 'C' // Goods issue posted - shipped
when '0060' then 'D' // deleted/obsolete HU
else cast(' ' as hu_status_api preserving type)
end as HandlingUnitProcessStatus,
SourceHandlingUnitUUID,
HandlingUnitSecondExternalId,
HandlingUnitSlotNumber,
HndlgUnitRlvnceForJIT,
// EWM specific fields
cast(' ' as /scwm/lgtyp preserving type) as StorageType,
cast(' ' as /scwm/lgber preserving type) as StorageSection,
cast(' ' as /scwm/lgpla preserving type) as StorageBin,
cast(' ' as /scwm/de_rsrc preserving type ) as EWMResource,
cast(' ' as /scwm/de_dstgrp preserving type) as EWMConsolidationGroup,
cast('' as /scwm/de_closed_pm preserving type ) as HandlingUnitIsClosed,
cast(' ' as /scwm/de_hutyp preserving type) as HandlingUnitType,
cast(' ' as /scwm/de_procs preserving type ) as ExternalStorageProcessStep,
cast('' as /scwm/de_copst preserving type ) as EWMHUProcessStepIsCompleted,
cast(' ' as /scwm/de_prces preserving type ) as StorageProcess,
cast('' as ewm_de_hu_is_planned preserving type ) as EWMHandlingUnitIsPlanned,
cast('' as ewm_de_hu_is_in_stock preserving type ) as EWMHandlingUnitIsInStock,
cast('' as ewm_de_hu_is_unloaded preserving type ) as EWMHandlingUnitIsUnloaded,
cast('' as ewm_de_hu_is_loaded preserving type ) as EWMHandlingUnitIsLoaded,
cast('' as ewm_de_hu_is_assigned_to_tu preserving type ) as EWMTranspUnitIsAssigned,
cast('' as ewm_de_hu_is_cntnt_change_blck preserving type ) as EWMHUContentChangeIsBlocked,
cast('' as ewm_de_hu_is_movement_blocked preserving type ) as EWMHUMovementChangeIsBlocked,
cast('' as ewm_de_hu_is_attrbt_chng_blck preserving type ) as EWMHUAttributeChangeIsBlocked,
cast('' as ewm_de_hu_is_posting_chng_blck preserving type ) as EWMHUPostingChangeIsBlocked,
cast('' as ewm_de_hu_is_blckd_by_customs preserving type ) as EWMHUIsBlockedByCustoms,
cast('' as ewm_de_hu_is_packed_for_ship preserving type ) as EWMHUIsPackedForShipping,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast( 0 as /scwm/de_maxl) as HandlingUnitMaxLength,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast(0 as /scwm/de_maxb) as HandlingUnitMaxWidth,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast(0 as /scwm/de_maxh) as HandlingUnitMaxHeight,
@Semantics.unitOfMeasure: true
cast(' ' as /scwm/de_maxdim_uom) as HandlingUnitMaxDimensionUnit,
cast('ERP' as lo_hu_hu_origin preserving type) as HandlingUnitOrigin,
cast( ' ' as ewm_de_serialnumber preserving type ) as EWMSrlNmbrForPackagingMaterial,
cast( ' ' as /scwm/de_jit_upd_relevance preserving type ) as EWMHUIsJITUpdateRelevant,
cast( ' ' as /scwm/de_hu_dest_psa preserving type ) as EWMHUDestinationProdnSupArea,
cast( ' ' as /scwm/de_hu_dest_lgpla preserving type ) as EWMHUDestinationStorageBin,
_WeightUnit,
_WeightUnitTare,
_VolumeUnit,
_VolumeUnitTare,
_UnitOfMeasureDimension
}
where
(
HandlingUnitPackingObjectType != '00' //Not assigned to an object.
and HandlingUnitPackingObjectType != '02' //Sales document
and HandlingUnitPackingObjectType != '04' //Shipment
and HandlingUnitPackingObjectType != '05' //Non-Assigned HU
and HandlingUnitPackingObjectType != '06' //Non-Assigned HU
and HandlingUnitPackingObjectType != '21' //Cross-Outbound Delivery
and HandlingUnitPackingObjectType != '23' //Cross-Inbound Delivery
)
and(
Warehouse is initial //only for S/4HANA Cloud! needs to be changed for onPrem
and HandlingUnitInternalStatus != '0060' //deleted
//additional conditions to not show HUs that are managed by EWM
and(
//standalone HUs that are managed externally
(
HandlingUnitStockMgmtType != 'A' //HU in system with IM stock -> HU is managed externally
and HandlingUnitPackingObjectType = '12' //Non-Assigned HU -> standalone HU
)
or(
HandlingUnitPackingObjectType = '09' // Work Order - Finished Product
or HandlingUnitPackingObjectType = '07' // Repititive Manufacturing
)
or(
HandlingUnitPackingObjectType = '01' // Outbound Delivery
or HandlingUnitPackingObjectType = '03' // Inbound Delivery
)
)
)
union all
// 2). select LO HUs for ext. WM storage locations - only for outbound deliveries
select from I_HandlingUnitHeader as vekp_decwhs
join I_ERPWarehouseParameters as whs_param on whs_param.Warehouse = vekp_decwhs.Warehouse
and(
whs_param.HandlingUnitWarehouseType = 'X'
or whs_param.HandlingUnitWarehouseType is initial
)
association [0..1] to I_UnitOfMeasure as _WeightUnit on _WeightUnit.UnitOfMeasure = $projection.WeightUnit
association [0..1] to I_UnitOfMeasure as _WeightUnitTare on _WeightUnitTare.UnitOfMeasure = $projection.HandlingUnitTareWeightUnit
association [0..1] to I_UnitOfMeasure as _VolumeUnit on _VolumeUnit.UnitOfMeasure = $projection.VolumeUnit
association [0..1] to I_UnitOfMeasure as _VolumeUnitTare on _VolumeUnitTare.UnitOfMeasure = $projection.HandlingUnitTareVolumeUnit
association [0..1] to I_UnitOfMeasure as _UnitOfMeasureDimension on _UnitOfMeasureDimension.UnitOfMeasure = $projection.UnitOfMeasureDimension
association [0..1] to I_HandlingUnitHeader as _HandlingUnitParentHdr on _HandlingUnitParentHdr.HandlingUnitInternalID = vekp_decwhs.HandlingUnitLowerLevelRefer
{
key HandlingUnitExternalID,
key cast(' ' as /scwm/lgnum preserving type) as Warehouse,
HandlingUnitCharUUID,
HandlingUnitCharUUID as HandlingUnitInternalCharUUID,
HandlingUnitExternalIdType,
Plant,
StorageLocation,
ShippingPoint,
HandlingUnitInternalID,
HandlingUnitLowerLevelRefer,
_HandlingUnitParentHdr.HandlingUnitExternalID as ParentHandlingUnitNumber,
cast( ' ' as exidv preserving type ) as HighestLevelHandlingUnitNumber,
PackagingMaterial,
PackagingMaterialType,
PackingInstruction,
@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,
@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,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitLength,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitWidth,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitHeight ,
@Semantics.unitOfMeasure:true
UnitOfMeasureDimension,
HandlingUnitPackingObjectType,
HandlingUnitPackingObjectKey as HandlingUnitReferenceDocument,
// EWM specific field for Cross HUs
cast( '' as abap_boolean preserving type ) as HandlingUnitHasCrossRefDoc,
CreatedByUser,
@Semantics.systemDateTime.createdAt: true
dats_tims_to_tstmp( CreationDate,
CreationTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' ) as CreationDateTime,
// always fill LastChanged timestamp/user, in particular to use single timestamp field as etag value
case
when LastChangedByUser is initial then
CreatedByUser
else
LastChangedByUser end as LastChangedByUser,
@Semantics.systemDateTime.lastChangedAt: true
case
when LastChangeDate is initial then
dats_tims_to_tstmp( CreationDate,
CreationTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' )
else
dats_tims_to_tstmp( LastChangeDate,
LastChangeTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' )
end as LastChangeDateTime,
HandlingUnitInternalStatus,
case HandlingUnitInternalStatus
when '0001' then 'A' // planned
when '0010' then 'A' // implemented - stock not posted as goods receipt
when '0015' then 'B' // visually checked
when '0020' then 'B' // stock at nonHU SLoc
when '0030' then 'B' // stock at HU SLoc
when '0031' then 'B' // Physical inventory
when '0040' then 'T' // in transit
when '0050' then 'C' // Goods issue posted - shipped
when '0060' then 'D' // deleted/obsolete HU
else cast(' ' as hu_status_api preserving type)
end as HandlingUnitProcessStatus,
SourceHandlingUnitUUID,
HandlingUnitSecondExternalId,
HandlingUnitSlotNumber,
HndlgUnitRlvnceForJIT,
// EWM specific fields
cast(' ' as /scwm/lgtyp preserving type) as StorageType,
cast(' ' as /scwm/lgber preserving type) as StorageSection,
cast(' ' as /scwm/lgpla preserving type) as StorageBin,
cast(' ' as /scwm/de_rsrc preserving type ) as EWMResource,
cast(' ' as /scwm/de_dstgrp preserving type) as EWMConsolidationGroup,
cast('' as /scwm/de_closed_pm preserving type ) as HandlingUnitIsClosed,
cast(' ' as /scwm/de_hutyp preserving type) as HandlingUnitType,
cast(' ' as /scwm/de_procs preserving type ) as ExternalStorageProcessStep,
cast('' as /scwm/de_copst preserving type ) as EWMHUProcessStepIsCompleted,
cast(' ' as /scwm/de_prces preserving type ) as StorageProcess,
cast('' as ewm_de_hu_is_planned preserving type ) as EWMHandlingUnitIsPlanned,
cast('' as ewm_de_hu_is_in_stock preserving type ) as EWMHandlingUnitIsInStock,
cast('' as ewm_de_hu_is_unloaded preserving type ) as EWMHandlingUnitIsUnloaded,
cast('' as ewm_de_hu_is_loaded preserving type ) as EWMHandlingUnitIsLoaded,
cast('' as ewm_de_hu_is_assigned_to_tu preserving type ) as EWMTranspUnitIsAssigned,
cast('' as ewm_de_hu_is_cntnt_change_blck preserving type ) as EWMHUContentChangeIsBlocked,
cast('' as ewm_de_hu_is_movement_blocked preserving type ) as EWMHUMovementChangeIsBlocked,
cast('' as ewm_de_hu_is_attrbt_chng_blck preserving type ) as EWMHUAttributeChangeIsBlocked,
cast('' as ewm_de_hu_is_posting_chng_blck preserving type ) as EWMHUPostingChangeIsBlocked,
cast('' as ewm_de_hu_is_blckd_by_customs preserving type ) as EWMHUIsBlockedByCustoms,
cast('' as ewm_de_hu_is_packed_for_ship preserving type ) as EWMHUIsPackedForShipping,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast( 0 as /scwm/de_maxl) as HandlingUnitMaxLength,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast(0 as /scwm/de_maxb) as HandlingUnitMaxWidth,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast(0 as /scwm/de_maxh) as HandlingUnitMaxHeight,
@Semantics.unitOfMeasure: true
cast(' ' as /scwm/de_maxdim_uom) as HandlingUnitMaxDimensionUnit,
cast('ERP' as lo_hu_hu_origin preserving type) as HandlingUnitOrigin,
cast( ' ' as ewm_de_serialnumber preserving type ) as EWMSrlNmbrForPackagingMaterial,
cast( ' ' as /scwm/de_jit_upd_relevance preserving type ) as EWMHUIsJITUpdateRelevant,
cast( ' ' as /scwm/de_hu_dest_psa preserving type ) as EWMHUDestinationProdnSupArea,
cast( ' ' as /scwm/de_hu_dest_lgpla preserving type ) as EWMHUDestinationStorageBin,
_WeightUnit,
_WeightUnitTare,
_VolumeUnit,
_VolumeUnitTare,
_UnitOfMeasureDimension
}
where
vekp_decwhs.Warehouse is not initial
and(
HandlingUnitPackingObjectType != '00' //Not assigned to an object.
or HandlingUnitPackingObjectType != '02' //Sales document
or HandlingUnitPackingObjectType != '04' //Shipment
or HandlingUnitPackingObjectType != '05' //Non-Assigned HU
or HandlingUnitPackingObjectType != '06' //Non-Assigned HU
or HandlingUnitPackingObjectType != '21' //Cross-Outbound Delivery
or HandlingUnitPackingObjectType != '23' //Cross-Inbound Delivery
)
and HandlingUnitInternalStatus <> '0060'
//additional condition to not show HUs that have been moved to a Warehouse
//-> not here as this query is interested in WH HUs
union all
// 3). select LO HUs for EWM storage locations - only for outbound deliveries
// - for inbound and standalone HUs EWM is master system,
// so for last ones we take them always from EWM and ignore LO ones
select from I_HandlingUnitHeader as vekp_ewm
join I_ERPWarehouseParameters as whs_param on whs_param.Warehouse = vekp_ewm.Warehouse
and whs_param.HandlingUnitWarehouseType = 'E'
join I_ERPWarehouseToEWMWarehouse as ewm_whs on ewm_whs.WarehouseERP = vekp_ewm.Warehouse
association [0..1] to I_UnitOfMeasure as _WeightUnit on _WeightUnit.UnitOfMeasure = vekp_ewm.WeightUnit
association [0..1] to I_UnitOfMeasure as _WeightUnitTare on _WeightUnitTare.UnitOfMeasure = vekp_ewm.HandlingUnitTareWeightUnit
association [0..1] to I_UnitOfMeasure as _VolumeUnit on _VolumeUnit.UnitOfMeasure = vekp_ewm.VolumeUnit
association [0..1] to I_UnitOfMeasure as _VolumeUnitTare on _VolumeUnitTare.UnitOfMeasure = vekp_ewm.HandlingUnitTareVolumeUnit
association [0..1] to I_UnitOfMeasure as _UnitOfMeasureDimension on _UnitOfMeasureDimension.UnitOfMeasure = vekp_ewm.UnitOfMeasureDimension
association [0..1] to I_HandlingUnitHeader as _HandlingUnitParentHdr on _HandlingUnitParentHdr.HandlingUnitInternalID = vekp_ewm.HandlingUnitLowerLevelRefer
{
key HandlingUnitExternalID,
key cast(' ' as /scwm/lgnum preserving type) as Warehouse,
HandlingUnitCharUUID,
HandlingUnitCharUUID as HandlingUnitInternalCharUUID,
HandlingUnitExternalIdType,
Plant,
StorageLocation,
ShippingPoint,
HandlingUnitInternalID,
HandlingUnitLowerLevelRefer,
_HandlingUnitParentHdr.HandlingUnitExternalID as ParentHandlingUnitNumber,
cast( ' ' as exidv preserving type ) as HighestLevelHandlingUnitNumber,
PackagingMaterial,
PackagingMaterialType,
PackingInstruction,
@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,
@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,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitLength,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitWidth,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasureDimension'
HandlingUnitHeight,
@Semantics.unitOfMeasure:true
UnitOfMeasureDimension,
HandlingUnitPackingObjectType,
HandlingUnitPackingObjectKey as HandlingUnitReferenceDocument,
// EWM specific field for Cross HUs
cast( '' as abap_boolean preserving type ) as HandlingUnitHasCrossRefDoc,
CreatedByUser,
@Semantics.systemDateTime.createdAt: true
dats_tims_to_tstmp( CreationDate,
CreationTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' ) as CreationDateTime,
// always fill LastChanged timestamp/user, in particular to use single timestamp field as etag value
case
when LastChangedByUser is initial then
CreatedByUser
else
LastChangedByUser end as LastChangedByUser,
@Semantics.systemDateTime.lastChangedAt: true
case
when LastChangeDate is initial then
dats_tims_to_tstmp( CreationDate,
CreationTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' )
else
dats_tims_to_tstmp( LastChangeDate,
LastChangeTime,
abap_system_timezone( $session.client, 'NULL'),
$session.client,
'NULL' )
end as LastChangeDateTime,
HandlingUnitInternalStatus,
case HandlingUnitInternalStatus
when '0001' then 'A' // planned
when '0010' then 'A' // implemented - stock not posted as goods receipt
when '0015' then 'B' // visually checked
when '0020' then 'B' // stock at nonHU SLoc
when '0030' then 'B' // stock at HU SLoc
when '0031' then 'B' // Physical inventory
when '0040' then 'T' // in transit
when '0050' then 'C' // Goods issue posted - shipped
when '0060' then 'D' // deleted/obsolete HU
else cast(' ' as hu_status_api preserving type)
end as HandlingUnitProcessStatus,
SourceHandlingUnitUUID,
HandlingUnitSecondExternalId,
HandlingUnitSlotNumber,
HndlgUnitRlvnceForJIT,
// EWM specific fields
cast(' ' as /scwm/lgtyp preserving type) as StorageType,
cast(' ' as /scwm/lgber preserving type) as StorageSection,
cast(' ' as /scwm/lgpla preserving type) as StorageBin,
cast(' ' as /scwm/de_rsrc preserving type ) as EWMResource,
cast(' ' as /scwm/de_dstgrp preserving type) as EWMConsolidationGroup,
cast('' as /scwm/de_closed_pm preserving type ) as HandlingUnitIsClosed,
cast(' ' as /scwm/de_hutyp preserving type) as HandlingUnitType,
cast(' ' as /scwm/de_procs preserving type ) as ExternalStorageProcessStep,
cast('' as /scwm/de_copst preserving type ) as EWMHUProcessStepIsCompleted,
cast(' ' as /scwm/de_prces preserving type ) as StorageProcess,
cast('' as ewm_de_hu_is_planned preserving type ) as EWMHandlingUnitIsPlanned,
cast('' as ewm_de_hu_is_in_stock preserving type ) as EWMHandlingUnitIsInStock,
cast('' as ewm_de_hu_is_unloaded preserving type ) as EWMHandlingUnitIsUnloaded,
cast('' as ewm_de_hu_is_loaded preserving type ) as EWMHandlingUnitIsLoaded,
cast('' as ewm_de_hu_is_assigned_to_tu preserving type ) as EWMTranspUnitIsAssigned,
cast('' as ewm_de_hu_is_cntnt_change_blck preserving type ) as EWMHUContentChangeIsBlocked,
cast('' as ewm_de_hu_is_movement_blocked preserving type ) as EWMHUMovementChangeIsBlocked,
cast('' as ewm_de_hu_is_attrbt_chng_blck preserving type ) as EWMHUAttributeChangeIsBlocked,
cast('' as ewm_de_hu_is_posting_chng_blck preserving type ) as EWMHUPostingChangeIsBlocked,
cast('' as ewm_de_hu_is_blckd_by_customs preserving type ) as EWMHUIsBlockedByCustoms,
cast('' as ewm_de_hu_is_packed_for_ship preserving type ) as EWMHUIsPackedForShipping,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast( 0 as /scwm/de_maxl) as HandlingUnitMaxLength,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast(0 as /scwm/de_maxb) as HandlingUnitMaxWidth,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
cast(0 as /scwm/de_maxh) as HandlingUnitMaxHeight,
@Semantics.unitOfMeasure: true
cast(' ' as /scwm/de_maxdim_uom) as HandlingUnitMaxDimensionUnit,
cast('ERP' as lo_hu_hu_origin preserving type) as HandlingUnitOrigin,
cast( ' ' as ewm_de_serialnumber preserving type ) as EWMSrlNmbrForPackagingMaterial,
cast( ' ' as /scwm/de_jit_upd_relevance preserving type ) as EWMHUIsJITUpdateRelevant,
cast( ' ' as /scwm/de_hu_dest_psa preserving type ) as EWMHUDestinationProdnSupArea,
cast( ' ' as /scwm/de_hu_dest_lgpla preserving type ) as EWMHUDestinationStorageBin,
_WeightUnit,
_WeightUnitTare,
_VolumeUnit,
_VolumeUnitTare,
_UnitOfMeasureDimension
}
where
vekp_ewm.Warehouse is not initial
and(
HandlingUnitPackingObjectType = '01'
or // Outbound Delivery
HandlingUnitPackingObjectType = '21'
) // Cross-Outbound Delivery
and HandlingUnitInternalStatus <> '0060'
//TO-DO Check if these are also sent to decentral EWM
// or HandlingUnitPackingObjectType = '06'
// or HandlingUnitPackingObjectType = '07'
// or HandlingUnitPackingObjectType = '08'
// or HandlingUnitPackingObjectType = '09'
// 4). select EWM HUs
union all
select from P_EWM_HandlingUnitHeader08 as huhdr
association [0..1] to I_UnitOfMeasure as _WeightUnit on _WeightUnit.UnitOfMeasure = huhdr.WeightUnit
association [0..1] to I_UnitOfMeasure as _WeightUnitTare on _WeightUnitTare.UnitOfMeasure = huhdr.HandlingUnitTareWeightUnit
association [0..1] to I_UnitOfMeasure as _VolumeUnit on _VolumeUnit.UnitOfMeasure = huhdr.VolumeUnit
association [0..1] to I_UnitOfMeasure as _VolumeUnitTare on _VolumeUnitTare.UnitOfMeasure = huhdr.HandlingUnitTareVolumeUnit
association [0..1] to I_UnitOfMeasure as _UnitOfMeasureDimension on _UnitOfMeasureDimension.UnitOfMeasure = huhdr.EWMDimensionUnit
{
key huhdr.HandlingUnitNumber as HandlingUnitExternalID,
key huhdr.EWMWarehouse as Warehouse,
bintohex(huhdr.EWMHandlingUnitExternalUUID) as HandlingUnitCharUUID,
bintohex(huhdr.HandlingUnitUUID) as HandlingUnitInternalCharUUID,
cast(' ' as exida preserving type) as HandlingUnitExternalIdType,
// cast(' ' as lgnum preserving type) as WarehouseERP,
// for EWM HUs Plant and Storage locations are not filled (since 1 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,
cast(' ' as venum preserving type) as HandlingUnitInternalID,
cast( ParentHandlingUnitNumber as char10 ) as HandlingUnitLowerLevelRefer,
huhdr.ParentHandlingUnitNumber,
huhdr.EWMHghstLvlHandlingUnitNumber as HighestLevelHandlingUnitNumber,
huhdr.PackagingMaterial as PackagingMaterial,
huhdr.PackagingMaterialType as PackagingMaterialType,
cast(' ' as pl_packnr_conv preserving type) as PackingInstruction,
huhdr.GrossWeight,
huhdr.NetWeight,
huhdr.HandlingUnitMaxWeight,
huhdr.WeightUnit,
huhdr.HandlingUnitTareWeight,
huhdr.HandlingUnitTareWeightUnit,
// huhdr.HandlingUnitWeightTolerance,
huhdr.GrossVolume,
huhdr.HandlingUnitNetVolume,
huhdr.HandlingUnitMaxVolume,
huhdr.VolumeUnit,
huhdr.HandlingUnitTareVolume,
// huhdr.HandlingUnitVolumeTolerance,
huhdr.HandlingUnitTareVolumeUnit,
cast( huhdr.HandlingUnitLength as laeng ) as HandlingUnitLength,
cast( huhdr.HandlingUnitWidth as breit ) as HandlingUnitWidth,
cast( huhdr.HandlingUnitHeight as hoehe ) as HandlingUnitHeight,
huhdr.EWMDimensionUnit as UnitOfMeasureDimension,
case EWMDeliveryDocumentCategory
when 'PDO' then '01' // Outbound Delivery
when 'PDI' then '03' // Inbound Delivery
else cast(' ' as vpobj preserving type)
end as HandlingUnitPackingObjectType,
cast( ltrim( ReferenceDocumentNumber, '0' ) as vpobjkey ) as HandlingUnitReferenceDocument,
HandlingUnitHasCrossRefDoc,
huhdr.CreatedByUser as CreatedByUser,
@Semantics.systemDateTime.createdAt: true
huhdr.CreationDateTime as CreationDateTime,
case
when huhdr.LastChangedByUser is initial then
huhdr.CreatedByUser
else
huhdr.LastChangedByUser end as LastChangedByUser,
@Semantics.systemDateTime.lastChangedAt: true
case
when huhdr.LastChangeDateTime is initial then
huhdr.CreationDateTime
else
huhdr.LastChangeDateTime
end as LastChangeDateTime,
cast(' ' as hu_status preserving type) as HandlingUnitInternalStatus,
case huhdr.HandlingUnitStatus
when 'A' then 'A' // planned
when 'B' then 'B' // active
when 'C' then 'C' // completed - shipped
when 'D' then 'D' // deleted/obsolete
else cast(' ' as hu_status_api preserving type)
end as HandlingUnitProcessStatus,
cast('0000000000000000000000' as guid_vekp) as SourceHandlingUnitUUID,
cast('00000000000000000000' as exidv2 ) as HandlingUnitSecondExternalId,
cast( '' as lohum_slot_num ) as HandlingUnitSlotNumber,
cast( '' as abap.char( 1 )) as HndlgUnitRlvnceForJIT,
huhdr.EWMStorageType as StorageType,
huhdr.EWMStorageBinSection as StorageSection,
huhdr.EWMStorageBin as StorageBin,
huhdr.EWMResource,
huhdr.EWMConsolidationGroup,
huhdr.HandlingUnitIsClosed,
huhdr.HandlingUnitType,
/* EWM Fields - Process Storage Control */
huhdr.ExternalStorageProcessStep,
huhdr.EWMHUProcessStepIsCompleted,
huhdr.StorageProcess,
/* EWM Fields - EWM HU Process Status */
huhdr.EWMHandlingUnitIsPlanned,
huhdr.EWMHandlingUnitIsInStock,
huhdr.EWMHandlingUnitIsUnloaded,
huhdr.EWMHandlingUnitIsLoaded,
huhdr.EWMTranspUnitIsAssigned,
/* EWM Fields - EWM HU Blocked Status */
huhdr.EWMHUContentChangeIsBlocked,
huhdr.EWMHUMovementChangeIsBlocked,
huhdr.EWMHUAttributeChangeIsBlocked,
huhdr.EWMHUPostingChangeIsBlocked,
huhdr.EWMHUIsBlockedByCustoms,
huhdr.EWMHUIsPackedForShipping,
/* Max Dimensions */
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
huhdr.HandlingUnitMaxLength,
huhdr.HandlingUnitMaxWidth,
@Semantics.quantity.unitOfMeasure: 'HandlingUnitMaxDimensionUnit'
huhdr.HandlingUnitMaxHeight,
@Semantics.unitOfMeasure: true
huhdr.HandlingUnitMaxDimensionUnit,
cast('EWM' as lo_hu_hu_origin preserving type) as HandlingUnitOrigin,
EWMSrlNmbrForPackagingMaterial,
// Multifitment Point
EWMHUIsJITUpdateRelevant,
EWMHUDestinationProdnSupArea,
EWMHUDestinationStorageBin,
_WeightUnit,
_WeightUnitTare,
_VolumeUnit,
_VolumeUnitTare,
_UnitOfMeasureDimension
}
where
huhdr.HandlingUnitStatus != 'D'