USER DEFINED COLUMN (UDC)
Back to current versionRestore this version

User Defined Columns (UDC)#

Features of User Defined Columns#

ProCon
Workflow can access and update this dataModification of the table structure is not supported
Date Sensitive changes will carry forward the valuesRequires users to get out, and a restart of the application
Change Audit Logging is supportedPrecise setup is needed, or the system may have problems
Supported by Object Security and Form Object SecurityRequires access to the database
Translatable to other languages
Natively read by report generators like Discoverer
More performant (faster) than User Defined Fields

Steps to Set up a new User Defined Column#

The following steps need to be followed to add a new column.

A. In Personality#

  1. Have all users sign out of system
  2. In IMCD form – add the new column with a column usage of “User Defined Column”. Ensure that Data Type, Domain Name and Length are all entered at a minimum. Review other columns for samples, as needed.
  3. Exit from the Personality system

B. In the Database #

In SQL*Plus as the P2K user:
  1. Set up the session audit information
    • Create session audits:
      BEGIN
      P2K_PMSESSION.LOGIN('SQ','Modifying code in the database - put name here');
      DBMS_SESSION.SET_ROLE(p2k_pmsec.smsr(user));
      END;
      /
  2. Add the column to the database
    ALTER TABLE P2K_xx_xxxxxx ADD (COLUMN_NAME COLUMN_TYPE);
    • where xx_xxxxxx is the table name
    • where COLUMN_NAME is the new column name
    • where COLUMN_TYPE is the data type of the new column: DATE, NUMBER, NUMBER(10,4), VARCHAR2(30), etc.
      Sample:
      ALTER TABLE P2K_HR_ASSIGNMENT_DETAILS ADD (ANNIVERSARY_MONTH VARCHAR2(16));
    • Multiple columns can be added using this script before running and moving onto the next stage of processing.
    • Run a db_recompile script (@db_recompile)
  3. Rebuild the workflow triggers. In SQLPlus as P2K user:

    • @SEED_CREATE_BIUD.pls
      @DB_RECOMPILE.sql
You may now exit SQL*Plus

C. Restart the application#

  1. In Oracle Enterprise Manager (OEM), restart the J2EE Server instance. This is required because the column definitions are only loaded at start up.
  2. Sign in to the eP system
  3. The new column is now available to be used in form definitions (IMFD) or workflow (IMUC) or for any other purpose

BEST PRACTICES for User Defined Columns#


Notes#

Click to create a new notes page