CustomersMD
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - CustomersMD
Overview
This view provides a comprehensive reporting dataset for SAP systems, specifically tailored for customer-related information. It combines data from the KNA1 and ADRC tables to offer a holistic view of customer details, addresses, and other relevant attributes.
Data Sources
| Source Table/View | Description |
|---|---|
| KNA1 | Customer Master Table |
| ADRC | Address Table |
Key Use Cases
- Customer Relationship Management (CRM)
- Sales and Distribution (SD)
- Financial Accounting (FI)
- Controlling (CO)
- Materials Management (MM)
Query Logic
The view joins the KNA1 and ADRC tables using the ADRNR field, ensuring that customer data is linked to their respective addresses. The join condition also filters out any inactive or outdated addresses by selecting records where the DATE_TO field in ADRC is set to '9999-12-31'. The resulting dataset provides a consolidated view of current and relevant customer information.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| XD01 | Create Customer |
| XD02 | Change Customer |
| XD03 | Display Customer |
| FBL5N | Customer Line Items |
| VA01 | Create Sales Order |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client_MANDT | Client number | |
| CustomerNumber_KUNNR | Customer number | |
| CountryKey_LAND1 | Country key | |
| Name1_NAME1 | Name 1 | |
| Name2_NAME2 | Name 2 | |
| City_ORT01 | City | |
| PostalCode_PSTLZ | Postal code | |
| CustomerRegion_REGIO | Customer region | |
| SortField_SORTL | Sort field | |
| StreetAndNumber_STRAS | Street and number | |
| FirstTelephoneNumber_TELF1 | First telephone number | |
| FaxNumber_TELFX | Fax number | |
| OneTimeAccount_XCPDK | One-time account | |
| Address_ADRNR | Address number | |
| MatchcodeSearch_MCOD1 | Matchcode search 1 | |
| MatchcodeSearch_MCOD2 | Matchcode search 2 | |
| MatchcodeSearch_MCOD3 | Matchcode search 3 | |
| Title_ANRED | Title | |
| CentralOrderBlockForCustomer_AUFSD | Central order block for customer | |
| ExpressTrainStation_BAHNE | Express train station | |
| TrainStation_BAHNS | Train station | |
| InternationalLocationNumber_BBBNR | International location number | |
| InternationalLocationNumber_BBSNR | International location number | |
| AuthorizationGroup_BEGRU | Authorization group | |
| IndustryKey_BRSCH | Industry key | |
| CheckDigitForTheInternationalLocationNumber_BUBKZ | Check digit for the international location number | |
| DataCommunicationLineNo_DATLT | Data communication line no | |
| DateCreated_ERDAT | Date created | |
| CreatedBy_ERNAM | Created by | |
| UnloadingPointsExist_EXABL | Unloading points exist | |
| CentralBillingBlock_FAKSD | Central billing block | |
| AccountNumberFiscalAddress_FISKN | Account number fiscal address | |
| WorkingTimeCalendar_KNAZK | Working time calendar | |
| AlternativePayer_KNRZA | Alternative payer | |
| GroupKey_KONZS | Group key | |
| CustomerAccountGroup_KTOKD | Customer account group | |
| CustomerClassification_KUKLA | Customer classification | |
| AccountNumberOfVendorOrCreditor_LIFNR | Account number of vendor or creditor | |
| CentralDeliveryBlockForTheCustomer_LIFSD | Central delivery block for the customer | |
| CityCoordinates_LOCCO | City coordinates | |
| CentralDeletionFlagForMasterRecord_LOEVM | Central deletion flag for master record | |
| Name3_NAME3 | Name 3 | |
| Name4_NAME4 | Name 4 | |
| NielsenId_NIELS | Nielsen ID | |
| District_ORT02 | District | |
| PoBox_PFACH | PO box | |
| POBoxPostalCode_PSTL2 | PO box postal code | |
| CountyCode_COUNC | County code | |
| CityCode_CITYC | City code | |
| RegionalMarket_RPMKR | Regional market | |
| CentralPostingBlock_SPERR | Central posting block | |
| LanguageKey_SPRAS | Language key | |
| TaxNumber1_STCD1 | Tax number 1 | |
| TaxNumber2_STCD2 | Tax number 2 | |
| SubjectToEqualizationTax_STKZA | Subject to equalization tax | |
| LiableForVat_STKZU | Liable for VAT | |
| TeleboxNumber_TELBX | Telebox number | |
| SecondTelephoneNumber_TELF2 | Second telephone number | |
| TeletexNumber_TELTX | Teletex number | |
| TelexNumber_TELX1 | Telex number | |
| TransportationZone_LZONE | Transportation zone | |
| AlternativePayerAllowed_XZEMP | Alternative payer allowed | |
| CompanyIdOfTradingPartner_VBUND | Company ID of trading partner | |
| VatRegistrationNumber_STCEG | VAT registration number | |
| Competitor_DEAR1 | Competitor | |
| SalesPartner_DEAR2 | Sales partner | |
| SalesProspect_DEAR3 | Sales prospect | |
| CustomerType4_DEAR4 | Customer type 4 | |
| IdForDefaultSoldToParty_DEAR5 | ID for default sold-to party | |
| LegalStatus_GFORM | Legal status | |
| IndustryCode1_BRAN1 | Industry code 1 | |
| IndustryCode2_BRAN2 | Industry code 2 | |
| IndustryCode3_BRAN3 | Industry code 3 | |
| IndustryCode4_BRAN4 | Industry code 4 | |
| IndustryCode5_BRAN5 | Industry code 5 | |
| InitialContact_EKONT | Initial contact | |
| AnnualSales_UMSAT | Annual sales | |
| YearForWhichSalesAreGiven_UMJAH | Year for which sales are given | |
| CurrencyOfSalesFigure_UWAER | Currency of sales figure | |
| YearlyNumberOfEmployees_JMZAH | Yearly number of employees | |
| YearForWhichTheNumberOfEmployeesIsGiven_JMJAH | Year for which the number of employees is given | |
| Attribute1_KATR1 | Attribute 1 | |
| Attribute2_KATR2 | Attribute 2 | |
| Attribute3_KATR3 | Attribute 3 | |
| Attribute4_KATR4 | Attribute 4 | |
| Attribute5_KATR5 | Attribute 5 | |
| Attribute6_KATR6 | Attribute 6 | |
| Attribute7_KATR7 | Attribute 7 | |
| Attribute8_KATR8 | Attribute 8 | |
| Attribute9_KATR9 | Attribute 9 | |
| Attribute10_KATR10 | Attribute 10 | |
| NaturalPerson_STKZN | Natural person | |
| AnnualSales_UMSA1 | Annual sales | |
| TaxJurisdiction_TXJCD | Tax jurisdiction | |
| FiscalYearVariant_PERIV | Fiscal year variant | |
| UsageIndicator_ABRVW | Usage indicator | |
| InspectionCarriedOutByCustomer_INSPBYDEBI | Inspection carried out by customer | |
| InspectionForADeliveryNote_INSPATDEBI | Inspection for a delivery note | |
| ReferenceAccountGroup_KTOCD | Reference account group | |
| PoBoxCity_PFORT | PO box city | |
| Plant_WERKS | Plant | |
| ReportKeyForDataMediumExchange_DTAMS | Report key for data medium exchange | |
| InstructionKeyForDataMediumExchange_DTAWS | Instruction key for data medium exchange | |
| StatusOfDataTransferIntoSubsequentRelease_DUEFL | Status of data transfer into subsequent release | |
| AssignmentToHierarchy_HZUOR | Assignment to hierarchy | |
| PaymentBlock_SPERZ | Payment block | |
| RLabeling_CustomerplantGroup_ETIKG | R labeling - customer plant group | |
| IdNonMilitaryUse_CIVVE | ID non-military use | |
| IdForMilitaryUse_MILVE | ID for military use | |
| ConditionGroup1_KDKG1 | Condition group 1 | |
| ConditionGroup2_KDKG2 | Condition group 2 | |
| ConditionGroup3_KDKG3 | Condition group 3 | |
| ConditionGroup4_KDKG4 | Condition group 4 | |
| ConditionGroup5_KDKG5 | Condition group 5 | |
| AlternativePayerUsingAccountNumber_XKNZA | Alternative payer using account number | |
| TaxType_FITYP | Tax type | |
| TaxNumberType_STCDT | Tax number type | |
| TaxNumber3_STCD3 | Tax number 3 | |
| TaxNumber4_STCD4 | Tax number 4 | |
| TaxNumber5_STCD5 | Tax number 5 | |
| CustomerIsIcmsExempt_XICMS | Customer is ICMS exempt | |
| CustomerIsIpiExempt_XXIPI | Customer is IPI exempt | |
| CustomerGroupForSubstituicaoTributariaCalculation_XSUBT | Customer group for substituicao tributaria calculation | |
| CustomerCfopCategory_CFOPC | Customer CFOP category | |
| TaxLaw_Icms_TXLW1 | Tax law - ICMS | |
| TaxLaw_Ipi_TXLW2 | Tax law - IPI | |
| IndicatorForBiochemicalWarfare_CCC01 | Indicator for biochemical warfare | |
| IndicatorForNuclearNonproliferation_CCC02 | Indicator for nuclear nonproliferation | |
| IndicatorForNationalSecurity_CCC03 | Indicator for national security | |
| IndicatorForMissileTechnology_CCC04 | Indicator for missile technology | |
| CentralSalesBlock_CASSD | Central sales block | |
| UniformResourceLocator_KNURL | Uniform resource locator | |
| NameOfRepresentative_J_1KFREPRE | Name of representative | |
| TypeOfBusiness_J_1KFTBUS | Type of business | |
| TypeOfIndustry_J_1KFTIND | Type of industry | |
| StatusOfChangeAuthorization_CONFS | Status of change authorization | |
| DateOnWhichTheChangesWereConfirmed_UPDAT | Date on which the changes were confirmed | |
| TimeOfLastChangeConfirmation_UPTIM | Time of last change confirmation | |
| CentralDeletionBlockForMasterRecord_NODEL | Central deletion block for master record | |
| Indicator_Consumer_DEAR6 | Indicator - consumer | |
| BusinessPurposeCompletedFlag_CVP_XBLCK | Business purpose completed flag | |
| SuframaCode_SUFRAMA | Suframa code | |
| RgNumber_RG | RG number | |
| IssuedBy_EXP | Issued by | |
| State_UF | State | |
| RgIssuingDate_RGDATE | RG issuing date | |
| RicNumber_RIC | RIC number | |
| ForeignNationalRegistration_RNE | Foreign national registration | |
| RneIssuingDate_RNEDATE | RNE issuing date | |
| Cnae_CNAE | CNAE | |
| LegalNature_LEGALNAT | Legal nature | |
| CrtNumber_CRTN | CRT number | |
| IcmsTaxpayer_ICMSTAXPAY | ICMS taxpayer | |
| IndustryMainType_INDTYP | Industry main type | |
| TaxDeclarationType_TDT | Tax declaration type | |
| CompanySize_COMSIZE | Company size | |
| DeclarationRegimenForPiscofins_DECREGPC | Declaration regimen for piscofins | |
| AgencyLocationCode_ALC | Agency location code | |
| PaymentOffice_PMT_OFFICE | Payment office | |
| FeeSchedule_FEE_SCHEDULE | Fee schedule | |
| DunsNumber_DUNS | DUNS number | |
| Duns4_DUNS4 | DUNS4 | |
| ProcessorGroup_PSOFG | Processor group | |
| SubledgerAcctPreprocessingProcedure_PSOIS | Subledger acct preprocessing procedure | |
| Name1_PSON1 | Name 1 | |
| Name2_PSON2 | Name 2 | |
| Name3_PSON3 | Name 3 | |
| FirstName_PSOVN | First name | |
| Title_PSOTL | Title | |
| Description_PSOO1 | Description 1 | |
| Description_PSOO2 | Description 2 | |
| Description_PSOO3 | Description 3 | |
| Description_PSOO4 | Description 4 | |
| Description_PSOO5 | Description 5 | |
| ValidFromDate_DATE_FROM | Valid from date | |
| VersionIdForInternationalAddresses_NATION | Version ID for international addresses | |
| ValidToDate_DATE_TO | Valid to date | |
| FormOfAddressKey_TITLE | Form of address key | |
| Addr_NAME1 | Address name 1 | |
| Addr_NAME2 | Address name 2 | |
| Addr_NAME3 | Address name 3 | |
| Addr_NAME4 | Address name 4 | |
| City_CITY1 | City 1 | |
| District_CITY2 | District 2 | |
| CityCodeForCitystreetFile_CITY_CODE | City code for citystreet file | |
| DistrictCodeForCityAndStreetFile_CITYP_CODE | District code for city and street file | |
| City_HOME_CITY | City home city | |
| DifferentCityForCitystreetFile_CITYH_CODE | Different city for citystreet file | |
| RegionalStructureGrouping_REGIOGROUP | Regional structure grouping | |
| CityPostalCode_POST_CODE1 | City postal code 1 | |
| PoBoxPostalCode_POST_CODE2 | PO box postal code 2 | |
| CompanyPostalCode_POST_CODE3 | Company postal code 3 | |
| PoBox_PO_BOX | PO box | |
| PoBoxAddressUndeliverableFlag_DONT_USE_P | PO box address undeliverable flag | |
| Flag_PoBoxWithoutNumber_PO_BOX_NUM | Flag - PO box without number | |
| PoBoxCity_PO_BOX_LOC | PO box city | |
| CityPoBoxCode_CityFile_CITY_CODE2 | City PO box code - city file | |
| RegionForPoBox_PO_BOX_REG | Region for PO box | |
| PoBoxCountry_PO_BOX_CTY | PO box country | |
| TransportationZoneToOrFromWhichTheGoodsAreDelivered_TRANSPZONE | Transportation zone to or from which the goods are delivered | |
| Street_STREET | Street | |
| StreetAddressUndeliverableFlag_DONT_USE_S | Street address undeliverable flag | |
| StreetNumberForCitystreetFile_STREETCODE | Street number for citystreet file | |
| HouseNumber_HOUSE_NUM1 | House number 1 | |
| HouseNumberSupplement_HOUSE_NUM2 | House number supplement | |
| Street2_STR_SUPPL1 | Street 2 | |
| Street3_STR_SUPPL2 | Street 3 | |
| Street4_STR_SUPPL3 | Street 4 | |
| Street5_LOCATION | Street 5 | |
| Building_NumberOrCode_BUILDING | Building - number or code | |
| FloorInBuilding_FLOOR | Floor in building | |
| RoomOrAppartmentNumber_ROOMNUMBER | Room or appartment number | |
| CountryKey_COUNTRY | Country key | |
| Language_LANGU | Language | |
| Region__REGION | Region | |
| AddressGroup_Key_BusinessAddressServices_ADDR_GROUP | Address group - key - business address services | |
| Flag_ThereAreMoreAddressGroupAssignments_FLAGGROUPS | Flag - there are more address group assignments | |
| Flag_ThisIsAPersonalAddress_PERS_ADDR | Flag - this is a personal address | |
| SearchTerm1_SORT1 | Search term 1 | |
| SearchTerm2_SORT2 | Search term 2 | |
| FirstTelephoneNo_DiallingCodenumber_TEL_NUMBER | First telephone no - dialling codenumber | |
| FirstTelephoneNo_Extension_TEL_EXTENS | First telephone no - extension | |
| CountyCodeForCounty_COUNTY_CODE | County code for county | |
| County_COUNTY | County | |
| TownshipCodeForTownship_TOWNSHIP_CODE | Township code for township | |
| Township_TOWNSHIP | Township | |
| CountyNameInUpperCaseForSearchHelp_MC_COUNTY | County name in upper case for search help | |
| TownshipNameInUpperCaseForSearchHelp_MC_TOWNSHIP | Township name in upper case for search help | |
| BusinessPurposeCompletedFlag_XPCPT | Business purpose completed flag |