R_MatlPriceSpclStockValnStrgy

@AbapCatalog.sqlViewName: 'RMPSSVSTRGY'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Mat Price Spec Stock Valuation Strategy'
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.lifecycle.contract.type: #SAP_INTERNAL_API
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #MIXED
define view R_MatlPriceSpclStockValnStrgy
  as select from ckmlpr                         as pr
    inner join   R_MatlPriceDataMigrationStatus as tf    on(
         tf.MatlPriceDataMigrationStatus                                                   = 'X'
       )
                                                         or(
                                                           tf.MatlPriceDataMigrationStatus = 'R'
                                                         )
    inner join   ckmlhd                         as hd    on  hd.kalnr =  pr.kalnr
                                                         and hd.sobkz <> ''
    inner join   t001k                          as t     on t.bwkey = hd.bwkey
    inner join   t001w                          as p     on p.bwkey = hd.bwkey
    inner join   I_MatlLedgerValnCrcyRoleName   as curtp on  curtp.CompanyCode                 = t.bukrs
                                                         and curtp.LedgerValuationCurrencyRole = pr.curtp
                                                         
{
  key pr.kalnr                               as CostEstimate,
  key curtp.CurrencyRole                     as CurrencyRole,
  key curtp.Ledger                           as Ledger,
      cast( '9999012' as fml_yearper_nconv ) as MatlPrcValdtyEndFsclYearPeriod,
      hd.matnr                               as Material,
      hd.bwkey                               as ValuationArea,
      hd.bwtar                               as ValuationType,
      p.werks                                as Plant,
      hd.vbeln                               as SalesOrder,
      hd.posnr                               as SalesOrderItem,
      cast ( hd.pspnr as ps_psp_pnr_core )   as WBSElementInternalID,
      cast( 'INVPR' as fml_price_type )      as MaterialPriceType,
      pr.lbwst                               as CurPlanProjSlsOrdValnStrategy
}
where
  pr.sdm_version is initial

union all select from    I_MaterialLedgerPrice          as fmltp
  inner join             R_MatlPriceDataMigrationStatus as tf    on(
       tf.MatlPriceDataMigrationStatus                                                             = 'F'
     )
                                                                 or(
                                                                   tf.MatlPriceDataMigrationStatus = 'R'
                                                                 )

  left outer to one join ckmlhd                         as hd    on hd.kalnr = fmltp.CostEstimate

  inner join             t001w                          as plant on plant.bwkey = fmltp.ValuationArea

{
  key fmltp.CostEstimate                                     as CostEstimate,
  key fmltp.CurrencyRole                                     as CurrencyRole,
  key fmltp.Ledger                                           as Ledger,
      fmltp.MatlPrcValdtyEndFsclYearPeriod                   as MatlPrcValdtyEndFsclYearPeriod,
      fmltp.Material                                         as Material,
      fmltp.ValuationArea                                    as ValuationArea,
      fmltp.InventoryValuationType                           as ValuationType,
      plant.werks                                            as Plant,
      fmltp.SalesOrder                                       as SalesOrder,
      fmltp.SalesOrderItem                                   as SalesOrderItem,
      cast ( fmltp.WBSElementInternalID as ps_psp_pnr_core ) as WBSElementInternalID,
      fmltp.MaterialPriceType                                as MaterialPriceType,
      fmltp.CurPlanProjSlsOrdValnStrategy                    as CurPlanProjSlsOrdValnStrategy
}
where
  (
    (
          fmltp.MaterialPriceType             =  'INVPR'
      and fmltp.MaterialPriceControl          =  'S'
    )
    or(
          fmltp.MaterialPriceType             =  'STDPR'
    )
  )
  and     fmltp.MatlPrcValidityEndFiscalYear  =  '9999'
  and     fmltp.StkMaterialPriceValuationType <> 'M'
  and(
    (
          hd.price_sdm_yearper                <> '0000000'
      and hd.price_sdm_yearper                <= fmltp.MatlPrcValdtyStrtFsclYearPerd
    )
    or(
          tf.MatlPriceDataMigrationStatus     =  'F'
    )
  )
//      and fmltp.CurPlanProjSlsOrdValnStrategy <> ' ')

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIALLEDGERPRICE",
"I_MATLLEDGERVALNCRCYROLENAME",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLHD",
"CKMLPR",
"T001K",
"T001W"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/