C_FRTORDSTOPLOADREFEWMDELIVERY

@EndUserText.label: 'Reference Stop to EWM Delivery Item'
@ObjectModel:   {   usageType:          {serviceQuality: #C,
                                         sizeCategory:   #XL,
                                         dataClass:      #TRANSACTIONAL}}
@VDM:           {   viewType:           #CONSUMPTION,
                    usage.type: [#TRANSACTIONAL_PROCESSING_SERVICE]}
@AccessControl: {   authorizationCheck: #MANDATORY,
                    personalData.blocking:#('TRANSACTIONAL_DATA') }
@Metadata.ignorePropagatedAnnotations: true

define view entity C_FrtOrdStopLoadRefEWMDelivery
  as select from    I_TranspOrdStopItmLoadRef as StopItemLoadref

  //Loadref Item to Item

  inner join      I_TransportationOrderItem as _LoadedItem on StopItemLoadref.TransportationOrderItemUUID = _LoadedItem.TransportationOrderItemUUID

  //Loadref Package to Iterm - we need only one child item per package

  left outer to one join  I_TransportationOrderItem as _ChildItem  on StopItemLoadref.TransportationOrderItemUUID = _ChildItem.TranspOrdItemParentItemUUID

//Loadref Package to package - we need two levels of nested packages

  left outer to one join  I_TransportationOrderItem as _ChildItemNested  on _ChildItem.TransportationOrderItemUUID = _ChildItemNested.TranspOrdItemParentItemUUID


  // Associations for loaded items

  association [0..1] to I_EWM_InbDeliveryItemBasic  as _EWMInboundDelivery       on  _LoadedItem.TranspOrdDocReferenceID    = _EWMInboundDelivery.EWMInboundDelivery
                                                                                 and _LoadedItem.TranspOrdDocReferenceItmID = _EWMInboundDelivery.EWMInboundDeliveryItem
                                                                                 and _LoadedItem.TranspOrdDocReferenceType  = '3004'
  association [0..1] to I_EWM_OutbDelivOrdItemBasic as _EWMOutboundDelivery      on  _LoadedItem.TranspOrdDocReferenceID    = _EWMOutboundDelivery.OutboundDeliveryOrder
                                                                                 and _LoadedItem.TranspOrdDocReferenceItmID = _EWMOutboundDelivery.OutboundDeliveryOrderItem
                                                                                 and _LoadedItem.TranspOrdDocReferenceType  = '496'

  // Associations for loaded package to iterm

  association [0..1] to I_EWM_InbDeliveryItemBasic  as _EWMInboundDeliveryChild  on  _ChildItem.TranspOrdDocReferenceID    = _EWMInboundDeliveryChild.EWMInboundDelivery
                                                                                 and _ChildItem.TranspOrdDocReferenceItmID = _EWMInboundDeliveryChild.EWMInboundDeliveryItem
                                                                                 and _ChildItem.TranspOrdDocReferenceType  = '3004'
  association [0..1] to I_EWM_OutbDelivOrdItemBasic as _EWMOutboundDeliveryChild on  _ChildItem.TranspOrdDocReferenceID    = _EWMOutboundDeliveryChild.OutboundDeliveryOrder
                                                                                 and _ChildItem.TranspOrdDocReferenceItmID = _EWMOutboundDeliveryChild.OutboundDeliveryOrderItem
                                                                                 and _ChildItem.TranspOrdDocReferenceType  = '496'

  // Associations for nested packages

  association [0..1] to I_EWM_InbDeliveryItemBasic  as _EWMInbDeliveryChildNested on  _ChildItemNested.TranspOrdDocReferenceID    = _EWMInbDeliveryChildNested.EWMInboundDelivery
                                                                                  and _ChildItemNested.TranspOrdDocReferenceItmID = _EWMInbDeliveryChildNested.EWMInboundDeliveryItem
                                                                                  and _ChildItemNested.TranspOrdDocReferenceType  = '3004'
  association [0..1] to I_EWM_OutbDelivOrdItemBasic as _EWMOutbDeliveryChildNested on  _ChildItemNested.TranspOrdDocReferenceID    = _EWMOutbDeliveryChildNested.OutboundDeliveryOrder
                                                                                   and _ChildItemNested.TranspOrdDocReferenceItmID = _EWMOutbDeliveryChildNested.OutboundDeliveryOrderItem
                                                                                   and _ChildItemNested.TranspOrdDocReferenceType  = '496'

  // Association needed for DCL

  association [0..1] to C_FreightOrderStopASR       as _Stop                     on  $projection.TransportationOrderStopUUID = _Stop.TransportationOrderStopUUID
{
  key StopItemLoadref.TransportationOrderUUID,
  key StopItemLoadref.TransportationOrderStopUUID,

      // Find staging area data

      // Inbound ReferenceType 3004, Outbound ReferenceType 496

      // Outbound deliveries (496) with PKG have the btd_id on package and item

      // Inbound deliveries (3004) with PKG item have the btd_id only on the child item

      // In special cases a nested HU requres two levels of child items




      //Staging Area

      case _LoadedItem.TranspOrdDocReferenceType
        when  '3004' then
          case
            when _LoadedItem.TranspOrdItemCategory = 'PRD' then _EWMInboundDelivery.StagingArea // Product was loaded

            when _ChildItemNested.TransportationOrderItemUUID is not null then _EWMInbDeliveryChildNested.StagingArea // nested HU was loaded

            else _EWMInboundDeliveryChild.StagingArea // Package was loaded

          end
        when '496' then
          case
            when _LoadedItem.TranspOrdItemCategory = 'PRD' then _EWMOutboundDelivery.StagingArea
            when _ChildItemNested.TransportationOrderItemUUID is not null then _EWMOutbDeliveryChildNested.StagingArea
            else _EWMOutboundDeliveryChild.StagingArea
          end
        when '' then
          case  //inbound with package find btd_id via child item

            when _ChildItem.TranspOrdDocReferenceType = '3004' then _EWMInboundDeliveryChild.StagingArea
            else '' //prevent having NULL values

          end
        else ''
        end as StagingArea,


      //Staging Area Group

      case _LoadedItem.TranspOrdDocReferenceType
        when  '3004' then
          case
            when _LoadedItem.TranspOrdItemCategory = 'PRD' then _EWMInboundDelivery.StagingAreaGroup
            when _ChildItemNested.TransportationOrderItemUUID is not null then _EWMInbDeliveryChildNested.StagingAreaGroup
            else _EWMInboundDeliveryChild.StagingAreaGroup
          end
        when '496' then
          case
            when _LoadedItem.TranspOrdItemCategory = 'PRD' then _EWMOutboundDelivery.StagingAreaGroup
            when _ChildItemNested.TransportationOrderItemUUID is not null then _EWMOutbDeliveryChildNested.StagingAreaGroup
            else _EWMOutboundDeliveryChild.StagingAreaGroup
          end
        when  '' then
          case  //inbound with package find btd_id via child item

            when _ChildItem.TranspOrdDocReferenceType = '3004' then _EWMInboundDeliveryChild.StagingAreaGroup
            else ''
          end
        else ''
        end as StagingAreaGroup,


      //Staging Area Bay

      case _LoadedItem.TranspOrdDocReferenceType
        when  '3004' then
          case
            when _LoadedItem.TranspOrdItemCategory = 'PRD' then _EWMInboundDelivery.StagingBay
            when _ChildItemNested.TransportationOrderItemUUID is not null then  _EWMInbDeliveryChildNested.StagingBay
            else _EWMInboundDeliveryChild.StagingBay
          end
        when '496' then
          case
            when _LoadedItem.TranspOrdItemCategory = 'PRD' then _EWMOutboundDelivery.StagingBay
            when _ChildItemNested.TransportationOrderItemUUID is not null then _EWMOutbDeliveryChildNested.StagingBay
            else _EWMOutboundDeliveryChild.StagingBay
          end
        when '' then
          case  //inbound with package find btd_id via child item

            when _ChildItem.TranspOrdDocReferenceType = '3004' then _EWMInboundDeliveryChild.StagingBay
            else ''
          end
        else ''
        end as StagingBay,


      //needed for DCL

      _Stop
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_EWM_INBDELIVERYITEMBASIC",
"I_EWM_OUTBDELIVORDITEMBASIC",
"I_TRANSPORDSTOPITMLOADREF",
"I_TRANSPORTATIONORDERITEM"
],
"ASSOCIATED":
[
"C_FREIGHTORDERSTOPASR"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/