This page (revision-32) was last changed on 04-Jan-2023 15:27 by Kevin Higgs

This page was created on 26-Nov-2021 10:22 by rforbes

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
32 04-Jan-2023 15:27 3 KB Kevin Higgs to previous
31 03-Jan-2023 14:04 3 KB Kevin Higgs to previous | to last
30 15-Jun-2022 11:25 3 KB Kevin Higgs to previous | to last
29 26-Nov-2021 10:22 3 KB Kevin Higgs to previous | to last USER DEFINED COLUMN ==> USER DEFINED COLUMN (UDC)
28 26-Nov-2021 10:22 3 KB khiggs to previous | to last
27 26-Nov-2021 10:22 3 KB khiggs to previous | to last
26 26-Nov-2021 10:22 3 KB rforbes to previous | to last
25 26-Nov-2021 10:22 3 KB khiggs to previous | to last
24 26-Nov-2021 10:22 3 KB khiggs to previous | to last
23 26-Nov-2021 10:22 3 KB rforbes to previous | to last
22 26-Nov-2021 10:22 3 KB rforbes to previous | to last
21 26-Nov-2021 10:22 3 KB rforbes to previous | to last

Page References

Incoming links Outgoing links
USER DEFINED COLUMN (UDC)
USER DEFINED COLUMN (UDC)

Version management

Difference between version and

At line 1 changed one line
!!User Defined Columns (UDC)
!!User Defined Columns
At line 4 changed 11 lines
||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.
%%
* [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]
At line 16 changed 6 lines
!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
At line 23 removed 2 lines
!!Steps to Set up a new User Defined Column
At line 26 removed 14 lines
! 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;\\ /}}
You may now exit SQL*Plus
! B. In Personality
At line 41 changed 8 lines
#Log in as MASTER
# 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] and [Length|VALUE_LENGTH] are all entered at a minimum. Review other columns for samples, as needed.
# Exit from the Personality system
! 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 (Not as MASTER)
# 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
At line 50 removed 3 lines
----
![Notes|Edit:Internal.USER_DEFINED_COLUMN]
[{InsertPage page='Internal.USER_DEFINED_COLUMN' default='Click to create a new notes page'}]
This page (revision-32) was last changed on 04-Jan-2023 15:27 by Kevin HiggsTop