@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PENHACTVSTOCKSEP'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_ALLOWED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.private: true
@VDM.viewType: #COMPOSITE
define view 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 >= PhysicalInventoryLastCountDate
or 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
//===============================================================================================
ProductType,
ProductGroup,
//===============================================================================================
// Fields from plant/company master data
//===============================================================================================
_CompanyCode.Currency as CompanyCodeCurrency,
_FiscalYear.FiscalYearEndDate,
//===============================================================================================
// Quantities
//===============================================================================================
MatlWrhsStkQtyInMatlBaseUnit,
cast(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,
CurrentDate,
case
when PhysicalInventoryLastCountDate is null then 0
when PhysicalInventoryLastCountDate = '00000000' then 0
else dats_days_between(PhysicalInventoryLastCountDate,CurrentDate)
end as NmbrOfDaysSinceLastPInvCount,
cast(case
when PhysicalInventoryLastCountDate is null then ''
when PhysicalInventoryLastCountDate = '00000000' then ''
else 'X'
end as xzael preserving type ) as PhysicalInventoryItemIsCounted,
sum(
case
when MatDoc.PostingDate > CurrentDate then MatlStkChangeQtyInBaseUnit
when MatDoc.PostingDate is null or MatDoc.PostingDate < PhysicalInventoryLastCountDate then 0
when MatDoc.PostingDate = PhysicalInventoryLastCountDate then cast(MatlStkChangeQtyInBaseUnit as abap.fltp) *0.5
/ (1.0 + cast(dats_days_between( PhysicalInventoryLastCountDate, CurrentDate ) as abap.fltp))
else cast(MatlStkChangeQtyInBaseUnit as abap.fltp)
* (0.5 + cast(dats_days_between( PhysicalInventoryLastCountDate, MatDoc.PostingDate ) as abap.fltp))
/ (1.0 + cast(dats_days_between( PhysicalInventoryLastCountDate, 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 MatlStkChangeQtyInBaseUnit > 0 and
_AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is null and
_AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is null
then 1 else 0
end
) as NrOfGdsRcptSinceLastPInvCount,
sum(
case
when MatlStkChangeQtyInBaseUnit > 0 and
_AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is null and
_AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is null
then MatlStkChangeQtyInBaseUnit else 0
end
) as GdsRcptQtySinceLastCtInBsUnt,
sum(
case
when MatlStkChangeQtyInBaseUnit < 0 and
_AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is null and
_AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is null
then 1 else 0
end
) as NrOfGoodsIssSinceLastPInvCount,
sum(
case
when MatlStkChangeQtyInBaseUnit < 0 and
_AdjmtPostingMovementTypeS.PhysInvtryDecreasingQtyMvtType is null and
_AdjmtPostingMovementTypeD.PhysInvtryDecreasingQtyMvtType is null
then -MatlStkChangeQtyInBaseUnit else 0
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,
CycleCountType,
_CycleCounting.NmbrOfPhysInvtryPerFiscalYear,
_CycleCounting.PhysInvtryIntvlForCycCounting,
_CycleCounting.PhysInvtryFloatTimeCycCounting,
ProductType,
ProductGroup,
_CompanyCode.Currency,
_FiscalYear.FiscalYearEndDate,
MatlWrhsStkQtyInMatlBaseUnit,
PhysicalInventoryLastCountDate,
NumberOfPhysInventoryCounts,
CurrentDate
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADJMTPOSTINGMOVEMENTTYPE",
"I_COMPANYCODE",
"I_CYCLECOUNTINGSETTINGS",
"I_FISCALYEAR",
"I_PLANT",
"I_WBSELEMENTBASICDATA",
"P_MATDOCREC",
"P_PHYSINTRYCURRENTPERIOD",
"P_PHYSINTRYVALUATIONAREA",
"P_PHYSINVTRYACTVSTKSEPARATOR"
],
"ASSOCIATED":
[
"I_ADJMTPOSTINGMOVEMENTTYPE",
"I_CYCLECOUNTINGSETTINGS",
"I_FISCALYEAR",
"I_WBSELEMENTBASICDATA"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/