The FastFormula Application Dictionary is designed to hide the complexity of the application database from the FastFormula user. When you write a formula, you reference database items. The Dictionary contains the information that FastFormula requires to generate the SQL and PL/SQL error checking code that extracts these database items.
For example, in a formula you might refer to the database item EMPLOYEE_LAST_NAME. When the formula is run, FastFormula uses information in the Dictionary to build up a complete SELECT statement to extract the name from the database.
Normally, you do not need to be aware of the contents of the Dictionary. For example, when you define a new element, several database items are generated automatically. The information that enables FastFormula to extract these new items is generated at the same time.
However, if you do need to define new database items directly in the Dictionary, you must also load the associated information. The next section describes the entities that you must create in the Dictionary. The following section gives step-by-step instructions for defining new database items.
Suppose FastFormula is running a formula that references the database item EMPLOYEE_LAST_NAME from the table PER_PEOPLE. The SQL required to extract EMPLOYEE_LAST_NAME is as follows:
SELECT TARGET.last_name
FROM per_people TARGET
, per_assignments ASSIGN
WHERE TARGET.person_id = ASSIGN.person_id
AND ASSIGN.assignment_id = &B1
This section explains where this information is stored in the Dictionary and how FastFormula builds it up to form the SQL statement.
Note that the Dictionary stores information at the physical level. That is, it stores parts of the text of SQL statements, which are used by FastFormula to build up the complete statements. It does not store information about entities and relationships.
EMPLOYEE_LAST_NAME is a value in the USER_NAME column of table FF_DATABASE_ITEMS in the Dictionary. When FastFormula runs a formula in which EMPLOYEE_LAST_NAME is a variable, it accesses this table for two reasons:
It gets the value in the DEFINITION_TEXT column. This is the value that appears in the SELECT clause of the SQL. In our example, it is PER_PEOPLE.LAST_NAME. (TARGET is an alias for PER_PEOPLE.)
It identifies the user entity of which the database item is a part. A user entity is a group of one or more database items that can be accessed by the same route. In our example, the user entity might be EMPLOYEE_DETAILS.
Using the user entity ID, FastFormula checks the table FF_USER_ENTITIES to identify the route associated with the user entity. The route is the text of the SQL statement following the FROM keyword. It is held in the table FF_ROUTES. In our example, the route is:
per_people TARGET,
per_assignments ASSIGN
WHERE TARGET.person_id = ASSIGN.person_id
AND ASSIGN.assignment_id = &B1
If several user entities use the same route, the route contains one or more placeholders of the form &U# (where # is a sequence number). Each placeholder references a parameter in table FF_ROUTE_PARAMETERS. FastFormula identifies the parameter ID from this table.
The values of the parameters are different for each user entity. Using the parameter ID, FastFormula accesses the value of the parameter for the relevant user entity in table FF_ROUTE_PARAMETER_VALUES. Since each user entity has a different set of parameter values, the text of the route is different for each user entity.
In our example, only one user entity uses the route so there are no route parameters.
The route may contain another type of placeholder of the form &B# (where # is a sequence number). These placeholders reference contexts in the table FF_ROUTE_CONTEXT_USAGES. FastFormula identifies the ID of the context from this table, and then the name of the context from table FF_CONTEXTS. Contexts are predefined in FF_CONTEXTS and you should not change them. Examples are Payroll ID, Organization ID, and Date Earned.
The value of the context is not fixed. It is passed through by the formula at run time.
In our example, the route requires one context, which is Assignment ID.
When you define a formula, you assign it to a formula type, such as Payroll formulas or QuickPaint formulas. The type of the formula determines the contexts for which it provides values. This is defined in table FF_FTYPE_CONTEXT_USAGES.
For example, a QuickPaint formula feeds through values for the contexts Assignment ID and Date Earned. Thus, when you define a QuickPaint formula, you can use database items that require the contexts Assignment ID and Date Earned. However, any database items that use the other contexts in their routes are not available to you. They do not appear in the list of values.
This is a mechanism to restrict the database items that a formula can use. It can only use database items that are appropriate to the formula context.
It follows that if a database item is based on a route that does not require any contexts (for example, a SELECT from DUAL), then every formula type in the system is able to access the database item.
FastFormula gets the value in the DEFINITION_TEXT column of FF_DATABASE ITEMS and puts it in the SELECT clause of the SQL.
It gets the user entity ID from FF_DATABASE ITEMS and uses it to get the route ID from FF_USER_ENTITIES.
It uses the route ID to get the route text from FF_ROUTES and puts it in the FROM clause of the SQL.
If the route contains a placeholder of the form &U#, FastFormula accesses FF_ROUTE_PARAMETERS to identify the parameter ID. Then it uses the parameter ID to get the value of the parameter for the relevant user entity in table FF_ROUTE_PARAMETER_VALUES.
If the route contains a placeholder of the form &B#, FastFormula accesses FF_ROUTE_CONTEXT_USAGES to identify the context ID. Then it uses the context ID to get the name of the context in table FF_CONTEXTS. This must be one of the contexts for which the formula passes through values (determined by the formula type in table FF_FTYPE_CONTEXT_USAGES).
Before defining new items, you should consider the following issues:
To which business group and legislation should the database item be available?
Can the database item have a null value? Can it be non-existent?
The two attributes Business Group ID and Legislation Code are associated with each user entity. These attributes determine the availability of the database items belonging to the user entity. If the Business Group ID is set to a particular value, then only formulas operating under that business group can 'see' the database item. If the Business Group ID is set to null, the database item can be 'seen' by all business groups. The same principle applies to Legislation Code.
New database items that you define must be associated with a specific business code and legislation. Generic startup items supplied as part of the core system are available to all formulas. Your localization group has added legislation-specific items that are available to all business groups under that legislation.
Note: The name of the database item must be unique within a business group.
To enable validation, you must define two flags in the FastFormula Application Dictionary:
The NULL_ALLOWED_FLAG is a column on the table FF_DATABASE_ITEMS, and hence applies to each database item. If the SQL statement to extract the database item may return a null value, you must set this flag to yes (Y). If you set the flag to no and a null value is returned, FastFormula will report an error.
The NOTFOUND_ALLOWED_FLAG is a column on the table FF_USER_ENTITIES, and hence applies to all the database items belonging to a particular user entity. If the SQL statement to extract database items may return no rows for any of the items, you must set this flag to yes ('Y'). If you set the flag to no and the SQL statement fails to return a row, FastFormula will report an error.
The formula writer must provide a default for a database item used in a formula, unless both of these flags are set to no. For more information on defaults, refer to the guide Using Oracle FastFormula.
To illustrate the steps to generate database items, we will use the example of a user entity called GRADE_RATE_USER_ENTITY, which comprises three database items:
GRADE_VALUE
GRADE_MINIMUM
GRADE_MAXIMUM
This user entity may share its route (GRADE_ROUTE) with other user entities. Each user entity uses a unique value for the route parameter RATE_ID, so that the WHERE clause for each entity is different. If the entities are in the same business group, the USER_NAME of each database item must be unique. One way to achieve this is to include the rate name in the USER_NAME; for example: <RATE_NAME>_GRADE_VALUE.
In this example, we suppose that the value of RATE_ID for GRADE_RATE_USER_ENTITY is 50012. For simplicity we consider only one user entity for the route.
The three database items are stored in table PAY_GRADE_RULES. To extract these items, FastFormula uses an assignment ID passed by the formula. This is the formula context.
This is the SQL required to extract these database items:
SELECT <DEFINITION_TEXT>
FROM pay_grade_rules TARGET
, per_assignments ASSIGN
WHERE TARGET.grade_or_spinal_point_id = ASSIGN.grade_id
AND TARGET.rate_type = 'G'
AND ASSIGN.assignment_id = &B1
AND TARGET.rate_id = &U1
<DEFINITION_TEXT> may be one of the three database items listed below:
| Database Item Name | <DEFINITION_TEXT> |
|---|---|
| GRADE_VALUE | TARGET.value |
| GRADE_MINIMUM | TARGET.minimum |
| GRADE_MAXIMUM | TARGET.maximum |
The following steps describe how to load the information into the Dictionary so that FastFormula can generate this SQL. An example of PL/SQL that loads the information is given at the end of this section.
Write the SQL
Write and test the SQL statement using SQL*Plus to ensure that the statement is correct. The SQL statement must not return more than one row because FastFormula cannot process multiple rows.
Load the Route
This is best done using a PL/SQL routine. Wherever possible, use the sequence value for the primary keys (such as FF_ROUTES_S.NEXTVAL) to populate the table. The route is held in the table FF_ROUTES as a 'long' data type. So, using the example above, you could assign the route to a long variable as follows:
set escape \
DECLARE
l_text long;
BEGIN
l_text := '/* route for grade rates */
pay_grade_rules TARGET,
per_assignments ASSIGN
WHERE TARGET.grade_or_spinal_point_id = ASSIGN.grade_id
AND TARGET.rate_type = ''G''
AND ASSIGN.assignment_id = \&B1
AND TARGET.rate_id = \&U1';
END;
Note the following changes from the original SQL that was given earlier:
Each '&' is preceded with the escape character.
The single quote mark is replaced with two single quote marks.
A comment may be placed at the start of the route if required.
Load the Contexts
The next step is to load the contexts into the table FF_ROUTE_CONTEXT_USAGES. The columns in this table are as follows:
Name |
Null? |
Type |
|---|---|---|
ROUTE_ID |
NOT NULL |
NUMBER(9) |
CONTEXT_ID |
NOT NULL |
NUMBER(9) |
SEQUENCE_NO |
NOT NULL |
NUMBER(9) |
Use the current sequence number for the route ID. This is FF_ROUTES_S.CURRVAL if you used the sequence FF_ROUTES_S.NEXTVAL to populate the table FF_ROUTES. You can obtain the context ID for the particular formula context (assignment ID in our example) from the table FF_CONTEXTS. The sequence number is simply the 'B' number.
For the example, you would insert one row for the route into the table FF_ROUTE_CONTEXT_USAGES (see the PL/SQL for the example, at the end of this section).
Insert Rows in the User Entity Table
For each route, insert at least one row in the table FF_USER_ENTITIES. This table holds the Business Group ID, Legislation Code, the ROUTE_ID, and the NOTFOUND_ALLOWED_FLAG.
Insert Rows for Route Parameters
For each placeholder of the form &U# in the route, you must insert a row into two tables:
FF_ROUTE_PARAMETERS, which references the route, and
FF_ROUTE_PARAMETER_VALUES, which contains the actual value for the route parameter, and references the user entity.
The columns in these tables are as follows:
SQL> desc ff_route_parameters
| Name | Null? | Type |
|---|---|---|
| ROUTE_PARAMETER_ID | NOT NULL | NUMBER(9) |
| ROUTE_ID | NOT NULL | NUMBER(9) |
| DATA_TYPE | NOT NULL | VARCHAR2(1) |
| PARAMETER_NAME | NOT NULL | VARCHAR2(80) |
| SEQUENCE_NO | NOT NULL | NUMBER(9) |
SQL> desc ff_route_parameter_values
| Name | Null? | Type |
|---|---|---|
| ROUTE_PARAMETER_ID | NOT NULL | NUMBER(9) |
| USER_ENTITY_ID | NOT NULL | NUMBER(9) |
| VALUE | NOT NULL | VARCHAR2(80) |
| LAST_UPDATE_DATE | DATE | |
| LAST_UPDATED_BY | NUMBER(15) | |
| LAST_UPDATE_LOGIN | NUMBER(15) | |
| CREATED_BY | NUMBER(15) | |
| CREATION_DATE | DATE |
The data type held in FF_ROUTE_PARAMETERS is either a number (N) or a text value (T).
In our example, the route parameter is RATE_ID. For GRADE_RATE_USER_ENTITY, its value is 50012. The values you would insert into these tables for the example are shown in the sample PL/SQL at the end of this section.
Insert the Database Item
You can now insert the database items. For our example, there are three rows in the table FF_DATABASE_ITEMS that refer to the same user entity. The columns in this table are as follows:
SQL> desc ff_database_items
| Name | Null? | Type |
|---|---|---|
| USER_NAME | NOT NULL | VARCHAR2(80) |
| USER_ENTITY_ID | NOT NULL | NUMBER(9) |
| DATA_TYPE | NOT NULL | VARCHAR2(1) |
| DEFINITION_TEXT | NOT NULL | VARCHAR2(240) |
| NULL_ALLOWED_FLAG | NOT NULL | VARCHAR2(1) |
| DESCRIPTION | VARCHAR2(240) | |
| LAST_UPDATE_DATE | DATE | |
| LAST_UPDATED_BY | NUMBER(15) | |
| LAST_UPDATE_LOGIN | NUMBER(15) | |
| CREATED_BY | NUMBER(15) | |
| CREATION_DATE | DATE |
The USER_NAME must be unique within the business group.
The values you would insert into this table for the three example database items are shown in the sample PL/SQL at the end of this section.
When you create the database items, it is useful to populate the other columns, such as LAST_UPDATE_DATE, and CREATION_DATE.
The following PL/SQL creates the database items in the example::
set escape \
DECLARE
l_text long;
l_user_entities_seq number;
l_route_id number;
BEGIN
--
-- assign the route to a local variable
--
l_text := '/* route for grade rates */
pay_grade_rules TARGET,
per_assignments ASSIGN
WHERE TARGET.grade_or_spinal_point_id = ASSIGN.grade_id
AND TARGET.rate_type = ''G''
AND ASSIGN.assignment_id = \&B1
AND TARGET.rate_id = \&U1';
--
-- insert the route into the table ff_routes
--
insert into ff_routes
(route_id,
route_name,
user_defined_flag,
description,
text,
last_update_date,
creation_date)
values (ff_routes_s.nextval,
'GRADE_ROUTE',
'Y',
'Route for grade rates',
l_text,
sysdate,
sysdate);
--
-- load the context
--
insert into ff_route_context_usages
(route_id,
context_id,
sequence_no)
select ff_routes_s.currval,
context_id,
1
from ff_contexts
where context_name = 'ASSIGNMENT_ID';
--
-- create a user entity
--
select ff_user_entities_s.nextval
into l_user_entities_seq
from dual;
--
select ff_routes_s.currval
into l_route_id
from dual;
--
insert into ff_user_entities
(user_entity_id,
business_group_id,
legislation_code,
route_id,
notfound_allowed_flag,
user_entity_name,
creator_id,
creator_type,
entity_description,
last_update_date,
creation_date)
values (l_user_entities_seq,
1, -- example business group id
'GB', -- example legislation
l_route_id,
'Y',
'GRADE_RATE_USER_ENTITY',
50012, -- example creator id
'CUST',
'Entity for the Grade Rates',
sysdate,
sysdate);
--
-- insert the route parameters
--
insert into ff_route_parameters
(route_parameter_id,
route_id,
data_type,
parameter_name,
sequence_no)
select ff_route_parameters_s.nextval,
l_route_id,
'N',
'Grade Rate ID',
1
from dual;
--
insert into ff_route_parameter_values
(route_parameter_id,
user_entity_id,
value,
last_update_date,
creation_date)
select ff_route_parameters_s.currval,
l_user_entities_seq,
50012,
sysdate,
sysdate
from dual;
--
-- insert the three database items
--
insert into ff_database_items
(user_name,
user_entity_id,
data_type,
definition_text,
null_allowed_flag,
description,
last_update_date,
creation_date)
values ('GRADE_VALUE',
l_user_entities_seq,
'T',
'TARGET.value',
'Y',
'Actual value of the Grade Rate',
sysdate,
sysdate);
--
insert into ff_database_items
(user_name,
user_entity_id,
data_type,
definition_text,
null_allowed_flag,
description,
last_update_date,
creation_date)
values ('GRADE_MINIMUM',
l_user_entities_seq,
'T',
'TARGET.minimum',
'Y',
'Minimum value of the Grade Rate',
sysdate,
sysdate);
--
insert into ff_database_items
(user_name,
user_entity_id,
data_type,
definition_text,
null_allowed_flag,
description,
last_update_date,
creation_date)
values ('GRADE_MAXIMUM',
l_user_entities_seq,
'T',
'TARGET.maximum',
'Y',
'Maximum value of the Grade Rate',
sysdate,
sysdate);
END;
/