@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
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIAL",
"I_MATERIALTEXT",
"I_MATERIALVALUATIONPRICES",
"I_PHYSINVTRYINDBATCHSTORLOC",
"I_PLANT",
"I_PRODUCT",
"I_PRODUCTPLANT",
"I_PRODUCTSTORAGELOCATION",
"P_ENHPHYSINVTRYACTVSTKSEPVAL"
],
"ASSOCIATED":
[
"I_CURRENCY",
"I_CUSTOMER",
"I_CUSTOMERCOMPANYBYPLANT",
"I_CYCLECOUNTINGSETTINGS",
"I_INVENTORYSPECIALSTOCKTYPE",
"I_INVENTORYSTOCKTYPE",
"I_MATERIAL",
"I_MATERIALVALUATIONPRICES",
"I_PHYSINVTRYINDBATCHSTORLOC",
"I_PLANT",
"I_PRODUCT",
"I_PRODUCTGROUP",
"I_PRODUCTPLANT",
"I_PRODUCTSTORAGELOCATION",
"I_PRODUCTTYPE",
"I_SALESORDER",
"I_STORAGELOCATION",
"I_SUPPLIER",
"I_SUPPLIERCOMPANYBYPLANT",
"I_UNITOFMEASURE",
"I_WBSELEMENTBYINTERNALKEY"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/