!!User Defined Columns !Features of User Defined Columns * [Workflow|WORKFLOW] can access and update this data * [Date Sensitive|DATE SENSITIVE] changes will carry forward the values * [Change Audit Logging|DETAILED CHANGE AUDITING] is supported * Supported by [Object Security|IMOS] and [Form Object Security|IMFOS] * [Translatable|TRANSLATION] to other languages * Natively read by report generators like Discoverer * Faster than [User Defined Fields|USER DEFINED FIELD] !Steps to Set up a new User Defined Column The following steps need to be followed to add a new column. # Have all users sign out of system # Create the new column on the table. In SQL*Plus as the P2K user: #* 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}}) # Sign into the eP system # In IMCD form – add the new column with a [column usage|COLUMN_USAGE] of “User Defined Column”. Ensure that [Data Type|DATA_TYPE], [Domain Name|DOMAIN_NAME] and [Length|VALUE_LENGTH] are all entered at a minimum. Review other columns for samples, as needed. # Exit from the eP system # Rebuild the workflow triggers. In SQLPlus as P2K user: #* {{@seed_create_biud.pls}} #* You may now exit SQL*Plus # In Oracle Enterprise Manager (OEM), restart the J2EE Server instance. This is required because the column definitions are only loaded at start up. # Sign in to the eP system # 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 * Create the Column on IMCD with a number range in excess of 9000 * Identify the column in the database with a name prefix that is unique to your organization (.e.g BEN_DUE_DATE rather than just DUE_DATE) * User Defined Columns are recommended over User Defined Fields if you are going to use them for __any__ processing (where clauses, workflow, approvals, loading, etc.). If you are just storing data to be reported on, user defined fields may be sufficient to the task. * Lexicons that are associated to a User Defined Column must be prefixed with "X_" (.e.g X_CUST_VALUE rather than CUST_VALUE) ---- ![Notes|Edit:Internal.USER_DEFINED_COLUMN] [{InsertPage page='Internal.USER_DEFINED_COLUMN' default='Click to create a new notes page'}]