Completed Catalog Request still visible in the Request Managers overview

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

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

CA Service Catalog 14.1SQL / ORACLE Database

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


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