How can I use a SQL query to find the details of the forms which are associated with a particular request?

Products

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

Issue/Introduction

We need to find the details of all of the forms which are associated with a particular request. How can we do this using an SQL query?

Environment

CA Service Catalog 17.3.X

Resolution

The database tables involved in this are:

Requests - usm_request_status
Subscriptions - usm_subscription_detail
Service Option Groups - usm_rate_plan and usm_offering_ratedef_inclusion
Service Options - usm_rate_definition

A request contains one or more subscriptions to an offering, the SQL statement which will list the offerings associated with a request is:

SELECT offering_id FROM usm_subscription_detail WHERE id IN
(SELECT subscription_detail_id FROM usm_request_status WHERE request_id = <request_id>)

Once you know which offerings are included in the request, you then need to go from offerings to service option groups and then from service option groups to service options. Once you know which service options are included in the request, you then need to use the usm_rate_definition table to find which forms are associated with a particular service option. Forms have the "item_type" field of 14, the text_1 field contains the id of the form and the text_2 field contains the form name.

SELECT text_1, text_2,  item_text FROM usm_rate_definition WHERE item_type = 14 AND status = 1 AND rate_plan_id IN
(SELECT distinct rate_plan_id FROM usm_offering_ratedef_inclusion WHERE parent_id = <offering_id>)

This will give you the form ID, the name of the form and also the name which was given to the form when it was associated with the service option. Once you have the form details, you can then find any further information you require from the usm_form_entities table.

This means that if a request only contains a single item, you can go from the request id to the list of forms involved in one SQL statement, by combining the two statements above like this:

SELECT text_1, text_2, item_text FROM usm_rate_definition WHERE item_type = 14 AND status = 1 AND rate_plan_id IN
(SELECT distinct rate_plan_id FROM usm_offering_ratedef_inclusion WHERE parent_id IN
(SELECT offering_id FROM usm_subscription_detail WHERE id IN
(SELECT subscription_detail_id FROM usm_request_status WHERE request_id = <request_id>)))