This Oracle form builder tutorial explains the how to achieve select/unselect all checkbox functionality in Oracle Apps.
Select all – when the master checkbox is checked then all child checkboxes on the form should be checked.
Unselect All – when the master checkbox is unchecked then all child checkboxes on the form should be unchecked.
This functionality is helpful to select and process multiple records at a time. In forms, you can use GO_BLOCK, GO_RECORD built-in and for loop to achieve this. Oracle Apps has built-in wrapper APP_RECORD.FOR_ALL_RECORDS on go_block, go_records which you can use to achieve it.
This is how it should work,
- Uncheck Master Check Box – All child checkbox should get unchecked
- Check Master Check Box – All child checkbox should get checked
- Uncheck child checkbox – Master Check box should get unchecked.
This tutorial assumes basic working knowledge of Oracle Form Builder in Oracle Apps Environment.
- Oracle Developer Suite 10G
- Oracle Apps 11.5.10 or 12.1.3
This procedure executes the specified trigger for every record of the current block or the specified block. If you specify a block, the GO_BLOCK built-in fires. When finished, the cursor returns to the original record and item.
If the trigger fails, FORM_TRIGGER_FAILURE is raised and the cursor is left in the record in which the failure occurred.You can pass arguments to the specified trigger using global variables simply by setting the global variables before calling this routine. APP_RECORD.FOR_ALL_RECORDS fires once when there are no queried records.
procedure APP_RECORD.FOR_ALL_RECORDS(block_name varchar2, trigger_name varchar2); procedure APP_RECORD.FOR_ALL_RECORDS(trigger_name varchar2);
block_name – The name of the block to navigate to
trigger_name – Name of the trigger to execute
Create a custom form
Create a custom form as shown below on any data source. I am using DBA_OBJECTS table to create a form.
Below is the layout of the form.
Let me show you object navigator view.
Code and Logic
It is a non-database block with a checkbox MASTER_CHECK_BOX.
|MASTER_CHECK_BOX||CHECK BOX||Non-Database Checkbox with Value Y and N.|
Add a trigger WHEN-CHECKBOX-CHANGED trigger for MASTER_CHECK_BOX and add below code. This trigger fires when checkbox is checked or uncheck.
It is a database block based on DBA_OBJECTS with the below items.
Item Name Type Description CHILD_CHECK_BOX Check Box Non-Database Checkbox with Value Y and N.
|CHILD_CHECK_BOX||Check Box||Non-Database Checkbox with Value Y and N.|
|OWNER||Text Item||Database Item|
|OBJECT_NAME||Text Item||Database Item|
|SUBOBJECT_NAME||Text Item||Database Item|
|OBJECT_TYPE||Text Item||Database Item|
|STATUS||Text Item||Database Item|
Note:- As this procedure works on all the items fetched on forms irrespective of the displayed item. Restrict the number of the item to 10 by setting Maximum Record Fetched to 10 at the block level. This will only fetch 10 records; else it will take a lot of time as DBA_OBJECTS may have millions of records.
It is a user-defined trigger at CHILD block level.
This code simply code value of MASTER_CHECK_BOX to CHILD_CHECK_BOX i.e if master checkbox is check, check all child box
:CHILD.CHILD_CHECK_BOX := :MASTER.MASTER_CHECK_BOX;
IF :CHILD.CHILD_CHECK_BOX ='N' THEN :MASTER.MASTER_CHECK_BOX := 'N'; END IF;
FTP form to respective custom_top and compile using frmcmp_batch command. Register form with any Responsibility using Application Developer Responsibility.
That’s it and you have designed a master checkbox to control child checkbox