I am working on an article about flexfields and flexfield validation.
Even though the article is not yet finished, I thought the part about ‘SPECIAL’ and ‘PAIR’ validation types might be interesting enough. Many people seem to think they can only use the seeded validation sets. However, you can also create your own validation sets. And their options are very powerful. So I wanted to publish this part of the article as a prelude to the full story.
Special validation is used to provide flexfield functionality for a single value. What that means is that you can have for example a concurrent program parameter that will be filled with a Key flexfield value, or a range of flexfield values.
Let’s go back to the Key Flexfield. We know that they are combinations of different segment values that are stored in a separate combination table.
When you want to submit a key-flexfield combination as a parameter to a concurrent program, you can code your own validation for the separate values. But you’ll be missing the nice functionality that gives you pop-ups, a validation over the resulting combination and if needed the ID-value for the flexfield combination.
That is possible with a ‘Special’ validation type.
The special validation uses a number of user exits to enter, validate and query keyflex segments. With special validation, you will be able to enter one or more segment values for a key flexfield. To enter these segment values, 3 user exits can be used. They are: ‘POPID’, ‘VALID’ and ‘LOADID’.
POPID is used to enable the user to enter the flexfield segment value. It is called when the users cursor enters the segment value field. With this user exit, you decide which segment values should be shown, and how they should be shown.
VALID is called when the user exits the segment value, or confirms the chosen flexfield combination. It validates the entered value against the values existing in the key flexfield table.
LOADID is optional, and it can be used to choose which information will be returned as flexfield value. This can be the concatenated segments, or the id-value for the flexfield combination or segment values.
These 3 user exits can be assigned to 3 ‘events’. There are more events possible, but they are either not yet in use, or their use is not yet supported. So we will only use ‘Validate’, ‘Edit’ and ‘Load’.
Sounds complicated, so far? Don’t worry; this is not an easy validation. But we’ll build some examples to give you an idea. First we start with building a very easy special validation. This will be built on our Code Combination key flexfield. We’ll be using a concurrent program ‘Test Flex Validation’ program to see our different options.
This program is based on the following procedure:
CREATE OR REPLACE PROCEDURE XXX_TEST_FLEXFIELD_PARAMS
( errbuf out varchar2
, retcode out varchar2
, p_flex in varchar2
, p_flex2 in varchar2 := ‘XXX’
, p_flex3 in varchar2 := ‘XXX’
, p_flex4 in varchar2 := ‘XXX’
, p_flex5 in varchar2 := ‘XXX’
This will only write the parameter value to the output of the request. To use flexfields as parameters for concurrent programs, we need to define a value set based on them.
We will start with the barest setup to enter a key-flexfield combination. For this article, we use the accounting flexfield, with code ‘GL#’ and id-num ‘101’.
In this case, we have the following definition: