This page (revision-20) was last changed on 26-Nov-2021 10:22 by rforbes

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
20 26-Nov-2021 10:22 6 KB rforbes to previous
19 26-Nov-2021 10:22 5 KB rforbes to previous | to last
18 26-Nov-2021 10:22 5 KB khiggs to previous | to last
17 26-Nov-2021 10:22 5 KB jaiken to previous | to last
16 26-Nov-2021 10:22 5 KB jaiken to previous | to last
15 26-Nov-2021 10:22 5 KB rforbes to previous | to last
14 26-Nov-2021 10:22 5 KB jmyers to previous | to last ERD-HR01 ==> ERD-HR1
13 26-Nov-2021 10:22 5 KB jmyers to previous | to last ERD-HR1 ==> ERD-HR01
12 26-Nov-2021 10:22 5 KB RForbes to previous | to last
11 26-Nov-2021 10:22 5 KB RForbes to previous | to last
10 26-Nov-2021 10:22 5 KB JAiken to previous | to last
9 26-Nov-2021 10:22 5 KB RForbes to previous | to last
8 26-Nov-2021 10:22 5 KB RForbes to previous | to last
7 26-Nov-2021 10:22 5 KB RForbes to previous | to last
6 26-Nov-2021 10:22 5 KB RForbes to previous | to last
5 26-Nov-2021 10:22 5 KB RForbes to previous | to last
4 26-Nov-2021 10:22 5 KB RForbes to previous | to last
3 26-Nov-2021 10:22 5 KB RForbes to previous | to last
2 26-Nov-2021 10:22 5 KB JAiken to previous | to last
1 26-Nov-2021 10:22 5 KB RForbes to last

Page References

Incoming links Outgoing links
USER_FORMULA_COLUMN
USER_FORMULA_COLUMN

Version management

Difference between version and

At line 1 changed one line
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 computed or derived value.
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 other screens as a display-only value.
At line 4 changed one line
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.
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 expected by the screen.
At line 8 changed one line
* 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).
* The function name __should NOT__ start with P2K_ so as to preclude any confusion about its source (P2K_xxxx is reserved for High Line).
At line 10 removed one line
* If you are going to have many user formula columns, you may find it advantageous to create a package with your customer prefix, and then include all the functions within that package. This keeps all your work together in one place.
At line 12 removed 2 lines
* It is important to ensure that a public synonym is created and grants made to the P2K_USER or users will get unexpected results in the screens where you may be using them.
* User Formula Columns should not be included in [ISPY] set up of PA Types.
At line 17 changed one line
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.
In this example it is desired to have a column showing the value of a statistic called (seen on [IMST]) 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.
At line 36 removed 3 lines
/
}}}
The two slashes are an indicator to SQL to execute the lines preceding.
At line 40 removed 2 lines
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].
{{{
At line 38 added one line
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].
At line 47 removed 3 lines
[More User Formula Examples|USER_FORMULA_EXAMPLES]
At line 52 changed 2 lines
* {{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.
{{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.
At line 55 changed 4 lines
!!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 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 provided values. (i.e. ACME_xxxx)
!!Table Definition
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 co9lumn with the following fields filled in:
;[Sequence]:It is recommended that you start your sequencing at 9999 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)
At line 60 changed 3 lines
;[Column Usage|COLUMN_USAGE]: __Must be User 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, must be one of the standard set.
;[Column Usage|COLUMN_USAGE]: __Must be User Defined Formula__
;[Data Type|DATA_TYPE]: Must of the the same type that the function returns (Number, Date, Varchar2)
;[Domain Name|DOMAIN]: __Must be CODE__
At line 65 changed one line
;[User Formula (Defaulted From)|DEFAULT_FROM]: __Must contain the name of the PL/SQL Function__
;[Defaulted From|DEFAULT_FROM]: __Must contain the name of the PL/SQL Function__
At line 67 changed 2 lines
%%information
Note that once the field has been added to the table, you will not see this new field right away as this information is key to the application and thus will only be picked up on an instance restart.
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.
At line 70 changed 4 lines
Restart the application instance.
%%
!!Screen Definition (IMFD)
!!Screen Definition
At line 76 changed 8 lines
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.
----
![Notes|Edit:Internal.USER_FORMULA_COLUMN]
[{InsertPage page='Internal.USER_FORMULA_COLUMN' default='Click to create a new notes page'}]
It is important to note that any such fields added, must be added with an [Item Usage|FORM_ITEM_USAGE] of ''User Defined'' so it is not overlaid by a subsequent release. It is also important to set the [Item Type|ITEM_TYPE] to ''Display Only'' so end users are not given the impression that they can update this value on the screen indicated.
This page (revision-20) was last changed on 26-Nov-2021 10:22 by rforbesTop