SBL-GEN-03008: Error calling SQLExecute

Applies to:

Siebel Remote - Version: 8.0.0.2 SIA [20412] and later   [Release: V8 and later ]
Information in this document applies to any platform.

Goal

Hi,

Thank you for submitting your question on Oracle Metalink3 portal. I have taken ownership for the Service Request generated (3-948299761).

I understand you are seeing issues with the Transaction Merger trying to process a problematic dx file for user XXXXXXX  and you wish to have the dx file "fxed" if possible in order to save the rest of the probably good Transactions.

I will examine the log files and see what the issue might be. We may be able to perhaps "fix" the dx file to save some of the information.



Thank you

Siebel Technical Support

Solution

An1:
Hi ,

Thank you for your patience, I have reviewed your Txn Merger log file.
""UPDATE dbo.S_ORG_PROD
SET MODIFICATION_NUM = MODIFICATION_NUM + 1, DB_LAST_UPD = {fn now()}, DB_LAST_UPD_SRC = 'REMOTE',
LAST_UPD = ?,
LAST_UPD_BY = ?,
DB_LAST_UPD = ?,
DB_LAST_UPD_SRC = ?
WHERE ROW_ID = ?
DBCLog DBCLogError 1 000000024a3e0c88:0 2009-06-21 14:45:03 [Microsoft][SQL Native Client][SQL Server]Column name 'DB_LAST_UPD' appears more than once in the result column list.
"

:
:

"03007: SQL error: native=264, state=37000, message=[Microsoft][SQL Native Client][SQL Server]Column name 'DB_LAST_UPD_SRC' appears more than once in the result column list.
GenericLog GenericError 1 000000024a3e0c88:0 2009-06-21 14:45:03 (mrgsupd.cpp (1231228) err=0 sys=1) SBL-GEN-00000: (mrgsupd.cpp: 1231228) error code = 0, system error = 1, msg1 = (null), msg2 = (null), msg3 = (null), msg4 = (null)
GenericLog GenericError 1 000000024a3e0c88:0 2009-06-21 14:45:03 (mrgsupd.cpp (700) err=3008 sys=0) SBL-GEN-03008: Error calling SQLExecDirect for UPDATE dbo.S_ORG_PROD
"

:
:

"GenericLog GenericError 1 000000024a3e0c88:0 2009-06-21 14:45:04 Message: GEN-03,
Additional Message: File: C:\sba80\siebsrvr\docking\XXXXXX\inbox\00000035.dx. Last successfully read txn id: 14411. Last committed txn id: 14411."

As you can see 'DB_LAST_UPD' AND 'DB_LAST_UPD_SRC' get two values...

This is a known Siebel Product Defect in Siebel 8.0.x when running against SQL Server database.

The Defect 12-1L0YDDK "apply_thrd failing as columns DB_LAST_UPD, DB_LAST_UPD_SRC listed twice in update statement"

is fixed in Siebel 8.0.0.7 Fix Pack which was released on 25th May 2009.

I have associated your Service Request to this Defect.

For the moment the workarounds are

1). Re-extract affected mobile clients
2). Provide the corrupted dx file along with the corresponding Merger log file OR applythtrd_xxx_xxx.log for an affected mobile client and Technical Support will fix this for you by removing the problematic transaction. (This is what I have done for you here... see my next update)

3). Replace the corrupted dx file with a Blank dx file.

For Options 2 and 3 from above, we will require you to replace the problem dx file with the new fixed or dummy dx file.


Thank You

Siebel Technical Support


An2:
Hi,

Thank you for your patience. As promised, I have identified the corrupt transactions in the dx file, They are


Txn Type : S
Txn Id : 14412
Src Node : 47N
Created By : 1-ER-17
DLog Row Id: 47N-D6U

OperType : Insert
TableName: S_ORG_PROD
Txn RowId : 47N-D6T
Txn LastUpdBy : 1-ER-17
Txn LastUpd : 2009-06-19 18:38:20.000000
Txn ModNum : 0
Txn ConflictId: 0
Txn Id: 14412
File Table : Unknown
Col 0 (C): AMT_CURCY_CD
Col 1 (C): DSTRBN_ACTV_FLG
Col 2 (C): ORDER_FLG
Col 3 (C): OU_ID
Col 4 (C): PROD_ID
Col 5 (C): SALES_FLG
Col 6 (C): STATUS_CD
Col 7 (C): X_BIC_NODELETE_FLG
Col 8 (C): X_BIC_PROM_ADJ_AMT_VALUE
New 0 : EUR
New 1 : Y
New 2 : Y
New 3 : 1-OX-1483
New 4 : 1-12HUZL
New 5 : Y
New 6 : Present
New 7 : N
New 8 : 0
Audit Hints:

Txn Type : S
Txn Id : 14413
Src Node : 47N
Created By : 1-ER-17
DLog Row Id: 47N-D6W

OperType : Insert
TableName: S_ORG_PROD
Txn RowId : 47N-D6V
Txn LastUpdBy : 1-ER-17
Txn LastUpd : 2009-06-19 18:38:41.000000
Txn ModNum : 0
Txn ConflictId: 0
Txn Id: 14413
File Table : Unknown
Col 0 (C): AMT_CURCY_CD
Col 1 (C): DSTRBN_ACTV_FLG
Col 2 (C): ORDER_FLG
Col 3 (C): OU_ID
Col 4 (C): PROD_ID
Col 5 (C): SALES_FLG
Col 6 (C): STATUS_CD
Col 7 (C): X_BIC_NODELETE_FLG
Col 8 (C): X_BIC_PROM_ADJ_AMT_VALUE
New 0 : EUR
New 1 : Y
New 2 : Y
New 3 : 1-OX-1483
New 4 : 1-12IERT
New 5 : Y
New 6 : Present
New 7 : N
New 8 : 0
Audit Hints:
:
:
“
Lets fix the Dx file and see if the issue ever re-turns.

To "Fix" the dx file, I have removed these two transactions TxnId : 14412, 14413 (which contain 1 sub-operation each) from the dx file "00000035.dx".

I have called the new dx file "fixed_35.dx. Please re-name this to the name of the original (00000035.dx) and use it to replace the original in

C:\sba80\siebsrvr\docking\XXXXXX\inbox\00000035.dx

Please re-name the "inbox" directory also back to it original name, ( if necessary?)

Please let me know if the Transaction Merger processes this new "fixed" Dx file.

Thank you

Siebel Technical Support

Applies to:

Siebel Remote - Version: 7.7.2.6 SIA [18372] and later   [Release: V7 and later ]
Information in this document applies to any platform.

Symptoms

=== ODM Issue Clarification ===

Transaction Router and Database Extract exhibit  the following errors


ORA-00600 internal error code [qertbFetchByRowID], [], [], [], [], [], [], []

SBL-GEN-03007: (dberror2.cpp: 0) error code = 3007

SBL-GEN-03008: (utlvis.cpp: 2688) error code = 3008, system error = 0, msg1 = SQLFetch, msg2 = VISIBILITY GET ID Party(1), msg3 = (null), msg4 = (null)

Changes

none.

Cause

=== ODM Cause Determination ===

Number of records on certain tables changed dramatically

We also found that simple queries run directly against the Database caused the same Error.

ORA-00600: internal error code, arguments: [qertbFetchByRowID], [], [], [], [], [], [], []

Solution

=== ODM Solution / Action Plan ===

Rebuild Database Indexes on tables that are exhibiting the error.

The exact steps for re-building indexes will vary slightly depending on the Database.

Please review database documentation or involved Database Administrator

Applies to:

Siebel Remote - Version: 8.0 [20405] to 8.1.1 [21112] - Release: V8 to V8
Information in this document applies to any platform.

Description

Customers using Siebel Remote may encounter various errors. These errors have been addressed in Fix Packs, Quick fixes or may have temporary workarounds available. Below is a list of the known defects, a clear description to help you identify if you have hit this defect and a table that lists in what fix pack (FP) or quick fix (QF) the defect has been fixed. This alert will be updated as required to keep customers informed of defects as well as fixed versions. FP's and QF's can be downloaded from My Oracle Support from the "Patches & Updates" tab

The below list is not exhaustive but includes the most critical defects that have been identified.

* Note that after installing the fix for CR #  12-1T2IN2L (Bug:10565949) some customer then experienced another behavior CR # 12-1U0AX75 (Bug:10570714) brought about by applying the fix.

As a result of this situation a new CR was created CR #12-1VNIMYR (Bug:10582025) to provide a thorough, redesigned and complete solution for these two behaviors. Therefore the fix for CR #12-1VNIMYR (Bug:10582025) is a complete solution for both CR # 12-1T2IN2L (Bug:10565949) and CR # 12-1U0AX75 (Bug:10570714)

Every customer that is running Siebel in version 8.0.x and 8.1.x should apply the complete solution. Please see the below table for details.

1) Change Request  12-1T2IN2L  Bug:10565949Transaction Processor may encounter slow performance. Customer may notice this performance issue soon after moving to Siebel 8.x or 8.1. This behavior has been detailed in the following Alert: Transaction Processor can exhibit slow performance Note 841888.1

2) Change Request 12-1TUWRGV Bug:10569597
3) Change Request 12-1QVMO57  Bug:10555407

In certain situations .dx files can be malformed and missing the table name value.   There are 2 Change Requests created to address this behavior. Though the end result is the same the behavior can occur in two distinct scenarios. The .dx file created looks similar and in both cases the TableName is missing.

Example:
Txn Type : S
Txn Id : 1176656580
Src Node : 1-2PPG-2
Created By : 1-2I2UD
DLog Row Id: 1-R231-1

OperType : Cascade / Merge
TableName:
Txn Id: 1176656580
File Table : Unknown
Col 0 (C): CON_PER_ID
Col 1 (C): ROW_ID
Old 0 : 2IPQ-IAI
Old 1 :
Operation 0 (Cascade Delete ): S_CALL_LST_CON.CON_PER_ID

Customer will find the following errors in the Transaction Merger log file or the client applythrd_xxx_yyy.log file

GenericLog pTableName is NULL or empty
GenericLog SBL-GEN-03006: Error calling function: DICFindTable

4) Change Request 12-1L0YDDK  Bug:10529443
Under certain conditions Transaction Merger or the client applythrd may fail when processing an update within a .dx file.The .dx file itself is not malformed but during the update process two system columns are listed twice in the update statement causing an error condition.DB_LAST_UPD and DB_LAST_UPS_SRCNote 848264.1

Set clause for column 'DB_LAST_UPD' used incorrectly for the client applythrd.log file
Column name 'DB_LAST_UPD' appears more than once in the result column list. for the Transaction Merger log file
or
[DataDirect][ODBC Oracle driver][Oracle]ORA-00957: duplicate column name

5) Change Request 12-1RPD2ML  Bug:10559246
Customers have experienced visdata corruption while running Transaction Router and Transaction Processor in a Siebel 8 environment. This behavior has been detailed in the following Alert: Transaction Router and Transaction Processor may encounter visdata.dbf file corruption in Siebel 8 remote environment Note 763195.1


6) Change Request 12-1U0AX75  Bug:10570714
After applying the fix for the Transaction Processor performance condition identified in 1) above it is possible another error can occur. After Transaction Processor restarts it will begin to read transactions from the Low Scan Mark (an internal marker).  These transactions have already been routed by the Transaction Processor and the Transaction Router. The processor places these already routed transactions in the next sequential .dx file - example 101.dx file. Since the transactions within the dx file have already been routed the Transaction Processor will delete this dx file(s) once it starts the Clean File routine. The Transaction Router will start and will be looking for the next sequential dx file in the TXNPROC folder (101.dx). If Transaction Router cannot find the dx file (101.dx) it will fail with:

Read error - system error code 2
Message: Unable to open User Txn Log file for reading or writing.,
SBL-DCK-00142: Error opening transaction file (...) for read

7) Change Request 12-1UP4EOX  Bug:10574894
Running the Database Extract task with certain parameter settings can cause the Transaction Router to fail. Using both the List method and the Optimal Mode=TRUE setting will cause this error condition.This behavior has been detailed in the following Alert:  Running Database Extract with specific settings causes Transaction Router to fail Note 948203.1

Likelihood of Occurrence

Customers using Siebel Remote and Replication on version 8 are likely to encounter these error conditions. Some of these Change Requests have been addressed in Fix Packs. In other cases a Quick Fix may be required on top of  the current version.

Possible Symptoms

Here are the error messages you may find in each scenario.

The error message for Change Request 12-1TUWRGV Bug:10569597 and Change Request 12-1QVMO57 Bug:10555407

GenericLog pTableName is NULL or empty
GenericLog SBL-GEN-03006: Error calling function: DICFindTable

The error message for Change Request 12-1L0YDDK Bug:10529443

Set clause for column 'DB_LAST_UPD' used incorrectly.
Found in the client applythrd.log file

Column name 'DB_LAST_UPD' appears more than once in the result column list.
[DataDirect][ODBC Oracle driver][Oracle]ORA-00957: duplicate column name Found in the Transaction Merger log file


The error message for Change Request 12-1U0AX75 Bug:10570714:

Read error - system error code 2
Message: Unable to open User Txn Log file for reading or writing.,
SBL-DCK-00142: Error opening transaction file (...) for read.


The error message for Change Request 12-1RPD2ML Bug:10559246:

Unable to open visibility database in 10 of 10 tries....
SBL-CSC-00213: Invalid visibility database.
SBL-CSC-00200: Cannot acquire spinlock createLatch

The error message for Change Request 12-1UP4EOX Bug:10574894:

SBL-GEN-03006: Error calling function: GetNodeProcessPref

Workaround or Resolution

• Bug:10574894:

In order to workaround this issue do not use both the list method of extracting users and the Optimal Mode setting together.

srvrmgr:app1> start task for comp dbxtract with client=@c:\clients.txt
srvrmgr:app1> start task for comp dbxtract with client=CLIENT1, optmode=TRUE
The default setting for Optimal Mode for the Database Extract component is FALSE

• Bug:10559246:

Stop all running Routers and Processors. Uncheck ( set to FALSE) the Optimized Visibility Check preference. By default this Remote System Preference is TRUE. You can find this preference in Administration-Siebel Remote> Remote System Preferences > checkbox bottom left of this applet. Unchecking does not require service restart. In addition there is no negative impact of unchecking this preference. Start Processor and Router.

• Bug:10570714:

         **(This is a manual workaround the complete and best solution for this behavior is to apply the fix
         for Bug:10582025)

Increase the TP parameter Clean .dx files iterations to a large value so it will not delete .dx files until the Transaction Router has had an opportunity to process them. Be sure to increase the parameter on each Transaction Processor in the environment. Increasing the value from 1 to a value between 100-200 for Clean .dx files iterations should prove effective. Do not run the Transaction processor without running the Transaction Router as well. There is no negative impact on processing. The TXNPROC directory will grow large until the Transaction processor purges the .dx files. For Example if setting the Clean.dx file iteration to 150

Patches

Change Request	Description	Fixed version
12-1VNIMYR Bug:10582025 (CR #'s 12-1T2IN2L Bug:10565949 and 12-1U0AX75 Bug:10570714 are addressed by applying this fix)	Provides the complete solution for "Slow Transaction Processor performance" and "dx file deleted before Router can read them."	Fix Pack: 8.0.0.10 8.1.1.3  Quick Fix 8.0.0.9[20433] QF0901 8.0.0.8 [20430]QF0809 8.0.0.7[20426] QF0754 8.0.0.6[20423] QF06AA 8.0.0.5 [20420]QF05CB 8.0.0.2 [20412]QF02E4 8.1.1.2 [21215] QF0238 8.1.1 SIA [21111] or HOR[21112] QF00AO
12-1TUWRGV Bug:10569597	pTable Name error	Fix Pack 8.0.0.9 8.1.1.2 Quick Fix 8.0.0.2 [20412]QF02C8 8.0.0.5 [20420]QF05BC 8.0.0.6 [20423]QF0661 8.0.0.8 [20430]QF0809 8.1.1 [21112] QF00AO
12-1QVMO57 Bug:10555407	Frequent dbxtract, dbinit and synchronization leads to TxnMerger failing	Fix Pack 8.1.1 HOR [21112] 8.0.0.9 8.1.1.4 Quick Fix 8.0.0.2 [20412]QF02C8 8.0.0.3 [20416]QF0368 8.0.0.5 [20420]QF05BO 8.0.0.6 [20423]QF06AJ 8.0.0.8 [20430]QF0809 8.1.1 SIA [21111] or HOR[21112] QF00AO
12-1L0YDDK Bug:10529443	DB_LAST_UPD listed twice	Fix Pack 8.0.0.7 8.0.0.8 8.1.1.2 Quick Fix 8.0.0.4 [20417]QF0420 8.0.0.5 [20420]QF0591 8.0.0.6 [20423]QF0669 8.1.1.1 [21211] QF01BN
12-1RPD2ML Bug:10559246	Visdata.dbf corruption	Fix Pack 8.0.0.8 8.1.1.2 Quick Fix 8.0.0.5 [20420]QF0571 8.0.0.6 [20423]QF0611 8.1.1 [21112] QF0076
12-1UP4EOX Bug:10574894	Router fails if Database Extract is run with specific settings	Fix Pack 8.0.0.13 8.1.1.7

References

NOTE:763195.1 - Transaction Router and Transaction Processor may encounter visdata.dbf file corruption in Siebel 8 remote environment
NOTE:790117.1 - The Txnskip functionality is not working as expected.
NOTE:841888.1 - Transaction Processor 8.x can exhibit slow performance
NOTE:848264.1 - Transaction Merger crashes on a DX File error SBL-GEN-03008
NOTE:948203.1 - Running Database Extract 8.x with specific settings causes Transaction Router to fail Error calling function: GetNodeProcessPref

Applies to:

Siebel Assignment Manager - Version 7.8.2.6 [19230] to 8.1 [21039] [Release V7 to V8]
Information in this document applies to any platform.

Symptoms

We have Repeating Component Request for Batch Assignment.  Our Batch Assignment task is erring out in Production with following error:

GenericLog GenericError 1 0 2008-08-25 13:43:11 Message: Internal error occurred in a procedure.,
Additional Message: rule-ERR-1109: Unable to read value from export file (Data length (4) > Column definition (3)).

GenericLog GenericError 1 0 2008-08-25 13:43:11 Message: Internal error occurred in a procedure.,
Additional Message: rule-ERR-1107: Unable to read row 0 from export file (UTLDataValReadUTLDataValRead pBuf, col 3 ).
GenericLog GenericError 1 0 2008-08-25 13:43:11 (asgnrule.cpp (2561) err=3008 sys=486992) SBL-GEN-03008: (asgnrule.cpp: 2561) error code = 3008, system error = 486992, msg1 = UTLDataFetch, msg2 =

SELECT R.ROW_ID, R.ASGN_TYPE_CD, R.SCORE_VAL, R.EXCLUSIVE_FLG, R.ASGN_CHILD_FLG, R.CREATE_ACT_FLG,
R.CAL_ADDTL_TIME, R.MIN_SCORE_VAL, R.ALL_POSTN_FLG, R.ALL_POSTN_FLG, R.PR_EMP_ID, R.PR_POSTN_ID, R.ALL_EMP_SKILL_FLG,
R.NAME, R.EFF_START_DT, R.EFF_END_DT, R.ALL_BU_FLG, R.PR_BU_ID, R.CHECK_CAL_FLG, RG.ROOT_RULE_GRP_ID,R.PER_CAND_SRC_CD, R.BU_CAND_SRC_CD
FROM SIEBEL

GenericLog GenericError 1 0 2008-08-25 13:43:11 Message: Internal error occurred in a procedure.,
Additional Message: rule-ERR-1109: Unable to read value from export file (UTLCompressFReadUTLCompressFRead (fseek)).

GenericLog GenericError 1 0 2008-08-25 13:43:11 Message: Internal error occurred in a procedure.,
Additional Message: rule-ERR-1107: Unable to read row 0 from export file (UTLDataValReadUTLDataValRead pBuf, col 0 ).


The S_SRM_REQUEST has over 1000 such requests still 'queued'.

COUNT(*) TRUNC(CREATED) TRUNC(LAST_UPD) SUBSTR(PARAM_VAL,2,INSTR(PARAM_VAL,'|')-2)
---------- ----------------- ----------------- --------------------------------------------------
1 09/27/2004 00:00:00 08/14/2007 00:00:00 AsgnKey=1-7OAG9
1232 02/26/2008 00:00:00 08/28/2008 00:00:00 AsgnKey=1-7OAG9

Can you please suggest the recommended steps to delete these stale requests by our DBA?

Thanks.

Cause

Customer discovered that the repeat errors are for an Assignment rule processing which is no longer active.

Solution

1. Cancel existing 1000 QUEUED tasks.
You can cancel 1000 QUEUED tasks so they won't get executed. One way is by manually updating the status to 'CANCELLED' in database, this should stop the tasks from starting. First execute this SQL in database to get component requests related to the inactive assignment rule in S_SRM_REQUEST and look them through.  The Asgnkey holds the assignment rule group's ROW_ID, S_SRM_REQUEST.ACTION_ID is the component ID.

SELECT a.STATUS, a.ACTION_ID, b.NAME, a.PARAM_VAL
FROM S_SRM_REQUEST a, S_SRM_ACTION b
WHERE a.ACTION_ID = b.ROW_ID
AND a.PARAM_VAL LIKE '%AsgnKey=1-7OAG9%'

Then use the following SQL to update the status:

UPDATE S_SRM_REQUEST
SET STATUS = 'CANCELLED'
WHERE STATUS = 'QUEUED'
AND ACTION_ID in (SELECT a.ACTION_ID
FROM S_SRM_REQUEST a, S_SRM_ACTION b
WHERE a.ACTION_ID = b.ROW_ID
AND a.PARAM_VAL LIKE '%AsgnKey=1-7OAG9%' )
AND PARAM_VAL like '%AsgnKey=1-L6G9%'

2. New RCR tasks for Batch Assignment are queued.
Customer set existing QUEUED tasks to CANCELLED.  Customer tried to re-schedule Batch Assignment tasks. But all the requests were getting queued sitting in QUEUED status. Customer restarted the Siebel Server having Assignment Management server component group.  After restart the status of "Assignment Manager" and "Batch Assignment" components were in "Shutdown" state. Customer restarted the component from the GUI, after that the components showed "Online" state.  But the requests were still queued.   Customer manually started a Batch Assignment task in UI, the task was QUEUED.  Customer found Batch Assignment tasks with the same input parameters submitted in srvrmgr command line were executed successfully:  Troubleshooting by following "Component Requests remain in a Queued status (Doc ID 476707.1)", no faults found.
Customer has 7 Siebel Servers in enterprise, and only one Siebel Server BEOVPVSS2 has Assignment Management server component group enabled.  In siebns.dat, the group showed enabled and online. But the 2 components were shown enabled and offline as the following:
enable state = enabled
status = offline
Applying solution from "Component becomes offline on rebooting Siebel server (Doc ID 543893.1)": online compgrp AsgnMgmt and restart Siebel Server, did not work.
This problemt got resolved after customer moved Assignment Management component group to another Siebel Server by doing the following:
- Disable the Assignment Management component group, Synchronize the batch components, and restart on 1st Siebel Servere;
- Enable the Assignment Management component group, Synchronize the batch components, and restart on 2nd Siebel Server. 
- Then customer moved Assignment Management component group back to original server, by doing the same steps. 
It looks like the component status got refreshed in the gateway configuration, by doing this 2 step process between 2 Siebel Servers.   It took time to confirm the solution worked because being in Production customer cannot restart servers during weekdays hence wait for the weekend

References

NOTE:476707.1 - Component Requests remain in a Queued status
@NOTE:543893.1 - Component becomes offline on rebooting Siebel server

Applies to:

Siebel Remote - Version: 8.0.0.5 SIA [20420] to 8.1 [21039] - Release: V8 to V8
Information in this document applies to any platform.

Symptoms

Transactions are not getting processed. Even though it says copying transactions.
Transaction Router fails with

SBL-CSC-00213: Invalid visibility database. Shut down and restart *all* remote components using it (txnproc and txnroute) to rebuild.

Cause

TxN Router wasn't working fine. It was failing with the following errors:

[DataDirect][ODBC Oracle driver][Oracle]ORA-00904: "SIEBEL"."VIS_DB_MAX_TXN_ID": invalid identifier

SBL-GEN-03007: SQL error: native=904, state=S0022, message=
[DataDirect][ODBC Oracle driver][Oracle]ORA-00904: "SIEBEL"."VIS_DB_MAX_TXN_ID": invalid identifier

SBL-GEN-03008: Error calling SQLExecute for VIS NODE INFO (1)

and finally with:

SBL-CSC-00213: Invalid visibility database. Shut down and restart *all* remote components using it (txnproc and txnroute) to rebuild.

All the Remote components must be working correctly. If one fails, it affects the others.

Solution

VIS_DB_MAX_TXN_ID is a function and it was suspected that it was not valid. It's been also seen in previous cases that after granting sse_role to SADMIN again and then granting execute on VIS_DB_MAX_TXN_ID to SADMIN, Transaction Router runs successfully.

So please:

1. Make sure sse_role has been properly granted to the user running Transaction Router

select * from dba_role_privs where grantee = '<>’;
2. Make sure the function VIS_DB_MAX_TXN_ID is valid

select object_name, object_type, status
from all_objects
where object_name = 'VIS_DB_MAX_TXN_ID'
and owner = 'SIEBEL';
If the function is invalid, this function can be recreated by executing the pkgvis.sql script in the \dbsrvr folder. When the function VIS_DB_MAX_TXN_ID is created, it also issues a grant execute to sse_role. This may very well be the missing privilege causing the ORA-00904 failure.
The above action plan resolved the issue.