P_DunningLevelDistribution

@AbapCatalog.sqlViewName: 'PDUNNLEVELDISTR'
@VDM.viewType: #COMPOSITE
@VDM.private:true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_DunningLevelDistribution
  with parameters
    P_LowerBoundaryDunningLevel : far_lower_bndry_dunning_level,
    P_UpperBoundaryDunningLevel : far_upper_bndry_dunning_level
  as select from I_ReceivablesPayablesItem
{
  key CompanyCode,
  key Debtor                                                                          as Customer,
  key DunningLevel,

      CompanyCodeCurrency,

      sum( case when DunningLevel = '1' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel1AmtInCoCodeCrcy,
      sum( case when DunningLevel = '2' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel2AmtInCoCodeCrcy,
      sum( case when DunningLevel = '3' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel3AmtInCoCodeCrcy,
      sum( case when DunningLevel = '4' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel4AmtInCoCodeCrcy,
      sum( case when DunningLevel = '5' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel5AmtInCoCodeCrcy,
      sum( case when DunningLevel = '6' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel6AmtInCoCodeCrcy,
      sum( case when DunningLevel = '7' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel7AmtInCoCodeCrcy,
      sum( case when DunningLevel = '8' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel8AmtInCoCodeCrcy,
      sum( case when DunningLevel = '9' then AmountInCompanyCodeCurrency else 0 end ) as DunningLevel9AmtInCoCodeCrcy,

      sum( case when DunningLevel >= :P_LowerBoundaryDunningLevel
                 and DunningLevel <= :P_UpperBoundaryDunningLevel
           then AmountInCompanyCodeCurrency
           else 0 end )                                                               as DunLevelRngeAmtInCoCodeCrcy

}
where
      FinancialAccountType =  'D'
  and ClearingDate         =  '00000000'
  // when restricting the dunning levels via the parameters this should also apply to the rows of dunning levels returned

  // e.g. do not return a result for dunning level 9, when lower boundary = 1 and upper boundary = 4

  // a client shall request only those dunning level amounts which fit to the lower and upper boundary for the dunning level

  and DunningLevel         >= :P_LowerBoundaryDunningLevel //'1'

  and DunningLevel         <= :P_UpperBoundaryDunningLevel //'9'


group by
  CompanyCode,
  Debtor,
  CompanyCodeCurrency,
  DunningLevel