BIP Report Notifications - Orphan Records in S_BCSTMSG_USER table
Last updated on MAY 25, 2016
Applies to:Siebel CRM - Version 184.108.40.206 SIA  and later
Information in this document applies to any platform.
On : 220.127.116.11 SIA  version, Reports
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.
When report is read, record should be deleted from S_BRDCST_MSG and S_BCSTMSG_USER tables.
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_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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms