In Oracle E-Business Suite release 11.5.10.2 some list of values (LOV) are not implemented considering the full functionality of the e-Business Suite. The " Project LOV’s" in Payables Invoices (f.e.) always shows all project tasks and expenditure types. Tasks and/or expenditure types, excluded for charges, are shown also. "Charge Allowed flags" and "Transaction Controls" in the Projects module aren’t taken into account in these LOV-queries.
Customers do not like this "feature" ! Being able to select tasks/expenditure types and "hearing afterwards (validation)" that tasks/expenditure types are not allowed for costing. Can we solve this LOV-problem using "Form Personalization (see note 279034.1 on Metalink)" … as long as the standard functionality isn’t implemented as it should be …?
The Form Personalization feature in Oracle e-Business Suite 11.5.10 allows you to 
declaratively alter the behavior of Forms-based screens, including changing properties, executing builtins, displaying messages, and adding menu entries. Simple LOV properties can also be modified using Form Personalization, including GROUP_NAME, HEIGHT, TITLE, WIDTH, X_POS and Y_POS. At this point in time, the property RECORD_GROUP_QUERY, which would allow you to dynamically change the underlying query of an LOV, cannot be changed. Oracle is considering this functionality for a future release (reference:FormPers_ML_RUP4 Oracle EBS 11.5.10 document on metalink). So, it’s obvious. Forms Personalization is no option in release 11.5.10.
…but maybe there is a solution in e-Business Suite release 12 ….
Oracle e-Business Suite release 12 is an important new release but setting the LOV-property "RECORD_GROUP_QUERY" using Form Personalization isn’t supported. The LOV issue (Payables Invoice project tasks and expenditure types), as mentioned above, isn’t solved in the standard functionality either
… Optimizing LOV-queries wasn’t a point of attention during the definition of the SWAN user interface … ?
LOV-queries may be optimized in future releases. Hopefully by releasing LOV-queries that do consider Projects functionality as "Allow Charges" and "Transaction Controls". If not, new versions of "Form Personalization" may offer workarounds. Let’s wait for release 12.x …. or Fusion
Hi,
You can create a record group for LOV at run time thru personalization.
Just open the personalization form, go to Actions tab, Give a sequence, choose Buit in from the lov in TYPE item, Give an appropriate desc.,then in right hand side there is Built in type, select ‘Create Record Group from Query’, then in Arguement, Key in the query, then give in the group name…like XX_abc_XYZ_RG.
Hi,
I have also same requirement. I am trying the solution given by Rodney Stephens , Apr 06, 2007. I am not able to find how to create a record group from query in forms personalization?
Can somebody tell me how to do this?
Thanks
Hi,
Thanks for all comments on this topic.
Indeed it’s is possible to change lov’s by using built ins.
Regards,
Bert
Hi Guys,
Yes i agree it works with ATG RUP3 onwards. Oracle has added new Built-ins to support this functionality.
I don’t have to touch the custom.pll to achieve this.
Thanks,
Nitin jain
Rodney can you pls tell me how to do the modification in the LOV query using forms personalization, I’m facing the problem why try to filter the data based on form values
Cool feature. Will save lot and lot of backend work.
Rodney,
Thanks for your comment. Can you tell me which release of EBS you are talking about?
Is it available in 11.5.10.2 ? Ididn’t find the lov-query-property ? Obviously you did …
Can you tell me where you found the lov property to assign a record group to the lov?
The latest version of forms personalization does allow you to modify the record group query. I have done this on a number of project forms to limit the list of employees to the organization of the responsiblity accesing the form. Project expenditures inquiry for example. Create a new record group from query in forms personalization, and assign the group to the lov.
Hi Nitin,
The only workaround is changing the list of value by adding a customization in your XX_custom_pll (this one is linked to the standaard custom.pll). Select the right event and the richt place (form and block name) to activate your customization. The example below shows the custom-code we used to change the record-group (content) of a list of values using xx_custom.pll.
In this example we wanted to restrict the number of customers shown (in list of values), creating a contract project. The customer-list of values is retricted to customers having an address in the operating unit of the reponsibility your in, instead of showing customers having one or more addresses in another operating unit(s).
— Show customers with an address in your operating unit (sub query multi-org view ra_addresses) instead of showing customers having an address in other operating units only.
if form_name = ‘PAXPREPR’ and (p_event_name = ‘WHEN-NEW-BLOCK-INSTANCE’) and (block_name) = ‘OVERRIDE’
then
declare
l_rg_id recordgroup;
l_rg_name varchar2(15) := ‘klant’;
l_errcode number;
l_query varchar2(3000);
begin
l_rg_id := find_group(l_rg_name);
if id_null(l_rg_id)
then
l_query := ‘select v.customer_name
, v.customer_id
, v.customer_number
from pa_customers_v v
where exists (select 1
from ra_addresses adr
where adr.customer_id = v.customer_id)
and v.status = ”A”
order by v.customer_name’;
l_rg_id := create_group_from_query(l_rg_name,l_query);
l_errcode := populate_group(l_rg_id);
set_lov_property(‘CUSTOMERS_OVERRIDE’,group_name,l_rg_name);
end if;
end;
end if;
In the original form (in this examaple PAXPREPR) you will find the name of the record group you need to set.
So, the only way to change the content of Form-list of values in the current releases of the e-Business Suite is setting the record group on run time. You can implement this using your xx_custom.pll.
Of course you can also make a customized version of a standard form, but you shouldn’t do this on a regular basis (few exceptions only) !
Nitin, I hope this will do !
Hi Bert,
Thanks for clarifying this, but do we have a work-around to this, especially now that we know that FP cannot serve this requirement. The reason why i am asking this is that this LOV issue is not only in Projects but also in Inventory & OM. The LOV’s for serial# and Install Base instances are the best examples, truly designed by oracle for users to commit mistakes :)-
Please let me know if you have any work-around to this issue and i’ll be extremely thankful to you.
Thanks,
Nitin jain