Monday, June 9, 2008

Query Find Form/ Find Block Form

1. Open APPSTAND form in your current form first
2. drag and drop QUERY_FIND object from APPSTAND as
COPY onto c01_CHN_REP form.
After It copies in three places.
1. then Delete QUERY_FIND object group from your C01_CHN_REP form.



Query Find
There are two implementations for Query 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 Applications User Interface Standards for Forms–Based Products describe what situations are better served by the two implementations.
Query Find Window
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.



Step 1: Copy the Query_find object group from APPSTAND, by opening APPSTAND form. Delete the object group from your form. ( after they are copied you do not need the Object group). Rename the appropriate objects

Step 2:
Rename the New Block, Canvas and Window just now created please !!
Rename the Find Block, Canvas, and Window.
Set the Queryable property of the block to No. ( query Allowed = N )


For this example, rename the block, canvas and window to
QF_BLK, QF_CANVAS and QF_W


Step3:
Edit the NEW Button’s Triggerin the QF_BLK provided button.
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. The code you will add is :

app_find.new('C01_CHN_REP_BLK');

Step 4:
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(’’); becomes
:parameter.G_query_find := 'TRUE';
/* can place additional Validation code here */
fnd_message.set_string('Inside when-button-pressed:
Find');
fnd_message.show ;
app_find.find('C01_CHN_REP_BLK');
:parameter.G_query_find := 'FALSE';

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.

Step 5:
Set Navigation Data Block Properties
Set the Previous Navigation Data Block property of the
QF_BLK (Find ) block to be
the results block ( C01_CHN_REP_BLK). 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.

Step 6:
Edit the KEY–NXTBLK Trigger
Edit the KEY–NXTBLK trigger on the QF_BLK Find block so that it has the exact
same functionality as the FIND button.
If the user selects ”Go–>NextBlock,” the behavior should mimic pressing the FIND button.

:parameter.G_query_find := 'TRUE';
app_find.find('C01_CHN_REP_BLK');
:parameter.G_query_find := 'FALSE';

Step 7:
Change the Find Window Title
Change the title of the QF_W Find window.
The Channel example uses ”Find Channel Reps”.

Step 8:
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.

Step 9
Fit the Find Window to Your Form
Adjust your Find window for your specific case: resize the window,
position, fields, and so on.( very dangerous fear WATSON !! )
Create a PRE–QUERY Trigger
Create a block–level Pre–Query trigger in the Results block CHN_REP_BLK (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 (,’’);
:parameter.G_query_find := ’FALSE’;
END IF;

Example for our form in PRE-QUERY trigger on CHN_REP_BLK
if :parameter.G_query_find = 'TRUE'
then
copy (:qf_blk.qf_chn_rep_code,
'C01_CHN_REP_BLK.CHN_REP_CODE') ;
copy (:qf_blk.qf_NAME, 'C01_CHN_REP_BLK.NAME') ;

:parameter.G_query_find := 'FALSE';
end if ;
Step 11
Create a QUERY_FIND Trigger
Create a block–level user–named trigger ”QUERY_FIND”
(Execution Hierarchy: Override) on the CHN_REP_BLK Results block
that contains:

APP_FIND.QUERY_FIND(’’,
’,
’);

/* Result Window, Query window, Query Blk */
app_find.query_find('C01_CHN_REPS_W',
'QF_W',
'QF_BLK' ) ;

Step 12:

Raising Query Find on Form Startup
Note: 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.
Also Save version c01_chn_rep_V8.fmb


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.


Step 1: 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 data type and length appropriately.

For example, for the DEPT block, create a parameter called DEPTNO_QF.


Step 2: 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.

Step 3: Create a PRE–QUERY Trigger

Create a block–level PRE–QUERY trigger (Execution Hierarchy: Before) that contains:

IF: PARAMETER.G_QUERY_FIND = ’TRUE’ THEN
:= :parameter.;
: PARAMETER.G_QUERY_FIND := ’FALSE’;
END IF;

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;


Step 4: 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 (’’);

For DEPT:
APP_FIND.QUERY_FIND (’DEPT_QF’);

22 comments:

Oracle Applications Techno Functional said...

Good work...Ganjam...Thankyou for the post

Neeraj Shrivastava

Unknown said...

Hi Ganjam,
I tried following ur steps but when i reach at step 6 & try to find key-nxtblk trigger in other triggers of QF_BLK which i copied from appstand...it does not show me key-nxtblk in trigger's list....

I am new to Oracle Apps & learning apps technical content...Pls help me out for this..I'll be thankful to you.

Anonymous said...

good post ...worth it

Lakki Reddy Sreehari Reddy said...

Excellent Work.. Its very very usefull for beginers.. Thanks a Lot..

sandy said...

Hi murthy,

does the record history column in standard apps form disables when i implement query find window.

regards ,
ashish

Aandavar said...

Great post my friend

Anonymous said...

Great post, thanks for spending the time to write that up

Anonymous said...

Hi Murthy,

I am facing an error. Cannot find LOV: invalid id after pressing the find button. All the LOV's are working find when i go to the new mode.

Seetha said...

Hi,
Thank you for the good post. I have a question. I implemented the Query Find exactly as listed. But, after the first query find, the system is not enabling the searchlight.

How to enable the same.

Thanks
Ram

chandu said...

Hi Murthy,

Thanks a lot.it is very useful for
form developers.

I have one question,How to pass the values from one form to another form and one block to another block.

Thanks
Chandrasekhar B

Unknown said...

Ability to access On-Hand Qty Form from Sales Order lines
Call on-hand quantity form in sales order line form by passing item as the parameter.
how to pass the same parameter values of one form into another form.
my actual requirement is when i opened the sales order line form after enter the ordered item field then i will go to the tools menu and click on the on Hand quantity details form then it must take the values of ordered_item and subinventory into item and subinventory field of on hand quantity form and it must display the form.how to do this?
plz any body help as early as possible as u can do.

thanks in advance.

sap testing said...

This post defined query to find form/find block form. The procedure is given in steps which are explained in details. You can implement the steps and see the creation of the find block form. I think screenshot help to understand the better picture of find form.

shaik said...

Hi Murthy,

Thank you for the post.
I have a question.i have developed a query find form and it is working fine when i provide values in query find form and click on find button it is fetching data in form.The problem is when i open the query find form and closing the query find form without providing any values it is giving progress winow.
Please help me out in this.

Anonymous said...

Hi Shaik,

To avoid displaying the progress window please comment the below 2 lines in when-button-pressed trigger of FIND button.

--parameter.G_query_find := 'TRUE';
app_find.find('C01_CHN_REP_BLK');
--:parameter.G_query_find := 'FALSE';

Sandeep

raju said...

Hi Sandeep,

Still am getting progress window even after commented in FIND trigger.

could you help me out.

Raju

Anonymous said...

The steps have been put together very well, thank you.

Unknown said...
This comment has been removed by the author.
Unknown said...

i am trying create query_find block for search. i've changed the first nagivation block to query_find, but still the result block is first when open teh form, not the search block. any idea?

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

Alex Turner said...

Interesting, thanks!
Did you have an opportunity to try dbForge Studio for Oracle? https://www.devart.com/dbforge/oracle/studio/
I've heard a lot of good feedback for this tool