Skip Headers

Oracle E-Business Suite Developer's Guide
Release 12.1
Part Number E12897-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Enabling Query Behavior

Overview of Query Find

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.

Raising Query Find on Form Startup

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.

Implementing Row-LOV

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 Parameter for Your Primary Key

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

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 PRE-QUERY Trigger

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; 

Create a QUERY_FIND Trigger

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'); 

Implementing Find Windows

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 APPSTAND

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 Block, Canvas and Window

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 NEW Button's Trigger

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 FIND Button's Trigger

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 Navigation Data Block Properties

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

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 Find Window Title

Change the title of the Find window.

The EMP example uses "Find Employees".

Create Necessary Items

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:

Fit the Find Window to Your Form

Adjust your Find window for your specific case: resize the window, position, fields, and so on.

Create a PRE-QUERY Trigger

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;

Create a QUERY_FIND Trigger

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');