@AbapCatalog.sqlViewName: 'ARUNI_CSTK_LINE'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@ClientHandling.algorithm: #AUTOMATED
@EndUserText.label: 'Batch Stock selection at storage level'
define view arun_i_batch_lines
as select from I_MaterialStock as ms
inner join marc as mc on ms.Material = mc.matnr
and ms.Plant = mc.werks
left outer join arun_stock_segmt as s on ms.Plant = s.plant
left outer join mcha as a on a.matnr = ms.Material
and a.werks = ms.Plant
and a.charg = ms.Batch
{
ms.MaterialBaseUnit,
ms.Material,
ms.Plant,
ms.StorageLocation,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then ''
else ms.Batch end as Batch,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then cast( '' as dats )
else a.vfdat end as ShelfExpDate,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then cast( '' as dats )
else a.hsdat end as MfgDate,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then cast( '' as dats )
else a.ersda end as BatchCreationDate,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then cast( '' as dats )
else a.lwedt end as LastGRDate,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then ''
else a.sgt_scat end as StockSegment,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then ''
else a.fsh_season_year end as SeasonYear,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then ''
else a.fsh_season end as Season,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then ''
else a.fsh_collection end as SCollection,
case
when mc.arun_fix_batch = '' and ms.Batch != ''
then ''
else a.fsh_theme end as Theme,
sum(ms.MatlWrhsStkQtyInMatlBaseUnit) as StockQty,
@Semantics.quantity.unitOfMeasure: 'MaterialBaseUnit'
mc.arun_fix_batch as ArunFixBatch
}
where
( (
ms.InventoryStockType = '02'
and s.quality_stock = 'X'
) //QI
or(
ms.InventoryStockType = '07'
and s.blocked_stock = 'X'
) //blocked
or(
ms.InventoryStockType = '08'
and s.restricted_stock = 'X'
) //restricted use
or(
ms.InventoryStockType = '04' or ms.InventoryStockType = '05'
and s.transfer_stock = 'X'
) //stock in transfer
or(
ms.InventoryStockType = '01'
) ) //unrestricted use alwayse select
and ( mc.xchpf = 'X' and mc.arun_fix_batch = '1' )
and mc.fsh_mg_arun_req = '1'
group by
ms.Material,
ms.Plant,
ms.CompanyCode,
ms.StorageLocation,
ms.Batch,
ms.Supplier,
ms.MaterialBaseUnit,
a.vfdat,
a.hsdat,
a.ersda,
a.lwedt,
a.sgt_scat,
a.fsh_season_year,
a.fsh_season,
a.fsh_collection,
a.fsh_theme,
mc.arun_fix_batch
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIALSTOCK",
"ARUN_STOCK_SEGMT",
"MARC",
"MCHA"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/