You are here

Home

Duplicate Records after Export in Siebel

This article lists out the steps to identify the Join, which causes the duplicate records during the export.
 
Basically there are two steps involved in the process.
 
  1. Identify the Join returning the duplicate record in the corresponding business component.
 
    1. To start with turn on the SQL Spool functionality for the behavior. See Document 475564.1 for more information.
 
    1. Log into the application and make sure that minimal steps are executed that can replicate the behavior.
 
    1. Log out of the application.
 
    1. From the SQL Spool identify the SQL Statement that performs the Export functionality.
 
    1. From the records exported from the user interface, for one set of duplicate records identify one of the fields that has the same value among them. (For example. the entire duplicate records for that set will have Name=’A’).
 
    1. Now to the SQL Statement identified in step d, append the condition (for example. SNAME = ‘A’) in the WHERE clause of the Statement using ‘AND’. (This is just to constrain the number of records that are retrieved from the database to one set of duplicate records.)
 
    1. Now run this query directly with the database and at this time only one set of duplicate records will be retrieved.
 
    1. From the above query try removing the Join statements one by one and see at the removal of which Join statement only one record is retrieved.
 
    1. This Join statement will be the culprit.
 
  1. Constrain the Join identified in step 1.
 
    1. Once the Join statement causing the duplicate records has been identified, try to redefine the Join Specification in Tools by constraining it according to the business requirements so as to return one single record. This can be done by following the steps below:
 
      1. Navigate to Siebel Tools.
 
      1. Business Component > <Business Component name> > Joins > <Join name> > Join Constraint.
 
      1. Set the properties of this Join Constraint appropriately.
 
However, you can also see duplicate records being created in the standard Siebel application as well.
 
Here is the list of a few such behaviors, their workarounds and also the Change Requests associated to them.
 
Duplicate records are exported when Account records are exported from My Accounts view in Siebel version 7.5.x.
 
This is because one or more than one Sales Representative record is associated to an Account record, and the number of duplicate records exported will be proportional to the number of Sales records associated to an Account record. For example, any account record that has two Sales Representatives is duplicated and any account that has three Sales Representatives will appear three times during the export.
 
  1. In Siebel Tools > Business Component > Account
 
  1. Join > S_ACCNT_POSTN > Join Constraint
 
  1. Create a record with following values:
 
Field
Value
Name
Position Id
Destination Column
POSITION_ID
Value
PositionId ()
 
Change Request 12-FD16ZQ had been logged to address this product defect and is fixed in Siebel version 7.7.
 
When the Account list applet exposed the CUT Address Multi Value Group that used a Primary Id field, the SQL statement included a join to S_CON_ADDR based on the Account Id and Address Id. In this case, the client side export would export duplicate records. The Multi Value Link Primary Id Field caused the SQL statement to do the Join to S_CON_ADDR.
 
This is because of the way the business component is implemented.
  
If the Account list applet did not expose the CUT Address Multi Value Group, the SQL statement would not do a join to S_CON_ADDR based on Account Id and the Address Id. Therefore, the client side export would not export duplicates.
 
Change Request 12-LP6VAK was logged to address the duplicate records on export with a valid configuration. This behavior is not reproduced in Siebel version 7.7 because the only join in 7.7 to S_CON_ADDR is: LEFT OUTER JOIN dbo.S_CON_ADDR T14 ON T2.PR_ADDR_ID = T14.ADDR_PER_ID AND T2.ROW_ID = T14.ACCNT_ID. This returns the Primary Address for the current Account. It will not return multiple records.
 
Duplicate records were found by the following configuration changes suggested in Siebel Maintenance Release guide version 7.5.2.210.
 
  1. Modify Business Component > Business Component User Property
 
    1. Open Siebel Tools.
 
    1. Go to Business Component.
 
    1. Query for Business Component Name = “Contact”.
 
    1. Go to Business Component User Property (under Business Component).
 
    1. Create new record as follows:
 
Field
Value
Name
Use My Position or Primary Contact Position for Position Join
Value
TRUE
 
  1. Modify Business Component > Join.
 
    1. Open Siebel Tools.
 
    1. Go to Business Component.
 
    1. Query for Business Component Name = “Contact”.
 
    1. Go to Join (under Business Component).
 
    1. Query for Join Alias = “Position Join”.
 
    1. Go to Join Constraint (under Join).
 
    1. Query for Join Constraint Name = “Position Id” (but there should be one record anyway).
 
    1. Delete Join Constraint record.
 
After removing the suggested configuration (above), export works fine.
 
Change Request 12-FOYV43 has been logged to address this product defect and this behavior has no longer been verified in Siebel versions 7.8.x and above.
 
Duplicate Contact records in Objectives > Contact Status View was found.
 
No Workaround for this issue. Change Request 12-FOYV4I had been logged to address this product defect and is fixed in Siebel version 7.7.
 
Query on a MLOV field that has a hierarchy produces duplicates with File > Export.
 
When an MLOV column is used in Search Specification or Sort Specification, the SQL statement does a join of the Base table to the S_LST_OF_VAL table on the NAME, TYPE and LANG_ID columns. These three columns are unique in most cases, but not for hierarchical LOVs, which may contain multiple entries with the same (NAME, TYPE, LANG_ID) values and as a result, the SQL statement will return duplicate records and therefore the export will have duplicates.
 
A workaround for this behavior is to rename the List of Values to ensure uniqueness by Name, Type and Language. Another workaround is to modify the List of Values value column to be unique for a given name, type, and language id.
 
Note, that the duplicate records on export only happened if the user executed a query with a query condition on a multilingual (MLOV) field. If the query conditions were not on a MLOV field, the SQL statement did not execute the join to S_LST_OF_VAL and therefore the export did not have any duplicate records.