SQL Query to get the Contexts for Formula Type in Oracle Fusion

SELECT ft.base_formula_type_name

      ,base_context_name 

  FROM ff_contexts_vl con

      ,ff_ftype_context_usages fcu

  ,ff_formula_types_vl ft

 WHERE ft.formula_type_id = ft.formula_type_id

   AND fcu.formula_type_id = ft.formula_type_id

   AND con.context_id = fcu.context_id

   AND ft.base_formula_type_name = :p_formula_type_name

SQL Query to get the contexts of a DBI in Oracle Fusion

SELECT dbi.user_name

      ,base_context_name

  FROM ff_database_items_vl dbi

      ,ff_user_entities_vl userent

      ,ff_routes_vl routes

      ,ff_route_context_usages rcu

      ,ff_contexts_vl con

 WHERE dbi.user_entity_id = userent.user_entity_id

   AND userent.route_id = routes.route_id

   AND routes.route_id = rcu.route_id

   AND con.context_id = rcu.context_id

   AND dbi.base_user_name = :p_dbi_name

SQL Query to find all DBI's based on ANC Tables in Oracle Fusion

SELECT d.base_user_name dbi_name

      ,d.data_type dbi_data_type

      ,(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 UPPER(d.base_user_name) LIKE 'ANC%'

   AND d.user_entity_id = u.user_entity_id

   AND r.route_id = u.route_id