Search from 700 + Posts

Sep 19, 2009

Query on Non-Database columns in Oracle Forms NOT working. What to do ?

In the world of Oracle Fusion , ADF Pages , JSP Pages , some of my customer are still build R12 extensions with Oracle form.

Recently I came across issue , where ends user was not able to do query on one of the field on the form.Form was very small and has only few columns.He was doing F11 , Putting search condition on Item_name field like 'Item%' and ctrl F11, but the system was displaying all the database records and not the one that starts with Item%.Well that's not a big deal.

On analysis I found that developer made the Item_name field as NON-Database column and kept database column 'Internal_item_name' as hidden .Also this internal_item_name database column is not storing user-friendly values.We try to convince end -user whether he can live with the values in Internal_item_name column , but he was not ok .As a consultant my job is "A Happy customer at the end of Day".

Immediately , I decided to create a View based on the Current table and the table that has column "Item Name" .I checked .fmb files from PVCS , Created new View. and change the data source from Table to newly created View.
Only good thing about my case is , this is a READ-ONLY form , so building form based on View
is no a issue .But in case we have READ-WRITE form then we need to write ON-INSERT/ON-UPDATE triggers at form Level or INSTEAD of Trigger at database level to handle insert/update.

No comments:

Post a Comment