P_CURRENTMATLVALNPRICE

@AbapCatalog.sqlViewName: 'PCURMATLVALNPRC'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #BASIC
define view P_CURRENTMATLVALNPRICE

  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 join        ckmlhd                                                     as hd         on hd.kalnr = crjoin.CostEstimate
    left outer join        ckmlpr                                                     as pr         on  pr.kalnr = crjoin.CostEstimate
                                                                                                    and pr.curtp = crjoin.CurrencyRole
    left outer join        prps                                                       as ps         on ps.pspnr = 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  cast( crjoin.CurrencyRole as fins_ext_curtype preserving type ) 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,
       hd.pspnr                                                        as WBSElementInternalID,
       ps.posid_edit                                                   as 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,
       crjoin.MovingAveragePrice,
       crjoin.StandardPrice,
       @Semantics.amount.currencyCode: 'Currency'
       crjoin.InventoryPrice,

       pr.zkprs                                                        as FuturePrice,
       pr.zprsdat                                                      as FuturePriceValidityStartDate,
       cast(pr.zplpr as fml_plpr)                                      as PlannedPrice,
       pr.bwva1                                                        as FutrCostEstValuationVariant,
       pr.pdatz                                                        as FutureCostEstFiscalYear,
       pr.pprdz                                                        as FutureCostEstimateFiscalPeriod,
       pr.vers1                                                        as FutureCostEstCostingVersion,
       //pr.zbwst                   as FutrPlanProjSlsOrdValnStrategy,

       cast(pr.fplpx as fml_plpx)                                      as FixedPortionOfPlannedPrice,

       _MLCurrencyInfo.LedgerName                                      as LedgerName,
       _MLCurrencyInfo.CurrencyRoleName                                as CurrencyRoleName,
       //       _CurrencyInfo.LegalCurrencyRole                             as LegalCurrencyRole,

       //       _CurrencyInfo.LegalCurrencyRoleName                         as LegalCurrencyRoleName,

       //       cast(_CurrencyInfo.AccountingValuationView as fins_valutyp) as AccountingValuationView,

       //       _CurrencyInfo.AcctgValnVwSubviewCombinedName                as AcctgValnVwSubviewCombinedName,

       _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 join        prps                                                       as ps         on ps.pspnr = 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'
association [0..1] to I_MaterialLedgerPrice        as _ActualPrice    on  _ActualPrice.CostEstimate                   = invprice.CostEstimate
                                                                      and _ActualPrice.Ledger                         = invprice.Ledger
                                                                      and _ActualPrice.CurrencyRole                   = invprice.CurrencyRole
                                                                      and _ActualPrice.MaterialPriceType              = 'ACT'
                                                                      and _ActualPrice.MaterialPriceSubtype           = 'ACT'
                                                                      and _ActualPrice.MaterialPriceValidityEndDate   = '99991231'
association [0..1] to I_MaterialLedgerPrice        as _MvgAvgPrice    on  _MvgAvgPrice.CostEstimate                   = invprice.CostEstimate
                                                                      and _MvgAvgPrice.Ledger                         = invprice.Ledger
                                                                      and _MvgAvgPrice.CurrencyRole                   = invprice.CurrencyRole
                                                                      and _MvgAvgPrice.MaterialPriceType              = 'MAP'
                                                                      and _MvgAvgPrice.MaterialPriceSubtype           = ' '
                                                                      and _MvgAvgPrice.MaterialPriceValidityEndDate   = '99991231'                                                                      
association [0..1] to I_MaterialLedgerPrice        as _FuturePrice    on  _FuturePrice.CostEstimate                   = invprice.CostEstimate
                                                                      and _FuturePrice.Ledger                         = invprice.Ledger
                                                                      and _FuturePrice.CurrencyRole                   = invprice.CurrencyRole
                                                                      and _FuturePrice.MaterialPriceType              = 'FUTURE'
                                                                      and _FuturePrice.MaterialPriceSubtype           = ' '
association [0..1] to I_MLPrExtRefCostEstimate     as _FutureStdPrice on  _FutureStdPrice.CostEstimate         = invprice.CostEstimate
                                                                      and _FutureStdPrice.Ledger               = invprice.Ledger
                                                                      and _FutureStdPrice.CurrencyRole         = invprice.CurrencyRole
                                                                      and _FutureStdPrice.MaterialPriceType    = 'FSTDCE'
                                                                      and _FutureStdPrice.MaterialPriceSubtype = ' '
// There should be only one entry per material

//                                                                   and _FutureStdPrice.MaterialPriceValidityStartDate <= $session.system_date

//                                                                   and _FutureStdPrice.MaterialPriceValidityEndDate   >= $session.system_date


{
  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,
      hd.pspnr                                       as WBSElementInternalID,
      ps.posid_edit                                  as 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 'S'
            then case hd.mlast
                when '2' then coalesce(_MvgAvgPrice.MaterialPrice, 0)
                when '3' then coalesce(_ActualPrice.MaterialPrice, 0)
                else cast (0 as fml_price )
            end
          else //when 'V'

            coalesce(invprice.MaterialPrice, 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,
      _FuturePrice.MaterialPrice                     as FuturePrice,
      _FuturePrice.MaterialPriceValidityStartDate    as FuturePriceValidityStartDate,
      _FutureStdPrice.MaterialPrice                  as PlannedPrice,
      _FutureStdPrice.ValuationVariant               as FutrCostEstValuationVariant,
      _FutureStdPrice.MatlPrcValidityStartFiscalYear as FutureCostEstFiscalYear,
      _FutureStdPrice.MatlPrcValdtyStartFiscalPeriod as FutureCostEstimateFiscalPeriod,
      _FutureStdPrice.CostingVersion                 as FutureCostEstCostingVersion,
      //pr.zbwst                   as FutrPlanProjSlsOrdValnStrategy,

      _FutureStdPrice.FixedPortionOfMaterialPrice    as FixedPortionOfPlannedPrice,

      _MLCurrencyInfo.LedgerName                     as LedgerName,
      _MLCurrencyInfo.CurrencyRoleName               as CurrencyRoleName,
      //      _CurrencyInfo.LegalCurrencyRole                             as LegalCurrencyRole,

      //      _CurrencyInfo.LegalCurrencyRoleName                         as LegalCurrencyRoleName,

      //      cast(_CurrencyInfo.AccountingValuationView as fins_valutyp) as AccountingValuationView,

      //      _CurrencyInfo.AcctgValnVwSubviewCombinedName                as AcctgValnVwSubviewCombinedName,

      _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'
    //    and     invprice.MaterialPriceValidityStartDate <= $session.system_date

    //    and     invprice.MaterialPriceValidityEndDate   >= $session.system_date   9999012‘

  )