-
Notifications
You must be signed in to change notification settings - Fork 31
/
Copy pathFixedAssetsMD.sql
129 lines (129 loc) · 7.64 KB
/
FixedAssetsMD.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
#-- Copyright 2022 Google LLC
#--
#-- Licensed under the Apache License, Version 2.0 (the "License");
#-- you may not use this file except in compliance with the License.
#-- You may obtain a copy of the License at
#--
#-- https://www.apache.org/licenses/LICENSE-2.0
#--
#-- Unless required by applicable law or agreed to in writing, software
#-- distributed under the License is distributed on an "AS IS" BASIS,
#-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#-- See the License for the specific language governing permissions and
#-- limitations under the License.
SELECT
ANLA.MANDT AS Client_MANDT, ANLA.BUKRS AS CompanyCode_BUKRS,
ANLA.ANLN1 AS MainAssetNumber_ANLN1, ANLA.ANLN2 AS AssetSubnumber_ANLN2,
ANLA.ANLKL AS AssetClass_ANLKL, ANLA.GEGST AS TechnicalAssetNumber_GEGST,
ANLA.ANLAR AS AssetTypes_ANLAR, ANLA.ERNAM AS NameOfPersonWhoCreatedTheObject_ERNAM,
ANLA.ERDAT AS DateOnWhichRecordWasCreated_ERDAT,
ANLA.AENAM AS NameOfPersonWhoChangedObject_AENAM,
ANLA.AEDAT AS ChangedOn_AEDAT,
ANLA.XLOEV AS Indicator_AccountMarkedForDeletion_XLOEV,
ANLA.XSPEB AS Indicator_AssetLockedToAcquisitionPostings_XSPEB,
ANLA.FELEI AS ScreenLayoutForAssetAccounting_FELEI,
ANLA.KTOGR AS AccountDetermination_KTOGR,
ANLA.XOPVW AS AssetUnderConstructWithLineItemSettlement_XOPVW,
ANLA.ANLTP AS AssetCategory_ANLTP,
ANLA.ZUJHR AS FiscalYearInWhichFirstAcquisitionWasPosted_ZUJHR,
ANLA.ZUPER AS PeriodInWhichFirstAcquisitionWasPosted_ZUPER,
ANLA.ZUGDT AS AssetValueDateOfTheFirstPosting_ZUGDT,
ANLA.AKTIV AS AssetCapitalizationDate_AKTIV,
ANLA.ABGDT AS AssetValueDateForTheLastRetirement_ABGDT,
ANLA.DEAKT AS DeactivationDate_DEAKT,
ANLA.GPLAB AS PlannedRetirementDate_GPLAB, ANLA.BSTDT AS AssetPurchaseOrderDate_BSTDT,
ANLA.ORD41 AS EvaluationGroup1_ORD41, ANLA.ORD42 AS EvaluationGroup2_ORD42,
ANLA.ORD43 AS EvaluationGroup3_ORD43,
ANLA.ORD44 AS EvaluationGroup4_ORD44, ANLA.ANLUE AS AssetSuperNumber_ANLUE,
ANLA.ZUAWA AS KeyForSortingAccordingToAssignmentNumbers_ZUAWA,
ANLA.ANEQK AS Indicator_HistoricalManagement_ANEQK,
ANLA.ANEQS AS CompletenessIndicatorForTheAsset_ANEQS,
ANLA.LIFNR AS AccountNumberOfVendor__otherKeyWord___LIFNR,
ANLA.LAND1 AS AssetsCountryOfOrigin_LAND1, ANLA.LIEFE AS NameOfAssetSupplier_LIEFE,
ANLA.HERST AS ManufacturerOfAsset_HERST, ANLA.EIGKZ AS PropertyIndicator_EIGKZ,
ANLA.AIBN1 AS OriginalAssetThatWasTransferred_AIBN1,
ANLA.AIBN2 AS OriginalAssetThatWasTransferred_AIBN2,
ANLA.AIBDT AS OriginalAcquisitionDateOfAucTransferredAsset_AIBDT,
ANLA.URJHR AS FiscalYearOfOriginalAcquisition_URJHR,
ANLA.URWRT AS OriginalAcquisitionValue_URWRT,
ANLA.ANTEI AS InHouseProductionPercentage_ANTEI,
ANLA.PROJN AS Old_ProjectNumber_Ps_posnr_PROJN,
ANLA.EAUFN AS InvestmentOrder_EAUFN, ANLA.MEINS AS BaseUnitOfMeasure_MEINS,
ANLA.MENGE AS Quantity_MENGE, ANLA.TYPBZ AS AssetTypeName_TYPBZ,
ANLA.IZWEK AS ReasonForInvestment_IZWEK, ANLA.INKEN AS InventoryIndicator_INKEN,
ANLA.IVDAT AS LastInventoryDate_IVDAT,
ANLA.INVZU AS SupplementaryInventorySpecifications_INVZU,
ANLA.VMGLI AS PropertyClassificationKey_VMGLI,
ANLA.XVRMW AS ManualNetWorthTaxValue_XVRMW, ANLA.WRTMA AS ManualNetWorthTaxValue_WRTMA,
ANLA.EHWRT AS AssessedValue_EHWRT, ANLA.AUFLA AS DateOfConveyance_AUFLA,
ANLA.EHWZU AS DateOfLastNoticeOfAssessment_EHWZU,
ANLA.EHWNR AS TaxNumberOfTheNoticeOfAssessment_EHWNR, ANLA.GRUVO AS LandRegisterOf_GRUVO,
ANLA.GREIN AS LandRegisterEntryOn_GREIN, ANLA.GRBND AS LandRegisterVolume_GRBND,
ANLA.GRBLT AS LandRegisterPage_GRBLT,
ANLA.GRLFD AS LandRegisterEntry_SequenceNumber_GRLFD,
ANLA.FLURK AS LandRegisterMapNumber_FLURK, ANLA.FLURN AS PlotNumber_FLURN,
ANLA.FIAMT AS LocalTaxOffice_FIAMT, ANLA.STADT AS Municipality_STADT,
ANLA.GRUND AS ReasonForManualValuationOfNetAssets_GRUND, ANLA.FEINS AS AreaUnit_FEINS,
ANLA.GRUFL AS SurfaceArea_GRUFL, ANLA.INVNR AS InventoryNumber_INVNR,
ANLA.VBUND AS CompanyIdOfTradingPartner_VBUND,
ANLA.SPRAS AS LanguageKeyANLA_SPRAS, ANLA.TXT50 AS AssetDescriptionMD_TXT50, ANLA.TXA50 AS AdditionalAssetDescription_TXA50, ANLA.XLTXID AS Indicator_LongTextActive_XLTXID,
ANLA.XVERID AS LongTextIndicator_PropertyValues_XVERID,
ANLA.XTCHID AS Indicator_TechnicalViewLongText_XTCHID,
ANLA.XKALID AS Indicator_LongTextActive_XKALID,
ANLA.XHERID AS Indicator_LongTextForManufacturerorigin_XHERID,
ANLA.XLEAID AS Indicator_LeasingDataLongText_XLEAID,
ANLA.LEAFI AS LeasingCompany_LEAFI, ANLA.LVDAT AS LeasingAgreementDate_LVDAT,
ANLA.LKDAT AS LeasingAgreementNoticeDate_LKDAT, ANLA.LEABG AS LeaseStartDate_LEABG,
ANLA.LEJAR AS LengthOfLeaseInYears_LEJAR,
ANLA.LEPER AS LengthOfLeaseInPeriods_LEPER, ANLA.LRYTH AS LeasePaymentCycle_LRYTH,
ANLA.LEGEB AS PeriodicLeasePaymentPaidToLeasingCompanylessor_LEGEB,
ANLA.LBASW AS LeasingBaseValueAsNew_LBASW,
ANLA.LKAUF AS LeasedAssetPurchasePrice_LKAUF,
ANLA.LMZIN AS LeasingMonthlyInterestRate__capitalLease___LMZIN,
ANLA.LZINS AS LeasingInterestRate__capitalLease___LZINS,
ANLA.LTZBW AS PostingDateOfTheLastLeasePayment_LTZBW,
ANLA.LKUZA AS TotalOfPostedLeasingPayments_LKUZA,
ANLA.LKUZI AS TotalLeasingInterestPosted_LKUZI,
ANLA.LLAVB AS TotalOfLongTermLeasingLiability_LLAVB,
ANLA.LEANZ AS NumberOfLeasePayments_LEANZ,
ANLA.LVTNR AS LeasingAgreementNumber_LVTNR,
ANLA.LETXT AS LeasingDataText_LETXT,
ANLA.XAKTIV AS Indicator_CapitalizeFixedAsset_XAKTIV,
ANLA.ANUPD AS Indicator_TypeOfChangeToTable_ANUPD,
ANLA.LBLNR AS NoOfLastDocumentWithWhichPostingToAssetWasMade_LBLNR,
ANLA.XV0DT AS ChangeDate_View0_XV0DT,
ANLA.XV0NM AS NameOfPersonInChargeWhoChangedView0_XV0NM,
ANLA.XV1DT AS ChangeDate_View1_XV1DT,
ANLA.XV1NM AS NameOfPersonInChargeWhoChangedView1_XV1NM,
ANLA.XV2DT AS ChangeDate_View2_XV2DT, ANLA.XV2NM AS NameOfThePersonInChargeWhoChangedView2_XV2NM, ANLA.XV3DT AS ChangeDate_View3_XV3DT,
ANLA.XV3NM AS NameOfThePersonInChargeWhoChangedView3_XV3NM,
ANLA.XV4DT AS ChangeDate_View4_XV4DT,
ANLA.XV4NM AS NameOfThePersonInChargeWhoChangedView4_XV4NM,
ANLA.XV5DT AS ChangeDate_View5_XV5DT,
ANLA.XV5NM AS NameOfThePersonInChargeWhoChangedView5_XV5NM,
ANLA.XV6DT AS ChangeDate_View6_XV6DT,
ANLA.XV6NM AS NameOfThePersonInChargeWhoChangedView6_XV6NM,
ANLA.AIMMO AS Indicator_RealEstate_AIMMO,
ANLA.OBJNR AS ObjectNumber_OBJNR, ANLA.LEART AS LeasingType_LEART,
ANLA.LVORS AS AdvancePayments_LVORS, ANLA.GDLGRP AS EvaluationGroup5_GDLGRP,
ANLA.POSNR AS WbsElementInvestmentProject_POSNR,
ANLA.XERWRT AS Indicator_IgnoreMemoValue_XERWRT,
ANLA.XAFABCH AS AssetAcquiredUsed_XAFABCH,
ANLA.XANLGR AS Indicator_AssetIsAGroupAsset_XANLGR,
ANLA.MCOA1 AS SearchTermForMatchcodeSearch_MCOA1,
ANLA.XINVM AS AssetUnderConstructionForInvestmentMeasures_XINVM,
ANLA.SERNR AS SerialNumber_SERNR, ANLA.UMWKZ AS ReasonForEnvironmentalInvestment_UMWKZ,
ANLA.LRVDAT AS DateOfLastRevaluation_LRVDAT,
ANLA.ACT_CHANGE_PM AS ChangeAssetMasterRecordFromEquipmentMaster_ACT_CHANGE_PM,
ANLA.HAS_TDDP AS AssetHasTimeDependentValuationParametersInAnlbza_HAS_TDDP,
ANLA.LAST_REORG_DATE AS DateOfLastReorganization_LAST_REORG_DATE,
ANLA.INTERNAL_CODE AS InternalCode_INTERNAL_CODE,
ANLA.APPROVAL_NUMBER AS ApprovalDocumentNumber_APPROVAL_NUMBER,
ANLA.APPROVAL_VERSION AS ApprovalVersion_APPROVAL_VERSION,
ANLA.BOGUS_ANLN1 AS BogusAssetNumber_BOGUS_ANLN1,
ANLA.ASSET_VERSION AS AssetVersion_ASSET_VERSION,
ANKT.SPRAS AS Language_SPRAS, ANKT.TXK20 AS ShortTextForAssetClassName_TXK20,
ANKT.TXK50 AS AssetClassDescription_TXK50, ANKT.TXT50 AS AssetDescription_TXT50,
ANKT.TXA50 AS AssetDescription_TXA50
FROM `{{ project_id_src }}.{{ dataset_cdc_processed }}.anla` AS anla INNER JOIN `{{ project_id_src }}.{{ dataset_cdc_processed }}.ankt` AS ankt
ON anla.mandt = ankt.mandt AND anla.anlkl = ankt.anlkl