Validation of Flexfield Values

Validation of Flexfield Values

Oracle Self Service HR, Web ADI and some forms use the HRMS APIs to record data in the database. Custom programs at your site, such as data upload programs, may also use the APIs.

From Release 11i (and R11.0 Patch Set D), the APIs validate flexfield values using value sets (in the same way as the professional Forms user interface). This provides the benefit that value set definitions only need to be implemented and maintained in one location. In previous releases, the APIs validated flexfield values using PL/SQL callouts to Skeleton Flexfield Validation server-side packages. These packages are no longer used.

This essay explains how to solve some problems you may encounter when the APIs use flexfield value sets. These problems occur when the value sets refer to objects that are not automatically available to API validation.

In summary, problems may occur when value sets refer to:

Problems may also be caused by:

The rest of this essay explains these issues in more detail with recommended solutions. For all of these solutions, the changes are not apparent to end users and it is not necessary to change where the data is physically held in the database.

Referencing User Profile Options

Referencing profile options in value sets does not cause a problem in the Professional Forms UI or Self Service HR. When a user logs on to these interfaces, the profiles are available, defined at site, application, responsibility, or user level.

However, when the APIs are executed directly in a SQL*Plus database session, there is no application log-on. If the profile is not defined at site level, its value will be null. Even if the profile is defined at site level, this may not give the appropriate values. For example, the PER_BUSINESS_GROUP_ID profile is defined at site level with a value of zero, for the Setup Business Group. If you do not use the Setup Business Group, the flexfield validation finds no rows and all data values are rejected as invalid.

Recommended Solution

Ensure any profiles you reference in value sets are set to the appropriate values before the flexfield validation is performed. You can do this using API user hooks. The following example uses the PER_BUSINESS_GROUP_ID profile.

Using API User Hooks to Set Business Group ID

the picture is described in the document text

hr_401.gif

Define a Before Process user hook call to set the PER_BUSINESS_GROUP_ID profile. Where the API user hook provides a mandatory p_business_group_id parameter, the profile can be set directly from this parameter value. Otherwise first derive the business_group_id value from the database tables using the API's mandatory primary key parameter value.

The PER_BUSINESS_GROUP_ID profile must only be populated when it is undefined or set to zero. If the profile is defined with a non-zero value then it should not be changed. This is to ensure there is no impact on the Professional UI and Self Service HR.

The Before Process user hook package should also remember when it has actually set the PER_BUSINESS_GROUP_ID profile. This can be done with a package global variable.

The second part of the solution is to define an After Process user hook to reset the PER_BUSINESS_GROUP_ID profile back to its original zero or null value. This is only necessary when the Before Process actually changed the value. This is to ensure the profile will be populated with the correct value when the API is called a second time.

For further information on using API user hooks, see the "APIs in Oracle HRMS" essay.

Alternative Solution

If you have only one program experiencing this problem, you could modify the program to set the PER_BUSINESS_GROUP_ID profile immediately before each API call. However, if you introduce any other programs in the future calling the same API, you would have to remember to set the PER_BUSINESS_GROUP_ID profile in these programs too.

Referencing Form block.field Items

If a value set references Form block.field items, an error is raised when the API executes the flexfield validation because the Form item values cannot be resolved on the server-side. This problem affects Oracle Self Service HR and any custom code that calls the API.

Recommended Solution

There are three parts to this solution:

  1. Modify the value sets so all block.item references are changed to custom profile names. These profiles do not have to be defined within the Oracle Applications data dictionary because profiles can be created and set dynamically at run-time.

  2. To ensure the modified value sets work, the profiles must be populated before the APIs execute the flexfield validation. As with the PER_BUSINESS_GROUP_ID profile problem, this requires an API Before Process user hook to populate the profile values. Some of the required values will not be immediately available from the user hook package parameters. However any missing values can be derived from the HRMS tables.

  3. To ensure the flexfield validation continues to work in the Professional UI, the profile values need to be populated before the flexfield pop-up window is displayed. This can be done using the CUSTOM library. For the specific Forms when certain events occur, read the Form items to populate the custom profiles.

Important: There may be some instances in the Self Service screens where it is not possible to display these flexfield values. This is because there is no Web page equivalent to the Forms' CUSTOM library to ensure the custom profiles are correctly populated. This will not be resolved until a future Release.

Alternative Solution

Another method would be to extend the value set Where clauses to obtain the required values from the database. This may require joins to additional database tables. This removes the need to reference Form block.field items. However, this solution is only suitable where values can be obtained from records already in the database. Attempting to reference columns on the record being processed by the current API call will fail. During an insert operation those values will not be available from the database table when the flexfield validation executes. During an update operation the pre-update values will be obtained.

Referencing FND_SESSIONS Row

The FND_SESSIONS database table is used to obtain the current user's DateTrack effective date. This table is only maintained by the Professional UI. The APIs and Self Service modules do not insert or update any rows in this table. So when the value set is executed from these modules, the join fails to find any rows.

Recommended Solution

Using an API Before Process user hook, if a row does not already exist in the FND_SESSIONS table for this database session, then insert one. The EFFECTIVE_DATE column should be set from the p_effective_date parameter made available at the user hook. It is important to ensure the EFFECTIVE_DATE column is set to a date value with no time component, that is, trunc(<date>). Otherwise some join conditions will still fail to find valid table rows.

When the API Before Process user hook has inserted a row into FND_SESSIONS, the After Process user hook should delete it. This ensures that when a second call to the same API is made, the FND_SESSIONS.EFFECTIVE_DATE column is set to the correct value.

If performance is a concern for batch uploading of data, it may be more efficient for the batch upload program to insert the FND_SESSIONS row before the first API call. That will only be acceptable if the set of records will be processed with the same effective date. The API user hooks will still need to be defined to ensure that other programs and interfaces work as required.

Alternative Solution

Another method would be to follow the same approach as the referencing Form block.field items solution. Instead of the value set using the FND_SESSIONS table to obtain the effective date, it could use a custom profile. This avoids the insert and delete DML steps. However, there is an impact on the Professional UI so the CUSTOM library will need to be changed to set the profile value.

Incomplete Context Field Value Lists

Using the APIs, you might see the following error if a flexfield's reference value does not appear in the flexfield Context Field Values list:

ORA-20001: Column ATTRIBUTE_CATEGORY, also known as CONTEXT, cannot have value X.

Suppose a flexfield uses the business_group_id as the reference field. When the API is called, the p_attribute_category parameter should be set to the business_group_id value. When the API validates the Flexfield Context Field (ATTRIBUTE_CATEGORY), it checks whether the business_group_id being used exists in the Flexfield Context Field Values list. If not, the API raises an error.

Recommended Solution

Ensure that the flexfield Context Field Values lists contain all possible values.

Alternative Solution

In some flexfield structures, there are some contexts where only the global data elements apply (there are no context-specific segments). You might consider setting the p_attribute_category parameter to null for these context values. This avoids the need to list these context values in the Context Field Values list. However, this is not recommended because it may cause other data errors to go undetected. For example, if the context field is set to null when a more specific value should be used, any mandatory segment validation associated with that other value will not be executed.