Derived columns can be added to tables, to further extend their utility, deriving their value from a PL/SQL function in the data base.  This allows the field to be displayed on screens as a display-only value.

!!Function Definition
The function must be defined in the data base as a PL/SQL function.  The PL/SQL function must receive exactly one parameter: the ID of the record from the table the field is associated to; And the function must return a value (or NULL) of the type that is specified in IMCD.

!Best Practices and Recommendations
* The function should be added to the P2K schema (i.e. the function is created by the P2K user) so that it is carried forward in any export/import operations.  
* The function name __should NOT__ start with P2K_ so as to preclude any confusion about its source (P2K_xxxx is reserved for those supplied with the application).
* Your function names start with an identifiable code that will distinguish this as yours (i.e. if your company is Acme Corp, call the functions you create ACME_xxxx)  
* It is important to ensure that __all error conditions__ are trapped or any errors that arise may cause unexpected or unexplainable screen errors.

!Example

In this example it is desired to have a column showing the value of a statistic called STAT-PA-DAYS (seen on [IEST]) for an employee.  By reviewing the [ERD|ERD-HR1] for the [P2K_HR_STATISTICS] table we can see that the record is identifiable as an intersection between  [EEM_ID] (The ID from [P2K_HR_EMPLOYMENTS]) and [DSC_ID] (The ID from [P2K_CM_STATISTIC_COMPONENTS]).  As we know the specific statistic code we will always want, we can determine that the variable element here is the employment record ID.

The following function will take the ID of the [P2K_HR_EMPLOYMENTS] record and return the value on [P2K_HR_STATISTICS] for the [STAT_CODE] of "STAT-PA-DAYS".  Any exceptional condition (no records found, etc.) will result in NULL being returned.

{{{
create or replace
FUNCTION ACME_PADAYS (p_eem_id IN NUMBER) RETURN NUMBER AS
  v_return number(18,6);
BEGIN
  select AMOUNT 
    into v_return 
    from P2K_HR_STATISTICS 
   where eem_id = p_eem_id 
     and dsc_id = (select ID from p2k_cm_statistic_components 
                            where stat_code = 'STAT-PA-DAYS');
  return v_return;
  EXCEPTION
    WHEN OTHERS then return NULL;
END ACME_PADAYS;
//
create public synonym ACME_PADAYS for ACME_PADAYS;
grant execute on ACME_PADAYS to P2K_USER;
}}}

The two slashes are an indicator to SQL to execute the lines preceding.

The last two lines are also important.  The creation of a Public Synonym means that users other than P2K (the schema owner) will have access to the function.  The grant of the execute privilege is required so that other users (other than P2K) will be able to execute this.  All users of the application must have the [P2K_USER] role granted in [IMUS] or [IMUR].

[More User Formula Examples|USER_FORMULA_EXAMPLES]

This function can be tested with the following select statements:

* {{SELECT ACME_PADAYS(12345) FROM DUAL;}} where 12345 is the ID of an employee's EMPLOYMENT record ([IEEI])
* {{SELECT ACME_PADAYS(ID) FROM P2K_HR_EMPLOYMENTS WHERE ...}}etc.  will take the ID straight from the table.

!!Table Definition (IMCD)
Once we have the function defined and working, we can extend the table (in our example [P2K_HR_EMPLOYMENTS]) to include this User Formula Column.  This is done in the [IMCD] screen by adding a new column with the following fields filled in:
;[Sequence]:It is recommended that you start your sequencing at 9000 and work down, so as to not collide with High Line provided sequence numbers
;[Column Name|COLUMN_NAME]:The name you wish to refer to this column in screen design.  It is recommended that you start your column name with the same identifier as your function, so as to preclude confusion with High Line provided values. (i.e. ACME_xxxx)
;[Prompt|FIELD_PROMPT]:This is optional, but recommended.  Any prompt you provide here will be used in screen layouts, but can always be overridden at each usage.
;[Column Usage|COLUMN_USAGE]: __Must be User Defined Formula__
;[Data Type|DATA_TYPE]: Must be the same type that the function returns (Number, Date, Varchar2)
;[Domain Name|DOMAIN]: __Must be of the type indicated by the function return__ ([DATE] for date fields; [VARCHARnn] for character fields (e.g. VARCHAR30); [NUMBERn] for whole number fields; [AMTnn]Vn for numeric fields with decimals.
;[Length|VALUE_LENGTH]: Optional, but recommended
;[Format|VALUE_FORMAT]: Optional, but may be used for date or number variables
;[Defaulted From|DEFAULT_FROM]: __Must contain the name of the PL/SQL Function__

!!Table Cacheing (IMTD)
Note that once the field has been added to the table, you will not normally see this new field right away as this information is relatively static and the application's caching logic will not pick it up.  To see the field right away, you can go to the [IMTD] screen for the [P2K_AM_TABLE_DETAILS] table and press the ~[Clear Table Cache] button.

!!Screen Definition (IMFD)
Now that we have a field on the table that will return to us the value we wish, we can add this field to any form that has access to the ID referred to in the function / table definition.  This is performed on the [IMFD] screen's form layout tab.

It is important to note that any such fields added, must be added with:
;[Item Type|ITEM_TYPE]:''Display Only'' so end users are not given the impression that they can update this value on the screen indicated.
;[Item Usage|FORM_ITEM_USAGE]: ''User Defined'' so it is not overlaid by a subsequent release.