Completed Catalog Request still visible in the Request Managers overview

Products

• CA Service Catalog
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

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

Environment

CA Service Catalog 14.1

SQL / 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