A Good Morning to you.
Possibly, the below queries will help you further ont his?
For already created/existing requests:
First it is to retrieve the subscription_detail_id of the request:
SELECT * FROM usm_subscription_detail
where request_id=11861 and subscription_type=5
From the result use the value of the column named 'id' in the next query:
SELECT subscription_detail_id,form_elem_name,form_elem_value,is_visible
FROM usm_request_item_form
where subscription_detail_id=<your id value>
This should show all fields(form_elem_name) and their value.
To check for the defined form fields:
SELECT * FROM usm_form_entities where form_entity_name like '%<your service name>%'
From the result use the value of the column named 'form_entity_parent_id' in the next query:
SELECT form_entity_id,form_entity_name,form_entity_type,form_entity_path,form_comp_id,attr_name,attr_value
FROM usm_form_entities,usm_form_component_attributes
where form_entity_path like '%<your id>%'
and usm_form_component_attributes.form_comp_id = usm_form_entities.form_entity_id
===========================================================================================
To query for an offering:
select distinct o.offering_id,o.offering_name,
i.parent_id,i.rate_plan_id,
rd.rate_plan_id,rd.item_text,rd.item_type,rd.text_1
from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
where o.offering_name = 'Order new Team Site'
and i.parent_id = o.offering_id
and rd.rate_plan_id = i.rate_plan_id
and rd.item_type = 14
order by rd.text_1
.....
Or in short:
select distinct rd.text_1
from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
where o.offering_name = 'Order new Team Site'
and i.parent_id = o.offering_id
and rd.rate_plan_id = i.rate_plan_id
and rd.item_type = 14
order by rd.text_1
Query the form components for the offering, copy value of column text_1 into:
SELECT form_entity_id,form_entity_name,form_entity_type,form_entity_path,form_comp_id,attr_name,attr_value
FROM usm_form_entities,usm_form_component_attributes
where form_comp_id = '34145' (or when multiple rows/values got returned "in ('34145','34158')")
and usm_form_component_attributes.form_comp_id = usm_form_entities.form_entity_id
Combined query:
select fe.form_entity_id,fe.form_entity_name,fe.form_entity_type,fe.form_entity_path,fca.form_comp_id,fca.attr_name,attr_value
from usm_form_entities fe, usm_form_component_attributes fca
where fca.form_comp_id in (
select distinct rd.text_1
from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
where o.offering_name = 'Order new Team Site'
and i.parent_id = o.offering_id
and rd.rate_plan_id = i.rate_plan_id
and rd.item_type = 14)
and fca.form_comp_id = fe.form_entity_id
==============================================
Kind regards, Louis.