Setting Up Key Flex Fields for Financials Fusion Applications Source Data

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.

  1. Enable and setup the VOs for extraction in Oracle BI Cloud Connector Console, as specified in Using Oracle BI Cloud Connector Console to Configure Cloud Extract.
  2. Configure the ODI repository in Oracle Data Integrator, as specified in Using ODI to Set Up Key Flex Fields.
  3. If you are deploying Fusion Financials, then perform the additional configuration steps in Additional Steps for Fusion Financials Implementations.

Using Oracle BI Cloud Connector Console to Configure Cloud Extract

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:

  1. In Oracle BI Cloud Connector Console, navigate to Configure Cloud Extract link on the Tasks pane to display the Configure Cloud Extract dialog.
  2. Scroll down to the Data Store area.
  3. Click Add to display the Add Data Store dialog, specify the details, then click Save and Close.

    Note:

    VO names are case-sensitive.

    You must add VOs under the GL Journals located under the Oracle Financial Analytics Offering.

    Description of GUID-257B55C0-2C08-4229-9489-FA32963EE66B-default.gif follows
    Description of the illustration GUID-257B55C0-2C08-4229-9489-FA32963EE66B-default.gif
  4. To enable VOs for extraction, select the parent Offering, Functional Area, FG/DG, or individual VO .
  5. Enable and add these VOs for extraction.

    • 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 Applications configuration.

      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.

  6. Create and save this configuration file in the same location where all the data files generated is this procedure are present.

    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.

Using ODI to Set Up Key Flex Fields

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.

  1. 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_.

    You must add these new columns in the file data store and the VO data store in ODI.

  2. Add the new columns to the FILE_FSCMTOPMODELAM_CATEGORYBIAM_FLEX_BI_CATEGORY_VI data store with the default data type of String (50,50) for all columns.
    Description of GUID-0E51713C-6625-4292-8E82-FEF304D4DFD2-default.gif follows
    Description of the illustration GUID-0E51713C-6625-4292-8E82-FEF304D4DFD2-default.gif

    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.

  3. Repeat the configuration steps for the corresponding VO data stores in ODI.

    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

    Description of GUID-F72DD7F5-2B17-41BE-B8C1-3BC2AA69BE4D-default.gif follows
    Description of the illustration GUID-F72DD7F5-2B17-41BE-B8C1-3BC2AA69BE4D-default.gif
    Scroll down to locate the VO data stores.
    Description of GUID-F20A2026-015C-49AB-9E56-E2EFD9EC02A0-default.gif follows
    Description of the illustration GUID-F20A2026-015C-49AB-9E56-E2EFD9EC02A0-default.gif
    Description of GUID-CA78DE29-DFA3-4AF1-8FA5-4333726ACD57-default.gif follows
    Description of the illustration GUID-CA78DE29-DFA3-4AF1-8FA5-4333726ACD57-default.gif
  4. For the newly added columns in both the file data store and VO data store, set the flexfield value for OBI SDS Column Short Name.

    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.

    For example, CAT# KFF File Data Store Column:
    Description of GUID-58AD3E77-8114-4119-B54B-BF92CF969FFC-default.gif follows
    Description of the illustration GUID-58AD3E77-8114-4119-B54B-BF92CF969FFC-default.gif
    For example, CAT# KFF VO Data Store Column:
    Description of GUID-38EC89A6-B7A6-45C3-A8E0-A16EBBC49ED3-default.gif follows
    Description of the illustration GUID-38EC89A6-B7A6-45C3-A8E0-A16EBBC49ED3-default.gif
  5. Repeat for LOC# KFF.
  6. Execute GENERATE_SDS_DDL in INCR_REFRESH_MODE.

    This process updates the SDS table to include the new extended columns.

  7. When you have added the columns in the appropriate data stores, map the columns in the corresponding ODI mappings to load the data.
  8. For the CAT# KFF, edit the FTS mapping located under the SDE_FUSION_V1_ADAPTOR folder to map these new columns.
  9. Create a one-to-one mapping between the segment columns in FTS_FUSION_FscmTopModelAM.AccountBIAM.FLEX_BI_Account_VI.
  10. Save the changes, then regenerate the underlying scenario for the package.
  11. Similarly, if you have LOC# KFF, edit the corresponding FTS mapping to map the new columns:

    LOC# KFF FTS mapping: FTS_FUSION_FscmTopModelAM.LocationBIAM.FLEX_BI_Location_VI.

  12. Save the changes and regenerate the underlying scenarios for the packages.
  13. Edit the SDE mapping for CAT# KFF to map these new columns to corresponding columns in the staging table.
    The SDE mapping is located in the same SDE_FUSION_V1_Adaptor folder.
    Description of GUID-93931C78-5BEB-4007-A21E-4BB4FADE2A9A-default.gif follows
    Description of the illustration GUID-93931C78-5BEB-4007-A21E-4BB4FADE2A9A-default.gif

    The column mapping is based on the segment label to BI Object mapping done in your Fusion Applications configuration.

    For CAT# KFF, if you have mapped BASED_CATEGORY and MINOR_CATEGORY then you can use them as the Major and Minor category.
    Description of GUID-CEEA8734-B4AC-484E-8EAC-94C289BF9B2E-default.gif follows
    Description of the illustration GUID-CEEA8734-B4AC-484E-8EAC-94C289BF9B2E-default.gif
  14. Save the changes and regenerate the underlying scenario for the package.
  15. If applicable, complete similar steps for LOC# KFF based on the columns that you extend for these:

    • SDE mapping: SDE_FUSION_FixedAssetLocationDimension

    • Staging table: W_ASSET_LOCATION_DS

  16. Before generating a load plan for execution, enable FTS and SDE scenarios corresponding to GL Segment 1-10 in load plan components so that they’re included in the final generated load plan.

    For FTS scenarios, you can edit any one of these FTS load plan components. These load plan components are 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

  17. Enable the FTS scenarios based on your Fusion Applications configuration. For example, if your PRODUCT label is mapped to Dim – GL Segment 1 and is a tree segment and ORG label is mapped to Dim – GL Segment 2 and is a non tree segment, then enable these scenarios:

    • 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

    Description of GUID-3E219A4D-D6CA-41E6-8E85-CD7FA6FB29B7-default.gif follows
    Description of the illustration GUID-3E219A4D-D6CA-41E6-8E85-CD7FA6FB29B7-default.gif

    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.

  18. For this load plan component, enable the main Parallel branch and then disable the steps that are not required.
  19. If your Segment 1 is a tree segment and Segment2 is a non-tree segment, enable these scenarios in the load plan component and disable the remaining steps:

    • 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 Applications, then enable:

    • SDE_FUSION_GLSEGMENTDIMENSIONHIERARCHY_SEGMENTx

    • SDE_FUSION_GLSEGMENTDIMENSION_TREE_SEGMENTx

    If Dim – GL Segmentx is mapped to a non tree segment label in Fusion Applications, then enable:

    • SDE_FUSION_GLSEGMENTDIMENSION_SEGMENTx

    Description of GUID-50FDD64E-CEB0-4414-A505-2EB0B6B6BFFA-default.gif follows
    Description of the illustration GUID-50FDD64E-CEB0-4414-A505-2EB0B6B6BFFA-default.gif
  20. Generate a load plan based on your offering and the modules implemented.

    In the generated load plan, verify that your settings on the FTS and SDE load plan components were carried over appropriately.

Additional Steps for Fusion Financials Implementations

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.

  1. In ODI Studio, locate these two FTS Fusion mappings in the ODI Repository.

    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.

    This screen shot is described in surrounding text.
  2. Edit the packages in the two folders.

    Before the change, the IS_INCREMENTAL variable should be displayed as in this example:

    This screen shot is described in surrounding text.
  3. For each package, click on the IS_INCREMENTAL variable to display the Properties dialog, and change the Type value from Refresh Variable to Set Variable, and set the Value to N.
    This screen shot is described in surrounding text.
  4. Save the changes to the packages and regenerate the scenario for both packages.