How to or not able to delete forms that are no longer needed

Document ID : KB000125359
Last Modified Date : 28/01/2019
Show Technical Document Details
Introduction:
N/A
Background:
N/A
Environment:
Windows
Instructions:
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>%')