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:
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:
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;