P_Matval_Manage_Max

@VDM.private: true
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PMATVALMGMMAX'
@ClientHandling.algorithm: #SESSION_VARIABLE

define view P_Matval_Manage_Max

  // Current Period

  as select from    t001k                    as tk
    inner join      R_MatlPriceDataMigrationStatus as tf on(tf.MatlPriceDataMigrationStatus = 'X')
                                                         or(tf.MatlPriceDataMigrationStatus = 'R')
    inner join      P_Matval_Manage_Max_Hdpp as ckml on tk.bwkey = ValuationArea
    inner join      marv                     as mv   on mv.bukrs = tk.bukrs
    inner join      ckmlhd                   as hd   on hd.kalnr = ckml.CostEstimate
{
  key CostEstimate,
  key ValuationArea,
  key 'LF'                  as period_type,
  
      max(FiscalYearPeriod) as MaxFiscPerCKMLPP,
      mv.lfgja              as PeriodTypeFiscalYear,
      concat('0',mv.lfmon)  as PeriodTypeFiscalPeriod
}
where
    ckml.sdm_version is initial
    
  and(
    tf.MatlPriceDataMigrationStatus = 'X'
    
    or(
        hd.price_sdm_yearper = '0000000'     
      or     
        hd.price_sdm_yearper > concat(mv.lfgja, concat('0', mv.lfmon)) 
    )
  )
group by
  CostEstimate,
  ValuationArea,
  mv.lfgja,
  mv.lfmon

// Previous Period

union all select from t001k                as tk
  inner join      R_MatlPriceDataMigrationStatus as tf on(tf.MatlPriceDataMigrationStatus = 'X')
                                                       or(tf.MatlPriceDataMigrationStatus = 'R')
  inner join      P_Matval_Manage_Max_Hdpp as ckml on tk.bwkey = ValuationArea
  inner join      marv                     as mv   on mv.bukrs = tk.bukrs
  inner join      ckmlhd                   as hd   on hd.kalnr = ckml.CostEstimate
{
  key CostEstimate,
  key ValuationArea,
  key 'VM'                  as period_type,
  
      max(FiscalYearPeriod) as MaxFiscPerCKMLPP,
      mv.vmgja              as PeriodTypeFiscalYear,
      concat('0',mv.vmmon)  as PeriodTypeFiscalPeriod
}
where
    ckml.sdm_version is initial
    
  and(
    tf.MatlPriceDataMigrationStatus = 'X'
    
    or(
        hd.price_sdm_yearper = '0000000'     
      or      
        hd.price_sdm_yearper > concat(mv.vmgja, concat('0', mv.vmmon))
    )
  )
  and(
       ckml.FiscalYearPeriod <= concat(mv.vmgja, concat('0', mv.vmmon))
  )
group by
  CostEstimate,
  ValuationArea,
  mv.vmgja,
  mv.vmmon

// Last Period of Previous Year

union all select from t001k                as tk
  inner join      R_MatlPriceDataMigrationStatus as tf on(tf.MatlPriceDataMigrationStatus = 'X')
                                                       or(tf.MatlPriceDataMigrationStatus = 'R')
  inner join      P_Matval_Manage_Max_Hdpp as ckml on tk.bwkey = ValuationArea
  inner join      marv                     as mv   on mv.bukrs = tk.bukrs
  inner join      ckmlhd                   as hd   on hd.kalnr = ckml.CostEstimate
{
  key CostEstimate,
  key ValuationArea,
  key 'VJ'                  as period_type,
  
      max(FiscalYearPeriod) as MaxFiscPerCKMLPP,
      mv.vjgja              as PeriodTypeFiscalYear,
      concat('0',mv.vjmon)  as PeriodTypeFiscalPeriod
}
where
  ckml.sdm_version is initial
    
  and(
    tf.MatlPriceDataMigrationStatus = 'X'
    
    or(
        hd.price_sdm_yearper = '0000000'     
      or      
        hd.price_sdm_yearper > concat(mv.vjgja, concat('0', mv.vjmon))
    )
  )
  and(
       ckml.FiscalYearPeriod <= concat(mv.vjgja, concat('0', mv.vjmon))
  )

group by
  CostEstimate,
  ValuationArea,
  mv.vjgja,
  mv.vjmon
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_MATVAL_MANAGE_MAX_HDPP",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLHD",
"MARV",
"T001K"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/