@AbapCatalog.sqlViewName: 'PFIACCOSTRATEBY'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.private: true
@VDM.viewType: #BASIC
@ObjectModel.usageType: {
sizeCategory: #L,
serviceQuality: #C,
dataClass: #TRANSACTIONAL
}
define view P_PlanCostRateByPeriod
as select
key PlanningCategory,
key BusinessTransactionType,
key ac.Ledger as Ledger,
key per.fiscal_year as FiscalYear,
key per.fiscal_period as FiscalPeriod,
key ac.ControllingArea,
key ac.CompanyCode,
key CostCenter,
key ActivityType,
CostCtrActivityTypeQtyUnit,
@Semantics.amount.currencyCode : 'TransactionCurrency'
cast( sum( case when CurrencyRole = '00' then CostRateTotalAmount else 0 end ) as fins_vwcur12 ) as CostRateTotalAmountInTransCrcy,
@Semantics.amount.currencyCode : 'TransactionCurrency'
cast( sum( case when CurrencyRole = '00' then CostRateFixedAmount else 0 end ) as fins_vwcur12 ) as CostRateFixedAmountInTransCrcy,
@Semantics.amount.currencyCode : 'TransactionCurrency'
cast( sum( case when CurrencyRole = '00' then cast(CostRateTotalAmount - CostRateFixedAmount as fco_rate_varbl_amount) else 0 end ) as fins_vwcur12 ) as CostRateVarblAmountInTransCrcy,
cast( max( case when CurrencyRole = '00' then ac.Currency else '' end ) as fins_currw ) as TransactionCurrency,
@Semantics.amount.currencyCode : 'CompanyCodeCurrency'
cast( sum( case when CurrencyRole = '10' then CostRateTotalAmount else 0 end ) as fins_vhcur12 ) as CostRateTotalAmtInCoCodeCrcy,
@Semantics.amount.currencyCode : 'CompanyCodeCurrency'
cast( sum( case when CurrencyRole = '10' then CostRateFixedAmount else 0 end ) as fins_vwcur12 ) as CostRateFixedAmtInCoCodeCrcy,
@Semantics.amount.currencyCode : 'CompanyCodeCurrency'
cast( sum( case when CurrencyRole = '10' then cast(CostRateTotalAmount - CostRateFixedAmount as fco_rate_varbl_amount) else 0 end ) as fins_vwcur12 ) as CostRateVarblAmtInCoCodeCrcy,
cast( max( case when CurrencyRole = '10' then ac.Currency else '' end ) as fins_currh ) as RateCompanyCodeCurrency,
@Semantics.amount.currencyCode : 'GlobalCurrency'
cast( sum( case when CurrencyRole = '30' then CostRateTotalAmount else 0 end ) as fins_vkcur12 ) as CostRateTotalAmountInGlobCrcy,
@Semantics.amount.currencyCode : 'GlobalCurrency'
cast( sum( case when CurrencyRole = '30' then CostRateFixedAmount else 0 end ) as fins_vwcur12 ) as CostRateFixedAmountInGlobCrcy,
@Semantics.amount.currencyCode : 'GlobalCurrency'
cast( sum( case when CurrencyRole = '30' then cast(CostRateTotalAmount - CostRateFixedAmount as fco_rate_varbl_amount) else 0 end ) as fins_vwcur12 ) as CostRateVarblAmountInGlobCrcy,
cast( max( case when CurrencyRole = '30' then ac.Currency else '' end ) as fins_currk ) as RateGlobalCurrency,
cast( concat(per.fiscal_year, per.fiscal_period) as char8 ) as FiscalYearPeriod, //fins_jahrper
max( case when CurrencyRole = '10' then 1 else 0 end ) as CoCodeCurrencyIsAvailable,
max( case when CurrencyRole = '30' then 1 else 0 end ) as GlobalCurrencyIsAvailable,
per.fiscal_period_end_date as FiscalPeriodEndDate,
cast( _I_CompanyCode.Currency as fins_currh ) as CompanyCodeCurrency,
cast( _I_ControllingArea.ControllingAreaCurrency as fins_currk ) as GlobalCurrency
from P_AccountingCostRate as ac
left outer join I_ControllingArea as _I_ControllingArea on _I_ControllingArea.ControllingArea = ac.ControllingArea
left outer join I_CompanyCode as _I_CompanyCode on _I_CompanyCode.CompanyCode = ac.CompanyCode
left outer join finsc_period as per
on per.fiscal_year_variant = _I_CompanyCode.FiscalYearVariant
and ac.ValidityStartDate <= per.fiscal_period_end_date
and ac.ValidityEndDate >= per.fiscal_period_end_date
and per.is_special_period = ''
// inner join I_FiscalYearForLedger as _I_FiscalYearForLedger
// on _I_FiscalYearForLedger.Ledger = ac.Ledger
// and _I_FiscalYearForLedger.CompanyCode = ac.CompanyCode
// and _I_FiscalYearForLedger.FiscalYear = per.fiscal_year
where
CostRateIsDeleted = ''
and BusinessTransactionType = 'MPCR'
// and PlanningCategory = 'PLN'
group by
PlanningCategory,
BusinessTransactionType,
ac.Ledger,
per.fiscal_year,
per.fiscal_period,
ac.ControllingArea,
ac.CompanyCode,
CostCenter,
ActivityType,
CostCtrActivityTypeQtyUnit,
fiscal_period_end_date,
_I_CompanyCode.Currency,
_I_ControllingArea.ControllingAreaCurrency
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COMPANYCODE",
"I_CONTROLLINGAREA",
"P_ACCOUNTINGCOSTRATE",
"FINSC_PERIOD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/