To import Segment Values and Hierarchies using FBDI process in Oracle Fusion follow the below steps:
- In Google search engine search for "oracle fusion fbdi 23a", as our version is 23a, and open the first link (https://docs.oracle.com/en/cloud/saas/financials/23a/oefbf/index.html)
- In the left pane go to the Payables section and select Import Segment Values and Hierarchies and then download the XLSM Template
- When you open the XLSM file, in case you get the below error :
- When you open the XLSM file, it already has the data in it. You can clear all the sheets and start inserting your own data
Before getting into to insert data in this FBDI sheet, let's understand a few things about Account Hierarchy.
Trees are hierarchical data models that you can use to organize data, apply business rules, control data access, and improve performance while querying.
Tree Structures- Provides the framework to organize data such that you can establish a hierarchy for use by the tree. It is just like a template that can help to create the trees.
Tree - Provides the framework to organize data such that you can establish a hierarchy for use by the tree.
The topmost node of a tree is called the root node. Child nodes branch
off from the root node. Child nodes at the same level with a common parent node, are called siblings. Leaves are details
branching off from a node but not extending further down the tree
hierarchy. You can create trees for multiple data sources and share
them across applications.
Tree Versions - By default a has only one version. If
required, you can create and maintain more than one editable tree
version. At any point, only one tree version must be active. If you
edit an existing version, it changes from active to draft. To use
it again, you must set it to active.
Tree Labels - Tree labels are short names given to trees
and tree structures.
- Go to Setup and Maintenance and search for "Manage Account Hierarchy". Click to open Manage Account Hierarchies
- Click on Create Tree button
- Enter the Tree Name, Code, Description, and choose the Icon Image.
- Select the Tree Structure as "Accounting Flexfield Hierarchy". Once you select it, two sections will be enabled :
- Enter Value Set Code in Bind_ValueSetCode field of both sections and click the save button once done.
- Please note that these value sets should already be created. Then click on the Next button in the top right corner.
- Click on the Next button.
- Click on the drop-down of Submit button and select "Submit and Add Version"
- Enter the Tree Version name, Effective Start Date, and Description
- This will create a blank tree version. Click on the OK button of the Confirmation message and click the Submit button
- Now, the tree and tree versions are ready to be used in the FBDI file.
- Let's first create segment values. Enter the below fields in the FBDI file's GL_SEGMENT_VALUES_INTERFACE sheet. All other fields are set to blank.
- Click on Generate CSV File button in the "Instructions and CSV Generation" sheet
- Now, let's upload this FBDI to Fusion to create segment values.
- Go to Schedules Processes and submit the "Load Interface File for Import".
- We need to submit only the loader job (Process Id: 877526). Below will be the sequence of jobs that will trigger automatically.
- Now to view Segment Values, go to Setup and Maintenance and search for Value Set, then click on Manage Chart of Accounts Value Set Values
- Search for the value set and then click on the Manage Values button
- Search for the values starting with 6000
- Now we have the values, let's create the hierarchies for the Tree and Tree Version we already created. Enter the below values in "GL_SEGMENT_HIER_INTERFACE" sheet, other cell values are set to blank.
- Now check the Account Hierarchy for the TEST_TREE
- Use the below query to check the values:
SELECT ffvs.flex_value_set_name VALUE_SET_CODE,ffv.flex_value VALUE,To_char(ffv.start_date_active, 'yyyy/mm/dd') START_DATE,To_char(ffv.end_date_active, 'yyyy/mm/dd') END_DATE,ffv.summary_flag SUMMARY_FLAG,ffv.enabled_flag ENABLED_FLAG,Regexp_substr(compiled_value_attributes, '[^'||Chr(10)||']+', 1, 4) AS account_type,Regexp_substr(compiled_value_attributes, '[^'||Chr(10)||']+', 1, 2) AS allow_posting,Regexp_substr(compiled_value_attributes, '[^'||Chr(10)||']+', 1, 3) AS allow_budgeting,Regexp_substr(compiled_value_attributes, '[^'||Chr(10)||']+', 1, 5) ASthird_party_control,Regexp_substr(compiled_value_attributes, '[^'||Chr(10)||']+', 1, 6) AS reconcile_flag,Regexp_substr(compiled_value_attributes, '[^'||Chr(10)||']+', 1, 7) ASfinancial_category,ffvt.descriptionFROM fnd_flex_value_sets ffvs,fnd_flex_values ffv,fnd_flex_values_tl ffvtWHERE 1 = 1AND ffvs.flex_value_set_id = ffv.flex_value_set_idAND ffv.flex_value_id = ffvt.flex_value_idAND ffvs.flex_value_set_name = 'TEST_VALUE_SET'
SELECT value_set_code,
tree_code,
tree_version_name,
tree_version_start_date,
tree_version_end_date,
Decode(max_depth - depth, 30, child,
NULL) parent30,
Decode(max_depth - depth, 29, child,
NULL) parent29,
Decode(max_depth - depth, 28, child,
NULL) parent28,
Decode(max_depth - depth, 27, child,
NULL) parent27,
Decode(max_depth - depth, 26, child,
NULL) parent26,
Decode(max_depth - depth, 25, child,
NULL) parent25,
Decode(max_depth - depth, 24, child,
NULL) parent24,
Decode(max_depth - depth, 23, child,
NULL) parent23,
Decode(max_depth - depth, 22, child,
NULL) parent22,
Decode(max_depth - depth, 21, child,
NULL) parent21,
Decode(max_depth - depth, 20, child,
NULL) parent20,
Decode(max_depth - depth, 19, child,
NULL) parent19,
Decode(max_depth - depth, 18, child,
NULL) parent18,
Decode(max_depth - depth, 17, child,
NULL) parent17,
Decode(max_depth - depth, 16, child,
NULL) parent16,
Decode(max_depth - depth, 15, child,
NULL) parent15,
Decode(max_depth - depth, 14, child,
NULL) parent14,
Decode(max_depth - depth, 13, child,
NULL) parent13,
Decode(max_depth - depth, 12, child,
NULL) parent12,
Decode(max_depth - depth, 11, child,
NULL) parent11,
Decode(max_depth - depth, 10, child,
NULL) parent10,
Decode(max_depth - depth, 9, child,
NULL) parent9,
Decode(max_depth - depth, 8, child,
NULL) parent8,
Decode(max_depth - depth, 7, child,
NULL) parent7,
Decode(max_depth - depth, 6, child,
NULL) parent6,
Decode(max_depth - depth, 5, child,
NULL) parent5,
Decode(max_depth - depth, 4, child,
NULL) parent4,
Decode(max_depth - depth, 3, child,
NULL) parent3,
Decode(max_depth - depth, 2, child,
NULL) parent2,
Decode(max_depth - depth, 1, child,
NULL) parent1,
Decode(max_depth - depth, 0, child,
NULL) value,
'Level '
||( max_depth - depth ) label_short_name
FROM (SELECT (SELECT p1.parameter_value
FROM fnd_tree_data_source_params p1,
fnd_ts_data_source_params p2
WHERE p1.parameter_id = p2.parameter_id
AND p1.tree_structure_code = 'GL_ACCT_FLEX'
AND p1.data_source_id = tree.data_source_id
AND p1.tree_code = tree.tree_code
AND p2.parameter_name = 'Bind_ValueSetCode')
value_set_code
,
tree.tree_code,
tree_v.tree_version_name,
To_char(tree_v.effective_start_date, ' yyyy/mm/dd')
TREE_VERSION_START_DATE,
To_char(tree_v.effective_end_date, 'yyyy/mm/dd')
TREE_VERSION_END_DATE,
tree.parent_pk1_value parent,
tree.pk1_start_value child,
depth,
(SELECT Max(depth)
FROM fnd_tree_node tree1
WHERE 1 = 1
AND tree.tree_structure_code = tree1.tree_structure_code
AND tree.tree_code = tree1.tree_code
AND tree.tree_version_id = tree1.tree_version_id)
max_depth
FROM fnd_tree_node tree,
fnd_tree_version_vl tree_v
WHERE 1 = 1
AND tree.tree_structure_code = tree_v.tree_structure_code
AND tree.tree_code = tree_v.tree_code
AND tree.tree_structure_code = 'GL_ACCT_FLEX'
AND tree.tree_code = :pTreeCode
AND tree_v.tree_version_name = :pTreeVersion
START WITH tree.parent_tree_node_id IS NULL
CONNECT BY PRIOR tree_node_id = parent_tree_node_id) t1;
--TreeCodeLOV:
SELECT DISTINCT tree_v.tree_code
FROM fnd_tree_node tree,
fnd_tree_version_vl tree_v
WHERE tree.tree_code = tree_v.tree_code
AND tree.tree_structure_code = 'GL_ACCT_FLEX'
ORDER BY 1;
--TreeVersionLOV
SELECT DISTINCT tree_v.tree_version_name
FROM fnd_tree_node tree,
fnd_tree_version_vl tree_v
WHERE tree.tree_code = tree_v.tree_code
AND tree.tree_structure_code = 'GL_ACCT_FLEX'
AND tree.tree_code = :pTreeCode
ORDER BY 1;
Comments
Post a Comment