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

How to Make a Form Query only?

Hi all,
This is one of the common requirement.They are many ways of doing this like formpersonalization ,Cutom.plland others ..the easiest way to do it is by passing the parameter QUERY_ONLY=YES..
But it doesn't gurantee all the forms will behave as expected because the form should be coded to make it work for this..not all standard forms have this..
In HRMS Forms case there is another concept called task flow by which we can make the form read only..(i will provide a article on this at some other time)..
Steps:
1.Check the form which need ot be made as query only.
2.Go to the menu to which it is attached and the get the user function name of the form
3.query the function from application developer(resp)-->application-->function

4.query the function you have to modify
5.Create a new function logically same as the old one (attach the same form)
6.Go to Form tab in the functions window.

7.Enter QUERY_ONLY="YES" in the parameters window
8.Attach the new view only function to the required menu..
Using Form Personalization:

My requrirement is to not all the users to add new responsibilities or new user with responsibilities..it is only allowed for certain people..lets see this sceniro implementation throught form personalization
1.Go to the form which needs to be personalized
2.Open the personalization screen

3.create a rule at when new form instance level


make update,delete,insert allowed property to false to the block you want to remove the funcionaltiy
4.save the record..
if you want to give access to only one user sysadmin
create one rule to remove access by making delete ,insert and update allowed false..
Create another rule at new form instance level , context-> user and sysadmin and then make insert ,delete,update allowed true..
These two rules will make that only sysadmin to attach new responsibilities..

Forms Personalization -1

What is forms Personalization?

It is new features where some of the customization can be handled easy by simply setting the rules from the front end.It is available from 11.5.10.2

Why do we need forms Personalization?

For example in scenarios like

1.Change the prompts

2.Display some custom message when certain conditions are met

3.Enable and disabling fields/buttons based on conditions

4.For Enabling special menu.(like opening another reference form from the current form)

IF both Form personalization and custom pll are done for a form which one will take effect first?

Form personalization first and then only custom pll

How to start doing it?

Open the form where you want to personalize .

Menu Navigation: Help ->Diagnostics->Custom Code -> Personalize

This should open the personalization Form.

There are two profile options which will decide whether you able to see or not

Profiles:

Utilities: Diagnostics = Yes/No
Hide Diagnostics = Yes/No

There is new option available for level to define whether the rule need to be applied at form level or function level.Because a form can be attached to multiple functions.

Insert Get Expression & Insert item value button help you in building the conditional statement , very help for non technical people.

The major thing to observe are

1.SEQ: The sequence in which the rules are executed

2.Description:Description of the Rule

3.Level -- whether it is at form level or Function level..

4.Condition –what is the event and the object where the below condition need to

be checked

5.Action-- what actions it should perform when the condition is met

6.Context -- same a profile option levels at what level the rule need to be applied

Under actions

SEQ: it gives the list of actions that need to be performed when the condition is met

Type:1.Property – used for changing the properties of field

Message –used to display message when the condition is met

Built in --used to calling some standard form and AOL functionalities

Menu -- used in case of enabling special menu’s

General & Important Observations:

All actions and rules can be enabled and disabled(using checkbox) or deleted using delete option on the tool bar

Always validate the rule after you create using validate button

Save the record before you close this personalization form

To move form personalization from one instance to other there is FNDLOAD utility available .

Check the site for FNDLOAD examples…

Standard Forms Customization(Part1--Custom PLL )Draft

There are different ways to handle Forms Level Customizations.
1.Using Custom.PLL
2.Using Forms Personalizations
3.Copy the Standard form Object and Change the Code

First lets see Custom.pll how to use it?


What is Custom.PLL??
The CUSTOM.pll library is a standard Oracle Forms PL/SQL library that is supplied by Oracle with the Oracle
Applications. This is Oracle’s built-in feature that allows the customer to enhance the standard functionality of the
Applications by implementing site-specific business rules. Every Oracle Forms -based eBusiness screen, and any
custom form developed using the Oracle Application development standards, will access the CUSTOM library.
This makes an ideal point of creating business rules that effect the entire organization.

Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.

How to add code to this ?
open this pll using the Form builder.make changes to the program units

How to compile this PLL ?
Once you make changes you need to compile the pll.use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special

What are Different Triggers that is supported?

WHEN-NEW-FORM-INSTANCE – initially entering a form

WHEN-NEW-BLOCK-INSTANCE – entering a zone (or block) within a form

WHEN-NEW-ITEM-INSTANCE – moving into a new field within the form

WHEN-NEW-RECORD-INSTANCE - creating a new record

WHEN-FORM-NAVIGATE – navigating thru a form using the mouse

WHEN-VALIDATE-RECORD – saving (committing) the information to the database

EXPORT – triggered by using the Export feature Some events are field specific

ZOOM – Pre -11 feature for moving to another form and querying up specific records

Some events are form specific

SPECIALn - (where n is a number between 1 and 45) used to generate entries in the ‘Special’ menu of the
tool bar and the code is triggered by selecting a menu choices from the ‘Special’ option on the toolbar

KEY-Fn – (where n is a number between 1 and 8) triggered by pressing the corresponding function key

Some events are application specific:
Application Object Library

WHEN-LOGIN-CHANGED – when a user logs on as a different user
WHEN-RESPONSIBILITY-CHANGED – when a user changes responsibilities
WHEN-PASSWORD-CHANGED – when a user changes their password

How to make changes get affected?
Once you make the changes compile the pll and generate the PLX
Since the CUSTOM library is loaded once for a given session, a user must log out of the
application and sign-on again before any changes will become apparent.



Examples--Metalink:

1. Sample code to make all the responsibilities read only for a specific user.
BEGIN

IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='' THEN

BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);

WHILE (blockname is not null) LOOP

IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN

SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);

END IF;

blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);

END LOOP;


END query_only_mode;


END;

2.How does one restrict or reduce the LOV?
"The customer LOV can be overriden using the when-new-item-instance or when-new-form-instance event at the form level through CUSTOM.pll."
You will need to write custom code using that specific event in the custom.pll

Some sample code
if (event_name = 'WHEN-NEW-FORM-INSTANCE' and form_name = 'form name')then
r:=find_group('group name');
if not id_null(r) then
delete_group('group name');
end if;
v:='select colum1,column2
from table';

r:=create_group_from_query('group name',v);

set_lov_property('lov NAME',group_name,r);--lov

See that the column names should be same as the old query so that the mappings still holds good



3.How to make the attachment function in specific responsibilities to act as read-only mode so that users who log into these specific responsibilities can only view attachments, while for the rest of the responsibilities allow users to add, update and delete attachments?


// Source File Name: custom.pll
// Source File path: $AU_TOP/resource

form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'FNDATTCH') then
if (FND_GLOBAL.RESP_NAME Like '') then --
Set_item_Property( SEQ_NUM, ENABLED,PROPERTY_FALSE);
Set_item_Property( CATEGORY_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DOCUMENT_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DATATYPE_NAME, ENABLED,PROPERTY_FALSE);
Set_item_Property( FILE_NAME_DISPLAY, ENABLED,PROPERTY_FALSE);
end if;
end if;
end if;

4. How to make the customisation CustomPO Number not less than PO 4 digits in sales order form?

procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
item_name varchar2(30) := name_in('system.cursor_item');

Begin
if (form_name = 'OEXOEORD'and block_name = 'ORDER') then
if LENGTH(name_in('ORDER.CUST_PO_NUMBER')) > 3 then
fnd_message.set_name('FND','Cust PO Number should be less than 4 digits');
fnd_message.Error;
RAISE FORM_TRIGGER_FAILURE;
End if;
End if;
End Event;