P_PHYSINVTRYACTVSTKSEPARATOR
Active stock separators for which a PI count can be done
P_PHYSINVTRYACTVSTKSEPARATOR is a CDS View in S/4HANA. Active stock separators for which a PI count can be done. It contains 18 fields. 1 CDS views read from this table.
CDS Views using this table (1)
| View | Type | Join | VDM | Description |
|---|---|---|---|---|
| P_EnhPhysInvtryActvStkSep | view_entity | from | COMPOSITE | Additional fields for P_EnhPhysInvtryActvStkSeparator |
Fields (18)
| Key | Field | CDS Fields | Used in Views |
|---|---|---|---|
| KEY | Batch | Batch | 1 |
| KEY | Customer | Customer | 1 |
| KEY | InventorySpecialStockType | InventorySpecialStockType | 1 |
| KEY | InventoryStockType | InventoryStockType | 1 |
| KEY | Material | Material | 1 |
| KEY | MaterialBaseUnit | MaterialBaseUnit | 1 |
| KEY | MaterialIsPostedToStock | MaterialIsPostedToStock | 1 |
| KEY | Plant | Plant | 1 |
| KEY | SalesOrder | SalesOrder | 1 |
| KEY | SalesOrderItem | SalesOrderItem | 1 |
| KEY | StockOwner | StockOwner | 1 |
| KEY | StorageLocation | StorageLocation | 1 |
| KEY | Supplier | Supplier | 1 |
| CurrentDate | CurrentDate | 1 | |
| MatlWrhsStkQtyInMatlBaseUnit | MatlWrhsStkQtyInMatlBaseUnit | 1 | |
| ProductGroup | ProductGroup | 1 | |
| ProductType | ProductType | 1 | |
| WBSElementInternalID | WBSElementInternalID | 1 |
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_ALLOWED
@VDM.private: true
@VDM.viewType: #COMPOSITE
define view entity P_PhysInvtryActvStkSeparator
as select from P_MaterialDocumentExtract as StockSep
join I_Product as prod on StockSep.StockIdentifyingMaterial = prod.Product
and StockSep.MaterialBaseUnit = prod.BaseUnit
join P_ProductPlantBasic as ProdPlant on StockSep.StockIdentifyingMaterial = ProdPlant.Product
and StockSep.Plant = ProdPlant.Plant
left outer to one join P_PhysIntryCurrentPeriod as _CurrentPeriod on _CurrentPeriod.CompanyCode = StockSep.CompanyCode
left outer join P_MappedPhysInvtryItem as PiItem on StockSep.StockIdentifyingMaterial = PiItem.Material
and StockSep.Plant = PiItem.Plant
and StockSep.StockIdfgStorageLocation = PiItem.StorageLocation
and StockSep.StockIdentifyingBatch = PiItem.Batch
and StockSep.SpecialStockIdfgSupplier = PiItem.Supplier
and StockSep.SpecialStockIdfgSalesOrder = PiItem.SalesOrder
and StockSep.SpecialStockIdfgSalesOrderItem = PiItem.SalesOrderItem
and StockSep.SpecialStockIdfgWBSElement = PiItem.WBSElementInternalID
and StockSep.SpecialStockIdfgCustomer = PiItem.Customer
and (StockSep.InventoryStockType = PiItem.InventoryStockType
or( StockSep.InventoryStockType = '08' and PiItem.InventoryStockType = '01' )
)
and StockSep.InventorySpecialStockType = PiItem.InventorySpecialStockType
and StockSep.MaterialBaseUnit = PiItem.MaterialBaseUnit
and StockSep.SpecialStockIdfgStockOwner = PiItem.StockOwner
and _CurrentPeriod.FiscalYear = PiItem.FiscalYear
and // Only looking at the current fiscal year
PiItem.PhysInvtryItemIsRecounted = ''
and // Filtering out closed inventory counts
PiItem.PhysInvtryDifferenceIsPosted = ''
and // Filtering out closed inventory counts
PiItem.PhysInvtryItemIsDeleted = '' // Filtering out closed inventory counts
association [0..1] to P_PhysInvtryKeyFiguresBase as _KeyFigures on _KeyFigures.Material = $projection.Material
and _KeyFigures.Plant = $projection.Plant
and _KeyFigures.StorageLocation = $projection.StorageLocation
and _KeyFigures.Batch = $projection.Batch
and _KeyFigures.Supplier = $projection.Supplier
and _KeyFigures.SalesOrder = $projection.SalesOrder
and _KeyFigures.SalesOrderItem = $projection.SalesOrderItem
and _KeyFigures.WBSElementInternalID = $projection.WBSElementInternalID
and _KeyFigures.Customer = $projection.Customer
and _KeyFigures.StockOwner = $projection.StockOwner
and ( _KeyFigures.InventoryStockType = $projection.InventoryStockType
or( _KeyFigures.InventoryStockType = '01' and $projection.InventoryStockType = '08' )
)
and _KeyFigures.InventorySpecialStockType = $projection.InventorySpecialStockType
and _KeyFigures.MaterialBaseUnit = $projection.MaterialBaseUnit
{
//===============================================================================================
// Stock Separators
//===============================================================================================
key cast(StockSep.StockIdentifyingMaterial as matnr preserving type) as Material,
key StockSep.Plant,
key cast(StockSep.StockIdfgStorageLocation as lgort_d preserving type) as StorageLocation,
key cast(StockSep.StockIdentifyingBatch as charg_d preserving type) as Batch,
key cast(StockSep.SpecialStockIdfgSupplier as elifn preserving type) as Supplier,
key cast(StockSep.SpecialStockIdfgSalesOrder as kdauf preserving type) as SalesOrder,
key cast(StockSep.SpecialStockIdfgSalesOrderItem as pi_sales_order_item preserving type) as SalesOrderItem,
key StockSep.SpecialStockIdfgWBSElement as WBSElementInternalID,
key cast(StockSep.SpecialStockIdfgCustomer as ekunn preserving type) as Customer,
key cast(StockSep.SpecialStockIdfgStockOwner as pi_owner_of_stock preserving type) as StockOwner,
key StockSep.InventoryStockType,
key StockSep.InventorySpecialStockType,
key StockSep.MaterialBaseUnit,
key cast( 'X' as materialispostedtostock preserving type ) as MaterialIsPostedToStock,
// Cycle counting Fields
max(ProdPlant.InventoryForCycleCountInd) as CycleCountType,
max(ProdPlant.CycleCountingIndicatorIsFixed) as CycleCountingIndicatorIsFixed,
//===============================================================================================
// Fields from product master data
//===============================================================================================
max(prod.ProductType) as ProductType,
max(prod.ProductGroup) as ProductGroup,
//===============================================================================================
// Quantities
//===============================================================================================
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
cast(sum(StockSep.MatlStkChangeQtyInBaseUnit) as nsdm_stock_qty ) as MatlWrhsStkQtyInMatlBaseUnit,
max(_KeyFigures.PhysicalInventoryLastCountDate) as PhysicalInventoryLastCountDate,
max(_KeyFigures.NumberOfPhysInventoryCounts) as NumberOfPhysInventoryCounts,
$session.system_date as CurrentDate
}
where
StockSep.MaterialBaseUnit <> ''
and StockSep.InventorySpecialStockType <> 'P'
and StockSep.InventorySpecialStockType <> 'T'
and StockSep.SpecialStockIdfgSupplier <> '$%§??§%$'
and StockSep.SpecialStockIdfgCustomer <> '$%§??§%$'
and StockSep.SpecialStockIdfgStockOwner <> '$%§??§%$'
// with zero stock the batch indicator for a material can be changed
and(
ProdPlant.IsInternalBatchManaged = ''
and StockSep.StockIdentifyingBatch = ''
) // 3098491
or(
ProdPlant.IsInternalBatchManaged <> ''
and StockSep.StockIdentifyingBatch <> ''
) // 3098491
group by
StockSep.StockIdentifyingMaterial,
StockSep.Plant,
StockSep.StockIdfgStorageLocation,
StockSep.StockIdentifyingBatch,
StockSep.SpecialStockIdfgSupplier,
StockSep.SpecialStockIdfgSalesOrder,
StockSep.SpecialStockIdfgSalesOrderItem,
StockSep.SpecialStockIdfgWBSElement,
StockSep.SpecialStockIdfgCustomer,
StockSep.SpecialStockIdfgStockOwner,
StockSep.InventoryStockType,
StockSep.InventorySpecialStockType,
StockSep.MaterialBaseUnit,
// ProdPlant.InventoryForCycleCountInd,
// ProdPlant.CycleCountingIndicatorIsFixed,
PiItem.PhysicalInventoryDocument // Helper field to filter out stock separators that have open inventory counts
// _KeyFigures.PhysicalInventoryLastCountDate,
// _KeyFigures.NumberOfPhysInventoryCounts
having
PiItem.PhysicalInventoryDocument is null
union all select from P_PhysInvtryNotPostedMaterial as _NotPostedMaterial
join I_Product as prod on _NotPostedMaterial.Material = prod.Product
join P_ProductPlantBasic as ProdPlant on _NotPostedMaterial.Material = ProdPlant.Product
and _NotPostedMaterial.Plant = ProdPlant.Plant
left outer to one join F_Mmim_Bukrs_Plant as _BUKRS_PLANT on _NotPostedMaterial.Plant = _BUKRS_PLANT.Plant
left outer to one join P_PhysIntryCurrentPeriod as _CurrentPeriod on _CurrentPeriod.CompanyCode = _BUKRS_PLANT.CompanyCode
left outer join P_MappedPhysInvtryItem as PiItem on _NotPostedMaterial.Material = PiItem.Material
and _NotPostedMaterial.Plant = PiItem.Plant
and _NotPostedMaterial.StorageLocation = PiItem.StorageLocation
and (
_NotPostedMaterial.Batch = PiItem.Batch
or _NotPostedMaterial.Batch is null
)
and ( _NotPostedMaterial.InventoryStockType = PiItem.InventoryStockType
or( _NotPostedMaterial.InventoryStockType = '08' and PiItem.InventoryStockType = '01' )
)
and prod.BaseUnit = PiItem.MaterialBaseUnit
and _CurrentPeriod.FiscalYear = PiItem.FiscalYear
and // Only looking at the current fiscal year
PiItem.PhysInvtryItemIsRecounted = ''
and // Filtering out closed inventory counts
PiItem.PhysInvtryDifferenceIsPosted = ''
and // Filtering out closed inventory counts
PiItem.PhysInvtryItemIsDeleted = '' // Filtering out closed inventory counts
association [0..1] to P_PhysInvtryKeyFiguresBase as _KeyFigures on _KeyFigures.Material = $projection.Material
and _KeyFigures.Plant = $projection.Plant
and _KeyFigures.StorageLocation = $projection.StorageLocation
and (
_KeyFigures.Batch = $projection.Batch
or $projection.Batch is null
)
and ( _KeyFigures.InventoryStockType = $projection.InventoryStockType
or( _KeyFigures.InventoryStockType = '01' and $projection.InventoryStockType = '08' )
)
and _KeyFigures.MaterialBaseUnit = $projection.MaterialBaseUnit
{
key _NotPostedMaterial.Material,
key _NotPostedMaterial.Plant,
key _NotPostedMaterial.StorageLocation,
key _NotPostedMaterial.Batch,
key _NotPostedMaterial.Supplier,
key _NotPostedMaterial.SalesOrder,
key _NotPostedMaterial.SalesOrderItem,
key _NotPostedMaterial.WBSElementInternalID,
key _NotPostedMaterial.Customer,
key _NotPostedMaterial.StockOwner,
key _NotPostedMaterial.InventoryStockType,
key cast( '' as nsdm_spcl_stock_type preserving type ) as InventorySpecialStockType,
key prod.BaseUnit as MaterialBaseUnit,
key cast( '' as materialispostedtostock preserving type ) as MaterialIsPostedToStock,
max(ProdPlant.InventoryForCycleCountInd) as CycleCountType,
max(ProdPlant.CycleCountingIndicatorIsFixed) as CycleCountingIndicatorIsFixed,
max(prod.ProductType) as ProductType,
max(prod.ProductGroup) as ProductGroup,
cast( 0 as nsdm_stock_qty ) as MatlWrhsStkQtyInMatlBaseUnit,
max(_KeyFigures.PhysicalInventoryLastCountDate) as PhysicalInventoryLastCountDate,
max(_KeyFigures.NumberOfPhysInventoryCounts) as NumberOfPhysInventoryCounts,
$session.system_date as CurrentDate
}
where
prod.BaseUnit <> ''
// with zero stock the batch indicator for a material can be changed
and(
ProdPlant.IsInternalBatchManaged = ''
and(
_NotPostedMaterial.Batch = ''
or _NotPostedMaterial.Batch is null
)
) // 3098491
or(
ProdPlant.IsInternalBatchManaged <> ''
and(
_NotPostedMaterial.Batch <> ''
or _NotPostedMaterial.Batch is not null
)
) // 3098491
group by
_NotPostedMaterial.Material,
_NotPostedMaterial.Plant,
_NotPostedMaterial.StorageLocation,
_NotPostedMaterial.Batch,
_NotPostedMaterial.Supplier,
_NotPostedMaterial.SalesOrder,
_NotPostedMaterial.SalesOrderItem,
_NotPostedMaterial.WBSElementInternalID,
_NotPostedMaterial.Customer,
_NotPostedMaterial.StockOwner,
_NotPostedMaterial.InventoryStockType,
prod.BaseUnit,
PiItem.PhysicalInventoryDocument // Helper field to filter out stock separators that have open inventory counts
having
PiItem.PhysicalInventoryDocument is null