CMD Modernization Data Model
In the CMD application data model, we have
- 15 base tables for Customer
- 6 base tables for Contact
- 5 base tables for Facility
- 21 Reference tables to maintain the Reference data.
- We also have 11 XREF tables to be used during the approval process.
Table of Contents
- 1. Schema Details:
- 2. MDM_SMDS Tables
- 3. Additional Supporting tables in MDM_SMDSMD schema:
- 4. E-R Diagram:
- 5. Data Dictionary:
- 6. DDL scripts:
- 7. Sequences:
1. Schema Details:
We have created two Schema
| Sl. no | Table Name | Description |
|---|---|---|
| 1 | MDM_SMDS | MDM_SMDS schema is used in all the customer core tables, contact core tables, customer facility core tables and reference data tables. |
| 2 | MDM_SMDSMD | MDM_SMDSMD schema is used to create all config tables, sequences etc for additional support. as mentioned in section #5 Contains all other tables which are used to support the other functionalities in API/UI/Data migration. |
2. MDM_SMDS Tables
1. Customer Core tables:
We have created 15 customer base tables. All the customer core tables are created in the MDM_SMDS schema.
Customer base tables:
These base tables are the core customer information tables where the actual master data of the customer will be stored. The purpose of each table is detailed in the table below.
| Sl. No | Table Name | Description |
|---|---|---|
| 1 | ADDRESS_CODES | Used to stored the address codes FACT and SCV codes for Customer and concern address. |
| 2 | CBU_HISTORY_DETAILS | This is used to main audit history of each and every update on CBU data. Data is stored here in JSON format. |
| 3 | CUSTOMER_BRANDS | This table contains information about the customer brand details. Customer can be associated with multiple brands. |
| 4 | CUSTOMER_BVD_INFORMATION | This is used to store the information enriched from BVD source. This will be updated with active integration process as well as passive integration process. |
| 5 | CUSTOMER_CBU_DETAIL | This table contains details of the relationship between Customer and collection business units. |
| 6 | CUSTOMER_EXTERNAL_IDENTIFIERS | This table contains the external application identifier details about the customer. Example: FCT, SVC, BE, BPMA etc.. |
| 7 | CUSTOMER_HISTORY_DETAILS | This is used to main audit history of each and every update on customer data. Data is stored here in JSON format. |
| 8 | CUSTOMER_INFORMATION | This table contains core information about customers along with the customer address. This table is also used to persist the concern details as well. |
| 9 | CUSTOMER_RELATIONSHIPS | It describes the relationship between customers by using the attributes parent_customer_code and child_customer_code. It also persist the relationship between customer and concern. |
| 10 | CUSTOMER_SEGMENT_DETAIL | Details about the customer segment types and segment values. |
| 11 | CUSTOMER_SIC_INFORMATION | This is used to store the information enriched from BVD source related Standard Industry Codes. This will be updated with passive integration process. |
| 12 | CUSTOMER_STATUS_REASONS | This table is used to persist the the customer suspended reasons. A customer can be suspended with multiple reasons, and all these reasons are stored in this table. |
| 13 | CUSTOMER_TAX_REF_INFO | This table contains information about the tax number and alternate references about customers. |
| 14 | CUSTOMER_WEB_BL_DETAIL | Details about the customer webbill information. |
| 15 | CUSTOMER_WF_PROCESS_DETAILS | This is used to maintain the customer information when the customer go for approval process. The customer updated or snapshot information will be stored in this table and once customer is approved the data will be updated into core tables. If its rejected, then the record will be removed from this table. This also will be stored in JSON format. |
2. Contact Core tables:
We have created 6 contact core tables. All the contact core tables are created in MDM_SMDS schema.
| Sl .No | Table Name | Description |
|---|---|---|
| 1 | CONTACT_INFORMATION | This table consists of information of the contact along with the email-address and mobile number. |
| 2 | CONTACT_CUSTOMER_RELATIONSHIP | This table describes the relationship between the customer and the contact. |
| 3 | CONTACT_CLASSIFICATION | This table classifies the contact and includes different classification codes. Classification like Contact Types and Contact brands. |
| 4 | CONTACT_EXTERNAL_IDENTIFIERS | This table contains the external application identifier details about the contact. Example: FCT, SVC, BPMA etc.. |
| 5 | CONTACT_DOC_TYPE_AND_COMM_PREF | This is used to store the contact communication preference and communication document type along with customer and brand information to which it belongs to. A contact can have multiple document type and communication type preferences. |
| 6 | CONTACT_HISTORY_DETAILS | This is used to maintain audit history of each and every update on contact data. Data is stored here in JSON format. |
3. Customer Facility Core tables:
Below are 5 core customer facility tables created to store the customer facility information are stored in MDM_SMDS schema.
| Sl. No | Table Name | Description |
|---|---|---|
| 1 | FACILITY_INFORMATION | Used to store basic customer facility information. |
| 2 | FACILITY_EXTERNAL_IDENTIFIERS | Used to store external identifiers of customer facility. |
| 3 | FACILITY_ADDRESS | Customer Facility actual address along with lat and long and also address accuracy level. |
| 4 | FACILITY_CUSTOMER_REL | Relationship between Facility and Customer is stored here. |
| 5 | FACILITY_HISTORY_DETAILS | This is used to main audit history of each and every update on contact data. Data is stored here in JSON format. |
4. Reference tables:
We have created 25 reference tables. All the reference tables are created in MDM_SMDS schema. These reference tables will be used to maintain the predefined reference data in the application. Any update, new reference data will be directly updated/created in the respective tables if there are any.
| Sl.No | Table Name | Description |
|---|---|---|
| 1 | ref_alternate_codes | Used to to store the alternate codes along with the system names for the customers, contact and customer facility. |
| 2 | ref_brands | This table provides information about the brands CMD maintains. There are around 5 different brands and 19 M&As available currently. |
| 3 | ref_bvd_tax_code_map | It contains BVD country tax code mappings with the CMD country tax code mappings, |
| 4 | ref_collection_business_units | It refers to the collection business units of each country and brands. |
| 5 | ref_communication_pref_types | It contains the details about the mode of communication preferred by different countries. |
| 6 | ref_contact_class_type | Contact class type which is Contact Type or Contact brands |
| 7 | ref_contact_primary_salutation | It refers to the primary salutation of contact. |
| 8 | ref_country | Used to track all countries |
| 9 | ref_customer_group_type | This table refers to the customer group type (SCPI or SCPI_TAX_EXEMPTED) |
| 10 | ref_customer_reference_types | It contains reference details of the customers (REF_NUM or TAX_NUM) |
| 11 | ref_customer_status | Customer status (Active, Suspended, Inactive) |
| 12 | ref_customer_status_rsn | List of reasons used to suspend|inactive customer. |
| 13 | ref_customer_type | Customer type either ZEXC or ZICC |
| 14 | ref_document_type | Contact document type |
| 15 | ref_international_cont_salutation | It refers to the salutation information of different countries. |
| 16 | ref_international_dialing_prefix | It contains the dialing codes information of countries. |
| 17 | ref_invoice_iso_languages | Invoice language codes for all countries |
| 18 | ref_iso_languages | Language ISO codes |
| 19 | ref_region | Used to track all regions in the countries |
| 20 | ref_relationship_types | All relationship types between customer to customer, customer to contact, concern to customer, customer to CBU, customer to customer facility etc |
| 21 | ref_segmentation_types | All customer segment types |
| 22 | ref_segmentation_value_types | All customer segment values types |
| 23 | ref_source_systems | Used to track all source system names from which the data will be feed to MDM system |
| 24 | ref_tax_number_codes | All customer tax types in CMD application. |
| 25 | ref_tax_type_local_name | CMD tax type to local/standard tax type mapping is stored. |
3. Additional Supporting tables in MDM_SMDSMD schema:
Below are the 63 tables created in MDM_SMDSMD schema.
| Table Name | Usecase Description | |
|---|---|---|
| 1 | adf_file_service_mapping | The table contains all the Azure Data Factory(ADF) related paths and filenames mapping for reference. |
| 2 | bbu_error_history | This table is used to track the error details of a record which got failed because of validations. This table tracks all failed records against a BBU job along with all error details. |
| 3 | bbu_history | This table is used to track each and every BBU job status. What is the template uploaded, how many records in the batch and how many are success and how many are failed and overall status of the job in JSON format. |
| 4 | bbu_template_info | This table is used to store all template-related information as reference which will help in validating the file or template structure. of The number records in this table is equal to number of BBU templates. |
| 5 | bvd_sync_track | This table contains details of the bvd IDs mapped to customer and the result of the bvd passive process. |
| 6 | Temporary tables | These are some temporary tables that don’t have any significance, but are present for intermediate use. They also have temp_ in them for distinguishing them. bvd_wapper_temp_response - its empty currentlytest_customer_history_details - for testing customer historytemp_seq_checktemp_india_pan_cleanup |
| 7 | cmd_application_config | Used to maintain the configurations related to applications/services like cmd_rule_engine, cmd_rkts07_push, cmd_wtsc_listener |
| 8 | cmd_common_validation_rules | Used to maintain the common validation rules for fields like name, address fields etc across entities like customer, contact, facility |
| 9 | cmd_generic_country_rules | Used to maintain the validation rules specific to each country. |
| 10 | concern_segment_sync | We automate the segments for isConcern and member concerns, this table is used to keep track of the sync between the segments sync between is concern and members and if any error occured. |
| 11 | consumer_fclty_traffic | Has facility retrieve information in JSON format. Used to send to consumers. |
| 12 | consumer_sync_track | Used to track the sync between the consumers and errors that occurred for those records across entities like Customer/Contact/Facility. |
| 13 | cont_risk_profile_hist | Used to track the history of contact inactivation and risk score associated with it. |
| 14 | cont_risk_profile_ldg - is emptycont_risk_profile_stg | Used to keep details of all the codes like SCV, BPMA, BE CODE, CMD code associated with contacts and their risk score. |
| 15 | cust_name_keywords | Used to maintain the name key words for customer name. |
| 16 | cust_stat_chng_req_trckr | Used to track the customer status change requests. It also contains the details about request sent and whether it was successful or failed with message. |
| 17 | cust_type_keywords | Used to maintain the name key words for customer name with respect to segment type. |
| 18 | customer_status_reasons_bkp | Backup of customer status reasons ex: Duplicate, Not in use etc |
| 19 | damco_cmd_dup_cust_map | Contains the mapping of Damco BE Codes, FACT, SCV codes with CMD Becode, FACT, SCV codes. |
| 20 | dgo_damco_idl_be_code | Damco - BE Codes from Informatica Data Load(IDL) are kept here. |
| 21 | email_validation_tracker | Used to store the informatica email validation results which is used to set the communication details for contact. |
| 22 | entity_update_history | All entities updates history is kept here in JSON format - ex - CUST/CONT/FACILITY etc. |
| 23 | eu_vat_validn_hist | Used to store the European tax vat validation results. |
| 24 | facility_duplicate_tracker | Used to track the duplicates found during Facility create/updates. |
| 25 | gems_cust_dtls | Used to store the details of customer from GEMS source system. |
| 26 | gen_name_extn | Stores the generic keywords like PVT, LTD, Logistic etc. |
| 27 | generated_be_code | Used to store the generated BE Codes in the system. |
| 28 | generic_test | Some test table related to generic country rules. |
| 29 | geo_region_details | Region data from Geo API with country details are kept here for later use. |
| 30 | pg_elk_sync_trck | Used to to track the sync of Customer/Concern/Contact/Facilities between the elastic search and PG tables, with any error message. |
| 31 | phone_validation_tracker | Used to store the informatica phone number validation results which is used to set the communication details for customer and contact. |
| 32 | producer_concern_traffic | Has Concern retrieve information in JSON format. Used by producer. |
| 33 | producer_contact_traffic | Has Contact retrieve information in JSON format. Used by producer. |
| 34 | producer_customer_traffic | Has Customer retrieve information in JSON format. Used by producer. |
| 35 | producer_fclty_traffic | Has Facility retrieve information in JSON format. Used by producer. |
| 36 | producer_sync_track | Used to to track the sync of Customer/Concern/Contact/Facilities while publishing to EMP, with any error messages. |
| 37 | ref_customer_status_rsn_bkp | Backup of reference data of Customer status reasons |
| 38 | scpi_country_rules | Used to maintain the SCPI validation rules specific to each country. |
| 39 | smds_generic_country_rules | Used to maintain the Normal/Non-SCPI validation rules specific to each country. |
| 40 | Stage tables | These are the stage tables used when doing MNA IDLstg_contact_classification - linked contact brands of all contactstg_contact_customer_relationship - contact relationship with customers of all contactstg_contact_external_identifiers - webbill information of all contactstg_contact_information - webbill information of all contactstg_customer_brands - linked brand details of all customersstg_customer_cbu_detail - linked cbu information of all customersstg_customer_external_identifiers - linked external identifiers like SCV, MODS etc of all customersstg_customer_information - information of all customersstg_customer_relationships - relationships like MASTER_DUP, CONCERN etc of all customersstg_customer_segment_detail - linked segments of all customersstg_customer_tax_ref_info - tax information of all customersstg_customer_web_bl_detail - webbill information of all customers |
| 41 | sync_code_trk_adf | Used to track the sync and publish of the Concern codes. |
| 42 | tamr_customer_data | Contains customer information details |
| 43 | tax_validn_hist | Used to store the tax validation results from external APIs , like for VIES, ABN, NZBN etc. |
| 44 | user_access_config | Used by the CMD portal to manage the role based access control. It contains all the versions of configuration changehanges. |
| 45 | Notification_Tracker | Used to track the notification message and details for applicable entities. Generally workflow notifications of customers. |
| 46 | vndr_user_role_map | Used to store the vendor portal related user’s APM ID roles/access levels. |
Below is the proposed solution to redesign the producer tracker tables.
- Create single table with primary key as row_id and index on row_id. This table will be used to track every publish event for every entity. The success and failure events will be tracked in the same table.
- If data volume is huge to maintain for every entity, then we will separate it by entity.
EMP_Producer_Tracker
Row_id BIGSERIAL NOT NULL, Entity_Type VARCHAR(5) NOT NULL, Entity_Code VARCHAR(50) NOT NULL, Event_Type VARCHAR(50) NULL, processed VARCHAR(1) NOT NULL, Payload text, Create_Time TIMESTAMPTZ NOT NULL, Update_Time TIMESTAMPTZ NOT NULL, Error_Msg VARCHAR(1000)
4. E-R Diagram:
Download the full-clarity image from here.
5. Data Dictionary:
Customer Tables
1. Customer Information
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CUSTOMER_GROUP_TYPE | customerGroupType | This is a FCT Code will be used accros applications to identify the customers. It will be 10 character length, first two characters will identify the country code and next 8 digits are sequence number from database. |
| CUSTOMER_CMD_CODE | N.A | Each customer is assigned an unique customer code MANDATORY IN DB |
| CUSTOMER_ROLE_TYPE | N.A | Stores whether the record is a Concern or a Customer MANDATORY IN DB |
| TRADING_NAME | tradingNameMANDATORY | The trading name is the name of customer by which it does business and is recognized. |
| CUSTOMER_STATUS_CD | customer status.statusCodeMANDATORY | It is I|A |
| LEGAL_NAME | legalName | Legal Name is the enriched name coming from BvD |
| ACCOUNT_GROUP_TYPE | customerType | Indicates whether the customer is internal customer or external. ZEXC for external and ZICC for internal |
| CUSTOMER_WEBSITE | url | URL of the customer |
| INVOICE_LNG_CD | invoiceISOLanguageCodePreference | invoicing language code of the customer - if not choose then defaults to the country specific default iso language. |
| EMAIL_ADDRESS | N.A | |
| BROK | isBrokerage | It is Y|N flag |
| FOFMC | isForwardersCompensation | It is Y|N |
| IS_SOLE_PROPRIETOR | isSoleProprietor | if the customer is sole proprietor |
| VIP_NON_ROLL | vipNonRoll | If the customer is a VIP and related to Rolling protection segment. |
| STREET_NO | customerAddress.streetNumber | Street Number |
| ADDRESS_LINE1 | streetName | Address line1 |
| POBOX | poBoxNumber | Po Box |
| ADDRESS_LINE2 | apartmentFloor | Address line2 - Apartment, Suite, Floor, etc. |
| ADDRESS_LINE3 | subArea / districtName | Address line3 - City sub-area/City District |
| CITY | cityNameMANDATORY | City name |
| ZIP_CODE | postalCode | Pincode |
| STATE_CODE | regionCode | Two character code for regions |
| STATE_NAME | regionName | State name |
| COUNTRY_CODE | isoCountryCode | Two character code for each country |
| COUNTRY_NAME | countryName | Country name |
| LATITUDE | latitude | Latitude Information of the customer address - populates from Azure Maps |
| LONGITUDE | longitude | Longitude Information of the customer address - populates from Azure Maps |
| PHONE_TYPE | communicationNumberType | Type of communication by which we can contact customer (TEL or MOB) |
| ISD_DIALING_CODE | internationalDialingCode | Country dialing code |
| ISD_COUNTRY_CODE | isd_country_code | Country code for phone number |
| EXTENSION_NUMBER | extensionNumber | Extension of phone number |
| PHONE_NUM | number | Customer phone number |
| IS_PENDING | isWorkflowPending | Indicates whether the party is pending (Y|N) from business point of view MANDATORY IN DB |
| IS_DELETED | N.A | Indicates whether the party is deleted(Y|N) from business point of view MANDATORY IN DB |
2. Customer Tax Ref Information
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CUSTOMER_CMD_CODE | MANDATORY | Customer code to which the reference is linked MANDATORY IN DB |
| ROW_ID | N.A | Its the primary Key MANDATORY IN DB |
| REF_CNTRY_CD | isoCountryCode | The country code to which the tax/reference is associated. |
| REF_TYPE | MANDATORY | Is it a reference or a tax indicated by REF_NUM or TAX_NUM |
| REF_TYPE_CD | identifierCode | Its the reference type code used to identify the type of tax or reference. |
| REFERENCE_VALUE | MANDATORY identiferValue | Its the value of the tax or reference. |
| IS_DELETED | isDeletedFlag | Indicates whether the party is deleted (Y|N) from business point of viewMANDATORY IN DB |
| IS_PENDING | N.A | Indicates whether the party is pending(Y|N) from business point of viewMANDATORY IN DB |
3. Customer Relationships
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| ROW_ID | ||
| REL_TYPE_CODE | Hierarchy relationship type code value defined in HM configuration in HUB console as shown in screenshot. | |
| PARENT_CUSTOMER_CODE | ||
| CHILD_CUSTOMER_CODE | ||
| VALID_FROM_DATE | From this date the relationship between parent and child is valid | |
| VALID_THRU_DATE | Till this date the relationship between parent and child is valid | |
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
4. Customer CBU Details
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CUSTOMER_CMD_CODE | ||
| ROW_ID | ||
| BRAND_CODE | ||
| CBU_ID | ||
| CBU_TYPE | ||
| VALID_FROM_DATE | From this date the relationship between parent and child is valid | |
| VALID_THRU_DATE | Till this date the relationship between parent and child is valid | |
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
5. CUSTOMER_SEGMENT_DETAIL
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CUSTOMER_CMD_CODE | ||
| ROW_ID | ||
| BRAND_CODE | ||
| SEGMENT_TYPE_CODE | ||
| SEGMENT_VALUE_CODE | ||
| SEGMENT_VALUE_NAME | ||
| SEGMENT_VALUE_DESC | ||
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
6. CUSTOMER_WEB_BL_DETAIL
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CUSTOMER_CMD_CODE | ||
| ROW_ID | Customer Row ID | |
| BRAND_CODE | Customer Brand Code | |
| NEGOTIABLE_BL | Flag indicating whether this is Negotiable customer or not based Brand | |
| NON_NEGOTIABLE_WAYBILL | Flag indicating whether this is Non-Negotiable customer or not based Brand | |
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
7. CUSTOMER_EXTERNAL_IDENTIFIERS
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CUSTOMER_CMD_CODE | ||
| ROW_ID | ||
| EXTERNAL_SOURCE_SYSTEM | ||
| EXTERNAL_SYSTEM_IDENTIFIER | ||
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
8. CUSTOMER_STATUS_REASONS
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CUSTOMER_CMD_CODE | ||
| ROW_ID | ||
| CUSTOMER_STATUS_CD | P,I,A sample values | |
| REASON_CD | Reason for suspending the customer | |
| REASON_NAME | ||
| REASON_DESC | ||
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
9. CUSTOMER_BVD_INFORMATION
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| BVD_ID | ||
| CUSTOMER_CMD_CODE | ||
| BVD_CUSTOMER_STATUS | ||
| CUSTOMER_LEGAL_NAME | ||
| GUO_BVD_ID | ||
| GUO_BVD_NAME | ||
| GUO_BVD_STATUS | ||
| HQ_BVD_ID | ||
| HQ_BVD_NAME | ||
| HQ_BVD_STATUS | ||
| BVD_MAJOR_SECTOR | ||
| MARKET_CAP | ||
| OPERATING_REVENUE_TURN_OVER | ||
| DATE_OF_ACCOUNT | ||
| PROFIT_MARGIN_PERCENT | ||
| CREDIT_RATING | ||
| RECOMMENDED_CREDIT_LIMIT | ||
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
10. CUSTOMER_SIC_INFORMATION
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| BVD_ID | ||
| CUSTOMER_CMD_CODE | ||
| BVD_SECTOR | ||
| CORE_SIC_CODE | ||
| CORE_SIC_DESC | ||
| PRIM_SIC_CODE | ||
| PRIM_SIC_DESC | ||
| SEC_SIC_CODE | ||
| SEC_SIC_DESC | ||
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
Contact Tables
11. Contact Information
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CONTACT_CMD_CODE | ||
| CONTACT_STATUS | ||
| SALUTATION_LOCAL_LANGUAGE | Contact salutation local language | |
| SALUTATION_STANDARD | Contact salutation standard | |
| FIRST_NAME_LOCAL_LANGUAGE | Contact first name | |
| FIRST_NAME_STANDARD | Contact first name standard | |
| LAST_NAME_LOCAL_LANGUAGE | Contact Last name | |
| LAST_NAME_STANDARD | Contact last name standard | |
| DEPARTMENT | Department of contact. This indicates that the contact belongs to perticular depatment in their home orgnaization. It will help us to identify right contact for right communication. | |
| JOB_TITLE | Job title of contact | |
| TELEPHONE_ISD_COUNTRY_CODE | International dialing code of Contact Phone number | |
| TELEPHONE_DIALING_CODE | ||
| TELEPHONE_NUMBER | Contact Telephone Number | |
| EXTENSION_NUMBER | Phone Extension | |
| MOBILE_ISD_COUNTRY_CODE | International dialing code of Contact Mobile number | |
| MOBILE_DIALING_CODE | ||
| MOBILE_NUMBER | Contact Mobile Number | |
| MOBILE_ISD_COUNTRY_CODE | International dialing code of Contact Fax number | |
| MOBILE_DIALING_CODE | ||
| FAX_NUMBER | Contact Fax Number | |
| PRIMARY_EMAIL_ADDRESS | Primary email. This indicates as primary contact. | |
| SECONDARY_EMAIL_ADDRESS | Secondary email of the contact which is optional | |
| LANGUAGE_PREFERENCE | Default is EN. This indicate a language by which we communicate to contact. | |
| TEAM_CONTACT_INDICATOR | ||
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
12. Contact customer relationship
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| ROW_ID | Contact record rowid to categorize as particular type. | |
| REL_TYPE_CODE | Hierarchy relationship type code value defined in HM configuration in HUB console as shown in screenshot. | |
| CUSTOMER_CMD_CODE | ||
| CONTACT_CMD_CODE | ||
| IS_MASTER_CONTACT | ||
| VALID_FROM_DATE | From this date the relationship between parent and child is valid | |
| VALID_THRU_DATE | Till this date the relationship between parent and child is valid | |
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
13. Contact classification
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| ROW_ID | Contact record rowid to categorize as particular type. | |
| CONTACT_CMD_CODE | ||
| CLASSIFICATION_TYPE | ||
| CLASSIFICATION_CODE | ||
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
14. Contact external identifiers
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CONTACT_CMD_CODE | ||
| ROW_ID | Contact record rowid to categorize as particular type. | |
| EXTERNAL_SOURCE_SYSTEM | ||
| EXTERNAL_SYSTEM_IDENTIFIER | ||
| IS_DELETED | Indicates whether the party is deleted (Y|N) from business point of view | |
| IS_PENDING | Indicates whether the party is pending(Y|N) from business point of view |
15. Contact communication preference document type
| DB ATTRIBUTE NAME | API ATTRIBUTE NAME | DESCRIPTION |
|---|---|---|
| CUSTOMER_CMD_CODE | ||
| CONTACT_CMD_CODE | ||
| BRAND_CODE | ||
| DOCUMENT_TYPE | ||
| COMMUNICATION_PREF_TYPE | ||
| OTH_COMM_DTL | ||
| COMM_PREF_DOC_TYP_STAT |
6. DDL scripts:
In DDL scripts we have written queries for creating customer core tables, contact core tables ,reference tables and denormalized tables along with different set of constrains on each table like primary key, foreign key etc.. It also contains drop query for all the created tables. For each reference tables insert scripts are included and also select queries are represented for each table.
Please find below git link for all DDL scripts
https://github.com/Maersk-Global/smds-cmd-database-artifacts
7. Sequences:
We have created 243 sequences in the below document. All the sequences are created in MDM_SMDSMD schema. In each sequence we have mentioned the attributes increment, min value, max value and start value. Each sequence name ends with the country code.
Please find below git link for sequences scripts
https://github.com/Maersk-Global/smds-cmd-database-artifacts
Was this page helpful?