P_MatVal_By_Period_Types

@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.sqlViewName: 'PMTVLPERIODTYPES'
@AbapCatalog.compiler.compareFilter:true
@AbapCatalog.preserveKey: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 P_MaterialLedger_Name_Curtp as mlnc on  mlnc.bukrs       = price.CompanyCode
                                                   and mlnc.ext_curtype = price.CurrencyRole
                                                   and mlnc.rldnr       = price.Ledger

//    inner join marv                        as mv   on mv.bukrs = price.CompanyCode

    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.curtp                            as LedgerValuationCurrencyRole,
  key mlnc.rldnr                            as Ledger,
  key price.ValuationArea,


      marvld.CurrentFiscalYearPeriod        as FiscalYearPer,
      mlnc.ext_curtype                      as CurrencyRole,
      price.CompanyCode,
      price.Material,
      price.MaterialPrice,
      price.MaterialPriceControl,
      price.MatlPrcValdtyStrtFsclYearPerd,
      price.MatlPrcValdtyEndFsclYearPeriod,
//      price.MaterialPriceUnitQty,

//      price.ValuationQuantityUnit           as BaseUnit,

//      price.InventoryValuationType,


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

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

// Previous period

union all select from I_MaterialLedgerPrice       as price
  inner join          P_MaterialLedger_Name_Curtp as mlnc on  mlnc.bukrs       = price.CompanyCode
                                                          and mlnc.ext_curtype = price.CurrencyRole
                                                          and mlnc.rldnr       = price.Ledger

//  inner join          marv                        as mv   on mv.bukrs = price.CompanyCode

  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.curtp                            as LedgerValuationCurrencyRole,
  key mlnc.rldnr                            as Ledger,
  key price.ValuationArea,

      marvld.PreviousFiscalYearPeriod       as FiscalYearPer,
      mlnc.ext_curtype                      as CurrencyRole,
      price.CompanyCode,
      price.Material,
      price.MaterialPrice,
      price.MaterialPriceControl,
      price.MatlPrcValdtyStrtFsclYearPerd,
      price.MatlPrcValdtyEndFsclYearPeriod,
//      price.MaterialPriceUnitQty,

//      price.ValuationQuantityUnit           as BaseUnit,

//      price.InventoryValuationType,


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

}
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          P_MaterialLedger_Name_Curtp as mlnc on  mlnc.bukrs       = price.CompanyCode
                                                          and mlnc.ext_curtype = price.CurrencyRole
                                                          and mlnc.rldnr       = price.Ledger

//  inner join          marv                        as mv   on mv.bukrs = price.CompanyCode

  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.curtp                            as LedgerValuationCurrencyRole,
  key mlnc.rldnr                            as Ledger,
  key price.ValuationArea,

      marvld.PrevFsclYearLastFsclYearPeriod as FiscalYearPer,
      mlnc.ext_curtype                      as CurrencyRole,
      price.CompanyCode,
      price.Material,
      price.MaterialPrice,
      price.MaterialPriceControl,
      price.MatlPrcValdtyStrtFsclYearPerd,
      price.MatlPrcValdtyEndFsclYearPeriod,
//      price.MaterialPriceUnitQty,

//      price.ValuationQuantityUnit           as BaseUnit,

//      price.InventoryValuationType,


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

}
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
    )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIALLEDGERPRICE",
"P_CCLDGRSHIFTEDPREVYEARPER",
"P_MATERIALLEDGER_NAME_CURTP",
"P_MATLCTRLRECDLDGRFISCALPERIOD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/