P_Matval_Manage_Val_Chart

@AbapCatalog.sqlViewName: 'PMATVALMGMVCH'
@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_Matval_Manage_Val_Chart
  as select from    P_Matval_Manage_Val_Ch_Max as mx
    left outer join ckmlcr                                                     as cr on  cr.kalnr = mx.CostEstimate
                                                                                     and cr.bdatj = left(mx.MaxFiscPerCKMLPP, 4)
                                                                                     and cr.poper = right(mx.MaxFiscPerCKMLPP, 3)
    inner join      ckmlhd                                                     as hd on hd.kalnr = mx.CostEstimate
    inner join      t001k                                                      as tk on tk.bwkey = hd.bwkey
    inner join      R_MatlPriceDataMigrationStatus                             as tf on(tf.MatlPriceDataMigrationStatus = 'X')
                                                                                     or(tf.MatlPriceDataMigrationStatus = 'R')
{
  key mx.CostEstimate,
  key mx.FiscalYear,
  key mx.FiscalPeriod,
  key curtp                          as LedgerValuationCurrencyRole,
  key hd.bwkey                       as ValuationArea,
  key tk.bukrs                       as CompanyCode,

      mx.FiscalYearPeriod,
      cast (case cr.vprsv
          when 'S' then stprs
          when 'V' then pvprs
          else 0
        end as fml_inventory_price)  as InventoryPrice,
      cr.waers                       as CompanyCodeCurrency,
      pvprs                          as MovingAveragePrice
}
where
    cr.sdm_version is initial
    
  and(
    tf.MatlPriceDataMigrationStatus = 'X'
    
    or(
      hd.price_sdm_yearper = '0000000'
      
      or(
              hd.price_sdm_yearper > concat(cr.bdatj, cr.poper)
          and hd.price_sdm_yearper < mx.FiscalYearPeriod
        )
    )
  )

union all select from    I_MaterialLedgerPrice                                      as price
//  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             I_FiscalYearPeriodForCmpnyCode                             as fyp        on  fyp.CompanyCode                      =  price.CompanyCode
                                                                                                  and price.MatlPrcValdtyStrtFsclYearPerd  <= fyp.FiscalYearPeriod
                                                                                                  and price.MatlPrcValdtyEndFsclYearPeriod >= fyp.FiscalYearPeriod  
                                                                                                  and fyp.FiscalYearPeriod                 <= marvld.CurrentFiscalYearPeriod
                                                                                                  and fyp.FiscalYearPeriod                 >= concat(marvld.PreviousFiscalYear, marvld.CurrentFiscalPeriod)
                                                                                                  and fyp.IsSpecialPeriod                  =  ''
  inner join             I_MatlLedgerValnCrcyRoleName                               as lvcr       on  lvcr.CompanyCode  = price.CompanyCode
                                                                                                  and lvcr.Ledger       = price.Ledger
                                                                                                  and lvcr.CurrencyRole = price.CurrencyRole
  inner join             R_MatlPriceDataMigrationStatus                             as tf         on(tf.MatlPriceDataMigrationStatus = 'F')
                                                                                                  or(tf.MatlPriceDataMigrationStatus = 'R')
  left outer to one join ckmlhd                                                     as hd         on price.CostEstimate = hd.kalnr
 
  association [0..1] to I_MaterialLedgerPrice as _ACT         
                                                     on _ACT.CostEstimate = price.CostEstimate  
                                                     and _ACT.CurrencyRole = price.CurrencyRole
                                                     and _ACT.Ledger = price.Ledger                                                  
                                                     and _ACT.MaterialPriceType = 'ACT'
                                                     and _ACT.MaterialPriceSubtype = 'ACT'
                                                     and _ACT.MatlPrcValdtyStrtFsclYearPerd <= price.MatlPrcValdtyStrtFsclYearPerd  
                                                     and _ACT.MatlPrcValdtyEndFsclYearPeriod >= price.MatlPrcValdtyEndFsclYearPeriod 
  association [0..1] to I_MaterialLedgerPrice as _MAP         
                                                     on _MAP.CostEstimate = price.CostEstimate 
                                                     and _MAP.CurrencyRole = price.CurrencyRole
                                                     and _MAP.Ledger = price.Ledger                                                  
                                                     and _MAP.MaterialPriceType = 'MAP'
                                                     and _MAP.MaterialPriceSubtype = ''
                                                     and _MAP.MatlPrcValdtyStrtFsclYearPerd <= price.MatlPrcValdtyStrtFsclYearPerd  
                                                     and _MAP.MatlPrcValdtyEndFsclYearPeriod >= price.MatlPrcValdtyEndFsclYearPeriod 

{
  key  price.CostEstimate,
  key  fyp.FiscalYear                   as FiscalYear,
  key  fyp.FiscalPeriod                 as FiscalPeriod,
  key  lvcr.LedgerValuationCurrencyRole as LedgerValuationCurrencyRole,
  key  price.ValuationArea,
  key  price.CompanyCode                as CompanyCode,

       fyp.FiscalYearPeriod             as FiscalYearPeriod,
       price.MaterialPrice              as InventoryPrice,
       price.Currency                   as CompanyCodeCurrency,
       case price.MaterialPriceControl
        when 'S'
            then case hd.mlast
                when '2' then coalesce(_MAP.MaterialPrice,0)
                when '3' then coalesce(_ACT.MaterialPrice,0)
            end
        when 'V'
            then coalesce(price.MaterialPrice,0)
        end as MovingAveragePrice
}
where
          price.MaterialPriceType    =  'INVPR'
  and     price.MaterialPriceSubtype =  ' '
  and( 
    tf.MatlPriceDataMigrationStatus = 'F'
    
    or( 
          hd.price_sdm_yearper <> '0000000' 
      and hd.price_sdm_yearper <= price.MatlPrcValdtyStrtFsclYearPerd 
      ) 
   )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_FISCALYEARPERIODFORCMPNYCODE",
"I_MATERIALLEDGERPRICE",
"I_MATLLEDGERVALNCRCYROLENAME",
"P_MATLCTRLRECDLDGRFISCALPERIOD",
"P_MATVAL_MANAGE_VAL_CH_MAX",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLCR",
"CKMLHD",
"T001K"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/