BIP Report Notifications - Orphan Records in S_BCSTMSG_USER table (Doc ID 2141910.1)

Last updated on MAY 25, 2016

Applies to:

Siebel CRM - Version 8.2.2.4 SIA [23030] and later
Information in this document applies to any platform.

Symptoms

On : 8.2.2.4 SIA [23030] version, Reports

ACTUAL BEHAVIOR
---------------
The following issues have been identified related to Report Notification feature:

1) Slowness while downloading the report due to a large number of records in S_BRDCST_MSG and S_BCSTMSG_USER tables. Once the data removed with respect to the user the download is much faster.

2) Whenever a new Notification is created on successful generation of BIP report a record is created in the S_BRDCST_MSG and S_BCSTMSG_USER tables. When user clicks on the notification and downloads the report the data in both the tables are deleted from tables but at the same time a new record is created in the child S_BCSTMSG_USER table with same MSG_ID as the earlier notification (which has been deleted) and STATUS_VAL column as 2. As the corresponding parent record is deleted from S_BRDCST_MSG, it has been found new orphan records in S_BCSTMSG_USER table.

EXPECTED BEHAVIOR
-----------------------
When report is read, record should be deleted from S_BRDCST_MSG and S_BCSTMSG_USER tables.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. In Siebel application, submit the report.
2. Read the report from My BIP Reports view.
3. Once the report notification is generated, just click red X button in front of report notification to close.

Execute the following SQL statements on Siebel Database BEFORE and AFTER reading the report generated:

SELECT * FROM SIEBEL.S_BRDCST_MSG WHERE PR_RECIP_USER_ID IN (SELECT ROW_ID FROM SIEBEL.S_USER WHERE LOGIN = 'USER_ABC');
SELECT * FROM SIEBEL.S_BCSTMSG_USER WHERE MSG_ID IN (SELECT ROW_ID FROM SIEBEL.S_BRDCST_MSG WHERE PR_RECIP_USER_ID IN (SELECT ROW_ID FROM SIEBEL.S_USER WHERE LOGIN = 'USER_ABC'));
SELECT * FROM SIEBEL.S_BCSTMSG_USER WHERE TRUNC(CREATED) = TRUNC(SYSDATE);


Before reading the report, it is going to return data in S_BRDCST_MSG and S_BCSTMSG_USER. After reading the report, it is going to return data only in S_BCSTMSG_USER.

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms