P_CURRENTMATLVALNPRICEDEX

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

  as select from           P_CURMATLVALNPRICE_CR_JOIN     as crjoin
    inner join             R_MatlPriceDataMigrationStatus as tf on(
      tf.MatlPriceDataMigrationStatus                                                             = 'X'
    )
                                                                or(
                                                                  tf.MatlPriceDataMigrationStatus = 'R'
                                                                )
    left outer to one join ckmlhd                         as hd on hd.kalnr = crjoin.CostEstimate
    left outer to one join mara                           as m  on m.matnr = hd.matnr
    left outer to one join I_WBSElementBasicData          as ps on ps.WBSElementInternalID = hd.pspnr
    left outer to one join t001k                          as tk on tk.bwkey = hd.bwkey
  association [0..1] to I_MatlLedgerValnCrcyRoleName as _MLCurrencyInfo on  _MLCurrencyInfo.LedgerValuationCurrencyRole = crjoin.CurrencyRole
                                                                        and _MLCurrencyInfo.CompanyCode                 = tk.bukrs

{
  key  crjoin.CostEstimate,
  key  COALESCE(_MLCurrencyInfo.Ledger, cast('  ' as fins_ledger))            as Ledger,
  key  COALESCE(_MLCurrencyInfo.CurrencyRole, cast('  ' as fins_ext_curtype)) as CurrencyRole,

       hd.matnr                                                               as Material,
       hd.bwkey                                                               as ValuationArea,
       hd.bwtar                                                               as InventoryValuationType,
       hd.vbeln                                                               as SalesOrder,
       hd.posnr                                                               as SalesOrderItem,
       hd.lifnr                                                               as Supplier,
       ps.WBSElementInternalID,
       ps.WBSElementExternalID,
       hd.sobkz                                                               as InvtryValnSpecialStockType,
       hd.kzbws                                                               as InventorySpecialStockValnType,
       hd.xobew                                                               as IsSupplierStockValuation,
       hd.mlast                                                               as PriceDeterminationControl,
       tk.bukrs                                                               as CompanyCode,

       crjoin.MaterialPriceControl,
       crjoin.MaterialPriceUnitQty,
       crjoin.Currency,
       cast(crjoin.MovingAveragePrice  as fml_price)                          as MovingAveragePrice,
       cast(crjoin.StandardPrice  as fml_price)                               as StandardPrice,
       @Semantics.amount.currencyCode: 'Currency'
       cast(crjoin.InventoryPrice as fml_price)                               as InventoryPrice,
       m.meins                                                                as BaseUnit,

       _MLCurrencyInfo.LedgerName                                             as LedgerName,
       _MLCurrencyInfo.CurrencyRoleName                                       as CurrencyRoleName,
       _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs                         as LedgerValnCrcyRoleIsUsedInLogs
}

where
  (
    (
         hd.price_sdm_yearper            = '0000000'
      or hd.price_sdm_yearper            > crjoin.MaxFiscPer
    )
    or   tf.MatlPriceDataMigrationStatus = 'X'
  )

union all

select from              I_MaterialLedgerPrice          as invprice
  inner join             R_MatlPriceDataMigrationStatus as tf on(
    tf.MatlPriceDataMigrationStatus                                                             = 'F'
  )
                                                              or(
                                                                tf.MatlPriceDataMigrationStatus = 'R'
                                                              )
  inner join             ckmlhd                         as hd on hd.kalnr = invprice.CostEstimate
  left outer to one join I_WBSElementBasicData          as ps on ps.WBSElementInternalID = hd.pspnr
association [0..1] to I_MatlLedgerValnCrcyRoleName as _MLCurrencyInfo on  _MLCurrencyInfo.CurrencyRole = invprice.CurrencyRole
                                                                      and _MLCurrencyInfo.Ledger       = invprice.Ledger
                                                                      and _MLCurrencyInfo.CompanyCode  = invprice.CompanyCode
association [0..1] to I_MaterialLedgerPrice        as _StandardPrice  on  _StandardPrice.CostEstimate                 = invprice.CostEstimate
                                                                      and _StandardPrice.Ledger                       = invprice.Ledger
                                                                      and _StandardPrice.CurrencyRole                 = invprice.CurrencyRole
                                                                      and _StandardPrice.MaterialPriceType            = 'STDPR'
                                                                      and _StandardPrice.MaterialPriceSubtype         = ' '
                                                                      and _StandardPrice.MaterialPriceValidityEndDate = '99991231'

{
  key invprice.CostEstimate,
  key invprice.Ledger,
  key invprice.CurrencyRole,

      hd.matnr                                       as Material,
      hd.bwkey                                       as ValuationArea,
      hd.bwtar                                       as InventoryValuationType,
      hd.vbeln                                       as SalesOrder,
      hd.posnr                                       as SalesOrderItem,
      hd.lifnr                                       as Supplier,
      ps.WBSElementInternalID,
      ps.WBSElementExternalID,
      hd.sobkz                                       as InvtryValnSpecialStockType,
      hd.kzbws                                       as InventorySpecialStockValnType,
      hd.xobew                                       as IsSupplierStockValuation,
      hd.mlast                                       as PriceDeterminationControl,
      invprice.CompanyCode                           as CompanyCode,

      invprice.MaterialPriceControl                  as MaterialPriceControl,
      invprice.MaterialPriceUnitQty                  as MaterialPriceUnitQty,
      invprice.Currency                              as Currency,
      @Semantics.amount.currencyCode: 'Currency'
      case invprice.MaterialPriceControl
          when 'V' then COALESCE(invprice.MaterialPrice, 0)
          else 0
          end                                        as MovingAveragePrice,
      @Semantics.amount.currencyCode: 'Currency'
      case invprice.MaterialPriceControl
          when 'S' then COALESCE(invprice.MaterialPrice, 0)
          else COALESCE(_StandardPrice.MaterialPrice, 0)
          end                                        as StandardPrice,
      invprice.MaterialPrice                         as InventoryPrice,
      invprice.ValuationQuantityUnit                 as BaseUnit,

      _MLCurrencyInfo.LedgerName                     as LedgerName,
      _MLCurrencyInfo.CurrencyRoleName               as CurrencyRoleName,
      _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs as LedgerValnCrcyRoleIsUsedInLogs

}
where
  (
    (
          hd.price_sdm_yearper                  <> '0000000'
      and hd.price_sdm_yearper                  <= invprice.MatlPrcValdtyStrtFsclYearPerd
    )
    or    tf.MatlPriceDataMigrationStatus       =  'F'
  )
  and(
          invprice.MaterialPriceType            =  'INVPR'
    and   invprice.MaterialPriceSubtype         =  ' '
    and   invprice.MaterialPriceValidityEndDate =  '99991231'
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIALLEDGERPRICE",
"I_MATLLEDGERVALNCRCYROLENAME",
"I_WBSELEMENTBASICDATA",
"P_CURMATLVALNPRICE_CR_JOIN",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLHD",
"MARA",
"T001K"
],
"ASSOCIATED":
[
"I_MATLLEDGERVALNCRCYROLENAME"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/