User Defined Columns (UDC)#
Features of User Defined Columns#
| Pro | Con |
|---|---|
| Workflow can access and update this data | Modification of the table structure is not supported |
| Date Sensitive changes will carry forward the values | Requires users to get out, and a restart of the application |
| Change Audit Logging is supported | Precise setup is needed, or the system may have problems |
| Supported by Object Security and Form Object Security | Requires access to the database |
| Translatable to other languages | |
| Natively read by report generators like Discoverer | |
| More performant (faster) than User Defined Fields |
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.
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).
Steps to Set up a new User Defined Column#
The following steps need to be followed to add a new column.
A. 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.
- Recompile the database:
@DB_RECOMPILE.sql - Rebuild the workflow triggers:
@SEED_CREATE_BIUD.sql - Close the session
BEGIN
p2k_pmsession.logoff;
END;
/
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
B. In Personality#
- Have all users sign out of system
- 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.
- Exit from the Personality system
