P_PhysInvtryProposal

@AbapCatalog.sqlViewName: 'P_PIPROPOSAL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK

@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@EndUserText.label: 'Proposals for new PI documents'

define view P_PhysInvtryProposal
  as select from P_EnhPhysInvtryActvStkSepVal as sep

    inner join   I_Plant on sep.Plant = I_Plant.Plant

  association [0..1] to I_PhysInvtryIndBatchStorLoc as _BatchStorageLocation      on  $projection.Plant           = _BatchStorageLocation.Plant
                                                                                  and $projection.StorageLocation = _BatchStorageLocation.StorageLocation
                                                                                  and $projection.Material        = _BatchStorageLocation.Material
                                                                                  and $projection.Batch           = _BatchStorageLocation.Batch


  association [1..1] to I_ProductPlant              as _ProductPlant              on  $projection.Material = _ProductPlant.Product
                                                                                  and $projection.Plant    = _ProductPlant.Plant

  association [0..1] to I_Plant                     as _Plant                     on  $projection.Plant = _Plant.Plant

  association [0..1] to I_StorageLocation           as _StorageLocation           on  $projection.Plant           = _StorageLocation.Plant
                                                                                  and $projection.StorageLocation = _StorageLocation.StorageLocation

  association [0..1] to I_Material                  as _Material                  on  $projection.Material = _Material.Material

  association [0..1] to I_Product                   as _Product                   on  $projection.Material = _Product.Product

  association [0..1] to I_Producttype               as _ProductType               on  $projection.ProductType = _ProductType.ProductType

  association [0..1] to I_ProductGroup              as _ProductGroup              on  $projection.ProductGroup = _ProductGroup.MaterialGroup

  association [0..1] to I_ProductStorageLocation    as _ProductStorageLocation    on  $projection.Material        = _ProductStorageLocation.Product
                                                                                  and $projection.Plant           = _ProductStorageLocation.Plant
                                                                                  and $projection.StorageLocation = _ProductStorageLocation.StorageLocation

  association [0..1] to I_Supplier                  as _Supplier                  on  $projection.Supplier = _Supplier.Supplier

  association [1..1] to I_SupplierCompanyByPlant    as _SupplierCompanyByPlant    on  $projection.Supplier = _SupplierCompanyByPlant.Supplier
                                                                                  and $projection.Plant    = _SupplierCompanyByPlant.Plant

  association [0..1] to I_Customer                  as _Customer                  on  $projection.Customer = _Customer.Customer

  association [1..1] to I_CustomerCompanyByPlant    as _CustomerCompanyByPlant    on  $projection.Customer = _CustomerCompanyByPlant.Customer
                                                                                  and $projection.Plant    = _CustomerCompanyByPlant.Plant

  association [0..1] to I_InventoryStockType        as _InventoryStockType        on  $projection.InventoryStockType = _InventoryStockType.InventoryStockType

  association [0..1] to I_InventorySpecialStockType as _InventorySpecialStockType on  $projection.InventorySpecialStockType = _InventorySpecialStockType.InventorySpecialStockType

  association [0..1] to I_SalesOrder                as _SalesOrder                on  $projection.SalesOrder = _SalesOrder.SalesOrder

  association [0..1] to I_UnitOfMeasure             as _MaterialBaseUnit          on  $projection.MaterialBaseUnit = _MaterialBaseUnit.UnitOfMeasure

  association [0..1] to I_Currency                  as _Currency                  on  $projection.CompanyCodeCurrency = _Currency.Currency

  association [0..1] to I_WBSElementByInternalKey   as _WBSElement                on  $projection.WBSElementInternalID = _WBSElement.WBSElementInternalID

  association [0..1] to I_MaterialValuationPrices   as _Price                     on  _Price.Material                  = $projection.Material
                                                                                  and I_Plant.ValuationArea            =  _Price.ValuationArea
                                                                                  and sep.InventoryValuationType       =  _Price.InventoryValuationType
                                                                                  and sep.InventorySpecialStockTypeVal =  _Price.InvtryValnSpecialStockType
                                                                                  and sep.SalesOrderVal                =  _Price.SalesOrder
                                                                                  and sep.SalesOrderItemVal            =  _Price.SalesOrderItem
                                                                                  and sep.WBSElementInternalIDVal      =  _Price.WBSElementInternalID
                                                                                  and sep.Supplier                     =  ''
                                                                                  and //if consider xobew, sep.SupplierVal = _Price.Supplier

                                                                                      _Price.CurrencyRole              =  '10'
                                                                                  and //Company Code Currency

                                                                                      _Price.FiscalPeriodType          =  'LF'
                                                                                  and //Current Period

                                                                                      (
    sep.InventorySpecialStockType                                                                                      =  ''
    or sep.InventorySpecialStockType                                                                                   =  'V'
    or sep.InventorySpecialStockType                                                                                   =  'W'
    or sep.InventorySpecialStockType                                                                                   =  'O'
    or //if consider xobew, sep.InventorySpecialStockTypeVal = 'O'

    sep.InventorySpecialStockValnType                                                                                  <> ''
  )


  association [0..1] to I_CycleCountingSettings     as _CycleCounting             on  _CycleCounting.CycleCountType = $projection.CycleCountType
                                                                                  and _CycleCounting.Plant          = $projection.Plant


{

  key sep.Plant,
  key StorageLocation,
  key Material,
  key RealProductBatch as Batch,
  key cast(InventoryValuationType as bwtar_d) as ValuationType, 
  key Supplier,
  key SalesOrder,
  key SalesOrderItem,
  key WBSElement,
  key Customer,
  key StockOwner,
  key InventoryStockType,
  key InventorySpecialStockType,
      @Environment.sql.passValue: true
      @Semantics.booleanIndicator: true
  key sep.MaterialIsPostedToStock,

      @Semantics.unitOfMeasure: true
  key MaterialBaseUnit,

      CycleCountType,
      NmbrOfPhysInvtryPerFiscalYear,
      @Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
      PhysInvtryIntvlForCycCounting,
      @Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
      PhysInvtryFloatTimeCycCounting,

      sep.WBSElementInternalID,

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

      // Material/Batch already inventoried

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

      case when InventoryStockType = '01' then _ProductStorageLocation.InvtryCurrentYearStockInd
           else case when InventoryStockType = '02' then _ProductStorageLocation.InvtryQualInspCurrentYrStkInd
                else case when InventoryStockType = '07' then _ProductStorageLocation.InventoryBlockStockInd
                     else '' end end end                                                                            as MaterialPhysInvtryInd,

      case when InventoryStockType = '01' then _ProductStorageLocation.InventoryStockPrevPeriod
           else case when InventoryStockType = '02' then _ProductStorageLocation.InvtryStockQltyInspPrevPeriod
                else case when InventoryStockType = '07' then _ProductStorageLocation.HasInvtryBlockStockPrevPeriod
                     else '' end end end                                                                            as MaterialPhysInvtryIndPrev,

      case when InventoryStockType = '01' then _BatchStorageLocation.InvtryCurrentYearStockInd
           else case when InventoryStockType = '02' then _BatchStorageLocation.InvtryQualInspCurrentYrStkInd
                else case when InventoryStockType = '07' then _BatchStorageLocation.InventoryBlockStockInd
                     else '' end end end                                                                            as BatchPhysInvtryInd,

      case when InventoryStockType = '01' then _BatchStorageLocation.InventoryStockPrevPeriod
           else case when InventoryStockType = '02' then _BatchStorageLocation.InvtryStockQltyInspPrevPeriod
                else case when InventoryStockType = '07' then _BatchStorageLocation.HasInvtryBlockStockPrevPeriod
                     else '' end end end                                                                            as BatchPhysInvtryIndPrev,


      //// 'is inventoried' part of the status fields

      //   case when InventoryStockType = '01' then right(_ProductStorageLocation.InvtryCurrentYearStockInd, 1)

      //       else case when InventoryStockType = '02' then right(_ProductStorageLocation.InvtryQualInspCurrentYrStkInd, 1)

      //            else case when InventoryStockType = '07' then right(_ProductStorageLocation.InventoryBlockStockInd, 1)

      //                 else '' end end end  as MaterialPhysInvtryIndInved,

      //

      //  case when InventoryStockType = '01' then right(_ProductStorageLocation.InventoryStockPrevPeriod, 1)

      //       else case when InventoryStockType = '02' then right(_ProductStorageLocation.InvtryStockQltyInspPrevPeriod, 1)

      //            else case when InventoryStockType = '07' then right(_ProductStorageLocation.HasInvtryBlockStockPrevPeriod, 1)

      //                 else '' end end end  as MaterialPhysInvtryIndPrevInved,

      //

      //  case when InventoryStockType = '01' then right(_BatchStorageLocation.InvtryCurrentYearStockInd, 1)

      //       else case when InventoryStockType = '02' then right(_BatchStorageLocation.InvtryQualInspCurrentYrStkInd, 1)

      //            else case when InventoryStockType = '07' then right(_BatchStorageLocation.InventoryBlockStockInd, 1)

      //                 else '' end end end  as BatchPhysInvtryIndInved,

      //

      //  case when InventoryStockType = '01' then right(_BatchStorageLocation.InventoryStockPrevPeriod, 1)

      //       else case when InventoryStockType = '02' then right(_BatchStorageLocation.InvtryStockQltyInspPrevPeriod, 1)

      //            else case when InventoryStockType = '07' then right(_BatchStorageLocation.HasInvtryBlockStockPrevPeriod, 1)

      //                 else '' end end end  as BatchPhysInvtryIndPrevInved,



      _ProductPlant.IsBatchManagementRequired,
      _Plant.FactoryCalendar,

      _ProductStorageLocation.CreationDate                                                                          as CreationDateMaterial,
      _BatchStorageLocation.CreationDate                                                                            as CreationDateBatch,

      _Material._Text[1: Language=$session.system_language].MaterialName                                            as MaterialName,

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

      // Fields from product master data

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

      ProductType,
      ProductGroup,
      _Product.SerialIdentifierAssgmtProfile,
      _ProductStorageLocation.WarehouseStorageBin,

      //Physical inventory needs deletion flag on batch, storage location, plant and client level

      _Product.IsMarkedForDeletion as IsMarkedForDeletionProduct,
      _ProductPlant.IsMarkedForDeletion,
      case  when _ProductStorageLocation.IsMarkedForDeletion is null then ' ' 
            else _ProductStorageLocation.IsMarkedForDeletion end
              as IsMarkedForDeletionStorLoc,
      case  when _BatchStorageLocation.IsMarkedForDeletion is null then ' ' 
            else _BatchStorageLocation.IsMarkedForDeletion end
              as IsMarkedForDeletionBatch,
              
      //===============================================================================================

      // Quantities

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

      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(MatlWrhsStkQtyInMatlBaseUnit as pi_current_quantity)                                                     as MatlWrhsStkQtyInMatlBaseUnit,

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

      // Prices

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

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      cast(_Price.InventoryPrice as pi_inventory_price preserving type)                                             as InventoryPrice,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      _Price.MaterialPriceUnitQty,

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

      // Fields from plant/company master data

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

      sep.CompanyCodeCurrency,

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

      // Key Figures from PI (only looking at current fiscal year)

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

      sep.NumberOfPhysInventoryCounts,
      sep.NumberOfPInvAdjustmentsPerYear,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      sep.PInvDiffQtyPerYearInBaseUnit,

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

      // Key Figures from PI (looking at complete history, not only at current fiscal year)

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

      sep.PhysicalInventoryLastCountDate,
      sep.NmbrOfDaysSinceLastPInvCount,
      sep.PhysicalInventoryItemIsCounted,
      sep.NrOfGdsRcptSinceLastPInvCount,
      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      sep.GdsRcptQtySinceLastCtInBsUnt                                                                              as GdsRcptQtySinceLastCtInBsUnt,

      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      cast(round(MatlWrhsStkQtyInMatlBaseUnit - sep.AvgStkQtySinceLastCountCorrect, 3) as pi_average_book_quantity) as AvgStkQtySinceLastCountInBsUnt,

      cast(
        case
          when sep.PhysicalInventoryLastCountDate is null then 0
          when sep.GdsIssQtySinceLastCountInBsUnt = 0 or sep.GdsIssQtySinceLastCountInBsUnt is null then 0
          when MatlWrhsStkQtyInMatlBaseUnit < sep.AvgStkQtySinceLastCountCorrect then 0
          when MatlWrhsStkQtyInMatlBaseUnit = sep.AvgStkQtySinceLastCountCorrect  then dats_days_between(sep.PhysicalInventoryLastCountDate,sep.CurrentDate)
          else  division(sep.GdsIssQtySinceLastCountInBsUnt,  MatlWrhsStkQtyInMatlBaseUnit -  sep.AvgStkQtySinceLastCountCorrect ,1)
        end as pi_turnover_since_last_cnt)                                                                          as InvtryTurnoverSinceLastCnt,

      sep.NrOfGoodsIssSinceLastPInvCount,

      @Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
      sep.GdsIssQtySinceLastCountInBsUnt                                                                            as GdsIssQtySinceLastCountInBsUnt,

      @Semantics.unitOfMeasure: true
      cast( cast( '10' as char3) as msehi)                                                                          as UnitOfMeasure, //Days


      @Consumption.filter.hidden: true
      _Plant,

      @Consumption.filter.hidden: true
      _StorageLocation,

      @Consumption.filter.hidden: true
      _Supplier,

      @Consumption.filter.hidden: true
      _Material,

      @Consumption.filter.hidden: true
      _SupplierCompanyByPlant,

      @Consumption.filter.hidden: true
      _Customer,

      @Consumption.filter.hidden: true
      _CustomerCompanyByPlant,


      @Consumption.filter.hidden: true
      _InventorySpecialStockType,

      @Consumption.filter.hidden: true
      _SalesOrder,

      @Consumption.filter.hidden: true
      _MaterialBaseUnit,

      @Consumption.filter.hidden: true
      _Currency,

      @Consumption.filter.hidden: true
      _WBSElement,

      @Consumption.filter.hidden: true
      _Product,

      @Consumption.filter.hidden: true
      _ProductType,

      @Consumption.filter.hidden: true
      _ProductGroup,

      @Consumption.filter.hidden: true
      _InventoryStockType,

      @Consumption.filter.hidden: true
      _CycleCounting,


      @Consumption.filter.hidden: true
      _ProductPlant

}