Capturing and Cleaning Leads

This chapter covers the following topics:

Leads from Multiple Sources

Leads are captured into Oracle Leads Management from various sources. The primary sources are:

Oracle Scripting

To develop a personalized relationship with customers, marketing organizations use the branching functionality in Oracle Scripting. The branching functionality responds differently to the input of customers based on their profiles or the answers that they provide to questions. When a customer expresses interest in a product, a lead is created and managed by Oracle Leads Management.

Oracle Scripting contains seeded scripts focused on various marketing activities. These scripts can be used with minimum configuration and can be deployed as call-center scripts or web surveys. In addition to simplifying processes, scripts can help to ensure communication consistency. For more information on implementing seeded scripts, see the Oracle Marketing Implementation Guide and the Oracle Scripting Implementation Guide.

Oracle iStore

Customers who use Oracle iStore for purchases can be mined in as leads for a cross-sell or an up-sell. Also, when customers abandon a shopping cart before making the final purchase, leads are created from such records, and followed up.

Marketing Campaigns

The marketing department may run several campaigns, and capture leads. These leads may be imported into Oracle Leads Management from a .csv, .txt or a flat file. For more information, see Importing Leads. The marketing campaign generates leads based on the Installbase.

Interactions

An interaction is a record of communication between a potential customer and a company representative. An interaction is generally timed and has an outcome or result that can be tracked. These interactions are tracked and leads are created from them.

Partner Referrals

The Referral Management feature in Oracle Partner Management allows a partner to submit referrals to the vendor. After the vendor accepts the referrals, the referral becomes a lead. For more information about Referral Management, see the Oracle Partner Management Partner User Guide.

The Lead Import Process

Importing leads is one of the sources to capture leads into Oracle Leads Management. Figure: The Lead Import Process depicts the methods of importing the leads and the processing that takes place after they are imported.

The Lead Import Process

the picture is described in the document text

You can import leads into Oracle Leads Management in two ways:

The imported records are stored in the AS_IMPORT_INTERFACE table. This table is an intermediary table that stages all lead records before they are refined and cleaned.

The Import Sales Lead concurrent program picks every lead from the AS_IMPORT_INTERFACE table, and runs it through Data Quality Management (DQM) to identify unique customer records, and through the Leads Deduplication rule to remove duplicate lead records.

All unique customer records identified by the DQM process are stored in the Trading Community Architecture (TCA) database. This database is a central repository that is accessible to all Oracle's E-Business Suite and ERP applications.

All unique lead records that are identified by the Leads Deduplication process are stored in the AS_SALES_LEAD table.

Importing Leads

Leads may be imported from a .csv, .txt, or from a flat file.

Topics in this section include:

Importing leads from the HTML Interface

You can import leads stored in a .csv or .txt file using the Import wizard from the HTML interface. Use the following procedure to import leads.

Prerequisite: A .csv or .txt file containing data for import is required.

Navigation: Log in as an administrator, and navigate to Audience Dashboard > Import.

Notes:

Evaluating Errors During Lead Import

The Import Sales Lead concurrent program stores errors that occur during lead import in the AS_LEAD_IMPORT_ERRORS table.

Table: Status and Descriptions for Lead Import Errors in the HTML InterfaceStatus and Descriptions for Lead Import Errors in the HTML Interface gives the status and descriptions for the lead import errors that you can see in the HTML interface.

Status and Descriptions for Lead Import Errors in the HTML Interface
Status Description
Complete All records are complete/successful.
Incomplete - Errors Found One or more records have errors.
Incomplete - Duplicates Found One or more records are duplicates.
Incomplete - Duplicates and Errors Found One or more duplicates AND one or more errors found.
Error All records have errors.
Duplicate All records are duplicates.

Note: Do not change the status of an imported lead from Success to New. A status of Success means that a lead has been successfully imported and cannot be imported again.

Correcting Errors During Lead Import

To check for errors detected by the Import Sales Lead concurrent program during lead import, and correct them, as a prerequisite, you must have imported records into the AS_IMPORT_INTERFACE table.

Navigation: Log in as an administrator, and navigate to Audience Dashboard > Import.

Notes:

Note: When you modify any of the details for a record, the original record is not updated. Instead, a new record is created and updated with the modifications.

Importing Leads From a Flat File

The flat file you are importing leads from must be a tilde-delimited file. The file name must have the extension .dat (for example, mynewleads.dat). Note the following points:

There are two ways to import leads into the AS_IMPORT_INTERFACE table from a flat file.

Importing Leads by Running the Concurrent Program

Use the following details to run the Load Sales Lead Interface Table from Flat File concurrent program.

Prerequisite: FTP the tilde(~) delimited flat file with lead data to a directory on the server. The file must have the .dat extension.

Responsibility: Oracle Sales Administrator

Name of program: Load Sales Lead Interface Table from Flat File

Parameter:

P_DATAFILE - Name of the .dat file

Schedule - Once

For steps to run the concurrent program, see Running Concurrent Programs.

Note: The Load Sales Lead Interface Table from Flat File concurrent program supports only loading the AS_IMPORT_INTERFACE table. To take advantage of other interface tables, you must write your own program to populate them.

Related Topics

Importing Leads by Running SQL Loader Manually

If you have access to Oracle SQL Loader, you can import details from the flat file without running the Load Sales Lead Interface Table from Flat File concurrent program. You must upload the flat file to a server, and create the SQL Loader file which will import the records from the flat file.

Prerequisites:

  1. FTP the tilde(~) delimited flat file with lead data to a directory on the server. The file must have the .dat extension.

  1. Create an Oracle SQL Loader parameter file. Here is what a sample SQL Loader parameter file will look like:

    userid=<username>/<password>


    control=ASTSLIMP.CTL


    data=<path><lead import data file name>.dat

  2. Upload lead records using this parameter file as command line parameter to SQL Loader.

Records from the flat file are imported and processed by the Import Sales Lead concurrent program.

Sample Flat File

Below is a sample flat file for loading the AS_IMPORT_INTERFACE table. This example contains only one line of data.

~16-Sep-01~-1~16-Sep-01~-1~-1~LEAD_LOAD~16-Sep-01~NEW~ABC Corp~US~123 Xyzst.~Suite1008~~~RedwoodCity~94065~CA~~~7374~2000~CUSTOMER~MAR~15000~5000000~6000000~DECISIONMAKER~1023472~1900~N~M~MR~~Hislast~Hisfirst~A~AccountsPayableSupervisor~ARC~101~926-2667~GEN~650~123~926-2600~650~www.xyz.com~abc@xyz.com~Y~Y~N~N~NEW~DECISION_MAKER~DIRECT~10000~APPROVED~US~1-3MONTHS~~AAA~Lead1~EMAIL~159~424~425~357~204~EA~100~50000~10588~10699~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Leadcollectedon16-SEP-01~~NEW~10001~OTN::990~10004~Y~Y~~~~~~N~Y~Y~~~~10060~Y~1-Jan-00~~~USERENTERED~XYZHQ~~~10~~~~~~94065-1282~~~HQ~~~~1008~~~~~~~~XYZ~~~~~~15-Aug-01~~~~~~~~~~N~1987SIC~~101~550000~~~N~MARKET~~10~~~Importantcontact~~Y~InformationTechnology~IT~DECISION_MAKER~10588~N~Y~USER_ENTERED~1~1~~~~MAILHTML~123~~~~~~~~~~~TSTENH

Flexfields

Use the AS_IMP_SL_FLEX table to store the flexfield values for all the following entities (tables). The entity names are seeded in AS_LOOKUPS, lookup_type = ENTITY_NAME.

The flexfields are imported along with the other data in the AS_IMPORT_INTERFACE table during the lead import process. To populate the data in the optional tables, use SQL*Loader or SQLPLUS.

The flexfields columns in HZ_ORG_CONTACT_ROLES, the global flexfields columns in HZ_PARTIES, HZ_LOCATIONS, HZ_CONTACT_POINTS, and HZ_ORG_CONTACTS are obsoleted. Hence, the Import Sales Lead concurrent program does not support these columns. For information about how to plan and set up flexfields, see the Oracle Applications Flexfields Guide.

Imported Leads and Oracle Sales Tables

Figure: Leads Data Imported into Oracle Sales Tablesshows how lead information is imported into Oracle Sales tables.

From the feeder system, imported leads are stored in the interface tables. These are the AS_IMPORT_INTERFACE, AS_IMP_CNT_PNT_INTERFACE, AS_IMP_CNT_ROL_INTERFACE, AS_IMP_LINES_INTERFACE, and the AS_IMP_SL_FLEX tables.

After the Import Sales Lead concurrent program is run, appropriate records are created in the TCA database, Oracle Sales tables, and imported records that resulted in errors are stored in the AS_LEAD_IMPORT_ERRORS table.

Leads Data Imported into Oracle Sales Tables

the picture is described in the document text

The Import Sales Lead Concurrent Program

The Import Sales Lead concurrent program runs every record in the AS_IMPORT_INTERFACE table through the DQM and Leads deduplication processes. The TCA database is updated with any unique customer records. Unique lead records are stored in the AS_SALES_LEADS table. The concurrent program calls the leads processing engines to filter, qualify, rate and channel these leads to the sales teams.

To set up, manage, and run the concurrent program, follow the procedures in these sections:

Import Sales Lead Concurrent Program Flow

Figure: Import Sales Lead Concurrent Program Flow illustrates the manner and sequence in which the Import Sales Lead concurrent program processes records.

Import Sales Lead Concurrent Program Flow

the picture is described in the document text

The Import Sales Lead concurrent program does the following:

  1. Checks for the existence of Original System Reference (OSR) using Leads Data Quality. See Checking for Duplicate Original System Reference.

  2. Checks for the existence of customer, address, contact, and contact points using DQM Data Quality. See Customer Data Quality.

  3. Creates a record in the TCA database, if the imported record is unique.

  4. Checks for duplicate leads using Leads Data Quality. See Leads Data Quality.

  5. Creates a lead in the AS_SALES_LEAD table, if the imported record is unique.

  6. Qualifies and ranks the lead using the Leads Processing Engine.

  7. Identifies the sales team, and assigns the lead to the owner of the sales team.

  8. Creates a sales team to interact with the customer (the lead's organization), if required.

Import Lead Inactive Parties Flow

Figure: Lead Import - Inactive Parties Flow  illustrates the manner and sequence in which the Import Lead concurrent program activates 'Inactive' records.

Import Lead Inactive Parties Flow

the picture is described in the document text

Lead import will consider 'Inactive' parties returned by DQM search to create a lead. If the selected party is ‘Inactive’ lead import will call TCA API to activate it. This is controlled by the profile 'OS: Activate inactive parties from lead import' as some customers might not want 'Inactivate' parties to get activated by the Lead Import program.

Before Running the Concurrent Program

The procedures in the following sections must be complete before running the Import Sales Lead concurrent program:

Setting Up DQM Match Rules

All customer records are stored in the TCA database. DQM checks if a customer already exists in the TCA database. If a customer in the import record already exists in TCA, DQM returns the party_id of the customer. If not, the Import Sales Lead concurrent program creates a new customer record.

DQM also returns 'Inactive' party records from the search. Lead import considers these 'Inactive' party records and creates leads against them and also activates the party. Lead import calls TCA API to activate the same. This is controlled by the profile 'OS: Activate inactive parties from lead import'.

DQM checks the following attributes to identify unique records - Customers, Addresses, Contacts, and Contact Points.

DQM uses the matching rules to decide if a customer record exists in TCA. You can create a rule based on the business requirements in your organization.

The DQM match rules that will be used are dependent on the following profiles:

Use the following procedure to create a sample DQM rule to find duplicate contact records. The procedure is based on the sample rule explained in Identify Duplicate Contacts .

Navigation: Log in to Oracle Forms with the Trading Community Manager responsibility and navigate to Data Quality Management > Setup > Match Rules.

Notes

Related Topics

For other sample matching rules, see Designing Matching Rules to Detect Duplicate Customer or Person.

Setting Profiles Used by the Import Sales Lead Concurrent Program

Because the Import Sales Lead concurrent program triggers other programs as part of its flow, the following profiles must be set before it is run.

Prerequisites: Create DQM matching rules.

Navigation: Log in to Oracle Forms with the System Administrator responsibility and navigate to Profile > System > Open.

Notes

Creating Valid Lookup Codes

Lookup codes map to drop-down lists in the User Interface. The SOURCE_SYSTEM lookup type identifies the source of the leads. For example, lead sources could be from a marketing campaign or a partner referral.

The SOURCE_SYSTEM lookup type categorizes the leads in the system, and helps you to track them. Seeded values in the SOURCE_SYSTEM lookup type are Interaction, Marketing, New, Referral, Sales_Campaign, Store, and User.

Navigation: Log in to Oracle Forms with the Oracle Sales Administrator responsibility and navigate to Oracle Sales Setup > Lookup Codes > Sales and select View > Query By Example > Enter.

Notes

Setting up Territories

A territory refers to the geographical location of a lead and a sales team. Setting up a territory is important so that the lead is assigned to the right sales team in the correct geographical location.

Create territories in the Oracle Sales and TeleSales node on the territory setup form of Territory Manager. Territory Manager is part of the CRM Foundation module.

Territory Rule Refresh Concurrent Program

Run the Territory Rule Refresh concurrent program. This concurrent program builds the API that returns the winning territories which are defined in territory setup. Run the program at least once before you import leads and every time after the territory setup is modified. You need not run this program every time you import leads. See Oracle Territory Management Implementation Guide.

Loading the Import Interface Tables

Load the AS_IMPORT_INTERFACE table before running the Import Sales Lead concurrent program

AS_IMPORT_INTERFACE (mandatory): This interface table holds sales leads, customers, addresses, and contacts information to be imported. This table also holds space to import five lead lines in one record.

The following are auxiliary tables. Load data into these tables using a custom program.

Running the Import Sales Lead Concurrent Program

The Import Sales Lead concurrent program must be scheduled to run at particular intervals. As a result of the concurrent program, unique leads are stored in the AS_SALES_LEAD table, and if any of these lead records are unique to the TCA database, they are added to it.

Use the following details to run the Import Sales Lead concurrent program.

Prerequisite: Complete all tasks covered in Before Running the Concurrent Program.

Responsibility: Oracle Sales Administrator

Parameters:

Schedule: Periodically

For the steps to run the concurrent program, see Running Concurrent Programs.

Import Sales Lead Concurrent Program Parameters

The following table lists the parameters for the Import Sales Lead concurrent program.

Import Sales Lead Concurrent Program Parameters
Parameter Req? Lookup Remarks
Lead Source System Y SOURCE_SYSTEM Used to identify leads generated from different business entities.
Only the records that match the parameter value are selected for processing. This is case-sensitive.
Debug message? N Y or N Default is N. If set to Y, the debug messages can be seen by clicking View Log in the Concurrent Request screen.
BatchID N - Used to process a small set of data. This is particularly useful when leads are imported in batches. The Batch ID may be used to process only a particular set of data in a batch.
Purge error message? N Y or N Default is N. If set to Y, all records in the AS_LEAD_IMPORT_ERRORS table are deleted.

Note: The Import Sales Lead concurrent program validates currency codes from the FND_CURRENCIES table while on the HTML UI, the currency codes are picked from the AS_LOOKUP table (lookup type = REPORTING_CURRENCY). The currency codes in both the places must be synchronized to import a lead successfully. If the currency code is not found in the FND_CURRENCIES table, then the currency value set in the JTF_Profile_Default_Currency profile is used.

Important: Users must run the full synchronization program after running the Import Sales Leads concurrent program.

Enhancing the Performance of the Import Sales Lead Concurrent Program

The Import Sales Lead concurrent program processes a number of records in a batch which may be time consuming. The performance of the concurrent program has been improved by running multiple concurrent programs in parallel - each processing fewer number of records.

Parallel Lead Import

In order to improve the performance, the Import Sales Lead concurrent program, itself being the parent, spawns multiple child requests to process the imported records in the AS_IMPORT_INTERFACE table. Since the child requests run in parallel, significant performance improvement is achieved. The concurrent program raises the Lead Import - Pre event before spawning child processes and then raises Lead Import - Post event after all the child processes are complete. For more information on the Pre and Post events, see Seeded Business Events.

The Import Sales Lead concurrent program splits into a parent process and multiple child processes. Figure: Parent and Child Processes illustrates the relationship between the parent and child processes.

Parent and Child Processes

the picture is described in the document text

The OS:Minimum Number of Records for Parallel Processing in Lead Import profile governs the number of child processes that are spawned. For each n number of records in the AS_IMPORT_INTERFACE table, a new request is placed. The default value for this profile is 400.

Limitation of the Import Sales Lead Concurrent Program

The Import Sales Lead concurrent program checks the database for duplicates using the DQM logic of customer, address, contact, and contact point before creating new records. However, the DQM logic has one limitation.

While importing leads, if the concurrent program creates new records such as Party, Contact, Party Site and Contact Points, the new entries are not reflected in the DQM staging schema. Therefore, if the same set of leads is imported again without any changes, the DQM logic will fail causing the Lead deduplication program to fail as well. To overcome this, the DQM Synchronization concurrent program must be run after the first import and before the next import.

However, lead deduplication can still fail if there are duplicate leads in a single set of imported records. See Custom User Hook to avoid this.

Running the DQM Synchronization Program

Use the following details to run the DQM Synchronization Program concurrent program.

Responsibility: Trading Community Manager

Schedule: Once

For the steps to run the concurrent program, see Running Concurrent Programs.

Data Quality

An information system is only as good as the data which resides within it. In Oracle Leads Management, any lead records that are imported go through rigorous screening and filtering. The records are checked for:

Table: Checking for Data Quality in imported Lead Records gives the sequence in which the Customer and Leads data quality checks are performed.

Checking for Data Quality in imported Lead Records
Task Performed Program
Checking for Duplicate Original System Reference Import Sales Lead concurrent program
Checking for Duplicate Customers Import Sales Lead concurrent program using DQM
Checking for Duplicate Addresses Import Sales Lead concurrent program using DQM
Checking for Contacts and Contact Points Import Sales Lead concurrent program using DQM
Leads Data Quality Import Sales Lead concurrent program using the Lead Deduplication Rule

Customer Data Quality

The Import Sales Lead concurrent program uses the rule-based DQM tool to identify existence of customer records in the TCA database. It uses customer entities like parties (both organization and person), party site, contacts and contact points information to match a record.

When a lead record is imported, it is important to find if a record for this customer already exists in your database. The DQM program matches the imported record with the records in the TCA database to find a matching customer record. If a match is not found, a customer record is created by the Import Sales Lead concurrent program in the TCA registry database.

The DQM program uses rules to identify a matching record. The rules that are used are dependent upon the profiles that are set. See Setting Up DQM Match Rules and "Setting Profiles Used by the Import Sales Lead Concurrent Program.

For more information on DQM, see the Oracle Trading Community Architecture Data Quality Management User Guide.

Checking for Duplicate Original System Reference

When leads are imported from a list generated by a third-party data source, each entry carries the ID of the party record in that third party database. This ID is referred to as Original System Reference (OSR).

The OSR is carried over to the party record in TCA thus maintaining a correlation between the TCA Party ID recorded on the lead and the party record in the external data source. If the existing party in TCA has a different OSR, the record is updated with the latest one.

OSR is not included in the list of attributes checked by DQM. Therefore, the OSR existence checking is done by the Import Sales Lead concurrent program itself.

Figure: OSR Logic Used by Import Sales Lead Concurrent Program gives the logic followed to check for duplicate OSR before DQM starts matching party and address.

OSR Logic Used by Import Sales Lead Concurrent Program

the picture is described in the document text

If the OSR is available in the import record, then the Import Sales Lead concurrent program uses it to find a set of parties with the same OSR in the TCA database. If a matching set is found, the DQM matching rules are applied on this set alone to find a matching party.

If OSR is available and no matching party set is found or if the OSR itself is not available in the import record, then the DQM matching rules are applied to all the records in the TCA database to find a matching party.

If a matching party is found by using the DQM matching rules, then the matched party_id is reused. If a matching party is not found, the Import Sales Lead concurrent program creates a new party.

Note: It is recommended that you pass OSR in an import record, if known. This substantially improves the performance of the Import Sales Lead concurrent program.

Checking for Duplicate Customers

After the Import Sales Lead concurrent program checks for the existence of OSR, the DQM program starts checking for a matching customer in the TCA database. Depending on whether the import record is an organization or a person, the matching rule created to identify duplicate Party or Person is used.

The Import Sales Lead concurrent program calls the HZ_PARTY_SEARCH.FIND_PARTIES API to run the rules that find duplicate customers. The Organization name (Party) or first name and last name (Person) is passed in along with the address-related information in the party site record. If the lead is created for Organization, the contact information is also passed in to find a better match on the party. If the lead is created for Person, the contact information is not passed in.

The API call returns the context ID and the number of matches found. The parties are returned and populated in the HZ_MATCHED_PARTIES_GT table sorted on score. If the number of matches found is greater than zero, the context ID is used to get the match details. The highest score will have the best match. If multiple parties with the same high scores are found, the party that was created last is picked up.

Checking for Duplicate Addresses

When a matching party is identified, the addresses between the import record and matching party are compared to see if they match as well.

The get_matching_party_sites API is called to check if the matching address exists. The party sites are returned and populated in the HZ_MATCHED_PARTY_SITES_GT table. If a match is found, the existing location ID and party site ID are used. If no match is found, the location and party site in the imported record are used.

Checking for Contacts and Contact Points

If the lead is created for Person and an existing party is found, the contact information is checked. The matching rule created to identify duplicate contacts is used. The party ID of the person along with the contact points are passed while calling the get_matching_contact_points API. The matched contact points are found in the HZ_MATCHED_CPTS_GT table.

If the lead is created for an Organization and there is no contact Person found for the Organization, a search is performed to find a matching contact Person existing in the database. In order to find a match, the contact person's first name, last name and contact points are passed in while calling the HZ_PARTY_SEARCH.FIND_PARTIES API.

To find a duplicate contact person, you must create a new rule which takes the details of the contact person name and the contact points.

Figure: How DQM Checks for Duplicate Entities  depicts how the DQM program checks for duplicate entities in the database.

How DQM Checks for Duplicate Entities

the picture is described in the document text

Setting Up DQM Staging Schema

Use the DQM Staging Program to create the staged schema and interMedia indexes. This program applies transformation functions to a portion of the data contained in the TCA registry and generates a separate schema with the transformed and standardized data. The time that the program takes to create the staged schema depends on the size of your database and the number of attributes and transformation functions that you defined.

Reference

Oracle Trading Community Architecture Data Quality Management User Guide

Use the following details to run the DQM Staging program concurrent program.

Prerequisites: Define attributes and transformation functions in DQM. For sample transformation functions, see Designing Matching Rules to Detect Duplicate Customer or Person.

Responsibility: Trading Community Manager

Parameters:

For the steps to run the concurrent program, see Running Concurrent Programs.

Note: Run the DQM Staging Program every time you add or modify the attributes or the transformation functions.

Designing Matching Rules to Detect Duplicate Customer or Person

You need three DQM match rules for customer or person existence checking during the lead import process. Matches are initially identified using the Acquisition Attributes, and a score is assigned to each match based on the scoring attributes. The party with the highest score is matched with the import lead record.

Use the following as samples while designing DQM matching rules for the Import Sales Lead concurrent program. Matching rules can also be tailored according to specific requirements using other seeded and custom attributes. For more detailed information, see the Oracle Trading Community Architecture Data Quality Management User Guide. For steps to create the sample rules, see Setting Up DQM Match Rules.

Identify Duplicate Parties and Party Sites

The first match rule is used to identify the organization party and address. It is assigned to the profile OS:Use DQM Rule Code to Match Party.

Use the following sample rule to identify existence of party (Organization) and addresses for the same. Because this rule is also used to identify the existence of addresses, it must contain PARTY_SITES entity attributes as well as PARTY entity attributes.

Match Rule Name: LEAD_DUP_ORG

Description: Finds identical organization parties based on Party Name and Address information.

Purpose: Identify duplicate organizations and addresses.

Table: Existence Checking for Party: Acquisition Attributes, Table: Existence Checking for Party: Matching Attributes, and Table: Existence Checking for Party: Scoring Attributes list the attributes for this rule.

Existence Checking for Party: Acquisition Attributes
Acquisition Attributes Entity Transformation Function Description Type
Party Type PARTY EXACT Catches format errors Lookup
Party Name PARTY WR NAMES + CLEANSE Captures the exact string, removes non-alphanumeric characters, forces upper case, removes vowels, and double letters -
State PARTY_SITES WR STATE Word replacement -
Country PARTY_SITES EXACT Captures the exact string, removes non-alphanumeric characters, forces upper case, and catches format errors. Lookup
CONTACT NAME CONTACTS WR+CLEANSE+REVERSE Word replacement of Person and Organization names, removes vowels and double letters, reorders first word to the back. Custom Attribute
Existence Checking for Party: Matching Attributes
Attribute Match Match Threshold Override Threshold Automatic Merge Threshold
Match Any 120 <null> <null>
Existence Checking for Party: Scoring Attributes
Scoring Attribute Entity Score Transformation Function Description Type Weight(%)
Party Name PARTY 100 EXACT STRING Captures the exact string, removes non-alphanumeric characters, and forces upper case. - 100
- - - WR CLEANSE & REVERSE Word replacement of Person and Organization names, removes vowels and double letters, reorders first word to the back. - 90
Address PARTY_SITES 20 EXACT Removes non-alphanumeric characters and forces upper case. Custom Attribute 100
- - - WR ADDRESS + CLEANSE Address with word replacement, and removes vowels and double letters. - 90
Postal Code PARTY_SITES 20 EXACT Removes non-alphanumeric characters and forces upper case. - 100
State PARTY_SITES 20 WR STATE State word replacements   100
Contact Name CONTACTS 40 EXACT STRING Captures the exact string, removes non-alphanumeric characters, and forces upper case. Custom Attribute 100
- - - WR CLEANSE + REVERSE Word replacement of Person and Organization names, removes vowels and double letters, reorders first word to the back. - 90

Apart from the specified attributes in the above sample matching rule, more party (organization) related attributes like DUNS Number, Tax Reference, and SIC Code can be specified as per custom requirements.

Identify Duplicate Persons

The second match rule is used to identify the person and address. It is assigned to the profile OS:Use DQM Rule Code to Match Person.

Use the following sample rule to identify existence of party (Person) and addresses. Because Party Type is an Acquisition attribute, the above sample matching rule can also be used for Person existence checking. You may add more Person-related attributes to the matching rule as per custom requirements. The PARTY_SITES entity attributes must be specified because the same rule is used to identify existence of addresses for a specified person.

Match Rule Name: LEAD_DUP_PERSON

Description: Finds duplicate persons based on Person Name & Address Information

Purpose: To identify duplicate persons and addresses

The following tables list the attributes for this rule.

Existence Checking for Person: Acquisition Attributes
Acquisition Attributes Entity Transformation Function Description Type
Party Name PARTY WORD REPLACE + CLEANSE + REVERSE Word replacement of Person and Organization names, removes vowels and double letters, reorders first word to the back. -
State PARTY_SITES WORD REPLACE + EXACT Word replacement of Person and Organization names, removes non-alphanumeric characters, and forces upper case. -
Country PARTY_SITES EXACT Removes non-alphanumeric characters, and forces upper case. Lookup
Existence Checking for Person: Matching Attributes
Attribute Match Match Threshold Override Threshold Automatic Merge Threshold
Match All 130 <null> <null>
Existence Checking for Person: Scoring Attributes
Scoring Attribute Entity Score Transformation Function Description Type Weight(%)
Party Name PARTY 100 EXACT_STRING Captures the exact string, removes non-alphanumeric characters, and forces upper case. - 100
- - - WR CLEANSE & REVERSE Word replacement of Person and Organization names, removes vowels and double letters, reorders first word to the back. - 90
Address PARTY_SITES 30 EXACT Removes non-alphanumeric characters, and forces upper case. Custom Attribute 100
- - - WR ADDRESS + CLEANSE Word replacement of Person and Organization names, removes vowels and double letters. - 90
Postal Code PARTY_SITES 10 EXACT Removes non-alphanumeric characters, and forces upper case. - 100
State PARTY_SITES 10 WORD REPLACE + CLEANSE Word replacement of State name, removes vowels and double letters. - 100

Identify Duplicate Contacts

The third match rule is used to identify a contact of the organization with relationship type of Contact of only. No other relationships types (such as Employee Of, Consumer Of) are considered. This rule is assigned to the profile OS:Use DQM Rule Code To Match Contact.

Use the following sample matching rule to identify duplicate Contacts and Contact Points like e-mail, phone number, and URL. Because the same rule is used to identify Contact Points, specify the CONTACT_POINTS entity attributes while designing the matching rule for the identification of contacts.

Match Rule Name: LEAD_DUP_CONTACT

Description: Finds identical contacts based on Contact Name and Contact Points.

Purpose: To identify duplicate contact and contact points

The following tables list the attributes for this rule.

Existence Checking for Contact: Acquisition Attributes
Acquisition Attributes Entity Transformation Function Description Type
Party Name PARTY WORD REPLACE + CLEANSE + REVERSE Word replacement of Person and Organization names, removes vowels and double letters, reorders first word to the back. -
Contact Name CONTACTS WORD REPLACE + CLEANSE + REVERSE Word replacement of Person and Organization names, removes vowels and double letters, reorders first word to the back. Custom Attribute
Phone Number Flexible Format CONTACT_POINTS EXACT Removes non-alphanumeric characters and white spaces. Custom Attribute
Existence Checking for Contact: Matching Attributes
Attribute Match Match Threshold Override Threshold Automatic Merge Threshold
Match All 110 <null> <null>
Existence Checking for Contact: Scoring Attributes
Scoring Attribute Entity Score Transformation Function Description Type Weight(%)
Contact Name CONTACTS 100 EXACT Captures the exact string, removes non-alphanumeric characters, and forces upper case Custom Attribute 100
- - - WORD REPLACE + CLEANSE + REVERSE Word replacement of Person and Organization names, removes vowels and double letters, reorders first word to the back. - 90
E-mail Address CONTACT_POINTS 10 EXACT (E-mail) Forces uppercase. - 100
- - - CLEANSE (E-mail) Removes vowels and double letters.   90
Phone Number Flexible Format CONTACT_POINTS 10 EXACT Removes non-alphanumeric characters and white spaces. - 100
URL CONTACT_POINTS 10 CLEANSE (URL) Removes non-alphanumeric characters, white spaces, vowels and double letters. - 100

Note: For existence checking of Party (Organization & Person), Addresses, Contact and Contact Points, the Import Sales Lead concurrent program solely depends on the results returned by the DQM matching rules. The more effective the matching rule, the more precise the result.

Leads Data Quality

When lead records are imported, they are temporarily stored in the AS_IMPORT_INTERFACE table. After the records are processed for lead data quality, unique records are transferred to the AS_SALES_LEAD table.

The Import Sales Lead concurrent program uses the Deduplication Rule to identify duplicate lead records between the AS_IMPORT_INTERFACE table and the existing leads in the AS_SALES_LEAD table. The lead is identified as duplicate based on a set of attributes. If a lead is identified as a duplicate, the status of the lead is marked as Duplicate, and the record is not transferred to the AS_SALES_LEAD table. The PV: Run Lead Deduplication Rule profile must be set to Y for the concurrent program to run the deduplication rule.

While creating the deduplication rule, you can check for duplicate leads using the following lead attributes:

Mandatory Attributes

Customer

Optional Attributes Part of Seeded Rule

Other Available Optional Attributes

The Deduplication Rule

The deduplication rule offers you the flexibility to decide the attributes that will identify a duplicate lead for the requirements in your organization. You can also specify the number of days within which the lead should have been created. By default, all leads created in the last 7 days are checked for duplicates.

There is a seeded rule available, which you can customize. This is the algorithm that the seeded rule follows:

  1. Look for duplicate customer (last name, first name). If duplicate, continue checking. Else unique lead.

  2. Look at contact (name, address, and country). If duplicate, continue checking. Else unique lead.

  3. Look at Campaign. If the matching lead(s) are created as a result of the same campaign, continue checking. Else unique lead.

  4. Look at all other fields (vehicle response, first lead note). If all are duplicate, lead is duplicate. Else unique lead.

Custom Deduplication Using User Hooks

User hooks permit you to bypass Oracle code and implement custom functions instead. Use the following user hook to implement a custom function and check for duplicate leads.

The custom function is executed only if the lead is identified as unique by the Import Sales Lead concurrent program.

Hook Name: IS_DUPLICATE_LEAD

Package Name: AS_IMPORT_SL_CUHK

Purpose

While importing leads, the Import Sales Lead concurrent program does not check for duplicate leads that may be stored in other third-party applications.

To implement custom lead duplicate checking, write a package according to the following specifications. The Import Sales Lead concurrent program creates either a new lead or skips a lead import record based on the value returned by your program. If the record is skipped, then no lead is created and the load_status of that lead import record is set to DUPLICATE.

Do not commit in the package body. After the transaction is completed, Oracle Application code issues a commit.

This user hook is called by the Import Sales Lead concurrent program.

Calling Package

AS_IMPORT_SL_PVT.Is_Duplicate_Lead

API Name

Is_Duplicate_Lead_Pre

Procedure Specification

CREATE or REPLACE PACKAGE as_import_sl_cuhk IS
  PROCEDURE Is_Duplicate_Lead_Pre(
            p_api_version_number    IN  NUMBER,
            p_init_msg_list         IN  VARCHAR2     := FND_API.G_FALSE,
            p_validation_level      IN  NUMBER       := FND_API.G_VALID_LEVEL_FULL,
            p_commit                IN  VARCHAR2     := FND_API.G_FALSE,
            p_import_interface_id   IN  NUMBER,
            x_duplicate_flag        OUT NOCOPY VARCHAR2,
            x_return_status         OUT NOCOPY VARCHAR2,
            x_msg_count             OUT NOCOPY NUMBER,
            x_msg_data              OUT NOCOPY VARCHAR2
       );
END as_import_sl_cuhk;
/

Procedure Body

CREATE or REPLACE PACKAGE BODY as_import_sl_cuhk AS
 PROCEDURE Is_Duplicate_Lead_Pre(
            p_api_version_number    IN  NUMBER,
            p_init_msg_list         IN  VARCHAR2     := FND_API.G_FALSE,
            p_validation_level      IN  NUMBER       := FND_API.G_VALID_LEVEL_FULL,
            p_commit                IN  VARCHAR2     := FND_API.G_FALSE,
            p_import_interface_id   IN  NUMBER,
            x_duplicate_flag        OUT  VARCHAR2,
            x_return_status         OUT  VARCHAR2,
            x_msg_count             OUT  NUMBER,
            x_msg_data              OUT  VARCHAR2
       )  IS
 BEGIN
 /*
 Custom code goes here for lead de-duplication
 Assign:
   x_duplicate_flag = 'Y' if lead is to be marked 'DUPLICATE'
   else
   x_duplicate_flag = 'N'
 */
 null;
 END;
END as_import_sl_cuhk;
/

In Parameters

The four parameters below are standard inputs:

Parameter Description
p_api_version_number For the Oracle Sales 12 application, this is 2.0.
p_init_msg_list Initialize message stack or not. This is set to FND_API.G_FALSE by default.
p_validation_level Validation level for pass-in values. This is set to FND_API.G_VALID_LEVEL_FULL by default.
p_commit To commit the whole API at the end of API, set to FND_API. G_FALSE. This is the default value.

The following parameter does not have a standard input:

Parameter Description
p_import_interface_id The import interface identifier. Pass the import_interface_id of the lead import record for which you want to perform the lead existence checking.

Out Parameters

The following three parameters are standard output parameters.

Parameter Description
x_return_status The return status. If your code completes successfully, then FND_API.G_RET_STS_SUCCESS must be returned. If you get an expected error, then return FND_API.G_RET_STS_ERROR, otherwise return FND_API.G_RET_STS_UNEXP_ERROR.
x_msg_count The message count. Call FND_MSG_PUB.Count_And_Get to get the message count and messages.
x_msg_data The messages. Call FND_MSG_PUB.Count_And_Get to get the message count and messages.

The following parameter does not have a standard output:

Parameter Description
x_duplicate_flag Indicates the status of the lead.
Y indicates the lead import record is a duplicate and was not imported.
N indicates the lead import record is not a duplicate and was imported.

Customizing the Deduplication Rule

To customize the deduplication rule to your requirements.

The Deduplication Rule Flow

The flow of the deduplication rule is explained in the following table using the settings in the seeded deduplication rule. The Result column in the table gives the outcome for each instance.

Seeded Deduplication Rule Flow
Customer Vehicle Response Campaign Customer Address Primary Contact Last Name Primary Contact First Name Lead Note/ Type Result
Digital Harvester E-mail Laptop Cross Sell 500 Oracle Pkwy Lorna Bennie Note1 Master Lead
Digital Harvester E-mail Laptop Cross Sell 500 Oracle Pkwy Lorna Bennie Note1 Exact Duplicate Lead
Digital Harvester E-mail Laptop Cross Sell 500 Oracle Pkwy Irvin Bennie Note1 Unique Lead (fails first duplicate check)
Digital Harvester Phone Laptop Cross Sell 500 Oracle Pkwy Lorna Bennie Note1 Unique Lead (fails Vehicle Response duplicate check)
Digital Harvester E-mail Printer Cross Sell 500 Oracle Pkwy Lorna Bennie Note1 Unique Lead (fails Campaign duplicate check)
Digital Harvester E-mail Laptop Cross Sell 500 Oracle Pkwy Lorna Bennie New Note1 Unique Lead (fails first note check)
Digital Harvester E-mail Laptop Cross Sell 500 Oracle Pkwy Lorna Bennie Note1New Note2 Duplicate Lead (second note not checked)

Variants in any other fields do not affect this check (such as Role, Source System, SIC code, and so on).

Contact Restrictions

The Import Sales Lead concurrent program allows you to set restrictions for the do_not_phone_flag, do_not_fax_flag, do_not_email_flag, and do_not_mail_flag flags for the contact (relationship party_id), and the do_not_mail_flag for the address (party_site). If you want to set the restrictions, set these flag values to Y.

Contact Points

The Import Sales Lead concurrent program creates the Phone, E-mail, Web, and Fax contact points.

Custom Codes with the Lead Import Program

In addition to the lead import process, you can subscribe to business events that will execute prior to and after the Import Sales Lead concurrent program is run. You can also customize the user hook provided to check for a party in TCA.

Seeded Business Events

Subscribe to business events when you want additional processing on lead records, or when you want a specific result at the end of the lead import process. You can specify the function codes to run by associating them with a business event, and subscribing to the event.

What is a Business Event?

A business event is an occurrence of any logical event in the application. Examples of business events are Creating a lead, Importing a lead, or Converting a lead to opportunity.

What is Event Subscription?

Event subscription is a pointer to a function code or a workflow. With every business event, you can register one or more subscriptions. When a business event occurs, the subscription(s) associated with the event are called, and the associated function code is executed. If there are more than one subscriptions associated with an event, then the order of execution of these subscriptions is decided from the phase number associated with each subscription.

A Pre function code is executed prior to running the Oracle code and a Post function code is called after the Oracle code is executed. In Oracle Leads Management, the Lead Import Event - Pre and Lead Import Event - Post functions are seeded for the Importing a Lead event.

Before starting to import a batch of records, the Import Sales Lead concurrent program raises the Lead Import Event - Pre function using the WF_EVENT.Raise () call. After the import process for a lead is completed, the Lead Import Event - Post function is executed. The same parameters that are passed to the Import Sales Lead concurrent program are passed to these functions as well. These parameters are batch_id, source_system, debug_flag, and purge_error_flag. For information about these parameters, see"Import Sales Lead Concurrent Program Parameters.

If there is any exception raised from the function's code, the lead import process for the particular record is terminated, and the load_status for the record is marked as ERROR.

In general, there can be more than one function subscribed to an event. If there are no functions subscribed, then the control is returned to the calling program without raising an error or exception.

Alternatively, a workflow process can also be associated with an event. For sample subscription codes, see Sample Function Codes for the Business Event Subscriptions. To subscribe to a business event, see the Oracle Workflow Developer's Guide.

Error Handling

If there are any exceptions generated from the functions, the Import Sales Lead concurrent program terminates abnormally and any further execution is marked as Error. The functions must be able to handle these exceptions themselves. If the Business Event system itself raises any errors while calling the WF_EVENT.Raise() method, then the error is placed on the WF_ERROR queue and a notification is sent to the System Administrator.

Execution Control

If multiple subscriptions are defined for the same event, you can control the order in which the Event Manager executes the subscriptions by specifying a phase number for each subscription. Subscriptions are executed in ascending phase order. For example, you can enter 10 for the subscription that you want to execute first when an event occurs, 20 for the subscription that you want to execute second, and so on. You can use phases to ensure that different types of actions are performed in the appropriate order, such as executing subscriptions that perform validation before subscriptions that perform other types of processing. If you enter the same phase number for more than one subscription, the Event Manager may execute them in any order, relative to each other. However, the Event Manager will execute that group of subscriptions in their specified place in the phase order, relative to subscriptions with other phase numbers.

You can also use the phase number to control whether the subscription is executed immediately or is deferred. The Event Manager treats subscriptions with a phase number of 100 or higher as deferred subscriptions (asynchronous). Subscriptions with a phase number from 1 to 99 are executed immediately (synchronously). The phase number for a lead import subscription must always be between 1 and 99. The phase number 0 (zero) is reserved for Oracle Workflow seeded subscriptions and should not be used.

Sample Function Codes for the Business Event Subscriptions

Use the following code as a sample to create your pre and post business event functions.

Sample Package Specification

CREATE OR REPLACE PACKAGE aml_import_event IS


 FUNCTION Pre(p_subscription_guid in raw,


               p_event in out wf_event_t) return varchar2;


 FUNCTION Post(p_subscription_guid in raw,


               p_event in out wf_event_t) return varchar2;


END;

Sample Package Body

CREATE OR REPLACE PACKAGE BODY aml_import_event IS

Sample Function Code - Pre

FUNCTION Pre(p_subscription_guid in raw,


               p_event in out wf_event_t) return varchar2


          IS


 l_parameter_list wf_parameter_list_t;


 l_batch_id VARCHAR2(15);


 l_source_system VARCHAR2(30);


 l_debug_msg_flag VARCHAR2(1);


 l_PURGE_ERROR_FLAG VARCHAR2(1);


 l_string VARCHAR2(50);


BEGIN


--Get parameters:


 l_parameter_list := WF_PARAMETER_LIST_T();


 l_parameter_list := p_event.GetParameterList;


 l_source_system := wf_event.GetValueForParameter('P_SOURCE_SYSTEM',l_parameter_list);


 l_batch_id := wf_event.GetValueForParameter('P_BATCH_ID',l_parameter_list);


 l_debug_msg_flag := wf_event.GetValueForParameter('P_DEBUG_MSG_FLAG',l_parameter_list);


 l_PURGE_ERROR_FLAG := wf_event.GetValueForParameter('P_PURGE_ERROR_FLAG',l_parameter_list);


/*


<-- CUSTOM GOES HERE -->


 l_string := substr('Pre- '||l_batch_id||'-'||l_source_system||'-'||l_debug_msg_flag


                    ||'-'||l_PURGE_ERROR_FLAG,1,50);


 commit;


<-- END CUSTOM CODE -->


*/


 return 'SUCCESS';


END Pre;

Sample Function Code - Post

FUNCTION Post(p_subscription_guid in raw,


               p_event in out wf_event_t) return varchar2


          IS


 l_parameter_list wf_parameter_list_t;


 l_batch_id VARCHAR2(15);


 l_source_system VARCHAR2(30);


 l_debug_msg_flag VARCHAR2(1);


 l_PURGE_ERROR_FLAG VARCHAR2(1);


 l_string VARCHAR2(50);


BEGIN


--Get parameters:


 l_parameter_list := WF_PARAMETER_LIST_T();


 l_parameter_list := p_event.GetParameterList;


 l_source_system := wf_event.GetValueForParameter('P_SOURCE_SYSTEM',l_parameter_list);


 l_batch_id := wf_event.GetValueForParameter('P_BATCH_ID',l_parameter_list);


 l_debug_msg_flag := wf_event.GetValueForParameter('P_DEBUG_MSG_FLAG',l_parameter_list);


 l_PURGE_ERROR_FLAG := wf_event.GetValueForParameter('P_PURGE_ERROR_FLAG',l_parameter_list);


/*


<-- CUSTOM GOES HERE -->


 l_string := substr('Post- '||l_batch_id||'-'||l_source_system||'-'||l_debug_msg_flag


                    ||'-'||l_PURGE_ERROR_FLAG,1,50);


 commit;


<-- END CUSTOM CODE -->


*/


 return 'SUCCESS';


END Post;


END aml_import_event;


/

Custom User Hook

You can execute a custom user hook that will be called from the Import Sales Lead concurrent program before DQM processes each record. This user hook tries to find a party_id with an exact customer name match in the HZ_PARTIES table in TCA. Calling the user hook from the Import Sales Lead concurrent program is controlled by the OS:Execute Custom Code from Lead Import profile. If the value of the profile is Yes, then the program is invoked.

Purpose of a Custom User Hook

Consider the following scenario where the custom user hook is used. A batch has two records with the same customer name, and the customer is not recorded in the HZ_PARTIES table in TCA. The custom user hook processes the first record in the batch and tries to find an exact string match with party_name in the HZ_PARTIES table. Because this is a new party, the query will not return any records. Next, DQM is invoked to find a party match. This would also fail because it is a new party. Finally, the Import Sales Lead concurrent program creates a new customer in TCA, and creates a lead against it.

While processing the second record, before calling DQM, the user hook returns the party_id of the newly created customer. Hence, DQM processing for the record will be bypassed.

If the user hook is not available, DQM will fail because the synchronization is not done between the DQM Staging Schema and the TCA before the second row is processed. See Limitation of the Import Sales Lead Concurrent Program.

On the other hand, if the user hook returns more than one record for the exact customer match, it would mean that the customer was created before this import batch was loaded, and should have been present in the DQM staging area. Therefore, the program should call DQM to find the one right match among the multiple matches.

Performance Impact

Running the user hook may affect performance because the program queries the HZ_PARTIES table for a party_id with the exact party_name.

Seeded Code for the User Hook

The following is the seeded PL/SQL package that is seeded for the user hook. You can customize it to suit your setup.

Procedure Specification

PACKAGE aml_find_party_match_pvt


   PROCEDURE main (imp             IN OUT NOCOPY as_imp_int%ROWTYPE,


                   X_return_status OUT NOCOPY varchar2)


   l_party_id  number;


   


Begin


   X_return_status := FND_API.G_RET_STS_SUCCESS;


   SELECT party_id


   INTO l_party_id


   FROM   hz_parties hzp


   WHERE  hzp.party_name = imp.customer_name;


   Imp.party_id := l_party_id;


   UPDATE as_import_interface


      SET party_id = l_party_id


    WHERE import_interface_id = imp.import_interface_id;


Exception


   When NO_DATA_FOUND Then


      l_party_id := NULL;


   When TOO_MANY_ROWS Then


      l_party_id := NULL;


End main;

Purging Staged Lead Records

The AS_IMPORT_INTERFACE table is a temporary location where the imported records are stored before unique records are moved to the AS_SALES_LEAD table. After the records are checked for duplicates, the remaining records in the AS_IMPORT_INTERFACE may not be required. If they are retained in the AS_IMPORT_INTERFACE table, the number of records may affect the performance of the Import Sales Lead concurrent program.

You can delete the records from this table by running the Purge Leads Import Interface Table concurrent program.

Use the following details to run the concurrent program.

Responsibility: Oracle Sales Administrator

Parameters:

Schedule: Once

For the steps to run the concurrent program, see Running Concurrent Programs.