Implementing Oracle Forms Customizations

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.

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 Applications 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 Applications 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:

• 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.

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;

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