Validation Type 'Table' in Value sets in Oracle
Definition:- A table-validated value set provides a predefined list of values like an
independent set, but its values are stored in an application table.
You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set.
Lets create a table validated value set.
Press "Edit Information" button to add details about table/view/query used for table validation.
Table Application:- Mention the relevant application to which table belongs.
Table Name:- You may mention the table name, view or query in this field.
To enter query, enter it in this form i.e. (select employee_number en, full_name fn from per_all_people_f). In my point of view, if data is not fully accessible through table, use a view rather than query.
Because once you entered the query and saved the value set, and applied it to many fields, you will not be able to edit the value sets' query. You will have to remove value set from all the fields.
Value:- Table column values which are shown for selection on the field.
Meaning:- Displays the value outside field, once any selection is made on the field.
ID:- The value which is returned to be saved in the table, or is returned in the report.
Where/Order by:- Name defines itself. To put where clause or order by clause. But you need to follow the order.
Additional Columns:- Any column which is to be shown parallel to one we select in Value field.
NOTE:
if you want to show multiple columns in parallel, keep the meaning field empty and place column names separated by comma in Additional Columns field and also specify their width.
If you specify only the SQL fragment but no alias or width, your column does not show up.
Specify Additional Columns in this format i.e.
Test and save the value set.
After i applied the value set to a field, result was like shown below.
Also see: Dependent Value sets in Oracle
Value Sets in Oracle Apps
Leave a comment for any query.
You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set.
Lets create a table validated value set.
Press "Edit Information" button to add details about table/view/query used for table validation.
Table Application:- Mention the relevant application to which table belongs.
Table Name:- You may mention the table name, view or query in this field.
To enter query, enter it in this form i.e. (select employee_number en, full_name fn from per_all_people_f). In my point of view, if data is not fully accessible through table, use a view rather than query.
Because once you entered the query and saved the value set, and applied it to many fields, you will not be able to edit the value sets' query. You will have to remove value set from all the fields.
Value:- Table column values which are shown for selection on the field.
Meaning:- Displays the value outside field, once any selection is made on the field.
ID:- The value which is returned to be saved in the table, or is returned in the report.
Where/Order by:- Name defines itself. To put where clause or order by clause. But you need to follow the order.
Additional Columns:- Any column which is to be shown parallel to one we select in Value field.
NOTE:
if you want to show multiple columns in parallel, keep the meaning field empty and place column names separated by comma in Additional Columns field and also specify their width.
If you specify only the SQL fragment but no alias or width, your column does not show up.
Specify Additional Columns in this format i.e.
column_name_1 "Column 1 Title"(width), column_name_2 "Column 2 Title"(width), ...
Test and save the value set.
After i applied the value set to a field, result was like shown below.
Also see: Dependent Value sets in Oracle
Value Sets in Oracle Apps
Leave a comment for any query.
Good Post.
ReplyDeleteGood Post.
ReplyDeletegreat
ReplyDeletegoood work...thanx for sharing..
ReplyDeletewhat is the behind query you have written to populate list of values(LOV) in form ,i also created like above but not able to populate list of values.
ReplyDeleteplease provide me query .
very urgent.
Thanks in adnvane
Good Post.
ReplyDeletetype C phones