How to correct Catalog request from being visible in the overview of the Request Managers
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)
Note: Replace <request_id> with original request number for correct results