7 Configuring Access to Applications Sources

This chapter contains the following topics:

• Setting Up Database Sources

• Setting Up Oracle E-Business Suite 11i Sources

• Setting Up Oracle E-Business Suite 12 Sources

• Setting up Siebel 7.8 Sources

• Setting Up Siebel 8 Sources

Setting Up Database Sources

With a database source, you can crawl any JDBC-enabled database. A database source can crawl database content projected as a view or query. Each record in the view or query result set is interpreted as a document.

See Also:

"Understanding Table Sources Versus Database Sources" for a list of the benefits of database sources versus the benefits of table sources

Important Notes for Database Sources

The view or query to be crawled must contain the following columns:

Note:

All column names must be upper case.

Table 7-1 Database Source Required Columns

Name	Type	Description
URL	varchar2	Display URL for the document. The value for this column cannot be null. This connector requires that there is URL-based access to the records in the result set of the view or query.
CONTENT	varchar2/clob	Document content.
LASTMODIFIEDDATE	date	Last modified date of the document. If you do not have a column you can use for the mandatory LastModifiedDate attribute, you can still crawl database sources. In the SQL query specified when the source is created, a constant date value can be used for the LastModifiedDate attribute. The value should be in a format in which the getTimestamp method of the corresponding JDBC driver works without erroring out. Note that incremental changes to records will not be picked up by re-crawls, so a full crawl should always be scheduled.
KEY	varchar2	Key to identify the record in the record set.
LANG	varchar2	Document language in ISO 639-1 language code; for example, "en" for English or "ja" for Japanese.

The view or query can contain the following optional columns:

Table 7-2 Database Source Optional Columns

Name	Type	Description
PATH	varchar2	Path to the document. This is used in the browse feature. This can be used to represent organizational hierarchy of the document. For example, level1#level2#level3.
ATTACHMENT_LINK	varchar2	HTTP link to the attachment for the document.
ATTACHMENT	blob	Binary attachments for the document.
CONTENTTYPE	varchar2	Content type of the document; for example, "text/html" for HTML documents, "application/pdf" for PDF documents, or "application/msword" for Microsoft Word documents. If the content type of a document is unknown, set this to "application/octet-stream".
TITLE	varchar2	Title of the document to be displayed in the Oracle SES search result page.

Any other column in the view or query is considered an attribute of the document.

Notes:

If the query or view contains both content and either attachment or attachment link, then one column (from the following order) will be considered document content:

1. ATTACHMENT_LINK

2. ATTACHMENT

3. CONTENT

Even if the ATTACHMENT_LINK or ATTACHMENT column is specified in the query, the mandatory CONTENT column should also be included. However, the contents of ATTACHMENT_LINK or ATTACHMENT will be indexed as document content.

If the document set specified by the view or query contains documents of varied content type, and if it is not feasible to specify the content type for each document individually, then the generic content type "application/octet-stream" can be specified for all of them.

Required Tasks

For crawling any non-Oracle database, copy the driver jar file and change the drivers.properties file:

1. Download the appropriate JDBC driver jar into the $ORACLE_HOME/search/lib/plugins/oracleapplications directory in Oracle SES.

2. Update the drivers.properties file with the following information: <Database name>: <Driver class name>.

3. Add the JDBC driver jar file name to the classpath in MANIFEST.MF of appsjdbc.jar and DBCrawler.jar.

4. For Oracle and SQL Server databases, the following default drivers will be used if none is specified in drivers.properties:

   • Oracle: oracle.jdbc.driver.OracleDriver

   • SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver

Creating Public Database Sources

1. Create a database source on the Home - Sources page. Select Database from the Source Type list, and click Create.

   • Database Connection String: JDBC connection string for the database with content to be crawled. The JDBC string is driver-specific. For example, jdbc:oracle:thin:@<server>:<port>:<SID>

   • User ID: User ID to login to the database specified in Database Connection String. This user ID should have access to the schema owning the view specified in View or the query specified in Query.

   • Password: Password to login to the database specified in Database Connection String.

   • View: Table or view to be crawled.

   • Document Count: Maximum number of documents to be crawled before indexing. Enter –1 if all documents should be crawled before indexing.

   • Query: Query projecting the content to be crawled. Only view or query should be specified.

   • Query File: Path to the XML file specifying the subqueries to crawl attachments and attributes of documents corresponding to every record in the main query.

     See Also:

     "XSD for the Query File"

   • URL Prefix: String to prefix the content of URL column to form a display URL for the document.

   • Cache File: Prefix of a local file name to which the contents can be temporarily cached while crawling.

   • Path Separator: The character separating the tokens in the PATH of the document as returned by the query or view. It must be a single character, and it cannot be a space, a single or double quote, or a control character.

   • Parse Attributes: Enter true if the values of the attributes should be extracted from the document content specified in CONTENT column; otherwise, enter false. For example, if the document content contains <attr1>22</attr1><attr2>333</attr2>, then attr1 and attr2 are extracted as attributes of the document with values 22 and 333 respectively.

     Content up to the first attribute is interpreted as the document content. The remaining portion is used to extract attributes only. For example, if the content is page<attr1>22</attr1>is<attr2>333</attr2>dispersed, then only "page" is considered document content.

     Note:

     Set the Parse Attributes parameter to false when the content is type text/html. Set it to true to index HTML tags as attributes.

   • Grant Security Attributes: Leave this parameter value blank for public source.

   • Deny Security Attributes: Leave this parameter value blank for public source.

2. Click Next.

3. Set authorization to No Access Control List, and clear the authorization manager class name and jar name.

4. Click Create to create the database source.

XSD for the Query File

This section provides the format of the XML query file.

<!--[if !supportEmptyParas]-->XSD for the XML sub-queries file:<!--[endif]-->
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  xmlns="http://xmlns.oracle.com/ses/sqlconnector/detail-attribute-queries" targetNamespace="http://xmlns.oracle.com/ses/sqlconnector/detail-attribute-queries"  elementFormDefault="qualified">
  <xsd:complexType name="sqlQueriesType">
    <xsd:annotation>
      <xsd:documentation>

Specify detail and attribute queries as a source parameter for each document fetched by the parent query.

</xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="attachmentQueries" maxOccurs="1" minOccurs="0">
        <xsd:annotation>
          <xsd:documentation>

Specify detail queries to fetch detail records for each document represented by the parent record. The parent records, fetched by the parent query, are specified as a source parameter. Each record in the document (parent) query can be associated with several detail (child) records. Each of these child records has a single column specifying the content that will be indexed as attachment to the parent document. The child query should select a single column, and the WHERE clause should have bind variables of the form ##PARENT ATTR##, where the value of PARENT ATTR from the parent record is substituted while executing the detail query.

</xsd:documentation> 
        </xsd:annotation>
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="query" maxOccurs="unbounded" minOccurs="1">
             <xsd:complexType>
                <!--Attribute to specify whether the contents retrieved by  the query is inline attachment or link to an attachment. The value "true" specifies that the content is a link to an attachment and "false" indicates inline attachment. Default value is false.-->                                    
                <xsd:attribute name="link" default="false"/>
                <!--Content type of the attachment. If no value is specified, SES will auto-detect the content type.-->
                <xsd:attribute name="contenttype" default="null"/>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:element name="attributeQueries" maxOccurs="1" minOccurs="0">
        <xsd:annotation>
          <xsd:documentation>

Specify queries to retrieve values of attributes of the parent document. Use this feature if the attribute can contain multiple values for a document. If the attribute is a single-valued attribute, then it can be specified in the parent query. The WHERE clause should have bind variables of the form ##PARENT ATTR##, where the value of PARENT ATTR from the parent record is substituted while executing the query.

</xsd:documentation>
        </xsd:annotation>
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="query" maxOccurs="unbounded" minOccurs="1"/>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:element name="sqlQueries" type="sqlQueriesType"/>
</xsd:schema>

Creating Secure Database Sources

The database crawler plug-in uses the user-defined security model in Oracle SES. To crawl in secure mode, some attributes in the view or query being crawled should be identified as security attributes. The values of these attributes determine if a user is authorized to view a document. These attributes can be either GRANT attributes or DENY attributes.

See Also:

"Authorization Plug-in API" for more information about these attribute types and the user-defined security model

Follow these steps to create a secure database source:

1. Create a database source on the Home - Sources page. Select Database from the Source Type list, and click Create.

2. Enter values for the parameters as explained in "Creating Public Database Sources". Specify the GRANT and DENY attributes as values for parameters Grant Security Attributes and Deny Security Attributes respectively. If there are multiple GRANT or DENY security attributes, then separate attribute names with a space.

3. Click Next.

4. Enter values for the authorization plug-in parameters:

   • Authorization Database Connection String: JDBC connection string for the authorization database. The values of the security attributes to which a given user is authorized will be retrieved from this database. The JDBC string is driver-specific.

   • User ID: User ID to login to the authorization database.

   • Password: Password to login to the authorization database.

   • Authorization Query: SQL query to retrieve the values of security attributes to which a given user is authorized. The SELECT clause of this query should have all the security attributes specified in Step 2 with identical names. This query can be of two types:

     • The query can return a single record for a given user. The value in each security attribute column should be a space-delimited list of values to which the user is authorized.

     • The query can return multiple records for a given user. The value in each security attribute column of every row of the result set of this query will be interpreted as a single value.

       The placeholder for the username in the query should be specified as '?'.

   • Single Record Query: Enter true if the authorization query returns a single record for a given user.

   • Authorization User ID Format: Format of the user ID to be used in the SQL query specified in Authorization Query. This format should be one of the authentication attributes of the active identity plug-in.

     For example, if Oracle SES is configured with the Oracle Internet Directory identity plug-in (which supports DN, nickname and e-mail address as authentication attributes), then this parameter can be specified as nickname. Then, the nickname of the current user will be used in the SQL authorization query to build the security filter.

     If no value is specified for this parameter, then the user ID in the canonical form of the active identity plug-in will be used in the authorization query to build the security filter.

5. Click Create to create the database source.

Example of Creating a Secure Database Source

Consider the following scenario:

The document set to be crawled is in tables T1 and T2 as specified by the following query:

SELECT 
   T1.ID, 
   T1.DESCRIPTION, 
   T2.NAME, 
   T1.LAST_UPDATE_DATE, 
   T2.AUTH_ID, T1.HIERARCHY
FROM 
   T1, T2 
WHERE 
   T1.ID = T2.DOC_ID 

The document content is given by the column T1.DESCRIPTION.

Each document has an HTTP access URL of the form http://my.company.com/docserver?doc_id=<ID of the document>.

Access to a document is controlled by the value of T2.AUTH_ID. A document is accessible to a user 'X' if and only if the value of T2.AUTH_ID for the document is in the list of AUTH_IDs for the user as retrieved by the following query:

SELECT 
   AUTH_ID 
FROM 
   USER_AUTH A 
WHERE 
   A.USER='X' 

This source can be crawled as a database source type with the following source parameter values:

Table 7-3 Crawler Plug-in Parameters

Parameter	Value
Database Connection String	jdbc:oracle:thin:@<server>:<port>:<SID>
User ID	apps_user
Password	*****
View
Document Count	-1
Query	SELECT 'docserver?doc_id='|| T1.ID URL, T1.ID "KEY", 'en' LANG, T1.LAST_UPDATE_DATE LASTMODIFIEDDATE, T1.DESCRIPTION CONTENT, 'text/plain' CONTENTTYPE, T2.NAME CUSTOMER_NAME, T2.AUTH_ID, T1.HIERARCHY PATH FROM T1, T2 WHERE T1.ID=T2.DOC_ID
Query File	<?xml version="1.0" encoding="UTF-8" ?> <sqlQueries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ses/sqlconnector/detail-attribute-queries detail-attribute-queries.xsd" xmlns="http://xmlns.oracle.com/ses/sqlconnector/detail-attribute-queries"> <attachmentQueries> <query> <![CDATA[SELECT COMMENTS FROM COMMENTS_TBL WHERE DOCID=##KEY##]]> </query> <query link="true" contenttype='text/html'> <![CDATA[SELECT NOTES FROM NOTES_LINK_TBL WHERE DOCID=##KEY##]]> </query> </attachmentQueries> <attributeQueries> <query> <![CDATA[SELECT AUTHOR FROM AUTHORS_TBL WHERE DOCID=##KEY##]]> </query> <query> <![CDATA[SELECT KEYWORD FROM KEYWORD_TBL WHERE DOCID=##KEY##]]> </query> </attributeQueries> </sqlQueries> AUTHID and KEY are columns in the select list of the parent query. Note: This must be the path to a local file containing the subqueries for attributes and attachments that are currently listed directly for query file.
URL Prefix	http://my.company.com/
Cache File	/tmp/cacheFile
Path Separator	#
Parse Attributes	false
Grant Security Attributes	AUTH_ID
Deny Security Attributes

Also, the following values for authorization:

Table 7-4 Authorization Plug-in Parameters

Parameter	Value
Database Connection String	jdbc:oracle:thin:@<server>:<port>:<SID>
User ID	apps_user
Password	*****
Authorization Query	SELECT AUTH_ID FROM USER_AUTH A WHERE A.USER=UPPER(?)
Single Record Query	false
Authorization User ID Format	user name

Setting Up Oracle E-Business Suite 11i Sources

Oracle SES supports Oracle Human Capital Management (HCM) employee directory search, Oracle Learning Management (OLM) class and course search, and Oracle iProcurement.

Important Notes for Oracle E-Business Suite 11i Sources

• To search Oracle HCM, see Note 400258.1 on Oracle MetaLink:

  http://metalink.oracle.com

• To search Oracle iProcurement, download ARU patch number 5608131.

• An Oracle E-Business Suite 11i source crawler is based on crawling a view or query in a database. Each record in the view or query is considered a document.

  The view or query to be crawled for this source should contain the following columns:

  Table 7-5 Oracle E-Business Suite 11i Source Required Columns

  Name	Type	Description
  URL	varchar2	Display URL for the document. The value for this column cannot be null. This connector requires that there is URL-based access to the records in the result set of the view or query.
  SOLUTION	varchar2/clob	Document content.
  LASTMODIFIEDDATE	date	Last modified date for crawls.
  KEY	varchar2	Key to the record.
  LANG	varchar2	Document language; for example, "en" for English or "ja" for Japanese.

  
  

  The view or query can contain the following optional columns:

  Table 7-6 Oracle E-Business Suite 11i Source Optional Columns

  Name	Type	Description
  PATH	varchar2	Path to the document. This is used in the browse feature.
  ATTACHMENT_LINK	varchar2	HTTP link to the attachment for the document. This attachment will be indexed instead of the SOLUTION column.
  ATTACHMENT	blob	Binary attachments for the document. This will be indexed instead of the SOLUTION column. This attachment will be indexed only if attachment link is not specified or the attachment pointed to by the link is not accessible.
  CONTENTTYPE	varchar2	Content type of the text content (text/plain or text/HTML). This column can also be used to indicate the content type (if known) for the binary content.

  
  

  Any other column in the view or query is considered an attribute of the document.

Setting Up Identity Management for Oracle E-Business Suite 11i

Activate the identity plug-in on the Global Settings - Identity Management Setup page. Select Oracle E-Business Suite 11i and click Activate. Enter the values for the following parameters:

• User Validation Database Connection String: JDBC connection string for the database, used for validating a user. For example, jdbc:oracle:thin:@<server>:<port>:<SID>

• User ID: User ID to login to the user validation database.

• Password: Password to login to the user validation database.

• User Authentication Query: SQL query to authenticate a user. The query should return a single record with a single column with a string value of 'Y' or 'N' based on successful or unsuccessful authentication, respectively. The placeholder for user name and password should be specified as '?'. The default query (which can be changed if needed) is:

  SELECT fnd_web_sec.Validate_login(upper(?),?)
  FROM dual
  

• User Validation Query: SQL query to validate a given user. The query should return 1 if the user is valid. Else, no rows should be returned. The placeholder for the user name should be specified as '?'. The default query (which can be changed if needed) is:

  SELECT 1 FROM fnd_user WHERE user_name = upper(?)
  

Click Finish.

Creating an Oracle E-Business Suite 11i Source

Create an Oracle E-Business Suite 11i source on the Home - Sources page. Select Oracle E-Business Suite 11i from the Source Type list, and click Create. Enter values for the following parameters:

• Database Connection String: JDBC connection string for the E-Business Suite database from which the content will be crawled. For example, jdbc:oracle:thin:@<server>:<port>:<SID>.

• User ID: User ID to login to the E-Business Suite database. This user ID should have access to the schema owning the view specified in the View parameter.

• Password: Password to login to the E-Business Suite database.

• View: Table or view containing the required set of columns.

• Document Count: Maximum number of documents to be crawled and indexed. Enter -1 if all documents should be crawled before indexing.

• Query: Query projecting the required set of columns. This query should be used if the view defined in the View parameter is not available. Only one of these (View or Query) should be specified.

• Query File: Path to the XML file specifying the subqueries to crawl attachments and attributes of documents corresponding to every record in the main query.

  See Also:

  "XSD for the Query File"

• URL Prefix: (optional) String to prefix the content of URL column to form a display URL for the document. For example, "<APPS_FRAMEWORK_AGENT profile>/<APPS_HTML_DIRECTORY profile>/". The values in < > can be found by looking at the specified profiles.

• Cache File: (optional) Local file to which the contents can be temporarily cached while crawling.

• Path Separator: The character separating the tokens in the PATH of the document as returned by the query or view. It must be a single character, and it cannot be a space, a single or double quote, or a control character.

• Parse Attributes: Enter true if the values of the attributes should be extracted from the document content specified in SOLUTION column. Otherwise, enter false.

  Content up to the first attribute is interpreted as the document content. The remaining portion is used to extract attributes only. For example, if the content is page<attr1>22</attr1>is<attr2>333</attr2>dispersed, then only "page" is considered document content.

  Note:

  Set the Parse Attributes parameter to false when the content is type text/html. Set it to true to index HTML tags as attributes.

• Grant Security Attributes: Space-delimited list of grant security attributes. For example, person_id, event_id, or activity_version_id.

• Deny Security Attributes: Space-delimited list of deny security attributes.

Click Next.

Click Get Parameters to obtain a list of parameters for the authorization manager plug-in.

Enter the values for the authorization manager plug-in parameters:

• Authorization Database Connection String: JDBC connection string for the authorization database. The values of the security attributes to which a given user is authorized will be retrieved from this database.

• User ID: User ID to login to the authorization database.

• Password: Password to login to the authorization database.

• Authorization Query: SQL query to retrieve the values of security attributes to which a given user is authorized. The SELECT clause of this query should have all the security attributes specified in the Grant Security Attributes and Deny Security Attributes parameters with identical names. This query can be of two types:

  • The query can return a single record for a given user. The value in each security attribute column should be a space-delimited list of values to which the user is authorized.

  • The query can return multiple records for a given user. The value in each security attribute column of every row of the result set of this query will be interpreted as a single value.

  The placeholder for the user name in the query should be specified as '?'. The SQL query can only have one input placeholder for user name.

• Single Record Query: Enter true if the authorization query returns a single record. Enter false if the query can return multiple records.

Click Create.

Setting Up Oracle E-Business Suite 12 Sources

The Oracle E-Business Suite 12 connector uses the Oracle SES XML connector framework, where searching is based on Oracle E-Business Suite 12 data available as XML feeds.

See Also:

"Overview of XML Connector Framework"

Setting Up Identity Management for Oracle E-Business Suite 12

Activate the identity plug-in on the Global Settings - Identity Management Setup page. Select Oracle E-Business Suite 12 and click Activate. Enter the values for the following parameters:

• HTTP endpoint for authentication: HTTP endpoint of Oracle E-Business Suite that provides the user authentication and validation service.

• User ID: Admin user ID for posting data to the endpoint specified in HTTP endpoint for authentication.

• Password: Admin password for posting data to the endpoint specified in HTTP endpoint for authentication.

Click Finish.

Creating an Oracle E-Business Suite 12 Source

Create an Oracle E-Business Suite 12 source on the Home - Sources page. Select Oracle E-Business Suite 12 from the Source Type list, and click Create. Enter values for the following parameters:

• Configuration URL: File URL of the XML configuration file providing details of the source, such as the data feed type, location, security attributes, and so on. Obtain this file from the Oracle E-Business Suite administrator and save it on the computer on which Oracle SES is installed. Enter the configuration URL as file://localhost/<absolute path of the configuration file>. For example, the URL on Linux will be file://localhost/private/oracle/config.xml.

• Authentication Type: The standard Java authentication type used by the application serving the control and data feed: BASIC for basic authentication, FORM for form-based authentication, and NATIVE for proprietary XML over HTTP authentication. This parameter is relevant only when the feeds are accessed over HTTP. Leave this parameter blank if the feeds are accessed over file or FTP protocols.

• User ID: User ID to access the data feeds, if the data feeds are to be accessed over HTTP/FTP. The access details of the data feed are specified in the configuration file. This can be obtained from Oracle E-Business Suite administrator.

• Password: Password to access the data feeds. This can be obtained from Oracle E-Business Suite administrator.

• Realm: The realm of the application serving the feeds. This parameter is relevant only when the control and data feeds are accessed over HTTP, and is mandatory when the authentication type is BASIC.

• Scratch Directory: A directory, in the computer where Oracle SES is installed, to temporarily write the status logs.

• Maximum number of connection attempts: Maximum number of attempts to connect to the target server to access the data feed.

Click Next.

Enter the values for the authorization plug-in parameters:

• HTTP endpoint for authorization: HTTP endpoint of E-Business Suite that provides the user authorization service.

• User ID: User ID for accessing the authorization service.

• Password: Password for accessing the authorization service.

• Business Component: Name of the Oracle E-Business Suite 12 business component being crawled. The values of the security attributes for which the current user is authorized in the realm of this business component will be retrieved to build the security filter for the user when the user logs into Oracle SES. For example, oracle.apps.fnd.fwk.search.NavigationSVO.

• Display URL Prefix: HTTP host information to prefix the partial URL specified in the access URL of the documents in XML feeds to form the complete URL. This complete URL will be the display URL of the document when the document link in the Oracle SES search results page is clicked.

Click Create.

After processing each data feed, a status feed will be uploaded to the location specified in the configuration file. This status feed will be named as the following:

• <data feed file name>.suc if the data feed was processed successfully

• <data feed file name>.err if any error was encountered while processing the feed. The errors will be listed in this status feed

Note:

The User ID and Password parameters can be left blank if the data feeds are available on the same computer as Oracle SES and are accessed over file protocol.

Setting up Siebel 7.8 Sources

A Siebel 7.8 source crawler is based on crawling a view or query in a database. Each record in the view or query is considered a document.

Note:

The Siebel 7.8 connector supports Siebel installations on the following databases:

• Oracle

• SQL Server

Important Notes for Siebel 7.8 Sources

The view or query to be crawled must contain the following columns:

Table 7-7 Siebel 7.8 Source Required Columns

Name	Type	Description
URL	varchar2	Display URL for the document. The value for this column cannot be null. This connector requires that there is URL-based access to the records in the result set of the view or query.
SOLUTION	varchar2/clob	Document content.
LASTMODIFIEDDATE	date	Last modified date for crawl.
KEY	varchar2	Key to the record.
LANG	varchar2	Document language; for example, "en" for English or "ja" for Japanese.

The view or query can contain the following optional columns:

Table 7-8 Siebel 7.8 Source Optional Columns

Name	Type	Description
PATH	varchar2	Path to the document. This is used in the browse feature.
ATTACHMENT_LINK	varchar2	HTTP link to the attachment for the document. This attachment will be indexed instead of the SOLUTION column.
ATTACHMENT	blob	Binary attachments for the document. This will be indexed instead of the SOLUTION column. This attachment will be indexed only if attachment link is not specified on the attachment pointed to by the link is not accessible.
CONTENTTYPE	varchar2	Content type of the text content (text/plain or text/HTML). This column can also be used to indicate the content type (if known) for the binary content.

Any other column in the view or query is considered an attribute of the document.

Required Tasks

If Siebel 7.8 is installed over Microsoft SQL Server database, then the JDBC driver for SQL Server, sqljdbc.jar, must be copied to $ORACLE_HOME/search/lib/plugins/oracleapplications directory:

1. Download sqljdbc.jar from http://www.microsoft.com/downloads/details.aspx?FamilyId=6D483869-816A-44CB-9787-A866235EFC7C.

2. Follow the instructions at the same location to install the driver.

3. Copy sqljdbc.jar from the installed directory to the $ORACLE_HOME/search/lib/plugins/oracleapplications directory.

Setting Up Identity Management for Siebel 7.8

Activate the Siebel 7.8 identity plug-in on the Global Settings - Identity Management Setup page with the following parameters:

• Authentication and Validation Database Connection String: JDBC connection string for the Siebel 7.8 database that should be used for authenticating and validating a user. The JDBC string is driver-specific.

• User ID: Admin user ID to login to the database (specified in Database Connection String) for validating a user

• Password: Admin password to login to the database (specified in Database Connection String) for validating a user

• User Validation Query: SQL query to validate a given user. The query should return 1 if the user is valid. Otherwise, no rows should be returned. The placeholder for the user name should be specified as '?'. The default query (which can be changed) is:

  SELECT 1 FROM dbo.S_USER WHERE LOGIN=upper(?)
  

Creating a Secure Siebel 7.8 Source

This section describes the steps to create a source to crawl the Siebel 7.8 secured business components supported by Oracle SES: Service Requests, Accounts, Contacts, Products, Sales Tool, and Service Request Attachments.

1. Create a source for the Siebel 7.8 source type on the Home - Sources page. Enter a source name.

2. Provide values for the configuration parameters in the following table:

   Table 7-9 Siebel 7.8 Source Parameters

   Name	Description
   Database Connection String	JDBC connection string for the Siebel 7.8 database from which the content has to be crawled. The JDBC string is driver-specific.
   User ID	User ID to login to the Siebel 7.8 database specified in Database Connection String. This user ID should have access to the schema owning the view specified in View or the query specified in Query.
   Password	Password to login to the Siebel 7.8 database specified in Database Connection String.
   View	Table or view with the columns needed for crawling. In addition to the required columns, the view should contain a column named visibilityid. The value in this column for each record should be the value of the visibility ID for the document corresponding to the record.
   Document Count	Maximum number of documents to be crawled before indexing. Enter –1 if all documents should be crawled before indexing.
   Query	Query projecting the columns for crawling. This query should be used if the view as defined in View is not available. Only one of these - View or Query – should be specified. In addition to the required columns, the view should contain a column named visibilityid. The value in this column for each record should be the value of the visibility ID for the document corresponding to the record. See Also: "Queries to Crawl Siebel 7.8 Business Components"
   Query File	Path to the XML file specifying the subqueries to crawl attachments and attributes of documents corresponding to every record in the main query. See Also: "XSD for the Query File"
   URL Prefix	String to prefix the content of URL column to form a display URL for the document
   Cache File	Local file to which the contents can be temporarily cached while crawling.
   Path Separator	The character separating the tokens in the PATH of the document as returned by the query or view. It must be a single character, and it cannot be a space, a single or double quote, or a control character.
   Parse Attributes	Enter true if the values of the attributes should be extracted from the document content specified in SOLUTION column; otherwise, enter false. Content up to the first attribute is interpreted as the document content. The remaining portion is used to extract attributes only. For example, if the content is page<attr1>22</attr1>is<attr2>333</attr2>dispersed, then only "page" is considered document content. Note: Set this parameter to false when the content is type text/html. Set it to true to index HTML tags as attributes.
   Grant Security Attributes	Space-delimited list of grant security attributes. Enter VISIBILITYID for this parameter.
   Deny Security Attributes	Space-delimited list of deny security attributes. Leave this parameter value blank.

   
   

3. Click Next. Enter values for the authorization plug-in parameters:

   • Authorization Database Connection String: JDBC connection string for the authorization database. The values of the visibility IDs for a given user will be retrieved from this database. Typically, this is same as the connection string specified in Database Connection String.

   • User ID: Admin user ID to login to the authorization database

   • Password: Admin password to login to the authorization database

   • Authorization Query: SQL query to retrieve the values of visibility IDs for a given user. The placeholder for the user name in the query should be specified as '?'. The following query is the default query that can be used for this parameter:

     SELECT p.BU_ID visibilityid FROM dbo.S_POSTN p inner join dbo.S_CONTACT c2 on c2.PR_HELD_POSTN_ID = p.ROW_ID inner join dbo.S_USER u on u.PAR_ROW_ID = c2.PAR_ROW_ID WHERE u.LOGIN = upper(?)
      
     

   Click Create.

   See Also:

   "Queries to Crawl Siebel 7.8 Business Components"

Creating a Public Siebel 7.8 Source

This section describes the steps to create a source to crawl the Siebel 7.8 public business components supported by Oracle SES. Oracle SES supports the Solution business component.

1. Go to the Oracle SES - Home - Sources page.

2. Select Siebel 7.8(Public) from the Source Type pull down list and click Create.

3. Enter values for the parameters, as discussed in "Creating a Secure Siebel 7.8 Source".

4. Click Create.

Queries to Crawl Siebel 7.8 Business Components

This section includes the queries to crawl the Siebel 7.8 business components supported by Oracle SES:

• Service Request Attachments Query

• Accounts Query

• Products Query

• Literature Query

• Solution Query

• Service Request Query

• Contacts Query

Service Request Attachments Query

SELECT
'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Service+Request+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Service+Request+Detail+Applet&SWERowId0='+c.PAR_ROW_ID+'&SRAttId='+c.ROW_ID URL,
'US' LANG,
c.LAST_UPD LASTMODIFIEDDATE,
'text/html' CONTENTTYPE,
c.ROW_ID "KEY",
coalesce('<b>Attachment Name:</b> '+c.FILE_NAME,'<null>')+coalesce(',<br><b>SR Number:</b> '+srv.SR_NUM,'<null>')+coalesce(',<br><b>SR Summary: </b>'+srv.SR_TITLE,'<null>') SOLUTION,
c.ROW_ID sblrowid,
c.CREATED created_on,
c.CREATED_BY createdby,
c.LAST_UPD_BY lastupdatedby,
c.PAR_ROW_ID title,
c.FILE_SRC_TYPE "type",
c.FILE_EXT code01,
c.COMMENTS "comment",
c.FILE_SRC_PATH location,
'Service Request Attachment' sblbctype,
usr.LOGIN owner,
srv.BU_ID visibilityid
 
FROM
dbo.S_SR_ATT c
INNER JOIN dbo.S_SRV_REQ srv      ON c.PAR_ROW_ID=srv.ROW_ID
LEFT OUTER JOIN dbo.S_USER usr    ON usr.PAR_ROW_ID = srv.OWNER_EMP_ID
LEFT OUTER JOIN dbo.S_CONTACT con ON con.PAR_ROW_ID = c.LAST_UPD_BY

Here is the same query formatted as a single line that could be cut and paste into the Oracle SES administration tool:

SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Service+Request+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Service+Request+Detail+Applet&SWERowId0='+c.PAR_ROW_ID+'&SRAttId='+c.ROW_ID URL, 'US' LANG, c.LAST_UPD LASTMODIFIEDDATE, 'text/html' CONTENTTYPE, c.ROW_ID "KEY", coalesce('<b>Attachment Name:</b> '+c.FILE_NAME,'<null>')+coalesce(',<br><b>SR Number:</b> '+srv.SR_NUM,'<null>')+coalesce(',<br><b>SR Summary: </b>'+srv.SR_TITLE,'<null>') SOLUTION, c.ROW_ID sblrowid, c.CREATED created_on, c.CREATED_BY createdby, c.LAST_UPD_BY lastupdatedby, c.PAR_ROW_ID title, c.FILE_SRC_TYPE "type",c.FILE_EXT code01, c.COMMENTS "comment", c.FILE_SRC_PATH location, 'Service Request Attachment' sblbctype, usr.LOGIN owner, srv.BU_ID visibilityid FROM dbo.S_SR_ATT c INNER JOIN dbo.S_SRV_REQ srv ON c.PAR_ROW_ID=srv.ROW_ID LEFT OUTER JOIN dbo.S_USER usr ON usr.PAR_ROW_ID = srv.OWNER_EMP_ID LEFT OUTER JOIN dbo.S_CONTACT con ON con.PAR_ROW_ID = c.LAST_UPD_BY 

Accounts Query

SELECT
'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Accounts+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Account+List+Applet&SWERowId0='+T1.ROW_ID URL,
'US' LANG,
T2.LAST_UPD LASTMODIFIEDDATE,
'text/html' CONTENTTYPE,
T1.ROW_ID "KEY",
coalesce('<b>Name:</b> '+T2.NAME,'<null>')+coalesce(',<br><b>Type:</b> '+T2.OU_TYPE_CD,'<null>')+',<br><b>Address:</b> '+coalesce(T5.ADDR,'<null>')+coalesce(','+T5.CITY,'<null>')+coalesce(','+T5.STATE+'&nbsp;'+T5.ZIPCODE,'<null>')+coalesce(','+T5.COUNTRY,'<null>') SOLUTION,
T1.ROW_ID sblrowid,
T2.CREATED created_on,
T2.CREATED_BY createdby,
T2.LAST_UPD_BY lastupdatedby,
T2.NAME title,
T2.OU_NUM csn,
T2.OU_TYPE_CD type,
T2.LOC location,
T10.LOGIN alias,
T5.ADDR street,
T5.CITY city,
T5.STATE state,
T5.COUNTRY country,
T5.ZIPCODE zipcode,
'Account' sblbctype,
T2.BU_ID visibilityid
 
FROM
dbo.S_PARTY T1
INNER JOIN dbo.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID
INNER JOIN dbo.S_ACCNT_POSTN T3 ON T2.PR_POSTN_ID = T3.POSITION_ID AND T2.ROW_ID = T3.OU_EXT_ID
INNER JOIN dbo.S_PARTY T4 ON T3.POSITION_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_POSTN T9 ON T3.POSITION_ID = T9.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG T5 ON T2.PR_ADDR_ID=T5.ROW_ID
LEFT OUTER JOIN dbo.S_USER T10 ON T9.PR_EMP_ID = T10.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T11 ON T11.PAR_ROW_ID=T2.LAST_UPD_BY
 
WHERE
(T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG = 'Y')

Here is the same query formatted as a single line that could be cut and paste into the Oracle SES administration tool:

SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Accounts+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Account+List+Applet&SWERowId0='+T1.ROW_ID URL, 'US' LANG, T2.LAST_UPD LASTMODIFIEDDATE, 'text/html' CONTENTTYPE, T1.ROW_ID "KEY", coalesce('<b>Name:</b> '+T2.NAME,'<null>')+coalesce(',<br><b>Type:</b> '+T2.OU_TYPE_CD,'<null>')+',<br><b>Address:</b> '+coalesce(T5.ADDR,'<null>')+coalesce(','+T5.CITY,'<null>')+coalesce(','+T5.STATE+'&nbsp;'+T5.ZIPCODE,'<null>')+coalesce(','+T5.COUNTRY,'<null>') SOLUTION, T1.ROW_ID sblrowid, T2.CREATED created_on, T2.CREATED_BY createdby, T2.LAST_UPD_BY lastupdatedby, T2.NAME title, T2.OU_NUM csn, T2.OU_TYPE_CD type, T2.LOC location, T10.LOGIN alias, T5.ADDR street, T5.CITY city, T5.STATE state, T5.COUNTRY country, T5.ZIPCODE zipcode, 'Account' sblbctype, T2.BU_ID visibilityid FROM dbo.S_PARTY T1 INNER JOIN dbo.S_ORG_EXT T2 ON T1.ROW_ID = T2.PAR_ROW_ID INNER JOIN dbo.S_ACCNT_POSTN T3 ON T2.PR_POSTN_ID = T3.POSITION_ID AND T2.ROW_ID = T3.OU_EXT_ID INNER JOIN dbo.S_PARTY T4 ON T3.POSITION_ID = T4.ROW_ID LEFT OUTER JOIN dbo.S_POSTN T9 ON T3.POSITION_ID = T9.PAR_ROW_ID LEFT OUTER JOIN dbo.S_ADDR_ORG T5 ON T2.PR_ADDR_ID=T5.ROW_ID LEFT OUTER JOIN dbo.S_USER T10 ON T9.PR_EMP_ID = T10.PAR_ROW_ID LEFT OUTER JOIN dbo.S_CONTACT T11 ON T11.PAR_ROW_ID=T2.LAST_UPD_BY WHERE (T2.INT_ORG_FLG != 'Y' OR T2.PRTNR_FLG = 'Y')

Products Query

SELECT
'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Products+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Product+List+Applet&SWERowId0='+c.ROW_ID URL,
'US' LANG,
c.LAST_UPD LASTMODIFIEDDATE,
'text/html' CONTENTTYPE,
c.ROW_ID "KEY",
coalesce('<b>Name:</b> '+ c.NAME,'<null>')+coalesce(',<br><b>Part Number:</b> '+c.VENDR_PART_NUM,'<null>')+coalesce(',<br><b>Catalog/Category:</b> '+ c2.NAME,'<null>') SOLUTION,
c.DESC_TEXT description,
c.ROW_ID sblrowid,
c.CREATED created_on,
c.CREATED_BY createdby,
c.NAME title,
'Product Catalog' sblbctype,
c.VENDR_PART_NUM name,
c.VENDR_PART_NUM + ' ' + c3.PROD_ID + ' ' + c3.CTLG_CAT_ID summary,
c.BU_ID visibilityid,
c2.NAME sblvisibilityinfo,
c.VERSION type
 
FROM
dbo.S_PROD_INT c
INNER JOIN      dbo.S_CTLG_CAT_PROD c3 ON c3.PROD_ID=c.ROW_ID
INNER JOIN      dbo.S_CTLG_CAT c2      ON c2.ROW_ID=c3.CTLG_CAT_ID
LEFT OUTER JOIN dbo.S_CONTACT c4       ON c4.PAR_ROW_ID=c.LAST_UPD_BY

Here is the same query formatted as a single line that could be cut and paste into the Oracle SES administration tool:

SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Products+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Product+List+Applet&SWERowId0='+c.ROW_ID URL, 'US' LANG, c.LAST_UPD LASTMODIFIEDDATE, 'text/html' CONTENTTYPE, c.ROW_ID "KEY", coalesce('<b>Name:</b> '+ c.NAME,'<null>')+coalesce(',<br><b>Part Number:</b> '+c.VENDR_PART_NUM,'<null>')+coalesce(',<br><b>Catalog/Category:</b> '+ c2.NAME,'<null>') SOLUTION, c.DESC_TEXT description, c.ROW_ID sblrowid, c.CREATED created_on, c.CREATED_BY createdby, c.NAME title, 'Product Catalog' sblbctype, c.VENDR_PART_NUM name, c.VENDR_PART_NUM + ' ' + c3.PROD_ID + ' ' + c3.CTLG_CAT_ID summary, c.BU_ID visibilityid, c2.NAME sblvisibilityinfo, c.VERSION type FROM dbo.S_PROD_INT c INNER JOIN dbo.S_CTLG_CAT_PROD c3 ON c3.PROD_ID=c.ROW_ID INNER JOIN dbo.S_CTLG_CAT c2 ON c2.ROW_ID=c3.CTLG_CAT_ID LEFT OUTER JOIN dbo.S_CONTACT c4 ON c4.PAR_ROW_ID=c.LAST_UPD_BY

Literature Query

SELECT
'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Sales+Tools+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Sales+Tool+List+Applet&SWERowId0='+c.ROW_ID URL,
'US' LANG,
c.LAST_UPD LASTMODIFIEDDATE,
'text/html' CONTENTTYPE,
c.LAST_UPD created_on,
c.LAST_UPD_BY lastupdatedby,
c.ROW_ID "KEY",
coalesce('<b>Name:</b> '+c.NAME,'<null>')+coalesce(',<br><b>Catalog/Category:</b> '+c4.NAME,'<null>') SOLUTION,
c.DESC_TEXT description,
c.NAME title,
c.NAME name,
c.FILE_REV_NUM +'' + c3.LIT_ID + ''+ c3.CTLG_CAT_ID + ''+ c4.ROW_ID + '' + c4.NAME summary,
c.LIT_CD "type",
c.BU_ID visibilityid,
c4.NAME sblvisibilityinfo,
'Sales Tool' sblbctype
 
FROM
dbo.S_LIT c
INNER JOIN      dbo.S_CTLG_CAT_LIT c3 ON c3.LIT_ID=c.ROW_ID
INNER JOIN      dbo.S_CTLG_CAT c4 ON c4.ROW_ID=c3.CTLG_CAT_ID
LEFT OUTER JOIN dbo.S_CONTACT c5 ON c5.PAR_ROW_ID=c.LAST_UPD_BY

Here is the same query formatted as a single line that could be cut and paste into the Oracle SES administration tool:

SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Sales+Tools+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Sales+Tool+List+Applet&SWERowId0='+c.ROW_ID URL, 'US' LANG, c.LAST_UPD LASTMODIFIEDDATE, 'text/html' CONTENTTYPE, c.LAST_UPD created_on, c.LAST_UPD_BY lastupdatedby, c.ROW_ID "KEY", coalesce('<b>Name:</b> '+c.NAME,'<null>')+coalesce(',<br><b>Catalog/Category:</b> '+c4.NAME,'<null>') SOLUTION, c.DESC_TEXT description, c.NAME title, c.NAME name, c.FILE_REV_NUM +'' + c3.LIT_ID + ''+ c3.CTLG_CAT_ID + ''+ c4.ROW_ID + '' + c4.NAME summary, c.LIT_CD "type", c.BU_ID visibilityid, c4.NAME sblvisibilityinfo, 'Sales Tool' sblbctype FROM dbo.S_LIT c INNER JOIN dbo.S_CTLG_CAT_LIT c3 ON c3.LIT_ID=c.ROW_ID INNER JOIN dbo.S_CTLG_CAT c4 ON c4.ROW_ID=c3.CTLG_CAT_ID LEFT OUTER JOIN dbo.S_CONTACT c5 ON c5.PAR_ROW_ID=c.LAST_UPD_BY

Solution Query

SELECT
'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Solution+List+View&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Solution+List+Applet&SWERowId0='+c.ROW_ID URL,
'US' LANG,
c.LAST_UPD LASTMODIFIEDDATE,
'text/html' CONTENTTYPE,
c.ROW_ID "KEY",
coalesce('<b>Name:</b> '+c.NAME,'<null>')+coalesce(',<br><b>Catalog/Category: </b>'+t.NAME,'<null>') + coalesce(',<br><b>Question: </b>'+ cast(c.FAQ_QUES_TEXT as nvarchar(4000)),'<null>')+ coalesce(',<br><b>Resolution: </b>'+ cast(c.RESOLUTION_TEXT as nvarchar(4000)),'<null>') SOLUTION,
c.ROW_ID sblrowid,
c.CREATED created_on,
c.CREATED_BY createdby,
c.NAME title,
c.FAQ_QUES_TEXT description,
c.RESOLUTION_TEXT summary,
c.TYPE_CD "type",
c.STATUS_CD status,
usr.LOGIN owner,
usr.LOGIN alias,
t.NAME location,
'Solution' sblbctype
 
FROM
dbo.S_RESITEM c
INNER JOIN dbo.S_USER usr          ON c.CREATED_BY = usr.PAR_ROW_ID
INNER JOIN dbo.S_CTLGCT_RESITM cct ON c.ROW_ID = cct.RES_ITEM_ID
INNER JOIN dbo.S_CTLG_CAT t        ON t.ROW_ID = cct.CTLG_CAT_ID
INNER JOIN dbo.S_CONTACT c2        ON c2.PAR_ROW_ID=c.LAST_UPD_BY

Here is the same query formatted as a single line that could be cut and paste into the Oracle SES administration tool:

SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Solution+List+View&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Solution+List+Applet&SWERowId0='+c.ROW_ID URL, 'US' LANG, c.LAST_UPD LASTMODIFIEDDATE, 'text/html' CONTENTTYPE, c.ROW_ID "KEY", coalesce('<b>Name:</b> '+c.NAME,'<null>')+coalesce(',<br><b>Catalog/Category: </b>'+t.NAME,'<null>') + coalesce(',<br><b>Question: </b>'+ cast(c.FAQ_QUES_TEXT as nvarchar(4000)),'<null>')+ coalesce(',<br><b>Resolution: </b>'+ cast(c.RESOLUTION_TEXT as nvarchar(4000)),'<null>') SOLUTION, c.ROW_ID sblrowid, c.CREATED created_on, c.CREATED_BY createdby, c.NAME title, c.FAQ_QUES_TEXT description, c.RESOLUTION_TEXT summary, c.TYPE_CD "type", c.STATUS_CD status, usr.LOGIN owner, usr.LOGIN alias, t.NAME location, 'Solution' sblbctype FROM dbo.S_RESITEM c INNER JOIN dbo.S_USER usr ON c.CREATED_BY = usr.PAR_ROW_ID INNER JOIN dbo.S_CTLGCT_RESITM cct ON c.ROW_ID = cct.RES_ITEM_ID INNER JOIN dbo.S_CTLG_CAT t ON t.ROW_ID = cct.CTLG_CAT_ID INNER JOIN dbo.S_CONTACT c2 ON c2.PAR_ROW_ID=c.LAST_UPD_BY

Service Request Query

SELECT
'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Service+Request+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Service+Request+List+Applet&SWERowId0='+c.ROW_ID URL,
'US' LANG,
c.LAST_UPD LASTMODIFIEDDATE,
'text/html' CONTENTTYPE,
c.ROW_ID "KEY",
coalesce('<b>SR Number:</b> '+c.SR_NUM,'<null>')+coalesce(',<br><b>Summary:</b> '+c.SR_TITLE,'<null>')+coalesce(',<br><b>Status:</b> '+c.SR_STAT_ID,'<null>')+coalesce(',<br><b>Area:</b> '+c.SR_AREA,'<null>')+coalesce(',<br><b>Subarea:</b> '+c.SR_SUB_AREA,'<null>')+coalesce(',<br><b>Resolution:</b> '+c.RESOLUTION_CD,'<null>') SOLUTION,
c.DESC_TEXT description,
c.BU_ID visibilityid,
c.ROW_ID sblrowid,
c.CREATED created_on,
c.CREATED_BY createdby,
c.SR_TITLE summary,
a.NAME orgName,
c.SR_AREA code01,
a.OU_NUM csn,
contact.FST_NAME firstName,
contact.LAST_NAME lastName,
c.SR_NUM title,
c.SR_STAT_ID status,
c.SR_SUB_AREA code02,
usr.LOGIN owner,
'Service Request' sblbctype
 
FROM
dbo.S_ORG_EXT a
INNER JOIN      dbo.S_SRV_REQ c        ON a.PAR_ROW_ID= c.CST_OU_ID
LEFT OUTER JOIN dbo.S_CONTACT contact  ON contact.PAR_ROW_ID =c.CST_CON_ID
LEFT OUTER JOIN dbo.S_USER usr         ON usr.PAR_ROW_ID = c.OWNER_EMP_ID
LEFT OUTER JOIN dbo.S_CONTACT c2       ON c2.PAR_ROW_ID=c.LAST_UPD_BY

Here is the same query formatted as a single line that could be cut and paste into the Oracle SES administration tool:

SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Service+Request+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Service+Request+List+Applet&SWERowId0='+c.ROW_ID URL, 'US' LANG, c.LAST_UPD LASTMODIFIEDDATE, 'text/html' CONTENTTYPE, c.ROW_ID "KEY", coalesce('<b>SR Number:</b> '+c.SR_NUM,'<null>')+coalesce(',<br><b>Summary:</b> '+c.SR_TITLE,'<null>')+coalesce(',<br><b>Status:</b> '+c.SR_STAT_ID,'<null>')+coalesce(',<br><b>Area:</b> '+c.SR_AREA,'<null>')+coalesce(',<br><b>Subarea:</b> '+c.SR_SUB_AREA,'<null>')+coalesce(',<br><b>Resolution:</b> '+c.RESOLUTION_CD,'<null>') SOLUTION, c.DESC_TEXT description, c.BU_ID visibilityid, c.ROW_ID sblrowid, c.CREATED created_on, c.CREATED_BY createdby, c.SR_TITLE summary, a.NAME orgName, c.SR_AREA code01, a.OU_NUM csn, contact.FST_NAME firstName, contact.LAST_NAME lastName, c.SR_NUM title, c.SR_STAT_ID status, c.SR_SUB_AREA code02, usr.LOGIN owner, 'Service Request' sblbctype FROM dbo.S_ORG_EXT a INNER JOIN dbo.S_SRV_REQ c ON a.PAR_ROW_ID= c.CST_OU_ID LEFT OUTER JOIN dbo.S_CONTACT contact ON contact.PAR_ROW_ID =c.CST_CON_ID LEFT OUTER JOIN dbo.S_USER usr ON usr.PAR_ROW_ID = c.OWNER_EMP_ID LEFT OUTER JOIN dbo.S_CONTACT c2 ON c2.PAR_ROW_ID=c.LAST_UPD_BY

Contacts Query

SELECT
'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Contacts+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Contact+List+Applet&SWERowId0='+c.PAR_ROW_ID URL,
'US' LANG,
c.LAST_UPD LASTMODIFIEDDATE,
'text/html' CONTENTTYPE,
c.PAR_ROW_ID "KEY",
'<b>Name: </b>'+coalesce(c.LAST_NAME,'<null>')+' '+coalesce(c.FST_NAME,'<null>')+coalesce(',<br><b>Phone No.:</b> '+c.WORK_PH_NUM,'<null>')+coalesce(',<br><b>E-Mail ID:</b> '+ c.EMAIL_ADDR,'<null>') SOLUTION,
t.PERS_AGENDA agenda,
c.PAR_ROW_ID sblrowid,
c.CREATED created_on,
c.CREATED_BY createdby,
a.NAME+'#'+c.JOB_TITLE PATH,
c.LAST_NAME+' '+c.FST_NAME title,
c.LAST_NAME lastName,
c.FST_NAME firstName,
c.EMP_ID owner,
c.EMAIL_ADDR emailID,
c.WORK_PH_NUM phoneNumber02,
'Contacts' sblbctype,
t.ACCOMPLISH summary,
addr.ZIPCODE zipcode,
addr.COUNTRY country,
party.NAME name,
addr.ADDR street,
c.BU_ID visibilityid
 
FROM
dbo.S_PARTY party
INNER JOIN      dbo.S_CONTACT c ON party.ROW_ID = c.PAR_ROW_ID
INNER JOIN      dbo.S_POSTN_CON T3 ON c.PR_POSTN_ID = T3.POSTN_ID AND c.ROW_ID = T3.CON_ID
INNER JOIN      dbo.S_PARTY T4 ON T3.POSTN_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT a ON a.PAR_ROW_ID = c.PR_DEPT_OU_ID
LEFT OUTER JOIN dbo.S_ADDR_ORG addr ON addr.ROW_ID = c.PR_PER_ADDR_ID
LEFT OUTER JOIN dbo.S_CONTACT_T t ON c.ROW_ID=t.PAR_ROW_ID
LEFT OUTER join dbo.S_CONTACT c2 ON c2.ROW_ID=c.LAST_UPD_BY
 
WHERE 
(c.PRIV_FLG = 'N')

Here is the same query formatted as a single line that could be cut and paste into the Oracle SES administration tool:

SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Contacts+across+Organizations&SWERF=1&SWEHo=<Host Name>&SWEBU=1&SWEApplet0=Contact+List+Applet&SWERowId0='+c.PAR_ROW_ID URL, 'US' LANG, c.LAST_UPD LASTMODIFIEDDATE, 'text/html' CONTENTTYPE, c.PAR_ROW_ID "KEY", '<b>Name: </b>'+coalesce(c.LAST_NAME,'<null>')+' '+coalesce(c.FST_NAME,'<null>')+coalesce(',<br><b>Phone No.:</b> '+c.WORK_PH_NUM,'<null>')+coalesce(',<br><b>E-Mail ID:</b> '+ c.EMAIL_ADDR,'<null>') SOLUTION, t.PERS_AGENDA agenda, c.PAR_ROW_ID sblrowid, c.CREATED created_on, c.CREATED_BY createdby, a.NAME+'#'+c.JOB_TITLE PATH, c.LAST_NAME+' '+c.FST_NAME title, c.LAST_NAME lastName, c.FST_NAME firstName, c.EMP_ID owner, c.EMAIL_ADDR emailID, c.WORK_PH_NUM phoneNumber02, 'Contacts' sblbctype, t.ACCOMPLISH summary, addr.ZIPCODE zipcode, addr.COUNTRY country, party.NAME name, addr.ADDR street, c.BU_ID visibilityid FROM dbo.S_PARTY party INNER JOIN dbo.S_CONTACT c ON party.ROW_ID = c.PAR_ROW_ID INNER JOIN dbo.S_POSTN_CON T3 ON c.PR_POSTN_ID = T3.POSTN_ID AND c.ROW_ID = T3.CON_ID INNER JOIN dbo.S_PARTY T4 ON T3.POSTN_ID = T4.ROW_ID LEFT OUTER JOIN dbo.S_ORG_EXT a ON a.PAR_ROW_ID = c.PR_DEPT_OU_ID LEFT OUTER JOIN dbo.S_ADDR_ORG addr ON addr.ROW_ID = c.PR_PER_ADDR_ID LEFT OUTER JOIN dbo.S_CONTACT_T t ON c.ROW_ID=t.PAR_ROW_ID LEFT OUTER join dbo.S_CONTACT c2 ON c2.ROW_ID=c.LAST_UPD_BY WHERE (c.PRIV_FLG = 'N')

Notes:

• These queries are for SQL Server database. The query should be changed for Oracle database by replacing the string concatenation operator '+' with '||'. Also, replace the table owner name dbo with the appropriate table owner name in Oracle.

• Replace <HOST NAME> with the name of the host where Siebel is installed.

• The values of the parameters SWEView and SWEApplet0 in the queries are the names of views and applets in a default Siebel installation. These should be changed appropriately if different names were used while installing Siebel 7.8.

• Add appropriate WHERE clauses to these queries depending on the search specification of views, applets and business components in the Siebel system. For example, if the Siebel system is configured to locate only internal service requests, then append the WHERE clause to the query for Service Request business component as follows: WHERE c.SR_TYPE_CD = 'Internal'.

Setting Up Siebel 8 Sources

The Siebel 8 connector uses the Oracle SES XML connector framework, where searching is based on Siebel data available as XML feeds.

See Also:

• "Overview of XML Connector Framework"

• Appendix A in the Siebel Search Administration Guide for searchable business components:

  http://download.oracle.com/docs/cd/B40099_01/80Siebel_HTML/books/Search/SearchTOC.html

• Siebel documentation on Oracle Technology Network (OTN) for information about supported Siebel modules:

  http://www.oracle.com/technology/documentation

Setting Up Identity Management for Siebel 8

Activate the identity plug-in on the Global Settings - Identity Management Setup page. Select Identity Plugin Manager for Siebel 8 and click Activate.

1. Enter values for the following parameters:

   • Siebel 8 authentication Web service endpoint: HTTP endpoint of the Siebel Web service that provides the authentication service

   • Siebel 8 validation Web service endpoint: HTTP endpoint of the Siebel Web service that provides the user validation service

   • User ID: Admin user ID for accessing the user validation service

   • Password: Admin password for accessing the user validation service

2. Click Finish.

Creating a Siebel 8 Source

Create a Siebel 8 source on the Home - Sources page. Select Siebel 8 from the Source Type list, and click Create.

1. Enter the values for the following parameters:

   • Configuration URL: File URL of the XML configuration file providing details about the source, such as the data feed type, location, security attributes, and so on.

     Obtain this file from Siebel administrator and save it on the computer on which Oracle SES is installed. Enter the configuration URL as file://localhost/<Absolute path of the configuration file>. For example: file://localhost/private/oracle/config.xml/.

   • Authentication Type: The standard Java authentication type used by the application serving the control and data feed: BASIC for basic authentication, FORM for form-based authentication, and NATIVE for proprietary XML over HTTP authentication. This parameter is relevant only when the feeds are accessed over HTTP. Leave this parameter blank if the feeds are accessed over file or FTP protocols.

   • User ID: User ID to login to the FTP server, if the data feeds are to be accessed over FTP. The access details of the data feed are specified in the configuration file. This can be obtained from Siebel administrator.

   • Password: Password to login to the FTP server. This can be obtained from Siebel administrator.

   • Realm: The realm of the application serving the feeds. This parameter is relevant only when the control and data feeds are accessed over HTTP, and is mandatory when the authentication type is BASIC.

   • Scratch Directory: A directory, in the computer where Oracle SES is installed to temporarily write the status logs.

   • Maximum number of connection attempts: Maximum number of attempts to connect to the target server to access the data feed.

2. Click Next.

3. Enter the values for the authorization manager plug-in parameters:

   • Siebel 8 authorization Web service endpoint: Webs service endpoint of the Siebel Web service that provides the authorization service

   • User ID: Admin user ID for accessing the authorization service

   • Password: Admin password for accessing the authorization service

4. Click Create.