SQL query to get DBI Contexts & back end query - HCM

While writing Fast Formula in HCM we consume DBIs often.

There are cases where we need to see the logic behind the DBI, what are the contexts we need to set to get the correct output from DBI.

Below query will help fetching those details for you -

SELECT d.base_user_name DBI_NAME
,      d.data_type DBI_DATA_TYPE
,      d.definition_text SELECT_CLAUSE
,      r.text WHERE_CLAUSE
,      (select listagg('<' || rcu.sequence_no || ',' || c.base_context_name || '>', ', ')
                    within group (order by rcu.sequence_no)
                    from ff_route_context_usages rcu
                    ,    ff_contexts_b c
                    where rcu.route_id = r.route_id
                    and   rcu.context_id = c.context_id) ROUTE_CONTEXT_USAGES
from   ff_database_items_b d
,      ff_user_entities_b u
,      ff_routes_b r
where  1 = 1
and UPPER(d.base_user_name) LIKE '%PER_ASG_PEOPLE_GROUP_SEGMENT1%'
--and UPPER(d.definition_text) LIKE '%DEPENDENT%FLAG%'
and    d.user_entity_id = u.user_entity_id
and    r.route_id = u.route_id