How to or not able to delete forms that are no longer needed
Article ID: 125359
Updated On:
Products
CA Service Catalog
CA Service Management - Asset Portfolio Management
CA Service Management - Service Desk Manager
Issue/Introduction
N/A
N/A
N/A
Environment
Windows
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>%')
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>%')
Feedback
Powered by
