The 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.
The Siebel 7.8 connector supports Siebel installations on the following databases:
Oracle
SQL Server
Views and queries to be crawled must contain the columns described in Table 9-7.
Table 9-7 Siebel 7.8 Source Required Columns
Any other columns in the views or queries is considered an attribute of the document.
If Siebel 7.8 is installed over a Microsoft SQL Server database, then Oracle SES must have access to the JDBC driver for SQL Server.
To install the SQL Server JDBC driver for Oracle SES:
Download the SQL Server 2005 JDBC driver 3.0 from http://www.microsoft.com/download/en/details.aspx?id=21599
Follow the instructions at the same location to install the driver.
Copy sqljdbc.jar
from the installed directory to the location ORACLE_HOME
/search/lib/plugins/oracleapplications/
.
If different JDBC drivers are required for JRE 1.5 and JRE 1.6, then perform the following:
Download both the driver jars into ORACLE_HOME
/search/lib/plugins/oracleapplications
in Oracle SES.
To the CLASSPATH
element of ORACLE_HOME
/search/config/searchctl.conf
, add an entry with the complete path of the JDBC driver jar sqljdbc4.jar
(JRE 1.6).
Restart the middle tier.
This procedure enables Oracle SES to validate users by querying the Siebel 7.8 identity management system.
To activate the Siebel 7.8 identity plug-in:
On the Global Settings page, select Identity Management Setup under the System heading.
The Global Settings - Identity Management Setup page is displayed.
Select Siebel 7.8 and click Activate.
Enter values for the parameters described in Table 9-8, then click Finish.
Table 9-8 Siebel 7.8 Identity Management Parameters
Parameter | Value |
---|---|
Authentication and Validation Database Connection String |
JDBC connection string for the Siebel 7.8 database for authenticating and validating users. |
User ID |
Administrator ID for the Siebel 7.8 database (specified in Authentication and Validation Database Connection String) for validating users. |
Password |
Password for User ID. |
User Validation Query |
SQL query for validating users. The query must return 1 if the user is valid, and null otherwise. Use a question mark (?) as a placeholder for the user name. This query replaces this default query: SELECT 1 FROM dbo.S_USER WHERE LOGIN=upper(?) |
Oracle SES supports these Siebel 7.8 secured business components: Activity, Accounts, Contacts, Literature, Products, and Service Request.
To create a source for Siebel 7.8 secured business components:
On the Home page, click the Sources secondary tab to display the Sources page.
Select Siebel 7.8 from the Source Type list, then click Create to display Step 1 Parameters.
Complete the form, entering values for the parameters described in Table 9-9.
Click Next to display Step 2 Authorization.
Provide values for the authorization parameters described in Table 9-10.
Click Create.
Table 9-9 Siebel 7.8 Source Parameters (Step 1)
Parameter | Description |
---|---|
Database Connection String |
JDBC connection string for the Siebel 7.8 database from which the content has to be crawled. |
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. Leave this parameter blank. |
Document Count |
Maximum number of documents to be crawled before indexing. Enter –1 for this parameter. |
Query |
Query projecting the columns for crawling. Specify the query for the required business component given in "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. Leave this parameter blank. |
URL Prefix |
String to prefix the content of URL column to form a complete display URL for the document |
Cache File |
Local file to which the contents can be temporarily cached while crawling. |
Path Separator |
Path separator character in the document path string. Leave this parameter blank. |
Parse Attributes |
Extracts attribute values from the document content specified in the SOLUTION or CONTENT column. Enter |
Grant Security Attributes |
Space-separated list of grant security attributes. Enter |
Deny Security Attributes |
Space-separated list of deny security attributes. Leave this parameter blank. |
Remove Deleted Documents |
Set this parameter to Set this parameter to Crawling may take longer when this parameter is set to |
Attachment Link Authentication Type |
Leave this parameter blank. |
Attachment Link User ID |
User ID for accessing the link in the attachment link column. Leave this parameter blank. |
Attachment Link Password |
Leave this parameter blank |
Attachment Link Realm |
Realm of the application serving the link in the attachment link column. Leave this parameter blank. |
Table 9-10 Siebel 7.8 Authorization Parameters (Step 2)
Parameter | Value |
---|---|
Authorization Database Connection String |
JDBC connection string for the authorization database. The values of the visibility IDs for users are retrieved from this database. Typically, this connection string is the same as the Authentication and Validation Database Connection String on Page 1. |
User ID |
Administrator user ID for the authorization database specified in Authorization Database Connection String |
Password |
Password for User ID. |
Authorization Query |
SQL query to retrieve the values of visibility IDs. Use a question mark (?) as a placeholder for the user name. See Example 9-1 for more information about authorization queries. |
Example 9-1 Siebel 7.8 Authorization Queries
The following query is the default authorization query that can be used for the Service Request, Accounts, Products, Literature, Solution, and Contacts business components:
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(?)
Use the following authorization query for Activity business components:
SELECT DISTINCT usr.LOGIN visibilityid FROM S_PARTY_RPT_REL rpt, S_POSTN postn, S_USER usr, S_PARTY pty WHERE rpt.PARTY_ID IN ( SELECT T3.PR_HELD_POSTN_ID FROM dbo.S_PARTY T1 INNER JOIN dbo.S_EMP_PER T2 ON T1.ROW_ID = T2.PAR_ROW_ID INNER JOIN dbo.S_CONTACT T3 ON T1.ROW_ID = T3.PAR_ROW_ID INNER JOIN dbo.S_USER T4 ON T1.ROW_ID = T4.PAR_ROW_ID WHERE (T3.EMP_FLG = 'Y') AND T4.LOGIN=upper(?)) AND rpt.SUB_PARTY_ID = postn.PAR_ROW_ID AND postn.PR_EMP_ID = usr.ROW_ID AND usr.PAR_ROW_ID = pty.ROW_ID
Oracle SES supports Solution as a public business component.
To create a source for Siebel 7.8 public business components:
On the Home page, click the Sources secondary tab to display the Sources page.
Select Siebel 7.8 (Public) from the Source Type list, then click Create.
Complete the form, entering values for the parameters described in Table 9-9. The two parameters to grant and deny security attributes are omitted from the configuration of a public source.
Click Create.
This section includes the queries to crawl the Siebel 7.8 business components supported by Oracle SES:
Note:
The Siebel 7.8 crawling queries provided in this section are supported only when used with default Siebel 7.8 database schema. Neither these queries nor any modifications to these queries are supported in any modified Siebel 7.8 database schema.The queries appear in two forms: A multi-line query for readability and a single-line version to cut-and-paste into the Query parameter. Queries use SQL Server syntax unless otherwise noted and must be modified slightly for use with Oracle Database.
To use a sample query as the value of the Query parameter:
Replace HostName
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. Change them as required 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'
.
To modify a query for use in Oracle Database instead of SQL Server:
Replace the string concatenation operator '+' with '||'.
Replace the table owner name dbo
with the appropriate table owner name in Oracle Database.
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Service+Request+across+Organizations
&SWERF=1
&SWEHo=HostName
&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
The following is the same query formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Service+Request+across+Organizations&SWERF=1&SWEHo=HostName&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
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Accounts+across+Organizations
&SWERF=1
&SWEHo=HostName
&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+' '+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')
The following is the same query formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Accounts+across+Organizations&SWERF=1&SWEHo=HostName&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+' '+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')
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Products+across+Organizations
&SWERF=1
&SWEHo=HostName
&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
The following is the same query formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Products+across+Organizations&SWERF=1&SWEHo=HostName&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
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Sales+Tools+across+Organizations
&SWERF=1
&SWEHo=HostName
&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
The following is the same query formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Sales+Tools+across+Organizations&SWERF=1&SWEHo=HostName&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
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Solution+List+View
&SWERF=1
&SWEHo=HostName
&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
The following is the same query formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Solution+List+View&SWERF=1&SWEHo=HostName&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
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Service+Request+across+Organizations
&SWERF=1
&SWEHo=HostName
&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
The following is the same query formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Service+Request+across+Organizations&SWERF=1&SWEHo=HostName&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
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Contacts+across+Organizations
&SWERF=1
&SWEHo=HostName
&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')
The following is the same query formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Contacts+across+Organizations&SWERF=1&SWEHo=HostName&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')
For the queries shown in this section, before starting the crawl, you must replace HostName
with the name or IP address of the Siebel host computer.
The following query can be used with Oracle Database:
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Activity+List+View
&SWERF=1
&SWEHo=HostName
&SWEBU=1
&SWEApplet0=Activity+Form+Applet
&SWERowId0='+T1.ROW_ID URL,
'US' LANG,
T1.ROW_ID "KEY",
T1.LAST_UPD LASTMODIFIEDDATE,
T1.NAME title,
coalesce('Activity Name:'+T1.NAME,'<null>')
+coalesce(', Activity Type:'+T1.TODO_CD,'<null>')
+coalesce(', Creation Date:'+convert(varchar,T1.CREATED,103),'<null>')
+coalesce(', Activity Status:'+T1.EVT_STAT_CD,'<null>')
+coalesce(', ActivityPriority:'+T1.EVT_PRIORITY_CD,'<null>')
+coalesce(', Activity Owner: '+T1.OWNER_LOGIN,'<null>') CONTENT,
T1.NAME ActivityName,
T1.CREATED CreatedOn,
T1.CREATED_BY CreatedBy,
T1.LAST_UPD_BY LastUpdatedBy,
T1.TODO_CD ActivityType,
T1.EVT_STAT_CD ActivityStatus,
T1.EVT_PRIORITY_CD ActivityPriority,
T1.TARGET_OU_ID Organization,
T1.OWNER_LOGIN visibilityid,
T1.OWNER_LOGIN ActivityOwner,
T1.ROW_ID SBLROWID,
T1.TODO_PLAN_START_DT StartDate,
T1.TODO_PLAN_END_DT EndDate,
T1.TODO_DUE_DT DueDate
FROM
dbo.S_EVT_ACT T1
INNER JOIN dbo.S_ORG_EXT T2 ON T2.PAR_ROW_ID = T1.TARGET_OU_ID
LEFT OUTER JOIN dbo.S_USER usr ON usr.LOGIN = T1.OWNER_LOGIN
WHERE
((T1.APPT_REPT_REPL_CD IS NULL)
AND ((T1.TEMPLATE_FLG != 'Y')
OR (T1.TEMPLATE_FLG = NULL)))
The following is the same query for use with Oracle Database, but formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Activity+List+View&SWERF=1&SWEHo=HostName&SWEBU=1&SWEApplet0=Activity+Form+Applet&SWERowId0='+T1.ROW_ID URL,'US' LANG,T1.ROW_ID "KEY", T1.LAST_UPD LASTMODIFIEDDATE, T1.NAME title , coalesce('Activity Name:'+T1.NAME,'<null>')+coalesce(', Activity Type:'+T1.TODO_CD,'<null>')+coalesce(', Creation Date:'+convert(varchar,T1.CREATED,103),'<null>')+coalesce(', Activity Status:'+T1.EVT_STAT_CD,'<null>')+coalesce(', ActivityPriority:'+T1.EVT_PRIORITY_CD,'<null>')+coalesce(', Activity Owner: '+T1.OWNER_LOGIN,'<null>') CONTENT,T1.NAME ActivityName,T1.CREATED CreatedOn, T1.CREATED_BY CreatedBy,T1.LAST_UPD_BY LastUpdatedBy, T1.TODO_CD ActivityType, T1.EVT_STAT_CD ActivityStatus,T1.EVT_PRIORITY_CD ActivityPriority, T1.TARGET_OU_ID Organization,T1.OWNER_LOGIN visibilityid, T1.OWNER_LOGIN ActivityOwner,T1.ROW_ID SBLROWID,T1.TODO_PLAN_START_DT StartDate, T1.TODO_PLAN_END_DT EndDate, T1.TODO_DUE_DT DueDate FROM dbo.S_EVT_ACT T1 INNER JOIN dbo.S_ORG_EXT T2 ON T2.PAR_ROW_ID = T1.TARGET_OU_ID LEFT OUTER JOIN dbo.S_USER usr ON usr.LOGIN = T1.OWNER_LOGIN WHERE ((T1.APPT_REPT_REPL_CD IS NULL) AND ((T1.TEMPLATE_FLG != 'Y') OR (T1.TEMPLATE_FLG = NULL)))
The following query can be used with SQL Server:
SELECT
'callcenter_enu/start.swe?SWECmd=GotoView
&SWEView=All+Activity+List+View
&SWERF=1
&SWEHo=HostName
&SWEBU=1
&SWEApplet0=Activity+Form+Applet
&SWERowId0='+T1.ROW_ID URL,
'US' LANG,
T1.ROW_ID "KEY",
T1.LAST_UPD LASTMODIFIEDDATE,
T1.NAME title,
coalesce('Activity Name:'+T1.NAME,'<null>')
+coalesce(', Activity Type:'+T1.TODO_CD,'<null>')
+coalesce(', Creation Date:'+convert(varchar,T1.CREATED,103),'<null>')
+coalesce(', Activity Status:'+T1.EVT_STAT_CD,'<null>')
+coalesce(', ActivityPriority:'+T1.EVT_PRIORITY_CD,'<null>')
+coalesce(', Activity Owner: '+T1.OWNER_LOGIN,'<null>') CONTENT,
T1.NAME ActivityName,
T1.CREATED CreatedOn,
T1.CREATED_BY CreatedBy,
T1.LAST_UPD_BY LastUpdatedBy,
T1.TODO_CD ActivityType,
T1.EVT_STAT_CD ActivityStatus,
T1.EVT_PRIORITY_CD ActivityPriority,
T1.TARGET_OU_ID Organization,
T1.OWNER_LOGIN visibilityid,
T1.OWNER_LOGIN ActivityOwner,
T1.ROW_ID SBLROWID,
T1.TODO_PLAN_START_DT StartDate,
T1.TODO_PLAN_END_DT EndDate,
T1.TODO_DUE_DT DueDate
FROM
dbo.S_EVT_ACT T1
INNER JOIN dbo.S_ORG_EXT T2 ON T2.PAR_ROW_ID = T1.TARGET_OU_ID
LEFT OUTER JOIN dbo.S_USER usr ON usr.LOGIN = T1.OWNER_LOGIN
WHERE
((T1.APPT_REPT_REPL_CD IS NULL)
AND ((T1.TEMPLATE_FLG != 'Y')
OR (T1.TEMPLATE_FLG = NULL)))
The following is the same query for use with SQL Server, but formatted as a single line that you can cut and paste into the Oracle SES Administration GUI:
SELECT 'callcenter_enu/start.swe?SWECmd=GotoView&SWEView=All+Activity+List+View&SWERF=1&SWEHo=HostName&SWEBU=1&SWEApplet0=Activity+Form+Applet&SWERowId0='+T1.ROW_ID URL,'US' LANG,T1.ROW_ID "KEY", T1.LAST_UPD LASTMODIFIEDDATE, T1.NAME title, coalesce('Activity Name:'+T1.NAME,'<null>')+coalesce(', Activity Type:'+T1.TODO_CD,'<null>')+coalesce(', Creation Date:'+convert(varchar,T1.CREATED,103),'<null>')+coalesce(', Activity Status:'+T1.EVT_STAT_CD,'<null>')+coalesce(', ActivityPriority:'+T1.EVT_PRIORITY_CD,'<null>')+coalesce(', Activity Owner: '+T1.OWNER_LOGIN,'<null>') CONTENT, T1.NAME ActivityName,T1.CREATED CreatedOn, T1.CREATED_BY CreatedBy,T1.LAST_UPD_BY LastUpdatedBy, T1.TODO_CD ActivityType, T1.EVT_STAT_CD ActivityStatus,T1.EVT_PRIORITY_CD ActivityPriority, T1.TARGET_OU_ID Organization,T1.OWNER_LOGIN visibilityid, T1.OWNER_LOGIN ActivityOwner,T1.ROW_ID SBLROWID, T1.TODO_PLAN_START_DT StartDate, T1.TODO_PLAN_END_DT EndDate, T1.TODO_DUE_DT DueDate FROM dbo.S_EVT_ACT T1 INNER JOIN dbo.S_ORG_EXT T2 ON T2.PAR_ROW_ID = T1.TARGET_OU_ID LEFT OUTER JOIN dbo.S_USER usr ON usr.LOGIN = T1.OWNER_LOGIN WHERE ((T1.APPT_REPT_REPL_CD IS NULL) AND ((T1.TEMPLATE_FLG != 'Y') OR (T1.TEMPLATE_FLG = NULL)))