@AccessControl.authorizationCheck: #NOT_ALLOWED
@VDM.private: true
@VDM.viewType: #COMPOSITE
define view entity P_EnhPhysInvtryActvStkSep
as select from P_PhysInvtryActvStkSeparator as sep
left outer to one join I_Plant as _Plant on sep.Plant = _Plant.Plant
left outer to one join P_PhysIntryValuationArea as _ValuationArea on _Plant.ValuationArea = _ValuationArea.ValuationArea
left outer to one join P_PhysIntryCurrentPeriod as _CurrentPeriod on _CurrentPeriod.CompanyCode = _ValuationArea.CompanyCode
left outer join P_MatDocRec as MatDoc on sep.Material = MatDoc.StockIdentifyingMaterial
and sep.Plant = MatDoc.Plant
and sep.StorageLocation = MatDoc.StockIdfgStorageLocation
and sep.Batch = MatDoc.StockIdentifyingBatch
and sep.Supplier = MatDoc.SpecialStockIdfgSupplier
and sep.SalesOrder = MatDoc.SpecialStockIdfgSalesOrder
and sep.SalesOrderItem = MatDoc.SpecialStockIdfgSalesOrderItem
and sep.WBSElementInternalID = MatDoc.SpecialStockIdfgWBSElement
and sep.Customer = MatDoc.SpecialStockIdfgCustomer
and sep.StockOwner = MatDoc.SpecialStockIdfgStockOwner
and sep.InventoryStockType = MatDoc.InventoryStockType
and sep.InventorySpecialStockType = MatDoc.InventorySpecialStockType
and sep.MaterialBaseUnit = MatDoc.MaterialBaseUnit
and (
MatDoc.PostingDate >= sep.PhysicalInventoryLastCountDate
or sep.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_FiscalYear as _FiscalYear on _FiscalYear.FiscalYearVariant = _CompanyCode.FiscalYearVariant
and _FiscalYear.FiscalYear = _CurrentPeriod.FiscalYear
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 <> ''
association [0..1] to I_CycleCountingSettings as _CycleCounting on _CycleCounting.CycleCountType = sep.CycleCountType
and _CycleCounting.Plant = $projection.Plant
{
//===============================================================================================
// Stock Separators
//===============================================================================================
key sep.Material,
key sep.Plant,
key sep.StorageLocation,
key sep.Batch,
key sep.Supplier,
key sep.SalesOrder,
key sep.SalesOrderItem,
key case when sep.WBSElementInternalID = '00000000' then '' else _WBSElement.WBSElement end as WBSElement,
key sep.Customer,
key sep.StockOwner,
key sep.InventoryStockType,
key sep.InventorySpecialStockType,
key sep.MaterialBaseUnit,
key sep.MaterialIsPostedToStock,
cast( sep.CycleCountType as mmim_cycle_count_type ) as CycleCountType,
_CycleCounting.NmbrOfPhysInvtryPerFiscalYear as NmbrOfPhysInvtryPerFiscalYear,
_CycleCounting.PhysInvtryIntvlForCycCounting as PhysInvtryIntvlForCycCounting,
_CycleCounting.PhysInvtryFloatTimeCycCounting as PhysInvtryFloatTimeCycCounting,
sep.WBSElementInternalID,
//===============================================================================================
// Fields from product master data
//===============================================================================================
sep.ProductType,
sep.ProductGroup,
//===============================================================================================
// Fields from plant/company master data
//===============================================================================================
_CompanyCode.Currency as CompanyCodeCurrency,
_FiscalYear.FiscalYearEndDate,
//===============================================================================================
// Quantities
//===============================================================================================
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
sep.MatlWrhsStkQtyInMatlBaseUnit,
cast(sep.PhysicalInventoryLastCountDate as pi_last_count_date preserving type) as PhysicalInventoryLastCountDate,
// cast(NumberOfPhysInventoryCounts as pi_nr_of_counts_in_fiscal_year) as NumberOfPhysInventoryCounts,
cast(case when sep.NumberOfPhysInventoryCounts is null then 0
else sep.NumberOfPhysInventoryCounts end as pi_nr_of_counts_in_fiscal_year) as NumberOfPhysInventoryCounts,
sep.CurrentDate,
case
when sep.PhysicalInventoryLastCountDate is null then 0
when sep.PhysicalInventoryLastCountDate = '00000000' then 0
else dats_days_between(sep.PhysicalInventoryLastCountDate,sep.CurrentDate)
end as NmbrOfDaysSinceLastPInvCount,
cast(case
when sep.PhysicalInventoryLastCountDate is null then ''
when sep.PhysicalInventoryLastCountDate = '00000000' then ''
else 'X'
end as xzael preserving type ) as PhysicalInventoryItemIsCounted,
sum(
case
when MatDoc.PostingDate > sep.CurrentDate then cast( MatDoc.MatlStkChangeQtyInBaseUnit as abap.fltp)
when MatDoc.PostingDate is null or MatDoc.PostingDate < sep.PhysicalInventoryLastCountDate then cast( 0 as abap.fltp)
when MatDoc.PostingDate = sep.PhysicalInventoryLastCountDate then cast(MatDoc.MatlStkChangeQtyInBaseUnit as abap.fltp) *0.5
/ (1.0 + cast(dats_days_between( sep.PhysicalInventoryLastCountDate, sep.CurrentDate ) as abap.fltp))
else cast(MatDoc.MatlStkChangeQtyInBaseUnit as abap.fltp)
* (0.5 + cast(dats_days_between( sep.PhysicalInventoryLastCountDate, MatDoc.PostingDate ) as abap.fltp))
/ (1.0 + cast(dats_days_between( sep.PhysicalInventoryLastCountDate, sep.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
sep.Material,
sep.Plant,
sep.StorageLocation,
sep.Batch,
sep.Supplier,
sep.SalesOrder,
sep.SalesOrderItem,
_WBSElement.WBSElement,
sep.WBSElementInternalID,
sep.Customer,
sep.StockOwner,
sep.InventoryStockType,
sep.InventorySpecialStockType,
sep.MaterialBaseUnit,
sep.MaterialIsPostedToStock,
sep.CycleCountType,
_CycleCounting.NmbrOfPhysInvtryPerFiscalYear,
_CycleCounting.PhysInvtryIntvlForCycCounting,
_CycleCounting.PhysInvtryFloatTimeCycCounting,
sep.ProductType,
sep.ProductGroup,
_CompanyCode.Currency,
_FiscalYear.FiscalYearEndDate,
sep.MatlWrhsStkQtyInMatlBaseUnit,
sep.PhysicalInventoryLastCountDate,
sep.NumberOfPhysInventoryCounts,
sep.CurrentDate