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.
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 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 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.
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.
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.
It is important to note that any such fields added, must be added with an Item Usage of User Defined so it is not overlaid by a subsequent release. It is also important to set the Item Type to Display Only so end users are not given the impression that they can update this value on the screen indicated.
Screen captures are meant to be indicative of the concept being presented and may not reflect the current screen design.
If you have any comments or questions please email the Wiki Editor
All content © High Line Corporation