This session explains how to configure a join in Siebel Tools and set up in the application

OBJECTIVES · Introduction about data, table and various types tables · What is meant by Join? · Principle behind the join · What are the various types of Joins · How join is constructed? · Join architecture? How to configure the Join

ABOUT THIS SESSION -

This session explains how to configure a join in Siebel Tools and set up in the application development environment.

OBJECTIVES

• Introduction about data, table and various types tables

• What is meant by Join?

• Principle behind the join

• What are the various types of Joins

• How join is constructed?

• Join architecture?

• How to configure the Join?

INTRODUCTION

All the organizations need to collect , process and store data for its human, financial material, insurance, order management ,material management, pharmaceuticals needs. To represents this information one can use DBMS modeling. In a DBMS a group of similar information or data which is of interest to an organization is called an ENTITY. Entity information is stored in an object called TABLE. Table contains rows and columns.

Table

A table object definition is the direct representation of a database table in DBMS. It has a Column and Index child object definitions. SIEBEL is the leading provider of Customer Relationship Management (CRM) software ...In Siebel , a set of standard tables is provided with Siebel applications for installation . these provide data storage for all of the features included in the standard Siebel applications. Standard tables and their table object definitions generally are of the following types. - Standard tables and their table object definitions are -

a) Data table

b) IntersectionTables

c)Extension table

d) interface tables.

Base table Means

It identifies the base table if the table in the object definition is an extension table. If the table in the object definition is a base table, this property is a Blank . An extension table always identifies a base table.

Data table Means

It compraises bulk data of the table object definitions in Siebel applications. The column in this table are provide the data for fields. It may primary source of data for that BC.

1. It compares most of the tables in Siebel applications.

2. They serve as a base table for BC.

3. Their column provide the data for fields.

Data tables are public or private.

JOINS

In some situations , you may find necessary for getting the information from two separate tables. join is one of the method to get the information . In this case i.e in Siebel a join object definition a relationship between a Business component and a table other than its base table. from database background knows, "Cardinality" between two tables can be of four types : a) One to One b) One to Many c) Many to One d) Many to Many.

Join in Siebel is, "When you have "One" on the right side of the cardinality, there you need to implement Join". That means, "One to One" and "Many to One" corresponds to the Join,

o A Join exist between a Business Component & a table

o Via Join system fetches a single record

o While implementing Join, we can use the "Join Constraint" to specify the condition on the child entity.

JOIN=BUSINESS COMPONENT+A TABLE

Principle

Fields in the business component can represents column from the joined table. And a joined field in the business component represents column from a table other than business component ‘s base table. A business component whose base table is a detail table in a master-detail relationship can include columns from the master table as fields. This is the principle behind a join.

WHAT ARE THE VARIOUS TYPES OF JOINS?

There are two type of commonly used joins

>Implicit Join

>Explicit Join

Implicit Join Implicit Join are those which are already define in Siebel and which are internally use by the Siebel to pull the values from the other than base table.

Example

join between base table and extension table like S_OPTY and S_OPTY_X and join between S_PARTY and S_ORG_EXT .

Explicit Joins Explicit Joints are those which are define by the developer to create the relationship between two tables.

Implied Joins

Underlying the one-to-one extension table’s relationships with the base table and business component I s a set of hidden relationships called an implied or implicit join .

The implied join makes the extension table rows available on a one-to-one basis to the business component that uses the extension table.

• Every extension table has an implied join with the business component it extends.

• This join always has the name of the extension table.

• An implied join is different from joins defined as object definitions.

• Data can be updated through an implied join. Data can be displayed only through other joins.

• This update capability is important for extension table functionality.

HOW JOIN IS CONSTRUCTED

Busienss component

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’s 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.

NOTE:-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.

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. Typically, a join object definition is given the same name as the joined table.

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).

Join Constraints.

A join constraint is a constant-valued search spec 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 component.

NOTE:-

1.In rare circumstances, there can be multiple join specifications in a single Join.

2. Set the Outer Join Flag to TRUE if you want to retrieve all the records in the business component even when the joined fields are empty.

3. When configuring a recursive or self join, the Alias name of the joins must be different than the Table Name.

Using the same name will result in the following error message: “Table ‘T1’ requires a unique correlation name.” This error is often due to a faulty recursive or self join definition.

How to configure a join

You have to follow the following five steps to have joined field on UI in Siebel

1.Identify the column to be used as Foreign key.

2. Configure field which will store the primary key of join table.

3. Configure a join.

4. Configure join specification.

5. Add a field which will be used at Applet.

EXAMPLE Suppose we want to have Opportunity Name on the Quote Applet.

Here is a step by step procedure with screen shots explaining how to do that.

1. Identification of Column to act as foreign key.

To configure a Join, first of all we have to find out a column in the base table where we can store primary key say (ROW_ID) of opportunity in quote which will become the basis of join.

If there is no column vacant in the base table we can use extension table for that base table e.g S_DOC_QUOTE_X for the S_DOC_QUOTE. Note: Make sure that the column you choose is not being used by any other BC or any other field of same BC.

2. Configure Field in Business Component

• Go to Business Component > Field

• Create a new record

• Enter the name of the field.

• Enter extension table name in Join field

if the column you identified belongs to extension table otherwise go to step 5

• Select the Column identified in column Field.

Note: The column which you have selected must have physical Type varchar. Our Foreign key is now ready for use.

Now we have to configure a joined Field which will use this foreign key

3. Configure/Create a Join

• Go to Business Component > Join

• Create new record.

• Give name of the table with which you want create join ( S_OPTY in our case)

• Give Name in Alias (New in our case).

• Check the Outer join Flag (Very Important).

Go to Object List Explorer (OBLE) on the left hand side and click + sign beside join there will be two options Join Specification and Join Constraints

4. Configure Join Specification

• Create New Record.

• Give Name in the Name field.

• Destination Column is given by default when insert a new record. You can change it if you want to.

• In the Source field give the name of the source field (OptyID) which you have configured earlier to be used as Foreign Key.

5. Configure the field (Opportunity Name) which you want to display on the User Interface.

• Go to Business Component > Field

• Insert New Record

• Give Name in the Name Field

• Enter the Join Alias which you have given for the join you created.

• Select the Name (for Opportunity Name) in the column field.

Column name here represent the data that you want to pull from opportunity.

6. Configure the Control in the Applet (UI).

• Go to Applet > Control (if Form Applet)

• Go to Applet > List > List Column (if List Applet)

• Create New Record.

• Enter the Name

• Enter the name you want to display on UI in Caption Override Field

• Enter the Name of the field you created in Field column

Don’t forget to map this field in Applet.

Right Click on the corresponding Applet and map it.

Now compile the entire project which you have made the changes and you will be able to see this joined field in the Quote Applet

When you see this field in User Interface it will be read only because when the data is pull from the join table it is only read only,

to make it editable you have configure Pick Applet on this field.

How To Create a Join to a Table

Through Another Table Lets say that you want to create a join field "Country" on the Service Request business component (based on the S_SRV_REQ table) where the "Country" field exists on table S_ADDR_ORG

but there is no direct foreign key relationship between S_SRV_REQ and S_ADDR_ORG to create the join.

The relationship is as such: (Service Request BC) S_SRV_REQ.REQUESTOR_ID -> S_ORG_EXT.ROW_ID -> S_ADDR_ORG.OU_ID (Address table with Country column) Therefore the Service Request is joined to S_ORG_EXT directly through foreign key REQUESTOR_ID and then the "Requestor" (S_ORG_EXT) is joined to the Address (S_ADDR_ORG).

It is straight forward to create business component joined fields that involve more than one tables as in the above case.

You need to create the joined fields one by one.

For the above example if you wanted to create a joined field for S_ADDR_ORG.COUNTRY on the Service Request BC you would firstly need to create a joined field to S_ORG_EXT.ROW_ID.

Then you would create a joined field using the joined field (S_ORG_EXT.ROW_ID) as the source field for the S_ADDR_ORG.COUNTRY joined field.

1. In Siebel Tools, Service Request BC > Join > Create a new record with:

Table: S_ORG_EXT

Alias: Requestor

Outer Join Flag: Y

Then in Join Specification for that Join:

Name: Requestor

Destination Column: ROW_ID

Source Field: Requestor Id

2. Service Request BC > Field > Create a new field with:

Name: Requestor Joined Id

Join: Requestor

Column: ROW_ID

3. Service Request BC > Join > Create a new record with:

Table: S_ADDR_ORG

Alias: Requestor Address

Outer Join Flag: Y

Then in Join Specification for that Join:

Name: Requestor Address

Destination Column: OU_ID

Source Field: Requestor Joined Id 4.

Now create the joined field for Country.

Service Request BC > Field > Create a new field with:

Name: Requestor Country J

oin: Requestor Address

Column: COUNTRY

You may now be aware that I did not need to create two joins I could have joined S_SRV_REQ.REQUESTOR_ID to S_ADDR_ORG.OU_ID

however I wanted to show the fact that you could join through multiple tables as an example.

NEXT

What is complex join?

Complex joins can have conditional mappings like >,=,NOT,AND,etc

Difference with Foreign key Primary Key join?

Complex joins are joins which have following diffrences from a PK-FK joins.

# Complex joins can have conditional mappings like >,=,NOT, AND, etc apart from '=' whereas the PK-FK joins are always '='.

# PK-FK joins are always (1-N), whereas complex joins can have (1 - 0,1) and (0,1 - N) kind of relationships.

Remember

Join field can be updated by configuring either dynamic pick list or MVG Applet on the field.

Generally join fields are read only. can be edited by dynamic picklist. Explicitly joined fields cannot be edited.

They are read only. In case of dynmaic picklist ;

it is the Foreign key id used in Join Definition gets edited and accordingly we see joined value.

This is about join.

Thank You,

Suggestions are most welcome:

Reference::

1) Siebel tools reference

2) From the various Siebel websites.