P_FixedAsset

@AbapCatalog: { sqlViewName: 'PFIXASSET', preserveKey: true, compiler.compareFilter: true }
@VDM.viewType: #BASIC
@VDM.private:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #NOT_REQUIRED

define view P_FixedAsset
  as select from           anla
    left outer to one join t001                                                            on t001.bukrs = anla.bukrs
    inner join             P_TF_AssetRedesignSwitchStatus( P_SAPClient : $session.client ) on P_TF_AssetRedesignSwitchStatus.IsActive = ''
{
  key anla.bukrs                                             as CompanyCode,
  key anla.anln1                                             as MasterFixedAsset,
  key anla.anln2                                             as FixedAsset,

      anla.anlkl                                             as AssetClass,
      anla.sernr                                             as AssetSerialNumber,
      anla.menge                                             as Quantity,
      anla.meins                                             as BaseUnit,
      anla.invnr                                             as Inventory,
      anla.txt50                                             as FixedAssetDescription,
      anla.txa50                                             as AssetAdditionalDescription,
      t001.waers                                             as Currency,
      anla.urwrt                                             as OriglAcqnAmtInCoCodeCrcy,
      anla.vbund                                             as PartnerCompany,
      anla.herst                                             as AssetManufacturerName,
      anla.antei                                             as InHouseProdnPercent,
      anla.liefe                                             as AssetSupplierName,
      anla.invzu                                             as InventoryNote,
      anla.ord41                                             as Group1AssetEvaluationKey,
      anla.ord42                                             as Group2AssetEvaluationKey,
      anla.ord43                                             as Group3AssetEvaluationKey,
      anla.ord44                                             as Group4AssetEvaluationKey,
      anla.gdlgrp                                            as Group5AssetEvaluationKey,
      anla.ktogr                                             as AssetAccountDetermination,
      anla.aneqk                                             as HasHistory,
      anla.aktiv                                             as AssetCapitalizationDate,
      anla.zujhr                                             as FirstAcquisitionFiscalYear,
      anla.zuper                                             as FirstAcquisitionFiscalPeriod,
      anla.deakt                                             as AssetDeactivationDate,
      anla.gplab                                             as PlannedRetirementDate,
      anla.bstdt                                             as FixedAssetOrderDate,
      anla.aibn1                                             as OriginalMasterFixedAsset,
      anla.anlue                                             as FixedAssetGroup,
      anla.izwek                                             as InvestmentReason,
      anla.aimmo                                             as AssetIsRealEstate,
      anla.feins                                             as AreaSizeUnit,
      anla.eaufn                                             as InvestmentOrder,
      anla.posnr                                             as InvestmentProjectWBSElement,
      cast(anla.posnr as fis_wbsint_no_conv preserving type) as InvestmentProjectWBSElement_2,
      anla.inken                                             as InventoryIsCounted,
      anla.ivdat                                             as LastInventoryDate,
      anla.act_change_pm                                     as AssetSynchronizationRule,
      anla.urjhr                                             as OriginalAcquisitionFiscalYear,
      anla.typbz                                             as AssetTypeName,
      anla.lifnr                                             as Supplier,
      anla.land1                                             as AssetCountryOfOrigin,
      anla.ehwnr                                             as NoticeOfAssessmentTaxID,
      anla.ehwzu                                             as LastAssessmentNoticeDate,
      anla.umwkz                                             as EnvrnmtlInvestmentReason,
      case when anla.aedat = '' then cast('00000000' as fis_aedat_anla)
           when anla.aedat <> '' then cast(anla.aedat as fis_aedat_anla)
      end                                                    as LastChangeDate,
      anla.aneqs                                             as FixedAssetFinDataCmpltns,
      anla.xloev                                             as AccountIsMarkedForDeletion,
      anla.xspeb                                             as AccountIsBlockedForPosting,
      anla.zugdt                                             as AcquisitionValueDate,
      anla.aibdt                                             as OriginalFixedAssetValueDate,
      anla.xinvm                                             as AssetUnderConstIsInvmtMsr,
      anla.abgdt                                             as LastRetirementValueDate,
      anla.aenam                                             as LastChangedByUser,
      anla.eigkz                                             as FixedAssetPropertyType,
      anla.erdat                                             as CreationDate,
      anla.ernam                                             as CreatedByUser,
      anla.grund                                             as ManualDepreciationReason,
      anla.last_reorg_date                                   as LastReorganizationDate,
      anla.ldt_date                                          as LegacyDataTransferDate,
      anla.xanlgr                                            as IsGroupAsset,
      anla.xopvw                                             as IsLineItemSettled,
      anla.aibn2                                             as OriginalFixedAsset,
      anla.mcoa1                                             as MasterFixedAssetSearchTerm,
      anla.xafabch                                           as AssetStatusAtPurchase,
      cast('' as xafabch)                                    as AssetIsAcquiredUsed,
      /* fields from new asset master */
      cast('' as faa_md_xmain)                               as IsMainAsset,
      cast('' as faa_md_lc_status)                           as AssetLifecycleStatus,
      cast('' as faa_md_compl_status)                        as AssetCompletenessStatus,
      cast('' as faa_md_auc_status)                          as AssetUnderConstructionStatus,
      cast('00000000' as faa_md_validity_date)               as AssetCreationValidityDate,
      cast(0 as timestamp)                                   as CreationDateTime,
      cast(0 as timestamp)                                   as LastChangeDateTime,
      cast(anla.felei as faa_layout)                         as AssetScreenLayout,
      cast(0 as urwrt)                                       as OriginalAcquisitionAmount,
      cast('' as faa_md_org_acq_curr)                        as OriginalAcquisitionCurrency,
      anla.vmgli                                             as PropertyClass,
      anla.fiamt                                             as LocalTaxOffice,
      anla.stadt                                             as Municipality,
      anla.gruvo                                             as LandRegisterDate,
      anla.grlfd                                             as LandRegisterEntrySequence,
      anla.grein                                             as LandRegisterEntryDate,
      anla.grbnd                                             as LandRegisterVolume,
      anla.grblt                                             as LandRegisterPage,
      anla.flurk                                             as LandRegisterMap,
      anla.flurn                                             as LandPlot,
      anla.aufla                                             as ConveyanceDate,
      anla.grufl                                             as AreaSize,
      anla.leafi                                             as LeaseSupplier,
      anla.lvtnr                                             as LeaseAgreement,
      anla.lvdat                                             as LeaseAgreementDate,
      anla.lkdat                                             as LeaseTermEndDate,
      anla.leabg                                             as LeaseTermStartDate,
      anla.lejar                                             as LeaseDurationInFiscalYears,
      anla.leper                                             as LeaseDurationInFiscalPeriods,
      anla.leart                                             as LeaseType,
      anla.letxt                                             as LeasedAssetNote,
      cast('' as faa_legacyasset)                            as LegacyAsset,
      cast('' as faa_md_ldt_comp_code)                       as LegacyFixedAssetCompanyCode,
      cast('' as faa_md_ldt_asset_no)                        as LegacyMasterFixedAsset,
      cast('' as faa_md_ldt_asset_subno)                     as LegacyFixedAsset,
      anla.ldt_seqno                                         as LegacyDataTransferSequence,
      cast('' as faa_md_xpostcap)                            as AssetIsForPostCapitalization,
      cast('CLASSIC' as  faa_authcntxt)                      as AssetAuthorizationContext,
      anla.objnr                                             as ObjectInternalID,
      P_TF_AssetRedesignSwitchStatus.IsActive                as AssetRedesignSwitchIsActive
}

union all select from    faat_md_root as root
  left outer to one join faat_md      as main                                            on  main.comp_code   = root.comp_code
                                                                                         and main.asset_no    = root.asset_no
                                                                                         and main.asset_subno = root.asset_subno
  left outer to one join t001                                                            on t001.bukrs = root.comp_code
  inner join             P_TF_AssetRedesignSwitchStatus( P_SAPClient : $session.client ) on P_TF_AssetRedesignSwitchStatus.IsActive = 'X'
{
  key root.comp_code                                                         as CompanyCode,
  key root.asset_no                                                          as MasterFixedAsset,
  key root.asset_subno                                                       as FixedAsset,

      main.asset_class                                                       as AssetClass,
      main.serial_number                                                     as AssetSerialNumber,
      cast(0 as am_menge)                                                    as Quantity, //in ledger

      main.base_unit                                                         as BaseUnit,
      main.inventory_number                                                  as Inventory,
      main.description                                                       as FixedAssetDescription,
      main.add_description                                                   as AssetAdditionalDescription,
      t001.waers                                                             as Currency,
      cast(0 as urwrt)                                                       as OriglAcqnAmtInCoCodeCrcy, //deleted

      main.trade_id                                                          as PartnerCompany,
      main.manufacturer                                                      as AssetManufacturerName,
      main.inhouse_prod_percent                                              as InHouseProdnPercent,
      cast('' as liefe)                                                      as AssetSupplierName, //deleted

      main.inventory_note                                                    as InventoryNote,
      cast('' as ord41)                                                      as Group1AssetEvaluationKey, //deleted

      cast('' as ord42)                                                      as Group2AssetEvaluationKey, //deleted

      cast('' as ord43)                                                      as Group3AssetEvaluationKey, //deleted

      cast('' as ord44)                                                      as Group4AssetEvaluationKey, //deleted

      cast('' as gdlgrp)                                                     as Group5AssetEvaluationKey, //deleted

      main.gl_account_det                                                    as AssetAccountDetermination,
      cast(root.is_historical_managed as aneqk)                              as HasHistory,
      cast('00000000' as aktivd )                                            as AssetCapitalizationDate,  //in ledger

      cast('0000' as dzujahr)                                                as FirstAcquisitionFiscalYear, //in ledger

      cast('000' as dzuper)                                                  as FirstAcquisitionFiscalPeriod, //in ledger

      cast('00000000' as deakt)                                              as AssetDeactivationDate, //in ledger

      cast('00000000' as gplab)                                              as PlannedRetirementDate, //deleted

      main.purchase_order_date                                               as FixedAssetOrderDate,
      main.original_asset                                                    as OriginalMasterFixedAsset,
      main.asset_super_no                                                    as FixedAssetGroup,
      main.invest_reason                                                     as InvestmentReason,
      main.real_estate_asset                                                 as AssetIsRealEstate,
      main.surface_area_unit                                                 as AreaSizeUnit,
      main.investment_io_origin                                              as InvestmentOrder,
      main.investment_wbs_origin                                             as InvestmentProjectWBSElement,
      cast(main.investment_wbs_origin as fis_wbsint_no_conv preserving type) as InvestmentProjectWBSElement_2,
      main.inventory_indicator                                               as InventoryIsCounted,
      main.inventory_date                                                    as LastInventoryDate,
      cast('' as aapm_act_change_pm)                                         as AssetSynchronizationRule,
      main.original_acq_year                                                 as OriginalAcquisitionFiscalYear,
      main.asset_type_name                                                   as AssetTypeName,
      main.supplier_no                                                       as Supplier,
      main.origin_country                                                    as AssetCountryOfOrigin,
      main.assessment_tax_number                                             as NoticeOfAssessmentTaxid,
      main.assessment_date                                                   as LastAssessmentNoticeDate,
      main.environmental_investment                                          as EnvrnmtlInvestmentReason,
      case when main.lastchange_datetime = 0 then cast('00000000' as fis_aedat_anla)
           when main.lastchange_datetime <> 0 then cast(substring(cast(main.lastchange_datetime  as char17), 1, 8) as fis_aedat_anla)
      end                                                                    as LastChangeDate,
      cast(root.completeness_status as aneqs)                                as FixedAssetFinDataCmpltns,
      case when root.lifecycle_status = '2' then cast('X' as xloev)
           when root.lifecycle_status <> '2' then cast('' as xloev)
      end                                                                    as AccountIsMarkedForDeletion,
      root.is_blocked                                                        as AccountIsBlockedForPosting,
      cast('00000000' as dzugdat)                                            as AcquisitionValueDate, //in ledger

      main.original_acq_date                                                 as OriginalFixedAssetValueDate,
      case when root.auc_status = '2' then cast('X' as im_xinvm)
           when root.auc_status <> '2' then cast('' as im_xinvm)
      end                                                                    as AssetUnderConstIsInvmtMsr,
      cast('00000000' as abgdat)                                             as LastRetirementValueDate, //in ledger

      main.lastchange_user                                                   as LastChangedByUser,
      main.property_ind                                                      as FixedAssetPropertyType,
      cast(substring(cast(main.creation_datetime as char17), 1, 8) as erdat) as CreationDate,
      main.creation_user                                                     as CreatedByUser,
      cast('' as grund)                                                      as ManualDepreciationReason, //deleted

      main.last_reorg_date                                                   as LastReorganizationDate,
      main.legacy_transfer_date                                              as LegacyDataTransferDate,
      cast('' as xanlgr_1)                                                   as IsGroupAsset,
      case when root.auc_status = '1' then cast('X' as opvwa)
           when root.auc_status <> '1' then cast('' as opvwa)
      end                                                                    as IsLineItemSettled,
      main.original_subasset                                                 as OriginalFixedAsset,
      cast(main.description as mcoa1)                                        as MasterFixedAssetSearchTerm, // use description as well here otherwise a search on the field is no longer supported in the classic asset master

      cast(main.asset_acquired_used as fis_asset_purchased_new_or_usd)       as AssetStatusAtPurchase,
      main.asset_acquired_used                                               as AssetIsAcquiredUsed,
      root.is_main_asset                                                     as IsMainAsset,
      root.lifecycle_status                                                  as AssetLifecycleStatus,
      root.completeness_status                                               as AssetCompletenessStatus,
      root.auc_status                                                        as AssetUnderConstructionStatus,
      root.validity_date                                                     as AssetCreationValidityDate,
      main.creation_datetime                                                 as CreationDateTime,
      main.lastchange_datetime                                               as LastChangeDateTime,
      main.screen_layout                                                     as AssetScreenLayout,
      main.original_acq_value                                                as OriginalAcquisitionAmount,
      main.original_acq_value_curr                                           as OriginalAcquisitionCurrency,
      main.property_key                                                      as PropertyClass,
      main.land_tax_office                                                   as LocalTaxOffice,
      main.land_municipality                                                 as Municipality,
      main.land_register_of                                                  as LandRegisterDate,
      main.land_register_entry_number                                        as LandRegisterEntrySequence,
      main.land_register_entry                                               as LandRegisterEntryDate,
      main.land_register_volume                                              as LandRegisterVolume,
      main.land_register_page                                                as LandRegisterPage,
      main.land_register_map_no                                              as LandRegisterMap,
      main.land_plot_number                                                  as LandPlot,
      main.conveyance_date                                                   as ConveyanceDate,
      main.surface_area                                                      as AreaSize,
      main.lease_company                                                     as LeaseSupplier,
      main.lease_agreement_no                                                as LeaseAgreement,
      main.lease_agreement_date                                              as LeaseAgreementDate,
      main.lease_notice_date                                                 as LeaseTermEndDate,
      main.lease_start_date                                                  as LeaseTermStartDate,
      main.lease_years                                                       as LeaseDurationInFiscalYears,
      main.lease_periods                                                     as LeaseDurationInFiscalPeriods,
      main.lease_type                                                        as LeaseType,
      main.lease_data_text                                                   as LeasedAssetNote,
      cast(main.legacy_asset_no as faa_legacyasset)                          as LegacyAsset,
      main.legacy_comp_code                                                  as LegacyFixedAssetCompanyCode,
      main.legacy_asset_no                                                   as LegacyMasterFixedAsset,
      main.legacy_asset_subno                                                as LegacyFixedAsset,
      main.legacy_transfer_seq_no                                            as LegacyDataTransferSequence,
      root.is_post_capitalization                                            as AssetIsForPostCapitalization,
      cast('NEW' as faa_authcntxt)                                           as AssetAuthorizationContext,
      main.object_nr                                                         as ObjectInternalID,
      P_TF_AssetRedesignSwitchStatus.IsActive                                as AssetRedesignSwitchIsActive
}
where
      root.lifecycle_status <> '2'
  and root.lifecycle_status <> '8'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"ANLA",
"FAAT_MD",
"FAAT_MD_ROOT",
"T001"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/