P_MatVal_By_Period_Types

@VDM.private: true
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PMTVLPERIODTYPES'
@AbapCatalog.compiler.compareFilter:true
// Inventory prices - Period types definition 

// special logic: if VM and VJ are the same, only vm is selected

define view P_MatVal_By_Period_Types
  as
  // Current Period

  select from  I_MaterialLedgerPrice      as price
    inner join R_MatlLedgerValnCrcyRole   as mlnc on mlnc.CompanyCode  = price.CompanyCode
                                                 and mlnc.Ledger       = price.Ledger
                                                 and mlnc.CurrencyRole = price.CurrencyRole

    inner join P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = price.CompanyCode 
                                                        and marvld.Ledger      = price.Ledger
    

{
  key price.CostEstimate,
  key marvld.CurrentFiscalYear              as FiscalYear,
  key marvld.CurrentFiscalPeriod            as FiscalPeriod,
  key mlnc.LedgerValuationCurrencyRole      as LedgerValuationCurrencyRole,
  key mlnc.Ledger                           as Ledger,
  key price.ValuationArea,


      marvld.CurrentFiscalYearPeriod        as FiscalYearPer,
      mlnc.CurrencyRole                     as CurrencyRole,
      price.CompanyCode,
      price.Material,
       @Semantics.amount.currencyCode: 'Currency'
      price.MaterialPrice,
      price.Currency,
      price.MaterialPriceControl,
      price.MatlPrcValdtyStrtFsclYearPerd,
      price.MatlPrcValdtyEndFsclYearPeriod,
      price.MaterialPriceUnitQty,
      price.ValuationQuantityUnit           as BaseUnit,
      price.InventoryValuationType,

      price.MatlPrcValdtyStrtFsclYearPerd   as MaxFiscPerCKMLPP,
      'LF'                                  as period_type,
      mlnc.JrnlEntrItemCrcyRolePosition

}
where
      price.MaterialPriceType              = 'INVPR'
  and price.MaterialPriceSubtype           = ' '
  and price.MatlPrcValdtyEndFsclYearPeriod = '9999012'

// Previous period

union all select from I_MaterialLedgerPrice       as price
  inner join R_MatlLedgerValnCrcyRole   as mlnc on mlnc.CompanyCode  = price.CompanyCode
                                               and mlnc.Ledger       = price.Ledger
                                               and mlnc.CurrencyRole = price.CurrencyRole

  inner join P_MatlCtrlRecdLdgrFiscalPeriod as marvld on  marvld.CompanyCode = price.CompanyCode 
                                                      and marvld.Ledger      = price.Ledger

{
  key price.CostEstimate,
  key marvld.PreviousFiscalPeriodFiscalYear as FiscalYear,
  key marvld.PreviousFiscalPeriod           as FiscalPeriod,
  key mlnc.LedgerValuationCurrencyRole      as LedgerValuationCurrencyRole,
  key mlnc.Ledger                           as Ledger,
  key price.ValuationArea,

      marvld.PreviousFiscalYearPeriod       as FiscalYearPer,
      mlnc.CurrencyRole                     as CurrencyRole,
      price.CompanyCode,
      price.Material,
       @Semantics.amount.currencyCode: 'Currency'
      price.MaterialPrice,
      price.Currency,
      price.MaterialPriceControl,
      price.MatlPrcValdtyStrtFsclYearPerd,
      price.MatlPrcValdtyEndFsclYearPeriod,
      price.MaterialPriceUnitQty,
      price.ValuationQuantityUnit           as BaseUnit,
      price.InventoryValuationType,

      price.MatlPrcValdtyStrtFsclYearPerd   as MaxFiscPerCKMLPP,
      'VM'                                  as period_type,
      mlnc.JrnlEntrItemCrcyRolePosition

}
where
      price.MaterialPriceType              =                                                  'INVPR'
  and price.MaterialPriceSubtype           =                                                  ' '
  and price.MatlPrcValdtyStrtFsclYearPerd  <= marvld.PreviousFiscalYearPeriod
  and price.MatlPrcValdtyEndFsclYearPeriod >= marvld.PreviousFiscalYearPeriod

// Last period of previous fiscal year

union all select from I_MaterialLedgerPrice       as price
  inner join R_MatlLedgerValnCrcyRole   as mlnc on mlnc.CompanyCode  = price.CompanyCode
                                               and mlnc.Ledger       = price.Ledger
                                               and mlnc.CurrencyRole = price.CurrencyRole

  inner join P_CCLdgrShiftedPrevYearPer as marvld on  marvld.CompanyCode = price.CompanyCode 
                                                  and marvld.Ledger      = price.Ledger

{
  key price.CostEstimate,
  key marvld.PreviousFiscalYear             as FiscalYear,
  key marvld.PrevFiscalYearLastFiscalPeriod as FiscalPeriod,
  key mlnc.LedgerValuationCurrencyRole      as LedgerValuationCurrencyRole,
  key mlnc.Ledger                           as Ledger,
  key price.ValuationArea,

      marvld.PrevFsclYearLastFsclYearPeriod as FiscalYearPer,
      mlnc.CurrencyRole                     as CurrencyRole,
      price.CompanyCode,
      price.Material,
       @Semantics.amount.currencyCode: 'Currency'
      price.MaterialPrice,
      price.Currency,
      price.MaterialPriceControl,
      price.MatlPrcValdtyStrtFsclYearPerd,
      price.MatlPrcValdtyEndFsclYearPeriod,
      price.MaterialPriceUnitQty,
      price.ValuationQuantityUnit           as BaseUnit,
      price.InventoryValuationType,

      price.MatlPrcValdtyStrtFsclYearPerd   as MaxFiscPerCKMLPP,
      'VJ'                                  as period_type,
      mlnc.JrnlEntrItemCrcyRolePosition

}
where
        price.MaterialPriceType              =                                                    'INVPR'
  and   price.MaterialPriceSubtype           =                                                    ' '
  and   price.MatlPrcValdtyStrtFsclYearPerd  <= marvld.PrevFsclYearLastFsclYearPeriod
  and   price.MatlPrcValdtyEndFsclYearPeriod >= marvld.PrevFsclYearLastFsclYearPeriod

  // special logic: if VM and VJ are the same, only vm is selected

  and   not(
      marvld.PreviousFiscalPeriodFiscalYear   = marvld.PreviousFiscalYear 
      and marvld.PreviousFiscalPeriod         = marvld.PrevFiscalYearLastFiscalPeriod
    )