List of User Entities, DBIs, Routes and Contexts from R13 19D
List of User Entities, DBIs, Routes and

 513 total views

In this article we will look into the list of User Entities, DBIs, Routes, back end table field, contexts required and contexts set fields.

The DBIs listed can be used for both HCM Extracts and Fast Formula.

Oracle product development team makes efforts to prepare the User Entities, DBIs, Routes list for every release. However I haven’t seen the latest spreadsheets for 20A or 20B. The latest I could find is the list for 19D release. Here are the details of them and the SQL queries using which you can download the latest details from your POD.

1) DBI Spreadsheet

You can use database items in:

  • Formulas, in the Manage Fast Formulas task
  • Extracts, in the Manage HCM Extract Definitions task

Note: In addition to the static database items listed in the worksheet, dynamic database items are generated from your definitions of elements, element input values, balances, and formula global values. You need to run the below SQLs to get the dynamic database items after these are configured.

Instructions to use the DBI Spreadsheet:

  • The worksheet is filtered to show the database items you can use as data elements in HCM Extracts; that is, database items that have a GROUP_NAME.
  • To see all database items that you can use in formulas, clear the filter on column C GROUP_NAME.
  • The database items you can use in a formula depend on the formula type. The formula type must provide the contexts used by the database item (see column M).

Definitions:

Database Item:
A database item provides a predefined programmatic approach for retrieving an individual piece of information. Behind a database item is a SQL Query, partly defined in a route. A route is connected to a database item by a user entity.

Database Item Group:
A database item group allows a database item to execute different route code depending upon the context presented to the database item at run time. This provides flexibility to use the same database item at multiple levels of the employment hierarchy.

User Entity:
The user entity provides a link between a database item and the underlying route code to identify the table to select from.

Description for the Columns on the DBI Spreadsheet:

A. MODULE_NAMEModule that owns the database item.
B. BASE_GROUP_NAMEUnique name for the database item group.
C. GROUP_NAMETranslated database item group name, used for selecting data elements in HCM Extracts.
D. BASE_USER_NAMEUnique name for the database item.
E.USER_NAMEUnique name for the database item
F. DESCRIPTIONTranslated description of the database item, used for selecting database items for formulas.
G. DATA_TYPEData type of the database item.
H. DEFINITION_TEXTSelect item text used in the built-up SQL statement.
I. BASE_USER_ENTITY_NAMEUnique name for the user entity.
J. DESCRIPTION_1Description of the user entity.
K. BASE_ROUTE_NAMEUnique name for the route.
L. MULTI_ROW_FLAGIndicates whether the FROM clause SQL can return more than one row.
M. CONTEXTS_USEDContexts required by the database item’s route.
N. PARAMETERSParameters that can be passed to the database item’s route to restrict the number of rows returned.
O. CONTEXTS_SETContexts provided by the database item.

These can be generated using the below SQL queries in your pod.

SQL Query for DBIs:

SELECT fat.module_name,
	fdg.base_group_name,
	fdg.group_name,
	fdi.base_user_name,
	fdi.user_name,
	fdi.description,
	fdi.data_type,
	fdi.definition_text,
	fue.base_user_entity_name,
	fue.description,
	fr.base_route_name,
	fr.multi_row_flag,
	(
		SELECT substr(sys.stragg(',' || base_context_name), 2) context
		FROM fusion.ff_route_context_usages i,
			fusion.ff_contexts_vl j
		WHERE i.context_id = j.context_id
			AND i.route_id = fr.route_id
		) contexts_used,
	(
		SELECT substr(sys.stragg(',' || parameter_name), 2) context
		FROM fusion.ff_route_parameters
		WHERE route_id = fr.route_id
		) parameters,
	(
		SELECT substr(sys.stragg(',' || base_context_name), 2) context
		FROM fusion.ff_dbi_groups_vl a,
			fusion.ff_dbi_usages b,
			fusion.ff_database_items_vl c,
			fusion.ff_contexts_vl d
		WHERE a.context_id = d.context_id
			AND a.dbi_group_id = b.dbi_group_id
			AND b.dbi_id = c.database_item_id
			AND c.user_entity_id = fue.user_entity_id
		) contexts_set
FROM fusion.ff_database_items_vl fdi,
	fusion.ff_dbi_usages fdu,
	fusion.ff_dbi_groups_vl fdg,
	fusion.fnd_appl_taxonomy_vl fat,
	fusion.ff_user_entities_vl fue,
	fusion.ff_routes_vl fr
WHERE fdi.module_id IS NOT NULL
	AND fdi.database_item_id = fdu.dbi_id(+)
	AND fdu.dbi_group_id = fdg.dbi_group_id(+)
	AND fdi.module_id = fat.module_id
	AND fdi.user_entity_id = fue.user_entity_id
	AND fue.route_id = fr.route_id
	AND fdi.module_id IS NOT NULL
ORDER BY module_name,
	fdi.base_user_name

2) Entity & Attribute Spreadsheet

This spreadsheet lists the User Entity attributes for HCM Extracts.

A user entity is a logical entity which you can associate to a block when you define a HCM extract. This spreadsheet provides you with all the user
entities and their associated DBIs. You can use this spreadsheet to view filtered lists of DBIs based on the user entity name you select. After you
select the user entity at the block level, the DBIs are visible when you create the data elements for the HCM extract.

SQL Query to pull User Entity attributes:

SELECT module_name,
	entity_name,
	entity_leg_code,
	entity_desc,
	attribute_name,
	attribute_leg_code,
	definition_text,
	flag
FROM (
	SELECT entity.user_entity_id,
		entity.base_user_entity_name entity_name,
		entity.description entity_desc,
		entity.legislation_code entity_leg_code,
		dg.group_name attribute_name,
		dg.legislation_code attribute_leg_code,
		fdi.definition_text,
		'Y' flag,
		dg.module_id
	FROM fusion.ff_user_entities_vl entity,
		fusion.ff_database_items_vl fdi,
		fusion.ff_dbi_usages du,
		fusion.ff_dbi_groups_vl dg
	WHERE entity.user_entity_id = fdi.user_entity_id
		AND fdi.database_item_id = du.dbi_id
		AND dg.dbi_group_id = du.dbi_group_id
	
	UNION
	
	SELECT entity.user_entity_id,
		entity.base_user_entity_name entity_name,
		entity.description entity_desc,
		entity.legislation_code entity_leg_code,
		dg.group_name attribute_name,
		dg.legislation_code attribute_leg_code,
		dbi.definition_text,
		'N' flag,
		dg.module_id
	FROM fusion.ff_routes_b ffr,
		fusion.ff_user_entities_b ue,
		fusion.ff_database_items_b dbi,
		fusion.ff_dbi_usages du,
		fusion.ff_dbi_groups_vl dg,
		fusion.ff_user_entities_vl entity
	WHERE dg.dbi_group_id = du.dbi_group_id
		AND dbi.database_item_id = du.dbi_id
		AND ue.user_entity_id = dbi.user_entity_id
		AND ue.route_id = ffr.route_id
		AND (
			ffr.multi_row_flag IS NULL
			OR ffr.multi_row_flag = 'N'
			)
		AND dbi.data_type IN (
			'D',
			'N',
			'T'
			)
		AND dbi.module_id IS NOT NULL
		AND NOT EXISTS (
			SELECT ffc.context_id,
				ffc.base_context_name
			FROM fusion.ff_route_context_usages rcu,
				fusion.ff_contexts_b ffc
			WHERE rcu.route_id = ffr.route_id
				AND ffc.context_id = rcu.context_id
				AND ffc.base_context_name NOT IN (
					'EFFECTIVE_DATE',
					'LEGISLATIVE_DATA_GROUP_ID'
					) MINUS
			SELECT fdg.context_id,
				ffc.base_context_name
			FROM fusion.ff_dbi_groups fdg,
				fusion.ff_dbi_usages fdu,
				fusion.ff_database_items_b dbi,
				fusion.ff_ftype_context_usages fcu,
				fusion.ff_formula_types_b ft,
				ff_contexts_b ffc
			WHERE dbi.user_entity_id = entity.user_entity_id
				AND ft.formula_type_id = fcu.formula_type_id
				AND ft.base_formula_type_name = 'Extract Record'
				AND ffc.context_id = fcu.context_id
				AND dbi.database_item_id = fdu.dbi_id
				AND fdg.dbi_group_id = fdu.dbi_group_id
				AND fdg.context_id = fcu.context_id
				AND fdg.context_id IS NOT NULL
			)
	) entity,
	fnd_appl_taxonomy_vl mname
WHERE entity.module_id = mname.module_id
	AND entity.user_entity_id IN (
		SELECT DISTINCT user_entity_id
		FROM fusion.ff_database_items_b a,
			fusion.ff_dbi_usages b,
			fusion.ff_dbi_groups_vl c
		WHERE a.database_item_id = b.dbi_id
			AND b.dbi_group_id = c.dbi_group_id
			AND a.module_id IS NOT NULL
			AND c.context_id IS NOT NULL
		)
ORDER BY module_name,
	entity_name,
	attribute_name

3) Route and Route Query:

Routes are the from and where clauses for User Entities. DBIs are the selected fields from the SQL Query.

SQL Query to get all the Routes:

SELECT base_route_name,
	TEXT
FROM ff_routes_vl

Spreadsheets from 19D release:

19D DBI SpreadsheetDownload Link
19D_Entity & Attribute Spreadsheet – Download Link
19D_Route And Route QueryDownload Link

Hope these will be useful when you want to search for Database Items while creating Fast Formulas/ HCM Extracts.

Tip: These can be referred to when you have a requirement to build any BI Reports as well as you can find the fields, tables and where conditions including joins. This will act as a good starting point and from there you can modify it to get your requirement accomplished.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

Sricharan

Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply