I_CurrentMaterialValuations

@EndUserText.label: 'Current Material Valuations'
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED

define view entity I_CurrentMaterialValuations
  as select from P_MATVAL_MANAGE_MD_FMLT     as md
    left outer to one join I_MaterialLedgerPrice       as INVPR     on  md.CostEstimate                      = INVPR.CostEstimate
                                                                          and md.Ledger                            = INVPR.Ledger
                                                                          and md.CurrencyRole                      = INVPR.CurrencyRole
                                                                          and md.CompanyCode                       = INVPR.CompanyCode
                                                                          and md.ValuationArea                     = INVPR.ValuationArea
                                                                          and INVPR.MaterialPriceType              = 'INVPR'
                                                                          and INVPR.MaterialPriceSubtype           = ''
                                                                          and INVPR.MatlPrcValdtyEndFsclYearPeriod = '9999012'
                                                                          and INVPR.Material                       = md.Material
                                                                          and INVPR.CompanyCode                    = md.CompanyCode
                                                                          and INVPR.ValuationArea                  = md.ValuationArea
    left outer to one join       R_MatlLedgerValnCrcyRole    as lvcr      on  md.Ledger       = lvcr.Ledger
                                                                          and md.CurrencyRole = lvcr.CurrencyRole
                                                                          and md.CompanyCode  = lvcr.CompanyCode
    left outer to one join       P_Matval_Manage_Baltab      as bal       on  bal.CostEstimate  = md.CostEstimate
                                                                          and bal.Ledger        = md.Ledger
                                                                          and bal.CompanyCode   = md.CompanyCode
                                                                          and bal.ValuationArea = md.ValuationArea
                                                                          and bal.Material      = md.Material
    left outer to one join       P_Matval_Manage_Bal_Split   as bal_split on  bal_split.CostEstimate  = md.CostEstimate
                                                                          and bal_split.Ledger        = md.Ledger
                                                                          and bal_split.CompanyCode   = md.CompanyCode
                                                                          and bal_split.ValuationArea = md.ValuationArea
                                                                          and bal_split.Material      = md.Material

    left outer to one join       I_FiscalYearPeriodForLedger as fpi       on  fpi.CompanyCode  = md.CompanyCode
                                                                          and fpi.Ledger       = md.Ledger
                                                                          and fpi.FiscalYear   = md.CurrentFiscalYear
                                                                          and fpi.FiscalPeriod = md.CurrentFiscalPeriod
    left outer to one join       I_Plant                     as plt       on plt.ValuationArea = md.ValuationArea

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

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

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

  association [0..1] to I_MaterialLedgerPrice as _FSTDCE       on  _FSTDCE.CostEstimate                   = md.CostEstimate
                                                               and _FSTDCE.CurrencyRole                   = md.CurrencyRole
                                                               and _FSTDCE.Ledger                         = md.Ledger
                                                               and _FSTDCE.MaterialPriceType              = 'FSTDCE'
                                                               and _FSTDCE.MaterialPriceSubtype           = ''
  //                                                             and _FSTDCE.MatlPrcValdtyEndFsclYearPeriod = '9999012'

                                                               and _FSTDCE.Material                       = md.Material
                                                               and _FSTDCE.Material                       = md.Material
                                                               and _FSTDCE.CompanyCode                    = md.CompanyCode
                                                               and _FSTDCE.ValuationArea                  = md.ValuationArea
                                                               and _FSTDCE.MatlPrcValidityStartFiscalYear = md.CurrentFiscalYear
                                                               and _FSTDCE.MatlPrcValdtyStartFiscalPeriod = md.CurrentFiscalPeriod

  association [0..1] to I_CompanyCode         as _CompanyCode  on  $projection.CompanyCode = _CompanyCode.CompanyCode
  association [0..1] to I_MaterialText        as _MaterialText on  $projection.Material   = _MaterialText.Material
                                                               and _MaterialText.Language = $session.system_language
{
  key       md.CostEstimate,
            @UI.hidden: true
  key       lvcr.LedgerValuationCurrencyRole,

            lvcr.LegalCurrencyRole,
            lvcr.Ledger,
            lvcr.AccountingValuationView,
            lvcr.AccountingValuationSubview,

            @UI.hidden: true
            lvcr.CurrencyRole,
            lvcr.LegalCurrencyRoleName,
            lvcr.AccountingValuationViewName,
            lvcr.AccountingValuationSubviewName,
            lvcr.AcctgValnVwSubviewCombinedName,
            lvcr.LedgerName,

            md.Material,
            md.ValuationArea,
            md.InventoryValuationType,

            _CompanyCode.ControllingArea,

            md.InvtryValnSpecialStockType,
            md.SalesOrder,
            md.SalesOrderItem,
            md.Supplier,
            md.WBSElementInternalID,
            md.WBSElementExternalID,
            @Semantics.amount.currencyCode : 'Currency'
            md.FuturePrice,
            md.FuturePriceValidityStartDate,
            @Semantics.amount.currencyCode: 'Currency'
            coalesce( _FSTDCE.MaterialPrice, cast(0 as fml_futureplannedprice))                                as FuturePlannedPrice,

            @Semantics.quantity.unitOfMeasure : 'BaseUnit'
            case
               when   md.InventoryValuationType     <> ''  then bal.TotalVltdStockQuantity
               when   md.InventoryValuationType     =  ''  and md.InventoryValuationCategory =  ''    then bal.TotalVltdStockQuantity
               when   md.InventoryValuationType     =  ''  and md.InventoryValuationCategory <> ''   then bal_split.TotalVltdStockQuantity
            end                                                  as TotalVltdStockQuantity,

            @Semantics.amount.currencyCode : 'Currency'
            case
               when   md.InventoryValuationType     <> ''  then
               (
                  case lvcr.JrnlEntrItemCrcyRolePosition
                     when 'H' then cast(bal.AmountInCompanyCodeCurrency as fml_salk3)
                     when 'K' then cast(bal.AmountInGroupCurrency as fml_salk3)
                     when 'O' then cast(bal.AmountInFreeDefinedCurrency1 as fml_salk3)
                     when 'V' then cast(bal.AmountInFreeDefinedCurrency2 as fml_salk3)
                     when 'B' then cast(bal.AmountInFreeDefinedCurrency3 as fml_salk3)
                     when 'C' then cast(bal.AmountInFreeDefinedCurrency4 as fml_salk3)
                     when 'D' then cast(bal.AmountInFreeDefinedCurrency5 as fml_salk3)
                     when 'E' then cast(bal.AmountInFreeDefinedCurrency6 as fml_salk3)
                     when 'F' then cast(bal.AmountInFreeDefinedCurrency7 as fml_salk3)
                     when 'G' then cast(bal.AmountInFreeDefinedCurrency8 as fml_salk3)
                  end
               )

               when   md.InventoryValuationType     =  ''  and md.InventoryValuationCategory =  ''    then
               (
                  case lvcr.JrnlEntrItemCrcyRolePosition
                     when 'H' then cast(bal.AmountInCompanyCodeCurrency as fml_salk3)
                     when 'K' then cast(bal.AmountInGroupCurrency as fml_salk3)
                     when 'O' then cast(bal.AmountInFreeDefinedCurrency1 as fml_salk3)
                     when 'V' then cast(bal.AmountInFreeDefinedCurrency2 as fml_salk3)
                     when 'B' then cast(bal.AmountInFreeDefinedCurrency3 as fml_salk3)
                     when 'C' then cast(bal.AmountInFreeDefinedCurrency4 as fml_salk3)
                     when 'D' then cast(bal.AmountInFreeDefinedCurrency5 as fml_salk3)
                     when 'E' then cast(bal.AmountInFreeDefinedCurrency6 as fml_salk3)
                     when 'F' then cast(bal.AmountInFreeDefinedCurrency7 as fml_salk3)
                     when 'G' then cast(bal.AmountInFreeDefinedCurrency8 as fml_salk3)
                  end
               )
               when   md.InventoryValuationType     =  ''  and md.InventoryValuationCategory <> ''   then
               (
                  case lvcr.JrnlEntrItemCrcyRolePosition
                     when 'H' then cast(bal_split.AmountInCompanyCodeCurrency as fml_salk3)
                     when 'K' then cast(bal_split.AmountInGroupCurrency as fml_salk3)
                     when 'O' then cast(bal_split.AmountInFreeDefinedCurrency1 as fml_salk3)
                     when 'V' then cast(bal_split.AmountInFreeDefinedCurrency2 as fml_salk3)
                     when 'B' then cast(bal_split.AmountInFreeDefinedCurrency3 as fml_salk3)
                     when 'C' then cast(bal_split.AmountInFreeDefinedCurrency4 as fml_salk3)
                     when 'D' then cast(bal_split.AmountInFreeDefinedCurrency5 as fml_salk3)
                     when 'E' then cast(bal_split.AmountInFreeDefinedCurrency6 as fml_salk3)
                     when 'F' then cast(bal_split.AmountInFreeDefinedCurrency7 as fml_salk3)
                     when 'G' then cast(bal_split.AmountInFreeDefinedCurrency8 as fml_salk3)
                  end
               )
            end                                                  as TotalValuatedStockAmount,

            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            case
               when   md.InventoryValuationType     <> ''  then bal.AmountInCompanyCodeCurrency
               when   md.InventoryValuationType     =  ''  and md.InventoryValuationCategory =  ''    then bal.AmountInCompanyCodeCurrency
               when   md.InventoryValuationType     =  ''  and md.InventoryValuationCategory <> ''   then bal_split.AmountInCompanyCodeCurrency
            end                                                  as AmountInCompanyCodeCurrency,

            INVPR.MaterialPriceControl,
            md.PriceDeterminationControl,
            INVPR.MaterialPrice                                  as InventoryPrice,

            @Semantics.amount.currencyCode : 'Currency'
            case
               when INVPR.MaterialPriceControl = 'S' and md.PriceDeterminationControl = '2' then  _MAP.MaterialPrice
               when INVPR.MaterialPriceControl = 'S' and md.PriceDeterminationControl = '3' then  _ACT.MaterialPrice
               when INVPR.MaterialPriceControl = 'V'  then  INVPR.MaterialPrice
            end                                                  as MovingAveragePrice,

            @Semantics.amount.currencyCode : 'Currency'
            case
              when INVPR.MaterialPriceControl = 'S' then INVPR.MaterialPrice
              when INVPR.MaterialPriceControl = 'V' then _STDPR.MaterialPrice
            end                                                  as StandardPrice,

            INVPR.MaterialPriceUnitQty,
            md.CompanyCode,
            lvcr.Currency,
            cast(_CompanyCode.Currency as fis_hwaer)             as CompanyCodeCurrency,

            md.BaseUnit,
            md.ValuationQuantityUnit,
            md.MaterialGroup,
            md.MaterialType,

            md.PlntMatlIsMarkedForDeletion,
            md.AssemblyScrapPercent,
            md.MaterialProcurementCategory,
            md.MaterialProcurementProfile,
            md.ProfitCenter,
            md.HasProductionVersion,
            md.AlternativeBillOfMaterial,
            md.BillOfMaterialVariantUsage,
            md.TaskListGroup,
            md.TaskListGroupCounter,
            @Semantics.quantity.unitOfMeasure: 'BASEUNIT'
            md.CostingLotSize,
            @ObjectModel.sapObjectNodeTypeReference:'ProcurementSubType'
            md.CostingMatProcurementProfile,
            md.IsCoProduct,
            md.ComponentScrapInPercent,
            md.IsBulkMaterial,
            md.BillOfOperationsType,
            md.CostingProductionVersion,
            md.IsFixedPriceCoProduct,
            md.IsBlockedForCosting,
            md.IsMatlOfValnTypeMarkedForDeltn,
            md.MaterialUsabilityProfile,
            md.MaterialValuationClass,
            md.ValuationClassInPrevPeriod,
            md.ValuationClassInPrevYear,
            md.CurrentFiscalYear,
            md.CurrentFiscalPeriod,
            md.PreviousFiscalPeriodFiscalYear,
            md.PreviousFiscalPeriod,
            md.PreviousFiscalYear,
            md.PreviousFiscalYearPeriod,
            md.InventoryValuationCategory,
            md.PriceLastChangeDate,
//            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'

            md.TaxPricel1InCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.CommercialPrice1InCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.TaxPrice3InCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.CommercialPrice3InCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.PlannedPriceInCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.PlannedPrice1InCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.PlannedPrice2InCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.PlannedPrice3InCoCodeCrcy,

            md.FuturePlndPrice1ValdtyDate,
            md.FuturePlndPrice2ValdtyDate,
            md.FuturePlndPrice3ValdtyDate,
            md.HasStdCostEstForFuturePeriod,
            md.HasStdCostEstForCurrentPeriod,
            md.HasStdCostEstForPreviousPeriod,
            md.IsLIFOAndFIFORelevant,
            md.LIFOValuationPoolNumber,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.CommercialPrice2InCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.TaxPrice2InCoCodeCrcy,
            md.DevaluationYearCount,
            md.MaintenanceStatus,
            md.FutrCostEstValuationVariant,
            md.CurCostEstValuationVariant,
            md.PrevCostEstValuationVariant,
            md.FutureCostEstCostingVersion,
            md.CurrentCostEstCostingVersion,
            md.PreviousCostEstCostingVersion,
            md.CostOriginGroup,
            md.CostingOverheadGroup,
            md.FutureCostEstimateFiscalPeriod,
            md.CurrentCostEstFiscalPeriod,
            md.PreviousCostEstFiscalPeriod,
            md.FutureCostEstFiscalYear,
            md.CurrentCostEstFiscalYear,
            md.PreviousCostEstFiscalYear,
            md.IsMaterialCostedWithQtyStruc,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.PreviousPlanPriceInCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.CurrentPlanPriceInCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            bal.TotStkValAtSlsPrcInCoCodeCrcy,
            md.IsMaterialRelatedOrigin,
            md.PhysicalInventoryBlockInd,
            md.DateOfLastPostedCntUnRstrcdStk,
            md.InventoryForCycleCountInd,
            md.ValuationMargin,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.FxdPortnOfPlanPrcInCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.FxdPortOfPrevPlPrcInCoCodeCrcy,
            @Semantics.amount.currencyCode : 'CompanyCodeCurrency'
            md.FxdPortnOfFutPlPrcInCoCodeCrcy,
            md.CurPlanProjSlsOrdValnStrategy,
            md.PrevPlanProjSlsOrdValnStrategy,
            md.FutrPlanProjSlsOrdValnStrategy,
            md.ValuationClassSalesOrderStock,
            md.ProjectStockValuationClass,
            md.ProductUsageType,
            md.ProductOriginType,
            md.IsProducedInhouse,
            md.ValnIsBsdOnBatchSpecificUnit,
            @Semantics.quantity.unitOfMeasure : 'BaseUnit'
            md.TaxBasedPricesPriceUnitQty,
            md.ValnClsForSpclStkAtVndr,

            fpi.FiscalPeriodEndDate,
            dats_add_days(fpi.FiscalPeriodEndDate, 1, 'INITIAL') as NextFiscalPeriodStartDate,

            lvcr.AccountingValuationView                         as ControllingValuationType,

            @Semantics.text: true
            _MaterialText.MaterialName,

            @Semantics.text: true
            _CompanyCode.CompanyCodeName,

            @ObjectModel.text.element: [ 'PlantName']
            plt.Plant,
            @Semantics.text: true
            plt.PlantName,

            // associations

            _CompanyCode._ControllingArea
}