General Description of the Feature
This feature is closely related with the "Master Data Concept" feature which was introduced in the product level of version 2.40. The Master DB Data Import process is developed to cover two scenarios:
- The enablement of a Master installation into an already live project. In this scenario the customer data that already exist in the Local database should be checked and prepared to be imported into the Master DB
- The addition and enablement of a new Dealer in the Local DB. In this scenario the customer data of the new dealer should be, as a first step, prepared and imported into the Local DB and then checked and be imported into the Master DB as well. Before importing the data of a new dealer into the Master DB, possible duplicate customer data with the existing master data should be found and be prepared for the info.
Steps for a successful import per scenario
Scenario A: This scenario is relevant only for projects that enable the Master Data Concept into an already live installation.
- The DB Team should export the Customer Data from the Local DB in a status right before the enablement of the Master installation
- The Consulting Team checks the newly generated excel with the customer data from the Local DB and fill up certain fields that re mandatory for the rest of the procedure (Extra Details - CP: U_IDMS_MasterContactCode & U_IDMS_MasterCardCode)
- The DB Team takes the generated excel from step 1 and autogenerates the excel for the import into the Master installation
- The Consulting Team checks the newly generated Import to Master installation excel and approves the import. In this excel and in the spreadsheet called "MasterDataLink" exists the whole link between the Local and Master customer entries for Business Partner, Contact Person and Addresses.
- The DB Team imports the prepared data into the Master DB.
Scenario B: This scenario is prepared to cover the need of adding the customer data of a new dealer into the Local and Master DB, the linkage between the two databases and the possibility of handling duplicate entries (a customer already exists in Master DB because of Dealer A and also exists in the new data that are about to be imported).
- The DB Team exports the latest version of the customer data from the Master DB
- The Consulting Team prepares the import excel with the new customer data for the Local DB and with Dimensions the new Dealer Company. In this case the consulting team should:
- Generate the Card Codes for the new customer entries (unique codes into the database) and also generate the Master DB Card Code, Master DB Contact codes and Master DB Address Codes in the relevant fields in the excel. More info regarding the excel can be found below.
- Consult the Master DB export excel (step 1) and try to identify if there are any customers in the new list what already exist in Master DB. If yes, then the consultants should use the existing "Master DB Codes" for these entries and not generate new.
- The DB Team takes the prepared excel and imports it into the Local DB
- The DB Team autogenerates the excel for the import into the Master installation.
- The Consulting Team checks the newly generated Import to Master installation excel and approves the import. In this excel and in the spreadsheet called "MasterDataLink" exists the whole link between the Local and Master customer entries for Business Partner, Contact Person and Addresses.
- The DB Team imports the prepared data into the Master DB.
How to fill the excel with new customer data
OCRD (Business Partner):
| Field Name | CardCode | CardName | CardType | GroupCode | CmpPrivate | Phone1 | Phone2 | Fax | CntctPrsn | LicTradNum | ListNum | Currency | Cellular | City | County | Country | E_Mail | CreateDate | UpdateDate | ECVatGroup | LangCode | U_IDMS_UpdateTime | U_IDMS_CreateTime | U_IDMS_DIM_Branch | U_IDMS_DIM_Company | U_IDMS_DIM_Location | U_IDMS_CustomVAT | U_IDMS_CreateDate | U_IDMS_UpdateDate | U_IDMS_VIP | U_IDMS_BusPartMasNo | BillToDef |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Comments | Value = Unique code of the BP in the Local DB | Field: Name BP Name for Companies or Last Name & First Name for Private | Field: Business Partner Type Values:
The customers that are added into the Master DB should always be of type "C" . | Value: 100 | Field: Type Values:
| Field: Telephone1 | Field: Telephone2 | Field: Fax | Field: Main Contact Person Value: OCPR.Name | Field: Federal Tax ID | Field: Pricelist | Field: Currency Value: EUR | Field: Mobile Phone | City of the BP | Country of the BP | Value: The selected Country Code. | Field: Email | Field can be left empty. Format: YYYY-MM-DD | Field can be left empty. Format: YYYY-MM-DD | Field: Vat Group Value: The selected Vat group code | Field: Language Value: The selected Language Code | Field can be left empty. Format: YYYY-MM-DD | Field can be left empty. Format: HHMM | Value: The Code of the Branch for Dimension Permission purposes. | Value: The Code of the Company for Dimension Permission purposes. | Value: The Code of the Location for Dimension Permission purposes. | Field: Vat Group Value: The Vat percentage for this BP. Format: 20.000000 | Field can be left empty. Format: HHMM | Field can be left empty. Format: YYYY-MM-DD | Field: VIP Values:
| Value: The Card Code that the related BP Master record has. Proposal: U_IDMS_DIM_Company & CardCode | Value from: CRD1.Address Add the value of the main BP Address. |
| Example | 10 | Luft Mario | C | 100 | I | +380256325632 | +380256325633 | Luft Mario | 1 | EUR | +380745874587 | UA | mario.luft@mailinator.com | UA1 | 33 | 50 | AWTD | 50 | 20.000000 | AWTD10 | Address 1 |
OCPR (Contact Person):
| Field Name | CntctCode | CardCode | Name | Position | Address | Tel1 | Tel2 | Cellolar | Fax | Gender | Profession | UpdateDate | UpdateTime | Title | Active | FirstName | MiddleName | LastName | U_IDMS_CreateTime | U_IDMS_UpdateTime | U_IDMS_Gender | U_IDMS_Position | U_IDMS_DIM_Branch | U_IDMS_DIM_Company | U_IDMS_DIM_Location | U_IDMS_Language | U_IDMS_VocativeFirstName | U_IDMS_Title | U_IDMS_CreateDate | U_IDMS_VocativeLastName | U_IDMS_UpdateDate | U_IDMS_PassportID | U_IDMS_ContactCode | U_IDMS_MasterContactCode | U_IDMS_MasterCardCode | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Comments | Field can be left empty. Unique code of the CP in the Local DB. | Value from: OCRD.CardCode The Card Code of the related BP. | Last Name & First Name | Field: Position | Value from: CRD1.Address Add the value of this CP's Address. | Field: Telephone1 | Field: Telephone2 | Field: Mobile Phone | Field: Fax | Field: Email | Field: Gender Values:
| Field: Profession | Field can be left empty. Format: YYYY-MM-DD | Field can be left empty. Format: HHMM | Field: Title Value: The Code of the selected Title | Values:
| Field: First Name | Field: Middle Name | Field: Last Name | Field can be left empty. Format: HHMM | Field can be left empty. Format: HHMM | Values:
| Value: The Code of the Branch for Dimension Permission purposes. | Value: The Code of the Company for Dimension Permission purposes. | Value: The Code of the Location for Dimension Permission purposes. | Field: Language Value = OCRD.LangCode The selected Language Code | Field: Vocative First Name Value: FirstName | Field: Title Value = Title The Code of the selected Title | Field can be left empty. Format: HHMM | Field: Vocative Last Name Value = LastName | Field can be left empty. Format: YYYY-MM-DD | Field: Passport ID | Value = Unique code of the CP in the Local DB. | Value: The Contact Code that the related CP Master record has. Proposal: U_IDMS_DIM_Company & U_IDMS_ContactCode | Value = OCRD.U_IDMS_BusPartMasNo Proposal: U_IDMS_DIM_Company & CardCode | |
| Example | 10 | Luft Mario | +380256325632 | +380745874587 | mario.luft@mailinator.com | M | Y | Mario | Luft | gt_Male | 50 | AWTD | 50 | 33 | c10 | AWTDc10 | AWTD10 |
CRD1 (Addresses):
| Field Name | Address | CardCode | LineNum | Street | ZipCode | City | Country | State | Block | AdresType | StreetNo | U_AddressType | U_IDMS_Latitude | U_IDMS_Longitude | U_IDMS_ODCountryCode | U_IDMS_ODCountry | U_IDMS_ContactCode | U_IDMS_CreateDate | U_IDMS_UpdateDate | U_IDMS_AddressCode | U_IDMS_DIM_Branch | U_IDMS_DIM_Company | U_IDMS_DIM_Location | U_IDMS_MasterAddressCode | U_IDMS_MasterCardCode | U_IDMS_MasterContactCode | U_IDMS_AddressType |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Comments | Passible Values: Address 1, Address 2, Address 3 etc. For each Address with the same Card Code (BP/CP) add a higher value (Address 1, Address 2, Address 3 etc.) | Value from: OCRD.CardCode The Card Code of the related BP. | For each Address with the same Card Code (BP/CP) add a higher LineNum (1, 2, 3 etc.) | Field: Street | Field: Zip Code | Field: City | Field: Country | Field: State | Field: Block | Field: Type (in the Address of Company BP) Values:
In case that the Address belongs to a Private BP, always add the value "B". | Field: Street Number | Field: Type (in the Address of PrivateBP) Values:
In case that the Address belongs to a Company BP add the value "3". | Latitude of the Address. | Longitude of the Address. | Value: The selected country Code | Value: The selected country | Value from: OCRD.U_IDMS_ContactCode The Contact Code of the related CP. | Field can be left empty. Format: YYYY-MM-DD | Field can be left empty. Format: YYYY-MM-DD | Value = Unique code of the Address in the Local DB. | Value: The Code of the Branch for Dimension Permission purposes. | Value: The Code of the Company for Dimension Permission purposes. | Value: The Code of the Location for Dimension Permission purposes. | Value: The Address Code that the related Address Master record has. Proposal: U_IDMS_DIM_Company & U_IDMS_AddressCode | Value = OCRD.U_IDMS_BusPartMasNo Proposal: U_IDMS_DIM_Company & CardCode | Value: The Contact Code that the related CP Master record has. Proposal: U_IDMS_DIM_Company & U_IDMS_ContactCode | Value = U_AddressType |
| Example | Address 1 | 10 | 1 | Bilomorska Street | 02000 | Kyiv | UA | B | 1 | 50.4613016 | 30.6428647 | UA | c10 | a10 | 50 | AWTD | 50 | AWTDa10 | AWTD10 | AWTDc10 | 2 |
Extra Details BP (IDMS_CST_FIELD)
| Field Name | U_IDMS_Entity | U_IDMS_StrProperty1 | U_IDMS_StrProperty2 | U_IDMS_StrProperty3 | U_IDMS_EntityValue |
|---|---|---|---|---|---|
| Comments | Value = BusinessPartner | Field: Customer Is Dealer Values:
| Field: Business Partner Full Name | Field: Main Address | Value from: OCRD.CardCode The Card Code of the related BP. |
| Example | BusinessPartner | false | Денисов Дмитрий Сергеевич | Украина Луганская г. Луганск вул. 50р. Утворення СРСР, б.34 | 10 |
Extra Details CP (IDMS_CST_FIELD)
| Field Name | U_IDMS_Entity | U_IDMS_StrProperty1 | U_IDMS_StrProperty3 | U_IDMS_StrProperty9 | U_IDMS_DateProperty1 | U_IDMS_ContactCode | CardCode | CntctCode | U_IDMS_MasterContactCode | U_IDMS_MasterCardCode |
|---|---|---|---|---|---|---|---|---|---|---|
| Comments | Value = ContactEmployee | Field: Is Citizen of Ukraine Values:
| Field: Issued By | Field: Passport is Old Values:
| Field: Date of Issue Format: YYYY-MM-DD | Value from: OCRD.U_IDMS_ContactCode The Contact Code of the related CP. | Value from: OCRD.CardCode The Card Code of the related BP. | Value from: OCPR.CntctCode Field can be left empty. | Value: The Contact Code that the related CP Master record has. OCPR.U_IDMS_MasterContactCode Proposal: U_IDMS_DIM_Company & U_IDMS_ContactCode | Value: The Card Code that the related BP Master record has. OCRD.U_IDMS_BusPartMasNo Proposal: U_IDMS_DIM_Company & CardCode |
| Example | ContactEmployee | true | 8034 | true | c10 | 10 | AWTDc10 | AWTD10 |
Date Privacy Statement Master
| Field Name | Code | U_Type | U_BPCode |
|---|---|---|---|
| Comments | Value = Unique code of the Data Privacy statement of a BP in the Local DB. | Value = BP | Value from: OCRD.CardCode The Card Code of the related BP. |
| Example | 10 | BP | 10 |
Data Privacy Statement Detail
| Field Name | Code | LineId | U_Type | U_SymbolCode | U_PhoneChannelState | U_PostChannelState | U_EmailChannelState | U_SMSChannelState | U_printTime | U_ExpiredSymbolCodeDt | U_UcMrktSurvey | U_UcPromotion | U_UcProfPrsServ | U_SignedPolicy | U_ProhibitDialog |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Comments | Value: The unique code of this entry added in the field "Code" in the above table. | Value = 1 | Value = D | Field can be left empty. | Field: Phone Channel Values:
| Field: Postal (Mail) Channel Values:
| Field: E-Mail Channel Values:
| Field: SMS Channel Values:
| Field can be left empty. | Field can be left empty. | Field: Market and Customer Satisfaction Surveys Values:
| Field: Promotion of Products and Services Values:
| Field: Profiling for personalized Services and Products Values:
| Field: The customer has signed the current privacy policy Values:
| Field can be left empty. |
| Example | 10 | 1 | D | N | N | Y | N | N | N | N | N |
Legend |
|---|
Marked with red are the fields that are mandatory when having an entry in this table. |
Marked with green are the fields that is proposed to be field when having an entry in this table. |
Marked with white are the fields that can be left empty when having an entry in this table. |
The above explained excel can be downloaded from here: AWT_Master_DataExportLocalDB.xlsx The data in all the spreadsheets of excel should be delivered as Text.
Business Benefit
The Master DB Data Import Process gives the possibility:
- to an existing live customer to enhance their solution with the Master Database Concept and integrate the existing customer data.
- to a new Dealer Company to be included into a customer solution at any time.
Configuration
Additional Information
- Feature No.: ODP-1101
- Related Parent feature: ODP-1105
Add Comment