How to fetch the database table details of a service offering and its associated rate definitions ?

Products

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

Issue/Introduction

How to query database to fetch the information about which offerings and requests are involved ?

Environment

CA Service Management 17.3.X

Resolution

Please execute the following queries to get information about which offerings and requests are involved.
The form name for a Service is held in the text_2 column of the usm_rate_definition table. 
The query "select distinct text_2 from usm_rate_definition" will return a full list of the "locked" forms, as a form cannot be deleted while it's in a Service Option Group. 
Or, to work out which Service Option Groups need to be edited to enable a particular form to be deleted, execute:

select rate_plan_id, rate_plan_name from usm_rate_plan
where rate_plan_id in (select distinct rate_plan_id from usm_rate_definition where text_2 = '<name of the form>')

Another slightly different query is: 

select text_2 AS form_name,usm_rate_plan.rate_plan_id AS sog_id,
 rate_plan_name AS sog_name,domain,item_text as sog_cell_description,
 usm_rate_definition.date_unavailable
FROM usm_rate_plan JOIN usm_rate_definition
  ON usm_rate_plan.rate_plan_id = usm_rate_definition.rate_plan_id
WHERE lower(usm_rate_definition.text_2) like lower('%<name of the form>%')

As to the knowledge: Not able to delete a form if there is a request with this form'.
The following query is to execute for retrieving request_id(s):

select request_id, request_type, usm_rate_plan.rate_plan_id, rate_plan_name,
 usm_rate_plan.domain, usm_rate_definition.item_id, item_text as FormName,
 usm_rate_definition.text_1, category, external_id, category_class,
 category_subclass, id, account_label, parent_domain, offering_id
from usm_rate_plan, usm_rate_definition, usm_subscription_detail
where usm_rate_plan.rate_plan_id = usm_rate_definition.rate_plan_id
  and usm_subscription_detail.rate_plan_id = usm_rate_plan.rate_plan_id
  and lower(usm_rate_definition.text_2) like lower('%<name of the form>%')