P_MATVAL_MANAGE_PNV_PRE

@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PMATVALMGMPRE'
@AbapCatalog.compiler.compareFilter:true
@AbapCatalog.preserveKey:true

// special logic: if VM and VJ are the same, only vm is selected

define view P_Matval_Manage_Pnv_Pre
  as select from P_Matval_Manage_Max            as mx
    inner join   R_MatlPriceDataMigrationStatus as tf   on ( tf.MatlPriceDataMigrationStatus = 'X' )
                                                        or ( tf.MatlPriceDataMigrationStatus = 'R' )
    inner join   t001k                          as tk   on tk.bwkey   = mx.ValuationArea
    inner join   marv                           as mv   on mv.bukrs   = tk.bukrs
    inner join   P_MaterialLedger_Name_Curtp    as mlnc on mlnc.bukrs = tk.bukrs

{
  key   CostEstimate,
  key   cast(case mx.period_type when 'LF' then mv.lfgja
                                 when 'VM' then mv.vmgja
                                 when 'VJ' then mv.vjgja
                                 else '' end as fin_fiscyear) as FiscalYear,
  key   cast(case mx.period_type when 'LF' then concat('0',mv.lfmon)
                                 when 'VM' then concat('0',mv.vmmon)
                                 when 'VJ' then concat('0',mv.vjmon)
                                 else '' end as fin_fiscper)  as FiscalPeriod,
  key   mlnc.curtp                                            as LedgerValuationCurrencyRole,
  key   mlnc.rldnr                                            as Ledger,
  key   mx.ValuationArea,

        MaxFiscPerCKMLPP,
        mx.period_type,
        mlnc.rcolumn
}
where
        not(
          mx.period_type = 'VJ'
          and mv.vmgja   = mv.vjgja
          and mv.vmmon   = mv.vjmon
        )

// from I_MaterialLedgerPrice

// Current Period

union all select from I_MaterialLedgerPrice as price
  inner join          R_MatlPriceDataMigrationStatus as tf   on ( tf.MatlPriceDataMigrationStatus = 'F' )
                                                             or ( tf.MatlPriceDataMigrationStatus = 'R' )
  inner join          P_MaterialLedger_Name_Curtp    as mlnc on  mlnc.bukrs       = price.CompanyCode
                                                             and mlnc.ext_curtype = price.CurrencyRole
                                                             and mlnc.rldnr       = price.Ledger
//  inner join          marv                           as mv   on  mv.bukrs            = price.CompanyCode

  inner join             P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = price.CompanyCode 
                                                                  and marvld.Ledger = price.Ledger
     
  inner join          ckmlhd                         as hd   on  price.CostEstimate  = hd.kalnr
                                                             and price.ValuationArea = hd.bwkey
                                                             and price.Material      = hd.matnr
{
  key CostEstimate,
  key marvld.CurrentFiscalYear            as FiscalYear,
  key marvld.CurrentFiscalPeriod          as FiscalPeriod,
  key mlnc.curtp                          as LedgerValuationCurrencyRole,
  key mlnc.rldnr                          as Ledger,
  key hd.bwkey                            as ValuationArea,
  
      price.MatlPrcValdtyStrtFsclYearPerd as MaxFiscPerCKMLPP,
      'LF'                                as period_type,
      mlnc.rcolumn

}
where
      price.MaterialPriceType              = 'INVPR'
  and price.MaterialPriceSubtype           = ' '
  and price.MatlPrcValdtyEndFsclYearPeriod = '9999012'
  and(
  
    tf.MatlPriceDataMigrationStatus = 'F'
    
    or(
          hd.price_sdm_yearper <> '0000000' 
      and hd.price_sdm_yearper <= price.MatlPrcValdtyStrtFsclYearPerd 
      ) 
  )

// Previous period

union all select from I_MaterialLedgerPrice as price
  inner join          R_MatlPriceDataMigrationStatus as tf   on ( tf.MatlPriceDataMigrationStatus = 'F' )
                                                             or ( tf.MatlPriceDataMigrationStatus = 'R' )
  inner join          P_MaterialLedger_Name_Curtp    as mlnc on  mlnc.bukrs       = price.CompanyCode
                                                             and mlnc.ext_curtype = price.CurrencyRole
                                                             and mlnc.rldnr       = price.Ledger
//  inner join          marv                           as mv   on  mv.bukrs = price.CompanyCode

  inner join          P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = price.CompanyCode 
                                                               and marvld.Ledger = price.Ledger  
  inner join          ckmlhd                         as hd   on  price.CostEstimate  = hd.kalnr
                                                             and price.ValuationArea = hd.bwkey
                                                             and price.Material      = hd.matnr
{
  key CostEstimate,
  key marvld.PreviousFiscalPeriodFiscalYear as FiscalYear,
  key marvld.PreviousFiscalPeriod           as FiscalPeriod,
  key mlnc.curtp                            as LedgerValuationCurrencyRole,
  key mlnc.rldnr                            as Ledger,
  key hd.bwkey                              as ValuationArea,
  
      price.MatlPrcValdtyStrtFsclYearPerd   as MaxFiscPerCKMLPP,
      'VM'                                  as period_type,
      mlnc.rcolumn

}
where
      price.MaterialPriceType              =  'INVPR'
  and price.MaterialPriceSubtype           =  ' '
  and price.MatlPrcValdtyStrtFsclYearPerd  <= marvld.PreviousFiscalYearPeriod
  and price.MatlPrcValdtyEndFsclYearPeriod >= marvld.PreviousFiscalYearPeriod
  and(
  
    tf.MatlPriceDataMigrationStatus = 'F'
    
    or(
          hd.price_sdm_yearper <> '0000000' 
      and hd.price_sdm_yearper <= price.MatlPrcValdtyStrtFsclYearPerd 
      ) 
  )

// Last period of previous fiscal year

union all select from I_MaterialLedgerPrice as price
  inner join          R_MatlPriceDataMigrationStatus as tf   on ( tf.MatlPriceDataMigrationStatus = 'F' )
                                                             or ( tf.MatlPriceDataMigrationStatus = 'R' )
  inner join          P_MaterialLedger_Name_Curtp    as mlnc on  mlnc.bukrs       = price.CompanyCode
                                                             and mlnc.ext_curtype = price.CurrencyRole
                                                             and mlnc.rldnr       = price.Ledger
//  inner join          marv                           as mv   on  mv.bukrs = price.CompanyCode

  inner join          P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = price.CompanyCode 
                                                               and marvld.Ledger = price.Ledger    
  inner join          ckmlhd                         as hd   on  price.CostEstimate  = hd.kalnr
                                                             and price.ValuationArea = hd.bwkey
                                                             and price.Material      = hd.matnr
{
  key CostEstimate,
  key marvld.PreviousFiscalYear             as FiscalYear,
  key marvld.PrevFiscalYearLastFiscalPeriod as FiscalPeriod,
  key mlnc.curtp                            as LedgerValuationCurrencyRole,
  key mlnc.rldnr                            as Ledger,
  key hd.bwkey                              as ValuationArea,
  
      price.MatlPrcValdtyStrtFsclYearPerd   as MaxFiscPerCKMLPP,
      'VJ'                                  as period_type,
      mlnc.rcolumn

}
where
        price.MaterialPriceType              =  'INVPR'
  and   price.MaterialPriceSubtype           =  ' '
  and   price.MatlPrcValdtyStrtFsclYearPerd  <= marvld.PrevFsclYearLastFsclYearPeriod
  and   price.MatlPrcValdtyEndFsclYearPeriod >= marvld.PrevFsclYearLastFsclYearPeriod
  
  // special logic: if VM and VJ are the same, only vm is selected

  and   not(
      marvld.PreviousFiscalPeriodFiscalYear  =  marvld.PreviousFiscalYear
      and marvld.PreviousFiscalPeriod        =  marvld.PrevFiscalYearLastFiscalPeriod
    )
  and(
  
    tf.MatlPriceDataMigrationStatus = 'F'
    
    or(
          hd.price_sdm_yearper <> '0000000' 
      and hd.price_sdm_yearper <= price.MatlPrcValdtyStrtFsclYearPerd 
      ) 
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIALLEDGERPRICE",
"P_MATERIALLEDGER_NAME_CURTP",
"P_MATLCTRLRECDLDGRFISCALPERIOD",
"P_MATVAL_MANAGE_MAX",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLHD",
"MARV",
"T001K"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/