I_FxdAstStatisticalLineItem

@AbapCatalog: {sqlViewName: 'IFASSETSTATLIT', preserveKey: true}
@EndUserText.label: 'Statistical Line Items of Fixed Asset'
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #CHECK
@ObjectModel: { usageType.serviceQuality: #C,
                usageType.sizeCategory: #XXL,
                usageType.dataClass: #MIXED }
@ClientHandling.algorithm: #SESSION_VARIABLE

define view I_FxdAstStatisticalLineItem
  as select from           faat_doc_it as docit
    left outer to one join I_LedgerCompanyCodeCrcyRoles on  docit.rldnr = I_LedgerCompanyCodeCrcyRoles.Ledger
                                                        and docit.bukrs = I_LedgerCompanyCodeCrcyRoles.CompanyCode

  association [0..1] to I_ReferenceDocumentType        as _ReferenceDocumentType        on  $projection.ReferenceDocumentType = _ReferenceDocumentType.ReferenceDocumentType
  association [0..1] to I_LogicalSystem                as _LogicalSystem                on  $projection.LogicalSystem = _LogicalSystem.LogicalSystem
  association [0..1] to I_DepreciationAreaForLedger    as _DepreciationArea             on  $projection.CompanyCode           = _DepreciationArea.CompanyCode
                                                                                        and $projection.Ledger                = _DepreciationArea.Ledger
                                                                                        and $projection.AssetDepreciationArea = _DepreciationArea.AssetDepreciationArea
  association [0..1] to I_ChartOfDepreciation          as _ChartOfDepreciation          on  $projection.chartofdepreciation = _ChartOfDepreciation.ChartOfDepreciation
  association [0..1] to I_CompanyCode                  as _CompanyCode                  on  $projection.CompanyCode = _CompanyCode.CompanyCode
  association [0..1] to I_FiscalYearForCompanyCode     as _FiscalYear                   on  $projection.FiscalYear  = _FiscalYear.FiscalYear
                                                                                        and $projection.CompanyCode = _FiscalYear.CompanyCode
  association [0..1] to I_FiscalYearPeriodForCmpnyCode as _FiscalPeriod                 on  $projection.FiscalYear   = _FiscalPeriod.FiscalYear
                                                                                        and $projection.FiscalPeriod = _FiscalPeriod.FiscalPeriod
                                                                                        and $projection.CompanyCode  = _FiscalPeriod.CompanyCode
  association [0..1] to I_MasterFixedAsset             as _MasterFixedAsset             on  $projection.CompanyCode      = _MasterFixedAsset.CompanyCode
                                                                                        and $projection.MasterFixedAsset = _MasterFixedAsset.MasterFixedAsset
  association [0..1] to I_FixedAsset                   as _FixedAsset                   on  $projection.CompanyCode      = _FixedAsset.CompanyCode
                                                                                        and $projection.MasterFixedAsset = _FixedAsset.MasterFixedAsset
                                                                                        and $projection.FixedAsset       = _FixedAsset.FixedAsset
  association [0..1] to I_MasterFixedAsset             as _PartnerMasterFixedAsset      on  $projection.CompanyCode             = _PartnerMasterFixedAsset.CompanyCode
                                                                                        and $projection.PartnerMasterFixedAsset = _PartnerMasterFixedAsset.MasterFixedAsset
  association [0..1] to I_FixedAsset                   as _PartnerFixedAsset            on  $projection.CompanyCode             = _PartnerFixedAsset.CompanyCode
                                                                                        and $projection.PartnerMasterFixedAsset = _PartnerFixedAsset.MasterFixedAsset
                                                                                        and $projection.PartnerFixedAsset       = _PartnerFixedAsset.FixedAsset
  association [0..1] to I_SubLedgerAccLineItemType     as _SubLedgerAccLineItemType     on  $projection.SubLedgerAcctLineItemType = _SubLedgerAccLineItemType.SubLedgerAcctLineItemType
  association [0..1] to I_Ledger                       as _Ledger                       on  $projection.Ledger = _Ledger.Ledger
  association [0..1] to I_LedgerGroup                  as _LedgerGroup                  on  $projection.LedgerGroup = _LedgerGroup.LedgerGroup

  association [0..1] to I_MovementCategory             as _AssetAcctTransClassfctn      on  $projection.AssetAcctTransClassfctn = _AssetAcctTransClassfctn.AssetAcctTransClassfctn
  association [0..1] to I_AssetTransactionType         as _AssetTransactionType         on  $projection.AssetTransactionType = _AssetTransactionType.AssetTransactionType
  association [1]    to I_DebitCreditCode              as _DebitCreditCode              on  $projection.DebitCreditCode = _DebitCreditCode.DebitCreditCode
  association [0..1] to I_Currency                     as _CompanyCodeCurrency          on  $projection.CompanyCodeCurrency = _CompanyCodeCurrency.Currency
  association [0..1] to I_Currency                     as _GlobalCurrency               on  $projection.GlobalCurrency = _GlobalCurrency.Currency
  association [0..1] to I_Currency                     as _FreeDefinedCurrency1         on  $projection.FreeDefinedCurrency1 = _FreeDefinedCurrency1.Currency
  association [0..1] to I_Currency                     as _FreeDefinedCurrency2         on  $projection.FreeDefinedCurrency2 = _FreeDefinedCurrency2.Currency
  association [0..1] to I_Currency                     as _FreeDefinedCurrency3         on  $projection.FreeDefinedCurrency3 = _FreeDefinedCurrency3.Currency
  association [0..1] to I_Currency                     as _FreeDefinedCurrency4         on  $projection.FreeDefinedCurrency4 = _FreeDefinedCurrency4.Currency
  association [0..1] to I_Currency                     as _FreeDefinedCurrency5         on  $projection.FreeDefinedCurrency5 = _FreeDefinedCurrency5.Currency
  association [0..1] to I_Currency                     as _FreeDefinedCurrency6         on  $projection.FreeDefinedCurrency6 = _FreeDefinedCurrency6.Currency
  association [0..1] to I_Currency                     as _FreeDefinedCurrency7         on  $projection.FreeDefinedCurrency7 = _FreeDefinedCurrency7.Currency
  association [0..1] to I_Currency                     as _FreeDefinedCurrency8         on  $projection.FreeDefinedCurrency8 = _FreeDefinedCurrency8.Currency
  association [1]    to I_AccountingPrinciple          as _AccountingPrinciple          on  $projection.AccountingPrinciple = _AccountingPrinciple.AccountingPrinciple
  association [0..1] to I_ChartOfDeprForAcctgPrinciple as _ChartOfDeprForAcctgPrinciple on  $projection.AccountingPrinciple = _ChartOfDeprForAcctgPrinciple.AccountingPrinciple
                                                                                        and $projection.CompanyCode         = _ChartOfDeprForAcctgPrinciple.CompanyCode
{
      @ObjectModel.foreignKey.association: '_CompanyCode'
  key docit.bukrs                                              as CompanyCode,
      @ObjectModel.foreignKey.association: '_MasterFixedAsset'
  key docit.anln1                                              as MasterFixedAsset,
      @ObjectModel.foreignKey.association: '_FixedAsset'
  key docit.anln2                                              as FixedAsset,
      @ObjectModel.foreignKey.association: '_FiscalYear'
  key docit.gjahr                                              as FiscalYear,
      @ObjectModel.foreignKey.association: '_ReferenceDocumentType'
  key cast(docit.awtyp as fis_awtyp)                           as ReferenceDocumentType,
  key cast(docit.awref as fis_awref)                           as ReferenceDocument,
  key docit.aworg                                              as ReferenceDocumentContext,
      @ObjectModel.foreignKey.association: '_LogicalSystem'
  key docit.awsys                                              as LogicalSystem,
  key docit.subta                                              as TransactionSubitem,
      @ObjectModel.foreignKey.association: '_DepreciationArea'
  key docit.afabe                                              as AssetDepreciationArea,
      @ObjectModel.foreignKey.association: '_SubledgerAccLineItemType'
  key cast(docit.slalittype as fis_slalittype preserving type) as SubLedgerAcctLineItemType,
      @ObjectModel.foreignKey.association: '_DebitCreditCode'
  key cast(docit.drcrk as fis_shkzg)                           as DebitCreditCode,

      @Semantics.fiscal.year: true
      cast(docit.gjahr as gjahr_pos)                           as LedgerFiscalYear,
      @ObjectModel.foreignKey.association: '_AccountingPrinciple'
      I_LedgerCompanyCodeCrcyRoles.AccountingPrinciple,
      @ObjectModel.foreignKey.association: '_Ledger'
      docit.rldnr                                              as Ledger,
      @ObjectModel.foreignKey.association: '_LedgerGroup'
      cast('' as faa_deprecated_target_ldgrp)                  as LedgerGroup,
      @ObjectModel.foreignKey.association: '_FiscalPeriod'
      docit.poper                                              as FiscalPeriod,
      cast('          ' as fagl_hkont preserving type)         as GLAccount,
      @ObjectModel.foreignKey.association: '_AssetAcctTransClassfctn'
      docit.movcat                                             as AssetAcctTransClassfctn,
      docit.anlgr                                              as GroupMasterFixedAsset,
      docit.anlgr2                                             as GroupFixedAsset,
      @ObjectModel.foreignKey.association: '_ChartOfDepreciation'
      _ChartOfDeprForAcctgPrinciple.ChartOfDepreciation,
      docit.awitem                                             as ReferenceDocumentItem,

      docit.awref_rev                                          as ReversalReferenceDocument,
      docit.aworg_rev                                          as ReversalReferenceDocumentCntxt,
      docit.xreversing                                         as IsReversal,
      docit.xreversed                                          as IsReversed,
      docit.bldat                                              as DocumentDate,
      docit.budat                                              as PostingDate,
      @ObjectModel.foreignKey.association: '_AssetTransactionType'
      docit.bwasl                                              as AssetTransactionType,
      docit.bzdat                                              as AssetValueDate,
      @Semantics.businessDate.at: true
      cast(docit.cpudt as fis_cpudt)                           as AccountingDocumentCreationDate,
      cast(docit.cputm as fis_cputm)                           as CreationTime,
      docit.sgtxt                                              as DocumentItemText,
      cast(docit.usnam as fis_usnam)                           as AccountingDocCreatedByUser,
      docit.vorgn                                              as GLBusinessTransactionType,
      docit.zuonr                                              as AssignmentReference,
      docit.recid                                              as JointVentureCostRecoveryCode,
      @ObjectModel.foreignKey.association: '_PartnerMasterFixedAsset'
      docit.panl1                                              as PartnerMasterFixedAsset,
      @ObjectModel.foreignKey.association: '_PartnerFixedAsset'
      docit.panl2                                              as PartnerFixedAsset,

      //Measures

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_CompanyCodeCurrency'
      docit.rhcur                                              as CompanyCodeCurrency,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
      docit.hsl                                                as AmountInCompanyCodeCurrency,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_GlobalCurrency'
      docit.rkcur                                              as GlobalCurrency,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
      docit.ksl                                                as AmountInGlobalCurrency,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_FreeDefinedCurrency1'
      docit.rocur                                              as FreeDefinedCurrency1,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'FreeDefinedCurrency1'} }
      docit.osl                                                as AmountInFreeDefinedCurrency1,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_FreeDefinedCurrency2'
      docit.rvcur                                              as FreeDefinedCurrency2,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'FreeDefinedCurrency2'} }
      docit.vsl                                                as AmountInFreeDefinedCurrency2,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_FreeDefinedCurrency3'
      docit.rbcur                                              as FreeDefinedCurrency3,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'FreeDefinedCurrency3'} }
      docit.bsl                                                as AmountInFreeDefinedCurrency3,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_FreeDefinedCurrency4'
      docit.rccur                                              as FreeDefinedCurrency4,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'FreeDefinedCurrency4'} }
      docit.csl                                                as AmountInFreeDefinedCurrency4,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_FreeDefinedCurrency5'
      docit.rdcur                                              as FreeDefinedCurrency5,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'FreeDefinedCurrency5'} }
      docit.dsl                                                as AmountInFreeDefinedCurrency5,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_FreeDefinedCurrency6'
      docit.recur                                              as FreeDefinedCurrency6,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'FreeDefinedCurrency6'} }
      docit.esl                                                as AmountInFreeDefinedCurrency6,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_FreeDefinedCurrency7'
      docit.rfcur                                              as FreeDefinedCurrency7,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'FreeDefinedCurrency7'} }
      docit.fsl                                                as AmountInFreeDefinedCurrency7,

      @Semantics.currencyCode:true
      @ObjectModel.foreignKey.association: '_FreeDefinedCurrency8'
      docit.rgcur                                              as FreeDefinedCurrency8,
      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'FreeDefinedCurrency8'} }
      docit.gsl                                                as AmountInFreeDefinedCurrency8,


      //Publish Associations

      _ReferenceDocumentType,
      _LogicalSystem,
      _DepreciationArea,
      _CompanyCode,
      _FiscalYear,
      _FiscalPeriod,
      _MasterFixedAsset,
      _FixedAsset,
      _SubLedgerAccLineItemType,
      _Ledger,
      _LedgerGroup,
      _AssetAcctTransClassfctn,
      _ChartOfDepreciation,
      _AssetTransactionType,
      _DebitCreditCode,
      _CompanyCodeCurrency,
      _GlobalCurrency,
      _FreeDefinedCurrency1,
      _FreeDefinedCurrency2,
      _FreeDefinedCurrency3,
      _FreeDefinedCurrency4,
      _FreeDefinedCurrency5,
      _FreeDefinedCurrency6,
      _FreeDefinedCurrency7,
      _FreeDefinedCurrency8,
      _AccountingPrinciple,
      _PartnerMasterFixedAsset,
      _PartnerFixedAsset
}
where
  (
    (
          vorgn            <>      'AFAB'
      and movcat           <>      '06'
      and not(
        slalittype         between '07203' and '07215'
      )
    )
    or(
          movcat           =       '00'
    )
  )
  //Exclude FAAT_DOC_IT records which are migrated to ACDOCA

  and     docit.sdm_status <>      '18'
  and     docit.sdm_status <>      '19'
  and     docit.sdm_status <>      '22'
  and     docit.sdm_status <>      '1A'