@AbapCatalog.sqlViewName: 'CROSRFACHTVALC'
@VDM.viewType: #CONSUMPTION
@AbapCatalog.preserveKey: true
@Analytics: { dataCategory: #CUBE }
@Analytics.internalName: #LOCAL
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED
@AccessControl.personalData.blocking:#BLOCKED_DATA_EXCLUDED
@EndUserText.label: 'RO Asset Chart Values and Trans - Cube'
@Metadata.allowExtensions:true
define view C_RO_StRpFxdAstChartValueCube
with parameters
P_FiscalYear : fis_gjahr,
P_FiscalPeriod : fis_poper,
P_AssetAccountingKeyFigureSet : faa_key_figure_set,
P_NationalClsfctnSourceField : fiaagen_natl_clfn_srce_fld,
P_KeyDate : fagl_keydate //Reporting End Date
as select from P_RO_StRpFixedAssetChartValue(P_FiscalYear: :P_FiscalYear,
P_FiscalPeriod: :P_FiscalPeriod,
P_AssetAccountingKeyFigureSet: :P_AssetAccountingKeyFigureSet,
P_NationalClsfctnSourceField: :P_NationalClsfctnSourceField )
inner join I_AssetValuationForLedger on I_AssetValuationForLedger.CompanyCode = P_RO_StRpFixedAssetChartValue.CompanyCode
and I_AssetValuationForLedger.MasterFixedAsset = P_RO_StRpFixedAssetChartValue.MasterFixedAsset
and I_AssetValuationForLedger.FixedAsset = P_RO_StRpFixedAssetChartValue.FixedAsset
and I_AssetValuationForLedger.Ledger = P_RO_StRpFixedAssetChartValue.Ledger
and I_AssetValuationForLedger.AssetRealDepreciationArea = AssetDepreciationArea
and I_AssetValuationForLedger.ValidityStartDate <= $parameters.P_KeyDate
and I_AssetValuationForLedger.ValidityEndDate >= $parameters.P_KeyDate
association [0..1] to I_FxdAstAssgmtByFsclPerdEndDte as _AssignmentByFiscalPeriodEnd on $projection.CompanyCode = _AssignmentByFiscalPeriodEnd.CompanyCode
and $projection.Ledger = _AssignmentByFiscalPeriodEnd.Ledger
and $projection.AssetDepreciationArea = _AssignmentByFiscalPeriodEnd.AssetDepreciationArea
and $projection.MasterFixedAsset = _AssignmentByFiscalPeriodEnd.MasterFixedAsset
and $projection.FixedAsset = _AssignmentByFiscalPeriodEnd.FixedAsset
association [0..1] to I_FxdAstDeprAreaByFsclPerdEnd as _DeprAreaByFiscalPeriodEnd on $projection.CompanyCode = _DeprAreaByFiscalPeriodEnd.CompanyCode
and $projection.Ledger = _DeprAreaByFiscalPeriodEnd.Ledger
and $projection.AssetDepreciationArea = _DeprAreaByFiscalPeriodEnd.AssetDepreciationArea
and $projection.MasterFixedAsset = _DeprAreaByFiscalPeriodEnd.MasterFixedAsset
and $projection.FixedAsset = _DeprAreaByFiscalPeriodEnd.FixedAsset
association [0..*] to I_CostCenter as _AssetCostCenter on $projection.ControllingArea = _AssetCostCenter.ControllingArea
and $projection.AssetCostCenter = _AssetCostCenter.CostCenter
association [0..1] to I_BusinessArea as _AssetBusinessArea on $projection.AssetBusinessArea = _AssetBusinessArea.BusinessArea
association [0..1] to I_Location as _AssetLocation on $projection.assetlocation = _AssetLocation.Location
and $projection.AssetPlant = _AssetLocation.Plant
association [0..1] to I_Plant as _AssetPlant on $projection.AssetPlant = _AssetPlant.Plant
association [0..1] to I_DepreciationKey as _DepreciationKey on $projection.ChartOfDepreciation = _DepreciationKey.ChartOfDepreciation
and $projection.depreciationkey = _DepreciationKey.DepreciationKey
association [0..1] to I_Country as _Country on $projection.Country = _Country.Country
association [0..*] to I_StRpJournalEntryLog as _ReportedItemsLog on _ReportedItemsLog.CompanyCode = $projection.CompanyCode
and _ReportedItemsLog.AccountingDocument = $projection.AccountingDocument
and _ReportedItemsLog.FiscalYear = $projection.FiscalYear
{
@ObjectModel.foreignKey.association: '_CompanyCode'
key P_RO_StRpFixedAssetChartValue.CompanyCode,
@ObjectModel.foreignKey.association: '_DepreciationArea'
key AssetDepreciationArea,
@ObjectModel.foreignKey.association: '_MasterFixedAsset'
key P_RO_StRpFixedAssetChartValue.MasterFixedAsset,
@ObjectModel.foreignKey.association: '_FixedAsset'
key P_RO_StRpFixedAssetChartValue.FixedAsset,
@ObjectModel.foreignKey.association: '_Ledger'
key P_RO_StRpFixedAssetChartValue.Ledger,
@ObjectModel.foreignKey.association: '_FiscalYear'
key FiscalYear,
//@ObjectModel.foreignKey.association: '_FiscalPeriod'
@ObjectModel.foreignKey.association: '_FiscalPeriodForVariant'
@Semantics.fiscal.period: true
key 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,
key TransactionSubitem,
@ObjectModel.foreignKey.association: '_ControllingArea'
ControllingArea,
//Document header
_JournalEntry.DocumentReferenceID, //BKPF-XBLNR
// asset master data fields ANLA
P_RO_StRpFixedAssetChartValue._FixedAsset.FixedAssetDescription,
P_RO_StRpFixedAssetChartValue._FixedAsset.AssetAdditionalDescription,
@ObjectModel.foreignKey.association: '_AssetClass'
AssetClass,
@Semantics.text
'' as AssetClassName, ////3246477 the field is not needed in the CDS view, supplied by the ABAP class
AssetCapitalizationDate,
I_AssetValuationForLedger.AssetOpgReadinessDate,
case when (P_RO_StRpFixedAssetChartValue.Inventory = '' or P_RO_StRpFixedAssetChartValue.Inventory is null)
then ltrim(P_RO_StRpFixedAssetChartValue.MasterFixedAsset, '0')
else P_RO_StRpFixedAssetChartValue.Inventory
end as Inventory,
AssetSerialNumber,
FixedAssetGroup,
Group1AssetEvaluationKey,
Group2AssetEvaluationKey,
Group3AssetEvaluationKey,
Group4AssetEvaluationKey,
Group5AssetEvaluationKey,
_AssignmentByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).InternalOrder,
// the fields from CompanyCode
@ObjectModel.foreignKey.association: '_Country'
Country,
cast( left(NationalClassification, 1) as fiaaro_natlclfngroup ) as NationalClassificationGroup,
NationalClassification,
@Semantics.text
'' as NationalClassificationDesc, //3246477 the field is not needed in the CDS view, supplied by the ABAP class
// @Semantics.text
// cast( case $parameters.P_NationalClsfctnSourceField
// when '1' then _FixedAssetNatlClfn._Text[1:Language = $session.system_language].NationalClassificationDesc
// when '2' then _FixedAssetGroup._Text[1:Language = $session.system_language].FixedAssetGroupName
// when '3' then _EvaluationGroup1._Text[1:Language = $session.system_language].AssetEvaluationKeyName
// when '4' then _EvaluationGroup2._Text[1:Language = $session.system_language].AssetEvaluationKeyName
// when '5' then _EvaluationGroup3._Text[1:Language = $session.system_language].AssetEvaluationKeyName
// when '6' then _EvaluationGroup4._Text[1:Language = $session.system_language].AssetEvaluationKeyName
// when '7' then _EvaluationGroup5._Text[1:Language = $session.system_language].Group5AssetEvaluationKeyName
// else _FixedAssetNatlClfn._Text[1:Language = $session.system_language].NationalClassificationDesc end as glo_natl_clfn_code_text ) as NationalClassificationDesc,
// asset master data fields ANLB, ANLBZA
_DeprAreaByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).DepreciationStartDate,
@ObjectModel.foreignKey.association: '_DepreciationKey'
_DeprAreaByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).DepreciationKey,
_DeprAreaByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).PlannedUsefulLifeInYears,
_DeprAreaByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).PlannedUsefulLifeInPeriods,
// asset master data fields ANLZ
@ObjectModel.foreignKey.association: '_AssetPlant'
cast (_AssignmentByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).Plant as faa_werks) as AssetPlant,
@ObjectModel.foreignKey.association: '_AssetCostCenter'
cast (_AssignmentByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).CostCenter as faa_kostl) as AssetCostCenter,
@ObjectModel.foreignKey.association: '_AssetBusinessArea' //added because of authority check
cast (_AssignmentByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).BusinessArea as faa_gsber) as AssetBusinessArea,
@ObjectModel.foreignKey.association: '_AssetLocation'
_AssignmentByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).AssetLocation,
_AssignmentByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).Room,
_AssignmentByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).VehicleLicensePlateNumber,
_AssignmentByFiscalPeriodEnd(P_FiscalYear: :P_FiscalYear, P_FiscalPeriod: :P_FiscalPeriod).PersonnelNumber,
// other accounting document fields
AssetValueDate,
@ObjectModel.foreignKey.association: '_AccountingDocumentType'
AccountingDocumentType,
@ObjectModel.foreignKey.association: '_DebitCreditCode'
DebitCreditCode,
@ObjectModel.foreignKey.association: '_AssetTransactionType'
AssetTransactionType,
cast('' as bwatxt) as AssetTransactionTypeName, //3406368 the field is not needed in the CDS view, supplied by the ABAP class
DocumentItemText,
AssignmentReference,
@ObjectModel.foreignKey.association: '_UnitOfMeasure'
@Semantics.unitOfMeasure:true
BaseUnit,
// @DefaultAggregation: #SUM
// @Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }
// Quantity as JournalEntryTransacQtyVal, //save original quantity into JournalEntryTransacQtyVal
@ObjectModel.foreignKey.association: '_CompanyCodeCurrency'
@Semantics.currencyCode: true
P_RO_StRpFixedAssetChartValue.CompanyCodeCurrency,
// Item Type for RO Asset Chart Report
ItemType,
AssetManufacturerName,
// ***************************************************************************************************************************
// Calculate requested Value amounts, from line items having ItemType <=2
// ***************************************************************************************************************************
// 1.Acquisition value at the beginning of FY
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when ( ( AssetAccountingKeyFigure = '0000700110' or
AssetAccountingKeyFigure = '0000700300' )
and ItemType <= '2' ) then cast( AmountInCompanyCodeCurrency as fiaaro_fystacqnvalamtincccrcy )
else cast('0' as fiaaro_fystacqnvalamtincccrcy)
end as FsclYrStrtAcqnValueAmtInCCCrcy,
// 2.Acquisition value at the end of reporting period
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when ( ( AssetAccountingKeyFigure = '0010700110' or
AssetAccountingKeyFigure = '0010700301' )
and ItemType <= '2' ) then cast( AmountInCompanyCodeCurrency as fiaaro_acqnvalamtincccrcy )
else cast('0' as fiaaro_acqnvalamtincccrcy)
end as AcquisitionValueAmtInCCCrcy,
// 3.Net Book Value at the end of reporting period
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when ( AssetAccountingKeyFigure = '0010790401'
and ItemType <= '2' ) then cast( AmountInCompanyCodeCurrency as fiaaro_netbookvalamtincccrcy )
else cast('0' as fiaaro_netbookvalamtincccrcy)
end as NetBookValueAmountInCoCodeCrcy,
// ***************************************************************************************************************************
// Calculate requested Transaction quantity and amounts, from line items having ItemType >=2
// ***************************************************************************************************************************
// Asset Chart Quantity
@DefaultAggregation: #SUM
@Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }
case when ( ItemType >= '2' ) then cast( Quantity as fiaaro_chtquantity )
else cast( '0' as fiaaro_chtquantity )
end as Quantity,
// Amount in company code currency
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when ( ItemType >= '2' ) then AmountInCompanyCodeCurrency
else cast( '0' as fis_hsl )
end as AmountInCompanyCodeCurrency,
// Debit amount in company code currency
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when ( DebitCreditCode = 'S' and ItemType >= '2' ) then cast( AmountInCompanyCodeCurrency as fis_dr_hsl )
else cast( '0' as fis_dr_hsl )
end as DebitAmountInCoCodeCrcy,
// Credit amount in transaction currency
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when ( DebitCreditCode = 'H' and ItemType >= '2' ) then cast( -AmountInCompanyCodeCurrency as fis_dr_hsl )
else cast( '0' as fis_cr_hsl )
end as CreditAmountInCoCodeCrcy,
// Revaluation Amount
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when ( ( SubLedgerAcctLineItemType = '07003' or SubLedgerAcctLineItemType = '07004'
or SubLedgerAcctLineItemType = '07023' or SubLedgerAcctLineItemType = '07024'
or SubLedgerAcctLineItemType = '07043' or SubLedgerAcctLineItemType = '07044'
or SubLedgerAcctLineItemType = '07203' or SubLedgerAcctLineItemType = '07204' ) and ItemType >= '2' )
then cast( AmountInCompanyCodeCurrency as fiaaro_revalamtincccrcy)
else cast('0' as fiaaro_revalamtincccrcy)
end as RevaluationAmountInCCCrcy,
// ***************************************************************************************************************************
// other fields
@ObjectModel.foreignKey.association: '_ChartOfDepreciation'
ChartOfDepreciation,
// @ObjectModel.foreignKey.association: '_LedgerFiscalYearForVariant'
@Semantics.fiscal.year: true
LedgerFiscalYear,
@ObjectModel.foreignKey.association: '_FiscalYearVariant'
@Semantics.fiscal.yearVariant: true
FiscalYearVariant,
// @ObjectModel.foreignKey.association: '_FiscalYearPeriodForVariant'
@Semantics.fiscal.yearPeriod: true
FiscalYearPeriod,
//@Semantics.fiscal.yearPeriod: true
DepreciationFiscalPeriod,
// expose associations
_CompanyCode,
_ControllingArea,
_DepreciationArea,
_MasterFixedAsset,
_FixedAsset,
_Ledger,
_FiscalYear,
_DebitCreditCode,
_SubLedgerAccLineItemType,
_AssetAcctTransClassfctn,
_AssetKeyFigure,
_AssetKeyFigureSet,
_ChartOfDepreciation,
_JournalEntry,
_AssetClass,
_AccountingDocumentType,
_AssetTransactionType,
_AssetPlant,
_AssetCostCenter,
_AssetBusinessArea,
_AssetLocation,
_UnitOfMeasure,
_CompanyCodeCurrency,
_DepreciationKey,
_FiscalYearVariant,
_LedgerFiscalYearForVariant,
_FiscalPeriodForVariant,
_FiscalYearPeriodForVariant,
_Country,
_ReportedItemsLog
// _FixedAssetGroup,
// _EvaluationGroup1,
// _EvaluationGroup2,
// _EvaluationGroup3,
// _EvaluationGroup4,
// _EvaluationGroup5
}
where
ItemType < '9' //skip item types not relevant for current report