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
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’
) IS
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex2);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex3);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex4);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_flex5);
END;
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:
So what does this mean?
The first box is for the edit event. This will be triggered when the user enters the cursor into the field with this value set.
FND POPID This is the user exit to pop up a flexfield screen, and let the user enter the flexfield values.
CODE=”GL#” This is the flexfield code for the key flexfield that we will be using.
APPL_SHORT_NAME=”SQLGL” The short name for the application the flexfield belongs too. Together with ‘Code’, this will identify the flexfield itself.
NUM=”101″ The id-number for the flexfield structure. If you have only a single structure flexfield, it is optional. For flexfields enabled for multiple structures, you need to enter the id-number.
VALIDATE=”PARTIAL” Validate can be ‘None’, ‘Partial’ or ‘Full’. None means the combination is not validated. Partial means that the separate segments are validated, there is no validation if the combination exists. Full means that segments and combination will be checked, and if a new value is entered, this will be inserted into the key flexfield table.
SEG=”:!VALUE” This is the forms field that will be used to store the value of the segments.
The second box is for the ‘Validation’ event. This code will be called when the user navigates out of the field, or submits the entire combination.
Now when we set this value set as a parameter for our concurrent program, we can see how the validation works:
Now when we run the program, we get this pop-up:
We have all the functionality of the key flexfield. We can use the ‘Combinations’ button to search for existing combinations, and all separate segments will be validated, as will be the final combination.
When we submit a value to our program, it will show the concatenated segments as the value of our parameter:
Now let’s see some more features of this validation. For example, we’d like to have the value of the combination id. (CODE_COMBINATION_ID in our case, since we use the Accounting Flexfield).
To get that, we need to add the LOADID user exit:
The ‘Load’ event will get the combination-id from the flexfield table. This is only possible for the ‘VALIDATE=”FULL”, since it will validate the whole combination. Also we need to set the ID=”:!ID”. This will populate the :!ID column with the ID value of the combination.
Finally, I added the ‘DINSERT=”NO” ‘, because we don’t want to allow insertion of new code combinations from this value set. (And Validation=”FULL” by default inserts new combinations into the flexfield column).
Now when we run the concurrent request, we see that the parameter value is the code_combination_id instead of the concatenated segments:
With these user exits it is also possible to select just a number of segments, instead of the whole combination. For this we remove the ‘Load’ / ‘LOADID’ part again.
Then we add a ‘DISPLAY=”x” ‘ to the ‘Edit’ and ‘Validate’ user exits. The “display” parameter is defaulting to ‘ALL’. But you can also specify separate segments by their sequence number or names. In our case, we display the first 2 segments:
Now when we run the concurrent program, we get a pop-up for only the first 2 values:
A very nice feature (at least as far as I’m concerned) is the use of a where clause on the combination values. Consider the following ‘Enter’ code:
FND POPID
CODE=”GL#”
NUM=”101″
APPL_SHORT_NAME=”SQLGL”
VALIDATE=”FULL”
TITLE=”Special Validation Key”
ID=”:!ID”
SEG=”:!VALUE”
DESC=”:!MEANING”
WHERE=”segment2 not like ‘1%’ “
The “WHERE” clause prevents us from choosing combinations that have a segment2 starting with ‘1’. When we run our concurrent program with this, and choose the combinations:
There is no Dpt starting with 1.
When we add the “WHERE”-clause to the validation event too, it will prevent us from entering the values manually:
The last feature that we’ll look into is the use of a pl/sql validation through the special validation routines. By using the user-exit PLSQL, we can call an anonymous PL/SQL block in our ‘Validation’ event. I created a value set with the following function for the ‘Validation’ event:
FND PLSQL ” declare
v_value varchar2( 10 ) := :!value ;
v_sum number;
v_valid boolean;
begin
v_sum:=0;
for i in 1..length(v_value) loop
v_sum :=v_sum+(length(v_value)+1-i)*substr(v_value,i,1);
end loop;
if mod(v_sum,11)=0 then
v_valid := TRUE;
else
v_valid:=FALSE;
end if;
if not v_valid then
fnd_message.set_name(‘FND’,’FND_GENERIC_MESSAGE’ );
fnd_message.set_token(‘MESSAGE’,’This is not a valid bank account’);
fnd_message.raise_error;
end if;
END; “
This PL/SQL procedure validates a (Dutch) bank account number. If it does need pass the test, a message will be displayed. This gives you almost unlimited possibilities for validating entered data.
As you can see, it is only a ‘Validate’ event. Because we don’t need any special functionality for entering the data. We can limit the entry to numbers only on the ‘Validation Set’ main page.
Now when we use this value set for our concurrent program, we can only enter valid dutch bank accounts:
And
The list of parameters for the user exits is longer than this. So we won’t be going through all the possibilities. You can check the Developers Guide and the Flexfield guide for a complete listing of options. (Did you notice the flexfield title that I sneaked into the pop-up? Try and find the option for that!)
Please try the different options for yourself, and realize the possibilities of the special validation.
Pair Validation
Meanwhile, we’ll continue to the ‘Pair’ validation. The pair validation is very much like the ‘special’ validation. It uses the same kind of user exits, but this time, a range of segment values or combinations is selected.
Let’s first create a range of the account segment. Instead of using POPID and VALID, we use POPIDR and VALIDR. The R-version of the user-exits automatically create a range.
Of course we need 2 parameters to set the range. However, we need only one validation set.
I created the validation set ‘XXX_PAIR_VAL’. I entered only the edit and validate events:
The next step is to set the parameters for both the low and high value. Both parameters have the validation set ‘XXX_PAIR_VAL’.
Now when we run the program, we can enter a range. This includes validation that the high value is indeed higher or equal to the low value.
Of course the concurrent program will receive the values for 2 parameters.
When we use the full validation we can enter a range of the whole account combination. Note that we cannot use the FULL validation for pair-validation. Because that would mean the use of the combination-id from the flexfield table and based on the combination-id’s you cannot build a range.
So we can only use PARTIAL and NONE for the validation. For that same reason, I have not yet had a reason to use a LOAD event for PAIR validation. It is however allowed to use one.
I created a PAIR validation for the whole accounting range as follows:
When used in the concurrent program, it will indeed allow us to enter a range of all segments:
That completes the chapter on PAIR validation too.
Solution: Problem with Desktop Integrator in R12 OAF Screen
Error:
Desktop Integration is not a valid responsibility for the current user. Please contact your System Administrator.
Solution:
By default below Profile Option is either Null or Yes. Just set the below option, then you can access the screen.
Set Profile Option :
ADI: Use Function Security => No
Share this:
How to Solve Concurrent Program Output Post Processor(OPP) Issue in R12
I have faced several issues with the EBS XML Reports esp. during or after Output Post processing.
Issue# 1- Concurrent Request completed with Warning and timed out
Concurrent Request completed with Warning and in the log file says the request timed out.
Fix:-
Go to System Administrator -> Profile -> System
Make the Profile Concurrent: OPP Process Timeout to 10800.
This issue comes when the OPP is timed out to publish the output. The time out will be based upon the value in the profile. For e.g. if the value set at this profile is 600 it means the program waits for 10 mins (600/60) and if OPP does not publish the output within this time then it will timed out. Having higher value i.e. 10800 which is 3 hours will solve most of the issues that may cause due to high volume of the data.
Issue# 2- Concurrent Request completed with Error with Java Heap Space.
This is very common issue when size of the XML is huge and OPP fails to publish the data it will generate this error.
One way to fix this issue is to see how many Active OPP Services is running on the server. We had this issue for one of my client and by increasing the number of OPP Services from 4 to 8 solves this issue.
System Administrator -> Concurrent ->Manager ->Administer
Click on Output Post Processor and then click Processes.
Share this:
How to Configure AME in R12 – Assign Roles & Create Grants to Apps User
Oracle Approvals Management (AME) functionality is a self-service web application that allows users to
define rules for administering approvals of various types of transactions. Learn how easy it is to use this
powerful functionality to create complex approval scenarios that meet your unique business requirements.
Case studies of recent successful implementations will be presented for PO, AP and HR. Learn how to
accommodate individual employee signing limits, how to integrate workflow timeout functionality with AME.
Share this:
R12 – How to migrate AME setups using FNDLOAD
If you want to migrate the AME setup like rules,conditions,approval groups etc. from one instance to another instance, you can use some of them by mean of FNDLOAD utility. The process is similar to what we are regularly using for other AOL Objects.
FNDLOAD will have such ldt file through which you can use.
Here are the details steps for each setup’s.
1. Condition:
The script that downloads AME conditions allows you to download all conditions for a given transaction type or only those associated with a particular attribute or group of attributes.
Download
FND_TOP apps/<apps password> 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesconk.lct <ldt filename> AME_CONDITIONS CONDITION_KEY=<condition key> TRANSACTION_TYPE_ID=<trans typs id> APPLICATION_SHORT_NAME=<application short name>
Upload
FNDLOAD apps/<passwd>@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesconk.lct <ldt filename>
Example:
FNDLOAD apps/<passwd>@sourcedb 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesconk.lct amecondition.ldt AME_CONDITIONS APPLICATION_SHORT_NAME=’PER’ TRANSACTION_TYPE_ID=’HRSSA’ ATTRIBUTE_NAME=’PROCESS_NAME’ CONDITION_KEY=’317772662:44552′;
FNDLOAD apps/<passwd>@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesconk.lct amecondition.ldt
2. Dynamic Approval group /Approver Groups:
An approver group can either be an ordered set of one or more approvers (persons and/or user accounts) or it can be a list, which is dynamically generated at rule evaluation time.
Download
FND_TOP apps/<apps password> 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesappg.lct <ldt filename> AME_APPROVAL_GROUPS APPROVAL_GROUP_NAME=<name of approval group> TRANSACTION_TYPE_ID=<trans typs id> APPLICATION_SHORT_NAME=<application short name>
Upload
FNDLOAD apps/<passwd>@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesappg.lct <ldt filename>
Example:
FNDLOAD apps/<passwd>@sourcedb 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesappg.lct ameapprovalgroup.ldt AME_APPROVAL_GROUPS APPROVAL_GROUP_NAME=’Dyn Apprv Hub Approval Group’ TRANSACTION_TYPE_ID=’HRSSA’ APPLICATION_SHORT_NAME=’PER’
FNDLOAD apps/<passwd>@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesappg.lct ameapprovalgroup.ldt
3. Dynamic Approval group config:
Download
FNDLOAD apps/<passwd>@sourcedb 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesaagc.lct ameapprovalgroupusage.ldt AME_APPROVAL_GROUP_CONFIG APPROVAL_GROUP_NAME=’Dyn. Post HROPs Approval Group’ TRANSACTION_TYPE_ID=’HRSSA’ APPLICATION_SHORT_NAME=’PER’
Upload
FNDLOAD apps/<passwd>@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesaagc.lct ameapprovalgroupusage.ldt
4. AME Rule:
An approval rule is a business rule that helps determine a transactions approval process. Rules are constructed from conditions and actions.
The AME rules can be downloaded for information about the rule (e.g. name, description, etc) along with associated conditions and rule type.
Download
$FND_TOP/bin/FNDLOAD apps/<apps password> 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesrulk.lct <ldt filename> AME_RULES RULE_KEY=<Rule Key > TRANSACTION_TYPE_ID=<trans typs id> APPLICATION_SHORT_NAME=<application short name>
#You can find Rule Key in AME_RULES table
Upload
FNDLOAD apps/<passwd>@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesrulk.lct amerule.ldt
Example:
FNDLOAD apps/<passwd>@sourcedb 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesrulk.lct amerule.ldt AME_RULES RULE_KEY=’317772662:54567′ TRANSACTION_TYPE_ID=’HRSSA’ APPLICATION_SHORT_NAME=’PER’
FNDLOAD apps/<passwd>@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesrulk.lct amerule.ldt
5. AME Rule Action Type Usage:
Download
FNDLOAD apps/<apps pw> 0 Y DOWNLOAD amesactu.lct <download file name>.ldt AME_ACTION_USAGES APPLICATION_SHORT_NAME=<FND application short name> TRANSACTION_TYPE_ID=<AME transaction type short name> [RULE_KEY=<Rule Key>]
# Rule Key is found in AME_RULES table
Upload
FNDLOAD apps/apps 0 Y UPLOAD amesactu.lct usage<download file name>.ldt
Ex:
FNDLOAD apps/<passwd>@sourcedb 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesactu.lct ameactionusage.ldt AME_ACTION_USAGES RULE_KEY=’317772662:54567′ TRANSACTION_TYPE_ID=’HRSSA’ APPLICATION_SHORT_NAME=’PER’
FNDLOAD apps/<passwd>@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesactu.lct ameactionusage.ldt
6. Transaction Types
An application that uses AME to govern its transactions’ approval processes is termed an integrating application. An integrating application may divide its transactions into several categories where each category requires a distinct set of approval rules. Each set of rules is called a transaction type. Different transaction types can use the same attribute name to represent values that are calculated in different ways or fetched from different places.
Download
FNDLOAD apps/<apps pwd> 0 Y DOWNLOAD amescvar.lct <download file name>.ldt AME_CALLING_APPS APPLICATION_SHORT_NAME=<FND application short name> TRANSACTION_TYPE_ID=<AME transaction type short name>
Upload
FNDLOAD apps/<apps pwd> 0 Y UPLOAD amescvar.lct <download file name>.ldt
Eg:
FNDLOAD apps/apps 0 Y DOWNLOAD amescvar.lct hubtrantype.ldt AME_CALLING_APPS APPLICATION_SHORT_NAME=SQLAP TRANSACTION_TYPE_ID=SBTRANSTYPE
FNDLOAD apps/apps 0 Y UPLOAD amescvar.lct hubtrantype.ldt
7. Attribute
Attributes are business variables with a single value for a particular transaction.
Download
FNDLOAD apps/<apps password> 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesmatt.lct <ldt filename> AME_ATTRIBUTES ATTRIBUTE_NAME=<attribute name> TRANSACTION_TYPE_ID=<trans typs id> APPLICATION_SHORT_NAME=<application short name>
Upload
FNDLOAD apps/<apps pwd> 0 Y UPLOAD amesmatt.lct <ldt download file name>
Example:
FNDLOAD apps/apps 0 Y DOWNLOAD amesmatt.lct hubattributes.ldt AME_ATTRIBUTES APPLICATION_SHORT_NAME=SQLAP
TRANSACTION_TYPE_ID= SBTRANSTYPE ATTRIBUTE_NAME=SB_CUST_ATTRIBUTE
FNDLOAD apps/apps 0 Y UPLOAD amesmatr.lct hubattributeusages.ldt
8. Attribute Usage
Download
FNDLOAD apps/<apps password> 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesmatr.lct <ldt filename> AME_ATTRIBUTE_USAGES ATTRIBUTE_NAME=<attribute name> TRANSACTION_TYPE_ID=<trans typs id> APPLICATION_SHORT_NAME=<application short name>
Upload
FNDLOAD apps/<apps pwd> 0 Y UPLOAD amesmatr.lct <download file name>.ldt
Example:
FNDLOAD apps/apps 0 Y DOWNLOAD amesmatr.lct hubattributeusage.ldt AME_ATTRIBUTE_USAGES aPPLICATION_SHORT_NAME=SQLAP
FNDLOAD apps/apps 0 Y UPLOAD amesmatr.lct hubattributeusages.ldt
Share this:
Oracle R12 – Special and Pair Validation Value Sets
Special and Pair validation types
Special Validation
With these user exits it is also possible to select just a number of segments, instead of the whole combination. For this we remove the ‘Load’ / ‘LOADID’ part again.
The list of parameters for the user exits is longer than this. So we won’t be going through all the possibilities. You can check the Developers Guide and the Flexfield guide for a complete listing of options. (Did you notice the flexfield title that I sneaked into the pop-up? Try and find the option for that!)
Pair Validation
Share this: