P_MaterialValuation

@AbapCatalog.sqlViewName: 'PMATLVALN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.private: true
@VDM.viewType: #COMPOSITE
define view P_MaterialValuation
  as select from           I_MaterialLedgerPrice    as price
    left outer to one join I_MaterialValuation      as xbew on xbew.CostEstimate = price.CostEstimate
    left outer to one join R_MatlLedgerValnCrcyRole as crcy on  price.Ledger       = crcy.Ledger
                                                            and price.CurrencyRole = crcy.CurrencyRole
                                                            and price.CompanyCode  = crcy.CompanyCode
    left outer to one join P_Matval_Manage_Baltab   as bal  on  bal.CostEstimate = price.CostEstimate
                                                            and bal.Ledger       = price.Ledger
                                                            and bal.CompanyCode  = price.CompanyCode
    left outer to one join I_WBSElementBasicData    as ps   on ps.WBSElementInternalID = xbew.WBSElement

  association [0..1] to I_MaterialLedgerPrice as _INVPR on  _INVPR.CostEstimate                   = price.CostEstimate
                                                        and _INVPR.Ledger                         = price.Ledger
                                                        and _INVPR.CurrencyRole                   = price.CurrencyRole
                                                        and _INVPR.MaterialPriceType              = 'INVPR'
                                                        and _INVPR.MaterialPriceSubtype           = ''
                                                        and _INVPR.MatlPrcValdtyEndFsclYearPeriod = '9999012'

  association [0..1] to I_MaterialLedgerPrice as _STDPR on  _STDPR.CostEstimate                   = price.CostEstimate
                                                        and _STDPR.Ledger                         = price.Ledger
                                                        and _STDPR.CurrencyRole                   = price.CurrencyRole
                                                        and _STDPR.MaterialPriceType              = 'STDPR'
                                                        and _STDPR.MaterialPriceSubtype           = ''
                                                        and _STDPR.MatlPrcValdtyEndFsclYearPeriod = '9999012'

  association [0..1] to I_MaterialLedgerPrice as _ACT   on  _ACT.CostEstimate                   = price.CostEstimate
                                                        and _ACT.CurrencyRole                   = price.CurrencyRole
                                                        and _ACT.Ledger                         = price.Ledger
                                                        and _ACT.MaterialPriceType              = 'ACT'
                                                        and _ACT.MaterialPriceSubtype           = 'ACT'
                                                        and _ACT.MatlPrcValdtyEndFsclYearPeriod = '9999012'

  association [0..1] to I_MaterialLedgerPrice as _MAP   on  _MAP.CostEstimate                   = price.CostEstimate
                                                        and _MAP.CurrencyRole                   = price.CurrencyRole
                                                        and _MAP.Ledger                         = price.Ledger
                                                        and _MAP.MaterialPriceType              = 'MAP'
                                                        and _MAP.MaterialPriceSubtype           = ''
                                                        and _MAP.MatlPrcValdtyEndFsclYearPeriod = '9999012'

{
  key price.CostEstimate,
  key price.CurrencyRole,
  key price.Ledger,

      price.MaterialPriceValidityStartDate,
      price.MaterialPriceValidityEndDate,

      price.Material,
      price.ValuationArea,
      price.InventoryValuationType,
      price.CompanyCode,
      xbew.InventoryValuationCategory,
      xbew.MaterialValuationClass,
      xbew.SDDocument                                                      as SalesOrder,
      xbew.SDDocumentItem                                                  as SalesOrderItem,
      xbew.Supplier,
      ps.WBSElementInternalID,
      xbew.InventorySpecialStockType,

      xbew.PriceDeterminationControl,
      price.ValuationQuantityUnit                                          as BaseUnit,
      price.MaterialPriceUnitQty,
      price.Currency,
      _INVPR.MaterialPriceControl,
      _INVPR.MaterialPrice                                                 as InventoryPrice,

      case _INVPR.MaterialPriceControl
        when 'S'
            then case xbew.PriceDeterminationControl
                when '2' then coalesce(_MAP.MaterialPrice,0)
                when '3' then coalesce(_ACT.MaterialPrice,0)
                else cast (0 as fml_price )
            end
        else //when 'V'

            coalesce(_INVPR.MaterialPrice,0)
        end                                                                as MovingAveragePrice,

      case _INVPR.MaterialPriceControl
        when 'S' then coalesce(_INVPR.MaterialPrice,0)
        else coalesce(_STDPR.MaterialPrice,0)
        end                                                                as StandardPrice,

      price.CurPlanProjSlsOrdValnStrategy,

      cast ( coalesce(bal.AmountInCompanyCodeCurrency, 0) as fins_vhcur12) as AmountInCompanyCodeCurrency,

      cast ( coalesce(bal.TotalVltdStockQuantity, 0) as fin_qtyval)        as TotalVltdStockQuantity,

      case crcy.JrnlEntrItemCrcyRolePosition
        when 'H' then coalesce(cast(bal.AmountInCompanyCodeCurrency as salk3), 0)
        when 'K' then coalesce(cast(bal.AmountInGroupCurrency as salk3), 0)
        when 'O' then coalesce(cast(bal.AmountInFreeDefinedCurrency1 as salk3), 0)
        when 'V' then coalesce(cast(bal.AmountInFreeDefinedCurrency2 as salk3), 0)
        when 'B' then coalesce(cast(bal.AmountInFreeDefinedCurrency3 as salk3), 0)
        when 'C' then coalesce(cast(bal.AmountInFreeDefinedCurrency4 as salk3), 0)
        when 'D' then coalesce(cast(bal.AmountInFreeDefinedCurrency5 as salk3), 0)
        when 'E' then coalesce(cast(bal.AmountInFreeDefinedCurrency6 as salk3), 0)
        when 'F' then coalesce(cast(bal.AmountInFreeDefinedCurrency7 as salk3), 0)
        when 'G' then coalesce(cast(bal.AmountInFreeDefinedCurrency8 as salk3), 0)
        else cast(0 as salk3)
      end                                                                  as TotalValuatedStockAmount,

      crcy.LedgerValnCrcyRoleIsUsedInLogs,
      cast(crcy.AccountingValuationView as fins_valutyp)                   as AccountingValuationView,
      crcy.LedgerName,
      crcy.CurrencyRoleName

}
where
          price.MaterialPriceType              =  'FUTURE'
  and     price.MaterialPriceSubtype           =  ' '
  and     price.MatlPrcValdtyEndFsclYearPeriod =  '9999012'
  and(
          price.InventoryValuationType         <> ''
    or(
          price.InventoryValuationType         =  ''
      and xbew.InventoryValuationCategory      =  ''
    )
  )



union all // mother segments

select from              I_MaterialLedgerPrice     as price
  left outer to one join I_MaterialValuation       as xbew on xbew.CostEstimate = price.CostEstimate
  left outer to one join R_MatlLedgerValnCrcyRole  as crcy on  price.Ledger       = crcy.Ledger
                                                           and price.CurrencyRole = crcy.CurrencyRole
                                                           and price.CompanyCode  = crcy.CompanyCode
  left outer to one join P_Matval_Manage_Bal_Split as bal  on  bal.CostEstimate  = price.CostEstimate
                                                           and bal.period_type   = 'LF'
                                                           and bal.ValuationArea = price.ValuationArea
                                                           and bal.Ledger        = price.Ledger
                                                           and bal.CompanyCode   = price.CompanyCode
  left outer to one join I_WBSElementBasicData     as ps   on ps.WBSElementInternalID = xbew.WBSElement

association [0..1] to I_MaterialLedgerPrice as _INVPR on  _INVPR.CostEstimate                   = price.CostEstimate
                                                      and _INVPR.Ledger                         = price.Ledger
                                                      and _INVPR.CurrencyRole                   = price.CurrencyRole
                                                      and _INVPR.MaterialPriceType              = 'INVPR'
                                                      and _INVPR.MaterialPriceSubtype           = ''
                                                      and _INVPR.MatlPrcValdtyEndFsclYearPeriod = '9999012'

association [0..1] to I_MaterialLedgerPrice as _STDPR on  _STDPR.CostEstimate                   = price.CostEstimate
                                                      and _STDPR.Ledger                         = price.Ledger
                                                      and _STDPR.CurrencyRole                   = price.CurrencyRole
                                                      and _STDPR.MaterialPriceType              = 'STDPR'
                                                      and _STDPR.MaterialPriceSubtype           = ''
                                                      and _STDPR.MatlPrcValdtyEndFsclYearPeriod = '9999012'

{
  key price.CostEstimate,
  key price.CurrencyRole,
  key price.Ledger,

      price.MaterialPriceValidityStartDate,
      price.MaterialPriceValidityEndDate,

      price.Material,
      price.ValuationArea,
      price.InventoryValuationType,
      price.CompanyCode,
      xbew.InventoryValuationCategory,
      xbew.MaterialValuationClass,
      xbew.SDDocument                                                      as SalesOrder,
      xbew.SDDocumentItem                                                  as SalesOrderItem,
      xbew.Supplier,
      ps.WBSElementInternalID,
      xbew.InventorySpecialStockType,

      xbew.PriceDeterminationControl,
      price.ValuationQuantityUnit                                          as BaseUnit,
      price.MaterialPriceUnitQty,
      price.Currency,

      cast ('V' as vprsv)                                                  as MaterialPriceControl,

      coalesce(_INVPR.MaterialPrice,0)                                     as InventoryPrice,
      coalesce(_INVPR.MaterialPrice,0)                                     as MovingAveragePrice,
      coalesce(_STDPR.MaterialPrice,0)                                     as StandardPrice,

      price.CurPlanProjSlsOrdValnStrategy,

      cast ( coalesce(bal.AmountInCompanyCodeCurrency, 0) as fins_vhcur12) as AmountInCompanyCodeCurrency,

      cast ( coalesce(bal.TotalVltdStockQuantity, 0) as fin_qtyval)        as TotalVltdStockQuantity,

      case crcy.JrnlEntrItemCrcyRolePosition
        when 'H' then coalesce(cast(bal.AmountInCompanyCodeCurrency as salk3), 0)
        when 'K' then coalesce(cast(bal.AmountInGroupCurrency as salk3), 0)
        when 'O' then coalesce(cast(bal.AmountInFreeDefinedCurrency1 as salk3), 0)
        when 'V' then coalesce(cast(bal.AmountInFreeDefinedCurrency2 as salk3), 0)
        when 'B' then coalesce(cast(bal.AmountInFreeDefinedCurrency3 as salk3), 0)
        when 'C' then coalesce(cast(bal.AmountInFreeDefinedCurrency4 as salk3), 0)
        when 'D' then coalesce(cast(bal.AmountInFreeDefinedCurrency5 as salk3), 0)
        when 'E' then coalesce(cast(bal.AmountInFreeDefinedCurrency6 as salk3), 0)
        when 'F' then coalesce(cast(bal.AmountInFreeDefinedCurrency7 as salk3), 0)
        when 'G' then coalesce(cast(bal.AmountInFreeDefinedCurrency8 as salk3), 0)
        else cast(0 as salk3)
      end                                                                  as TotalValuatedStockAmount,

      crcy.LedgerValnCrcyRoleIsUsedInLogs,
      cast(crcy.AccountingValuationView as fins_valutyp)                   as AccountingValuationView,
      crcy.LedgerName,
      crcy.CurrencyRoleName

}
where
      price.MaterialPriceType              =  'FUTURE'
  and price.MaterialPriceSubtype           =  ' '
  and price.MatlPrcValdtyEndFsclYearPeriod =  '9999012'
  and xbew.InventoryValuationCategory      <> ''
  and price.InventoryValuationType         =  ''