P_PackedDeliveryForStore_F4674

@AbapCatalog.sqlViewName: 'PPCKDDELVSTF4674'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@VDM.private: true
define view P_PackedDeliveryForStore_F4674
  as

  // Outbound Delivery

  select distinct from I_DeliveryDocument     as Delivery
    inner join         I_DeliveryDocumentItem as Item on Item.DeliveryDocument = Delivery.DeliveryDocument
{
  key Delivery.ReceivingPlant                                 as Store,
  key Delivery.DeliveryDocument,

      Delivery.DeliveryDate,
      Delivery.OverallGoodsMovementStatus,
      Item.Plant                                              as SupplyingSite,
      cast('' as md_supplier)                                 as Supplier,
      cast(Item.ReferenceSDDocument as ebeln preserving type) as PurchaseOrder,
      cast('01' as vpobj)                                     as HandlingUnitPackingObjectType
}
where
       Delivery.SDDocumentCategory         = 'J' // Outbound Delivery

  and  Delivery.OverallGoodsMovementStatus = 'C' // Completely processed (i.e. goods issue is posted)

  and  Item.ReferenceSDDocumentCategory    = 'V' // Purchase Order

  and(
       Item.PackingStatus                  = 'B' // Partially processed

    or Item.PackingStatus                  = 'C' // Completely processed

  )

union

// Inbound Delivery

select distinct from I_DeliveryDocument     as Delivery
  inner join         I_DeliveryDocumentItem as Item on Item.DeliveryDocument = Delivery.DeliveryDocument
{
  key Item.Plant                                              as Store,
  key Delivery.DeliveryDocument,

      Delivery.DeliveryDate,
      Delivery.OverallGoodsMovementStatus,
      cast('' as werks_d)                                     as SupplyingSite,
      Delivery.Supplier                                       as Supplier,
      cast(Item.ReferenceSDDocument as ebeln preserving type) as PurchaseOrder,
      cast('03' as vpobj)                                     as HandlingUnitPackingObjectType
}
where
       Delivery.SDDocumentCategory      = '7' // Inbound Delivery

  and  Item.ReferenceSDDocumentCategory = 'V'    // Purchase Order

  and(
       Item.PackingStatus               = 'B'    // Partially processed

    or Item.PackingStatus               = 'C'    // Completely processed

  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_DELIVERYDOCUMENT",
"I_DELIVERYDOCUMENTITEM"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/