@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl:{
authorizationCheck: #NOT_REQUIRED,
personalData.blocking: #BLOCKED_DATA_EXCLUDED
}
@EndUserText.label: 'Inventory proposal level 2'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #D,
sizeCategory: #XL,
dataClass: #TRANSACTIONAL
}
@VDM:{
private: true,
viewType: #COMPOSITE
}
define view entity P_PhysInvtryProposal_L2
as select from P_PhysInvtryProposal_L1 as L1
-- simplification in CE: Plant is always valuation area
-- left outer to one join I_Plant as _Plant on L1.Plant = _Plant.Plant
-- left outer to one join P_PhysIntryValuationArea as _ValuationArea on _Plant.ValuationArea = _ValuationArea.ValuationArea
left outer to one join I_ValuationArea as _ValuationArea on L1.Plant = _ValuationArea.ValuationArea
left outer to many join P_MatDocRec as MatDoc on L1.Material = MatDoc.StockIdentifyingMaterial
and L1.Plant = MatDoc.Plant
and L1.StorageLocation = MatDoc.StockIdfgStorageLocation
and L1.Batch = MatDoc.StockIdentifyingBatch
and L1.Supplier = MatDoc.SpecialStockIdfgSupplier
and L1.SalesOrder = MatDoc.SpecialStockIdfgSalesOrder
and L1.SalesOrderItem = MatDoc.SpecialStockIdfgSalesOrderItem
and L1.WBSElementInternalID = MatDoc.SpecialStockIdfgWBSElement
and L1.Customer = MatDoc.SpecialStockIdfgCustomer
and L1.StockOwner = MatDoc.SpecialStockIdfgStockOwner
and L1.InventoryStockType = MatDoc.InventoryStockType
and L1.InventorySpecialStockType = MatDoc.InventorySpecialStockType
and L1.MaterialBaseUnit = MatDoc.MaterialBaseUnit
and ( MatDoc.PostingDate >= L1.PhysicalInventoryLastCountDate or L1.PhysicalInventoryLastCountDate is null )
inner join I_CompanyCode as _CompanyCode on _CompanyCode.CompanyCode = _ValuationArea.CompanyCode
association [0..1] to I_WBSElementBasicData as _WBSElement on $projection.WBSElementInternalID = _WBSElement.WBSElementInternalID
association [0..1] to I_AdjmtPostingMovementType as _AdjmtPostingMovementTypeS on MatDoc.GoodsMovementType = _AdjmtPostingMovementTypeS.PhysInvtryIncreasingQtyMvtType
and MatDoc.InventorySpecialStockType = _AdjmtPostingMovementTypeS.InventorySpecialStockType
and _AdjmtPostingMovementTypeS.PhysicalInventoryStockType <> ''
association [0..1] to I_AdjmtPostingMovementType as _AdjmtPostingMovementTypeD on MatDoc.GoodsMovementType = _AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType
and MatDoc.InventorySpecialStockType = _AdjmtPostingMovementTypeD.InventorySpecialStockType
and _AdjmtPostingMovementTypeD.PhysicalInventoryStockType <> ''
{
//===============================================================================================
// Stock Separators
//===============================================================================================
key L1.Material,
key L1.Plant,
key L1.StorageLocation,
key L1.Batch,
-- key case when L1.Batch is null then '' else L1.Batch end as Batch,
key L1.Supplier,
key L1.SalesOrder,
key case when L1.SalesOrderItem is null then '000000' else L1.SalesOrderItem end as SalesOrderItem,
key case when L1.WBSElementInternalID = '00000000' then '' else _WBSElement.WBSElement end as WBSElement,
key L1.Customer,
key L1.StockOwner,
key L1.InventoryStockType,
key L1.InventorySpecialStockType,
key L1.MaterialBaseUnit,
key L1.MaterialIsPostedToStock,
L1.CostEstimate,
L1.InventoryValuationType,
L1.RealProductBatch,
-- case when L1.RealProductBatch is null then '' else L1.RealProductBatch end as RealProductBatch,
L1.CycleCountType,
L1.SerialIdentifierAssgmtProfile,
L1.MatlBatchIsInRstrcdUseStock,
L1.WBSElementInternalID,
_CompanyCode.Currency as CompanyCodeCurrency,
L1.CurrentDate,
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
L1.MatlWrhsStkQtyInMatlBaseUnit,
//===============================================================================================
// Key figures
//===============================================================================================
cast(L1.PhysicalInventoryLastCountDate as pi_last_count_date preserving type) as PhysicalInventoryLastCountDate,
cast(case when L1.NumberOfPhysInventoryCounts is null then 0
else L1.NumberOfPhysInventoryCounts end as pi_nr_of_counts_in_fiscal_year) as NumberOfPhysInventoryCounts,
case
when L1.PhysicalInventoryLastCountDate is null then 0
when L1.PhysicalInventoryLastCountDate = '00000000' then 0
else dats_days_between(L1.PhysicalInventoryLastCountDate,L1.CurrentDate)
end as NmbrOfDaysSinceLastPInvCount,
cast(case
when L1.PhysicalInventoryLastCountDate is null then ''
when L1.PhysicalInventoryLastCountDate = '00000000' then ''
else 'X'
end as xzael preserving type ) as PhysicalInventoryItemIsCounted,
sum(
case
when MatDoc.PostingDate > L1.CurrentDate then cast( MatDoc.MatlStkChangeQtyInBaseUnit as abap.fltp)
when MatDoc.PostingDate is null or MatDoc.PostingDate < L1.PhysicalInventoryLastCountDate then cast( 0 as abap.fltp)
when MatDoc.PostingDate = L1.PhysicalInventoryLastCountDate then cast(MatDoc.MatlStkChangeQtyInBaseUnit as abap.fltp) *0.5
/ (1.0 + cast(dats_days_between( L1.PhysicalInventoryLastCountDate, L1.CurrentDate ) as abap.fltp))
else cast(MatDoc.MatlStkChangeQtyInBaseUnit as abap.fltp)
* (0.5 + cast(dats_days_between( L1.PhysicalInventoryLastCountDate, MatDoc.PostingDate ) as abap.fltp))
/ (1.0 + cast(dats_days_between( L1.PhysicalInventoryLastCountDate, L1.CurrentDate ) as abap.fltp))
end
) as AvgStkQtySinceLastCountCorrect,
-- sum(
-- case when MatDoc.MaterialDocument is null or
-- _AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is not null or
-- _AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is not null
-- then 0 else 1 end
-- ) as NrOfGdsMvtSinceLastPInvCnt,
sum(
case
when MatDoc.MatlStkChangeQtyInBaseUnit > 0 and
_AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is null and
_AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is null
then 1 else 0
end
) as NrOfGdsRcptSinceLastPInvCount,
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
sum(
case
when MatDoc.MatlStkChangeQtyInBaseUnit > 0 and
_AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is null and
_AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is null
then MatDoc.MatlStkChangeQtyInBaseUnit else cast( 0 as nsdm_stock_qty )
end
) as GdsRcptQtySinceLastCtInBsUnt,
sum(
case
when MatDoc.MatlStkChangeQtyInBaseUnit < 0 and
_AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is null and
_AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is null
then 1 else 0
end
) as NrOfGoodsIssSinceLastPInvCount,
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
sum(
case
when MatDoc.MatlStkChangeQtyInBaseUnit < 0 and
_AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is null and
_AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is null
then -MatDoc.MatlStkChangeQtyInBaseUnit else cast( 0 as nsdm_stock_qty )
end
) as GdsIssQtySinceLastCountInBsUnt
}
group by
L1.Material,
L1.Plant,
L1.StorageLocation,
L1.Batch,
L1.Supplier,
L1.SalesOrder,
L1.SalesOrderItem,
_WBSElement.WBSElement,
L1.WBSElementInternalID,
L1.Customer,
L1.StockOwner,
L1.InventoryStockType,
L1.InventorySpecialStockType,
L1.MaterialBaseUnit,
L1.MaterialIsPostedToStock,
L1.CostEstimate,
L1.InventoryValuationType,
L1.RealProductBatch,
L1.CycleCountType,
L1.SerialIdentifierAssgmtProfile,
L1.MatlBatchIsInRstrcdUseStock,
_CompanyCode.Currency,
L1.MatlWrhsStkQtyInMatlBaseUnit,
L1.PhysicalInventoryLastCountDate,
L1.NumberOfPhysInventoryCounts,
L1.CurrentDate