Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 21 June 2012

Creating LOV [LIST OF VALUES]

An LOV is a scrollable popup window that provides the end user with either a single or multi-column selection list. It is defined at the form level, which means you can use them to support text items in any block of the form module.
Default Key for LOV – F9

LOVs provide the following functionality and Benefits:
ü LOV can be invoked from any text item, or from outside a text item if called programmatically.
ü LOV auto-reduction and search features allow end users to locate specific values.
ü LOV values that are selected by the end user can be assigned to form items according to the return items you designate.
ü At design time, an LOV can be attached to one or more text items in the form.
ü You can use the same LOV to support several items
ü You can design LOVs to reuse data already loaded into the form, instead of accessing the database for every call.
ü LOV values are derived from record groups.

Creating (LOV)
§   Select the Lov wizard from Employee Layout.
§   Write the query Text: SELECT DEPARTMENT.DNUMBER, DEPARTMENT.DNAME FROM DEPARTMENT Include both dname, dno column on Lov column
§   Select return on dno
§   Finally change the properties of New loves such as:
Automatic Display: yes
Automatic Refresh: yes
Automatic Select: yes
Automatic Position: yes
Creating (LOV) on Non-Base table field
§   Create a new form called departmg.fmb that includes all of the columns in the DEPARTMENT table. In the layout wizard, use the Tabular layout and display 5 records in the form. Add a scrollbar.
§   In the Layout Editor, create a new text item called MGR_LAST_NAME in the DEPARTMENT data block. Be sure the DEPARTMENT block is selected when the new item is created. Change the properties of MGR_LAST_NAME so it is not a Database item.
Add a text label to this new MGR_LAST_NAME field.
§   Create a list of values called LOV_MGR based upon the query: SELECT ssn, lname  FROM  employee
§   Map the employee.ssn column to the :DEPARTMENT.MGRSSN field.
Map the employee.lname column to the :DEPARTMENT.MGR_LAST_NAME field.
§   Create a POST-CHANGE trigger on the MGRSSN item so that when it changes, the following code will execute:
BEGIN
SELECT lname
INTO   :DEPARTMENT.MGR_LAST_NAME
FROM   employee
WHERE  employee.ssn = :DEPARTMENT.MGRSSN;
END;
SHOW_LOV
Displays a list of values (LOV) window at the given coordinates, and returns TRUE if the operator selects a value from the list, and FALSE if the operator Cancels and dismisses the list.
SHOW_LOV( lov_id);
SHOW_LOV( lov_id, x, y);
SHOW_LOV( lov_name);
SHOW_LOV( lov_name, x, y);
Example: Built–in: SHOW_LOV: Display a named List of Values (LOV)
DECLARE
   a_value_chosen BOOLEAN;
BEGIN
   a_value_chosen := Show_Lov(’My_lov’);
   IF NOT a_value_chosen THEN
      Message(’You have not selected a value.’);
      Bell;
      RAISE Form_Trigger_Failure;
   END IF;
END;

LOV Properties:
1. GET_LOV_PROPERTY (lov_id, property LOV);
Returns information about a specified list of values (LOV).
2. SET_LOV_PROPERTY(lov_id LOV, property NUMBER, value NUMBER);
Sets the given LOV property for the given LOV.
3.GROUP_NAME
Specifies the name of the record group on which an LOV is based.
Set_LOV_Property('my_lov', GROUP_NAME, 'new_group');
4. SET_LOV_COLUMN_PROPERTY
Sets the given LOV property for the given LOV.
SET_LOV_COLUMN_PROPERTY (lov_id LOV,colnum NUMBER, property NUMBER,value VARCHAR2);
5. Automatic Skip (LOV) property
Moves the cursor to the next navigable item when the operator makes a selection from an LOV to a text item. When Automatic Skip is set to No, the focus remains in the text item after the operator makes a selection from the LOV.
6. Column Mapping
This property is used to specify the return item. If it is not specified then value from Lov cannot assigned to the block.
7. Long List
It is used to append a ‘ where clause ‘ to the select statement. When this property is true a dialog box appear. In this dialog box any value can be entered, from this value entered the where clause constructed.
8. LOV for Validation [ Yes / No ]
If it is true so that the system check value entered with the list of values
Validation from LOV – text item property - Validation from Lov to text item.
9. Title property
Specifies the title to be displayed for the object.
10. Automatic Refresh property
Determines whether Form Builder re-executes the query to populate an LOV that is based on a query record group.
11. Automatic Display property
Specifies whether Form Builder displays the LOV automatically when the operator or the application navigates into a text item to which the LOV is attached.
12. Automatic Select property
Specifies what happens when an LOV has been invoked and the user reduces the list to a single choice when using auto-reduction or searching:
When Automatic Confirm is set to Yes, the LOV is dismissed automatically and column values from the single row are assigned to their corresponding return items.
When Automatic Confirm is set to No, the LOV remains displayed, giving the operator the option to explicitly select the remaining choice or dismiss the LOV.

Key-LISTVAL Trigger: List_Values;
This trigger, as written, is of little value since it merely duplicates default Form Builder functionality. However, using a Key-LISTVAL trigger allows you to add subsequent PL/SQL statements that execute after the LOV is displayed, but before Form Builder returns to the normal event sequence.
In the following example, an IF statement is added to the previous trigger:
Key-LISTVAL Trigger: List_Values;
IF :Department.id IS NULL THEN
    Go_Item ('department.id');
END IF;

Record Group:
A record group is a column-and-row structure stored within Forms Runtime memory and is similar to the structure of a database table. It holds records that can be reused by Oracle Developer applications, hence reducing repeated access to external data.
Record groups can provide the following:
§   Data that is presented by LOVs
§   Data for dynamic list items
§   Data to be passed to Report Builder and Graphics Builder
§   Other application-defined uses
Note: Because LOVs and record groups are separate objects, you can create multiple LOVs based on the same record group.
Click on the LOV wizards and Type the SQL query for LOV
SELECT name, id FROM s_customer
ORDER BY name
Avoid very large queries—use restrictions.
§   Use column in LOV to validate user input—place this column first in SELECT list.
§   Define return items later, or use optional INTO clause.
§   Use optional WHERE, GROUP BY, and ORDER BY clauses.

0 comments:

Post a Comment