I_FldLogsHandlingUnits

@AbapCatalog.sqlViewName: 'IFLHU'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@EndUserText.label: 'Handling Units'
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType: {serviceQuality: #C,
                         dataClass: #TRANSACTIONAL,
                         sizeCategory: #L}
@ClientHandling.algorithm: #SESSION_VARIABLE
/*
For Stock Items CE 2108 -> Only EWM HU are required
For Supplier Item -> Both LE & EWM HU are required
*/

define view I_FldLogsHandlingUnits

  as select distinct from  I_FldLogsEWMDelivDocItem      as _EWM_InbDeliveryItem

  //Linkage between LE and EWM IBD

    inner join             I_FldLogsEWMRefDoc            as _FldLogsEWMRefDoc       on  _FldLogsEWMRefDoc.DeliveryUUID                = _EWM_InbDeliveryItem.InboundDeliveryUUID
                                                                                    and _FldLogsEWMRefDoc.DeliveryItemUUID            = _EWM_InbDeliveryItem.InboundDeliveryItemUUID
                                                                                    and _FldLogsEWMRefDoc.EWMDeliveryDocumentCategory = 'PDI'

    inner join             I_EWM_HandlingUnitItem        as _EWM_HANDLINGUNITITEM   on  _EWM_HANDLINGUNITITEM.DeliveryUUID     = _EWM_InbDeliveryItem.InboundDeliveryUUID
                                                                                    and _EWM_HANDLINGUNITITEM.DeliveryItemUUID = _EWM_InbDeliveryItem.InboundDeliveryItemUUID
    inner join             I_EWM_HandlingUnitHdr_2       as _EWM_HandlingUnitHdr    on _EWM_HandlingUnitHdr.HandlingUnitUUID = _EWM_HANDLINGUNITITEM.ParentHandlingUnitUUID
   //OGFIELDLOGISTICS8-3397 to display parent HU at base

    left outer to one join I_EWM_HandlingUnitHdr_2       as _EWM_HandlingUnitParHdr on _EWM_HandlingUnitParHdr.HandlingUnitUUID = _EWM_HandlingUnitHdr.EWMHghstLvlHandlingUnitUUID
    inner join             I_FldLogsHandlingUnitTypeText as _HandlingUnitTypeText   on  _HandlingUnitTypeText.HandlingUnitType = _EWM_HandlingUnitParHdr.HandlingUnitType
                                                                                    and _HandlingUnitTypeText.Language         = $session.system_language

{
  key  _FldLogsEWMRefDoc.DeliveryDocument                                                   as DeliveryDocument,
  key  _FldLogsEWMRefDoc.DeliveryDocumentItem                                               as DeliveryDocumentItem,
  key  cast('          ' as venum preserving type  )                                        as HandlingUnitInternalID,
  key  cast('000000' as vepos)                                                              as HandlingUnitItem,
  key  cast( _EWM_HandlingUnitHdr.HandlingUnitNumber as exidv preserving type )             as HandlingUnitExternalID,

       cast( _EWM_HandlingUnitHdr.HandlingUnitType as /scwm/de_hutyp preserving type )      as HandlingUnitType,

       _HandlingUnitTypeText.HandlingUnitTypeText,
       //Warehouse

       _EWM_HandlingUnitHdr.HandlingUnitUUID,
       case when _EWM_HandlingUnitHdr.HandlingUnitNumber <> _EWM_HandlingUnitParHdr.HandlingUnitNumber
                     then
                     _EWM_HandlingUnitParHdr.HandlingUnitNumber
                     else
                     ' '
                     end                                                                    as HighestLevelHandlingUnitNumber,
       _EWM_InbDeliveryItem.EWMWarehouse,

       cast( _EWM_HANDLINGUNITITEM.HandlingUnitBaseUnitOfMeasure as vemeh preserving type ) as HandlingUnitQuantityUnit,
       cast( _EWM_HANDLINGUNITITEM.HandlingUnitQuantity as vmeng )                          as HandlingUnitQuantity,
       _EWM_HandlingUnitHdr.PackagingMaterialUUID,
       cast('                                        ' as vhilm preserving type )           as PackagingMaterial,
       //WHT

       cast('    ' as lfart preserving type )                                               as DeliveryDocumentType,
       cast('          ' as vbeln_vl preserving type )                                      as HandlingUnitReferenceDocName,
       cast('000000' as posnr_vl preserving type )                                          as HandlingUnitRefDocumentItem,
       cast('                    ' as vpobjkey preserving type )                            as HandlingUnitPackingObjectKey
}


union all

select distinct from     I_FldLogsDeliveryDocItem as _DeliveryItem



  inner join             I_HandlingUnitItem       as _IBDHU       on  _DeliveryItem.DeliveryDocument     = _IBDHU.HandlingUnitReferenceDocument
                                                                      
     //OGFIELDLOGISTICS8-3397 to display parent HU at remote                                                             and _DeliveryItem.DeliveryDocumentItem = _IBDHU.HandlingUnitRefDocumentItem

  left outer to one join I_HandlingUnitItem       as _IBDHUNested on _IBDHUNested.HandlingUnitNestedInternalID = _IBDHU.HandlingUnitInternalID

{
  key  _DeliveryItem.DeliveryDocument,
  key  _DeliveryItem.DeliveryDocumentItem,
  key  _IBDHU.HandlingUnitInternalID,
  key  _IBDHU.HandlingUnitItem,
  key
       cast( _IBDHU._HandlingUnitHeader.HandlingUnitExternalID as exidv preserving type )   as HandlingUnitExternalID,
       cast( '    ' as /scwm/de_hutyp preserving type  )                                    as HandlingUnitType,
       cast('                                        ' as /scwm/de_hutypt preserving type ) as HandlingUnitTypeText,
       //Warehouse

       hextobin( '00000000000000000000000000000000' )                                       as HandlingUnitUUID,
       cast( case when( _IBDHUNested.HandlingUnitExternalID is not null or _IBDHUNested.HandlingUnitExternalID is not initial )

       then

       _IBDHUNested.HandlingUnitExternalID

       else

        ' ' end as exidv preserving type )                                                  as HighestLevelHandlingUnitNumber,


       cast( '    ' as /scwm/lgnum preserving type )                                        as EWMWarehouse,
       cast( _IBDHU.HandlingUnitQuantityUnit as vemeh preserving type  )                    as HandlingUnitQuantityUnit,
       cast( _IBDHU.HandlingUnitQuantity as vmeng preserving type )                         as HandlingUnitQuantity,
       hextobin( '00000000000000000000000000000000' )                                       as PackagingMaterialUUID,

       _IBDHU._HandlingUnitHeader.PackagingMaterial,
       //WHT

       _DeliveryItem.DeliveryDocumentType,
       _IBDHU.HandlingUnitReferenceDocument                                                 as HandlingUnitReferenceDocName,
       _IBDHU.HandlingUnitRefDocumentItem,
       _IBDHU._HandlingUnitHeader.HandlingUnitPackingObjectKey                              as HandlingUnitPackingObjectKey

}

where
      _DeliveryItem.DeliveryDocumentType = 'LO' //OBD with ref.

  or  _DeliveryItem.DeliveryDocumentType = 'NL' //OBD  - Without ARef.

  and _DeliveryItem.DeliveryDocument     = _IBDHU._HandlingUnitHeader.HandlingUnitPackingObjectKey

// HUs after confirmation of the Pick Warehouse Tasks w.r.t. EWM IBDs

union all select from    I_EWM_WhseTaskWhseReqItem      as _EWM_WhseTaskWhseReqItem
  inner join             I_FldLogsEWMRefDoc             as _FldLogsEWMRefDoc on  _FldLogsEWMRefDoc.DeliveryUUID               = _EWM_WhseTaskWhseReqItem.EWMWarehouseRequestUUID
                                                                             and _FldLogsEWMRefDoc.DeliveryItemUUID           = _EWM_WhseTaskWhseReqItem.EWMWarehouseRequestItemUUID
                                                                             and _EWM_WhseTaskWhseReqItem.EWMDocumentCategory = 'PDI'
  inner join             I_EWM_ConfirmedWarehouseTask_2 as _ConfWHT          on  _ConfWHT.EWMWarehouse                = _EWM_WhseTaskWhseReqItem.EWMWarehouse
                                                                             and _ConfWHT.WarehouseTask               = _EWM_WhseTaskWhseReqItem.WarehouseTask
                                                                             and _ConfWHT.IsHandlingUnitWarehouseTask is not initial
                                                                             and _ConfWHT.WarehouseProcessCategory    = '2'
                                                                             and _ConfWHT.WarehouseTaskStatus         = 'C'

  left outer to one join I_EWM_HandlingUnitHdr_2        as _Hdr              on  _Hdr.HandlingUnitNumber   = _ConfWHT.SourceHandlingUnit
                                                                             and (
                                                                                _Hdr.HandlingUnitStatus    = 'B'
                                                                                or _Hdr.HandlingUnitStatus = 'C'
                                                                              )
                                                                             and _Hdr.EWMWarehouse         = _ConfWHT.EWMWarehouse

  left outer to one join I_EWM_HandlingUnitHeaderLog_2  as _Log              on  _Log.HandlingUnitExternalID = _ConfWHT.SourceHandlingUnit
                                                                             and (
                                                                                _Log.HandlingUnitStatus      = 'B'
                                                                                or _Log.HandlingUnitStatus   = 'C'
                                                                              )
                                                                             and _Log.EWMWarehouse           = _ConfWHT.EWMWarehouse


{
  key  _FldLogsEWMRefDoc.DeliveryDocument                                                   as DeliveryDocument,
  key  _FldLogsEWMRefDoc.DeliveryDocumentItem                                               as DeliveryDocumentItem,
  key  cast('          ' as venum preserving type  )                                        as HandlingUnitInternalID,
  key  cast('000000' as vepos)                                                              as HandlingUnitItem,
  key  _ConfWHT.SourceHandlingUnit                                                          as HandlingUnitExternalID,

       _ConfWHT.HandlingUnitType,

       cast('                                        ' as /scwm/de_hutypt preserving type ) as HandlingUnitTypeText,

       _ConfWHT.SourceHandlingUnitUUID                                                      as HandlingUnitUUID,
       cast( ' ' as exidv  )                                                                as HighestLevelHandlingUnitNumber,

       _ConfWHT.EWMWarehouse                                                                as EWMWarehouse,

       cast(_ConfWHT.BaseUnit as meins preserving type )                                    as HandlingUnitQuantityUnit,
       cast( _ConfWHT.ActualQuantityInBaseUnit as vmeng )                                   as HandlingUnitQuantity,


       //      _ConfWHT.ProductUUID as PackagingMaterialUUID,

       case when _Log.PackagingMaterialUUID =  hextobin( '00000000000000000000000000000000' ) or _Log.PackagingMaterialUUID is null
       then
       _Hdr.PackagingMaterialUUID
       else
        _Log.PackagingMaterialUUID end                                                      as PackagingMaterialUUID,
       cast('                                        ' as vhilm preserving type )           as PackagingMaterial,
       cast('    ' as lfart preserving type )                                               as DeliveryDocumentType,
       cast('          ' as vbeln_vl preserving type )                                      as HandlingUnitReferenceDocName,
       cast('000000' as posnr_vl preserving type )                                          as HandlingUnitRefDocumentItem,
       cast('                    ' as vpobjkey preserving type )                            as HandlingUnitPackingObjectKey

}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_EWM_CONFIRMEDWAREHOUSETASK_2",
"I_EWM_HANDLINGUNITHDR_2",
"I_EWM_HANDLINGUNITHEADERLOG_2",
"I_EWM_HANDLINGUNITITEM",
"I_EWM_WHSETASKWHSEREQITEM",
"I_FLDLOGSDELIVERYDOCITEM",
"I_FLDLOGSEWMDELIVDOCITEM",
"I_FLDLOGSEWMREFDOC",
"I_FLDLOGSHANDLINGUNITTYPETEXT",
"I_HANDLINGUNITHEADER",
"I_HANDLINGUNITITEM"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/