The chapter includes the following sections:
About Using the Applications Hierarchy Component to Develop Applications
About Integrating Custom Task Flows into the Applications Hierarchy Component
About Using the fnd:hierarchy Property Inspector to Specify Tree Versions
About Using the Expression Builder to Bind TreeCode_ TreeStructureCode_ and TreeVersionId Properties
Oracle Fusion tree management allows data in applications to be organized into a hierarchical fashion, and allows you to create tree hierarchies based on specific data.
Here are some of the advantages of how using tree hierarchies to develop applications can help you:
Reusable code that results in a one-time-only implementation of many tree-management features, and can be used immediately by every type of application hierarchy.
Open metadata that can be read by any application that needs to use tree-management hierarchies. This does the following:
Minimizes the number of application programming interfaces (APIs) that need to be written for accessing hierarchies
Allows the sharing and understanding of hierarchies across multiple applications
Allows the sharing of hierarchies with Oracle Business Intelligence reporting and analytics systems
Tree structures that capture the business rules the data must adhere to.
ADF Business Components view objects that are used as data sources, eliminating the need to build new types of data sources.
Hierarchical relationships between entities that are external to the entity itself, allowing multiple hierarchical views to be implemented for the same set of entities. Each of these hierarchies can be used to implement a different business function.
Data flattening that improves query performance against the hierarchical data, especially for hierarchical queries such as roll-up queries.
Business events that can be consumed by any application requiring additional processing on specific tree operations.
Tree and node-level access control that eliminates the need for product teams to write their own access-control code.
Well-defined APIs available for metadata and data that make it easy for the Oracle Fusion Upgrade Office to write migration tools for existing hierarchies in E-Business Suite, PeopleSoft, and Siebel.
As a developer, you will work mostly with tree structures. The task of working with trees and tree versions normally will fall to customers. However, since you probably also will be required to work with trees and tree versions, both types of tasks are described in this chapter.
A tree structure is a way of describing a hierarchy. A tree is an instance of this hierarchy. Every tree structure contains a tree. Trees may have one or more versions. Each tree version contains at least one root node; that is, a member that has no superior. (Occasionally, a tree version may have more than one root node.) The lines connecting elements in a tree structure are branches; the elements themselves are nodes.
The names of relationships are modeled after family relations:
A node is a parent of another node if it is one step higher in the hierarchy and closer to the root node.
Sibling nodes share the same parent node.
For example, in the following picture, XYZ Corp. is the parent of Marketing and Finance, which are its children. Accounts Receivable and Accounts Payable are siblings, and are the children of Finance.
Figure 20-1 Example of a Tree
In Oracle Fusion tree management, a tree structure defines a group of common business rules for a family of trees, for example, Department, Account, or Project, and allows an application to select and enable a subset of trees to fulfill a specific purpose in that application.
A tree contains data that is organized in a hierarchy, allowing the creation of groupings and rollups of information that already exist within an organization. A tree can have one or more tree versions. Typically, when changes are made to an existing tree, a new version is created and published.
A tree structure data source supplies the data for a tree through its nodes. Multiple data sources can be associated with a tree structure and can have well-defined relationships among them. Using the example in the previous picture, the Accounts Receivable data source is a child of the Finance data source. Data sources also support business rules that define how the data from a data source participates in a tree.
The following table lists other commonly used tree terms and their descriptions.
Table 20-1 Common Tree Terminology
Term | Description |
---|---|
Depth |
The depth of a node is the length of the path from the root to the node. The root node is at depth zero. |
Label |
Allows for a storage of "tags" that can be used on each tree node in a tree. There are three labeling schemes:
Labels can be stored in any table and the label data source is registered with the tree structure. |
Tree label |
When a labeling scheme is used for trees, the selected labels are stored in the tree label entity and each tree node references a tree label. See "Label." |
Node |
A logical term that refers to the actual data, whatever that may be. Technically, the node may be stored either in a product-specific table or in an entity that has been established by the Tree Management solution as the default storage mechanism. However, since all data in Oracle Applications usually already has a storage home, only customers should store the node in an entity. |
Tree node |
A node that is included in a tree. |
Tree node type |
A tree node has a node type. Node types can be any one of the following:
|
Tree levels |
Provide a way to organize tree nodes. In most trees, all nodes at the same level represent the same kind of information. For example, in a tree that reflects the organizational hierarchy, all division nodes appear on one level and all department nodes on another. Similarly, in a tree that organizes a user's product catalog, the nodes representing individual products might appear on one level and the nodes representing product lines on the next higher level. When levels are not used, the nodes in the tree have no real hierarchy or reporting structure but do form a logical summarization structure. Strictly enforced levels mean that the named levels describe each node's position in the tree. This is natural for most hierarchies. Loosely enforced levels mean that the nodes at the same visual level of indentation do not all represent the same kind of information, or nodes representing the same kind of information appear at multiple levels. With loosely enforced levels, users assign a level to each node individually; the level is not tied to a particular visual position. |
Tree structure access |
The set of rules that control access to a tree structure. |
Tree access |
The set of rules that control access to a tree. |
Tree node access |
The set of rules that control access to a particular node (and its subtree) within a given tree version. |
Effective dates |
Enable users to specify new objects, departments, reporting relationships, or organizational structures in advance and have them take effect automatically. Users also can use trees with past, present, or future effective dates when reporting on current or historic data. |
Reference data set determinant (external) |
A value that determines which reference data set will be used for each reference data object. Business units, regulatory regions, and reference data sets all can determine which reference data sets are valid for the creation of a transaction or reference data object. |
Audit |
A process that runs a series of tests against tree metadata and tree data to validate its integrity. |
Before you can manage tree structures, trees, and tree versions using the web-browser-based trees application, you must create the application launch page in Oracle JDeveloper. The launch page contains links to the Tree Structures, Trees and Tree Versions, and Manage Labels applications, which contain the management task flows you will use.
You also will need to perform additional steps that are required to schedule the concurrent processes that the trees application uses for audit and flattening.
Before you begin:
Create an application initialized for use with Oracle Middleware Extensions for Applications. For more information, see Chapter 2, "Setting Up Your Development Environment."
Tree Structures define hierarchical relationships between information stored in one or more data sources. Working with tree structures includes the following tasks:
Managing tree structure data sources
Specifying data source parameters
Searching for, creating, duplicating, editing, or deleting tree structures
Setting tree structure status
Auditing tree structures.
A flag named PROTECTED_FLAG
is available for the tree structures. By default, this flag is set to N
. If you set this flag to Y
, you cannot modify the tree structure. When you want to protect a tree structure from being updated, you can set this flag to Yes. A protected tree structure can be modified or deleted only by the SEED_DATA_FROM_APPLICATION
user.
You can create an exclusion list and tree structures to that list. Any tree structure that is a part of the exclusion list will not be considered for export. The tree structures which have been excluded can be populated by writing an SQL statement.
Tree structure data sources provide the data items for a hierarchy. In the tree-management infrastructure, these are ADF Business Components view objects. You should define view objects for all the intended data sources before setting up a tree structure. For each view object attribute that is to be displayed in the hierarchy column of an ADF Faces Tree or ADF Faces TreeTable, the Application property HierarchyDisplay is set to true
.
Figure 20-4 View Object Attributes: HierarchyDisplay Property
Tree management provides a generic data source for holding nodes: oracle.apps.fnd.applcore.trees.model.view.FndNodeVO
. This data source may be used for tree-only nodes, that is, nodes that do not exist in any other entity in the system. Likewise, a generic label data source has also been provided: oracle.apps.fnd.applcore.trees.model.view.FndLabelVO
.
Tree data sources have optional data source parameters with defined view criteria and associated bind variables. You can specify view criteria as a data source parameter when creating a tree structure, and edit the parameters when creating a tree.
Note:
Parameter values customized at the tree level will override the default values specified at the tree-structure level.
The parameters will be applied when performing node operations, and the display of the nodes in the hierarchy, for any tree version under that data source. Data source parameters also provide an additional level of filtering for different tree structures.
Tree management supports three data source parameter types:
VIEW_CRITERIA - Used to capture the view criteria name, which will be applied to the data source view object
BOUND_VALUE - Used to capture any bound value, which will be used as part of the view criteria condition
VARIABLE - Used to capture and bind variable that is being used by the data source view object, particularly for WHERE
clause support
In addition to parameter values provided by the customer, tree management provides support for those special parameters whose values for any bind variable are seeded at runtime by tree management.
For example, to use the effectiveStartDate attribute of a tree version that a data source uses as one of the bind variables from which the value for the effectiveStartDate bind variable will be retrieved from the trees effective start date, you can specify a data source parameter effectiveStartDate
with the value #{treeVersion.effectiveStartDate}
. You would then need to expose an effectiveStartDate bind variable for the data source view object either in view criteria or a WHERE
clause.
You can specify parameters using the syntax for value and name shown in the following table.
Table 20-2 Parameter Syntax
Attribute | Syntax |
---|---|
Tree Structure |
#{treeStructure.ATTR_NAME_WITH FIRSTCHAR_IN_LOWER CASE} For example, |
Tree |
#{tree.ATTR_NAME_WITH FIRSTCHAR_IN_LOWER CASE} For example, |
Tree Version |
#{treeVersion.ATTR_NAME_WITH FIRSTCHAR_IN_LOWER CASE} For example, |
Note:
Binding parameters are supported for String, Number, and Date data types only.
In 11gR1, tree management does not support View Criteria and Variable when used in combination, or multiple View Criteria as data source parameters.
This section includes an example use case and discusses basic use cases and their settings.
The data source DemoEmpVO has the view criteria DemoEmpVC1, which is based on the bound values DemoEmpBV1 and DemoEmpBV2. These are to be applied to the data source view object for tree versions under the DEMO_EMP_TS tree structure, with varying bound values DemoEmpBV1 and DemoEmpBV2 for trees under this tree structure.
When creating the tree structure FND_DEMO_EMP_TS, the following parameters must be added to the tree structure data source that corresponds to DemoEmpVO:
Parameter name: VIEW_CRITERIA_NAME
Parameter type: View Criteria
Parameter value: <name of the view criteria to be applied
>, in this case DemoEmpVC1
The following two bindings also must be added:
Parameter names: DemoEmpBV1
and DemoEmpBV2
Parameter type: Bound Value
Parameter value: <actual value, which can be overridden at tree level
>
Note:
Binding parameters are supported for String and Number data types only.
The following are examples of use cases and settings that you can implement using the parameter infrastructure for tree structure data sources.
Data source having a view criteria defined with a bind variable:
Figure 20-5 View Object Setup Wizard
Figure 20-6 Parameters in Data Source Parameter UI
Data source has a WHERE clause using a bind variable:
Figure 20-7 View Object Setup Wizard
Figure 20-8 Parameters in Data Source Parameter UI
Data source has a view criteria defined with a bind variable for special parameters:
Figure 20-9 View Object Setup Wizard (View Criteria)
Figure 20-10 Parameters in Data Source Parameter UI
Data source has a WHERE clause using a bind variable for special parameters:
Figure 20-11 View Object Setup Wizard (WHERE Clause)
Figure 20-12 Parameters in Data Source Parameter UI
If you wish to duplicate, edit, or delete an existing tree structure and it is not currently visible in the results list, you can search for it using the following procedure. The procedure assumes that the Tree Structure summary page is open in your web browser.
To search for an existing tree structure, do the following:
Throughout the trees application you will see a search field located to the right of many field names, as shown in the following picture.
Figure 20-13 Search Field
Click the down arrow to display a dropdown list that contains the available values for that field. You can select from the list, or search for other values. For example, the following picture shows the dropdown list that displays when you click the down arrow associated with the Application search field found on the Create Tree Structure: Specify Definition page.
Figure 20-14 Search Field Dropdown List
From each search field dropdown list, you can do one of the following:
Select a value from the list that displays.
Click the Search link to search for a value that does not exist in the list.
If you select a value from the list, the dropdown list closes and that value appears in the search field.
If you click the Search link, a search-and-select window similar to the one shown in the following picture opens:
Figure 20-15 Search-and-Select Window
You now can search for a value and then click OK to select it.
The following procedure explains how to create a new tree structure. The procedure assumes that the Tree Structure summary page is open in your web browser.
To create a tree structure, do the following:
Duplicating a tree structure simply copies the metadata definition from an existing tree structure to the duplicate. This operation does not copy the underlying tree and tree versions defined for the source tree structure.
To duplicate a tree structure, do the following:
When you edit an existing tree structure, you simply step through many of the same pages you used to create a tree structure.
To edit an existing tree structure, do the following:
Deleting a tree structure also deletes all associated tree and tree versions defined under that specific tree structure.
To delete a tree structure, do the following:
Changing the status of a tree structure also changes the status of the trees and tree versions contained in that tree structure. You can set the status of a tree structure to any one of the following:
Draft
Active
Inactive
Setting a tree structure's status to Active automatically triggers an audit of that tree structure. See the following section for more information about auditing.
To set the status of a tree structure, do the following:
Auditing tree-structure metadata verifies that it conforms to all rules and ensures data integrity. Running an audit allows you to view audit details and messages, and to correct any validation errors that the audit detects.
Setting a tree structure's status to Active automatically triggers an audit of that tree structure. You also can audit a tree structure manually.
The following table describes what each validator checks for, as well as possible reasons why each validator might fail.
Table 20-3 Validator Descriptions
Validator | Checks for... | Validation may have failed because... | To correct... |
---|---|---|---|
Restrict by SetID Validator |
If the tree structure has Restrict Tree Node List of Values Based on SetID flag set to This restriction does not apply when the flag is set to |
The tree structure has Restrict Tree Node List of Values Based on SetID flag = |
Consult the owning developer. If SetID restriction is desired for this tree structure, ensure your developer has included a SetID attribute on all data sources. If SetID restriction is not desired, ensure your developer sets the flag to |
Row Flattened Table Name Validator |
A valid "Row Flattened Table" should be specified for the tree structure on the "Specify Performance Options" page. It can be the standard row flattened table |
|
Consult the owning developer to correct the row flattened table definition. |
Available Label Data Sources Validator |
If the tree structure has a Labeling Scheme specified, the label data source view object specified for each data source must be accessible and the primary keys must be valid. This restriction does not apply when the Labeling Scheme has been set to |
|
|
Available Data Sources Validator |
Each data source view object specified for the tree structure must be accessible and all its primary key attributes should be valid. |
|
|
Column Flattened Table Name Validator |
A valid "Column Flattened Table" should be specified for the tree structure on the "Specify Performance Options" page. It can be the standard row flattened table |
|
Consult the owning developer to correct the column flattened table definition. |
Restrict by Date Validator |
If the tree structure has Restrict Tree Node List of Values Based on Date flag set to This restriction does not apply when the flag is set to |
The tree structure has |
Consult the owning developer. If the date restriction is desired for this tree structure, ensure your developer has included an EffectiveStartDate and EffectiveEndDate attribute on all data sources. If the date restriction is not desired, ensure your developer sets the flag to |
Tree Node Table Name Validator |
A valid "Tree Node Table" should be specified for the tree structure on the "Specify Performance Options" page. It can be the standard row flattened table |
|
Consult the owning developer to correct the tree node table definition. |
Allow Node Level Security Validator |
If "Allow Node Level Security" flag is set to |
"Allow Node Level Security" flag is set as |
Consult the owning developer to correct the "Allow Node Level Security" flags in the tree structure and/or its data sources. |
When you work with trees, you can do any of the following:
Search
Create
Duplicate
Edit
Delete
You also can audit trees. For more information, see About Auditing Trees and Tree Versions.
If you wish to duplicate, edit, or delete an existing tree and it is not currently visible in the results list, you can search for it using the following procedure. The procedure assumes that the Tree summary page is open in your web browser.
To search for an existing tree, do the following:
Click Advanced to perform an advanced search by specifying additional options for search. You also can save your search criteria for future use.
The following procedure explains how to create a tree. You will also need to create a tree version with a root node. For more information, see How to Create a Tree Version.
To create a tree, do the following:
Duplicating a tree copies only the selected tree. The operation does not copy any of the underlying tree versions.
To duplicate a tree, do the following:
When you edit an existing tree, you step through many of the same pages you used to create a tree.
To edit a tree, do the following:
When you work with tree versions, you can do any of the following:
Search
Create
Add nodes to a tree version
Edit existing nodes
Drag and drop nodes to move them
Perform CRUD Operations on Tree Nodes using APIs
Perform Sub-tree Node Operations using PL/SQL APIs
Create a new record for a data source
Duplicate
Edit
Delete
Set tree version status
Audit tree versions
Trees require tree versions. You can create a tree with no tree version, but you must add at least one tree version to the tree after it has been created. You either can create the tree version during the tree-creation process, or by editing an existing tree.
To create a tree version, do the following:
Note:
This procedure assumes you are editing an existing tree.
Tree nodes are elements in a tree structure. A tree version must contain at least one root node. If specified, a tree version also can contain multiple root nodes. A node can be the parent of another node if it is one step higher in the hierarchy and closer to the root node.
There are three types of tree nodes:
Those with specific values
Those that have a range of values
Those that have values from a referenced tree
Each type of node has its own configuration options. In addition, you can add tree nodes using a custom Search UI, use drag-and-drop to move nodes once they have been added, and edit existing nodes.
The procedures used to perform these tasks are described in the sections that follow.
The following procedure explains how to configure the Add Tree Node options when the Specific Value node type has been selected.
To configure specific values, do the following:
Note:
This procedure assumes that the Add Tree Node window is open.
The following procedure explains how to configure the Add Tree Node options when the Values within a range node type has been selected.
To configure values within a range, do the following:
Note:
This procedure assumes that the Add Tree Node window is open.
The following procedure explains how to configure the Add Tree Node options when the Values from a referenced hierarchy node type has been selected.
To configure values from a referenced hierarchy, do the following:
Note:
This procedure assumes that the Add Tree Node window is open.
This procedure assumes that the Add Tree Node window shown is open.
Once you have added value-based nodes to a tree version, you can move these nodes around simply by dragging and dropping them.
You can move individual nodes, an entire range of nodes, or an entire referenced node. You cannot, however, move a single node in a range of nodes or a single node in a referenced node.
If the Search UI is not registered for a data source, the default behavior for all nodes displayed in the Add Tree Node window will be used. However, if you register your own Search UI, it will be used to add and select value nodes instead of the default Search UI.
To add a node using a custom Search UI, do the following:
The procedures for adding a node using a custom Search UI are the same as those found in About Adding Tree Nodes to a Tree Version. However, the Add Tree Node window that displays will be the registered custom Search UI rather than the default UI. Note that Select Value Nodes has replaced the default Node Navigator, Available Nodes, and Selected Nodes options.
Figure 20-47 Custom Search UI: Specific Values
You can edit any existing tree node's details.
To edit a tree node, do the following:
Note:
This procedure assumes that the Manage Trees and Tree Versions page is open.
You can export all the nodes or selected nodes of a tree version to a CSV file. This export feature helps you in reviewing the tree versions easily, especially when you have a large number of tree nodes.
To export all nodes in a tree version, do the following:
Note:
This procedure assumes that the View Tree Version page is open.
You can create a record for a data source "on the fly" and add it to the hierarchy. Doing so calls the custom UI you registered with the data source.
You can create a record either when creating a tree version or when editing an existing one.
To create a new data-source record, do t he following:
Note:
This procedure assumes the following:
You are editing an existing tree version
The tree version does not allow multiple root nodes
The following procedure explains how to duplicate a tree version.
To duplicate a tree version, do the following:
The following procedure explains how to edit a tree version.
To edit a tree version, do the following:
This section describes the APIs that allow you to perform Create, Read, Update, and Delete (CRUD) operations on the Tree Nodes. These APIs do not commit for each CRUD operation, but give you the control to decide whether to commit or not.
The APIs are listed below:
add_value_tree_node
This API allows you to add a primary key value based tree node.
Table 20-4 add_value_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_parent_tree_node_id |
ID of the parent tree node |
VARCHAR2 |
Mandatory |
p_data_source_id |
ID of the data source |
VARCHAR2 |
Mandatory |
p_pk1_value |
Value of PK1 |
VARCHAR2 |
Mandatory |
p_pk2_value |
Value of PK2 |
VARCHAR2 |
Mandatory |
p_pk3_value |
Value of PK3 |
VARCHAR2 |
Mandatory |
p_pk4_value |
Value of PK4 |
VARCHAR2 |
Mandatory |
p_pk5_value |
Value of PK5 |
VARCHAR2 |
Mandatory |
p_tree_label_id |
ID of the tree label |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
x_tree_node_id |
ID of the tree node |
VARCHAR2 |
Optional |
add_range_tree_node
This API allows you to add a primary key range based tree node.
Table 20-5 add_range_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_parent_tree_node_id |
ID of the parent tree node |
VARCHAR2 |
Mandatory |
p_data_source_id |
ID of the data source |
VARCHAR2 |
Mandatory |
p_pk1_start_value |
Start value of PK1 |
VARCHAR2 |
Mandatory |
p_pk2_start_value |
Start value of PK2 |
VARCHAR2 |
Mandatory |
p_pk3_start_value |
Start value of PK3 |
VARCHAR2 |
Mandatory |
p_pk4_start_value |
Start value of PK4 |
VARCHAR2 |
Mandatory |
p_pk5_start_value |
Start value of PK5 |
VARCHAR2 |
Mandatory |
p_pk1_end_value |
End value of PK1 |
VARCHAR2 |
Mandatory |
p_pk2_end_value |
End value of PK2 |
VARCHAR2 |
Mandatory |
p_pk3_end_value |
End value of PK3 |
VARCHAR2 |
Mandatory |
p_pk4_end_value |
End value of PK4 |
VARCHAR2 |
Mandatory |
p_pk5_end_value |
End value of PK5 |
VARCHAR2 |
Mandatory |
p_tree_label_id |
ID of the tree label |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
x_tree_node_id |
ID of the tree node |
VARCHAR2 |
Optional |
add_tree_tree_node
This API allows you to add a reference tree-based tree node.
Table 20-6 add_tree_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_parent_tree_node_id |
ID of the parent tree node |
VARCHAR2 |
Mandatory |
p_reference_tree_code |
Code of the reference tree |
VARCHAR2 |
Mandatory |
p_reference_tree_version_id |
ID of the reference tree version |
VARCHAR2 |
Mandatory |
p_tree_label_id |
ID of the tree label |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
x_tree_node_id |
ID of the tree node |
VARCHAR2 |
Optional |
delete_tree_node
This API allows you to delete a node. All children of the node that is being deleted are promoted as children of its parents node.
Table 20-7 delete_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_tree_node_id |
ID of the tree node |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
update_tree_node
This API allows you to update a tree node.
Table 20-8 update_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_tree_node_id |
ID of the tree node |
VARCHAR2 |
Mandatory |
p_parent_tree_node_id |
ID of the parent tree node. As of now, this API does not allow you to change the parent node. Hence, this parameter is ignored. |
VARCHAR2 |
Mandatory |
p_data_source_id |
ID of the data source |
VARCHAR2 |
Mandatory |
p_pk1_start_value |
Start value of PK1 |
VARCHAR2 |
Mandatory |
p_pk2_start_value |
Start value of PK2 |
VARCHAR2 |
Mandatory |
p_pk3_start_value |
Start value of PK3 |
VARCHAR2 |
Mandatory |
p_pk4_start_value |
Start value of PK4 |
VARCHAR2 |
Mandatory |
p_pk5_start_value |
Start value of PK5 |
VARCHAR2 |
Mandatory |
p_pk1_end_value |
End value of PK1 |
VARCHAR2 |
Mandatory |
p_pk2_end_value |
End value of PK2 |
VARCHAR2 |
Mandatory |
p_pk3_end_value |
End value of PK3 |
VARCHAR2 |
Mandatory |
p_pk4_end_value |
End value of PK4 |
VARCHAR2 |
Mandatory |
p_pk5_end_value |
End value of PK5 |
VARCHAR2 |
Mandatory |
p_reference_tree_code |
Code of the reference tree |
VARCHAR2 |
Mandatory |
p_reference_tree_version_id |
ID of the reference tree version |
VARCHAR2 |
Mandatory |
p_tree_label_id |
ID of the tree label |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
move_tree_node
This API allows you to move a tree node under a new parent.
Table 20-9 move_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_tree_node_id |
ID of the tree node |
VARCHAR2 |
Mandatory |
p_dest_parent_tree_node_id |
ID of the destination parent tree node |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
PL/SQL APIs allow you to move, copy, and remove sub-tree nodes. The following are the PL/SQL APIs that allow you to perform the operations:
move_sub_tree_node
This API allows you to move a given node along with its descendants from one hierarchy version to another hierarchy or within the same hierarchy tree version. The destination hierarchy can belong to same tree code or a different tree code belonging to same tree structure. When move_sub_tree_node operation happens across the hierarchy or within the same hierarchy, flattening logs will be reset to ensure that next run of flattening generates accurate flattening data. A tree node id pointing to details of new subtree in destination hierarchy will be returned.
Table 20-10 move_sub_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_tree_node_id |
ID of the tree node |
VARCHAR2 |
Mandatory |
p_dest_parent_tree_node_id |
ID of the destination parent tree node |
VARCHAR2 |
Mandatory |
p_dest_tree_version_id |
ID of the destination parent tree version |
VARCHAR2 |
Mandatory |
p_dest_tree_code |
Destination tree code |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
x_tree_node_id |
ID of the tree node |
VARCHAR2 |
Optional |
copy_sub_tree_node
This API allows you to copy a given node along with its descendants within the same hierarchy tree version. The tree version must allow duplicates for the copy operation to be successful. The destination hierarchy can belong to same tree code or a different tree code belonging to same tree structure. When copy_sub_tree_node operation happens, flattening logs will be reset to ensure that next run of flattening generates accurate flattening data. A tree node id pointing to details of new subtree in destination hierarchy will be returned.
Table 20-11 copy_sub_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_tree_node_id |
ID of the tree node |
VARCHAR2 |
Mandatory |
p_dest_parent_tree_node_id |
ID of the destination parent tree node |
VARCHAR2 |
Mandatory |
p_dest_tree_version_id |
ID of the destination parent tree version |
VARCHAR2 |
Mandatory |
p_dest_tree_code |
Destination tree code |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
x_tree_node_id |
ID of the tree node |
VARCHAR2 |
Optional |
remove_sub_tree_node
This API allows you to remove a given node along with its descendants from any hierarchy. When remove_sub_tree_node operation happens, all the descendants will be deleted.
Table 20-12 remove_sub_tree_node API Parameters
Parameter | Description | Datatype | Mandatory/Optional |
---|---|---|---|
p_tree_structure_code |
Code of the tree structure |
VARCHAR2 |
Mandatory |
p_tree_code |
Code of the tree |
VARCHAR2 |
Mandatory |
p_tree_version_id |
ID of the tree version |
VARCHAR2 |
Mandatory |
p_tree_node_id |
ID of the tree node |
VARCHAR2 |
Mandatory |
p_commit_flag |
Commit flag |
VARCHAR2 |
Optional Default value is Yes. If commit flag is passed as "N", the control to commit is handed over to the API call. |
Although trees do not have status, tree versions do. You can set tree version status to any one of the following:
Draft
Active
Inactive
To activate a tree version, the tree version's tree structure must already be in Active status.
Setting a tree version's status to Active automatically triggers an audit of that tree structure.
To set the status of a tree version, do the following:
Auditing tree and tree version data verifies that it conforms to all rules and ensures data integrity. Running audits allow you to view audit details and messages, and to correct any validation errors that the audit detects. There are three ways to run an audit:
Run an immediate audit
Schedule an audit
Trigger an audit through a service API
The following table describes what each validator checks for, as well as possible reasons why each validator might fail.
Table 20-13 Validator Descriptions
Validator | Checks for... | Validation may have failed because... | To correct... |
---|---|---|---|
Effective Date Validator |
The effective start and end dates of the tree version should be valid. |
Effective end date is set to a value that is not greater than effective start date. |
Modify the effective start and/or end dates so that effective start date falls before effective end date. |
Root Node Validator |
If Allow Multiple Root Nodes flag on the tree structure has been set to If the flag has been set to |
Allow Multiple Root Nodes flag has been set to |
Modify the tree version so that there is exactly one root node. |
Data Source Max Depth Validator |
For each data source in the tree structure, if the data source is depth-limited, the data in the tree version must adhere to the specified depth limit. This restriction does not apply to data sources that have no depth restriction (depth = -1 means unlimited depth). |
Tree version has data at a depth greater than the specified depth limit on one or more data sources. |
Modify the tree version so that all nodes are at a depth that complies with the data source depth limit. |
Duplicate Node Validator |
If Allow Duplicate Nodes flag on the data source has been set to If the flag has been set to |
Your tree version contains duplicate nodes with the same primary key. |
Remove any duplicate nodes from the tree version. |
Available Node Validator |
All the nodes in the tree version should be valid and available in the underlying data source. |
|
|
Node Relationship Validator |
All nodes should adhere to the relationships mandated by the data sources registered in the tree structure. |
The tree structure has data sources arranged in a parent-child relationship, but the nodes in the tree do not adhere to the same parent-child relationship. For example, if the tree structure has a Project data source with a Task data source as its child, Task nodes should always be under Project nodes in the tree version. This validator will fail if there are instances where a Project node. has been added as a child of a Task node. |
Modify the tree version so that the nodes adhere to the same parent-child relationships as the data sources. |
SetID Restricted Node Validator |
For each data source that has Restrict Tree Node List of Values Based on SetID flag set to This restriction does not apply when the flag is set to |
The data source has Restrict Tree Node List of Values Based on SetID flag set to |
Modify the tree version so that all nodes in the tree have data sources with SetID matching that of the tree. |
Label Enabled Node Validator |
If the tree structure has a Labeling Scheme specified, all nodes should have labels. This restriction does not apply when the Labeling Scheme is set to |
The tree structure has a labeling scheme but the tree version has nodes without labels. |
Assign labels to any nodes that do not have labels. |
Date Restricted Node Validator |
If Restrict Tree Node List of Values Based on Date Range flag on the tree structure has been set to If the flag is set to |
Restrict Tree Node List of Values Based on Date Range flag has been set to For example, if the tree version is effective from Jan-01-2012 to Dec-31-2012, all nodes in the tree version must be effective from Jan-01-2012 to Dec-31-2012 at a minimum. It is acceptable for the nodes to be effective for a date range that exceeds the tree version's effective date range (for example, the node data source value is effective from Dec-01-2011 to Mar-31-2012). It is not acceptable if the nodes are effective for none or only part of the tree version's effective date range (for example, the node data source value are only effective from Jan-01-2012 to June-30-2012). |
Ensure that for all nodes in the tree version, they have date effectivity at least for the effective date range for the tree version. |
Multiple Active Tree Version Validator |
If Allow Multiple Active Tree Versions Flag on the tree structure has been set to If Allow Multiple Active Tree Versions is set to |
Allow Multiple Active Tree Versions has been set to |
Make no more than one tree version Active within the same date range and set the others to Inactive or Draft. |
Range Based Node Validator |
If Allow Range Children on the data source has been set to If the flag is set to |
There are range-based nodes from a data source that has Allow Range Children set to |
Ensure that any range nodes in your tree version are from a data source that has Allow Range Children set to |
Terminal Node Validator |
If Allow Usage as Leaves flag is set to If Allow Usage as Leaves flag is set to |
There are leaf nodes (terminal nodes) whose values come from a data source marked with Allow Usage as Leaves flag set to |
Modify the tree version so that all terminal nodes come from data sources with Allow Usage as Leaves set to |
Usage Limit Validator |
If Usage Limit is set to If Usage Limit is set to |
The data source has Usage Limit set to |
Add nodes to the tree version for each data source value that is not yet present. |
To run an immediate audit, do the following:
Note:
The following procedure assumes the Manage Trees and Tree Versions page is open.
To trigger an audit, through a service API, use the service API shown in the example that follows.
Example 20-1 Audit Service API
/** * Processes the audit scheduled for any tree or tree version. * @param requestId Request ID for scheduled audit (for online can be defaulted as -1) * @param auditType Auditing mode wheere audit invoked for tree/tree version(pass as TREE_AUDIT for tree and * TREE_VERSION_AUDIT for tree version. * @param tsCode Tree Structure Code * @param treeCode Tree Code * @param treeVersionId Tree Version Id */ public void processAudit(Long requestId, String auditType, String tsCode, String treeCode, String treeVersionId)
The tree-flattening process filters an implicit tree structure into a simple sequence of leaves. It coalesces nodes so that each sub-tree has a single cache list representing all of its children at one transformed level.
Figure 20-61 Example of a Flattened Tree Structure
Tables that store flattened data are either row or column flattened. By eliminating recursive queries, row flattening is particularly useful for efficiently performing operations across an entire sub-tree.
Understanding Row Flattening
Row flattening is a technique where parent-child information is optimized for run-time performance by storing additional rows in a table (as compared to just normalized parent-child rows) to instantly find all descendants to a parent value, without initiating a Connect By SQL statement.
Normalized data, for example, might be the following:
Corporation - Sales Division
Sales Division - Region
In a row flattened table, the above rows are still stored but one additional row is added:
Corporation - Region
In addition, additional columns are added to store the "depth" from top parent.
Understanding Column Flattening
Column flattening is a technique where parent-child information is optimized for run-time performance by storing additional column in a table for all parents of a child.
Normalized data, for example, might be the following:
Corporation - Sales Division
Sales Division - Region
Sales Division
In a column-flattened table, the above data is converted to rows and columns.
Table 20-14 Rows and Columns in a Column-Flattened Table
Column 1 | Column 2 | Column 3 |
---|---|---|
Region |
Sales Division |
Corporation |
Usually, the number of levels (possible parents) are pre-defined to a maximum number and may also have additional "dummy" values on levels where real values are missing.
When a label is chosen for a tree structure, the label data source can be either of the following:
A custom, product-team-owned label data source
The generic data label source.
This section describes how to use the Central Labels tab of the trees application launch page to create, edit, and delete values in the generic label data source.
All procedures assume that the Manage Labels summary page is open in your web browser.
If you wish to edit or delete an existing label and it is not currently visible in the results list, you can search for it using the following procedure.
To search for a label:
In the Search area of the page, construct a search using any or all of the following search criteria:
Tree Structure Code
Name
Short Name
Click Search.
All labels matching your search criteria appear in the Results area of the page.
Click Advanced to perform an advanced search by specifying additional options. You also can save your search criteria for future use.
The following procedure explains how to create a new label.
To create a label:
From the Manage Labels summary page, click Create, or choose Create Label from the Actions dropdown menu.
The Create Label page displays An additional field Set Name will appear if it is used by the selected tree structure.
Figure 20-64 Create Label Page
Enter a tree structure code or click the down arrow to select or search for one.
The page refreshes and the Data Source field populates with an appropriate value.
Enter a short name for the label.
Enter a name for the label.
If you wish, enter a description.
If you wish, enter an icon name.
Enter an effective start date, or click the calendar icon to select one.
Enter an effective end date, or click the calendar icon to select one.
Enter a Set Name or click the down arrow to select or search one.
Click Save and Close to create the label.
Click OK to close the Confirmation window.
The Manage Labels summary page displays showing the new label in the Results list.
The following procedure explains how to edit an existing label.
To edit a label:
Select the label you want to edit.
See How to Search for a Label, if the label is not in the current Results list.
From the Manage Labels summary page, click Edit, or choose Edit from the Actions dropdown menu.
The Edit Label page displays.
Figure 20-65 Edit Label Page
Edit the appropriate data.
Do one of the following:
Click Save and Close to save your changes and exit the editing session.
Click Cancel to cancel the operation.
The following procedure explains how to delete an existing label.
To delete a label:
Select the label you want to delete.
See How to Search for a Label, if the label is not in the current Results list.
From the Manage Labels summary page, click Delete, or choose Delete from the Actions dropdown menu.
The warning page displays.
Figure 20-66 Delete Label Warning Page
Do one of the following:
Click No to cancel the operation.
Click Yes to delete the label.
Now that you have worked with tree structures, trees, and tree versions, you can start developing applications in JDeveloper using the Applications Hierarchy component.
The Applications Hierarchy component is denoted by the fnd:hierarchy tag and contains two facets: hierarchy and toolbar. The hierarchy facet holds the af:tree
or af:treeTable
; the toolbar facet can hold action buttons used with items within the tree
or treeTable
.
The following picture shows an example of the Applications Hierarchy component in JDeveloper.
Figure 20-67 Applications Hierarchy Component
You can add any JSF or ADF Faces component to these facets, even with the generated af:tree or af:treeTable. The fnd:hierarchy tag supports the TreeCode and TreeVersionId properties to display specific trees or tree versions.
You can create two types of Hierarchy applications: Tree and Tree Table.
The following section explains how to create a tree application using the Applications Hierarchy component.
Before you begin:
Create an application initialized for use with Oracle Middleware Extensions for Applications. For more information, see Set Up Your Development Environment.
To create a Tree application, do the following:
The following section explains how to create a tree table application using the applications Hierarchy component.
Before you begin:
Create an application initialized for use with Oracle Middleware Extensions for Applications. For more information, see Set Up Your Development Environment.
To create a Tree Table application, do the following:
The Applications Hierarchy component supports six node operations – Add, Create, Duplicate, Edit, Remove, and Delete – which are performed in a standard popup window in the user interface. Since all nodes in a tree come from their data sources, you must create a custom task flow for each operation that requires one, and register it in your data source before you can use it.
Node operations that only manage data in the tree table do not require custom task flows. For example, adding a node affects only the data in the tree table. However, searching for a node requires access to the data source. Subsequently, you must create and register a custom task flow that will enable node searches.
In addition, since removing a node affects only the data in the tree table, you do not need to create and register a custom task flow for this operation.
Related Links
The following document provides more information about the topic discussed in this section:
For more information about task flows, see Getting Started with ADF Task Flows in the Developing Fusion Web Applications with Oracle Application Development Framework.
You use the Property Inspector in Oracle JDeveloper to register custom task flows.
Figure 20-76 Data Source Properties
The full code used to register each custom task flow in the data-source view object is shown in the examples that follow.
Example 20-2 Search Task Flow for the Add Node Operation
<Properties> <SchemaBasedProperties> <fnd:SEARCH_PAGE Value="/WEB-INF/EmpSearch.xml#EmpSearchTF"/> ... ...
Example 20-3 Create Task Flow
<Properties> <SchemaBasedProperties> <fnd:CREATE_PAGE Value="/WEB-INF/CreateEmpNode.xml#CreateEmpNode"/> ... ...
Example 20-4 Duplicate Task Flow
<Properties> <SchemaBasedProperties> <fnd:DUPLICATE_PAGE Value="/WEB-INF/DupEmpNode.xml#DupEmpNode"/> ... ...
Example 20-5 Edit Task Flow
<Properties> <SchemaBasedProperties> <fnd:UPDATE_PAGE Value="/WEB-INF/EditEmpNode.xml#EditEmpNode"/> ... ...
Example 20-6 Delete Task Flow
<Properties> <SchemaBasedProperties> <fnd:DELETE_PAGE Value="/WEB-INF/DelEmpNode.xml#DelEmpNode"/> ... ...
This section discusses how to create custom task flows for the Search, Create, Duplicate, Edit, and Delete node operations.
The Search task flow provides a shortcut to select nodes. In the task flow, you specify the Search page fragment, task-flow parameters, back-end Java bean, and task-flow activities.
To create the Search task flow, do the following:
The Create task flow is used to create a new node in the data-source table. In the task flow, you specify the Create page fragment, task-flow parameters, back-end Java bean, and task-flow activities.
To create the Create task flow, do the following:
The Duplicate task flow is used to duplicate a node in the data-source table. In the task flow, you specify the Duplicate page fragment, task-flow parameters, back-end Java bean, and task-flow activities.
To create a Duplicate task flow, do the following:
The Edit task flow is used to edit an existing node in the data-source table. In the task flow, you specify the Edit page fragment, task-flow parameters, back-end Java bean, and task-flow activities.
To create the Edit task flow, do the following:
Using the Hierarchy component to develop applications in JDeveloper requires you to specify a tree structure in the Property Inspector.
To access the Hierarchy-Property Inspector, highlight fnd:hierarchy in the Structure window and select the Hierarchy-Property Inspector tab.
Figure 20-77 Hierarchy-Property Inspector
The Hierarchy component has facets and properties listed in the next tables.
Table 20-15 Hierarchy Facets
Facet | Description | Values |
---|---|---|
hierarchy |
Holds ADF Tree or TreeTable |
af:tree or af:treeTable |
toolbar |
Additional toolbar buttons to be added for custom use |
ADF command toolbar buttons under an ADF toolbar |
Table 20-16 Hierarchy Properties
Property | Description | Values |
---|---|---|
id |
Unique identification number for hierarchy |
string |
rendered |
Indicates if the hierarchy is rendered |
boolean |
readOnly |
Indicates if the hierarchy will render in read-only mode |
boolean |
treeStructureCode |
Code for the tree structure to be used for the hierarchy |
string |
tree Code |
Code for the tree to be used for the hierarchy |
string |
treeVersionId |
ID for the tree version to be used for the hierarchy |
string |
actionsMenuRendered |
Controls if action menu needs to be rendered |
boolean |
toolbarRendered |
Controls if toolbar needs to be rendered |
boolean |
addVisible |
Controls if add action is visible |
boolean |
addRendered |
Controls if add action is rendered |
boolean |
addDisabled |
Controls if add action is disabled |
boolean |
addText |
Custom text to be used for add action |
string |
createVisible |
Controls if create action is visible |
boolean |
createRendered |
Controls if create action is rendered |
boolean |
createDisabled |
Controls if create action is disabled |
boolean |
createText |
Custom text to be used for create action |
string |
duplicateVisible |
Controls if duplicate action is visible |
boolean |
duplicateRendered |
Controls if duplicate action is rendered |
boolean |
duplicateDisabled |
Controls if duplicate action is disabled |
boolean |
duplicateText |
Custom text to be used for duplicate action |
string |
editVisible |
Controls if edit action is visible |
boolean |
editRendered |
Controls if edit action is rendered |
boolean |
editDisabled |
Controls if edit action is disabled |
boolean |
editText |
Custom text to be used for edit action |
string |
removeVisible |
Controls if remove action is visible |
boolean |
removeRendered |
Controls if remove action is rendered |
boolean |
removeDisabled |
Controls if remove action is disabled |
boolean |
removeText |
Custom text to be used for remove action |
string |
deleteVisible |
Controls if delete action is visible |
boolean |
deleteRendered |
Controls if delete action is rendered |
boolean |
deleteDisabled |
Controls if delete action is disabled |
boolean |
deleteText |
Custom text to be used for delete action |
string |
registerTaskflow |
Specifies whether to use task flow ( For example: <fnd:hierarchy registerTaskflow="true" ... </fnd:hierarchy> |
boolean |
Note:
Since only customers create tree versions, you must use service APIs to generate lists of tree versions or active tree versions.
The data that displays in your application depends on the tree structure you specify in the Property Inspector. The tree structure automatically determines the following at run time:
The tree available under this particular tree structure. If there are multiple trees, the first one is chosen.
The active tree version (for the current date) available under the tree. If there are multiple tree versions, the first one is chosen.
You also can use the Expression Builder to bind some of the properties mentioned in the previous section. They are the following:
TreeCode
TreeStructureCode
TreeVersionId
Use the following expressions:
TreeCode expression:
#{HierarchyHandler.treeModelsList[hierarchyId].treeCode}
TreeStructureCode expression:
#{HierarchyHandler.treeModelsList[hierarchyId].treeStructureCode}
TreeVersionId expression:
#{HierarchyHandler.treeModelsList[hierarchyId].treeVersionId}
Note:
The hierarchyId
variable is the ID of the Hierarchy component.
Tree Picker is a reusable Oracle ADF task flow, similar to a date picker, that enables you to select tree data from a list of values. It is found in the Trees-View.jar section of the Component Palette.
To add a Tree Picker component to your user interface, do the following:
The Tree Picker returns a list of TreeNodes:
If the Tree Picker is launched as a popup window, the return value can be obtained in the returnListener of the icon or button using:
List<TreeNode> = selectedTreeNodes(List<TreeNode>)event.getReturnValue();
If the Tree Picker is launched in place, the return value can be obtained from the pageFlowScope using:
Map pageFlow = AdfFacesContext.getCurrentInstance().getPageFlowScope();List<TreeNode> selectedNodes = (List<TreeNode>) pageFlow.get("return TreeNodes");
The following picture shows an example of a Tree Picker user interface.
Figure 20-78 Example Tree Picker
The following picture shows the results window that displays when you enter a tree-structure-code value and click Select Tree Node.
Figure 20-79 Tree Picker Results Window
Every data source requires a view object. If the data source view object has bind variables and view criteria that tree management needs to apply, you must set them manually in JDeveloper.
An application programming interface, or API, is a source code interface that a library provides to support requests for services to be made of it by computer programs. In other words, APIs provide the building blocks that make it easier to develop these programs. Although an API specifies an interface and the behavior of the identifiers specified in that interface, it does not specify how the behavior might be implemented.
There are three service application modules that you can use to interact with the tree management infrastructure:
TreeStructureService
TreeService
TreeNodeService.
Note:
No Service Data Objects (SDOs) are provided and these application modules must be instantiated and invoked in a co-located mode.
The TreeStructureService application module is defined in oracle.apps.fnd.applcore.trees.service.applicationModule.TreeStructureService
and allows access to tree structure metadata. This application module exposes the TreeStructureVO under the name "TreeStructure" as well as the hierarchy of ADF Business Components objects accessible through the TreeStructureVO. This application module does not include any of the tree or tree version entities. The Javadoc for the available APIs is included with JDeveloper. To access the Javadoc in JDeveloper, do the following:
This application module is considered a public API to work with tree structure metadata and exposes the APIs shown in the following table.
Table 20-17 TreeStructureService APIs
API | Description |
---|---|
getTreeStructure |
This API is used to retrieve the FndTreeStructureVORow corresponding to a particular tree structure code. |
getRootDataSourceRels |
This API gets a row iterator over FndTsDataSourceRelVORow rows representing the root data sources of the given tree structure. |
getAllTreeColumns |
This API returns a list of VO attributes that are available for use from the various data sources associated with the tree structure. This is a cumulative list across all the data sources. |
getAllDataSources |
Returns a row iterator over FndTsDataSourceVORow rows corresponding to all data sources for the given tree structure. |
getTreeNodeTable |
Returns the name of the tree node table in use by a given tree structure. |
duplicateTreeStructure |
This API is a Java front end to the PL/SQL API FND_TREE_UTILS.duplicate_tree_structure and is used to duplicate a tree structure. It does not duplicate any underlying trees or tree versions associated with the tree structure. |
deleteTreeStructure |
This API is a Java front end to the PL/SQL API FND_TREE_UTILS.delete_tree_structure and is used to delete a tree structure. This deletes all underlying trees and tree versions associated with this tree structure (including flattened data, if any). |
The TreeService application module is defined in oracle.apps.fnd.applcore.trees.service.applicationModule.TreeService
and provides access to trees and tree versions. TreeService also provides flattening APIs. The Javadoc for the available APIs is included with JDeveloper.
To access the Javadoc, do the following:
This application module is considered a public API to work with trees and tree versions and exposes the APIs shown in the following table.
Table 20-18 TreeService APIs
API | Description | Optional Parameters |
---|---|---|
getTreeRows |
This API returns all trees associated with a given tree structure. |
|
getTreeCodes |
This API returns a list of tree codes associated with a given tree structure. |
|
findTree |
This API is used to find a specific tree given its tree structure code and tree code. |
|
createTree |
This API is used to create a tree for a given tree structure.
This API is used to create a tree for a given tree structure. |
treeDescription |
updateTree |
This API is used to update details of a tree for a given tree structure. |
|
updateTreeDataSourceParamValues |
This API is used to update the value of the data source parameters for a given tree. The parameter values customized at the tree level will override the default values specified at the tree-structure level. |
|
duplicateTree |
This API duplicates a specific tree and assigns a specified tree code to the duplicate. It is a front end to the FND_TREE_UTILS.duplicate_tree PL/SQL API. |
|
deleteTree |
This API deletes a tree, all its associated tree versions, including flattened data. It is a front end to the FND_TREE_UTILS.delete_tree PL/SQL API. |
|
getAllTreeVersions |
This API returns a list of all tree versions associated with a given tree. |
|
getTreeVersions |
This API returns a list of tree versions associated with a given tree as of a particular date. |
|
getCurrentTreeVersions |
This API returns a list of tree versions associated with a given tree as of the current date. |
|
findTreeVersion |
This API is used to locate a specific tree version given its tree structure code, tree code and tree version ID. |
|
createTreeVersion |
This API creates a tree version and returns the ID of the created tree version. All parameters are mandatory.
This API creates a tree version and returns the ID of the created tree version. The default values for all these parameters is NULL. The default for the effectiveEndDate parameter is 31-DEC-4712. |
treeVersionDescription, effectiveEndDate, treeVersionNote |
updateTreeVersion |
This API updates the specified tree version and returns the ID of the updated tree version for a given tree structure and tree code. |
effectiveEndDate updatedTreeVersionName, updatedTreeVersionDescription, updatedEffectiveStartDate, updatedEffectiveEndDate, updatedTreeVersionNote |
duplicateTreeVersion |
This API is a front end to the PL/SQL API FND_TREE_UTILS.duplicate_tree_version and duplicates a specific tree version. The API returns the auto-generated ID of the duplicate tree version. |
|
deleteTreeVersion |
This API is a front end to the PL/SQL API FND_TREE_UTILS.delete_tree_version and deletes a tree version including its flattened data (if any). |
|
rowFlatten |
This API row-flattens a specific tree version. |
|
columnFlatten |
This API column-flattens a specific tree version. |
The TreeNodeService application module is defined in oracle.apps.fnd.applcore.trees.service.applicationModule.TreeNodeService
and provides the core node operations such as adding and deleting nodes. The APIs support three types of nodes:
value nodes
range nodes
tree-in-tree nodes.
The Java APIs are covers to the PL/SQL APIs that are provided in the FND_TREE_UTILS PL/SQL package.
To access the Javadoc, do the following:
This application module is considered a public API to work with tree nodes and exposes the APIs shown in the next table.
Table 20-19 TreeNodeService APIs
API | Description |
---|---|
addValueTreeNode |
This API adds a value-based tree node to a specific tree version. It is a front end to the PL/SQL API FND_TREE_UTILS.add_value_tree_node. It returns the tree node ID of the newly added node. The API has two signatures - one that takes in a tree label to be associated with the tree node and one that does not. |
addRangeTreeNode |
This API adds a range-based tree node to a specific tree version. It is a front end to the PL/SQL API FND_TREE_UTILS.add_range_tree_node. It returns the tree node ID of the newly added node. The API has two signatures - one that takes in a tree label to be associated with the tree node and one that does not. |
addTreeTreeNode |
This API adds a tree node that references another tree version. It is a front end to the PL/SQL API FND_TREE_UTILS.add_tree_tree_node. It returns the tree node ID of the newly added node. The API has two signatures - one that takes in a tree label to be associated with the tree node and one that does not. |
deleteTreeNode |
This API is a front end to the PL/SQL API FND_TREE_UTILS.delete_tree_node and deletes a specific tree node. Any children of that node are automatically promoted up the hierarchy. |
updateTreeNode |
This API is a front end to the PL/SQL API FND_TREE_UTILS.update_tree_node and is used to update the data associated with a specific tree node. It cannot be used to move the tree node. |
moveTreeNode |
This API is used to move a tree node within the hierarchy. The entire sub-tree rooted at the node being moved is moved. This API is a front end to the PL/SQL API FND_TREE_UTILS.move_tree_node. |
moveSubTree |
This API moves a sub-tree under the specified parent tree node within the same tree or across trees and returns the ID of the tree node that has been moved. |
copySubTree |
This API copies a sub tree under specified parent Tree Node within the same tree or across trees and returns the ID of the tree node that has been copied. |
removeSubTree |
This API removes sub tree under the specified parent tree node. |
findValueTreeNodes |
This API is used to find all value tree nodes with the specified primary key. |
findRangeTreeNodes |
This API is used to find all range tree nodes with the specified range. |
findRefTreeNodes |
This API is used to find all tree nodes that reference the specified tree version. |
This section includes information about the following advanced topics:
Tree data model
PL/SQL APIs
Web services APIs
Incremental flattening
Oracle Enterprise Scheduler Service for auditing and flattening trees
Trees business events
WebLogic Scripting Tool (WLST) commands for flattening
XML report formats for WLST commands
Purging unused tree logs
The following are new or modified tables and views that are used by and relevant to the Tree Management infrastructure. They are set up in the FUSION schema.
Tables:
FND_TREE_STRUCTURE
FND_TREE_STRUCTURE_TL
FND_TS_DATA_SOURCE
FND_TS_DATA_SOURCE_REL
FND_TS_DATA_SOURCE_PARAMS
FND_LABEL
FND_LABEL_TL
FND_TREE
FND_TREE_TL
FND_TREE_DATA_SOURCE_PARAMS
FND_TREE_VERSION
FND_TREE_VERSION_TL
FND_NODE
FND_NODE_TL
FND_TREE_LABEL
FND_TREE_NODE
FND_TREE_NODE_RF
FND_TREE_NODE_CF
FND_TREE_AUDIT_JOB
FND_TREE_VERSION_AUDIT_RES
FND_TREE_VERSION_AUDIT_RES_TL
FND_TREE_LOG
FND_TREE_LOG_PARAMS
FND_TREE_FLATTENING_HISTORY
Views:
FND_TREE_STRUCTURE_VL
FND_LABEL_VL
FND_TREE_VL
FND_TREE_VERSION_VL
FND_NODE_VL
FND_TREE_VERSION_AUDIT_RES_VL
Tree Management provides public PL/SQL APIs to work with trees. You can find these APIs in the PL/SQL package FND_TREE_UTILS in the FUSION schema.
Note:
The PL/SQL package FND_TREE_UTILS_PVT contains private APIs for internal use with Oracle Fusion tree management. No other use of these APIs is supported.
Oracle Fusion Applications uses the trees framework for modeling several hierarchies, and for managing tree structures, trees, and tree versions. In addition to APIs, hierarchical components, and task flows, the framework exposes web services to accomplish integration.
The following tables include web services APIs for TreeService and TreeNodeService.
Table 20-20 Web Services APIs for TreeService
Operation | API |
---|---|
Create tree |
Creates a tree. |
Update tree |
Updates a tree. |
Update tree data source parameter values |
Updates the value of the data source parameter for a given tree. Parameter values customized at the tree level will override the default values specified at the tree-structure level. |
Duplicate tree |
Duplicates a tree without duplicating the underlying tree versions. |
Delete tree |
Deletes a tree. |
Create tree version |
Creates a tree version and returns a string with the ID of the tree version that has been created. |
Update tree version |
Updates a tree version. |
Duplicate tree version |
Duplicates a tree version. |
Delete tree version |
Deletes the specified tree version. |
Row flatten tree version |
Row flattens a tree version. |
Column flatten tree version |
Column flattens a tree version. |
Table 20-21 Web Services APIs for TreeNodeService
Operation | API |
---|---|
Add tree value node |
Adds a value-based tree node and returns the ID of the tree node that was added. |
Add tree range node |
Adds a range-based tree node and returns the ID of the tree node that was added. |
Add reference tree node |
Adds a reference tree node and returns the ID of the tree node that was added. |
Delete tree node |
Deletes a tree node. |
Move tree node |
Moves a tree node under specified parent tree node. |
Update tree node |
Updates an existing tree node. |
Move sub-tree |
Moves a sub-tree under specified parent tree node within the same tree or across trees. |
Copy sub-tree |
Copies a sub-tree under specified parent tree node within the same tree or across trees. |
Remove sub-tree |
Removes the sub-tree under the specified parent tree node. |
Incremental flattening optimizes the process by starting with the results of a previous flattening instead of flattening the data from scratch.
Figure 20-81 Flattening Delta
To flatten incrementally, a delta of flattening operations that occurred between these two sets of start and end points is created, and information about what happened during those operations is stored in three tables:
FND_TREE_FLATTENING_HISTORY
FND_TREE_LOG
FND_TREE_LOG_PARAMS
These tables are described in the sections that follow.
This table records the flattening history for a specific tree version. For optimization, only the last process point is recorded. Process_Point records the time of the last tree-node operation that has been flattened.
The following table shows the contents of the FND_TREE_FLATENNING_HISTORY table.
Table 20-22 FND_TREE_FLATTENING_HISTORY
Column | Data Type | Nullable? |
---|---|---|
Tree_Structure_Code (Primary Key) |
Varchar2(30) |
No |
Tree_Code (Primary Key) |
Varchar2(30) |
No |
Tree_Version_ID (Primary Key) |
Varchar2(32) |
No |
Process_Point |
Timestamp(6) |
No |
Flattening_Type (Primary Key) |
Varchar2(32) |
No |
Created_By |
Varchar2(64) |
No |
Creation_Date |
Timestamp(6) |
No |
Last_Updated_By |
Varchar2(64) |
No |
Last_Update_Date |
Timestamp(6) |
No |
Last_Update_Login |
Varchar2(32) |
Yes |
FND_TREE_LOG is a log of all flattening operations for one tree version. The log enables database administrators (DBAs) to move data easily and efficiently to external systems such as a data warehouse, or from test to production systems.
FND_TREE_LOG stores tree-node operations. For each specific tree-version operation, a unique Log_Entry_ID is assigned and the operation type is logged.
There are three types of tree nodes:
value
range
tree node
There also are three types of tree-node operations:
add
move
delete
Subsequently, there are nine types of operations:
add value node
move value node
delete value node
add range node
move range node
delete range node
add tree node
move tree node
delete tree node
The following table shows the contents of the FND_TREE_LOG table.
Table 20-23 FND_TREE_LOG
Column | Data Type | Nullable? |
---|---|---|
Log_Entry_ID (Primary Key) |
Varchar2(32) |
No |
Tree_Structure_Code |
Varchar2(30) |
No |
Tree_Code |
Varchar2(30) |
No |
Tree_Version_ID |
Varchar2(32) |
No |
Operation_Type |
Varchar1(32) |
No |
Created_By |
Varchar2(64) |
No |
Creation_Date |
Timestamp(6) |
No |
Last_Updated_By |
Varchar2(64) |
No |
Last_Update_Date |
Timestamp(6) |
No |
Last_Update_Login |
Varchar2(32) |
Yes |
Since the FND_TREE_LOG table does not record parameters for each operation, the FND_TREE_LOG_PARAMS table is used to log them. The two tables are referenced by a foreign key, Log_Entry_ID. This design helps save space and clearly organizes the information.
The following table shows the contents of the FND_TREE_LOG_PARAMS table.
Table 20-24 FND_TREE_LOG_PARAMS
Column | Data Type | Nullable? |
---|---|---|
Log_Entry_ID (Primary Key) |
Varchar2(32) |
No |
Param_Name (Primary Key) |
Varchar2(64) |
No |
Param_Value |
Varchar2(100) |
No |
Created_By |
Varchar2(64) |
No |
Creation_Date |
Timestamp(6) |
No |
Last_Updated_By |
Varchar2(64) |
No |
Last_Udpate_Date |
Timestamp(6) |
No |
Last_Update_Login |
Varchar2(32) |
Yes |
Row-flattening results are stored in the table registered as the row-flattening table for the tree structure. If you register a custom row-flattening table for your tree structure, ensure it has the same schema as FND_TREE_NODE_RF.
IS_LEAF and DISTANCE are two important row-flattening-table columns. For more information, see the following sections.
This column provides information about whether or not a tree node is a leaf. In many instances, only a leaf contain meaningful information, while other nodes provide a structural purpose. IS_LEAF makes it easier to differentiate a leaf from other nodes, and makes it simpler to write simple queries and get faster responses. Valid values are Y (yes) and N (no).
This column indicates the distance between the node and its ancestor, which is specified in the row. For example, the distance between a node and its parent or children is 1, between a node and its grandparent or grandchildren is 2, and so on. DISTANCE, then, helps developers to get the entire path - from the root node to the intermediate leaf/node without having to perform any additional queries.
In the FND_TREE_NODE table, DISTANCE is stored in the form of an adjacency list, as shown in the next table.
Figure 20-82 Example of DISTANCE
Table 20-25 Adjacency List
Node | Parent |
---|---|
A |
Null |
B |
A |
C |
A |
D |
C |
After flattening, DISTANCE is stored in the FND_TREE_NODE table.
Note:
The node ancestor also includes itself.
Table 20-26 Flattened FND_TREE_NODE Table
Node | Ancestor | Distance | IS_LEAF? |
---|---|---|---|
A |
Null |
1 |
N |
A |
A |
0 |
N |
B |
Null |
2 |
Y |
B |
A |
1 |
Y |
B |
B |
0 |
Y |
C |
Null |
2 |
N |
C |
A |
1 |
N |
C |
C |
0 |
N |
D |
Null |
3 |
Y |
D |
A |
2 |
Y |
D |
C |
1 |
Y |
D |
D |
0 |
Y |
To find the path from the root of D, the query would be the following:
select * from fnd_tree_node_rf where tree_node_id = D order by distance
Column flattening generally applies only to level-based trees. In the case of a view object for Business Intelligence (BICVO), however, value-based trees also can be column-flattened. (For more information, see Designing and Securing View Objects for .)
For level-based trees, the maximum level of a tree version is 32. Subsequently, if a tree version is not level-based or if it has a tree-version level that exceeds 32, only row flattening can be performed.
Column-flattening results are stored in the table specified in the tree structure, which has the same schema as FND_TREE_NODE_CF.
Each row in the FND_TREE_NODE_CF table represents a path in a tree, and can hold a maximum of 32 nodes. The rows are arranged from leaf to root.
Figure 20-83 Leaf-to-Root Order
The following table shows the results after flattening.
Table 20-27 Column-Flattening Results
Dep0 | Dep1 | Dep2 | Dep3 | Dep4 | ... | Dep31 |
---|---|---|---|---|---|---|
LA |
CA |
USA |
North America |
Null |
... |
Null |
SF |
CA |
USA |
North America |
Null |
... |
Null |
DC |
Null |
USA |
North America |
Null |
... |
Null |
Note:
All fields not containing nodes will be filled with Null.
For trees with thousands of nodes, the auditing and flattening process for tree versions can take a very long time. To shorten the process, you can submit Oracle Enterprise Scheduler Service jobs using the Oracle Fusion Middleware Extensions for Applications (Applications Core) Setup UI.
From the UI, you can select any tree version on the fly and choose to do the auditing and flattening either "Online" or "Offline". The "Online" option triggers the process in synchronous mode. The "Offline" option allows you to audit or flatten without having to write additional code or create a custom job. It submits a PL/SQL API call that invokes the trees Oracle Enterprise Scheduler Service program for performing the flattening or auditing based on selected options.
Online/Offline Options
Online/offline options are available on the Manage Tree and Tree Versions page. The figures that follow show the Online/Offline options and confirmation dialog in the Applications Core Setup UI.
Figure 20-84 Trees Auditing: Online/Offline Options
Figure 20-85 Trees Flattening: Online/Offline Options
Figure 20-86 Offline Job-Submission Confirmation
Configuring Oracle Enterprise Scheduler Service to audit and flatten trees involves the following tasks:
Do the following:
Ensure that you have a hosting Oracle Enterprise Scheduler Service application for carrying the trees jobs. For example, for Oracle Fusion Human Capital Management, it is the HcmESS application; for Oracle Fusion Financials, it is the FinancialESS application, which is being centrally hosted.
Ensure that the Setup application consuming the trees taskflow has been configured properly for Oracle Enterprise Scheduler Service so that it can refer the Central Oracle Enterprise Scheduler Service application.
Prior to uptaking any trees jobs, install Oracle Enterprise Scheduler Service and validate that you are able to deploy the two applications referenced in the first bullet item.
There are two .xml
files for trees jobs: one for auditing and one for flattening. Example 20-7 and Example 20-8 show sample jobs.
Do the following to audit and flatten jobs:
$BUILD_HOME/jdeveloper/oaext/applcore/trees/Trees-SampleJob.xml
to some writeable local file.AuditJob.xml
, found in essmeta/oracle/apps/ess/fnd/applcore/trees/Jobs
and set <parameter name="SYS_effectiveApplication" data-type="string">EarFinLedgerEss</parameter> and <parameter name="SYS_product" data-type="string">EarFinLedgerEss</parameter> to reflect the correct application name for your Central Oracle Enterprise Scheduler Service application. For example, for Oracle Human Capital Management it can be set as something like HCMEss, and Oracle Financials can be set to FinancialEss per their Central Oracle Enterprise Scheduler Service hosting application name.FlattenJob.xml
, which also can be found in essmeta/oracle/apps/ess/fnd/applcore/trees/Jobs
.essmeta/oracle/apps/ess/fnd/applcore/trees/Jobs
to your hosting Oracle Enterprise Scheduler Service application.Example 20-7 Sample Audit Job
<?xml version = '1.0'?> <job-definition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="AuditJob" job-type="/oracle/as/ess/ext/JobType/JavaJobType.xml" xmlns="http://xmlns.oracle.com/scheduler"> <display-name>AuditJob</display-name><description>Audit for Trees and Tree Versions</description> <parameter-list><parameter name="SYS_product" data-type="string">EarFinLedgerEss</parameter> <parameter name="SYS_className" data-type="string">oracle.apps.fnd.applcore. trees.ess.program.AuditCP</parameter> <parameter name="SYS_effectiveApplication" data-type="string">EarFinLedgerEss </parameter> </parameter-list></job-definition>
Example 20-8 Sample Flatten Job
<?xml version = '1.0'?> <job-definition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="FlattenJob" job-type="/oracle/as/ess/ext/JobType/JavaJobType.xml" xmlns="http://xmlns.oracle.com/scheduler"> <display-name>FlattenJob</display-name><description>Flattening for Trees and Tree Versions</description> <parameter-list><parameter name="SYS_product" data-type="string">EarFinLedgerEss</parameter> <parameter name="SYS_className" data-type="string">oracle.apps.fnd.applcore. trees.ess.program.Flattening</parameter> <parameter name="SYS_effectiveApplication" data-type="string">EarFinLedgerEss </parameter> </parameter-list></job-definition>
FlattenCP.java
is the concurrent program provided for flattening Oracle Enterprise Scheduler Service jobs. To support various flattening requirements, the program accepts the parameters in the following table as part of Oracle Enterprise Scheduler Service request.
Table 20-28 Parameters for the FlattenCP.java Program
Parameter | Usage |
---|---|
TreeStructureCode |
Mandatory parameter to pass the tree structure code value of the hierarchy for which flattening needs to be scheduled |
TreeCode |
Mandatory parameter to pass the tree code value of the hierarchy for which flattening needs to be scheduled |
TreeVersionId |
Mandatory parameter to pass the tree version ID value of the hierarchy for which flattening needs to be scheduled |
FlatteningMode |
Mandatory parameter to pass the type of flattening requested. Accepted values are the following:
|
Force |
Mandatory parameter to indicate if force flattening is requested. (Values: |
For your Central hosting Oracle Enterprise Scheduler Service application, open weblogic-application.xml
and ensure that you have referenced the Oracle Fusion Middleware Extensions for Applications / Oracle Enterprise Scheduler Service library.
<library-ref> <library-name>oracle.applcore.ess</library-name> </library-ref>
Note:
It is assumed that all trees being audited through the Central Oracle Enterprise Scheduler Service hosting application have access to the .jar
files that contain the related view objects for the data source. Otherwise, you will encounter "class not found" exceptions during audit or flattening.
It is necessary to add the metadata security permission for the two jobs you packaged in your hosted Oracle Enterprise Scheduler Service application.
Add jazn-data.xml
entries, like the ones shown below, for these trees jobs.
<permission> <class>oracle.as.scheduler.security.MetadataPermission</class> <name>oracle.apps.ess.fnd.applcore.trees.JobDefinition.FlattenJob</name> <actions>READ,UPDATE,DELETE,EXECUTE</actions> </permission> <permission> <class>oracle.as.scheduler.security.MetadataPermission</class> <name>oracle.apps.ess.fnd.applcore.trees.JobDefinition.AuditJob</name> <actions>READ,UPDATE,DELETE,EXECUTE</actions> </permission>
You now should be able to launch the Trees Summary page and perform the following operations:
Schedule an audit for a tree or tree version and view the results of the audit.
Activate a tree version by setting its status to "Active." (If an audit is needed, the Audit page will open; otherwise, the tree version will be activated.)
Schedule flattening for rows and columns.
You can schedule flattening for rows and columns in both incremental and force modes, as shown in the following figure.
Figure 20-87 Schedule Force Flattening Option
A business event typically is a one-way, fire-and-forget, asynchronous way to send a notification of a business occurrence. You can raise business events when a situation of interest occurs. The Tree Management infrastructure provides create, update, and delete business events on tree structures, trees and tree versions. The event definitions are available in $MW_HOME/jdeveloper/jdev/oaext/events/Trees-Model-Events.jar
.
The following table includes details of the create, update, and delete events.
Table 20-29 Trees Business Events
Entity | Event Name | Condition | Payload |
---|---|---|---|
Tree Structure |
TreeStructureCreateEvent |
Create |
TreeStructureCode |
Tree Structure |
TreeStructureUpdateEvent |
Update |
TreeStructureCode |
Tree Structure |
TreeStructureDeleteEvent |
Delete |
TreeStructureCode |
Tree |
TreeCreateEvent |
Create |
TreeStructureCode, TreeCode |
Tree |
TreeUpdateEvent |
Update |
TreeStructureCode, TreeCode |
Tree |
TreeDeleteEvent |
Delete |
TreeStructureCode, TreeCode |
Tree Version |
TreeVersionCreateEvent |
Create |
TreeStructureCode, TreeCode, TreeVersionId |
Tree Version |
TreeVersionUpdateEvent |
Update |
TreeStructureCode, TreeCode, TreeVersionId |
Tree Version |
TreeVersionDeleteEvent |
Delete |
TreeStructureCode, TreeCode, TreeVersionId |
Tree Node |
TreeNode Created |
New node added to tree (includes value, range and referenced tree nodes) |
TreeStructureCode, TreeCode, TreeVersionId, TreeNodeId |
Tree Node |
TreeNode Deleted |
Node removed from tree (includes value, range and referenced tree nodes) |
TreeStructureCode, TreeCode, TreeVersionId, TreeNodeId |
Tree Node |
TreeNode Updated |
Node updated in tree (includes value, range and referenced tree nodes) |
TreeStructureCode, TreeCode, TreeVersionId, TreeNodeId |
Tree Node |
TreeNode Moved |
Node moved within tree (includes value, range and referenced tree nodes) |
TreeStructureCode, TreeCode, TreeVersionId, TreeNodeId |
Related Links
The following document provides more information about the topic discussed in this section:
For more information, see Using Business Events and the Event Delivery Network in Developing SOA Applications with Oracle SOA Suite.
WebLogic Scripting Tool (WLST) is a command line scripting interface that you can use to create, manage, and monitor WebLogic Server domains.
Tree management provides the data flattening feature that improves query performance against the hierarchical data. It supports two types of flattening: column flattening and row flattening. Column flattening optimizes the parent-child information for run-time performance by storing additional column in a table for all parents of a child. Row flattening optimizes parent-child information for run-time performance by storing additional rows in a table (as compared to just normalized parent-child rows) to instantly find all descendants to a parent value, without initiating a CONNECT BY SQL statement.
WLST commands are to be executed from the wlst
prompt. Execute the shell script located at $JDEV_HOME/oracle_common/common/bin
to navigate to the wlst
prompt.
cd $JDEV_HOME/oracle_common/common/bin/ sh wlst.sh
The treeManagement.py
Python Script defines the WLST commands that can be executed at the WLST prompt. Each command corresponds to a method in the python script. The python script is available at $ADE_VIEW_ROOT/atgpf/applcore/etc/wlst/
.
A package oracle.apps.fnd.applcore.trees.mbean
is added to the Trees ViewController Layer. This package includes the following files:
Table 20-30 Files in the oracle.apps.fnd.applcore.trees.mbean Package
File | Description |
---|---|
|
Manages registration and de-registration of Trees MBean |
|
Defines the signature and implements the java based code logic for all WLST commands |
|
Bean Data Structure for the object that stores flattening results for all individual Tree Versions |
|
Outlines the design and generates the output XML Report for the WLST commands |
|
Serves as the supporting utility and resource files |
The defined contextual parameter in TreeDeployMBeanLifeCycleCallBack.java
and the class need to be registered as a listener in the web.xml
file of the Fnd Setup application.
Contextual Parameter:
<context-param> <param-name>oracle.apps.fnd.internal.trees.mbean.ENABLED</param-name> <param-value>true</param-value> </context-param>
Listener Class:
<listener> <listener-class> oracle.apps.fnd.applcore.trees.runtime.TreeFlatteningMBeanLifeCycleCallBack </listener-class> </listener>
Ensure the following before you execute the WLST commands for flattening:
Integrated WebLogic Server is up and running.
Connection is established to the WebLogic Server.
Connection is established to the Trees MBean package.
Each WLST command is defined.
The MBean package exposes the following set of APIs:
flattenAll
flattenTreeStructure
flattenTree
flattenTreeVersion
flattenTreeVersionAsync
flattenTreeVersionId
forceFlattenTreeVersion
auditTreeVersion
auditTreeVersionAsync
The flattening APIs exposed by the MBean package can be invoked in two ways:
flattenAll
APIThis API performs incremental flattening of all active Tree Versions under all the available Tree Structures in the application.
Note:
Only active Tree Versions are flattened.
The following table describes the parameters of this API:
Table 20-31 flattenAll API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
type |
String |
Type of flattening to be performed. It can be COLUMN or ROW flattening. By default, this API triggers both types of flattening |
Optional |
The following is an example of this API:
Example 20-9 flattenAll
API Example
flattenAll(type='COLUMN')
flattenTreeStructure
APIThis API performs incremental flattening for all active hierarchies for a given Tree Structure.
Note:
Only active Tree Structures are flattened.
The following table describes the parameters of this API:
Table 20-32 flattenTreeStructure API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
treeStructureCode |
String |
Code of the tree structure on which flattening needs to be performed |
Mandatory |
type |
String |
Type of flattening to be performed. It can be COLUMN or ROW flattening. By default, this API triggers both types of flattening |
Optional |
The following is an example of this API:
Example 20-10 flattenTreeStructure API Example
flattenTreeStructure(treeStructureCode='FND_DEMO_EMP_TS')
flattenTree
APIThis API performs incremental flattening for all active Tree Versions for the specified Tree Code.
Note:
Only active Tree Versions are flattened.
The following table describes the parameters of this API:
Table 20-33 flattenTree API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
treeStructureCode |
String |
Code of the tree structure on which flattening needs to be performed |
Mandatory |
treeCode |
String |
Code of the tree on which flattening needs to be performed |
Mandatory |
type |
String |
Type of flattening to be performed. It can be COLUMN or ROW flattening. By default, this API triggers both types of flattening |
Optional |
The following is an example of this API:
Example 20-11 flattenTree API Example
flattenTree(treeCode='FND_DEMO_EMP_T',treeStructureCode='FND_DEMO_EMP_TS')
flattenTreeVersion
APIThis API performs incremental flattening for the specified tree version synchronously.
Note:
Only the tree structures that are in active or draft state are flattened.
The following table describes the parameters of this API:
Table 20-34 flattenTreeVersion API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
treeStructureCode |
String |
Code of the tree structure on which flattening needs to be performed |
Mandatory |
treeCode |
String |
Code of the tree on which flattening needs to be performed |
Mandatory |
treeVersionName |
String |
Name of the tree version that needs to be flattened |
Mandatory |
type |
String |
Type of flattening to be performed. It can be COLUMN or ROW flattening. By default, this API triggers both types of flattening |
Optional |
The following is an example of this API:
Example 20-12 flattenTreeVersion API Example
flattenTreeVersion(treeStructureCode='FND_DEMO_EMP_TS',treeCode='FND_DEMO_EMP_T',treeVersionName='FND Demo Employee Tree Version',type='ROW')
flattenTreeVersionAsync
APIThis API performs incremental flattening for the specified tree version asynchronously.
Note:
Only the tree structures that are in active or draft state are flattened.
The following table describes the parameters of this API:
Table 20-35 flattenTreeVersionAsync API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
treeStructureCode |
String |
Code of the tree structure on which flattening needs to be performed |
Mandatory |
treeCode |
String |
Code of the tree on which flattening needs to be performed |
Mandatory |
treeVersionName |
String |
Name of the tree version that needs to be flattened |
Mandatory |
type |
String |
Type of flattening to be performed. It can be COLUMN or ROW flattening. By default, this API triggers both types of flattening |
Optional |
The following is an example of this API:
Example 20-13 flattenTreeVersionAsync API Example
flattenTreeVersion(treeStructureCode='FND_DEMO_EMP_TS',treeCode='FND_DEMO_EMP_T',treeVersionName='FND Demo Employee Tree Version',type='ROW')
flattenTreeVersionId
APIThis API performs incremental flattening for the specified tree version with the given ID.
The following table describes the parameters of this API:
Table 20-36 flattenTreeVersionId API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
treeStructureCode |
String |
Code of the tree structure on which flattening needs to be performed |
Mandatory |
treeCode |
String |
Code of the tree on which flattening needs to be performed |
Mandatory |
treeVersionId |
String |
Id of the tree version that needs to be flattened |
Mandatory |
type |
String |
Type of flattening to be performed. It can be COLUMN or ROW flattening. By default, this API triggers both types of flattening |
Optional |
The following is an example of this API:
Example 20-14 flattenTreeVersionId API Example
flattenTreeVersion(treeStructureCode='FND_DEMO_EMP_TS',treeCode='FND_DEMO_EMP_T',treeVersionId='FNDDemoEmployeeTreeVersion',type='ROW')
forceFlattenTreeVersion
APIThis API forces flattening on the specified tree version.
Note:
Only the tree versions that are in active or draft state are flattened.
The following table describes the parameters of this API:
Table 20-37 forceFlattenTreeVersion API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
treeStructureCode |
String |
Code of the tree structure on which flattening needs to be performed |
Mandatory |
treeCode |
String |
Code of the tree on which flattening needs to be performed |
Mandatory |
treeVersionName |
String |
Name of the tree version that needs to be flattened |
Mandatory |
The following is an example of this API:
Example 20-15 forceFlattenTreeVersion API Example
forceFlattenTreeVersion(treeStructureCode='FND_DEMO_EMP_TS',treeCode='FND_DEMO_EMP_T',treeVersionName='FND Demo Employee Tree Version')
auditTreeVersion
APIThis API performs an audit of the specified tree version synchronously.
The following table describes the parameters of this API:
Table 20-38 auditTreeVersion API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
treeStructureCode |
String |
Code of the tree structure on which auditing needs to be performed |
Mandatory |
treeCode |
String |
Code of the tree on which auditing needs to be performed |
Mandatory |
treeVersionName |
String |
Name of the tree version that needs to be audited |
Mandatory |
setActive |
Boolean |
Flag to indicate that the tree version is in active status after auditing |
Optional |
The following is an example of this API:
Example 20-16 auditTreeVersion API Example
auditTreeVersion(treeStructureCode='FND_DEMO_EMP_TS',treeCode='FND_DEMO_EMP_T',treeVersionName='FND Demo Employee Tree Version',setActive=true)
auditTreeVersion
Async
APIThis API performs an audit of the specified tree version asynchronously.
The following table describes the parameters of this API:
Table 20-39 auditTreeAsyncVersion API Parameters
Parameter | Datatype | Description | Mandatory/Optional |
---|---|---|---|
treeStructureCode |
String |
Code of the tree structure on which auditing needs to be performed |
Mandatory |
treeCode |
String |
Code of the tree on which auditing needs to be performed |
Mandatory |
treeVersionName |
String |
Name of the tree version that needs to be audited |
Mandatory |
setActive |
Boolean |
Flag to indicate that the tree version is in active status after auditing |
Optional |
The following is an example of this API:
Example 20-17 auditTreeVersionAsync API Example
auditTreeVersionAsynch(treeStructureCode='FND_DEMO_EMP_TS',treeCode='FND_DEMO_EMP_T',treeVersionName='FND Demo Employee Tree Version',setActive=true)
A WLST Command Report is generated and printed on the console when a WLST command is executed. The following details are displayed on the console:
WLST command that is executed
registration of the MBean
flattening call invoked
XML report
Note:
You can redirect the details printed on the console to a file, if required.
An XML Report is generated every time you execute a WLST command. The XML report contains all the results of the flattening APIs invoked. The following examples illustrate the XML report generated in various scenarios.
Example 20-18 WLST Command Report
wls:/DefaultDomain/serverConfig> flattenTree(treeStructureCode='TEST_VS_BICVO_TS',treeCode='TEST_VSBICVO_TREE') <FLATTENING_INFO>Trees Flattening Mbean name:oracle.apps.fnd.applcore.trees.mbean:name=TreeFlatten,* <SUCCESS> Connected to Weblogic Server Location changed to domainRuntime tree. This is a read-only tree with DomainMBean as the root. For more help, use help(domainRuntime) <FLATTENING_INFO> Type of Flattening : BOTH <SUCCESS> TreeFlatten MBean found : oracle.apps.fnd.applcore.trees.mbean:Location=DefaultServer,name=TreeFlatten,type=AppsRuntimeMBean,Application=FndSetup,ApplicationVersion=V2.0 <FLATTENING_INFO>Invoking Mbean : for operation flattenTree <FLATTENING_INFO> parseXMLForError : returning {''} XML report Start: <?xml version = '1.0' encoding = 'UTF-8'?> <TreesFlatteningReport status="SUCCESS"> <TreeStructure treeStructureCode="TEST_VS_BICVO_TS" treeCodeCount="1" success="1" error="0"> <TreeCode treeCode="TEST_VSBICVO_TREE" treeVersionCount="2" success="2" error="0"> <TreeVersion treeVersionName="TEST_VSBICVO_TV1" treeVersionId="ABA44835820F97B8E0404498A8F14DB6" flatteningType="ROW" time="7.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_VSBICVO_TV1" treeVersionId="ABA44835820F97B8E0404498A8F14DB6" flatteningType="COLUMN" time="2.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_VSBICVO_TV1_OLD_DATE" treeVersionId="ABA44835823A97B8E0404498A8F14DB6" flatteningType="ROW" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_VSBICVO_TV1_OLD_DATE" treeVersionId="ABA44835823A97B8E0404498A8F14DB6" flatteningType="COLUMN" time="1.0secs" status="SUCCESS"/> </TreeCode> </TreeStructure> </TreesFlatteningReport> XML Report Ends <FLATTENING_INFO>Completed flattenTree. wls:/DefaultDomain/serverConfig>
Example 20-19 Success Report
<?xml version = '1.0' encoding = 'UTF-8'?> <TreesFlatteningReport status="SUCCESS"> <TreeStructure treeStructureCode="TEST_PLSQL_EXTN_TS" treeCodeCount="2" success="2" error="0"> <TreeCode treeCode="TEST_PLSQL_EXTN_TR1" treeVersionCount="3" success="3" error="0"> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V1" treeVersionId="B7598796828D8027E040449895F14B45" flatteningType="ROW" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V1" treeVersionId="B7598796828D8027E040449895F14B45" flatteningType="COLUMN" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V2" treeVersionId="B7598796829A8027E040449895F14B45" flatteningType="ROW" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V2" treeVersionId="B7598796829A8027E040449895F14B45" flatteningType="COLUMN" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V3" treeVersionId="B7598796829F8027E040449895F14B45" flatteningType="ROW" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V3" treeVersionId="B7598796829F8027E040449895F14B45" flatteningType="COLUMN" time="0.0secs" status="SUCCESS"/> </TreeCode> <TreeCode treeCode="TEST_PLSQL_EXTN_TR2" treeVersionCount="1" success="1" error="0"> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR2_V1" treeVersionId="B759879682A48027E040449895F14B45" flatteningType="ROW" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR2_V1" treeVersionId="B759879682A48027E040449895F14B45" flatteningType="COLUMN" time="0.0secs" status="SUCCESS"/> </TreeCode> </TreeStructure> </TreesFlatteningReport>
Example 20-20 Error Report with Flattening Errors but no Exception
<?xml version = '1.0' encoding = 'UTF-8'?> <TreesFlatteningReport status="ERROR"> <TreeStructure treeStructureCode="TEST_PLSQL_EXTN_TS" treeCodeCount="1" success="0" error="1"> <TreeCode treeCode="TEST_PLSQL_EXTN_TR1" treeVersionCount="3" success="2" error="1"> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V1" treeVersionId="B7598796828D8027E040449895F14B45" flatteningType="ROW" time="4.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V1" treeVersionId="B7598796828D8027E040449895F14B45" flatteningType="COLUMN" time="0.0secs" status="ERROR"> ERROR:JBO-FTM-S-0045: Flattening cannot proceed due to the failure of auditing tree version TEST_PLSQL_EXTN_TS, TEST_PLSQL_EXTN_TR1, B7598796828D8027E040449895F14B45. </TreeVersion> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V2" treeVersionId="B7598796829A8027E040449895F14B45" flatteningType="ROW" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V2" treeVersionId="B7598796829A8027E040449895F14B45" flatteningType="COLUMN" time="1.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V3" treeVersionId="B7598796829F8027E040449895F14B45" flatteningType="ROW" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="TEST_PLSQL_EXTN_TR1_V3" treeVersionId="B7598796829F8027E040449895F14B45" flatteningType="COLUMN" time="1.0secs" status="SUCCESS"/> </TreeCode> </TreeStructure> </TreesFlatteningReport>
Example 20-21 Error Report with Exception but no Flattening Errors
<?xml version = '1.0' encoding = 'UTF-8'?> <TreesFlatteningReport status="ERROR"> <Exception>null releaseTreeService Exception: Cannot release TreeService Handle </Exception> <TreeStructure treeStructureCode="FND_DEMO_EMP_TS" treeCodeCount="1" success="1" error="0"> <TreeCode treeCode="FND_DEMO_EMP_T" treeVersionCount="1" success="1" error="0"> <TreeVersion treeVersionName="FND Demo Employee Tree Version" treeVersionId="A43EE0B37975BF52E040449833F06958" flatteningType="ROW" time="0.0secs" status="SUCCESS"/> <TreeVersion treeVersionName="FND Demo Employee Tree Version" treeVersionId="A43EE0B37975BF52E040449833F06958" flatteningType="COLUMN" time="0.0secs" status="SUCCESS"/> </TreeCode> </TreeStructure> </TreesFlatteningReport>
Node operations performed for a tree hierarchy also generate logs in trees tables that identify the changes made during the tree-flattening process. Once the flattening is processed for a hierarchy version, the logs that were generated prior to the last flattening run become obsolete. Over time, these obsolete records can accumulate in the log table, slowing the querying process that occurs during tree duplication or flattening.
You can purge all of these unused or archived logs for all underlying tree and tree versions associated with a selected tree structure by going to Tree Structure Search UI > Actions > Purge Logs in the Manage Tree Structures task flow.
Note:
You can only purge logs that were generated at least one day prior to the last flattening run for any tree version.
Figure 20-88 Purge Unused Tree Logs