REST API to Manage Value Set value hierarchies in Oracle Fusion

In Fusion value sets are very popular however for GL in Fusion along with value sets and values tied to COA , hierarchy of value set values is also important.

Recently there was a requirement to manage value set hierarchies. However I couldn’t find any better document explaining the same.

Thus I am trying to share my experience and API to manage the value set hierarchies.

Consider following data

1. Valueset name — Location_Hierarchy
2. Parent Node — Canada
3. Child Node- Alberta
4. Tree Code Name (Structure Name) — Location_Code

-- Query1 to print hierarchical data for the valuesets.
SELECT tree.parent_pk1_value parent,
tree.pk1_start_value child
,level
,tree.*
FROM fnd_tree_node tree
WHERE tree.tree_structure_code = 'GL_ACCT_FLEX'
AND tree.tree_code = 'Location_Code' 
AND tree.tree_version_id = (select tree_version_id 
from fnd_tree_version_vl
where tree_code = 'Location_Code' 
and tree_version_name = 'V1')
START WITH tree.parent_tree_node_id IS NULL
CONNECT BY prior tree_node_id = parent_tree_node_id

-- Query2 to see the flex value set details.
select * from fnd_flex_value_sets where flex_value_set_name like 'Location_Hierarchy';

In order to create value in the hierarchy use below API.

Endpoint: 
https://XXX.fa.ocs.oraclecloud.com/fscmRestApi/resources/11.13.18.05/fndTreeNodeOperations/action/addValueTreeNode
Verb: POST

Content-Type: application/vnd.oracle.adf.action+json
Accept: application/vnd.oracle.adf.actionresult+json

Data:
{
"treeStructureCode":"GL_ACCT_FLEX",
"treeCode":"Location_Code",
"treeVersionId":"1A2C483C311E23DAE063019F6A0ABD21",
"parentTreeNodeId":"1A2C483C6B7C23DAE063019F6A0ABD21",
"dataSourceId":"634331F4AB5AED36E04044987CF032D9",
"pk1Value":"Alberta",
"pk2Value":"Location_Hierarchy",
"pk3Value":"",
"pk4Value":"",
"pk5Value":""
}

treeVersionId = This you will receive from the query1.Column name TREE_VERSION_ID
dataSourceId = This you will receive from the query1.Column name DATA_SOURCE_ID
parentTreeNodeId = This is the TREE_NODE_ID for Canada (Parent node in our example)
pk1Value = Child node value that you want to create in hierarchy
pk2Value = Valueset name for which we want to create hierarchy.

In order to delete the same node use below API.

Note: Even for the delete the verb is POST and not DELETE

Endpoint:
https://XXX.fa.ocs.oraclecloud.com/fscmRestApi/resources/11.13.18.05/fndTreeNodeOperations/action/deleteTreeNode
Verb: POST

Content-Type: application/vnd.oracle.adf.action+json
Accept: application/vnd.oracle.adf.actionresult+json

Data:

{
"treeStructureCode":"GL_ACCT_FLEX",
"treeCode":"Location_Code",
"treeVersionId":"1A2C483C311E23DAE063019F6A0ABD21",
"treeNodeId":"1DFA0C7F99FCFB66E063109E6A0A7895"
}

treeVersionId = This you will receive from the query1.Column name TREE_VERSION_ID
treeNodeId = This is the TREE_NODE_ID for Canada (Node that we want to delete).Column name TREE_VERSION_ID

Note- For update you can perform delete the note and create the node in different hierarchy.

This was the short explanation of the API to be used to manage the hierarchies in Oracle Fusion.

Hope you enjoyed reading this.