@AbapCatalog.sqlViewName: 'PROSRFACHTVATR'
@AbapCatalog.preserveKey:true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #CONSUMPTION
@VDM.private:true
define view P_RO_StRpFixedAssetChartValue with parameters
P_FiscalYear : fis_gjahr,
P_FiscalPeriod : fis_poper,
P_AssetAccountingKeyFigureSet : faa_key_figure_set,
P_NationalClsfctnSourceField : fiaagen_natl_clfn_srce_fld
as select from I_FixedAssetValue( P_AssetAccountingKeyFigureSet: :P_AssetAccountingKeyFigureSet )
inner join I_FiscalYearPeriodForLedger on I_FixedAssetValue.CompanyCode = I_FiscalYearPeriodForLedger.CompanyCode
and I_FixedAssetValue.Ledger = I_FiscalYearPeriodForLedger.Ledger
and I_FiscalYearPeriodForLedger.FiscalYear = :P_FiscalYear
and I_FiscalYearPeriodForLedger.FiscalPeriod = :P_FiscalPeriod
association [0..1] to I_Group1AssetEvaluationKey as _EvaluationGroup1 on $projection.Group1AssetEvaluationKey = _EvaluationGroup1.Group1AssetEvaluationKey
association [0..1] to I_Group2AssetEvaluationKey as _EvaluationGroup2 on $projection.Group2AssetEvaluationKey = _EvaluationGroup2.Group2AssetEvaluationKey
association [0..1] to I_Group3AssetEvaluationKey as _EvaluationGroup3 on $projection.Group3AssetEvaluationKey = _EvaluationGroup3.Group3AssetEvaluationKey
association [0..1] to I_Group4AssetEvaluationKey as _EvaluationGroup4 on $projection.Group4AssetEvaluationKey = _EvaluationGroup4.Group4AssetEvaluationKey
association [0..1] to I_Group5AssetEvaluationKey as _EvaluationGroup5 on $projection.Group5AssetEvaluationKey = _EvaluationGroup5.Group5AssetEvaluationKey
association [0..1] to I_FixedAssetNatlClfn as _FixedAssetNatlClfn on $projection.Country = _FixedAssetNatlClfn.Country
and $projection.NationalClassification = _FixedAssetNatlClfn.NationalClassification
association [0..1] to I_FixedAsset as _FixedAsset on $projection.CompanyCode = _FixedAsset.CompanyCode
and $projection.MasterFixedAsset = _FixedAsset.MasterFixedAsset
and $projection.FixedAsset = _FixedAsset.FixedAsset
{
@ObjectModel.foreignKey.association: '_CompanyCode'
key I_FixedAssetValue.CompanyCode,
@ObjectModel.foreignKey.association: '_DepreciationArea'
key AssetDepreciationArea,
@ObjectModel.foreignKey.association: '_MasterFixedAsset'
key MasterFixedAsset,
@ObjectModel.foreignKey.association: '_FixedAsset'
key FixedAsset,
@ObjectModel.foreignKey.association: '_Ledger'
key I_FixedAssetValue.Ledger,
@ObjectModel.foreignKey.association: '_FiscalYear'
key I_FixedAssetValue.FiscalYear,
//@ObjectModel.foreignKey.association: '_FiscalPeriod'
@ObjectModel.foreignKey.association: '_FiscalPeriodForVariant'
key I_FixedAssetValue.FiscalPeriod,
@ObjectModel.foreignKey.association: '_SubledgerAccLineItemType'
key SubLedgerAcctLineItemType,
@ObjectModel.foreignKey.association: '_AssetAcctTransClassfctn'
key AssetAcctTransClassfctn,
@ObjectModel.foreignKey.association: '_AssetKeyFigure'
key AssetAccountingKeyFigure,
@ObjectModel.foreignKey.association: '_AssetKeyFigureSet'
key AssetAccountingKeyFigureSet,
// remaining accounting document key fields
@ObjectModel.foreignKey.association: '_JournalEntry'
key AccountingDocument,
TransactionSubitem,
@ObjectModel.foreignKey.association: '_ControllingArea'
ControllingArea,
// asset master data fields ANLA
@ObjectModel.foreignKey.association: '_AssetClass'
AssetClass,
AssetCapitalizationDate,
Inventory,
AssetSerialNumber,
// the fields from CompanyCode
Country,
FixedAssetGroup,
Group1AssetEvaluationKey,
Group2AssetEvaluationKey,
Group3AssetEvaluationKey,
Group4AssetEvaluationKey,
Group5AssetEvaluationKey,
// other accounting document fields
// if AssetValueDate is null then use PostingDate
case when (AssetValueDate = '00000000' or AssetValueDate is null) then PostingDate else AssetValueDate end as AssetValueDate,
@ObjectModel.foreignKey.association: '_AccountingDocumentType'
AccountingDocumentType,
@ObjectModel.foreignKey.association: '_DebitCreditCode'
DebitCreditCode,
@ObjectModel.foreignKey.association: '_AssetTransactionType'
AssetTransactionType,
DocumentItemText,
AssignmentReference,
@ObjectModel.foreignKey.association: '_UnitOfMeasure'
@Semantics.unitOfMeasure:true
BaseUnit,
@DefaultAggregation: #SUM
@Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }
Quantity,
@ObjectModel.foreignKey.association: '_CompanyCodeCurrency'
@Semantics.currencyCode: true
CompanyCodeCurrency,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
cast( AmountInCompanyCodeCurrency as fis_hsl ) as AmountInCompanyCodeCurrency,
cast( case $parameters.P_NationalClsfctnSourceField
when '1' then NationalClassification
when '2' then FixedAssetGroup
when '3' then Group1AssetEvaluationKey
when '4' then Group2AssetEvaluationKey
when '5' then Group3AssetEvaluationKey
when '6' then Group4AssetEvaluationKey
when '7' then Group5AssetEvaluationKey
else NationalClassification end as glo_natl_clfn_code ) as NationalClassification,
// @DefaultAggregation: #SUM
// @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
// case DebitCreditCode
// when 'S' then cast( AmountInCompanyCodeCurrency as fis_dr_hsl )
// else cast( '0' as fis_dr_hsl )
// end as DebitAmountInCoCodeCrcy, //Debit amount
// @DefaultAggregation: #SUM
// @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
// case DebitCreditCode
// when 'H' then cast( -AmountInCompanyCodeCurrency as fis_cr_hsl )
// else cast( '0' as fis_cr_hsl )
// end as CreditAmountInCoCodeCrcy, //Credit amount
// ***************************************************************************************************************************
// calculate ItemType, returning values :
// 1 - line items relevant for Values only
// 2 - line items relevant for both Values and Transactions
// 3 - line items relevant for Transactions only
// 4 - line items not relevant for report at all (are skipped in where condition in Cube)
// 5 - migrated line items relevant for both Values and Transactions but needs to be filtered later on
// ***************************************************************************************************************************
case
// Key figures dedicated for Values only
when ( ( AssetAccountingKeyFigure = '0000700110' or
AssetAccountingKeyFigure = '0000700300' or
AssetAccountingKeyFigure = '0010700110' or
AssetAccountingKeyFigure = '0010700301' ) and I_FixedAssetValue.FiscalYear = :P_FiscalYear )
then cast( '1' as fiaaro_chtitemtype )
// Key figure 0010790401 is complex, because it is used both by Values - in case of current year items
// and also by Transactions in case of Legacy Data Transfer (LDT) items
when ( AssetAccountingKeyFigure = '0010790401' ) then
case
when ( AssetAcctTransClassfctn = 'C7' or AssetAcctTransClassfctn = 'E7')
then //LDT line item
case
when I_FixedAssetValue.FiscalYear = :P_FiscalYear then cast( '2' as fiaaro_chtitemtype )
else cast( '3' as fiaaro_chtitemtype )
end
else //non LDT line item
case
when I_FixedAssetValue.FiscalYear = :P_FiscalYear then cast( '1' as fiaaro_chtitemtype )
else
case
when (AccountingDocument = 'DA00000000') then cast( '4' as fiaaro_chtitemtype ) //item migrated to UJE in prev.years
else cast( '9' as fiaaro_chtitemtype )
end
end
end
// Key figures dedicated for Transactions only (no LDT)
when ( AssetAccountingKeyFigure = '0009700000' or
AssetAccountingKeyFigure = '0009700100' or
AssetAccountingKeyFigure = '0009700200' or
AssetAccountingKeyFigure = '0009700300' or
AssetAccountingKeyFigure = '0009700400' ) then
case
when ( AssetAcctTransClassfctn = 'C7' or AssetAcctTransClassfctn = 'E7' )
then cast( '9' as fiaaro_chtitemtype )
else cast( '3' as fiaaro_chtitemtype )
end
// Key figures dedicated for Transactions only
when ( AssetAccountingKeyFigure = '0009700500' or
AssetAccountingKeyFigure = '0009700600' or
AssetAccountingKeyFigure = '0009700700' or
AssetAccountingKeyFigure = '0009700800' or
AssetAccountingKeyFigure = '0008700500' or //3195760
AssetAccountingKeyFigure = '0008700600' or //3195760
AssetAccountingKeyFigure = '0008700700' or //3195760
AssetAccountingKeyFigure = '0009700900' )
then cast( '3' as fiaaro_chtitemtype )
// No other Key figures are expected in current report
else cast( '9' as fiaaro_chtitemtype )
end as ItemType,
// other fields
@ObjectModel.foreignKey.association: '_ChartOfDepreciation'
ChartOfDepreciation,
@ObjectModel.foreignKey.association: '_LedgerFiscalYearForVariant'
@Semantics.fiscal.year: true
LedgerFiscalYear,
@ObjectModel.foreignKey.association: '_FiscalYearVariant'
@Semantics.fiscal.yearVariant: true
I_FixedAssetValue.FiscalYearVariant,
@ObjectModel.foreignKey.association: '_FiscalYearPeriodForVariant'
@Semantics.fiscal.yearPeriod: true
I_FixedAssetValue.FiscalYearPeriod,
//@Semantics.fiscal.yearPeriod: true
I_FixedAssetValue.DepreciationFiscalPeriod,
// I_FixedAssetValue.PersonnelNumber,
// expose other associations
I_FixedAssetValue._CompanyCode,
_DepreciationArea,
_MasterFixedAsset,
_FixedAsset,
I_FixedAssetValue._Ledger,
I_FixedAssetValue._FiscalYear,
_DebitCreditCode,
_SubLedgerAccLineItemType,
_AssetAcctTransClassfctn,
_AssetKeyFigure,
_AssetKeyFigureSet,
_ChartOfDepreciation,
_JournalEntry,
_AssetClass,
_AccountingDocumentType,
_AssetTransactionType,
_ControllingArea,
_UnitOfMeasure,
_CompanyCodeCurrency,
I_FixedAssetValue._FiscalYearVariant,
_LedgerFiscalYearForVariant,
_FiscalPeriodForVariant,
_FiscalYearPeriodForVariant,
_FixedAssetGroup,
_EvaluationGroup1,
_EvaluationGroup2,
_EvaluationGroup3,
_EvaluationGroup4,
_EvaluationGroup5,
_FixedAssetNatlClfn,
_FixedAsset.AssetManufacturerName
}
where
//the view selects all transactions until the end of reporting period
( I_FixedAssetValue.FiscalYear < :P_FiscalYear or
I_FixedAssetValue.FiscalYear = :P_FiscalYear and I_FixedAssetValue.FiscalPeriod <= :P_FiscalPeriod )
//the view selects not retired assets and also retired assets - if retired after 1st day of fiscal year
and ( AssetDeactivationDate = '00000000' or
AssetDeactivationDate <> '00000000' and AssetDeactivationDate > I_FiscalYearPeriodForLedger.FiscalYearStartDate )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_FISCALYEARPERIODFORLEDGER",
"I_FIXEDASSET",
"I_FIXEDASSETVALUE"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENTTYPE",
"I_ASSETCLASS",
"I_ASSETKEYFIGURE",
"I_ASSETKEYFIGURESET",
"I_ASSETTRANSACTIONTYPE",
"I_CHARTOFDEPRECIATION",
"I_COMPANYCODE",
"I_CONTROLLINGAREA",
"I_CURRENCY",
"I_DEBITCREDITCODE",
"I_DEPRECIATIONAREAFORLEDGER",
"I_FISCALPERIODFORVARIANT",
"I_FISCALYEARFORCOMPANYCODE",
"I_FISCALYEARFORVARIANT",
"I_FISCALYEARPERIODFORVARIANT",
"I_FISCALYEARVARIANT",
"I_FIXEDASSET",
"I_FIXEDASSETGROUP",
"I_FIXEDASSETNATLCLFN",
"I_GROUP1ASSETEVALUATIONKEY",
"I_GROUP2ASSETEVALUATIONKEY",
"I_GROUP3ASSETEVALUATIONKEY",
"I_GROUP4ASSETEVALUATIONKEY",
"I_GROUP5ASSETEVALUATIONKEY",
"I_JOURNALENTRY",
"I_LEDGER",
"I_MASTERFIXEDASSET",
"I_MOVEMENTCATEGORY",
"I_SUBLEDGERACCLINEITEMTYPE",
"I_UNITOFMEASURE"
],
"BASE":
[
"I_FIXEDASSETVALUE"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/