Skip to main content

Hubble Installation

Oracle Database and User

This install requires SYSTEM access to your Oracle Applications database and involves the creation or definition of a database user and a corresponding schema normally defined as HUBBLE. Using APPS or another user will typically result in an installation that will not work as intended.

Using the HR module in Hubble will require the use of the APPS user in addition to the HUBBLE user in order to allow Oracle HR Security to be upheld.

HR & Payroll Module

In order to enable security and allow the application to mirror EBS settings, Hubble uses the seeded Oracle Secure Views. Due to constraints within the security logic, these views can only be accessed correctly using the APPS schema. As a result, the HR and/or Payroll Module requires an HR Security connection in the profile that connects using the APPS schema.

Activate the HR and/or Payroll module for selection by following the below steps.

  1. If you are creating a new repository, you will need your standard connection to the Hubble schema plus an additional connection to the same database that uses the APPS user.

  2. The profile wizard on the Oracle Connectivity screen now has an additional field for the HR Security Connection that is used for both HR and Payroll. Here you will select the Connection with the APPS user.

  3. The HR and/or Payroll Module can now be selected in the Module Selection window of the profile wizard if the repository has an HR License.

Install Script

Install Script For Reporting in Hubble

The script creates a user/schema, necessitating whoever is running it to select a username, password, SYSTEM user and default and temporary tablespaces. A pre-existing username, TNS Admin entry and index creation are all optional entries.

There are various prompts when running the script.

  1. Enter the desired username and password:

  1. If you have a pre-existing username you can enter this username in this field to pick up any grants that have been applied (useful for DX upgrades); leave blank otherwise.

  1. Enter default and temporary tablespaces. If the below fields are left blank, then the SYSTEM and TEMP tablespaces will be used.

  1. Enter details for the user we will use to run the script:

The install script will perform various GRANT privileges on the newly-created database user, including granting permission to create views and synonyms. This script will also create some procedures and packages used in the Hubble application. Required Permissions are covered later in this guide.

  1. Enter Y/N to adding indexes. To improve performance at login, two indexes are created on FND tables:

    FND_TABLES_INSIGHT1 on FND_TABLES FND_COLUMNS_INSIGHT1 on FND_COLUMNS

  2. Enter Y/N to add indexes to improve the performance of Fixed Asset reports. IS_NDX_FA_TRANS_NULL on FA_BOOKS IS_NDX_FA_TRANS_DATE_ENT on FA_TRANSACTION_HEADERS

    IS_NDX_FA_DIST_TRANS & IS_NDX_FA_DIST_NULL on FA_DISTRIBUTION_HISTORY

Note: These indexes, while recommended, are not mandatory. The person who runs the script will have the option of not creating these via a prompt.

A breakdown of what the script does is detailed below.

Install Script Breakdown for Reporting in Hubble

Create user and grant privileges (Lines 1 to 70).

  1. Gather login information, such as the user name and name and password for system user.

  2. Check if the user we want to create exists. If it does not exist, create it.

  3. Grant basic connection privileges:

    1. Connect.

    2. Resource.

    3. Create View. We need to create some views in Administrator.

    4. Create Synonym. We need to create synonyms.

    Grant access to tables, views and procedures needed (Lines 70 to 1207)

  1. Definition of containers to store tables and/or procedures to grant access to:

    1. SELECT_COMMON – Grant select and create synonyms on EBS 11i and R12.

    2. SELECT_SYS_COMMON - Grant select and create synonyms on EBS 11i and R12 to tables/ views belonging to SYS.

    3. SELECT_11I – Grant select and create synonyms on EBS 11i only.

    4. SELECT_R12 - Grant select and create synonyms on EBS R12 only.

    5. ALL_COMMON – Create synonyms on both EBS versions.

    6. ALL_11I - Create synonyms on EBS 11i only (empty).

    7. ALL_R12 - Create synonyms on EBS R12 only.

    8. EXECUTE_COMMON – Grant execute on both servers.

    9. EXECUTE_11I – Grant execute on EBS 11i only (empty).

    10. EXECUTE_R12 – Grant execute on EBS R12 only.

  2. Logic to grant specific privileges to a small set of tables that require insert and/or delete access. These tables are:

    1. FND_LOG_MESSAGES (SELECT, INSERT) - used by FND_GLOBAL.APPS_INITIALIZE function to

      allows the correct security to be applied to the EBS User. My Oracle support document (822225.1).

    2. MO_GLOB_ORG_ACCESS_TMP (SELECT, INSERT, DELETE) - used as part of Multi Org Process. My Oracle support documents (462383.1 and 1564525.1).

    3. FND_MO_SP_PREFERENCES (SELECT, DELETE) - referenced by MO_GLOBAL package to perform DML operation on this table.

  3. Logic to read each of the containers, depending on the EBS version with we are running the script.

Note: Most of the GRANT ALL and GRANT EXECUTE objects come from How To Run the FND_GLOBAL.APPS_INITIALIZE Using A User Other Than APPS (Oracle Doc ID 822225.1). “FND_GLOBAL.APPS_INITIALIZE” helps us to initialize the database connection as the EBS front end would do. It initializes variables that affect views and security, among other things.

All Oracle documents referenced in this guide, such as Doc ID 822225.1, can be found in My Oracle Support.

Grant access to tables used by KFF (Key Flex Field) definitions (Lines 1208 to 1290)

  1. The method will run the SQL below to obtain the tables used by KFF:

    1. SELECT APPLICATION_TABLE_NAME

    2. FROM APPS.FND_FLEX_VALIDATION_TABLES

    3. WHERE FLEX_VALUE_SET_ID IN (

    4. SELECT FND_ID_FLEX_SEGMENTS.FLEX_VALUE_SET_ID

    5. FROM APPS.FND_ID_FLEX_SEGMENTS FND_ID_FLEX_SEGMENTS,

    6. APPS.FND_ID_FLEX_STRUCTURES FND_ID_FLEX_STRUCTURES,

    7. APPS.FND_FLEX_VALUE_SETS FND_FLEX_VALUE_SETS

    8. WHERE FND_ID_FLEX_SEGMENTS.ID_FLEX_NUM    = FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM AND FND_ID_FLEX_SEGMENTS.ID_FLEX_CODE IN ('GL#', 'LOC#', 'CAT#', 'KEY#', 'MCAT', 'MTLL', 'CT#') AND FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID =

      FND_ID_FLEX_SEGMENTS.FLEX_VALUE_SET_ID

    9. AND FND_FLEX_VALUE_SETS.VALIDATION_TYPE = 'F');

  2. Step through the resulting tables,` running GRANT SELECT ON table_name TO user_name. Grant access to the additional tables for which the “old user” has access to (Lines 1291 to 1316). Create a view for Table Discovery (Lines 1317 to 1350).

    1. GRANT SELECT ON ' || V_REC.TABLE_NAME

      Create indexes for Hubble (Lines 1351 to 1457)

    2. FND_TABLES_INSIGHT1 on FND_TABLES

    3. FND_COLUMNS_INSIGHT1 on FND_COLUMNS

    4. IS_NDX_FA_TRANS_NULL on FA_BOOKS

    5. IS_NDX_FA_TRANS_DATE_ENT on FA_TRANSACTION_HEADERS

    6. IS_NDX_FA_DIST_TRANS & IS_NDX_FA_DIST_NULL on FA_DISTRIBUTION_HISTORY

      Create functions and packages to be used when running Hubble (lines 1458 to END)

  3. Function GETLONGCOLUMN: Gets text within columns with LONG data type. The driver does not support it; it is only used for KFF.

  4. Function FINDLONGCOLUMNLENGTH: Gets the size of a LONG column to be able to read using GETLONGCOLUMN.

  5. Function SAFE_DIVIDE: Safe divide changes the divisor to 1 if it is 0, thus preventing an exception to be thrown.

  6. Function INITAPPS: Function used to initialize the database. This is the function which runs “FND_GLOBAL.APPS_INITIALIZE”.

All functions are created using the user created; we do not invade other users or system space.