fmlv_ckmlcr_compat

@AbapCatalog.sqlViewName: 'FMLVCKMLCRCOMPAT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Compatibility View for CKMLCR'
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
define view fmlv_ckmlcr_compat
  as select from           fmlt_price                     as price 

    inner join R_MatlPriceDataMigrationStatus as tf on ( tf.MatlPriceDataMigrationStatus = 'F' ) 
                                                    or ( tf.MatlPriceDataMigrationStatus = 'R' ) 
    inner join             ckmlhd                         as hd         on hd.kalnr = price.kalnr

    inner join             marv                           as m          on m.bukrs = price.bukrs
    inner join             I_FiscalCalYearPeriodForCoCode as p          on  p.CompanyCode      =  price.bukrs
                                                                        and p.FiscalYearPeriod >= price.yrper_from
                                                                        and p.FiscalYearPeriod <= price.yrper_to
                                                                        and p.FiscalYearPeriod <= concat(
      m.lfgja, concat(
        '0', m.lfmon
      )
    ) 
                                                                        and p.IsSpecialPeriod  =  ' '
    inner join             fmlv_curtp_ml                  as curtp      on  curtp.rbukrs      = price.bukrs
                                                                        and curtp.rldnr       = price.rldnr
                                                                        and curtp.ext_curtype = price.ext_curtype

    left outer join        fmlt_price                     as STDPR      on  price.kalnr         =  STDPR.kalnr
                                                                        and price.rldnr         =  STDPR.rldnr
                                                                        and price.ext_curtype   =  STDPR.ext_curtype
                                                                        and STDPR.price_type    =  'STDPR'
                                                                        and STDPR.price_subtype =  ''
                                                                        and STDPR.yrper_from    <= p.FiscalYearPeriod
                                                                        and STDPR.yrper_to      >= p.FiscalYearPeriod

    left outer join        fmlt_price                     as ACT        on  price.kalnr       =  ACT.kalnr
                                                                        and price.rldnr       =  ACT.rldnr
                                                                        and price.ext_curtype =  ACT.ext_curtype
                                                                        and ACT.price_type    =  'ACT'
                                                                        and ACT.price_subtype =  'ACT'
                                                                        and ACT.yrper_from    <= p.FiscalYearPeriod
                                                                        and ACT.yrper_to      >= p.FiscalYearPeriod

    left outer to one join tcurm                          as tcurm      on tcurm.mandt = price.mandt  // to check if price type MAP is valid

    left outer join        fmlt_price                     as MAP        on  tcurm.mb_lock_mode <> '3' // only then MAP is available

                                                                        and price.kalnr        =  MAP.kalnr
                                                                        and price.rldnr        =  MAP.rldnr
                                                                        and price.ext_curtype  =  MAP.ext_curtype
                                                                        and MAP.price_type     =  'MAP'
                                                                        and MAP.price_subtype  =  ''
                                                                        and MAP.yrper_from     <= p.FiscalYearPeriod
                                                                        and MAP.yrper_to       >= p.FiscalYearPeriod
{
  key price.kalnr,
  key p.FiscalYear          as bdatj,
  key p.FiscalPeriod        as poper,
  key '000'                 as untper,
  key curtp.curtp           as curtp,
      price.rldnr           as rldnr,
      price.ext_curtype     as ext_curtype,
      price.peinh           as peinh,
      price.vprsv           as vprsv,
      @Semantics.amount.currencyCode : 'fmlv_ckmlcr_compat.waers'
      case price.vprsv
        when 'S' then price.price
        else STDPR.price
      end                   as stprs,
      
      @Semantics.amount.currencyCode : 'fmlv_ckmlcr_compat.waers'
      case price.vprsv
        when 'V' then price.price
        when 'S' then case hd.mlast when '2' then MAP.price when '3' then ACT.price else cast( 0 as ck_pvprs_1 )  end 
        else cast( 0 as ck_pvprs_1 )     
      end                   as pvprs,
      price.waers               as waers,
      price.salkv_compat        as salkv
}
where
  ( ( hd.price_sdm_yearper <> '0000000' and hd.price_sdm_yearper <= price.yrper_from ) or ( tf.MatlPriceDataMigrationStatus = 'F' ) )
  and       price.price_type     =  'INVPR'
  and       price.price_subtype  =  ' '

union all select from    ckmlcr                    as c
  inner join R_MatlPriceDataMigrationStatus as tf on ( tf.MatlPriceDataMigrationStatus = 'X' ) 
                                                  or ( tf.MatlPriceDataMigrationStatus = 'R' ) 
  inner join             ckmlhd                    as hd         on hd.kalnr = c.kalnr
  inner join             t001k                     as t          on t.bwkey = hd.bwkey
  inner join             fmlv_curtp_ml             as curtp      on  curtp.rbukrs      = t.bukrs // duplicate entries for additional ledgers

                                                                 and curtp.ext_curtype = c.curtp
{
  key c.kalnr,
  key bdatj,
  key poper,
  key untper,
  key curtp.curtp,
      curtp.rldnr       as rldnr,
      curtp.ext_curtype as ext_curtype,
      peinh,
      vprsv,
      stprs,
      pvprs,
      c.waers,
      c.salkv
}
where
  ( ( hd.price_sdm_yearper = '0000000' or hd.price_sdm_yearper > concat( bdatj, poper ) ) or ( tf.MatlPriceDataMigrationStatus = 'X' ) )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FMLV_CURTP_ML",
"I_FISCALCALYEARPERIODFORCOCODE",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLCR",
"CKMLHD",
"FMLT_PRICE",
"MARV",
"T001K",
"TCURM"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/