I_JITHandlingUnitHeader

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'JIT Handling Unit Header'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #C,
    sizeCategory: #XXL,
    dataClass: #MIXED
}
@VDM:{
    viewType: #COMPOSITE
}
/*----------------------------------------------------------------------------------------------------------*/
/* The view I_HandlingUnitHeaderCombined selects 4 different categories of HUs
/* 1. LO HUs for non-EWM and non-WM storage locations
/* 2. LO HUs for ext. WM storage locations - only for outbound deliveries
/* 3. LO HUs for EWM storage locations - only for outbound deliveries
/* 4. EWM HUs
/* However, currently JIT only supports the first three cases. For performance reasons, it is decided to
/* have this view as a leaner version of the CDS view I_HandlingUnitHeaderCombined and only get the
/* required columns, required categories of HUs
/*----------------------------------------------------------------------------------------------------------*/
define view entity I_JITHandlingUnitHeader
  as select distinct from I_HandlingUnitHeader
  association [0..1] to I_HandlingUnitProcessStatus as _HandlingUnitProcessStatus on $projection.HandlingUnitProcessStatus = _HandlingUnitProcessStatus.HandlingUnitProcessStatus
{
  key HandlingUnitExternalID,
      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,
      Plant,
      CreationDate,
      PackagingMaterial,
      cast( HandlingUnitPackingObjectKey as vbeln) as OutboundDelivery,
      _HandlingUnitProcessStatus
}
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 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_HandlingUnitProcessStatus as _HandlingUnitProcessStatus on $projection.HandlingUnitProcessStatus = _HandlingUnitProcessStatus.HandlingUnitProcessStatus
{
  key vekp_decwhs.HandlingUnitExternalID,
      case vekp_decwhs.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,
      vekp_decwhs.Plant,
      vekp_decwhs.CreationDate,
      vekp_decwhs.PackagingMaterial,
      cast( vekp_decwhs.HandlingUnitPackingObjectKey as vbeln) as OutboundDelivery,
      _HandlingUnitProcessStatus
}
where
       vekp_decwhs.Warehouse                     is not initial
  and(
       vekp_decwhs.HandlingUnitPackingObjectType != '00' //Not assigned to an object.

    or vekp_decwhs.HandlingUnitPackingObjectType != '02' //Sales document

    or vekp_decwhs.HandlingUnitPackingObjectType != '04' //Shipment

    or vekp_decwhs.HandlingUnitPackingObjectType != '05' //Non-Assigned HU

    or vekp_decwhs.HandlingUnitPackingObjectType != '06' //Non-Assigned HU

    or vekp_decwhs.HandlingUnitPackingObjectType != '21' //Cross-Outbound Delivery

    or vekp_decwhs.HandlingUnitPackingObjectType != '23' //Cross-Inbound Delivery

  )
  and  vekp_decwhs.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



// 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

union all 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_HandlingUnitProcessStatus as _HandlingUnitProcessStatus on $projection.HandlingUnitProcessStatus = _HandlingUnitProcessStatus.HandlingUnitProcessStatus

{
  key vekp_ewm.HandlingUnitExternalID,
      case vekp_ewm.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,
      vekp_ewm.Plant,
      vekp_ewm.CreationDate,
      vekp_ewm.PackagingMaterial,
      cast( vekp_ewm.HandlingUnitPackingObjectKey as vbeln) as OutboundDelivery,
      _HandlingUnitProcessStatus
}
where
      vekp_ewm.Warehouse                     is not initial
  and(
      vekp_ewm.HandlingUnitPackingObjectType =  '01'
    or // Outbound Delivery

      vekp_ewm.HandlingUnitPackingObjectType =  '21'
  )    // Cross-Outbound Delivery

  and vekp_ewm.HandlingUnitInternalStatus    <> '0060'