Home > Oracle, Siebel > Using SQL for Advanced Query of Siebel Tools

Using SQL for Advanced Query of Siebel Tools

We can query all fields in Siebel Tools no problem. In the Object Explorer we can choose flat mode and query across all objects. If we need to however perform more advanced queries, query for all applets used within a particular screen as an example, we need to use SQL.

Here is the method; we will use the example of querying for all applets used within a Screen. In this example we will not include all pick applets, mvg applets and popup applets in the screen (although this can be achieved), we will just query for the form and list applets.

  • Determine the logic for our query:

To retrieve all applets within a screen,  determine that a screen contains views and views contain view web templates, view web templates contain view web template items and view web template items contain applets. We can see this relationship in Siebel Tools Object Explorer, expanding the Screen object as such:

Screen

|

Screen View

|

View (Drill into Screen View)

|

View Web Template

|

View Web Template Item

|

Applet (Drill into Applet)

My query needs to join these links together.

  • Determine the underlying BusComps/Tables:

We know the relationship of our objects from the step above, we need to determine what each of these objects underlying table is in the Siebel Repository. We can do this in the same way as we would if we were trying to determine that underlying table of an applet in the Siebel Application. We can do a Help – About View – you can do this in Siebel Tools.

So if we start with Screen, In Siebel Tools Object Explorer click on the Screen icon, then Help – About View shows us the underlying BusComp is Repository Screen.

Open another Siebel Tools instance, in the Object Explorer click on Business Component and query for Repository Screen. We can see on this record that the table is S_SCREEN.

Repeat this for all other items in our relationship links above.

  • Determine how underlying Tables link to one another:

I find that usually a child object in the object explorer will link to a parent object at the table level through a foreign key column with a name descriptive enough to pick it out. For example Screen View links to Screen through:

S_SCREEN_VIEW.SCREEN_ID = S_SCREEN.ROW_ID

These repository tables have few enough columns to be able to eyeball the table and determine the foreign key column. If you have to jump from an object in the Object Explorer via drill down then you will need to match the object based on name. For example I had to drill into the Screen View View to get to the View object. Therefore we can link these as such:

S_SCREEN_VIEW.VIEW_NAME = S_VIEW.NAME

4. Add Inactive Flag and Repository Id predicates.

In Siebel Tools, all repository items have an Inactive Flag (which indicates if the object is inactive) and a Repository Id. The Repository Id indicates what repository the object is associated to.

We need to ensure our SQL query only checks objects that are active:

INACTIVE_FLG = ‘N’

We need to ensure our SQL query only checks objects that is in the current repository: Siebel Repository. We can get the ROW_ID of the current Siebel Respository:

SELECT ROW_ID

FROM S_REPOSITORY

WHERE NAME = ‘Siebel Repository’;

Then use this ROW_ID as a bind variable in our SQL query:

S_SCREEN.REPOSITORY_ID = :repid

  • Put it all together:

Now we put the SQL together to return the distinct list of applets within a particular screen:

SELECT ROW_ID
FROM S_REPOSITORY
WHERE NAME = 'Siebel Repository';

SELECT DISTINCT appl.NAME
FROM SIEBEL.S_SCREEN screen
,SIEBEL.S_SCREEN_VIEW scrview
,SIEBEL.S_VIEW vw
,SIEBEL.S_VIEW_WEB_TMPL vwweb
,SIEBEL.S_VIEW_WTMPL_IT vwwebit
,SIEBEL.S_APPLET appl
WHERE screen.REPOSITORY_ID = :repid
AND scrview.REPOSITORY_ID = :repid
AND vwweb.REPOSITORY_ID = :repid
AND vw.REPOSITORY_ID = :repid
AND vwwebit.REPOSITORY_ID = :repid
AND appl.REPOSITORY_ID = :repid
AND appl.INACTIVE_FLG = 'N'
AND vwwebit.INACTIVE_FLG = 'N'
AND vwweb.INACTIVE_FLG = 'N'
AND scrview.INACTIVE_FLG = 'N'
AND vw.INACTIVE_FLG = 'N'
AND appl.NAME = vwwebit.APPLET_NAME
AND vwwebit.view_web_tmpl_id = vwweb.ROW_ID
AND vwweb.view_id = vw.ROW_ID
AND vw.NAME = scrview.VIEW_NAME
AND scrview.screen_id = screen.ROW_ID
AND screen.NAME = :screenname;
Advertisements
  1. May 17, 2013 at 9:26 pm
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: