Skip Headers
Oracle® Secure Enterprise Search Administrator's Guide
11g Release 2 (11.2.1)

Part Number E17332-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Setting Up Siebel 7.8 Sources

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:

Requirements for Siebel 7.8 Sources

Views and queries to be crawled must contain the columns described in Table 8-12.

Table 8-12 Siebel 7.8 Source Required Columns

Column Name Datatype 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 or CONTENT

VARCHAR2 or CLOB

Document content.

LASTMODIFIEDDATE

DATE

Last modified date for crawl.

KEY

VARCHAR2

Primary key of the records.

LANG

VARCHAR2

Document language, such as en for English or ja for Japanese.


Any other columns in the views or queries is considered an attribute of the document.

Installing the JDBC Driver for Microsoft SQL Server

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: 

  1. Download the SQL Server 2005 JDBC driver 1.1 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 sqljdbc4.jar for JRE 1.6 from the installed directory to ORACLE_HOME/search/lib/plugins/oracleapplications/.

  4. Add sqljdbc4.jar to the classpath in MANIFEST.MF of appsjdbc.jar and DBCrawler.jar.

  5. Restart the middle tier.

Starting the Decompression Server

If you are using Windows Native Access for the decompression server, then you can skip this procedure.

To start the decompression server on the Siebel host: 

  1. Install JDK if it is not already installed on the Siebel host.

  2. Set the JAVA_HOME environment variable to the JDK folder.

  3. Update the PATH environment variable to begin with JAVA_HOME\bin.

  4. Start the RMI registry:

    • To use default port 1099, double-click JAVA_HOME\bin\rmiregistry.exe.

    • To use a different port number, execute this command from the Windows command prompt, replacing port_number as desired:

      %JAVA_HOME%\bin\rmiregistry port_number
      
  5. Copy this file from the Oracle SES host to the Siebel host and unzip it into the SiebelDecompSvr folder:

    ORACLE_HOME/search/lib/plugins/oracleapplications/DecompressionServer.zip
    
  6. Update SiebelDecompSvr\startDecompServer.bat by replacing the placeholders in the java command with the following information:

    • Absolute path to the sseunzip.exe decompression utility.

    • Folder where Siebel attachment files can be temporarily decompressed.

    • Level for logging decompression server output: FINE, INFO, WARNING, SEVERE. Default is INFO.

    • For user credential-based security, the user name specified in the Oracle SES configuration. See Table 8-13, "Siebel 7.8 Identity Management Parameters".

    • For user credential-based security, the password specified in the Oracle SES configuration.

  7. For RMI security, replace the placeholders in the sec.policy security policy file with the IP address or host name of the Oracle SES instances that crawls the Siebel attachments.

  8. Double-click SiebelDecompSvr\startDecompServer.bat on the Siebel host to start the decompression server.

Setting Up Identity Management for Siebel 7.8

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: 

  1. On the Global Settings page, select Identity Management Setup under the System heading.

    The Global Settings - Identity Management Setup page is displayed.

  2. Select Siebel 7.8 and click Activate.

  3. Enter values for the parameters described in Table 8-13, then click Finish.

Table 8-13 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(?)

Creating a Secured Siebel 7.8 Source

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: 

  1. On the Home page, click the Sources secondary tab to display the Sources page.

  2. Select Siebel 7.8 from the Source Type list, then click Create to display Step 1 Parameters.

  3. Complete the form, entering values for the parameters described in Table 8-14.

  4. Click Next to display Step 2 Authorization.

  5. Provide values for the authorization parameters described in Table 8-15.

  6. Click Create.

Table 8-14 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 false for this parameter.

Grant Security Attributes

Space-separated list of grant security attributes.

Enter VISIBILITYID for this parameter.

Deny Security Attributes

Space-separated list of deny security attributes.

Leave this parameter blank.

Remove Deleted Documents

Set this parameter to false to optimize incremental crawl. Only records that have changed since the previous crawl are crawled. The documents deleted in the database are not deleted from the Oracle SES index.

Set this parameter to true if records that have been deleted from the database since the previous full crawl, or since a previous incremental crawl with this parameter set to true, should be deleted from the Oracle SES index as well.

Crawling may take longer when this parameter is set to true than when it is set to false.

Attachment Link Authentication Type

Enter NATIVE for user credential-based security; otherwise, enter PUBLIC for this parameter.

Attachment Link User ID

User ID for accessing the link in the attachment link column.

If the Attachment Link Authentication Type is NATIVE and attachments are accessed using RMI, then enter the user name provided in SiebelDecompSvr\startDecompServer.bat; otherwise, leave this parameter blank.

Attachment Link Password

Password for accessing the link in the attachment link column.

If the Attachment Link Authentication Type is NATIVE and Attachments are accessed using RMI, then enter the password provided in SiebelDecompSvr\startDecompServer.bat; otherwise, leave this parameter blank.

Attachment Link Realm

Realm of the application serving the link in the attachment link column.

Leave this parameter blank.


Table 8-15 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 matches 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.

Note: Oracle SES does not allow a password to contain the characters &, \, ", < , or >.

Authorization Query

SQL query to retrieve the values of visibility IDs. Use a question mark (?) as a placeholder for the user name. See Example 8-1 for more information about authorization queries.


Example 8-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

See "Queries to Crawl Siebel 7.8 Business Components".

Creating a Public Siebel 7.8 Source

Oracle SES supports Solution as a public business component.

To create a source for Siebel 7.8 public business components: 

  1. On the Home page, click the Sources secondary tab to display the Sources page.

  2. Select Siebel 7.8 (Public) from the Source Type list, then click Create.

  3. Complete the form, entering values for the parameters described in Table 8-14. The two parameters to grant and deny security attributes are omitted from the configuration of a public 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:

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.

Service Request Attachments

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 

Accounts

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+'&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')

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+'&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

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

Literature

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

Solution

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

Service Request

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

Contacts

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')

Activity

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))) 

Activity Attachment

For the queries shown in this section, before starting the crawl, substitute the following with the appropriate values:

  • Siebel_Host: Siebel host name or IP address.

  • Folder_Path: Absolute path to the folder containing attachment files on the Siebel host.

  • Attachment_Decompressor: Choose the appropriate value:

    • When crawling from Linux or from different Windows domain, use RMI:

      rmi://Siebel_Host[:RMI_Port]/Decompressor
      

      For example:

      rmi://ses5-pc.example.com:2155/Decompressor
      

      RMI_Port is the RMI port number. Specify it only when the RMI registry in the Siebel host is running on a port other than the default port 1099.

      The RMI Decompression Server must be running.

    • When crawling from Windows in the same domain, use Windows Native Access:

      \\Siebel_Host\sseunzip_path
      

      For example:

      \\ses5-pc.example.com\BIN\sseunzip.exe
      

      sseunzip_path is the path to the shared sseunzip.exe utility.

Use this query with Oracle Database:

SELECT
   'callcenter_enu/start.swe?SWECmd=GotoView
      &SWEView=Activity+Attachment+View
      &SWERF=1
      &SWEHo=Host_name
      &SWEBU=1
      &SWEApplet0=Activity+Form+Applet
      &SWERowId0='¦¦T2.ROW_ID¦¦'
      &SWEApplet1=Activity+Attachment+Applet
      &SWERowId1='¦¦T1.ROW_ID URL,
   'US' LANG,
   T1.ROW_ID KEY,
   GREATEST(T1.LAST_UPD, T2.LAST_UPD) LASTMODIFIEDDATE,
   DECODE(T1.FILE_EXT, 'ppt', 'application/vnd.ms-powerpoint',
      'doc', 'application/msword', 'html', 'text/html',
      'txt', 'text/plain', 'pdf', 'application/pdf','xls',
      'application/vnd.ms-excel') CONTENTTYPE,
      Attachment_Decompressor
      Folder_Path\S_ACTIVITY_ATT_'+T1.ROW_ID+'_'
      +FILE_REV_NUM+'.SAF' ATTACHMENT_LINK,
   T1.FILE_NAME CONTENT,
   T1.FILE_NAME TITLE,
   T1.CREATED CreatedOn,
   T1.CREATED_BY CreatedBy,
   T1.LAST_UPD_BY LastUpdatedBy,
   T1.FILE_SRC_TYPE FileType,
   T1.FILE_DATE FileDate,
   replace(T1.FILE_NAME,' ','_') FileName,
   T1.FILE_SIZE FileSize,
   T1.FILE_EXT FileExtension,
   T1.COMMENTS AttachmentComment,
   'Activity Attachment' sblbctype,
   T2.OWNER_LOGIN ActivityOwner,
   T2.OWNER_LOGIN visibilityid,
   T2.TARGET_OU_ID Organization,
   T2.ROW_ID SBLROWID
FROM
   dbo.S_ACTIVITY_ATT T1
   INNER JOIN      dbo.S_EVT_ACT T2  ON T1.PAR_ROW_ID = T2.ROW_ID
   LEFT OUTER JOIN dbo.S_USER    usr ON usr.LOGIN     = T2.OWNER_LOGIN

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=Activity+Attachment+View&SWERF=1&SWEHo=Host_name&SWEBU=1&SWEApplet0=Activity+Form+Applet&SWERowId0='¦¦T2.ROW_ID¦¦'&SWEApplet1=Activity+Attachment+Applet&SWERowId1='¦¦T1.ROW_ID URL,'US' LANG,T1.ROW_ID KEY,GREATEST(T1.LAST_UPD, T2.LAST_UPD) LASTMODIFIEDDATE, decode(T1.FILE_EXT, 'ppt', 'application/vnd.ms-powerpoint', 'doc', 'application/msword', 'html', 'text/html', 'txt', 'text/plain', 'pdf', 'application/pdf','xls', 'application/vnd.ms-excel') CONTENTTYPE, 'Attachment_Decompressor Folder_Path\S_ACTIVITY_ATT_'+T1.ROW_ID+'_'+FILE_REV_NUM+'.SAF' ATTACHMENT_LINK,T1.FILE_NAME CONTENT, T1.FILE_NAME TITLE, T1.CREATED CreatedOn,T1.CREATED_BY CreatedBy,T1.LAST_UPD_BY LastUpdatedBy,T1.FILE_SRC_TYPE FileType,T1.FILE_DATE FileDate,replace(T1.FILE_NAME,' ','_') FileName,T1.FILE_SIZE FileSize, T1.FILE_EXT FileExtension, T1.COMMENTS AttachmentComment,'Activity Attachment' sblbctype,T2.OWNER_LOGIN ActivityOwner,T2.OWNER_LOGIN visibilityid,T2.TARGET_OU_ID Organization, T2.ROW_ID SBLROWID FROM dbo.S_ACTIVITY_ATT T1 INNER JOIN dbo.S_EVT_ACT T2 ON T1.PAR_ROW_ID=T2.ROW_ID LEFT OUTER JOIN dbo.S_USER usr ON usr.LOGIN = T2.OWNER_LOGIN

Use this query with SQL Server:

SELECT
   'callcenter_enu/start.swe?SWECmd=GotoView
      &SWEView=Activity+Attachment+View
      &SWERF=1
      &SWEHo=Host_name
      &SWEBU=1
      &SWEApplet0=Activity+Form+Applet
      &SWERowId0='+T2.ROW_ID+'
      &SWEApplet1=Activity+Attachment+Applet
      &SWERowId1='+T1.ROW_ID URL,
   'US' LANG,
   T1.ROW_ID "KEY",
   CASE
      WHEN (DATEDIFF(second, T1.LAST_UPD, T2.LAST_UPD)> 0) THEN T2.LAST_UPD
      ELSE T1.LAST_UPD
   END
LASTMODIFIEDDATE,
   Attachment_Decompressor Folder_Path
      \S_ACTIVITY_ATT_'+T1.ROW_ID+'_'+FILE_REV_NUM+'.SAF' ATTACHMENT_LINK,
   T1.FILE_NAME CONTENT,
   T1.FILE_NAME TITLE,
   T1.CREATED CreatedOn,
   T1.CREATED_BY CreatedBy,
   T1.LAST_UPD_BY LastUpdatedBy,
   T1.FILE_SRC_TYPE FileType,
   T1.FILE_DATE FileDate,
   REPLACE(T1.FILE_NAME,' ','_') FileName,
   T1.FILE_SIZE FileSize,
   T1.FILE_EXT FileExtension,
   T1.COMMENTS AttachmentComment,
   'Activity Attachment' sblbctype,
   T2.OWNER_LOGIN ActivityOwner,
   T2.OWNER_LOGIN visibilityid,
   T2.TARGET_OU_ID Organization,
   T2.ROW_ID SBLROWID, 
   CASE T1.FILE_EXT
      WHEN 'ppt'  THEN 'application/vnd.ms-powerpoint'
      WHEN 'doc'  THEN 'application/msword'
      WHEN 'html' THEN 'text/html' when 'txt' then 'text/plain'
      WHEN 'pdf'  THEN 'application/pdf'
      WHEN 'xls'  THEN 'application/vnd.ms-excel'
   END
   CONTENTTYPE
FROM
   dbo.S_ACTIVITY_ATT T1
   INNER JOIN      dbo.S_EVT_ACT T2  ON T1.PAR_ROW_ID = T2.ROW_ID
   LEFT OUTER JOIN dbo.S_USER    usr ON usr.LOGIN     = T2.OWNER_LOGIN

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=Activity+Attachment+View&SWERF=1&SWEHo=Host_name&SWEBU=1&SWEApplet0=Activity+Form+Applet&SWERowId0='+T2.ROW_ID+'&SWEApplet1=Activity+Attachment+Applet&SWERowId1='+T1.ROW_ID URL,'US' LANG,T1.ROW_ID "KEY",case when (datediff(second, T1.LAST_UPD, T2.LAST_UPD)> 0) then T2.LAST_UPD else T1.LAST_UPD end LASTMODIFIEDDATE,'Attachment_Decompressor Folder_Path\S_ACTIVITY_ATT_'+T1.ROW_ID+'_'+FILE_REV_NUM+'.SAF' ATTACHMENT_LINK, T1.FILE_NAME CONTENT, T1.FILE_NAME TITLE, T1.CREATED CreatedOn,T1.CREATED_BY CreatedBy, T1.LAST_UPD_BY LastUpdatedBy,T1.FILE_SRC_TYPE FileType,T1.FILE_DATE FileDate,replace(T1.FILE_NAME,' ','_') FileName,T1.FILE_SIZE FileSize, T1.FILE_EXT FileExtension, T1.COMMENTS AttachmentComment,'Activity Attachment' sblbctype,T2.OWNER_LOGIN ActivityOwner,T2.OWNER_LOGIN visibilityid,T2.TARGET_OU_ID Organization, T2.ROW_ID SBLROWID,  case T1.FILE_EXT when 'ppt' then 'application/vnd.ms-powerpoint' when 'doc' then 'application/msword' when 'html' then 'text/html' when 'txt' then 'text/plain' when 'pdf' then 'application/pdf' when 'xls' then 'application/vnd.ms-excel' end CONTENTTYPE FROM dbo.S_ACTIVITY_ATT T1 INNER JOIN dbo.S_EVT_ACT T2 ON T1.PAR_ROW_ID=T2.ROW_ID LEFT OUTER JOIN dbo.S_USER usr ON usr.LOGIN = T2.OWNER_LOGIN