Database Items

In accordance with the fast formula architecture, only database items whose contexts are available within the formula type maybe used in the various absence formulas. Information about the contexts associated with DBIs and the SQL WHERE clause behind the DBI is stored in the HCM Cloud database and can be retrieved using the following SQL query. Replace <ENTER DBI NAME HERE> with the actual DBI name with quotes. This information is to be used to help determine which DBI should be used in a formula.

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  d.base_user_name =  <ENTER DBI NAME HERE>
and    d.user_entity_id = u.user_entity_id
and    r.route_id = u.route_id;

To find all DBIs based on ANC tables use the following query.

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  UPPER(d.base_user_name) LIKE 'ANC%'
and    d.user_entity_id = u.user_entity_id
and    r.route_id = u.route_id;