Completed Catalog Request still visible in the Request Managers overview

Document ID : KB000006316
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

How to correct Catalog request from being visible in the overview of the Request Managers

Environment:
CA Service Catalog 14.1SQL / ORACLE Database
Cause:

The probable root cause is

usm_request_pending_action gets few entries with Object_type 1(offering) till the request is in pending approval. 

Once the request is approved and reached pending fulfillment, few more records are inserted with object_type 2 (rate item). 

When the request reached 'pending fulfillment' stage, someone edited the service definition and the request got stuck and hence the status was not updated in this table

Resolution:

To fix this issue try the following: 

1) Take a backup of the current MDB (please do this first if this solution is on PROD environment). 

2) Execute the following SQL statements (SQL / ORACLE) 

SELECT * FROM usm_request_pending_action WHERE request_id= <request_id>

3) Execute the below query to set the status to correct value i.e. 

UPDATE usm_request_pending_action SET status=2 WHERE request_pending_action_id IN 

(SELECT DISTINCT urpa.request_pending_action_id 

FROM usm_request ur, usm_request_pending_action urpa 

WHERE ur.request_id=<request_id> AND urpa.request_id=ur.request_id and ur.status=2 and urpa.status=1)

Additional Information:

Note: Replace <request_id> with original request number for correct results