Working with Merge/Purge Sold-to Names (MMCS)

Working with Merge/Purge Sold-to Names (MMCS)

Purpose: Merging and purging customer sold-to names allows you to streamline your customer database by consolidating order and sales information from customers that have duplicate match codes (source customers) into a target customer record. Several source customer numbers can point to the same target customer number if they all share a match code. The system selects the oldest customer record as the target customer. This is the customer number that remains on the system when you run the merge/purge.

When you process the merge/purge, the system consolidates information from each source customer with the target customer. In each case, the system attempts to preserve the most current and complete information. The logic that the system uses in performing the merge/purge is discussed later in this topic.

Note: It is recommended that you process the merge/purge for your bill-to customers before your sold-to customer, to avoid unnecessary duplicate bill-to errors.

Where do duplicates come from? Duplicate codes can result when two customers have demographic information close enough to produce identical match codes. For example, Gerald Smith and Geraldine Smith live at the same address, and use the same nickname, Jerry. A duplicate can also occur during Order Entry if a customer uses a variation of his first name on different orders. For example, Jonathan Jones places orders on two occasions and gives Jon as his first name on one order, Jonathan as his first name on another order.

Omitting customers from merge/purge: You can use the delete option at the Work with Source Customers Screen to omit a customer from the merge. Using this option flags the customer’s Status as M, which indicates to omit the customer from future merge/purge lists. To reset all customers flagged with the M status and make them eligible for merge/purge selection again, use the Clearing Customer Sold To Status (MCST) menu option.

Adding records to the merge/purge: This function also allows you to create target and source customers in order to merge customer records that the system would not ordinarily select because the match codes are entirely different.

For example, if customer, Ann Delaney, resumes using her maiden name, Williams, and places an order under that name, you would want to merge Ann Delaney's customer and order history with her new customer record, Ann Williams. In order to do this, you would want to create Ann Williams as the target customer and Ann Delaney as the source customer.

If you have not generated a list of duplicate records to work with, this should be the first option that you perform. Then you can review and change customer sold-to records, if necessary, in preparation for the merge/purge.

E-Commerce: If the Generate E-Commerce Customer Merge Staging Files (H86) system control value is selected, the system generates an E-Commerce Customer Merge Web XML File (CustomerMergeWeb). The web storefront can use this file to review customers that have been merged in Oracle Retail Order Management System and update the customer sold to records for the web.

In this chapter:

• Merge/Purge Customer Sold To Screen

• Submit Generation Window (Generating a List)

- Ghost Customers

- Maximum Number of Source Customers

• Confirm Delete Window (Deleting a List)

• Work with Source Customers Screen

• Create Target Customer Screen

• Creating a Source Customer

• Performing a Merge

• Display Customer Screen

• Merge/Purge Logic

- How are the Customer Tables Updated?

• E-Commerce Customer Merge Web XML File (CustomerMergeWeb)

For more information: See Clearing Customer Sold To Status (MCST).

Merge/Purge Customer Sold To Screen

How to display this screen: Enter MMCS in the Fast Path field at the top of any menu or select the Merge/Purge Customer Sold To option from a menu.

About this screen: This screen will contain no data if you have not generated a listing of customers with duplicate match codes by selecting Generate List. See Submit Generation Window (Generating a List).

If you have already generated a listing, this screen will show the names of target customer records, along with the customer numbers that have duplicate match codes. The duplicate match code customer numbers appear in bold below the target customer name.

Field	Description
Customer #	The number assigned to the target customer; the system appends relevant information from the source customer onto the target customer record. Numeric, 9 positions; optional.
Name	The name or company name of the customer. Customer names display last name first. Company name: alphanumeric, 30 positions; display-only. Customer name: alphanumeric, 25 positions (last name) and 15 positions (first name); display-only.)
Duplicate	The customer number of the duplicate (source) customer record. If there is more than one source customer, the additional customer numbers will display. Numeric, 9 positions; display-only.
Status	The status of a customer record. Valid values are: B = Bill to Duplicate O = Open Enter a valid status code to display records of that status. Optional.

Screen Option	Procedure
Create target customer	Select Create to advance to the Create Target Customer Screen.
Delete a customer sold-to merge/purge record	Select Delete for a customer to delete a customer sold-to record from the merge.
Work with existing duplicates (source customers) or add duplicates	Select Duplicates for a customer to advance to the Work with Source Customers Screen in Change mode. See Creating a Source Customer for information on working with this screen in Add mode.
Change customer sold-to	Select Change cust sold to for a customer to advance to the Display Customer Screen.
Display customer sold-to	Select Display cust sold to for a customer to advance to the Display Customer Screen.
Generate a list of duplicate customer names	Select Generate List to submit this job for processing. See Submit Generation Window (Generating a List). Note: This option displays only if you have authority to the Allow Generate List in Merge/Purge (J06) secured feature.
Delete list of target and source customers	Select Delete list to delete the list. See Confirm Delete Window (Deleting a List).
Merge and purge customers	Select Perform Merge to submit the merge/purge job for processing. See Performing a Merge.

Submit Generation Window (Generating a List)

Purpose: To generate a list of duplicate names eligible for merge/purge, select Generate List at the Merge/Purge Customer Sold To Screen.

Note: The Allow Generate List in Merge/Purge (J06) secured feature controls access to the Generate List option on the Merge/Purge Customer Sold To Screen.

Ghost Customers

Use the Save source as ghost field on this window to indicate whether you want to delete or save source customer records with duplicate match codes once the order and customer history is merged with the target record. The Ghost? field defaults to unselected, but you can change it to Selected.

Records retained: If you select this field, the system will retain the source customer record in the Customer Sold To table, but the record will be flagged as a Ghost customer. All other records, such as alternate customer numbers, phone numbers, email, order history, billing history, mail history and entity history will not exist for the ghost customer, since all other records are merged into the target customer’s records; see Merge/Purge Logic.

Ghost Customer window: When you enter a Ghost customer number in order entry, or when entering a catalog request or customer membership, a pop-up window will inform you that the customer number you entered belongs to a Ghost customer, and will indicate the target customer you can enter instead. See Ghost Customer Number Warning Window.

Ghost customer restrictions: Ghost customers continue to be available for review; however, if you enter a ghost customer number in any maintenance function, the system does not display any history, such as orders or catalog requests, because all history has been merged to the target customer. Additionally, you cannot include a ghost customer as a target customer or source customer in a future merge/purge.

Maximum Number of Source Customers

You can also define the maximum number of source customers you wish to process for a target customer. If you enter a maximum number, the system will complete the merge/purge process once the maximum number has been reached and the update for the target customer the system is currently working on has completed.

When you complete this screen and press Enter, the system submits the batch job M_PGENERAT, and returns you to the Merge/Purge Customer Sold To screen; exit and re-enter to review the generated list.

Confirm Delete Window (Deleting a List)

Purpose: To delete the existing list of target and source customers and clear the Customer Merge/Purge tables, select Delete list at the Merge/Purge Customer Sold To Screen.

Work with Source Customers Screen

Purpose: Once you have generated the list of target and source customers, you can work with the duplicate, or source customers; optionally, you can retain the records of the source customers by flagging them as Ghost?. Use this screen in Add mode to select a source customer for a target customer you added to the merge/purge list using the Create option at the Merge/Purge Customer Sold To Screen; see Creating a Source Customer.

When you delete a customer at this screen, it sets the customer’s Status to M, which indicates not to select the customer for subsequent merge/purge lists. To clear a status of M from any flagged record in the Customer Sold To file, use the Clearing Customer Sold To Status (MCST) menu option. Once you use this menu option to clear all M statuses, the customers are eligible for selection the next time you generate a merge/purge list.

How to display this screen: Select Duplicates for a target customer at the Merge/Purge Customer Sold To Screen.

About this screen: This screen includes each source customer for the target customer, including both matches found by the system and source customers you selected.

Field	Description
Customer #	The number assigned to the source customer; the system appends relevant information from the source customer onto the target customer record. Numeric, 9 positions; optional.
Name	The name or company name of the customer. Customer names display last name first. Company name: alphanumeric, 30 positions; display-only. Customer name: alphanumeric, 25 positions (last name) and 15 positions (first name); display-only.)
Ghost?	A ghost customer is a source customer whose records have been merged into a target customer’s records. Select this field to retain the customer records in the Customer Sold To table for the source customer after you have completed the Merge/Purge. If you retain ghost customer records, the system will flag the records as Ghost customers. When you enter a Ghost customer number in order entry, or when entering a catalog request or customer membership, a pop-up window will inform you that the customer number you entered belongs to a Ghost customer, and will indicate the target customer you can enter instead. See Ghost Customer Number Warning Window. Alphanumeric, 1 position; required.

Screen Option	Procedure
Add a source customer to the merge/purge	Select Create to advance to Add mode. See Creating a Source Customer.
Delete a source customer from the merge/purge	Select Delete for a customer to delete the customer record from the record from the merge/purge and subsequent merge/purges. See Clearing Customer Sold To Status (MCST) for more information.
Change customer sold-to	Select Change cust sold to for a customer to advance to the First Create Sold To Customer Screen.
Display customer sold-to	Select Display cust sold to for a customer to advance to the Display Customer Screen.

Create Target Customer Screen

Purpose: You can create a target customer in order to merge customer records that the system would not normally select because the match codes are entirely different.

How to display this screen: Select Create on the Merge/Purge Customer Sold To Screen.

Field	Description
Target customer	The customer number for the sold-to customer that you wish to add to the merge/purge as a target customer. If you select any source records for this customer (see Creating a Source Customer), the system will consolidate the customer records when you perform the merge/purge. Numeric, 9 positions; required.

Field

Description

Target customer

The customer number for the sold-to customer that you wish to add to the merge/purge as a target customer.

If you select any source records for this customer (see Creating a Source Customer), the system will consolidate the customer records when you perform the merge/purge.

Numeric, 9 positions; required.

Instructions:

1. Complete the Target customer field.

2. You receive a system message informing you that a Customer Merge/Purge Target (Comp #/Cust #) has been created. When you return to the Merge/Purge Customer Sold To Screen, the target customer you just created now appears.

3. To select a source customer and link it to the target customer so the records will be consolidated when you process the merge/purge, see Creating a Source Customer.

Creating a Source Customer

Purpose: Use this option to select a source customer to merge with a target customer that you created manually (through the Create option at the Merge/Purge Customer Sold To Screen). You can also use this option to change source customers for merge/purge records created by the system; see Work with Source Customers Screen.

How to display this screen: Select Duplicates for a target customer record on the Merge/Purge Customer Sold To Screen, then select Create at the Work with Source Customers Screen to advance to Add mode.

About this screen: Each field on this screen is described under Work with Source Customers Screen.

Instructions:

1. Enter the Customer # for each customer record you would like to add as a source customer.

2. Optionally, select Ghost? field for each customer to add.

3. When you return to the Merge/Purge Customer Sold To Screen, the customer number you created as a source customer appears in the Duplicate field below the target customer.

Performing a Merge

Purpose: Use this option to merge the customer and order history of a target customer with a source customer(s) and purge source customer records from the system.

To perform a merge/purge: To merge the order and customer history of the source with the target customer, select Perform Merge at the Merge/Purge Customer Sold To Screen. A message displays announcing that MERGE_PURG job has been submitted.

See Merge/Purge Logic for a discussion of which tables are affected by the merge/purge, and how the system determines which information to keep from the target or source customers.

Display Customer Screen

To display: Select Display cust sold to for a target customer at the Merge/Purge Customer Sold To Screen to advance to the first Display Customer screen. You cannot change anything on this screen.

Merge/Purge Logic

When you process a merge/purge through Working with Merge/Purge Sold-to Names (MMCS), the system attempts to keep the best, most recent and most complete information for the target and source customers consolidated into one customer record. See Understanding Customer Types.

Affected Table	Comments
Alternate Customer # Cross Reference	Adds source records to target.
Catalog Requests	Adds source requests to target.
Correspondence History	Adds source emails to target.
Customer Action	Adds the source information to the target.
Customer Address Change	Adds source address changes to target. See How are the Customer Tables Updated?
Customer Contract Pricing	Adds the source information to the target.
Customer Fraud	Adds the source information to the target.
Customer Note	Adds the source notes to the target.
Customer Ownership	Adds the source information to the target.
Customer Pay Type Exclusion	Adds the source information to the target.
Customer Profile	For any of these fields, if the source has information and the target does not, uses the source information.
Customer Ship To	Merges matching ship-to customers based on match code; adds additional source ship-to customers to target.
Customer Ship To Extended	See How are the Customer Tables Updated?
Customer Ship To Mail History	Adds the source information to the target.
Customer Ship To Order History	Adds the source history to the target history.
Customer Ship-To Phone Number	Uses most recent phone numbers based on change date. See How are the Customer Tables Updated?
Customer Sold To	See How are the Customer Tables Updated?
Customer Sold To Email	Adds source records to target; target’s primary email address does not change.
Customer Sold To Extended	See How are the Customer Tables Updated?
Customer Sold To Item Class	Adds the source information to the target.
Customer Sold To Mail History	Adds the source information to the target.
Customer Sold To Order History	Adds the source history to the target history. See How are the Customer Tables Updated? for more information.
Customer Sold To Phone Number	Uses most recent phone numbers based on change date.
Customer Sold To Promotion	Adds the source information to the target.
Customer Sold To User Fields	If any user field is blank for the target customer but not the source customer, uses the source information.
Customer Subscription	Adds the source information to the target.
Customer Tax	Adds the source information to the target.
Customer Warehouse	Order Management System uses the following logic: • if the source has a customer warehouse and the target does not, use the source customer warehouse. • if the target has a customer warehouse and the source does not, use the target customer warehouse and delete the source customer warehouse.
Customer Warranty	Adds the source records to the target customer.
Order Billing History (both sold-to and ship-to customers)	Adds all target and source dollar and quantity figures together; see below for additional information.
Order Header	Adds the source records to the target.
Order Ship To	Adds the source records to the target.
Pick Control Header	Adds the source records to the target.
Pick Gen Select Customer	Adds the source records to the target.
Pick Gen Select PO	Adds the source records to the target.
PO Header	Adds the source records to the target.
Promotion Customer	Adds the source records to the target.
Store Pickup Header	Adds the source records to the target. Not currently implemented.
Tickler	Adds the source records to the target.
Tickler History	Adds the source records to the target.

How are the Customer Tables Updated?

Field	How Updated?
Address information:
Prefix First name Middle initial Last Name Suffix Company name Street Address Apartment Address line 2 Address line 3 Address line 4 City State Zip Delivery code PO Box? Phone numbers Match code User fields 1-4	The system decides which information to keep based on the Change date from the most recent record in the Customer Address Change table and the Last order date in the Customer Order History table. • If the Last Change date for the source customer is more recent than the last Change date for the target customer, the system uses the source address information and creates a customer address change record for the target. • If there is no last Change date for either the target or the source, the system compares the target and source Last order date. If the Last order date for the source customer is more recent than the target customer, the system uses the source address information and creates a customer address change record for the target, as above. Prefix: The system does not validate the target prefix against the Prefix table, regardless of the setting of the Validate Prefix (I27) system control value. See Working with Prefix Codes (WPFX) for an overview. Phone numbers: If the system uses the source address information and one or more of the phone numbers for the source is blank, the system will retain the target phone number instead of removing it. Example: If the source has a day number, but not an evening number, the system will use the source day number, but retain the target evening number.
Primary email address	Uses the target’s primary email address; however, the source’s email addresses are added to the target’s list.
Additional fields:
Entry date	Compares target and source Entry date and uses earlier date.
Original mail type	If the target's original mail type is not B (buyer), uses the source's original mail type.
Current mail type	If the target has no order history (the Last order date field is blank), uses the source's current mail type.
Original source code	If the target is newer than the source based on the Entry date, uses the source information. However, if the source has the older Entry date but no original source code, then the target’s original source code is retained.
Customer class	If the target is newer than the source based on the Active since date, uses the source information. (The Active since date is filled when the customer first places an order; if the customer has never placed an order, the Active since date is set to 99/99/99).
Mail Rent Seed	If any of these flags are selected for either the target or the source, sets the flag to selected.
Hold/bypass/fraud Customer type Discount percent Price column Pop-up windows Alternate customer number Salesman number Country Mail code Call code Pop-up windows VAT number Currency code Language code Price Group Reserve warehouse Van Route Van Sequence #	For any of these fields, if the source has information in the field and the target does not, uses the source information; otherwise, use the target information.
Tax exempt status Exemption certificate number	If the source has a certificate number and the target does not, uses the certificate number and tax exempt status.
Track item history Item history tracking level	If the Track item history field is set to Sold to tracking or Sold to/ship to tracking for the source but not the target, uses the Item history tracking setting from the source.
Current source code	Uses the last source code used by either the source or the target from the Customer Sold To Order History table.
External sales amount External sales update date	Uses the target customer’s External sales amount and External sales update date unless the source customer’s External sales update date is more recent, or the target customer has no external sales information; in either of these cases, the target customer record is updated with the source customer’s external sales information.
Inactive flag Commercial flag Bypass reservation flag Auto cancel backorder flag Associate flag Bill-to customer number	Uses the target information.
Life to date warranty dollars	If the source customer has a dollar amount defined and the target customer does not, the system uses the dollar amount defined for the source customer. If a value has been defined in the Life to date warranty dollars field in the Customer Sold To Order History table for both the source customer and the target customer, the system adds the two values together.
Relate Id	If: • the target customer has a Relate Id, do not change it, regardless of whether a source customer also has a Relate Id. • the source customer has a Relate Id and the target does not, the system uses the source customer’s Relate Id. • multiple source customers have Relate Ids and the target does not, the system uses the Relate Id for the first customer who has one.
Synchronize with remote	If any of the source customers have this flag set to Y, the system sets this flag to Y for the target customer.

E-Commerce Customer Merge Web XML File (CustomerMergeWeb)

Purpose: The E-Commerce Customer Merge Web XML file (CustomerMergeWeb) contains customer sold to merge information as a result of Performing a Merge in Working with Merge/Purge Sold-to Names (MMCS). The system generates this file if the Generate E-Commerce Customer Merge Staging Files (H86) system control value is selected, based on records in the EC Cust Sold To Merge table. Once the system generates the CustomerMergeWeb file, the system clears the records in the EC Cust Sold To Merge table.

Location of file: The ECOMMERCE_DIRECTORY_PATH in Working with Cloud Properties (CPRP) defines the location on the application server where the system downloads the E-Commerce Customer Merge Web XML file. An example of the directory is /domain/conf/OMSFiles/WebData/, where domain is the WebLogic domain directory for Order Management System. Note: If the ECOMMERCE_DIRECTORY_PATH is not valid or is blank, or the specified folder does not exist, the system does not generate the CustomerMergeWeb file and writes an error message to the Application log.

Name of file: The name of the file is CustomerMergeWeb_999_YYMMDDHHMMSS.xml, where 999 is the company code where you processed the customer merge and YYMMDD is the date and time when the file was created.

Application log: The system writes any messages related to the customer merge download to the Application Log.

Sample message: See Sample E-Commerce Customer Merge Web XML Message (CustomerMergeWeb) for a sample message.

Attribute Name	Type	Length	Comments
CustomerMerges
CompanyCode	numeric	3	A code for the company where the customer sold to merge was processed. From ECO Company Code in the EC Cust Sold To Merge table.
CustomerMerge A separate row exists for each duplicate (source) customer that is merged with a target customer.
SourceCustSoldTo	numeric	9	The customer number of the duplicate (source) customer record. From ESM Source Cust Sold To in the EC Cust Sold To Merge table.
TargetCustSoldTo	numeric	9	The number assigned to the target customer; the system appends relevant information from the source customer onto the target customer record. See Merge/Purge Logic for more information on how the system consolidates the customer information into the target customer. From ESM Target Cust Sold To in the EC Cust Sold To Merge table.

Sample E-Commerce Customer Merge Web XML Message (CustomerMergeWeb)

A sample of the E-Commerce Customer Merge Web XML message (CustomerMergeWeb) is presented below.

</CustomerMerges>

Using House List Options

Contents

SCVs

Search

Glossary

Reports

Solutions

XML

Index

Clearing Customer Sold To Status (MCST)

MMCS OMS 17.1 September 2018 OTN