B Setting Up Multiple Path Codes for Auditing and Electronic Signatures

This appendix describes how to set up multiple EnterpriseOne path codes for auditing and electronic signatures in support of the FDA 21 CFR Part 11 regulation. It contains the following topics:

B.1 Understanding Auditing and Electronic Signatures in Multiple EnterpriseOne Path Codes

You can configure auditing and electronic signatures in EnterpriseOne 9.0 for multiple path codes. This configuration enables you to mimic or test auditing in a "validation" path code prior to promoting changes (auditing or otherwise) to your production environment.

Important:

Auditing of system tables is NOT supported because system tables are shared across multiple path codes. You cannot enable auditing on a system table in one path code when the same system table is already enabled for auditing in another path code.

This appendix provides the technical requirements associated with this type of a configuration as well as the effects the configuration has on some of the standard EnterpriseOne administration processes, such as change control, data refreshes, ESU applications, and so forth.

Hereafter in this appendix, the "auditing and electronic signatures" functionality is referred to simply as "auditing" or "21 CFR Part 11."

B.2 Prerequisites

See the Prerequisites section in the main part of this guide for a full list of tasks that you must perform before configuring auditing for multiple path codes. In addition to the prerequisites, consider the following items before you proceed:

  • Before upgrading to a new version of JD Edwards EnterpriseOne or applying software updates that contains table changes, you should turn off auditing and electronic signature functions.

  • Table Conversions. You must disable auditing functionality when performing table conversions. See the Table Conversions section in this guide for more information.

  • ESUs. You must disable auditing functionality when applying ESUs.

B.3 Creating Required Oracle Table Spaces and Users

When implementing 21 CFR Part 11 across multiple path codes on an Oracle database, you need to create table spaces to house system/configuration tables used for 21 CFR Part 11. For each path code that you plan to configure, you should create at least four table spaces:

  • Two table spaces for storing Julian configuration tables.

  • Two tables spaces for storing non-Julian configuration tables.

To create the table spaces:

  1. Verify the disk space and location for the Julian and no-Julian table spaces for each path code to be audited.

  2. Connect your database via SQWLL Plus.

  3. Log into the database with SYSDBA User and perform the database operations to create table spaces, database users and owners, and grant rights to owners.

  4. Issue the create tablespace command:

    • Create tablespace XXSYT datafile 'YYYY' size 35M autoextend on

    • Create tablespace XXSYI datafile 'YYYY' size 35M autoextend on

    • Create tablespace XXNJT datafile 'YYYY' size 35M autoextend on

    • Create tablespace XXNJI datafile 'YYYY' size 35M autoextend on

      Where:

      • XX is a denotation of the path code for which you are setting up CFR Part 11, for example DV for Development, PY for Prototype, or PD for Production.

      • SY indicates the system or configuration tables with Julian dates.

      • NJ indicates system or configuration tables tat are non-Julian.

      • T indicates that this is the table space for storing table level information.

      • I indicates that this is the table space for storing index level information.

      • YYYY is the location and name of the database data file, for example: /u01/oracle/data/dvsyt01.dbf

  5. Repeat this process for each path code you would like to configure for auditing.

  6. Create the required Oracle database users and owners.

  7. While still logged into the database as a "System" user, issue the following commands:

    • Create user XXSY identified by password default tablespace XXSYT temporary tablespace TEMP quota unlimited on XXSYT quota unlimited on XXSYI

    • Create user XXNJ identified by password default tablespace XXNJT temporary tablespace TEMP quota unlimited on XXNJT quota unlimited on XXNJI

      Where:

      • XX is a denotation of the path code for which you are setting up for auditing, for example DV for Development, PY for Prototype, or PD for Production.

      • SY indicates the system or configuration tables with Julian dates.

      • NJ indicates system or configuration tables tat are non-Julian.

      • T indicates that this is the table space for storing table level information.

      • I indicates that this is the table space for storing index level information.

  8. Repeat this process for each path code you would like to configure for auditing.

    You have now successfully created the required database table spaces and users to configure auditing for your identified path codes.

B.4 Creating Julian and Non-Julian Database Data Sources for 21 CFR Part 11

When setting up auditing, you must configure a Julian database data source and non-Julian database data source for the auditing system/configuration tables. To do so:

  1. Sign in to the EnterpriseOne Windows client.

  2. In the Fast Path, enter GH9011.

  3. Drill down into Data Source Management.

  4. Create a Julian Database Data Source.

  5. Open the Database Data Sources application and locate the data source:

    1. Select System - 900.

    2. In the Data Source Name QBE (Query by example) field, enter System - 900.

    3. Click Find.

    4. Select System - 900 in the grid and click Copy.

    5. Enter System - XXSY in the Data Source Name field, where:

      XX denotes the path code for which you are configuring auditing, such as DV for Development, PY for Prototype, or PD for Production.

    6. Enter the database server name.

    7. In the Object Owner ID field, enter the database owner, for example: DVSY for Development Julian system and configuration tables.

    8. Click Advanced and make sure the "Use Julian Dates" check box is selected.

    9. Click OK.

    10. Select the Row menu and then select Database Sizing.

      This opens the Work with Oracle Database Object Sizing form.

    11. Click the Add button.

    12. In the Object Name field, enter Default.

    13. In the Table Space Name field, enter xxsyt, where:

      xx denotes the path code you are configuring for auditing.

    14. In the Index Space Name field, enter xxsyi, where:

      xx denotes the path code you are configuring for 21 CFR Part 11.

    15. Click OK.

    16. Click Cancel.

    17. Click OK.

  6. To configure the non-Julian data source:

    1. Open the Database Data Sources application.

    2. Enter System - 900 in the Data Source Name QBE (query by example) field.

    3. Click Find.

    4. Select the System - 900 data source.

    5. Click Copy.

    6. Enter System - XXNJ in the Data Source Name field, where:

      XX denotes the path code you are configuring 21 CFR Part 11 for.

      NJ denotes that the data source will contain non-Julian tables.

    7. Enter the database server name.

    8. Enter the object owner ID.

    9. Enter the database name.

    10. Click on Advanced.

    11. Clear the Use Julian Dates check box.

    12. Click OK.

    13. Click the Database Sizing Form exit.

    14. Click Add.

    15. In the Object Name field, enter default.

    16. In the Table Space Name field, enter xxnjt, where:

      xx denotes the path code you are configuring 21 CFR Part 11 for.

    17. In the Index Name field, enter xxnji, where:

      xx denotes the path code you are configuring 21 CFR Part 11 for.

    18. Click OK.

    19. Click Close.

    20. Click OK.

    Repeat these steps for each path code for which you are configuring 21 CFR Part 11 in System and all server maps.

    After defining the System - XXSY (Julian) and System - XXNJ (Non-Julian) data sources, generate the required OCM mappings.

    After creating OCM mappings and data sources, you must restart the EnterpriseOne Windows client for the mappings to take effect.

B.5 Generating Tables for 21 CFR Part 11

After defining the System - XXSY (Julian) and System - XXNJ (Non-Julian) data sources, generate the required tables for 21 CFR Part 11 into the new data sources.

  1. In the Fast Path, enter OMW.

  2. In OMW, locate your default project.

  3. Place the following tables in your default project:

    • F9500001

    • F9500002

    • F9500003

    • F9500004

    • F9500005

    • F9500006

    • F986112

  4. Perform a Get on all of these tables to ensure you have current specifications on your EnterpriseOne Windows client.

  5. Generate the tables into each of the data sources as listed:

    • System - XXSY data source

      F9500001

      F9500002

      F9500005

      F986112

    • System - XXNJ data source

      F9500003

      F9500004

      F9500006

  6. Repeat these steps for each path code being configured 21 CFR Part 11.

B.6 Creating OCM Mappings for 21 CFR Part 11

After generating the required tables to the System - XXSY (Julian) and System - XXNJ (Non-Julian) data sources, create the required Object Configuration Manager (OCM) mappings. To do so:

  1. In the Fast Path, enter OCM.

  2. Create OCM mappings for *PUBLIC in the System and Server Maps for all environments that leverage the path codes you are configuring for 21 CFR Part 11.

    1. Map the following tables to the System - XXSY data source:

      F9500001

      F9500002

      F9500005

      F986112

    2. Map the following tables to the System - XXNJ data source:

      F9500003

      F9500004

      F9500006

    3. Map the following objects to the LOCAL data source:

      B9500001

      B9500002

      B986112

    4. Map B9500005 to the server that executes the business functions for the path code you are configuring for 21 CFR Part 11.

  3. Repeat the preceding step for each path code you are defining for 21 CFR Part 11.

    Note:

    You must map the J environments as well.
  4. Activate all the mappings.

  5. Restart EnterpriseOne services for:

    • Enterprise, application, and logic servers.

    • HTML servers.

  6. To verify that the records have been successfully uploaded:

    1. On the EnterpriseOne Windows client machine, locate and double-click the dbtempates.exe file, located in the system\bin32 directory. For example:

      C:\e900\system\bin32\dbtempates.exe

    2. At the prompt to sign in to EnterpriseOne, enter your EnterpriseOne user credentials.

    3. Sign in to the path code and environment you have configured OCM mappings for, such as DV900.

      A DOS window appears informing you that records have been successfully uploaded.

    4. Press Enter to close the DOS window.

B.7 Configuring an Oracle Database for 21 CFR Part 11

For auditing to work properly, you must grant database privileges to the owners of the audit tables and audited tables such as PRODDTA, CRPDTA. To do so:

  1. Start SQL PLUCS and connect to the instance as SYS.

  2. For each owner, such as XXSY, XXNJ, PRODDTA, CRPDTA..., enter the following commands:

    • GRANT CREATE PROCEDURE TO owner

    • GRANT CREATE TRIGGER TO owner

    • GRANT SELECT ON V_$SESSION TO owner

    • GRANT CREATE TABLE TO owner

  3. Restart the application and HTML services.

B.8 Configuring Auditing and Electronic Signatures for Multiple Path Codes

Note the following items when configuring multiple path codes for 21 CFR Part 11:

  • When auditing is enabled in EnterpriseOne, the system records changes, additions, and deletions to an audit table (otherwise referred to as a shadow table) to provide a tracking history.

  • An audit table contains the columns used as the primary key of original table along with any other columns that you define.

  • For every change to an audited column in an original table (Fxxxx), the audit table records:

    • Field that was changed.

    • Previous value in the field.

    • New value in the field.

    • Date and time of the change.

    • Other fields in the table selected for auditing.

    • Other fields in the table selected for recording.

    • Form, application, and version name used to make the change.

    • The user ID that made the change.

The following task provides high-level steps for configuring auditing and electronic signatures in multiple path codes in EnterpriseOne. For more detailed instructions about any particular step, refer to the appropriate sections in the main chapters of this guide.

Important:

Auditing of system tables is NOT supported because system tables are shared across multiple path codes. You cannot enable auditing on a system table in one path code when the same system table is already enabled for auditing in another path code.
  1. Make sure that users are signed out of the EnterpriseOne installation that is configured for auditing and electronic signature approvals.

  2. Use the Configuration Application (P9500001) to add the path code to enable auditing and electronic signatures. See Setting Up an Audit and Electronic Signature Configuration for a Pathcode.

  3. Sign off and sign in to EnterpriseOne Windows client and restart EnterpriseOne services when 21 CFR Part 11 is enabled for one path code. Do this for all path codes for which 21 CFR Part 11 is enabled.

  4. After enabling auditing and electronic signatures for a path code, use the Configuration Application (P9500001) to enable auditing for a table. See Configuring a Table for Auditing.

    Important:

    Provide a different name for the A table when the same table is enabled for auditing on multiple path codes. For example, if F0101 is enabled for auditing, provide the following names:

    For DV900 path code - A0101DV

    For PD900 path code - A0101PD

    When you select a table, EnterpriseOne launches Table Design Aid (TDA) and automatically adds all primary key columns of the audited table to the audit table.

    If a default index does not exist, TDA creates one for the audit table and populates it with CFRGUID and B4ORAFTR.

  5. After it completes, save the changes to the audit table.

  6. Check in the audit table.

    Note:

    Before you copy the audit information, you have to promote the audit tables to the path code you are auditing.

    Remember to provide different names for audit tables when auditing multiple path codes, for example AXXX for one path code and DVXXX for another.