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 key flexfield (KFF) setup in Oracle Fusion Applications and deployed the flexfield, view objects (VO) are generated for each segment that you have enabled for Oracle Business Intelligence (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 GL Journals under the Oracle Financial Analytics Offering, as shown in the example screen shot.
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:
For these VOs, ensure that you enable all the columns for the select list.
When you add the TREECODE view objects, you must enable the Disable Effective date filter option. Additionally, ensure that invunitsofmeasurep
view object is disabled for Human Capital Management. For Fusion Supply Chain Management and Customer Relationship Management, deploy the INV_UOM_CLASSES
descriptive flexfield forinvunitsofmeasurep
view object.
The VOs generated for any segment label apart from FA_COST_CTR, GL_BALANCING
and GL_ACCOUNT
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
.
For Units of Measure (UOM) extensions, add FscmTopModelAM.InvUomPublicViewAM.InvUnitsOfMeasurePVO
view object.
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 replication stage files logical schema in ODI (Fusion 9: DS_FUSION_9_0_REPLSTG_FILES
, Fusion 10: DS_FUSION_10_0_REPLSTG_FILES
).
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.
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.
This configuration file has to be placed in the replication staging location in the ODI environment (the location where the files are copied and placed from UCM to ODI).
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 configuration 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 (business intelligence) flattened VO (view object) generated for each key flexfield (KFF) has columns relating to each segment label. Use this procedure to set up the mappings for these columns.
file_fscmtopmodelam_categorybiam_flex_bi_category_vi%.csv
.
These segment columns end with “_” or “_c”. For example, BASED_CATEGORY_, MINOR_CATEGORY_.
You must add these new columns in the file data store and the VO data store in ODI.
FILE_FSCMTOPMODELAM_CATEGORYBIAM_FLEX_BI_CATEGORY_VI
data store with the default data type of String (50,50) for all columns.
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 LOC# KFF is FILE_FSCMTOPMODELAM_LOCATIONBIAM_FLEX_BI_LOCATION_VI
.
The VO data stores are in the path shown in the image. Use the default data type of Varchar(50, 50).
CAT# KFF VO data store: FscmTopModelAM.CategoryBIAM.FLEX_BI_Category_VI
LOC# KFF VO data store: FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI
Ensure that the flexfield value is the same as the column name. Also, ensure that you set the flexfield value ODI Populate Column to Y in SDS for these columns.
These images display example flexfield values.
GENERATE_SDS_DDL in INCR_REFRESH_MODE
.
This process updates the SDS table to include the new extended columns.
FTS_FUSION_FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI
.
LOC# KFF FTS mapping: FTS_FUSION_FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI
.
SDE_FUSION_V1_Adaptor
folder.
The column mapping is based on the segment label to BI Object mapping done in your Fusion Applications configuration.
SDE mapping: SDE_FUSION_FixedAssetLocationDimension
Staging table: W_ASSET_LOCATION_DS
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_10_ADAPTOR.
Before the change, the IS_INCREMENTAL variable should be displayed as in this example: