If applicable, load the financial KFF data for GL#, CAT# and LOC# KFFs into Oracle Business Analytics Warehouse.
These instructions only apply if you are deploying a Fusion Applications cloud data source.
When you have completed the KFF setup in Fusion and deployed the Flexfield, VOs are generated for each segment that you have enabled for BI. Each VO must be seeded and enabled for BI extraction so that it can be loaded into Oracle Business Analytics Warehouse.
To seed and enable VOs for extraction:
Note:
VO names are case-sensitive.You must add VOs under the Custom Fact Group for Custom Facts under Custom Functional Area for Custom Content under the Oracle Financial Analytics Offering, as shown in the example screen shots.
For example, this screenshot shows the parent Oracle Custom Analytics:
For the GL# KFF, you must add the BI Flattened VO along with the segment VOs generated for the segment labels mapped to BI Objects in your Fusion setup.
The BI Flattened VO name for GL# that must be added is FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI.
The VOs generated for segment labels FA_COST_CTR (mapped to Dim – Cost Center), GL_ACCOUNT (mapped to Dim – Natural Account Segment), and GL_BALANCING (mapped to Dim – Balancing Segment) are known prior. You must add these VOs:
FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_FA_COST_CTR_VI FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_FA_COST_CTR_VI FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_GL_BALANCING_VI FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_GL_BALANCING_VI FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_GL_ACCOUNT_VI FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_GL_ACCOUNT_VI
Note: When you add the TREECODE VOs, you must enable the 'Disable Effective date filter' option.
The VOs generated for any segment label apart from the 3 mentioned in the previous point and mapped to the BI Object Dim – GL Segmentx are not known before hand as the VO names depends on the segment label. Therefore, you must add the VO names based on the naming pattern.
If the segment is a tree segment (if you have created hierarchies for value sets used for those segments), then the VOs are named:
- FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_<segment label>_VI
- FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_<segment label>_VI
If the segment is a non tree segment (if you have NOT created hierarchies for value sets used for those segments), then the VOs are named:
FscmTopModelAM.AccountBIAM.FLEX_VS_<segment label>_VI
For example:
Segment Label | Tree v/s Non Tree | VO Name |
---|---|---|
PRODUCT |
Tree |
FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_PRODUCT_VI FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_PRODUCT_VI |
ORG |
Non Tree |
FscmTopModelAM.AccountBIAM.FLEX_VS_ORG_VI |
Note: When you add TREECODE VOs, you must enable the 'Disable Effective date filter' option.
For the CAT# and LOC# KFFs, you will need to add the BI Flattened VO for each KFF.
The BI Flattened VO name for CAT# that needs to be added is FscmTopModelAM.CategoryBIAM.FLEX_BI_Category_VI.The BI Flattened VO name for LOC# that needs to be added is FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI.
Once you have added these VOs and completed the extraction process, the data files for these VOs will be downloaded into the physical location configured for DS_FUSION10_REPLSTG_FILES logical schema in ODI.
The files generated for GL# segment labels mapped to Dim – GL Segmentx (Dim – GL Segment 1-10) will have corresponding pre-seeded data stores using a generic naming convention in ODI. The next step is to rename the corresponding files with appropriate file names so that they can load the corresponding data stores. For reference, the data stores seeded in ODI are shown in this screenshot:
The mapping between the files generated and the new file names corresponding to the data stores is based on the segment label to BI Object mapping and setup for that segment (tree v/s non tree).
Taking the same example as mentioned in the previous steps – PRODUCT label is mapped to Dim – GL Segment 1 and is a tree segment and ORG label is mapped to Dim – GL Segment2 and is a non tree segment, then the mapping between the generated files and the new files will be:
File Generated | New File Name |
---|---|
File_FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_PRODUCT_VI |
File_FscmTopModelAM.AccountBIAM.FLEX_TREE_VS_GL_SEGMENT1_VI |
File_FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_PRODUCT_VI |
File_FscmTopModelAM.AccountBIAM.FLEX_TREECODE_VS_GL_SEGMENT1_VI |
File_FscmTopModelAM.AccountBIAM.FLEX_VS_ORG_VI |
File_FscmTopModelAM.AccountBIAM.FLEX_VS_GL_SEGMENT2_VI |
This mapping has to be provided in a configuration file named FinKFFFileRename.cfg which will be used in the downstream processes to load the data from those files.
The configuration file has to be created as a comma delimited file and the format of the entries in the file has to be as shown in this sample entry. Save the file when you are done making the entries.
<Generated File Name>,<New File Name based on the pre seeded data store>
A sample entry in the file would look like this:
file_fscmtopmodelam_accountbiam_flex_vs_gl_management_vi,file_fscmtopmodelam_accountbiam_flex_vs_gl_segment2_vi
Create the config file in the same platform (Windows or Linux) where you will be eventually saving this file. Avoid copy and paste from one platform to the other. This will avoid issues regarding special characters in text files when crossing different platforms.
The BI flattened VO generated for each KFF has columns relating to each segment label. Use this procedure to set up the mappings for these columns.
file_fscmtopmodelam_accountbiam_flex_bi_account_vi%.csv
.
These segment columns end with ‘_' or ‘_c'. For example, FA_COST_CTR_ and FA_COST_CTR_c, PRODUCT_ and PRODUCT_c.
You must add these new columns in the file data store and the VO data store in ODI.
The file data store is located in the path shown in this screen shot:
For example, new columns added in FILE_FSCMTOPMODELAM_ACCOUNTBIAM_FLEX_BI_ACCOUNT_VI.
For CAT# KFF, check the csv file generated for the BI Flattened VO for the list of additional columns for each segment - file_fscmtopmodelam_categorybiam_flex_bi_category_vi%.csv
. These segment columns end with ‘_' or ‘_c'. For example, BASED_CATEGORY_, MINOR_CATEGORY_.
For LOC# KFF, check the csv file generated for the BI Flattened VO for the list of additional columns for each segment - ‘file_fscmtopmodelam_locationbiam_flex_bi_location_vi%.csv'. These segment columns end with ‘_' or ‘_c'.
The corresponding file data stores for CAT# and LOC# are:
CAT#KFF file data store: FILE_FSCMTOPMODELAM_CATEGORYBIAM_FLEX_BI_CATEGORY_VI
LOC#KFF file data store: FILE_FSCMTOPMODELAM_LOCATIONBIAM_FLEX_BI_LOCATION_VI
For example, new columns added in FILE_FSCMTOPMODELAM_CATEGORYBIAM_FLEX_BI_CATEGORY_VI
The VO data stores are in the path shown in the screenshot. Use the default data type of Varchar(50,50).
GL# KFF VO data store: FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI
CAT#KFF VO data store: FscmTopModelAM.CategoryBIAM.FLEX_BI_Category_VI
LOC#KFF VO data store: FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI
The value should be the same as the column name. Also ensure that the Flexfield value Odi Populate Column in SDS is set to Y for these columns.
These screen shots show example Flexfield values.
For example, GL# KFF File Data Store Column:
For example, CAT# KFF File Data Store Column:
For example, GL# KFF VO Data store Column:
For example, CAT# KFF VO Data store Column:
GENERATE_SDS_DDL in INCR_REFRESH_MODE
.
This process updates the SDS table to include the new extended columns.
The FTS mapping is be located under the folder SDE_FUSION_V1_ADAPTOR.
For example, Column mappings in FTS_FUSION_FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI.
CAT#KFF FTS mapping: FTS_FUSION_FscmTopModelAM.CategoryBIAM.FLEX_BI_Category_VI
LOC#KFF FTS mapping: FTS_FUSION_FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI
For example, Column mappings in FTS_FUSION_FscmTopModelAM.CategoryBIAM.FLEX_BI_Category_VI
The SDE mapping is located in the same SDE_FUSION_V1_Adaptor folder.
The column mapping is based on the segment label to BI Object mapping done in your Fusion setup.
For GL#KFF, the mappings for FA_COST_CTR, GL_ACCOUNT and GL_BALANCING labels are known and should be mapped as shown in this table.
Target Column Name | Mapping |
---|---|
COST_CENTER_ATTRIB |
CCIDVO.FA_COST_CTR_c |
COST_CENTER_NUM |
CCIDVO.FA_COST_CTR_ |
BALANCING_SEGMENT_ATTRIB |
CCIDVO.GL_BALANCING_c |
BALANCING_SEGMENT_NUM |
CCIDVO.GL_BALANCING_ |
NATURAL_ACCOUNT_ATTRIB |
CCIDVO.GL_ACCOUNT_c |
NATURAL_ACCOUNT_NUM |
CCIDVO.GL_ACCOUNT_ |
The column mapping for the other segment labels is based on the corresponding BI Objects mapped in your Fusion setup.
For example, assuming that you have mapped PRODUCT label to Dim – GL Segment1 and ORG label to Dim – GL Segment2, the column mapping will look like:
Target Column Name | Mapping |
---|---|
ACCOUNT_SEG1_ATTRIB |
CCIDVO.PRODUCT_c |
ACCOUNT_SEG1_CODE |
CCIDVO.PRODUCT_ |
ACCOUNT_SEG2_ATTRIB |
CCIDVO.ORG_c |
ACCOUNT_SEG2_CODE |
CCIDVO.ORG_ |
For example, assuming that you have mapped ALTACCT label to Dim – GL Segment3 and GL_MANAGEMENT label to Dim – GL Segment5, then the column mapping will look like:
Target Column Name | Mapping |
---|---|
ACCOUNT_SEG3_ATTRIB |
CCIDVO.ALTACCT _c |
ACCOUNT_SEG3_CODE |
CCIDVO.ALTACCT _ |
ACCOUNT_SEG5_ATTRIB |
CCIDVO.GL_MANAGEMENT _c |
ACCOUNT_SEG5_CODE |
CCIDVO.GL_MANAGEMENT__ |
The corresponding mappings for both are given here.
CAT#KFF
a.SDE mapping: SDE_FUSION_FixedAssetCategoryDimension
b.Staging table: W_ASSET_CATEGORY_DS
LOC#KFF:
a.SDE mapping: SDE_FUSION_FixedAssetLocationDimension
b.Staging table: W_ASSET_LOCATION_DS
For CAT#KFF, if you have mapped BASED_CATEGORY and MINOR_CATEGORY and would like to use them as the Major and Minor Category, then the column mapping is:
Target Column Name | Mapping |
---|---|
MAJOR_CATEGORY |
FLEXVO.BASED_CATEGORY_ |
MINOR_CATEGORY |
FLEXVO.MINOR_CATEGORY_ |
For FTS scenarios, you can edit any ONE of these FTS load plan components. These LP components will be located in BIAPPS Load Plan – Load Plan Dev Components –FTS:
3 FTS Fact BUDGET_FG FUSION_1_0
3 FTS Fact GLBAL_FG FUSION_1_0
3 FTS Fact GLCOGS_FG FUSION_1_0
3 FTS Fact GLJRNLS_FG FUSION_1_0
3 FTS Fact GLREVN_FG FUSION_1_0
FTS_FUSION_FSCMTOPMODELAM_ACCOUNTBIAM_FLEX_TREE_VS_GL_SEGMENT1_VI
FTS_FUSION_FSCMTOPMODELAM_ACCOUNTBIAM_FLEX_TREECODE_VS_GL_SEGMENT1_VI
FTS_ FUSION_FSCMTOPMODELAM_ACCOUNTBIAM_FLEX_VS_GL_SEGMENT2_VI
For SDE scenarios, edit the 3 SDE Dims GLSEG_DIM FUSION_1_0 load plan component. This load plan component will be located in the path BIAPPS Load Plan – Load Plan Dev Components –SDE – FUSION_1_0.
SDE_FUSION_GLSEGMENTDIMENSIONHIERARCHY_SEGMENT1
SDE_FUSION_GLSEGMENTDIMENSION_TREE_SEGMENT1
SDE_FUSION_GLSEGMENTDIMENSION_SEGMENT2
If Dim – GL Segmentx is mapped to a tree segment label in Fusion, enable:
SDE_FUSION_GLSEGMENTDIMENSIONHIERARCHY_SEGMENTx
SDE_FUSION_GLSEGMENTDIMENSION_TREE_SEGMENTx
If Dim – GL Segmentx is mapped to a non tree segment label in Fusion, enable:
SDE_FUSION_GLSEGMENTDIMENSION_SEGMENTx
This screen shot shows an example modified Load Plan Component.
In the generated load plan, verify that your settings on the FTS and SDE load plan components were carried over accordingly.
If you are deploying Fusion Financials on Oracle Cloud, then you must make the additional modifications to FTS Fusion mappings related to GL Balances. These steps are only applicable if you have an Oracle Fusion implementation on Oracle Cloud.
FTS_FUSION_FscmTopModelAM.FinGlInquiryBalancesAM.BalanceFullPVO
FTS_FUSION_FscmTopModelAM.FinGlInquiryBalancesAM.BalancePVO
The FTS mappings are located in the ODI repository under the folder Mappings - SDE_FUSION_V1_ADAPTOR.
Before the change, the IS_INCREMENTAL variable should be displayed as in this example: