P_PhysInventorySituation

@AbapCatalog.sqlViewName: 'PPHYSINVSITN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true


@ClientHandling.algorithm: #SESSION_VARIABLE

//@AccessControl.authorizationCheck: #CHECK


@VDM.private: true
@VDM.viewType: #COMPOSITE

@ObjectModel:{
    usageType:{
      sizeCategory: #L,
      serviceQuality: #D,
      dataClass:#TRANSACTIONAL
     }
}

define view P_PhysInventorySituation as select from P_PhysInvtryDocItem as currentItem
    left outer join P_PhysInvtryDocItem as previousItem 
                                        on currentItem.PhysicalInventoryDocument = previousItem.PhysInvtryRecountDocument
                                        and previousItem.Plant = currentItem.Plant 
                                        and previousItem.StorageLocation = currentItem.StorageLocation
                                        and previousItem.Material = currentItem.Material 
                                        and previousItem.Batch = currentItem.Batch 
                                        and previousItem.Supplier = currentItem.Supplier               
                                        and previousItem.SalesOrder = currentItem.SalesOrder  
                                        and previousItem.WBSElementInternalID = currentItem.WBSElementInternalID
                                        and previousItem.Customer = currentItem.Customer   
                                        and previousItem.StockOwner = currentItem.StockOwner 
                                        and previousItem.PhysicalInventoryStockType = currentItem.PhysicalInventoryStockType
                                        and previousItem.InventorySpecialStockType = currentItem.InventorySpecialStockType
                                        and previousItem.MaterialBaseUnit = currentItem.MaterialBaseUnit
                                        
    
    left outer join P_EnhPhysInvtryActvStkSepVal as _Stock     on  _Stock.Plant = currentItem.Plant 
                                                                     and _Stock.StorageLocation = currentItem.StorageLocation
                                                                     and _Stock.Material = currentItem.Material 
                                                                     and _Stock.Batch = currentItem.Batch 
                                                                     and _Stock.Supplier = currentItem.Supplier               
                                                                     and _Stock.SalesOrder = currentItem.SalesOrder  
                                                                     and _Stock.WBSElementInternalID = currentItem.WBSElementInternalID
                                                                     and _Stock.Customer = currentItem.Customer   
                                                                     and _Stock.StockOwner = currentItem.StockOwner 
                                                                     and (
                                                                         ( currentItem.PhysicalInventoryStockType = '1' and _Stock.InventoryStockType = '01' )
//                                                                   or  ( currentItem.PhysicalInventoryStockType = '1' and _Stock.InventoryStockType = '08' )

                                                                     or  ( currentItem.PhysicalInventoryStockType = '2' and _Stock.InventoryStockType = '02' )
                                                                     or  ( currentItem.PhysicalInventoryStockType = '4' and _Stock.InventoryStockType = '07' )
                                                                     )
                                                                     and _Stock.InventorySpecialStockType = currentItem.InventorySpecialStockType     
                                                                     and _Stock.MaterialBaseUnit = currentItem.MaterialBaseUnit       
    
    association [1..1] to I_PhysInvtryDocHeader as _PhysInvtryDocHeader on currentItem.PhysicalInventoryDocument = _PhysInvtryDocHeader.PhysicalInventoryDocument
                                                                     and currentItem.FiscalYear = _PhysInvtryDocHeader.FiscalYear
    
    association [0..1] to I_WBSElementByInternalKey   as _WBSElement on  currentItem.WBSElementInternalID = _WBSElement.WBSElementInternalID
                                                                     and currentItem.WBSElementInternalID <> '00000000' 
                                                                     
    association [0..1] to I_Product                   as _Product                     on $projection.Material =  _Product.Product
    
    
{
    
    @UI.hidden: true
    currentItem.PhysInvtrySitnID,
//  key cast( CONCAT( currentItem.FiscalYear, CONCAT( currentItem.PhysicalInventoryDocument, currentItem.PhysicalInventoryDocumentItem ) )   as mmim_pi_situation_id) as PhysInvtrySitnID,

    
    currentItem.FiscalYear,
    currentItem.PhysicalInventoryDocument,
    currentItem.PhysicalInventoryDocumentItem,
    currentItem.Material,
    currentItem.Plant,
    currentItem.StorageLocation,
    currentItem._ProductStorageLocation.WarehouseStorageBin,
    currentItem.Batch,
    currentItem.InventorySpecialStockType,
    currentItem.GoodsMovementType,
    currentItem.PhysicalInventoryStockType,
    currentItem.SalesOrder,
    currentItem.SalesOrderItem,
    currentItem.ScheduleLine,
    currentItem.Supplier,
    currentItem.StockOwner,
    currentItem.Customer,
    
    currentItem.WBSElementInternalID,
    _WBSElement.WBSElement,
    
    currentItem.ProductCategory,
    currentItem.LastChangeDate,
    currentItem.PhysicalInventoryLastCountDate,
    currentItem.PostingDate,
    _PhysInvtryDocHeader.PhysInventoryPlannedCountDate,
    currentItem.IsHandledInAltvUnitOfMsr,
    currentItem.PhysicalInventoryItemIsZero,
    
    currentItem.QuantityInUnitOfEntry,
    currentItem.Quantity,
    currentItem.BookQtyBfrCountInMatlBaseUnit,
    currentItem.DifferenceQuantityInBaseUnit,
    
    //currentItem.DiffCountedBookAmtInCoCodeCry,

    
    case 
        when currentItem.BookQtyBfrCountInMatlBaseUnit = 0
        then cast( 0 as mmim_pi_difference_in_percent )
        else cast( DIVISION( currentItem.DifferenceQuantityInBaseUnit, currentItem.BookQtyBfrCountInMatlBaseUnit, 3 ) * 100   as mmim_pi_difference_in_percent )
    end as PhysInvtryDifferenceInPercent,
    currentItem.MaterialBaseUnit,
    currentItem.UnitOfEntry,
    currentItem.Currency,
    currentItem.SlsPriceAmountInCoCodeCrcy,
    currentItem.PhysInvtryCtAmtInCoCodeCrcy,
    currentItem.BookQtyAmountInCoCodeCrcy,
    currentItem.DifferenceAmountInCoCodeCrcy,
    currentItem.DiffCountedBookAmtInCoCodeCry,
    currentItem.EnteredSlsAmtInCoCodeCrcy,
    
    currentItem.IsValueOnlyMaterial,
    currentItem.MaterialDocument,
    currentItem.MaterialDocumentYear,
    currentItem.MaterialDocumentItem,
    currentItem.CycleCountType,
    currentItem.NmbrOfPhysInvtryPerFiscalYear,
    currentItem.PhysInvtryIntvlForCycCounting,
    currentItem.PhysInvtryFloatTimeCycCounting,
    currentItem.ReasonForPhysInvtryDifference,
    currentItem.CrossPlantConfigurableMaterial,
    currentItem.PhysInvtryDiffIsDistributed,
    currentItem.PhysicalInventoryItemIsCounted,
    currentItem.PhysInvtryDifferenceIsPosted,
    currentItem.PhysInvtryItemIsRecounted,
    case when previousItem.PhysicalInventoryDocument is not null
    then cast('X' as mmim_pi_doc_created_by_recount preserving type ) 
    else cast('' as mmim_pi_doc_created_by_recount preserving type )
    end as PhysInvtryIsCreatedByRecount,
    currentItem._PhysInvtryDocHeader.PhysicalInventoryNumber,
    currentItem._PhysInvtryDocHeader.PhysInvtryDocHasQtySnapshot,
    currentItem._PhysInvtryDocHeader.SalesPriceValuationIsActive,
    currentItem._ProductPlant.SerialNumberProfile,
        
    cast( _Stock.ProductType as comt_product_type ) as ProductType,
    cast( _Stock.ProductGroup as productgroup preserving type ) as ProductGroup,
    _Product.SerialIdentifierAssgmtProfile,
    _Product.IsMarkedForDeletion,
    _Stock.NumberOfPhysInventoryCounts,
    _Stock.NumberOfPInvAdjustmentsPerYear,
    _Stock.PInvDiffQtyPerYearInBaseUnit,
    cast( _Stock.NmbrOfDaysSinceLastPInvCount as mmim_nmbrofdayssincelastpinvco preserving type ) as NmbrOfDaysSinceLastPInvCount,
    cast ( case 
        when _Stock.PhysicalInventoryItemIsCounted = ''
        then 'X'
        else ''
    end as mmim_pi_stock_is_never_counted preserving type )  as PhysInvtryItemIsNeverCounted,
    _Stock.NrOfGdsRcptSinceLastPInvCount,
    _Stock.GdsRcptQtySinceLastCtInBsUnt,
    
    // Fomulas copied from C_PhysInvtryProposal

    cast(round(_Stock.MatlWrhsStkQtyInMatlBaseUnit - _Stock.AvgStkQtySinceLastCountCorrect, 3) as pi_average_book_quantity ) as AvgStkQtySinceLastCountInBsUnt,    
    cast(
      case 
        when _Stock.PhysicalInventoryLastCountDate is null then 0
        when _Stock.GdsIssQtySinceLastCountInBsUnt = 0 or _Stock.GdsIssQtySinceLastCountInBsUnt is null then 0
        when _Stock.MatlWrhsStkQtyInMatlBaseUnit < _Stock.AvgStkQtySinceLastCountCorrect then 0
        when _Stock.MatlWrhsStkQtyInMatlBaseUnit = _Stock.AvgStkQtySinceLastCountCorrect  then DATS_DAYS_BETWEEN(_Stock.PhysicalInventoryLastCountDate,_Stock.CurrentDate)
        else  DIVISION(_Stock.GdsIssQtySinceLastCountInBsUnt,  _Stock.MatlWrhsStkQtyInMatlBaseUnit -  _Stock.AvgStkQtySinceLastCountCorrect ,1) 
    end as pi_turnover_since_last_cnt) as InvtryTurnoverSinceLastCnt,  
    
    _Stock.NrOfGoodsIssSinceLastPInvCount,
    _Stock.GdsIssQtySinceLastCountInBsUnt,
    
    currentItem._Customer,
    currentItem._CustomerCompanyByPlant,
    currentItem._Supplier,
    currentItem._SupplierCompanyByPlant,
    currentItem._Material,
    currentItem._CrossPlantConfigurableMatl,
    currentItem._MaterialBaseUnit,
    currentItem._Currency,
    currentItem._Plant,
    currentItem._StorageLocation,
    currentItem._UnitOfEntry,
    currentItem._PhysicalInventoryStockType,
    currentItem._InventorySpecialStockType,
    currentItem._PhysInvtryReasonCode,
    currentItem._GoodsMovementType,
    currentItem._ProductCategory,
    currentItem.PhysInvtryItemIsDeleted as PhysInvtryItemIsDeleted
      
    
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PHYSINVTRYDOCHEADER",
"I_PRODUCT",
"I_PRODUCTPLANT",
"I_PRODUCTSTORAGELOCATION",
"I_WBSELEMENTBYINTERNALKEY",
"P_ENHPHYSINVTRYACTVSTKSEPVAL",
"P_PHYSINVTRYDOCITEM"
],
"ASSOCIATED":
[
"I_CURRENCY",
"I_CUSTOMER",
"I_CUSTOMERCOMPANYBYPLANT",
"I_GOODSMOVEMENTTYPE",
"I_INVENTORYSPECIALSTOCKTYPE",
"I_MATERIAL",
"I_PHYSINVTRYDOCHEADER",
"I_PHYSINVTRYREASONCODE",
"I_PHYSINVTRYSTOCKTYPE",
"I_PLANT",
"I_PRODUCT",
"I_PRODUCTCATEGORY",
"I_STORAGELOCATION",
"I_SUPPLIER",
"I_SUPPLIERCOMPANYBYPLANT",
"I_UNITOFMEASURE",
"I_WBSELEMENTBYINTERNALKEY"
],
"BASE":
[
"P_PHYSINVTRYDOCITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/