Oracle Workflow Table details with relationship diagram

A) Some comments on the graphical representation:

The columns belonging to the primary key are indicated in red.

Only the columns that either belong to a primary key or a foreign key are shown on this diagram.
The columns belonging to a foreign key may also belong to a primary key.

This model is split into 2 diagrams. Due to this, sometimes the start or the end point of a foreign key arrow is not on the current diagram.

Due to the graphical representation of foreign key structure, a column name may appear more than once inside a table. Of course this does not mean the column is duplicated within the table.

For a complete description of each of the tables, please refer to:

Oracle eTRM Technical Reference web site: http://etrm.oracle.com or note 150230.1 (ETRM DIRECT ACCESS DOCUMENT).


B) Some comments on workflow tables:

1) Difference between WF_ACTIVITIES and WF_PROCESS_ACTIVITIES

WF_ACTIVITIES contains only the activities that belong to the item type
Does NOT contain activities that belong to other item types (ie pre-defined activities).

WF_PROCESS_ACTIVITIES contains the activities of the item type AND the pre-defined activities that are referenced in the item type.

WF_PROCESS_ACTIVITIES, in addition to its primary key instance_id, has a unique key defined on the following columns:

1.Instance_label
2.Process_item_type
3.Process_name
4.Process_version


2) The following tables have a corresponding translation ( _TL) table:


- WF_ITEM_TYPES
- WF_ITEM_ATTRIBUTES
- WF_ACTIVITIES
- WF_ACTIVITY_ATTRIBUTES
- WF_MESSAGES
- WF_MESSAGE_ATTRIBUTES

The workflow lookup tables only exist as translation (_TL) tables:

WF_LOOKUP_TYPES_TL
WF_LOOKUPS_TL


(Translation tables are not shown in the above diagrams)



3) Difference between WF_ITEM_ACTIVITY_STATUSES and WF_ITEM_ACTIVITY_STATUSES_H

WF_ITEM_ACTIVITY_STATUSES and WF_ITEM_ACTIVITY_STATUSES_H have the same structure.

When the same activity is executed multiple times within a workflow (ie looping activity),
WF_ITEM_ACTIVITY_STATUSES will only contain information on the last execution of the activity, whereas the information on all the previous executions will be stored in WF_ITEM_ACTIVITY_STATUSES_H.

When an activity is executed only once within a workflow, only WF_ITEM_ACTIVITY_STATUSES will be populated.

Oracle Applications : Descriptive Flexfields (DFF) Overview and the SQL to find the Columns & Details.

Descriptive Flexfields

Descriptive flexfields let you satisfy different groups of users without having to reprogram your application, by letting you provide customizable “expansion space”? on your forms.

For example, suppose you have a retail application that keeps track of customers. Your Customers form would normally include fields such as Name, Address, State, Customer Number, and so on. However, your form might not include extra fields to keep track of customer clothing size and color preferences, or regular salesperson, since these are attributes of the customer entity that depend on how your users use your application.

For example, if your retail application is used for a tool company, a field for clothing size would be undesirable. Even if you initially provide all the fields your users need, your users might later identify even more customer attributes that they want to keep track of. You add a descriptive flexfield to your form so that your users have the desired expansion space. Your users can also take advantage of the fact that descriptive flexfields can be context sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

A descriptive flexfield describes an application entity, providing form and database expansion space that you can customize. Each descriptive segment has a name you assign. You can specify valid segment values or set up criteria to validate the entry of any value.

Oracle General Ledger includes a descriptive flexfield in its journal entry form to allow end users to add information of their own choosing. For example, end users might want to capture additional information about each journal entry, such as source document number or the name of the person who prepared the entry. You could use a descriptive flexfield in a fixed assets application you build to allow further description of a fixed asset. You could let the structure of your assets flexfield depend on the value of an asset type field. For example, if asset type were “desk”, your descriptive flexfield could prompt for style, size and wood type. If asset type were “computer”, your descriptive flexfield could prompt for CPU chip and memory size.

SELECT fdfv.title
,fdfv.application_table_name
,fdfv.context_column_name
,fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num
,fdfcu.application_column_name
,fdfcu.end_user_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu
fnd_descriptive_flexs_vl fdfv
WHERE fdfv.title = 'Line Transaction Flexfield'
AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
AND fdfcu.application_id = fdfv.application_id
ORDER BY fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num ;

SELECT fdfv.title,
,fdfv.application_table_name
,fdfv.context_column_name
,fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num
,fdfcu.application_column_name
,fdfcu.end_user_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu
,fnd_descriptive_flexs_vl fdfv
WHERE fdfv.title = 'Invoice Transaction Flexfield'
AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
AND fdfcu.application_id = fdfv.application_id
ORDER BY fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num ;


SELECT fdfv.title,
,fdfv.application_table_name
,fdfv.context_column_name
,fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num
,fdfcu.application_column_name
,fdfcu.end_user_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu
,fnd_descriptive_flexs_vl fdfv
WHERE fdfv.title = 'Reference Transaction Flexfield'
AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
AND fdfcu.application_id = fdfv.application_id
ORDER BY fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num ;


SELECT fdfv.title,
,fdfv.application_table_name
,fdfv.context_column_name
,fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num
,fdfcu.application_column_name
,fdfcu.end_user_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu
,fnd_descriptive_flexs_vl fdfv
WHERE fdfv.title = 'Link-to Transaction Flexfield'
AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
AND fdfcu.application_id = fdfv.application_id
ORDER BY fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num ;

Oracle applications - Key Flex Field Structures & Table Details.

Here is some of quite commonly used AOL FND (Foundation) tables and their usage. There are many other tables also in FND but here i am putting only few commonly used tables. for other table if needed we can dig further.

FND_ID_FLEXS stores registration information about key flexfields. Each row includes the four–character code that identifies the key flexfield, the title of the flexfield (by which a user identifies theflexfield), the name of the combinations table that contains the key flexfield columns, and the name of the structure defining (MultiFlex) column for the flexfield (SET_DEFINING_COLUMN_NAME). Each row also contains values that identify the application that owns the combination table and the application that owns the key flexfield, a table–type flag that specifies whether the combinations table is specificor generic (S or G), whether dynamic inserts are feasible for the flexfield(Y or N), whether the key flexfield can use ID type value sets, and the name of the unique ID column in the combinations table. You need one row for each key flexfield in each application. Oracle Application ObjectLibrary uses this information to generate a compiled key flexfield definition

FND_ID_FLEX_SEGMENTS: FND_ID_FLEX_SEGMENTS stores setup information about keyflexfield segments, as well as the correspondences between application table columns and the key flexfield segments the columns are used for. Each row includes a flexfield application identifier, the flexfield code,which identifies the key flexfield, the structure number(ID_FLEX_NUM), the value set application identifier, the segment number (the segment’s sequence in the flexfield window), the name of the column the segment corresponds to (usually SEGMENTn, where n is an integer). Each row also includes the segment name, whether security is enabled for the segment, whether the segment is required, whether the segment is one of a high, low segment pair, whether the segment is displayed, whether the segment is enabled (Y or N), type of default value, display information about the segment such as prompts and display size, and the value set the segment uses. Each row also includes a flag for whether the table column is indexed; this value is normally Y. You need one row for each segment of each structure for each flexfield. Oracle Application Object Library uses this information to generate a compiled key flexfield definition to store in the FND_COMPILED_ID_FLEXS table Thanks – Shivmohan Purohit

FND_ID_FLEX_STRUCTURES : FND_ID_FLEX_STRUCTURES stores structure information about keyflexfields. Each row includes the flexfield code and the structurenumber (ID_FLEX_NUM), which together identify the structure, and the name and description of the structure. Each row also includes values that indicate whether the flexfield structure is currently frozen, whether rollup groups are frozen (FREEZE_STRUCTURED_HIER_FLAG), whether users can dynamically insert new combinations of segment values through the flexfield pop–up window, and whether the flexfield should use segment cross–validation rules. Each row also contains information about shorthand flexfield entry for this structure, including whether shorthand entry is enabled, the prompt for the shorthand window, and the length of the shorthand alias field in the shorthandwindow. You need one row for each structure of each key flexfield. Oracle Application Object Library uses this information to generate acompiled key flexfield definition to store in the FND_COMPILED_ID_FLEXS table

FND_FLEX_VALUES stores valid values for key and descriptive flexfield segments. Oracle Application Object Library uses this table when users define values for independent or dependent type value sets. Oracle Application Object Library also uses this table when users define parent values for ranges of child values that exist in a validation table(Oracle Application Object Library stores the parent values in this table). Each row includes the value (FLEX_VALUE) and its hierarchy level if applicable as well as the identifier of the value set the value belongs to. If the value is a dependent value, PARENT_FLEX_VALUE_LOW contains the independent value this value depends upon. Oracle Application Object Library does not use the PARENT_FLEX_VALUE_HIGH column. If ENABLED_FLAG contains N, this value is currently invalid, regardless of the start and end dates.

If ENABLED_FLAG contains Y, the start and end dates indicate if this value is currently valid.SUMMARY_FLAG indicates if this value is a parent value that has child values, and STRUCTURED_HIERARCHY_LEVEL contains the rollup group the parent value belongs to, if any (1 through 9). COMPILED_VALUE_ATTRIBUTES contains the compiled values of anysegment qualifiers assigned to this value. These values are in a special Oracle Application Object Library format, and you should never modify them.

VALUE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE50 are descriptive flexfield columns, where VALUE_CATEGORY is the context (structure defining) column.

These descriptive flexfield columns do not contain values unless you have defined the descriptive flexfield at your site. You need one row for each independent, dependent or parent value belonging to a value set.Oracle Application Object Library uses this information to ensure that users enter valid values in flexfield segments

FND_FLEX_VALUE_HIERARCHIES stores information about child value ranges for key flexfield segment values. Each row includes an identification of the parent value the range belongs to, as well as the low and high values that make up the range of child values. FLEX_VALUE_SET_ID identifies the value set to which the parent value belongs. You need one row for each range of child values (you can have more than one row for each parent value). Oracle Application Object Library provides this information for applications reporting purposes.

SELECT
B.APPLICATION_ID, B.ID_FLEX_CODE, B.ID_FLEX_NUM,
B.ID_FLEX_STRUCTURE_CODE,
B.CONCATENATED_SEGMENT_DELIMITER,
B.CROSS_SEGMENT_VALIDATION_FLAG, B.DYNAMIC_INSERTS_ALLOWED_FLAG, B.ENABLED_FLAG,
B.FREEZE_FLEX_DEFINITION_FLAG, B.FREEZE_STRUCTURED_HIER_FLAG, B.SHORTHAND_ENABLED_FLAG,
T.ID_FLEX_STRUCTURE_NAME, T.DESCRIPTION
FROM FND_ID_FLEX_STRUCTURES_TL T, FND_ID_FLEX_STRUCTURES B
WHERE B.APPLICATION_ID = T.APPLICATION_ID
AND B.ID_FLEX_CODE = T.ID_FLEX_CODE
AND B.ID_FLEX_NUM = T.ID_FLEX_NUM
AND T.LANGUAGE = userenv(‘LANG’)
AND B.ENABLED_FLAG = ‘Y’
AND B.FREEZE_STRUCTURED_HIER_FLAG = ‘Y’
AND B.ID_FLEX_CODE = ‘GL#’
AND B.ID_FLEX_NUM = &&number
Here are some more SQLs to find the GL combinations structures:

SELECT s.FLEX_VALUE_SET_ID,s.FLEX_VALUE_SET_NAME, v.FLEX_VALUE, t.DESCRIPTION
FROM fnd_flex_values v,fnd_flex_value_sets s,fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME LIKE ‘%&&Company_Department%’
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
ORDER BY FLEX_VALUE
SELECT s.FLEX_VALUE_SET_ID,s.FLEX_VALUE_SET_NAME, v.FLEX_VALUE, t.DESCRIPTION
FROM fnd_flex_values v,fnd_flex_value_sets s,fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME LIKE ‘%&&Company_Account%’
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
ORDER BY FLEX_VALUE

SELECT s.FLEX_VALUE_SET_ID,s.FLEX_VALUE_SET_NAME, v.FLEX_VALUE, t.DESCRIPTION
FROM fnd_flex_values v,fnd_flex_value_sets s,fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME LIKE ‘%&&Company_Future%’
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
ORDER BY FLEX_VALUE

SELECT s.FLEX_VALUE_SET_ID,s.FLEX_VALUE_SET_NAME, v.FLEX_VALUE, t.DESCRIPTION
FROM fnd_flex_values v,fnd_flex_value_sets s,fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME LIKE ‘%&&Company_Location_FA%’
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
ORDER BY FLEX_VALUE

SELECT s.FLEX_VALUE_SET_ID,s.FLEX_VALUE_SET_NAME, v.FLEX_VALUE, t.DESCRIPTION
FROM fnd_flex_values v,fnd_flex_value_sets s,fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME LIKE ‘%&&Company_Country_FA%’
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
ORDER BY FLEX_VALUE

SELECT s.FLEX_VALUE_SET_ID,s.FLEX_VALUE_SET_NAME, v.FLEX_VALUE, t.DESCRIPTION
FROM fnd_flex_values v,fnd_flex_value_sets s,fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME LIKE ‘%&&Company_State_FA%’
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
ORDER BY FLEX_VALUE

SELECT s.FLEX_VALUE_SET_ID,s.FLEX_VALUE_SET_NAME, v.FLEX_VALUE, t.DESCRIPTION
FROM fnd_flex_values v,fnd_flex_value_sets s,fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME LIKE ‘%&&Company_City_FA%’
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
ORDER BY FLEX_VALUE

SELECT *
FROM fnd_flex_value_sets a
,fnd_flex_values b
WHERE a.flex_value_set_id = b.flex_value_set_id
AND a.flex_value_set_name like 'YES_NO'

SELECT *
FROM fnd_flex_value_sets a
WHERE a.flex_value_set_name like 'YES_NO'