Correct Child Overlapping in the Hierarchy of a Valueset

It is highly recommended to check this note on support.oracle.com as there may be any update in this note.

How to correct Child Overlapping in the Hierarchy of a Valueset [ID 371580.1]

Applies to:

Oracle General Ledger - Version: 11.5.2 to 11.5.10
Information in this document applies to any platform.

Goal

How to correct Child Overlapping in the Hierarchy of a Valueset.

Solution

For Reporting and Budgetary purpose, hierarchy is maintained in the values of a Value Set i.e. Child values are assigned to a Parent value, which are in turn assigned to Grand Parents thus forming a Tree structure as follows:

How to correct Child Overlapping in the Hierarchy of a Valueset [ID 371580.1]

For a correct hierarchy, it is needed that a child value is assigned to only one Parent value. In case a Child value is
assigned to more than one Parent value, it results in Child Overlapping. 

There are two possible scenarios of Overlapping Child Ranges:

Scenario One

A Child value is assigned to a Parent value more than once. 

How to correct Child Overlapping in the Hierarchy of a Valueset [ID 371580.1]

For example, in the above hierarchy structure, the child ranges for 1000 are:
1001 to 1010 -----> Child Values Only
1010 to 1020 -----> Child Values Only

Due to above two assignments, 1010 is getting assigned twice to 1000. 

Scenario Two

A Child value is assigned to more than one Parent. 

How to correct Child Overlapping in the Hierarchy of a Valueset [ID 371580.1]

For Example: In the above hierarchy
The child range for 2100 is defined as "2050 to 2150 -----> Child Values Only"
The child range for 2200 is defined as "2150 to 2250 -----> Child Values Only"

Thus 2150 is getting assigned to both 2100 and 2200. 

Now we have a Parent value 2000, for which child range is defined as "2001 to 2999 -----> Parent Values Only"

If we evaluate final child values, 2150 will be rolling up twice to 2000.

NOTE : To get the list of child values which are getting rolled up more than once to a Parent value, refer Note 118130.1 on "How to Read Output of Script to Detect Overlapping Child Ranges in a Value Set".

The two scenarios explained above are possible only when a value (Child or Parent) is assigned more than once in the overall hierarchy structure.

How to identify Duplicate Assignments

Use the following SQL to identify the duplicate assignments in all the Value Sets: 

SELECT          fset.flex_value_set_name "Value Set",
                         fvalue.flex_value_set_id "Value Set Id",
                         fvalue.flex_value "Child Value",
                         fvalue.parent_flex_value "Parent Value"
FROM             FND_FLEX_VALUE_SETS                  fset,
                         FND_FLEX_VALUE_CHILDREN_V  fvalue
WHERE          (fvalue.flex_value, fvalue.flex_value_set_id) in (
                         select flex_value, flex_value_set_id
                         from FND_FLEX_VALUE_CHILDREN_V
                         group by flex_value, flex_value_set_id
                         having count(1) > 1)
AND               fset.flex_value_set_id = fvalue.flex_value_set_id
ORDER BY    fvalue.flex_value_set_id, fvalue.flex_value, fvalue.parent_flex_value;

For all the values in a value set, which have more than one Parent value assigned to it, the output of this query will give
the name of the Valueset, Child Value and the Parent value assigned to it.

To correct the Hierarchy, follow the steps given below:
1. Go to General Ledger Responsibility
2. Setup -> Financials -> Flexfields -> Key -> Values
3. Select the Value Set option in "Find Values By" and Enter the Value Set name as returned by the SQL above.
4. Query for the Child value returned by the SQL.
5. Click on "View Hierarchies" for this value.
6. Click on "Up" button.
You will observe that this is returning more than one value as it is assigned to multiple Parent values.
7. Query for the Parent Values and Click on "Define Child Range"
8. Correct the hierarchy i.e remove the assignment of the Child value from all but one Parent Value.
9. Saving the changes and exiting the form will initiate the Compile Value Set Hierarchy program.

This will resolve the issue. Rerun the script once again to check that it is not returning any rows.

NOTE: In case you just want to correct hierarchy of a particular Valueset, please use the following query:

SELECT      flex_value_set_id "Value Set Id",
                    flex_value "Child Value",
                    parent_flex_value "Parent Value"
FROM        FND_FLEX_VALUE_CHILDREN_V
WHERE      (flex_value, flex_value_set_id) in (
                   select flex_value, flex_value_set_id
                   from FND_FLEX_VALUE_CHILDREN_V
                   where flex_value_set_id =
                   (select flex_value_set_id
                   from fnd_flex_value_sets fset
                   where flex_value_set_name = '&Flexfield_Value_Set_Name')
                   group by flex_value, flex_value_set_id
                   having count(1) > 1)
ORDER BY    flex_value_set_id, flex_value, parent_flex_value;

References

NOTE:118130.1 - How to Read Output of Script to Detect Overlapping Child Ranges in a Value Set
NOTE:143175.1 - How To Identify Overlapping Child Ranges In A Value Set



Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

How to download form personalization and upload it to another Instance

How to download form personalization and upload it to another Instance in Oracle E-Business Suite

Here is a very useful command FNDLOAD, that can be use to take a backup of your form personalization.
Your personalizations will be saved on .ldt file.
We will learn this through two simple steps,

*** You should take a note that upon uploading, all prior personalization for that function are first deleted, and then the contents of the loader file are inserted. So be careful.

Step 1: Download your personalization.
From Linux, set the environment file, your .ldt file will be created on current directory

FNDLOAD <apps_user>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <file_name>.ldt FND_FORM_CUSTOM_RULES form_name=<form_name>
 Here three parameters are used
<apps_user>    = apps user name normally apps
<password>     = apps password
<file_name>      = file name (in our case test.ldt)
<form name>    = form name can be get form Help > About Oracle Applications

Usage:
FNDLOAD apps/*** 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct test.ldt FND_FORM_CUSTOM_RULES form_name=FNDSCAUS


Step 2: Upload your personalization to another instance.
 From Linux, set the environment file, go to path where .ldt file is copied
 FNDLOAD <apps_user>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct <file_name>.ldt

Usage:
FNDLOAD apps/*** 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct test.ldt

Enjoy ;)

Read More

APP-FND-00222: Encountered an error while getting the ORACLE user account for your concurrent request.

APP-FND-00222: Encountered an error while getting the ORACLE user account for your concurrent request.

Today when I was trying to run the report from my EBS R12 it shows following error when I click on "SUBMIT" button.

"APP-FND-00222: Encountered an error while getting the ORACLE user account for your concurrent request
Contact your system administrator"

APP-FND-00222: Encountered an error while getting the ORACLE user account for your concurrent request.

When I googled the error I came to know that in my concurrent program I have given custom application name instead of  "Human Resources" for whom this report is belongs to as shown below:

APP-FND-00222: Encountered an error while getting the ORACLE user account for your concurrent request.

In my case I don't want it to run from custom application so I just changed the "Application" field from "CUSTOM" to "Human Resources" and it works fine. For those who want to run the report in custom application please refer to note:

Concurrent Processing - APP-FND-00222 Error When Submitting a Custom Concurrent Request Registered under a Custom Application [ID 150993.1]

Checked for relevance 14-Apr-2010
Application Install 11.5.6 to 12.0

fact: Oracle Application Object Library

fact: FNDRSRUN - Run Reports

symptom: APP-FND-00222 Encountered an error while getting the ORACLE user 
account for your concurrent request to run from

symptom: Error submitting concurrent request registered to a custom application

cause: The custom application has not been assigned to any data group



fix:

Add the custom application to a data group.

1.  Under the System Administrator responsibility navigate to Security --> 
Oracle --> DataGroup.
2.  Query up a data group (Standard Data Group is appropriate).
3.  A list of applications associated with the standard data group will appear.
4.  Click on FILE in the toolbar menu then click on NEW.
5.  In the Application column select the custom application from the LOV.
6.  Add an Oracle ID to the ORACLE ID column (APPS is appropriate)
7.  Save the changes.
8.  Resubmit the concurrent request.

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions

Read More

Restrict the View of Key Flexfields Values through Forms Personalization

Its a very common issue faced by ERP administrators, that a GL user with admin rights is also eligible to view flexfield values of other modules (fa,hr,payroll etc.). Here is a simple solution of this problem through form personalization. I found this solution on (http://www.biztech.com/blog/2011/08/view-only-access-to-key-flexfield-values/), so if you find some difficulty in understanding you can go use given link.

So, as you can see the flexfield find window in screenshot, our first target will be to disable all radio buttons on left side except 'Key Flexfield' radio button.



Following are the screenshots of personalization to disable radio buttons, trigger event will be 'WHEN-NEW-FORM-INSTANCE' and we will have to define three actions separately for each radio button.







Now we will move further to restrict list of values of find window as follows,
1- Application Name: General Ledger
2- Title: Accounting Flexfield
3- Structure: XX Accounting Flexfield


To achieve this we will write new record group sql quires, I will provide these, if you want to change these you need a technical person for that. Given quires are extracted from the request groups of standard form and if you want to change these it is recommended to have fresh copy of these quires from standard form.

First define record group for lov 'Application' and then assign that record group to item.

Query: select application_name, application_id from fnd_application_vl where  application_id = 101

Now define record group for 'Title' and assign that to respective item

Query: select id_flex_name,id_flex_code from fnd_id_flexs where application_id = 101 and id_flex_name = 'Accounting Flexfield'

 Now define record group for 'Structure' and assign that to respective item

Query: select id_flex_structure_name,id_flex_num from fnd_id_flex_structures_vl where application_id = 101 and ID_FLEX_STRUCTURE_NAME = 'WT Accounting Flexfield' and id_flex_code = 'GL#'



Now, user will have only one choice in find window lovs. 

Read More