Historical Audit Trail Information Missing after Siebel 8.0 Upgrade

Hi Friends,

If you have just upgraded to Siebel 8.0, then be prepared to have a big shocker in waiting! All the historical audit trail information will be missing from UI due to the New Encoded Audit Trail storing mechanism introduced in Siebel 8.0

A major change in Siebel 8.0 is the query formed while retrieving the Audit Trail records:
" WHERE
((T1.BC_BASE_TBL = ?) AND
(T1.RECORD_ID = ?))"

All Historical Audit Trail records would be having BC_BASE_TBL column as null causing this issue.
If you are thinking to populate this column using EIM, then it wont work as S_AUDIT_ITEM does not have the required userkeys.

So how to go about populating BC_BASE_TBL directly? We need to query S_BUSCOMP & get the Base Table name for each record.

I have wrote a PL/SQL Block for directly updating S_AUDIT_ITEM which will populate the BC_BASE_TBL for all the historical records inorder to resolve this issue...

############################################################
/* Update Audit Trail Historical Records for UI Visibility
Author:- Shalabhsneha Katdare (SHALABHKATDARE AT GMAIL.COM)
Version 1.0
*/

DECLARE

V_COUNT NUMBER:=0;
V_COUNT1 NUMBER:=0;
V_ROW_ID VARCHAR2(30);
V_ERR_MSG VARCHAR2(200);
v_Table_Name VARCHAR2(75);
v_Buscomp_Name VARCHAR2(75);


CURSOR CUR_AUDIT_RECORDS
IS
SELECT
ROW_ID, BUSCOMP_NAME
FROM
SIEBEL.S_AUDIT_ITEM
WHERE
BC_BASE_TBL is null;

BEGIN
FOR CUR1 IN CUR_AUDIT_RECORDS LOOP
EXIT WHEN CUR_AUDIT_RECORDS%NOTFOUND;

v_row_id := cur1.row_id;
v_Buscomp_Name := cur1.BUSCOMP_NAME;
V_COUNT:=V_COUNT+1;

select table_name into v_Table_Name from s_buscomp
where name=v_Buscomp_Name
and repository_id in (select row_id from s_repository where name='Siebel Repository');

if v_Table_Name is not null then
/* Its always a best practice to update LAST_UPD_BY, LAST_UPD & MODIFICATION_NUM while doing a backend update directly on Siebel Base Table*/

UPDATE
SIEBEL.S_AUDIT_ITEM
SET
BC_BASE_TBL = v_Table_Name
,LAST_UPD_BY='0-1'
,LAST_UPD = SYSDATE
,MODIFICATION_NUM = NVL(MODIFICATION_NUM,0)+1
WHERE
ROW_ID = CUR1.ROW_ID;
END IF;

/* A COMMIT Counter in order to commit after every 1000 records*/
IF V_COUNT>=1000 THEN
COMMIT;
V_COUNT:=0;
END IF;

END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
COMMIT;
/* Exception Handling Code if you want to add */
END;
/
##########################################################

I hope this helps to all who have recently upgraded to Siebel 8.0!
Welcome to Siebel 8.0 :)

Enjoy!
Keywords:
audit trail,audit trails,audit report,audit management,siebel crm,siebel, siebel systems,S_AUDIT_ITEM,BC_BASE_TBL,EIM,Historical Audit,etc

ESD [EMS 2012] Error: FindClass org/apache/catalina/startup/Bootstrap failed

Hello Friends,

If you happen to use the new breed of Siebel Email Marketing Server (ESD/BHD/CTD) [EMS2012], then you might be greeted with below error while starting the Service for the first time in jakarta_service_date.log file under \logs:

[2009-05-11 06:22:38] [info] Procrun (2.0.3.0) started
[2009-05-11 06:22:38] [info] Running Service...
[2009-05-11 06:22:38] [info] Starting service...
[2009-05-11 06:22:38] [443 javajni.c] [error] FindClass org/apache/catalina/startup/Bootstrap failed
[2009-05-11 06:22:38] [997 prunsrv.c] [error] Failed loading main org/apache/catalina/startup/Bootstrap class d:\Program Files\esd\bin\bootstrap.jar
[2009-05-11 06:22:38] [1260 prunsrv.c] [error] ServiceStart returned 3
[2009-05-11 06:22:38] [info] Run service finished.
[2009-05-11 06:22:38] [info] Procrun finished.

This is mainly because, Apache Tomcat 6 is now distributed with the latest EMS components which needs updated JDK Version to be installed prior its installation.

You can install J2SDK 1.5 update 18 & then re-install to eliminate this error.

If you are planning to install J2SDK 1.6, then you might want to read the below page first:
https://issues.apache.org/bugzilla/show_bug.cgi?id=41538

Keywords: ESD, Email Sending Daemon, crm,siebel, siebel systems,Siebel Bounce Handler Daemon, Siebel Click Through Daemon, BHD, CTD,EMS, Email Marketing Server,etc