AddressesMD
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - AddressesMD
Overview
This view is a reporting view for SAP systems that provides a comprehensive view of address data. It combines data from multiple tables to provide a single source of truth for address information.
Data Sources
| Source Table/View | Description |
|---|---|
| ADRC | Address Header Data |
| ADRCT | Address Text |
| ADR6 | Communication Data for Address |
| ADRT | Communication Data for Address |
Key Use Cases
- Can be used to retrieve address information for a specific customer or business partner.
- Can be used to generate mailing labels or other correspondence.
- Can be used to analyze address data to identify trends or patterns.
Query Logic
The view uses a combination of inner joins and left outer joins to combine data from the ADRC, ADRCT, ADR6, and ADRT tables. The view filters the data to only include addresses that have a date_to value of '9999-12-31', which indicates that the address is still valid.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| XD01 | Create Customer |
| XD02 | Change Customer |
| XD03 | Display Customer |
| BP | Business Partner |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| Client | Client number | |
| AddressNumber_ADDRNUMBER | Address number | |
| ValidFromDATE_FROM | Valid from date | |
| VersionIdForInternationalAddresses_NATION | Version ID for international addresses | |
| ValidToDate_DATE_TO | Valid to date | |
| FormOfAddressKey_TITLE | Form of address key | |
| Name1_NAME1 | Name 1 | |
| Name2_NAME2 | Name 2 | |
| Name3_NAME3 | Name 3 | |
| Name4_NAME4 | Name 4 | |
| ConvertedNameField__withFormOfAddress___NAME_TEXT | Converted name field with form of address | |
| CoName_NAME_CO | Company name | |
| City_CITY1 | City | |
| District_CITY2 | District | |
| CityCodeForCitystreetFile_CITY_CODE | City code for city street file | |
| DistrictCodeForCityAndStreetFile_CITYP_CODE | District code for city and street file | |
| City__differentFromPostalCity___HOME_CITY | City different from postal city | |
| DifferentCityForCitystreetFile_CITYH_CODE | Different city for city street file | |
| CityFileTestStatus_CHCKSTATUS | City file test status | |
| RegionalStructureGrouping_REGIOGROUP | Regional structure grouping | |
| CityPostalCode_POST_CODE1 | City postal code | |
| PoBoxPostalCode_POST_CODE2 | PO box postal code | |
| CompanyPostalCode__forLargeCustomers___POST_CODE3 | Company postal code for large customers | |
| cityPostalCodeExtension_PCODE1_EXT | City postal code extension | |
| PoBoxPostalCodeExtension_PCODE2_EXT | PO box postal code extension | |
| 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 city street file | |
| HouseNumber_HOUSE_NUM1 | House number | |
| 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_businessAddressServicesADDR_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 | |
| CommunicationMethod__key_businessAddressServicesDEFLT_COMM | Communication method (key) (business address services) | |
| FirstTelephone_TEL_NUMBER | First telephone | |
| FirstExtension_TEL_EXTENS | First extension | |
| FirstFaxNor_FAX_NUMBER | First fax nor | |
| FirstFax_FAX_EXTENS | First fax | |
| Flag_TelephoneNumber__s__Defined_FLAGCOMM2 | Flag: Telephone number(s) defined | |
| Flag_FaxNumber__s__Defined_FLAGCOMM3 | Flag: Fax number(s) defined | |
| Flag_TeletexNumber__s__Defined_FLAGCOMM4 | Flag: Teletex number(s) defined | |
| Flag_TelexNumber__s__Defined_FLAGCOMM5 | Flag: Telex number(s) defined | |
| EMailAddressX_FLAGCOMM6 | EMail address X | |
| Flag_Rml__remoteMail__Addresse__s__Defined_FLAGCOMM7 | Flag: Rml (remote mail) addresse(s) defined | |
| Flag_X400FLAGCOMM8 | Flag: X400FLAGCOMM8 | |
| Flag_RfcDestination__s__Defined_FLAGCOMM9 | Flag: Rfc destination(s) defined | |
| Flag_PrinterDefined_FLAGCOMM10 | Flag: Printer defined | |
| Flag_SsfDefined_FLAGCOMM11 | Flag: Ssf defined | |
| Flag_UriftpAddressDefined_FLAGCOMM12 | Flag: Uriftp address defined | |
| Flag_PagerAddressDefined_FLAGCOMM13 | Flag: Pager address defined | |
| Name__fieldName1__InUppercaseForSearchHelp_MC_NAME1 | Name (field name 1) in uppercase for search help | |
| CityNameInUppercaseForSearchHelp_MC_CITY1 | City name in uppercase for search help | |
| StreetNameInUppercaseForSearchHelp_MC_STREET | Street name in uppercase for search help | |
| AddressTimeZone_TIME_ZONE | Address time zone | |
| TaxJurisdiction_TAXJURCODE | Tax jurisdiction | |
| AddressRecordCreationOriginalLanguage_LANGU_CREA | Address record creation original language | |
| UuidUsedInTheAddress_ADRC_UUID | Uuid used in the address | |
| Indicator_UuidCreatedLater_UUID_BELATED | Indicator: Uuid created later | |
| CategoryOfAnAddressId_ID_CATEGORY | Category of an address ID | |
| ErrorStatusOfAddress_ADRC_ERR_STATUS | Error status of address | |
| PoBoxLobby_PO_BOX_LOBBY | PO box lobby | |
| TypeOfDeliveryService_DELI_SERV_TYPE | Type of delivery service | |
| NumberOfDeliveryService_DELI_SERV_NUMBER | Number of delivery service | |
| 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 | |
| ValidFrom_DATE_FROM | Valid from | |
| VersionInternationalAddresses_NATION | Version international addresses | |
| AddressNotes_REMARK | Address notes | |
| PersonNumber_PERSNUMBER | Person number | |
| CommunicationMethod__key_businessAddressServicesCOMM_TYPE | Communication method (key) (business address services) | |
| ValidFromDate_DATE_FROM | Valid from date | |
| SequenceNumber_CONSNUMBER | Sequence number | |
| CommunicationLinkNotes_REMARK | Communication link notes | |
| Flag_ThisAddressIsTheDefaultAddress_FLGDEFAULT | Flag: This address is the default address | |
| Flag_ThisCommunicationNumberIsNotUsed_FLG_NOUSE | Flag: This communication number is not used | |
| RecipientAddressInThisCommunicationType__mailSys_HOME_FLAG | Recipient address in this communication type (mail sys) | |
| EMailAddress_SMTP_ADDR | EMail address | |
| EMailAddressSearchField_SMTP_SRCH | EMail address search field | |
| Flag_RecipientIsStandardRecipientForThisAddress_DFT_RECEIV | Flag: Recipient is standard recipient for this address | |
| Flag_ConnectedToAnSapSystem_R3_USER | Flag: Connected to an SAP system | |
| RequiredDataEncoding__eMail___ENCODE | Required data encoding (eMail) | |
| Flag_ReceiverCanReceiveTnefEncodingBySmtp_TNEF | Flag: Receiver can receive Tnef encoding by Smtp | |
| CommunicationData_ValidFrom__yyyymmddhhmmss___VALID_FROM | Communication data valid from (yyyymmddhhmmss) | |
| CommunicationData_ValidTo__yyyymmddhhmmss___VALID_TO | Communication data valid to (yyyymmddhhmmss) |