Good Morning Divya.
Please consider the below queries. And see whether one of these helps you further.
Of course these can be used as 'starting point' for you and be tweaked per your wishes.
Kind regards, Louis.
/* Query the form components for the offering, copy value of column text_1 into: */
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
==============================================
/* Root form components of an offering */
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
============================================================================
/* Recursive query to fetch all component hierarchy of a form or folder */
WITH Form (form_entity_id, form_entity_name, form_entity_parent_id, Hir)
AS
(
-- Anchor member definition
SELECT rt.form_entity_id, rt.form_entity_name, rt.form_entity_parent_id, 0 AS Hir
FROM usm_form_entities rt
WHERE rt.form_entity_name = 'Forms'
UNION ALL
-- Recursive member definition
SELECT rec.form_entity_id, rec.form_entity_name, rec.form_entity_parent_id, Hir + 1
FROM usm_form_entities rec
INNER JOIN Form AS Par ON Par.form_entity_id = rec.form_entity_parent_id
)
-- Statement that executes the CTE
SELECT form_entity_id, form_entity_name, form_entity_parent_id, Hir
FROM Form
order by Hir
============================================================================
/* Query1: When you know the name of the form, you can execute: */
select form_entity_id,form_entity_type,form_entity_name,form_entity_path from USM_FORM_ENTITIES
where form_entity_name = 'A_Components_Form'
/* Example form_entity_path = /1/21942/ */
/* And take the form_entity_path from the result into the next query:
Query2: */
select form_entity_id,form_entity_type,form_entity_name,form_entity_path from USM_FORM_ENTITIES
where form_entity_path like '%/1/21942/%'
/* Query3: */
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 fe.form_entity_path like '%/1/21942/%'
and fca.form_comp_id = fe.form_entity_id
============================================================================