@AbapCatalog.sqlViewName: 'CROSAFTFATRANCUB'
@VDM.viewType: #CONSUMPTION
@Analytics: { dataCategory: #CUBE }
@Analytics.internalName: #LOCAL
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.serviceQuality: 'D'
@ObjectModel.usageType.dataClass: 'TRANSACTIONAL'
@AccessControl.authorizationCheck: #MANDATORY
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.allowExtensions:true
@EndUserText.label: 'RO SAFT Fixed Asset Transaction - Cube'
define view C_RO_SAFTFixedAssetTransCube
with parameters
P_AssetAccountingKeyFigureSet : faa_key_figure_set
as select from I_FixedAssetLineItem as Values
inner join I_AssetKeyFigureSetSpec as AssetKeyFigureSetSpec on AssetKeyFigureSetSpec.AssetAccountingKeyFigureSet = $parameters.P_AssetAccountingKeyFigureSet
inner join I_AssetKeyFigureSpecification as AssetKeyFigureSpec on AssetKeyFigureSpec.AssetAccountingKeyFigure = AssetKeyFigureSetSpec.AssetAccountingKeyFigure
and AssetKeyFigureSpec.AssetAcctAnlytlTransClassfctn = Values.AssetAcctTransClassfctn
and AssetKeyFigureSpec.SubLedgerAcctLineItemType = Values.SubLedgerAcctLineItemType
left outer to one join P_RO_SAFTAssetTransacTypeClfn
on Values.AssetTransactionType = P_RO_SAFTAssetTransacTypeClfn.AssetTransactionType
{
@ObjectModel.foreignKey.association: '_CompanyCode'
key Values.CompanyCode,
@ObjectModel.foreignKey.association: '_DepreciationArea'
key Values.AssetDepreciationArea,
@ObjectModel.foreignKey.association: '_MasterFixedAsset'
key Values.MasterFixedAsset,
@ObjectModel.foreignKey.association: '_FixedAsset'
key Values.FixedAsset,
@ObjectModel.foreignKey.association: '_Ledger'
key Values.Ledger,
@ObjectModel.foreignKey.association: '_FiscalYear'
key Values.FiscalYear,
key Values.FiscalPeriod,
@ObjectModel.foreignKey.association: '_SubLedgerAccLineItemType'
key Values.SubLedgerAcctLineItemType,
key Values.AssetAcctTransClassfctn,
@ObjectModel.foreignKey.association: '_AssetKeyFigure'
key AssetKeyFigureSetSpec.AssetAccountingKeyFigure,
@ObjectModel.foreignKey.association: '_AssetKeyFigureSet'
key AssetKeyFigureSetSpec.AssetAccountingKeyFigureSet,
key Values.AccountingDocument,
// key Values.LedgerGLLineItem,
key Values.TransactionSubitem,
key Values.DebitCreditCode,
Values._FixedAsset.AssetClass,
@ObjectModel.foreignKey.association: '_ChartOfDepreciation'
Values.ChartOfDepreciation,
Values.ControllingArea,
// Values.Country,
// Values.FinancialManagementArea,
// @ObjectModel.foreignKey.association: '_FiscalYearVariant'
// Values.FiscalYearVariant,
// Values.LedgerFiscalYear,
// Values.FiscalYearPeriod,
Values.LedgerGroup,
Values.GroupMasterFixedAsset,
Values.GroupFixedAsset,
@ObjectModel.foreignKey.association: '_AccountingDocumentType'
AccountingDocumentType,
Values.AccountingDocumentCreationDate,
Values.CreationTime,
Values.PostingDate,
Values.DocumentDate,
Values.AssetValueDate,
Values.AccountingDocCreatedByUser,
@ObjectModel.foreignKey.association: '_AssetTransactionType'
Values.AssetTransactionType,
Values.AssignmentReference,
Values.DocumentItemText,
Values.ReferenceDocumentType,
Values.ReferenceDocument,
Values.LogicalSystem,
Values.ReferenceDocumentContext,
Values.ReversalReferenceDocumentCntxt,
Values.ReversalReferenceDocument,
Values.ReversalTransactionSubitem,
Values.IsReversal,
Values.IsReversed,
Values.BusinessArea,
Values.Segment,
Values.FinancialAccountType,
Values.Fund,
// Values.GrantID,
Values.FunctionalArea,
// Values.BudgetPeriod,
Values.IsStatisticalDocument,
//SAFTAssetTransactionType,
cast( case when P_RO_SAFTAssetTransacTypeClfn.SAFTAssetTransactionType != ''
then P_RO_SAFTAssetTransacTypeClfn.SAFTAssetTransactionType
else case when ( Values.SubLedgerAcctLineItemType = '07205' or //Ordinary depr. posted periodically
Values.SubLedgerAcctLineItemType = '07206' ) and //Special depr. posted periodically
Values.AssetAcctTransClassfctn = '06'
then cast( '030' as /ceecv/ro_sa_ast_trans_type ) //Depreciations
else P_RO_SAFTAssetTransacTypeClfn.SAFTAssetTransactionType
end
end as /ceecv/ro_sa_ast_trans_type preserving type ) as SAFTAssetTransactionType,
cast( case when P_RO_SAFTAssetTransacTypeClfn.SAFTAssetValueType != ''
then P_RO_SAFTAssetTransacTypeClfn.SAFTAssetValueType
else case when ( Values.SubLedgerAcctLineItemType = '07205' or //Ordinary depr. posted periodically
Values.SubLedgerAcctLineItemType = '07206' ) and //Special depr. posted periodically
Values.AssetAcctTransClassfctn = '06'
then cast( '40' as /ceecv/ro_sa_ast_val_type ) //Depreciations
else P_RO_SAFTAssetTransacTypeClfn.SAFTAssetValueType
end
end as /ceecv/ro_sa_ast_val_type preserving type ) as SAFTAssetValueType,
AcctgDocIsPostedPeriodically,
cast( case AssetKeyFigureSetSpec.AssetAccountingKeyFigure
when '0007701500' //Manually Posted Ordinary Depreciation
then 'N1'
when '0007701600' //Manually Posted Special Depreciation
then 'S1'
when '0007700500' //Ordinary Depreciation Posted in UJE
then 'N2'
when '0007700600' //Special Depreciation Posted in UJE
then 'S2'
else '' end as saft_aa_ro_ast_depr_type ) as AssetDepreciationType,
Values.CompanyCodeCurrency,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
Values.AmountInCompanyCodeCurrency,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
case when Values.SubLedgerAcctLineItemType = '07000' or Values.SubLedgerAcctLineItemType = '07001' or //Cumulative Acquisition Costs or Cumulative Down Payments
Values.SubLedgerAcctLineItemType = '07002' or Values.SubLedgerAcctLineItemType = '07003' or //Cumulative Investment Support or Cumulative Revaluation of Acquisition Costs
Values.SubLedgerAcctLineItemType = '07008' or Values.SubLedgerAcctLineItemType = '07023' or //Cumulative Transfer of Reserves or Revaluation of Acquisition Costs Related to Prior Year
Values.SubLedgerAcctLineItemType = '07028' or Values.SubLedgerAcctLineItemType = '07040' or //Transfer of Reserves Related to Prior Year or Acquisition Costs, Current Year
Values.SubLedgerAcctLineItemType = '07041' or Values.SubLedgerAcctLineItemType = '07042' or //Down Payments Related to Current Year or Investment Support, Current Year
Values.SubLedgerAcctLineItemType = '07043' or Values.SubLedgerAcctLineItemType = '07048' or //Revaluation of Current-Year Acquisition Costs or Transfer of Reserves Related to Current Year
Values.SubLedgerAcctLineItemType = '07103' or Values.SubLedgerAcctLineItemType = '07108' or //Manually posted Revaluation of RV PY or Manually posted transfer of reseves PY
Values.SubLedgerAcctLineItemType = '07123' or Values.SubLedgerAcctLineItemType = '07128' or //Manually posted revaluation RV PY in CY or Manually posted transfer of reseves PY in CY
Values.SubLedgerAcctLineItemType = '07143' or Values.SubLedgerAcctLineItemType = '07148' or //Manually posted revaluation RV CY or Manually posted transfer of reseves CY
Values.SubLedgerAcctLineItemType = '07203' or Values.SubLedgerAcctLineItemType = '07208' //Revaluation of CY Acquisition Costs, Posted Periodically or Reserve, Posted Periodically
then cast(Values.AmountInCompanyCodeCurrency as fis_apcamount)
else
cast('0' as fis_apcamount)
end as AcqnProdnAmtInDspCrcy,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
case when Values.SubLedgerAcctLineItemType = '07004' or Values.SubLedgerAcctLineItemType = '07005' or //Cumulative Revaluation of Ordinary Depreciation or Cumulative Ordinary Depreciation
Values.SubLedgerAcctLineItemType = '07006' or Values.SubLedgerAcctLineItemType = '07007' or //Cumulative Special Depreciation or Cumulative Current-Value Depreciation
Values.SubLedgerAcctLineItemType = '07024' or Values.SubLedgerAcctLineItemType = '07025' or //Revaluation of Ordinary Depreciation Related to Prior Year or Ordinary Depreciation Related to Prior Year
Values.SubLedgerAcctLineItemType = '07026' or Values.SubLedgerAcctLineItemType = '07027' or //Special Depreciation Related to Prior Year or Unplanned Depreciation Related to Prior Year
Values.SubLedgerAcctLineItemType = '07044' or Values.SubLedgerAcctLineItemType = '07045' or //Revaluation of Depreciation Related to Current Year or Ordinary Depreciation Related to Current Year
Values.SubLedgerAcctLineItemType = '07046' or Values.SubLedgerAcctLineItemType = '07047' or //Special Depreciation Related to Current Year or Unplanned Depreciation Related to Current Year
Values.SubLedgerAcctLineItemType = '07104' or Values.SubLedgerAcctLineItemType = '07105' or //Manually posted Revaluation of Depreciation PY or Manually posted ordinary depreciation PY
Values.SubLedgerAcctLineItemType = '07106' or Values.SubLedgerAcctLineItemType = '07107' or //Manually posted special depreciation PY or Manually posted unplanned depreciation PY
Values.SubLedgerAcctLineItemType = '07124' or Values.SubLedgerAcctLineItemType = '07125' or //Manually posted revaluation Depr PY in CY or Manually posted ordinary depreciation PY in CY
Values.SubLedgerAcctLineItemType = '07126' or Values.SubLedgerAcctLineItemType = '07127' or //Manually posted special depreciation PY in CY or Manually posted unplanned depreciation PY in CY
Values.SubLedgerAcctLineItemType = '07144' or Values.SubLedgerAcctLineItemType = '07145' or //Manually posted revaluation Depr CY or Manually posted ordinary depreciation CY
Values.SubLedgerAcctLineItemType = '07146' or Values.SubLedgerAcctLineItemType = '07147' or //Manually posted special depreciation CY or Manually posted unplanned depreciation CY
Values.SubLedgerAcctLineItemType = '07204' or Values.SubLedgerAcctLineItemType = '07205' or //Revaluation of Ordinary CY Depreciation, Posted Periodically or Ordinary Depreciation, Posted Periodically
Values.SubLedgerAcctLineItemType = '07206' or Values.SubLedgerAcctLineItemType = '07207' //Special Depreciation, Posted Periodically or Unplanned Depreciation, Posted Periodically
then cast(Values.AmountInCompanyCodeCurrency as fis_valueadjamt)
else
cast('0' as fis_valueadjamt)
end as ValueAdjustmentAmtInDspCrcy,
@DefaultAggregation:#SUM
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
case when Values.SubLedgerAcctLineItemType = '07900' or Values.SubLedgerAcctLineItemType = '07940' //Revenue from Asset Sale Related to Prior Year or Revenue Relating to Current Year Acquisitions
then cast(Values.AmountInCompanyCodeCurrency as fis_retirementrevenueamount)
else
cast('0' as fis_retirementrevenueamount)
end as RetirementRevenueAmtInDspCrcy,
AcqnProdnCostOnTransIsReset,
// Associations
_CompanyCode,
_DepreciationArea,
_MasterFixedAsset,
_FixedAsset,
_Ledger,
_FiscalYear,
_SubLedgerAccLineItemType,
_AssetKeyFigure,
_AssetKeyFigureSet,
_ChartOfDepreciation,
// _FiscalYearVariant,
_AssetTransactionType,
_AccountingDocumentType
}
where Values.AssetAcctTransClassfctn <> '00' //No balance carry forward items