P_ENHPHYSINVTRYACTVSTKSEPVAL

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PPIEACTSTKSEPVL'

@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_ALLOWED
@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.private: true
@VDM.viewType: #COMPOSITE
define view P_EnhPhysInvtryActvStkSepVal
  as select from           P_EnhPhysInvtryActvStkSep                                      as sep

    left outer join        P_PhysInvtryDocItemPerYear(P_StartDate: $session.system_date ) as _PIPerYear on  _PIPerYear.Material                  = sep.Material
                                                                                                        and _PIPerYear.Plant                     = sep.Plant
                                                                                                        and _PIPerYear.StorageLocation           = sep.StorageLocation
                                                                                                        and _PIPerYear.Batch                     = sep.Batch
                                                                                                        and _PIPerYear.Supplier                  = sep.Supplier
                                                                                                        and _PIPerYear.SalesOrder                = sep.SalesOrder
                                                                                                        and _PIPerYear.SalesOrderItem            = sep.SalesOrderItem
                                                                                                        and _PIPerYear.WBSElementInternalID      = sep.WBSElementInternalID
                                                                                                        and _PIPerYear.Customer                  = sep.Customer
                                                                                                        and _PIPerYear.StockOwner                = sep.StockOwner
                                                                                                        and _PIPerYear.InventoryStockType        = sep.InventoryStockType
                                                                                                        and _PIPerYear.InventorySpecialStockType = sep.InventorySpecialStockType

    left outer to one join I_BatchPlant                                                   as batch      on  sep.Material = batch.Material
                                                                                                        and sep.Plant    = batch.Plant
                                                                                                        and sep.Batch    = batch.Batch
    left outer to one join I_WBSElement                                                   as wbs        on sep.WBSElementInternalID = wbs.WBSElementInternalID
    left outer to one join I_SalesDocumentItemBasic                                       as sd         on  sep.SalesOrder     = sd.SalesDocument
                                                                                                        and sep.SalesOrderItem = sd.SalesDocumentItem
                                                                                                        
    association [0..1] to I_ProductPlantBasic         as _ProductPlantBasic           on  $projection.Material = _ProductPlantBasic.Product
                                                                                      and $projection.Plant    = _ProductPlantBasic.Plant                                                                                                    

{
      //===============================================================================================

      // Stock Separators

      //===============================================================================================

  key cast(sep.Material as matnr preserving type)                        as Material,
  key sep.Plant,
  key cast(sep.StorageLocation as pi_storage_location preserving type)   as StorageLocation,
  key sep.Batch                                                          as Batch,  
  key sep.Supplier                                                       as Supplier,
  key sep.SalesOrder                                                     as SalesOrder,
  key sep.SalesOrderItem                                                 as SalesOrderItem,
  key sep.WBSElement,
  key sep.Customer                                                       as Customer,
  key sep.StockOwner                                                     as StockOwner,
  key sep.InventoryStockType,
  key sep.InventorySpecialStockType,
  key sep.MaterialBaseUnit,
  key sep.MaterialIsPostedToStock, 
  cast( 
    case when ( _ProductPlantBasic.ValuationCategory <> '' and _ProductPlantBasic.IsBatchManagementRequired = '' )
    then ( '' )
    else ( sep.Batch ) end
  as charg_d ) as RealProductBatch, 

      CycleCountType,
      NmbrOfPhysInvtryPerFiscalYear,
      PhysInvtryIntvlForCycCounting,
      PhysInvtryFloatTimeCycCounting,

      sep.WBSElementInternalID,

      //===============================================================================================

      // Fields from product master data

      //===============================================================================================

      ProductType,
      sep.ProductGroup,

      //===============================================================================================

      // Fields from plant/company master data

      //===============================================================================================

      CompanyCodeCurrency,

      //===============================================================================================

      // Quantities

      //===============================================================================================

      MatlWrhsStkQtyInMatlBaseUnit,

      //===============================================================================================

      // Valuation relevant keys

      //===============================================================================================

      case
        when ( sep.InventorySpecialStockType = 'E' or sep.InventorySpecialStockType = 'F' ) and sd.InventorySpecialStockValnType = 'M' then sep.SalesOrder
        else ''
      end                                                                as SalesOrderVal,
      case
        when ( sep.InventorySpecialStockType = 'E' or sep.InventorySpecialStockType = 'F' ) and sd.InventorySpecialStockValnType = 'M' then sep.SalesOrderItem
        else '000000'
      end                                                                as SalesOrderItemVal,
      case
        when ( sep.InventorySpecialStockType = 'Q' or sep.InventorySpecialStockType = 'R' ) and wbs.InventorySpecialStockValnType = 'M' then sep.WBSElementInternalID
        else '00000000'
      end                                                                as WBSElementInternalIDVal,
      case
        when ( ( sep.InventorySpecialStockType = 'E' or sep.InventorySpecialStockType = 'F' ) and sd.InventorySpecialStockValnType = 'M' ) then 'E'
        when ( ( sep.InventorySpecialStockType = 'Q' or sep.InventorySpecialStockType = 'R' ) and wbs.InventorySpecialStockValnType = 'M' ) then 'Q'
        else ''
      end                                                                as InventorySpecialStockTypeVal, //sobkz

      coalesce(batch.InventoryValuationType, '')                         as InventoryValuationType, //bwtar

      batch.InventoryValuationType as SearchForValuationType,
      case
        when sep.InventorySpecialStockType = 'Q' or sep.InventorySpecialStockType = 'R' then wbs.InventorySpecialStockValnType
        when sep.InventorySpecialStockType = 'E' or sep.InventorySpecialStockType = 'F' then sd.InventorySpecialStockValnType
        else ''
      end                                                                as InventorySpecialStockValnType, //kzbws

      PhysicalInventoryLastCountDate,
      NumberOfPhysInventoryCounts,
      NmbrOfDaysSinceLastPInvCount,
      PhysicalInventoryItemIsCounted,
      cast(GdsIssQtySinceLastCountInBsUnt as pi_gi_qty_since_last_cnt)   as GdsIssQtySinceLastCountInBsUnt,
      FLTP_TO_DEC( AvgStkQtySinceLastCountCorrect as abap.dec(31,14) )   as AvgStkQtySinceLastCountCorrect,
      //  FLTP_TO_DEC( AvgStkQtySinceLastCountCorrect as abap.dec(27,5) ) as AvgStkQtySinceLastCountCorrect,

      DifferenceAmtPerYrInCoCodeCrcy,
      cast(NrOfGdsMvtSinceLastPInvCnt as pi_nr_of_gm_since_last_cnt)     as NrOfGdsMvtSinceLastPInvCnt,
      cast(NumberOfPInvAdjustmentsPerYear as pi_nr_of_adjstmnt_per_year) as NumberOfPInvAdjustmentsPerYear,
      cast(PInvDiffQtyPerYearInBaseUnit as pi_difference_qty_per_year)   as PInvDiffQtyPerYearInBaseUnit,
      cast(NrOfGdsRcptSinceLastPInvCount as pi_nr_of_gr_since_last_cnt)  as NrOfGdsRcptSinceLastPInvCount,
      cast(GdsRcptQtySinceLastCtInBsUnt as pi_gr_qty_since_last_cnt)     as GdsRcptQtySinceLastCtInBsUnt,
      cast(NrOfGoodsIssSinceLastPInvCount as pi_nr_of_gi_since_last_cnt) as NrOfGoodsIssSinceLastPInvCount,
      CurrentDate
}