Tag Archives: siebel

Configuration of Native Load Balancing and SCBroker in Siebel 8.1.x

Client configuration

The client configuration file, such as eapps.cfg, must be changed depending on the type of load balancer being used.

In the release version, the installer and config wizard will prompt the user for the necessary parameters and add them to eapps.cfg.  If the config wizard is not used, these parameters must be made manually.

There are three mutually exclusive options for load balancing that may be configured on the client.  The options are no load balancing, native load balancing, and third party load balancing.

No load balancer

If using only one server, it may be less work to configure the SWSE client to use no load balancer.  The only thing that may need to be modified is the connect strings in eapps.cfg so that the SCBroker listening port is referenced.

The format of the connect string is as follows:

ConnectString = siebel://<hostname>:<SCBroker port>/<enterprise> /<component>

SCBroker port, which defaults to 2321, can be verified by running the following srvrmgr command:

srvrmgr> list param portnumber for comp scbroker show PA_VALUE

Example change for eapps.cfg

[/sales_enu]

ConnectString = siebel://HOSTNAMENAME:2321/siebel/SSEObjMgr_enu

Native load balancer

To use the native round robin load balancing provided by the Siebel session manager, there are 3 changes that must be made.

1. Modify [ConnMgmt] section of eapps.cfg

Create a new section in eapps.cfg called [ConnMgmt].  To enable round robin load balancing, the variables listed in the following table must be set.

[ConnMgmt] variables for round robin load balancing

Variable name Acceptable values Description
EnableVirtualHosts true or false EnableVirtualHosts is used to enable internal load balancing.  A value of true means that the Client has enabled Session Manager based load balancing.
VirtualHostsFile path to LB config VirtualHostsFile contains the Session manager load balancing configuration file.  This file describes virtual server to host mapping

Example change for eapps.cfg

[ConnMgmt]

EnableVirtualHosts=true

VirtualHostsFile=m:\siebel\admin\lbconfig.txt

2.  Create virtual hosts file

The load balancer parses a file containing a mapping of virtual servers to hosts.

Each line of VirtualHostsFile must be in the following format:

vservername=<sid1>:<hostname1>:<SCBroker port>;…;

<sidn>:<hostnamen>:<SCBroker port>;

 

Details on creating the virtual hosts file

  • The first value of each host declaration (server ID) can be determined by running the following srvrmgr command:srvrmgr> list server show SBLSRVR_NAME, SV_SRVRID
  • The third value of each host declaration (SCBroker listening port) can be determined by running the following srvrmgr command:srvrmgr> list param portnumber for comp scbroker show PA_VALUE
  • Each virtual host declaration should be specified on one line.  Each line will be parsed as a separate virtual host.
  • A line starting with “#” is ignored.
  • If the file has syntax errors and is unable to be parsed, there will be a corresponding error in the client log file after the first connect attempt has been made, and users will not be able to connect to any components through the Web server.

Example load balancer config file (lbconfig.txt)

#

# Sample session manager load balancing file.

#

# Format: each line has the format of

# vservername=sid1:hostname1:port1;…;sidn:hostnamen:portn;

SalesVserver      =3:host1:2321;4:host2:2321;5:host3:2321;

CallCenterVserver =7:host4:2321;9:host5:2321;10:host6:2321;

3. Modify connect strings in eapps.cfg with virtual hosts

The connect strings in eapps.cfg must be changed to refer to the virtual servers in VirtualHostsFile.

Example change of connect strings in eapp.cfg

[/sales_enu]

ConnectString = siebel://SalesVserver/siebel/SSEObjMgr_enu

[/callcenter_enu]

ConnectString = siebel://CallCenterVserver/siebel/SCCObjMgr_enu

Upon making these changes to eapp.cfg, the Web server should be restarted.

Third party load balancer

To use a third party load balancer, such as Resonate, first configure the scheduling rules in the third party load balancer, using the appropriate third party configuration tools.  Next, modify the connect strings eapps.cfg to reference the virtual IP and virtual port.

For example, if using the Sales application with virtual IP 172.20.74.100 and virtual port 2512, modify eapps.cfg to contain the following:

Example change for eapps.cfg

[/sales_enu]

ConnectString = siebel://172.20.74.100:2512/siebel/SSEObjMgr_enu

After these changes, browser clients can simply connect to
http://<web server hostname>/sales_enu and the load balancer will route the request to an available OM.

Third party load balancer server configuration

When using a third party load balancer, 3 types of rules must be configured for each component:

Rule type Rule format Use of the rule
Connect */<enterprise>/<component> Initial connect from the client (SWSE) to the Siebel server
Reconnect */<enterprise>/<component>/!<server ID>.* Reconnect to the server where the session was initially established, in case of disconnect or errors.
Round robin */<enterprise>/<component>/RR To enable the client to choose another OM process to connect to in case the initial connect fails.

The same virtual port should be used for each rule, and the physical port for each rule is the listening port of the SCBroker component.

The procedure for configuring the rules in the third party load balancer is as follows:

  1. Use srvrmgr to connect to the server.  Run the following command to determine the SCBroker listening port:srvrmgr> list param portnumber for comp scbroker show PA_VALUE

    PA_VALUE
    ——–
    2321

    In this case, the port is 2321.  This will be the physical port used for the rules.

  1. Use srvrmgr to determine the server IDs of the servers for which scheduling rules will be registered.srvrmgr> list server show SBLSRVR_NAME, SV_SRVRID

    SBLSRVR_NAME  SV_SRVRID
    ————  ———
    srvr1         3
    srvr2         5

  1. Choose an unused port to use as the virtual port, such as 2512.
  1. Use the appropriate third party load balancer configuration tool to create the scheduling rules.  In the Resonate case, use CDAction or DispatchManager can be used.

Example configuration

Suppose the server is to be configured with CallCenter.  We already have determined the following using the steps above:

  • SCBroker is listing on port 2512
  • Srvr1 has server ID 3 and is running on physical host “host1
  • Srvr2 has server ID 5 and is running on physical host “host2

Suppose we also know:

  • The virtual IP for our load balancer is 172.0.0.1, and the virtual port is 2512

Then for the current example, the following rules would need to be registered:

Rule VIP VPort Host Port Description
*/siebel/sccobjmgr_enu 172.0.0.1 2512 host1 2321 Connect rule for srvr1
*/siebel/sccobjmgr_enu 172.0.0.1 2512 host2 2321 Connect rule for srvr2
*/siebel/sccobjmgr_enu/RR 172.0.0.1 2512 host1 2321 Round robin rule for srvr1
*/siebel/sccobjmgr_enu/RR 172.0.0.1 2512 host2 2321 Round robin rule for srvr1
*/siebel/sccobjmgr_enu/!3.* 172.0.0.1 2512 host1 2321 Reconnect rule for srvr1
*/siebel/sccobjmgr_enu/!5.* 172.0.0.1 2512 host2 2321 Reconnect rule for srvr2

Once these rules have been registered and the connect strings in eapps.cfg have been modified to use the VIP and VPort, the configuration is done.

Server parameters for SCBroker

In Siebel 7.7 there is a new system component called SCBroker, which will be started along with server.  If this component does not start, clients will not be able to connect to any OM processes.  The SCBroker has a number of associated parameters which can be configured.

Component parameters for SCBroker

Parameter name Description Default value
DfltTasks Default number of service tasks to start 2
MaxTasks Maximum number of running tasks for a service 2
PortNumber Static TCP/IP port number used by the service or the Siebel Connection Broker 2321
AutoRestart This component is restartable automatically True

New server parameters for SCBroker

Parameter name Description Default value
ConnectionTimeout Incoming connection timeout (msec) 500
TransferTimeout Connection Transfer timeout (msec) 500

New events

SCBroker events can be traced by setting the “SCBroker” event.  For example, in srvrmgr:

srvrmgr > change evtloglvl SCBroker=4 for comp scbroker

Finding Applet(List/Form) Name with Given Display Name from GUI on Siebel

————————-
FOR LIST APPLETS
————————-
SELECT
T10.CONFLICT_ID,
T10.LAST_UPD,
T10.CREATED,
T10.LAST_UPD_BY,
T10.CREATED_BY,
T10.MODIFICATION_NUM,
T10.ROW_ID,
T9.UIFREEZE_FLG,
T9.UIFREEZE_BY,
T10.DET_APPLET_NAME,
T10.DISPLAYFORMAT,
T6.STRING_VALUE,
T8.STRING_VALUE,
T1.DISPLAY_NAME,
T1.APP_CD,
T10.AVAILABLE_FLG,
T1.AVAILABLE_FLG,
T10.BITMAP_COLHEAD_FLG,
T10.CHECKBITMAP,
T10.COMMENTS,
T10.DISPLAY_NAME_REF,
T10.FIELD_NAME,
T10.FIELD_RETRIEVAL_CD,
T5.MODULE_NAME,
T2.APPLET_ID,
T5.NAME,
T10.HTML_ATTR,
T1.LANG_CD,
T10.MVG_APPLET_NAME,
T10.HTML_DISPLAY_MODE,
T10.HTML_HEIGHT,
T1.HTML_HEIGHT,
T10.HTML_ICON_MAP,
T10.MODULE_NAME,
T10.NAME,
T5.OBJ_LOCKED_LANG,
T5.OBJ_LOCKED_FLG,
T5.OBJ_LOCKED_BY,
T5.BUSCOMP_NAME,
T10.LIST_ID,
T2.NAME,
T10.PICK_APPLET_NAME,
T10.POPUP_EDIT_FLG,
T5.PROJECT_ID,
T9.LOCKED_FLG,
T9.LOCKED_BY,
T10.VISIBLE,
T1.VISIBLE_FLG,
T10.SHOW_POPUP_FLG,
T10.TEXT_ALIGNMENT,
T1.TEXT_ALIGNMENT,
T10.LABEL_TXT_ALGNMENT,
T1.LABEL_TXT_ALGNMENT,
T10.HTML_EDIT_FLG,
T10.HTML_MAX_CHAR_DISP,
T1.HTML_MAX_CHAR_DISP,
T10.HTML_ONLY_FLG,
T10.HTML_ROW_SEN_FLG,
T10.HTML_SEQUENCE,
T1.HTML_SEQUENCE,
T10.HTML_TYPE,
T10.HTML_WIDTH,
T1.HTML_WIDTH,
T10.INACTIVE_FLG,
T9.NAME,
T10.TOTAL_CURRCODE,
T10.TOTAL_REQUIRED,
T10.TYPE,
T10.WIDTH,
T1.WIDTH,
T10.REPOSITORY_ID,
T3.NAME,
T10.RUNTIME_FLG,
T10.SEQUENCE,
T1.SEQUENCE,
T7.STRING_VALUE,
T4.STRING_VALUE,
T1.PROMPT_TEXT,
T10.PROMPT_TEXT_REF,
T10.READONLY,
T10.CONTENT_FIXUP_NAME
FROM
SIEBEL.S_LIST_COL_INTL T1,
SIEBEL.S_LIST T2,
SIEBEL.S_REPOSITORY T3,
SIEBEL.S_SYM_STR_INTL T4,
SIEBEL.S_APPLET T5,
SIEBEL.S_SYM_STR_INTL T6,
SIEBEL.S_SYM_STR_INTL T7,
SIEBEL.S_SYM_STR_INTL T8,
SIEBEL.S_PROJECT T9,
SIEBEL.S_LIST_COLUMN T10
WHERE
T5.PROJECT_ID = T9.ROW_ID (+) AND
T10.DISPLAY_NAME_REF = T6.SYM_STR_KEY (+) AND T10.REPOSITORY_ID = T6.REPOSITORY_ID (+) AND T6.APP_CD (+) = ‘STD’ AND T6.LANG_CD (+) = ‘ENU’ AND T6.INACTIVE_FLG (+) = ‘N’ AND
T10.DISPLAY_NAME_REF = T8.SYM_STR_KEY (+) AND T10.REPOSITORY_ID = T8.REPOSITORY_ID (+) AND T8.APP_CD (+) = ‘STD’ AND T8.LANG_CD (+) = ‘ENU’ AND T8.INACTIVE_FLG (+) = ‘N’ AND
T2.APPLET_ID = T5.ROW_ID AND
T10.ROW_ID = T1.LIST_COLUMN_ID (+) AND T1.APP_CD (+) = ‘STD’ AND T1.LANG_CD (+) = ‘ENU’ AND T1.INACTIVE_FLG (+) = ‘N’ AND
T10.PROMPT_TEXT_REF = T7.SYM_STR_KEY (+) AND T10.REPOSITORY_ID = T7.REPOSITORY_ID (+) AND T7.APP_CD (+) = ‘STD’ AND T7.LANG_CD (+) = ‘ENU’ AND T7.INACTIVE_FLG (+) = ‘N’ AND
T10.PROMPT_TEXT_REF = T4.SYM_STR_KEY (+) AND T10.REPOSITORY_ID = T4.REPOSITORY_ID (+) AND T4.APP_CD (+) = ‘STD’ AND T4.LANG_CD (+) = ‘ENU’ AND T4.INACTIVE_FLG (+) = ‘N’ AND
T10.LIST_ID = T2.ROW_ID AND
T10.REPOSITORY_ID = T3.ROW_ID (+) AND
(T10.REPOSITORY_ID IN (SELECT ROW_ID FROM SIEBEL.S_REPOSITORY WHERE NAME = ‘Siebel Repository’)) AND
((NVL (T1.DISPLAY_NAME, NVL (T8.STRING_VALUE, NVL (T6.STRING_VALUE, T6.STRING_VALUE)))) = __ADD_HERE_YOUR_FIELD_NAME_)
ORDER BY
T10.REPOSITORY_ID, T10.NAME;

——————————————————————————————————————

————————-
FOR FORM APPLETS
————————-
SELECT
T11.CONFLICT_ID,
T11.LAST_UPD,
T11.CREATED,
T11.LAST_UPD_BY,
T11.CREATED_BY,
T11.MODIFICATION_NUM,
T11.ROW_ID,
T11.BKGRND_COLOR,
T11.BITMAP_NAME,
T10.STRING_VALUE,
T1.STRING_VALUE,
T6.CAPTION,
T11.CAPTION_REF,
T11.CLASS_NAME,
T11.COMMENTS,
T11.LEFT_COORD,
T6.LEFT_COORD,
T11.LEFT_TEXT,
T11.MVG_APPLET_NAME,
T11.METHOD_INVOKED,
T11.MODULE_NAME,
T11.MULTI_LINE,
T11.NAME,
T7.OBJ_LOCKED_LANG,
T7.OBJ_LOCKED_FLG,
T7.OBJ_LOCKED_BY,
T11.OWNER_DRAW,
T7.BUSCOMP_NAME,
T11.APPLET_ID,
T8.NAME,
T2.STRING_VALUE,
T9.STRING_VALUE,
T6.TOOLTIP_TEXT,
T11.HTML_ICON_MAP,
T11.HTML_MAX_CHAR_DISP,
T6.HTML_MAX_CHAR_DISP,
T11.HTML_ONLY_FLG,
T11.HTML_ROW_SEN_FLG,
T11.HTML_SEQUENCE,
T6.HTML_SEQUENCE,
T11.HTML_TYPE,
T11.HTML_WIDTH,
T6.HTML_WIDTH,
T11.HEIGHT,
T6.HEIGHT,
T11.INACTIVE_FLG,
T11.RUNTIME_FLG,
T11.SEQUENCE,
T6.SEQUENCE,
T11.SHOW_POPUP_FLG,
T11.SORT,
T11.TAB_STOP,
T11.TARGET_VIEW_FRAME,
T11.TEXT_ALIGNMENT,
T6.TEXT_ALIGNMENT,
T11.LABEL_TXT_ALGNMENT,
T6.LABEL_TXT_ALGNMENT,
T11.TEXT_STYLE_NAME,
T11.ACTIVEX_BIND_PROP,
T11.ACTIVEX_PROPS,
T6.APP_CD,
T11.AUTO_HSCROLL,
T11.AUTO_VSCROLL,
T7.NAME,
T5.LOCKED_FLG,
T5.LOCKED_BY,
T5.NAME,
T5.UIFREEZE_FLG,
T5.UIFREEZE_BY,
T11.PROMPT_FLG,
T4.STRING_VALUE,
T3.STRING_VALUE,
T6.PROMPT_TEXT,
T11.PROMPT_TEXT_REF,
T11.READONLY,
T11.REPOSITORY_ID,
T11.HTML_DISPLAY_MODE,
T11.HTML_HEIGHT,
T6.HTML_HEIGHT,
T11.CONTENT_FIXUP_NAME,
T11.DEFAULT_BUTTON,
T11.DET_APPLET_NAME,
T11.DISPLAY_FORMAT,
T11.FIELD_NAME,
T11.FIELD_RETRIEVAL_CD,
T7.MODULE_NAME,
T11.FRGRND_COLOR,
T11.ISGROUP,
T11.HTML_ATTR,
T11.HTML_BITMAP,
T11.HTML_DEFAULT_FLG,
T11.TOOLTIP_TEXT_REF,
T11.TOP_COORD,
T6.TOP_COORD,
T11.TYPE,
T11.VERTICAL_SCROLL,
T11.VISIBLE,
T6.VISIBLE_FLG,
T11.WANT_RETURN,
T11.WIDTH,
T6.WIDTH,
T11.FIELD_TYPE_CD,
T7.TASK_NAME,
T7.TYPE,
T11.HTML_DISA_BITMAP,
T6.LANG_CD,
T11.PICK_APPLET_NAME,
T11.POPUP_EDIT_FLG,
T7.PROJECT_ID
FROM
SIEBEL.S_SYM_STR_INTL T1,
SIEBEL.S_SYM_STR_INTL T2,
SIEBEL.S_SYM_STR_INTL T3,
SIEBEL.S_SYM_STR_INTL T4,
SIEBEL.S_PROJECT T5,
SIEBEL.S_CONTROL_INTL T6,
SIEBEL.S_APPLET T7,
SIEBEL.S_REPOSITORY T8,
SIEBEL.S_SYM_STR_INTL T9,
SIEBEL.S_SYM_STR_INTL T10,
SIEBEL.S_CONTROL T11
WHERE
T11.CAPTION_REF = T10.SYM_STR_KEY (+) AND T11.REPOSITORY_ID = T10.REPOSITORY_ID (+) AND T10.APP_CD (+) = ‘STD’ AND T10.INACTIVE_FLG (+) = ‘N’ AND T10.LANG_CD (+) = ‘ENU’ AND
T11.REPOSITORY_ID = T1.REPOSITORY_ID (+) AND T11.CAPTION_REF = T1.SYM_STR_KEY (+) AND T1.APP_CD (+) = ‘STD’ AND T1.INACTIVE_FLG (+) = ‘N’ AND T1.LANG_CD (+) = ‘ENU’ AND
T11.REPOSITORY_ID = T8.ROW_ID (+) AND
T11.ROW_ID = T6.CONTROL_ID (+) AND T6.APP_CD (+) = ‘STD’ AND T6.INACTIVE_FLG (+) = ‘N’ AND T6.LANG_CD (+) = ‘ENU’ AND
T11.APPLET_ID = T7.ROW_ID AND
T11.PROMPT_TEXT_REF = T4.SYM_STR_KEY (+) AND T11.REPOSITORY_ID = T4.REPOSITORY_ID (+) AND T4.APP_CD (+) = ‘STD’ AND T4.INACTIVE_FLG (+) = ‘N’ AND T4.LANG_CD (+) = ‘ENU’ AND
T11.PROMPT_TEXT_REF = T3.SYM_STR_KEY (+) AND T11.REPOSITORY_ID = T3.REPOSITORY_ID (+) AND T3.APP_CD (+) = ‘STD’ AND T3.INACTIVE_FLG (+) = ‘N’ AND T3.LANG_CD (+) = ‘ENU’ AND
T11.REPOSITORY_ID = T2.REPOSITORY_ID (+) AND T11.TOOLTIP_TEXT_REF = T2.SYM_STR_KEY (+) AND T2.APP_CD (+) = ‘STD’ AND T2.INACTIVE_FLG (+) = ‘N’ AND T2.LANG_CD (+) = ‘ENU’ AND
T11.REPOSITORY_ID = T9.REPOSITORY_ID (+) AND T11.TOOLTIP_TEXT_REF = T9.SYM_STR_KEY (+) AND T9.APP_CD (+) = ‘STD’ AND T9.INACTIVE_FLG (+) = ‘N’ AND T9.LANG_CD (+) = ‘ENU’ AND
T7.PROJECT_ID = T5.ROW_ID (+) AND
(T11.REPOSITORY_ID = IN (SELECT ROW_ID FROM SIEBEL.S_REPOSITORY WHERE NAME = ‘Siebel Repository’)) AND
((NVL (T6.CAPTION, NVL (T1.STRING_VALUE, NVL (T10.STRING_VALUE, T10.STRING_VALUE)))) = __ADD_HERE_YOUR_FIELD_NAME_)
ORDER BY
T11.REPOSITORY_ID, T11.NAME;

Understanding Siebel SQL Spool

You can spool the SQL to a file using dedicated client by including the /s switch in your dedicated client link. However, I advise that it is better to just view the siebel dedicated client log file as the siebel log file will contain not only the SQL spool but also the Event Context and other useful information as to what Siebel is doing. You need to ensure that the SIEBEL_LOG_EVENTS environment variable is set to 4 on your local machine to get this information.

In the Siebel log file, the following text will precede the SQL spool associated with a business component:

Begin: Execute SqlObj ‘[bc_name]’

Where [bc_name] = The name of the business component

There can exist multiple SQL statements associated with instantiating a business component. The first SQL statement is the main core SQL query to instantiate the business component. Then any additional SQL statements exist associated to multi value links that have not got the Use Primary Join set. Also additional SQL statements are generated through calculated fields that perform counts on multi value fields.

The main SQL statement is generated based on the configuration of the business component as such:

1. The primary table of the business component will be listed last in the FROM clause. For example, the Action business component would have the S_EVT_ACT table as the last table in the FROM clause.

2. Any fields exposed through the UI will be included in the SQL. If the field is a join, then the join will automatically be included in the SQL. If the field is a MVF where the MVL uses primary join then the join will be included in the SQL.

3. Any fields that have Force Active = Y, Immediate Post Changes = Y or Link Specification = Y or Required = Y will be included in the SQL even if these fields are not rendered through the UI.

4. Any calculated fields that use InvokeServiceMethod to invoke a business service would result in additional SQL statements for any queries that are performed within the business service called.

5. The search specification of the business component will be incorporated into the WHERE clause of the SQL statement. If the user performs a a query through the UI then this would be applied to the SQL WHERE clause also.

6. The sort specification of the business component will be included in the SQL in the ORDER BY clause.

7. The business component view mode will be incorporated into the SQL depending on the view mode applied.

8. Siebel Tools does not always show all joins used by a business component – these are vanilla joins. For example the Contact BC, when the SQL is spooled this shows joins to S_CONTACT_SS and S_CONTACT_BU which are not configured in Siebel Tools but exist by default associated to the Contact BC.

9. For any joins configured in Siebel Tools, if the Outer Join Flag = Y then the join will be shown in the SQL spool as an outer join. The join will be shown as an inner join if the Outer Join Flag != Y.

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;