How to configure siebel Joins
A Join object definition creates a relationship between a business component and a table other than its base table. This relationship allows the business component to use columns from the other table. The join uses a foreign key in the business component to obtain rows on a one-to-one basis from the joined table, even though the two tables do not necessarily have a one-to-one relationship.
The master-detail relationship is implemented with a foreign key column in the detail table (as shown in fig 1.0. Multiple rows in the detail table have the same foreign key value pointing back to the same row in the master table. A join is always one-to-one and it is always between a business component and a table. After a join is created, you can create additional fields in the business component based on columns in the joined table. Fig 1.0 Master-Detail Relationship in a Join
Implicit joins exist as part of the Siebel object architecture. They are not explicitly defined using Siebel Tools. Unlike joins that you define in Siebel Tools, users can update the columns from implicit joins.
Implicit joins exist for the following:
– All 1:1 (_X) extension tables and all relevant intersection tables.
– Extension tables of S_PARTY, such as S_ORG_EXT, S_CONTACT, S_POSTN, and S_USER.
These implicit joins are used to map data to party business components. For example, if you were to add a field to the Account business component and then selected the Join property, you would see several implicit joins that do not appear in the Join list displayed in the Tools Object List Editor, including joins with an alias of S_ORG_EXT and S_USERs.
Implicit joins usually use the table name as the Join Alias.
Map fields in party extension tables to party business components
Party business components have S_PARTY as the base table, but store their main data in S_PARTY extension tables.
Construction of a Join
Fig: Join Architecture
The roles of the object in the diagram are summarized as follows:
Business Component object type. The business component is the parent object definition of the join. Because of the join, fields in the business component (called joined fields) can represent columns from the joined table.
Joined field. A joined field in the business component represents a column from a table other
than the business component base table. Therefore, a joined field must obtain its values
through a join. A joined field has the name of the join in its Join property. Together the Join
property and Column property identify the column and how to access it. When creating a joined field in a business component, you can change the Type property from the default DTYPE_TEXT to a more appropriate type. For example, if you are joining a table column that contains phone numbers, you can change the Type field to DTYPE_PHONE.
Join object type. Join is a child object type of the Business Component object type. The Join
object definition uniquely identifies a join relationship for the parent business component and
provides the name of the destination (joined) table. The join object definition identifies the joined table in the Table property. The name of the base table is already known to the business component.
Join Specification object type. The join specification object definition is a child of the join
object definition. It identifies the foreign key field in the business component and the primary
key column in the joined table (that the foreign key points to).
The Source Field property identifies the foreign key field in the business component. If left blank, the Source Field is the Id field, indicating a one-to-one relationship between the business component and the joined table. Occasionally, a system field such as Created By or Updated By may be specified as the foreign key field in the Source Field property.
The Destination Column property identifies the primary key column in the joined table. A
nonblank Destination Column property value is required if the join occurs on a column other than ROW_ID. A blank value in the Destination Column property means that the destination column is ROW_ID, which is typically the primary key in tables in Siebel applications.
Join Constraints. A join constraint is a constant-valued search specification applied to a column
during a join. It is for use with outer joins.
Foreign key (source) field and foreign key column. The foreign key field is identified in the
Source Field property of the join specification. It represents a foreign key column in the base
table, pointing to rows in a particular table used in joins. For example, in the Contact business
component, the foreign key field to the join on accounts data is the Account Id field, which
represents the PR_DEPT_OU_ID column in the base table.
Joined table. The joined table is the master table in the master-detail relationship. It provides
columns to the business component through the join. The joined table is identified in the Table
property of the Join object definition.
Primary key (destination) column. The join specification identifies the primary key column in
the joined table (in the Destination Column property). Every standard table in standard Siebel
applications has a ROW_ID column that uniquely identifies rows in the table. ROW_ID is the
destination in most joins.
Mapped column. Columns in the joined table are available for use in fields in the business
Points to be remembered while configuring a Join:
A join defines a logical relationship between the base table of a business component and another table. The join is a child object of a business component. Fields in a business component reference joins. A join should only be used when the resulting database join will retrieve no records (zero) or only one record. For example, a join is used to retrieve the primary Account for an Opportunity.
A business component may have more than one join with the same destination table if you
specify an alias for each join using the Alias property. For example, the Action business
component may have two joins to the S_CONTACT table, one to retrieve the owner of the person who created the activity, and another to retrieve the contact associated to the activity. In this example, the joins aliases are Owner and Primary Contact respectively.
It is important that the Alias property of the join be distinct even though the destination table is the same. It is usually not a good practice to use the table name as the Alias name, even though this is common in the standard repository. This is because implicit joins will use the table name as the Alias to make sure that the explicit join name is not used instead. To make sure that no conflict exists, you should always give the join a distinct and custom alias name.
For joins to nonparty related tables, the only column you can update is the field (in the parent
business component) with the foreign key value. You must specify the table to join to and
whether it is an outer join. You must also specify the join specification definition with the source field in the parent business component that stores the foreign key value and the destination column in the child table, which is usually ROW_ID.
For joins to party-related tables, those that are extensions of the S_PARTY table (such as S_CONTACT, S_ORG_EXT, S_USER, or S_POSTN), require that the foreign key value be exposed as the source field. However, unlike joins to nonparty related tables, the destination Column must reference the PAR_ROW_ID column in the joined table.
Explicit Joins: Non-Party Business Component
Data in a party table can be joined into a non-party business component
Example: Bringing account data into the Opportunity business component for display in an Opportunity applet
Explicit Join: Another Party Business Component
Uses the explicit join to the party table and not the implicit one
Example: Bringing parent account data into the Account business component for display in an Account applet
Explicit Join Definition
References the extension table storing the data of interest
Explicit Join Specification Definition
Source field (as before) references the relevant foreign key column to the joined table
Destination column references the PAR_ROW_ID column in the joined table