s4/VendorsMD.sql

SELECT
  LFA1.MANDT AS Client_MANDT, LFA1.LIFNR AS AccountNumberOfVendorOrCreditor_LIFNR,
  LFA1.LAND1 AS CountryKey_LAND1, LFA1.NAME1 AS NAME1,
  LFA1.NAME2 AS NAME2, LFA1.NAME3 AS NAME3,
  LFA1.NAME4 AS NAME4, LFA1.ORT01 AS City_ORT01,
  LFA1.ORT02 AS District_ORT02, LFA1.PFACH AS PoBox_PFACH, LFA1.PSTL2 AS POBoxPostalCode_PSTL2,
  LFA1.PSTLZ AS PostalCode_PSTLZ,
  LFA1.REGIO AS Region__REGIO, LFA1.SORTL AS SortField_SORTL,
  LFA1.STRAS AS StreetAndHouseNumber_STRAS, LFA1.ADRNR AS Address_ADRNR,
  LFA1.MCOD1 AS SearchTermForMatchcodeSearch_MCOD1,
  LFA1.MCOD2 AS SearchTermForMatchcodeSearch_MCOD2,
  LFA1.MCOD3 AS SearchTermForMatchcodeSearch_MCOD3, LFA1.ANRED AS Title_ANRED,
  LFA1.BAHNS AS TrainStation_BAHNS,
  LFA1.BBBNR AS InternationalLocationNumber__part1___BBBNR,
  LFA1.BBSNR AS InternationalLocationNumber__part2___BBSNR, LFA1.BEGRU AS AuthorizationGroup_BEGRU,
  LFA1.BRSCH AS IndustryKey_BRSCH,
  LFA1.BUBKZ AS CheckDigitForTheInternationalLocationNumber_BUBKZ,
  LFA1.DATLT AS DataCommunicationLineNo_DATLT, LFA1.DTAMS AS ReportKeyForDataMediumExchange_DTAMS,
  LFA1.DTAWS AS InstructionKeyForDataMediumExchange_DTAWS,
  LFA1.ERDAT AS DateOnWhichTheRecordWasCreated_ERDAT,
  LFA1.ERNAM AS NameOfPersonWhoCreatedTheObject_ERNAM,
  LFA1.ESRNR AS PorSubscriberNumber_ESRNR,
  LFA1.KONZS AS GroupKey_KONZS, LFA1.KTOKK AS VendorAccountGroup_KTOKK,
  LFA1.KUNNR AS CustomerNumber_KUNNR, LFA1.LNRZA AS AccountNumberOfTheAlternativePayee_LNRZA,
  LFA1.LOEVM AS CentralDeletionFlagForMasterRecord_LOEVM,
  LFA1.SPERR AS CentralPostingBlock_SPERR, LFA1.SPERM AS CentrallyImposedPurchasingBlock_SPERM,
  LFA1.SPRAS AS LanguageKey_SPRAS, LFA1.STCD1 AS TaxNumber1_STCD1,
  LFA1.STCD2 AS TaxNumber2_STCD2,
  LFA1.STKZA AS Indicator_BusinessPartnerSubjectToEqualizationTax_STKZA,
  LFA1.STKZU AS LiableForVat_STKZU, LFA1.TELBX AS TeleboxNumber_TELBX,
  LFA1.TELF1 AS FirstTelephoneNumber_TELF1,
  LFA1.TELF2 AS SecondTelephoneNumber_TELF2, LFA1.TELFX AS FaxNumber_TELFX,
  LFA1.TELTX AS TeletexNumber_TELTX, LFA1.TELX1 AS TelexNumber_TELX1,
  LFA1.XCPDK AS Indicator_IsTheAccountAOneTimeAccount_XCPDK,
  LFA1.XZEMP AS Indicator_AlternativePayeeInDocumentAllowed_XZEMP,
  LFA1.VBUND AS CompanyIdOfTradingPartner_VBUND,
  LFA1.FISKN AS AccountNumberOfTheMasterRecordWithFiscalAddress_FISKN,
  LFA1.STCEG AS VatRegistrationNumber_STCEG, LFA1.STKZN AS NaturalPerson_STKZN,
  LFA1.SPERQ AS FunctionThatWillBeBlocked_SPERQ,
  LFA1.GBORT AS PlaceOfBirthOfThePersonSubjectToWithholdingTax_GBORT,
  LFA1.GBDAT AS DateOfBirthOfThePersonSubjectToWithholdingTax_GBDAT,
  LFA1.SEXKZ AS KeyForTheSexOfThePersonSubjectToWithholdingTax_SEXKZ,
  LFA1.KRAUS AS CreditInformationNumber_KRAUS,
  LFA1.REVDB AS LastReview__external___REVDB, LFA1.QSSYS AS VendorsQmSystem_QSSYS,
  LFA1.KTOCK AS ReferenceAccountGroupForOneTimeAccount_vendor_KTOCK,
  LFA1.PFORT AS PoBoxCity_PFORT,
  LFA1.WERKS AS Plant__ownOrExternal___WERKS, LFA1.LTSNA AS Indicator_VendorSubRangeRelevant_LTSNA,
  LFA1.WERKR AS Indicator_PlantLevelRelevant_WERKR,
  LFA1.PLKAL AS FactoryCalendarKey_PLKAL,
  LFA1.DUEFL AS StatusOfDataTransferIntoSubsequentRelease_DUEFL,
  LFA1.TXJCD AS TaxJurisdiction_TXJCD,
  LFA1.SPERZ AS PaymentBlock_SPERZ, LFA1.SCACD AS StandardCarrierAccessCode_SCACD,
  LFA1.SFRGR AS ForwardingAgentFreightGroup_SFRGR,
  LFA1.LZONE AS TransportationZoneToOrFromWhichTheGoodsAreDelivered_LZONE,
  LFA1.XLFZA AS Indicator_AlternativePayeeUsingAccountNumber_XLFZA,
  LFA1.DLGRP AS ServiceAgentProcedureGroup_DLGRP, LFA1.FITYP AS TaxType_FITYP,
  LFA1.STCDT AS TaxNumberType_STCDT, LFA1.REGSS AS RegisteredForSocialInsurance_REGSS,
  LFA1.ACTSS AS ActivityCodeForSocialInsurance_ACTSS,
  LFA1.STCD3 AS TaxNumber3_STCD3, LFA1.STCD4 AS TaxNumber4_STCD4, LFA1.STCD5 AS TaxNumber5_STCD5,
  LFA1.IPISP AS TaxSplit_IPISP, LFA1.TAXBS AS TaxBaseInPercentage_TAXBS,
  LFA1.PROFS AS Profession_PROFS,
  LFA1.STGDL AS Shipment_StatisticsGroupTransportationServiceAgent_STGDL,
  LFA1.EMNFR AS ExternalManufacturerCodeNameOrNumber_EMNFR,
  LFA1.LFURL AS UniformResourceLocator_LFURL,
  LFA1.J_1KFREPRE AS NameOfRepresentative_J_1KFREPRE,
  LFA1.J_1KFTBUS AS TypeOfBusiness_J_1KFTBUS, LFA1.J_1KFTIND AS TypeOfIndustry_J_1KFTIND,
  LFA1.CONFS AS StatusOfChangeAuthorization__central___CONFS,
  LFA1.UPDAT AS DateOnWhichTheChangesWereConfirmed_UPDAT,
  LFA1.UPTIM AS TimeOfLastChangeConfirmation_UPTIM,
  LFA1.NODEL AS CentralDeletionBlockForMasterRecord_NODEL,
  LFA1.QSSYSDAT AS ValidityDateOfCertification_QSSYSDAT,
  LFA1.PODKZB AS VendorIndicatorRelevantForProofOfDelivery_PODKZB,
  LFA1.FISKU AS AccountNumberOfMasterRecordOfTaxOfficeResponsible_FISKU,
  LFA1.STENR AS TaxNumberAtResponsibleTaxAuthority_STENR,
  LFA1.CARRIER_CONF AS CarrierConfirmationIsExpected_CARRIER_CONF,
  LFA1.MIN_COMP AS MicroCompanyIndicator_MIN_COMP, LFA1.TERM_LI AS TermsOfLiability_TERM_LI,
  LFA1.CRC_NUM AS CrcNumber_CRC_NUM,
  LFA1.CVP_XBLCK AS BusinessPurposeCompletedFlag_CVP_XBLCK, LFA1.RG AS RgNumber_RG,
  LFA1.EXP AS IssuedBy_EXP, LFA1.UF AS State_UF, LFA1.RGDATE AS RgIssuingDate_RGDATE,
  LFA1.RIC AS RicNumber_RIC,
  LFA1.RNE AS ForeignNationalRegistration_RNE, LFA1.RNEDATE AS RneIssuingDate_RNEDATE,
  LFA1.CNAE AS Cnae_CNAE, LFA1.LEGALNAT AS LegalNature_LEGALNAT, LFA1.CRTN AS CrtNumber_CRTN,
  LFA1.ICMSTAXPAY AS IcmsTaxpayer_ICMSTAXPAY, LFA1.INDTYP AS IndustryMainType_INDTYP,
  LFA1.TDT AS TaxDeclarationType_TDT, LFA1.COMSIZE AS CompanySize_COMSIZE, LFA1.DECREGPC AS DeclarationRegimenForPiscofins_DECREGPC,
  LFA1.J_SC_CAPITAL AS CapitalAmount_J_SC_CAPITAL, LFA1.J_SC_CURRENCY AS Currency_J_SC_CURRENCY, LFA1.ALC AS AgencyLocationCode_ALC, LFA1.PMT_OFFICE AS PaymentOffice_PMT_OFFICE,
  LFA1.PPA_RELEVANT AS VendorIsPpaRelevant_PPA_RELEVANT, LFA1.PSOFG AS ProcessorGroup_PSOFG, LFA1.PSOIS AS SubledgerAcctPreprocessingProcedure_PSOIS, LFA1.PSON1 AS Name1_PSON1, LFA1.PSON2 AS Name2_PSON2,
  LFA1.PSON3 AS Name3_PSON3, LFA1.PSOVN AS FirstName_PSOVN, LFA1.PSOTL AS Title_PSOTL,
  LFA1.TRANSPORT_CHAIN AS TransportationChain_TRANSPORT_CHAIN, LFA1.STAGING_TIME AS StagingTimeInDays_STAGING_TIME, LFA1.SCHEDULING_TYPE AS SchedulingProcedure_SCHEDULING_TYPE,
  LFA1.SUBMI_RELEVANT AS CrossDocking_RelevantForCollectiveNumbering_SUBMI_RELEVANT,
  ADRC.DATE_FROM AS ValidFromDate_DATE_FROM, ADRC.NATION AS VersionIdForInternationalAddresses_NATION,
  ADRC.DATE_TO AS ValidToDate_DATE_TO, ADRC.TITLE AS FormOfAddressKey_TITLE, ADRC.NAME1 AS Addr_NAME1, ADRC.NAME2 AS Addr_NAME2, ADRC.NAME3 AS Addr_NAME3,
  ADRC.NAME4 AS Addr_NAME4, ADRC.CITY1 AS Addr_CITY1,
  ADRC.CITY2 AS Addr_District_CITY2,
  ADRC.CITY_CODE AS CityCode_CITY_CODE, ADRC.CITYP_CODE AS DistrictCode_CITYP_CODE,
  ADRC.HOME_CITY AS City_HOME_CITY,
  ADRC.CITYH_CODE AS DifferentCityForCitystreetFile_CITYH_CODE,
  ADRC.REGIOGROUP AS RegionalStructureGrouping_REGIOGROUP,
  ADRC.POST_CODE1 AS CityPostalCode_POST_CODE1,
  ADRC.POST_CODE2 AS PoBoxPostalCode_POST_CODE2,
  ADRC.POST_CODE3 AS CompanyPostalCode_POST_CODE3,
  ADRC.PO_BOX AS PoBox_PO_BOX,
  ADRC.DONT_USE_P AS PoBoxAddressUndeliverableFlag_DONT_USE_P,
  ADRC.PO_BOX_NUM AS Flag_PoBoxWithoutNumber_PO_BOX_NUM,
  ADRC.PO_BOX_LOC AS PoBoxCity_PO_BOX_LOC,
  ADRC.CITY_CODE2 AS CityPoBoxCode__cityFile___CITY_CODE2,
  ADRC.PO_BOX_REG AS RegionForPoBox_PO_BOX_REG,
  ADRC.PO_BOX_CTY AS PoBoxCountry_PO_BOX_CTY,
  ADRC.TRANSPZONE AS TransportationZoneToOrFromWhichTheGoodsAreDelivered_TRANSPZONE,
  ADRC.STREET AS Street_STREET, ADRC.DONT_USE_S AS StreetAddressUndeliverableFlag_DONT_USE_S,
  ADRC.STREETCODE AS StreetNumberForCitystreetFile_STREETCODE,
  ADRC.HOUSE_NUM1 AS HouseNumber_HOUSE_NUM1,
  ADRC.HOUSE_NUM2 AS HouseNumberSupplement_HOUSE_NUM2,
  ADRC.STR_SUPPL1 AS Street2_STR_SUPPL1, ADRC.STR_SUPPL2 AS Street3_STR_SUPPL2,
  ADRC.STR_SUPPL3 AS Street4_STR_SUPPL3, ADRC.LOCATION AS Street5_LOCATION,
  ADRC.BUILDING AS Building__numberOrCode___BUILDING,
  ADRC.FLOOR AS FloorInBuilding_FLOOR, ADRC.ROOMNUMBER AS RoomOrAppartmentNumber_ROOMNUMBER,
  ADRC.COUNTRY AS Country_COUNTRY, ADRC.LANGU AS Language_LANGU,
  ADRC.REGION AS Region_Addr_REGION,
  ADRC.ADDR_GROUP AS AddressGroup__key_businessAddressServices___ADDR_GROUP,
  ADRC.FLAGGROUPS AS Flag_ThereAreMoreAddressGroupAssignments_FLAGGROUPS,
  ADRC.PERS_ADDR AS Flag_ThisIsAPersonalAddress_PERS_ADDR,
  ADRC.SORT1 AS SearchTerm1_SORT1, ADRC.SORT2 AS SearchTerm2_SORT2,
  ADRC.DEFLT_COMM AS CommunicationMethod__key_businessAddressServices_DEFLT_COMM,
  ADRC.TEL_NUMBER AS FirstTelephoneNo_DiallingCodenumber_TEL_NUMBER,
  ADRC.TEL_EXTENS AS FirstTelephoneNo_Extension_TEL_EXTENS,
  ADRC.FAX_NUMBER AS FirstFaxNo_DiallingCodenumber_FAX_NUMBER,
  ADRC.FAX_EXTENS AS FirstFaxNo_Extension_FAX_EXTENS,
  ADRC.COUNTY_CODE AS CountyCodeForCounty_COUNTY_CODE,
  ADRC.COUNTY AS County_Addr_COUNTY, ADRC.TOWNSHIP_CODE AS TownshipCodeForTownship_TOWNSHIP_CODE,
  ADRC.TOWNSHIP AS Township_TOWNSHIP,
  ADRC.MC_COUNTY AS CountyNameInUpperCaseForSearchHelp_MC_COUNTY,
  ADRC.MC_TOWNSHIP AS TownshipNameInUpperCaseForSearchHelp_MC_TOWNSHIP, ADRC.XPCPT AS BusinessPurposeCompletedFlag_XPCPT
FROM `{{ project_id_src }}.{{ dataset_cdc_processed_s4 }}.lfa1` AS LFA1
INNER JOIN `{{ project_id_src }}.{{ dataset_cdc_processed_s4 }}.adrc` AS ADRC
  ON LFA1.ADRNR = ADRC.addrnumber
    AND LFA1.mandt = ADRC.client