P_MaterialValuationPrices

@AbapCatalog.sqlViewName: 'PMATVALPRICES'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.private: true
@VDM.viewType: #COMPOSITE
define view P_MaterialValuationPrices
  as select from           P_MlPrice_Period_By_Periodtype as mlper
    inner join   R_MatlPriceDataMigrationStatus as tf on(
      tf.MatlPriceDataMigrationStatus                                                   = 'X'
    )
                                                      or(
                                                        tf.MatlPriceDataMigrationStatus = 'R'
                                                      )                                                                                
    inner join             R_MaterialLedgerPriceCmptblty  as mlprc      on  mlprc.CostEstimate     = mlper.CostEstimate
                                                                        and mlprc.ValuationArea    = mlper.ValuationArea
                                                                        and mlprc.Material         = mlper.Material
                                                                        and mlprc.CurrencyRole     = mlper.CurrencyRole
                                                                        and mlprc.FiscalYearPeriod = mlper.MLPriceLatestFiscalYearPeriod
  association [0..1] to R_MatlLedgerValnCrcyRole as _MLCurrencyInfo on  _MLCurrencyInfo.LedgerValuationCurrencyRole = mlper.CurrencyRole
                                                                    and _MLCurrencyInfo.CompanyCode = mlper.CompanyCode

{
  key  mlper.CostEstimate,
  key  coalesce(_MLCurrencyInfo.Ledger, cast('  ' as fins_ledger))   as Ledger,
  key  mlper.CurrencyRole,
  key  mlper.FiscalYear,
  key  mlper.FiscalPeriod,
  key  PeriodType,
  key  FiscalPeriodType,
       mlper.Material,
       mlper.ValuationArea,
       mlprc.InventoryValuationType,
       SalesOrder,
       SalesOrderItem,
       Supplier,
       WBSElementInternalID,
       WBSElementExternalID,
       InvtryValnSpecialStockType,
       InventorySpecialStockValnType,
       IsSupplierStockValuation,
       CompanyCode,
       PriceDeterminationControl,
       MaterialPriceControl,
       MaterialPriceUnitQty,
       Currency,
       MovingAveragePrice,
       StandardPrice,
       @Semantics.amount.currencyCode: 'Currency'
       InventoryPrice,
       FuturePrice,
       FuturePriceValidityStartDate,
       PlannedPrice,
       FutrCostEstValuationVariant,
       FutureCostEstFiscalYear,
       FutureCostEstimateFiscalPeriod,
       FutureCostEstCostingVersion,
       //       FutrPlanProjSlsOrdValnStrategy,

       FixedPortionOfPlannedPrice,

       _MLCurrencyInfo.LedgerName                                    as LedgerName,
       _MLCurrencyInfo.CurrencyRoleName                              as CurrencyRoleName,
       _MLCurrencyInfo.LegalCurrencyRole                             as LegalCurrencyRole,
       _MLCurrencyInfo.LegalCurrencyRoleName                         as LegalCurrencyRoleName,
       _MLCurrencyInfo.AccountingValuationView                       as AccountingValuationView,
       _MLCurrencyInfo.AcctgValnVwSubviewCombinedName                as AcctgValnVwSubviewCombinedName,
       _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs                as LedgerValnCrcyRoleIsUsedInLogs

}

where
  ( (
       mlper.MatlPrcMigratedUntilFsclYrPerd = '0000000'
    or mlper.MatlPrcMigratedUntilFsclYrPerd > mlper.MLPriceLatestFiscalYearPeriod
  ) or tf.MatlPriceDataMigrationStatus = 'X' )

union all

select from              P_MlPrice_By_Periodtype   as mlprice
  inner join R_MatlPriceDataMigrationStatus as tf on(
    tf.MatlPriceDataMigrationStatus                                                 = 'F'
  )
                                                  or(
                                                    tf.MatlPriceDataMigrationStatus = 'R'
                                                  )                                                                              
association [0..1] to R_MatlLedgerValnCrcyRole as _MLCurrencyInfo on  _MLCurrencyInfo.CurrencyRole = mlprice.CurrencyRole
                                                                  and _MLCurrencyInfo.Ledger       = mlprice.Ledger
                                                                  and _MLCurrencyInfo.CompanyCode  = mlprice.CompanyCode

{
  key  CostEstimate,
  key  Ledger,
  key  CurrencyRole,
  key  FiscalYear,
  key  FiscalPeriod,
  key  PeriodType,
  key  FiscalPeriodType,
       Material,
       ValuationArea,
       InventoryValuationType,
       SalesOrder,
       SalesOrderItem,
       Supplier,
       WBSElementInternalID,
       WBSElementExternalID,
       InvtryValnSpecialStockType,
       InventorySpecialStockValnType,
       IsSupplierStockValuation,
       CompanyCode,
       PriceDeterminationControl,
       MaterialPriceControl,
       MaterialPriceUnitQty,
       Currency,
       MovingAveragePrice,
       StandardPrice,
       InventoryPrice,
       FuturePrice,
       FuturePriceValidityStartDate,
       PlannedPrice,
       FutrCostEstValuationVariant,
       FutureCostEstFiscalYear,
       FutureCostEstimateFiscalPeriod,
       FutureCostEstCostingVersion,
       //FutrPlanProjSlsOrdValnStrategy,

       FixedPortionOfPlannedPrice,

       _MLCurrencyInfo.LedgerName                                    as LedgerName,
       _MLCurrencyInfo.CurrencyRoleName                              as CurrencyRoleName,
       _MLCurrencyInfo.LegalCurrencyRole                             as LegalCurrencyRole,
       _MLCurrencyInfo.LegalCurrencyRoleName                         as LegalCurrencyRoleName,
       _MLCurrencyInfo.AccountingValuationView                       as AccountingValuationView,
       _MLCurrencyInfo.AcctgValnVwSubviewCombinedName                as AcctgValnVwSubviewCombinedName,
       _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs                as LedgerValnCrcyRoleIsUsedInLogs

}

where
  ( (
          price_sdm_yearper    <> '0000000'
      and price_sdm_yearper    <= MatlPrcValdtyStrtFsclYearPerd
    ) or tf.MatlPriceDataMigrationStatus = 'F'
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_MLPRICE_BY_PERIODTYPE",
"P_MLPRICE_PERIOD_BY_PERIODTYPE",
"R_MATERIALLEDGERPRICECMPTBLTY",
"R_MATLLEDGERVALNCRCYROLE",
"R_MATLPRICEDATAMIGRATIONSTATUS"
],
"ASSOCIATED":
[
"R_MATLLEDGERVALNCRCYROLE"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/