Ztest_I_ProductAmount.sql

@AbapCatalog.sqlViewName: 'ZTEST_VPRODAMNT'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@EndUserText.label: 'Available product amount'

define view Ztest_I_ProductAmount
  as select from Ztest_I_ShopProducts

  association [*] to Ztest_I_ProductReplenishment as _Replenishment on _Replenishment.Material = $projection.Material
{
  key Material,

      sum( AvailableAmount ) as AvailableAmount,

      _Replenishment,
      _Material
}
group by
  Material

Ztest_I_ProductHistory.sql

@AbapCatalog.sqlViewName: 'ZTEST_VPRODHIST'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@EndUserText.label: 'Sales history of the product'

define view Ztest_I_ProductHistory
  as select from Ztest_I_ShopSalesHistory

{
  Material,
  ShopID,

  div(
     tstmp_seconds_between(OperationDateTime , tstmp_current_utctimestamp(), 'FAIL'),
     86400
   ) as DaysAgo,
   
   Amount
}

Ztest_I_ProductHorizonSum.sql

@AbapCatalog.sqlViewName: 'ZTEST_I_PRODHSUM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@EndUserText.label: 'Sales sum in certain horizon'

define view Ztest_I_ProductHorizonSum
  with parameters
    p_horizon_days : abap.int4
  as select from Ztest_I_ProductHistory
{
  key Material,
  key ShopID,

      sum(Amount) as Amount
}
where
  DaysAgo <= $parameters.p_horizon_days
group by
  Material,
  ShopID

Ztest_I_ProductReplenishment.sql

@AbapCatalog.sqlViewName: 'ZTEST_VPRODREPL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@EndUserText.label: 'Replenishment of each product'

define view Ztest_I_ProductReplenishment
  with parameters
    p_horizon_days : abap.int4,
    p_current_date : abap.dats
  as select from    Ztest_I_ShopProducts as Stock
    left outer join Ztest_I_ProductHorizonSum(
                    p_horizon_days: $parameters.p_horizon_days
                    )                    as History on  History.Material = Stock.Material
                                                    and History.ShopID   = Stock.ShopID
{
  key Stock.Material,
  key Stock.ShopID,

      @Semantics.businessDate.to: true
      cast(case
        when History.Amount is not null and History.Amount <> 0
          then dats_add_days(
            $parameters.p_current_date,
            ceil(division(Stock.AvailableAmount, History.Amount , 3) * $parameters.p_horizon_days),
            'FAIL')
          else '99991231'
      end as abap.dats) as ReplenishmentDate,
      
      _Shop,
      _Material
}