This chapter covers the following topics:
Leads are captured into Oracle Leads Management from various sources. The primary sources are:
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.
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.
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.
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.
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.
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.
You can import leads into Oracle Leads Management in two ways:
Import data from a .csv or a .txt file, using the Lead Import utility in the HTML interface.
Import data from a flat file by running the Lead Sales Table from Flat File concurrent program.
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.
Leads may be imported from a .csv, .txt, or from a flat file.
Topics in this section include:
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:
Source File: If the source file is at a client location, click Go next to the Client field to select the name and location of a source file from the local hard disk or network.
If the source file is at a server location, in the Server field, enter the URL for the source file.
If the source file is at a FTP location, click Go next to the FTP field, and enter the full path for the source file.
The file types supported are: a .zip file containing a .csv or a .txt file, a .csv file or a .txt file.
Column Delimiter: Use the Column Delimiter list to select the delimiter used in the file to distinguish between two columns. Choose tilde (~) unless you are using SQL Loader.
Field Enclosed By:Use the Field Enclosed By list to select the character that encloses each field in the file. This is required when the data in your file has special characters that must not be mistaken for the column delimiter.
Select the File Header Exists box, if the columns in the source file have a header.
Source Fields: Source Fields are columns in your import file.
Target Fields: Target Fields are columns present in the table.
Select a Source field and a corresponding Target field.
>: The mapped fields appear in the Mapped Source Target fields section. Ensure that all mandatory fields are mapped.
After reviewing the details, you can import the lead. The lead is then processed.
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 | 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.
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:
Click the Import Source Line ID link, and modify the column which has an error and click update
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.
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:
To distinguish between imports, use different batch IDs.
There are some mandatory columns in the AS_IMPORT_INTERFACE table. For such columns, a null or incorrect entry in the field results in a database error. You must provide valid values to these columns. Run SQL*Plus queries to obtain some of the values required by this table.
If you have flexfields set up in your application, you must also populate the AS_IMP_SL_FLEX table. See Flexfields for more information.
There are two ways to import leads into the AS_IMPORT_INTERFACE table from a flat file.
Use the following details to run the Load Sales Lead Interface Table from Flat File concurrent program.
You must have read and write permissions for the directory on the server.
The data in your import file must contain the required fields and the Load Status of each record must be NEW.
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
For a sample data file, see Sample Flat File.
For information on flexfields, see Flexfields.
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:
You must be familiar with Oracle SQL Loader as described in the Oracle9i Database Utilities Guide.
You must be familiar with running SQL Plus database queries.
FTP the tilde(~) delimited flat file with lead data to a directory on the server. The file must have the .dat extension.
The directory on the server must have read and write permissions.
The data in your import file must contain the required fields and the Load Status of each record must be NEW.
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
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.
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
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.
HZ_PARTIES
HZ_LOCATIONS
HZ_CONTACT_POINTS
HZ_PARTY_SITES
HZ_ORG_CONTACTS
AS_SALES_LEADS
AS_SALES_LEAD_LINES
AS_SALES_LEAD_CONTACTS
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 E-Business Suite Flexfields Guide.
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 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:
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 Import Sales Lead concurrent program does the following:
Checks for the existence of Original System Reference (OSR) using Leads Data Quality. See Checking for Duplicate Original System Reference.
Checks for the existence of customer, address, contact, and contact points using DQM Data Quality. See Customer Data Quality.
Creates a record in the TCA database, if the imported record is unique.
Checks for duplicate leads using Leads Data Quality. See Leads Data Quality.
Creates a lead in the AS_SALES_LEAD table, if the imported record is unique.
Qualifies and ranks the lead using the Leads Processing Engine.
Identifies the sales team, and assigns the lead to the owner of the sales team.
Creates a sales team to interact with the customer (the lead's organization), if required.
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
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.
The procedures in the following sections must be complete before running the Import Sales Lead concurrent program:
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:
OS: Use DQM Rule code to match Party
OS: Use DQM Rule code to match Party Site
OS: Use DQM Rule code to match Person
OS: Use DQM Rule code to match Contact
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
Purpose: Select Identify Duplicates in the Purpose area.
Acquisition tab: In the Attribute Name column, enter Name. The Entity column displays Party.
In the Attribute Name column, enter Contact Name. The Entity column displays Contacts, and the Type column displays Custom Attribute.
In the Attribute Name column, enter Phone Number Flexible Format. The Entity column displays Contact_Points.
Transformation tab: Select each attribute, and select a transformation.
Scoring tab: In the Match Threshold field, enter 110.
In the Attribute Name column, enter Contact Name. The Entity column displays Contacts, and the Type column displays Custom Attribute.
Contact Name attribute: Select Exact String, and enter 100 for Weight (%) and select WR Person + Cleanse, and enter 90 for Weight (%).
e-Mail Address attribute: Select Exact (E-mail), and enter 100 for Weight (%).
Phone Number Flexible Format attribute: Select Exact, and enter 100 for Weight (%).
URL attribute:Select Cleanse (URL), and enter 100 for Weight (%).
Click Compile.
Related Topics
For other sample matching rules, see Designing Matching Rules to Detect Duplicate Customer or Person.
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.
OS: Use DQM Rule code to match Party
OS: Use DQM Rule code to match Party Site
OS: Use DQM Rule code to match Person
OS: Use DQM Rule code to match Contact
OS: Default Resource ID Used for Sales Lead Assignment
Prerequisites: Create DQM matching rules.
Navigation: Log in to Oracle Forms with the System Administrator responsibility and navigate to Profile > System > Open.
Notes
Site level:
OS: Use DQM Rule code to match Contact: Associate it with rules that find matching records based on the Contact in the imported record.
OS: Use DQM Rule code to match Party: Associate it with rules that find matching records based on the Party ID in the imported record.
OS: Use DQM Rule code to match Party Site: Associate it with rules that find matching records based on the Party Site ID in the imported record.
OS: Use DQM Rule code to match Person: Associate it with rules that find matching records based on the Person in the imported record.
OS: Default Resource ID Used for Sales Lead Assignment:Set it to the resource who will handle any leads that are not assigned to any current territory.
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
Meaning: The meaning is displayed as one of the values in the drop-down list. For example, the Meaning 'Yes' is displayed for Code Y. The code is stored in a hidden field.
Description: The description along with the meaning gives more information about your lookup code.
Tag: The tag can be used to categorize lookup values. This field is optional.
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.
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.
AS_IMP_LINES_INTERFACE (optional): This interface table can be used to hold lead lines information, in case you have more than five line items for a lead.
AS_IMP_CNT_ROL_INTERFACE (optional): This interface table is used to hold contact roles information to be imported.
AS_IMP_CNT_PNT_INTERFACE (optional): This interface table is to hold any extra contact points information to be imported apart from the AS_IMPORT_ INTERFACE table.
AS_IMP_SL_FLEX (optional): This interface table is to store the flexfields values.
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:
Lead Source System - NEW
Show Debug Message - N
Batch Id - Batch number if you have imported leads in batches
Schedule: Periodically
For the steps to run the concurrent program, see Running Concurrent Programs.
The following table lists the parameters for the Import Sales Lead concurrent program.
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.
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.
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.
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.
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.
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.
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:
Customer Data Quality - performed by Data Quality Management (DQM).
Leads Data Quality - performed by the Leads Deduplication rule.
Table: Checking for Data Quality in imported Lead Records gives the sequence in which the Customer and Leads data quality checks are performed.
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 |
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.
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
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.
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.
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.
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
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:
Number of Parallel Staging Workers - 1
Staging Command - STAGE_ALL_DATA
Continue Previous Execution - No
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.
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.
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.
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 |
Attribute Match | Match Threshold | Override Threshold | Automatic Merge Threshold |
---|---|---|---|
Match Any | 120 | <null> | <null> |
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.
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.
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 |
Attribute Match | Match Threshold | Override Threshold | Automatic Merge Threshold |
---|---|---|---|
Match All | 130 | <null> | <null> |
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 |
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.
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 |
Attribute Match | Match Threshold | Override Threshold | Automatic Merge Threshold |
---|---|---|---|
Match All | 110 | <null> | <null> |
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.
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
Optional Attributes Part of Seeded Rule
Primary Contact
Customer Address
Campaign
Response Channel
Lead Note/Type
Other Available Optional Attributes
Product Category
Project
Total Budget
Total Purchase Amount - Product
Budget Status
Purchase Timeframe
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:
Look for duplicate customer (last name, first name). If duplicate, continue checking. Else unique lead.
Look at contact (name, address, and country). If duplicate, continue checking. Else unique lead.
Look at Campaign. If the matching lead(s) are created as a result of the same campaign, continue checking. Else unique lead.
Look at all other fields (vehicle response, first lead note). If all are duplicate, lead is duplicate. Else unique lead.
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. |
To customize the deduplication rule to your requirements.
Log in with the Oracle Marketing Superuser responsibility and navigate to Administration > Leads > Processing > Deduplication.
Leads created within the last n number of days are checked for duplicates.
To add additional attributes for the rule, select lead attributes from the drop-down lists in the Matching Attributes region. These attributes are used to check for duplicates.
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.
Customer | Vehicle Response | Campaign | Customer Address | Primary Contact Last Name | Primary Contact First Name | Lead Note/ Type | Result |
---|---|---|---|---|---|---|---|
Digital Harvester | Laptop Cross Sell | 500 Oracle Pkwy | Lorna | Bennie | Note1 | Master Lead | |
Digital Harvester | Laptop Cross Sell | 500 Oracle Pkwy | Lorna | Bennie | Note1 | Exact Duplicate Lead | |
Digital Harvester | 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 | Printer Cross Sell | 500 Oracle Pkwy | Lorna | Bennie | Note1 | Unique Lead (fails Campaign duplicate check) | |
Digital Harvester | Laptop Cross Sell | 500 Oracle Pkwy | Lorna | Bennie | New Note1 | Unique Lead (fails first note check) | |
Digital Harvester | 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.
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.
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.
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.
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.
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.
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;
/
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.
The user hook party match program must be properly maintained for it to be effective.
The user hook party match program can find a matching party only if the customer names in the batch are exactly the same.
Running the user hook may affect performance because the program queries the HZ_PARTIES table for a party_id with the exact party_name.
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;
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:
From and To - This date range indicates the dates when the lead was created. When the program is run, all leads that were created in the specified range are deleted. For example, if the From and To Dates are 3-Nov-2003 and 15-Nov-2003, all leads created between the 3rd and 15th of November, 2003 are deleted from the table.
Load status of records to be purged - Status of the leads that you want to purge
Debug - Enter Yes if you want to see debug messages.
SQL Trace - Enter Yes if you want to trace SQL.
Schedule: Once
For the steps to run the concurrent program, see Running Concurrent Programs.