Creating an Alternate Schema in an Enterprise Resource Planning (ERP) Source System

Prebuilt integrations to an Enterprise Resource Planning (ERP) source system in FDMEE use a basic filter for data extraction, and assume that appropriate security has been defined to enable access by FDMEE. In some environments, direct access to the source systems tables is prohibited because of system policies, or you want to define a source system filter that is more detailed than what is provided in the FDMEE user interface.

For example, in the Account Reconciliation Manager (ARM) you want to extract balance sheet accounts or active accounts only from the source system. To do this, create an alternate schema in the source system. This method provides a desired level of security, a different source system filter, or both.

  To create an alternate hierarchy for E-Business Suite and Peoplesoft (PSFT) systems:

  1. Create a new schema or user in the source system database.

  2. Grant SELECT or SELECT/INSERT access to the list of source tables used by FDMEE to the new schema.

    Refer to the source table list provided in Source System Tables Used by FDMEE.

  3. For E-Business Suite systems, create a new view named GL_CODE_COMBINATIONS, which includes the desired source filter.

    For Peoplesoft systems, create a view using the PS_LEDGER table.

    All columns from the source table must be included in the view.

  4. Create synonyms for all remaining source tables that FDMEE references from the source system in the new schema.

    Synonyms point to the base tables in the source system schema.

  5. Update Oracle Data Integrator (ODI) to use the new schema in the physical schema for the related data server.

    For example, the view created on the EBS GL_CODE_COMBINATIONS table may look like:

    CREATE VIEW GL_COMBINATIONS (SEGMENT1, SEGMENT2,…....)
SELECT SEGMENT1, SEGMENT2,…..
FROM APPS.GL_CODE_COMBINATIONS
WHERE “ADD FILTERS”

Any views created in the new schema or synonyms must use the exact same name as specified in Source System Tables Used by FDMEE. Because the table and view names are the same as the core schema, FDMEE can access the updated contents with a change to the schema specification in ODI.

For SAP or JDE source systems, change the filter definition in the adapter rather than creating an alternate schema.

Before making any changes, it is recommended that you contact Oracle support to review the process to ensure a smooth update to the system.