The unique identifier associated with every record in Siebel Enterprise databases is known as a Row ID. The column in which this value is found is ROW_ID and it is present on every table. The ROW_ID is unique for an entity. For example, the ROW_IDs for the same person in S_PARTY, S_CONTACT, and S_CONTACT_X are the same because they each refer to the same person.
Row IDs are used extensively throughout Siebel Enterprises to access specific records. Although users access records by a User Primary Key (such as Opportunity Name), it is more efficient for the Siebel Enterprise to store and access related data via the Row ID.
The Row ID is a base-36 sequence number generated using a confidential, proprietary algorithm that ensures no duplication, thus protecting the referential integrity of the database. The ROW_ID column is VARCHAR(15), which may contain one or two non-numeric symbols (plus signs or hyphens, or both).
The format of the ROW_ID is one of the following:
CP-NS | Records created by the user interface |
CP+NP+NS | Records created by Interface Manager (EIM) |
CP-NP-NS | Records created by EIM |
where:
CP | = Corporate Prefix, up to 2 alphanumeric characters |
NP | = Next Prefix, up to 6 alphanumeric characters |
NS | = Next Suffix, up to 7 alphanumeric characters |
The maximum length of the ROW_ID is 15 alphanumeric characters.
The corporate prefix will always be unique for any database (main or local). The server maintains its original value, and mobile databases created against that particular server database are always assigned a new, unique value.
The Siebel ROW_ID is a combination of the S_SEQUENCE_S and information from S_SSA_ID table.
All connected users share the same prefix, which is obtained from the table S_SSA_ID on the server database. Remote Users are assigned a unique prefix when they are db-extracted. This value is also stored in the S_SSA_ID table on the local database.
Suffix is generated using an internal algorithm handled by the source code. When a new record is created through the user interface, the Siebel application reads the value of the current NS column from S_SSA_ID table and increments this value by a value more than 1 – for performance reasons, generally 50. The client caches these fifty potential ROW_IDs for future inserts. A new record entered from the user interface may result in many inserts to the underlying tables, depending on the business components used. When the client disconnects, cached ROW_IDs are lost.
The combination of the prefix and suffix generates a unique row_id.
Since Siebel does not expose the algorithm behind generation of row ids, the ROW_ID generation is internal to the EIM process. Therefore Siebel provides user keys to map to a unique record in the base table.
During EIM, the EIM table columns, which map to the user keys of the base table, are populated with values so as to map to a unique record. If that record is to be updated by EIM later, the same user key values have to be populated in the corresponding EIM table columns. Otherwise, it will not resolve into the correct base table record.
For example, PERSON_ID and BU_ID constitute the user keys for S_CONTACT. The corresponding EIM_CONTACT columns are CON_PERSON_UID and CON_BU. When a new record is imported in S_CONTACT using EIM_CONTACT, the CON_PERSON_UID and CON_BU should uniquely identify a Contact record. Now, for updating some fields for this Contact using EIM_CONTACT, CON_PERSON_UID and CON_BU have to be populated with the same set of values that were used during the initial load in order to map to the same Contact record in the base table.
Similarly while loading a child table, which references the row id of the master table, the EIM table columns that map to the foreign-key column of the child table must be populated in the same way the user keys of the master table were populated.
For example, S_CONTACT_XM is a child table of S_CONTACT. The column PAR_ROW_ID of S_CONTACT_XM references the ROW_ID of S_CONTACT. S_CONTACT_XM is loaded by EIM_CON_DTL. The columns of EIM_CON_DTL that need to be populated to generate the PAR_ROW_ID of S_CONTACT_XM are CON_BU, CON_PERSON_UID and CON_PRIV_FLG. These EIM columns should be populated with the same set of values that were used to populate its parent S_CONTACT record through EIM_CONTACT. Note that though PRIV_FLG is not a part of the user key of S_CONTACT, but it is a required column and is part of the foreign-key mapping of its child table.