P_AssetBalanceCube2

@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE
@VDM.private:true
@Metadata.ignorePropagatedAnnotations: true

define view entity P_AssetBalanceCube2
  with parameters
    P_AssetAccountingKeyFigureSet : faa_key_figure_set,
    P_FiscalYear                  : fis_gjahr,
    P_FiscalPeriod                : fis_poper

  as select from           I_LedgerCompanyCodeCrcyRoles   as LedgerCompanyCodeCrcyRole
    inner join             I_FixedAssetLineItem           as Values                       on  Values.CompanyCode = LedgerCompanyCodeCrcyRole.CompanyCode
                                                                                          and Values.Ledger      = LedgerCompanyCodeCrcyRole.Ledger
    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 I_RealDepreciationAreaByCoCode as RealDepreciationAreaByCoCode on  RealDepreciationAreaByCoCode.CompanyCode               = Values.CompanyCode
                                                                                          and RealDepreciationAreaByCoCode.AssetRealDepreciationArea = Values.AssetDepreciationArea

{
  key Values.CompanyCode,
  key Values.AssetDepreciationArea,
  key Values.MasterFixedAsset,
  key Values.FixedAsset,
  key Values.Ledger,
  key Values.FiscalYear,
  key Values.FiscalPeriod,
  key Values.SubLedgerAcctLineItemType,
  key Values.AssetAcctTransClassfctn,
  key AssetKeyFigureSetSpec.AssetAccountingKeyFigure,
  key AssetKeyFigureSetSpec.AssetAccountingKeyFigureSet,
  key Values.AccountingDocument,
  key Values.LedgerGLLineItem,
  key Values.TransactionSubitem,
  key Values.DebitCreditCode,

      Values.LedgerFiscalYear,
      Values.FiscalYearVariant,
      Values.FiscalYearPeriod,
      Values.DepreciationFiscalPeriod,
      Values.PostingDate,

      Values.ChartOfDepreciation,
      AssetKeyFigureSetSpec.AssetAccountingSortedKeyFigure,
      Values.ChartOfAccounts,
      Values.ControllingArea,
      Values.Country,
      Values.CountryChartOfAccounts,
      Values.FinancialManagementArea,
      RealDepreciationAreaByCoCode.FinancialStatementVariant,
      Values.AccountingPrinciple,
      Values.LedgerGroup,
      Values.GLAccount,
      Values.AlternativeGLAccount,
      Values.GroupMasterFixedAsset,
      Values.GroupFixedAsset,
      Values.FinancialAccountType,
      Values.AccountingDocumentCreationDate,
      Values.CreationTime,
      Values.JointVentureCostRecoveryCode,
      Values.CostCenter,
      Values.Plant,
      Values.BusinessArea,
      Values.PersonnelNumber,
      Values.Fund,
      Values.ProfitCenter,
      Values.Segment,
      Values.FunctionalArea,
      Values.FundsCenter,
      Values.AssetTransactionType,
      Values.GrantID,
      Values.BudgetPeriod,
      Values.AssetValueDate,
      Values.ReferenceDocument,
      Values.ReferenceDocumentItem,
      Values.IsReversal,
      Values.IsReversed,
      Values.DocumentDate,
      Values.AccountingDocumentType,
      Values.DocumentItemText,
      Values.AssignmentReference,
      Values.GLBusinessTransactionType,
      Values.BusinessTransactionType,
      Values.BusinessTransactionCategory,
      Values.FinancialClosingStep,
      Values.LogicalSystem,
      Values.PartnerCompanyCode,
      Values.PartnerMasterFixedAsset,
      Values.PartnerFixedAsset,
      Values.ReferenceDocumentContext,
      Values.ReferenceDocumentType,

      Values.CompanyCodeCurrency,
      Values.GlobalCurrency,
      Values.FunctionalCurrency,
      Values.FreeDefinedCurrency1,
      Values.FreeDefinedCurrency2,
      Values.FreeDefinedCurrency3,
      Values.FreeDefinedCurrency4,
      Values.FreeDefinedCurrency5,
      Values.FreeDefinedCurrency6,
      Values.FreeDefinedCurrency7,
      Values.FreeDefinedCurrency8,

      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      Values.AmountInCompanyCodeCurrency,
      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'GlobalCurrency'
      Values.AmountInGlobalCurrency,
      @DefaultAggregation:#SUM
      @Semantics.amount.currencyCode: 'FunctionalCurrency'
      Values.AmountInFunctionalCurrency,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'FreeDefinedCurrency1'
      Values.AmountInFreeDefinedCurrency1,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'FreeDefinedCurrency2'
      Values.AmountInFreeDefinedCurrency2,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'FreeDefinedCurrency3'
      Values.AmountInFreeDefinedCurrency3,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'FreeDefinedCurrency4'
      Values.AmountInFreeDefinedCurrency4,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'FreeDefinedCurrency5'
      Values.AmountInFreeDefinedCurrency5,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'FreeDefinedCurrency6'
      Values.AmountInFreeDefinedCurrency6,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'FreeDefinedCurrency7'
      Values.AmountInFreeDefinedCurrency7,
      @DefaultAggregation: #SUM
      @Semantics.amount.currencyCode: 'FreeDefinedCurrency8'
      Values.AmountInFreeDefinedCurrency8,

      LedgerCompanyCodeCrcyRole.CompanyCodeCurrencyRole,
      LedgerCompanyCodeCrcyRole.GlobalCurrencyRole,
      LedgerCompanyCodeCrcyRole.FunctionalCurrencyRole,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency1Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency2Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency3Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency4Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency5Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency6Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency7Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency8Role,

      Values.BaseUnit,
      @DefaultAggregation: #SUM
      @Semantics: { quantity : {unitOfMeasure: 'BaseUnit'} }
      Values.Quantity
}
where
  (
        AssetKeyFigureSpec.PlannedValueIsInclusive =  '1'
    and Values.LedgerFiscalYear                    = $parameters.P_FiscalYear
    and Values.DepreciationFiscalPeriod            <= $parameters.P_FiscalPeriod
  )
  or(
        AssetKeyFigureSpec.PlannedValueIsInclusive =  '2'
    and Values.LedgerFiscalYear                    = $parameters.P_FiscalYear
    and Values.DepreciationFiscalPeriod            <= $parameters.P_FiscalPeriod
    and Values.FiscalPeriod                        > $parameters.P_FiscalPeriod
    and Values.DepreciationFiscalPeriod            <> '000'
  )
  or(
        AssetKeyFigureSpec.PlannedValueIsInclusive =  '0'
    and Values.LedgerFiscalYear                    = $parameters.P_FiscalYear
    and Values.FiscalPeriod                        <= $parameters.P_FiscalPeriod
  )


union all select from    I_LedgerCompanyCodeCrcyRoles   as LedgerCompanyCodeCrcyRole
  inner join             I_FixedAssetPlanValue          as Values                       on  Values.CompanyCode = LedgerCompanyCodeCrcyRole.CompanyCode
                                                                                        and Values.Ledger      = LedgerCompanyCodeCrcyRole.Ledger
  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
  inner join             I_CompanyCode                  as CompanyCode                  on CompanyCode.CompanyCode = Values.CompanyCode
  left outer to one join I_RealDepreciationAreaByCoCode as RealDepreciationAreaByCoCode on  RealDepreciationAreaByCoCode.CompanyCode               = Values.CompanyCode
                                                                                        and RealDepreciationAreaByCoCode.AssetRealDepreciationArea = Values.AssetDepreciationArea
{
  key Values.CompanyCode,
  key Values.AssetDepreciationArea,
  key Values.MasterFixedAsset,
  key Values.FixedAsset,
  key Values.Ledger,
  key Values.FiscalYear,
  key Values.FiscalPeriod,
  key Values.SubLedgerAcctLineItemType,
  key Values.AssetAcctTransClassfctn,
  key AssetKeyFigureSetSpec.AssetAccountingKeyFigure,
  key AssetKeyFigureSetSpec.AssetAccountingKeyFigureSet,
  key cast('' as fis_belnr)              as AccountingDocument,
  key cast('' as fis_docln)              as LedgerGLLineItem,
  key cast('000000' as fins_subta)       as TransactionSubitem,
  key cast('' as shkzg)                  as DebitCreditCode,

      Values.LedgerFiscalYear,
      LedgerCompanyCodeCrcyRole.FiscalYearVariant,
      cast('0000000' as jahrper)         as FiscalYearPeriod,
      Values.FiscalPeriod                as DepreciationFiscalPeriod,
      cast('00000000' as budat)          as PostingDate,

      Values.ChartOfDepreciation,
      AssetKeyFigureSetSpec.AssetAccountingSortedKeyFigure,
      CompanyCode.ChartOfAccounts,
      CompanyCode.ControllingArea,
      CompanyCode.Country,
      CompanyCode.CountryChartOfAccounts as CountryChartOfAccounts,
      CompanyCode.FinancialManagementArea,
      RealDepreciationAreaByCoCode.FinancialStatementVariant,
      Values.AccountingPrinciple,
      Values.LedgerGroup,
      cast('' as fagl_hkont)             as GLAccount,
      cast('' as  fis_altkt_skb1)        as AlternativeGLAccount,
      Values.GroupMasterFixedAsset,
      Values.GroupFixedAsset,
      Values.FinancialAccountType,
      cast('00000000' as fis_cpudt)      as AccountingDocumentCreationDate,
      cast('000000' as fis_cputm)        as CreationTime,
      Values.JointVentureCostRecoveryCode,
      cast('' as kostl)                  as CostCenter,
      cast('' as werks_d)                as Plant,
      cast('' as gsber)                  as BusinessArea,
      cast('' as pernr_d)                as PersonnelNumber,
      cast('' as bp_geber)               as Fund,
      cast('' as prctr)                  as ProfitCenter,
      cast('' as fb_segment)             as Segment,
      cast('' as fm_farea)               as FunctionalArea,
      cast('' as fistl)                  as FundsCenter,
      cast('' as bwasl)                  as AssetTransactionType,
      cast('' as gm_grant_nbr)           as GrantID,
      cast('' as fm_budget_period)       as BudgetPeriod,
      cast('00000000' as bzdat)          as AssetValueDate,
      cast('' as awref)                  as ReferenceDocument,
      cast('000000' as fins_awitem)      as ReferenceDocumentItem,
      cast('' as fins_xreversing)        as IsReversal,
      cast('' as fins_xreversed)         as IsReversed,
      cast('00000000' as bldat)          as DocumentDate,
      cast('' as farp_blart)             as AccountingDocumentType,
      cast('' as sgtxt)                  as DocumentItemText,
      cast('' as dzuonr)                 as AssignmentReference,
      cast('' as vorgn)                  as GLBusinessTransactionType,
      cast('' as fins_bttype)            as BusinessTransactionType,
      cast('' as fins_custbttype)        as BusinessTransactionCategory,
      cast('' as fins_closingstep)       as FinancialClosingStep,
      cast('' as awsys)                  as LogicalSystem,
      cast('' as pbukrs)                 as PartnerCompanyCode,
      cast('' as panl1)                  as PartnerMasterFixedAsset,
      cast('' as panl2)                  as PartnerFixedAsset,
      cast('' as aworg)                  as ReferenceDocumentContext,
      cast('' as fis_awtyp)              as ReferenceDocumentType,

      Values.CompanyCodeCurrency,
      Values.GlobalCurrency,
      cast('' as fins_currfc)            as FunctionalCurrency,
      Values.FreeDefinedCurrency1,
      Values.FreeDefinedCurrency2,
      Values.FreeDefinedCurrency3,
      Values.FreeDefinedCurrency4,
      Values.FreeDefinedCurrency5,
      Values.FreeDefinedCurrency6,
      Values.FreeDefinedCurrency7,
      Values.FreeDefinedCurrency8,

      Values.AmountInCompanyCodeCurrency,
      Values.AmountInGlobalCurrency,
      cast(0 as fins_vfccur12)           as AmountInFunctionalCurrency,
      Values.AmountInFreeDefinedCurrency1,
      Values.AmountInFreeDefinedCurrency2,
      Values.AmountInFreeDefinedCurrency3,
      Values.AmountInFreeDefinedCurrency4,
      Values.AmountInFreeDefinedCurrency5,
      Values.AmountInFreeDefinedCurrency6,
      Values.AmountInFreeDefinedCurrency7,
      Values.AmountInFreeDefinedCurrency8,

      LedgerCompanyCodeCrcyRole.CompanyCodeCurrencyRole,
      LedgerCompanyCodeCrcyRole.GlobalCurrencyRole,
      LedgerCompanyCodeCrcyRole.FunctionalCurrencyRole,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency1Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency2Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency3Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency4Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency5Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency6Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency7Role,
      LedgerCompanyCodeCrcyRole.FreeDefinedCurrency8Role,

      cast('' as meins)                  as BaseUnit,
      cast(0 as quan1_12)                as Quantity
}
where
      AssetKeyFigureSpec.PlannedValueIsInclusive <> '0'
  and Values.LedgerFiscalYear                    = $parameters.P_FiscalYear
  and Values.FiscalPeriod                        <= $parameters.P_FiscalPeriod