!!User Defined Columns (UDC)

!Features of User Defined Columns
||Pro||Con
|[Workflow|WORKFLOW] can access and update this data|Modification of the table structure is not supported
|[Date Sensitive|DATE SENSITIVE] changes will carry forward the values|Requires users to get out, and a restart of the application
|[Change Audit Logging|AUDIT LOGGING] is supported|Precise setup is needed, or the system may have problems
|Supported by [Object Security|IMOS] and [Form Object Security|IMFOS]|Requires access to the database
|[Translatable|TRANSLATION] to other languages|
|Natively read by report generators like Discoverer|
|More performant (faster) than [User Defined Fields|USER DEFINED FIELD]|
%%warning 
Note: UDC’s used in where clauses and find blocks, or as the 1st column of an LOV, can result in performance degradation.  UDC’s are meant to contain supplemental information.
%%
!!Steps to Set up a new User Defined Column

The following steps need to be followed to add a new column.   
! A. In Personality
# Have all users sign out of 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 Personality system

! B. In the Database 
In SQL*Plus as the P2K user: 
# Set up the session audit information\\{{BEGIN \\ P2K_PMSESSION.LOGIN('SQ','Modifying code in the database - ''put name here'''); \\ DBMS_SESSION.SET_ROLE(p2k_pmsec.smsr(user)); \\ END; \\ /}}
# 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.  
# Rebuild the workflow triggers:\\{{@SEED_CREATE_BIUD.sql\\@DB_RECOMPILE.sql\\}}
# Close the session\\ {{BEGIN \\ p2k_pmsession.logoff; \\ END;\\ /}}
You may now exit SQL*Plus
! C. Restart the application
# On your Oracle Application server (OAS or WebLogic) restart the container for this application.  If you have a separate HLAppResources instance in WebLogic, this must also be restarted
#* This is required because the column definitions are only loaded at start up.
# Sign in to the Personality 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)
* The name of the User Defined Column should not start with a number.  Depending on its planned usage in the application, this will cause errors that are not apparent with initial or form setup and usage (usercalcs). 


----
![Notes|Edit:Internal.USER_DEFINED_COLUMN]
[{InsertPage page='Internal.USER_DEFINED_COLUMN' default='Click to create a new notes page'}]