P_EWM_PInvDocBusKeyItem

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType: {serviceQuality: #C,
                         dataClass: #MIXED,
                         sizeCategory: #L}
@VDM.private: true
@VDM.viewType:#COMPOSITE

// Important info!!!

// this CDS view is designed to retrieve the business information ONLY for product

define view entity P_EWM_PInvDocBusKeyItem
  as select from I_EWM_PInvDocBusKeyItem as _PIItemBusKey // remove unused fields


    inner join   I_Product               as _Product on  _Product.ProductUUID      = _PIItemBusKey.ProductUUID
                                                     and _PIItemBusKey.ProductUUID is not initial

  association [0..1] to I_EWM_SerialNumberHeader_2 as _SNHeader          on  _SNHeader.EWMWarehouse           = _PIItemBusKey.EWMWarehouse
                                                                         and _SNHeader.EntitledToDisposeParty = _PIItemBusKey.EntitledToDisposeParty
                                                                         and _SNHeader.ProductUUID            = _Product.ProductUUID // that's why we don't use assocation


  //  association [0..1] to I_Batch                    as _Batch        on  _Batch.BatchExtWhseMgmtInternalId = _PIItemBusKey.BatchUUID

  //                                                                    and _Batch.Material                   = _Product.Product

  association [0..1] to I_BatchDistinct            as _Batch             on  _PIItemBusKey.BatchUUID = _Batch.BatchExtWhseMgmtInternalId
                                                                         and _PIItemBusKey.BatchUUID is not initial //<> '00000000000000000000000000000000' // do association only if batch uuid is not initial


  association [0..1] to I_EWM_MaterialValuation    as _MaterialValuation on  _PIItemBusKey.BatchUUID = _MaterialValuation.BatchUUID
                                                                         and _PIItemBusKey.BatchUUID is not initial //<> '00000000000000000000000000000000'


  association [0..1] to I_WBSElementBasicData      as _WBSBaiscData      on  _WBSBaiscData.WBSElementInternalID = _PIItemBusKey.StockDocumentNumber

{
  key _PIItemBusKey.PhysicalInventoryDocumentUUID,
  key _PIItemBusKey.PhysicalInventoryItemNumber,
  key _PIItemBusKey.LineIndexOfPInvItem,
  key _PIItemBusKey.PhysicalInventoryChangeVersion,
  key _PIItemBusKey.PInvDocumentItemType,

      _PIItemBusKey.EWMStockOwner,
      _PIItemBusKey.StockOwnerPartnerRole,
      _PIItemBusKey.GlobalTradeItemNumber,
      _PIItemBusKey.GlobalTradeItemNumberVariant,
      _PIItemBusKey.EWMStockType,
      _PIItemBusKey.EWMStockUsage,
      _PIItemBusKey.StockUsage,
      _PIItemBusKey.InventorySpclStockWBSElement,
      _PIItemBusKey.PlanningVersionUUID,
      _PIItemBusKey.ProducedMaterial,
      _PIItemBusKey.EWMWarehouse,
      _PIItemBusKey.ProductUUID,
      _Product.Product,

      _PIItemBusKey.BatchUUID,
      case
         when _PIItemBusKey.BatchUUID is not initial and _Batch.Batch is null then cast ( _MaterialValuation.InventoryValuationType as charg_d preserving type )
         else cast ( _Batch.Batch as charg_d preserving type )
      end                                                                                    as Batch,

      _SNHeader.SerialNumberRequiredLevel,
      /*-- special stocks --*/
      // follow P_EWM_PINVDOCCNTITEM02_2, not I_EWM_OutbDelivOrdItem

      cast( _PIItemBusKey.StockDocumentCategory as ewm_de_stockdocumentcat preserving type ) as StockDocumentCategory,

      // WBSElementInternalID

      case _PIItemBusKey.StockDocumentCategory
        when 'PJS' then _WBSBaiscData.WBSElementInternalID
        else cast ('00000000' as ps_s4_pspnr preserving type )
      end                                                                                    as WBSElementInternalID,

      // WBSElementExternalID

      case _PIItemBusKey.StockDocumentCategory
       when 'PJS' then _WBSBaiscData.WBSElementExternalID
       else cast( '' as ps_posid_edit )
       end                                                                                   as WBSElementExternalID,

      // SpecialStockIdfgSalesOrder

      case _PIItemBusKey.StockDocumentCategory
       when 'SOS' then cast(ltrim(_PIItemBusKey.StockDocumentNumber, '0') as ewm_de_special_stock_idfg_hdr)
       else cast( '' as ewm_de_special_stock_idfg_hdr)
      end                                                                                    as SpecialStockIdfgSalesOrder,

      // SpecialStockIdfgSalesOrderItem

      case _PIItemBusKey.StockDocumentCategory
        when 'SOS' then cast(ltrim(_PIItemBusKey.StockItemNumber, '0') as mat_kdpos)
        else cast( '000000' as mat_kdpos )
      end                                                                                    as SpecialStockIdfgSalesOrderItem,

      /*-- special stocks --*/

      _PIItemBusKey.StockItemNumber,
      _PIItemBusKey.DocumentReltdStockDocCat,
      _PIItemBusKey.EntitledToDisposeParty,
      _PIItemBusKey.EntitledToDisposePartnerRole,
      _PIItemBusKey.CounterForStockSeparation,
      _PIItemBusKey.SerialShippingContainerCode,
      _PIItemBusKey.HandlingUnitNumber,
      _PIItemBusKey.HandlingUnitIndicator,
      _PIItemBusKey.LocationPlant,
      _PIItemBusKey.StorageLocation,
      _PIItemBusKey.GlobalLocationNumber,
      _PIItemBusKey.WarehouseLocationUUID,
      _PIItemBusKey.WarehouseLocation,
      _PIItemBusKey.BusinessPartner,
      _PIItemBusKey.WarehouseLocationRole,
      _PIItemBusKey.EWMMeansOfTransport,
      _PIItemBusKey.StorageType,
      _PIItemBusKey.EWMStorageType,
      _PIItemBusKey.EWMStorageBin,
      _PIItemBusKey.SourceResource, 
      _PIItemBusKey.WarehouseViewForGlobalStocks
      //_PIItemBusKey.PackagingMaterial,

      //_PIItemBusKey.HandlingUnitType

}
where
        _PIItemBusKey.LineIndexOfPInvItem            =  0   
  or(
        _PIItemBusKey.LineIndexOfPInvItem            >  0   
    and _PIItemBusKey.PhysicalInventoryChangeVersion =  0   
    and _PIItemBusKey.HandlingUnitIndicator          <> 'A' // not dummy HU

  )