Blueprints → Install and Configure Oracle EBS for Incorta Cloud

About the Oracle EBS Blueprint for Incorta Cloud

The Incorta Oracle E-Business Suite (EBS) blueprint provides a new, faster, more effective option for analyzing and understanding your data, with no data modeling or extract, transform, load (ETL) required. You will get instant access to up-to-date, consolidated enterprise resource planning (ERP) data and can build all of the high-performance reports you need on your own.

With an extensive library of prebuilt content, Oracle EBS users gain immediate access to rich content areas and sample dashboards that are easily customizable and logically grouped by department and persona to help answer the most-pressing business questions quickly.

In addition, the Oracle EBS Blueprints expedite your organization’s migration from legacy reporting tools, and drastically speed your Oracle EBS-based reporting and analytics project’s time to value. They also let you more quickly add new data sources to your Oracle EBS analysis.

Following are the Oracle EBS modules for which blueprints are available in the cloud version of Incorta:

Financial Modules

Supply Chain Planning Modules

Business Flows

  • Order to Cash
  • Procure to Pay

Other Modules

Each blueprint module represents a 1:1 mapping of the corresponding tables and relationships in Oracle EBS. Also included is prebuilt metadata that can be used to load data for each module from the Oracle database and view it in prebuilt dashboards.

Note

You can install the blueprint modules individually, or you can install all modules at once with the EBS Suite blueprint.

Install the Oracle EBS Blueprint

Here are the steps to install the Oracle EBS Blueprint:

  • Sign in to the Incorta Cloud Administrator Portal (CAP).
  • In the Navigation bar, select Blueprints.
  • Locate the Oracle EBS blueprint of interest. You can use the Filter panel or the Search bar to display only the relevant blueprints.
  • Select Install.
  • The Install Blueprint dialog appears with prefilled data for Cluster, Tenant, Username, and Password. You can edit these properties as necessary.
  • Enable Include Sample Data to include Oracle EBS sample data in the blueprint installation, which will allow you to preview the blueprint dashboards and insights before you connect to your Oracle EBS instance and configure the blueprint for your instance.
  • Select Install.

Configure the Oracle EBS Blueprint

There are required and optional steps to configure the Oracle EBS blueprint. To connect the blueprint to your Oracle EBS instance, the following steps are required:

You can optionally apply Oracle EBS customizations to the blueprint. These steps are covered in detail below.

Configure the Data Agent to Connect to your Oracle EBS data

To connect the blueprint to your on-premises Oracle EBS data, you need to add a data agent. Once you configure the data agent, you will configure your data sources to use the data agent. To learn more about how to configure a data agent, please review Tools → Data Agent.

Configure the Oracle EBS Blueprint Data Source

To connect the blueprint to your Oracle EBS data, follow these steps:

  • Sign in to the Incorta Cloud Administrator Portal (CAP).
  • In the Navigation bar, select Clusters.
  • Select the Cluster you installed the Oracle EBS blueprint on.
  • Select Incorta Analytics.
  • The Incorta Analytics sign-in page appears with pre-filled data for Tenant, Username, and Password. You can edit these properties as necessary.
  • Select Sign In.
  • In the Navigation bar, select Data.
  • In the Data Manager, in the Context bar, select the External Data Source tab.
  • In the List View of external data sources, for the EBS data source, select Edit (pen icon).
  • Configure the Oracle EBS connection properties for your instance:

    • Enter the Username
    • Enter the Password
    • Enter the Connection Pool: 10
    • Enter the Connection String: jdbc:oracle:thin:@<HOST>:<PORT>/<DATABASE>
    • Enter the Connection Properties (optional)
    • Enable Use Data Agent
    • Select the Data Agent you added earlier

Configure the VCP Data Source - for ASCP customers only

  • In the List View of external data sources, for the VCP data source, select Edit (pen icon).
  • Enter the VCP connection properties for your instance:

    • Enter the Username
    • Enter the Password
    • Enter the Connection Pool: 10
    • Enter the Connection String: jdbc:oracle:thin:@<HOST>:<PORT>/<DATABASE>
    • Enter the Connection Properties (optional)
    • Enable Use Data Agent
    • Select the Data Agent you added earlier
Note

If you have a centralized EBS/VCP Oracle Apps environment, the EBS and VCP data sources will have the same database connection.

Configure Global Variables

Configure the $$flex_value_set_id global variable to point to your default General Ledger (GL) Account and Cost Center hierarchies. This global variable is used in the SQL of the Account_Hierarchy and CostCenter_Hierarchy tables in the EBS_FIN_COMMON physical schema.

  • Run the following SQL against your Oracle EBS database:

    SELECT
       FIFS.ID_FLEX_NUM AS CHART_OF_ACCOUNT_ID,
       FIFS.SEGMENT_NUM,
       FIFS.SEGMENT_NAME,
       FIFS.APPLICATION_COLUMN_NAME,
       FIFS.FLEX_VALUE_SET_ID,
       LISTAGG(FSAV.SEGMENT_ATTRIBUTE_TYPE, ',') WITHIN GROUP(
           ORDER BY
               FSAV.SEGMENT_ATTRIBUTE_TYPE AS QUALIFIERS
           FROM
               APPS.FND_ID_FLEX_SEGMENTS FIFS
    LEFT JOIN APPS.FND_SEGMENT_ATTRIBUTE_VALUES FSAV ON FIFS.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
               AND FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
               AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
               AND FSAV.ATTRIBUTE_VALUE = 'Y'
           WHERE
               FIFS.ID_FLEX_CODE = 'GL#'
           GROUP BY
               FIFS.ID_FLEX_NUM,
               FIFS.SEGMENT_NUM,
               FIFS.SEGMENT_NAME,
               FIFS.APPLICATION_COLUMN_NAME,
               FIFS.FLEX_VALUE_SET_ID
           ORDER BY
               FIFS.ID_FLEX_NUM,
               FIFS.SEGMENT_NUM

    The query will return the FLEX_VALUE_SET_ID for each unique combination of CHART_OF_ACCOUNT_ID and SEGMENT_NUM. Select the FLEX_VALUE_SET_ID that corresponds to the appropriate CHART_OF_ACCOUNT_ID and SEGMENT_NUM.

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Global Variables tab.
  • In the list view of global variables, select the flex_value_set_id global variable to open it.
  • Update the Value based on the query results.
  • Select Ok.

Configure the General Ledger Account Key Flexfields

Configure the GL account to reflect your implementation of the GL# Key Flexfields KFFs. By default, the Oracle EBS blueprints use three segments in the chart of accounts. If you use only three segments, follow the minimal configuration steps in this section. If you have additional segments, then follow the steps under optional configurations to add additional segments.

Determine the Chart of Accounts to use for reporting

Run the SQL query below if the chart of accounts to use for reporting are not already known:

    SELECT
       NAME AS LEDGER_NAME,
       CHART_OF_ACCOUNTS_ID,
       MAX(GLB.LAST_UPDATE_DATE)
    FROM
       APPS.GL_LEDGERS GL,
       APPS.GL_BALANCES GLB
    WHERE
       GL.LEDGER_ID = GLB.LEDGER_ID
    GROUP BY
       NAME,
       CHART_OF_ACCOUNTS_ID
    ORDER BY
       3 DESC;

Get the List of Segments by Chart of Accounts ID

Run the following SQL against your Oracle EBS database to get the SEGMENT_NUMs and FLEX_VALUE_SET_IDs for the GL# KFF:

    SELECT
       FIFS.ID_FLEX_NUM AS CHART_OF_ACCOUNT_ID,
       FIFS.SEGMENT_NUM,
       FIFS.SEGMENT_NAME,
       FIFS.APPLICATION_COLUMN_NAME,
       FIFS.FLEX_VALUE_SET_ID,
       LISTAGG(FSAV.SEGMENT_ATTRIBUTE_TYPE, ',') WITHIN GROUP(
           ORDER BY
               FSAV.SEGMENT_ATTRIBUTE_TYPE
       ) AS QUALIFIERS
    FROM
       APPS.FND_ID_FLEX_SEGMENTS FIFS
       LEFT JOIN APPS.FND_SEGMENT_ATTRIBUTE_VALUES FSAV ON FIFS.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
       AND FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
       AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
       AND FSAV.ATTRIBUTE_VALUE = 'Y'
    WHERE
       FIFS.ID_FLEX_CODE = 'GL#'
    GROUP BY
       FIFS.ID_FLEX_NUM,
       FIFS.SEGMENT_NUM,
       FIFS.SEGMENT_NAME,
       FIFS.APPLICATION_COLUMN_NAME,
       FIFS.FLEX_VALUE_SET_ID
    ORDER BY
       FIFS.ID_FLEX_NUM,
       FIFS.SEGMENT_NUM

Configure the Segments to Use for Reporting

The Oracle EBS blueprint has three segment value tables in the EBS_FIN_COMMON schema: GL_ACCOUNT_SEG, GL_COSTCENTER_SEG, and GL_BALANCING_SEG. You can use these tables if they match the segments in your Oracle EBS instance.

Update the Business View Labels for the Segments

Here are the steps to update the segments in the CodeCombination business view of the GeneralLedger business schema:

  • In the Navigation bar, select Business Schema.
  • In the Business Schema Manager, in the list view of business schemas, select the GeneralLedger business schema to open it.
  • In the Business Schema Designer, select the CodeCombination business view to expand it.
  • Verify the segment labels correspond to your requirements. Update the labels as necessary:

    • Select Edit (vertical ellipsis icon).
    • Enter the new values in Label for SEGMENT1, SEGMENT2, or SEGMENT3.
  • In the Action bar, select Done.

Perform a Full Load of the Oracle EBS Common Physical Schemas

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • Select each of the tables below. In the Action bar, select Load → Load now → Full.

    • EBS_ITEM_COMMON
    • EBS_CAL_COMMON
    • EBS_FND_COMMON
    • EBS_HR_COMMON
    • EBS_FIN_COMMON
    • EBS_PARTY_COMMON

To configure a specific EBS module, refer to the list of Financial Modules or Supply Chain Planning Modules and select the module for configuration details.

Important

Monitor your Incorta cluster disk space as you load the EBS schemas. For large tables, to limit full data loads, add a SQL clause to the table query: WHERE CREATION_DATE > <DATE>.

EBS Blueprint Security Groups

The EBS Blueprint tenant includes the following security groups:

Group Role(s)
ReportAdmins Analyze User, Schema Manager, User, User Manager
ReportUsers Individual Analyzer, User

Apply Optional Customizations to the Oracle EBS Blueprints

Following are common Oracle EBS customizations that you can optionally incorporate into the blueprint:

Configure the Initial Extract Date

Optionally configure the $$Default_InitialExtractDate global variable to store the initial extract date, or rolling period. This will limit rows for large transaction tables during full load.

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Global Variables tab.
  • In the list view of global variables, select the Default_InitialExtractDate global variable to open it.
  • Update the Value.
  • Select Ok.

Here are the steps to update the SQL query for the tables you would like to limit:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • In the list view of physical schemas, select a schema to open it.
  • In the Schema Designer, in the Tables section, select a table to open it.
  • In the Table Editor, select the EBS SQL icon to open the Data Source dialog.
  • In the Data Source dialog,

    • In Query, add WHERE creation_date >= $$Default_InitialExtractDate as shown below.
    • Select Done.
  • Select Save.
  • In the Action bar, select Done.
Note

The $$Default_InitialExtractDate global variable should be used to limit transaction tables only. If you apply a limit to reference data, it may lead to missing foreign keys.

Configure Additional Segments for the General Ledger Account Key Flexfields

The required configurations section above outlined the steps to configure the GL account to reflect your implementation of the GL# KFFs when you use only three segments in your Oracle EBS instance. This section outlines the steps to configure the GL account to reflect your implementation of the GL# KFFs when you use more than three segments.

Determine the Chart of Accounts to Use for Reporting

Run the SQL query below if the chart of accounts to use for reporting are not already known:

    SELECT
       NAME AS LEDGER_NAME,
       CHART_OF_ACCOUNTS_ID,
       MAX(GLB.LAST_UPDATE_DATE)
    FROM
       APPS.GL_LEDGERS GL,
       APPS.GL_BALANCES GLB
    WHERE
       GL.LEDGER_ID = GLB.LEDGER_ID
    GROUP BY
       NAME,
       CHART_OF_ACCOUNTS_ID
    ORDER BY
       3 DESC;

Get the List of Segments by Chart of Accounts ID

Run the following SQL against your Oracle EBS database to get the SEGMENT_NUMs and FLEX_VALUE_SET_IDs for the GL# KFF:

    SELECT
       FIFS.ID_FLEX_NUM AS CHART_OF_ACCOUNT_ID,
       FIFS.SEGMENT_NUM,
       FIFS.SEGMENT_NAME,
       FIFS.APPLICATION_COLUMN_NAME,
       FIFS.FLEX_VALUE_SET_ID,
       LISTAGG(FSAV.SEGMENT_ATTRIBUTE_TYPE, ',') WITHIN GROUP(
           ORDER BY
               FSAV.SEGMENT_ATTRIBUTE_TYPE
       ) AS QUALIFIERS
    FROM
       APPS.FND_ID_FLEX_SEGMENTS FIFS
       LEFT JOIN APPS.FND_SEGMENT_ATTRIBUTE_VALUES FSAV ON FIFS.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
       AND FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
       AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
       AND FSAV.ATTRIBUTE_VALUE = 'Y'
    WHERE
       FIFS.ID_FLEX_CODE = 'GL#'
    GROUP BY
       FIFS.ID_FLEX_NUM,
       FIFS.SEGMENT_NUM,
       FIFS.SEGMENT_NAME,
       FIFS.APPLICATION_COLUMN_NAME,
       FIFS.FLEX_VALUE_SET_ID
    ORDER BY
       FIFS.ID_FLEX_NUM,
       FIFS.SEGMENT_NUM

An example will demonstrate the remaining steps involved. In this example, we will add an additional segment for CHART_OF_ACCOUNT_ID 50194, which has 4 segments:

SEGMENT1 → Company (qualifying segment)
SEGMENT2 → Department (qualifying segment)
SEGMENT3→ Account (qualifying segment)
SEGMENT4→ Intercompany (qualifying segment)

Configure the Segments to Use for Reporting

The Oracle EBS blueprint has three segment value tables in the EBS_FIN_COMMON schema: GL_ACCOUNT_SEG, GL_COSTCENTER_SEG, and GL_BALANCING_SEG. You can use these tables if they match the segments in your Oracle EBS instance. The fourth segment, Intercompany, will be added since it is needed for reporting.

Add a new table for the additional segment

Here are the steps to create a new table for the Intercompany segment:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • In the list view of physical schemas, select the EBS_FIN_COMMON schema to open it.
  • In the Schema Designer, in the Action bar, select +NewTableSQL Database.
  • In the Data Source dialog, enter the following properties

    • For Data Source, select EBS.
    • Enable Incremental.
    • For Query, enter

      SELECT
         FIFS.ID_FLEX_NUM,
         B.ID_FLEX_STRUCTURE_NAME CHART_OF_ACCOUNT,
         FIFS.APPLICATION_COLUMN_NAME,
         FIFS.SEGMENT_NAME,
         FFV.FLEX_VALUE CC_SEG_VALUE,
         FFV.DESCRIPTION,
         GCC.CODE_COMBINATION_ID
      FROM
         APPS.FND_ID_FLEX_SEGMENTS FIFS,
         APPS.FND_FLEX_VALUES_VL FFV,
         APPS.FND_SEGMENT_ATTRIBUTE_VALUES FSAV,
         APPS.GL_CODE_COMBINATIONS GCC,
         APPS.FND_ID_FLEX_STRUCTURES_TL B
      WHERE
         FIFS.ID_FLEX_CODE = 'GL#'
         AND FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
         AND FIFS.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
         AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_INTERCOMPANY'
         AND FSAV.ATTRIBUTE_VALUE = 'Y'
         AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
         AND FIFS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
         AND DECODE(
             FIFS.APPLICATION_COLUMN_NAME,
             'SEGMENT1',
             GCC.SEGMENT1,
             'SEGMENT2',
             GCC.SEGMENT2,
             'SEGMENT3',
             GCC.SEGMENT3,
             'SEGMENT4',
             GCC.SEGMENT4,
             'SEGMENT5',
             GCC.SEGMENT5
         ) = FFV.FLEX_VALUE
         AND GCC.CHART_OF_ACCOUNTS_ID = FIFS.ID_FLEX_NUM
         AND FIFS.APPLICATION_ID = B.APPLICATION_ID
         AND FIFS.ID_FLEX_CODE = B.ID_FLEX_CODE
         AND B.LANGUAGE = 'US'
         AND FIFS.ID_FLEX_NUM = B.ID_FLEX_NUM
    • For Update Query, enter

      SELECT
         FIFS.ID_FLEX_NUM,
         B.ID_FLEX_STRUCTURE_NAME CHART_OF_ACCOUNT,
         FIFS.APPLICATION_COLUMN_NAME,
         FIFS.SEGMENT_NAME,
         FFV.FLEX_VALUE CC_SEG_VALUE,
         FFV.DESCRIPTION,
         GCC.CODE_COMBINATION_ID
      FROM
         APPS.FND_ID_FLEX_SEGMENTS FIFS,
         APPS.FND_FLEX_VALUES_VL FFV,
         APPS.FND_SEGMENT_ATTRIBUTE_VALUES FSAV,
         APPS.GL_CODE_COMBINATIONS GCC,
         APPS.FND_ID_FLEX_STRUCTURES_TL B
      WHERE
         FIFS.ID_FLEX_CODE = 'GL#'
         AND FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
         AND FIFS.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
         AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_INTERCOMPANY'
         AND FSAV.ATTRIBUTE_VALUE = 'Y'
         AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
         AND FIFS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
         AND DECODE(
            FIFS.APPLICATION_COLUMN_NAME,
            'SEGMENT1',
            GCC.SEGMENT1,
            'SEGMENT2',
            GCC.SEGMENT2,
            'SEGMENT3',
            GCC.SEGMENT3,
            'SEGMENT4',
            GCC.SEGMENT4,
            'SEGMENT5',
            GCC.SEGMENT5
        ) = FFV.FLEX_VALUE
        AND GCC.CHART_OF_ACCOUNTS_ID = FIFS.ID_FLEX_NUM
        AND FIFS.APPLICATION_ID = B.APPLICATION_ID
        AND FIFS.ID_FLEX_CODE = B.ID_FLEX_CODE
        AND B.LANGUAGE = 'US'
        AND FIFS.ID_FLEX_NUM = B.ID_FLEX_NUM
        AND GCC.LAST_UPDATE_DATE > ?
                                                                                                  ```
  • Select Add.
  • Select Yes for the following warning message: This table does not have a key defined. If you run an incremental load the new data will be added to the existing data, without applying any updates. Are you sure this is what you are trying to do?
  • For table name, enter GL_INTERCOMPANY_SEG.
  • In the Table Editor, for the CODE_COMBINATION_ID column, for Function, select key.
  • Select Done.

Load the New Values Table for the Additional Segment

Perform a full load of the GL_INTERCOMPANY_SEG table. Select the down arrow at the far right of the GL_INTERCOMPANY_SEG and select Load Table.

Create Joins from the Transaction Tables to the New Segment Table

Following are the transaction tables joined to each of the existing segment tables:

EBS_AP.AP_EXP_REPORT_DISTS_ALL
EBS_AP.AP_EXPENSE_REPORT_HEADERS_ALL
EBS_AP.AP_EXPENSE_REPORT_LINES_ALL
EBS_AP.AP_PAYMENT_DISTRIBUTIONS_ALL
EBS_AP.AP_RECURRING_PAYMENTS_ALL
EBS_AR.AR_CASH_RECEIPT_HISTORY_ALL
EBS_AR.AR_DISTRIBUTIONS_ALL
EBS_AR.AR_RECEIVABLE_APPLICATIONS_ALL
EBS_AR.AR_RECEIVABLES_TRX_ALL
EBS_AR.RA_CUST_TRX_LINE_GL_DIST_ALL
EBS_AR_MV.GL_CODES_MV
EBS_FIN_COMMON.AP_BANK_ACCOUNTS_ALL
EBS_INV.MTL_MATERIAL_TRANSACTIONS
EBS_PO.PO_REQ_DISTRIBUTIONS_ALL
EBS_PO.RCV_PARAMETERS
EBS_PO.RCV_SHIPMENT_LINES

For each transaction table listed above, create a join to GL_INTERCOMPANY_SEG. Here are the steps for the first transaction table, EBS_AP.AP_EXP_REPORT_DISTS_ALL.

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • In the list view of physical schemas, select the EBS_AP schema to open it.
  • In the Schema Designer, in the Tables section, select the AP_EXP_REPORT_DISTS_ALL transaction table to open it.
  • In the Action bar, select +Newjoin (as child).
  • For Child, for Select a column, select CODE_COMBINATION_ID.
  • For Parent

    • For Select Schema, select EBS_FIN_COMMON.
    • For Select Table, select GL_INTERCOMPANY_SEG.
    • For Select a column select CODE_COMBINATION_ID.

Update the Business View Label for the Additional Segment

Update the segments in the CodeCombination view of the GeneralLedger business schema with the following steps:

  • In the Navigation bar, select Business Schema.
  • In the Business Schema Manager, in the list view of business schemas, select the GeneralLedger business schema to open it.
  • In the Business Schema Designer, select the CodeCombination business view to expand it.
  • Verify the segment labels correspond to your requirements. Update the labels as necessary:

    • Select Edit (vertical ellipsis icon).
    • Enter Intercompany (SEGMENT4) in Label for SEGMENT4.
  • In the Action bar, select Done.

Create a New Business View for the Additional Segment

Create a new Business View called InterCompany in the GeneralLedger business schema with the following steps:

  • In the Navigation bar, select Business Schema.
  • In the Business Schema Manager, in the Action bar, select +NewAdd New View.
  • Drag and drop G_INTERCOMPANY_SEG from the Data panel to the business schema view.
  • Name the Business View InterCompany.
  • In the Action bar, select Done.

Create New Hierarchies for the Additional Segment

Create a new InterCompany hierarchy table with the following steps:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • In the list view of physical schemas, select the EBS_FIN_COMMON schema to open it.
  • In the Schema Designer, in the Action bar, select +NewTableSQL Database.
  • In the Data Source dialog, enter the following properties

    • For Data Source, select EBS.
    • For Query, enter

      WITH FV AS (
         SELECT
             DISTINCT FLEX_VALUE_SET_ID,
             FLEX_VALUE,
             DESCRIPTION
         FROM
             APPS.FND_FLEX_VALUES_VL
         WHERE
             FLEX_VALUE_SET_ID = $$FLEX_VALUE_SET_ID
      )
      SELECT
         H.CHILD_FLEX_VALUE,
         H.PARENT_FLEX_VALUE,
         H.LAST_UPDATE_DATE,
         CD.DESCRIPTION CHILD_DESC,
         PD.DESCRIPTION PARENT_DESC
      FROM
         (
             SELECT
                 FLEX_VALUE_SET_ID,
                 CHILD_FLEX_VALUE,
                 PARENT_FLEX_VALUE,
                 LAST_UPDATE_DATE
             FROM
                 APPS.GL_SEG_VAL_NORM_HIERARCHY
             WHERE
                 FLEX_VALUE_SET_ID = $$FLEX_VALUE_SET_ID START WITH PARENT_FLEX_VALUE = 'PT' CONNECT BY NOCYCLE PARENT_FLEX_VALUE = PRIOR CHILD_FLEX_VALUE
         ) H
         LEFT OUTER JOIN FV CD ON CD.FLEX_VALUE_SET_ID = H.FLEX_VALUE_SET_ID
         AND CD.FLEX_VALUE = H.CHILD_FLEX_VALUE
         LEFT OUTER JOIN FV PD ON PD.FLEX_VALUE_SET_ID = H.FLEX_VALUE_SET_ID
         AND PD.FLEX_VALUE = H.PARENT_FLEX_VALUE
  • Select Add.
  • Select Yes for the following warning message: This table does not have a key defined. If you run an incremental load the new data will be added to the existing data, without applying any updates. Are you sure this is what you are trying to do?
  • For table name, enter InterCompany_Hierarchy.
  • Select Done.
Note

If the segment has multiple parents, a limited number of separate attributes or hierarchies can be created in Incorta.

Join the New Segment Hierarchy Table to the Segment Values Table

Here are the steps to create a join between InterCompany_Hierarchy and GL_CODE_COMBINATIONS.SEGMENT4:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • In the list view of physical schemas, select the EBS_FIN_COMMON schema to open it.
  • In the Schema Designer, in the Tables section, select the GL_CODE_COMBINATIONS table to open it.
  • In the Action bar, select +NewJoin.
  • For Child, for Select a column, select SEGMENT4.
  • For Parent

    • For Select Table, select InterCompany_Hierarchy.
    • For Select a column select CHILD_FLEX_VALUE.
  • Select Done.
  • In the Action bar, select +NewJoin.
  • For Child

    • For Select Table, select InterCompany_Hierarchy.
    • For Select a column, select PARENT_FLEX_VALUE.
  • For Parent

    • For Select Table, select InterCompany_Hierarchy.
    • For Select a column select CHILD_FLEX_VALUE.
  • Select Done.

Add the New Business View and Columns to a Blueprint Dashboard Insight

Here are the steps to add InterCompany or any of its columns to a blueprint dashboard insight:

  • In the Navigation bar, select Content.
  • Select a dashboard to open it.
  • For an insight on a dashboard tab, select Edit (pen icon).
  • In the Analyzer, in the Data panel, add the GeneralLedger business schema if it has not already been added.
  • In the Data panel, drag and drop InterCompany or any of its columns to a tray in the Insight panel.

Configure Asset Category and Asset Location Key Flexfields

If you are using Oracle Fixed Assets, you may have additional segments in the Asset Category and Asset Location KFFs, for which you need to add segment columns to the Asset Category and Asset Location business views of the Fixed Asset Details business schema.

Identify the Asset Category KFF Segments

Run the following SQL against your Oracle EBS database to get a list of the segments in the asset category flexfield:

SELECT
   FIFS.ID_FLEX_NUM AS CHART_OF_ACCOUNT_ID,
   FIFS.SEGMENT_NUM,
   FIFS.SEGMENT_NAME,
   FIFS.APPLICATION_COLUMN_NAME,
   FIFS.FLEX_VALUE_SET_ID,
   LISTAGG(FSAV.SEGMENT_ATTRIBUTE_TYPE, ',') WITHIN GROUP(
       ORDER BY
           FSAV.SEGMENT_ATTRIBUTE_TYPE
   ) AS QUALIFIERS
FROM
   APPS.FND_ID_FLEX_SEGMENTS FIFS
   LEFT JOIN APPS.FND_SEGMENT_ATTRIBUTE_VALUES FSAV ON FIFS.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
   AND FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
   AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
   AND FSAV.ATTRIBUTE_VALUE = 'Y'
WHERE
   FIFS.ID_FLEX_CODE = 'CAT#'
GROUP BY
   FIFS.ID_FLEX_NUM,
   FIFS.SEGMENT_NUM,
   FIFS.SEGMENT_NAME,
   FIFS.APPLICATION_COLUMN_NAME,
   FIFS.FLEX_VALUE_SET_ID
ORDER BY
   FIFS.ID_FLEX_NUM,
   FIFS.SEGMENT_NUM

Update the Asset Category KFF Segments in the Business View

Here are the steps to change the labels in the AssetCategory business views of the FixedAssetDetails business schema to reflect your implementation:

  • In the Navigation bar, select Business Schema.
  • In the Business Schema Manager, in the list view of business schemas, select the FixedAssetDetails business schema to open it.
  • In the Business Schema Designer, select the AssetCategory business view to expand it.
  • Update the labels to reflect the Asset Category KFF segment names.
  • In the Action bar, select Done.

Identify the Asset Location KFF Segments

Run the following SQL against your Oracle EBS database to get a list of the segments in the asset location flexfield:

SELECT
   FIFS.ID_FLEX_NUM AS CHART_OF_ACCOUNT_ID,
   FIFS.SEGMENT_NUM,
   FIFS.SEGMENT_NAME,
   FIFS.APPLICATION_COLUMN_NAME,
   FIFS.FLEX_VALUE_SET_ID,
   LISTAGG(FSAV.SEGMENT_ATTRIBUTE_TYPE, ',') WITHIN GROUP(
       ORDER BY
           FSAV.SEGMENT_ATTRIBUTE_TYPE
   ) AS QUALIFIERS
FROM
   APPS.FND_ID_FLEX_SEGMENTS FIFS
   LEFT JOIN APPS.FND_SEGMENT_ATTRIBUTE_VALUES FSAV ON FIFS.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
   AND FIFS.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
   AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
   AND FSAV.ATTRIBUTE_VALUE = 'Y'
WHERE
   FIFS.ID_FLEX_CODE = 'LOC#'
GROUP BY
   FIFS.ID_FLEX_NUM,
   FIFS.SEGMENT_NUM,
   FIFS.SEGMENT_NAME,
   FIFS.APPLICATION_COLUMN_NAME,
   FIFS.FLEX_VALUE_SET_ID
ORDER BY
   FIFS.ID_FLEX_NUM,
   FIFS.SEGMENT_NUM

Update the Asset Location KFF Segments in the Business View

Here are the steps to change the labels in the AssetLocation business views of the FixedAssetDetails business schema to reflect your implementation:

  • In the Navigation bar, select Business Schema.
  • In the Business Schema Manager, in the list view of business schemas, select the FixedAssetDetails business schema to open it.
  • In the Business Schema Designer, select the AssetLocation business view to expand it.
  • Update the labels to reflect the Asset Location KFF segment names.
  • In the Action bar, select Done.

Configure Descriptive Flexfields

You can configure Descriptive Flexfields (DFFs) you would like to use for reporting.

Get the list of DFF segments used by customer

Run the following SQL against your Oracle EBS database to identify the DFFs:

SELECT
   DFF.APPLICATION_ID APPLICATION_ID,
   DFF.DESCRIPTIVE_FLEXFIELD_NAME FLEXFIELD_CODE,
   DFFTL.TITLE FLEXFIELD_NAME,
   DFFCTX.DESCRIPTIVE_FLEX_CONTEXT_CODE CONTEXT_CODE,
   DFF.CONTEXT_COLUMN_NAME CONTEXT_COLUMN_NAME,
   DFFCOL.APPLICATION_COLUMN_NAME COLUMN_CODE,
   DFFCOLTL.FORM_LEFT_PROMPT COLUMN_NAME,
   DFFCOL.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID,
   DFF.APPLICATION_TABLE_NAME APPLICATION_TABLE_NAME
FROM
   APPS.FND_DESCR_FLEX_COLUMN_USAGES DFFCOL,
   APPS.FND_DESCRIPTIVE_FLEXS DFF,
   APPS.FND_DESCRIPTIVE_FLEXS_TL DFFTL,
   APPS.FND_DESCR_FLEX_CONTEXTS DFFCTX,
   APPS.FND_DESCR_FLEX_CONTEXTS_TL DFFCTXTL,
   APPS.FND_DESCR_FLEX_COL_USAGE_TL DFFCOLTL
WHERE
   (
       DFFCTXTL.APPLICATION_ID = DFFCTX.APPLICATION_ID
       AND DFFCTXTL.DESCRIPTIVE_FLEXFIELD_NAME = DFFCTX.DESCRIPTIVE_FLEXFIELD_NAME
       AND DFFCTXTL.DESCRIPTIVE_FLEX_CONTEXT_CODE = DFFCTX.DESCRIPTIVE_FLEX_CONTEXT_CODE
   )
   AND (
       DFF.APPLICATION_ID = DFFTL.APPLICATION_ID
       AND DFF.DESCRIPTIVE_FLEXFIELD_NAME = DFFTL.DESCRIPTIVE_FLEXFIELD_NAME
   )
   AND (
       DFF.APPLICATION_ID = DFFCOL.APPLICATION_ID
       AND DFF.DESCRIPTIVE_FLEXFIELD_NAME = DFFCOL.DESCRIPTIVE_FLEXFIELD_NAME
   )
   AND (
       DFFCOL.APPLICATION_ID = DFFCTX.APPLICATION_ID
       AND DFFCOL.DESCRIPTIVE_FLEXFIELD_NAME = DFFCTX.DESCRIPTIVE_FLEXFIELD_NAME
       AND DFFCOL.DESCRIPTIVE_FLEX_CONTEXT_CODE = DFFCTX.DESCRIPTIVE_FLEX_CONTEXT_CODE
   )
   AND (
       DFFCOL.APPLICATION_ID = DFFCOLTL.APPLICATION_ID
       AND DFFCOL.DESCRIPTIVE_FLEXFIELD_NAME = DFFCOLTL.DESCRIPTIVE_FLEXFIELD_NAME
       AND DFFCOL.DESCRIPTIVE_FLEX_CONTEXT_CODE = DFFCOLTL.DESCRIPTIVE_FLEX_CONTEXT_CODE
       AND DFFCOL.APPLICATION_COLUMN_NAME = DFFCOLTL.APPLICATION_COLUMN_NAME
   )
   AND (
       DFFCOL.APPLICATION_ID = 222
       AND DFFCOL.DESCRIPTIVE_FLEXFIELD_NAME LIKE 'HZ_PARTIES%'
   )
   AND (DFFCTX.ENABLED_FLAG = 'Y')
   AND (DFFCTXTL.LANGUAGE = DFFCOLTL.LANGUAGE)
   AND (DFFCTXTL.LANGUAGE = DFFTL.LANGUAGE)
   AND (DFFTL.LANGUAGE IN ('US'))

In the example above, HZ_PARTIES has two DFFs in the Telco context: Connection and IMEI. This example will illustrate how you can add DFFs to the Oracle EBS blueprint.

Add Custom Columns to the Physical Schema

To add the DFFs to the HZ_PARTIES table of the EBS_PARTY_COMMON schema, you can modify the Query and Update Query SQL as follows:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • In the list view of physical schemas, select the EBS_PARTY_COMMON schema to open it.
  • In the Schema Designer, in the Tables section, select the HZ_PARTIES table to open it.
  • In the Table Editor, select the EBS SQL icon to open the Data Source dialog.
  • In the Data Source dialog

    • In Query, add ATTRIBUTE11 and ATTRIBUTE12 to the end of the SELECT statement.
    • In Update Query, add ATTRIBUTE11 and ATTRIBUTE12 to the end of the SELECT statement.
  • Select Validate.
  • Select Done.

Query

Update Query

Load Data into the Custom Columns

Here are the steps to load the HZ_PARTIES table and preview the data:

  • Select the down arrow at the far right of the HZ_PARTIES and select Load Table.
  • Select the HZ_PARTIES table to open it.
  • In the Table Editor, in the Columns section, select Preview Data to view a sample of the data in the custom columns.

Add the Custom Columns to the Business Schema Views

Here are the steps to add the custom columns to the OrderCustomerInformation business view:

  • In the Navigation bar, select Business Schema.
  • In the Business Schema Manager, select the OM_SalesOrders business schema to open it.
  • In the Business Schema Designer, select the OrderCustomerInformation business view to open it.
  • Drag and drop Attribute11 and Attribute12 from the Data panel to the business schema view.
  • Change the Name and Label of Attribute11 to Connection.
  • Change the Name and Label of Attribute12 to IMEI.
  • In the Action bar, select Done.

If a single DFF has multiple contexts, use a formula column in the business view to evaluate the segment column based on attribute category.

Here is an example of an HZ_PARTIES DFF with multiple contexts, Life Sciences Customer and Physician. The query below is used to identify the DFF contexts:

SELECT
DESCRIPTIVE_FLEXFIELD_NAME,
DESCRIPTIVE_FLEX_CONTEXT_CODE,
    APPLICATION_COLUMN_NAME,
    END_USER_COLUMN_NAME
FROM
    FND_DESCR_FLEX_COL_USAGE_VL
WHERE
    DESCRIPTIVE_FLEXFIELD_NAME LIKE 'HZ_PARTIES%';

In this example, you can use a case statement to determine the context:

  • Drag and drop New Formula to the business view.
  • Select Set Formula.
  • In the Formula Builder, enter the following:

    CASE(
    EBS_PARTY_COMMON.HZ_PARTIES.ATTRIBUTE_CATEGORY = "Life Sciences Customer",
    EBS_PARTY_COMMON.HZ_PARTIES.ATTRIBUTE11,
    EBS_PARTY_COMMON.HZ_PARTIES.ATTRIBUTE12
    )
  • Select Validate & Save.
  • In the Action bar, select Done.

Add the New Custom Columns to a Blueprint Dashboard Insight

Here are the steps to add the Connection and IMEI columns to a blueprint dashboard insight:

  • In the Navigation bar, select Content.
  • Select a dashboard to open it.
  • For an insight on a dashboard tab, select Edit (pen icon).
  • In the Analyzer, in the Data panel, add the OM_SalesOrders business schema if it has not already been added.
  • In the Data panel, drag and drop Connection or IMEI to a tray in the Insight panel.

Modify Currency Conversion Logic

You can modify the currency conversion logic in the business schemas, as needed. For example, here are the steps to modify the AvgRate formula in the Payments business view of the BillingAndTax business schema:

  • In the Navigation bar, select Business Schema.
  • In the Business Schema Manager, select the BillingAndTax business schema to open it.
  • In the Business Schema Designer, for the Payments business view, select Edit (vertical ellipsis icon).
  • For AvgRate, select Change Formula:

    • In the Formula Builder, modify the formula as necessary.
    • Select Validate & Save.
  • In the Action bar, select Done.

Configure Seeded Session Variables

Configure the following seeded session variables that are used to default values in dashboard prompts:

Variable Name Description Type Query
Default_Book_Type_code This variable is used to provide a default fixed asset book type Session SELECT 'OPS CORP' FROM dual
Default_Global_Currency This variable is used to provide a default currency Session query('USD')
Default_Exch_Rate_Type This variable is used to provide a default exchange rate type Session query('Corporate')
Default_Fiscal_Year This variable is used to provide a default fiscal year in the financial dashboards Session query(2010)
Default_Ledger This variable is used to provide a default ledger value in the financial dashboards Session SELECT 'Vision Operations (USA)' FROM dual
Default_Period This variable is used to provide a default fiscal period in the financial dashboards Session SELECT 'Nov-09' FROM dual
Default_Period_2 This variable is used to provide a default fiscal period in the financial dashboards Session SELECT 'Sep-09' FROM dual
Default_Start_Date This variable is used to provide a default start date Session query(2007)

Here are the steps to configure the variables:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Session Variables tab.
  • Select Edit (pen icon) for the session variable you would like to configure.
  • In the Internal Variable dialog

    • Select Query to open the Formula Builder.
    • Update the query as necessary.
    • Select Done.
    • Select Save.

Configure Data Security

Configure additional data security as required. There is a seeded session variable for org based data security named ORG_IDS_EBS_EXAMPLE.

For example, if you want to secure RA_CUSTOMER_TRX_ALL using org security, add a table security filter as follows:

or($user='admin',inlist(EBS_AR.RA_CUSTOMER_TRX_ALL.ORG_ID,$ORG_IDS_EBS_EXAMPLE))

If any other form of data security is required, refer to Data Security Using Session Variables.

Additional Information

Multi-Currency

Out of the box, Incorta supports amounts in Entered and Accounted currency. To support other global currencies, such as USD, for the multi-ledger use case, use the EBS_FIN_COMMON.GL_DAILY_RATES table and customize it as required. Perform a lookup to this table to get the exchange rate and multiply it with the base amount. For an example, refer to one of the following fields in the CollectionsManager.Payments schema:

  • Amount Due Original (AR Global)
  • Amount Due Original (Revenue Global)
  • Amount Due Remaining (AR Global)
  • Amount Due Remaining (Revenue Global)

Foundation (FND) Lookups

You can support FND_LOOKUPS by using a lookup() function in a formula field so that you do not need to add a join to the table. For example:

lookup(EBS_FND_COMMON.FND_LOOKUP_VALUES.MEANING, EBS_FND_COMMON.FND_LOOKUP_VALUES.LOOKUP_TYPE,'TRANSFER STATUS', EBS_FND_COMMON.FND_LOOKUP_VALUES.LOOKUP_CODE,EBS_PA.PA_COST_DISTRIBUTION_LINES_ALL.TRANSFER_STATUS_CODE , EBS_FND_COMMON.FND_LOOKUP_VALUES.LANGUAGE,'US')

Calendars

The Gregorian calendar is supported with a join to the EBS_CAL_COMMON.GREGORIAN_CALENDAR table. This table is loaded from the Date_US.csv file. The fiscal calendar is supported with a join to the EBS_CAL_COMMON.GL_DATE table.

Many to Many Joins

To support many to many joins involving a parent table with two child tables, you can create a bridge table using a materialized view. The bridge table will contain IDs for each of the three tables, which will be used to join the bridge table as a child to each of the three tables. For more information, refer to the Joins section of the Modeling Schemas, Tables, and Joins help article on the Incorta Community.

Multiple Chart of Accounts

For Cost Center, Balancing Segment, and natural accounts, you can view across the chart of accounts, as the data can be driven by a qualifier that is specific to a chart of accounts. You can union the values and join from the transaction table using the code combination ID.

For other segments, there are two approaches:

  • Create separate aliases to the code combination tables for each chart of accounts. This assumes that separate dashboards can be created for each chart of accounts. It works well when the reporting requirements are different for the main chart of accounts and local chart of accounts.
  • Use extract SQL or a materialized view on the top of the code combination table to flatten the segment data from each chart of accounts as separate columns. It is possible to manually put the segments that are shared across the chart of accounts together if they share the same value set.

© Incorta, Inc. All Rights Reserved.