Business Partner - Master Data - Import

SSIS Control Flow Chart

Load all tables to be used in this process to a cache
Import data from cache to MT Tables, after making sure they are empty. The MT Tables serve as temporary database.
Import excel data

ODMasterDataV1_1.xls Content

The Database Mapping columns contain the reference of the excel column in the database, where the data from the excel column is being stored
The Lookup columns contain the database table from which the excel column retrieves data

BusinessPartners

		Database Mapping			Lookup
Column	Description	Table	Field	DB Type	Table	Field
DMS Code	The code from customer's system(ERP-DMS)	OCRD	U_IDMS_DMSCode	NVARCHAR(50)
CardCode	Customer's code for OneDealer	OCRD	CardCode	NVARCHAR(15)
CardName	BP Name	OCRD	CardName	NVARCHAR(100)
CardType	Type C = Customer S = Vendor L = Lead	OCRD	CardType	NVARCHAR(1)
GroupCode	Group Code	OCRD	GroupCode	SMALLINT
CmpPrivate	Company or Private C = Company I = Private U = Unknown	OCRD	CmpPrivate	NVARCHAR(1)
Phone1	Telephone 1	OCRD	Phone1	NVARCHAR(20)
Phone2	Telephone 2	OCRD	Phone2	NVARCHAR(20)
CntctPrsn	Contact Person Name	OCRD	CntctPrsn	NVARCHAR(90)
LicTradNum	Federal Tax ID	OCRD	LicTradNum	NVARCHAR(32)
Currency	Currency Code	OCRD	Currency	NVARCHAR(3)
Cellular	Mobile Phone	OCRD	Cellular	NVARCHAR(50)
City	City	OCRD	City	NVARCHAR(100)
Country	Country Code	OCRD	Country	NVARCHAR(3)
E_Mail	Email	OCRD	E_Mail	NVARCHAR(100)
CreateDate	Create Date	OCRD	CreateDate	TIMESTAMP
UpdateDate	Last Update Date	OCRD
ECVatGroup	VAT Group	OCRD	ECVatGroup	NVARCHAR(8)	OVTG	Code
LangCode	Language Code	OCRD	LangCode	INTEGER	OLNG	Code
U_IDMS_DIM_Branch	Dimension Company	OCRD	U_IDMS_DIM_Branch	NVARCHAR(30)	"IDMS_COMMONDEV211"."BRANCH"	Code
U_IDMS_DIM_Company	Dimension Branch	OCRD	U_IDMS_DIM_Company	NVARCHAR(30)	"IDMS_COMMONDEV211"."COMPANY"	Code
U_IDMS_DIM_Location	Dimension Location	OCRD	U_IDMS_DIM_Location	NVARCHAR(30)	"IDMS_COMMONDEV211"."LOCATION"	Code
U_IDMS_CustomVAT	Custom VAT	OCRD	U_IDMS_CustomVAT	DECIMAL(21,6)
U_IDMS_VatBusinessGroupCode	Vat Business Group Code	OCRD	U_IDMS_VatBusinessGroupCode	NVARCHAR(50)	IDMS_VATBUSGROUP	Code
U_IDMS_FinancialGroup	Financial Group Code	OCRD	U_IDMS_FinancialGroup	NVARCHAR(50)	IDMS_FINANCIALGROUP	Code

ContactPersons

		Database Mapping			Lookup
Column	Description	Table	Field	DB Type	Table	Field
CardCode	BP Code	OCPR	CardCode	NVARCHAR(15)	OCRD	CardCode
Name	Name	OCPR	Name	NVARCHAR(50)
Address	Address	OCPR	Address	NVARCHAR(100)	CRD1 or OCRD	Address
Tel1	Telephone 1	OCPR	Tel1	NVARCHAR(20)
Tel2	Telephone 2	OCPR	Tel2	NVARCHAR(20)
Cellolar	Mobile Phone	OCPR	Cellolar	NVARCHAR(50)
E-mail	Email	OCPR	E_MailL	NVARCHAR(100)
Gender	Gender E = Undefined F = Female M = Male	OCPR	Gender	NVARCHAR(1)
Title	Title	OCPR	Title	NVARCHAR(10)
Active	Is Active (Y/N)	OCPR	Active	NVARCHAR(1)
FirstName	First Name	OCPR	FirstName	NVARCHAR(50)
LastName	Last Name	OCPR	LastName	NVARCHAR(50)
U_IDMS_Gender	OD Gender E = gt_Undefined F = gt_Female M = gt_Male	OCPR	U_IDMS_Gender	NVARCHAR(30)
U_IDMS_DIM_Branch	Dimension Branch	OCPR	U_IDMS_DIM_Branch	NVARCHAR(30)	"IDMS_COMMONDEV211"."BRANCH"
U_IDMS_DIM_Company	Dimension Company	OCPR	U_IDMS_DIM_Company	NVARCHAR(30)	"IDMS_COMMONDEV211"."COMPANY"
U_IDMS_DIM_Location	Dimension Location	OCPR	U_IDMS_DIM_Location	NVARCHAR(30)	"IDMS_COMMONDEV211"."LOCATION"
U_IDMS_Language	Language Code	OCPR	U_IDMS_Language	INTEGER
U_IDMS_Title	OD Title	OCPR	U_IDMS_Title	NVARCHAR(50)
U_IDMS_ContactCode	Contact person unique code	OCPR	U_IDMS_ContactCode	NVARCHAR(50)

Addresses

		Database Mapping			Lookup
Column	Description	Table	Field	DB Type	Table	Field
Address	Address Code	CRD1	Address	NVARCHAR(50)
CardCode	BP Code	CRD1	CardCode	NVARCHAR(15)	OCRD	CardCode
LineNum	Line Number	CRD1	LineNum	INTEGER
Street	Street	CRD1	Street	NVARCHAR(100)
ZipCode	Zip Code	CRD1	ZipCode	NVARCHAR(20)
City	City	CRD1	City	NVARCHAR(100)
Country	Country Code	CRD1	Country	NVARCHAR(3)
AdresType	Address Type S = Ship to B = Bill to	CRD1	AdresType	NVARCHAR(1)
StreetNo	Street Number	CRD1	StreetNo	NVARCHAR(100)
U_IDMS_ODCountryCode	OneDealer Country Code
U_IDMS_ODCountry	OneDealer Country	CRD1	U_IDMS_ODCountry	NVARCHAR(50)	OCRY	Name
U_IDMS_ContactCode	CP Code	CRD1	U_IDMS_ContactCode	NVARCHAR(50)	OCPR	U_IDMS_ContactCode
U_IDMS_AddressCode		CRD1	U_IDMS_AddressCode	NVARCHAR(50)
U_IDMS_DIM_Branch	Dimension Branch	CRD1	U_IDMS_DIM_Branch	NVARCHAR(30)	"IDMS_COMMONDEV211"."BRANCH"
U_IDMS_DIM_Company	Dimension Company	CRD1	U_IDMS_DIM_Company	NVARCHAR(30)	"IDMS_COMMONDEV211"."COMPANY"
U_IDMS_DIM_Location	Dimension Location	CRD1	U_IDMS_DIM_Location	NVARCHAR(30)	"IDMS_COMMONDEV211"."LOCATION"
U_IDMS_AddressType	OD Address Type 0 = Work 1 = Home 2 = Other	CRD1	U_IDMS_AddressType	NVARCHAR(30)

CustomFieldsBP

NOTE: CustomFiledsBP is an entity that enables us to add information dynamically. The entity's type defers, based on the needs of each customer

		Database Mapping			Lookup
Column	Description	Table	Field	DB Type	Table	Field
U_IDMS_Entity	U_IDMS_Entity	@IDMS_CST_FIELD	U_IDMS_Entity	NVARCHAR(30)
U_IDMS_StrProperty1	String Property 1	@IDMS_CST_FIELD	U_IDMS_StrProperty1	NVARCHAR(254)
U_IDMS_StrProperty2	String Property 2	@IDMS_CST_FIELD	U_IDMS_StrProperty2	NVARCHAR(254)
U_IDMS_StrProperty3	String Property 3	@IDMS_CST_FIELD	U_IDMS_StrProperty3	NVARCHAR(254)
U_IDMS_EntityValue	BP Code	@IDMS_CST_FIELD	U_IDMS_EntityValue	NVARCHAR(254)

CustomFieldsCP

		Database Mapping			Lookup
Column	Description	Table	Field	DB Type	Table	Field
U_IDMS_Entity	U_IDMS_Entity	@IDMS_CST_FIELD	U_IDMS_Entity	NVARCHAR(30)
U_IDMS_StrProperty1	String Property 1	@IDMS_CST_FIELD	U_IDMS_StrProperty1	NVARCHAR(254)
U_IDMS_StrProperty3	String Property 3	@IDMS_CST_FIELD	U_IDMS_StrProperty3	NVARCHAR(254)
U_IDMS_StrProperty9	String Property 9	@IDMS_CST_FIELD	U_IDMS_StrProperty9	NVARCHAR(254)
U_IDMS_DateProperty1	Date Property 1	@IDMS_CST_FIELD	U_IDMS_DateProperty1	TIMESTAMP
U_IDMS_ContactCode		@IDMS_CST_FIELD
CardCode	BP Code	@IDMS_CST_FIELD
CntctCode	CntctCode	@IDMS_CST_FIELD	U_IDMS_EntityValue	NVARCHAR(254)

Template Excel file

ODMasterDataV1_2.xls

The Sheets DataPrivMaster, and DataPrivDetail refer to GDPR - Import / Export Processes but are included in the above excel file

Page tree