There are two implementations for Query Find (also known as View Find). One implementation shows a Row-LOV that shows the available rows and allows you to choose one. The other implementation opens a Find window, which shows you the fields the user is likely to want to use for selecting data.
Use only one implementation for a given block. All queryable blocks within your form should support Query Find. The Oracle E-Business Suite User Interface Standards for Forms-Based Products describe what situations are better served by the two implementations.
If you want a Row-LOV or Find window to raise immediately upon entering the form, at the end of your WHEN-NEW-FORM- INSTANCE trigger, call:
EXECUTE_TRIGGER('QUERY_FIND');
This will simulate the user invoking the function while in the first block of the form.
To implement a Row-LOV, create an LOV that selects the primary key of the row the user wants into a form parameter, and then copy that value into the primary key field in the results block right before executing a query.
This example uses the DEPT block, which is based on the DEPT table, and consists of the three columns DEPTNO, DNAME and LOC. This table contains a row for each department in a company.
Create a form parameter(s) to hold the primary key(s) for the LOV. If the Row-LOV is for a detail block, you do not need a parameter for the foreign key to the master block (the join column(s)), as you should include that column in the WHERE clause of your record group in a later step. Set the datatype and length appropriately.
For example, for the DEPT block, create a parameter called DEPTNO_QF.
Create an LOV that includes the columns your user needs to identify the desired row. If the Row-LOV is for a detail block, you should include the foreign key to the master block (the join column(s)) in the WHERE clause of your record group. Return the primary key for the row into the parameter.
For our example, create an LOV, DEPT_QF, that contains the columns DEPTNO and DNAME. Set the return item for DEPTNO into parameter DEPTNO_QF. Although the user sees DNAME , it is not returned into any field.
Create a block-level PRE-QUERY trigger (Execution Hierarchy: Before) that contains:
IF :parameter.G_query_find = 'TRUE' THEN <Primary Key> := :parameter.<Your parameter>; :parameter.G_query_find := 'FALSE'; END IF;
For multi-part keys, you need multiple assignments for the primary key.
The parameter G_query_find exists in the TEMPLATE form.
For the Dept example, your PRE-QUERY trigger contains:
IF :parameter.G_query_find = 'TRUE' THEN :DEPT.DEPTNO := :parameter.DEPTNO_QF :parameter.G_query_find := 'FALSE'; END IF;
Finally, create a block-level user-named trigger QUERY_FIND on the results block (Execution Hierarchy: Override) that contains:
APP_FIND.QUERY_FIND('<Your LOV Name>');
For DEPT:
APP_FIND.QUERY_FIND('DEPT_QF');
To implement a Find window, create an additional window that contains the fields a user is most likely to search by when they initiate the search and copy all the item values from that block into the results block just before executing a query.
In this example, there is a block based on the EMP table. This is referred to as the results block. The primary key for this table is EMPNO. This block also contains the date field HIREDATE. The Find window is designed to locate records by EMPNO or a range of HIREDATES.
For more information on the look and feel of Find windows, see the Oracle E-Business Suite User Interface Standards for Forms-Based Products.
Flexfields in Find windows require special treatment. See the section Using Key Flexfields in Find Windows for information.
Copy the QUERY_FIND object group from the APPSTAND form to your form. It contains a window, a block and a canvas from which to start building your Find window.
After you copy it, delete the object group. This leaves the window, canvas and block, but allows you to copy the object group again if you need another Find window.
Warning: DO NOT REFERENCE THIS OBJECT GROUP; you need to customize it.
Rename the Find Block, Canvas, and Window. Set the queryable property of the block to No.
For this example, rename the block, canvas and window to EMP_QF, EMP_QF_CANVAS, and EMP_QF_WINDOW, respectively.
Edit the WHEN-BUTTON-PRESSED trigger for the NEW button in the Find window block so that it passes the Results block name as the argument. This information allows Oracle E-Business Suite to navigate to your block and place you on a new record. This button is included because when you first enter a form, the Find window may automatically come up; users who want to immediately start entering a new record can press this button.
app_find.new('<Your results blockname here>');
becomes
app_find.new('EMP');
Edit the WHEN-BUTTON-PRESSED trigger for the FIND button so that it passes the Results block name. This information allows Oracle E-Business Suite to navigate to your block and execute a query.
app_find.find('<Your results blockname here>');
becomes
app_find.find('EMP')
If you need to do further validation of items in the Find window, place your code before the call to APP_FIND.FIND. Specifically, you should validate that any low/high range fields are correct. You may also give a warning if no criteria has been entered at all, or if the criteria entered may take a very long time to process.
Set the Previous Navigation Data Block property of the Find block to be the results block. This allows the user to leave the Find window without executing a query.
From the results block, next and previous data block only move up and down the hierarchy of objects; they never take you to the Find window.
Edit the KEY-NXTBLK trigger on the Find block so that it has the exact same functionality as the FIND button. If the user selects "Go->Next Block," the behavior should mimic pressing the FIND button.
Change the title of the Find window.
The EMP example uses "Find Employees".
Create the items that the user can query on in the Find window block. You may find it convenient to copy items from the Results block to the Find window block.
Follow these guidelines for items in the Find window:
Set the Required property to No
Set the default value to NULL
If you copied the items from the Results block, ensure that your new items all have Database Item set to No, and remove all triggers associated with them (especially validation triggers). If for some reason you decide you need to keep a particular trigger, remember to change the fields it references to point to the Find block.
Typically, an item in the Find window block has an LOV associated with it, because users should usually be able to select exactly one valid value for the item. The LOV should show all values that have ever been valid, not just those values that are currently valid. Date fields may use the Calendar and the related KEY-LISTVAL trigger.
If you have an item that has a displayed value and an associated ID field, the Find window block should have both as well. The ID field should be used to drive the query to improve performance.
Items that are check boxes or option groups in the Results block should be poplists in the Find window block. When they are NULL, no restriction is imposed on the query.
Adjust your Find window for your specific case: resize the window, position, fields, and so on.
Create a block-level Pre-Query trigger in the Results block (Execution Hierarchy: Before) that copies query criteria from the Find window block to the Results block (where the query actually occurs).
You can use the Oracle Forms COPY built-in to copy character data. For other data types, you can assign the values directly using :=, but this method does not allow the user to use wildcards. However, most of your Find window items use LOVs to provide a unique value, so wildcards would not be necessary.
IF :parameter.G_query_find = 'TRUE' THEN COPY (<find Window field>,'<results field>'); :parameter.G_query_find := 'FALSE'; END IF;
A commonly used 'special criteria' example is to query on ranges of numbers, dates, or characters. The APP_FIND.QUERY_RANGE procedure is defined to take care of the query logic for you. Pass in the low and high values as the first two arguments, and the name of the database field actually being queried on as the third argument.
In our EMP example,
IF :parameter.G_query_find = 'TRUE' THEN
COPY(:EMP_QF.EMPNO, 'EMP.EMPNO');
APP_FIND.QUERY_RANGE(:EMP_QF.Hiredate_from,
:EMP_QF.Hiredate_to,
'EMP.Hiredate');
:parameter.G_query_find := 'FALSE';
END IF;
Your base table field query length (in the Results block) must be long enough to contain the query criteria. If it is not, you get an error that the value is too long for your field. All fields should have a minimum query length of 255.
If you have radio groups, list items, or check boxes based on database fields in your Results block, you should only copy those values from the Find window if they are not NULL.
If you ever need to adjust the default WHERE clause, remember to set it back when you do a non-query-find query.
Create a block-level user-named trigger "QUERY_FIND" (Execution Hierarchy: Override) on the Results block that contains:
APP_FIND.QUERY_FIND('<results block window>',
'<Find window>',
'<Find window block>');
In our EMP example:
APP_FIND.QUERY_FIND('EMP_WINDOW', 'EMP_QF_WINDOW',
'EMP_QF');