Friday, May 8, 2009

Oracle Apps-Changing LOV Query-Form Pesonalization

How to change a LOV query from Forms Personalization??

This is one of the requrirement that we want to add a additional condition to the LOV query or you want to populate the LOV all together from your SQL.
Its a simple four step process

Step1:
Get the Query that of the LOV of the standard form. You can do it by opening the standard form in the form builder or Get the query by taking the trace of the form and getting the query from the trace..

Step2:
If you want to add only a few more conditions to the query just add the conditions
In other case create a view with column same as in the standard query.

Step3:
In this step on when new form instance we will create the record Group from query

Step4:
Attach the record group to the LOV you want to change…

Step1:Get the query
The standard query is

select event_name, description from fa_maint_schedule_dtl WHERE EVENT_NAME LIKE :1

I want to populate from my own sql so I create a view

create or replace view XX_AMC_EVENT_DETAILS as
select 'SERVICE' EVENT_name ,'ATM SERVICE' DESCRIPTION from dual
union
select 'AMC' ,'AMC CONTRACT' from dual
union
select 'TEST1' ,'TEST2' from dual

Step2:
Plz find teh screen shots attached..




9 comments:

madan said...

Murthy Ganjam,

We thanks for providing such a wonderful document on personalization and really appreciate your presentation and detailed steps.
Keep up the good work.

Thanks and Regards,

Jithendra said...

Hi Murthy,

Can we assign a new LOV (on the fly) to a text item field using Personalization?

If yes, please throw some light on it.

Thanks,
Jithendra

Salaah said...

Much thanks Murthy, this was exactly what we needed.
One note - When we first tried it, we were getting the error "FRM-41826 Cannot replace group: columns don't match LOV". This was solved by using to_char on all number columns in the query for the lov. Then you have to alias with the original column name. So the query would look like : select to_char(some_number_col) some_number_col, some_text_col from .....

Arivazhagan said...

Excellent Post. But it doesn't work for one particular issue of mine.
I am on version R12(12.1.3).
I have to restrict the Payment method field in the Receipts Form (Application=AR).
This field is associated with LOV RGW_PAYMENT_METHOD_MISC and record group RGW_PAYMENT_METHOD_MISC.
However, this LOV RGW_PAYMENT_METHOD_MISC is not available to be chosen as a target object.
Regardless, I manually entered this value in target object and pointed it to the custom record record group.
When i try to apply, i get a run time error FRM-41072: Cannot create Group.
Welcome any suggestion to make this work

Regards
Ariv

Sridevi Koduru said...
This comment has been removed by the author.
Vamshi Surapaneni said...

Hi Arivazhagan,

How you resolve your Problem for error FRM-41072 same issue i am facing can you please help on this issue i am doing in inventory Module for Miscellaneous Transactions.

ronak tawde said...

Hi All,

I have a requirement to Restrict PAY Group LOV user Wise in AP Invoice Header.
User can See Specific PAY Group according custom lookup.

Please guide me how to define form personalization.

Regards,

Ronak

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.