CMD - DMDM Integration

Customer Entity Fields mapping

CMD Entity	CMD EMP Field	Transformation Logic	DAMCO MQ Message Fields
Customer	NA	Always CUSTOMER	Organization\EventName
Customer	NA	Either UPDATE or CREATE	Organization\EventType
Customer	NA	CurrentDateTime	Organization\EventTime
Customer	NA	Is it required?	Organization\MessageId
Customer	NA	Is it required?	Organization\CorrelationId
Customer	tradingName	tradingName.substring(0, 36)	Organization\OrganizationBasic\OrganizationName
Customer	tradingName	tradingName.substring(36, 72)	Organization\OrganizationBasic\OrganizationExtensionName
Customer		Short name of the customer(10 Chars)? What is short name mean by?	Organization\OrganizationBasic\AbbreviatedName
Customer	NA	Default value as CUST	Organization\OrganizationBasic\PartyRoleType
Customer	customerGroupType	Is this correct mapping?	Organization\OrganizationBasic\OrganisationType
Customer	statusCode/statusName	Convert from code to full status name like A to Active	Organization\OrganizationBasic\Status
Customer	reasonCode/reasonName	No Logic, direct mapping	Organization\OrganizationBasic\StatusReason
Customer	NA	value set as 'N' always fixed	Organization\OrganizationBasic\SharedCodeIndicator
Customer	streetNumber	No Logic, direct mapping	Organization\PrimaryAddress\HouseNumber
Customer	streetName	No Logic, direct mapping	Organization\PrimaryAddress\Street
Customer	apartmentOrFloor	No Logic, direct mapping	Organization\PrimaryAddress\AddressLine2
Customer		Alt Zip Code in cases that postal code is not mandatory for the country?	Organization\PrimaryAddress\AltZipCity
Customer	postalCode	No Logic, direct mapping	Organization\PrimaryAddress\PostalCode
Customer		Auto-populated once the Postal Code is entered from GEO	Organization\PrimaryAddress\PostalCodeLocation
Customer	poBoxNumber	No Logic, direct mapping	Organization\PrimaryAddress\POBox
Customer	countryName/isoCountryCode	No Logic, direct mapping	Organization\PrimaryAddress\Country
Customer		Reference from Geo	Organization\PrimaryAddress\CountryRKSTCode
Customer		Reference from Geo	Organization\PrimaryAddress\CountryRKTSCode
Customer		Reference from Geo	Organization\PrimaryAddress\CountryGeoId
Customer	regionCode/regionName	No Logic, direct mapping	Organization\PrimaryAddress\State
Customer		Reference from Geo	Organization\PrimaryAddress\State_Code
Customer	cityName	No Logic, direct mapping	Organization\PrimaryAddress\City
Customer		Reference from Geo	Organization\PrimaryAddress\CityRKSTCode
Customer		Reference from Geo	Organization\PrimaryAddress\CityRKTSCode
Customer	cityCode	Reference from Geo	Organization\PrimaryAddress\CityGeoId
Customer	NA	Default is HQ for all customers from CMD	Organization\PrimaryAddress\BEFunction
Customer		Site code logic used in DMDM to be followed - Using the inputs CMD customer ?	Organization\PrimaryAddress\MODSSiteCode
Customer		Generating SCV and FCT for location using stored procedure - PKG_DMDM_UTILS.SP_GetRefNum; Input - CMD Country Row id?	Organization\PrimaryAddress\LocationSCVNumber
Customer		Generating SCV and FCT for location using stored procedure - PKG_DMDM_UTILS.SP_GetRefNum; Input - CMD Country Row id	Organization\PrimaryAddress\LocationFACTCode
Contact	number	No Logic, direct mapping	Organization\ContactDetails\PhoneNumber
Contact	number	No Logic, direct mapping	Organization\ContactDetails\FaxNumber
Contact	primaryEmailId	No Logic, direct mapping	Organization\ContactDetails\Email
Customer	externalSystemName/externalSystemReference=SCV	No Logic, direct mapping	Organization\ReferenceNumber\SCVNumber
Customer	externalSystemName/externalSystemReference=FCT	No Logic, direct mapping	Organization\ReferenceNumber\FactCode
Customer	externalSystemName/externalSystemReference=MODS	Country + BE CODE	Organization\ReferenceNumber\MODSBECode
Customer	identifierType/identifierCode/identifierValue	DUNS REF type	Organization\ReferenceNumber\DunsNumber
Customer	Need to get it in JSON	No Logic, direct mapping	Organization\ReferenceNumber\CustomerAccountGroup
Customer	customerType	No Logic, direct mapping	Organization\ReferenceNumber\CustomerAccountGroupCode
Customer		LTRIM(RTRIM(tns_ReferenceTypeNumber))?	Organization\ReferenceNumber\CustomsID
Customer	url	No Logic, direct mapping	Organization\ReferenceNumber\CustomerURL
Customer	identifierType/identifierCode/identifierValue	No Logic, direct mapping	Organization\ReferenceNumber\TaxReference
Customer	invoiceISOLanguageCodePreference	No Logic, direct mapping	Organization\ReferenceNumber\Language
Customer		Get the code from C_LKP_LANG_CTRY_REL & PKG_CUST_COUNTRY?	Organization\ReferenceNumber\LanguageKey
Customer	NA	Default is null	Organization\ReferenceNumber\EmpCode
Customer	relationshipType	cmdCustomer.duplicationReasonCode != null? 'N' :'Y'	Organization\ReferenceNumber\IsMaster
Customer	parentCustomerCode	Parent customer SCVCode	Organization\ReferenceNumber\MasterSCVNumber
Customer	relationshipType	ETL Mapping - IIF(tns_RelationshipTypeCd = 'MASTER_DUP', 'SCVDP', NULL) && PartyRole->DuplicateReasonCode => actualScvCode==parentScvCode?null:cmdCustomer.DuplicateReasonCode	Organization\ReferenceNumber\DuplicateReasonCode
Customer	relationshipTypeName	ETL Mapping -IIF(tns_RelationshipTypeCd = 'MASTER_DUP', 'Duplicate Migrated from SCV', NULL)	Organization\ReferenceNumber\DuplicateReasonDescription
			Organization\CustomerService
			Organization\Sales\AccountManager
			Organization\Sales\Segmentation
			Organization\Sales\Vertical
			Organization\OrgDetailsInLocalLanguage\Name1
			Organization\OrgDetailsInLocalLanguage\Name2
			Organization\OrgDetailsInLocalLanguage\AddressLine1
			Organization\OrgDetailsInLocalLanguage\AddressLine2
			Organization\OrgDetailsInLocalLanguage\AddressLine3
			Organization\OrgDetailsInLocalLanguage\PostalCode
			Organization\OrgDetailsInLocalLanguage\City
			Organization\OrgDetailsInLocalLanguage\Country
			Organization\OrgDetailsInLocalLanguage\LanguageKey

Contact (Person) Entity field mapping

CMD Entity	CMD EMP Field	Transformation Logic	DAMCO MQ Message Fields
Contact	NA	Always PERSON	Person\EventName
Contact	NA	Either UPDATE or CREATE	Person\EventType
Contact	NA	CurrentDateTime	Person\EventTime
Contact	NA	Is it required?	Person\MessageId
Contact	NA	Is it required?	Person\CorrelationId
Contact	NA	Default value - USER	Person\PersonDetails\PartyRoleType
Contact	internationalSalutationCode	No Logic, direct mapping	Person\PersonDetails\InitialPrsnName
Contact	firstName	No Logic, direct mapping	Person\PersonDetails\PersonFirstName
Contact	lastName	No Logic, direct mapping	Person\PersonDetails\PersonLstName
Contact	statusCode	IIF(LTRIM(RTRIM(IS_ACTV_IND))='Y','Active','Inactive')	Person\PersonDetails\Status
Contact	isoCountryCode	No Logic, direct mapping	Person\ContactDetails\Country
Contact		Reference from Geo	Person\ContactDetails\CountryRKSTCode
Contact		Reference from Geo	Person\ContactDetails\CountryRKTSCode
Contact		Reference from Geo	Person\ContactDetails\CountryGeoId
Contact	number	IIF(NOT ISNULL(ltrim(rtrim(TELCOM_NUM))),rtrim(ltrim(TEL_DIALNG_CD))|	'-'
Contact	extensionNumber	No Logic, direct mapping	Person\ContactDetails\PhoneExtn
Contact	number	IIF(NOT ISNULL(ltrim(rtrim(MOBILE))),rtrim(ltrim(MOB_DIALNG_CD))|	'-'
Contact	number	IIF(NOT ISNULL(ltrim(rtrim(FAX_NUM))),rtrim(ltrim(FAX_DIALNG_CD))|	'-'
Contact	primaryEmailId	No Logic, direct mapping	Person\ContactDetails\Email
Contact	externalSystemReference/externalSystemName=SCV	No Logic, direct mapping	Person\ReferenceNumber\PersonID
Contact	contactCode	No Logic, direct mapping	Person\ReferenceNumber\PersonFactCode

Concern Entity Field Mapping

CMD Entity	CMD EMP Field	Transformation Logic	DAMCO MQ Message Fields
Customer	NA	Always CONCERN	Organization\EventName
Customer	NA	Either UPDATE or CREATE	Organization\EventType
Customer	NA	CurrentDateTime	Organization\EventTime
Customer	NA	Is it required?	Organization\MessageId
Customer	NA	Is it required?	Organization\CorrelationId
Customer	tradingName	tradingName.substring(0, 36)	Organization\OrganizationBasic\OrganizationName
Customer	tradingName	tradingName.substring(36, 72)	Organization\OrganizationBasic\OrganizationExtensionName
Customer		Short name of the customer(10 Chars)? What is short name mean by?	Organization\OrganizationBasic\AbbreviatedName
Customer		Default value as CUST	Organization\OrganizationBasic\PartyRoleType
Customer	customerGroupType	No Logic, direct mapping	Organization\OrganizationBasic\OrganisationType
Customer	statusCode/statusName	Convert from code to full status name like A to Active	Organization\OrganizationBasic\Status
Customer	reasonCode/reasonName	No Logic, direct mapping	Organization\OrganizationBasic\StatusReason
Customer		value set as 'N' always fixed	Organization\OrganizationBasic\SharedCodeIndicator
Customer	streetNumber	No Logic, direct mapping	Organization\PrimaryAddress\HouseNumber
Customer	streetName	No Logic, direct mapping	Organization\PrimaryAddress\Street
Customer	apartmentOrFloor	No Logic, direct mapping	Organization\PrimaryAddress\AddressLine2
Customer		Alt Zip Code in cases that postal code is not mandatory for the country?	Organization\PrimaryAddress\AltZipCity
Customer	postalCode	No Logic, direct mapping	Organization\PrimaryAddress\PostalCode
Customer		Auto-populated once the Postal Code is entered from GEO	Organization\PrimaryAddress\PostalCodeLocation
Customer	poBoxNumber	No Logic, direct mapping	Organization\PrimaryAddress\POBox
Customer	countryName/isoCountryCode	No Logic, direct mapping	Organization\PrimaryAddress\Country
Customer		Reference from Geo	Organization\PrimaryAddress\CountryRKSTCode
Customer		Reference from Geo	Organization\PrimaryAddress\CountryRKTSCode
Customer		Reference from Geo	Organization\PrimaryAddress\CountryGeoId
Customer	regionCode/regionName	No Logic, direct mapping	Organization\PrimaryAddress\State
Customer		Reference from Geo	Organization\PrimaryAddress\State_Code
Customer	cityName	No Logic, direct mapping	Organization\PrimaryAddress\City
Customer	cityCode	Reference from Geo	Organization\PrimaryAddress\CityRKSTCode
Customer		Reference from Geo	Organization\PrimaryAddress\CityRKTSCode
Customer		Reference from Geo	Organization\PrimaryAddress\CityGeoId
Customer		Default is HQ for all customers from CMD	Organization\PrimaryAddress\BEFunction
Customer		Site code logic used in DMDM to be followed - Using the inputs CMD customer ?	Organization\PrimaryAddress\MODSSiteCode
Customer		Generating SCV and FCT for location using stored procedure - PKG_DMDM_UTILS.SP_GetRefNum; Input - CMD Country Row id?	Organization\PrimaryAddress\LocationSCVNumber
Customer		Generating SCV and FCT for location using stored procedure - PKG_DMDM_UTILS.SP_GetRefNum; Input - CMD Country Row id?	Organization\PrimaryAddress\LocationFACTCode
Contact	number	No Logic, direct mapping	Organization\ContactDetails\PhoneNumber
Contact	number	No Logic, direct mapping	Organization\ContactDetails\FaxNumber
Contact	primaryEmailId	No Logic, direct mapping	Organization\ContactDetails\Email
Customer	externalSystemName/externalSystemReference=SCV	No Logic, direct mapping	Organization\ReferenceNumber\SCVNumber
Customer	externalSystemName/externalSystemReference=FCT	No Logic, direct mapping	Organization\ReferenceNumber\FactCode
Customer	externalSystemName/externalSystemReference=MODS	No Logic, direct mapping	Organization\ReferenceNumber\MODSBECode
Customer		How are we getting it?	Organization\ReferenceNumber\DunsNumber
Customer	customerType	No Logic, direct mapping	Organization\ReferenceNumber\CustomerAccountGroup
Customer		No Logic, direct mapping	Organization\ReferenceNumber\CustomerAccountGroupCode
Customer		LTRIM(RTRIM(tns_ReferenceTypeNumber))?	Organization\ReferenceNumber\CustomsID
Customer	url	No Logic, direct mapping	Organization\ReferenceNumber\CustomerURL
Customer	identifierType/identifierCode/identifierValue	No Logic, direct mapping	Organization\ReferenceNumber\TaxReference
Customer	invoiceISOLanguageCodePreference	No Logic, direct mapping	Organization\ReferenceNumber\Language
Customer		Get the code from C_LKP_LANG_CTRY_REL & PKG_CUST_COUNTRY?	Organization\ReferenceNumber\LanguageKey
Customer		Default is null	Organization\ReferenceNumber\EmpCode
Customer			Organization\Sales\AccountManager
Customer			Organization\Sales\Segmentation
Customer			Organization\Sales\Vertical

Department Entity Field Mapping

CMD Entity	CMD EMP Field	Transformation Logic	DAMCO MQ Message Fields
Department	NA	Always DEPARTMENT	Department\EventName
Department	NA	Either UPDATE or CREATE	Department\EventType
Department	NA	CurrentDateTime	Department\EventTime
Department	NA	Is it required?	Department\MessageId
Department	NA	Is it required?	Department\CorrelationId
Department		Default value 'DEPT'	Department\DepartmentDetails\PartyRoleType
Department		Values: Accounts Payable Export Department Finance Department Import Department APM Internal	Department\DepartmentDetails\DeptType
Department			Department\DepartmentDetails\DeptSrchRef
Department		Default value 'Active'	Department\DepartmentDetails\Status
Department			Department\ContactDetails\Country
Department			Department\ContactDetails\CountryRKSTCode
Department			Department\ContactDetails\CountryRKTSCode
Department			Department\ContactDetails\CountryGeoId
Department			Department\ContactDetails\PhoneNumber
Department			Department\ContactDetails\FaxNumber
Department			Department\ContactDetails\Email
Department			Department\ReferenceNumber\DeptID
Department			Department\ReferenceNumber\DeptFactCode

Master_Dup entity field mapping

CMD Entity	CMD EMP Field	Transformation Logic	DAMCO MQ Message Fields
Customer	NA	Always MASTER_DUP	Relationship\EventName
Customer	NA	Either UPDATE or CREATE	Relationship\EventType
Customer	NA	CurrentDateTime	Relationship\EventTime
Customer	NA	Is it required?	Relationship\MessageId
Customer	NA	Is it required?	Relationship\CorrelationId
Customer			Relationship\ParentDetails\OrganizationName
Customer			Relationship\ParentDetails\PartyRoleType
Customer			Relationship\ParentDetails\SCVNumber
Customer			Relationship\ParentDetails\FactCode
Customer			Relationship\ParentDetails\MODSBECode
Customer			Relationship\ParentDetails\Status
Customer			Relationship\ParentDetails\BEFunction
Customer			Relationship\ParentDetails\Country
Customer			Relationship\ParentDetails\CountryRKSTCode
Customer			Relationship\ParentDetails\CountryRKTSCode
Customer			Relationship\ParentDetails\CountryGeoId
Customer			Relationship\ChildDetails\ChildPartyRoleType
Customer			Relationship\ChildDetails\ChildSCVNumber
Customer			Relationship\ChildDetails\ChildFactCode
Customer			Relationship\ChildDetails\ChildMODSBECode
Customer			Relationship\ChildDetails\ChildStatus
Customer			Relationship\ChildDetails\RelationshipType

Relationship Entity Field Mapping

CMD Entity	CMD EMP Field	Transformation Logic	DAMCO MQ Message Fields
Customer	NA	Always RELATIONSHIP	Relationship\EventName
Customer	NA	Either UPDATE or CREATE	Relationship\EventType
Customer	NA	CurrentDateTime	Relationship\EventTime
Customer	NA	Is it required?	Relationship\MessageId
Customer	NA	Is it required?	Relationship\CorrelationId
Customer			Relationship\ParentDetails\OrganizationName
Customer			Relationship\ParentDetails\PartyRoleType
Customer			Relationship\ParentDetails\SCVNumber
Customer			Relationship\ParentDetails\FactCode
Customer			Relationship\ParentDetails\MODSBECode
Customer			Relationship\ParentDetails\Status
Customer			Relationship\ParentDetails\BEFunction
Customer			Relationship\ParentDetails\Country
Customer			Relationship\ParentDetails\CountryRKSTCode
Customer			Relationship\ParentDetails\CountryRKTSCode
Customer			Relationship\ParentDetails\CountryGeoId
Customer			Relationship\ChildDetails\ChildPartyRoleType
Customer			Relationship\ChildDetails\ChildSCVNumber
Customer			Relationship\ChildDetails\ChildFactCode
Customer			Relationship\ChildDetails\ChildMODSBECode
Customer			Relationship\ChildDetails\ChildStatus
Customer			Relationship\ChildDetails\RelationshipType