P_RO_STRPFIXEDASSETCHARTVALUE

@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
                          case
                            when AssetAcctTransClassfctn = '00' then cast( '4' as fiaaro_chtitemtype ) //item migrated to UJE in prev.years

                       else cast( '9' as fiaaro_chtitemtype )
                          end
                     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
  )